-- EduFlow school onboarding approval foundation.
-- Run after schema.sql (and after 001_approval_permissions.sql where present).
-- Existing schools are marked approved so this migration does not lock out
-- already operating tenants. New registrations use the pending default.

SET @has_status := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schools'
    AND COLUMN_NAME = 'status'
);
SET @sql := IF(
  @has_status = 0,
  "ALTER TABLE schools ADD COLUMN status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'approved' AFTER cycle",
  'SELECT 1'
);
PREPARE add_school_status FROM @sql;
EXECUTE add_school_status;
DEALLOCATE PREPARE add_school_status;

-- Existing rows received approved above. Set the default only after that
-- backfill so future registrations are pending; safe to re-run.
ALTER TABLE schools MODIFY status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending';

CREATE TABLE IF NOT EXISTS school_onboarding_audit (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  school_id       INT NOT NULL,
  actor_user_id   INT NOT NULL,
  action          ENUM('approve','reject','suspend') NOT NULL,
  previous_status ENUM('pending','approved','rejected','suspended') NOT NULL,
  new_status      ENUM('pending','approved','rejected','suspended') NOT NULL,
  reason          VARCHAR(500) NOT NULL DEFAULT '',
  metadata        JSON NULL,
  ip_address      VARCHAR(45) NOT NULL DEFAULT '',
  user_agent      VARCHAR(512) NOT NULL DEFAULT '',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX onboarding_audit_school_created (school_id, created_at),
  INDEX onboarding_audit_actor_created (actor_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;