-- Nachklang e.V. Calendar module — step 5 of docs/calendar-auth-migration.md.
-- Apply manually against the CALENDAR_DB database, after 003:
--   mysql -h <DB_HOST> -u <DB_USER> -p <CALENDAR_DB> < 004_drop_legacy_auth.sql
--
-- *** APPLY THIS AFTER DEPLOYING THE API, NOT BEFORE. ***
--
-- This is the opposite order from the step 4 cutover, and getting it wrong by
-- analogy is the obvious mistake. Step 4's migration only *added* things, so it
-- was safe ahead of the deploy. This one removes columns and a table that the
-- currently running build still selects and joins - applying it first fails
-- every calendar read, including the anonymous public feed the website uses.
-- The step 5 build touches none of them, so it runs happily against the old
-- schema; deploy it, confirm the calendar works, then run this.
--
-- Nothing here loses information that is still reachable: the creators' display
-- names were snapshotted into events.created_by_name and
-- event_versions.version_created_by_name by migration 002, and the step 4
-- runbook re-ran that backfill after the deploy. Verify before running:
--
--   SELECT SUM(created_by_id IS NOT NULL AND created_by_name IS NULL) FROM events;
--   SELECT SUM(version_created_by_id IS NOT NULL AND version_created_by_name IS NULL) FROM event_versions;
--
-- Both must be 0. A non-zero count is an event whose author this migration
-- would erase; re-run 002's backfill first.

-- The foreign keys have to go before the columns they are declared on.
-- IF EXISTS so that a re-run after a partial failure gets past them.
ALTER TABLE `events`
    DROP FOREIGN KEY IF EXISTS `events_users_user_id_fk`;

ALTER TABLE `event_versions`
    DROP FOREIGN KEY IF EXISTS `event_versions_users_user_id_fk`;

ALTER TABLE `events`
    DROP INDEX IF EXISTS `events_users_user_id_fk`,
    DROP COLUMN IF EXISTS `created_by_id`;

ALTER TABLE `event_versions`
    DROP INDEX IF EXISTS `event_versions_users_user_id_fk`,
    DROP COLUMN IF EXISTS `version_created_by_id`;

-- The accounts themselves are renamed aside rather than dropped.
--
-- Nothing visible depends on them any more - the names are snapshotted, and no
-- code has referenced these tables since the step 4 cutover. But they still
-- hold e-mail addresses and password hashes, and a rename makes them
-- unreachable without destroying anything.
--
-- `sessions` has a foreign key into `users`; InnoDB rewires it to the new name
-- on rename, so after this it reads REFERENCES `users_legacy_archive` and the
-- pair stays internally consistent whichever order they are renamed in.
-- Verified on MariaDB 11.
--
-- Unlike the statements above this is not re-runnable, and that is the safe
-- behaviour: a second run fails on a missing `sessions` rather than doing
-- anything. Drop them for real whenever you like, at a moment when nobody is
-- mid-deploy:
--   DROP TABLE `sessions_legacy_archive`, `users_legacy_archive`;
RENAME TABLE `sessions` TO `sessions_legacy_archive`;
RENAME TABLE `users`    TO `users_legacy_archive`;