-- ============================================================================
-- iGaming API - Complete Database Schema
-- Enterprise iGaming GGR Client Management System
-- ============================================================================

CREATE DATABASE IF NOT EXISTS developer0_api CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE developer0_api;

-- ============================================================================
-- 1. ADMINS 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 COMMENT 'bcrypt hash',
    full_name VARCHAR(100),
    role ENUM('super_admin') DEFAULT 'super_admin',
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login DATETIME,
    remember_token VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. CLIENTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_code VARCHAR(20) UNIQUE NOT NULL COMMENT 'Auto-generated like CLT-00001',
    company_name VARCHAR(100) NOT NULL,
    contact_name VARCHAR(100),
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    api_token VARCHAR(64) UNIQUE COMMENT 'SHA-256 hex token',
    api_secret VARCHAR(64) UNIQUE,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    ggr_percentage DECIMAL(5,2) DEFAULT 30.00,
    settlement_cycle ENUM('daily', 'weekly', 'biweekly', 'monthly') DEFAULT 'monthly',
    negative_ggr_enabled TINYINT(1) DEFAULT 0,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    notes TEXT,
    last_login DATETIME,
    remember_token VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_api_token (api_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. PROVIDERS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS providers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 4. PROVIDER CREDENTIALS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS provider_credentials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider_id INT NOT NULL,
    base_url_encrypted TEXT NOT NULL COMMENT 'AES-256 encrypted',
    token_encrypted TEXT NOT NULL,
    secret_encrypted TEXT NOT NULL,
    iv VARCHAR(32) NOT NULL COMMENT 'Initialization vector for AES encryption',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_provider_credentials_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. WALLETS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS wallets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNIQUE NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0.00,
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    credit_limit DECIMAL(15,2) DEFAULT 0.00,
    credit_used DECIMAL(15,2) DEFAULT 0.00,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    is_frozen TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_wallets_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 6. WALLET LOGS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS wallet_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    wallet_id INT NOT NULL,
    client_id INT NOT NULL,
    type ENUM('credit', 'debit', 'freeze', 'unfreeze', 'settlement', 'rollback', 'adjustment', 'opening_balance') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    description VARCHAR(255),
    reference_id VARCHAR(100),
    performed_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_type (type),
    INDEX idx_created (created_at),
    CONSTRAINT fk_wallet_logs_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id),
    CONSTRAINT fk_wallet_logs_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 7. BETS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS bets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    transaction_id VARCHAR(100) UNIQUE NOT NULL,
    round_id VARCHAR(100),
    game_id VARCHAR(100),
    provider_code VARCHAR(50),
    player_id VARCHAR(100),
    amount DECIMAL(15,2) NOT NULL,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    status ENUM('pending', 'confirmed', 'cancelled', 'rolled_back') DEFAULT 'confirmed',
    raw_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_transaction (transaction_id),
    INDEX idx_created (created_at),
    CONSTRAINT fk_bets_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. WINS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS wins (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    transaction_id VARCHAR(100) UNIQUE NOT NULL,
    round_id VARCHAR(100),
    game_id VARCHAR(100),
    provider_code VARCHAR(50),
    player_id VARCHAR(100),
    bet_transaction_id VARCHAR(100),
    amount DECIMAL(15,2) NOT NULL,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'confirmed',
    raw_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    CONSTRAINT fk_wins_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. ROLLBACKS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS rollbacks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    transaction_id VARCHAR(100) UNIQUE NOT NULL,
    original_transaction_id VARCHAR(100) NOT NULL,
    round_id VARCHAR(100),
    type ENUM('bet_rollback', 'win_rollback', 'refund') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    reason VARCHAR(255),
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    raw_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_rollbacks_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. SETTLEMENTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS settlements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_bet DECIMAL(15,2) DEFAULT 0.00,
    total_win DECIMAL(15,2) DEFAULT 0.00,
    ggr DECIMAL(15,2) DEFAULT 0.00,
    client_ggr_percentage DECIMAL(5,2),
    client_share DECIMAL(15,2) DEFAULT 0.00,
    company_share DECIMAL(15,2) DEFAULT 0.00,
    status ENUM('pending', 'approved', 'settled', 'disputed') DEFAULT 'pending',
    settled_at DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_period (period_start, period_end),
    CONSTRAINT fk_settlements_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 11. GGR REPORTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS ggr_reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    report_date DATE NOT NULL,
    report_type ENUM('daily', 'weekly', 'monthly') DEFAULT 'daily',
    total_bet DECIMAL(15,2) DEFAULT 0.00,
    total_win DECIMAL(15,2) DEFAULT 0.00,
    ggr DECIMAL(15,2) DEFAULT 0.00,
    player_count INT DEFAULT 0,
    bet_count INT DEFAULT 0,
    currency ENUM('INR', 'USD', 'PKR', 'BDT') DEFAULT 'INR',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_client_date_type (client_id, report_date, report_type),
    CONSTRAINT fk_ggr_reports_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 12. API LOGS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS api_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    client_id INT,
    method VARCHAR(10),
    endpoint VARCHAR(255),
    request_headers TEXT,
    request_body TEXT,
    response_status INT,
    response_body TEXT,
    ip_address VARCHAR(45),
    execution_time DECIMAL(8,4) COMMENT 'Execution time in seconds',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 13. WEBHOOK LOGS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS webhook_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    provider_code VARCHAR(50),
    event_type VARCHAR(50),
    idempotency_key VARCHAR(100) UNIQUE,
    payload JSON,
    signature VARCHAR(255),
    signature_valid TINYINT(1),
    processing_status ENUM('received', 'processing', 'completed', 'failed') DEFAULT 'received',
    response_code INT,
    response_body TEXT,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_idempotency (idempotency_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 14. ACTIVITY LOGS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('admin', 'client') NOT NULL,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_type, user_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 15. NOTIFICATIONS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('admin', 'client') NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'danger') DEFAULT 'info',
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_read (user_type, user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 16. LOGIN HISTORY TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS login_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('admin', 'client') NOT NULL,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    status ENUM('success', 'failed') NOT NULL,
    failure_reason VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_type, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 17. SETTINGS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_group VARCHAR(50) DEFAULT 'general',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SEED DATA: Default Settings
-- ============================================================================
INSERT INTO settings (setting_key, setting_value, setting_group) VALUES
    ('app_name', 'iGamingApi', 'general'),
    ('default_currency', 'INR', 'general'),
    ('default_ggr_percentage', '30.00', 'ggr'),
    ('default_settlement_cycle', 'monthly', 'settlement'),
    ('default_credit_limit', '0.00', 'wallet'),
    ('maintenance_mode', '0', 'general'),
    ('rate_limit_per_minute', '60', 'security');

-- ============================================================================
-- SEED DATA: Default Provider
-- ============================================================================
INSERT INTO providers (name, code, status) VALUES
    ('Primary Provider', 'primary', 'active');