-- ============================================================
-- GRIDPRO REPLICA - COMPLETE DATABASE
-- Single SQL file containing ALL current database changes:
-- admin panel, dividends, company logo, profile photos,
-- email verification, deposits, withdrawals, investments,
-- trading/copy trading, notifications and support.
--
-- IMPORTANT: This is a clean-install schema. Import into the
-- Gridpro database you want this project to use.
-- ============================================================

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

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS email_verifications;
DROP TABLE IF EXISTS admin_audit_logs;
DROP TABLE IF EXISTS admins;
DROP TABLE IF EXISTS support_tickets;
DROP TABLE IF EXISTS copy_subscriptions;
DROP TABLE IF EXISTS copy_traders;
DROP TABLE IF EXISTS withdrawals;
DROP TABLE IF EXISTS deposits;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS investments;
DROP TABLE IF EXISTS investment_plans;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS app_settings;

SET FOREIGN_KEY_CHECKS=1;

-- ------------------------------------------------------------
-- Application settings / branding
-- ------------------------------------------------------------
CREATE TABLE app_settings (
    id TINYINT UNSIGNED NOT NULL,
    dividend_frequency ENUM('weekly','monthly') NOT NULL DEFAULT 'monthly',
    logo_path VARCHAR(255) NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (id, dividend_frequency, logo_path)
VALUES (1, 'monthly', NULL);

-- ------------------------------------------------------------
-- Users
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(60) NOT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(40) NULL,
    dob DATE NULL,
    country VARCHAR(80) NULL,
    address TEXT NULL,
    balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    rewards DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    total_deposit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    total_withdrawal DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    bank_name VARCHAR(120) NULL,
    account_name VARCHAR(120) NULL,
    account_no VARCHAR(120) NULL,
    swiftcode VARCHAR(80) NULL,
    btc_address VARCHAR(255) NULL,
    eth_address VARCHAR(255) NULL,
    ltc_address VARCHAR(255) NULL,
    usdt_address VARCHAR(255) NULL,
    roi_email ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
    profile_photo VARCHAR(255) NULL,
    plan_email ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
    status ENUM('Pending','Active','Suspended') NOT NULL DEFAULT 'Pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    email_verified_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_status (status),
    KEY idx_users_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Transactions
-- ------------------------------------------------------------
CREATE TABLE transactions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    type VARCHAR(40) NOT NULL,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    fee DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'Completed',
    reference VARCHAR(80) NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_transactions_user (user_id),
    KEY idx_transactions_created (created_at),
    KEY idx_transactions_reference (reference),
    CONSTRAINT fk_transactions_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Investment plans
-- ------------------------------------------------------------
CREATE TABLE investment_plans (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    min_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    max_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    roi DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    duration_days INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    KEY idx_plans_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO investment_plans
(name, min_amount, max_amount, roi, duration_days, status)
VALUES
('Starter Plan', 100.00, 4999.00, 8.00, 30, 1),
('Growth Plan', 5000.00, 24999.00, 18.00, 60, 1),
('Elite Plan', 25000.00, 1000000.00, 35.00, 90, 1);

-- ------------------------------------------------------------
-- User investments
-- ------------------------------------------------------------
CREATE TABLE investments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    plan_id INT UNSIGNED NOT NULL,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    roi DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'Active',
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at DATETIME NULL,
    last_dividend_at DATETIME NULL,
    next_dividend_at DATETIME NULL,
    dividend_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_investments_user (user_id),
    KEY idx_investments_plan (plan_id),
    KEY idx_investments_status (status),
    KEY idx_investments_next_dividend (next_dividend_at),
    CONSTRAINT fk_investments_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_investments_plan
        FOREIGN KEY (plan_id) REFERENCES investment_plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Notifications
-- ------------------------------------------------------------
CREATE TABLE notifications (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(180) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_notifications_user (user_id),
    KEY idx_notifications_read (user_id, is_read),
    CONSTRAINT fk_notifications_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Deposits
-- ------------------------------------------------------------
CREATE TABLE deposits (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    method VARCHAR(80) NOT NULL,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'Pending',
    reference VARCHAR(80) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_deposits_user (user_id),
    KEY idx_deposits_status (status),
    CONSTRAINT fk_deposits_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Withdrawals
-- ------------------------------------------------------------
CREATE TABLE withdrawals (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    method VARCHAR(80) NOT NULL,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'Pending',
    reference VARCHAR(80) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_withdrawals_user (user_id),
    KEY idx_withdrawals_status (status),
    CONSTRAINT fk_withdrawals_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Copy traders
-- ------------------------------------------------------------
CREATE TABLE copy_traders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    risk VARCHAR(30) NULL,
    tier VARCHAR(40) NULL,
    roi DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    win_rate DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    trades INT NOT NULL DEFAULT 0,
    followers INT NOT NULL DEFAULT 0,
    slots INT NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'Open',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO copy_traders
(name, risk, tier, roi, win_rate, trades, followers, slots, status)
VALUES
('James M Anderson', 'Low Risk', 'Elite', 70.00, 82.00, 35, 95, 77, 'Open'),
('Sarah Johnson', 'Low Risk', 'Standard', 60.00, 72.00, 25, 165, 49, 'Open'),
('Emily Wong', 'Low Risk', 'Elite', 50.00, 77.00, 18, 226, 148, 'Open');

-- ------------------------------------------------------------
-- Copy subscriptions
-- ------------------------------------------------------------
CREATE TABLE copy_subscriptions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    trader_id INT UNSIGNED NOT NULL,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(30) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_copy_subscriptions_user (user_id),
    KEY idx_copy_subscriptions_trader (trader_id),
    CONSTRAINT fk_copy_subscriptions_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_copy_subscriptions_trader
        FOREIGN KEY (trader_id) REFERENCES copy_traders(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Support tickets
-- ------------------------------------------------------------
CREATE TABLE support_tickets (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'Open',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_support_user (user_id),
    KEY idx_support_status (status),
    CONSTRAINT fk_support_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Admin users
-- Default login:
-- Username: admin
-- Password: Admin@12345
-- ------------------------------------------------------------
CREATE TABLE admins (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(80) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_admin_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admins (username, password_hash)
VALUES ('admin', '$2y$12$CRop9DZ203sc3OI2mqzMEudYlVpXoc/nQwogt2yVXvYyjOPwNSBtK');

-- ------------------------------------------------------------
-- Admin audit log
-- ------------------------------------------------------------
CREATE TABLE admin_audit_logs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    action VARCHAR(60) NOT NULL,
    amount DECIMAL(18,2) NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_admin (admin_id),
    KEY idx_audit_user (user_id),
    KEY idx_audit_created (created_at),
    CONSTRAINT fk_audit_admin
        FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Email verification
-- One-time hashed tokens, normally valid for 24 hours.
-- ------------------------------------------------------------
CREATE TABLE email_verifications (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email_verification_token (token_hash),
    KEY idx_email_verifications_user (user_id),
    KEY idx_email_verifications_expires (expires_at),
    CONSTRAINT fk_email_verifications_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- END OF COMPLETE GRIDPRO DATABASE
-- ============================================================