import { query } from "../../db/pool.js";
import { normalizeCatalogName } from "./catalog-normalization.js";

export type CatalogKind = "store" | "brand" | "product";
export type CatalogStatus = "seeded" | "verified" | "pending" | "rejected";

export interface CanonicalCatalogEntry {
  id: number;
  kind: CatalogKind;
  canonical_name: string;
  display_name: string;
  normalized_name: string;
  status: CatalogStatus;
  metadata: Record<string, unknown>;
  source_label: string;
  source_url: string;
  alias_count: number;
  observed_count: number;
  created_at: string;
  updated_at: string;
}

export interface CatalogObservation {
  raw_value: string;
  normalized_value: string;
  observed_count: number;
  matched_entry_id: number | null;
  matched_display_name: string | null;
}

export interface AdminCatalogSummary {
  entries: CanonicalCatalogEntry[];
  observations: CatalogObservation[];
}

export interface CatalogSuggestion {
  raw_value: string;
  normalized_value: string;
  observed_count: number;
  suggested_action: "alias" | "create";
  suggested_entry_id: number | null;
  suggested_display_name: string | null;
  suggested_canonical_name: string | null;
  similarity: number;
  reason: string;
}

export interface CatalogEntryInput {
  kind: CatalogKind;
  canonicalName: string;
  displayName: string;
  status: CatalogStatus;
  sourceLabel?: string;
  sourceUrl?: string;
}

export interface ResolvedCatalogEntry {
  id: number;
  display_name: string;
  canonical_name: string;
  normalized_name: string;
}

function normalizeByKind(kind: CatalogKind, value: string): string {
  return normalizeCatalogName(value, { stripLocation: kind === "store" });
}

function tokenize(value: string): string[] {
  return value
    .split(" ")
    .map((part) => part.trim())
    .filter((part) => part.length >= 3);
}

function diceCoefficient(left: string, right: string): number {
  if (!left || !right) return 0;
  if (left === right) return 1;
  const leftTokens = tokenize(left);
  const rightTokens = tokenize(right);
  if (leftTokens.length === 0 || rightTokens.length === 0) return 0;
  const leftSet = new Set(leftTokens);
  const rightSet = new Set(rightTokens);
  let shared = 0;
  for (const token of leftSet) {
    if (rightSet.has(token)) shared += 1;
  }
  return (2 * shared) / (leftSet.size + rightSet.size);
}

export async function resolveCatalogEntry(kind: CatalogKind, rawValue: string): Promise<ResolvedCatalogEntry | null> {
  const normalized = normalizeByKind(kind, rawValue);
  if (!normalized) return null;

  const rows = await query<ResolvedCatalogEntry & { normalized_alias: string | null }>(`
    SELECT e.id, e.display_name, e.canonical_name, e.normalized_name, a.normalized_alias
    FROM canonical_catalog_entries e
    LEFT JOIN canonical_catalog_aliases a ON a.entry_id = e.id
    WHERE e.kind = $1
  `, [kind]);

  const exact = rows.find((row) => row.normalized_name === normalized || row.normalized_alias === normalized);
  if (exact) {
    return {
      id: exact.id,
      display_name: exact.display_name,
      canonical_name: exact.canonical_name,
      normalized_name: exact.normalized_name,
    };
  }

  const partial = rows.find((row) =>
    normalized.startsWith(`${row.normalized_name} `) ||
    normalized.startsWith(`${row.normalized_alias ?? ""} `)
  );
  if (!partial) return null;

  return {
    id: partial.id,
    display_name: partial.display_name,
    canonical_name: partial.canonical_name,
    normalized_name: partial.normalized_name,
  };
}

export async function listAdminCatalog(kind: CatalogKind): Promise<AdminCatalogSummary> {
  const observationUnionSql =
    kind === "store"
      ? `
        SELECT NULLIF(TRIM(vendor), '') AS raw_value
        FROM products
        UNION ALL
        SELECT NULLIF(TRIM(vendor_raw), '') AS raw_value
        FROM purchase_documents
      `
      : kind === "brand"
        ? `
        SELECT NULLIF(TRIM(brand), '') AS raw_value
        FROM products
        UNION ALL
        SELECT NULLIF(TRIM(brand_raw), '') AS raw_value
        FROM purchase_document_lines
      `
        : `
        SELECT NULLIF(TRIM(name), '') AS raw_value
        FROM products
        UNION ALL
        SELECT NULLIF(TRIM(name_raw), '') AS raw_value
        FROM purchase_document_lines
      `;
  const normalizeExpr = `BTRIM(LOWER(REGEXP_REPLACE(TRANSLATE(raw_value, 'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'), '[^a-zA-Z0-9]+', ' ', 'g')))`; 
  const [entries, rawRows, aliases] = await Promise.all([
    query<CanonicalCatalogEntry>(`
      WITH raw_items AS (
        ${observationUnionSql}
      ),
      normalized_items AS (
        SELECT raw_value, ${normalizeExpr} AS normalized_value
        FROM raw_items
        WHERE raw_value IS NOT NULL
      )
      SELECT
        e.*,
        COUNT(DISTINCT a.id)::int AS alias_count,
        COUNT(DISTINCT ni.raw_value)::int AS observed_count
      FROM canonical_catalog_entries e
      LEFT JOIN canonical_catalog_aliases a ON a.entry_id = e.id
      LEFT JOIN normalized_items ni ON (
        ni.normalized_value = e.normalized_name
        OR ni.normalized_value = a.normalized_alias
        OR ni.normalized_value LIKE e.normalized_name || ' %'
        OR (a.normalized_alias IS NOT NULL AND ni.normalized_value LIKE a.normalized_alias || ' %')
      )
      WHERE e.kind = $1
      GROUP BY e.id
      ORDER BY
        CASE e.status WHEN 'pending' THEN 0 WHEN 'seeded' THEN 1 WHEN 'verified' THEN 2 ELSE 3 END,
        observed_count DESC,
        e.display_name ASC
    `, [kind]),
    query<{ raw_value: string; observed_count: string }>(`
      WITH raw_items AS (
        ${observationUnionSql}
      )
      SELECT raw_value, COUNT(*)::int AS observed_count
      FROM raw_items
      WHERE raw_value IS NOT NULL
      GROUP BY raw_value
      ORDER BY observed_count DESC, raw_value ASC
      LIMIT 200
    `),
    query<{ entry_id: number; display_name: string; normalized_alias: string; normalized_name: string }>(`
      SELECT e.id AS entry_id, e.display_name, a.normalized_alias, e.normalized_name
      FROM canonical_catalog_entries e
      LEFT JOIN canonical_catalog_aliases a ON a.entry_id = e.id
      WHERE e.kind = $1
    `, [kind]),
  ]);

  const observations = rawRows.map((row) => {
    const rawValue = row.raw_value ?? "";
    const normalizedValue = normalizeByKind(kind, rawValue);
    const match = aliases.find((alias) =>
      alias.normalized_name === normalizedValue ||
      alias.normalized_alias === normalizedValue ||
      normalizedValue.startsWith(`${alias.normalized_name} `) ||
      normalizedValue.startsWith(`${alias.normalized_alias} `)
    );

    return {
      raw_value: rawValue,
      normalized_value: normalizedValue,
      observed_count: Number(row.observed_count),
      matched_entry_id: match?.entry_id ?? null,
      matched_display_name: match?.display_name ?? null,
    };
  });

  return { entries, observations };
}

export async function listCatalogSuggestions(kind: CatalogKind, limit = 12): Promise<CatalogSuggestion[]> {
  const catalog = await listAdminCatalog(kind);
  const existing = catalog.entries.map((entry) => ({
    id: entry.id,
    displayName: entry.display_name,
    canonicalName: entry.canonical_name,
    normalizedName: entry.normalized_name,
  }));

  return catalog.observations
    .filter((observation) => observation.matched_entry_id === null)
    .map((observation) => {
      let best: { id: number; displayName: string; canonicalName: string; normalizedName: string; score: number } | null = null;

      for (const entry of existing) {
        const score = Math.max(
          diceCoefficient(observation.normalized_value, entry.normalizedName),
          diceCoefficient(observation.normalized_value, normalizeByKind(kind, entry.displayName)),
          diceCoefficient(observation.normalized_value, normalizeByKind(kind, entry.canonicalName))
        );
        if (!best || score > best.score) {
          best = { ...entry, score };
        }
      }

      const shouldAlias = best !== null && best.score >= 0.55;
      const candidate = best ?? { id: 0, displayName: "", canonicalName: "", normalizedName: "", score: 0 };
      return {
        raw_value: observation.raw_value,
        normalized_value: observation.normalized_value,
        observed_count: observation.observed_count,
        suggested_action: shouldAlias ? "alias" : "create",
        suggested_entry_id: shouldAlias ? candidate.id : null,
        suggested_display_name: shouldAlias ? candidate.displayName : null,
        suggested_canonical_name: shouldAlias ? candidate.canonicalName : null,
        similarity: shouldAlias ? Number(candidate.score.toFixed(2)) : 0,
        reason: shouldAlias
          ? `Se parece a ${candidate.displayName} (${Math.round(candidate.score * 100)}%)`
          : "Sin coincidencia suficiente en el catálogo",
      } as CatalogSuggestion;
    })
    .sort((a, b) => b.observed_count - a.observed_count || b.similarity - a.similarity || a.raw_value.localeCompare(b.raw_value, "es"))
    .slice(0, limit);
}

export async function createCatalogEntry(input: CatalogEntryInput): Promise<CanonicalCatalogEntry> {
  const normalizedName = normalizeByKind(input.kind, input.canonicalName);
  const rows = await query<CanonicalCatalogEntry>(`
    INSERT INTO canonical_catalog_entries (
      kind, canonical_name, display_name, normalized_name, status, source_label, source_url
    ) VALUES ($1, $2, $3, $4, $5, $6, $7)
    ON CONFLICT (kind, normalized_name) DO UPDATE SET
      display_name = EXCLUDED.display_name,
      status = EXCLUDED.status,
      source_label = EXCLUDED.source_label,
      source_url = EXCLUDED.source_url,
      updated_at = NOW()
    RETURNING *, 0::int AS alias_count, 0::int AS observed_count
  `, [
    input.kind,
    input.canonicalName,
    input.displayName,
    normalizedName,
    input.status,
    input.sourceLabel ?? "",
    input.sourceUrl ?? "",
  ]);
  return rows[0]!;
}

export async function updateCatalogEntry(id: number, input: Partial<Omit<CatalogEntryInput, "kind">>): Promise<CanonicalCatalogEntry | null> {
  const current = await query<{ id: number; kind: CatalogKind; canonical_name: string }>(
    "SELECT id, kind, canonical_name FROM canonical_catalog_entries WHERE id = $1",
    [id]
  );
  const entry = current[0];
  if (!entry) return null;

  const canonicalName = input.canonicalName ?? entry.canonical_name;
  const normalizedName = normalizeByKind(entry.kind, canonicalName);
  const rows = await query<CanonicalCatalogEntry>(`
    UPDATE canonical_catalog_entries
    SET
      canonical_name = $2,
      display_name = COALESCE($3, display_name),
      normalized_name = $4,
      status = COALESCE($5, status),
      source_label = COALESCE($6, source_label),
      source_url = COALESCE($7, source_url),
      updated_at = NOW()
    WHERE id = $1
    RETURNING *, 0::int AS alias_count, 0::int AS observed_count
  `, [
    id,
    canonicalName,
    input.displayName,
    normalizedName,
    input.status,
    input.sourceLabel,
    input.sourceUrl,
  ]);
  return rows[0] ?? null;
}

export async function addCatalogAlias(entryId: number, alias: string): Promise<boolean> {
  const entries = await query<{ id: number; kind: CatalogKind }>(
    "SELECT id, kind FROM canonical_catalog_entries WHERE id = $1",
    [entryId]
  );
  const entry = entries[0];
  if (!entry) return false;
  const normalizedAlias = normalizeByKind(entry.kind, alias);
  await query(
    `INSERT INTO canonical_catalog_aliases (entry_id, alias, normalized_alias, confidence, source)
     VALUES ($1, $2, $3, 1, 'admin')
     ON CONFLICT (entry_id, normalized_alias) DO NOTHING`,
    [entryId, alias, normalizedAlias]
  );
  return true;
}