-- Royal Casino Platform Database Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.3+


-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin','moderator','finance_manager','support_agent','user') DEFAULT 'user',
    first_name VARCHAR(50) DEFAULT NULL,
    last_name VARCHAR(50) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    locale VARCHAR(5) DEFAULT 'bn',
    avatar VARCHAR(255) DEFAULT NULL,
    balance DECIMAL(15,2) DEFAULT 0.00,
    bonus_balance DECIMAL(15,2) DEFAULT 0.00,
    vip_level INT DEFAULT 0,
    referral_code VARCHAR(20) UNIQUE,
    referred_by INT UNSIGNED DEFAULT NULL,
    status ENUM('active','inactive','banned','pending') DEFAULT 'active',
    email_verified TINYINT(1) DEFAULT 0,
    remember_token VARCHAR(255) DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (role),
    INDEX idx_status (status),
    INDEX idx_referral (referral_code)
) ENGINE=InnoDB;

-- Settings Table (Dynamic Branding)
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_group VARCHAR(50) DEFAULT 'general',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Game Providers
CREATE TABLE IF NOT EXISTS providers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    logo VARCHAR(255) DEFAULT NULL,
    description TEXT,
    status ENUM('active','inactive') DEFAULT 'active',
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Games
CREATE TABLE IF NOT EXISTS games (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_id INT UNSIGNED DEFAULT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    image VARCHAR(255) DEFAULT NULL,
    category ENUM('slots','live_casino','sports','hot','table','other') DEFAULT 'slots',
    description TEXT,
    game_url VARCHAR(500) DEFAULT NULL,
    is_hot TINYINT(1) DEFAULT 0,
    is_featured TINYINT(1) DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    sort_order INT DEFAULT 0,
    play_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL,
    INDEX idx_category (category),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Banners
CREATE TABLE IF NOT EXISTS banners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    subtitle VARCHAR(255) DEFAULT NULL,
    image VARCHAR(255) NOT NULL,
    link VARCHAR(500) DEFAULT NULL,
    button_text VARCHAR(50) DEFAULT 'Play Now',
    status ENUM('active','inactive') DEFAULT 'active',
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Promotions
CREATE TABLE IF NOT EXISTS promotions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    image VARCHAR(255) DEFAULT NULL,
    description TEXT,
    terms TEXT,
    bonus_amount DECIMAL(10,2) DEFAULT NULL,
    bonus_percent DECIMAL(5,2) DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    status ENUM('active','inactive','expired') DEFAULT 'active',
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- VIP Levels
CREATE TABLE IF NOT EXISTS vip_levels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    level INT NOT NULL UNIQUE,
    min_deposit DECIMAL(15,2) DEFAULT 0,
    cashback_percent DECIMAL(5,2) DEFAULT 0,
    bonus_multiplier DECIMAL(5,2) DEFAULT 1.00,
    icon VARCHAR(255) DEFAULT NULL,
    benefits TEXT,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Referrals
CREATE TABLE IF NOT EXISTS referrals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT UNSIGNED NOT NULL,
    referred_id INT UNSIGNED NOT NULL,
    commission DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending','approved','paid') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_referral (referrer_id, referred_id)
) ENGINE=InnoDB;

-- Deposits
CREATE TABLE IF NOT EXISTS deposits (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    method ENUM('bank_transfer','credit_card','crypto','ewallet','other') DEFAULT 'bank_transfer',
    transaction_id VARCHAR(100) DEFAULT NULL,
    proof_image VARCHAR(255) DEFAULT NULL,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    admin_note TEXT,
    processed_by INT UNSIGNED DEFAULT NULL,
    processed_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Withdrawals
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    method ENUM('bank_transfer','crypto','ewallet','other') DEFAULT 'bank_transfer',
    account_details TEXT,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    admin_note TEXT,
    processed_by INT UNSIGNED DEFAULT NULL,
    processed_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Transactions (Wallet History)
CREATE TABLE IF NOT EXISTS transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type ENUM('deposit','withdraw','bet','win','bonus','referral','adjustment') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    reference_type VARCHAR(50) DEFAULT NULL,
    reference_id INT UNSIGNED DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_type (user_id, type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Notifications
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info','success','warning','promo','system') DEFAULT 'info',
    is_read TINYINT(1) DEFAULT 0,
    is_global TINYINT(1) DEFAULT 0,
    link VARCHAR(500) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read)
) ENGINE=InnoDB;

-- Audit Logs
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) DEFAULT NULL,
    entity_id INT UNSIGNED DEFAULT NULL,
    old_values JSON DEFAULT NULL,
    new_values JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Backups Log
CREATE TABLE IF NOT EXISTS backups (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_size BIGINT DEFAULT 0,
    created_by INT UNSIGNED DEFAULT NULL,
    status ENUM('completed','failed') DEFAULT 'completed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default Settings
INSERT INTO settings (setting_key, setting_value, setting_group) VALUES
('app_name', 'KRIKZO', 'branding'),
('logo', '', 'branding'),
('favicon', '', 'branding'),
('app_icon', '', 'branding'),
('primary_color', '#00c9a7', 'theme'),
('secondary_color', '#ffc107', 'theme'),
('accent_color', '#00897b', 'theme'),
('background_color', '#003c32', 'theme'),
('footer_text', '© 2026 KRIKZO. All rights reserved. Play responsibly. 18+', 'branding'),
('footer_links', '[]', 'branding'),
('currency', 'USD', 'general'),
('currency_symbol', '$', 'general'),
('min_deposit', '10', 'general'),
('min_withdraw', '20', 'general'),
('support_email', 'support@royalcasino.com', 'general'),
('support_phone', '+1-800-ROYAL', 'general'),
('social_facebook', '#', 'social'),
('social_twitter', '#', 'social'),
('social_instagram', '#', 'social'),
('social_telegram', '#', 'social'),
('maintenance_mode', '0', 'general'),
('referral_bonus', '10', 'referral'),
('referral_commission', '5', 'referral'),
('admin_default_locale', 'en', 'general');

-- Default Super Admin (password: Admin@123)
INSERT INTO users (username, email, password, role, first_name, last_name, referral_code, status, email_verified) VALUES
('superadmin', 'admin@royalcasino.com', '$2y$10$1qbWcuh3shGuMCVpEHMC7OLHgMjqxMCEvbHGhmUuTLDQO6HcZAYaG', 'super_admin', 'Super', 'Admin', 'ADMIN001', 'active', 1);

-- Default VIP Levels
INSERT INTO vip_levels (name, level, min_deposit, cashback_percent, bonus_multiplier, benefits) VALUES
('Bronze', 1, 0, 0.5, 1.00, 'Basic support, Weekly bonuses'),
('Silver', 2, 500, 1.0, 1.10, 'Priority support, Enhanced bonuses'),
('Gold', 3, 2000, 2.0, 1.25, 'Dedicated manager, Exclusive promotions'),
('Platinum', 4, 10000, 3.0, 1.50, 'VIP events, Personal account manager'),
('Diamond', 5, 50000, 5.0, 2.00, 'Ultimate VIP experience, Luxury rewards');

-- Sample Providers
INSERT INTO providers (name, slug, description, status, sort_order) VALUES
('Evolution Gaming', 'evolution', 'Leading live casino provider', 'active', 1),
('Pragmatic Play', 'pragmatic', 'Top slots and live games', 'active', 2),
('NetEnt', 'netent', 'Premium slot games', 'active', 3),
('Microgaming', 'microgaming', 'Classic casino games', 'active', 4);

-- Sample Games
INSERT INTO games (provider_id, name, slug, category, is_hot, is_featured, status, sort_order) VALUES
(1, 'Live Blackjack', 'live-blackjack', 'live_casino', 1, 1, 'active', 1),
(1, 'Live Roulette', 'live-roulette', 'live_casino', 1, 1, 'active', 2),
(2, 'Sweet Bonanza', 'sweet-bonanza', 'slots', 1, 1, 'active', 1),
(2, 'Gates of Olympus', 'gates-of-olympus', 'slots', 1, 1, 'active', 2),
(3, 'Starburst', 'starburst', 'slots', 0, 1, 'active', 3),
(2, 'Mega Roulette', 'mega-roulette', 'live_casino', 1, 0, 'active', 3),
(4, 'Football Betting', 'football-betting', 'sports', 1, 1, 'active', 1),
(4, 'Basketball Live', 'basketball-live', 'sports', 0, 1, 'active', 2);

-- Sample Banners
INSERT INTO banners (title, subtitle, image, link, button_text, status, sort_order) VALUES
('Welcome Bonus', 'Get 100% up to $500 on your first deposit!', 'banners/default-banner-1.jpg', '/register', 'Claim Now', 'active', 1),
('VIP Rewards', 'Join our exclusive VIP program today', 'banners/default-banner-2.jpg', '/vip', 'Learn More', 'active', 2),
('Live Casino', 'Experience real dealers, real action', 'banners/default-banner-3.jpg', '/live-casino', 'Play Live', 'active', 3);

-- Sample Promotions
INSERT INTO promotions (title, slug, description, bonus_percent, status, sort_order) VALUES
('Welcome Bonus 100%', 'welcome-bonus', 'Double your first deposit up to $500!', 100, 'active', 1),
('Weekly Cashback', 'weekly-cashback', 'Get 10% cashback on all losses every week', 10, 'active', 2),
('Free Spins Friday', 'free-spins-friday', '50 free spins every Friday for active players', NULL, 'active', 3);