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

function rowToHallmarkGrade(row: Record<string, unknown>): HallmarkGrade {
  return { id: row.id as string, name: row.name as string };
}

export async function findAll(conn?: PoolConnection): Promise<HallmarkGrade[]> {
  const sql = `SELECT id as id, name FROM hallmark_grades WHERE deleted_at IS NULL ORDER BY name ASC`;
  const rows = await queryWithConn(sql, undefined, conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToHallmarkGrade);
}

export async function findById(id: string, conn?: PoolConnection): Promise<HallmarkGrade | null> {
  const sql = `SELECT id as id, name FROM hallmark_grades WHERE id = ? AND deleted_at IS NULL`;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToHallmarkGrade(rows[0]!) : null;
}

export async function findByName(name: string, excludeId?: string, conn?: PoolConnection): Promise<HallmarkGrade | null> {
  const sql = excludeId
    ? `SELECT id as id, name FROM hallmark_grades WHERE LOWER(name) = LOWER(?) AND id != ? AND deleted_at IS NULL LIMIT 1`
    : `SELECT id as id, name FROM hallmark_grades WHERE LOWER(name) = LOWER(?) AND deleted_at IS NULL LIMIT 1`;
  const params = excludeId ? [name, excludeId] : [name];
  const rows = await queryWithConn(sql, params, conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToHallmarkGrade(rows[0]!) : null;
}

export async function insert(name: string, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('INSERT INTO hallmark_grades (id, name, created_by) VALUES (?, ?, ?)', [id, name, createdBy], conn);
}

export async function update(id: string, name: string, updatedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('UPDATE hallmark_grades SET name = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL', [name, updatedBy, id], conn);
}

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

// Blocks deleting a hallmark grade that's still attached to a live
// (non-deleted) order line — mirrors product.repo.ts's hasProductsReferencing,
// but against order_products since hallmark grade is chosen per order line,
// not on the product/variant itself.
export async function hasOrderProductsReferencing(id: string, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    'SELECT 1 FROM order_products WHERE hallmark_grade_id = ? AND deleted_at IS NULL LIMIT 1',
    [id], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}