-- Create the receipts table
CREATE TABLE IF NOT EXISTS `receipts` (
  `id` char(32) NOT NULL,
  `repayment_id` char(32) NOT NULL,
  `loan_id` char(32) NOT NULL,
  `borrower_id` int(11) NOT NULL,
  `receipt_number` varchar(20) NOT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `payment_method` varchar(20) NOT NULL,
  `payment_date` datetime(6) NOT NULL,
  `previous_balance` decimal(12,2) NOT NULL,
  `new_balance` decimal(12,2) NOT NULL,
  `pdf_file` varchar(100) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  UNIQUE KEY `repayment_id` (`repayment_id`),
  KEY `receipts_loan_id_idx` (`loan_id`),
  KEY `receipts_borrower_id_idx` (`borrower_id`),
  CONSTRAINT `receipts_repayment_id_fk` FOREIGN KEY (`repayment_id`) REFERENCES `repayments` (`id`),
  CONSTRAINT `receipts_loan_id_fk` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `receipts_borrower_id_fk` FOREIGN KEY (`borrower_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;