-- ============================================
-- CRÉATION DE LA BASE DE DONNÉES
-- ============================================

CREATE DATABASE IF NOT EXISTS nelsius_pay 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE nelsius_pay;

-- ============================================
-- TABLE: users
-- ============================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100),
    prenom VARCHAR(100),
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255),
    role ENUM('user','merchant','admin','super_admin') DEFAULT 'user',
    email_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(255),
    kyc_status ENUM('pending','verified','rejected') DEFAULT 'pending',
    is_active BOOLEAN DEFAULT TRUE,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255),
    last_login DATETIME NULL,
    remember_token VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone),
    INDEX idx_users_status (kyc_status, is_active),
    INDEX idx_users_role (role)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: oauth_providers
-- ============================================
CREATE TABLE oauth_providers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    client_id VARCHAR(255) NOT NULL,
    client_secret VARCHAR(255) NOT NULL,
    redirect_url VARCHAR(500),
    scopes JSON,
    is_active BOOLEAN DEFAULT TRUE,
    icon VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================
-- TABLE: oauth_accounts
-- ============================================
CREATE TABLE oauth_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    provider_id INT NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,
    email VARCHAR(150),
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at DATETIME NULL,
    avatar_url VARCHAR(500),
    profile_data JSON,
    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,
    FOREIGN KEY (provider_id) REFERENCES oauth_providers(id) ON DELETE CASCADE,
    UNIQUE KEY unique_provider_user (provider_id, provider_user_id),
    INDEX idx_oauth_user (user_id),
    INDEX idx_oauth_provider (provider_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_profiles
-- ============================================
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    date_naissance DATE NULL,
    photo VARCHAR(255) NULL,
    ville VARCHAR(100) NULL,
    quartier VARCHAR(100) NULL,
    salaire DECIMAL(18,2) NULL,
    etudiant BOOLEAN DEFAULT FALSE,
    genre ENUM('male','female','other') NULL,
    adresse TEXT NULL,
    pays VARCHAR(50) DEFAULT 'Cameroun',
    code_postal VARCHAR(20),
    bio TEXT,
    site_web VARCHAR(255),
    facebook_url VARCHAR(255),
    twitter_url VARCHAR(255),
    linkedin_url VARCHAR(255),
    autres_infos JSON 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,
    UNIQUE KEY unique_user_profile (user_id),
    INDEX idx_user_profiles_user (user_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: wallets
-- ============================================
CREATE TABLE wallets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    balance DECIMAL(18,2) DEFAULT 0.00,
    available_balance DECIMAL(18,2) DEFAULT 0.00,
    hold_balance DECIMAL(18,2) DEFAULT 0.00,
    is_primary BOOLEAN DEFAULT TRUE,
    wallet_type ENUM('personal','business','escrow','savings') DEFAULT 'personal',
    wallet_number VARCHAR(50) UNIQUE NOT NULL,
    wallet_name VARCHAR(100) DEFAULT 'Portefeuille principal',
    status ENUM('active','frozen','closed','suspended') DEFAULT 'active',
    min_balance DECIMAL(18,2) DEFAULT 0.00,
    max_balance DECIMAL(18,2) DEFAULT 100000000.00,
    last_transaction_at DATETIME NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_currency (user_id, currency, wallet_type),
    INDEX idx_wallets_user (user_id),
    INDEX idx_wallets_number (wallet_number),
    INDEX idx_wallets_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: payment_links
-- ============================================
CREATE TABLE payment_links (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('simple','multiple','donate','investment','subscription','product','service') DEFAULT 'simple',
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    image VARCHAR(255) NULL,
    amount DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    qr_code VARCHAR(255) NULL,
    short_url VARCHAR(100) UNIQUE,
    custom_id VARCHAR(100) UNIQUE,
    views_count INT DEFAULT 0,
    success_count INT DEFAULT 0,
    payment_methods JSON,
    redirect_url VARCHAR(500) NULL,
    webhook_url VARCHAR(500) NULL,
    metadata JSON,
    expires_at DATETIME NULL,
    is_reusable BOOLEAN DEFAULT FALSE,
    max_uses INT NULL,
    current_uses INT DEFAULT 0,
    status ENUM('active','inactive','expired','deleted') DEFAULT 'active',
    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_payment_links_user (user_id),
    INDEX idx_payment_links_status (status),
    INDEX idx_payment_links_custom (custom_id),
    INDEX idx_payment_links_short (short_url),
    INDEX idx_payment_links_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: items
-- ============================================
CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payment_link_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    image VARCHAR(255) NULL,
    url VARCHAR(255) NULL,
    sku VARCHAR(100) NULL,
    quantity_available INT DEFAULT NULL,
    quantity_sold INT DEFAULT 0,
    other_infos JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_link_id) REFERENCES payment_links(id) ON DELETE CASCADE,
    INDEX idx_items_payment_link (payment_link_id),
    INDEX idx_items_sku (sku)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: merchant_products
-- ============================================
CREATE TABLE merchant_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sku VARCHAR(100) UNIQUE,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    cost_price DECIMAL(18,2) NULL,
    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 10,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    images JSON,
    attributes JSON,
    weight DECIMAL(10,2) NULL,
    dimensions VARCHAR(100) NULL,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    is_digital BOOLEAN DEFAULT FALSE,
    download_url VARCHAR(255) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_products_merchant (merchant_id),
    INDEX idx_products_sku (sku),
    INDEX idx_products_category (category),
    INDEX idx_products_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: transactions
-- ============================================
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    wallet_id INT NOT NULL,
    link_id INT NULL,
    product_id INT NULL,
    merchant_id INT NULL,
    transaction_code VARCHAR(50) UNIQUE NOT NULL,
    reference_id VARCHAR(100) NULL,
    parent_transaction_id INT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    status ENUM('pending','completed','failed','cancelled','refunded','disputed','on_hold') DEFAULT 'pending',
    type ENUM('payment','withdraw','transfer','investment','refund','deposit','commission','fee') DEFAULT 'payment',
    method ENUM('mobile_money','bank','card','paypal','wallet','cash') DEFAULT 'mobile_money',
    sub_method VARCHAR(50) NULL,
    payer_name VARCHAR(100) NULL,
    payer_email VARCHAR(150) NULL,
    payer_phone VARCHAR(20) NULL,
    sender_phone VARCHAR(20) NULL,
    receiver_phone VARCHAR(20) NULL,
    sender_wallet_id INT NULL,
    receiver_wallet_id INT NULL,
    details JSON,
    fees DECIMAL(18,2) DEFAULT 0.00,
    net_amount DECIMAL(18,2) NOT NULL,
    is_refund BOOLEAN DEFAULT FALSE,
    refund_reason TEXT NULL,
    refunded_amount DECIMAL(18,2) DEFAULT 0.00,
    metadata JSON,
    operator_transaction_id VARCHAR(100) NULL,
    bank_reference VARCHAR(100) NULL,
    failure_reason TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    confirmed_at DATETIME 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,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
    FOREIGN KEY (link_id) REFERENCES payment_links(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES items(id) ON DELETE SET NULL,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (sender_wallet_id) REFERENCES wallets(id) ON DELETE SET NULL,
    FOREIGN KEY (receiver_wallet_id) REFERENCES wallets(id) ON DELETE SET NULL,
    INDEX idx_transactions_user (user_id),
    INDEX idx_transactions_code (transaction_code),
    INDEX idx_transactions_status_date (status, created_at),
    INDEX idx_transactions_merchant (merchant_id),
    INDEX idx_transactions_reference (reference_id),
    INDEX idx_transactions_parent (parent_transaction_id),
    INDEX idx_transactions_type_method (type, method),
    INDEX idx_transactions_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: withdrawals
-- ============================================
CREATE TABLE withdrawals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    wallet_id INT NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    fees DECIMAL(18,2) DEFAULT 0.00,
    net_amount DECIMAL(18,2) NOT NULL,
    method ENUM('mobile_money','bank','card') NOT NULL,
    phone_number VARCHAR(20),
    operator ENUM('orange_money','mtn_money','moov_money','express_union','western_union'),
    bank_name VARCHAR(100),
    bank_code VARCHAR(50),
    account_number VARCHAR(50),
    account_name VARCHAR(150),
    iban VARCHAR(34) NULL,
    transaction_id INT NULL,
    withdrawal_reference VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('pending','processing','completed','failed','cancelled','reversed') DEFAULT 'pending',
    failure_reason TEXT,
    processed_by INT NULL,
    processed_at DATETIME 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,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_withdrawals_user (user_id),
    INDEX idx_withdrawals_status (status),
    INDEX idx_withdrawals_reference (withdrawal_reference),
    INDEX idx_withdrawals_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: invoices
-- ============================================
CREATE TABLE invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    transaction_id INT NOT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    customer_name VARCHAR(150),
    customer_email VARCHAR(150),
    customer_phone VARCHAR(20),
    customer_address TEXT,
    amount DECIMAL(18,2),
    currency CHAR(3) DEFAULT 'XAF',
    tax_amount DECIMAL(18,2) DEFAULT 0.00,
    total_amount DECIMAL(18,2),
    items JSON,
    due_date DATE NULL,
    paid_at DATETIME NULL,
    notes TEXT,
    status ENUM('draft','pending','paid','cancelled','overdue','partially_paid') DEFAULT 'pending',
    pdf_url VARCHAR(255) NULL,
    details JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    INDEX idx_invoices_merchant (merchant_id),
    INDEX idx_invoices_number (invoice_number),
    INDEX idx_invoices_status (status),
    INDEX idx_invoices_due_date (due_date)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: fees
-- ============================================
CREATE TABLE fees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('transaction','withdraw','transfer','investment','deposit','monthly','annual') DEFAULT 'transaction',
    method ENUM('mobile_money','bank','card','paypal','wallet','cash') DEFAULT 'mobile_money',
    sub_method VARCHAR(50) NULL,
    country CHAR(2) DEFAULT 'CM',
    value_type ENUM('percentage','fixed','tiered') DEFAULT 'percentage',
    value DECIMAL(10,4) NOT NULL,
    min_amount DECIMAL(18,2) DEFAULT 0.00,
    max_amount DECIMAL(18,2) NULL,
    min_fee DECIMAL(18,2) DEFAULT 0.00,
    max_fee DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    applies_to ENUM('sender','receiver','both','platform') DEFAULT 'sender',
    description VARCHAR(255),
    active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_fees_type_method (type, method),
    INDEX idx_fees_active (active),
    INDEX idx_fees_country (country)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: commissions
-- ============================================
CREATE TABLE commissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    merchant_id INT NOT NULL,
    platform_fee DECIMAL(18,2),
    merchant_amount DECIMAL(18,2),
    commission_rate DECIMAL(5,2),
    status ENUM('pending','paid','hold','cancelled') DEFAULT 'pending',
    payout_date DATE NULL,
    paid_at DATETIME NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_commissions_merchant (merchant_id),
    INDEX idx_commissions_status (status),
    INDEX idx_commissions_payout (payout_date)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: exchange_rates
-- ============================================
CREATE TABLE exchange_rates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_currency CHAR(3) NOT NULL,
    to_currency CHAR(3) NOT NULL,
    rate DECIMAL(18,6) NOT NULL,
    inverse_rate DECIMAL(18,6) NOT NULL,
    source VARCHAR(50) DEFAULT 'manual',
    valid_from DATETIME NOT NULL,
    valid_to DATETIME NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_rate_pair (from_currency, to_currency, valid_from),
    INDEX idx_exchange_rates_active (is_active),
    INDEX idx_exchange_rates_from (from_currency),
    INDEX idx_exchange_rates_to (to_currency)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: api_keys
-- ============================================
CREATE TABLE api_keys (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    merchant_id INT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    api_secret VARCHAR(128) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    permissions JSON,
    webhook_url VARCHAR(500) NULL,
    ip_whitelist JSON,
    rate_limit_per_minute INT DEFAULT 60,
    rate_limit_per_day INT DEFAULT 10000,
    calls_today INT DEFAULT 0,
    last_reset_date DATE,
    status ENUM('active','inactive','suspended','revoked') DEFAULT 'active',
    last_used_at DATETIME NULL,
    expires_at DATETIME 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,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_api_keys_key (api_key),
    INDEX idx_api_keys_user (user_id),
    INDEX idx_api_keys_status (status),
    INDEX idx_api_keys_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: webhook_endpoints
-- ============================================
CREATE TABLE webhook_endpoints (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    merchant_id INT NULL,
    url VARCHAR(500) NOT NULL,
    description VARCHAR(255),
    events JSON NOT NULL,
    secret_key VARCHAR(64) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    success_count INT DEFAULT 0,
    failure_count INT DEFAULT 0,
    last_called_at DATETIME NULL,
    last_status_code INT 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,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_webhook_endpoints_user (user_id),
    INDEX idx_webhook_endpoints_active (is_active),
    INDEX idx_webhook_endpoints_url (url(100))
) ENGINE=InnoDB;

-- ============================================
-- TABLE: webhook_events
-- ============================================
CREATE TABLE webhook_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_webhook_events_name (event_name)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: webhook_logs
-- ============================================
CREATE TABLE webhook_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    webhook_endpoint_id INT NULL,
    api_key_id INT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_id VARCHAR(100) NOT NULL,
    payload JSON,
    url VARCHAR(500),
    headers JSON,
    response_code INT,
    response_body TEXT,
    attempts INT DEFAULT 1,
    status ENUM('pending','delivered','failed') DEFAULT 'pending',
    error_message TEXT,
    next_retry_at DATETIME NULL,
    delivered_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (webhook_endpoint_id) REFERENCES webhook_endpoints(id) ON DELETE SET NULL,
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL,
    INDEX idx_webhooks_event (event_type),
    INDEX idx_webhooks_status (status),
    INDEX idx_webhooks_created (created_at),
    INDEX idx_webhooks_endpoint (webhook_endpoint_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: jwt_refresh_tokens
-- ============================================
CREATE TABLE jwt_refresh_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token_hash VARCHAR(255) UNIQUE NOT NULL,
    device_id VARCHAR(255) NULL,
    device_name VARCHAR(100) NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at DATETIME NOT NULL,
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at DATETIME NULL,
    revoked_by_ip VARCHAR(45) NULL,
    revoked_reason VARCHAR(100) NULL,
    parent_token_hash VARCHAR(255) NULL,
    family_id VARCHAR(64) NULL,
    last_used_at DATETIME 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_refresh_tokens_user (user_id),
    INDEX idx_refresh_tokens_hash (token_hash),
    INDEX idx_refresh_tokens_expires (expires_at),
    INDEX idx_refresh_tokens_family (family_id),
    INDEX idx_refresh_tokens_revoked (is_revoked)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: jwt_blacklist
-- ============================================
CREATE TABLE jwt_blacklist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    token_hash VARCHAR(255) UNIQUE NOT NULL,
    token_type ENUM('access','refresh') DEFAULT 'access',
    user_id INT NULL,
    expires_at DATETIME NOT NULL,
    blacklisted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reason ENUM('logout','password_change','security_breach','admin_revoked','suspicious_activity') DEFAULT 'logout',
    ip_address VARCHAR(45) NULL,
    metadata JSON,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_jwt_blacklist_hash (token_hash),
    INDEX idx_jwt_blacklist_expires (expires_at),
    INDEX idx_jwt_blacklist_user (user_id),
    INDEX idx_jwt_blacklist_type (token_type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: jwt_key_rotation
-- ============================================
CREATE TABLE jwt_key_rotation (
    id INT PRIMARY KEY AUTO_INCREMENT,
    key_id VARCHAR(50) UNIQUE NOT NULL,
    key_type ENUM('current','previous','future') DEFAULT 'current',
    algorithm VARCHAR(20) DEFAULT 'HS256',
    secret_key TEXT NOT NULL,
    public_key TEXT NULL,
    private_key TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    activated_at DATETIME NULL,
    revoked_at DATETIME NULL,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_jwt_keys_keyid (key_id),
    INDEX idx_jwt_keys_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: password_resets
-- ============================================
CREATE TABLE password_resets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(150) NOT NULL,
    token VARCHAR(255) NOT NULL,
    user_id INT NULL,
    device_info JSON,
    ip_address VARCHAR(45),
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_password_resets_token (token),
    INDEX idx_password_resets_email (email),
    INDEX idx_password_resets_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_devices
-- ============================================
CREATE TABLE user_devices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    device_id VARCHAR(255) NOT NULL,
    device_type ENUM('ios','android','web','desktop') NOT NULL,
    device_name VARCHAR(100),
    device_model VARCHAR(100),
    os_version VARCHAR(50),
    app_version VARCHAR(20),
    push_token VARCHAR(255),
    fcm_token VARCHAR(255),
    last_login_at DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    notifications_enabled BOOLEAN DEFAULT TRUE,
    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,
    UNIQUE KEY unique_device_user (user_id, device_id, device_type),
    INDEX idx_user_devices_user (user_id),
    INDEX idx_user_devices_token (push_token),
    INDEX idx_user_devices_fcm (fcm_token)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_sessions
-- ============================================
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    session_id VARCHAR(100) UNIQUE NOT NULL,
    access_token_hash VARCHAR(255) NULL,
    refresh_token_hash VARCHAR(255) NULL,
    device_id INT NULL,
    device_info JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    location JSON,
    last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES user_devices(id) ON DELETE SET NULL,
    INDEX idx_user_sessions_token (session_id),
    INDEX idx_user_sessions_user (user_id),
    INDEX idx_user_sessions_expires (expires_at),
    INDEX idx_user_sessions_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: push_notifications
-- ============================================
CREATE TABLE push_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    device_id INT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSON,
    type ENUM('transaction','security','promotion','system','alert') DEFAULT 'system',
    priority ENUM('normal','high','urgent') DEFAULT 'normal',
    status ENUM('pending','sent','delivered','failed','read') DEFAULT 'pending',
    sent_at DATETIME NULL,
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    failure_reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES user_devices(id) ON DELETE SET NULL,
    INDEX idx_push_notifications_user (user_id),
    INDEX idx_push_notifications_status (status),
    INDEX idx_push_notifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: subscription_plans
-- ============================================
CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    price DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    `interval` ENUM('daily','weekly','monthly','yearly','lifetime') DEFAULT 'monthly',
    trial_days INT DEFAULT 0,
    features JSON,
    is_active BOOLEAN DEFAULT TRUE,
    is_popular BOOLEAN DEFAULT FALSE,
    max_transactions_per_month INT NULL,
    max_amount_per_transaction DECIMAL(18,2) NULL,
    fee_percentage DECIMAL(5,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscription_plans_slug (slug),
    INDEX idx_subscription_plans_active (is_active)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_subscriptions
-- ============================================
CREATE TABLE user_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    stripe_subscription_id VARCHAR(255) NULL,
    paddle_subscription_id VARCHAR(255) NULL,
    status ENUM('active','canceled','past_due','unpaid','trialing','expired') DEFAULT 'active',
    current_period_start DATETIME,
    current_period_end DATETIME,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    canceled_at DATETIME NULL,
    trial_ends_at DATETIME NULL,
    payment_method ENUM('card','mobile_money','bank','wallet') DEFAULT 'card',
    metadata JSON,
    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,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
    INDEX idx_user_subscriptions_user (user_id),
    INDEX idx_user_subscriptions_status (status),
    INDEX idx_user_subscriptions_plan (plan_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: subscription_invoices
-- ============================================
CREATE TABLE subscription_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,
    transaction_id INT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency CHAR(3) DEFAULT 'XAF',
    period_start DATETIME,
    period_end DATETIME,
    status ENUM('draft','open','paid','void','uncollectible') DEFAULT 'open',
    pdf_url VARCHAR(500) NULL,
    paid_at DATETIME NULL,
    due_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_subscription_invoices_subscription (subscription_id),
    INDEX idx_subscription_invoices_status (status),
    INDEX idx_subscription_invoices_number (invoice_number)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: api_request_logs
-- ============================================
CREATE TABLE api_request_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    api_key_id INT NULL,
    user_id INT NULL,
    method VARCHAR(10) NOT NULL,
    endpoint VARCHAR(500) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_headers JSON,
    request_body JSON,
    response_code INT,
    response_body TEXT,
    duration_ms INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_api_logs_user (user_id),
    INDEX idx_api_logs_endpoint (endpoint(100)),
    INDEX idx_api_logs_created (created_at),
    INDEX idx_api_logs_response (response_code)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: rate_limit_logs
-- ============================================
CREATE TABLE rate_limit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    api_key_id INT NULL,
    user_id INT NULL,
    ip_address VARCHAR(45),
    endpoint VARCHAR(500),
    hits INT DEFAULT 1,
    minute_window DATETIME,
    blocked_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_rate_limit_ip (ip_address),
    INDEX idx_rate_limit_window (minute_window),
    INDEX idx_rate_limit_user (user_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: dynamic_qr_codes
-- ============================================
CREATE TABLE dynamic_qr_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    code VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(18,2) NULL,
    currency CHAR(3) DEFAULT 'XAF',
    description VARCHAR(255),
    payment_link_id INT NULL,
    is_reusable BOOLEAN DEFAULT FALSE,
    max_uses INT NULL,
    current_uses INT DEFAULT 0,
    expires_at DATETIME NULL,
    status ENUM('active','inactive','expired') DEFAULT 'active',
    image_url VARCHAR(255) 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,
    FOREIGN KEY (payment_link_id) REFERENCES payment_links(id) ON DELETE SET NULL,
    INDEX idx_qr_codes_user (user_id),
    INDEX idx_qr_codes_code (code),
    INDEX idx_qr_codes_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: settings
-- ============================================
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(50) DEFAULT 'general',
    `key` VARCHAR(100) NOT NULL,
    value TEXT,
    type ENUM('string','number','boolean','json','array') DEFAULT 'string',
    description VARCHAR(255),
    is_public BOOLEAN DEFAULT FALSE,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT NULL,
    UNIQUE KEY unique_setting_key (category, `key`),
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_settings_category (category),
    INDEX idx_settings_key (`key`)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: email_logs
-- ============================================
CREATE TABLE email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    type ENUM('payment_success','merchant_notification','system','verification','password_reset','invoice','newsletter') DEFAULT 'system',
    to_email VARCHAR(150) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT,
    template VARCHAR(100) NULL,
    attachments JSON,
    status ENUM('pending','sent','failed','delivered') DEFAULT 'pending',
    error_message TEXT,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_email_logs_user (user_id),
    INDEX idx_email_logs_status (status),
    INDEX idx_email_logs_created (created_at),
    INDEX idx_email_logs_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: merchant_reviews
-- ============================================
CREATE TABLE merchant_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    merchant_id INT NOT NULL,
    user_id INT NULL,
    transaction_id INT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    response TEXT,
    response_by INT NULL,
    response_at DATETIME NULL,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    status ENUM('pending','approved','rejected','flagged') DEFAULT 'pending',
    helpful_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    FOREIGN KEY (response_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reviews_merchant (merchant_id),
    INDEX idx_reviews_user (user_id),
    INDEX idx_reviews_rating (rating),
    INDEX idx_reviews_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: support_tickets
-- ============================================
CREATE TABLE support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    email VARCHAR(150) NULL,
    whatsapp VARCHAR(20) NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
    category ENUM('technical','payment','account','merchant','refund','other') DEFAULT 'other',
    assigned_to INT NULL,
    response TEXT,
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    resolved_at DATETIME 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 SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tickets_user (user_id),
    INDEX idx_tickets_status (status),
    INDEX idx_tickets_priority (priority),
    INDEX idx_tickets_category (category)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: kyc_documents
-- ============================================
CREATE TABLE kyc_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    document_type ENUM('id_card','passport','driver_license','utility_bill','business_registration','tax_certificate') NOT NULL,
    document_number VARCHAR(100),
    document_url VARCHAR(500) NOT NULL,
    front_image VARCHAR(500) NULL,
    back_image VARCHAR(500) NULL,
    selfie_image VARCHAR(500) NULL,
    status ENUM('pending','verified','rejected','expired') DEFAULT 'pending',
    verified_by INT NULL,
    verified_at DATETIME NULL,
    rejection_reason TEXT,
    expiry_date DATE NULL,
    submitted_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,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_kyc_user (user_id),
    INDEX idx_kyc_status (status),
    INDEX idx_kyc_type (document_type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: email_verifications
-- ============================================
CREATE TABLE email_verifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    email VARCHAR(150) NOT NULL,
    type ENUM('signup','password_reset','email_change','two_factor') DEFAULT 'signup',
    status ENUM('pending','verified','expired') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_email_verifications_code (code),
    INDEX idx_email_verifications_user (user_id),
    INDEX idx_email_verifications_expires (expires_at),
    INDEX idx_email_verifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: audit_logs
-- ============================================
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    old_values JSON,
    new_values JSON,
    changes JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_action (action),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: notifications
-- ============================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('payment','withdrawal','system','promotion','security','kyc','transaction','announcement') DEFAULT 'system',
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSON,
    is_read BOOLEAN DEFAULT FALSE,
    read_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user (user_id),
    INDEX idx_notifications_read (is_read),
    INDEX idx_notifications_created (created_at),
    INDEX idx_notifications_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: bank_accounts
-- ============================================
CREATE TABLE bank_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    bank_name VARCHAR(100) NOT NULL,
    bank_code VARCHAR(50),
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    account_type ENUM('checking','savings','business','joint') DEFAULT 'checking',
    currency CHAR(3) DEFAULT 'XAF',
    iban VARCHAR(34) NULL,
    swift_code VARCHAR(11) NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at DATETIME NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    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,
    UNIQUE KEY unique_bank_account (user_id, account_number, bank_name),
    INDEX idx_bank_accounts_user (user_id),
    INDEX idx_bank_accounts_status (status)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: mobile_money_accounts
-- ============================================
CREATE TABLE mobile_money_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    operator ENUM('orange_money','mtn_money','moov_money','express_union','western_union','other') NOT NULL,
    country CHAR(2) DEFAULT 'CM',
    is_primary BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at DATETIME NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    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,
    UNIQUE KEY unique_mobile_account (user_id, phone_number, operator),
    INDEX idx_mobile_accounts_user (user_id),
    INDEX idx_mobile_accounts_operator (operator)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: disputes
-- ============================================
CREATE TABLE disputes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    user_id INT NOT NULL,
    merchant_id INT NOT NULL,
    dispute_reason ENUM('not_received','defective','not_as_described','fraud','duplicate','unauthorized','other') NOT NULL,
    description TEXT NOT NULL,
    evidence JSON,
    status ENUM('open','under_review','resolved','closed','escalated') DEFAULT 'open',
    resolution ENUM('refund','partial_refund','merchant_wins','user_wins','compromise') NULL,
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    refund_amount DECIMAL(18,2) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_disputes_transaction (transaction_id),
    INDEX idx_disputes_user (user_id),
    INDEX idx_disputes_status (status),
    INDEX idx_disputes_merchant (merchant_id)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: api_cache
-- ============================================
CREATE TABLE api_cache (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cache_key VARCHAR(255) UNIQUE NOT NULL,
    data JSON NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_api_cache_key (cache_key),
    INDEX idx_api_cache_expires (expires_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: user_favorites
-- ============================================
CREATE TABLE user_favorites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('merchant','product','service','payment_link') NOT NULL,
    target_id INT NOT NULL,
    metadata JSON,
    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,
    UNIQUE KEY unique_favorite (user_id, type, target_id),
    INDEX idx_user_favorites_user (user_id),
    INDEX idx_user_favorites_type (type)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: sms_logs
-- ============================================
CREATE TABLE sms_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('verification','notification','alert','promotion') DEFAULT 'notification',
    status ENUM('pending','sent','failed','delivered') DEFAULT 'pending',
    provider VARCHAR(50),
    message_id VARCHAR(100),
    error_message TEXT,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_sms_logs_phone (phone_number),
    INDEX idx_sms_logs_status (status),
    INDEX idx_sms_logs_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: security_events
-- ============================================
CREATE TABLE security_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    event_type ENUM('login_success','login_failed','logout','password_change','token_refresh','token_revoked','suspicious_activity') NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    location JSON,
    metadata JSON,
    risk_level ENUM('low','medium','high','critical') DEFAULT 'low',
    is_handled BOOLEAN DEFAULT FALSE,
    handled_at DATETIME NULL,
    handled_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_security_events_user (user_id),
    INDEX idx_security_events_type (event_type),
    INDEX idx_security_events_risk (risk_level),
    INDEX idx_security_events_created (created_at)
) ENGINE=InnoDB;

-- ============================================
-- TABLE: failed_jobs
-- ============================================
CREATE TABLE failed_jobs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_failed_jobs_failed_at (failed_at)
) ENGINE=InnoDB;

-- ============================================
-- TRIGGERS COMPLETS
-- ============================================

-- Trigger 1: Création automatique du wallet et profil à l'inscription
DELIMITER $$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    -- Créer un wallet par défaut
    INSERT INTO wallets (user_id, currency, wallet_number, wallet_name, wallet_type)
    VALUES (NEW.id, 'XAF', CONCAT('WAL', LPAD(NEW.id, 8, '0')), 'Portefeuille principal', 'personal');
    
    -- Créer un profil utilisateur vide
    INSERT INTO user_profiles (user_id)
    VALUES (NEW.id);
    
    -- Enregistrer l'événement de sécurité
    INSERT INTO security_events (user_id, event_type, risk_level, metadata)
    VALUES (NEW.id, 'login_success', 'low', 
            JSON_OBJECT('registration_type', 'manual', 'timestamp', NOW()));
END$$
DELIMITER ;

-- Trigger 2: Mise à jour des soldes après transaction complétée
DELIMITER $$
CREATE TRIGGER after_transaction_completed
AFTER UPDATE ON transactions
FOR EACH ROW
BEGIN
    IF OLD.status != 'completed' AND NEW.status = 'completed' THEN
        -- Transaction de type PAYMENT: créditer le wallet du marchand
        IF NEW.type = 'payment' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Transaction de type TRANSFER: débiter l'expéditeur, créditer le destinataire
        IF NEW.type = 'transfer' THEN
            -- Débiter l'expéditeur
            IF NEW.sender_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance - NEW.amount,
                    available_balance = available_balance - NEW.amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.sender_wallet_id;
            END IF;
            
            -- Créditer le destinataire
            IF NEW.receiver_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance + NEW.net_amount,
                    available_balance = available_balance + NEW.net_amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.receiver_wallet_id;
            END IF;
        END IF;
        
        -- Transaction de type WITHDRAW: débiter le wallet
        IF NEW.type = 'withdraw' AND NEW.sender_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance - NEW.amount,
                available_balance = available_balance - NEW.amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.sender_wallet_id;
        END IF;
        
        -- Transaction de type DEPOSIT: créditer le wallet
        IF NEW.type = 'deposit' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Mettre à jour le compteur de succès du payment link
        IF NEW.link_id IS NOT NULL THEN
            UPDATE payment_links 
            SET success_count = success_count + 1,
                current_uses = current_uses + 1,
                updated_at = NOW()
            WHERE id = NEW.link_id;
        END IF;
        
        -- Mettre à jour le compteur de ventes pour les items
        IF NEW.product_id IS NOT NULL THEN
            UPDATE items 
            SET quantity_sold = quantity_sold + 1,
                updated_at = NOW()
            WHERE id = NEW.product_id;
        END IF;
        
        -- Créer une notification
        INSERT INTO notifications (user_id, type, title, message, data)
        VALUES (NEW.user_id, 'transaction', 
                CONCAT('Transaction ', UPPER(NEW.type), ' complétée'),
                CONCAT('Votre transaction #', NEW.transaction_code, ' de ', NEW.amount, ' ', NEW.currency, ' a été complétée avec succès.'),
                JSON_OBJECT(
                    'transaction_id', NEW.id,
                    'transaction_code', NEW.transaction_code,
                    'amount', NEW.amount,
                    'currency', NEW.currency,
                    'type', NEW.type,
                    'net_amount', NEW.net_amount,
                    'fees', NEW.fees
                ));
    END IF;
END$$
DELIMITER ;

-- Trigger 3: Blacklist automatique après changement de mot de passe
DELIMITER $$
CREATE TRIGGER after_user_password_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.password_hash != NEW.password_hash THEN
        -- Enregistrer l'événement de sécurité
        INSERT INTO security_events (user_id, event_type, risk_level, metadata)
        VALUES (NEW.id, 'password_change', 'medium', 
                JSON_OBJECT('change_type', 'password_update', 'timestamp', NOW()));
        
        -- Révoquer tous les refresh tokens de l'utilisateur
        UPDATE jwt_refresh_tokens 
        SET is_revoked = TRUE, 
            revoked_at = NOW(),
            revoked_reason = 'password_change'
        WHERE user_id = NEW.id 
          AND is_revoked = FALSE;
        
        -- Blacklist les tokens via application (côté Laravel)
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, metadata)
        VALUES (NEW.id, 'user.password_changed', 'users', NEW.id,
                JSON_OBJECT('tokens_revoked', 'all', 'reason', 'password_change'));
    END IF;
END$$
DELIMITER ;

-- Trigger 4: Sécurité après KYC rejeté
DELIMITER $$
CREATE TRIGGER after_kyc_rejected
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.kyc_status != 'rejected' AND NEW.kyc_status = 'rejected' THEN
        -- Enregistrer l'événement de sécurité
        INSERT INTO security_events (user_id, event_type, risk_level, metadata)
        VALUES (NEW.id, 'suspicious_activity', 'high', 
                JSON_OBJECT('reason', 'kyc_rejected', 'timestamp', NOW()));
        
        -- Révoquer tous les tokens de l'utilisateur
        UPDATE jwt_refresh_tokens 
        SET is_revoked = TRUE, 
            revoked_at = NOW(),
            revoked_reason = 'security_breach'
        WHERE user_id = NEW.id 
          AND is_revoked = FALSE;
        
        -- Geler le wallet principal
        UPDATE wallets 
        SET status = 'frozen',
            updated_at = NOW()
        WHERE user_id = NEW.id 
          AND is_primary = TRUE 
          AND status = 'active';
    END IF;
END$$
DELIMITER ;

-- Trigger 5: Audit des modifications utilisateur
DELIMITER $$
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    -- Ne pas enregistrer si seule la date de mise à jour change
    IF NOT (OLD.updated_at != NEW.updated_at AND 
           OLD.nom <=> NEW.nom AND
           OLD.prenom <=> NEW.prenom AND
           OLD.email <=> NEW.email AND
           OLD.phone <=> NEW.phone AND
           OLD.password_hash <=> NEW.password_hash AND
           OLD.role <=> NEW.role AND
           OLD.email_verified <=> NEW.email_verified AND
           OLD.kyc_status <=> NEW.kyc_status AND
           OLD.is_active <=> NEW.is_active) THEN
        
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values, new_values, changes, ip_address)
        VALUES (
            NEW.id,
            'user.updated',
            'users',
            NEW.id,
            JSON_OBJECT(
                'email', OLD.email,
                'phone', OLD.phone,
                'role', OLD.role,
                'kyc_status', OLD.kyc_status,
                'is_active', OLD.is_active,
                'two_factor_enabled', OLD.two_factor_enabled
            ),
            JSON_OBJECT(
                'email', NEW.email,
                'phone', NEW.phone,
                'role', NEW.role,
                'kyc_status', NEW.kyc_status,
                'is_active', NEW.is_active,
                'two_factor_enabled', NEW.two_factor_enabled
            ),
            JSON_OBJECT(
                'email_changed', IF(OLD.email != NEW.email, TRUE, FALSE),
                'phone_changed', IF(OLD.phone != NEW.phone, TRUE, FALSE),
                'role_changed', IF(OLD.role != NEW.role, TRUE, FALSE),
                'kyc_status_changed', IF(OLD.kyc_status != NEW.kyc_status, TRUE, FALSE),
                'active_status_changed', IF(OLD.is_active != NEW.is_active, TRUE, FALSE),
                '2fa_changed', IF(OLD.two_factor_enabled != NEW.two_factor_enabled, TRUE, FALSE)
            ),
            NULL  -- IP address sera rempli par l'application
        );
    END IF;
END$$
DELIMITER ;

-- Trigger 6: Mise à jour du stock après vente
DELIMITER $$
CREATE TRIGGER after_item_sold
AFTER UPDATE ON items
FOR EACH ROW
BEGIN
    IF NEW.quantity_sold > OLD.quantity_sold THEN
        -- Vérifier le stock bas
        IF NEW.quantity_available IS NOT NULL AND NEW.quantity_available <= NEW.low_stock_threshold THEN
            -- Créer une notification pour le marchand
            INSERT INTO notifications (user_id, type, title, message, data)
            SELECT p.user_id, 'alert', 'Stock faible',
                   CONCAT('Le produit "', NEW.title, '" est en stock faible (', NEW.quantity_available, ' restants)'),
                   JSON_OBJECT('product_id', NEW.id, 'product_name', NEW.title, 
                               'current_stock', NEW.quantity_available, 'low_threshold', NEW.low_stock_threshold)
            FROM payment_links p
            WHERE p.id = NEW.payment_link_id;
        END IF;
    END IF;
END$$
DELIMITER ;

-- Trigger 7: Nettoyage automatique des sessions expirées
DELIMITER $$
CREATE TRIGGER cleanup_expired_sessions
BEFORE INSERT ON user_sessions
FOR EACH ROW
BEGIN
    -- Supprimer les sessions expirées pour cet utilisateur
    DELETE FROM user_sessions 
    WHERE user_id = NEW.user_id 
      AND expires_at < NOW();
END$$
DELIMITER ;

-- ============================================
-- DONNÉES INITIALES
-- ============================================

-- Insérer les providers OAuth
INSERT INTO oauth_providers (name, client_id, client_secret, redirect_url, scopes, icon, is_active) VALUES
('google', 'YOUR_GOOGLE_CLIENT_ID', 'YOUR_GOOGLE_CLIENT_SECRET', 'http://localhost:8000/auth/google/callback', 
 '["openid", "profile", "email"]', 'google', TRUE),
('github', 'YOUR_GITHUB_CLIENT_ID', 'YOUR_GITHUB_CLIENT_SECRET', 'http://localhost:8000/auth/github/callback',
 '["read:user", "user:email"]', 'github', TRUE),
('facebook', 'YOUR_FACEBOOK_CLIENT_ID', 'YOUR_FACEBOOK_CLIENT_SECRET', 'http://localhost:8000/auth/facebook/callback',
 '["email", "public_profile"]', 'facebook', TRUE);

-- Insérer les plans d'abonnement
INSERT INTO subscription_plans (name, slug, description, price, `interval`, trial_days, features, is_active, is_popular, max_transactions_per_month, fee_percentage) VALUES
('Gratuit', 'free', 'Plan gratuit pour les petits commerçants', 0.00, 'monthly', 14, 
 '["Jusqu\\''à 10 transactions/mois", "Frais à 2.5%", "Support par email", "1 utilisateur"]', TRUE, FALSE, 10, 2.5),
('Pro', 'pro', 'Pour les commerçants en croissance', 5000.00, 'monthly', 7,
 '["Transactions illimitées", "Frais à 1.5%", "Support prioritaire", "API avancée", "5 utilisateurs", "Statistiques détaillées"]', TRUE, TRUE, NULL, 1.5),
('Business', 'business', 'Pour les entreprises', 15000.00, 'monthly', 0,
 '["Toutes fonctionnalités Pro", "Frais à 0.5%", "Support dédié", "Dashboard avancé", "Utilisateurs illimités", "Intégrations personnalisées"]', TRUE, FALSE, NULL, 0.5);

-- Insérer les frais par défaut
INSERT INTO fees (type, method, value_type, value, min_amount, min_fee, max_fee, currency, applies_to, description) VALUES
('transaction', 'mobile_money', 'percentage', 1.5, 100, 50, 500, 'XAF', 'sender', 'Frais de transaction mobile money'),
('transaction', 'bank', 'percentage', 2.0, 1000, 100, 2000, 'XAF', 'sender', 'Frais de transaction bancaire'),
('transaction', 'card', 'percentage', 2.5, 100, 100, 2500, 'XAF', 'sender', 'Frais de transaction par carte'),
('withdraw', 'mobile_money', 'fixed', 100, 100, 100, 100, 'XAF', 'sender', 'Frais de retrait mobile money'),
('withdraw', 'bank', 'fixed', 500, 5000, 500, 500, 'XAF', 'sender', 'Frais de retrait bancaire'),
('transfer', 'wallet', 'percentage', 0.5, 10, 10, 500, 'XAF', 'sender', 'Frais de transfert entre wallets');

-- Insérer les taux de change
INSERT INTO exchange_rates (from_currency, to_currency, rate, inverse_rate, source, valid_from, is_active) VALUES
('XAF', 'EUR', 0.001524, 656.33, 'manual', NOW(), TRUE),
('XAF', 'USD', 0.001667, 600.00, 'manual', NOW(), TRUE),
('EUR', 'XAF', 656.33, 0.001524, 'manual', NOW(), TRUE),
('USD', 'XAF', 600.00, 0.001667, 'manual', NOW(), TRUE),
('XAF', 'XOF', 1.000, 1.000, 'manual', NOW(), TRUE);

-- Insérer les événements webhook
INSERT INTO webhook_events (event_name, description) VALUES
('payment.completed', 'Paiement complété avec succès'),
('payment.failed', 'Paiement échoué'),
('payment.pending', 'Paiement en attente'),
('withdrawal.completed', 'Retrait complété'),
('withdrawal.failed', 'Retrait échoué'),
('transfer.completed', 'Transfert complété'),
('invoice.paid', 'Facture payée'),
('invoice.overdue', 'Facture en retard'),
('merchant.payout', 'Paiement au marchand'),
('kyc.verified', 'KYC vérifié'),
('user.registered', 'Nouvel utilisateur inscrit'),
('subscription.created', 'Nouvel abonnement'),
('subscription.cancelled', 'Abonnement annulé'),
('refund.processed', 'Remboursement traité'),
('dispute.opened', 'Litige ouvert'),
('api_key.created', 'Nouvelle clé API créée'),
('api_key.revoked', 'Clé API révoquée'),
('jwt.token_revoked', 'Token JWT révoqué'),
('user.password_changed', 'Mot de passe utilisateur changé'),
('security.alert', 'Alerte de sécurité détectée');

-- Insérer les paramètres par défaut
INSERT INTO settings (category, `key`, value, type, description, is_public) VALUES
('general', 'site_name', 'MonAgregateurPaiement', 'string', 'Nom du site', TRUE),
('general', 'site_url', 'https://votresite.com', 'string', 'URL du site', TRUE),
('general', 'site_currency', 'XAF', 'string', 'Devise principale', TRUE),
('general', 'country', 'CM', 'string', 'Pays par défaut', TRUE),
('general', 'timezone', 'Africa/Douala', 'string', 'Fuseau horaire', TRUE),
('general', 'language', 'fr', 'string', 'Langue par défaut', TRUE),
('payment', 'min_payment_amount', '100', 'number', 'Montant minimum de paiement', TRUE),
('payment', 'max_payment_amount', '10000000', 'number', 'Montant maximum de paiement', TRUE),
('payment', 'default_payment_method', 'mobile_money', 'string', 'Méthode de paiement par défaut', TRUE),
('security', 'max_login_attempts', '5', 'number', 'Nombre maximum de tentatives de connexion', FALSE),
('security', 'session_timeout', '30', 'number', 'Timeout session en minutes', FALSE),
('security', 'password_min_length', '8', 'number', 'Longueur minimale du mot de passe', FALSE),
('jwt', 'access_token_ttl', '3600', 'number', 'Durée de vie du token d''accès en secondes', FALSE),
('jwt', 'refresh_token_ttl', '86400', 'number', 'Durée de vie du token de rafraîchissement', FALSE),
('jwt', 'algorithm', 'HS256', 'string', 'Algorithme de signature JWT', FALSE),
('jwt', 'blacklist_enabled', 'true', 'boolean', 'Activer la blacklist JWT', FALSE),
('jwt', 'blacklist_grace_period', '30', 'number', 'Période de grâce blacklist en secondes', FALSE),
('oauth', 'allow_registration', 'true', 'boolean', 'Autoriser l''inscription via OAuth', TRUE),
('oauth', 'auto_verify_email', 'true', 'boolean', 'Vérification automatique email via OAuth', FALSE),
('email', 'smtp_host', 'smtp.gmail.com', 'string', 'Serveur SMTP', FALSE),
('email', 'smtp_port', '587', 'number', 'Port SMTP', FALSE),
('email', 'smtp_encryption', 'tls', 'string', 'Encryption SMTP', FALSE),
('email', 'support_email', 'support@votresite.com', 'string', 'Email du support', TRUE),
('email', 'noreply_email', 'noreply@votresite.com', 'string', 'Email d''envoi', FALSE),
('sms', 'default_provider', 'twilio', 'string', 'Fournisseur SMS par défaut', FALSE),
('sms', 'verification_enabled', 'true', 'boolean', 'Activer les SMS de vérification', TRUE),
('notifications', 'push_enabled', 'true', 'boolean', 'Activer les notifications push', TRUE),
('notifications', 'email_enabled', 'true', 'boolean', 'Activer les notifications email', TRUE),
('notifications', 'sms_enabled', 'true', 'boolean', 'Activer les notifications SMS', TRUE),
('kyc', 'required_for_merchants', 'true', 'boolean', 'KYC requis pour les marchands', TRUE),
('kyc', 'required_for_withdrawals', 'true', 'boolean', 'KYC requis pour les retraits', TRUE),
('kyc', 'required_amount_threshold', '100000', 'number', 'Seuil KYC pour montant cumulé', TRUE),
('api', 'rate_limit_default', '60', 'number', 'Limite de requêtes par minute par défaut', FALSE),
('api', 'webhook_timeout', '30', 'number', 'Timeout webhook en secondes', FALSE),
('api', 'webhook_retry_attempts', '3', 'number', 'Nombre de tentatives de webhook', FALSE),
('security', 'enable_two_factor', 'false', 'boolean', 'Activer l''authentification à deux facteurs', TRUE),
('security', 'max_sessions_per_user', '5', 'number', 'Nombre maximum de sessions simultanées', FALSE),
('security', 'ip_whitelist_enabled', 'false', 'boolean', 'Activer la whitelist d''IP pour admin', FALSE);

-- Insérer une clé JWT initiale (à changer en production)
INSERT INTO jwt_key_rotation (key_id, key_type, algorithm, secret_key, is_active, activated_at) VALUES
('default_key_1', 'current', 'HS256', 'your-super-secret-jwt-key-change-in-production', TRUE, NOW());

-- ============================================
-- VUES
-- ============================================

-- Vue pour les statistiques des marchands
CREATE VIEW merchant_stats AS
SELECT 
    u.id as merchant_id,
    u.nom,
    u.prenom,
    u.email,
    COUNT(DISTINCT t.id) as total_transactions,
    SUM(CASE WHEN t.status = 'completed' THEN t.amount ELSE 0 END) as total_revenue,
    SUM(CASE WHEN t.status = 'completed' THEN t.fees ELSE 0 END) as total_fees,
    COUNT(DISTINCT p.id) as total_products,
    AVG(r.rating) as average_rating,
    COUNT(DISTINCT r.id) as total_reviews
FROM users u
LEFT JOIN transactions t ON u.id = t.merchant_id
LEFT JOIN merchant_products p ON u.id = p.merchant_id
LEFT JOIN merchant_reviews r ON u.id = r.merchant_id AND r.status = 'approved'
WHERE u.role = 'merchant' AND u.is_active = TRUE
GROUP BY u.id;

-- Vue pour les rapports journaliers
CREATE VIEW daily_reports AS
SELECT 
    DATE(created_at) as report_date,
    COUNT(*) as total_transactions,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as total_amount,
    SUM(CASE WHEN status = 'completed' THEN fees ELSE 0 END) as total_fees,
    COUNT(DISTINCT user_id) as unique_users,
    SUM(CASE WHEN type = 'payment' THEN 1 ELSE 0 END) as payment_count,
    SUM(CASE WHEN type = 'withdraw' THEN 1 ELSE 0 END) as withdraw_count,
    SUM(CASE WHEN type = 'transfer' THEN 1 ELSE 0 END) as transfer_count,
    SUM(CASE WHEN type = 'refund' THEN 1 ELSE 0 END) as refund_count
FROM transactions
GROUP BY DATE(created_at);

-- Vue pour les soldes des utilisateurs
CREATE VIEW user_balances AS
SELECT 
    u.id as user_id,
    u.email,
    u.phone,
    u.role,
    w.currency,
    SUM(w.balance) as total_balance,
    SUM(w.available_balance) as available_balance,
    SUM(w.hold_balance) as hold_balance,
    COUNT(w.id) as wallet_count
FROM users u
JOIN wallets w ON u.id = w.user_id
WHERE w.status = 'active'
GROUP BY u.id, w.currency;

-- Vue pour les paiements en attente de webhook
CREATE VIEW pending_webhooks AS
SELECT 
    wl.id as webhook_id,
    wl.url,
    wl.events,
    wl.secret_key,
    COUNT(w.id) as pending_count
FROM webhook_endpoints wl
LEFT JOIN webhook_logs w ON wl.id = w.webhook_endpoint_id AND w.status = 'pending'
WHERE wl.is_active = TRUE
GROUP BY wl.id;

-- Vue pour monitoring des sessions actives
CREATE VIEW active_sessions_monitor AS
SELECT 
    u.id as user_id,
    u.email,
    u.role,
    COUNT(DISTINCT s.id) as active_sessions,
    COUNT(DISTINCT rt.id) as active_refresh_tokens,
    MAX(s.last_activity) as last_activity,
    GROUP_CONCAT(DISTINCT s.ip_address) as ips,
    GROUP_CONCAT(DISTINCT LEFT(s.user_agent, 50)) as devices
FROM users u
LEFT JOIN user_sessions s ON u.id = s.user_id AND s.is_active = TRUE AND s.expires_at > NOW()
LEFT JOIN jwt_refresh_tokens rt ON u.id = rt.user_id AND rt.is_revoked = FALSE AND rt.expires_at > NOW()
GROUP BY u.id
HAVING active_sessions > 0 OR active_refresh_tokens > 0;

-- Vue pour détection d'anomalies de sécurité
CREATE VIEW security_anomalies AS
SELECT 
    se.*,
    u.email,
    u.phone,
    TIMESTAMPDIFF(MINUTE, se.created_at, NOW()) as minutes_ago,
    CASE 
        WHEN se.event_type = 'login_failed' AND se.risk_level = 'high' THEN 'Bloquer IP'
        WHEN se.event_type = 'suspicious_activity' THEN 'Vérifier manuellement'
        ELSE 'Surveiller'
    END as recommended_action
FROM security_events se
LEFT JOIN users u ON se.user_id = u.id
WHERE se.is_handled = FALSE
ORDER BY se.risk_level DESC, se.created_at DESC;

-- ============================================
-- FONCTIONS UTILITAIRES
-- ============================================

-- Fonction pour générer un numéro de transaction unique
DELIMITER $$
CREATE FUNCTION generate_transaction_code() RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE new_code VARCHAR(50);
    DECLARE counter INT DEFAULT 1;
    
    WHILE counter <= 10 DO
        SET new_code = CONCAT('TXN', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
        
        IF NOT EXISTS (SELECT 1 FROM transactions WHERE transaction_code = new_code) THEN
            RETURN new_code;
        END IF;
        
        SET counter = counter + 1;
    END WHILE;
    
    -- Fallback: timestamp + random
    RETURN CONCAT('TXN', UNIX_TIMESTAMP(), LPAD(FLOOR(RAND() * 1000), 3, '0'));
END$$
DELIMITER ;

-- Fonction pour vérifier si un token est blacklisté
DELIMITER $$
CREATE FUNCTION is_token_blacklisted(token_hash VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE token_count INT;
    
    SELECT COUNT(*) INTO token_count
    FROM jwt_blacklist 
    WHERE token_hash = token_hash 
      AND expires_at > NOW();
    
    RETURN token_count > 0;
END$$
DELIMITER ;

-- ============================================
-- ÉVÉNEMENTS MYSQL (nettoyage automatique)
-- ============================================

-- Événement pour nettoyer les données expirées quotidiennement
DELIMITER $$
CREATE EVENT IF NOT EXISTS daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '23:59:59')
DO
BEGIN
    -- Nettoyer les tokens expirés
    DELETE FROM jwt_refresh_tokens WHERE expires_at < NOW();
    DELETE FROM password_resets WHERE expires_at < NOW();
    DELETE FROM email_verifications WHERE expires_at < NOW();
    DELETE FROM user_sessions WHERE expires_at < NOW();
    
    -- Nettoyer la blacklist JWT expirée
    DELETE FROM jwt_blacklist WHERE expires_at < NOW();
    
    -- Nettoyer le cache API expiré
    DELETE FROM api_cache WHERE expires_at < NOW();
    
    -- Désactiver les liens de paiement expirés
    UPDATE payment_links 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Désactiver les QR codes expirés
    UPDATE dynamic_qr_codes 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Nettoyer les notifications expirées
    DELETE FROM notifications WHERE expires_at < NOW();
END$$
DELIMITER ;

-- Événement pour réinitialiser les compteurs d'API quotidiennement
DELIMITER $$
CREATE EVENT IF NOT EXISTS reset_api_counters
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '00:00:00')
DO
BEGIN
    UPDATE api_keys 
    SET calls_today = 0, 
        last_reset_date = CURDATE() 
    WHERE last_reset_date < CURDATE() OR last_reset_date IS NULL;
END$$
DELIMITER ;

-- ============================================
-- FIN - MESSAGE DE CONFIRMATION
-- ============================================

-- Message de confirmation
SELECT '✅ BASE DE DONNÉES CRÉÉE AVEC SUCCÈS!' as message;
SELECT CONCAT('📊 Nombre de tables créées: ', COUNT(*)) as table_count 
FROM information_schema.tables 
WHERE table_schema = 'p';

SELECT CONCAT('🔑 JWT Configuration: ', 'Tables refresh_tokens, blacklist et key_rotation créées') as jwt_status;
SELECT CONCAT('🔐 Sécurité: ', '7 triggers de sécurité implémentés') as security_status;
SELECT CONCAT('🌐 API: ', 'Webhooks, rate limiting et logs API configurés') as api_status;
SELECT CONCAT('💳 Paiements: ', 'Transactions, wallets, commissions et frais optimisés') as payment_status;
SELECT CONCAT('📱 Mobile: ', 'Sessions multi-devices et notifications push prêtes') as mobile_status;
SELECT CONCAT('⚡ Triggers: ', '7 triggers automatiques configurés') as triggers_status;

-- Vérification des tables
SHOW TABLES;

-- Afficher la liste des triggers créés
SHOW TRIGGERS;