-- Adds draft support to MOD reports: a report can now be auto-saved
-- periodically while someone is still filling it out, resumed later if
-- they navigate away or their browser closes, and only becomes a real
-- finalized report once they actually click Submit.
--
-- `status` distinguishes a draft (auto-saved, still editable, not yet
-- finalized) from a submitted report. `created_by` identifies whose draft
-- it is, so returning to the form only ever resumes YOUR OWN in-progress
-- draft, never someone else's.
--
-- Every row that already exists predates this feature entirely -- it was
-- already a real, finalized report under the old submit-only flow, so
-- those are backfilled to 'submitted' rather than defaulting to 'draft'.
--
-- Safe to run more than once.

ALTER TABLE `mod_reports`
    ADD COLUMN IF NOT EXISTS `status` ENUM('draft','submitted') NOT NULL DEFAULT 'draft' AFTER `shift_timeline`,
    ADD COLUMN IF NOT EXISTS `created_by` INT(11) DEFAULT NULL AFTER `status`;

UPDATE `mod_reports` SET `status` = 'submitted' WHERE `status` = 'draft';