CREATE DATABASE IF NOT EXISTS upnewsxy_admin;
USE upnewsxy_admin;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    balance INT DEFAULT 0,
    device_id VARCHAR(255),
    referral_code VARCHAR(20) UNIQUE,
    referred_by VARCHAR(20),
    status ENUM('active', 'banned') DEFAULT 'active',
    fcm_token TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



-- Settings Table (for dynamic admin control)
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL
);

-- Insert Default Settings
INSERT INTO settings (setting_key, setting_value) VALUES
('currency_rate', '100'), -- 1 BDT = 100 Coins
('min_withdraw', '5000'), -- Minimum coin to withdraw
('signup_bonus', '100'),
('daily_checkin_reward', '50'),
('spin_limit', '10'),
('scratch_limit', '10'),
('banner_ad_id', 'ca-app-pub-3940256099942544/6300978111'), -- Test ID
('interstitial_ad_id', 'ca-app-pub-3940256099942544/1033173712'), -- Test ID
('rewarded_ad_id', 'ca-app-pub-3940256099942544/5224354917'), -- Test ID
('maintainance_mode', 'false')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Tasks Table
CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('install', 'video', 'visit', 'telegram', 'other') NOT NULL,
    link TEXT NOT NULL,
    reward INT NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Quiz Questions
CREATE TABLE IF NOT EXISTS quiz_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    option_a VARCHAR(255) NOT NULL,
    option_b VARCHAR(255) NOT NULL,
    option_c VARCHAR(255) NOT NULL,
    option_d VARCHAR(255) NOT NULL,
    correct_answer ENUM('A', 'B', 'C', 'D') NOT NULL,
    reward INT DEFAULT 10,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Withdrawals
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    method VARCHAR(50) NOT NULL, -- bKash, Nagad, Rocket
    account_number VARCHAR(50) NOT NULL,
    amount_coins INT NOT NULL,
    amount_bdt DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Transaction/Earning History
CREATE TABLE IF NOT EXISTS history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type VARCHAR(50) NOT NULL, -- spin, scratch, withdraw, referral, bonus
    details VARCHAR(255),
    amount INT NOT NULL, -- Positive for earning, Negative for withdraw
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Admin Users
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default Admin
INSERT INTO admins (username, password_hash) 
SELECT 'antunusu', '$2y$12$cXcHNSujFZ/Vv/PW7A8PU.eDkawe8HXWB1noczfC6aYGkKfy2zAJy' 
WHERE NOT EXISTS (SELECT * FROM admins WHERE username = 'antunusu');

-- Notices / News
CREATE TABLE IF NOT EXISTS notices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    is_breaking BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);