-- Adminer 5.2.1 MariaDB 11.8.3-MariaDB-0+deb13u1 from Debian dump

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';


CREATE TABLE `AnnualChapterReview` (
  `ReviewID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ChapterName` varchar(100) DEFAULT NULL,
  `ReviewYear` int(11) NOT NULL,
  `TotalActiveGroups` int(11) DEFAULT NULL,
  `TotalActiveBeneficiaries` int(11) DEFAULT NULL,
  `TotalInactiveBeneficiaries` int(11) DEFAULT NULL,
  `ReasonsInactive` text DEFAULT NULL,
  `Summary` text DEFAULT NULL,
  `SubmittedBy` varchar(100) DEFAULT NULL,
  `SubmissionDate` date DEFAULT curdate(),
  PRIMARY KEY (`ReviewID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `ApplicationReview` (
  `ReviewID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ApplicationID` bigint(20) unsigned NOT NULL,
  `ReviewerID` bigint(20) unsigned DEFAULT NULL,
  `DecisionDate` date DEFAULT NULL,
  `DecisionType` varchar(20) DEFAULT NULL,
  `Notes` text DEFAULT NULL,
  PRIMARY KEY (`ReviewID`),
  KEY `ApplicationID` (`ApplicationID`),
  KEY `ReviewerID` (`ReviewerID`),
  CONSTRAINT `ApplicationReview_ibfk_1` FOREIGN KEY (`ApplicationID`) REFERENCES `LoanApplication` (`ApplicationID`),
  CONSTRAINT `ApplicationReview_ibfk_3` FOREIGN KEY (`ReviewerID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `AuditLogs` (
  `LogID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) DEFAULT NULL,
  `Action` varchar(255) DEFAULT NULL,
  `Timestamp` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`LogID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `AuditLogs` (`LogID`, `UserID`, `Action`, `Timestamp`) VALUES
(47,	10,	'Created enquiry for Iron Man with MembershipID VSI/1/2025/09/0001',	'2025-09-11 11:30:30'),
(48,	10,	'Created enquiry for Sokka Bridgewater with MembershipID ',	'2025-09-11 11:40:51'),
(49,	10,	'Created cohort: September-2025 Cohort (Route: Treasured)',	'2025-09-11 11:42:14'),
(50,	10,	'Added 1 member(s) to cohort ID: 5',	'2025-09-11 11:42:24'),
(51,	10,	'Added 1 member(s) to cohort ID: 5',	'2025-09-11 11:43:20'),
(52,	10,	'Finalised cohort ID: 5',	'2025-09-11 11:43:35'),
(53,	10,	'Processed membership for Beneficiary 22',	'2025-09-11 11:58:38'),
(54,	10,	'Generated MembershipID VSI/1/2025/09/0003 for Beneficiary 23',	'2025-09-11 12:22:11'),
(55,	10,	'Processed membership for Beneficiary 23',	'2025-09-11 12:22:12'),
(56,	10,	'Created enquiry for Christopher Frozne with MembershipID ',	'2025-09-11 12:33:23'),
(57,	10,	'Generated MembershipID VSI/1/2025/09/0004 for Beneficiary 24',	'2025-09-29 09:36:54'),
(58,	10,	'Processed membership for Beneficiary 24',	'2025-09-29 09:36:55');

CREATE TABLE `Beneficiary` (
  `BeneficiaryID` int(11) NOT NULL AUTO_INCREMENT,
  `FullName` varchar(100) NOT NULL,
  `DateOfBirth` date DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  `Email` varchar(100) NOT NULL,
  `Address` text NOT NULL,
  `Category` varchar(50) DEFAULT NULL,
  `MembershipStatus` tinyint(1) DEFAULT 0,
  `MembershipStartDate` date DEFAULT NULL,
  `MembershipApplicationDate` date DEFAULT NULL,
  `MembershipPhase` varchar(11) DEFAULT NULL,
  `BusinessAddress` text DEFAULT NULL,
  `PhotoIDType` varchar(50) DEFAULT NULL,
  `PhotoIDAttachments` text DEFAULT NULL,
  `EnquiryID` bigint(20) unsigned DEFAULT NULL,
  `LoanID` bigint(20) unsigned DEFAULT NULL,
  `ChapterID` bigint(20) unsigned DEFAULT NULL,
  `MembershipID` varchar(50) DEFAULT NULL,
  `CreatedBy` bigint(20) unsigned DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `MembershipClass` varchar(20) DEFAULT NULL,
  `NextOfKinName` varchar(100) DEFAULT NULL,
  `NextOfKinTitle` varchar(100) DEFAULT NULL,
  `NextOfKinPhone` varchar(20) DEFAULT NULL,
  `NextOfKinAddress` text DEFAULT NULL,
  `NextOfKinOccupation` varchar(100) DEFAULT NULL,
  `FeePaid` tinyint(1) DEFAULT 0,
  `FeeAmount` decimal(10,2) DEFAULT NULL,
  `PaymentConfirmation` varchar(100) DEFAULT NULL,
  `BeneficiaryPhotoLink` text DEFAULT NULL,
  `MembershipFormLink` text DEFAULT NULL,
  PRIMARY KEY (`BeneficiaryID`),
  KEY `CreatedBy` (`CreatedBy`),
  KEY `ChapterID` (`ChapterID`),
  KEY `MembershipID` (`MembershipID`),
  CONSTRAINT `BeneficiaryEnquiry_ibfk_1` FOREIGN KEY (`CreatedBy`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `BeneficiaryEnquiry_ibfk_2` FOREIGN KEY (`ChapterID`) REFERENCES `Chapters` (`ChapterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `Beneficiary` (`BeneficiaryID`, `FullName`, `DateOfBirth`, `PhoneNumber`, `Email`, `Address`, `Category`, `MembershipStatus`, `MembershipStartDate`, `MembershipApplicationDate`, `MembershipPhase`, `BusinessAddress`, `PhotoIDType`, `PhotoIDAttachments`, `EnquiryID`, `LoanID`, `ChapterID`, `MembershipID`, `CreatedBy`, `CreatedAt`, `MembershipClass`, `NextOfKinName`, `NextOfKinTitle`, `NextOfKinPhone`, `NextOfKinAddress`, `NextOfKinOccupation`, `FeePaid`, `FeeAmount`, `PaymentConfirmation`, `BeneficiaryPhotoLink`, `MembershipFormLink`) VALUES
(22,	'Iron Man',	NULL,	'07394749273',	'realtony@gmail.com',	'50 Partridge Way\nChadderton',	'Low Income',	1,	'2025-09-11',	NULL,	'processed',	'TOP House, Shawheath Close',	'Passport',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1757591917_org-logo.png',	22,	NULL,	1,	'VSI/1/2025/09/0001',	10,	'2025-09-11 11:30:29',	'Treasured',	'Chris NextKine',	'Mrs',	'087961507501',	'50 Partridge Avenue',	'Nurse',	1,	3000.00,	'slip',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/loan-applications/loan_photo_68da5560f402f_cross.jpeg',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1757591918_a5d9124b44564a16463acdcde8ee65e218cc.pdf'),
(23,	'Sokka Bridgewater',	NULL,	'08012345678',	'themooniscool@outlook.com',	'The moon',	'Low Income',	1,	'2025-09-11',	NULL,	'processed',	'The moon',	'Passport',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/beneficiary-forms/1757593331_program-logo.png',	23,	NULL,	1,	'VSI/1/2025/09/0003',	10,	'2025-09-11 11:40:51',	'Treasured',	'Chris NextKine',	'Mrs',	'08012345678',	'50 Partridge Avenue',	'Nurse',	1,	3000.00,	'slip',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-applications/loan_photo_68da598fa62f8_1757343610_org-logo.png',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/beneficiary-forms/1757593331_LW_Secured_Loan_Application.pdf'),
(24,	'Christopher Frozne',	NULL,	'08012345678',	'frozone@outlook.co.uk',	'239 Creed Road, Abuja',	'Low Income,Abandoned',	1,	'2025-09-29',	NULL,	'processed',	'The moon',	'Passport',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0004/beneficiary-forms/1759138614_Church_Nudge.png',	24,	NULL,	1,	'VSI/1/2025/09/0004',	10,	'2025-09-11 12:33:23',	'Treasured',	'Chris NextKine',	'Mrs',	'08012345678',	'50 Partridge Avenue',	'Nurse',	1,	3000.00,	'slip',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0004/beneficiary-forms/1759138615_BarracudaCentral.org_-_Technical_Insight_for_Security_Pros.pdf');

CREATE TABLE `BeneficiaryEnquiry` (
  `EnquiryID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) NOT NULL,
  `ContactDate` date NOT NULL,
  `ContactTime` time NOT NULL,
  `ContactMethod` varchar(20) DEFAULT NULL,
  `DiscoveryMethod` varchar(100) DEFAULT NULL,
  `EnquiryType` varchar(50) DEFAULT NULL,
  `EnquiryDescription` text DEFAULT NULL,
  `AdditionalNotes` text DEFAULT NULL,
  `RouteSelected` varchar(20) DEFAULT NULL,
  `ScannedFormLinkId` text DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `Status` enum('Pending','Processed') DEFAULT 'Pending',
  PRIMARY KEY (`EnquiryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `BeneficiaryEnquiry` (`EnquiryID`, `BeneficiaryID`, `ContactDate`, `ContactTime`, `ContactMethod`, `DiscoveryMethod`, `EnquiryType`, `EnquiryDescription`, `AdditionalNotes`, `RouteSelected`, `ScannedFormLinkId`, `CreatedAt`, `Status`) VALUES
(22,	22,	'2025-09-11',	'12:28:00',	'Visit',	'Email',	'New business',	'New Description',	'Additional Notes: New Notes\n',	'Treasured',	'35',	'2025-09-11 11:30:29',	'Processed'),
(23,	23,	'2025-09-09',	'11:11:00',	'',	'Friend',	'Other',	'General Enquiry',	'I have a note',	'Treasured',	'36',	'2025-09-11 11:40:51',	'Pending'),
(24,	24,	'2025-09-09',	'15:04:00',	'Visit',	'Friend',	'General enquiry',	'Happy descitptioin',	'New notes',	'Treasured',	'41',	'2025-09-11 12:33:23',	'Pending');

CREATE TABLE `BeneficiarySavings` (
  `SavingsID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `DepositDate` date NOT NULL,
  `AmountDeposited` decimal(10,2) DEFAULT NULL,
  `LinkedToRepayment` tinyint(1) DEFAULT 1,
  `TellerReference` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`SavingsID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  CONSTRAINT `BeneficiarySavings_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `Chapters` (
  `ChapterID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ChapterName` varchar(100) NOT NULL,
  `Region` varchar(100) DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`ChapterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `Chapters` (`ChapterID`, `ChapterName`, `Region`, `CreatedAt`) VALUES
(1,	'Lagos Chapter',	'South West',	'2025-08-01 12:14:44'),
(2,	'Owerri Chapter',	'South East',	'2025-08-01 12:14:44'),
(3,	'Ibadan Chapter',	'South West',	'2025-08-01 12:14:44'),
(4,	'Port Harcourt',	'South South',	'2025-08-01 12:14:44'),
(5,	'Accra Chapter',	'West Africa',	'2025-08-01 12:14:44');

CREATE TABLE `Cohort` (
  `CohortID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Description` text DEFAULT NULL,
  `CreatedAt` timestamp NULL DEFAULT current_timestamp(),
  `CreatedBy` bigint(20) unsigned DEFAULT NULL,
  `ChapterID` bigint(20) unsigned DEFAULT NULL,
  `RouteSelected` varchar(20) NOT NULL,
  `Finalised` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`CohortID`),
  KEY `CreatedBy` (`CreatedBy`),
  KEY `ChapterID` (`ChapterID`),
  CONSTRAINT `Cohort_ibfk_1` FOREIGN KEY (`CreatedBy`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `Cohort_ibfk_2` FOREIGN KEY (`ChapterID`) REFERENCES `Chapters` (`ChapterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `Cohort` (`CohortID`, `Name`, `Description`, `CreatedAt`, `CreatedBy`, `ChapterID`, `RouteSelected`, `Finalised`) VALUES
(5,	'September-2025 Cohort',	'New Treasured Cohort',	'2025-09-11 11:42:14',	10,	1,	'Treasured',	1);

CREATE TABLE `CohortMembers` (
  `CohortMemberID` int(11) NOT NULL AUTO_INCREMENT,
  `CohortID` int(11) NOT NULL,
  `BeneficiaryID` int(11) NOT NULL,
  `AddedAt` timestamp NULL DEFAULT current_timestamp(),
  `AddedBy` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`CohortMemberID`),
  KEY `CohortID` (`CohortID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  KEY `AddedBy` (`AddedBy`),
  CONSTRAINT `CohortMembers_ibfk_1` FOREIGN KEY (`CohortID`) REFERENCES `Cohort` (`CohortID`),
  CONSTRAINT `CohortMembers_ibfk_2` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`),
  CONSTRAINT `CohortMembers_ibfk_3` FOREIGN KEY (`AddedBy`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `CohortMembers` (`CohortMemberID`, `CohortID`, `BeneficiaryID`, `AddedAt`, `AddedBy`) VALUES
(12,	5,	22,	'2025-09-11 11:42:24',	10),
(13,	5,	23,	'2025-09-11 11:43:20',	10);

CREATE TABLE `finance_benevolence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `beneficiary_name` varchar(255) NOT NULL,
  `requested_by_name` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'Pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `requisition_id` varchar(64) DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `scanned_form_link` varchar(255) DEFAULT NULL,
  `approved_by` varchar(255) DEFAULT NULL,
  `rejected_by` varchar(255) DEFAULT NULL,
  `approval_date` datetime DEFAULT NULL,
  `rejection_date` datetime DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `authorised_by_name` varchar(255) DEFAULT NULL,
  `chapter_id` int(11) DEFAULT NULL,
  `recipient_position` varchar(255) DEFAULT NULL,
  `cheques_payable_to` varchar(255) DEFAULT NULL,
  `paid_status` enum('Pending','Paid') DEFAULT 'Pending',
  `paid_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_benevolence` (`id`, `user_id`, `beneficiary_name`, `requested_by_name`, `amount`, `status`, `created_at`, `requisition_id`, `file_url`, `scanned_form_link`, `approved_by`, `rejected_by`, `approval_date`, `rejection_date`, `reason`, `authorised_by_name`, `chapter_id`, `recipient_position`, `cheques_payable_to`, `paid_status`, `paid_at`) VALUES
(1,	10,	'Benevolence  Name',	'',	20000.00,	'Pending',	'2025-10-04 10:29:02',	'Benevolence/2025/10/1/10/1',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759573742_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759573742_Sample-Deed-of-Guarantee.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	NULL,	NULL,	'Pending',	NULL),
(2,	10,	'Benevolence  Name2',	'',	50000.00,	'Pending',	'2025-10-04 10:33:30',	'Benevolence/2025/10/1/10/2',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574010_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574010_Blank-Guarantor-Form.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	NULL,	NULL,	'Pending',	NULL),
(3,	10,	'Benevolence  Name2',	'',	50000.00,	'Pending',	'2025-10-04 10:33:30',	'Benevolence/2025/10/1/10/3',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574010_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574010_Blank-Guarantor-Form.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Leader2',	'Market LEader2',	'Pending',	NULL),
(4,	10,	'Benevolence  Name 3',	'',	100000.00,	'Pending',	'2025-10-04 10:35:02',	'Benevolence/2025/10/1/10/4',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574102_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574102_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	NULL,	NULL,	'Pending',	NULL),
(5,	10,	'Benevolence  Name 3',	'',	100000.00,	'Pending',	'2025-10-04 10:35:02',	'Benevolence/2025/10/1/10/5',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574102_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574102_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Leader 3',	'Market Leader 3',	'Pending',	NULL),
(6,	10,	'Benevolence  Name 4',	'',	5000.00,	'Pending',	'2025-10-04 10:37:33',	'Benevolence/2025/10/1/10/6',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574252_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574252_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Leader 4',	'Market LEader 4',	'Pending',	NULL),
(7,	10,	'Benevolence  Name 5',	'',	34000.00,	'Pending',	'2025-10-04 10:39:26',	'Benevolence/2025/10/1/10/7',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574365_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574365_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Leader 5',	'Market LEader 5',	'Pending',	NULL),
(8,	10,	'Benevolence  Name 6',	'',	7890.00,	'Pending',	'2025-10-04 10:41:05',	'Benevolence/2025/10/1/10/8',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574465_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574465_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Leader 6',	'Market LEader 6',	'Pending',	NULL),
(9,	10,	'Benevolence  Name 7',	'Jane Lagos',	7000.00,	'Rejected',	'2025-10-04 10:42:27',	'Benevolence/2025/10/1/10/9',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574546_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574546_Sample-Deed-of-Guarantee.pdf',	NULL,	'4',	NULL,	'2025-10-04 10:49:13',	'Not accepted',	'Ahmed Coordinator',	1,	'Leader 7',	'Market LEader 7',	'Pending',	NULL),
(10,	10,	'Benevolence  Name 8',	'Jane Lagos',	8900.00,	'Approved',	'2025-10-04 10:44:42',	'Benevolence/2025/10/1/10/10',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574682_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574682_Blank-Guarantor-Form.pdf',	'4',	NULL,	'2025-10-04 10:48:32',	NULL,	'Approved',	'Ahmed Coordinator',	1,	'Leader 8',	'Market LEader 8',	'Pending',	NULL);

CREATE TABLE `finance_chapter_budget` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `financial_year` varchar(7) NOT NULL COMMENT 'Format: 2024-25',
  `category_id` int(11) NOT NULL,
  `budget_allocated` decimal(12,2) NOT NULL DEFAULT 0.00,
  `budget_spent` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_budget` (`chapter_id`,`financial_year`,`category_id`),
  KEY `idx_financial_year` (`financial_year`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_category_id` (`category_id`),
  CONSTRAINT `finance_chapter_budget_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `finance_chapter_budget_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `finance_expense_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `finance_chapter_budget` (`id`, `chapter_id`, `financial_year`, `category_id`, `budget_allocated`, `budget_spent`, `created_at`, `updated_at`) VALUES
(1,	1,	'2025-26',	3,	50000.00,	0.00,	'2025-10-08 00:18:48',	'2025-10-08 00:18:48');

CREATE TABLE `finance_chapter_income` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `financial_year` varchar(7) NOT NULL COMMENT 'Format: 2025-26',
  `income_number` varchar(100) NOT NULL,
  `income_category` enum('DONATION','FORM SALE','REFUNDS','CLOTH SALES','REPAYMENT','OTHERS') NOT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `income_date` date NOT NULL,
  `payment_method` enum('CASH','CHEQUE','BANK TRANSFER','UPI','CARD','OTHERS') DEFAULT NULL,
  `payment_reference` varchar(255) DEFAULT NULL,
  `received_by` varchar(255) DEFAULT NULL,
  `receipt_number` varchar(100) DEFAULT NULL,
  `supporting_document` text DEFAULT NULL,
  `verified_by` bigint(20) unsigned DEFAULT NULL,
  `verification_status` enum('PENDING','VERIFIED','REJECTED') DEFAULT 'PENDING',
  `verification_date` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_financial_year` (`financial_year`),
  KEY `idx_income_category` (`income_category`),
  KEY `idx_income_date` (`income_date`),
  KEY `idx_verification_status` (`verification_status`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_verified_by` (`verified_by`),
  CONSTRAINT `finance_chapter_income_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `finance_chapter_income_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `Users` (`UserID`) ON DELETE CASCADE,
  CONSTRAINT `finance_chapter_income_ibfk_3` FOREIGN KEY (`verified_by`) REFERENCES `Users` (`UserID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_chapter_income` (`id`, `chapter_id`, `financial_year`, `income_number`, `income_category`, `description`, `amount`, `income_date`, `payment_method`, `payment_reference`, `received_by`, `receipt_number`, `supporting_document`, `verified_by`, `verification_status`, `verification_date`, `created_by`, `created_at`, `updated_at`) VALUES
(1,	1,	'2025-26',	'',	'FORM SALE',	'mcy',	67000.00,	'2025-10-08',	'CHEQUE',	'xcfg',	'',	'',	NULL,	NULL,	'PENDING',	NULL,	12,	'2025-10-08 09:33:35',	'2025-10-08 09:33:35'),
(2,	1,	'2025-26',	'',	'FORM SALE',	'mcy',	67000.00,	'2025-10-08',	'CHEQUE',	'xcfg',	'',	'',	NULL,	12,	'VERIFIED',	'2025-10-08 09:42:33',	12,	'2025-10-08 09:33:36',	'2025-10-08 09:42:33'),
(3,	1,	'2025-26',	'',	'REFUNDS',	'ygvjh',	70000.00,	'2025-10-08',	'CHEQUE',	'',	'Jane Lagos',	'jgyj6',	NULL,	12,	'VERIFIED',	'2025-10-08 09:50:11',	12,	'2025-10-08 09:49:56',	'2025-10-08 09:50:11'),
(4,	1,	'2025-26',	'',	'FORM SALE',	'drtnrb',	78555.00,	'2025-10-08',	'BANK TRANSFER',	'e',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759917062_Blank-Guarantor-Form.pdf',	NULL,	'PENDING',	NULL,	12,	'2025-10-08 09:51:02',	'2025-10-08 09:51:02'),
(5,	1,	'2025-26',	'',	'FORM SALE',	'drtnrb',	78555.00,	'2025-10-08',	'BANK TRANSFER',	'e',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759917062_Blank-Guarantor-Form.pdf',	NULL,	'PENDING',	NULL,	12,	'2025-10-08 09:51:02',	'2025-10-08 09:51:02'),
(6,	1,	'2025-26',	'INCOME/1/2025/0006',	'FORM SALE',	'Jane sales',	30000.00,	'2025-10-08',	'CASH',	'adas',	'Jane Lagos',	'drtc',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759920922_Sample-Deed-of-Guarantee.pdf',	NULL,	'PENDING',	NULL,	10,	'2025-10-08 10:55:22',	'2025-10-08 10:55:22'),
(7,	1,	'2025-26',	'INCOME/1/2025/0007',	'CLOTH SALES',	'Old lcothes',	45000.00,	'2025-10-01',	'UPI',	'',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759921101_Sample-Deed-of-Guarantee.pdf',	12,	'VERIFIED',	'2025-10-08 11:03:06',	12,	'2025-10-08 10:58:21',	'2025-10-08 11:03:06'),
(8,	1,	'2025-26',	'INCOME/1/2025/0008',	'CLOTH SALES',	'Old clothes',	45000.00,	'2025-10-01',	'CASH',	'l,nk',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759921189_Guarantor-Agreement.pdf',	NULL,	'PENDING',	NULL,	10,	'2025-10-08 10:59:49',	'2025-10-08 10:59:49'),
(9,	1,	'2025-26',	'INCOME/1/2025/0009',	'CLOTH SALES',	'Old clothes',	45000.00,	'2025-10-01',	'CASH',	'l,nk',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759921189_Guarantor-Agreement.pdf',	NULL,	'PENDING',	NULL,	10,	'2025-10-08 10:59:49',	'2025-10-08 10:59:49'),
(10,	1,	'2025-26',	'INCOME/1/2025/0010',	'CLOTH SALES',	'Old clothes',	45000.00,	'2025-10-01',	'CASH',	'l,nk',	'Sharon Guest',	'',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759921328_Guarantor-Agreement.pdf',	NULL,	'PENDING',	NULL,	10,	'2025-10-08 11:02:08',	'2025-10-08 11:02:08'),
(11,	1,	'2025-26',	'INCOME/1/2025/0011',	'REPAYMENT',	'Loan Repayment - Agreement ID: 4',	5000.00,	'2025-10-02',	'BANK TRANSFER',	'sfsdrg',	'Loan Officer',	NULL,	NULL,	12,	'VERIFIED',	'2025-10-08 13:53:19',	10,	'2025-10-08 13:50:53',	'2025-10-08 13:53:19');

CREATE TABLE `finance_donors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `donor_name` varchar(255) NOT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `donation_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `purpose` varchar(255) DEFAULT NULL,
  `requisition_id` varchar(64) DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `scanned_form_link` varchar(255) DEFAULT NULL,
  `submitted_by` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `chapter_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_donors` (`id`, `donor_name`, `contact`, `amount`, `donation_date`, `notes`, `purpose`, `requisition_id`, `file_url`, `scanned_form_link`, `submitted_by`, `user_id`, `created_at`, `chapter_id`) VALUES
(1,	'Donor Name',	'0803782742832 | ben1@lagos.com',	50000.00,	'2025-10-01',	'Note donation',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	NULL),
(2,	'Donor Name',	'0803782742832 | tom@hausa.com',	50000.00,	'2025-10-01',	'NOtes donations',	'Office Desk',	'Donation/2025/10/1/10/2',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759588310_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759588310_LW_Secured_Loan_Application.pdf',	NULL,	10,	'2025-10-04 14:31:51',	1),
(3,	'Donor Name 2',	'0803782742834 | sharo@vsi.org',	56000.00,	'2025-10-01',	'donation',	'Sit stand Desk',	'Donation/2025/10/1/10/3',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759588499_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759588499_Guarantor-Agreement.pdf',	NULL,	10,	'2025-10-04 14:35:00',	1),
(4,	'Donor Name 3',	'0803782742832 | ben1@lagos.com',	98000.00,	'2025-12-01',	'Hello',	'Loan',	'Donation/2025/10/1/10/4',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589610_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589610_Sample-Deed-of-Guarantee.pdf',	NULL,	10,	'2025-10-04 14:53:30',	1),
(5,	'Donor Name 3',	'0803782742832 | ben1@lagos.com',	98000.00,	'2025-12-01',	'Hello',	'Loan',	'Donation/2025/10/1/10/5',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589609_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589609_Sample-Deed-of-Guarantee.pdf',	NULL,	10,	'2025-10-04 14:53:30',	1),
(6,	'Donor Name 4',	'0803782742834 | lagos4@lagos.com',	40000.00,	'2025-10-04',	'note fom donors',	'Loan support',	'Donation/2025/10/1/10/6',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589798_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589798_Blank-Guarantor-Form.pdf',	NULL,	10,	'2025-10-04 14:56:40',	1),
(7,	'Donor Name 5',	'0803782742835 | tom@hausa.com',	56000.00,	'2025-10-01',	'5 Notes',	'Office Desk 5',	'Donation/2025/10/1/10/7',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590157_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590157_Sample-Deed-of-Guarantee.pdf',	NULL,	10,	'2025-10-04 15:02:40',	1),
(8,	'Donor Name 6',	'0803782742836 | ben6@lagos.com',	60000.00,	'2025-12-06',	'6 donors',	'Office Desk 6',	'Donation/2025/10/1/10/8',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590449_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590449_LW_Secured_Loan_Application.pdf',	NULL,	10,	'2025-10-04 15:07:29',	1),
(9,	'Donor Name 7',	'0803782742837 | lagos1@lagos.com',	70000.00,	'2025-10-07',	'Donors 7',	'Office Desk 7',	'Donation/2025/10/1/10/9',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759591917_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759591917_Blank-Guarantor-Form.pdf',	NULL,	10,	'2025-10-04 15:32:01',	1),
(10,	'Donor Name 8',	'08037827428328 | lagos1@lagos.com',	80000.00,	'2025-09-08',	'8 donors',	'Office Desk 8',	'Donation/2025/10/1/10/10',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759592426_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759592426_Blank-Guarantor-Form.pdf',	'',	10,	'2025-10-04 15:40:28',	1),
(11,	'Donor Name',	'08037827428328 | tom@hausa.com',	120000.00,	'2025-10-02',	'nxcgb v',	'Office Desk 7s',	'Donation/2025/10/1/10/11',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759603387_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759603387_Sample-Deed-of-Guarantee.pdf',	'',	10,	'2025-10-04 18:43:08',	1),
(12,	'Donor Name',	'0803782742832 | lagos1@lagos.com',	30000.00,	'2025-10-01',	'vsrvf',	'Loan',	'Donation/2025/10/1/10/12',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604357_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604357_Blank-Guarantor-Form.pdf',	'',	10,	'2025-10-04 18:59:20',	1),
(13,	'Donor Name 3',	'0803782742832 | tom@hausa.com',	789000.00,	'2024-12-01',	'loj',	'New chair',	'Donation/2025/10/1/10/13',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604922_Blank-Guarantor-Form.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604922_Blank-Guarantor-Form.pdf',	'',	10,	'2025-10-04 19:08:42',	1),
(14,	'Donor Name 3',	'08037827428328 | tom@hausa.com',	15890.00,	'2025-09-30',	'h98ohij',	'Loan',	'Donation/2025/10/1/10/14',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605395_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605395_Guarantor-Agreement.pdf',	'',	10,	'2025-10-04 19:16:36',	1),
(15,	'Donor Name 4',	'0803782742835 | lagos2@lagos.com',	87000.00,	'2025-09-10',	'bdryfb',	'Sit stand Desk',	'Donation/2025/10/1/10/15',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605545_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605545_Sample-Deed-of-Guarantee.pdf',	'',	10,	'2025-10-04 19:19:06',	1),
(16,	'Donor Name 30',	'0803782742835 | lagos4@lagos.com',	890000.00,	'2025-09-29',	'sbrt5bd',	'New chair yt',	'Donation/2025/10/1/10/16',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605693_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605693_LW_Secured_Loan_Application.pdf',	'Jane Lagos',	10,	'2025-10-04 19:21:33',	1),
(17,	'Donor Name 50',	'08037827428350 | lagos4@lagos.com',	5000000.00,	'2025-08-01',	'50th',	'New chair Go 50',	'Donation/2025/10/1/10/17',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606019_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606019_LW_Secured_Loan_Application.pdf',	'Jane Lagos',	10,	'2025-10-04 19:27:00',	1),
(18,	'Donor Name 200',	'08037827428328 | tom@hausa.com',	7856453.00,	'2023-09-09',	'Notes',	'Sit stand Desk',	'Donation/2025/10/1/10/18',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606249_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606249_Sample-Deed-of-Guarantee.pdf',	'Jane Lagos',	10,	'2025-10-04 19:30:51',	1),
(19,	'Donor Name 2005',	'080378274283505 | ben1@lagos.com',	64000.00,	'2025-09-30',	'hfygbf',	'New chair',	'Donation/2025/10/1/10/19',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606369_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606369_Sample-Deed-of-Guarantee.pdf',	'Jane Lagos',	10,	'2025-10-04 19:32:49',	1);

CREATE TABLE `finance_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_number` varchar(50) NOT NULL,
  `date` date NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` text NOT NULL,
  `category_id` int(11) NOT NULL,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `project_id` varchar(100) DEFAULT NULL,
  `payment_method` enum('CASH','CHEQUE','BANK TRANSFER','UPI','CARD') NOT NULL,
  `payment_reference` varchar(100) DEFAULT NULL,
  `scanned_form_link` varchar(500) DEFAULT NULL,
  `approval_status` enum('PENDING','APPROVED','AUTHORISED','REJECTED') DEFAULT 'PENDING',
  `submitted_by` bigint(20) unsigned NOT NULL,
  `submitted_by_name` varchar(100) NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_by_name` varchar(100) DEFAULT NULL,
  `approved_date` 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 `expense_number` (`expense_number`),
  KEY `idx_expense_number` (`expense_number`),
  KEY `idx_date` (`date`),
  KEY `idx_approval_status` (`approval_status`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_submitted_by` (`submitted_by`),
  KEY `idx_approved_by` (`approved_by`),
  KEY `idx_date_chapter` (`date`,`chapter_id`),
  CONSTRAINT `finance_expenses_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `finance_expense_categories` (`id`),
  CONSTRAINT `finance_expenses_ibfk_2` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `finance_expenses_ibfk_4` FOREIGN KEY (`submitted_by`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `finance_expenses_ibfk_5` FOREIGN KEY (`approved_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `finance_expenses` (`id`, `expense_number`, `date`, `amount`, `description`, `category_id`, `chapter_id`, `project_id`, `payment_method`, `payment_reference`, `scanned_form_link`, `approval_status`, `submitted_by`, `submitted_by_name`, `approved_by`, `approved_by_name`, `approved_date`, `created_at`, `updated_at`) VALUES
(18,	'ExpenseClaims/2025/10/1/10/10',	'2025-10-01',	10000.00,	'szvr',	14,	1,	NULL,	'BANK TRANSFER',	'b4tes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759853351_Blank-Guarantor-Form.pdf',	'APPROVED',	10,	'Jane Lagos',	4,	'Ahmed Coordinator',	'2025-10-04 09:52:44',	'2025-10-07 16:09:11',	'2025-10-07 16:09:11'),
(19,	'EXP/2025/10/7/1/2',	'2025-10-07',	5699.00,	'jyfjy',	6,	1,	'',	'CHEQUE',	'vmvjm',	NULL,	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 19:03:09',	'2025-10-07 21:19:57'),
(20,	'Expenses/2025/10/7/1/3',	'2025-09-30',	40000.00,	'gTesli',	7,	1,	'',	'CASH',	'dasd',	'finances/expenses/2025/1759871271_LW_Secured_Loan_Application.pdf',	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 21:07:51',	'2025-10-07 21:22:24'),
(21,	'Expenses/2025/10/7/1/4',	'2025-10-07',	7869.00,	'dcc f',	5,	1,	'',	'CHEQUE',	' cfh',	'finances/expenses/2025/1759872788_Blank-Guarantor-Form.pdf',	'PENDING',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 21:33:08',	'2025-10-07 21:33:08'),
(22,	'Expenses/2025/10/7/1/5',	'2025-10-07',	34000.00,	'Chair',	11,	1,	'',	'BANK TRANSFER',	'ksdjc sd',	'finances/expenses/2025/1759873057_Blank-Guarantor-Form.pdf',	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 21:37:37',	'2025-10-07 21:37:37'),
(23,	'Expenses/2025/10/7/1/6',	'2025-10-07',	9000.00,	'Press',	14,	1,	'',	'BANK TRANSFER',	'escrfxdrv',	NULL,	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 22:00:56',	'2025-10-07 22:00:56'),
(24,	'Expenses/2025/10/7/1/7',	'2025-10-07',	56789.00,	'r dxcfg',	4,	1,	'',	'CASH',	' xcfg',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759875724_Sample-Deed-of-Guarantee.pdf',	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 22:22:04',	'2025-10-07 22:22:04'),
(25,	'Expenses/2025/10/7/1/8',	'2025-10-07',	56000.00,	'f yvg',	9,	1,	'',	'BANK TRANSFER',	' c',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759877446_LW_Secured_Loan_Application.pdf',	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 22:50:46',	'2025-10-07 22:50:46'),
(26,	'Expenses/2025/10/7/1/9',	'2025-10-07',	545353.00,	'sr vxdg',	4,	1,	'',	'CASH',	'r xv',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759880055_Sample-Deed-of-Guarantee.pdf',	'AUTHORISED',	12,	'',	NULL,	NULL,	NULL,	'2025-10-07 23:34:15',	'2025-10-07 23:34:15'),
(27,	'PAYROLL/2025/10/4584/CH3',	'2025-10-08',	160200.00,	'Staff wages for October 2025 - 2 staff members: John Coord, Finance Lagos',	8,	3,	NULL,	'BANK TRANSFER',	NULL,	NULL,	'AUTHORISED',	4,	'Ahmed Coordinator',	4,	'Ahmed Coordinator',	'2025-10-08 13:09:51',	'2025-10-08 13:09:51',	'2025-10-08 13:09:51'),
(28,	'LOANS/PAYOUT/5/',	'2025-10-02',	21000.00,	'Loan Payout - PayoutID: 4, Loan Application: 13',	1,	1,	NULL,	'CHEQUE',	'cheque issued',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan_payouts/1759933111_Blank-Guarantor-Form.pdf',	'AUTHORISED',	10,	'Jane Lagos',	NULL,	NULL,	NULL,	'2025-10-08 14:18:31',	'2025-10-08 14:18:31');

CREATE TABLE `finance_expenses_claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `receipt_attached` enum('Yes','No') DEFAULT 'No',
  `receipt_url` varchar(255) DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'Pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `requisition_id` varchar(64) DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `scanned_form_link` varchar(255) DEFAULT NULL,
  `approved_by` varchar(255) DEFAULT NULL,
  `rejected_by` varchar(255) DEFAULT NULL,
  `approval_date` datetime DEFAULT NULL,
  `rejection_date` datetime DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `authorised_by_name` varchar(100) DEFAULT NULL,
  `chapter_id` int(11) DEFAULT NULL,
  `paid_status` enum('Pending','Paid') DEFAULT 'Pending',
  `paid_at` datetime DEFAULT NULL,
  `cheques_payable_to` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_expenses_claims` (`id`, `user_id`, `description`, `amount`, `receipt_attached`, `receipt_url`, `status`, `created_at`, `requisition_id`, `file_url`, `scanned_form_link`, `approved_by`, `rejected_by`, `approval_date`, `rejection_date`, `reason`, `authorised_by_name`, `chapter_id`, `paid_status`, `paid_at`, `cheques_payable_to`) VALUES
(1,	10,	'Print',	123124.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'Pending',	'2025-10-03 15:53:14',	'ExpenseClaims/2025/10/1/10/1',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(2,	10,	'Market',	8633.00,	'No',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'Pending',	'2025-10-03 15:53:14',	'ExpenseClaims/2025/10/1/10/1',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(3,	10,	'Print',	123124.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'Pending',	'2025-10-03 15:54:32',	'ExpenseClaims/2025/10/1/10/3',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(4,	10,	'Market',	8633.00,	'No',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'Pending',	'2025-10-03 15:54:33',	'ExpenseClaims/2025/10/1/10/3',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(5,	10,	'Print',	123124.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'Rejected',	'2025-10-03 15:56:55',	'ExpenseClaims/2025/10/1/10/5',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	NULL,	'4',	NULL,	'2025-10-04 10:00:55',	'not good',	'Ahmed Coordinator',	1,	'Pending',	NULL,	'Jane Lagos'),
(6,	10,	'Market',	8633.00,	'No',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'Rejected',	'2025-10-03 15:56:55',	'ExpenseClaims/2025/10/1/10/5',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	NULL,	'4',	NULL,	'2025-10-04 10:00:55',	'not good',	'Ahmed Coordinator',	1,	'Pending',	NULL,	'Jane Lagos'),
(7,	10,	'gvbjh',	10000.00,	'Yes',	'',	'Pending',	'2025-10-03 15:57:28',	'ExpenseClaims/2025/10/1/10/7',	'',	'',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(8,	10,	'gvbjh',	10000.00,	'Yes',	'',	'Approved',	'2025-10-03 15:59:47',	'ExpenseClaims/2025/10/1/10/8',	'',	'',	'4',	NULL,	'2025-10-04 09:50:25',	NULL,	'Good approval',	'',	1,	'Pending',	NULL,	'Jane Lagos'),
(9,	10,	'gvbjh',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507196_Guarantor-Agreement.pdf',	'Pending',	'2025-10-03 15:59:56',	'ExpenseClaims/2025/10/1/10/9',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507196_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507196_Guarantor-Agreement.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(10,	10,	'gvbjh',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507232_Guarantor-Agreement.pdf',	'Approved',	'2025-10-03 16:00:34',	'ExpenseClaims/2025/10/1/10/10',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507232_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507232_Guarantor-Agreement.pdf',	'4',	NULL,	'2025-10-04 09:52:44',	NULL,	'LEt\'s see',	'Ahmed Coordinator',	1,	'Paid',	'2025-10-07 16:09:11',	'Jane Lagos'),
(11,	10,	'Okay',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507254_Guarantor-Agreement.pdf',	'Approved',	'2025-10-03 16:00:57',	'ExpenseClaims/2025/10/1/10/11',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507254_Guarantor-Agreement.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507254_Guarantor-Agreement.pdf',	'4',	NULL,	'2025-10-04 09:57:03',	NULL,	'More info',	'Ahmed Coordinator',	1,	'Pending',	NULL,	'Jane Lagos'),
(12,	10,	'sevf',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507609_LW_Secured_Loan_Application.pdf',	'Approved',	'2025-10-03 16:06:49',	'ExpenseClaims/2025/10/1/10/12',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507609_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507609_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	'Great',	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(13,	10,	'sevf',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507937_LW_Secured_Loan_Application.pdf',	'Approved',	'2025-10-03 16:12:17',	'ExpenseClaims/2025/10/1/10/13',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507937_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507937_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	'Test',	NULL,	1,	'Pending',	NULL,	'Jane Lagos'),
(14,	10,	'I have go',	10000.00,	'Yes',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'Approved',	'2025-10-03 16:13:20',	'ExpenseClaims/2025/10/1/10/14',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'4',	NULL,	'2025-10-04 09:54:49',	NULL,	'Approved',	'Ahmed Coordinator',	1,	'Pending',	NULL,	'Jane Lagos'),
(15,	10,	'Bukka',	2000.00,	'No',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'Approved',	'2025-10-03 16:13:20',	'ExpenseClaims/2025/10/1/10/14',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'4',	NULL,	'2025-10-04 09:54:49',	NULL,	'Approved',	'Ahmed Coordinator',	1,	'Pending',	NULL,	'Jane Lagos');

DELIMITER ;;;

DELIMITER ;

CREATE TABLE `finance_expense_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `category_type` enum('PROGRAM','ADMIN','FUNDRAISING') NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `idx_category_type` (`category_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `finance_expense_categories` (`id`, `name`, `description`, `category_type`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
(1,	'DISBURSEMENT',	'Direct program disbursements to beneficiaries',	'PROGRAM',	1,	1,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(2,	'PRINTING/PHOTOCOPY/BINDINGS',	'Printing, photocopying and binding services',	'ADMIN',	1,	2,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(3,	'BANK CHARGES',	'Bank transaction fees and charges',	'ADMIN',	1,	3,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(4,	'TELEPHONE',	'Telephone and mobile communication expenses',	'ADMIN',	1,	4,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(5,	'TRANSPORT',	'Transportation and logistics',	'ADMIN',	1,	5,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(6,	'STATIONARIES',	'Office stationery supplies',	'ADMIN',	1,	6,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(7,	'UTILITY BILL',	'Electricity, water and other utilities',	'ADMIN',	1,	7,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(8,	'STAFF WAGES',	'Salaries and wages for staff',	'ADMIN',	1,	8,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(9,	'RENT',	'Office or facility rent',	'ADMIN',	1,	9,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(10,	'OFFICE SUPPLIES',	'General office supplies and materials',	'ADMIN',	1,	10,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(11,	'FURNITURE/EQUIPMENT',	'Furniture and equipment purchases',	'ADMIN',	1,	11,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(12,	'REPAIR/MAINTENANCE',	'Repairs and maintenance costs',	'ADMIN',	1,	12,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(13,	'RENTAL',	'Equipment or facility rentals',	'ADMIN',	1,	13,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(14,	'MEDIA/PUBLICITY',	'Marketing, media and publicity expenses',	'FUNDRAISING',	1,	14,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(15,	'TRAVEL',	'Travel expenses for staff and volunteers',	'ADMIN',	1,	15,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(16,	'REFRESHMENT',	'Refreshments for meetings and events',	'ADMIN',	1,	16,	'2025-10-04 23:06:39',	'2025-10-04 23:06:39'),
(17,	'Projects_Events',	'FUnds used to execute an events',	'PROGRAM',	1,	17,	'2025-10-04 23:40:31',	'2025-10-04 23:40:43');

CREATE TABLE `finance_payouts` (
  `payout_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `requisition_id` varchar(64) NOT NULL,
  `payout_date` date NOT NULL,
  `method` varchar(32) NOT NULL,
  `payment_details` varchar(255) NOT NULL,
  `note` text DEFAULT NULL,
  `paid_by_name` varchar(128) NOT NULL,
  `paid_by_id` varchar(32) NOT NULL,
  `payment_proof_url` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`payout_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_payouts` (`payout_id`, `requisition_id`, `payout_date`, `method`, `payment_details`, `note`, `paid_by_name`, `paid_by_id`, `payment_proof_url`, `created_at`, `updated_at`) VALUES
(18,	'ExpenseClaims/2025/10/1/10/10',	'2025-10-01',	'BANK TRANSFER',	'b4tes',	'4gvwser',	'FinanceBossLagos',	'12',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759853351_Blank-Guarantor-Form.pdf',	'2025-10-07 16:09:11',	NULL);

CREATE TABLE `finance_requisitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `purpose` varchar(255) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'Pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `requisition_id` varchar(64) DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `scanned_form_link` varchar(255) DEFAULT NULL,
  `approved_by` varchar(255) DEFAULT NULL,
  `rejected_by` varchar(255) DEFAULT NULL,
  `approval_date` datetime DEFAULT NULL,
  `rejection_date` datetime DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `chapter_id` int(11) DEFAULT NULL,
  `descriptions` text DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `cost` decimal(12,2) DEFAULT NULL,
  `authorised_by_name` varchar(100) DEFAULT NULL,
  `paid_status` enum('Pending','Paid') DEFAULT 'Pending',
  `paid_at` datetime DEFAULT NULL,
  `cheques_payable_to` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `finance_requisitions` (`id`, `user_id`, `purpose`, `status`, `created_at`, `requisition_id`, `file_url`, `scanned_form_link`, `approved_by`, `rejected_by`, `approval_date`, `rejection_date`, `reason`, `chapter_id`, `descriptions`, `quantity`, `price`, `cost`, `authorised_by_name`, `paid_status`, `paid_at`, `cheques_payable_to`) VALUES
(5,	10,	'Sit stand Desk',	'Pending',	'2025-10-03 11:34:58',	'2025/10/1/10/5',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759491297_LW_Secured_Loan_Application.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	1,	NULL,	NULL,	NULL,	NULL,	NULL,	'Pending',	NULL,	'Jane Lagos'),
(6,	10,	'Office',	'Pending',	'2025-10-03 11:40:24',	'2025/10/1/10/6',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759491624_Blank-Guarantor-Form.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	1,	NULL,	NULL,	NULL,	NULL,	NULL,	'Pending',	NULL,	'Jane Lagos'),
(7,	10,	'New chair Go',	'Approved',	'2025-10-03 12:30:42',	'Requisition/2025/10/1/10/7',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759494641_Blank-Guarantor-Form.pdf',	'4',	NULL,	'2025-10-03 15:03:31',	NULL,	'Good',	1,	'The boss',	3,	40000.00,	120000.00,	'Ahmed Coordinator',	'Pending',	NULL,	'Jane Lagos'),
(8,	2,	'Supervisor Chair',	'Rejected',	'2025-10-03 12:56:00',	'Requisition/2025/10/5/2/8',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759496160_Sample-Deed-of-Guarantee.pdf',	NULL,	'4',	NULL,	'2025-10-03 15:03:54',	'Not now',	5,	'The main man',	1,	20000.00,	20000.00,	'Ahmed Coordinator',	'Pending',	NULL,	'John Supervisor'),
(9,	10,	'Office Desk - Auto',	'Pending',	'2025-10-05 07:21:44',	'Requisition/2025/10/1/10/9',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759648903_Blank-Guarantor-Form.pdf',	NULL,	NULL,	NULL,	NULL,	NULL,	1,	'Let us see',	2,	50000.00,	100000.00,	NULL,	'Pending',	NULL,	'Jane Lagos');

DELIMITER ;;;

DELIMITER ;

CREATE TABLE `Guarantor` (
  `GuarantorID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `LoanApplicationID` varchar(50) DEFAULT NULL,
  `FullName` varchar(100) DEFAULT NULL,
  `RelationshipToBeneficiary` varchar(50) DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Occupation` varchar(100) DEFAULT NULL,
  `HomeAddress` text DEFAULT NULL,
  `OfficeAddress` text DEFAULT NULL,
  `NextOfKinName` varchar(100) DEFAULT NULL,
  `NextOfKinPhone` varchar(20) DEFAULT NULL,
  `IncomeVerificationDetails` text DEFAULT NULL,
  `GuaranteeAmount` decimal(10,2) DEFAULT NULL,
  `GuarantorFormLink` text DEFAULT NULL,
  `Title` varchar(100) DEFAULT NULL,
  `DateSigned` date DEFAULT NULL,
  `NextOfKinTitle` varchar(100) DEFAULT NULL,
  `NextOfKinEmail` varchar(100) DEFAULT NULL,
  `NextOfKinOccupation` varchar(100) DEFAULT NULL,
  `NextOfKinAddress` text DEFAULT NULL,
  `GuarantorHierarchy` int(11) DEFAULT NULL,
  PRIMARY KEY (`GuarantorID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  CONSTRAINT `Guarantor_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `Guarantor` (`GuarantorID`, `BeneficiaryID`, `LoanApplicationID`, `FullName`, `RelationshipToBeneficiary`, `PhoneNumber`, `Email`, `Occupation`, `HomeAddress`, `OfficeAddress`, `NextOfKinName`, `NextOfKinPhone`, `IncomeVerificationDetails`, `GuaranteeAmount`, `GuarantorFormLink`, `Title`, `DateSigned`, `NextOfKinTitle`, `NextOfKinEmail`, `NextOfKinOccupation`, `NextOfKinAddress`, `GuarantorHierarchy`) VALUES
(10,	22,	'VSI-Loan/1/2025/09/0001',	'Guarantor 1',	'Husband',	'08012345678',	'guarantor1@vsi.org',	'Teacher',	'239 Creed Road, Abuja',	'Chadderton',	'Next Kin 1',	'090123423477',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/loan-applications/guarantor1_form_68da58a9234cc',	'Mr',	'2025-09-27',	'Mr',	'NextKin1@vsi.org',	'Trader',	'50 Partridge Way',	1),
(11,	22,	'VSI-Loan/1/2025/09/0001',	'Guarantor 2',	'Friends',	'089898934845',	'guarantor2@vsi.org',	'Trader',	'23 Chaddesley Glen, Poole, UK',	'Top House',	'Next Kin 2',	'07286492273',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/loan-applications/guarantor2_form_68da58a9aba37',	'Mrs',	'2025-09-27',	'Mrs',	'NextKin2@vsi.org',	'Trader',	'TOP House, Shawheath Close',	2),
(12,	22,	'VSI-Loan/1/2025/09/0001',	'Guarantor 3',	'Friend',	'09038934785',	'guarantor3@vsi.org',	'Teacher',	'23 Chaddesley Glen, Poole, UK',	'Top House',	'Next Kin 3',	'06273521832',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/loan-applications/guarantor3_form_68da58aa6aa06',	'Mr',	'2025-09-28',	'Mrs',	'NextKin3@vsi.org',	'Singer',	'123 Faith Lane, London, UK',	3),
(13,	23,	'VSI-Loan/1/2025/09/0002',	'Guarantor 1',	'Husband',	'08012345678',	'guarantor1@vsi.org',	'Teacher',	'239 Creed Road, Abuja',	'Chadderton',	'Next Kin 1',	'090123423477',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-applications/guarantor1_form_68da5cf0a11ba_RTWChecklist.pdf',	'Mr',	'2025-09-27',	'Mr',	'NextKin1@vsi.org',	'Trader',	'50 Partridge Way',	1),
(14,	23,	'VSI-Loan/1/2025/09/0002',	'Guarantor 2',	'Friends',	'089898934845',	'guarantor2@vsi.org',	'Trader',	'23 Chaddesley Glen, Poole, UK',	'Top House',	'Next Kin 2',	'07286492273',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-applications/guarantor2_form_68da5cf127742_2022_Bookmatter_Industry40ForTheBuiltEnvironme.pdf',	'Mrs',	'2025-09-27',	'Mrs',	'NextKin2@vsi.org',	'Trader',	'TOP House, Shawheath Close',	2),
(15,	23,	'VSI-Loan/1/2025/09/0002',	'Guarantor 3',	'Friend',	'09038934785',	'guarantor3@vsi.org',	'Teacher',	'23 Chaddesley Glen, Poole, UK',	'Top House',	'Next Kin 3',	'06273521832',	NULL,	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-applications/guarantor3_form_68da5cf1c9f53_s44223-024-00071-2.pdf',	'Mr',	'2025-09-28',	'Mrs',	'NextKin3@vsi.org',	'Singer',	'123 Faith Lane, London, UK',	3);

CREATE TABLE `InternalScannedDocuments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `requisition_id` varchar(64) NOT NULL,
  `uploader_user_id` int(11) NOT NULL,
  `chapter_id` int(11) NOT NULL,
  `document_type` varchar(64) NOT NULL,
  `document_link` varchar(255) NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT current_timestamp(),
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `requisition_id` (`requisition_id`),
  KEY `chapter_id` (`chapter_id`),
  KEY `uploader_user_id` (`uploader_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `InternalScannedDocuments` (`id`, `requisition_id`, `uploader_user_id`, `chapter_id`, `document_type`, `document_link`, `uploaded_at`, `notes`) VALUES
(3,	'2025/10/1/10/5',	10,	1,	'Finance Requisition',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759491297_LW_Secured_Loan_Application.pdf',	'2025-10-03 11:34:58',	'For national Coodinator'),
(4,	'2025/10/1/10/6',	10,	1,	'Finance Requisition',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759491624_Blank-Guarantor-Form.pdf',	'2025-10-03 11:40:24',	'vrsr'),
(5,	'Requisition/2025/10/1/10/7',	10,	1,	'Finance Requisition',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759494641_Blank-Guarantor-Form.pdf',	'2025-10-03 12:30:42',	'The boss'),
(6,	'Requisition/2025/10/5/2/8',	2,	5,	'Finance Requisition',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759496160_Sample-Deed-of-Guarantee.pdf',	'2025-10-03 12:56:00',	'The main man'),
(7,	'ExpenseClaims/2025/10/1/10/1',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'2025-10-03 15:53:14',	'Print'),
(8,	'ExpenseClaims/2025/10/1/10/1',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506794_Guarantor-Agreement.pdf',	'2025-10-03 15:53:15',	'Print'),
(9,	'ExpenseClaims/2025/10/1/10/3',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'2025-10-03 15:54:32',	'Print'),
(10,	'ExpenseClaims/2025/10/1/10/3',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759506871_Guarantor-Agreement.pdf',	'2025-10-03 15:54:33',	'Print'),
(11,	'ExpenseClaims/2025/10/1/10/5',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'2025-10-03 15:56:55',	'Print'),
(12,	'ExpenseClaims/2025/10/1/10/5',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507014_Guarantor-Agreement.pdf',	'2025-10-03 15:56:55',	'Print'),
(13,	'ExpenseClaims/2025/10/1/10/9',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507196_Guarantor-Agreement.pdf',	'2025-10-03 15:59:57',	'gvbjh'),
(14,	'ExpenseClaims/2025/10/1/10/10',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507232_Guarantor-Agreement.pdf',	'2025-10-03 16:00:35',	'gvbjh'),
(15,	'ExpenseClaims/2025/10/1/10/11',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507254_Guarantor-Agreement.pdf',	'2025-10-03 16:00:58',	'Okay'),
(16,	'ExpenseClaims/2025/10/1/10/12',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507609_LW_Secured_Loan_Application.pdf',	'2025-10-03 16:06:49',	'sevf'),
(17,	'ExpenseClaims/2025/10/1/10/13',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507937_LW_Secured_Loan_Application.pdf',	'2025-10-03 16:12:17',	'sevf'),
(18,	'ExpenseClaims/2025/10/1/10/14',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'2025-10-03 16:13:20',	'I have go'),
(19,	'ExpenseClaims/2025/10/1/10/14',	10,	1,	'Expenses Claim',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses_claim/2025/1759507999_Sample-Deed-of-Guarantee.pdf',	'2025-10-03 16:13:20',	'I have go'),
(20,	'Benevolence/2025/10/1/10/1',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759573742_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 10:29:02',	'Benevolence  Name'),
(21,	'Benevolence/2025/10/1/10/3',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574010_Blank-Guarantor-Form.pdf',	'2025-10-04 10:33:30',	'Benevolence  Name2'),
(22,	'Benevolence/2025/10/1/10/5',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574102_LW_Secured_Loan_Application.pdf',	'2025-10-04 10:35:03',	'Benevolence  Name 3'),
(23,	'Benevolence/2025/10/1/10/6',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574252_LW_Secured_Loan_Application.pdf',	'2025-10-04 10:37:33',	'Benevolence  Name 4'),
(24,	'Benevolence/2025/10/1/10/7',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574365_LW_Secured_Loan_Application.pdf',	'2025-10-04 10:39:26',	'Benevolence  Name 5'),
(25,	'Benevolence/2025/10/1/10/8',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574465_Guarantor-Agreement.pdf',	'2025-10-04 10:41:07',	'Benevolence  Name 6'),
(26,	'Benevolence/2025/10/1/10/9',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574546_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 10:42:27',	'Benevolence  Name 7'),
(27,	'Benevolence/2025/10/1/10/10',	10,	1,	'Benevolence/Honorarium',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/benevolence/2025/1759574682_Blank-Guarantor-Form.pdf',	'2025-10-04 10:44:42',	'Benevolence  Name 8'),
(28,	'Donation/2025/10/1/10/3',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759588499_Guarantor-Agreement.pdf',	'2025-10-04 14:35:00',	'donation'),
(29,	'Donation/2025/10/1/10/4',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589610_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 14:53:30',	'Hello'),
(30,	'Donation/2025/10/1/10/5',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589609_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 14:53:30',	'Hello'),
(31,	'Donation/2025/10/1/10/6',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759589798_Blank-Guarantor-Form.pdf',	'2025-10-04 14:56:41',	'note fom donors'),
(32,	'Donation/2025/10/1/10/7',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590157_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 15:02:41',	'5 Notes'),
(33,	'Donation/2025/10/1/10/8',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759590449_LW_Secured_Loan_Application.pdf',	'2025-10-04 15:07:30',	'6 donors'),
(34,	'Donation/2025/10/1/10/9',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759591917_Blank-Guarantor-Form.pdf',	'2025-10-04 15:32:01',	'Donors 7'),
(35,	'Donation/2025/10/1/10/10',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759592426_Blank-Guarantor-Form.pdf',	'2025-10-04 15:40:28',	'8 donors'),
(36,	'Donation/2025/10/1/10/11',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759603387_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 18:43:08',	'nxcgb v'),
(37,	'Donation/2025/10/1/10/12',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604357_Blank-Guarantor-Form.pdf',	'2025-10-04 18:59:21',	'vsrvf'),
(38,	'Donation/2025/10/1/10/13',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759604922_Blank-Guarantor-Form.pdf',	'2025-10-04 19:08:42',	'loj'),
(39,	'Donation/2025/10/1/10/14',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605395_Guarantor-Agreement.pdf',	'2025-10-04 19:16:36',	'h98ohij'),
(40,	'Donation/2025/10/1/10/15',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605545_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 19:19:06',	'bdryfb'),
(41,	'Donation/2025/10/1/10/16',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759605693_LW_Secured_Loan_Application.pdf',	'2025-10-04 19:21:33',	'sbrt5bd'),
(42,	'Donation/2025/10/1/10/17',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606019_LW_Secured_Loan_Application.pdf',	'2025-10-04 19:27:00',	'50th'),
(43,	'Donation/2025/10/1/10/18',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606249_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 19:30:51',	'Notes'),
(44,	'Donation/2025/10/1/10/19',	10,	1,	'Donation',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/donations/2025/1759606369_Sample-Deed-of-Guarantee.pdf',	'2025-10-04 19:32:50',	'hfygbf'),
(45,	'Requisition/2025/10/1/10/9',	10,	1,	'Finance Requisition',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/requisition/2025/1759648903_Blank-Guarantor-Form.pdf',	'2025-10-05 07:21:44',	'Let us see'),
(46,	'ExpenseClaims/2025/10/1/10/10',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759760095_Diving_Into_Doctorate_Study_Handout.docx',	'2025-10-06 14:14:56',	'sdrfdsf'),
(47,	'ExpenseClaims/2025/10/1/10/11',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759762241_Diving_Into_Doctorate_Study_Handout.docx',	'2025-10-06 14:50:41',	';lioin'),
(48,	'ExpenseClaims/2025/10/1/10/12',	10,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759777834_Guarantor-Agreement.pdf',	'2025-10-06 19:10:34',	'hjuyh'),
(50,	'ExpenseClaims/2025/10/1/10/8',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759830539_Guarantor-Agreement.pdf',	'2025-10-07 09:48:59',	'hgcjyt'),
(51,	'ExpenseClaims/2025/10/1/10/14',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759843515_Sample-Deed-of-Guarantee.pdf',	'2025-10-07 13:25:15',	'c45y'),
(52,	'ExpenseClaims/2025/10/1/10/13',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759844251_Blank-Guarantor-Form.pdf',	'2025-10-07 13:37:31',	'wrcfaser'),
(53,	'ExpenseClaims/2025/10/1/10/10',	12,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759848593_Blank-Guarantor-Form.pdf',	'2025-10-07 14:49:53',	'agrsfv'),
(54,	'Benevolence/2025/10/1/10/10',	10,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759851474_Blank-Guarantor-Form.pdf',	'2025-10-07 15:37:54',	'ewrafser'),
(55,	'Requisition/2025/10/1/10/7',	10,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759852734_Blank-Guarantor-Form.pdf',	'2025-10-07 15:58:54',	'eafaesve'),
(56,	'ExpenseClaims/2025/10/1/10/10',	10,	1,	'Finance Payout',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/payout/2025/1759853351_Blank-Guarantor-Form.pdf',	'2025-10-07 16:09:11',	'szvr'),
(57,	'Expenses/2025/10/7/1/7',	12,	1,	'Expense',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759875724_Sample-Deed-of-Guarantee.pdf',	'2025-10-07 22:22:04',	'r dxcfg'),
(58,	'Expenses/2025/10/7/1/8',	12,	1,	'Expense',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759877446_LW_Secured_Loan_Application.pdf',	'2025-10-07 22:50:46',	'f yvg'),
(59,	'Expenses/2025/10/7/1/9',	12,	1,	'Expense',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/expenses/2025/1759880055_Sample-Deed-of-Guarantee.pdf',	'2025-10-07 23:34:15',	'sr vxdg'),
(60,	'INCOME/1/2025/0010',	10,	1,	'Income',	'http://s3.visionshapers.hftisolutions.co.uk:7000/finances/income/1/2025-26/1759921328_Guarantor-Agreement.pdf',	'2025-10-08 11:02:08',	'Old clothes');

CREATE TABLE `LoanAgreement` (
  `AgreementID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ApplicationID` bigint(20) unsigned NOT NULL,
  `TermMonths` int(11) DEFAULT NULL,
  `LoanAmountApproved` decimal(10,2) DEFAULT NULL,
  `TotalRepayable` decimal(10,2) DEFAULT NULL,
  `ServiceCharge` decimal(10,2) DEFAULT NULL,
  `RepaymentMode` varchar(20) DEFAULT NULL,
  `FirstRepaymentDate` date DEFAULT NULL,
  `LastRepaymentDate` date DEFAULT NULL,
  `RequiredDownPayment` decimal(10,2) DEFAULT NULL,
  `AmountBorrowed` decimal(10,2) DEFAULT NULL,
  `GuarantorName` varchar(100) DEFAULT NULL,
  `AmountGuaranteed` decimal(10,2) DEFAULT NULL,
  `GuarantorSignatureDate` date DEFAULT NULL,
  `BeneficiarySignatureDate` date DEFAULT NULL,
  `CollateralDescription` text DEFAULT NULL,
  `AgreementDocumentLink` text DEFAULT NULL,
  `MediaConsent` tinyint(1) DEFAULT 1,
  `RepaymentPlanDetails` text DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`AgreementID`),
  KEY `ApplicationID` (`ApplicationID`),
  CONSTRAINT `LoanAgreement_ibfk_1` FOREIGN KEY (`ApplicationID`) REFERENCES `LoanApplication` (`ApplicationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanAgreement` (`AgreementID`, `ApplicationID`, `TermMonths`, `LoanAmountApproved`, `TotalRepayable`, `ServiceCharge`, `RepaymentMode`, `FirstRepaymentDate`, `LastRepaymentDate`, `RequiredDownPayment`, `AmountBorrowed`, `GuarantorName`, `AmountGuaranteed`, `GuarantorSignatureDate`, `BeneficiarySignatureDate`, `CollateralDescription`, `AgreementDocumentLink`, `MediaConsent`, `RepaymentPlanDetails`, `CreatedAt`) VALUES
(4,	12,	18,	10000.00,	12000.00,	2000.00,	'monthly',	'2025-10-01',	'2027-10-30',	1000.00,	8000.00,	'Guarantor 1',	9000.00,	'2025-09-30',	'2025-09-30',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/loan-agreements/1759156189_Blank-Guarantor-Form.pdf',	1,	NULL,	'2025-09-29 14:29:50'),
(5,	13,	20,	23000.00,	25000.00,	2000.00,	'monthly',	'2025-10-01',	'2025-10-02',	2000.00,	21000.00,	'Guarantor 1',	20000.00,	'2025-10-01',	'2025-10-01',	NULL,	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-agreements/1759933059_Blank-Guarantor-Form.pdf',	1,	NULL,	'2025-10-08 14:17:39');

CREATE TABLE `LoanApplication` (
  `ApplicationID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `ApplicationDate` date NOT NULL,
  `LoanAmountRequested` decimal(10,2) DEFAULT NULL,
  `CurrencyType` varchar(10) DEFAULT 'Naira',
  `PurposeOfLoan` text DEFAULT NULL,
  `RefereeName` varchar(100) DEFAULT NULL,
  `RefereeLetterLink` text DEFAULT NULL,
  `ApplicationStatus` varchar(20) DEFAULT NULL,
  `CurrentStage` varchar(20) DEFAULT NULL,
  `ExpectedRepaymentDate` date DEFAULT NULL,
  `LoanApplicationFormLink` text DEFAULT NULL,
  `LoanApplicationID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ApplicationID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  CONSTRAINT `LoanApplication_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanApplication` (`ApplicationID`, `BeneficiaryID`, `ApplicationDate`, `LoanAmountRequested`, `CurrencyType`, `PurposeOfLoan`, `RefereeName`, `RefereeLetterLink`, `ApplicationStatus`, `CurrentStage`, `ExpectedRepaymentDate`, `LoanApplicationFormLink`, `LoanApplicationID`) VALUES
(12,	22,	'2025-09-25',	10000.00,	'Naira',	'New Stall',	NULL,	NULL,	'Paid Out',	'DetailsEntered',	'2027-12-29',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1759140008_RTWChecklist.pdf',	'VSI-Loan/1/2025/09/0001'),
(13,	23,	'2025-09-16',	10000.00,	'Naira',	'Open new shop',	NULL,	NULL,	'Paid Out',	'DetailsEntered',	'2027-12-29',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan-applications/loan_application_68da5cf034e41_ba4b288b-029f-4c5c-9ec3-cd640c645eaa.pdf',	'VSI-Loan/1/2025/09/0002');

CREATE TABLE `LoanAssessment` (
  `AssessmentID` int(11) NOT NULL AUTO_INCREMENT,
  `ApplicationID` varchar(50) DEFAULT NULL,
  `OfficerID` bigint(20) unsigned DEFAULT NULL,
  `VisitDate` date DEFAULT NULL,
  `InterviewSummary` text DEFAULT NULL,
  `GuarantorContactStatus` varchar(50) DEFAULT NULL,
  `BusinessViabilityReport` text DEFAULT NULL,
  `ReportDocumentLink` text DEFAULT NULL,
  `CRC_SDS_Outcome` varchar(255) DEFAULT NULL,
  `BusinessType` varchar(100) DEFAULT NULL,
  `BusinessLocation` varchar(255) DEFAULT NULL,
  `BusinessWorth` decimal(15,2) DEFAULT NULL,
  `LowestDailySale` decimal(15,2) DEFAULT NULL,
  `HighestDailySale` decimal(15,2) DEFAULT NULL,
  `ApplicantExperience` varchar(255) DEFAULT NULL,
  `LoanPurpose` varchar(255) DEFAULT NULL,
  `AmountApplied` decimal(15,2) DEFAULT NULL,
  `AmountRecommended` decimal(15,2) DEFAULT NULL,
  `GuarantorRelationship` varchar(255) DEFAULT NULL,
  `ViabilityComments` text DEFAULT NULL,
  `OtherInfo` text DEFAULT NULL,
  `OfficerRecommendation` text DEFAULT NULL,
  `CompletedBy` varchar(100) DEFAULT NULL,
  `CompletionDate` datetime DEFAULT NULL,
  `AssessmentStatus` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`AssessmentID`),
  KEY `OfficerID` (`OfficerID`),
  CONSTRAINT `LoanAssessment_ibfk_1` FOREIGN KEY (`OfficerID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanAssessment` (`AssessmentID`, `ApplicationID`, `OfficerID`, `VisitDate`, `InterviewSummary`, `GuarantorContactStatus`, `BusinessViabilityReport`, `ReportDocumentLink`, `CRC_SDS_Outcome`, `BusinessType`, `BusinessLocation`, `BusinessWorth`, `LowestDailySale`, `HighestDailySale`, `ApplicantExperience`, `LoanPurpose`, `AmountApplied`, `AmountRecommended`, `GuarantorRelationship`, `ViabilityComments`, `OtherInfo`, `OfficerRecommendation`, `CompletedBy`, `CompletionDate`, `AssessmentStatus`) VALUES
(6,	'13',	10,	NULL,	NULL,	NULL,	NULL,	NULL,	'Outcome of CRC and SDS checks:',	'crayfish market',	'Ikosi market',	30000.00,	3000.00,	7000.00,	'Trading for 5 years',	'To open a new stall',	10000.00,	8000.00,	'Husband',	'Thriving business',	'We should support her',	'Let\'s proceed with the loan application but offer reduce amaount',	'Jane Lagos',	'2025-09-29 11:19:00',	'Proceed to VLP'),
(7,	'12',	10,	NULL,	NULL,	NULL,	NULL,	NULL,	'Outcome of CRC and SDS checks:',	'crayfish market',	'Ikosi market',	30000.00,	3000.00,	7000.00,	'Trading for 5 years',	'To open a new stall',	10000.00,	8000.00,	'Husband',	'General comments on the viability of the business:\n',	'Any other supporting information:\n',	'Project Officer’s recommendation:\n',	'Jane Lagos',	'2025-09-29 11:27:00',	'Proceed to VLP');

CREATE TABLE `LoanInterview` (
  `InterviewID` int(11) NOT NULL AUTO_INCREMENT,
  `ApplicationID` int(11) DEFAULT NULL,
  `InterviewDate` date DEFAULT NULL,
  `InterviewerID` bigint(20) unsigned DEFAULT NULL,
  `Outcome` varchar(50) DEFAULT NULL,
  `Notes` text DEFAULT NULL,
  `InterviewReportSummary` text DEFAULT NULL,
  `AwardReportSummary` text DEFAULT NULL,
  `ScannedReportPath` varchar(255) DEFAULT NULL,
  `FinalDecision` enum('Approve','Declined','Recycle') DEFAULT NULL,
  PRIMARY KEY (`InterviewID`),
  KEY `InterviewerID` (`InterviewerID`),
  CONSTRAINT `LoanInterview_ibfk_1` FOREIGN KEY (`InterviewerID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanInterview` (`InterviewID`, `ApplicationID`, `InterviewDate`, `InterviewerID`, `Outcome`, `Notes`, `InterviewReportSummary`, `AwardReportSummary`, `ScannedReportPath`, `FinalDecision`) VALUES
(3,	12,	'2025-09-29',	NULL,	NULL,	NULL,	'Loan Interview Report Summary:',	'Loan Award Report Summary:',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/loan-applications/1759154986_Sample-Deed-of-Guarantee.pdf',	'Approve'),
(4,	13,	'2025-10-08',	NULL,	NULL,	NULL,	'fg gf',	' drgsrd',	'',	'Approve'),
(5,	13,	'2025-10-08',	NULL,	NULL,	NULL,	'fg gf',	' drgsrd',	'',	'Approve');

CREATE TABLE `LoanPanelReview` (
  `ReviewID` int(11) NOT NULL AUTO_INCREMENT,
  `LoanApplicationID` int(11) DEFAULT NULL,
  `DecisionType` varchar(50) DEFAULT NULL,
  `ReviewDate` date DEFAULT NULL,
  `PanelNotes` text DEFAULT NULL,
  `RequiresInterview` tinyint(1) DEFAULT 0,
  `ApprovedLoanValue` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`ReviewID`),
  KEY `AssessmentID` (`LoanApplicationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanPanelReview` (`ReviewID`, `LoanApplicationID`, `DecisionType`, `ReviewDate`, `PanelNotes`, `RequiresInterview`, `ApprovedLoanValue`) VALUES
(7,	12,	'Approved for F2F',	'2025-09-29',	'Happy to support',	1,	8000.00),
(8,	13,	'Approved for F2F',	'2025-09-29',	'Let\'s approve',	1,	10000.00);

CREATE TABLE `LoanPayout` (
  `PayoutID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ApplicationID` bigint(20) unsigned NOT NULL,
  `PaymentDate` date NOT NULL,
  `PaymentMethod` varchar(30) NOT NULL,
  `Acknowledgement` varchar(255) NOT NULL,
  `ScannedInstallmentForm` text DEFAULT NULL,
  `ScannedPaymentDocument` text DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`PayoutID`),
  KEY `ApplicationID` (`ApplicationID`),
  CONSTRAINT `LoanPayout_ibfk_1` FOREIGN KEY (`ApplicationID`) REFERENCES `LoanApplication` (`ApplicationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanPayout` (`PayoutID`, `ApplicationID`, `PaymentDate`, `PaymentMethod`, `Acknowledgement`, `ScannedInstallmentForm`, `ScannedPaymentDocument`, `CreatedAt`) VALUES
(3,	12,	'2025-09-29',	'cheque',	'cheque issued',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/loan_payouts/1759156798_LW_Secured_Loan_Application.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/loan_payouts/1759156798_Sample-Deed-of-Guarantee.pdf',	'2025-09-29 14:39:59'),
(4,	13,	'2025-10-02',	'cheque',	'cheque issued',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan_payouts/1759933111_Sample-Deed-of-Guarantee.pdf',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/loan_payouts/1759933111_Blank-Guarantor-Form.pdf',	'2025-10-08 14:18:31');

CREATE TABLE `LoanReceipt` (
  `ReceiptID` int(11) NOT NULL AUTO_INCREMENT,
  `AgreementID` int(11) DEFAULT NULL,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `PaymentDate` date DEFAULT NULL,
  `AmountPaid` decimal(10,2) DEFAULT NULL,
  `NotificationType` varchar(50) DEFAULT NULL,
  `ScannedProofLink` text DEFAULT NULL,
  `ReceiptNumber` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ReceiptID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  CONSTRAINT `LoanReceipt_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `LoanRepaymentSchedule` (
  `RepaymentID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `AgreementID` bigint(20) unsigned NOT NULL,
  `ScheduledDate` date DEFAULT NULL,
  `AmountDue` decimal(10,2) DEFAULT NULL,
  `AmountPaid` decimal(10,2) DEFAULT NULL,
  `PaymentDate` date DEFAULT NULL,
  `TellerReference` varchar(50) DEFAULT NULL,
  `PaymentStatus` varchar(20) DEFAULT NULL,
  `ExpectedDate` date DEFAULT NULL,
  `Status` varchar(30) DEFAULT NULL,
  `ApprovalDate` date DEFAULT NULL,
  `Notes` text DEFAULT NULL,
  PRIMARY KEY (`RepaymentID`),
  KEY `AgreementID` (`AgreementID`),
  CONSTRAINT `LoanRepaymentSchedule_ibfk_1` FOREIGN KEY (`AgreementID`) REFERENCES `LoanAgreement` (`AgreementID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `LoanRepaymentSchedule` (`RepaymentID`, `AgreementID`, `ScheduledDate`, `AmountDue`, `AmountPaid`, `PaymentDate`, `TellerReference`, `PaymentStatus`, `ExpectedDate`, `Status`, `ApprovalDate`, `Notes`) VALUES
(3,	4,	'2025-10-01',	1000.00,	10000.00,	'2025-09-30',	'wefse4er',	NULL,	'2025-09-18',	'On Time',	'2025-09-25',	'Good standing'),
(4,	4,	'2025-10-02',	1111.00,	1111.00,	'2025-09-24',	'UNion Bank:00004',	NULL,	'2025-09-25',	'On Time',	NULL,	'Great'),
(10,	4,	'2025-10-01',	5000.00,	5000.00,	'2025-10-02',	'sfsdrg',	NULL,	'2025-10-01',	'On Time',	NULL,	'Good test income');

CREATE TABLE `MentorshipAssignment` (
  `AssignmentID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `MentorID` bigint(20) unsigned DEFAULT NULL,
  `MenteeID` int(11) NOT NULL,
  `AssignmentDate` date NOT NULL,
  `Status` varchar(50) DEFAULT 'Active',
  PRIMARY KEY (`AssignmentID`),
  KEY `MentorID` (`MentorID`),
  KEY `MenteeID` (`MenteeID`),
  CONSTRAINT `MentorshipAssignment_ibfk_2` FOREIGN KEY (`MenteeID`) REFERENCES `Beneficiary` (`BeneficiaryID`),
  CONSTRAINT `MentorshipAssignment_ibfk_3` FOREIGN KEY (`MentorID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `MonitoringVisit` (
  `VisitID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `VisitDate` date NOT NULL,
  `VisitNotes` text DEFAULT NULL,
  `FollowUpRequired` tinyint(1) DEFAULT 0,
  `OfficerID` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`VisitID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  KEY `OfficerID` (`OfficerID`),
  CONSTRAINT `MonitoringVisit_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`),
  CONSTRAINT `MonitoringVisit_ibfk_2` FOREIGN KEY (`OfficerID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `payroll_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(50) NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `total_staff` int(11) NOT NULL DEFAULT 0,
  `total_gross_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_deductions` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_net_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('DRAFT','GENERATED','VERIFIED','PAID') DEFAULT 'DRAFT',
  `generated_by` bigint(20) unsigned NOT NULL,
  `generated_date` datetime DEFAULT current_timestamp(),
  `verified_by` bigint(20) unsigned DEFAULT NULL,
  `verified_date` datetime DEFAULT NULL,
  `payment_verified_by` bigint(20) unsigned DEFAULT NULL,
  `payment_verified_date` 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 `batch_id` (`batch_id`),
  UNIQUE KEY `unique_month_year` (`month`,`year`),
  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_month_year` (`month`,`year`),
  KEY `idx_status` (`status`),
  KEY `payroll_batches_ibfk_1` (`generated_by`),
  KEY `payroll_batches_ibfk_2` (`verified_by`),
  KEY `payroll_batches_ibfk_3` (`payment_verified_by`),
  CONSTRAINT `payroll_batches_ibfk_1` FOREIGN KEY (`generated_by`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `payroll_batches_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `payroll_batches_ibfk_3` FOREIGN KEY (`payment_verified_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payroll_batches` (`id`, `batch_id`, `month`, `year`, `total_staff`, `total_gross_amount`, `total_deductions`, `total_net_amount`, `status`, `generated_by`, `generated_date`, `verified_by`, `verified_date`, `payment_verified_by`, `payment_verified_date`, `created_at`, `updated_at`) VALUES
(1,	'PAYROLL/2025/10/4584',	10,	2025,	2,	178000.00,	17800.00,	160200.00,	'PAID',	4,	'2025-10-08 13:03:54',	NULL,	NULL,	4,	'2025-10-08 13:09:51',	'2025-10-08 13:03:54',	'2025-10-08 13:09:51');

CREATE TABLE `payroll_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(50) NOT NULL,
  `staff_id` varchar(20) NOT NULL,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `staff_name` varchar(100) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `basic_salary` decimal(12,2) NOT NULL,
  `allowances` decimal(12,2) DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL,
  `tax_deduction` decimal(12,2) DEFAULT 0.00,
  `other_deductions` decimal(12,2) DEFAULT 0.00,
  `total_deductions` decimal(12,2) DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `sort_code` varchar(20) DEFAULT NULL,
  `payment_status` enum('PENDING','PROCESSED','PAID') DEFAULT 'PENDING',
  `payment_reference` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_staff_id` (`staff_id`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_payment_status` (`payment_status`),
  CONSTRAINT `payroll_entries_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `payroll_batches` (`batch_id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_entries_ibfk_2` FOREIGN KEY (`staff_id`) REFERENCES `payroll_staff` (`staff_id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_entries_ibfk_3` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payroll_entries` (`id`, `batch_id`, `staff_id`, `chapter_id`, `staff_name`, `designation`, `basic_salary`, `allowances`, `gross_salary`, `tax_deduction`, `other_deductions`, `total_deductions`, `net_salary`, `bank_account_number`, `bank_name`, `sort_code`, `payment_status`, `payment_reference`, `created_at`, `updated_at`) VALUES
(1,	'PAYROLL/2025/10/4584',	'001',	3,	'John Coord',	'National Boss',	100000.00,	2000.00,	102000.00,	10200.00,	0.00,	10200.00,	91800.00,	'9887354203462',	'Union Bank',	'23-243-42',	'PAID',	NULL,	'2025-10-08 13:03:54',	'2025-10-08 13:09:51'),
(2,	'PAYROLL/2025/10/4584',	'0012',	3,	'Finance Lagos',	'Finance',	75000.00,	1000.00,	76000.00,	7600.00,	0.00,	7600.00,	68400.00,	'9887354203463',	'Union Bank',	'23-243-43',	'PAID',	NULL,	'2025-10-08 13:03:54',	'2025-10-08 13:09:51');

CREATE TABLE `payroll_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` varchar(20) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `designation` varchar(100) NOT NULL,
  `employment_type` enum('FULL_TIME','PART_TIME','CONTRACT','INTERN') NOT NULL DEFAULT 'FULL_TIME',
  `basic_salary` decimal(12,2) NOT NULL,
  `allowances` decimal(12,2) DEFAULT 0.00,
  `deductions` decimal(12,2) DEFAULT 0.00,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `sort_code` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `joining_date` date NOT NULL,
  `status` enum('ACTIVE','INACTIVE','TERMINATED') DEFAULT 'ACTIVE',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `staff_id` (`staff_id`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_status` (`status`),
  KEY `idx_staff_id` (`staff_id`),
  KEY `payroll_staff_ibfk_2` (`created_by`),
  CONSTRAINT `payroll_staff_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `payroll_staff_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payroll_staff` (`id`, `staff_id`, `full_name`, `chapter_id`, `designation`, `employment_type`, `basic_salary`, `allowances`, `deductions`, `bank_account_number`, `bank_name`, `sort_code`, `pan_number`, `joining_date`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1,	'001',	'John Coord',	3,	'National Boss',	'FULL_TIME',	100000.00,	2000.00,	0.00,	'9887354203462',	'Union Bank',	'23-243-42',	'',	'2010-01-01',	'ACTIVE',	4,	'2025-10-08 13:01:52',	'2025-10-08 13:01:52'),
(2,	'0012',	'Finance Lagos',	3,	'Finance',	'FULL_TIME',	75000.00,	1000.00,	0.00,	'9887354203463',	'Union Bank',	'23-243-43',	'ldsihc8',	'2012-02-01',	'ACTIVE',	4,	'2025-10-08 13:03:31',	'2025-10-08 13:03:31');

CREATE TABLE `Projects_Events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(100) NOT NULL,
  `event_code` varchar(50) NOT NULL,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `event_type` enum('PROJECT','EVENT','CAMPAIGN') NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `estimated_cost` decimal(12,2) DEFAULT 0.00,
  `actual_cost` decimal(12,2) DEFAULT 0.00,
  `status` enum('PLANNED','ONGOING','COMPLETED','CANCELLED') DEFAULT 'PLANNED',
  `chapter_id` bigint(20) unsigned NOT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `event_code` (`event_code`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_status` (`status`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_start_date` (`start_date`),
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `Projects_Events_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `Projects_Events_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `RepaymentStatusTracker` (
  `StatusID` int(11) NOT NULL AUTO_INCREMENT,
  `AgreementID` int(11) DEFAULT NULL,
  `CurrentStatus` varchar(20) DEFAULT NULL,
  `LastUpdated` date DEFAULT NULL,
  `Notes` text DEFAULT NULL,
  PRIMARY KEY (`StatusID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `RolePermissions` (
  `PermissionID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Role` varchar(50) NOT NULL,
  `CanView` tinyint(1) DEFAULT 0,
  `CanEdit` tinyint(1) DEFAULT 0,
  `CanApprove` tinyint(1) DEFAULT 0,
  `CanDelete` tinyint(1) DEFAULT 0,
  `ModuleAccess` text DEFAULT NULL,
  `RegionScope` varchar(100) DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`PermissionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `RolePermissions` (`PermissionID`, `Role`, `CanView`, `CanEdit`, `CanApprove`, `CanDelete`, `ModuleAccess`, `RegionScope`, `CreatedAt`) VALUES
(1,	'Admin',	1,	1,	1,	1,	'Users,Finance,Reports,Settings',	'All',	'2025-08-01 12:13:47'),
(2,	'Super User',	1,	1,	1,	1,	'All',	'All',	'2025-08-01 12:13:47'),
(3,	'Project Officer',	1,	1,	0,	0,	'FieldReports,Attendance',	'Assigned',	'2025-08-01 12:13:47'),
(4,	'Supervisor',	1,	1,	1,	0,	'FieldReports,TeamManagement',	'North Central',	'2025-08-01 12:13:47'),
(5,	'Location Team Lead',	1,	1,	0,	0,	'TeamReports',	'Southwest',	'2025-08-01 12:13:47'),
(6,	'National Coordinator',	1,	1,	1,	0,	'Coordination,Reports',	'All',	'2025-08-01 12:13:47'),
(7,	'Directors / Exec Team',	1,	0,	1,	0,	'StrategicReports',	'All',	'2025-08-01 12:13:47'),
(8,	'Donors / Partners',	1,	0,	0,	0,	'ImpactDashboard',	'All',	'2025-08-01 12:13:47'),
(9,	'Account Officer',	1,	1,	0,	0,	'Finance',	'All',	'2025-08-01 12:13:47');

CREATE TABLE `ScannedDocuments` (
  `DocumentID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BeneficiaryID` int(11) DEFAULT NULL,
  `DocumentType` varchar(50) DEFAULT NULL,
  `ScannedFormLink` varchar(255) NOT NULL,
  `UploadDate` timestamp NULL DEFAULT current_timestamp(),
  `UploadedByID` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`DocumentID`),
  KEY `BeneficiaryID` (`BeneficiaryID`),
  KEY `UploadedByID` (`UploadedByID`),
  CONSTRAINT `ScannedDocuments_ibfk_1` FOREIGN KEY (`BeneficiaryID`) REFERENCES `Beneficiary` (`BeneficiaryID`),
  CONSTRAINT `ScannedDocuments_ibfk_2` FOREIGN KEY (`UploadedByID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `ScannedDocuments` (`DocumentID`, `BeneficiaryID`, `DocumentType`, `ScannedFormLink`, `UploadDate`, `UploadedByID`) VALUES
(35,	22,	'Enquiry',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1757590229_ba4b288b-029f-4c5c-9ec3-cd640c645eaa.pdf',	'2025-09-11 11:30:29',	10),
(36,	23,	'Enquiry',	'http://s3.visionshapers.hftisolutions.co.uk:7000/general/beneficiary-forms/1757590851_a5d9124b44564a16463acdcde8ee65e218cc.pdf',	'2025-09-11 11:40:51',	10),
(37,	22,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1757591917_org-logo.png',	'2025-09-11 11:58:38',	10),
(38,	22,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0001/beneficiary-forms/1757591918_a5d9124b44564a16463acdcde8ee65e218cc.pdf',	'2025-09-11 11:58:38',	10),
(39,	23,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/beneficiary-forms/1757593331_program-logo.png',	'2025-09-11 12:22:11',	10),
(40,	23,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0003/beneficiary-forms/1757593331_LW_Secured_Loan_Application.pdf',	'2025-09-11 12:22:11',	10),
(41,	24,	'Enquiry',	'http://s3.visionshapers.hftisolutions.co.uk:7000/VSI-1-2025-09-0004/beneficiary-forms/beneficiary-forms/1757594003_Guarantor-Agreement.pdf',	'2025-09-11 12:33:23',	10),
(42,	24,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0004/beneficiary-forms/1759138614_Church_Nudge.png',	'2025-09-29 09:36:55',	10),
(43,	24,	'Membership',	'http://s3.visionshapers.hftisolutions.co.uk:7000/vsi-1-2025-09-0004/beneficiary-forms/1759138615_BarracudaCentral.org_-_Technical_Insight_for_Security_Pros.pdf',	'2025-09-29 09:36:55',	10);


CREATE TABLE `SystemAuditLog` (
  `AuditID` int(11) NOT NULL AUTO_INCREMENT,
  `ActorID` bigint(20) unsigned DEFAULT NULL,
  `ActionType` varchar(100) DEFAULT NULL,
  `TargetEntity` varchar(50) DEFAULT NULL,
  `TargetID` int(11) DEFAULT NULL,
  `ActionTimestamp` datetime DEFAULT current_timestamp(),
  `Notes` text DEFAULT NULL,
  PRIMARY KEY (`AuditID`),
  KEY `ActorID` (`ActorID`),
  CONSTRAINT `SystemAuditLog_ibfk_1` FOREIGN KEY (`ActorID`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `TrainingAttendance` (
  `AttendanceID` int(11) NOT NULL AUTO_INCREMENT,
  `CohortTrainingID` int(11) NOT NULL,
  `CohortMemberID` int(11) NOT NULL,
  `Status` enum('Present','Absent','Late') DEFAULT 'Present',
  `Notes` text DEFAULT NULL,
  `MarkedBy` bigint(20) unsigned DEFAULT NULL,
  `MarkedAt` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`AttendanceID`),
  KEY `CohortTrainingID` (`CohortTrainingID`),
  KEY `CohortMemberID` (`CohortMemberID`),
  KEY `MarkedBy` (`MarkedBy`),
  CONSTRAINT `TrainingAttendance_ibfk_1` FOREIGN KEY (`CohortTrainingID`) REFERENCES `CohortTraining` (`CohortTrainingID`),
  CONSTRAINT `TrainingAttendance_ibfk_2` FOREIGN KEY (`CohortMemberID`) REFERENCES `CohortMembers` (`CohortMemberID`),
  CONSTRAINT `TrainingAttendance_ibfk_3` FOREIGN KEY (`MarkedBy`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `TrainingAttendance` (`AttendanceID`, `CohortTrainingID`, `CohortMemberID`, `Status`, `Notes`, `MarkedBy`, `MarkedAt`) VALUES
(21,	9,	12,	'Present',	'',	10,	'2025-09-11 11:50:25'),
(22,	9,	13,	'Late',	'',	10,	'2025-09-11 11:50:25'),
(23,	10,	12,	'Present',	'',	10,	'2025-09-11 11:51:04'),
(24,	10,	13,	'Present',	'',	10,	'2025-09-11 11:51:04'),
(25,	11,	12,	'Absent',	'',	10,	'2025-09-11 11:51:18'),
(26,	11,	13,	'Late',	'',	10,	'2025-09-11 11:51:18'),
(27,	12,	12,	'Present',	'',	10,	'2025-09-11 11:51:29'),
(28,	12,	13,	'Absent',	'',	10,	'2025-09-11 11:51:29');

CREATE TABLE `TrainingCategory` (
  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(100) NOT NULL,
  `CreatedAt` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`CategoryID`),
  UNIQUE KEY `CategoryName` (`CategoryName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `TrainingCategory` (`CategoryID`, `CategoryName`, `CreatedAt`) VALUES
(1,	'IBBM',	'2025-09-06 05:56:58'),
(2,	'IBBM2',	'2025-09-06 05:57:12'),
(3,	'Taster',	'2025-09-06 05:57:21');

CREATE TABLE `UserChapterAssignments` (
  `AssignmentID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `UserID` bigint(20) unsigned NOT NULL,
  `ChapterID` bigint(20) unsigned NOT NULL,
  `AssignedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`AssignmentID`),
  KEY `UserID` (`UserID`),
  KEY `ChapterID` (`ChapterID`),
  CONSTRAINT `UserChapterAssignments_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `Users` (`UserID`) ON DELETE CASCADE,
  CONSTRAINT `UserChapterAssignments_ibfk_2` FOREIGN KEY (`ChapterID`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `VLPGroup` (
  `VLPGroupID` int(11) NOT NULL AUTO_INCREMENT,
  `VLPMemberID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`VLPGroupID`),
  KEY `VLPGroup_ibfk_1` (`VLPMemberID`),
  CONSTRAINT `VLPGroup_ibfk_1` FOREIGN KEY (`VLPMemberID`) REFERENCES `VLP_Users` (`VLPMemberID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `VLPMeeting` (
  `MeetingID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(100) NOT NULL,
  `ScheduledDate` datetime NOT NULL,
  `CreatedBy` bigint(20) unsigned NOT NULL,
  `Status` varchar(20) DEFAULT 'Scheduled',
  `Summary` text DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`MeetingID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `VLPMeeting` (`MeetingID`, `Title`, `ScheduledDate`, `CreatedBy`, `Status`, `Summary`, `CreatedAt`) VALUES
(5,	'Sept 2025 VLP',	'2025-10-02 10:00:00',	10,	'Completed',	NULL,	'2025-09-29 12:37:26');

CREATE TABLE `VLPMeetingApplications` (
  `MeetingAppID` int(11) NOT NULL AUTO_INCREMENT,
  `MeetingID` int(11) NOT NULL,
  `ApplicationID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`MeetingAppID`),
  KEY `MeetingID` (`MeetingID`),
  KEY `ApplicationID` (`ApplicationID`),
  CONSTRAINT `VLPMeetingApplications_ibfk_1` FOREIGN KEY (`MeetingID`) REFERENCES `VLPMeeting` (`MeetingID`),
  CONSTRAINT `VLPMeetingApplications_ibfk_2` FOREIGN KEY (`ApplicationID`) REFERENCES `LoanApplication` (`ApplicationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `VLPMeetingApplications` (`MeetingAppID`, `MeetingID`, `ApplicationID`) VALUES
(6,	5,	12),
(7,	5,	13);

CREATE TABLE `VLPMeetingUsers` (
  `MeetingUserID` int(11) NOT NULL AUTO_INCREMENT,
  `MeetingID` int(11) NOT NULL,
  `VLPMemberID` bigint(20) unsigned NOT NULL,
  `Role` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`MeetingUserID`),
  KEY `MeetingID` (`MeetingID`),
  KEY `VLPMeetingUsers_ibfk_2` (`VLPMemberID`),
  CONSTRAINT `VLPMeetingUsers_ibfk_1` FOREIGN KEY (`MeetingID`) REFERENCES `VLPMeeting` (`MeetingID`),
  CONSTRAINT `VLPMeetingUsers_ibfk_2` FOREIGN KEY (`VLPMemberID`) REFERENCES `VLP_Users` (`VLPMemberID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `VLP_Users` (
  `VLPMemberID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `FullName` varchar(100) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `Region` varchar(100) DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `ChapterID` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`VLPMemberID`),
  UNIQUE KEY `Email` (`Email`),
  KEY `fk_vlpusers_chapter` (`ChapterID`),
  CONSTRAINT `fk_vlpusers_chapter` FOREIGN KEY (`ChapterID`) REFERENCES `Chapters` (`ChapterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `VLP_Users` (`VLPMemberID`, `FullName`, `Email`, `Region`, `PhoneNumber`, `CreatedAt`, `ChapterID`) VALUES
(1,	'Lagos VLP member 1',	'demo@caarrs.co.uk',	'South West',	'07893939314',	'2025-09-29 11:19:50',	1),
(3,	'Lagos VLP member 2',	'testtwo@caarrs.co.uk',	'South West',	'07893939314',	'2025-09-29 11:20:47',	1),
(7,	'Lagos VLP member 3',	'sharo@vsi.org',	'South West',	'0898788273973',	'2025-09-29 12:47:37',	1);

-- 2025-10-08 14:32:59 UTC
