-- =====================================================================
-- Prime Pay BD - Payment Automation Platform
-- MySQL 8.0+ Database Schema
-- Engine: InnoDB | Charset: utf8mb4
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- ROLES & PERMISSIONS (RBAC)
-- ---------------------------------------------------------------------
CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,       -- admin, merchant, user
    display_name VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,      -- e.g. transactions.view, users.delete
    module VARCHAR(50) NOT NULL,            -- e.g. transactions, users, gateways
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    UNIQUE KEY uq_role_permission (role_id, permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- USERS
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    role_id INT UNSIGNED NOT NULL DEFAULT 3,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    business_name VARCHAR(150) DEFAULT NULL,      -- for merchants
    website_url VARCHAR(255) DEFAULT NULL,
    status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'pending',
    email_verified_at TIMESTAMP NULL DEFAULT NULL,
    kyc_status ENUM('not_submitted','pending','approved','rejected') NOT NULL DEFAULT 'not_submitted',
    kyc_document VARCHAR(255) DEFAULT NULL,
    two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    two_factor_recovery_codes TEXT DEFAULT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_users_role (role_id),
    INDEX idx_users_status (status),
    INDEX idx_users_email (email),
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pr_email (email),
    INDEX idx_pr_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    payload TEXT DEFAULT NULL,
    last_activity INT UNSIGNED NOT NULL,
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_attempts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    success TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_la_email_ip (email, ip_address),
    INDEX idx_la_attempted (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- MERCHANTS (extended profile linked 1:1 to a user with role=merchant)
-- ---------------------------------------------------------------------
CREATE TABLE merchants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    merchant_code VARCHAR(20) NOT NULL UNIQUE,     -- e.g. PPBD-XXXXXX
    business_type VARCHAR(100) DEFAULT NULL,
    trade_license VARCHAR(255) DEFAULT NULL,
    commission_rate DECIMAL(5,2) NOT NULL DEFAULT 2.00,  -- % fee charged by platform
    webhook_url VARCHAR(255) DEFAULT NULL,
    success_url VARCHAR(255) DEFAULT NULL,
    cancel_url VARCHAR(255) DEFAULT NULL,
    ip_whitelist TEXT DEFAULT NULL,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_merchants_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- WALLETS
-- ---------------------------------------------------------------------
CREATE TABLE wallets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    pending_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_earned DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_withdrawn DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wallet_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wallet_id INT UNSIGNED NOT NULL,
    type ENUM('credit','debit') NOT NULL,
    source ENUM('payment','withdrawal','refund','adjustment','transfer','fee') NOT NULL,
    reference_type VARCHAR(50) DEFAULT NULL,   -- e.g. 'transaction', 'withdrawal'
    reference_id BIGINT UNSIGNED DEFAULT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    note VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ledger_wallet (wallet_id),
    INDEX idx_ledger_reference (reference_type, reference_id),
    CONSTRAINT fk_ledger_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- PAYMENT GATEWAYS
-- ---------------------------------------------------------------------
CREATE TABLE gateways (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,          -- bkash, nagad, rocket, bank_transfer, manual
    name VARCHAR(100) NOT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    type ENUM('automatic','manual') NOT NULL DEFAULT 'automatic',
    environment ENUM('sandbox','live') NOT NULL DEFAULT 'sandbox',
    credentials JSON DEFAULT NULL,             -- encrypted at rest by app layer
    fee_type ENUM('flat','percentage') NOT NULL DEFAULT 'percentage',
    fee_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    min_amount DECIMAL(15,2) NOT NULL DEFAULT 10.00,
    max_amount DECIMAL(15,2) NOT NULL DEFAULT 500000.00,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    instructions TEXT DEFAULT NULL,            -- for manual gateways
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- PAYMENT LINKS
-- ---------------------------------------------------------------------
CREATE TABLE payment_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    merchant_id INT UNSIGNED NOT NULL,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(191) NOT NULL UNIQUE,
    description TEXT DEFAULT NULL,
    amount_type ENUM('fixed','custom') NOT NULL DEFAULT 'fixed',
    amount DECIMAL(15,2) DEFAULT NULL,          -- null when custom
    min_amount DECIMAL(15,2) DEFAULT NULL,
    max_amount DECIMAL(15,2) DEFAULT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    success_url VARCHAR(255) DEFAULT NULL,
    cancel_url VARCHAR(255) DEFAULT NULL,
    qr_code_path VARCHAR(255) DEFAULT NULL,
    expiry_date DATETIME DEFAULT NULL,
    usage_limit INT UNSIGNED DEFAULT NULL,      -- max number of times payable
    usage_count INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('active','inactive','expired') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_paylinks_merchant (merchant_id),
    INDEX idx_paylinks_status (status),
    CONSTRAINT fk_paylinks_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- INVOICES
-- ---------------------------------------------------------------------
CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    merchant_id INT UNSIGNED NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    customer_email VARCHAR(150) DEFAULT NULL,
    customer_phone VARCHAR(20) DEFAULT NULL,
    customer_address TEXT DEFAULT NULL,
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    due_date DATE DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    pdf_path VARCHAR(255) DEFAULT NULL,
    status ENUM('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
    paid_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invoices_merchant (merchant_id),
    INDEX idx_invoices_status (status),
    CONSTRAINT fk_invoices_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoice_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    item_name VARCHAR(150) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- TRANSACTIONS  (core payment records — from links, invoices, or API)
-- ---------------------------------------------------------------------
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    invoice_id_ref VARCHAR(100) DEFAULT NULL,        -- merchant-supplied external invoice id (API)
    merchant_id INT UNSIGNED NOT NULL,
    payment_link_id INT UNSIGNED DEFAULT NULL,
    invoice_id INT UNSIGNED DEFAULT NULL,
    gateway_id INT UNSIGNED NOT NULL,
    gateway_transaction_id VARCHAR(150) DEFAULT NULL,  -- ID returned by bKash/Nagad/etc
    sender_number VARCHAR(20) DEFAULT NULL,
    customer_name VARCHAR(150) DEFAULT NULL,
    customer_email VARCHAR(150) DEFAULT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) NOT NULL DEFAULT 'BDT',
    metadata JSON DEFAULT NULL,
    status ENUM('pending','processing','completed','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
    verified_manually_by INT UNSIGNED DEFAULT NULL,
    webhook_sent TINYINT(1) NOT NULL DEFAULT 0,
    webhook_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    ip_address VARCHAR(45) DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tx_merchant (merchant_id),
    INDEX idx_tx_status (status),
    INDEX idx_tx_gateway (gateway_id),
    INDEX idx_tx_created (created_at),
    INDEX idx_tx_gwtxid (gateway_transaction_id),
    CONSTRAINT fk_tx_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    CONSTRAINT fk_tx_gateway FOREIGN KEY (gateway_id) REFERENCES gateways(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tx_paylink FOREIGN KEY (payment_link_id) REFERENCES payment_links(id) ON DELETE SET NULL,
    CONSTRAINT fk_tx_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    CONSTRAINT fk_tx_verifier FOREIGN KEY (verified_manually_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- API KEYS
-- ---------------------------------------------------------------------
CREATE TABLE api_keys (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT UNSIGNED NOT NULL,
    label VARCHAR(100) NOT NULL DEFAULT 'Default Key',
    api_key VARCHAR(64) NOT NULL UNIQUE,
    api_secret_hash VARCHAR(255) NOT NULL,
    last_used_at TIMESTAMP NULL DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_apikeys_merchant (merchant_id),
    CONSTRAINT fk_apikeys_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- WEBHOOKS
-- ---------------------------------------------------------------------
CREATE TABLE webhooks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT UNSIGNED NOT NULL,
    url VARCHAR(255) NOT NULL,
    secret VARCHAR(64) NOT NULL,
    events JSON DEFAULT NULL,     -- e.g. ["payment.completed","payment.failed"]
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_webhooks_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webhook_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    webhook_id INT UNSIGNED NOT NULL,
    transaction_id BIGINT UNSIGNED DEFAULT NULL,
    event VARCHAR(50) NOT NULL,
    payload JSON DEFAULT NULL,
    response_code INT DEFAULT NULL,
    response_body TEXT DEFAULT NULL,
    attempt INT UNSIGNED NOT NULL DEFAULT 1,
    success TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_wlogs_webhook FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- WITHDRAWALS
-- ---------------------------------------------------------------------
CREATE TABLE withdrawal_methods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,        -- bkash, nagad, rocket, bank
    name VARCHAR(100) NOT NULL,
    fee_type ENUM('flat','percentage') NOT NULL DEFAULT 'flat',
    fee_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    min_amount DECIMAL(15,2) NOT NULL DEFAULT 100.00,
    max_amount DECIMAL(15,2) NOT NULL DEFAULT 100000.00,
    processing_time VARCHAR(100) DEFAULT '24-48 hours',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE withdrawals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id INT UNSIGNED NOT NULL,
    withdrawal_method_id INT UNSIGNED NOT NULL,
    account_number VARCHAR(100) NOT NULL,      -- wallet number / bank account
    account_details JSON DEFAULT NULL,         -- bank name, branch, routing etc
    amount DECIMAL(15,2) NOT NULL,
    fee_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','approved','processing','completed','rejected','cancelled') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(255) DEFAULT NULL,
    reviewed_by INT UNSIGNED DEFAULT NULL,
    reviewed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_wd_user (user_id),
    INDEX idx_wd_status (status),
    CONSTRAINT fk_wd_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_wd_method FOREIGN KEY (withdrawal_method_id) REFERENCES withdrawal_methods(id) ON DELETE RESTRICT,
    CONSTRAINT fk_wd_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- NOTIFICATIONS
-- ---------------------------------------------------------------------
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL,             -- payment, withdrawal, security, system
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    data JSON DEFAULT NULL,
    channel ENUM('in_app','email','sms') NOT NULL DEFAULT 'in_app',
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notif_user (user_id, is_read),
    CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SETTINGS (key-value, grouped)
-- ---------------------------------------------------------------------
CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group` VARCHAR(50) NOT NULL DEFAULT 'general',  -- general, mail, sms, security, currency
    `key` VARCHAR(100) NOT NULL,
    `value` LONGTEXT DEFAULT NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 0,   -- can be exposed to frontend
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_settings_group_key (`group`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- AUDIT LOGS / ACTIVITY LOGS
-- ---------------------------------------------------------------------
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,          -- e.g. 'user.login', 'withdrawal.approved'
    module VARCHAR(50) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    old_values JSON DEFAULT NULL,
    new_values JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_action (action),
    INDEX idx_audit_created (created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- PLUGINS
-- ---------------------------------------------------------------------
CREATE TABLE plugins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,       -- folder name under /plugins
    name VARCHAR(150) NOT NULL,
    version VARCHAR(20) NOT NULL,
    author VARCHAR(150) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    dependencies JSON DEFAULT NULL,
    min_platform_version VARCHAR(20) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    settings JSON DEFAULT NULL,
    installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plugin_hooks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id INT UNSIGNED NOT NULL,
    hook_name VARCHAR(100) NOT NULL,     -- e.g. after_payment_create
    callback VARCHAR(150) NOT NULL,      -- e.g. Class@method
    priority INT NOT NULL DEFAULT 10,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    INDEX idx_hooks_name (hook_name),
    CONSTRAINT fk_hooks_plugin FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plugin_menus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id INT UNSIGNED NOT NULL,
    panel ENUM('admin','merchant','user') NOT NULL DEFAULT 'admin',
    label VARCHAR(100) NOT NULL,
    icon VARCHAR(50) DEFAULT NULL,
    route VARCHAR(150) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_pmenus_plugin FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plugin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id INT UNSIGNED NOT NULL,
    level ENUM('info','warning','error') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_plogs_plugin FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- RATE LIMITING
-- ---------------------------------------------------------------------
CREATE TABLE rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(150) NOT NULL,     -- e.g. ip:1.2.3.4:login, apikey:xxxx:create_payment
    hits INT UNSIGNED NOT NULL DEFAULT 1,
    window_start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ratelimit_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SEED DATA
-- =====================================================================

INSERT INTO roles (id, name, display_name, description) VALUES
(1, 'admin', 'Administrator', 'Full platform access'),
(2, 'merchant', 'Merchant', 'Business account accepting payments'),
(3, 'user', 'User', 'Standard end-user account');

INSERT INTO permissions (name, module, description) VALUES
('users.view','users','View users'),('users.manage','users','Create/edit/delete users'),
('merchants.view','merchants','View merchants'),('merchants.manage','merchants','Manage merchants'),
('transactions.view','transactions','View transactions'),('transactions.manage','transactions','Manage transactions'),
('withdrawals.view','withdrawals','View withdrawals'),('withdrawals.approve','withdrawals','Approve/reject withdrawals'),
('gateways.manage','gateways','Configure payment gateways'),
('settings.manage','settings','Manage platform settings'),
('plugins.manage','plugins','Install/manage plugins'),
('audit.view','audit','View audit logs');

INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions;

INSERT INTO gateways (code, name, type, fee_type, fee_value, is_enabled, sort_order) VALUES
('bkash','bKash','automatic','percentage',1.85,0,1),
('nagad','Nagad','automatic','percentage',1.50,0,2),
('rocket','Rocket','automatic','percentage',1.80,0,3),
('bank_transfer','Bank Transfer','manual','flat',0.00,0,4),
('manual','Manual Payment','manual','flat',0.00,1,5);

INSERT INTO withdrawal_methods (code, name, fee_type, fee_value, min_amount, max_amount, processing_time) VALUES
('bkash','bKash','percentage',1.00,100,50000,'Instant - 24 hours'),
('nagad','Nagad','percentage',1.00,100,50000,'Instant - 24 hours'),
('rocket','Rocket','percentage',1.00,100,50000,'Instant - 24 hours'),
('bank','Bank Transfer','flat',10.00,500,500000,'1-3 business days');

INSERT INTO settings (`group`,`key`,`value`,is_public) VALUES
('general','site_name','Prime Pay BD',1),
('general','site_logo','/assets/img/logo.png',1),
('general','currency','BDT',1),
('general','currency_symbol','৳',1),
('general','maintenance_mode','0',1),
('general','timezone','Asia/Dhaka',0),
('security','max_login_attempts','5',0),
('security','lockout_minutes','15',0),
('security','force_2fa_admin','1',0),
('mail','mail_driver','smtp',0),
('mail','mail_from_address','noreply@primepaybd.com',0),
('commission','default_merchant_fee','2.00',0);