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

function rowToSeal(row: Record<string, unknown>): Seal {
  return {
    id: row.id as string,
    client_id: row.client_id as string,
    name: row.name as string,
  };
}

export async function findByClientId(clientId: string, conn?: PoolConnection): Promise<Seal[]> {
  const sql = `
    SELECT s.id as id, s.client_id as client_id, s.name
    FROM seals s
    WHERE s.client_id = ? AND s.deleted_at IS NULL
    ORDER BY s.name ASC
  `;
  const rows = await queryWithConn(sql, [clientId], conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToSeal);
}

export async function insert(clientId: string, name: string, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  const sql = `
    INSERT INTO seals (id, client_id, name, created_by)
    VALUES (?, ?, ?, ?)
  `;
  await executeWithConn(sql, [id, clientId, name, createdBy], conn);
}

export async function softDelete(id: string, deletedBy: string, conn?: PoolConnection): Promise<void> {
  const sql = 'UPDATE seals SET deleted_at = NOW(), deleted_by = ? WHERE id = ? AND deleted_at IS NULL';
  await executeWithConn(sql, [deletedBy, id], conn);
}