-- ============================================================
-- Migration: User self-registration + admission approval workflow
-- Run this against an existing database. New installs should use
-- the updated database/schema.sql instead.
-- ============================================================

ALTER TABLE `users`
  ADD COLUMN `approval_status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved' AFTER `is_active`,
  ADD COLUMN `approved_by` INT UNSIGNED DEFAULT NULL AFTER `approval_status`,
  ADD COLUMN `approved_at` DATETIME DEFAULT NULL AFTER `approved_by`,
  ADD COLUMN `rejection_reason` VARCHAR(255) DEFAULT NULL AFTER `approved_at`,
  ADD COLUMN `requested_role_id` TINYINT UNSIGNED DEFAULT NULL AFTER `role_id`,
  ADD CONSTRAINT `fk_user_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

-- Existing rows (created by an admin, or the seeded sysadmin) are
-- already trusted, so they default to 'approved' above and are
-- backfilled explicitly here for clarity.
UPDATE `users` SET `approval_status` = 'approved', `approved_at` = `created_at` WHERE `approval_status` IS NULL OR `approval_status` = 'approved';