-- ============================================================
-- REZOPAY DATABASE SCHEMA
-- Core PHP + MySQL | No ORM | cPanel Shared Hosting Compatible
-- Charset: utf8mb4 | Engine: InnoDB (transaction-safe)
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- 1. USERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_number VARCHAR(15) NOT NULL UNIQUE,
    name VARCHAR(150) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    referral_code VARCHAR(20) NOT NULL UNIQUE,
    referred_by BIGINT UNSIGNED DEFAULT NULL,
    dark_mode TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','blocked','deleted') NOT NULL DEFAULT 'active',
    device_id VARCHAR(255) DEFAULT NULL,
    fcm_token VARCHAR(500) DEFAULT NULL,
    last_login_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_mobile (mobile_number),
    INDEX idx_users_referral_code (referral_code),
    INDEX idx_users_referred_by (referred_by),
    INDEX idx_users_status (status),
    CONSTRAINT fk_users_referred_by FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. OTP_VERIFICATIONS (user login/register OTP)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS otp_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile_number VARCHAR(15) NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    purpose ENUM('login','register','delete_account') NOT NULL DEFAULT 'login',
    attempt_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_otp_mobile (mobile_number),
    INDEX idx_otp_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. ADMIN_USERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) DEFAULT NULL,
    role ENUM('super_admin','admin','support') NOT NULL DEFAULT 'admin',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    failed_login_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME DEFAULT NULL,
    last_login_at DATETIME DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_admin_username (username),
    INDEX idx_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. ADMIN_2FA
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_2fa (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    purpose ENUM('login','sensitive_action') NOT NULL DEFAULT 'login',
    action_context VARCHAR(100) DEFAULT NULL COMMENT 'e.g. commission_change, api_credential_change',
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    attempt_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_2fa_admin (admin_id),
    INDEX idx_2fa_expires (expires_at),
    CONSTRAINT fk_2fa_admin FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. ADMIN_ACTIVITY_LOGS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(150) NOT NULL,
    module VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    old_value TEXT DEFAULT NULL,
    new_value TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_admin (admin_id),
    INDEX idx_activity_module (module),
    INDEX idx_activity_created (created_at),
    CONSTRAINT fk_activity_admin FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. SERVICE_CATEGORIES (Recharge / BBPS top-level categories)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS service_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    type ENUM('recharge','bbps','other') NOT NULL DEFAULT 'bbps',
    icon VARCHAR(255) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_cat_slug (slug),
    INDEX idx_cat_type (type),
    INDEX idx_cat_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. SERVICES (individual service ON/OFF, e.g. Mobile Prepaid, Electricity)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS services (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL,
    icon VARCHAR(255) DEFAULT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_services_category (category_id),
    INDEX idx_services_enabled (is_enabled),
    CONSTRAINT fk_services_category FOREIGN KEY (category_id) REFERENCES service_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. OPERATORS (telecom/DTH operators for recharge)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS operators (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL COMMENT 'Operator code used by recharge API provider',
    logo VARCHAR(255) DEFAULT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_operators_service (service_id),
    INDEX idx_operators_code (code),
    CONSTRAINT fk_operators_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. BILLERS (BBPS billers e.g. specific electricity boards)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS billers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id INT UNSIGNED NOT NULL,
    biller_name VARCHAR(150) NOT NULL,
    biller_code VARCHAR(50) NOT NULL COMMENT 'BBPS biller ID from provider',
    state VARCHAR(100) DEFAULT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    fetch_bill_supported TINYINT(1) NOT NULL DEFAULT 0,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_billers_service (service_id),
    INDEX idx_billers_code (biller_code),
    CONSTRAINT fk_billers_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. COMMISSION_SETTINGS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS commission_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id INT UNSIGNED NOT NULL,
    operator_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = applies to all operators under this service',
    commission_type ENUM('percentage','flat') NOT NULL DEFAULT 'percentage',
    commission_value DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    min_amount DECIMAL(10,2) DEFAULT NULL COMMENT 'cap: minimum commission amount',
    max_amount DECIMAL(10,2) DEFAULT NULL COMMENT 'cap: maximum commission amount',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED DEFAULT NULL,
    INDEX idx_commission_service (service_id),
    INDEX idx_commission_operator (operator_id),
    CONSTRAINT fk_commission_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_operator FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE,
    CONSTRAINT fk_commission_updated_by FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. REFERRAL_SETTINGS (single-row config table)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referral_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    reward_per_referral DECIMAL(10,2) NOT NULL DEFAULT 10.00,
    min_transactions_for_eligibility INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'referred user must complete N successful txns for referrer to earn reward',
    max_discount_per_transaction DECIMAL(10,2) DEFAULT NULL COMMENT 'cap on how much referral discount can be applied in one transaction',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED DEFAULT NULL,
    CONSTRAINT fk_referral_settings_updated_by FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. REFERRALS (referrer -> referred user mapping)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referrals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referrer_user_id BIGINT UNSIGNED NOT NULL,
    referred_user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','eligible','rewarded') NOT NULL DEFAULT 'pending',
    eligible_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_referred_user (referred_user_id),
    INDEX idx_referrals_referrer (referrer_user_id),
    INDEX idx_referrals_status (status),
    CONSTRAINT fk_referrals_referrer FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_referrals_referred FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. TRANSACTIONS (master transaction table - recharge + bbps + giftcard)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    txn_ref VARCHAR(40) NOT NULL UNIQUE COMMENT 'Internal unique transaction reference / idempotency key',
    user_id BIGINT UNSIGNED NOT NULL,
    txn_type ENUM('recharge','bbps','gift_card') NOT NULL,
    service_id INT UNSIGNED NOT NULL,
    operator_id INT UNSIGNED DEFAULT NULL,
    biller_id INT UNSIGNED DEFAULT NULL,
    original_amount DECIMAL(10,2) NOT NULL,
    commission_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    referral_discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    final_payable_amount DECIMAL(10,2) NOT NULL,
    api_cost DECIMAL(10,2) DEFAULT NULL,
    profit_amount DECIMAL(10,2) DEFAULT NULL,
    status ENUM('PENDING','PAYMENT_VERIFIED','API_PROCESSING','SUCCESS','FAILED','API_PENDING','API_FAILED','REFUND_REQUIRED','REFUNDED') NOT NULL DEFAULT 'PENDING',
    customer_number VARCHAR(50) DEFAULT NULL COMMENT 'mobile/consumer number being recharged/paid',
    idempotency_key VARCHAR(64) NOT NULL UNIQUE,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_txn_user (user_id),
    INDEX idx_txn_status (status),
    INDEX idx_txn_type (txn_type),
    INDEX idx_txn_ref (txn_ref),
    INDEX idx_txn_created (created_at),
    CONSTRAINT fk_txn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_txn_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 14. RECHARGE_TRANSACTIONS (recharge-specific detail)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS recharge_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL UNIQUE,
    operator_id INT UNSIGNED NOT NULL,
    mobile_number VARCHAR(15) NOT NULL,
    plan_id VARCHAR(50) DEFAULT NULL,
    plan_details TEXT DEFAULT NULL,
    api_transaction_id VARCHAR(100) DEFAULT NULL,
    api_status VARCHAR(50) DEFAULT NULL,
    api_response TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_recharge_mobile (mobile_number),
    INDEX idx_recharge_api_txn (api_transaction_id),
    CONSTRAINT fk_recharge_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    CONSTRAINT fk_recharge_operator FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 15. BBPS_TRANSACTIONS (bill payment specific detail)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bbps_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL UNIQUE,
    biller_id INT UNSIGNED NOT NULL,
    consumer_number VARCHAR(100) NOT NULL,
    consumer_name VARCHAR(150) DEFAULT NULL,
    bill_amount DECIMAL(10,2) DEFAULT NULL,
    bill_due_date DATE DEFAULT NULL,
    api_transaction_id VARCHAR(100) DEFAULT NULL,
    api_status VARCHAR(50) DEFAULT NULL,
    api_response TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_bbps_consumer (consumer_number),
    INDEX idx_bbps_api_txn (api_transaction_id),
    CONSTRAINT fk_bbps_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    CONSTRAINT fk_bbps_biller FOREIGN KEY (biller_id) REFERENCES billers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. PAYMENTS (UPI payment attempts per transaction)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL,
    upi_app VARCHAR(50) DEFAULT NULL,
    upi_txn_ref VARCHAR(100) DEFAULT NULL COMMENT 'reference returned by UPI app / PSP',
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('INITIATED','SUCCESS','FAILED','PENDING','TIMEOUT') NOT NULL DEFAULT 'INITIATED',
    verified_at DATETIME DEFAULT NULL,
    verification_method ENUM('webhook','manual','status_check') DEFAULT NULL,
    gateway_response TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payments_txn (transaction_id),
    INDEX idx_payments_status (status),
    INDEX idx_payments_upi_ref (upi_txn_ref),
    CONSTRAINT fk_payments_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. COMMISSIONS (ledger entry per transaction - for reporting)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL UNIQUE,
    commission_setting_id INT UNSIGNED DEFAULT NULL,
    commission_type ENUM('percentage','flat') NOT NULL,
    commission_rate DECIMAL(10,4) NOT NULL,
    commission_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_commissions_txn (transaction_id),
    CONSTRAINT fk_commissions_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    CONSTRAINT fk_commissions_setting FOREIGN KEY (commission_setting_id) REFERENCES commission_settings(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18. REFERRAL_DISCOUNTS (ledger entry per transaction)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referral_discounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    referral_count_used INT UNSIGNED NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ref_discount_txn (transaction_id),
    INDEX idx_ref_discount_user (user_id),
    CONSTRAINT fk_ref_discount_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    CONSTRAINT fk_ref_discount_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 19. REFUNDS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS refunds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    status ENUM('pending','processed','failed') NOT NULL DEFAULT 'pending',
    processed_by INT UNSIGNED DEFAULT NULL,
    processed_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_refunds_txn (transaction_id),
    INDEX idx_refunds_status (status),
    CONSTRAINT fk_refunds_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    CONSTRAINT fk_refunds_admin FOREIGN KEY (processed_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 20. GIFT_CARDS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gift_cards (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    brand_name VARCHAR(150) NOT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    denominations VARCHAR(255) DEFAULT NULL COMMENT 'comma separated available denominations',
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_giftcards_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 21. NOTIFICATIONS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL = broadcast to all users',
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    type ENUM('recharge_success','recharge_failed','bbps_success','bbps_failed','commission_update','referral_update','announcement','app_update') NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    sent_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notif_user (user_id),
    INDEX idx_notif_type (type),
    CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 22. BANNERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS banners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) DEFAULT NULL,
    image VARCHAR(255) NOT NULL,
    link_type ENUM('none','service','url') NOT NULL DEFAULT 'none',
    link_value VARCHAR(255) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_banners_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 23. SUPPORT_TICKETS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS support_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_no VARCHAR(30) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    subject VARCHAR(200) NOT NULL,
    transaction_id BIGINT UNSIGNED DEFAULT NULL,
    status ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    priority ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
    assigned_to INT UNSIGNED DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tickets_user (user_id),
    INDEX idx_tickets_status (status),
    CONSTRAINT fk_tickets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_tickets_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    CONSTRAINT fk_tickets_admin FOREIGN KEY (assigned_to) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 24. SUPPORT_MESSAGES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS support_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    sender_type ENUM('user','admin') NOT NULL,
    sender_id BIGINT UNSIGNED NOT NULL,
    message TEXT NOT NULL,
    attachment VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_support_msg_ticket (ticket_id),
    CONSTRAINT fk_support_msg_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 25. API_SETTINGS (recharge/bbps/giftcard/payment provider credentials)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(100) NOT NULL,
    api_type ENUM('recharge','bbps','gift_card','payment_verification') NOT NULL,
    api_url VARCHAR(255) DEFAULT NULL,
    api_key_encrypted TEXT DEFAULT NULL,
    api_username VARCHAR(150) DEFAULT NULL,
    api_password_encrypted TEXT DEFAULT NULL,
    current_balance DECIMAL(12,2) DEFAULT NULL,
    low_balance_threshold DECIMAL(12,2) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    last_checked_at DATETIME DEFAULT NULL,

    -- ---- Generic HTTP provider configuration (no-code integration) ----
    -- Lets any REST-style provider be wired entirely from the Admin Panel:
    -- request_url_template/request_body_template support placeholders like
    -- {mobile_number}, {operator_code}, {biller_code}, {consumer_number},
    -- {amount}, {txn_ref}, {api_key}, {api_username}, {api_password} which
    -- get substituted with real values at request time. response_*_path
    -- fields use dot notation into the JSON response, e.g. "data.status".
    request_method ENUM('GET','POST') NOT NULL DEFAULT 'POST',
    request_content_type ENUM('json','form') NOT NULL DEFAULT 'json',
    request_url_template VARCHAR(500) DEFAULT NULL,
    request_body_template TEXT DEFAULT NULL,
    auth_type ENUM('none','header','query','basic') NOT NULL DEFAULT 'header',
    auth_param_name VARCHAR(100) DEFAULT NULL COMMENT 'header name if auth_type=header, query param name if auth_type=query',
    response_success_path VARCHAR(150) DEFAULT NULL COMMENT 'dot-path to the status field, e.g. status or data.status',
    response_success_value VARCHAR(100) DEFAULT NULL COMMENT 'value that means success, e.g. SUCCESS or 1',
    response_txn_id_path VARCHAR(150) DEFAULT NULL COMMENT 'dot-path to the providers own transaction id',
    response_message_path VARCHAR(150) DEFAULT NULL COMMENT 'dot-path to a human-readable status message',
    response_bill_amount_path VARCHAR(150) DEFAULT NULL COMMENT 'BBPS fetch-bill only: dot-path to the bill amount',
    response_consumer_name_path VARCHAR(150) DEFAULT NULL COMMENT 'BBPS fetch-bill only: dot-path to the consumer name',

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED DEFAULT NULL,
    INDEX idx_api_settings_type (api_type),
    CONSTRAINT fk_api_settings_admin FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 26. API_LOGS (request/response logs for all external API calls)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_setting_id INT UNSIGNED DEFAULT NULL,
    transaction_id BIGINT UNSIGNED DEFAULT NULL,
    request_url VARCHAR(255) DEFAULT NULL,
    request_payload TEXT DEFAULT NULL,
    response_payload TEXT DEFAULT NULL,
    http_status INT DEFAULT NULL,
    status ENUM('success','failed','timeout') NOT NULL DEFAULT 'failed',
    duration_ms INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_api_logs_setting (api_setting_id),
    INDEX idx_api_logs_txn (transaction_id),
    INDEX idx_api_logs_created (created_at),
    CONSTRAINT fk_api_logs_setting FOREIGN KEY (api_setting_id) REFERENCES api_settings(id) ON DELETE SET NULL,
    CONSTRAINT fk_api_logs_txn FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 27. APP_SETTINGS (key-value store for maintenance mode, min version, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT DEFAULT NULL,
    setting_type ENUM('string','boolean','integer','json') NOT NULL DEFAULT 'string',
    description VARCHAR(255) DEFAULT NULL,
    is_sensitive TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = requires admin 2FA to change',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED DEFAULT NULL,
    INDEX idx_app_settings_key (setting_key),
    CONSTRAINT fk_app_settings_admin FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 28. DOMAIN_CONFIGURATIONS (dynamic API domain / bootstrap config)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS domain_configurations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_base_url VARCHAR(255) NOT NULL,
    api_version VARCHAR(20) NOT NULL DEFAULT 'v1',
    maintenance_mode TINYINT(1) NOT NULL DEFAULT 0,
    maintenance_message VARCHAR(255) DEFAULT NULL,
    minimum_app_version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
    force_update TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'only one row should be active at a time',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED DEFAULT NULL,
    INDEX idx_domain_config_active (is_active),
    CONSTRAINT fk_domain_config_admin FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DATA (minimum required for admin panel to boot)
-- ============================================================

-- Default super admin (username: admin / password set via install script, NOT hardcoded here)
-- Default referral settings row
INSERT INTO referral_settings (is_enabled, reward_per_referral, min_transactions_for_eligibility, max_discount_per_transaction)
VALUES (1, 10.00, 1, NULL);

-- Default domain configuration
INSERT INTO domain_configurations (api_base_url, api_version, maintenance_mode, minimum_app_version, force_update, is_active)
VALUES ('https://recharge.wbtopper.in/api/', 'v1', 0, '1.0.0', 0, 1);

-- Default app settings
INSERT INTO app_settings (setting_key, setting_value, setting_type, description, is_sensitive) VALUES
('site_name', 'RezoPay', 'string', 'Application display name', 0),
('support_email', 'support@rezopay.com', 'string', 'Support contact email', 0),
('support_phone', '', 'string', 'Support contact phone', 0),
('wallet_system_enabled', '0', 'boolean', 'Must always remain 0 - no wallet system', 1),
('gift_card_module_enabled', '0', 'boolean', 'Gift card module master switch', 1);

-- Default service categories
INSERT INTO service_categories (name, slug, type, sort_order, status) VALUES
('Mobile Prepaid', 'mobile-prepaid', 'recharge', 1, 'active'),
('Mobile Postpaid', 'mobile-postpaid', 'recharge', 2, 'active'),
('DTH', 'dth', 'recharge', 3, 'active'),
('FASTag', 'fastag', 'recharge', 4, 'active'),
('Electricity', 'electricity', 'bbps', 5, 'active'),
('Water', 'water', 'bbps', 6, 'active'),
('Gas', 'gas', 'bbps', 7, 'active'),
('Broadband', 'broadband', 'bbps', 8, 'active'),
('Landline', 'landline', 'bbps', 9, 'active'),
('LPG Gas', 'lpg-gas', 'bbps', 10, 'active'),
('Credit Card Bill', 'credit-card-bill', 'bbps', 11, 'active'),
('Insurance Premium', 'insurance-premium', 'bbps', 12, 'active'),
('Loan EMI', 'loan-emi', 'bbps', 13, 'active'),
('Municipal Tax', 'municipal-tax', 'bbps', 14, 'active'),
('Education Fees', 'education-fees', 'bbps', 15, 'active');

-- ============================================================
-- FIRST SUPER ADMIN (auto-appended)
-- Username : samir   |   Password : Rezo@200069!Wb
-- ============================================================

INSERT INTO admin_users (username, email, password_hash, full_name, role, status, created_at)
VALUES (
    'samir',
    'samir@wbtopper.in',
    '$2y$12$2UdImvFw8vlZZ7cdh0Ad.eiZe7bEA5ncASkego3y1sy.frfLaIAJy',
    'Samir Kumar',
    'super_admin',
    'active',
    NOW()
);