/**
 * Knex migration for Offboarding Workflows
 * Creates `offboarding_records` and `offboarding_actions` tables, and adds `archive_status` column to `users`.
 */

exports.up = async function(knex) {
  // 1. offboarding_records
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS offboarding_records (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      user_id INTEGER NOT NULL REFERENCES users(id),
      audience TEXT NOT NULL CHECK(audience IN ('student', 'staff')),
      reason TEXT NOT NULL CHECK(reason IN (
        'graduated', 'transferred', 'withdrawn', 'expelled',
        'resigned', 'terminated', 'retired', 'deceased', 'other'
      )),
      reason_notes TEXT,
      initiated_by INTEGER REFERENCES users(id),
      status TEXT DEFAULT 'in_progress' CHECK(status IN ('in_progress', 'completed', 'cancelled')),
      effective_date DATE NOT NULL,
      destination TEXT,
      alumni_status TEXT CHECK(alumni_status IN ('active_alumni', 'inactive_alumni')),
      created_at DATETIME DEFAULT (datetime('now', 'localtime')),
      updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced', 'pending', 'conflict')),
      is_deleted INTEGER DEFAULT 0
    );
  `);

  // 2. offboarding_actions
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS offboarding_actions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      record_id INTEGER NOT NULL REFERENCES offboarding_records(id) ON DELETE CASCADE,
      step TEXT NOT NULL CHECK(step IN (
        'enrollment_closed', 'hostel_released', 'transport_removed',
        'clubs_removed', 'fee_settled', 'leave_closed', 'payroll_finalized',
        'class_assignments_cleared', 'subject_assignments_cleared',
        'account_deactivated', 'record_archived'
      )),
      status TEXT NOT NULL CHECK(status IN ('pending', 'completed', 'skipped', 'failed')),
      notes TEXT,
      performed_by INTEGER REFERENCES users(id),
      performed_at DATETIME DEFAULT (datetime('now', 'localtime')),
      created_at DATETIME DEFAULT (datetime('now', 'localtime')),
      updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced', 'pending', 'conflict')),
      is_deleted INTEGER DEFAULT 0
    );
  `);

  // 3. ALTER users table
  try {
    await knex.schema.raw(`
      ALTER TABLE users ADD COLUMN archive_status TEXT CHECK(archive_status IN ('active_alumni', 'inactive_alumni', 'former_staff'));
    `);
  } catch (error) {
    // If the column already exists (e.g. running seed/init logic again)
    console.log('[migrate] users.archive_status column might already exist:', error.message);
  }
};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS offboarding_actions;');
  await knex.schema.raw('DROP TABLE IF EXISTS offboarding_records;');
  // Note: SQLite doesn't easily support dropping a column in ALTER TABLE without recreating the table.
  // We can leave archive_status on users, or drop and recreate if strict rollback is needed.
  // But generally, leaving it is standard practice in SQLite migrations.
};
