-- =============================================================================
-- FUNDI FINANCE COMPANY LTD — Core Finance House Platform
-- Database / Script: fundi_finance
-- Licensed Finance House (CBN) — Abuja, Nigeria
-- Generated: July 2026
-- =============================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `fundi_finance`
--
CREATE DATABASE IF NOT EXISTS `fundi_finance`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `fundi_finance`;

-- -----------------------------------------------------------------------------
-- Core identity & RBAC
-- -----------------------------------------------------------------------------

CREATE TABLE `roles` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `module` varchar(80) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permission_role` (
  `permission_id` bigint UNSIGNED NOT NULL,
  `role_id` bigint UNSIGNED NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `permission_role_role_id_foreign` (`role_id`),
  CONSTRAINT `permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `permission_role_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `staff_code` varchar(30) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role_id` bigint UNSIGNED DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `designation` varchar(120) DEFAULT NULL,
  `is_inputer` tinyint(1) NOT NULL DEFAULT 0,
  `is_authorizer` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_uuid_unique` (`uuid`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_staff_code_unique` (`staff_code`),
  KEY `users_role_id_foreign` (`role_id`),
  CONSTRAINT `users_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint UNSIGNED NOT NULL,
  `reserved_at` int UNSIGNED DEFAULT NULL,
  `available_at` int UNSIGNED NOT NULL,
  `created_at` int UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Branches & organization
-- -----------------------------------------------------------------------------

CREATE TABLE `branches` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(150) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(80) DEFAULT 'Abuja',
  `state` varchar(80) DEFAULT 'FCT',
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `is_head_office` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `branches_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- CRM / Customers / KYC
-- -----------------------------------------------------------------------------

CREATE TABLE `customers` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `customer_no` varchar(30) NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `type` enum('individual','sme','corporate','contractor','government') NOT NULL DEFAULT 'individual',
  `title` varchar(20) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `business_name` varchar(200) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) NOT NULL,
  `alt_phone` varchar(30) DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `bvn` varchar(11) DEFAULT NULL,
  `nin` varchar(20) DEFAULT NULL,
  `rc_number` varchar(50) DEFAULT NULL,
  `tin` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `occupation` varchar(120) DEFAULT NULL,
  `employer` varchar(150) DEFAULT NULL,
  `monthly_income` decimal(18,2) DEFAULT NULL,
  `risk_rating` enum('low','medium','high') NOT NULL DEFAULT 'medium',
  `kyc_status` enum('pending','in_review','verified','rejected') NOT NULL DEFAULT 'pending',
  `portal_password` varchar(255) DEFAULT NULL,
  `portal_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `relationship_officer_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('active','inactive','blacklisted','closed') NOT NULL DEFAULT 'active',
  `notes` text,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customers_uuid_unique` (`uuid`),
  UNIQUE KEY `customers_customer_no_unique` (`customer_no`),
  KEY `customers_branch_id_foreign` (`branch_id`),
  KEY `customers_relationship_officer_id_foreign` (`relationship_officer_id`),
  KEY `customers_type_status_index` (`type`,`status`),
  CONSTRAINT `customers_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `customers_relationship_officer_id_foreign` FOREIGN KEY (`relationship_officer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_kyc_documents` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint UNSIGNED NOT NULL,
  `doc_type` enum('id_card','passport','utility_bill','cac','tax_clearance','bank_statement','other') NOT NULL,
  `doc_number` varchar(100) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `issued_at` date DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `verified` tinyint(1) NOT NULL DEFAULT 0,
  `verified_by` bigint UNSIGNED DEFAULT NULL,
  `verified_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_kyc_documents_customer_id_foreign` (`customer_id`),
  CONSTRAINT `customer_kyc_documents_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `leads` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `source` varchar(80) DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `business_name` varchar(200) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) NOT NULL,
  `interest` varchar(120) DEFAULT NULL,
  `estimated_amount` decimal(18,2) DEFAULT NULL,
  `stage` enum('new','contacted','qualified','proposal','won','lost') NOT NULL DEFAULT 'new',
  `assigned_to` bigint UNSIGNED DEFAULT NULL,
  `notes` text,
  `converted_customer_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `leads_uuid_unique` (`uuid`),
  KEY `leads_assigned_to_foreign` (`assigned_to`),
  CONSTRAINT `leads_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `crm_interactions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `lead_id` bigint UNSIGNED DEFAULT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `channel` enum('call','email','visit','whatsapp','meeting','other') NOT NULL DEFAULT 'call',
  `subject` varchar(200) NOT NULL,
  `notes` text,
  `next_follow_up` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_interactions_customer_id_foreign` (`customer_id`),
  KEY `crm_interactions_lead_id_foreign` (`lead_id`),
  KEY `crm_interactions_user_id_foreign` (`user_id`),
  CONSTRAINT `crm_interactions_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `crm_interactions_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `crm_interactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Products catalog
-- -----------------------------------------------------------------------------

CREATE TABLE `loan_products` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `category` enum('personal','women','education','salary_advance','sme','working_capital','contractor','invoice_discounting','lpo','trade','project','vehicle','equipment_lease','machinery','school_fees') NOT NULL,
  `description` text,
  `min_amount` decimal(18,2) NOT NULL DEFAULT 50000.00,
  `max_amount` decimal(18,2) NOT NULL DEFAULT 50000000.00,
  `interest_rate` decimal(8,4) NOT NULL COMMENT 'Annual percentage rate',
  `interest_method` enum('reducing','flat','discount') NOT NULL DEFAULT 'reducing',
  `min_tenor_months` int NOT NULL DEFAULT 1,
  `max_tenor_months` int NOT NULL DEFAULT 36,
  `processing_fee_pct` decimal(8,4) NOT NULL DEFAULT 1.0000,
  `penalty_rate_pct` decimal(8,4) NOT NULL DEFAULT 1.0000,
  `requires_collateral` tinyint(1) NOT NULL DEFAULT 0,
  `requires_guarantor` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_products_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `investment_products` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `category` enum('fixed','premium','corporate_note') NOT NULL,
  `description` text,
  `min_amount` decimal(18,2) NOT NULL DEFAULT 100000.00,
  `interest_rate` decimal(8,4) NOT NULL,
  `min_tenor_days` int NOT NULL DEFAULT 30,
  `max_tenor_days` int NOT NULL DEFAULT 365,
  `interest_payout` enum('maturity','monthly','quarterly') NOT NULL DEFAULT 'maturity',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `investment_products_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Accounts & transactions (core ledger for customers)
-- -----------------------------------------------------------------------------

CREATE TABLE `accounts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `account_no` varchar(30) NOT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `account_type` enum('savings','current','investment','loan_disbursement','escrow') NOT NULL DEFAULT 'savings',
  `currency` char(3) NOT NULL DEFAULT 'NGN',
  `balance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `available_balance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `lien_amount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `interest_rate` decimal(8,4) DEFAULT NULL,
  `opened_at` date NOT NULL,
  `closed_at` date DEFAULT NULL,
  `status` enum('active','dormant','frozen','closed') NOT NULL DEFAULT 'active',
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_uuid_unique` (`uuid`),
  UNIQUE KEY `accounts_account_no_unique` (`account_no`),
  KEY `accounts_customer_id_foreign` (`customer_id`),
  KEY `accounts_branch_id_foreign` (`branch_id`),
  CONSTRAINT `accounts_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `accounts_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `account_transactions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `reference` varchar(50) NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `contra_account_id` bigint UNSIGNED DEFAULT NULL,
  `txn_type` enum('credit','debit') NOT NULL,
  `category` enum('deposit','withdrawal','transfer','loan_disbursement','loan_repayment','interest','fee','investment','treasury','reversal','other') NOT NULL DEFAULT 'other',
  `amount` decimal(18,2) NOT NULL,
  `balance_after` decimal(18,2) NOT NULL,
  `narration` varchar(255) DEFAULT NULL,
  `value_date` date NOT NULL,
  `posted_by` bigint UNSIGNED DEFAULT NULL,
  `authorized_by` bigint UNSIGNED DEFAULT NULL,
  `status` enum('pending','posted','reversed') NOT NULL DEFAULT 'posted',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_transactions_uuid_unique` (`uuid`),
  UNIQUE KEY `account_transactions_reference_unique` (`reference`),
  KEY `account_transactions_account_id_foreign` (`account_id`),
  KEY `account_transactions_value_date_index` (`value_date`),
  CONSTRAINT `account_transactions_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Loan management
-- -----------------------------------------------------------------------------

CREATE TABLE `loans` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `loan_no` varchar(30) NOT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `product_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `account_id` bigint UNSIGNED DEFAULT NULL,
  `principal` decimal(18,2) NOT NULL,
  `interest_rate` decimal(8,4) NOT NULL,
  `interest_method` enum('reducing','flat','discount') NOT NULL DEFAULT 'reducing',
  `tenor_months` int NOT NULL,
  `processing_fee` decimal(18,2) NOT NULL DEFAULT 0.00,
  `disbursed_amount` decimal(18,2) DEFAULT NULL,
  `outstanding_principal` decimal(18,2) NOT NULL DEFAULT 0.00,
  `outstanding_interest` decimal(18,2) NOT NULL DEFAULT 0.00,
  `total_repayable` decimal(18,2) NOT NULL DEFAULT 0.00,
  `total_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `purpose` varchar(255) DEFAULT NULL,
  `application_date` date NOT NULL,
  `approval_date` date DEFAULT NULL,
  `disbursement_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `next_due_date` date DEFAULT NULL,
  `status` enum('draft','submitted','underwriting','approved','rejected','disbursed','active','in_arrears','restructured','written_off','closed') NOT NULL DEFAULT 'draft',
  `credit_score` int DEFAULT NULL,
  `officer_id` bigint UNSIGNED DEFAULT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `disbursed_by` bigint UNSIGNED DEFAULT NULL,
  `rejection_reason` text,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loans_uuid_unique` (`uuid`),
  UNIQUE KEY `loans_loan_no_unique` (`loan_no`),
  KEY `loans_customer_id_foreign` (`customer_id`),
  KEY `loans_product_id_foreign` (`product_id`),
  KEY `loans_status_index` (`status`),
  CONSTRAINT `loans_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loans_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `loan_products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_guarantors` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_id` bigint UNSIGNED NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `relationship` varchar(80) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `bvn` varchar(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_guarantors_loan_id_foreign` (`loan_id`),
  CONSTRAINT `loan_guarantors_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_collaterals` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_id` bigint UNSIGNED NOT NULL,
  `collateral_type` enum('property','vehicle','equipment','inventory','receivable','cash','other') NOT NULL,
  `description` varchar(255) NOT NULL,
  `estimated_value` decimal(18,2) NOT NULL DEFAULT 0.00,
  `document_ref` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_collaterals_loan_id_foreign` (`loan_id`),
  CONSTRAINT `loan_collaterals_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_schedules` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_id` bigint UNSIGNED NOT NULL,
  `installment_no` int NOT NULL,
  `due_date` date NOT NULL,
  `principal_due` decimal(18,2) NOT NULL,
  `interest_due` decimal(18,2) NOT NULL,
  `fees_due` decimal(18,2) NOT NULL DEFAULT 0.00,
  `total_due` decimal(18,2) NOT NULL,
  `principal_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `interest_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `fees_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `penalty_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','partial','paid','overdue','waived') NOT NULL DEFAULT 'pending',
  `paid_at` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_schedules_loan_installment_unique` (`loan_id`,`installment_no`),
  CONSTRAINT `loan_schedules_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_repayments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `reference` varchar(50) NOT NULL,
  `loan_id` bigint UNSIGNED NOT NULL,
  `schedule_id` bigint UNSIGNED DEFAULT NULL,
  `amount` decimal(18,2) NOT NULL,
  `principal_component` decimal(18,2) NOT NULL DEFAULT 0.00,
  `interest_component` decimal(18,2) NOT NULL DEFAULT 0.00,
  `penalty_component` decimal(18,2) NOT NULL DEFAULT 0.00,
  `fee_component` decimal(18,2) NOT NULL DEFAULT 0.00,
  `payment_method` enum('cash','transfer','cheque','salary_deduction','portal') NOT NULL DEFAULT 'transfer',
  `payment_date` date NOT NULL,
  `narration` varchar(255) DEFAULT NULL,
  `received_by` bigint UNSIGNED DEFAULT NULL,
  `authorized_by` bigint UNSIGNED DEFAULT NULL,
  `status` enum('pending','posted','reversed') NOT NULL DEFAULT 'posted',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_repayments_uuid_unique` (`uuid`),
  UNIQUE KEY `loan_repayments_reference_unique` (`reference`),
  KEY `loan_repayments_loan_id_foreign` (`loan_id`),
  CONSTRAINT `loan_repayments_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Investments
-- -----------------------------------------------------------------------------

CREATE TABLE `investments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `investment_no` varchar(30) NOT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `product_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED DEFAULT NULL,
  `principal` decimal(18,2) NOT NULL,
  `interest_rate` decimal(8,4) NOT NULL,
  `tenor_days` int NOT NULL,
  `start_date` date NOT NULL,
  `maturity_date` date NOT NULL,
  `expected_interest` decimal(18,2) NOT NULL DEFAULT 0.00,
  `interest_paid` decimal(18,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','active','matured','rolled_over','liquidated','cancelled') NOT NULL DEFAULT 'pending',
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `authorized_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `investments_uuid_unique` (`uuid`),
  UNIQUE KEY `investments_investment_no_unique` (`investment_no`),
  KEY `investments_customer_id_foreign` (`customer_id`),
  KEY `investments_product_id_foreign` (`product_id`),
  CONSTRAINT `investments_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `investments_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `investment_products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Treasury
-- -----------------------------------------------------------------------------

CREATE TABLE `treasury_instruments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(40) NOT NULL,
  `name` varchar(150) NOT NULL,
  `instrument_type` enum('treasury_bill','commercial_paper','bond','money_market') NOT NULL,
  `issuer` varchar(150) DEFAULT NULL,
  `face_value` decimal(18,2) NOT NULL,
  `coupon_rate` decimal(8,4) DEFAULT NULL,
  `discount_rate` decimal(8,4) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `status` enum('available','subscribed','matured','sold') NOT NULL DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `treasury_instruments_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `treasury_positions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `position_no` varchar(30) NOT NULL,
  `instrument_id` bigint UNSIGNED NOT NULL,
  `portfolio` enum('own_book','client_managed') NOT NULL DEFAULT 'own_book',
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `quantity` decimal(18,2) NOT NULL DEFAULT 1.00,
  `cost_amount` decimal(18,2) NOT NULL,
  `market_value` decimal(18,2) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `authorized_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `treasury_positions_uuid_unique` (`uuid`),
  UNIQUE KEY `treasury_positions_position_no_unique` (`position_no`),
  KEY `treasury_positions_instrument_id_foreign` (`instrument_id`),
  CONSTRAINT `treasury_positions_instrument_id_foreign` FOREIGN KEY (`instrument_id`) REFERENCES `treasury_instruments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Accounting / GL
-- -----------------------------------------------------------------------------

CREATE TABLE `chart_of_accounts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(150) NOT NULL,
  `account_class` enum('asset','liability','equity','income','expense') NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `is_postable` tinyint(1) NOT NULL DEFAULT 1,
  `balance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chart_of_accounts_code_unique` (`code`),
  KEY `chart_of_accounts_parent_id_foreign` (`parent_id`),
  CONSTRAINT `chart_of_accounts_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `journal_entries` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `entry_no` varchar(30) NOT NULL,
  `entry_date` date NOT NULL,
  `narration` varchar(255) NOT NULL,
  `source_module` varchar(50) DEFAULT NULL,
  `source_id` bigint UNSIGNED DEFAULT NULL,
  `total_debit` decimal(18,2) NOT NULL DEFAULT 0.00,
  `total_credit` decimal(18,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','pending_approval','posted','reversed') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `authorized_by` bigint UNSIGNED DEFAULT NULL,
  `posted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `journal_entries_uuid_unique` (`uuid`),
  UNIQUE KEY `journal_entries_entry_no_unique` (`entry_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `journal_lines` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `journal_entry_id` bigint UNSIGNED NOT NULL,
  `gl_account_id` bigint UNSIGNED NOT NULL,
  `debit` decimal(18,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(18,2) NOT NULL DEFAULT 0.00,
  `narration` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journal_lines_journal_entry_id_foreign` (`journal_entry_id`),
  KEY `journal_lines_gl_account_id_foreign` (`gl_account_id`),
  CONSTRAINT `journal_lines_journal_entry_id_foreign` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_lines_gl_account_id_foreign` FOREIGN KEY (`gl_account_id`) REFERENCES `chart_of_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Dual control / operations queue
-- -----------------------------------------------------------------------------

CREATE TABLE `authorization_queue` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `module` varchar(50) NOT NULL,
  `action` varchar(80) NOT NULL,
  `record_type` varchar(80) NOT NULL,
  `record_id` bigint UNSIGNED NOT NULL,
  `payload` json DEFAULT NULL,
  `amount` decimal(18,2) DEFAULT NULL,
  `priority` enum('low','normal','high','critical') NOT NULL DEFAULT 'normal',
  `status` enum('pending','approved','rejected','expired') NOT NULL DEFAULT 'pending',
  `inputer_id` bigint UNSIGNED NOT NULL,
  `authorizer_id` bigint UNSIGNED DEFAULT NULL,
  `remarks` text,
  `decided_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `authorization_queue_uuid_unique` (`uuid`),
  KEY `authorization_queue_status_index` (`status`),
  KEY `authorization_queue_inputer_id_foreign` (`inputer_id`),
  CONSTRAINT `authorization_queue_inputer_id_foreign` FOREIGN KEY (`inputer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Advisory / compliance / audit
-- -----------------------------------------------------------------------------

CREATE TABLE `advisory_engagements` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `engagement_no` varchar(30) NOT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `service_type` enum('financial_advisory','business_consulting','fund_raising','capital_structuring') NOT NULL,
  `title` varchar(200) NOT NULL,
  `fee_amount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `status` enum('prospect','active','completed','cancelled') NOT NULL DEFAULT 'prospect',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `assigned_to` bigint UNSIGNED DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `advisory_engagements_uuid_unique` (`uuid`),
  UNIQUE KEY `advisory_engagements_engagement_no_unique` (`engagement_no`),
  KEY `advisory_engagements_customer_id_foreign` (`customer_id`),
  CONSTRAINT `advisory_engagements_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(80) NOT NULL,
  `record_type` varchar(80) DEFAULT NULL,
  `record_id` bigint UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_index` (`user_id`),
  KEY `audit_logs_module_index` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `settings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` varchar(100) NOT NULL,
  `value` text,
  `group` varchar(50) DEFAULT 'general',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `notifications` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `body` text NOT NULL,
  `type` varchar(50) DEFAULT 'info',
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `notifications_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- SEED DATA
-- =============================================================================

INSERT INTO `roles` (`id`, `name`, `slug`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Managing Director', 'managing-director', 'Executive oversight and final approvals', NOW(), NOW()),
(2, 'Marketing & Credit Lead', 'credit-lead', 'Credit team leadership and underwriting', NOW(), NOW()),
(3, 'Marketing/Credit Officer', 'credit-officer', 'Origination and relationship management', NOW(), NOW()),
(4, 'CFO / Treasurer', 'cfo', 'Finance, treasury and accounting', NOW(), NOW()),
(5, 'Internal Control & Compliance', 'compliance', 'Controls, KYC/AML and compliance', NOW(), NOW()),
(6, 'Operations Authorizer', 'ops-authorizer', 'Operations dual-control authorizer', NOW(), NOW()),
(7, 'Operations Inputer', 'ops-inputer', 'Operations dual-control inputer', NOW(), NOW()),
(8, 'Human Resources', 'hr', 'HR administration', NOW(), NOW()),
(9, 'Legal / Company Secretary', 'legal', 'Legal advisory and company secretariat', NOW(), NOW()),
(10, 'System Administrator', 'admin', 'Full platform administration', NOW(), NOW());

INSERT INTO `permissions` (`id`, `name`, `slug`, `module`, `created_at`, `updated_at`) VALUES
(1, 'View Dashboard', 'dashboard.view', 'dashboard', NOW(), NOW()),
(2, 'Manage Customers', 'customers.manage', 'customers', NOW(), NOW()),
(3, 'Manage Loans', 'loans.manage', 'loans', NOW(), NOW()),
(4, 'Approve Loans', 'loans.approve', 'loans', NOW(), NOW()),
(5, 'Manage Accounts', 'accounts.manage', 'accounts', NOW(), NOW()),
(6, 'Manage Investments', 'investments.manage', 'investments', NOW(), NOW()),
(7, 'Manage Treasury', 'treasury.manage', 'treasury', NOW(), NOW()),
(8, 'Manage Accounting', 'accounting.manage', 'accounting', NOW(), NOW()),
(9, 'Authorize Operations', 'operations.authorize', 'operations', NOW(), NOW()),
(10, 'Input Operations', 'operations.input', 'operations', NOW(), NOW()),
(11, 'Manage CRM', 'crm.manage', 'crm', NOW(), NOW()),
(12, 'View Reports', 'reports.view', 'reports', NOW(), NOW()),
(13, 'Manage Staff', 'staff.manage', 'staff', NOW(), NOW()),
(14, 'Manage Settings', 'settings.manage', 'settings', NOW(), NOW()),
(15, 'Compliance Review', 'compliance.review', 'compliance', NOW(), NOW());

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p CROSS JOIN roles r WHERE r.slug IN ('admin', 'managing-director');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 4 FROM permissions p WHERE p.slug IN ('dashboard.view','accounts.manage','investments.manage','treasury.manage','accounting.manage','reports.view','operations.authorize');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 2 FROM permissions p WHERE p.slug IN ('dashboard.view','customers.manage','loans.manage','loans.approve','crm.manage','reports.view');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 3 FROM permissions p WHERE p.slug IN ('dashboard.view','customers.manage','loans.manage','crm.manage');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 6 FROM permissions p WHERE p.slug IN ('dashboard.view','accounts.manage','operations.authorize','loans.manage');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 7 FROM permissions p WHERE p.slug IN ('dashboard.view','accounts.manage','operations.input','loans.manage');

INSERT INTO `permission_role` (`permission_id`, `role_id`)
SELECT p.id, 5 FROM permissions p WHERE p.slug IN ('dashboard.view','customers.manage','compliance.review','reports.view','operations.authorize');

-- Demo passwords are finalized by: php artisan db:seed --force  (Password@123)
INSERT INTO `users` (`id`, `uuid`, `staff_code`, `name`, `email`, `phone`, `password`, `role_id`, `department`, `designation`, `is_inputer`, `is_authorizer`, `status`, `email_verified_at`, `created_at`, `updated_at`) VALUES
(1, '11111111-1111-1111-1111-111111111001', 'FFC-MD-001', 'Shehu Ojonimi', 'md@fundifinance.ng', '08030000001', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'Executive', 'Managing Director', 0, 1, 'active', NOW(), NOW(), NOW()),
(2, '11111111-1111-1111-1111-111111111002', 'FFC-CL-001', 'Amina Bello', 'credit.lead@fundifinance.ng', '08030000002', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 2, 'Credit', 'Marketing & Credit Team Lead', 0, 1, 'active', NOW(), NOW(), NOW()),
(3, '11111111-1111-1111-1111-111111111003', 'FFC-CO-001', 'Chinedu Okeke', 'credit1@fundifinance.ng', '08030000003', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 3, 'Credit', 'Marketing/Credit Officer', 0, 0, 'active', NOW(), NOW(), NOW()),
(4, '11111111-1111-1111-1111-111111111004', 'FFC-CFO-001', 'Fatima Yusuf', 'cfo@fundifinance.ng', '08030000004', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 4, 'Finance', 'CFO / Treasurer', 0, 1, 'active', NOW(), NOW(), NOW()),
(5, '11111111-1111-1111-1111-111111111005', 'FFC-IC-001', 'Ibrahim Musa', 'compliance@fundifinance.ng', '08030000005', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 5, 'Compliance', 'Internal Control & Compliance', 0, 1, 'active', NOW(), NOW(), NOW()),
(6, '11111111-1111-1111-1111-111111111006', 'FFC-OA-001', 'Grace Adeyemi', 'ops.auth@fundifinance.ng', '08030000006', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 6, 'Operations', 'Operations Officer (Authorizer)', 0, 1, 'active', NOW(), NOW(), NOW()),
(7, '11111111-1111-1111-1111-111111111007', 'FFC-OI-001', 'Tunde Balogun', 'ops.input@fundifinance.ng', '08030000007', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 7, 'Operations', 'Operations Officer (Inputer)', 1, 0, 'active', NOW(), NOW(), NOW()),
(8, '11111111-1111-1111-1111-111111111008', 'FFC-HR-001', 'Ngozi Eze', 'hr@fundifinance.ng', '08030000008', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 8, 'HR', 'Human Resources Officer', 0, 0, 'active', NOW(), NOW(), NOW()),
(9, '11111111-1111-1111-1111-111111111009', 'FFC-LG-001', 'Emmanuel Ocheni', 'legal@fundifinance.ng', '08030000009', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 9, 'Legal', 'Legal Adviser / Company Secretary', 0, 0, 'active', NOW(), NOW(), NOW()),
(10, '11111111-1111-1111-1111-111111111010', 'FFC-AD-001', 'System Admin', 'admin@fundifinance.ng', '08030000010', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 10, 'IT', 'System Administrator', 1, 1, 'active', NOW(), NOW(), NOW());

-- Fix passwords properly with bcrypt for "Password@123" — regenerate via seeder if needed.
-- Using Laravel default hash for "password" above; seeder will reset to Password@123.

INSERT INTO `branches` (`id`, `code`, `name`, `address`, `city`, `state`, `phone`, `email`, `is_head_office`, `status`, `created_at`, `updated_at`) VALUES
(1, 'ABJ-HO', 'Abuja Head Office', 'Central Business District, Abuja', 'Abuja', 'FCT', '09000000001', 'info@fundifinance.ng', 1, 'active', NOW(), NOW());

INSERT INTO `loan_products` (`id`, `code`, `name`, `category`, `description`, `min_amount`, `max_amount`, `interest_rate`, `interest_method`, `min_tenor_months`, `max_tenor_months`, `processing_fee_pct`, `requires_collateral`, `requires_guarantor`, `status`, `created_at`, `updated_at`) VALUES
(1, 'PL-001', 'Personal Loan', 'personal', 'Flexible personal financing for salaried individuals', 50000, 5000000, 28.0000, 'reducing', 3, 24, 1.5000, 0, 1, 'active', NOW(), NOW()),
(2, 'WI-001', 'Women Initiative Loan', 'women', 'Empowerment loans for women-owned enterprises', 100000, 10000000, 24.0000, 'reducing', 3, 36, 1.0000, 0, 1, 'active', NOW(), NOW()),
(3, 'ED-001', 'Education Loan', 'education', 'School and tertiary education financing', 100000, 8000000, 22.0000, 'reducing', 6, 48, 1.0000, 0, 1, 'active', NOW(), NOW()),
(4, 'SA-001', 'Salary Advance', 'salary_advance', 'Short-term salary advances for payroll clients', 20000, 1000000, 5.0000, 'flat', 1, 3, 2.0000, 0, 0, 'active', NOW(), NOW()),
(5, 'SME-001', 'SME Loan', 'sme', 'Growth financing for small and medium enterprises', 500000, 50000000, 26.0000, 'reducing', 6, 36, 1.5000, 1, 1, 'active', NOW(), NOW()),
(6, 'WC-001', 'Working Capital Loan', 'working_capital', 'Short-cycle working capital for traders and SMEs', 250000, 30000000, 27.0000, 'reducing', 3, 18, 1.5000, 1, 1, 'active', NOW(), NOW()),
(7, 'CF-001', 'Contractor Finance', 'contractor', 'Contract financing for verified contractors', 1000000, 100000000, 25.0000, 'reducing', 3, 24, 2.0000, 1, 1, 'active', NOW(), NOW()),
(8, 'ID-001', 'Invoice Discounting', 'invoice_discounting', 'Discounting of verified receivables', 500000, 80000000, 20.0000, 'discount', 1, 6, 1.0000, 0, 0, 'active', NOW(), NOW()),
(9, 'LPO-001', 'LPO Financing', 'lpo', 'Local Purchase Order financing', 500000, 50000000, 22.0000, 'discount', 1, 6, 1.5000, 0, 1, 'active', NOW(), NOW()),
(10, 'TF-001', 'Trade Finance', 'trade', 'Import/export and domestic trade finance', 1000000, 150000000, 24.0000, 'reducing', 3, 12, 1.5000, 1, 1, 'active', NOW(), NOW()),
(11, 'PF-001', 'Project Finance', 'project', 'Medium-term project financing', 5000000, 500000000, 23.0000, 'reducing', 12, 60, 2.0000, 1, 1, 'active', NOW(), NOW()),
(12, 'VF-001', 'Vehicle Finance', 'vehicle', 'Asset finance for vehicles', 1500000, 40000000, 21.0000, 'reducing', 12, 48, 1.5000, 1, 0, 'active', NOW(), NOW()),
(13, 'EL-001', 'Equipment Leasing', 'equipment_lease', 'Lease financing for productive equipment', 1000000, 80000000, 20.0000, 'reducing', 12, 48, 1.5000, 1, 0, 'active', NOW(), NOW()),
(14, 'MF-001', 'Machinery Finance', 'machinery', 'Machinery acquisition financing', 2000000, 100000000, 21.0000, 'reducing', 12, 60, 1.5000, 1, 1, 'active', NOW(), NOW()),
(15, 'SF-001', 'School Fees Finance', 'school_fees', 'Term financing for school fees obligations', 100000, 5000000, 18.0000, 'flat', 3, 12, 1.0000, 0, 1, 'active', NOW(), NOW());

INSERT INTO `investment_products` (`id`, `code`, `name`, `category`, `description`, `min_amount`, `interest_rate`, `min_tenor_days`, `max_tenor_days`, `interest_payout`, `status`, `created_at`, `updated_at`) VALUES
(1, 'FI-001', 'Fixed Investment', 'fixed', 'Fixed tenure investment with guaranteed return', 100000, 14.0000, 30, 365, 'maturity', 'active', NOW(), NOW()),
(2, 'PI-001', 'Premium Investment Account', 'premium', 'Higher-yield premium investment for HNW clients', 5000000, 16.5000, 90, 730, 'quarterly', 'active', NOW(), NOW()),
(3, 'CN-001', 'Corporate Investment Note', 'corporate_note', 'Corporate investment notes for institutional clients', 10000000, 15.0000, 180, 1095, 'maturity', 'active', NOW(), NOW());

INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `account_class`, `parent_id`, `is_postable`, `balance`, `status`, `created_at`, `updated_at`) VALUES
(1, '1000', 'Assets', 'asset', NULL, 0, 0, 'active', NOW(), NOW()),
(2, '1100', 'Cash and Bank', 'asset', 1, 0, 0, 'active', NOW(), NOW()),
(3, '1101', 'Cash on Hand', 'asset', 2, 1, 5000000.00, 'active', NOW(), NOW()),
(4, '1102', 'Bank - Current Account', 'asset', 2, 1, 85000000.00, 'active', NOW(), NOW()),
(5, '1200', 'Loans and Advances', 'asset', 1, 0, 0, 'active', NOW(), NOW()),
(6, '1201', 'Loan Portfolio', 'asset', 5, 1, 0, 'active', NOW(), NOW()),
(7, '1300', 'Treasury Assets', 'asset', 1, 0, 0, 'active', NOW(), NOW()),
(8, '1301', 'Treasury Bills', 'asset', 7, 1, 20000000.00, 'active', NOW(), NOW()),
(9, '2000', 'Liabilities', 'liability', NULL, 0, 0, 'active', NOW(), NOW()),
(10, '2100', 'Customer Deposits', 'liability', 9, 0, 0, 'active', NOW(), NOW()),
(11, '2101', 'Investment Liabilities', 'liability', 10, 1, 0, 'active', NOW(), NOW()),
(12, '3000', 'Equity', 'equity', NULL, 0, 0, 'active', NOW(), NOW()),
(13, '3101', 'Share Capital', 'equity', 12, 1, 100000000.00, 'active', NOW(), NOW()),
(14, '4000', 'Income', 'income', NULL, 0, 0, 'active', NOW(), NOW()),
(15, '4101', 'Interest Income - Loans', 'income', 14, 1, 0, 'active', NOW(), NOW()),
(16, '4102', 'Fee Income', 'income', 14, 1, 0, 'active', NOW(), NOW()),
(17, '4103', 'Treasury Income', 'income', 14, 1, 0, 'active', NOW(), NOW()),
(18, '4104', 'Advisory Income', 'income', 14, 1, 0, 'active', NOW(), NOW()),
(19, '5000', 'Expenses', 'expense', NULL, 0, 0, 'active', NOW(), NOW()),
(20, '5101', 'Personnel Costs', 'expense', 19, 1, 0, 'active', NOW(), NOW()),
(21, '5102', 'Operating Expenses', 'expense', 19, 1, 0, 'active', NOW(), NOW()),
(22, '5103', 'Interest Expense', 'expense', 19, 1, 0, 'active', NOW(), NOW());

INSERT INTO `treasury_instruments` (`id`, `code`, `name`, `instrument_type`, `issuer`, `face_value`, `coupon_rate`, `discount_rate`, `issue_date`, `maturity_date`, `status`, `created_at`, `updated_at`) VALUES
(1, 'NTB-364-2026A', 'CBN Treasury Bill 364-Day', 'treasury_bill', 'Central Bank of Nigeria', 10000000.00, NULL, 12.5000, '2026-01-15', '2027-01-14', 'available', NOW(), NOW()),
(2, 'CP-DANG-90', 'Dangote CP 90-Day', 'commercial_paper', 'Dangote Industries', 5000000.00, NULL, 14.0000, '2026-06-01', '2026-08-30', 'available', NOW(), NOW()),
(3, 'FGN-2031', 'FGN Bond 2031', 'bond', 'Debt Management Office', 20000000.00, 13.5000, NULL, '2024-03-01', '2031-03-01', 'subscribed', NOW(), NOW()),
(4, 'MM-CALL-001', 'Overnight Money Market', 'money_market', 'Interbank', 1000000.00, 11.0000, NULL, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'available', NOW(), NOW());

INSERT INTO `customers` (`id`, `uuid`, `customer_no`, `branch_id`, `type`, `title`, `first_name`, `last_name`, `business_name`, `email`, `phone`, `gender`, `date_of_birth`, `bvn`, `address`, `city`, `state`, `occupation`, `employer`, `monthly_income`, `risk_rating`, `kyc_status`, `portal_password`, `portal_enabled`, `relationship_officer_id`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, '22222222-2222-2222-2222-222222222001', 'FFC-C-000001', 1, 'individual', 'Mr', 'David', 'Okoro', NULL, 'david.okoro@email.com', '08011110001', 'male', '1988-04-12', '22112211221', 'Gwarinpa, Abuja', 'Abuja', 'FCT', 'Civil Servant', 'Federal Ministry', 450000.00, 'low', 'verified', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 3, 'active', 3, NOW(), NOW()),
(2, '22222222-2222-2222-2222-222222222002', 'FFC-C-000002', 1, 'sme', NULL, NULL, NULL, 'Greenfield Agro Ltd', 'info@greenfieldagro.ng', '08011110002', NULL, NULL, NULL, 'Karu, Nasarawa', 'Karu', 'Nasarawa', NULL, NULL, 2500000.00, 'medium', 'verified', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 3, 'active', 3, NOW(), NOW()),
(3, '22222222-2222-2222-2222-222222222003', 'FFC-C-000003', 1, 'contractor', NULL, NULL, NULL, 'SolidBuild Contractors', 'contracts@solidbuild.ng', '08011110003', NULL, NULL, NULL, 'Wuse II, Abuja', 'Abuja', 'FCT', NULL, NULL, 8000000.00, 'medium', 'verified', NULL, 0, 2, 'active', 2, NOW(), NOW()),
(4, '22222222-2222-2222-2222-222222222004', 'FFC-C-000004', 1, 'corporate', NULL, NULL, NULL, 'Horizon Logistics Plc', 'treasury@horizonlog.ng', '08011110004', NULL, NULL, NULL, 'Maitama, Abuja', 'Abuja', 'FCT', NULL, NULL, 15000000.00, 'low', 'in_review', NULL, 0, 2, 'active', 2, NOW(), NOW()),
(5, '22222222-2222-2222-2222-222222222005', 'FFC-C-000005', 1, 'individual', 'Mrs', 'Halima', 'Sani', NULL, 'halima.sani@email.com', '08011110005', 'female', '1992-09-03', '33443344334', 'Kubwa, Abuja', 'Abuja', 'FCT', 'Entrepreneur', 'Self Employed', 320000.00, 'low', 'verified', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 3, 'active', 3, NOW(), NOW());

UPDATE `customers` SET `rc_number` = 'RC-882211', `tin` = '12345678-0001' WHERE `id` = 2;
UPDATE `customers` SET `rc_number` = 'RC-445566', `tin` = '87654321-0001' WHERE `id` = 3;
UPDATE `customers` SET `rc_number` = 'RC-998877', `tin` = '11223344-0001' WHERE `id` = 4;

INSERT INTO `accounts` (`id`, `uuid`, `account_no`, `customer_id`, `branch_id`, `account_type`, `currency`, `balance`, `available_balance`, `opened_at`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, '33333333-3333-3333-3333-333333333001', '1100000001', 1, 1, 'savings', 'NGN', 850000.00, 850000.00, CURDATE(), 'active', 7, NOW(), NOW()),
(2, '33333333-3333-3333-3333-333333333002', '1100000002', 2, 1, 'current', 'NGN', 4200000.00, 4000000.00, CURDATE(), 'active', 7, NOW(), NOW()),
(3, '33333333-3333-3333-3333-333333333003', '1100000003', 3, 1, 'current', 'NGN', 12500000.00, 12500000.00, CURDATE(), 'active', 7, NOW(), NOW()),
(4, '33333333-3333-3333-3333-333333333004', '1100000004', 5, 1, 'savings', 'NGN', 210000.00, 210000.00, CURDATE(), 'active', 7, NOW(), NOW()),
(5, '33333333-3333-3333-3333-333333333005', '1200000001', 2, 1, 'investment', 'NGN', 5000000.00, 0.00, CURDATE(), 'active', 4, NOW(), NOW());

INSERT INTO `account_transactions` (`id`, `uuid`, `reference`, `account_id`, `txn_type`, `category`, `amount`, `balance_after`, `narration`, `value_date`, `posted_by`, `authorized_by`, `status`, `created_at`, `updated_at`) VALUES
(1, '44444444-4444-4444-4444-444444444001', 'TXN-20260728-0001', 1, 'credit', 'deposit', 850000.00, 850000.00, 'Opening deposit', CURDATE(), 7, 6, 'posted', NOW(), NOW()),
(2, '44444444-4444-4444-4444-444444444002', 'TXN-20260728-0002', 2, 'credit', 'deposit', 4200000.00, 4200000.00, 'Business account funding', CURDATE(), 7, 6, 'posted', NOW(), NOW()),
(3, '44444444-4444-4444-4444-444444444003', 'TXN-20260728-0003', 3, 'credit', 'deposit', 12500000.00, 12500000.00, 'Contractor float', CURDATE(), 7, 6, 'posted', NOW(), NOW()),
(4, '44444444-4444-4444-4444-444444444004', 'TXN-20260728-0004', 5, 'credit', 'investment', 5000000.00, 5000000.00, 'Fixed investment placement', CURDATE(), 7, 4, 'posted', NOW(), NOW());

INSERT INTO `loans` (`id`, `uuid`, `loan_no`, `customer_id`, `product_id`, `branch_id`, `account_id`, `principal`, `interest_rate`, `interest_method`, `tenor_months`, `processing_fee`, `disbursed_amount`, `outstanding_principal`, `outstanding_interest`, `total_repayable`, `total_paid`, `purpose`, `application_date`, `approval_date`, `disbursement_date`, `maturity_date`, `next_due_date`, `status`, `credit_score`, `officer_id`, `approved_by`, `disbursed_by`, `created_by`, `created_at`, `updated_at`) VALUES
(1, '55555555-5555-5555-5555-555555555001', 'LN-2026-0001', 1, 1, 1, 1, 1500000.00, 28.0000, 'reducing', 12, 22500.00, 1477500.00, 1375000.00, 185000.00, 1740000.00, 125000.00, 'Home renovation', DATE_SUB(CURDATE(), INTERVAL 45 DAY), DATE_SUB(CURDATE(), INTERVAL 40 DAY), DATE_SUB(CURDATE(), INTERVAL 38 DAY), DATE_ADD(CURDATE(), INTERVAL 10 MONTH), DATE_ADD(CURDATE(), INTERVAL 15 DAY), 'active', 720, 3, 2, 6, 3, NOW(), NOW()),
(2, '55555555-5555-5555-5555-555555555002', 'LN-2026-0002', 2, 5, 1, 2, 8000000.00, 26.0000, 'reducing', 24, 120000.00, 7880000.00, 8000000.00, 0.00, 10080000.00, 0.00, 'Working capital for agro inputs', DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL, NULL, NULL, 'approved', 680, 3, 2, NULL, 3, NOW(), NOW()),
(3, '55555555-5555-5555-5555-555555555003', 'LN-2026-0003', 3, 7, 1, 3, 25000000.00, 25.0000, 'reducing', 12, 500000.00, NULL, 25000000.00, 0.00, 0.00, 0.00, 'Federal contract mobilization', DATE_SUB(CURDATE(), INTERVAL 5 DAY), NULL, NULL, NULL, NULL, 'underwriting', 650, 2, NULL, NULL, 2, NOW(), NOW()),
(4, '55555555-5555-5555-5555-555555555004', 'LN-2026-0004', 5, 2, 1, 4, 750000.00, 24.0000, 'reducing', 18, 7500.00, NULL, 750000.00, 0.00, 0.00, 0.00, 'Boutique expansion', CURDATE(), NULL, NULL, NULL, NULL, 'submitted', 700, 3, NULL, NULL, 3, NOW(), NOW());

INSERT INTO `loan_schedules` (`loan_id`, `installment_no`, `due_date`, `principal_due`, `interest_due`, `total_due`, `principal_paid`, `interest_paid`, `status`, `paid_at`, `created_at`, `updated_at`) VALUES
(1, 1, DATE_SUB(CURDATE(), INTERVAL 8 DAY), 125000.00, 35000.00, 160000.00, 125000.00, 35000.00, 'paid', DATE_SUB(CURDATE(), INTERVAL 5 DAY), NOW(), NOW()),
(1, 2, DATE_ADD(CURDATE(), INTERVAL 15 DAY), 125000.00, 32083.33, 157083.33, 0, 0, 'pending', NULL, NOW(), NOW()),
(1, 3, DATE_ADD(CURDATE(), INTERVAL 45 DAY), 125000.00, 29166.67, 154166.67, 0, 0, 'pending', NULL, NOW(), NOW());

INSERT INTO `loan_repayments` (`uuid`, `reference`, `loan_id`, `schedule_id`, `amount`, `principal_component`, `interest_component`, `payment_method`, `payment_date`, `narration`, `received_by`, `authorized_by`, `status`, `created_at`, `updated_at`) VALUES
('66666666-6666-6666-6666-666666666001', 'RPY-2026-0001', 1, 1, 160000.00, 125000.00, 35000.00, 'transfer', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'Installment 1 repayment', 7, 6, 'posted', NOW(), NOW());

INSERT INTO `loan_guarantors` (`loan_id`, `full_name`, `phone`, `email`, `relationship`, `address`, `created_at`, `updated_at`) VALUES
(1, 'Peter Okoro', '08022220001', 'peter.okoro@email.com', 'Brother', 'Gwarinpa, Abuja', NOW(), NOW()),
(4, 'Aisha Mohammed', '08022220002', NULL, 'Business Partner', 'Kubwa, Abuja', NOW(), NOW());

INSERT INTO `loan_collaterals` (`loan_id`, `collateral_type`, `description`, `estimated_value`, `document_ref`, `created_at`, `updated_at`) VALUES
(2, 'inventory', 'Agro input stock and warehouse inventory', 12000000.00, 'COL-INV-002', NOW(), NOW()),
(3, 'receivable', 'Assignment of contract receivables', 35000000.00, 'COL-REC-003', NOW(), NOW());

INSERT INTO `investments` (`uuid`, `investment_no`, `customer_id`, `product_id`, `account_id`, `principal`, `interest_rate`, `tenor_days`, `start_date`, `maturity_date`, `expected_interest`, `interest_paid`, `status`, `created_by`, `authorized_by`, `created_at`, `updated_at`) VALUES
('77777777-7777-7777-7777-777777777001', 'INV-2026-0001', 2, 1, 5, 5000000.00, 14.0000, 180, DATE_SUB(CURDATE(), INTERVAL 30 DAY), DATE_ADD(CURDATE(), INTERVAL 150 DAY), 345205.48, 0.00, 'active', 4, 1, NOW(), NOW());

INSERT INTO `treasury_positions` (`uuid`, `position_no`, `instrument_id`, `portfolio`, `quantity`, `cost_amount`, `market_value`, `purchase_date`, `status`, `created_by`, `authorized_by`, `created_at`, `updated_at`) VALUES
('88888888-8888-8888-8888-888888888001', 'TRP-2026-0001', 3, 'own_book', 1.00, 19500000.00, 19800000.00, '2025-11-15', 'open', 4, 1, NOW(), NOW());

INSERT INTO `leads` (`uuid`, `source`, `full_name`, `business_name`, `email`, `phone`, `interest`, `estimated_amount`, `stage`, `assigned_to`, `notes`, `created_by`, `created_at`, `updated_at`) VALUES
('99999999-9999-9999-9999-999999999001', 'Referral', 'Kunle Adewale', 'Adewale Supplies', 'kunle@adewalesupplies.ng', '08033330001', 'LPO Financing', 4500000.00, 'qualified', 3, 'Needs LPO finance for ministry supply', 3, NOW(), NOW()),
('99999999-9999-9999-9999-999999999002', 'Website', 'Blessing Uche', NULL, 'blessing.uche@email.com', '08033330002', 'Personal Loan', 800000.00, 'new', 3, NULL, 3, NOW(), NOW()),
('99999999-9999-9999-9999-999999999003', 'Walk-in', 'Farouk Hassan', 'Hassan Motors', 'farouk@hassanmotors.ng', '08033330003', 'Vehicle Finance', 12000000.00, 'proposal', 2, 'Fleet acquisition for ride-hailing', 2, NOW(), NOW());

INSERT INTO `crm_interactions` (`customer_id`, `lead_id`, `user_id`, `channel`, `subject`, `notes`, `next_follow_up`, `created_at`, `updated_at`) VALUES
(2, NULL, 3, 'visit', 'Site visit — Greenfield Agro', 'Reviewed inventory and warehouse controls', DATE_ADD(CURDATE(), INTERVAL 7 DAY), NOW(), NOW()),
(NULL, 1, 3, 'call', 'LPO document checklist', 'Requested award letter and invoice copies', DATE_ADD(CURDATE(), INTERVAL 2 DAY), NOW(), NOW());

INSERT INTO `authorization_queue` (`uuid`, `module`, `action`, `record_type`, `record_id`, `payload`, `amount`, `priority`, `status`, `inputer_id`, `created_at`, `updated_at`) VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaa0001', 'loans', 'disburse', 'loan', 2, '{"loan_no":"LN-2026-0002","principal":8000000}', 8000000.00, 'high', 'pending', 7, NOW(), NOW()),
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaa0002', 'accounts', 'withdrawal', 'account', 2, '{"account_no":"1100000002","amount":200000}', 200000.00, 'normal', 'pending', 7, NOW(), NOW());

INSERT INTO `advisory_engagements` (`uuid`, `engagement_no`, `customer_id`, `service_type`, `title`, `fee_amount`, `status`, `start_date`, `assigned_to`, `notes`, `created_at`, `updated_at`) VALUES
('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbb0001', 'ADV-2026-0001', 4, 'capital_structuring', 'Debt restructuring advisory', 2500000.00, 'active', CURDATE(), 4, 'Horizon Logistics capital structure review', NOW(), NOW());

INSERT INTO `journal_entries` (`uuid`, `entry_no`, `entry_date`, `narration`, `source_module`, `source_id`, `total_debit`, `total_credit`, `status`, `created_by`, `authorized_by`, `posted_at`, `created_at`, `updated_at`) VALUES
('cccccccc-cccc-cccc-cccc-cccccccc0001', 'JE-2026-0001', CURDATE(), 'Share capital injection — CBN minimum capital', 'equity', NULL, 100000000.00, 100000000.00, 'posted', 4, 1, NOW(), NOW(), NOW());

INSERT INTO `journal_lines` (`journal_entry_id`, `gl_account_id`, `debit`, `credit`, `narration`, `created_at`, `updated_at`) VALUES
(1, 4, 100000000.00, 0.00, 'Bank receipt — capital', NOW(), NOW()),
(1, 13, 0.00, 100000000.00, 'Share capital credited', NOW(), NOW());

INSERT INTO `settings` (`key`, `value`, `group`, `created_at`, `updated_at`) VALUES
('company_name', 'Fundi Finance Company Ltd', 'general', NOW(), NOW()),
('company_short_name', 'Fundi Finance', 'general', NOW(), NOW()),
('company_city', 'Abuja', 'general', NOW(), NOW()),
('company_state', 'FCT', 'general', NOW(), NOW()),
('currency', 'NGN', 'general', NOW(), NOW()),
('currency_symbol', '₦', 'general', NOW(), NOW()),
('cbn_minimum_capital', '100000000', 'regulatory', NOW(), NOW()),
('dual_control_threshold', '100000', 'operations', NOW(), NOW()),
('support_email', 'support@fundifinance.ng', 'general', NOW(), NOW()),
('support_phone', '09000000001', 'general', NOW(), NOW());

INSERT INTO `notifications` (`uuid`, `user_id`, `title`, `body`, `type`, `created_at`, `updated_at`) VALUES
('dddddddd-dddd-dddd-dddd-dddddddd0001', 6, 'Pending authorization', 'Loan LN-2026-0002 awaits disbursement authorization', 'action', NOW(), NOW()),
('dddddddd-dddd-dddd-dddd-dddddddd0002', 2, 'New loan application', 'Women Initiative Loan submitted by Halima Sani', 'info', NOW(), NOW());

INSERT INTO `audit_logs` (`user_id`, `action`, `module`, `record_type`, `record_id`, `ip_address`, `created_at`) VALUES
(10, 'login', 'auth', 'user', 10, '127.0.0.1', NOW()),
(3, 'create', 'loans', 'loan', 4, '127.0.0.1', NOW()),
(2, 'approve', 'loans', 'loan', 2, '127.0.0.1', NOW());

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;