CREATE DATABASE IF NOT EXISTS westglobalzentra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE westglobalzentra;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 full_name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 transaction_pin_hash VARCHAR(255) NULL,
 account_number VARCHAR(30) NOT NULL UNIQUE,
 currency CHAR(3) NOT NULL DEFAULT 'NGN',
 balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
 role ENUM('customer','admin') NOT NULL DEFAULT 'customer',
 status ENUM('active','suspended','closed') NOT NULL DEFAULT 'active',
 verification_status ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified',
 dob DATE NULL,
 verification_document_type VARCHAR(50) NULL,
 verification_document_number VARCHAR(100) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE beneficiaries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL,
 bank_name VARCHAR(120) NOT NULL,
 account_number VARCHAR(50) NOT NULL,
 status ENUM('active','disabled') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id)
) ENGINE=InnoDB;

CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 beneficiary_id BIGINT UNSIGNED NULL,
 type ENUM('credit','debit') NOT NULL,
 amount DECIMAL(18,2) NOT NULL,
 description VARCHAR(255) NOT NULL,
 reference VARCHAR(80) NOT NULL UNIQUE,
 status ENUM('pending','completed','failed','reversed') NOT NULL DEFAULT 'pending',
 provider_reference VARCHAR(120) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(beneficiary_id) REFERENCES beneficiaries(id) ON DELETE SET NULL,
 INDEX(user_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE deposits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(18,2) NOT NULL,
 reference VARCHAR(80) NOT NULL UNIQUE,
 provider_reference VARCHAR(120) NULL,
 status ENUM('pending','completed','failed','reversed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE withdrawals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(18,2) NOT NULL,
 destination_account VARCHAR(80) NOT NULL,
 reference VARCHAR(80) NOT NULL UNIQUE,
 provider_reference VARCHAR(120) NULL,
 status ENUM('pending','completed','failed','reversed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE virtual_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 provider VARCHAR(80) NOT NULL,
 account_number VARCHAR(80) NULL,
 bank_name VARCHAR(120) NULL,
 provider_reference VARCHAR(120) NULL,
 status ENUM('pending','active','disabled') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_provider_account(provider,account_number),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE otp_challenges (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 purpose VARCHAR(50) NOT NULL,
 code_hash VARCHAR(255) NOT NULL,
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 expires_at DATETIME NOT NULL,
 used_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,purpose,expires_at)
) ENGINE=InnoDB;

CREATE TABLE notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(150) NOT NULL,
 message VARCHAR(500) NOT NULL,
 read_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(120) NOT NULL,
 ip_address VARCHAR(45) NULL,
 metadata JSON NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(user_id,created_at)
) ENGINE=InnoDB;