"use client";

import { useCallback, useEffect, useMemo, useState } from "react";
import { createClient, getBrowserUserId } from "@/lib/supabase/client";
import { useWorkspaceStore } from "@/stores/workspace-store";
import type {
  BusinessEntity,
  BusinessEntityType,
  InsightDataSource,
  InsightGoal,
  InsightMetricAggregation,
  InsightMetricDefinition,
  InsightMetricDirection,
  InsightMetricEntry,
  InsightMetricValueType,
  InsightRecommendation,
} from "@/types";

export interface BusinessEntityInput {
  color?: string;
  description?: string;
  entity_type?: BusinessEntityType;
  name: string;
}

export interface InsightMetricDefinitionInput {
  aggregation?: InsightMetricAggregation;
  business_entity_id?: string | null;
  category?: string;
  color?: string;
  direction?: InsightMetricDirection;
  name: string;
  unit?: string;
  value_type?: InsightMetricValueType;
}

export interface InsightMetricEntryInput {
  business_entity_id?: string | null;
  metric_definition_id: string;
  note?: string;
  period_end: string;
  period_start: string;
  value: number;
}

export interface InsightGoalInput {
  business_entity_id?: string | null;
  current_value?: number;
  metric_definition_id?: string | null;
  name: string;
  period_end: string;
  period_start: string;
  target_value: number;
}

export interface InsightKpiSummary {
  currentValue: number;
  entries: InsightMetricEntry[];
  metric: InsightMetricDefinition;
  previousValue: number | null;
}

interface OperationalLeadSignal {
  company: string | null;
  created_at: string;
  score: "cold" | "warm" | "hot";
  status: "neu" | "kontaktiert" | "angebot" | "gewonnen" | "verloren";
  value: number | null;
}

interface OperationalInvoiceSignal {
  counterparty_name: string;
  created_at: string;
  direction: "outgoing" | "incoming";
  document_type: "invoice" | "quote";
  due_date: string;
  status: "draft" | "ready" | "sent" | "received" | "paid" | "overdue" | "cancelled";
  total_cents: number;
}

export interface OperationalInsightOverview {
  activeCompanies: number;
  hotLeads: number;
  incomingOpenCents: number;
  leads: number;
  offerVolumeCents: number;
  offers: number;
  openInvoiceCents: number;
  paidRevenueCents: number;
  pipelineValue: number;
  wonLeads: number;
}

function toInsightsErrorMessage(error: unknown) {
  if (error instanceof Error && error.message) return error.message;

  if (error && typeof error === "object") {
    const record = error as Record<string, unknown>;
    const message = typeof record.message === "string" ? record.message : "";
    const details = typeof record.details === "string" ? record.details : "";
    const hint = typeof record.hint === "string" ? record.hint : "";
    const code = typeof record.code === "string" ? record.code : "";
    const combined = [message, details, hint].filter(Boolean).join(" ");

    if (
      code === "42P01" ||
      code === "PGRST205" ||
      /relation .* does not exist/i.test(combined) ||
      /could not find the table .* in the schema cache/i.test(combined)
    ) {
      return "Insights-Datenbanktabellen fehlen noch. Bitte die Migration 20260808000001_insights_core.sql ausfuehren.";
    }

    if (message) return message;
    if (details) return details;
    if (hint) return hint;

    try {
      return JSON.stringify(error);
    } catch {
      return "Insights konnten nicht geladen werden.";
    }
  }

  if (typeof error === "string" && error.trim()) return error;
  return "Insights konnten nicht geladen werden.";
}

function slugify(value: string) {
  return value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9äöüß_-]+/gi, "-")
    .replace(/-+/g, "-")
    .replace(/^-|-$/g, "");
}

function aggregateEntries(entries: InsightMetricEntry[], aggregation: InsightMetricAggregation) {
  if (entries.length === 0) return 0;
  const values = entries.map((entry) => Number(entry.value) || 0);

  if (aggregation === "avg") {
    return values.reduce((sum, value) => sum + value, 0) / values.length;
  }

  if (aggregation === "last") {
    return [...entries].sort((left, right) => right.period_end.localeCompare(left.period_end))[0]?.value || 0;
  }

  if (aggregation === "min") return Math.min(...values);
  if (aggregation === "max") return Math.max(...values);
  return values.reduce((sum, value) => sum + value, 0);
}

function isWithinRange(entry: InsightMetricEntry, start: string, end: string) {
  return entry.period_end >= start && entry.period_start <= end;
}

function isDateInRange(value: string, start: string, end: string) {
  if (!value) return false;
  const day = value.split("T")[0];
  return day >= start && day <= end;
}

function shiftRangeBack(start: string, end: string) {
  const startDate = new Date(`${start}T00:00:00`);
  const endDate = new Date(`${end}T00:00:00`);
  const days = Math.max(1, Math.round((endDate.getTime() - startDate.getTime()) / 86_400_000) + 1);
  startDate.setDate(startDate.getDate() - days);
  endDate.setDate(endDate.getDate() - days);

  return {
    end: endDate.toISOString().split("T")[0],
    start: startDate.toISOString().split("T")[0],
  };
}

export function useInsights() {
  const [entities, setEntities] = useState<BusinessEntity[]>([]);
  const [sources, setSources] = useState<InsightDataSource[]>([]);
  const [metrics, setMetrics] = useState<InsightMetricDefinition[]>([]);
  const [entries, setEntries] = useState<InsightMetricEntry[]>([]);
  const [goals, setGoals] = useState<InsightGoal[]>([]);
  const [recommendations, setRecommendations] = useState<InsightRecommendation[]>([]);
  const [operationalLeads, setOperationalLeads] = useState<OperationalLeadSignal[]>([]);
  const [operationalInvoices, setOperationalInvoices] = useState<OperationalInvoiceSignal[]>([]);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState<string | null>(null);
  const [userId, setUserId] = useState<string | null>(null);
  const activeTeamId = useWorkspaceStore((state) => state.activeTeamId);
  const initialized = useWorkspaceStore((state) => state.initialized);
  const supabase = createClient();

  useEffect(() => {
    void getBrowserUserId().then(setUserId).catch(() => setUserId(null));
  }, []);

  const requireCurrentUserId = useCallback(async () => {
    const currentUserId = userId || (await getBrowserUserId());
    if (currentUserId && currentUserId !== userId) setUserId(currentUserId);
    if (!currentUserId) throw new Error("Not authenticated");
    return currentUserId;
  }, [userId]);

  const fetchInsights = useCallback(async () => {
    if (!initialized) return;

    setLoading(true);
    setError(null);

    try {
      const currentUserId = await requireCurrentUserId();
      const scope = activeTeamId ? { column: "team_id", value: activeTeamId } : null;
      const personal = !activeTeamId;

      const leadsQuery = supabase
        .from("leads")
        .select("company, created_at, score, status, value")
        .order("created_at", { ascending: false })
        .limit(500);
      const invoicesQuery = supabase
        .from("invoices")
        .select("counterparty_name, created_at, direction, document_type, due_date, status, total_cents")
        .order("created_at", { ascending: false })
        .limit(500);

      if (scope) {
        leadsQuery.eq(scope.column, scope.value);
        invoicesQuery.eq(scope.column, scope.value);
      } else if (personal) {
        leadsQuery.eq("user_id", currentUserId).is("team_id", null);
        invoicesQuery.eq("user_id", currentUserId).is("team_id", null);
      }

      const [leadsRes, operationalInvoicesRes] = await Promise.all([leadsQuery, invoicesQuery]);
      setOperationalLeads(leadsRes.error ? [] : ((leadsRes.data || []) as OperationalLeadSignal[]));
      setOperationalInvoices(
        operationalInvoicesRes.error ? [] : ((operationalInvoicesRes.data || []) as OperationalInvoiceSignal[]),
      );

      const entityQuery = supabase
        .from("business_entities")
        .select("*")
        .eq("is_archived", false)
        .order("created_at", { ascending: true });
      const metricQuery = supabase
        .from("insight_metric_definitions")
        .select("*")
        .eq("is_archived", false)
        .order("sort_order")
        .order("created_at", { ascending: true });
      const entryQuery = supabase
        .from("insight_metric_entries")
        .select("*")
        .order("period_end", { ascending: false })
        .limit(500);
      const goalQuery = supabase
        .from("insight_goals")
        .select("*")
        .order("period_end", { ascending: true });
      const sourceQuery = supabase
        .from("insight_data_sources")
        .select("*")
        .order("created_at", { ascending: false });
      const recommendationQuery = supabase
        .from("insight_recommendations")
        .select("*")
        .eq("status", "open")
        .order("created_at", { ascending: false })
        .limit(20);

      if (scope) {
        entityQuery.eq(scope.column, scope.value);
        metricQuery.eq(scope.column, scope.value);
        entryQuery.eq(scope.column, scope.value);
        goalQuery.eq(scope.column, scope.value);
        sourceQuery.eq(scope.column, scope.value);
        recommendationQuery.eq(scope.column, scope.value);
      } else if (personal) {
        entityQuery.is("team_id", null);
        metricQuery.is("team_id", null);
        entryQuery.is("team_id", null);
        goalQuery.is("team_id", null);
        sourceQuery.is("team_id", null);
        recommendationQuery.is("team_id", null);
      }

      const [entitiesRes, metricsRes, entriesRes, goalsRes, sourcesRes, recommendationsRes] =
        await Promise.all([entityQuery, metricQuery, entryQuery, goalQuery, sourceQuery, recommendationQuery]);

      if (entitiesRes.error) throw new Error(toInsightsErrorMessage(entitiesRes.error));
      if (metricsRes.error) throw new Error(toInsightsErrorMessage(metricsRes.error));
      if (entriesRes.error) throw new Error(toInsightsErrorMessage(entriesRes.error));
      if (goalsRes.error) throw new Error(toInsightsErrorMessage(goalsRes.error));
      if (sourcesRes.error) throw new Error(toInsightsErrorMessage(sourcesRes.error));
      if (recommendationsRes.error) throw new Error(toInsightsErrorMessage(recommendationsRes.error));

      setEntities((entitiesRes.data || []) as BusinessEntity[]);
      setMetrics((metricsRes.data || []) as InsightMetricDefinition[]);
      setEntries((entriesRes.data || []) as InsightMetricEntry[]);
      setGoals((goalsRes.data || []) as InsightGoal[]);
      setSources((sourcesRes.data || []) as InsightDataSource[]);
      setRecommendations((recommendationsRes.data || []) as InsightRecommendation[]);
    } catch (fetchError) {
      setError(toInsightsErrorMessage(fetchError));
    } finally {
      setLoading(false);
    }
  }, [activeTeamId, initialized, requireCurrentUserId, supabase]);

  useEffect(() => {
    if (!initialized) return;
    const timer = window.setTimeout(() => {
      void fetchInsights();
    }, 0);

    return () => window.clearTimeout(timer);
  }, [fetchInsights, initialized]);

  const getKpiSummaries = useCallback(
    (input: { entityId: string | "all"; end: string; start: string }) => {
      const previousRange = shiftRangeBack(input.start, input.end);
      return metrics.map<InsightKpiSummary>((metric) => {
        const scopedEntries = entries.filter((entry) => {
          if (entry.metric_definition_id !== metric.id) return false;
          if (input.entityId !== "all" && entry.business_entity_id !== input.entityId) return false;
          return isWithinRange(entry, input.start, input.end);
        });
        const previousEntries = entries.filter((entry) => {
          if (entry.metric_definition_id !== metric.id) return false;
          if (input.entityId !== "all" && entry.business_entity_id !== input.entityId) return false;
          return isWithinRange(entry, previousRange.start, previousRange.end);
        });

        return {
          currentValue: aggregateEntries(scopedEntries, metric.aggregation),
          entries: scopedEntries,
          metric,
          previousValue: previousEntries.length > 0 ? aggregateEntries(previousEntries, metric.aggregation) : null,
        };
      });
    },
    [entries, metrics],
  );

  const getOperationalOverview = useCallback(
    (input: { end: string; start: string }) => {
      const scopedLeads = operationalLeads.filter((lead) => isDateInRange(lead.created_at, input.start, input.end));
      const scopedInvoices = operationalInvoices.filter((invoice) =>
        isDateInRange(invoice.created_at, input.start, input.end),
      );
      const companies = new Set<string>();

      scopedLeads.forEach((lead) => {
        if (lead.company?.trim()) companies.add(lead.company.trim().toLowerCase());
      });
      scopedInvoices.forEach((invoice) => {
        if (invoice.counterparty_name?.trim()) companies.add(invoice.counterparty_name.trim().toLowerCase());
      });

      const offers = scopedInvoices.filter((invoice) => invoice.document_type === "quote" && invoice.direction === "outgoing");
      const outgoingInvoices = scopedInvoices.filter(
        (invoice) => invoice.document_type === "invoice" && invoice.direction === "outgoing",
      );
      const incomingInvoices = scopedInvoices.filter(
        (invoice) => invoice.document_type === "invoice" && invoice.direction === "incoming",
      );
      const isOpen = (invoice: OperationalInvoiceSignal) =>
        invoice.status !== "paid" && invoice.status !== "cancelled";

      return {
        activeCompanies: companies.size,
        hotLeads: scopedLeads.filter((lead) => lead.score === "hot").length,
        incomingOpenCents: incomingInvoices.filter(isOpen).reduce((sum, invoice) => sum + Number(invoice.total_cents || 0), 0),
        leads: scopedLeads.length,
        offerVolumeCents: offers.reduce((sum, invoice) => sum + Number(invoice.total_cents || 0), 0),
        offers: offers.length,
        openInvoiceCents: outgoingInvoices.filter(isOpen).reduce((sum, invoice) => sum + Number(invoice.total_cents || 0), 0),
        paidRevenueCents: outgoingInvoices
          .filter((invoice) => invoice.status === "paid")
          .reduce((sum, invoice) => sum + Number(invoice.total_cents || 0), 0),
        pipelineValue: scopedLeads.reduce((sum, lead) => sum + Number(lead.value || 0), 0),
        wonLeads: scopedLeads.filter((lead) => lead.status === "gewonnen").length,
      } satisfies OperationalInsightOverview;
    },
    [operationalInvoices, operationalLeads],
  );

  const portfolioStats = useMemo(
    () => ({
      activeEntities: entities.filter((entity) => entity.entity_type !== "portfolio").length,
      entries: entries.length,
      goals: goals.filter((goal) => goal.status === "active").length,
      openRecommendations: recommendations.length,
      sources: sources.length,
    }),
    [entities, entries.length, goals, recommendations.length, sources.length],
  );

  async function createEntity(input: BusinessEntityInput) {
    const currentUserId = await requireCurrentUserId();
    const { data, error: createError } = await supabase
      .from("business_entities")
      .insert({
        color: input.color || "#38bdf8",
        description: input.description?.trim() || "",
        entity_type: input.entity_type || "company",
        name: input.name.trim(),
        team_id: activeTeamId,
        user_id: currentUserId,
      })
      .select("*")
      .single();

    if (createError) throw new Error(toInsightsErrorMessage(createError));
    await fetchInsights();
    return data as BusinessEntity;
  }

  async function createMetricDefinition(input: InsightMetricDefinitionInput) {
    const currentUserId = await requireCurrentUserId();
    const { data, error: createError } = await supabase
      .from("insight_metric_definitions")
      .insert({
        aggregation: input.aggregation || "sum",
        business_entity_id: input.business_entity_id || null,
        category: input.category?.trim() || "business",
        color: input.color || "#38bdf8",
        direction: input.direction || "up",
        name: input.name.trim(),
        slug: slugify(input.name),
        team_id: activeTeamId,
        unit: input.unit?.trim() || "",
        user_id: currentUserId,
        value_type: input.value_type || "number",
      })
      .select("*")
      .single();

    if (createError) throw new Error(toInsightsErrorMessage(createError));
    await fetchInsights();
    return data as InsightMetricDefinition;
  }

  async function createMetricEntry(input: InsightMetricEntryInput) {
    const currentUserId = await requireCurrentUserId();
    const { data, error: createError } = await supabase
      .from("insight_metric_entries")
      .insert({
        business_entity_id: input.business_entity_id || null,
        metric_definition_id: input.metric_definition_id,
        note: input.note?.trim() || "",
        period_end: input.period_end,
        period_start: input.period_start,
        team_id: activeTeamId,
        user_id: currentUserId,
        value: input.value,
      })
      .select("*")
      .single();

    if (createError) throw new Error(toInsightsErrorMessage(createError));
    await fetchInsights();
    return data as InsightMetricEntry;
  }

  async function createGoal(input: InsightGoalInput) {
    const currentUserId = await requireCurrentUserId();
    const { data, error: createError } = await supabase
      .from("insight_goals")
      .insert({
        business_entity_id: input.business_entity_id || null,
        current_value: input.current_value || 0,
        metric_definition_id: input.metric_definition_id || null,
        name: input.name.trim(),
        period_end: input.period_end,
        period_start: input.period_start,
        target_value: input.target_value,
        team_id: activeTeamId,
        user_id: currentUserId,
      })
      .select("*")
      .single();

    if (createError) throw new Error(toInsightsErrorMessage(createError));
    await fetchInsights();
    return data as InsightGoal;
  }

  return {
    createEntity,
    createGoal,
    createMetricDefinition,
    createMetricEntry,
    entities,
    entries,
    error,
    getOperationalOverview,
    getKpiSummaries,
    goals,
    loading,
    metrics,
    portfolioStats,
    recommendations,
    refetch: fetchInsights,
    sources,
  };
}