-- CrediAjo BNPL + Digital Thrift/ROSCA Integration Migration
-- Import this AFTER your existing jjbytes database SQL.
-- This migration uses the existing subscribers table for users and sysusers table for admins.

CREATE TABLE IF NOT EXISTS `fin_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(80) DEFAULT 'general',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_merchants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) DEFAULT NULL,
  `business_name` varchar(160) NOT NULL,
  `business_email` varchar(120) DEFAULT NULL,
  `business_phone` varchar(30) DEFAULT NULL,
  `business_type` varchar(80) DEFAULT NULL,
  `registration_number` varchar(80) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `commission_rate` decimal(7,4) DEFAULT 0.0000,
  `wallet_balance` decimal(18,2) DEFAULT 0.00,
  `risk_rating` enum('low','medium','high') DEFAULT 'medium',
  `status` enum('pending','approved','suspended','rejected') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_product_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `slug` varchar(140) NOT NULL,
  `icon` varchar(80) DEFAULT 'fa-box',
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(180) NOT NULL,
  `slug` varchar(220) NOT NULL,
  `sku` varchar(80) DEFAULT NULL,
  `short_description` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `specifications` text DEFAULT NULL,
  `price` decimal(18,2) NOT NULL DEFAULT 0.00,
  `sale_price` decimal(18,2) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `min_down_payment_percent` decimal(6,2) DEFAULT 20.00,
  `max_tenor_months` int(11) DEFAULT 12,
  `interest_rate_percent` decimal(6,2) DEFAULT 3.50,
  `warranty_info` varchar(255) DEFAULT NULL,
  `delivery_options` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `rating` decimal(3,2) DEFAULT 0.00,
  `review_count` int(11) DEFAULT 0,
  `status` enum('draft','active','out_of_stock','suspended') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `merchant_id` (`merchant_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  KEY `price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_product_media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `media_type` enum('image','video') DEFAULT 'image',
  `url` varchar(255) NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_product_variants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL,
  `variant_value` varchar(120) NOT NULL,
  `price_adjustment` decimal(18,2) DEFAULT 0.00,
  `stock_quantity` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_credit_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) NOT NULL,
  `employment_status` varchar(80) DEFAULT NULL,
  `employer_name` varchar(150) DEFAULT NULL,
  `monthly_income` decimal(18,2) DEFAULT 0.00,
  `income_source` varchar(120) DEFAULT NULL,
  `bank_name` varchar(120) DEFAULT NULL,
  `bvn_status` enum('not_submitted','pending','verified','failed') DEFAULT 'not_submitted',
  `nin_status` enum('not_submitted','pending','verified','failed') DEFAULT 'not_submitted',
  `address_status` enum('not_submitted','pending','verified','failed') DEFAULT 'not_submitted',
  `device_reputation_score` int(11) DEFAULT 50,
  `behaviour_score` int(11) DEFAULT 50,
  `risk_level` enum('low','medium','high') DEFAULT 'medium',
  `credit_limit` decimal(18,2) DEFAULT 0.00,
  `available_credit` decimal(18,2) DEFAULT 0.00,
  `last_scored_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriber_id` (`subscriber_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_credit_scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) NOT NULL,
  `score` int(11) NOT NULL DEFAULT 300,
  `score_band` varchar(50) DEFAULT 'starter',
  `risk_score` decimal(8,4) DEFAULT 0.0000,
  `factors_json` longtext DEFAULT NULL,
  `model_version` varchar(50) DEFAULT 'rule-engine-v1',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `subscriber_id` (`subscriber_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_bnpl_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_ref` varchar(80) NOT NULL,
  `subscriber_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `product_name` varchar(180) DEFAULT NULL,
  `principal_amount` decimal(18,2) NOT NULL,
  `down_payment` decimal(18,2) DEFAULT 0.00,
  `financed_amount` decimal(18,2) NOT NULL,
  `interest_rate_percent` decimal(8,2) DEFAULT 0.00,
  `tenor_months` int(11) DEFAULT 1,
  `frequency` enum('weekly','biweekly','monthly','quarterly','semiannual','annual','custom') DEFAULT 'monthly',
  `installment_amount` decimal(18,2) DEFAULT 0.00,
  `total_payable` decimal(18,2) DEFAULT 0.00,
  `purpose` varchar(255) DEFAULT NULL,
  `credit_score` int(11) DEFAULT NULL,
  `ai_risk_band` enum('low','medium','high') DEFAULT 'medium',
  `status` enum('draft','pending','approved','rejected','active','completed','defaulted','cancelled') DEFAULT 'pending',
  `decision_reason` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `application_ref` (`application_ref`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `merchant_id` (`merchant_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_bnpl_installments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `installment_no` int(11) NOT NULL,
  `due_date` date NOT NULL,
  `amount_due` decimal(18,2) NOT NULL,
  `amount_paid` decimal(18,2) DEFAULT 0.00,
  `penalty_amount` decimal(18,2) DEFAULT 0.00,
  `status` enum('pending','part_paid','paid','overdue','waived') DEFAULT 'pending',
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `application_id` (`application_id`),
  KEY `due_date` (`due_date`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_wallet_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ledger_ref` varchar(80) NOT NULL,
  `subscriber_id` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `entry_type` enum('credit','debit') NOT NULL,
  `source` varchar(80) NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `old_balance` decimal(18,2) DEFAULT 0.00,
  `new_balance` decimal(18,2) DEFAULT 0.00,
  `status` enum('pending','successful','failed','reversed') DEFAULT 'successful',
  `description` varchar(255) DEFAULT NULL,
  `gateway` varchar(80) DEFAULT NULL,
  `gateway_ref` varchar(120) DEFAULT NULL,
  `metadata_json` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ledger_ref` (`ledger_ref`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `merchant_id` (`merchant_id`),
  KEY `source` (`source`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_thrift_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_ref` varchar(80) NOT NULL,
  `creator_id` int(11) NOT NULL,
  `group_name` varchar(160) NOT NULL,
  `description` text DEFAULT NULL,
  `group_type` enum('private','public','family','corporate','cooperative','community','religious','friends','women','students','business') DEFAULT 'private',
  `contribution_amount` decimal(18,2) NOT NULL,
  `member_limit` int(11) DEFAULT 10,
  `frequency` enum('daily','weekly','biweekly','monthly','quarterly','yearly','custom') DEFAULT 'monthly',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `rotation_method` enum('random_draw','manual_order','first_registered','auction','voting','priority','emergency','admin_selection','lottery','custom_order') DEFAULT 'first_registered',
  `late_fee` decimal(18,2) DEFAULT 0.00,
  `penalty_percent` decimal(8,2) DEFAULT 0.00,
  `grace_period_days` int(11) DEFAULT 3,
  `auto_removal` tinyint(1) DEFAULT 0,
  `voting_enabled` tinyint(1) DEFAULT 0,
  `invite_code` varchar(80) DEFAULT NULL,
  `private_password` varchar(255) DEFAULT NULL,
  `group_rules` text DEFAULT NULL,
  `admin_approval_required` tinyint(1) DEFAULT 1,
  `waiting_list_enabled` tinyint(1) DEFAULT 1,
  `reserve_members` int(11) DEFAULT 0,
  `status` enum('draft','open','active','paused','completed','cancelled') DEFAULT 'open',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_ref` (`group_ref`),
  UNIQUE KEY `invite_code` (`invite_code`),
  KEY `creator_id` (`creator_id`),
  KEY `status` (`status`),
  KEY `group_type` (`group_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_thrift_group_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `subscriber_id` int(11) NOT NULL,
  `rotation_position` int(11) DEFAULT NULL,
  `member_role` enum('creator','admin','member','reserve') DEFAULT 'member',
  `status` enum('pending','active','suspended','removed','completed') DEFAULT 'pending',
  `joined_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_user` (`group_id`,`subscriber_id`),
  KEY `subscriber_id` (`subscriber_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_thrift_contributions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `subscriber_id` int(11) NOT NULL,
  `cycle_no` int(11) DEFAULT 1,
  `due_date` date DEFAULT NULL,
  `amount_due` decimal(18,2) NOT NULL,
  `amount_paid` decimal(18,2) DEFAULT 0.00,
  `penalty_amount` decimal(18,2) DEFAULT 0.00,
  `payment_ref` varchar(120) DEFAULT NULL,
  `status` enum('pending','paid','late','missed','waived') DEFAULT 'pending',
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_thrift_payouts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `subscriber_id` int(11) NOT NULL,
  `cycle_no` int(11) DEFAULT 1,
  `amount` decimal(18,2) NOT NULL,
  `method` varchar(80) DEFAULT 'wallet',
  `status` enum('scheduled','processing','paid','failed','cancelled') DEFAULT 'scheduled',
  `scheduled_date` date DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_referrals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `referrer_id` int(11) NOT NULL,
  `referred_id` int(11) DEFAULT NULL,
  `campaign` varchar(100) DEFAULT NULL,
  `bonus_amount` decimal(18,2) DEFAULT 0.00,
  `status` enum('pending','earned','paid','rejected') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `referrer_id` (`referrer_id`),
  KEY `referred_id` (`referred_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_rewards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) NOT NULL,
  `reward_type` varchar(80) NOT NULL,
  `points` int(11) DEFAULT 0,
  `amount` decimal(18,2) DEFAULT 0.00,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('pending','available','redeemed','expired') DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `subscriber_id` (`subscriber_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_kyc_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) NOT NULL,
  `document_type` enum('bvn','nin','passport','national_id','driver_license','utility_bill','selfie','cac','other') DEFAULT 'other',
  `document_number` varchar(120) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `verification_status` enum('pending','verified','failed','expired') DEFAULT 'pending',
  `risk_score` int(11) DEFAULT 50,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `document_type` (`document_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_deliveries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bnpl_application_id` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `subscriber_id` int(11) DEFAULT NULL,
  `courier_name` varchar(120) DEFAULT NULL,
  `tracking_code` varchar(120) DEFAULT NULL,
  `delivery_address` varchar(255) DEFAULT NULL,
  `scheduled_date` date DEFAULT NULL,
  `proof_of_delivery` varchar(255) DEFAULT NULL,
  `status` enum('pending','assigned','in_transit','delivered','failed','returned') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `bnpl_application_id` (`bnpl_application_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_ref` varchar(80) NOT NULL,
  `subscriber_id` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `category` varchar(80) DEFAULT NULL,
  `subject` varchar(180) NOT NULL,
  `message` text DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `status` enum('open','pending','resolved','closed') DEFAULT 'open',
  `assigned_to` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_ref` (`ticket_ref`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) DEFAULT NULL,
  `channel` enum('email','sms','push','in_app','whatsapp','telegram','broadcast','webhook') DEFAULT 'in_app',
  `title` varchar(160) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `status` enum('pending','sent','failed','read') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `subscriber_id` (`subscriber_id`),
  KEY `channel` (`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_ai_fraud_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriber_id` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `risk_score` int(11) DEFAULT 50,
  `risk_band` enum('low','medium','high','critical') DEFAULT 'medium',
  `description` text DEFAULT NULL,
  `metadata_json` longtext DEFAULT NULL,
  `status` enum('open','reviewing','cleared','blocked') DEFAULT 'open',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `risk_band` (`risk_band`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_admin_audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) DEFAULT NULL,
  `subscriber_id` int(11) DEFAULT NULL,
  `action` varchar(150) NOT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `ip_address` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `metadata_json` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  KEY `entity_type` (`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_type` enum('platform','merchant','subscriber') DEFAULT 'merchant',
  `owner_id` int(11) DEFAULT NULL,
  `api_key` varchar(128) NOT NULL,
  `secret_hash` varchar(255) DEFAULT NULL,
  `environment` enum('sandbox','production') DEFAULT 'sandbox',
  `status` enum('active','revoked') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_key` (`api_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_api_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `api_key_id` int(11) DEFAULT NULL,
  `endpoint` varchar(180) DEFAULT NULL,
  `method` varchar(20) DEFAULT NULL,
  `status_code` int(11) DEFAULT NULL,
  `ip_address` varchar(80) DEFAULT NULL,
  `request_body` longtext DEFAULT NULL,
  `response_body` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `api_key_id` (`api_key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_webhook_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(80) DEFAULT NULL,
  `event_type` varchar(120) DEFAULT NULL,
  `payload` longtext DEFAULT NULL,
  `signature_valid` tinyint(1) DEFAULT 0,
  `status` enum('received','processed','failed') DEFAULT 'received',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `provider` (`provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_promotions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(160) NOT NULL,
  `promo_type` varchar(80) DEFAULT 'cashback',
  `value` decimal(18,2) DEFAULT 0.00,
  `starts_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `status` enum('draft','active','expired','paused') DEFAULT 'draft',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(80) NOT NULL,
  `discount_type` enum('fixed','percent') DEFAULT 'fixed',
  `discount_value` decimal(18,2) DEFAULT 0.00,
  `usage_limit` int(11) DEFAULT NULL,
  `used_count` int(11) DEFAULT 0,
  `status` enum('active','inactive','expired') DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_settlements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) DEFAULT NULL,
  `settlement_ref` varchar(80) NOT NULL,
  `gross_amount` decimal(18,2) DEFAULT 0.00,
  `commission_amount` decimal(18,2) DEFAULT 0.00,
  `net_amount` decimal(18,2) DEFAULT 0.00,
  `status` enum('pending','processing','paid','failed') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `settlement_ref` (`settlement_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fin_reconciliations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(80) DEFAULT NULL,
  `expected_amount` decimal(18,2) DEFAULT 0.00,
  `actual_amount` decimal(18,2) DEFAULT 0.00,
  `variance` decimal(18,2) DEFAULT 0.00,
  `status` enum('matched','unmatched','investigating','resolved') DEFAULT 'matched',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed fintech settings
INSERT IGNORE INTO `fin_settings` (`setting_key`,`setting_value`,`setting_group`) VALUES
('bnpl_default_interest_rate','3.5','bnpl'),
('bnpl_min_down_payment_percent','20','bnpl'),
('bnpl_max_tenor_months','12','bnpl'),
('thrift_platform_commission_percent','1.0','thrift'),
('fraud_high_risk_threshold','75','risk'),
('payment_gateway_mode','manual_placeholder','payment'),
('paymentpoint_integration_status','already-integrated-existing-system','payment'),
('platform_brand_name','CrediAjo','brand');

-- Seed categories matching the master prompt.
INSERT IGNORE INTO `fin_product_categories` (`name`,`slug`,`icon`,`description`) VALUES
('Smartphones','smartphones','fa-mobile-alt','Phones available for installment purchase.'),
('Laptops','laptops','fa-laptop','Computers and work devices.'),
('Tablets','tablets','fa-tablet-alt','Tablets for school and business.'),
('Smartwatches','smartwatches','fa-clock','Wearables and accessories.'),
('TVs','tvs','fa-tv','Smart TVs and home entertainment.'),
('Home Appliances','home-appliances','fa-blender','Appliances for homes.'),
('Furniture','furniture','fa-couch','Premium furniture and interiors.'),
('Office Equipment','office-equipment','fa-briefcase','Printers, desks, office tools.'),
('Solar Equipment','solar-equipment','fa-solar-panel','Solar panels, inverters and batteries.'),
('Generators','generators','fa-bolt','Power generators.'),
('Motorcycles','motorcycles','fa-motorcycle','Transport and delivery motorcycles.'),
('Vehicles','vehicles','fa-car','Vehicle financing category.'),
('Fashion','fashion','fa-tshirt','Fashion products.'),
('Building Materials','building-materials','fa-hard-hat','Construction and building supplies.'),
('Medical Equipment','medical-equipment','fa-stethoscope','Medical and clinical equipment.'),
('Educational Products','educational-products','fa-book','School and education materials.'),
('Farm Equipment','farm-equipment','fa-tractor','Agricultural tools and machines.'),
('Industrial Equipment','industrial-equipment','fa-industry','Industrial equipment.'),
('Electronics','electronics','fa-plug','General electronics.'),
('Beauty Products','beauty-products','fa-spa','Beauty and personal care.'),
('Sports Equipment','sports-equipment','fa-football-ball','Sports products.'),
('Toys','toys','fa-gamepad','Toys and games.'),
('Books','books','fa-book-open','Books and learning materials.'),
('Digital Products','digital-products','fa-cloud-download-alt','Eligible digital goods.');

-- Seed a demo merchant and products. Replace later with live merchants.
INSERT IGNORE INTO `fin_merchants` (`id`,`business_name`,`business_email`,`business_phone`,`business_type`,`status`,`risk_rating`,`commission_rate`) VALUES
(1,'CrediAjo Demo Merchant','merchant@example.com','08000000000','Marketplace Demo','approved','low',2.5000);

INSERT IGNORE INTO `fin_products` (`id`,`merchant_id`,`category_id`,`name`,`slug`,`sku`,`short_description`,`description`,`specifications`,`price`,`stock_quantity`,`min_down_payment_percent`,`max_tenor_months`,`interest_rate_percent`,`warranty_info`,`delivery_options`,`image`,`rating`,`review_count`,`status`) VALUES
(1,1,1,'Premium Android Smartphone','premium-android-smartphone','BNPL-PHONE-001','High-performance smartphone with flexible repayment.','A premium smartphone eligible for BNPL installment purchase.','8GB RAM, 256GB Storage, 5000mAh Battery',350000,25,20,12,3.5,'12 months warranty','Doorstep delivery / pickup','assets/fintech/banners/banner-phone.svg',4.8,128,'active'),
(2,1,2,'Business Laptop Pro','business-laptop-pro','BNPL-LAP-001','Work laptop for business owners and students.','Reliable business laptop available with flexible installment repayment.','Core i5, 16GB RAM, 512GB SSD',650000,12,25,12,3.5,'12 months warranty','Insured courier delivery','assets/fintech/banners/banner-laptop.svg',4.7,64,'active'),
(3,1,9,'Solar Inverter Kit','solar-inverter-kit','BNPL-SOLAR-001','Solar and inverter package for homes.','Solar power package for homes and shops with repayment options.','1.5KVA inverter, battery, panels',950000,8,30,18,3.2,'Installation warranty','Installer delivery','assets/fintech/banners/banner-solar.svg',4.9,42,'active'),
(4,1,7,'Modern Home Sofa Set','modern-home-sofa-set','BNPL-FURN-001','Luxury furniture with installment plan.','Comfortable sofa set available through merchant financing.','7-seater, premium fabric, custom colours',480000,5,20,10,3.5,'6 months warranty','Merchant delivery','assets/fintech/banners/banner-furniture.svg',4.6,37,'active');