/**
 * Migration to ensure student_fees has plan_id, applied_by, academic_year columns,
 * and subjects_new table exists for legacy queries.
 */

exports.up = async function (knex) {
  // 1. Ensure student_fees has plan_id, applied_by, academic_year columns
  const hasPlanId = await knex.schema.hasColumn('student_fees', 'plan_id');
  if (!hasPlanId) {
    await knex.schema.alterTable('student_fees', (table) => {
      table.integer('plan_id').nullable();
    });
  }

  const hasAppliedBy = await knex.schema.hasColumn('student_fees', 'applied_by');
  if (!hasAppliedBy) {
    await knex.schema.alterTable('student_fees', (table) => {
      table.integer('applied_by').nullable();
    });
  }

  const hasAcademicYear = await knex.schema.hasColumn('student_fees', 'academic_year');
  if (!hasAcademicYear) {
    await knex.schema.alterTable('student_fees', (table) => {
      table.string('academic_year').nullable();
    });
  }

  // 2. Ensure subjects_new table exists for legacy controller queries
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS subjects_new (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      code TEXT,
      department_id INTEGER,
      class_id INTEGER,
      teacher_id INTEGER,
      credits INTEGER DEFAULT 1,
      description TEXT,
      syllabus_url TEXT,
      syllabus_name TEXT,
      created_at DATETIME DEFAULT (datetime('now', 'localtime')),
      updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    );
  `);
};

exports.down = async function (knex) {
  // no-op
};
