-- ============================================================
-- 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;



-- ------------------------------------------------------------
-- 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);

-- ------------------------------------------------------------
-- 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 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;

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