-- WOW Payments Gateway — MySQL Schema
-- Run this once in phpMyAdmin / cPanel MySQL to create all tables.

CREATE TABLE IF NOT EXISTS merchants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
    fee_tier VARCHAR(30) NOT NULL DEFAULT 'standard',
    webhook_secret VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    public_key VARCHAR(64) NOT NULL UNIQUE,
    secret_key_hash VARCHAR(255) NOT NULL,
    secret_key_preview VARCHAR(12) NOT NULL,
    mode ENUM('live','test') NOT NULL DEFAULT 'live',
    revoked TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Coins/networks the platform supports. Admin manages this table.
CREATE TABLE IF NOT EXISTS coins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL,          -- e.g. USDT, BTC, ETH, BNB
    network VARCHAR(20) NOT NULL,       -- e.g. TRC20, ERC20, BEP20, NATIVE
    label VARCHAR(50) NOT NULL,         -- display name e.g. "USDT (TRC-20)"
    deposit_address VARCHAR(150) NOT NULL,
    min_confirmations INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY coin_network (code, network)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    order_ref VARCHAR(100) NOT NULL,        -- merchant's own order id
    payment_id VARCHAR(40) NOT NULL UNIQUE, -- our generated public payment id
    coin_id INT NOT NULL,
    expected_amount DECIMAL(24,8) NOT NULL,
    received_amount DECIMAL(24,8) NULL,
    txid VARCHAR(120) NULL,
    deposit_address VARCHAR(150) NOT NULL,
    status ENUM('pending','confirming','confirmed','underpaid','expired','failed') NOT NULL DEFAULT 'pending',
    confirmations INT NOT NULL DEFAULT 0,
    callback_url VARCHAR(255) NULL,
    redirect_url VARCHAR(255) NULL,
    webhook_sent TINYINT(1) NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (coin_id) REFERENCES coins(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhook_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    attempt INT NOT NULL DEFAULT 1,
    response_code INT NULL,
    response_body TEXT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fee_tiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tier_name VARCHAR(30) NOT NULL UNIQUE,
    percentage_fee DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    flat_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

INSERT INTO fee_tiers (tier_name, percentage_fee, flat_fee) VALUES
    ('standard', 1.00, 0.00),
    ('pro', 0.50, 0.00)
ON DUPLICATE KEY UPDATE tier_name = tier_name;

-- Seed the coin list requested: USDT (TRC20/ERC20/BEP20), BTC, ETH, BNB
INSERT INTO coins (code, network, label, deposit_address, min_confirmations) VALUES
    ('USDT', 'TRC20', 'USDT (TRC-20 / Tron)', 'REPLACE_WITH_YOUR_TRON_ADDRESS', 19),
    ('USDT', 'ERC20', 'USDT (ERC-20 / Ethereum)', 'REPLACE_WITH_YOUR_ETH_ADDRESS', 12),
    ('USDT', 'BEP20', 'USDT (BEP-20 / BNB Chain)', 'REPLACE_WITH_YOUR_BSC_ADDRESS', 15),
    ('BTC', 'NATIVE', 'Bitcoin', 'REPLACE_WITH_YOUR_BTC_ADDRESS', 2),
    ('ETH', 'ERC20', 'Ethereum', 'REPLACE_WITH_YOUR_ETH_ADDRESS', 12),
    ('BNB', 'BEP20', 'BNB (BNB Chain)', 'REPLACE_WITH_YOUR_BSC_ADDRESS', 15)
ON DUPLICATE KEY UPDATE label = VALUES(label);