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

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

export async function findAll(conn?: PoolConnection): Promise<Weight[]> {
  const sql = `SELECT id as id, name FROM weights 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(rowToWeight);
}

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

export async function findByName(name: string, excludeId?: string, conn?: PoolConnection): Promise<Weight | null> {
  const sql = excludeId
    ? `SELECT id as id, name FROM weights WHERE LOWER(name) = LOWER(?) AND id != ? AND deleted_at IS NULL LIMIT 1`
    : `SELECT id as id, name FROM weights 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 ? rowToWeight(rows[0]!) : null;
}

export async function insert(name: string, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('INSERT INTO weights (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 weights 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 weights SET deleted_at = NOW(), deleted_by = ? WHERE id = ? AND deleted_at IS NULL', [deletedBy, id], conn);
}