-- FAST EARN LIMITED Database Schema
-- Created: 2025-12-22

CREATE DATABASE IF NOT EXISTS fast_earn_2;
USE fast_earn_2;

-- Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE DEFAULT NULL COMMENT 'Username for admin login',
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    account_type ENUM('JazzCash', 'Easypaisa') NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user' COMMENT 'User role',
    profile_photo VARCHAR(255) DEFAULT NULL,
    wallet_balance DECIMAL(10, 6) DEFAULT 0.000000,
    total_income DECIMAL(10, 6) DEFAULT 0.000000,
    total_withdraw DECIMAL(10, 6) DEFAULT 0.000000,
    today_income DECIMAL(10, 6) DEFAULT 0.000000,
    referrer_id INT DEFAULT NULL,
    referral_count INT DEFAULT 0,
    ads_earning_active TINYINT(1) DEFAULT 1,
    ads_earning_stopped_date DATE DEFAULT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Plans Table
CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name VARCHAR(255) NOT NULL,
    plan_price DECIMAL(10, 2) NOT NULL,
    ads_limit_per_day INT NOT NULL,
    plan_duration INT NOT NULL COMMENT 'Duration in days',
    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;

-- User Plans Table
CREATE TABLE user_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    status ENUM('pending', 'active', 'expired', 'rejected') DEFAULT 'pending',
    payment_screenshot VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ads Table
CREATE TABLE ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad_title VARCHAR(255) NOT NULL,
    ad_url TEXT NOT NULL,
    timer INT NOT NULL COMMENT 'Timer in seconds',
    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;

-- User Ads (Track which ads user has watched)
CREATE TABLE user_ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ad_id INT NOT NULL,
    earning DECIMAL(10, 6) NOT NULL,
    watched_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_ad_date (user_id, ad_id, watched_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Referrals Table
CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Deposits Table
CREATE TABLE deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    method ENUM('JazzCash', 'Easypaisa') NOT NULL,
    screenshot VARCHAR(255) NOT NULL,
    plan_id INT DEFAULT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_note TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Withdraw Requests Table
CREATE TABLE withdraw_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 6) NOT NULL,
    method ENUM('JazzCash', 'Easypaisa') NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    transaction_id VARCHAR(255) DEFAULT NULL,
    admin_note TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- D-Pins Table
CREATE TABLE d_pins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pin_code VARCHAR(50) UNIQUE NOT NULL,
    user_id INT DEFAULT NULL COMMENT 'User who purchased this pin',
    used_by INT DEFAULT NULL COMMENT 'User who used this pin',
    price DECIMAL(10, 2) NOT NULL,
    status ENUM('unused', 'used') DEFAULT 'unused',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    used_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Support Tickets Table
CREATE TABLE support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('open', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Support Ticket Replies Table
CREATE TABLE ticket_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    is_admin TINYINT(1) DEFAULT 0,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Banners/Sliders Table
CREATE TABLE banners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image_path VARCHAR(255) NOT NULL,
    title VARCHAR(255) DEFAULT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings Table
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert Default Settings
INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'FAST EARN LIMITED'),
('site_logo', 'assets/images/logo.png'),
('admin_jazzcash_number', '03001234567'),
('admin_jazzcash_name', 'Admin Account'),
('admin_easypaisa_number', '03001234567'),
('admin_easypaisa_name', 'Admin Account'),
('minimum_withdraw', '5.00'),
('referral_required_days', '7'),
('referral_required_count', '1'),
('ads_earning_base', '0.0004'),
('ads_earning_daily_increase', '0.0001'),
('ads_earning_max', '0.0020'),
('announcement_text', 'Welcome to FAST EARN LIMITED! Start earning today!'),
('currency_symbol', '$'),
('dpin_price', '10.00'),
('referral_level_1_commission', '10'),
('referral_level_2_commission', '5'),
('referral_level_3_commission', '3'),
('referral_level_4_commission', '2'),
('referral_level_5_commission', '1');

-- Insert Default Admin User (username: admin, password: admin123)
INSERT INTO users (username, full_name, email, phone, account_type, account_number, account_name, password, role, status) VALUES
('admin', 'Administrator', 'admin@fastearn.com', '03000000000', 'JazzCash', '03000000000', 'Admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Insert Sample Plans
INSERT INTO plans (plan_name, plan_price, ads_limit_per_day, plan_duration, status) VALUES
('Basic Plan', 10.00, 10, 30, 'active'),
('Standard Plan', 25.00, 25, 30, 'active'),
('Premium Plan', 50.00, 50, 30, 'active'),
('VIP Plan', 100.00, 100, 30, 'active');

-- Insert Sample Ads
INSERT INTO ads (ad_title, ad_url, timer, status) VALUES
('Sample Ad 1', 'https://example.com/ad1', 30, 'active'),
('Sample Ad 2', 'https://example.com/ad2', 45, 'active'),
('Sample Ad 3', 'https://example.com/ad3', 60, 'active');