CREATE DATABASE IF NOT EXISTS quiz_game_app;
USE quiz_game_app;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    device_id VARCHAR(255) UNIQUE,
    coins DECIMAL(10,2) DEFAULT 0.00,
    referral_code VARCHAR(10) UNIQUE,
    status ENUM('active', 'blocked', 'deleted') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL
);

CREATE TABLE daily_checkins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    coins_earned DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE quiz_questions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_type ENUM('spelling', 'image_puzzle', 'find_faster', 'math_check') NOT NULL,
    question_data JSON NOT NULL,
    correct_answer VARCHAR(255) NOT NULL,
    reward_coins DECIMAL(10,2) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE quiz_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_id BIGINT UNSIGNED,
    option_text VARCHAR(255),
    image_url VARCHAR(255),
    is_correct TINYINT(1) NOT NULL,
    option_order INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES quiz_questions(id)
);

CREATE TABLE user_quiz_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    question_id BIGINT UNSIGNED,
    answer VARCHAR(255) NOT NULL,
    is_correct TINYINT(1) NOT NULL,
    coins_earned DECIMAL(10,2) DEFAULT 0.00,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (question_id) REFERENCES quiz_questions(id)
);

CREATE TABLE website_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(255) NOT NULL,
    timer_seconds INT NOT NULL,
    coins_to_earn DECIMAL(10,2) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user_website_task_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    website_task_id BIGINT UNSIGNED,
    status ENUM('pending', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (website_task_id) REFERENCES website_tasks(id)
);

CREATE TABLE promo_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    coins DECIMAL(10,2) NOT NULL,
    max_usage INT NOT NULL,
    used_count INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_promo_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    promo_code_id BIGINT UNSIGNED,
    coins_earned DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id)
);

CREATE TABLE redeem_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('google_play', 'amazon_pay', 'flipkart_voucher', 'upi_transfer') NOT NULL,
    coin_cost DECIMAL(10,2) NOT NULL,
    min_redeem_coins DECIMAL(10,2) NOT NULL,
    redeem_instructions TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE redeem_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    redeem_option_id BIGINT UNSIGNED,
    code VARCHAR(255) UNIQUE NOT NULL,
    value DECIMAL(10,2) NOT NULL,
    status ENUM('available', 'used') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (redeem_option_id) REFERENCES redeem_options(id)
);

CREATE TABLE withdrawal_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    redeem_option_id BIGINT UNSIGNED,
    requested_coins DECIMAL(10,2) NOT NULL,
    user_remark TEXT,
    admin_remark TEXT,
    redeem_code_id BIGINT UNSIGNED NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (redeem_option_id) REFERENCES redeem_options(id),
    FOREIGN KEY (redeem_code_id) REFERENCES redeem_codes(id)
);

CREATE TABLE admin_activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT UNSIGNED,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id)
);

CREATE TABLE user_game_plays (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    game_id VARCHAR(255) NOT NULL,
    game_name VARCHAR(255) NOT NULL,
    coins_earned DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE app_settings (
    setting_key VARCHAR(255) PRIMARY KEY,
    setting_value TEXT NOT NULL
);

INSERT INTO app_settings (setting_key, setting_value) VALUES
('app_status', 'active'),
('maintenance_message', 'App is under maintenance...'),
('vpn_warning_enabled', '1'),
('latest_app_version', '1.0.0'),
('telegram_link', 'https://t.me/your_channel'),
('privacy_policy_url', 'https://yourdomain.com/privacy'),
('how_to_work_url', 'https://yourdomain.com/how-to-work'),
('admin_contact_email', 'support@yourdomain.com'),
('earning_mode_enabled', '1'),
('daily_task_limit_earning_on', '1'),
('daily_task_limit_earning_off', '99999'),
('website_task_enabled', '0'),
('show_ad_after_task_completion_if_website_task_disabled', '1'),
('referrer_coins_on_referral', '10.00'),
('referred_coins_on_referral', '5.00'),
('daily_spin_coins', '10.00'),
('game_play_coins', '5.00'),
('token_expiry_days', '7');