import { DataSource } from 'typeorm';
import { withQueryRunner } from './database-migrations.runner';

export async function ensureSyncStateLeaseColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const table = await runner.getTable('sync_state');
    if (!table) return;
    const columns = new Set(table.columns.map((column) => column.name));
    if (!columns.has('run_id')) {
      await runner.query('ALTER TABLE sync_state ADD COLUMN run_id VARCHAR(64)');
    }
    if (!columns.has('running_since')) {
      await runner.query('ALTER TABLE sync_state ADD COLUMN running_since DATETIME');
    }
  });
}

export async function ensureStudentProfileCollegeColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const table = await runner.getTable('student_profiles');
    if (!table) return;
    const columns = new Set(table.columns.map((column) => column.name));
    const additions: Array<[string, string]> = [
      ['college_school', 'VARCHAR(100)'],
      ['college_major', 'VARCHAR(100)'],
    ];
    for (const [name, definition] of additions) {
      if (!columns.has(name)) await runner.query(`ALTER TABLE student_profiles ADD COLUMN ${name} ${definition}`);
    }
  });
}

export async function ensureAttendanceDevicesSchema(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const pk = 'INTEGER PRIMARY KEY AUTO_INCREMENT';
    await runner.query(`CREATE TABLE IF NOT EXISTS attendance_devices (
        id ${pk},
        device_sn VARCHAR(100) NOT NULL,
        device_name VARCHAR(100) NOT NULL,
        classroom_id INTEGER NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'active',
        location VARCHAR(200),
        notes TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
      )`);
    const table = await runner.getTable('attendance_devices');
    const columnNames = new Set(table?.columns.map((column) => column.name) ?? []);
    const additions: Array<[string, string]> = [
      ['device_sn', 'VARCHAR(100) NOT NULL DEFAULT \'\''],
      ['device_name', 'VARCHAR(100) NOT NULL DEFAULT \'\''],
      ['classroom_id', 'INTEGER NOT NULL DEFAULT 0'],
      ['status', "VARCHAR(20) NOT NULL DEFAULT 'active'"],
      ['location', 'VARCHAR(200)'],
      ['notes', 'TEXT'],
      ['created_at', 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP'],
      ['updated_at', 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP'],
    ];
    for (const [name, definition] of additions) {
      if (!columnNames.has(name)) await runner.query(`ALTER TABLE attendance_devices ADD COLUMN ${name} ${definition}`);
    }
    const refreshed = await runner.getTable('attendance_devices');
    const createIndex = async (sql: string) => {
      try {
        await runner.query(sql);
      } catch {
        // Existing MySQL indexes cannot use IF NOT EXISTS; startup must stay idempotent.
      }
    };
    const uniqueSn = refreshed?.indices.some((index) => index.columnNames.length === 1 && index.columnNames[0] === 'device_sn' && index.isUnique);
    if (!uniqueSn) {
      await createIndex(
        'CREATE UNIQUE INDEX idx_attendance_devices_device_sn ON attendance_devices (device_sn)',
      );
    }
    await createIndex(
      'CREATE INDEX idx_attendance_devices_classroom_id ON attendance_devices (classroom_id)',
    );
  });
}

export async function ensureStudentWalletSchema(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const pk = 'INTEGER PRIMARY KEY AUTO_INCREMENT';
    await runner.query(`CREATE TABLE IF NOT EXISTS student_wallets (
        id ${pk}, student_id INTEGER NOT NULL UNIQUE, balance DECIMAL(12,2) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
      )`);
    await runner.query(`CREATE TABLE IF NOT EXISTS wallet_transactions (
        id ${pk}, student_id INTEGER NOT NULL, bill_id INTEGER, type VARCHAR(30) NOT NULL,
        amount DECIMAL(12,2) NOT NULL, balance_after DECIMAL(12,2) NOT NULL,
        description VARCHAR(300), recorded_by INTEGER,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
      )`);
    await runner.query(`CREATE TABLE IF NOT EXISTS financial_operations (
        id ${pk}, operation_id VARCHAR(64) NOT NULL UNIQUE, type VARCHAR(64) NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'running', result_json TEXT, error_message VARCHAR(500),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
      )`);
    const walletTransactions = await runner.getTable('wallet_transactions');
    if (walletTransactions) {
      const columns = new Set(walletTransactions.columns.map((column) => column.name));
      if (!columns.has('operation_id')) {
        await runner.query('ALTER TABLE wallet_transactions ADD COLUMN operation_id VARCHAR(64)');
      }
    }
    const billItems = await runner.getTable('bill_items');
    if (billItems) {
      const columns = new Set(billItems.columns.map((column) => column.name));
      for (const [name, definition] of [
        ['room_expense_id', 'INTEGER'],
        ['personal_expense_id', 'INTEGER'],
      ]) {
        if (!columns.has(name)) await runner.query(`ALTER TABLE bill_items ADD COLUMN ${name} ${definition}`);
      }
    }
    const roomExpenses = await runner.getTable('room_expenses');
    if (roomExpenses) {
      const columns = new Set(roomExpenses.columns.map((column) => column.name));
      if (!columns.has('import_key')) {
        await runner.query('ALTER TABLE room_expenses ADD COLUMN import_key VARCHAR(120)');
      }
      const refreshedRoomExpenses = await runner.getTable('room_expenses');
      const hasImportKey = refreshedRoomExpenses?.indices.some((index) =>
        index.isUnique && index.columnNames.length === 1 && index.columnNames[0] === 'import_key');
      if (!hasImportKey) {
        await runner.query(
          'CREATE UNIQUE INDEX idx_room_expenses_import_key ON room_expenses (import_key)',
        );
      }
    }
    const bills = await runner.getTable('bills');
    if (bills) {
      const columns = new Set(bills.columns.map((column) => column.name));
      const additions = [
        ['source', "VARCHAR(30) NOT NULL DEFAULT 'batch'"],
        ['paid_amount', 'DECIMAL(10,2) NOT NULL DEFAULT 0'],
        ['outstanding_amount', 'DECIMAL(10,2) NOT NULL DEFAULT 0'],
        ['cancelled_at', 'DATETIME'],
        ['cancel_reason', 'VARCHAR(300)'],
      ];
      for (const [name, definition] of additions) {
        if (!columns.has(name)) await runner.query(`ALTER TABLE bills ADD COLUMN ${name} ${definition}`);
      }
      await runner.query("UPDATE bills SET outstanding_amount = total_amount WHERE outstanding_amount = 0 AND status <> 'paid'");
      await runner.query("UPDATE bills SET paid_amount = total_amount, outstanding_amount = 0 WHERE status = 'paid'");
      await runner.query("UPDATE bills SET status = 'unpaid' WHERE status IN ('draft', 'confirmed')");
    }
    const personalExpenses = await runner.getTable('personal_expenses');
    if (personalExpenses && !personalExpenses.columns.some((column) => column.name === 'bill_id')) {
      await runner.query('ALTER TABLE personal_expenses ADD COLUMN bill_id INTEGER');
    }
  });
}

export async function removeUnusedClassroomColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables(['classrooms']);
    if (tables.length === 0) return;

    const table = await runner.getTable('classrooms');
    const columnNames = new Set(table?.columns.map((column) => column.name) ?? []);
    for (const columnName of ['course_type', 'supervisor']) {
      if (columnNames.has(columnName)) {
        await runner.query(`ALTER TABLE classrooms DROP COLUMN ${columnName}`);
      }
    }
  });
}

export async function removeUnusedRoomColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables(['rooms']);
    if (tables.length === 0) return;

    const table = await runner.getTable('rooms');
    if (table?.columns.some((column) => column.name === 'gender')) {
      await runner.dropColumn('rooms', 'gender');
    }
  });
}

export async function cleanupDepositRefundColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables(['deposits']);
    if (tables.length === 0) return;

    const table = await runner.getTable('deposits');
    const columnNames = new Set(table?.columns.map((column) => column.name) ?? []);
    for (const [legacyName, currentName] of [
      ['refund_approved_by', 'refunded_by'],
      ['refund_approved_at', 'refunded_at'],
    ] as const) {
      if (!columnNames.has(legacyName)) continue;

      if (columnNames.has(currentName)) {
        await runner.query(
          `UPDATE deposits SET ${currentName} = COALESCE(${currentName}, ${legacyName})`,
        );
        await runner.dropColumn('deposits', legacyName);
      } else {
        await runner.renameColumn('deposits', legacyName, currentName);
        columnNames.add(currentName);
      }
      columnNames.delete(legacyName);
    }

    for (const columnName of ['refund_status', 'refund_requested_at', 'refund_rejected_reason']) {
      if (columnNames.has(columnName)) {
        await runner.dropColumn('deposits', columnName);
        columnNames.delete(columnName);
      }
    }
  });
}

export async function removeUnusedClassStudentColumns(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables(['class_student']);
    if (tables.length === 0) return;

    const table = await runner.getTable('class_student');
    if (table?.columns.some((column) => column.name === 'enrollment_id')) {
      await runner.dropColumn('class_student', 'enrollment_id');
    }
  });
}

export async function normalizeClassroomStatuses(
  dataSource: DataSource,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables(['classrooms']);
    if (tables.length === 0) return;
    await runner.query(`
        UPDATE classrooms
        SET status = 'available'
        WHERE status IS NULL OR status NOT IN ('available', 'maintenance', 'archived')
      `);
  });
}
