-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `fullname` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('user', 'admin') DEFAULT 'user',
    `balance` DECIMAL(15, 2) DEFAULT 0.00,
    `profit_balance` DECIMAL(15, 2) DEFAULT 0.00,
    `kyc_status` ENUM(
        'unverified',
        'pending',
        'approved',
        'rejected'
    ) DEFAULT 'unverified',
    `status` ENUM(
        'active',
        'suspended',
        'blocked'
    ) DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

-- 2. System Settings & Wallet Addresses
CREATE TABLE IF NOT EXISTS `admin_wallets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `currency_name` VARCHAR(50) NOT NULL,
    `symbol` VARCHAR(10) NOT NULL,
    `wallet_address` VARCHAR(255) NOT NULL,
    `qr_code` VARCHAR(255) NULL,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE = InnoDB;

-- 3. Deposits Table
CREATE TABLE IF NOT EXISTS `deposits` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `currency` VARCHAR(20) NOT NULL,
    `proof_file` VARCHAR(255) NULL,
    `status` ENUM(
        'pending',
        'approved',
        'rejected'
    ) DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

-- 4. Withdrawals Table
CREATE TABLE IF NOT EXISTS `withdrawals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `currency` VARCHAR(20) NOT NULL,
    `wallet_address` VARCHAR(255) NOT NULL,
    `status` ENUM(
        'pending',
        'approved',
        'rejected'
    ) DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

-- 5. Copy Trading Masters
CREATE TABLE IF NOT EXISTS `copy_masters` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `image` VARCHAR(255) NOT NULL,
    `subscribers` INT DEFAULT 0,
    `commission` DECIMAL(5, 2) DEFAULT 0.00,
    `gain` DECIMAL(5, 2) DEFAULT 0.00,
    `annuity` TINYINT(1) DEFAULT 0,
    `status` ENUM('active', 'inactive') DEFAULT 'active'
) ENGINE = InnoDB;

-- 6. User Copy Trades
CREATE TABLE IF NOT EXISTS `copy_trades` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `master_id` INT NOT NULL,
    `master_name` VARCHAR(100) NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `trade_type` ENUM('regular', 'annuity') DEFAULT 'regular',
    `elapse_days` INT NOT NULL,
    `roi_pct` DECIMAL(8, 2) NOT NULL,
    `estimated_profit` DECIMAL(15, 2) NOT NULL,
    `status` ENUM(
        'active',
        'completed',
        'cancelled'
    ) DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`master_id`) REFERENCES `copy_masters` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

-- 7. Transactions / PnL Table
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `type` ENUM(
        'deposit',
        'withdrawal',
        'trade_investment',
        'trade_profit',
        'swap'
    ) NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `description` TEXT NULL,
    `status` ENUM(
        'pending',
        'completed',
        'failed'
    ) DEFAULT 'completed',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

-- Default Data: Default Admin User & Sample Master Traders
INSERT INTO
    `users` (
        `fullname`,
        `email`,
        `password`,
        `role`,
        `balance`,
        `kyc_status`
    )
VALUES (
        'System Admin',
        'admin@quantroalgo.com',
        '$2y$10$kaCV51ivYVdhUochdazUOuzEHedHt9Iy77LQxIqwbIpCuIYdp272W',
        'admin',
        0.00,
        'approved'
    );

INSERT INTO
    `copy_masters` (
        `name`,
        `image`,
        `subscribers`,
        `commission`,
        `gain`,
        `annuity`
    )
VALUES (
        'n3tofts - Fast & Furious V2',
        'https://quantroalgo.com/public/copt/1751206763.',
        5678,
        9.00,
        11.00,
        0
    ),
    (
        'marginator',
        'https://quantroalgo.com/public/copt/1751239297.',
        7641,
        9.00,
        12.00,
        1
    ),
    (
        'growcapital',
        'https://quantroalgo.com/public/copt/1751239983.',
        9876,
        11.00,
        13.00,
        1
    ),
    (
        'HYPEtrade',
        'https://quantroalgo.com/public/copt/1751240039.avif',
        10042,
        11.00,
        9.00,
        0
    ),
    (
        'KCoinMaster',
        'https://quantroalgo.com/public/copt/1751240128.avif',
        29417,
        11.00,
        13.00,
        1
    );

-- Schema update to support the redesigned Account Overview dashboard
-- Run this against the existing quantroalgo_db database.

-- The new dashboard shows a separate "locked" balance (with a lock icon)
-- alongside the normal wallet balance and profit balance. Add a column
-- for it if it doesn't already exist.
ALTER TABLE `users`
ADD COLUMN IF NOT EXISTS `locked_balance` DECIMAL(15, 2) DEFAULT 0.00 AFTER `profit_balance`;

-- Nothing else changes: the dashboard's "Deposit" / "Withdraw" / "Copy Trading"
-- stat cards are computed on the fly from the existing `deposits`,
-- `withdrawals`, and `copy_trades` tables, and Recent Transactions reads from
-- the existing `transactions` table. No new tables are required.

-- Seeds the 4 deposit wallets (BTC, ETH, USDT TRC20, USDT ERC20) into
-- admin_wallets if they aren't already there. Safe to re-run: it checks
-- for an existing row with the same currency_name before inserting, so it
-- won't create duplicates if you run this more than once or already added
-- some of these manually through an admin panel.

INSERT INTO
    `admin_wallets` (
        `currency_name`,
        `symbol`,
        `wallet_address`,
        `is_active`
    )
SELECT 'Bitcoin (BTC)', 'BTC', 'bc1qp6ljsft95mllclz76pc3jq8wpsgqu6565g9a89', 1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM `admin_wallets`
        WHERE
            `currency_name` = 'Bitcoin (BTC)'
    );

INSERT INTO
    `admin_wallets` (
        `currency_name`,
        `symbol`,
        `wallet_address`,
        `is_active`
    )
SELECT 'Ethereum (ERC20)', 'ETH', '0x9C772Ea37592D4A3C043CBb9A07DaE1982511b20', 1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM `admin_wallets`
        WHERE
            `currency_name` = 'Ethereum (ERC20)'
    );

INSERT INTO
    `admin_wallets` (
        `currency_name`,
        `symbol`,
        `wallet_address`,
        `is_active`
    )
SELECT 'Tether USDT (TRC20)', 'USDT', 'TAxopGvr8BJhePoPHw5onyJ4BTsN1uSMLM', 1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM `admin_wallets`
        WHERE
            `currency_name` = 'Tether USDT (TRC20)'
    );

INSERT INTO
    `admin_wallets` (
        `currency_name`,
        `symbol`,
        `wallet_address`,
        `is_active`
    )
SELECT 'Tether USDT (ERC20)', 'USDT', '0x9C772Ea37592D4A3C043CBb9A07DaE1982511b20', 1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM `admin_wallets`
        WHERE
            `currency_name` = 'Tether USDT (ERC20)'
    );

-- Adds the fields the Account Settings page lets a user edit beyond their
-- existing fullname/email. Safe to re-run: each ADD COLUMN is guarded.
ALTER TABLE `users`
ADD COLUMN IF NOT EXISTS `phone` VARCHAR(30) NULL AFTER `fullname`,
ADD COLUMN IF NOT EXISTS `gender` ENUM('Male', 'Female') NULL AFTER `phone`,
ADD COLUMN IF NOT EXISTS `country` VARCHAR(100) NULL AFTER `gender`;