-- EduFlow 360 approval authorization migration.
-- Run once against existing installations after schema.sql. It is safe to
-- re-run: the role change is conditional and the table uses IF NOT EXISTS.

SET @has_bursar := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'role'
    AND COLUMN_TYPE LIKE '%bursar%'
);
SET @sql := IF(
  @has_bursar = 0,
  "ALTER TABLE users MODIFY role ENUM('admin','director','principal','bursar','teacher','parent') NOT NULL DEFAULT 'admin'",
  'SELECT 1'
);
PREPARE add_bursar_role FROM @sql;
EXECUTE add_bursar_role;
DEALLOCATE PREPARE add_bursar_role;

CREATE TABLE IF NOT EXISTS approval_permissions (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  school_id     INT NOT NULL,
  role          ENUM('admin','principal','bursar') NOT NULL,
  can_finalize  TINYINT(1) NOT NULL DEFAULT 0,
  granted_by    INT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY school_approval_role (school_id, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;