-- Chapter Income Management Table (Corrected Foreign Keys)
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_category` enum('DONATION', 'FORM SALE', 'REFUNDS', 'CLOTH SALES', '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;

