-- ═══════════════════════════════════════════════════════════════════════
-- Bulk Fee Payment — Phase 1 schema (feature: bulk_fee_payment)
--
-- Deliberately does NOT restructure `payments` or `online_payments` — every
-- balance/report query in the app reads `payments.amount_paid` directly, and
-- Phase 0 just consolidated the worst of that duplication. A bulk payment
-- still writes one ordinary `payments` row per fee item; these two additions
-- are a thin layer on top purely for grouping/receipts/credit:
--
--   bulk_payment_batches  One header row per bulk transaction (e.g. "pay
--                         this term in full"), so a receipt/SMS/report can
--                         say "this one transaction covered these N fee
--                         items" without re-deriving it from timing or
--                         transaction_id matching. `receipt_number` is left
--                         NULL here — Phase 2/3 (once something actually
--                         inserts rows) should set it from the row's own
--                         auto_increment id right after insert, e.g.
--                         CONCAT('BLK-', academic_year, '-', LPAD(id,6,'0')),
--                         so it's sequential and unique for free rather than
--                         the timestamp+random scheme online_payments uses.
--
--   payments.batch_id     Nullable FK-by-convention (this codebase doesn't
--                         use real FOREIGN KEY constraints elsewhere either)
--                         back to the batch a payments row belongs to. NULL
--                         for every payment recorded before this migration,
--                         and for any payment that isn't part of a bulk
--                         transaction going forward.
--
--   recorded_via +='bulk' A new payments row inserted as part of a batch
--                         should be tagged this way, distinct from the
--                         existing (unused) 'canteen_bulk' value, whose name
--                         is specific to canteen and would be misleading for
--                         a batch that also covers tuition/attire/etc.
--
--   student_fee_credits   Ledger for the "overpayment becomes a credit"
--                         decision: a credit is only ever created by an
--                         explicit admin/bursar action (Phase 2), never
--                         auto-detected from withdrawal/transfer/correction
--                         scenarios — that inference is out of scope. Consumed
--                         automatically against a student's next balance due.
--                         rbcAvailableCredit() (ai/fee_balance.php) is the
--                         read side, added now so Phase 2's write side has
--                         somewhere to report into; nothing calls it yet,
--                         since there is no way to create a credit until then.
--
-- Idempotent on MariaDB (XAMPP), matching every other migration in this folder.
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `bulk_payment_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `term` varchar(20) NOT NULL,
  `payment_channel` enum('cash','bank_transfer','online') NOT NULL,
  `gateway` varchar(30) DEFAULT NULL,
  `gateway_reference` varchar(100) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `receipt_number` varchar(40) DEFAULT NULL,
  `status` enum('pending','completed','failed') NOT NULL DEFAULT 'completed',
  `initiated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `student_id`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `academic_year`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `payment_channel` enum('cash','bank_transfer','online') NOT NULL AFTER `term`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `gateway` varchar(30) DEFAULT NULL AFTER `payment_channel`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `gateway_reference` varchar(100) DEFAULT NULL AFTER `gateway`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `total_amount` decimal(10,2) NOT NULL AFTER `gateway_reference`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `total_amount`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `receipt_number` varchar(40) DEFAULT NULL AFTER `discount_amount`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `status` enum('pending','completed','failed') NOT NULL DEFAULT 'completed' AFTER `receipt_number`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `initiated_by` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `initiated_by`;
ALTER TABLE `bulk_payment_batches` ADD COLUMN IF NOT EXISTS `completed_at` timestamp NULL DEFAULT NULL AFTER `created_at`;

ALTER TABLE `bulk_payment_batches` ADD UNIQUE KEY IF NOT EXISTS `uniq_bulk_receipt_number` (`receipt_number`);
ALTER TABLE `bulk_payment_batches` ADD KEY IF NOT EXISTS `idx_bulk_student` (`student_id`);
ALTER TABLE `bulk_payment_batches` ADD KEY IF NOT EXISTS `idx_bulk_gateway_reference` (`gateway_reference`);

ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `batch_id` int(11) DEFAULT NULL AFTER `recorded_via`;
ALTER TABLE `payments` ADD KEY IF NOT EXISTS `idx_payments_batch_id` (`batch_id`);

ALTER TABLE `payments` MODIFY COLUMN `recorded_via`
    enum('standard','express_scanner','ai_agent','import','canteen_bulk','bulk') DEFAULT 'standard';

CREATE TABLE IF NOT EXISTS `student_fee_credits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `source` enum('overpayment','admin_adjustment','fee_correction') NOT NULL,
  `source_batch_id` int(11) DEFAULT NULL,
  `status` enum('available','applied','refunded') NOT NULL DEFAULT 'available',
  `applied_to_payment_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `amount` decimal(10,2) NOT NULL AFTER `student_id`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `source` enum('overpayment','admin_adjustment','fee_correction') NOT NULL AFTER `amount`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `source_batch_id` int(11) DEFAULT NULL AFTER `source`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `status` enum('available','applied','refunded') NOT NULL DEFAULT 'available' AFTER `source_batch_id`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `applied_to_payment_id` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `applied_to_payment_id`;
ALTER TABLE `student_fee_credits` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `notes`;

ALTER TABLE `student_fee_credits` ADD KEY IF NOT EXISTS `idx_credits_student_status` (`student_id`, `status`);
