-- Database creation and selection is handled by install.php
-- This file assumes the database is already selected

-- 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,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    coins INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price_coins INT NOT NULL,
    image_url VARCHAR(255),
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    total_coins INT NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Transactions table (for coin history)
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('purchase', 'refund', 'bonus', 'withdrawal') NOT NULL,
    amount INT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Admin users table
CREATE TABLE IF NOT EXISTS admins (
    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('admin', 'super_admin') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Admin user will be created during installation

-- AdMob settings table
CREATE TABLE IF NOT EXISTS admob_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(100) NOT NULL,
    video_ad_unit_id VARCHAR(100) NOT NULL,
    reward_coins INT DEFAULT 10,
    daily_limit INT DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Video reward history table
CREATE TABLE IF NOT EXISTS video_rewards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ad_unit_id VARCHAR(100) NOT NULL,
    coins_earned INT NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- App settings table
CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- App versions table
CREATE TABLE IF NOT EXISTS app_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(50) NOT NULL,
    version_code INT NOT NULL,
    update_type ENUM('optional', 'forced') DEFAULT 'optional',
    message TEXT,
    download_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert sample products
INSERT INTO products (name, description, price_coins, category) VALUES 
('Product 1', 'Sample product description', 100, 'electronics'),
('Product 2', 'Another sample product', 200, 'clothing'),
('Product 3', 'Premium product', 500, 'premium');

-- Insert default AdMob settings
INSERT INTO admob_settings (app_id, video_ad_unit_id, reward_coins, daily_limit) VALUES 
('ca-app-pub-xxxxxxxxxxxxxxxx~yyyyyyyyyy', 'ca-app-pub-xxxxxxxxxxxxxxxx/zzzzzzzzzz', 15, 10);

-- Insert default app settings
INSERT INTO app_settings (setting_key, setting_value, description) VALUES 
('app_name', 'Members App', 'نام اپلیکیشن'),
('app_version', '1.0.0', 'نسخه اپلیکیشن'),
('maintenance_mode', '0', 'حالت تعمیر و نگهداری'),
('registration_enabled', '1', 'فعال بودن ثبت‌نام'),
('max_login_attempts', '5', 'حداکثر تلاش برای ورود'),
('session_timeout', '3600', 'زمان انقضای نشست (ثانیه)'),
('default_coins', '100', 'سکه‌های اولیه برای کاربران جدید'),
('welcome_message', 'به اپلیکیشن ما خوش آمدید!', 'پیام خوش‌آمدگویی');
