/**
 * Knex migration for Medical Document Ingestion & Kitchen Dining Hall Allergy Alerts
 * Creates `student_medical_documents` and `dining_hall_alerts` tables with offline sync support.
 */

exports.up = async function(knex) {
  // 1. student_medical_documents (Lab reports, doctor notes, scanned records, file attachments)
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS student_medical_documents (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL REFERENCES users(id),
      file_name TEXT NOT NULL,
      file_url TEXT NOT NULL,
      file_type TEXT DEFAULT 'pdf',
      document_type TEXT DEFAULT 'lab_report' CHECK(document_type IN ('lab_report', 'doctor_note', 'prescription', 'vaccination_cert', 'ingested_record', 'other')),
      notes TEXT,
      uploaded_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
    );
  `);

  // 2. dining_hall_alerts (Broadcast allergy & dietary restriction alerts to Kitchen / Dining Staff)
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS dining_hall_alerts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL REFERENCES users(id),
      allergy_details TEXT NOT NULL,
      severity TEXT DEFAULT 'severe' CHECK(severity IN ('mild', 'severe', 'life_threatening')),
      special_instructions TEXT,
      alert_status TEXT DEFAULT 'active' CHECK(alert_status IN ('active', 'acknowledged', 'resolved')),
      acknowledged_by INTEGER REFERENCES users(id),
      acknowledged_at DATETIME,
      flagged_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
    );
  `);
};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS dining_hall_alerts;');
  await knex.schema.raw('DROP TABLE IF EXISTS student_medical_documents;');
};
