/**
 * Migration: Student Medical Records
 *
 * Brings older school.db files up to the schema required by the
 * student-medical-records feature:
 *   1. CREATE TABLE IF NOT EXISTS for the 3 medical tables
 *      (student_medical_profiles, student_medication_logs, student_medical_history)
 *      plus their indexes. Idempotent — no-op on fresh DBs that already have them.
 *   2. Recreates the users table with the expanded role CHECK constraint
 *      (`dining_staff` is new). Older DBs that lack that role will reject
 *      INSERT for that role string until this migration runs.
 *
 * Idempotent: detects whether the new role is already accepted and skips the
 * users table rename-and-recreate when it is.
 *
 * Run with:  node server/src/database/migration_medical_records.js
 * (Safe to re-run any number of times.)
 */

const Database = require('better-sqlite3');
const path = require('path');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

console.log('Running migration: Student Medical Records ...');

// FK enforcement must be disabled for this migration. After
// `ALTER TABLE users RENAME TO users_old`, child tables (parent_students,
// student_social_docs, …) have their FKs rewritten to point at
// `users_old`. Dropping `users_old` would then violate those FKs.
// `PRAGMA foreign_keys = OFF` is the standard SQLite migration escape hatch.
const FK_ON_BEFORE = db.pragma('foreign_keys', { simple: true });
db.pragma('foreign_keys = OFF');

try {
  db.transaction(() => {
    // 1. Medical tables — CREATE TABLE IF NOT EXISTS is idempotent on its own.
    db.exec(`
      CREATE TABLE IF NOT EXISTS student_medical_profiles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE,
        student_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
        blood_group TEXT CHECK(blood_group IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-', 'Unknown')),
        allergies TEXT,
        dietary_restrictions TEXT,
        contraindications TEXT,
        chronic_conditions TEXT,
        emergency_instructions TEXT,
        updated_by INTEGER REFERENCES users(id),
        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
      );

      CREATE TABLE IF NOT EXISTS student_medication_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE,
        student_id INTEGER NOT NULL REFERENCES users(id),
        medication_name TEXT NOT NULL,
        dosage TEXT NOT NULL,
        administered_at DATETIME NOT NULL,
        administered_by INTEGER NOT NULL REFERENCES users(id),
        reason TEXT,
        notes TEXT,
        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
      );

      CREATE TABLE IF NOT EXISTS student_medical_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE,
        student_id INTEGER NOT NULL REFERENCES users(id),
        event_type TEXT NOT NULL CHECK(event_type IN ('illness', 'injury', 'hospitalization')),
        description TEXT NOT NULL,
        severity TEXT DEFAULT 'moderate' CHECK(severity IN ('mild', 'moderate', 'severe')),
        onset_date DATE,
        resolution_date DATE,
        treatment_details TEXT,
        doctor_notes TEXT,
        is_visible_to_teachers INTEGER DEFAULT 1,
        reported_by INTEGER REFERENCES users(id),
        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
      );
    `);
    console.log('Medical tables ensured.');

    db.exec(`
      CREATE INDEX IF NOT EXISTS idx_med_profiles_student ON student_medical_profiles(student_id);
      CREATE INDEX IF NOT EXISTS idx_med_profiles_sync ON student_medical_profiles(sync_status, is_deleted);
      CREATE INDEX IF NOT EXISTS idx_med_logs_student ON student_medication_logs(student_id);
      CREATE INDEX IF NOT EXISTS idx_med_logs_administered ON student_medication_logs(administered_at DESC);
      CREATE INDEX IF NOT EXISTS idx_med_logs_sync ON student_medication_logs(sync_status, is_deleted);
      CREATE INDEX IF NOT EXISTS idx_med_history_student ON student_medical_history(student_id);
      CREATE INDEX IF NOT EXISTS idx_med_history_teacher_visible ON student_medical_history(is_visible_to_teachers, is_deleted);
      CREATE INDEX IF NOT EXISTS idx_med_history_sync ON student_medical_history(sync_status, is_deleted);
    `);
    console.log('Medical indexes ensured.');

    // 2. users.role CHECK — extend with 'dining_staff'.
    const usersCreateSql = db.prepare("SELECT sql FROM sqlite_master WHERE name='users'").get()?.sql || '';
    const hasDiningStaff = usersCreateSql.includes("'dining_staff'");

    if (!hasDiningStaff) {
      console.log('Expanding users.role CHECK to include dining_staff (rename-and-recreate)...');

      db.exec('ALTER TABLE users RENAME TO users_old');

      db.exec(`
        CREATE TABLE users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          uid TEXT UNIQUE,
          email TEXT UNIQUE NOT NULL,
          password TEXT NOT NULL,
          role TEXT NOT NULL CHECK(role IN ('systems_admin', 'school_admin', 'teacher', 'student', 'parent', 'accountant', 'librarian', 'nurse', 'clubs_head', 'principal', 'hr', 'bursar', 'dining_staff')),
          first_name TEXT NOT NULL,
          last_name TEXT NOT NULL,
          phone,
          address,
          date_of_birth DATE,
          gender TEXT CHECK(gender IN ('male', 'female', 'other')),
          id_number TEXT,
          profile_image TEXT,
          emergency_contact TEXT,
          emergency_phone TEXT,
          department_id INTEGER REFERENCES departments(id),
          is_active INTEGER DEFAULT 1,
          last_login DATETIME,
          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
        )
      `);

      // Copy all columns. Note SQLite's INSERT ... SELECT preserves NULLs naturally.
      db.exec(`
        INSERT INTO users (
          id, uid, email, password, role, first_name, last_name, phone, address,
          date_of_birth, gender, id_number, profile_image, emergency_contact,
          emergency_phone, department_id, is_active, last_login, created_at,
          updated_at, last_synced_at, sync_status, is_deleted
        )
        SELECT
          id, uid, email, password, role, first_name, last_name, phone, address,
          date_of_birth, gender, id_number, profile_image, emergency_contact,
          emergency_phone, department_id, is_active, last_login, created_at,
          updated_at, last_synced_at, sync_status, is_deleted
        FROM users_old
      `);

      db.exec('DROP TABLE users_old');

      // Recreate the standard users indexes (mirrors init.js).
      db.exec('CREATE INDEX IF NOT EXISTS idx_users_sync ON users(sync_status, is_deleted)');
      db.exec('CREATE INDEX IF NOT EXISTS idx_users_uid ON users(uid)');
      db.exec('CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)');
      db.exec('CREATE INDEX IF NOT EXISTS idx_users_role ON users(role)');
      db.exec('CREATE INDEX IF NOT EXISTS idx_users_dept ON users(department_id)');

      console.log('Users table recreated with dining_staff role accepted.');
    } else {
      console.log('users.role CHECK already accepts dining_staff — no recreate needed.');
    }

    // Seed the demo dining-staff user that init.js creates on fresh DBs.
    // Idempotent: only inserts if no row with this email exists yet. Runs in
    // both branches so a pre-existing prod DB that already has dining_staff in
    // the CHECK (e.g. via an earlier init.js update) still gets the demo user.
    const existingDining = db.prepare('SELECT id FROM users WHERE email = ?').get('dining@school.com');
    if (!existingDining) {
      const bcrypt = require('bcryptjs');
      const { v4: uuidv4 } = require('uuid');
      db.prepare(`
        INSERT INTO users (uid, email, password, role, first_name, last_name, phone)
        VALUES (?, 'dining@school.com', ?, 'dining_staff', 'Chenai', 'Mvubu', '+263770000002')
      `).run(uuidv4(), bcrypt.hashSync('dining123', 10));
      console.log('Seeded dining@school.com / dining123');
    }
  })();

  console.log('Migration complete!');
} catch (error) {
  console.error('Migration failed:', error);
  process.exitCode = 1;
} finally {
  // Restore FK enforcement (FK_ON_BEFORE was '1' on the dev DB but the
  // connection pragma may have been '0' if the runner never set it).
  // best-effort; ignore failures.
  try { db.pragma('foreign_keys = ' + FK_ON_BEFORE); } catch {}
}

process.exit(process.exitCode || 0);
