import { useEffect, useState } from "react";
import { supabase } from "@/lib/supabase";
import {
  TARIFF_CATALOG,
  WORK_TYPE_REFERENCE,
  type TariffCatalogItem,
  type TariffLevel,
  type TariffWorkType,
} from "@/data/tariff";

export type TariffCatalogSource = "supabase" | "fallback";

export type TariffCatalogRow = {
  id: string;
  catalog_item_id: string;
  section: string;
  category: string;
  service: string;
  notes: string;
  work_type_id: string;
  work_type_label: string;
  work_type_short_label: string;
  hour_reference: string;
  level_id: string;
  level_label: string;
  reference: string;
  reference_min: number | null;
  reference_max: number | null;
  level_hint: string;
  sort_order: number;
  is_active: boolean;
  managed_by?: "sheet" | "app" | "system" | string;
  created_at?: string | null;
  updated_at?: string | null;
};

export type TariffCatalogUpsertInput = Omit<TariffCatalogRow, "created_at" | "updated_at">;

type TariffCatalogState = {
  rows: TariffCatalogRow[];
  catalog: TariffCatalogItem[];
  source: TariffCatalogSource;
  loading: boolean;
  error: string | null;
};

const TABLE_NAME = "tablero_cdc_tariff_catalog";
const DEFAULT_WORK_TYPE_LABEL = "Referencia";
const DEFAULT_LEVEL_LABEL = "Precio único";

let cache: TariffCatalogState = {
  rows: flattenTariffCatalog(TARIFF_CATALOG),
  catalog: TARIFF_CATALOG,
  source: "fallback",
  loading: false,
  error: null,
};

let initialized = false;
let listeners: Array<() => void> = [];
let loadRequestSeq = 0;

function notify() {
  listeners.forEach((listener) => listener());
}

function asNumber(value: unknown): number | null {
  if (typeof value === "number" && Number.isFinite(value)) return value;
  if (typeof value === "string") {
    const parsed = Number(value.trim());
    if (Number.isFinite(parsed)) return parsed;
  }
  return null;
}

function slugify(value: string) {
  return String(value || "")
    .trim()
    .toLowerCase()
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 80);
}

function rowId(itemId: string, workTypeId: string, levelId: string) {
  return [itemId, workTypeId, levelId].map(slugify).filter(Boolean).join("__");
}

function normalizeDbRow(row: Record<string, unknown>): TariffCatalogRow {
  const section = String(row.section || "grafico").trim().toLowerCase() || "grafico";
  const category = String(row.category || "").trim();
  const service = String(row.service || "").trim();
  const catalogItemId = String(
    row.catalog_item_id || slugify(`${section}-${category}-${service}`),
  ).trim();
  const workTypeLabel = String(row.work_type_label || DEFAULT_WORK_TYPE_LABEL).trim();
  const levelLabel = String(row.level_label || DEFAULT_LEVEL_LABEL).trim();
  const workTypeId = String(
    row.work_type_id || slugify(workTypeLabel) || WORK_TYPE_REFERENCE,
  ).trim();
  const levelId = String(row.level_id || slugify(levelLabel) || "project").trim();

  return {
    id: String(row.id || rowId(catalogItemId, workTypeId, levelId)).trim(),
    catalog_item_id: catalogItemId,
    section,
    category,
    service,
    notes: String(row.notes || "").trim(),
    work_type_id: workTypeId,
    work_type_label: workTypeLabel,
    work_type_short_label: String(row.work_type_short_label || workTypeLabel).trim(),
    hour_reference: String(row.hour_reference || "").trim(),
    level_id: levelId,
    level_label: levelLabel,
    reference: String(row.reference || "").trim(),
    reference_min: asNumber(row.reference_min),
    reference_max: asNumber(row.reference_max),
    level_hint: String(row.level_hint || "").trim(),
    sort_order: Number(row.sort_order || 0),
    is_active: row.is_active !== false,
    managed_by: String(row.managed_by || "sheet").trim() || "sheet",
    created_at: typeof row.created_at === "string" ? row.created_at : null,
    updated_at: typeof row.updated_at === "string" ? row.updated_at : null,
  };
}

export function buildTariffCatalogFromRows(rows: TariffCatalogRow[]): TariffCatalogItem[] {
  const itemMap = new Map<string, TariffCatalogItem>();
  const workTypeMaps = new Map<string, Map<string, TariffWorkType>>();

  const activeRows = rows
    .filter((row) => row.is_active)
    .sort((a, b) => a.sort_order - b.sort_order || a.category.localeCompare(b.category));

  for (const row of activeRows) {
    const itemId = row.catalog_item_id || slugify(`${row.section}-${row.category}-${row.service}`);
    let item = itemMap.get(itemId);

    if (!item) {
      item = {
        id: itemId,
        section: row.section,
        category: row.category,
        service: row.service,
        notes: row.notes,
        workTypes: [],
      };
      itemMap.set(itemId, item);
      workTypeMaps.set(itemId, new Map());
    }

    const workTypeKey = row.work_type_id || WORK_TYPE_REFERENCE;
    const workTypeMap = workTypeMaps.get(itemId)!;
    let workType = workTypeMap.get(workTypeKey);

    if (!workType) {
      workType = {
        id: workTypeKey,
        label: row.work_type_label || DEFAULT_WORK_TYPE_LABEL,
        shortLabel: row.work_type_short_label || row.work_type_label || DEFAULT_WORK_TYPE_LABEL,
        hourReference: row.hour_reference || undefined,
        levels: [],
      };
      workTypeMap.set(workTypeKey, workType);
      item.workTypes.push(workType);
    }

    const level: TariffLevel = {
      id: row.level_id || "project",
      label: row.level_label || DEFAULT_LEVEL_LABEL,
      reference: row.reference || "",
      min: row.reference_min ?? undefined,
      max: row.reference_max ?? undefined,
      hint: row.level_hint || undefined,
    };

    if (!workType.levels.some((existing) => existing.id === level.id)) {
      workType.levels.push(level);
    }
  }

  return Array.from(itemMap.values());
}

export function flattenTariffCatalog(catalog: TariffCatalogItem[]): TariffCatalogRow[] {
  const rows: TariffCatalogRow[] = [];

  catalog.forEach((item, itemIndex) => {
    item.workTypes.forEach((workType, workTypeIndex) => {
      workType.levels.forEach((level, levelIndex) => {
        rows.push({
          id: rowId(item.id, workType.id, level.id),
          catalog_item_id: item.id,
          section: item.section,
          category: item.category,
          service: item.service,
          notes: item.notes || "",
          work_type_id: workType.id,
          work_type_label: workType.label,
          work_type_short_label: workType.shortLabel,
          hour_reference: workType.hourReference || "",
          level_id: level.id,
          level_label: level.label,
          reference: level.reference,
          reference_min: level.min ?? null,
          reference_max: level.max ?? null,
          level_hint: level.hint || "",
          sort_order: itemIndex * 1000 + workTypeIndex * 100 + levelIndex,
          is_active: true,
          managed_by: "system",
        });
      });
    });
  });

  return rows;
}

export function buildTariffRowId(
  input: Pick<TariffCatalogRow, "catalog_item_id" | "work_type_id" | "level_id">,
) {
  return rowId(input.catalog_item_id, input.work_type_id, input.level_id);
}

function missingTariffTable(error: unknown) {
  const message = error instanceof Error ? error.message : String(error || "");
  return (
    message.includes(TABLE_NAME) ||
    message.includes("Could not find the table") ||
    message.includes("relation") ||
    message.includes("does not exist")
  );
}

export async function loadTariffCatalog(force = false): Promise<TariffCatalogState> {
  if (initialized && !force) return cache;

  const requestId = (loadRequestSeq += 1);
  cache = { ...cache, loading: true, error: null };
  notify();

  try {
    const queryWithManagementSource = await supabase
      .from(TABLE_NAME)
      .select(
        "id, catalog_item_id, section, category, service, notes, work_type_id, work_type_label, work_type_short_label, hour_reference, level_id, level_label, reference, reference_min, reference_max, level_hint, sort_order, is_active, managed_by, created_at, updated_at",
      )
      .order("sort_order", { ascending: true })
      .order("category", { ascending: true });

    let data = queryWithManagementSource.data as Array<Record<string, unknown>> | null;
    let error = queryWithManagementSource.error;

    if (error && String(error.message || "").includes("managed_by")) {
      const legacyQuery = await supabase
        .from(TABLE_NAME)
        .select(
          "id, catalog_item_id, section, category, service, notes, work_type_id, work_type_label, work_type_short_label, hour_reference, level_id, level_label, reference, reference_min, reference_max, level_hint, sort_order, is_active, created_at, updated_at",
        )
        .order("sort_order", { ascending: true })
        .order("category", { ascending: true });

      data = legacyQuery.data as Array<Record<string, unknown>> | null;
      error = legacyQuery.error;
    }

    if (error) throw error;

    const dbRows = (data || []).map((row) => normalizeDbRow(row as Record<string, unknown>));
    const fallbackRows = flattenTariffCatalog(TARIFF_CATALOG);
    const rowMap = new Map<string, TariffCatalogRow>();

    fallbackRows.forEach((row) => rowMap.set(row.id, row));
    dbRows.forEach((row) => rowMap.set(row.id, row));

    const mergedRows = Array.from(rowMap.values()).sort(
      (a, b) => a.sort_order - b.sort_order || a.category.localeCompare(b.category),
    );
    const catalog = buildTariffCatalogFromRows(mergedRows);

    if (requestId === loadRequestSeq) {
      cache = {
        rows: mergedRows,
        catalog,
        source: dbRows.length ? "supabase" : "fallback",
        loading: false,
        error: dbRows.length
          ? null
          : "La tabla del tarifario está vacía. Se está usando el tarifario base de la app como respaldo.",
      };
      initialized = true;
      notify();
    }
  } catch (error) {
    const message = error instanceof Error ? error.message : String(error || "");
    const friendlyMessage = missingTariffTable(error)
      ? "Falta crear la tabla tablero_cdc_tariff_catalog en Supabase. Se está usando el tarifario base de la app como respaldo."
      : message || "No se pudo cargar el tarifario dinámico.";

    if (requestId === loadRequestSeq) {
      cache = {
        rows: flattenTariffCatalog(TARIFF_CATALOG),
        catalog: TARIFF_CATALOG,
        source: "fallback",
        loading: false,
        error: friendlyMessage,
      };
      initialized = true;
      notify();
    }
  }

  return cache;
}

export async function upsertTariffCatalogRow(input: TariffCatalogUpsertInput) {
  const row = normalizeDbRow(input as unknown as Record<string, unknown>);
  const id = row.id || buildTariffRowId(row);

  const { error } = await supabase.from(TABLE_NAME).upsert(
    {
      id,
      catalog_item_id: row.catalog_item_id,
      section: row.section,
      category: row.category,
      service: row.service,
      notes: row.notes,
      work_type_id: row.work_type_id,
      work_type_label: row.work_type_label,
      work_type_short_label: row.work_type_short_label,
      hour_reference: row.hour_reference,
      level_id: row.level_id,
      level_label: row.level_label,
      reference: row.reference,
      reference_min: row.reference_min,
      reference_max: row.reference_max,
      level_hint: row.level_hint,
      sort_order: row.sort_order,
      is_active: row.is_active,
      managed_by: "app",
    },
    { onConflict: "id" },
  );

  if (error) throw error;

  initialized = false;
  await loadTariffCatalog(true);
}


export async function saveTariffCatalogRow(
  input: TariffCatalogUpsertInput,
  original?: TariffCatalogRow | null,
) {
  const row = normalizeDbRow(input as unknown as Record<string, unknown>);
  const id = row.id || buildTariffRowId(row);

  if (original?.catalog_item_id) {
    const metadataChanged =
      original.category !== row.category ||
      original.service !== row.service ||
      original.notes !== row.notes;

    if (metadataChanged) {
      const { error: metadataError } = await supabase
        .from(TABLE_NAME)
        .update({
          category: row.category,
          service: row.service,
          notes: row.notes,
          managed_by: "app",
        })
        .eq("catalog_item_id", original.catalog_item_id);

      if (metadataError) throw metadataError;
    }
  }

  const { error } = await supabase.from(TABLE_NAME).upsert(
    {
      id,
      catalog_item_id: row.catalog_item_id,
      section: row.section,
      category: row.category,
      service: row.service,
      notes: row.notes,
      work_type_id: row.work_type_id,
      work_type_label: row.work_type_label,
      work_type_short_label: row.work_type_short_label,
      hour_reference: row.hour_reference,
      level_id: row.level_id,
      level_label: row.level_label,
      reference: row.reference,
      reference_min: row.reference_min,
      reference_max: row.reference_max,
      level_hint: row.level_hint,
      sort_order: row.sort_order,
      is_active: row.is_active,
      managed_by: "app",
    },
    { onConflict: "id" },
  );

  if (error) throw error;

  initialized = false;
  return loadTariffCatalog(true);
}

export async function setTariffCatalogRowActive(row: TariffCatalogRow, isActive: boolean) {
  const { error } = await supabase
    .from(TABLE_NAME)
    .update({ is_active: isActive, managed_by: "app" })
    .eq("id", row.id);

  if (error) throw error;

  initialized = false;
  await loadTariffCatalog(true);
}

export function useTariffCatalog() {
  const [, force] = useState(0);

  useEffect(() => {
    const listener = () => force((value) => value + 1);
    listeners.push(listener);

    if (!initialized && !cache.loading) {
      void loadTariffCatalog();
    }

    return () => {
      listeners = listeners.filter((current) => current !== listener);
    };
  }, []);

  return {
    ...cache,
    refresh: loadTariffCatalog,
  };
}
