CREATE DATABASE IF NOT EXISTS adstask_db;
USE adstask_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uid VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    referral_code VARCHAR(50),
    balance DECIMAL(10, 2) DEFAULT 0.00,
    total_earnings DECIMAL(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    reward DECIMAL(10, 2) NOT NULL,
    icon_url VARCHAR(255),
    task_type VARCHAR(50),
    action_url VARCHAR(255),
    timer_seconds INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS completed_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_uid VARCHAR(255),
    task_id INT,
    reward_amount DECIMAL(10, 2),
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_uid) REFERENCES users(uid),
    FOREIGN KEY (task_id) REFERENCES tasks(id)
);

CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_uid VARCHAR(255),
    amount DECIMAL(10, 2) NOT NULL,
    method VARCHAR(50) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_uid) REFERENCES users(uid)
);

INSERT INTO tasks (title, description, reward, icon_url, task_type, action_url, timer_seconds) VALUES
('Watch Video', 'Watch a 30s video to earn.', 0.05, 'https://example.com/video_icon.png', 'VIDEO', 'https://youtube.com', 30),
('Install App', 'Install and open the app.', 0.50, 'https://example.com/app_icon.png', 'INSTALL', 'https://play.google.com', 0),
('Visit Website', 'Stay on the site for 60 seconds.', 0.02, 'https://example.com/web_icon.png', 'VISIT', 'https://google.com', 60);