-- Astrabank Banking System (MVP+) - PHP/PDO + MySQL
-- Run this file in your database.

SET sql_mode = 'STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION';
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(30) NULL,
  kyc_status ENUM('unverified','pending','verified','rejected') DEFAULT 'unverified',
  status ENUM('active','suspended') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY(user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS accounts (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  account_no VARCHAR(20) NOT NULL UNIQUE,
  account_type ENUM('savings','current','control') DEFAULT 'savings',
  currency VARCHAR(10) DEFAULT 'GHS',
  status ENUM('active','frozen','closed') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_accounts_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ledger_txn (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  ref VARCHAR(40) NOT NULL UNIQUE,
  type ENUM('deposit','withdraw','transfer','admin_adjust','wire','crypto_buy','crypto_sell','crypto_send','reversal') NOT NULL,
  status ENUM('pending','approved','rejected','posted','reversed') DEFAULT 'posted',
  narration VARCHAR(255) NULL,
  created_by BIGINT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ledger_created_at (created_at),
  INDEX idx_ledger_type (type)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ledger_entries (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  txn_id BIGINT NOT NULL,
  account_id BIGINT NOT NULL,
  direction ENUM('debit','credit') NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency VARCHAR(10) DEFAULT 'GHS',
  balance_after DECIMAL(18,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_entries_txn FOREIGN KEY(txn_id) REFERENCES ledger_txn(id) ON DELETE CASCADE,
  CONSTRAINT fk_entries_account FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE RESTRICT,
  INDEX idx_entries_account (account_id),
  INDEX idx_entries_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS otp_codes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  purpose VARCHAR(40) NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  attempts INT DEFAULT 0,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_otp_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_otp_lookup (user_id, purpose, used_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS beneficiaries (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  name VARCHAR(120) NOT NULL,
  bank_name VARCHAR(120) NOT NULL,
  account_number VARCHAR(40) NOT NULL,
  verified TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ben_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ben_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wire_transfers (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  txn_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  beneficiary_id BIGINT NULL,
  amount DECIMAL(18,2) NOT NULL,
  fee DECIMAL(18,2) DEFAULT 0,
  currency VARCHAR(10) DEFAULT 'GHS',
  status ENUM('draft','otp_verified','pending_review','approved','rejected','processed') DEFAULT 'draft',
  reason VARCHAR(255) NULL,
  reviewed_by BIGINT NULL,
  reviewed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_wire_txn FOREIGN KEY(txn_id) REFERENCES ledger_txn(id) ON DELETE CASCADE,
  CONSTRAINT fk_wire_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_wire_ben FOREIGN KEY(beneficiary_id) REFERENCES beneficiaries(id) ON DELETE SET NULL,
  INDEX idx_wire_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crypto_assets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  symbol VARCHAR(10) UNIQUE NOT NULL,
  name VARCHAR(30) NOT NULL,
  enabled TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crypto_wallets (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  asset_id INT NOT NULL,
  balance DECIMAL(24,8) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, asset_id),
  CONSTRAINT fk_cw_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_cw_asset FOREIGN KEY(asset_id) REFERENCES crypto_assets(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crypto_txn (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  ref VARCHAR(40) UNIQUE NOT NULL,
  user_id BIGINT NOT NULL,
  asset_id INT NOT NULL,
  type ENUM('buy','sell','send','deposit','withdraw') NOT NULL,
  amount DECIMAL(24,8) NOT NULL,
  rate DECIMAL(18,2) NULL,
  fee DECIMAL(24,8) DEFAULT 0,
  status ENUM('pending','approved','rejected','posted') DEFAULT 'posted',
  destination VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ctxn_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ctxn_asset FOREIGN KEY(asset_id) REFERENCES crypto_assets(id) ON DELETE RESTRICT,
  INDEX idx_ctxn_user (user_id),
  INDEX idx_ctxn_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  title VARCHAR(120) NOT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_tickets (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  subject VARCHAR(160) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','pending','closed') DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- CARD REQUESTS + VIRTUAL CARDS (demo / internal ledger)
CREATE TABLE IF NOT EXISTS card_requests (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  card_type ENUM('virtual') DEFAULT 'virtual',
  network ENUM('visa','mastercard') DEFAULT 'visa',
  tier ENUM('standard','premium') DEFAULT 'standard',
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  note VARCHAR(255) NULL,
  reviewed_by BIGINT NULL,
  reviewed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cr_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_cr_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS virtual_cards (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  request_id BIGINT NULL,
  network ENUM('visa','mastercard') DEFAULT 'visa',
  cardholder_name VARCHAR(120) NOT NULL,
  pan_enc TEXT NOT NULL,            -- encrypted PAN
  last4 VARCHAR(4) NOT NULL,
  exp_month TINYINT NOT NULL,
  exp_year SMALLINT NOT NULL,
  cvv_enc TEXT NOT NULL,            -- encrypted CVV
  status ENUM('active','frozen','closed') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_vc_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_vc_req FOREIGN KEY(request_id) REFERENCES card_requests(id) ON DELETE SET NULL,
  INDEX idx_vc_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  actor_id BIGINT NOT NULL,
  action VARCHAR(80) NOT NULL,
  meta JSON NULL,
  ip VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_actor (actor_id),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  bank_name VARCHAR(120) DEFAULT 'Astrabank',
  brand_primary VARCHAR(20) DEFAULT '#0B1F3B',
  brand_accent VARCHAR(20) DEFAULT '#2D6CDF',
  currency VARCHAR(10) DEFAULT 'GHS',
  wire_fee DECIMAL(18,2) DEFAULT 2.00,
  wire_daily_limit DECIMAL(18,2) DEFAULT 5000.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (id) VALUES (1)
ON DUPLICATE KEY UPDATE id=id;

INSERT IGNORE INTO roles (id, name) VALUES
 (1,'super_admin'),
 (2,'ops'),
 (3,'compliance'),
 (4,'support');

INSERT IGNORE INTO crypto_assets (symbol, name, enabled) VALUES
 ('BTC','Bitcoin',1),
 ('ETH','Ethereum',1),
 ('USDT','Tether',1);

-- SYSTEM user + control account for double-entry
INSERT IGNORE INTO users (id, full_name, email, password_hash, status, kyc_status)
VALUES (1, 'Astrabank System', 'system@astrabank.local', '$2y$10$kZ9S3pE1tS0QwK5wV4fC7u3kKkN1hK8EwQ7QpB9mY9u5w4L4JpP6O', 'active', 'verified');

INSERT IGNORE INTO accounts (user_id, account_no, account_type, currency, status)
VALUES (1, 'BANK_CONTROL_GHS', 'control', 'GHS', 'active');