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

function rowToProduct(row: Record<string, unknown>): Product {
  return {
    id: row.id as string,
    name: row.name as string,
    item_id: (row.item_id as string) ?? undefined,
    metal_type_id: (row.metal_type_id as string) ?? undefined,
    purity_id: (row.purity_id as string) ?? undefined,
    category_id: (row.category_id as string) ?? undefined,
    size_id: (row.size_id as string) ?? undefined,
    weight_id: (row.weight_id as string) ?? undefined,
    wire_size_id: (row.wire_size_id as string) ?? undefined,
    hook_id: (row.hook_id as string) ?? undefined,
    endcap_id: (row.endcap_id as string) ?? undefined,
    huid: (row.huid as string) ?? undefined,
  };
}

const PRODUCT_COLUMNS = `p.id as id, p.name, p.item_id, p.metal_type_id, p.purity_id,
           p.category_id as category_id, p.size_id, p.weight_id, p.wire_size_id, p.hook_id, p.endcap_id, p.huid`;

export async function findAll(
  search?: string,
  categoryId?: string,
  page = 1,
  limit = 20,
  conn?: PoolConnection,
): Promise<{ rows: Product[]; total: number }> {
  const offset = (Number(page) - 1) * Number(limit);
  const conditions = ['p.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (search) {
    conditions.push('p.name LIKE ?');
    params.push(`%${search}%`);
  }
  if (categoryId) {
    conditions.push('p.category_id = ?');
    params.push(categoryId);
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const countRows = await queryWithConn(`SELECT COUNT(*) as total FROM products p ${where}`, params.length ? params : undefined, conn) as unknown as { total: number }[];
  const total = countRows[0]?.total ?? 0;

  const dataSql = `
    SELECT ${PRODUCT_COLUMNS}
    FROM products p ${where}
    ORDER BY p.name ASC
    LIMIT ? OFFSET ?
  `;
  const dataParams = [...params, Number(limit), Number(offset)];
  const dataRows = await queryWithConn(dataSql, dataParams, conn) as unknown as Record<string, unknown>[];
  return { rows: dataRows.map(rowToProduct), total };
}

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

// Case-insensitive duplicate check — the generated variant name (Item-
// Category-Purity-ChainSize-Weight, see buildVariantName()) is effectively
// the variant's identity, so two rows resolving to the same name are the
// same variant re-created rather than a legitimately distinct one.
export async function findByName(name: string, excludeId?: string, conn?: PoolConnection): Promise<Product | null> {
  const sql = excludeId
    ? `SELECT ${PRODUCT_COLUMNS} FROM products p WHERE LOWER(p.name) = LOWER(?) AND p.id != ? AND p.deleted_at IS NULL LIMIT 1`
    : `SELECT ${PRODUCT_COLUMNS} FROM products p WHERE LOWER(p.name) = LOWER(?) AND p.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 ? rowToProduct(rows[0]!) : null;
}

// Batch lookup for callers (e.g. reports) that need many products by id at
// once — one query instead of one per row.
export async function findByIds(ids: string[], conn?: PoolConnection): Promise<Product[]> {
  if (ids.length === 0) return [];
  const unique = [...new Set(ids)];
  const sql = `
    SELECT ${PRODUCT_COLUMNS}
    FROM products p
    WHERE p.id IN (${unique.map(() => '?').join(',')}) AND p.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, unique, conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToProduct);
}

export async function insert(data: Partial<Product>, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  const sql = `
    INSERT INTO products (id, name, item_id, metal_type_id, purity_id, category_id, size_id, weight_id, wire_size_id, hook_id, endcap_id, huid, created_by)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;
  await executeWithConn(sql, [
    id, data.name,
    data.item_id ?? null,
    data.metal_type_id ?? null, data.purity_id ?? null,
    data.category_id ? data.category_id : null,
    data.size_id ?? null, data.weight_id ?? null,
    data.wire_size_id ?? null, data.hook_id ?? null, data.endcap_id ?? null,
    data.huid ?? null,
    createdBy,
  ], conn);
}

export async function update(id: string, data: Partial<Product>, updatedBy: string, conn?: PoolConnection): Promise<void> {
  const sets: string[] = [];
  const params: unknown[] = [];

  if (data.name !== undefined) { sets.push('name = ?'); params.push(data.name); }
  if (data.item_id !== undefined) { sets.push('item_id = ?'); params.push(data.item_id || null); }
  if (data.metal_type_id !== undefined) { sets.push('metal_type_id = ?'); params.push(data.metal_type_id || null); }
  if (data.purity_id !== undefined) { sets.push('purity_id = ?'); params.push(data.purity_id || null); }
  if (data.category_id !== undefined) { sets.push('category_id = ?'); params.push(data.category_id || null); }
  if (data.size_id !== undefined) { sets.push('size_id = ?'); params.push(data.size_id || null); }
  if (data.weight_id !== undefined) { sets.push('weight_id = ?'); params.push(data.weight_id || null); }
  if (data.wire_size_id !== undefined) { sets.push('wire_size_id = ?'); params.push(data.wire_size_id || null); }
  if (data.hook_id !== undefined) { sets.push('hook_id = ?'); params.push(data.hook_id || null); }
  if (data.endcap_id !== undefined) { sets.push('endcap_id = ?'); params.push(data.endcap_id || null); }
  if (data.huid !== undefined) { sets.push('huid = ?'); params.push(data.huid || null); }

  if (sets.length === 0) return;
  sets.push('updated_by = ?');
  params.push(updatedBy);
  params.push(id);

  await executeWithConn(`UPDATE products SET ${sets.join(', ')} WHERE id = ? AND deleted_at IS NULL`, params, conn);
}

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

const REFERENCING_COLUMNS = [
  'metal_type_id', 'purity_id', 'category_id', 'size_id', 'weight_id',
  'item_id', 'wire_size_id', 'hook_id', 'endcap_id',
] as const;
export type ReferencingColumn = (typeof REFERENCING_COLUMNS)[number];

// Blocks deleting a metal type/purity/category/size/weight/item/wire size/
// hook/endcap that's still attached to a live product (variant) — without
// this, the product's FK (ON DELETE SET NULL) would silently blank that
// field on every referencing row.
export async function hasProductsReferencing(column: ReferencingColumn, id: string, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    `SELECT 1 FROM products WHERE ${column} = ? AND deleted_at IS NULL LIMIT 1`,
    [id], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}

// Used by product.service.ts's regenerateNamesReferencing() — every variant
// pointing at a master row that just got renamed (item/category/purity/
// chain size/weight/wire size) needs its stored `name` recomputed, since
// that name is generated once at create/update time, not read live from the
// master tables on every request.
export async function findAllReferencing(column: ReferencingColumn, id: string, conn?: PoolConnection): Promise<Product[]> {
  const rows = await queryWithConn(
    `SELECT ${PRODUCT_COLUMNS} FROM products p WHERE p.${column} = ? AND p.deleted_at IS NULL`,
    [id], conn,
  ) as unknown as Record<string, unknown>[];
  return rows.map(rowToProduct);
}