-- ═══════════════════════════════════════════════════════════════════════
-- Bulk Fee Payment — Phase 0 schema (feature: bulk_fee_payment)
--
-- Adds two columns fee_structures needs before bulk payment (paying several
-- fee items/periods for one student in a single transaction) can be built:
--
--   term               Which of the school's terms this fee item belongs to.
--                      Not a real column today — some fee_type values do
--                      carry descriptive term text ('Tuition Fee - First
--                      Term'), but the live "Add Fee Structure" form only
--                      offers 6 plain fee types with no term field at all,
--                      so this can't be trusted as the sole signal either.
--                      Backfilled two ways: (1) match due_date against
--                      school_terms for the row's academic_year, which is
--                      authoritative where the school has defined that
--                      term's calendar; (2) for rows that still don't match
--                      (e.g. no school_terms row yet for that year/term),
--                      fall back to the term word already sitting in
--                      fee_type. Anything left after both passes is
--                      genuinely ambiguous and stays NULL — same "don't
--                      guess" policy as academicStampScoreClasses() in
--                      lib/academic_period.php. Going forward, fees.php
--                      collects it explicitly via a new required dropdown
--                      (ACADEMIC_TERMS from lib/academic_period.php), so
--                      every fee item created from now on is unambiguous.
--
--   billing_frequency  'termly' (one lump sum per term — tuition/attire/
--                      admission/printing) vs 'recurring_weekly' (feeding —
--                      priced and covered per week, see weekly_feeding_payments
--                      and canteen_auto_generate_entitlement()). Backfilled
--                      using the same feeding|canteen|meal regex the canteen
--                      module already trusts (includes/canteen_helpers.php),
--                      so this doesn't introduce a second, possibly-divergent
--                      definition of "is this a feeding fee".
--
-- Idempotent on MariaDB (XAMPP), matching every other migration in this folder.
-- ═══════════════════════════════════════════════════════════════════════

ALTER TABLE `fee_structures`
    ADD COLUMN IF NOT EXISTS `term` VARCHAR(20) DEFAULT NULL AFTER `class_name`;

ALTER TABLE `fee_structures`
    ADD COLUMN IF NOT EXISTS `billing_frequency` ENUM('termly','recurring_weekly') NOT NULL DEFAULT 'termly' AFTER `term`;

-- Pass 1: due_date against the school's own calendar (authoritative where defined).
UPDATE `fee_structures` fs
JOIN `school_terms` st
  ON st.academic_year = fs.academic_year
 AND fs.due_date BETWEEN st.start_date AND st.end_date
SET fs.term = st.term
WHERE fs.term IS NULL;

-- Pass 2: fallback to term text already embedded in fee_type, for rows
-- pass 1 couldn't resolve (e.g. no school_terms row for that year/term yet).
UPDATE `fee_structures` SET term = 'First Term'  WHERE term IS NULL AND fee_type REGEXP 'first';
UPDATE `fee_structures` SET term = 'Second Term' WHERE term IS NULL AND fee_type REGEXP 'second';
UPDATE `fee_structures` SET term = 'Third Term'  WHERE term IS NULL AND fee_type REGEXP 'third';

-- Backfill billing_frequency from fee_type (deterministic, safe to rerun).
UPDATE `fee_structures`
SET billing_frequency = 'recurring_weekly'
WHERE fee_type REGEXP 'feeding|canteen|meal';
