CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE merchants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id VARCHAR(32) NOT NULL UNIQUE,
    secret_key VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    transaction_password_hash VARCHAR(255) DEFAULT NULL,
    totp_secret VARCHAR(64) DEFAULT NULL,
    totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
    business_name VARCHAR(150) NOT NULL,
    domain VARCHAR(255) DEFAULT NULL,
    server_ip VARCHAR(45) DEFAULT NULL,
    deposit_fee DECIMAL(6,2) NOT NULL DEFAULT 5.00,
    payout_fee DECIMAL(6,2) NOT NULL DEFAULT 2.00,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    must_change_password TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE merchant_domains (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT UNSIGNED NOT NULL,
    domain_or_ip VARCHAR(255) NOT NULL,
    type ENUM('domain','ip') NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_merchant_origin (merchant_id, domain_or_ip),
    CONSTRAINT fk_md_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT UNSIGNED NOT NULL,
    transaction_id VARCHAR(50) NOT NULL UNIQUE,
    type ENUM('payin','payout') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee DECIMAL(15,2) NOT NULL DEFAULT 0,
    net_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'BDT',
    status ENUM('pending','processing','success','failed','cancelled') NOT NULL DEFAULT 'pending',
    customer_reference VARCHAR(150) DEFAULT NULL,
    callback_url VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tx_merchant_status (merchant_id, status),
    CONSTRAINT fk_tx_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE merchant_withdrawals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT UNSIGNED NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee DECIMAL(15,2) NOT NULL DEFAULT 0,
    net_amount DECIMAL(15,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'BDT',
    payment_method ENUM('bkash','nagad') NOT NULL,
    payment_number VARCHAR(30) NOT NULL,
    transaction_password_hash VARCHAR(255) NOT NULL,
    google_auth_enabled TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('pending','processing','success','rejected') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mw_merchant_status (merchant_id, status),
    CONSTRAINT fk_mw_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create your first admin AFTER uploading the project:
-- php -r "echo password_hash('ChangeThisPassword!', PASSWORD_DEFAULT), PHP_EOL;"
-- Then:
-- INSERT INTO admins (username,password_hash) VALUES ('admin','PASTE_HASH_HERE');


CREATE TABLE IF NOT EXISTS payment_numbers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id INT UNSIGNED NOT NULL,
 payment_method ENUM('bkash','nagad') NOT NULL,
 payment_number VARCHAR(30) NOT NULL,
 amount DECIMAL(15,2) NOT NULL DEFAULT 100.00,
 display_order INT NOT NULL DEFAULT 0,
 status TINYINT(1) NOT NULL DEFAULT 1,
 current_uses INT UNSIGNED NOT NULL DEFAULT 0,
 last_used_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_payment_number (merchant_id,payment_method,payment_number,amount),
 INDEX idx_rotation (merchant_id,payment_method,amount,status,display_order,last_used_at),
 CONSTRAINT fk_pn_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;