import { pgQuery } from './client';
import { nextPrefixedId } from '@/lib/data';
import type { NewAttendanceRecord, AttendanceAuditEntry, EmployeeBiometricMapping } from '@/lib/types';

/**
 * Get attendance records with filtering
 */
export async function pgGetAttendanceRecords(params: {
  page: number;
  pageSize: number;
  employeeId?: string;
  fromDate?: string;
  toDate?: string;
  recordType?: string;
  status?: string;
}) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let whereClause = `
    FROM attendance_records
    WHERE 1=1
  `;

  const params_list: any[] = [];

  if (params.employeeId) {
    whereClause += ` AND employee_id = $${params_list.length + 1}`;
    params_list.push(params.employeeId);
  }

  if (params.fromDate) {
    whereClause += ` AND date >= $${params_list.length + 1}`;
    params_list.push(params.fromDate);
  }

  if (params.toDate) {
    whereClause += ` AND date <= $${params_list.length + 1}`;
    params_list.push(params.toDate);
  }

  if (params.recordType) {
    whereClause += ` AND record_type = $${params_list.length + 1}`;
    params_list.push(params.recordType);
  }

  if (params.status) {
    whereClause += ` AND status = $${params_list.length + 1}`;
    params_list.push(params.status);
  }

  // Count total
  const countResult = await pgQuery(
    `SELECT COUNT(*) as count ${whereClause}`,
    params_list
  );
  const total = Number(countResult.rows[0]?.count || 0);

  // Get paginated results
  const dataQuery = `
    SELECT
      id, employee_id, date, record_type,
      check_in_time, check_out_time, duration_hours, late_minutes,
      device, device_id, ip_address,
      notes, justification, justification_type,
      status, approved_by, approval_date, approval_reason,
      created_at, created_by, updated_at, updated_by
    ${whereClause}
    ORDER BY date DESC, created_at DESC
    LIMIT $${params_list.length + 1} OFFSET $${params_list.length + 2}
  `;

  const dataResult = await pgQuery(
    dataQuery,
    [...params_list, limit, offset]
  );

  return {
    items: (dataResult.rows || []) as any[],
    total,
    page: params.page,
    pageSize: limit,
  };
}

/**
 * Get single attendance record
 */
export async function pgGetAttendanceRecordById(recordId: string) {
  const result = await pgQuery(
    `SELECT * FROM attendance_records WHERE id = $1`,
    [recordId]
  );
  return result.rows[0] as any;
}

/**
 * Create new attendance record
 */
export async function pgCreateAttendanceRecord(data: Omit<NewAttendanceRecord, 'id' | 'createdAt' | 'updatedAt'>) {
  const id = await nextPrefixedId('att');
  const now = new Date().toISOString();

  const result = await pgQuery(
    `INSERT INTO attendance_records (
      id, employee_id, date, record_type,
      check_in_time, check_out_time, duration_hours, late_minutes,
      device, device_id, ip_address,
      notes, justification, justification_type,
      status, created_by, updated_by
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
    RETURNING *`,
    [
      id,
      data.employeeId,
      data.date,
      data.recordType,
      data.checkInTime || null,
      data.checkOutTime || null,
      data.durationHours || null,
      data.lateMinutes || 0,
      data.device,
      data.deviceId || null,
      data.ipAddress || null,
      data.notes || null,
      data.justification || null,
      data.justificationType || null,
      data.status || 'pending',
      data.createdBy || null,
      data.createdBy || null,
    ]
  );

  await pgAppendAttendanceAuditLog({
    recordId: id,
    action: 'created',
    changedBy: data.createdBy || 'system',
    newValue: result.rows[0],
    reason: 'Record created',
  });

  return result.rows[0] as NewAttendanceRecord;
}

/**
 * Update attendance record
 */
export async function pgUpdateAttendanceRecord(recordId: string, updates: Partial<NewAttendanceRecord>, updatedBy: string) {
  const record = await pgGetAttendanceRecordById(recordId);
  if (!record) throw new Error('Attendance record not found');

  const setClause: string[] = [];
  const params_list: any[] = [];
  let paramIndex = 1;

  const fieldsMap: Record<string, string> = {
    recordType: 'record_type',
    checkInTime: 'check_in_time',
    checkOutTime: 'check_out_time',
    durationHours: 'duration_hours',
    lateMinutes: 'late_minutes',
    device: 'device',
    deviceId: 'device_id',
    ipAddress: 'ip_address',
    notes: 'notes',
    justification: 'justification',
    justificationType: 'justification_type',
    status: 'status',
  };

  for (const [key, dbField] of Object.entries(fieldsMap)) {
    if (key in updates && updates[key as keyof typeof updates] !== undefined) {
      setClause.push(`${dbField} = $${paramIndex}`);
      params_list.push(updates[key as keyof typeof updates]);
      paramIndex++;
    }
  }

  if (setClause.length === 0) return record;

  setClause.push(`updated_by = $${paramIndex}`);
  params_list.push(updatedBy);
  paramIndex++;

  setClause.push(`updated_at = CURRENT_TIMESTAMP`);

  const updateQuery = `
    UPDATE attendance_records
    SET ${setClause.join(', ')}
    WHERE id = $${paramIndex}
    RETURNING *
  `;

  const result = await pgQuery(updateQuery, [...params_list, recordId]);

  if (result.rows.length > 0) {
    await pgAppendAttendanceAuditLog({
      recordId,
      action: 'updated',
      changedBy: updatedBy,
      previousValue: record,
      newValue: result.rows[0],
      reason: 'Record updated',
    });
  }

  return result.rows[0] as NewAttendanceRecord;
}

/**
 * Approve attendance record
 */
export async function pgApproveAttendanceRecord(recordId: string, approvedBy: string, reason?: string) {
  const record = await pgGetAttendanceRecordById(recordId);
  if (!record) throw new Error('Attendance record not found');

  const result = await pgQuery(
    `UPDATE attendance_records
     SET status = 'approved', approved_by = $1, approval_date = CURRENT_TIMESTAMP, approval_reason = $2, updated_by = $1
     WHERE id = $3
     RETURNING *`,
    [approvedBy, reason || null, recordId]
  );

  if (result.rows.length > 0) {
    await pgAppendAttendanceAuditLog({
      recordId,
      action: 'approved',
      changedBy: approvedBy,
      previousValue: record,
      newValue: result.rows[0],
      reason: reason || 'Record approved',
    });
  }

  return result.rows[0] as NewAttendanceRecord;
}

/**
 * Reject attendance record
 */
export async function pgRejectAttendanceRecord(recordId: string, rejectedBy: string, reason?: string) {
  const record = await pgGetAttendanceRecordById(recordId);
  if (!record) throw new Error('Attendance record not found');

  const result = await pgQuery(
    `UPDATE attendance_records
     SET status = 'rejected', approved_by = $1, approval_date = CURRENT_TIMESTAMP, approval_reason = $2, updated_by = $1
     WHERE id = $3
     RETURNING *`,
    [rejectedBy, reason || 'Rejected', recordId]
  );

  if (result.rows.length > 0) {
    await pgAppendAttendanceAuditLog({
      recordId,
      action: 'rejected',
      changedBy: rejectedBy,
      previousValue: record,
      newValue: result.rows[0],
      reason: reason || 'Record rejected',
    });
  }

  return result.rows[0] as NewAttendanceRecord;
}

/**
 * Delete attendance record
 */
export async function pgDeleteAttendanceRecord(recordId: string, deletedBy: string, reason?: string) {
  const record = await pgGetAttendanceRecordById(recordId);
  if (!record) throw new Error('Attendance record not found');

  await pgAppendAttendanceAuditLog({
    recordId,
    action: 'deleted',
    changedBy: deletedBy,
    previousValue: record,
    reason: reason || 'Record deleted',
  });

  await pgQuery(`DELETE FROM attendance_records WHERE id = $1`, [recordId]);
  return true;
}

/**
 * Append to attendance audit log
 */
export async function pgAppendAttendanceAuditLog(data: Omit<AttendanceAuditEntry, 'id' | 'changedAt'>) {
  const id = await nextPrefixedId('att-audit');
  
  const result = await pgQuery(
    `INSERT INTO attendance_audit_log (
      id, record_id, action, changed_by, previous_value, new_value, reason
    ) VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING *`,
    [
      id,
      data.recordId,
      data.action,
      data.changedBy,
      data.previousValue ? JSON.stringify(data.previousValue) : null,
      data.newValue ? JSON.stringify(data.newValue) : null,
      data.reason || null,
    ]
  );

  return result.rows[0] as AttendanceAuditEntry;
}

/**
 * Get attendance audit log for a record
 */
export async function pgGetAttendanceAuditLog(recordId: string) {
  const result = await pgQuery(
    `SELECT * FROM attendance_audit_log WHERE record_id = $1 ORDER BY changed_at DESC`,
    [recordId]
  );

  return (result.rows || []) as AttendanceAuditEntry[];
}

/**
 * Manage employee biometric mappings
 */
export async function pgGetEmployeeBiometricMappings(params: {
  page: number;
  pageSize: number;
  employeeId?: string;
  isActive?: boolean;
}) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let query = `SELECT * FROM employee_biometric_mapping WHERE 1=1`;
  const params_list: any[] = [];

  if (params.employeeId) {
    query += ` AND employee_id = $${params_list.length + 1}`;
    params_list.push(params.employeeId);
  }

  if (params.isActive !== undefined) {
    query += ` AND is_active = $${params_list.length + 1}`;
    params_list.push(params.isActive);
  }

  const countResult = await pgQuery(
    query.replace('SELECT *', 'SELECT COUNT(*) as count'),
    params_list
  );
  const total = Number(countResult.rows[0]?.count || 0);

  const dataResult = await pgQuery(
    query + ` ORDER BY created_at DESC LIMIT $${params_list.length + 1} OFFSET $${params_list.length + 2}`,
    [...params_list, limit, offset]
  );

  return {
    items: (dataResult.rows || []) as EmployeeBiometricMapping[],
    total,
    page: params.page,
    pageSize: limit,
  };
}

/**
 * Add employee biometric mapping
 */
export async function pgCreateEmployeeBiometricMapping(data: Omit<EmployeeBiometricMapping, 'id' | 'createdAt' | 'updatedAt'>) {
  const id = await nextPrefixedId('ebm');
  const now = new Date().toISOString();

  const result = await pgQuery(
    `INSERT INTO employee_biometric_mapping (
      id, employee_id, employee_name, biometric_id, device_type, device_mac_address, is_active
    ) VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING *`,
    [id, data.employeeId, data.employeeName, data.biometricId, data.deviceType, data.deviceMacAddress || null, true]
  );

  return result.rows[0] as EmployeeBiometricMapping;
}

/**
 * Deactivate biometric mapping
 */
export async function pgDeactivateEmployeeBiometricMapping(mappingId: string) {
  await pgQuery(
    `UPDATE employee_biometric_mapping SET is_active = false WHERE id = $1`,
    [mappingId]
  );
  return true;
}

/**
 * Get biometric mapping by mapping ID
 */
export async function pgGetEmployeeBiometricMappingById(mappingId: string) {
  const result = await pgQuery(
    `SELECT * FROM employee_biometric_mapping WHERE id = $1`,
    [mappingId]
  );
  return result.rows[0] as EmployeeBiometricMapping | undefined;
}

/**
 * Get biometric mapping by employee ID
 */
export async function pgGetEmployeeBiometricByEmployeeId(employeeId: string) {
  const result = await pgQuery(
    `SELECT * FROM employee_biometric_mapping WHERE employee_id = $1 AND is_active = true`,
    [employeeId]
  );
  return result.rows[0] as EmployeeBiometricMapping | undefined;
}

/**
 * Get biometric mapping by biometric ID
 */
export async function pgGetEmployeeBiometricByBiometricId(biometricId: string) {
  const result = await pgQuery(
    `SELECT * FROM employee_biometric_mapping WHERE biometric_id = $1 AND is_active = true`,
    [biometricId]
  );
  return result.rows[0] as EmployeeBiometricMapping | undefined;
}

/**
 * Get monthly attendance summary grouped by employee
 */
export async function pgGetMonthlyAttendanceSummary(params: {
  fromDate: string;
  toDate: string;
  employeeId?: string;
}) {
  const paramsList: any[] = [params.fromDate, params.toDate];
  let employeeFilter = '';

  if (params.employeeId) {
    employeeFilter = ` AND employee_id = $3`;
    paramsList.push(params.employeeId);
  }

  const result = await pgQuery(
    `SELECT
      employee_id,
      COUNT(DISTINCT CASE WHEN record_type = 'present' AND status = 'approved' THEN date END) AS present_days,
      COUNT(DISTINCT CASE WHEN record_type = 'absent' AND status = 'approved' THEN date END) AS absent_days,
      COUNT(CASE WHEN record_type = 'late' AND status = 'approved' THEN 1 END) AS late_count,
      COUNT(DISTINCT CASE WHEN record_type = 'sick' AND status = 'approved' THEN date END) AS sick_days,
      COUNT(DISTINCT CASE WHEN record_type = 'leave' AND status = 'approved' THEN date END) AS leave_days,
      COALESCE(SUM(CASE WHEN status = 'approved' THEN duration_hours ELSE 0 END), 0) AS total_hours
     FROM attendance_records
     WHERE date >= $1
       AND date <= $2
       ${employeeFilter}
     GROUP BY employee_id
     ORDER BY employee_id ASC`,
    paramsList
  );

  return result.rows || [];
}

/**
 * Create attendance correction request
 */
export async function pgCreateAttendanceCorrectionRequest(data: {
  recordId: string;
  employeeId: string;
  reason: string;
  requestedRecordType?: string;
  requestedCheckInTime?: string;
  requestedCheckOutTime?: string;
}) {
  const id = await nextPrefixedId('att-corr');

  const result = await pgQuery(
    `INSERT INTO attendance_correction_requests (
      id, record_id, employee_id, reason,
      requested_record_type, requested_check_in_time, requested_check_out_time,
      status
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, 'pending')
    RETURNING *`,
    [
      id,
      data.recordId,
      data.employeeId,
      data.reason,
      data.requestedRecordType || null,
      data.requestedCheckInTime || null,
      data.requestedCheckOutTime || null,
    ]
  );

  return result.rows[0] as any;
}

/**
 * Get attendance correction requests
 */
export async function pgGetAttendanceCorrectionRequests(params: {
  page: number;
  pageSize: number;
  employeeId?: string;
  status?: string;
  fromDate?: string;
  toDate?: string;
}) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let query = `
    SELECT
      c.*,
      r.date as attendance_date,
      r.record_type as attendance_record_type,
      r.check_in_time as attendance_check_in_time,
      r.check_out_time as attendance_check_out_time
    FROM attendance_correction_requests c
    LEFT JOIN attendance_records r ON r.id = c.record_id
    WHERE 1=1
  `;

  const paramsList: any[] = [];

  if (params.employeeId) {
    query += ` AND c.employee_id = $${paramsList.length + 1}`;
    paramsList.push(params.employeeId);
  }

  if (params.status) {
    query += ` AND c.status = $${paramsList.length + 1}`;
    paramsList.push(params.status);
  }

  if (params.fromDate) {
    query += ` AND c.created_at::date >= $${paramsList.length + 1}`;
    paramsList.push(params.fromDate);
  }

  if (params.toDate) {
    query += ` AND c.created_at::date <= $${paramsList.length + 1}`;
    paramsList.push(params.toDate);
  }

  const countResult = await pgQuery(
    `SELECT COUNT(*) as count FROM (${query}) q`,
    paramsList
  );
  const total = Number(countResult.rows[0]?.count || 0);

  const dataResult = await pgQuery(
    query + ` ORDER BY c.created_at DESC LIMIT $${paramsList.length + 1} OFFSET $${paramsList.length + 2}`,
    [...paramsList, limit, offset]
  );

  return {
    items: dataResult.rows || [],
    total,
    page: params.page,
    pageSize: limit,
  };
}

/**
 * Get correction request by ID
 */
export async function pgGetAttendanceCorrectionRequestById(requestId: string) {
  const result = await pgQuery(
    `SELECT * FROM attendance_correction_requests WHERE id = $1`,
    [requestId]
  );
  return result.rows[0] as any;
}

/**
 * Approve correction request and apply updates on attendance record
 */
export async function pgApproveAttendanceCorrectionRequest(requestId: string, reviewedBy: string, reviewReason?: string) {
  const request = await pgGetAttendanceCorrectionRequestById(requestId);
  if (!request) throw new Error('Correction request not found');

  if (request.status !== 'pending') {
    return request;
  }

  const updates: Partial<NewAttendanceRecord> = {};
  if (request.requested_record_type) {
    updates.recordType = request.requested_record_type;
  }
  if (request.requested_check_in_time) {
    updates.checkInTime = request.requested_check_in_time;
  }
  if (request.requested_check_out_time) {
    updates.checkOutTime = request.requested_check_out_time;
  }

  if (Object.keys(updates).length > 0) {
    await pgUpdateAttendanceRecord(request.record_id, updates, reviewedBy);
  }

  const result = await pgQuery(
    `UPDATE attendance_correction_requests
     SET status = 'approved',
         reviewed_by = $1,
         reviewed_at = CURRENT_TIMESTAMP,
         review_reason = $2,
         updated_at = CURRENT_TIMESTAMP
     WHERE id = $3
     RETURNING *`,
    [reviewedBy, reviewReason || null, requestId]
  );

  return result.rows[0] as any;
}

/**
 * Reject correction request
 */
export async function pgRejectAttendanceCorrectionRequest(requestId: string, reviewedBy: string, reviewReason?: string) {
  const request = await pgGetAttendanceCorrectionRequestById(requestId);
  if (!request) throw new Error('Correction request not found');

  if (request.status !== 'pending') {
    return request;
  }

  const result = await pgQuery(
    `UPDATE attendance_correction_requests
     SET status = 'rejected',
         reviewed_by = $1,
         reviewed_at = CURRENT_TIMESTAMP,
         review_reason = $2,
         updated_at = CURRENT_TIMESTAMP
     WHERE id = $3
     RETURNING *`,
    [reviewedBy, reviewReason || null, requestId]
  );

  return result.rows[0] as any;
}

type PgPageParams = { page: number; pageSize: number };

export async function pgGetBiometricDevices(params: PgPageParams & { status?: string; providerType?: string; branchId?: string }) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let query = `SELECT * FROM biometric_devices WHERE 1=1`;
  const paramsList: any[] = [];

  if (params.status) {
    query += ` AND status = $${paramsList.length + 1}`;
    paramsList.push(params.status);
  }

  if (params.providerType) {
    query += ` AND provider_type = $${paramsList.length + 1}`;
    paramsList.push(params.providerType);
  }

  if (params.branchId) {
    query += ` AND branch_id = $${paramsList.length + 1}`;
    paramsList.push(params.branchId);
  }

  const countResult = await pgQuery(`SELECT COUNT(*) AS count FROM (${query}) q`, paramsList);
  const total = Number(countResult.rows[0]?.count || 0);

  const dataResult = await pgQuery(
    query + ` ORDER BY created_at DESC LIMIT $${paramsList.length + 1} OFFSET $${paramsList.length + 2}`,
    [...paramsList, limit, offset]
  );

  return {
    items: dataResult.rows || [],
    total,
    page: params.page,
    pageSize: limit,
  };
}

export async function pgCreateBiometricDevice(data: {
  deviceId: string;
  deviceName: string;
  brand?: string;
  model?: string;
  serialNumber: string;
  ipAddress: string;
  port?: number;
  communicationKey?: string;
  branchId?: string;
  timezone?: string;
  providerType: string;
  syncMode: 'push' | 'pull';
  status?: 'active' | 'inactive' | 'maintenance' | 'offline';
  lastSyncAt?: string;
  firmwareVersion?: string;
}) {
  const id = await nextPrefixedId('bio-dev');

  const result = await pgQuery(
    `INSERT INTO biometric_devices (
      id, device_id, device_name, brand, model, serial_number,
      ip_address, port, communication_key, branch_id, timezone,
      provider_type, sync_mode, status, last_sync_at, firmware_version
    ) VALUES (
      $1, $2, $3, $4, $5, $6,
      $7, $8, $9, $10, $11,
      $12, $13, $14, $15, $16
    )
    RETURNING *`,
    [
      id,
      data.deviceId,
      data.deviceName,
      data.brand || null,
      data.model || null,
      data.serialNumber,
      data.ipAddress,
      data.port || 4370,
      data.communicationKey || null,
      data.branchId || null,
      data.timezone || 'Asia/Riyadh',
      data.providerType,
      data.syncMode,
      data.status || 'active',
      data.lastSyncAt || null,
      data.firmwareVersion || null,
    ]
  );

  return result.rows[0] as any;
}

export async function pgGetBiometricDeviceById(id: string) {
  const result = await pgQuery(`SELECT * FROM biometric_devices WHERE id = $1`, [id]);
  return result.rows[0] as any;
}

export async function pgUpdateBiometricDeviceStatus(id: string, status: 'active' | 'inactive' | 'maintenance' | 'offline') {
  const result = await pgQuery(
    `UPDATE biometric_devices
     SET status = $1, updated_at = CURRENT_TIMESTAMP
     WHERE id = $2
     RETURNING *`,
    [status, id]
  );
  return result.rows[0] as any;
}

export async function pgTouchBiometricDeviceLastSync(id: string, lastSyncAt?: string) {
  const result = await pgQuery(
    `UPDATE biometric_devices
     SET last_sync_at = COALESCE($1::timestamptz, CURRENT_TIMESTAMP), updated_at = CURRENT_TIMESTAMP
     WHERE id = $2
     RETURNING *`,
    [lastSyncAt || null, id]
  );
  return result.rows[0] as any;
}

export async function pgGetBiometricEnrollments(params: PgPageParams & { employeeId?: string; deviceId?: string; enrollmentStatus?: string }) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let query = `
    SELECT
      e.*,
      d.device_name,
      d.device_id,
      d.provider_type,
      d.branch_id,
      emp.raw->>'name' AS employee_name
    FROM biometric_enrollments e
    INNER JOIN biometric_devices d ON d.id = e.device_id
    LEFT JOIN employees emp ON emp.id = e.employee_id
    WHERE 1=1
  `;
  const paramsList: any[] = [];

  if (params.employeeId) {
    query += ` AND e.employee_id = $${paramsList.length + 1}`;
    paramsList.push(params.employeeId);
  }

  if (params.deviceId) {
    query += ` AND e.device_id = $${paramsList.length + 1}`;
    paramsList.push(params.deviceId);
  }

  if (params.enrollmentStatus) {
    query += ` AND e.enrollment_status = $${paramsList.length + 1}`;
    paramsList.push(params.enrollmentStatus);
  }

  const countResult = await pgQuery(`SELECT COUNT(*) AS count FROM (${query}) q`, paramsList);
  const total = Number(countResult.rows[0]?.count || 0);

  const dataResult = await pgQuery(
    query + ` ORDER BY e.created_at DESC LIMIT $${paramsList.length + 1} OFFSET $${paramsList.length + 2}`,
    [...paramsList, limit, offset]
  );

  return {
    items: dataResult.rows || [],
    total,
    page: params.page,
    pageSize: limit,
  };
}

export async function pgCreateBiometricEnrollment(data: {
  employeeId: string;
  deviceId: string;
  biometricUserId: string;
  cardNumber?: string;
  enrollmentStatus?: 'active' | 'pending' | 'suspended' | 'revoked';
  enrolledAt?: string;
}) {
  const id = await nextPrefixedId('bio-enr');

  const result = await pgQuery(
    `INSERT INTO biometric_enrollments (
      id, employee_id, device_id, biometric_user_id, card_number, enrollment_status, enrolled_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING *`,
    [
      id,
      data.employeeId,
      data.deviceId,
      data.biometricUserId,
      data.cardNumber || null,
      data.enrollmentStatus || 'active',
      data.enrolledAt || null,
    ]
  );

  return result.rows[0] as any;
}

export async function pgGetBiometricEnrollmentById(id: string) {
  const result = await pgQuery(`SELECT * FROM biometric_enrollments WHERE id = $1`, [id]);
  return result.rows[0] as any;
}

export async function pgUpdateBiometricEnrollmentStatus(id: string, status: 'active' | 'pending' | 'suspended' | 'revoked') {
  const result = await pgQuery(
    `UPDATE biometric_enrollments
     SET enrollment_status = $1, updated_at = CURRENT_TIMESTAMP
     WHERE id = $2
     RETURNING *`,
    [status, id]
  );
  return result.rows[0] as any;
}

export async function pgGetAttendanceDeviceLogs(params: PgPageParams & {
  deviceId?: string;
  employeeId?: string;
  syncStatus?: string;
  fromDate?: string;
  toDate?: string;
}) {
  const offset = Math.max(0, (params.page - 1) * params.pageSize);
  const limit = Math.max(1, Math.min(500, params.pageSize));

  let query = `
    SELECT
      l.*,
      d.device_name,
      d.device_id,
      d.provider_type,
      emp.raw->>'name' AS employee_name,
      e.biometric_user_id
    FROM attendance_device_logs l
    INNER JOIN biometric_devices d ON d.id = l.device_id
    LEFT JOIN biometric_enrollments e ON e.id = l.enrollment_id
    LEFT JOIN employees emp ON emp.id = l.employee_id
    WHERE 1=1
  `;
  const paramsList: any[] = [];

  if (params.deviceId) {
    query += ` AND l.device_id = $${paramsList.length + 1}`;
    paramsList.push(params.deviceId);
  }

  if (params.employeeId) {
    query += ` AND l.employee_id = $${paramsList.length + 1}`;
    paramsList.push(params.employeeId);
  }

  if (params.syncStatus) {
    query += ` AND l.sync_status = $${paramsList.length + 1}`;
    paramsList.push(params.syncStatus);
  }

  if (params.fromDate) {
    query += ` AND l.log_time::date >= $${paramsList.length + 1}`;
    paramsList.push(params.fromDate);
  }

  if (params.toDate) {
    query += ` AND l.log_time::date <= $${paramsList.length + 1}`;
    paramsList.push(params.toDate);
  }

  const countResult = await pgQuery(`SELECT COUNT(*) AS count FROM (${query}) q`, paramsList);
  const total = Number(countResult.rows[0]?.count || 0);

  const dataResult = await pgQuery(
    query + ` ORDER BY l.log_time DESC LIMIT $${paramsList.length + 1} OFFSET $${paramsList.length + 2}`,
    [...paramsList, limit, offset]
  );

  return {
    items: dataResult.rows || [],
    total,
    page: params.page,
    pageSize: limit,
  };
}

export async function pgCreateAttendanceDeviceLog(data: {
  deviceId: string;
  enrollmentId?: string;
  employeeId?: string;
  logTime: string;
  rawPayload?: Record<string, any>;
  processedRecordId?: string;
  sourceType?: string;
  punchType?: string;
  syncStatus?: 'pending' | 'processed' | 'failed' | 'duplicate' | 'retried';
  duplicateKey?: string;
  isDuplicate?: boolean;
  manualAdjustment?: boolean;
  manualAdjustedBy?: string;
  manualAdjustmentReason?: string;
  retryCount?: number;
  auditTrail?: Record<string, any>;
}) {
  const id = await nextPrefixedId('bio-log');

  const result = await pgQuery(
    `INSERT INTO attendance_device_logs (
      id, device_id, enrollment_id, employee_id, log_time,
      raw_payload, processed_record_id, source_type, punch_type, sync_status,
      duplicate_key, is_duplicate, manual_adjustment, manual_adjusted_by,
      manual_adjustment_reason, retry_count, audit_trail
    ) VALUES (
      $1, $2, $3, $4, $5,
      $6, $7, $8, $9, $10,
      $11, $12, $13, $14,
      $15, $16, $17
    )
    RETURNING *`,
    [
      id,
      data.deviceId,
      data.enrollmentId || null,
      data.employeeId || null,
      data.logTime,
      data.rawPayload ? JSON.stringify(data.rawPayload) : null,
      data.processedRecordId || null,
      data.sourceType || 'device',
      data.punchType || null,
      data.syncStatus || 'pending',
      data.duplicateKey || null,
      data.isDuplicate === true,
      data.manualAdjustment === true,
      data.manualAdjustedBy || null,
      data.manualAdjustmentReason || null,
      data.retryCount || 0,
      data.auditTrail ? JSON.stringify(data.auditTrail) : null,
    ]
  );

  return result.rows[0] as any;
}

export async function pgGetBiometricHealthSummary() {
  const [devicesAgg, logsAgg] = await Promise.all([
    pgQuery<{
      total_devices: string;
      offline_devices: string;
      active_devices: string;
      stale_sync_devices: string;
    }>(
      `SELECT
         COUNT(*)::text AS total_devices,
         COUNT(*) FILTER (WHERE status = 'offline')::text AS offline_devices,
         COUNT(*) FILTER (WHERE status = 'active')::text AS active_devices,
         COUNT(*) FILTER (WHERE last_sync_at IS NULL OR last_sync_at < NOW() - INTERVAL '24 hours')::text AS stale_sync_devices
       FROM biometric_devices`
    ),
    pgQuery<{
      total_logs: string;
      failed_sync_logs: string;
      duplicate_logs: string;
      retried_logs: string;
      pending_logs: string;
    }>(
      `SELECT
         COUNT(*)::text AS total_logs,
         COUNT(*) FILTER (WHERE sync_status = 'failed')::text AS failed_sync_logs,
         COUNT(*) FILTER (WHERE sync_status = 'duplicate' OR is_duplicate = true)::text AS duplicate_logs,
         COUNT(*) FILTER (WHERE retry_count > 0 OR sync_status = 'retried')::text AS retried_logs,
         COUNT(*) FILTER (WHERE sync_status = 'pending')::text AS pending_logs
       FROM attendance_device_logs`
    ),
  ]);

  const d = devicesAgg.rows[0] || {
    total_devices: '0',
    offline_devices: '0',
    active_devices: '0',
    stale_sync_devices: '0',
  };
  const l = logsAgg.rows[0] || {
    total_logs: '0',
    failed_sync_logs: '0',
    duplicate_logs: '0',
    retried_logs: '0',
    pending_logs: '0',
  };

  return {
    totalDevices: Number(d.total_devices || 0),
    offlineDevices: Number(d.offline_devices || 0),
    activeDevices: Number(d.active_devices || 0),
    staleSyncDevices: Number(d.stale_sync_devices || 0),
    totalLogs: Number(l.total_logs || 0),
    failedSyncLogs: Number(l.failed_sync_logs || 0),
    duplicateLogs: Number(l.duplicate_logs || 0),
    retriedLogs: Number(l.retried_logs || 0),
    pendingLogs: Number(l.pending_logs || 0),
  };
}

