import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import type { OfficeSettings, HolidayEntry } from '../types/index.js';

function rowToSettings(row: Record<string, unknown>): OfficeSettings {
  return {
    startTime: row.start_time as string,
    endTime: row.end_time as string,
    weeklyOffDay: Number(row.weekly_off_day),
  };
}

export async function getSettings(conn?: PoolConnection): Promise<OfficeSettings> {
  const rows = await queryWithConn(
    `SELECT start_time, end_time, weekly_off_day FROM office_settings WHERE id = 'default'`, undefined, conn,
  ) as unknown as Record<string, unknown>[];
  return rowToSettings(rows[0]!);
}

export async function updateSettings(
  data: { startTime: string; endTime: string; weeklyOffDay: number }, userId: string, conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE office_settings SET start_time = ?, end_time = ?, weekly_off_day = ?, updated_by = ? WHERE id = 'default'`,
    [data.startTime, data.endTime, data.weeklyOffDay, userId], conn,
  );
}

function rowToHoliday(row: Record<string, unknown>): HolidayEntry {
  return {
    id: row.id as string,
    date: row.date as string,
    type: row.type as 'holiday' | 'half_day' | 'active_day',
    halfDayStart: (row.half_day_start as string) ?? undefined,
    halfDayEnd: (row.half_day_end as string) ?? undefined,
    remark: (row.remark as string) ?? undefined,
  };
}

export async function listHolidays(conn?: PoolConnection): Promise<HolidayEntry[]> {
  const rows = await queryWithConn(
    `SELECT id, date, type, half_day_start, half_day_end, remark FROM holiday_calendar ORDER BY date ASC`, undefined, conn,
  ) as unknown as Record<string, unknown>[];
  return rows.map(rowToHoliday);
}

export async function findHolidayByDate(date: string, conn?: PoolConnection): Promise<HolidayEntry | null> {
  const rows = await queryWithConn(
    `SELECT id, date, type, half_day_start, half_day_end, remark FROM holiday_calendar WHERE date = ?`, [date], conn,
  ) as unknown as Record<string, unknown>[];
  return rows.length ? rowToHoliday(rows[0]!) : null;
}

export async function findHolidayById(id: string, conn?: PoolConnection): Promise<HolidayEntry | null> {
  const rows = await queryWithConn(
    `SELECT id, date, type, half_day_start, half_day_end, remark FROM holiday_calendar WHERE id = ?`, [id], conn,
  ) as unknown as Record<string, unknown>[];
  return rows.length ? rowToHoliday(rows[0]!) : null;
}

export async function insertHoliday(
  data: { date: string; type: 'holiday' | 'half_day' | 'active_day'; halfDayStart?: string; halfDayEnd?: string; remark?: string },
  id: string, createdBy: string, conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `INSERT INTO holiday_calendar (id, date, type, half_day_start, half_day_end, remark, created_by)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [id, data.date, data.type, data.halfDayStart ?? null, data.halfDayEnd ?? null, data.remark ?? null, createdBy], conn,
  );
}

export async function updateHoliday(
  id: string,
  data: { date: string; type: 'holiday' | 'half_day' | 'active_day'; halfDayStart?: string; halfDayEnd?: string; remark?: string },
  updatedBy: string, conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE holiday_calendar SET date = ?, type = ?, half_day_start = ?, half_day_end = ?, remark = ?, updated_by = ? WHERE id = ?`,
    [data.date, data.type, data.halfDayStart ?? null, data.halfDayEnd ?? null, data.remark ?? null, updatedBy, id], conn,
  );
}

export async function removeHoliday(id: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(`DELETE FROM holiday_calendar WHERE id = ?`, [id], conn);
}