-- ============================================================
-- PAYVORA COMPLETE DATABASE SCHEMA v2.0
-- Nigeria's Financial Super App
-- Covers: Users, Transactions, Airtime/Data/Bills, NIMC, CAC,
--         Gift Cards, Crypto P2P, Virtual Accounts, Virtual Cards,
--         Homevora, Marketvora, Escrow, MLM Referrals, Chat,
--         Notifications, Admin, Site Settings, Analytics
-- ============================================================

START TRANSACTION;

CREATE DATABASE IF NOT EXISTS `payvora_db`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE `payvora_db`;

-- ============================================================
-- 1. SITE SETTINGS
-- ============================================================
CREATE TABLE `site_settings` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `site_name`           VARCHAR(100)  NOT NULL DEFAULT 'Payvora',
  `site_url`            VARCHAR(255)  NOT NULL DEFAULT 'https://getpayvora.com',
  `app_url`             VARCHAR(255)  NOT NULL DEFAULT 'https://app.getpayvora.com',
  `support_email`       VARCHAR(255)  NOT NULL DEFAULT 'support@payvora.ng',
  `support_phone`       VARCHAR(20)   DEFAULT NULL,
  `support_whatsapp`    VARCHAR(20)   DEFAULT NULL,
  `site_color`          VARCHAR(20)   NOT NULL DEFAULT '#0A6647',
  `site_logo`           VARCHAR(255)  DEFAULT NULL,
  `site_favicon`        VARCHAR(255)  DEFAULT NULL,
  -- Referral MLM
  `referral_l1_bonus`   DECIMAL(12,2) NOT NULL DEFAULT 500.00,
  `referral_l2_bonus`   DECIMAL(12,2) NOT NULL DEFAULT 200.00,
  `referral_l3_bonus`   DECIMAL(12,2) NOT NULL DEFAULT 100.00,
  `referral_tx_pct_l1`  DECIMAL(5,2)  NOT NULL DEFAULT 2.00,
  `referral_tx_pct_l2`  DECIMAL(5,2)  NOT NULL DEFAULT 1.00,
  `referral_tx_pct_l3`  DECIMAL(5,2)  NOT NULL DEFAULT 0.50,
  -- Service Fees
  `airtime_markup_pct`  DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  `data_markup_pct`     DECIMAL(5,2)  NOT NULL DEFAULT 2.00,
  `bill_markup_pct`     DECIMAL(5,2)  NOT NULL DEFAULT 1.00,
  `nimc_fee`            DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  `cac_bn_fee`          DECIMAL(10,2) NOT NULL DEFAULT 25000.00,
  `cac_ltd_fee`         DECIMAL(10,2) NOT NULL DEFAULT 55000.00,
  `cac_inc_fee`         DECIMAL(10,2) NOT NULL DEFAULT 80000.00,
  `escrow_pct`          DECIMAL(5,2)  NOT NULL DEFAULT 1.50,
  `homevora_listing_fee`DECIMAL(10,2) NOT NULL DEFAULT 2000.00,
  `market_listing_fee`  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `virtual_card_fee`    DECIMAL(10,2) NOT NULL DEFAULT 1500.00,
  `card_funding_fee_pct`DECIMAL(5,2)  NOT NULL DEFAULT 1.50,
  `card_funding_cap`    DECIMAL(10,2) NOT NULL DEFAULT 2500.00,
  -- Maintenance
  `maintenance_mode`    TINYINT(1)    NOT NULL DEFAULT 0,
  `maintenance_msg`     TEXT          DEFAULT NULL,
  `registration_open`   TINYINT(1)    NOT NULL DEFAULT 1,
  -- Token
  `token_name`          VARCHAR(50)   NOT NULL DEFAULT 'PVT',
  `token_per_tx`        INT           NOT NULL DEFAULT 5,
  `updated_at`          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `site_settings`
(`site_name`,`site_url`,`app_url`,`support_email`,`support_phone`,
 `referral_l1_bonus`,`referral_l2_bonus`,`referral_l3_bonus`)
VALUES
('Payvora','https://getpayvora.com','https://app.getpayvora.com',
 'support@payvora.ng','09000000000',500.00,200.00,100.00);

-- ============================================================
-- 2. ADMIN USERS
-- ============================================================
CREATE TABLE `admins` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `admin_id`    VARCHAR(30)  UNIQUE NOT NULL,
  `fname`       VARCHAR(100) NOT NULL,
  `lname`       VARCHAR(100) NOT NULL,
  `email`       VARCHAR(255) UNIQUE NOT NULL,
  `phone`       VARCHAR(20)  DEFAULT NULL,
  `password`    VARCHAR(255) NOT NULL COMMENT 'bcrypt hashed',
  `role`        ENUM('super_admin','admin','support','finance','moderator') NOT NULL DEFAULT 'admin',
  `permissions` JSON         DEFAULT NULL COMMENT 'granular permission flags',
  `two_factor`  TINYINT(1)   NOT NULL DEFAULT 0,
  `status`      ENUM('active','suspended') NOT NULL DEFAULT 'active',
  `last_login`  DATETIME     DEFAULT NULL,
  `last_ip`     VARCHAR(45)  DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default super admin — password: Admin@2025 — CHANGE ON FIRST LOGIN
INSERT INTO `admins`
(`admin_id`,`fname`,`lname`,`email`,`password`,`role`,`status`)
VALUES
('ADMIN-001','Payvora','Admin','admin@payvora.ng',
 '$2y$12$LGKfmqNYBp6Z4G7Uew7j6.rGS.QM4z8EoVyaF6kXVrCnWkm4kQEQG',
 'super_admin','active');

-- ============================================================
-- 3. USERS
-- ============================================================
CREATE TABLE `users` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unique_id`       VARCHAR(20)  UNIQUE NOT NULL,
  `fname`           VARCHAR(100) NOT NULL,
  `lname`           VARCHAR(100) NOT NULL,
  `email`           VARCHAR(255) UNIQUE NOT NULL,
  `phone`           VARCHAR(20)  UNIQUE NOT NULL,
  `gender`          ENUM('Male','Female','Other') DEFAULT NULL,
  `address`         TEXT         DEFAULT NULL,
  `state`           VARCHAR(100) DEFAULT NULL,
  `city`            VARCHAR(100) DEFAULT NULL,
  `dob`             DATE         DEFAULT NULL,
  `password`        VARCHAR(255) NOT NULL,
  `pin`             VARCHAR(255) NOT NULL COMMENT '4-digit bcrypt hashed',
  `api_key`         VARCHAR(64)  UNIQUE NOT NULL,
  `img`             VARCHAR(255) NOT NULL DEFAULT 'default.png',
  -- Wallet
  `bal`             DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `bonus_wallet`    DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `token_points`    INT UNSIGNED  NOT NULL DEFAULT 0,
  `locked_bal`      DECIMAL(15,2) NOT NULL DEFAULT 0.00  COMMENT 'escrow holds',
  -- Referral
  `referral_code`   VARCHAR(20)  UNIQUE NOT NULL,
  `referred_by`     VARCHAR(20)  DEFAULT NULL,
  `referral_level`  TINYINT      NOT NULL DEFAULT 0      COMMENT 'level in upline',
  -- KYC & Status
  `level`           ENUM('Basic','Silver','Gold','Platinum') NOT NULL DEFAULT 'Basic',
  `kyc_status`      ENUM('none','pending','verified','rejected') NOT NULL DEFAULT 'none',
  `kyc_type`        VARCHAR(50)  DEFAULT NULL,
  `email_verified`  TINYINT(1)   NOT NULL DEFAULT 0,
  `phone_verified`  TINYINT(1)   NOT NULL DEFAULT 0,
  `bvn_verified`    TINYINT(1)   NOT NULL DEFAULT 0,
  `two_factor`      ENUM('Off','On') NOT NULL DEFAULT 'Off',
  `mode`            ENUM('light','dark') NOT NULL DEFAULT 'light',
  `status`          ENUM('Active','Suspended','Banned') NOT NULL DEFAULT 'Active',
  -- Limits
  `daily_limit`     DECIMAL(15,2) NOT NULL DEFAULT 200000.00,
  `tx_count_today`  INT           NOT NULL DEFAULT 0,
  `last_login`      DATETIME      DEFAULT NULL,
  `last_login_ip`   VARCHAR(45)   DEFAULT NULL,
  `created_at`      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email`     (`email`),
  INDEX `idx_phone`     (`phone`),
  INDEX `idx_ref_code`  (`referral_code`),
  INDEX `idx_referred`  (`referred_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. USER KYC DOCUMENTS
-- ============================================================
CREATE TABLE `kyc_documents` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       VARCHAR(20)  NOT NULL,
  `nin`           VARCHAR(20)  DEFAULT NULL,
  `bvn`           VARCHAR(20)  DEFAULT NULL,
  `id_type`       ENUM('national_id','drivers_license','voters_card','passport','nin_slip') DEFAULT NULL,
  `id_number`     VARCHAR(50)  DEFAULT NULL,
  `id_front`      VARCHAR(255) DEFAULT NULL,
  `id_back`       VARCHAR(255) DEFAULT NULL,
  `selfie`        VARCHAR(255) DEFAULT NULL,
  `status`        ENUM('none','pending','verified','rejected') NOT NULL DEFAULT 'none',
  `reject_reason` TEXT         DEFAULT NULL,
  `verified_by`   VARCHAR(30)  DEFAULT NULL,
  `submitted_at`  DATETIME     DEFAULT NULL,
  `verified_at`   DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. LOGIN SESSIONS
-- ============================================================
CREATE TABLE `login_sessions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     VARCHAR(20)  NOT NULL,
  `session_token` VARCHAR(128) UNIQUE NOT NULL,
  `ip_address`  VARCHAR(45)  DEFAULT NULL,
  `user_agent`  TEXT         DEFAULT NULL,
  `device`      VARCHAR(100) DEFAULT NULL,
  `location`    VARCHAR(255) DEFAULT NULL,
  `platform`    ENUM('web','mobile','api') NOT NULL DEFAULT 'web',
  `status`      ENUM('active','expired','revoked') NOT NULL DEFAULT 'active',
  `expires_at`  DATETIME     NOT NULL,
  `logged_in`   DATETIME     NOT NULL,
  `logged_out`  DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_token` (`session_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. OTP CODES
-- ============================================================
CREATE TABLE `otp_codes` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     VARCHAR(20)  DEFAULT NULL,
  `email`       VARCHAR(255) DEFAULT NULL,
  `phone`       VARCHAR(20)  DEFAULT NULL,
  `code`        VARCHAR(10)  NOT NULL,
  `type`        ENUM('email_verify','phone_verify','login_2fa','reset_password','reset_pin','transaction') NOT NULL,
  `used`        TINYINT(1)   NOT NULL DEFAULT 0,
  `attempts`    TINYINT      NOT NULL DEFAULT 0,
  `expires_at`  DATETIME     NOT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`,`type`),
  INDEX `idx_phone` (`phone`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 7. TRANSACTIONS (Master Ledger)
-- ============================================================
CREATE TABLE `transactions` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(60)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `type`          ENUM('credit','debit') NOT NULL,
  `category`      ENUM('airtime','data','electricity','cable','internet','water',
                        'transfer','crypto','gift_card','wallet_fund','withdrawal',
                        'virtual_card','escrow','referral_bonus','admin_credit',
                        'admin_debit','nimc','cac','token_redeem','fee') NOT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `fee`           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `balance_before`DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `balance_after` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `phone`         VARCHAR(20)  DEFAULT NULL,
  `network`       VARCHAR(30)  DEFAULT NULL,
  `provider`      VARCHAR(100) DEFAULT NULL,
  `recipient`     VARCHAR(255) DEFAULT NULL,
  `external_ref`  VARCHAR(255) DEFAULT NULL COMMENT 'API/gateway reference',
  `gateway`       VARCHAR(50)  DEFAULT NULL,
  `status`        ENUM('success','pending','failed','reversed','processing') NOT NULL DEFAULT 'pending',
  `note`          TEXT         DEFAULT NULL,
  `metadata`      JSON         DEFAULT NULL,
  `ip_address`    VARCHAR(45)  DEFAULT NULL,
  `processed_at`  DATETIME     DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user_date`   (`user_id`,`created_at`),
  INDEX `idx_category`    (`category`,`status`),
  INDEX `idx_ext_ref`     (`external_ref`),
  INDEX `idx_status`      (`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. WALLET FUNDING
-- ============================================================
CREATE TABLE `wallet_funding` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(60)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `fee`           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `net_amount`    DECIMAL(15,2) NOT NULL,
  `gateway`       ENUM('flutterwave','paystack','monnify','bank_transfer','admin') NOT NULL,
  `gateway_ref`   VARCHAR(255) DEFAULT NULL,
  `gateway_txn_id`VARCHAR(255) DEFAULT NULL,
  `payment_method`VARCHAR(50)  DEFAULT NULL COMMENT 'card/bank/ussd',
  `status`        ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  `webhook_data`  JSON         DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL,
  `verified_at`   DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user`    (`user_id`),
  INDEX `idx_gateway_ref` (`gateway_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 9. VIRTUAL ACCOUNTS
-- ============================================================
CREATE TABLE `virtual_accounts` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       VARCHAR(20)  UNIQUE NOT NULL,
  `bank_name`     VARCHAR(100) NOT NULL,
  `acc_name`      VARCHAR(255) NOT NULL,
  `acc_no`        VARCHAR(20)  UNIQUE NOT NULL,
  `bank_code`     VARCHAR(10)  DEFAULT NULL,
  `reference`     VARCHAR(100) DEFAULT NULL,
  `provider`      VARCHAR(50)  NOT NULL DEFAULT 'monnify',
  `status`        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 10. VIRTUAL CARDS (International USD)
-- ============================================================
CREATE TABLE `virtual_cards` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `card_id`       VARCHAR(50)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `card_number`   VARCHAR(25)  DEFAULT NULL COMMENT 'encrypted',
  `card_name`     VARCHAR(255) NOT NULL,
  `expiry_month`  TINYINT      DEFAULT NULL,
  `expiry_year`   SMALLINT     DEFAULT NULL,
  `cvv`           VARCHAR(10)  DEFAULT NULL COMMENT 'encrypted',
  `currency`      VARCHAR(10)  NOT NULL DEFAULT 'USD',
  `balance`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `card_type`     ENUM('Visa','Mastercard') NOT NULL DEFAULT 'Visa',
  `card_brand`    VARCHAR(30)  DEFAULT NULL,
  `status`        ENUM('active','frozen','terminated') NOT NULL DEFAULT 'active',
  `provider`      VARCHAR(50)  DEFAULT NULL,
  `provider_id`   VARCHAR(100) DEFAULT NULL,
  `creation_fee`  DECIMAL(10,2) NOT NULL DEFAULT 1500.00,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `virtual_card_transactions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `card_id`     VARCHAR(50)  NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `type`        ENUM('funding','debit','refund') NOT NULL,
  `amount`      DECIMAL(10,2) NOT NULL,
  `currency`    VARCHAR(10)  NOT NULL DEFAULT 'USD',
  `description` VARCHAR(255) DEFAULT NULL,
  `merchant`    VARCHAR(255) DEFAULT NULL,
  `status`      ENUM('success','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at`  DATETIME     NOT NULL,
  FOREIGN KEY (`card_id`) REFERENCES `virtual_cards`(`card_id`) ON DELETE CASCADE,
  INDEX `idx_card` (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 11. API SETTINGS
-- ============================================================
CREATE TABLE `api_settings` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `service`         VARCHAR(50)  UNIQUE NOT NULL,
  `display_name`    VARCHAR(100) NOT NULL,
  `public_key`      TEXT         DEFAULT NULL,
  `secret_key`      TEXT         DEFAULT NULL,
  `contract_code`   VARCHAR(100) DEFAULT NULL,
  `base_url`        VARCHAR(255) DEFAULT NULL,
  `webhook_secret`  VARCHAR(255) DEFAULT NULL,
  `extra_config`    JSON         DEFAULT NULL,
  `status`          ENUM('active','inactive','test') NOT NULL DEFAULT 'inactive',
  `notes`           TEXT         DEFAULT NULL,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `api_settings` (`service`,`display_name`,`status`) VALUES
('flutterwave',   'Flutterwave Payments',      'inactive'),
('paystack',      'Paystack Payments',          'inactive'),
('monnify',       'Monnify Virtual Accounts',   'inactive'),
('gladtiding',    'GladTiding VTU API',          'inactive'),
('dataplug',      'DataPlug VTU',               'inactive'),
('clubkonnect',   'ClubKonnect VTU',            'inactive'),
('n3tdata',       'N3TData VTU',                'inactive'),
('nimc_nibss',    'NIMC/NIBSS NIN API',         'inactive'),
('card_sudo',     'Sudo.Africa Virtual Card',   'inactive'),
('card_bridgecard','BridgeCard Virtual Card',   'inactive'),
('smtp',          'SMTP Email Settings',         'inactive'),
('termii',        'Termii SMS Gateway',          'inactive');

-- ============================================================
-- 12. DATA PLANS
-- ============================================================
CREATE TABLE `data_plans` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `network`     ENUM('MTN','GLO','AIRTEL','9MOBILE') NOT NULL,
  `plan_type`   ENUM('SME','GIFTING','CORPORATE','DIRECT') NOT NULL DEFAULT 'SME',
  `name`        VARCHAR(100) NOT NULL,
  `size`        VARCHAR(30)  NOT NULL,
  `validity`    VARCHAR(50)  NOT NULL,
  `sell_price`  DECIMAL(10,2) NOT NULL,
  `cost_price`  DECIMAL(10,2) NOT NULL,
  `api_code`    VARCHAR(100) DEFAULT NULL,
  `sort_order`  INT          NOT NULL DEFAULT 0,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  INDEX `idx_network` (`network`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `data_plans`
(`network`,`plan_type`,`name`,`size`,`validity`,`sell_price`,`cost_price`,`sort_order`) VALUES
('MTN','SME','MTN 500MB Daily','500MB','1 Day',  100.00,95.00,1),
('MTN','SME','MTN 500MB','500MB','30 Days',145.00,138.00,2),
('MTN','SME','MTN 1GB','1GB','30 Days',268.00,255.00,3),
('MTN','SME','MTN 2GB','2GB','30 Days',508.00,483.00,4),
('MTN','SME','MTN 3GB','3GB','30 Days',750.00,713.00,5),
('MTN','SME','MTN 5GB','5GB','30 Days',1388.00,1320.00,6),
('MTN','SME','MTN 10GB','10GB','30 Days',2488.00,2365.00,7),
('MTN','SME','MTN 20GB','20GB','30 Days',4500.00,4275.00,8),
('GLO','SME','Glo 500MB','500MB','14 Days',125.00,119.00,1),
('GLO','SME','Glo 1GB','1GB','30 Days',238.00,226.00,2),
('GLO','SME','Glo 2GB','2GB','30 Days',478.00,455.00,3),
('GLO','SME','Glo 5GB','5GB','30 Days',1225.00,1165.00,4),
('GLO','SME','Glo 10GB','10GB','30 Days',2500.00,2375.00,5),
('AIRTEL','SME','Airtel 500MB','500MB','30 Days',148.00,141.00,1),
('AIRTEL','SME','Airtel 1GB','1GB','30 Days',268.00,255.00,2),
('AIRTEL','SME','Airtel 2GB','2GB','30 Days',495.00,471.00,3),
('AIRTEL','SME','Airtel 5GB','5GB','30 Days',1390.00,1321.00,4),
('AIRTEL','SME','Airtel 10GB','10GB','30 Days',2490.00,2366.00,5),
('9MOBILE','SME','9mobile 500MB','500MB','30 Days',135.00,129.00,1),
('9MOBILE','SME','9mobile 1GB','1GB','30 Days',248.00,236.00,2),
('9MOBILE','SME','9mobile 2GB','2GB','30 Days',490.00,466.00,3),
('9MOBILE','SME','9mobile 5GB','5GB','30 Days',1200.00,1140.00,4);

-- ============================================================
-- 13. AIRTIME TRANSACTIONS LOG
-- ============================================================
CREATE TABLE `airtime_logs` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`         VARCHAR(60)  UNIQUE NOT NULL,
  `tx_ref`      VARCHAR(60)  NOT NULL COMMENT 'links to transactions table',
  `user_id`     VARCHAR(20)  NOT NULL,
  `network`     VARCHAR(20)  NOT NULL,
  `phone`       VARCHAR(20)  NOT NULL,
  `amount`      DECIMAL(10,2) NOT NULL,
  `api_ref`     VARCHAR(255) DEFAULT NULL,
  `api_response`JSON         DEFAULT NULL,
  `status`      ENUM('success','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at`  DATETIME     NOT NULL,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 14. DATA TRANSACTION LOG
-- ============================================================
CREATE TABLE `data_logs` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`         VARCHAR(60)  UNIQUE NOT NULL,
  `tx_ref`      VARCHAR(60)  NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `plan_id`     INT UNSIGNED NOT NULL,
  `network`     VARCHAR(20)  NOT NULL,
  `phone`       VARCHAR(20)  NOT NULL,
  `plan_name`   VARCHAR(100) NOT NULL,
  `amount`      DECIMAL(10,2) NOT NULL,
  `api_ref`     VARCHAR(255) DEFAULT NULL,
  `api_response`JSON         DEFAULT NULL,
  `status`      ENUM('success','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at`  DATETIME     NOT NULL,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 15. BILLS PAYMENT LOG
-- ============================================================
CREATE TABLE `bills_logs` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(60)  UNIQUE NOT NULL,
  `tx_ref`        VARCHAR(60)  NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `bill_type`     ENUM('electricity','cable','internet','water','tax') NOT NULL,
  `provider`      VARCHAR(100) NOT NULL,
  `provider_code` VARCHAR(50)  DEFAULT NULL,
  `account_no`    VARCHAR(100) NOT NULL COMMENT 'meter/smart card/account',
  `account_name`  VARCHAR(255) DEFAULT NULL,
  `package`       VARCHAR(100) DEFAULT NULL,
  `meter_type`    ENUM('prepaid','postpaid') DEFAULT NULL,
  `amount`        DECIMAL(10,2) NOT NULL,
  `token`         VARCHAR(255) DEFAULT NULL COMMENT 'electricity token',
  `api_ref`       VARCHAR(255) DEFAULT NULL,
  `api_response`  JSON         DEFAULT NULL,
  `status`        ENUM('success','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at`    DATETIME     NOT NULL,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 16. NIMC LOOKUPS
-- ============================================================
CREATE TABLE `nimc_lookups` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`         VARCHAR(60)  UNIQUE NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `lookup_type` ENUM('nin','phone','vnin') NOT NULL DEFAULT 'nin',
  `search_value`VARCHAR(30)  NOT NULL,
  `result_data` JSON         DEFAULT NULL,
  `fee`         DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  `status`      ENUM('success','failed','not_found') NOT NULL DEFAULT 'success',
  `api_response`JSON         DEFAULT NULL,
  `created_at`  DATETIME     NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 17. CAC REGISTRATION REQUESTS
-- ============================================================
CREATE TABLE `cac_requests` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`             VARCHAR(30)  UNIQUE NOT NULL,
  `user_id`         VARCHAR(20)  NOT NULL,
  `reg_type`        ENUM('business_name','limited_company','incorporated_trustee','llp') NOT NULL DEFAULT 'business_name',
  `company_name`    VARCHAR(255) NOT NULL,
  `alt_name_1`      VARCHAR(255) DEFAULT NULL,
  `alt_name_2`      VARCHAR(255) DEFAULT NULL,
  `business_address`TEXT         NOT NULL,
  `business_nature` VARCHAR(255) DEFAULT NULL,
  `directors`       TEXT         NOT NULL,
  `shareholders`    TEXT         DEFAULT NULL,
  `share_capital`   DECIMAL(15,2) DEFAULT NULL,
  `contact_phone`   VARCHAR(20)  DEFAULT NULL,
  `contact_email`   VARCHAR(255) DEFAULT NULL,
  `fee_charged`     DECIMAL(10,2) NOT NULL,
  `status`          ENUM('pending','processing','approved','rejected','delivered') NOT NULL DEFAULT 'pending',
  `admin_note`      TEXT         DEFAULT NULL,
  `cert_file`       VARCHAR(255) DEFAULT NULL,
  `processed_by`    VARCHAR(30)  DEFAULT NULL,
  `created_at`      DATETIME     NOT NULL,
  `processed_at`    DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user`   (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 18. GIFT CARDS CATALOGUE
-- ============================================================
CREATE TABLE `gift_cards` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`            VARCHAR(100) NOT NULL,
  `slug`            VARCHAR(100) UNIQUE NOT NULL,
  `category`        VARCHAR(50)  DEFAULT NULL,
  `country`         VARCHAR(10)  NOT NULL DEFAULT 'US',
  `currency`        VARCHAR(10)  NOT NULL DEFAULT 'USD',
  `buy_rate`        DECIMAL(10,2) DEFAULT NULL COMMENT 'rate when buying from users',
  `sell_rate`       DECIMAL(10,2) DEFAULT NULL COMMENT 'rate when selling to users',
  `min_amount`      DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `max_amount`      DECIMAL(10,2) NOT NULL DEFAULT 1000.00,
  `img`             VARCHAR(255) DEFAULT NULL,
  `instructions`    TEXT         DEFAULT NULL,
  `trade_type`      ENUM('sell','buy','both') NOT NULL DEFAULT 'sell',
  `status`          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order`      INT          NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `gift_cards`(`name`,`slug`,`country`,`currency`,`buy_rate`,`min_amount`,`max_amount`,`trade_type`,`status`) VALUES
('iTunes/Apple','itunes','US','USD',380.00,1.00,1000.00,'sell','active'),
('Amazon','amazon','US','USD',370.00,1.00,1000.00,'sell','active'),
('Google Play','google-play','US','USD',355.00,1.00,500.00,'sell','active'),
('Steam','steam','US','USD',360.00,1.00,500.00,'sell','active'),
('eBay','ebay','US','USD',350.00,1.00,500.00,'sell','active'),
('Walmart','walmart','US','USD',365.00,1.00,500.00,'sell','active'),
('Visa Gift Card','visa-gift','US','USD',340.00,10.00,500.00,'sell','active'),
('Netflix','netflix','US','USD',370.00,1.00,100.00,'sell','active');

-- ============================================================
-- 19. GIFT CARD TRADES
-- ============================================================
CREATE TABLE `gift_card_trades` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(30)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `card_id`       INT UNSIGNED NOT NULL,
  `card_name`     VARCHAR(100) NOT NULL,
  `trade_type`    ENUM('sell','buy') NOT NULL DEFAULT 'sell',
  `amount_foreign`DECIMAL(10,2) NOT NULL,
  `currency`      VARCHAR(10)  NOT NULL DEFAULT 'USD',
  `rate`          DECIMAL(10,2) NOT NULL,
  `naira_value`   DECIMAL(15,2) NOT NULL,
  `card_number`   TEXT         DEFAULT NULL COMMENT 'encrypted',
  `card_pin`      TEXT         DEFAULT NULL COMMENT 'encrypted',
  `card_images`   JSON         DEFAULT NULL,
  `status`        ENUM('pending','under_review','approved','declined','processing') NOT NULL DEFAULT 'pending',
  `admin_note`    TEXT         DEFAULT NULL,
  `processed_by`  VARCHAR(30)  DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL,
  `processed_at`  DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`card_id`) REFERENCES `gift_cards`(`id`),
  INDEX `idx_user`   (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 20. CRYPTO P2P ORDERS
-- ============================================================
CREATE TABLE `crypto_orders` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`      VARCHAR(30)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `coin`          ENUM('BTC','ETH','USDT','BNB','SOL','MATIC','XRP','DOGE') NOT NULL,
  `order_type`    ENUM('buy','sell') NOT NULL,
  `rate_ngn`      DECIMAL(18,2) NOT NULL,
  `amount_coin`   DECIMAL(18,8) NOT NULL,
  `available_coin`DECIMAL(18,8) NOT NULL,
  `filled_coin`   DECIMAL(18,8) NOT NULL DEFAULT 0,
  `min_trade_ngn` DECIMAL(15,2) NOT NULL DEFAULT 1000.00,
  `max_trade_ngn` DECIMAL(15,2) NOT NULL DEFAULT 10000000.00,
  `payment_window`INT           NOT NULL DEFAULT 30 COMMENT 'minutes to pay',
  `trade_count`   INT UNSIGNED  NOT NULL DEFAULT 0,
  `completion_pct`DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  `status`        ENUM('open','paused','completed','cancelled') NOT NULL DEFAULT 'open',
  `created_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_coin_type`   (`coin`,`order_type`,`status`),
  INDEX `idx_user`        (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 21. CRYPTO P2P TRADES
-- ============================================================
CREATE TABLE `crypto_trades` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `trade_ref`     VARCHAR(30)  UNIQUE NOT NULL,
  `order_id`      INT UNSIGNED NOT NULL,
  `initiator_id`  VARCHAR(20)  NOT NULL COMMENT 'who initiated the trade',
  `counter_id`    VARCHAR(20)  NOT NULL COMMENT 'order owner',
  `coin`          VARCHAR(10)  NOT NULL,
  `coin_amount`   DECIMAL(18,8) NOT NULL,
  `ngn_amount`    DECIMAL(15,2) NOT NULL,
  `rate`          DECIMAL(18,2) NOT NULL,
  `payment_proof` VARCHAR(255) DEFAULT NULL,
  `status`        ENUM('pending','paid','confirmed','cancelled','disputed') NOT NULL DEFAULT 'pending',
  `escrow_held`   TINYINT(1)   NOT NULL DEFAULT 1,
  `chat_room_id`  VARCHAR(40)  DEFAULT NULL,
  `expires_at`    DATETIME     NOT NULL,
  `paid_at`       DATETIME     DEFAULT NULL,
  `confirmed_at`  DATETIME     DEFAULT NULL,
  `cancelled_at`  DATETIME     DEFAULT NULL,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`)     REFERENCES `crypto_orders`(`id`),
  FOREIGN KEY (`initiator_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`counter_id`)   REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_initiator` (`initiator_id`),
  INDEX `idx_counter`   (`counter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 22. REFERRALS (3-Level MLM)
-- ============================================================
CREATE TABLE `referrals` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `referrer_id`   VARCHAR(20)  NOT NULL COMMENT 'who gets the bonus',
  `referred_id`   VARCHAR(20)  NOT NULL COMMENT 'who signed up',
  `level`         TINYINT      NOT NULL DEFAULT 1 COMMENT '1,2 or 3',
  `bonus_type`    ENUM('signup','transaction') NOT NULL DEFAULT 'signup',
  `bonus`         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tx_ref`        VARCHAR(60)  DEFAULT NULL COMMENT 'transaction that triggered bonus',
  `status`        ENUM('pending','credited','cancelled') NOT NULL DEFAULT 'credited',
  `created_at`    DATETIME     NOT NULL,
  FOREIGN KEY (`referrer_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_referrer` (`referrer_id`,`level`),
  INDEX `idx_referred` (`referred_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 23. NOTIFICATIONS
-- ============================================================
CREATE TABLE `notifications` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`   VARCHAR(20)  NOT NULL,
  `title`     VARCHAR(255) NOT NULL,
  `body`      TEXT         NOT NULL,
  `type`      ENUM('success','info','warning','error','system') NOT NULL DEFAULT 'info',
  `icon`      VARCHAR(50)  DEFAULT NULL,
  `action_url`VARCHAR(255) DEFAULT NULL,
  `is_read`   TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`DATETIME     NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user_read` (`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 24. HOMEVORA PROPERTIES
-- ============================================================
CREATE TABLE `properties` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `prop_id`         VARCHAR(20)  UNIQUE NOT NULL,
  `owner_id`        VARCHAR(20)  NOT NULL,
  `listing_type`    ENUM('direct_owner','agent') NOT NULL DEFAULT 'direct_owner',
  `title`           VARCHAR(255) NOT NULL,
  `description`     TEXT         DEFAULT NULL,
  `type`            ENUM('flat','house','duplex','bungalow','mansion','office','shop','land','shortlet','room') NOT NULL DEFAULT 'flat',
  `bedrooms`        TINYINT UNSIGNED DEFAULT NULL,
  `bathrooms`       TINYINT UNSIGNED DEFAULT NULL,
  `toilets`         TINYINT UNSIGNED DEFAULT NULL,
  `size_sqm`        DECIMAL(10,2)    DEFAULT NULL,
  `furnishing`      ENUM('unfurnished','semi_furnished','fully_furnished') DEFAULT NULL,
  `state`           VARCHAR(100) NOT NULL,
  `lga`             VARCHAR(100) DEFAULT NULL,
  `location`        VARCHAR(255) NOT NULL,
  `address`         TEXT         DEFAULT NULL,
  `latitude`        DECIMAL(10,7) DEFAULT NULL,
  `longitude`       DECIMAL(10,7) DEFAULT NULL,
  `price`           DECIMAL(15,2) NOT NULL,
  `price_period`    ENUM('per_year','per_month','per_week','outright') NOT NULL DEFAULT 'per_year',
  `caution_fee`     DECIMAL(15,2) DEFAULT NULL,
  `agency_fee`      DECIMAL(15,2) DEFAULT NULL,
  `amenities`       JSON         DEFAULT NULL,
  `images`          JSON         DEFAULT NULL,
  `video_url`       VARCHAR(255) DEFAULT NULL,
  `owner_verified`  TINYINT(1)   NOT NULL DEFAULT 0,
  `prop_verified`   TINYINT(1)   NOT NULL DEFAULT 0,
  `is_featured`     TINYINT(1)   NOT NULL DEFAULT 0,
  `commission_pct`  DECIMAL(5,2) NOT NULL DEFAULT 2.50,
  `views`           INT UNSIGNED  NOT NULL DEFAULT 0,
  `inquiries`       INT UNSIGNED  NOT NULL DEFAULT 0,
  `listing_fee_paid`TINYINT(1)   NOT NULL DEFAULT 0,
  `status`          ENUM('active','rented','sold','inactive','pending_review') NOT NULL DEFAULT 'active',
  `expires_at`      DATETIME     DEFAULT NULL,
  `created_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`owner_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_status_state` (`status`,`state`),
  INDEX `idx_type`         (`type`,`status`),
  INDEX `idx_owner`        (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Property Inspection Bookings
CREATE TABLE `property_inspections` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `prop_id`     VARCHAR(20)  NOT NULL,
  `owner_id`    VARCHAR(20)  NOT NULL,
  `tenant_id`   VARCHAR(20)  NOT NULL,
  `date`        DATE         NOT NULL,
  `time_slot`   VARCHAR(20)  NOT NULL,
  `status`      ENUM('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending',
  `note`        TEXT         DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_prop`   (`prop_id`),
  INDEX `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Property Favourites
CREATE TABLE `property_favourites` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`   VARCHAR(20)  NOT NULL,
  `prop_id`   VARCHAR(20)  NOT NULL,
  `created_at`TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_fav` (`user_id`,`prop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 25. MARKETVORA LISTINGS
-- ============================================================
CREATE TABLE `market_listings` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `listing_id`    VARCHAR(20)  UNIQUE NOT NULL,
  `seller_id`     VARCHAR(20)  NOT NULL,
  `title`         VARCHAR(255) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `category`      VARCHAR(50)  NOT NULL,
  `sub_category`  VARCHAR(50)  DEFAULT NULL,
  `brand`         VARCHAR(100) DEFAULT NULL,
  `condition`     ENUM('new','like_new','fairly_used','used','damaged') NOT NULL DEFAULT 'new',
  `price`         DECIMAL(15,2) NOT NULL,
  `negotiable`    TINYINT(1)   NOT NULL DEFAULT 1,
  `quantity`      INT UNSIGNED  NOT NULL DEFAULT 1,
  `state`         VARCHAR(100) DEFAULT NULL,
  `location`      VARCHAR(255) DEFAULT NULL,
  `images`        JSON         DEFAULT NULL,
  `video_url`     VARCHAR(255) DEFAULT NULL,
  `is_featured`   TINYINT(1)   NOT NULL DEFAULT 0,
  `boosted_until` DATETIME     DEFAULT NULL,
  `seller_verified`TINYINT(1)  NOT NULL DEFAULT 0,
  `views`         INT UNSIGNED  NOT NULL DEFAULT 0,
  `status`        ENUM('active','sold','paused','inactive','pending_review') NOT NULL DEFAULT 'active',
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`seller_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_cat_status` (`category`,`status`),
  INDEX `idx_seller`     (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Market Favourites
CREATE TABLE `market_favourites` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`   VARCHAR(20)  NOT NULL,
  `listing_id`VARCHAR(20)  NOT NULL,
  `created_at`TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_fav` (`user_id`,`listing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 26. ESCROW (Marketvora & Homevora)
-- ============================================================
CREATE TABLE `escrow_orders` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `escrow_ref`    VARCHAR(30)  UNIQUE NOT NULL,
  `buyer_id`      VARCHAR(20)  NOT NULL,
  `seller_id`     VARCHAR(20)  NOT NULL,
  `item_id`       VARCHAR(20)  DEFAULT NULL,
  `item_type`     ENUM('market','property') NOT NULL DEFAULT 'market',
  `item_title`    VARCHAR(255) NOT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `commission`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `commission_pct`DECIMAL(5,2)  NOT NULL DEFAULT 1.50,
  `payment_deadline` DATETIME  DEFAULT NULL,
  `release_deadline` DATETIME  DEFAULT NULL,
  `status`        ENUM('initiated','funded','in_progress','released','refunded','disputed','cancelled') NOT NULL DEFAULT 'initiated',
  `dispute_reason`TEXT         DEFAULT NULL,
  `dispute_by`    VARCHAR(20)  DEFAULT NULL,
  `resolved_by`   VARCHAR(30)  DEFAULT NULL,
  `note`          TEXT         DEFAULT NULL,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `funded_at`     DATETIME     DEFAULT NULL,
  `released_at`   DATETIME     DEFAULT NULL,
  FOREIGN KEY (`buyer_id`)  REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`seller_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_buyer`  (`buyer_id`),
  INDEX `idx_seller` (`seller_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 27. IN-APP CHAT (Homevora + Marketvora + Crypto P2P)
-- ============================================================
CREATE TABLE `chat_rooms` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `room_id`     VARCHAR(40)  UNIQUE NOT NULL,
  `user1_id`    VARCHAR(20)  NOT NULL,
  `user2_id`    VARCHAR(20)  NOT NULL,
  `context_type`ENUM('property','market','crypto','support','general') NOT NULL DEFAULT 'general',
  `context_id`  VARCHAR(30)  DEFAULT NULL COMMENT 'prop_id or listing_id or trade_ref',
  `last_msg`    TEXT         DEFAULT NULL,
  `last_msg_at` DATETIME     DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user1` (`user1_id`),
  INDEX `idx_user2` (`user2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `chat_messages` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `room_id`     VARCHAR(40)  NOT NULL,
  `sender_id`   VARCHAR(20)  NOT NULL,
  `message`     TEXT         NOT NULL,
  `msg_type`    ENUM('text','image','offer','system') NOT NULL DEFAULT 'text',
  `media_url`   VARCHAR(255) DEFAULT NULL,
  `is_read`     TINYINT(1)   NOT NULL DEFAULT 0,
  `flagged`     TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'auto-moderation flag',
  `sent_at`     DATETIME     NOT NULL,
  FOREIGN KEY (`room_id`) REFERENCES `chat_rooms`(`room_id`) ON DELETE CASCADE,
  INDEX `idx_room`   (`room_id`,`sent_at`),
  INDEX `idx_sender` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 28. SUPPORT TICKETS
-- ============================================================
CREATE TABLE `support_tickets` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id`   VARCHAR(20)  UNIQUE NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `subject`     VARCHAR(255) NOT NULL,
  `category`    ENUM('payment','account','airtime','data','bills','crypto','gift_card',
                     'homevora','marketvora','technical','other') NOT NULL DEFAULT 'other',
  `priority`    ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `status`      ENUM('open','assigned','in_progress','awaiting_user','resolved','closed') NOT NULL DEFAULT 'open',
  `assigned_to` VARCHAR(30)  DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user`   (`user_id`),
  INDEX `idx_status` (`status`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `support_replies` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id`   VARCHAR(20)  NOT NULL,
  `sender_id`   VARCHAR(30)  NOT NULL,
  `is_admin`    TINYINT(1)   NOT NULL DEFAULT 0,
  `message`     TEXT         NOT NULL,
  `attachments` JSON         DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`ticket_id`) ON DELETE CASCADE,
  INDEX `idx_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 29. ADMIN FUND LOG
-- ============================================================
CREATE TABLE `admin_fund_log` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `admin_id`    VARCHAR(30)  NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `amount`      DECIMAL(15,2) NOT NULL,
  `type`        ENUM('credit','debit') NOT NULL,
  `reason`      TEXT         NOT NULL,
  `bal_before`  DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `bal_after`   DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user`  (`user_id`),
  INDEX `idx_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 30. DAILY STATISTICS (for Admin Analytics)
-- ============================================================
CREATE TABLE `daily_stats` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `stat_date`       DATE         UNIQUE NOT NULL,
  `new_users`       INT UNSIGNED  NOT NULL DEFAULT 0,
  `active_users`    INT UNSIGNED  NOT NULL DEFAULT 0,
  `total_tx`        INT UNSIGNED  NOT NULL DEFAULT 0,
  `total_volume`    DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `airtime_tx`      INT UNSIGNED  NOT NULL DEFAULT 0,
  `airtime_vol`     DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `data_tx`         INT UNSIGNED  NOT NULL DEFAULT 0,
  `data_vol`        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `bills_tx`        INT UNSIGNED  NOT NULL DEFAULT 0,
  `bills_vol`       DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `crypto_tx`       INT UNSIGNED  NOT NULL DEFAULT 0,
  `crypto_vol`      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `giftcard_tx`     INT UNSIGNED  NOT NULL DEFAULT 0,
  `giftcard_vol`    DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `wallet_funded`   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `wallet_withdrawn`DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `new_properties`  INT UNSIGNED  NOT NULL DEFAULT 0,
  `new_listings`    INT UNSIGNED  NOT NULL DEFAULT 0,
  `total_fees`      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `computed_at`     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 31. BANNERS / ADS
-- ============================================================
CREATE TABLE `banners` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title`       VARCHAR(255) NOT NULL,
  `image`       VARCHAR(255) NOT NULL,
  `link`        VARCHAR(255) DEFAULT NULL,
  `position`    ENUM('home_top','home_mid','services','homevora','market') NOT NULL DEFAULT 'home_top',
  `platform`    ENUM('both','web','mobile') NOT NULL DEFAULT 'both',
  `click_count` INT UNSIGNED  NOT NULL DEFAULT 0,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `starts_at`   DATETIME     DEFAULT NULL,
  `ends_at`     DATETIME     DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 32. TOKEN SHOP (PVT Token Redemption)
-- ============================================================
CREATE TABLE `token_rewards` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title`       VARCHAR(255) NOT NULL,
  `description` TEXT         DEFAULT NULL,
  `reward_type` ENUM('airtime','data','cashback','discount','item') NOT NULL,
  `points_cost` INT UNSIGNED  NOT NULL,
  `value`       DECIMAL(10,2) NOT NULL,
  `stock`       INT UNSIGNED  DEFAULT NULL COMMENT 'NULL = unlimited',
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `token_redemptions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     VARCHAR(20)  NOT NULL,
  `reward_id`   INT UNSIGNED  NOT NULL,
  `points_used` INT UNSIGNED  NOT NULL,
  `status`      ENUM('pending','processed','failed') NOT NULL DEFAULT 'pending',
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`)   REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`reward_id`) REFERENCES `token_rewards`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 33. SAVED BANK ACCOUNTS (for withdrawals)
-- ============================================================
CREATE TABLE `saved_banks` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     VARCHAR(20)  NOT NULL,
  `bank_code`   VARCHAR(10)  NOT NULL,
  `bank_name`   VARCHAR(100) NOT NULL,
  `acc_number`  VARCHAR(20)  NOT NULL,
  `acc_name`    VARCHAR(255) NOT NULL,
  `is_default`  TINYINT(1)   NOT NULL DEFAULT 0,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 34. WITHDRAWAL REQUESTS
-- ============================================================
CREATE TABLE `withdrawals` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(60)  UNIQUE NOT NULL,
  `user_id`       VARCHAR(20)  NOT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `fee`           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `net_amount`    DECIMAL(15,2) NOT NULL,
  `bank_code`     VARCHAR(10)  NOT NULL,
  `bank_name`     VARCHAR(100) NOT NULL,
  `acc_number`    VARCHAR(20)  NOT NULL,
  `acc_name`      VARCHAR(255) NOT NULL,
  `gateway`       VARCHAR(50)  DEFAULT NULL,
  `gateway_ref`   VARCHAR(255) DEFAULT NULL,
  `status`        ENUM('pending','processing','success','failed','reversed') NOT NULL DEFAULT 'pending',
  `admin_note`    TEXT         DEFAULT NULL,
  `processed_by`  VARCHAR(30)  DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL,
  `processed_at`  DATETIME     DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_user`   (`user_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 35. SEND MONEY / TRANSFERS
-- ============================================================
CREATE TABLE `transfers` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(60)  UNIQUE NOT NULL,
  `sender_id`     VARCHAR(20)  NOT NULL,
  `recipient_type`ENUM('payvora_user','bank_account') NOT NULL DEFAULT 'bank_account',
  `recipient_id`  VARCHAR(20)  DEFAULT NULL COMMENT 'if Payvora user',
  `bank_code`     VARCHAR(10)  DEFAULT NULL,
  `bank_name`     VARCHAR(100) DEFAULT NULL,
  `acc_number`    VARCHAR(20)  DEFAULT NULL,
  `acc_name`      VARCHAR(255) DEFAULT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `fee`           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `narration`     VARCHAR(255) DEFAULT NULL,
  `gateway`       VARCHAR(50)  DEFAULT NULL,
  `gateway_ref`   VARCHAR(255) DEFAULT NULL,
  `status`        ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  `created_at`    DATETIME     NOT NULL,
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  INDEX `idx_sender` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 36. PROPERTY ESCROW (Rent Payment via Payvora)
-- ============================================================
CREATE TABLE `rent_payments` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref`           VARCHAR(30)  UNIQUE NOT NULL,
  `tenant_id`     VARCHAR(20)  NOT NULL,
  `landlord_id`   VARCHAR(20)  NOT NULL,
  `prop_id`       VARCHAR(20)  NOT NULL,
  `amount`        DECIMAL(15,2) NOT NULL,
  `commission`    DECIMAL(10,2) NOT NULL,
  `duration`      VARCHAR(50)  DEFAULT NULL,
  `status`        ENUM('held','access_confirmed','released','refunded','disputed') NOT NULL DEFAULT 'held',
  `access_confirmed_at` DATETIME DEFAULT NULL,
  `released_at`   DATETIME     DEFAULT NULL,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`tenant_id`)   REFERENCES `users`(`unique_id`) ON DELETE CASCADE,
  FOREIGN KEY (`landlord_id`) REFERENCES `users`(`unique_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- PERFORMANCE INDEXES
-- ============================================================
ALTER TABLE `transactions`    ADD INDEX `idx_ref`      (`ref`);
ALTER TABLE `transactions`    ADD INDEX `idx_created`  (`created_at`);
ALTER TABLE `notifications`   ADD INDEX `idx_created`  (`created_at`);
ALTER TABLE `chat_messages`   ADD INDEX `idx_read`     (`is_read`);
ALTER TABLE `properties`      ADD INDEX `idx_featured` (`is_featured`,`status`);
ALTER TABLE `market_listings` ADD INDEX `idx_featured` (`is_featured`,`status`);
ALTER TABLE `otp_codes`       ADD INDEX `idx_expires`  (`expires_at`);
ALTER TABLE `crypto_orders`   ADD INDEX `idx_status`   (`status`,`created_at`);

COMMIT;

-- ============================================================
-- NOTES FOR DEPLOYMENT
-- ============================================================
-- 1. Update zen/conn.php with your DB credentials
-- 2. Change admin password immediately after first login
-- 3. Configure API keys via Admin Panel → API Settings
-- 4. Set up webhook URLs with payment gateways:
--    Monnify:     https://yourdomain.com/webhook/monnify.php
--    Flutterwave: https://yourdomain.com/webhook/flutterwave.php
--    Paystack:    https://yourdomain.com/webhook/paystack.php
-- 5. Create upload directories:
--    /uploads/avatars/, /uploads/properties/, /uploads/market/,
--    /uploads/giftcards/, /uploads/kyc/, /uploads/cac/
-- 6. Set APP_ENV=production before going live
-- ============================================================
