-- One-off cleanup: billing:charge-students (App\Console\Commands\ChargeStudents)
-- used to never check students.status_id, only student_groups.is_active, so
-- charges could be created for students whose status was already
-- "Не учится" (not studying) as of the charge date. That gap is now fixed
-- (ChargeStudents.php filters s.status_id = 1); this script removes the
-- charges that were created incorrectly under the old behavior, and
-- restores any student_tariffs.lessons_remaining that were wrongly
-- decremented alongside them.
--
-- Status at charge time is reconstructed from student_status_history: the
-- latest status change on or before the charge's date, falling back to the
-- student's earliest known status, then to their current status_id if no
-- history exists at all. Same-day status changes are treated as already in
-- effect for that day's charge run (the charge job runs at 00:05
-- Asia/Almaty) — this is a best-effort heuristic, not exact to the minute.
--
-- Removed rows are copied into charges_removed_not_studying_backup before
-- deletion so this can be reviewed, or reversed by hand, afterward.
--
-- Run once against the app database:
--   mysql -h <host> -u <user> -p <database> < scripts/fix_charges_for_students_not_studying.sql
--
-- (Cyrillic text below — force utf8mb4 on the client, same as
-- scripts/add_status_change_reasons.sql, otherwise mojibake:
--   mysql --default-character-set=utf8mb4 -h <host> -u <user> -p <database> \
--       < scripts/fix_charges_for_students_not_studying.sql)


DROP TEMPORARY TABLE IF EXISTS tmp_incorrect_charges;

CREATE TEMPORARY TABLE tmp_incorrect_charges AS
SELECT
    c.id AS charge_id,
    c.student_id,
    c.group_id,
    c.tariff_id,
    c.charged_date,
    COALESCE(
        (SELECT h.new_status_id
           FROM student_status_history h
          WHERE h.student_id = c.student_id
            AND DATE(h.created_at) <= c.charged_date
          ORDER BY h.created_at DESC, h.id DESC
          LIMIT 1),
        (SELECT h.old_status_id
           FROM student_status_history h
          WHERE h.student_id = c.student_id
          ORDER BY h.created_at ASC, h.id ASC
          LIMIT 1),
        s.status_id
    ) AS status_at_charge_time
FROM charges c
JOIN students s ON s.id = c.student_id;

DELETE FROM tmp_incorrect_charges WHERE status_at_charge_time = 1 OR status_at_charge_time IS NULL;

CREATE TABLE IF NOT EXISTS charges_removed_not_studying_backup (
    id                      INT NOT NULL,
    student_id              INT NOT NULL,
    group_id                INT NULL,
    tariff_id               INT NULL,
    amount                  DECIMAL(10,2) NOT NULL,
    charged_date            DATE NOT NULL,
    status_at_charge_time   INT NULL,
    removed_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO charges_removed_not_studying_backup
    (id, student_id, group_id, tariff_id, amount, charged_date, status_at_charge_time)
SELECT c.id, c.student_id, c.group_id, c.tariff_id, c.amount, c.charged_date, t.status_at_charge_time
FROM charges c
JOIN tmp_incorrect_charges t ON t.charge_id = c.id;

-- Restore lessons_remaining for charges that were sourced from a
-- student_tariffs override (same matching logic as ChargeStudents.php's
-- tariff_source = 'student' case: a student_tariffs row covering the
-- charge's group + charge date, with a matching tariff_id).
UPDATE student_tariffs st
JOIN tmp_incorrect_charges t
    ON st.student_id = t.student_id
   AND st.group_id = t.group_id
   AND st.tariff_id = t.tariff_id
   AND st.start_date <= t.charged_date
   AND st.end_date >= t.charged_date
SET st.lessons_remaining = st.lessons_remaining + 1;

DELETE c FROM charges c
JOIN tmp_incorrect_charges t ON t.charge_id = c.id;

SELECT COUNT(*) AS charges_removed FROM charges_removed_not_studying_backup;

DROP TEMPORARY TABLE IF EXISTS tmp_incorrect_charges;
