-- One-off migration: adds a sort_order column to every dictionary table
-- managed by admin/indexes.php that doesn't already have one
-- (student_statuses already has sort_order and is left untouched), and
-- backfills it to match the current id-based ordering so nothing visibly
-- reshuffles the first time this runs.
--
-- IMPORTANT: this file may contain Cyrillic text via existing row data —
-- force the client to utf8mb4:
--
--   docker compose exec -T db mysql --default-character-set=utf8mb4 \
--       -u"$(grep '^DB_USER=' .env | cut -d '=' -f2-)" \
--       -p"$(grep '^DB_PASS=' .env | cut -d '=' -f2-)" \
--       "$(grep '^DB_NAME=' .env | cut -d '=' -f2-)" \
--       < scripts/add_sort_order_to_dictionaries.sql
--
-- MySQL doesn't support IF NOT EXISTS on ADD COLUMN, so these are plain
-- statements — safe to run once; re-running will error with "Duplicate
-- column name" on any table already migrated, which is harmless (just
-- skip past that line and continue with the rest).

ALTER TABLE learning_types         ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE classes                ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE student_status_reasons ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE disciplines            ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE languages              ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE classrooms             ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE comment_categories     ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE comments               ADD COLUMN sort_order INT NOT NULL DEFAULT 0;

SET @rn := 0;
UPDATE learning_types SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE classes SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE student_status_reasons SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE disciplines SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE languages SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE classrooms SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE comment_categories SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
SET @rn := 0;
UPDATE comments SET sort_order = (@rn := @rn + 1) ORDER BY id ASC;
