-- ============================================================
-- Schema Update 6 — Fix Weekly Partial Payment Tracking
-- ============================================================

-- Add paid_amount to track exactly how much has been paid
-- toward this specific installment (supports partial payments)
ALTER TABLE `WeeklySchedule`
  ADD COLUMN `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER `amount_due`;

-- Back-fill: for already-paid installments, set paid_amount = amount_due
UPDATE `WeeklySchedule` SET paid_amount = amount_due WHERE is_paid = 1 AND paid_amount = 0;

-- Track exactly which Payments contributed to which WeeklySchedule rows,
-- and how much — required to correctly reverse partial/cascaded payments
-- when a payment is deleted.
CREATE TABLE IF NOT EXISTS `PaymentAllocations` (
  `id`          INT NOT NULL AUTO_INCREMENT,
  `payment_id`  INT NOT NULL,
  `schedule_id` INT NOT NULL,
  `amount`      DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pa_payment`  (`payment_id`),
  KEY `idx_pa_schedule` (`schedule_id`),
  CONSTRAINT `fk_pa_payment`  FOREIGN KEY (`payment_id`)  REFERENCES `Payments`(`payment_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pa_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `WeeklySchedule`(`id`)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
