-- ═══════════════════════════════════════════════════════════════════════
-- Restore missing PRIMARY KEY + AUTO_INCREMENT on `id` (local environment repair)
--
-- Confirmed via information_schema on this local MariaDB instance: 24 real
-- EduTrack tables have an `id int(11) NOT NULL` column with NO PRIMARY KEY
-- and NO AUTO_INCREMENT at all — contrary to database/schema.sql, and
-- contrary to the live/production server, which has this column defined
-- correctly. This is local-only drift (from however this dev copy of the DB
-- was created/restored), not a production issue.
--
-- Consequence: this DB's sql_mode does not include STRICT_TRANS_TABLES, so
-- every INSERT that omits `id` — which is every application code path, none
-- of them ever set it manually — silently stores literal 0 instead of
-- erroring or auto-generating, and $stmt->insert_id comes back 0. Three
-- tables already accumulated real duplicate id=0 rows before this was found:
-- payments (4 rows, one predating any related feature work), sms_logs (24),
-- user_activities (27). Every other affected table's row count is consistent
-- with a normal (gap-tolerant) sequential id column, so only these three
-- need deduplication before a PRIMARY KEY can be added.
--
-- Dedup approach: `UPDATE t SET id = <next> WHERE id = 0 ORDER BY created_at
-- ASC LIMIT 1` run once per duplicate row. Each run changes exactly one row's
-- id away from 0, so the next run's `WHERE id = 0` matches a different one of
-- the remaining duplicates — standard technique, no data other than the id
-- column itself is touched. New ids start at MAX(id)+1 for that table so
-- they can't collide with anything already in use.
--
-- Adding AUTO_INCREMENT to a column that already has data makes MariaDB
-- initialize the counter to MAX(existing value)+1 automatically — verified
-- directly against this DB before writing this migration, no explicit
-- `AUTO_INCREMENT = n` needed.
--
-- Idempotent: `ADD PRIMARY KEY IF NOT EXISTS` no-ops on a table this has
-- already been applied to; the dedup UPDATEs are naturally idempotent too
-- (WHERE id = 0 matches nothing once none remain).
-- ═══════════════════════════════════════════════════════════════════════

-- ── Dedup: payments (4 rows collided at id=0) ──────────────────────────────
UPDATE `payments` SET id = 1165 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `payments` SET id = 1166 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `payments` SET id = 1167 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `payments` SET id = 1168 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;

-- ── Dedup: sms_logs (24 rows collided at id=0) ─────────────────────────────
UPDATE `sms_logs` SET id = 91 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 92 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 93 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 94 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 95 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 96 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 97 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 98 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 99 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 100 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 101 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 102 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 103 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 104 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 105 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 106 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 107 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 108 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 109 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 110 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 111 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 112 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 113 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `sms_logs` SET id = 114 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;

-- ── Dedup: user_activities (27 rows collided at id=0) ──────────────────────
UPDATE `user_activities` SET id = 131 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 132 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 133 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 134 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 135 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 136 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 137 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 138 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 139 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 140 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 141 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 142 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 143 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 144 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 145 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 146 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 147 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 148 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 149 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 150 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 151 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 152 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 153 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 154 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 155 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 156 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;
UPDATE `user_activities` SET id = 157 WHERE id = 0 ORDER BY `created_at` ASC LIMIT 1;

-- ── Restore PRIMARY KEY + AUTO_INCREMENT on id for every affected table ─────
ALTER TABLE `parent_accounts` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `parent_messages` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `parent_notifications` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `parent_student_link` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `payments` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `question_bank` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `report_details` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `sba_component_types` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `sba_scores` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `school_holidays` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `school_terms` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `sms_logs` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `staff` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `student_interventions` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `students` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `subjects` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `teacher_lesson_notes` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `teacher_lesson_plans` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `timetable_email_reminders` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `timetable_periods` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `timetable_slots` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `timetable_substitutions` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `user_activities` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `users` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
ALTER TABLE `weekly_feeding_payments` MODIFY id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY IF NOT EXISTS (id);
