import { Logger } from '@nestjs/common';
import { DataSource, QueryRunner } from 'typeorm';
import { withQueryRunner } from './database-migrations.runner';

export function attendanceSessionsDdl(tableName: string, idClause: string): string {
  return `
          CREATE TABLE ${tableName} (
            ${idClause},
            schedule_id INTEGER NOT NULL,
            class_id INTEGER NOT NULL,
            lesson_date DATE NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'in_progress',
            started_by INTEGER,
            started_at DATETIME,
            completed_by INTEGER,
            completed_at DATETIME,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (schedule_id) REFERENCES class_schedule(id) ON DELETE RESTRICT,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE RESTRICT
          )
        `;
}

export async function ensureCourseAttendanceSchema(
  dataSource: DataSource,
  logger: Logger,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    const tables = await runner.getTables([
      'class_schedule',
      'attendance_records',
      'attendance_sessions',
    ]);
    const tableNames = new Set(tables.map((table) => table.name));

    if (!tableNames.has('attendance_sessions')) {
      await runner.query(
        attendanceSessionsDdl('attendance_sessions', 'id INTEGER PRIMARY KEY AUTO_INCREMENT'),
      );
    }

    if (tableNames.has('class_schedule')) {
      const scheduleTable = await runner.getTable('class_schedule');
      const scheduleColumns = new Set(scheduleTable?.columns.map((column) => column.name) ?? []);
      if (!scheduleColumns.has('attendance_advance_minutes')) {
        await runner.query(
          'ALTER TABLE class_schedule ADD COLUMN attendance_advance_minutes INTEGER NOT NULL DEFAULT 30',
        );
        logger.log('已为排课添加课前签到分钟配置');
      }
    }

    const attendanceTable = await runner.getTable('attendance_records');
    const columnNames = new Set(attendanceTable?.columns.map((column) => column.name) ?? []);
    if (!columnNames.has('schedule_id')) {
      await runner.query('ALTER TABLE attendance_records ADD COLUMN schedule_id INTEGER');
    }
    if (!columnNames.has('attendance_session_id')) {
      await runner.query(
        'ALTER TABLE attendance_records ADD COLUMN attendance_session_id INTEGER',
      );
    }

    const createIndex = async (sql: string) => {
      try {
        await runner.query(sql);
      } catch {
        // Existing MySQL indexes cannot use IF NOT EXISTS; startup must stay idempotent.
      }
    };
    await createIndex(
      'CREATE UNIQUE INDEX uq_attendance_session_schedule_date ON attendance_sessions(schedule_id, lesson_date)',
    );
    await createIndex(
      'CREATE UNIQUE INDEX uq_attendance_session_student ON attendance_records(attendance_session_id, student_id)',
    );
  });
}

export async function ensureDingLeaveSchema(
  dataSource: DataSource,
  logger: Logger,
): Promise<void> {
  await withQueryRunner(dataSource, async (runner) => {
    await runner.query(`
      CREATE TABLE IF NOT EXISTS ding_leave_raw (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        ding_user_id VARCHAR(100) NOT NULL,
        user_name VARCHAR(100) NOT NULL DEFAULT '',
        work_date DATE NOT NULL,
        ding_id VARCHAR(100) NOT NULL,
        leave_type VARCHAR(100) NOT NULL DEFAULT '',
        tag_name VARCHAR(50) NOT NULL DEFAULT '',
        start_time DATETIME,
        end_time DATETIME,
        approved_at DATETIME,
        duration VARCHAR(20) NOT NULL DEFAULT '',
        duration_unit VARCHAR(20) NOT NULL DEFAULT '',
        match_status VARCHAR(20) NOT NULL DEFAULT 'unmatched',
        matched_student_id INTEGER,
        raw_data TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
    `);

    const createIndex = async (sql: string) => {
      try {
        await runner.query(sql);
      } catch {
        // Existing MySQL indexes cannot use IF NOT EXISTS; startup must stay idempotent.
      }
    };
    await createIndex('CREATE UNIQUE INDEX uq_ding_leave_raw_ding_id ON ding_leave_raw (ding_id)');
    await createIndex('CREATE INDEX idx_ding_leave_raw_work_date ON ding_leave_raw (work_date)');
    await createIndex('CREATE INDEX idx_ding_leave_raw_match_status ON ding_leave_raw (match_status)');
    logger.log('已确保钉钉请假原始表 ding_leave_raw');
  });
}

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

    await migrateMySQLAttendanceFKs(runner, logger);
  });
}

export async function migrateMySQLAttendanceFKs(
  runner: QueryRunner,
  logger: Logger,
): Promise<void> {
  // Drop any existing FK constraint on schedule_id or class_id
  const fkColumns = ['schedule_id', 'class_id'];
  for (const col of fkColumns) {
    const fkRows: { CONSTRAINT_NAME: string }[] = await runner.query(
      `
        SELECT CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'attendance_sessions'
          AND COLUMN_NAME = ?
          AND REFERENCED_TABLE_NAME IS NOT NULL
      `,
      [col],
    );

    for (const row of fkRows) {
      try {
        await runner.query(
          `ALTER TABLE attendance_sessions DROP FOREIGN KEY \`${row.CONSTRAINT_NAME}\``,
        );
        logger.log(`已移除考勤场次 FK 约束: ${row.CONSTRAINT_NAME}`);
      } catch {
        // constraint may have already been dropped
      }
    }
  }

  const constraints: Array<{ name: string; col: string; ref: string }> = [
    { name: 'fk_as_schedule_protect', col: 'schedule_id', ref: 'class_schedule(id)' },
    { name: 'fk_as_class_protect', col: 'class_id', ref: 'classes(id)' },
  ];
  for (const c of constraints) {
    // Only skip if RESTRICT constraint is already confirmed via information_schema
    const existing: Array<{ DELETE_RULE: string }> = await runner.query(
      `
        SELECT DELETE_RULE
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = DATABASE()
          AND TABLE_NAME = 'attendance_sessions'
          AND CONSTRAINT_NAME = ?
      `,
      [c.name],
    );

    if (existing.length > 0 && existing[0].DELETE_RULE === 'RESTRICT') {
      logger.log(`考勤场次删除保护约束已存在: ${c.name}`);
      continue;
    }

    // ADD RESTRICT must throw on failure — no catch
    await runner.query(`
        ALTER TABLE attendance_sessions
        ADD CONSTRAINT ${c.name}
        FOREIGN KEY (${c.col}) REFERENCES ${c.ref}
        ON DELETE RESTRICT
      `);
    logger.log(`已添加考勤场次删除保护约束: ${c.name}`);
  }
}
