-- ============================================
-- CYBER INVEST INVESTMENT PLATFORM DATABASE
-- ============================================
-- Run this SQL file in phpMyAdmin or MySQL CLI
-- ============================================

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


-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    full_name VARCHAR(100),
    country VARCHAR(50),
    city VARCHAR(50),
    address TEXT,
    postal_code VARCHAR(20),
    balance DECIMAL(15,2) DEFAULT 0.00,
    total_deposit DECIMAL(15,2) DEFAULT 0.00,
    total_withdrawal DECIMAL(15,2) DEFAULT 0.00,
    profit_balance DECIMAL(15,2) DEFAULT 0.00,
    referral_balance DECIMAL(15,2) DEFAULT 0.00,
    referral_code VARCHAR(20) UNIQUE,
    referred_by INT,
    account_status ENUM('pending', 'active', 'suspended', 'blocked') DEFAULT 'pending',
    email_verified BOOLEAN DEFAULT FALSE,
    email_verification_token VARCHAR(100),
    password_reset_token VARCHAR(100),
    password_reset_expiry TIMESTAMP NULL,
    two_fa_enabled BOOLEAN DEFAULT FALSE,
    two_fa_secret VARCHAR(255),
    kyc_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    kyc_document VARCHAR(255),
    kyc_verified_at TIMESTAMP NULL,
    last_login TIMESTAMP NULL,
    last_ip VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (account_status),
    INDEX idx_referral (referred_by),
    INDEX idx_email (email),
    INDEX idx_created (created_at),
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin users table
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('superadmin', 'admin', 'support', 'moderator') DEFAULT 'admin',
    permissions TEXT,
    last_login TIMESTAMP NULL,
    last_ip VARCHAR(45),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Investment plans table
CREATE TABLE IF NOT EXISTS investment_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    min_amount DECIMAL(15,2) NOT NULL,
    max_amount DECIMAL(15,2) NOT NULL,
    daily_return DECIMAL(5,2) NOT NULL,
    duration_days INT NOT NULL,
    total_return DECIMAL(5,2) NOT NULL,
    principal_return BOOLEAN DEFAULT TRUE,
    compound_interest BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    features TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_amount (min_amount, max_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Investments table
CREATE TABLE IF NOT EXISTS investments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    daily_profit DECIMAL(15,2) NOT NULL,
    total_profit DECIMAL(15,2) DEFAULT 0.00,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP NULL,
    status ENUM('running', 'completed', 'cancelled') DEFAULT 'running',
    last_profit_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES investment_plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transactions table
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal', 'investment', 'profit', 'referral_bonus', 'referral_commission', 'bonus', 'debit', 'credit') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee DECIMAL(15,2) DEFAULT 0.00,
    net_amount DECIMAL(15,2) DEFAULT 0.00,
    method VARCHAR(50),
    description TEXT,
    reference VARCHAR(100) UNIQUE,
    status ENUM('pending', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
    admin_action_by INT NULL,
    admin_note TEXT,
    payment_proof VARCHAR(255),
    wallet_address VARCHAR(255),
    transaction_hash VARCHAR(255),
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_reference (reference),
    INDEX idx_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_action_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Support tickets table
CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ticket_id VARCHAR(20) UNIQUE NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    category ENUM('general', 'deposit', 'withdrawal', 'investment', 'account', 'technical', 'other') DEFAULT 'general',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('open', 'answered', 'closed', 'resolved') DEFAULT 'open',
    admin_reply TEXT,
    replied_by INT NULL,
    replied_at TIMESTAMP NULL,
    attachments JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_ticket_id (ticket_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (replied_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ticket replies table
CREATE TABLE IF NOT EXISTS ticket_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    user_id INT NULL,
    user_type ENUM('user', 'admin') NOT NULL DEFAULT 'user',
    message TEXT NOT NULL,
    attachments JSON,
    is_internal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ticket (ticket_id),
    INDEX idx_user (user_id),
    INDEX idx_user_type (user_type),
    INDEX idx_created (created_at),
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity logs table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    admin_id INT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_admin (admin_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login attempts table
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    username VARCHAR(100),
    success BOOLEAN DEFAULT FALSE,
    user_agent TEXT,
    attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip (ip_address),
    INDEX idx_time (attempt_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email templates table
CREATE TABLE IF NOT EXISTS email_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    variables TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_read (is_read),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT,
    `type` VARCHAR(50) DEFAULT 'text',
    category VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `idx_key_unique` (`key`),
    INDEX idx_key (`key`),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SAMPLE DATA INSERTION
-- ============================================

-- Insert default admin (password: Admin123!)
INSERT IGNORE INTO admin_users (username, email, password, role) VALUES
('admin', 'admin@yourdomain.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'superadmin'),
('support', 'support@yourdomain.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'support');

-- Insert investment plans
INSERT IGNORE INTO investment_plans (name, description, min_amount, max_amount, daily_return, duration_days, total_return, principal_return, features) VALUES
('Starter Plan', 'Perfect for beginners. Start with small investments.', 50.00, 1000.00, 5.00, 30, 150.00, TRUE, '["Daily Profits", "Principal Return", "24/7 Support"]'),
('Professional Plan', 'For experienced investors. Higher returns.', 1000.00, 10000.00, 7.00, 45, 315.00, TRUE, '["Higher Returns", "Priority Support", "Weekly Reports"]'),
('Premium Plan', 'Maximum returns for serious investors.', 10000.00, 100000.00, 10.00, 60, 600.00, TRUE, '["Maximum Returns", "Personal Manager", "Instant Withdrawals"]'),
('VIP Plan', 'Exclusive plan for VIP members only.', 50000.00, 500000.00, 12.00, 90, 1080.00, TRUE, '["Exclusive Returns", "Dedicated Support", "Special Bonuses"]');

-- Insert default settings
INSERT IGNORE INTO settings (`key`, `value`, `type`, category, description) VALUES
('site_name', 'Cyber Invest', 'text', 'general', 'Website name'),
('site_email', 'support@yourdomain.com', 'text', 'general', 'Contact email'),
('currency', 'USD', 'text', 'general', 'Default currency'),
('currency_symbol', '$', 'text', 'general', 'Currency symbol'),
('min_deposit', '10.00', 'number', 'financial', 'Minimum deposit amount'),
('max_deposit', '100000.00', 'number', 'financial', 'Maximum deposit amount'),
('min_withdrawal', '5.00', 'number', 'financial', 'Minimum withdrawal amount'),
('withdrawal_fee', '0.05', 'number', 'financial', 'Withdrawal fee percentage'),
('referral_bonus', '10.00', 'number', 'referral', 'Referral bonus amount'),
('referral_commission', '0.05', 'number', 'referral', 'Referral commission rate'),
('maintenance_mode', '0', 'boolean', 'general', 'Maintenance mode status'),
('registration_open', '1', 'boolean', 'general', 'Allow new registrations'),
('email_verification', '1', 'boolean', 'security', 'Require email verification'),
('two_factor_auth', '0', 'boolean', 'security', 'Enable 2FA');

-- Insert email templates
INSERT IGNORE INTO email_templates (name, subject, content, variables) VALUES
('welcome', 'Welcome to Cyber Invest!', '<h1>Welcome {username}!</h1><p>Thank you for registering with Cyber Invest.</p><p>Your account has been created successfully.</p><p>Start investing today and grow your wealth!</p>', '["username", "email", "date"]'),
('deposit_approved', 'Deposit Approved', '<h1>Deposit Approved</h1><p>Your deposit of {amount} has been approved.</p><p>Your new balance is {balance}.</p>', '["username", "amount", "balance", "reference"]'),
('withdrawal_approved', 'Withdrawal Approved', '<h1>Withdrawal Approved</h1><p>Your withdrawal of {amount} has been approved.</p><p>Transaction reference: {reference}</p>', '["username", "amount", "reference", "method"]'),
('daily_profit', 'Daily Profit Added', '<h1>Daily Profit Added</h1><p>Your daily profit of {amount} has been added to your account.</p><p>Total balance: {balance}</p>', '["username", "amount", "balance", "investment_id"]');

-- Insert sample user (password: User123!)
INSERT IGNORE INTO users (username, email, password, balance, account_status, email_verified, referral_code) VALUES
('john_doe', 'john@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1000.00, 'active', TRUE, 'JOHNDOE123');

-- ============================================
-- END OF DATABASE SETUP
-- ============================================