import type { SupabaseClient } from "@supabase/supabase-js";

/* DRE analítico: as entradas e saídas do fluxo de caixa de um intervalo, com
   forma de pagamento, conta e categoria. Saiu de
   app/api/financial/dre-analytic/route.ts para que a tela e o assistente
   somem do mesmo jeito. Quem chama confere a permissão. */

export function normalizeType(value: unknown): "IN" | "OUT" | null {
  if (typeof value !== "string") return null;
  const normalized = value.trim().toLowerCase();
  if (normalized === "in" || normalized === "entrada") return "IN";
  if (normalized === "out" || normalized === "saida" || normalized === "saída") return "OUT";
  return null;
}

const MANUAL_PREFIX = "[MANUAL] ";

export function isManualTagged(description: unknown) {
  return typeof description === "string" && description.startsWith(MANUAL_PREFIX);
}

export function normalizeOrigin(origin: unknown, description: unknown) {
  if (origin === "MANUAL") return "MANUAL";
  if (origin === "AJUSTE" && isManualTagged(description)) return "MANUAL";
  return typeof origin === "string" ? origin : "UNKNOWN";
}

export function cleanManualDescription(description: unknown) {
  if (typeof description !== "string") return "Sem descrição";
  const cleaned = isManualTagged(description) ? description.slice(MANUAL_PREFIX.length).trim() : description;
  return cleaned || "Sem descrição";
}

export type EnrichedRow = {
  date: string | null;
  type: string | null;
  origin: string | null;
  amount: number | null;
  description: string | null;
  bank_account_id: string | null;
  bank_account_name: string | null;
  bank_name: string | null;
  logo_key: string | null;
  payment_method: string | null;
  order_id: string | null;
  payable_id: string | null;
  category_id: string | null;
  category_name: string | null;
};

export type DreAnalyticEntry = {
  date: string;
  type: "IN" | "OUT" | "UNKNOWN";
  origin: string;
  amount: number;
  description: string;
  bank_account_id: string | null;
  bank_account_name: string | null;
  bank_name: string | null;
  logo_key: string | null;
  payment_method: string | null;
  order_id: string | null;
  payable_id: string | null;
  receivable_id: string | null;
  category_id: string | null;
  category_name: string | null;
};

export function isRevenueEntry(entry: Pick<DreAnalyticEntry, "origin" | "type">) {
  return (entry.origin === "PEDIDO" || entry.origin === "RECEBER" || entry.origin === "AJUSTE" || entry.origin === "MANUAL") && entry.type === "IN";
}

export function isExpenseEntry(entry: Pick<DreAnalyticEntry, "origin" | "type">) {
  return (entry.origin === "PAGAR" || entry.origin === "AJUSTE" || entry.origin === "MANUAL") && entry.type === "OUT";
}

export type ResultadoDoDre =
  | {
      ok: true;
      corpo: {
        ok: true;
        kpis: { revenue: number; expenses: number; net: number; margin: number };
        breakdowns: {
          revenueByMethod: Array<{ method: string; total: number; pct: number }>;
          revenueByBank: Array<{ bank_account_id: string | null; bank_name: string; total: number; pct: number; logo_key: string | null }>;
          expenseByCategory: Array<{ category_id: string | null; category_name: string; total: number; pct: number }>;
          topIn: DreAnalyticEntry[];
          topOut: DreAnalyticEntry[];
        };
        entries: DreAnalyticEntry[];
      };
    }
  | { ok: false; status: number; corpo: Record<string, unknown> };

/**
 * `intervalo.start` entra, `intervalo.end` não entra (é o dia seguinte ao
 * último). Para um mês: do dia 1 ao dia 1 do mês seguinte.
 */
export async function montarDreAnalitico(params: {
  supabase: SupabaseClient;
  leitura: SupabaseClient;
  companyId: string;
  tenantIds: string[];
  intervalo: { start: string; end: string };
  bankAccountId: string;
  origin: string;
  type: string;
}): Promise<ResultadoDoDre> {
  const { supabase, leitura, companyId, tenantIds, intervalo, bankAccountId, origin, type } = params;
  const buildQuery = (source: string) => {
    let query = supabase
      .from(source)
      .select(
        "date,type,origin,amount,description,bank_account_id,bank_account_name,bank_name,logo_key,payment_method,order_id,payable_id,category_id,category_name"
      )
      .eq("company_id", companyId)
      .gte("date", intervalo.start)
      .lt("date", intervalo.end)
      .order("date", { ascending: true });

    if (bankAccountId && bankAccountId !== "all") {
      query = query.eq("bank_account_id", bankAccountId);
    }

    if (origin && origin !== "ALL") {
      if (origin === "MANUAL" || origin === "AJUSTE") {
        query = query.eq("origin", "AJUSTE");
      } else {
        query = query.eq("origin", origin);
      }
    }

    if (type && type !== "ALL") {
      query = query.eq("type", type);
    }

    return query;
  };

  let data: unknown = null;
  let error: { message?: string; details?: string; hint?: string; code?: string } | null = null;

  const primary = await buildQuery("v_cash_flow_enriched");
  data = primary.data ?? null;
  error = primary.error ?? null;

  const shouldFallback =
    !!error &&
    (error.code === "PGRST106" ||
      error.code === "PGRST204" ||
      error.code === "PGRST205" ||
      (typeof error.message === "string" && error.message.includes("schema cache")));

  if (shouldFallback) {
    const fallback = await buildQuery("cash_flow_enriched");
    data = fallback.data ?? null;
    error = fallback.error ?? null;
  }

  let entries: DreAnalyticEntry[] = [];
  if (error) {
    let cf = supabase
      .from("cash_flow")
      .select("date,type,origin,amount,description,bank_account_id,reference_id")
      .eq("company_id", companyId)
      .gte("date", intervalo.start)
      .lt("date", intervalo.end)
      .order("date", { ascending: true });
    if (bankAccountId && bankAccountId !== "all") {
      cf = cf.eq("bank_account_id", bankAccountId);
    }
    if (origin && origin !== "ALL") {
      cf = cf.eq("origin", origin);
    }
    if (type && type !== "ALL") {
      cf = cf.eq("type", type);
    }
    const { data: cfRows, error: cfError } = await cf;
    if (cfError) {
      console.error("Erro ao carregar cash_flow para fallback:", cfError);
      return {
        ok: false as const,
        status: 500,
        corpo: {
          error: "Falha ao carregar DRE analítico.",
          message: cfError.message,
          details: cfError.details,
          hint: cfError.hint,
          code: cfError.code,
        },
      };
    }
    const base = (cfRows ?? []).map((row) => {
      const t = normalizeType((row as { type: string | null }).type);
      return {
        date: typeof (row as { date: string | null }).date === "string" ? (row as { date: string }).date : "",
        type: t ?? "UNKNOWN",
        origin: normalizeOrigin(
          (row as { origin: string | null }).origin,
          (row as { description: string | null }).description
        ),
        amount: Number((row as { amount: number | null }).amount ?? 0) || 0,
        description: cleanManualDescription((row as { description: string | null }).description),
        bank_account_id: (row as { bank_account_id: string | null }).bank_account_id ?? null,
        bank_account_name: null,
        bank_name: null,
        logo_key: null,
        payment_method: null,
        order_id: null,
        payable_id: null,
        receivable_id: null,
        category_id: null,
        category_name: null,
        reference_id: (row as { reference_id?: string | null }).reference_id ?? null,
      } as DreAnalyticEntry & { reference_id: string | null };
    });
    const bankIds = Array.from(
      new Set(base.map((e) => e.bank_account_id).filter((v): v is string => typeof v === "string" && v.length > 0))
    );
    const pedidoIds = Array.from(
      new Set(
        base
          .filter((e) => e.origin === "PEDIDO")
          .map((e) => e.reference_id)
          .filter((v): v is string => typeof v === "string" && v.length > 0)
      )
    );
    const pagarIds = Array.from(
      new Set(
        base
          .filter((e) => e.origin === "PAGAR")
          .map((e) => e.reference_id)
          .filter((v): v is string => typeof v === "string" && v.length > 0)
      )
    );
    const receberIds = Array.from(
      new Set(
        base
          .filter((e) => e.origin === "RECEBER")
          .map((e) => e.reference_id)
          .filter((v): v is string => typeof v === "string" && v.length > 0)
      )
    );
    const baMap = new Map<string, { name: string | null; bank_name: string | null; logo_key: string | null }>();
    if (bankIds.length > 0) {
      const { data: baData } = await leitura
        .from("bank_accounts")
        .select("id,name,bank_name,logo_key")
        .eq("company_id", companyId)
        .in("id", bankIds);
      (baData ?? []).forEach((row) => {
        const r = row as { id: string; name: string | null; bank_name: string | null; logo_key: string | null };
        baMap.set(r.id, { name: r.name, bank_name: r.bank_name, logo_key: r.logo_key });
      });
    }
    const payMap = new Map<string, { payment_method: string | null; order_id: string | null }>();
    if (pedidoIds.length > 0) {
      const { data: payData } = await leitura
        .from("payments")
        .select("id,payment_method,order_id")
        .in("id", pedidoIds);
      (payData ?? []).forEach((row) => {
        const r = row as { id: string; payment_method: string | null; order_id: string | null };
        payMap.set(r.id, { payment_method: r.payment_method ?? null, order_id: r.order_id ?? null });
      });
    }
    const apMap = new Map<string, { category_id: string | null }>();
    if (pagarIds.length > 0) {
      const { data: apData } = await supabase.from("accounts_payable").select("id,category_id").in("id", pagarIds);
      (apData ?? []).forEach((row) => {
        const r = row as { id: string; category_id: string | null };
        apMap.set(r.id, { category_id: r.category_id ?? null });
      });
    }
    const rrMap = new Map<string, { payment_method: string | null; receivable_id: string | null; category_id: string | null }>();
    if (receberIds.length > 0) {
      const { data: rrData } = await leitura
        .from("receivable_receipts")
        .select("id,payment_method,receivable_id,accounts_receivable(category_id)")
        .in("id", receberIds);
      (rrData ?? []).forEach((row) => {
        const r = row as {
          id: string;
          payment_method: string | null;
          receivable_id: string | null;
          accounts_receivable?: { category_id: string | null }[] | { category_id: string | null } | null;
        };
        const ar = Array.isArray(r.accounts_receivable) ? r.accounts_receivable[0] : r.accounts_receivable;
        rrMap.set(r.id, {
          payment_method: r.payment_method ?? null,
          receivable_id: r.receivable_id ?? null,
          category_id: ar?.category_id ?? null,
        });
      });
    }
    const categoryIds = Array.from(
      new Set(
        [...Array.from(apMap.values()).map((v) => v.category_id), ...Array.from(rrMap.values()).map((v) => v.category_id)].filter(
          (v): v is string => !!v
        )
      )
    );
    const catMap = new Map<string, { name: string | null }>();
    if (categoryIds.length > 0) {
      const { data: catData } = await supabase.from("financial_categories").select("id,name").in("id", categoryIds);
      (catData ?? []).forEach((row) => {
        const r = row as { id: string; name: string | null };
        catMap.set(r.id, { name: r.name ?? null });
      });
    }
    entries = base.map((e) => {
      const ba = e.bank_account_id ? baMap.get(e.bank_account_id) : undefined;
      const isPedido = e.origin === "PEDIDO";
      const isPagar = e.origin === "PAGAR";
      const isReceber = e.origin === "RECEBER";
      const pay = isPedido && e.reference_id ? payMap.get(e.reference_id) : undefined;
      const ap = isPagar && e.reference_id ? apMap.get(e.reference_id) : undefined;
      const rr = isReceber && e.reference_id ? rrMap.get(e.reference_id) : undefined;
      const cat = (ap?.category_id || rr?.category_id) ? catMap.get((ap?.category_id || rr?.category_id) as string) : undefined;
      const bankLabel =
        (ba?.name || "").trim() || (ba?.bank_name || "").trim() || (e.bank_account_id ? "Conta bancária" : null);
      return {
        date: e.date,
        type: e.type,
        origin: e.origin,
        amount: e.amount,
        description: e.description,
        bank_account_id: e.bank_account_id,
        bank_account_name: bankLabel,
        bank_name: ba?.bank_name ?? null,
        logo_key: ba?.logo_key ?? null,
        payment_method: pay?.payment_method ?? rr?.payment_method ?? null,
        order_id: pay?.order_id ?? null,
        payable_id: isPagar ? e.reference_id : null,
        receivable_id: rr?.receivable_id ?? null,
        category_id: ap?.category_id ?? rr?.category_id ?? null,
        category_name: cat?.name ?? (ap?.category_id || rr?.category_id ? "Sem categoria" : null),
      } as DreAnalyticEntry;
    });
  } else {
    const rows = Array.isArray(data) ? data : [];
    entries = rows.map((row: unknown) => {
      const r = row as EnrichedRow;
      const normalizedType = normalizeType(r.type);
      return {
        date: typeof r.date === "string" ? r.date : "",
        type: normalizedType ?? "UNKNOWN",
        origin: normalizeOrigin(r.origin, r.description),
        amount: Number(r.amount ?? 0) || 0,
        description: cleanManualDescription(r.description),
        bank_account_id: r.bank_account_id ?? null,
        bank_account_name: r.bank_account_name ?? null,
        bank_name: r.bank_name ?? null,
        logo_key: r.logo_key ?? null,
        payment_method: r.payment_method ?? null,
        order_id: r.order_id ?? null,
        payable_id: r.payable_id ?? null,
        receivable_id: null,
        category_id: r.category_id ?? null,
        category_name: r.category_name ?? null,
      } satisfies DreAnalyticEntry;
    });
  }

  const orderIdsFromEntries = Array.from(
    new Set(
      entries
        .filter((entry) => entry.origin === "PEDIDO")
        .map((entry) => entry.order_id)
        .filter((value): value is string => typeof value === "string" && value.length > 0)
    )
  );
  if (orderIdsFromEntries.length > 0) {
    const { data: canceledOrders } = await leitura
      .from("orders")
      .select("id")
      .in("id", orderIdsFromEntries)
      .in("user_id", tenantIds)
      .eq("status", "canceled");
    const canceledOrderSet = new Set((canceledOrders ?? []).map((row) => (row as { id: string }).id));
    entries = entries.filter((entry) => {
      if (entry.origin !== "PEDIDO") return true;
      if (!entry.order_id) return true;
      return !canceledOrderSet.has(entry.order_id);
    });
  }

  if (origin === "MANUAL") {
    entries = entries.filter((entry) => entry.origin === "MANUAL");
  }
  if (origin === "AJUSTE") {
    entries = entries.filter((entry) => entry.origin === "AJUSTE");
  }

  const revenueEntries = entries.filter(isRevenueEntry);
  const expenseEntries = entries.filter(isExpenseEntry);
  const revenue = revenueEntries.reduce((acc, entry) => acc + entry.amount, 0);
  const expenses = expenseEntries.reduce((acc, entry) => acc + entry.amount, 0);
  const net = revenue - expenses;
  const margin = revenue > 0 ? (net / revenue) * 100 : 0;

  const revenueByMethodMap = new Map<string, { method: string; total: number }>();
  const revenueByBankMap = new Map<
    string,
    { bank_account_id: string | null; bank_name: string; total: number; logo_key: string | null }
  >();
  const expenseByCategoryMap = new Map<string, { category_id: string | null; category_name: string; total: number }>();

  entries.forEach((entry) => {
    if (isRevenueEntry(entry)) {
      const key =
        entry.payment_method?.trim() ||
        (entry.origin === "AJUSTE" || entry.origin === "MANUAL" ? "manual" : "sem_forma");
      const current = revenueByMethodMap.get(key);
      if (current) {
        current.total += entry.amount;
      } else {
        revenueByMethodMap.set(key, { method: key, total: entry.amount });
      }
    }

    if (isRevenueEntry(entry)) {
      const key = entry.bank_account_id || "unknown";
      const label =
        entry.bank_account_name?.trim() ||
        entry.bank_name?.trim() ||
        "Conta bancária";
      const current = revenueByBankMap.get(key);
      if (current) {
        current.total += entry.amount;
      } else {
        revenueByBankMap.set(key, {
          bank_account_id: entry.bank_account_id ?? null,
          bank_name: label,
          total: entry.amount,
          logo_key: entry.logo_key ?? null,
        });
      }
    }

    if (isExpenseEntry(entry)) {
      const key = entry.category_id || "uncategorized";
      const label = entry.category_name?.trim() || "Sem categoria";
      const current = expenseByCategoryMap.get(key);
      if (current) {
        current.total += entry.amount;
      } else {
        expenseByCategoryMap.set(key, {
          category_id: entry.category_id ?? null,
          category_name: label,
          total: entry.amount,
        });
      }
    }
  });

  const revenueByMethod = Array.from(revenueByMethodMap.values())
    .map((item) => ({
      method: item.method,
      total: item.total,
      pct: revenue > 0 ? (item.total / revenue) * 100 : 0,
    }))
    .sort((a, b) => b.total - a.total);

  const revenueByBank = Array.from(revenueByBankMap.values())
    .map((item) => ({
      bank_account_id: item.bank_account_id,
      bank_name: item.bank_name,
      total: item.total,
      pct: revenue > 0 ? (item.total / revenue) * 100 : 0,
      logo_key: item.logo_key,
    }))
    .sort((a, b) => b.total - a.total);

  const expenseByCategory = Array.from(expenseByCategoryMap.values())
    .map((item) => ({
      category_id: item.category_id,
      category_name: item.category_name,
      total: item.total,
      pct: expenses > 0 ? (item.total / expenses) * 100 : 0,
    }))
    .sort((a, b) => b.total - a.total);

  const sortedIn = [...revenueEntries].sort((a, b) => b.amount - a.amount);
  const sortedOut = [...expenseEntries].sort((a, b) => b.amount - a.amount);

  const topIn = sortedIn.slice(0, 10);
  const topOut = sortedOut.slice(0, 10);

  const inTotal = revenueEntries.reduce((acc, entry) => acc + entry.amount, 0);
  const outTotal = expenseEntries.reduce((acc, entry) => acc + entry.amount, 0);
  const topInSum = topIn.reduce((acc, it) => acc + it.amount, 0);
  const restInSum = sortedIn.slice(10).reduce((acc, it) => acc + it.amount, 0);
  const topOutSum = topOut.reduce((acc, it) => acc + it.amount, 0);
  const restOutSum = sortedOut.slice(10).reduce((acc, it) => acc + it.amount, 0);

  if (Math.abs(inTotal - revenue) > 0.01 || Math.abs(outTotal - expenses) > 0.01) {
    console.warn("Sanity check divergente no DRE analítico.", {
      revenue,
      expenses,
      inTotal,
      outTotal,
    });
  }

  if (Math.abs(topInSum + restInSum - revenue) > 0.01) {
    console.warn("Divergência entre TopIn+Restantes e KPI receita.", {
      revenue,
      topInSum,
      restInSum,
      diff: (topInSum + restInSum) - revenue,
    });
  }

  if (Math.abs(topOutSum + restOutSum - expenses) > 0.01) {
    console.warn("Divergência entre TopOut+Restantes e KPI despesas.", {
      expenses,
      topOutSum,
      restOutSum,
      diff: (topOutSum + restOutSum) - expenses,
    });
  }

  if (revenue > 0 && revenueByMethod.length === 0 && revenueByBank.length === 0) {
    console.warn("DRE analítico retornou receita sem breakdowns.");
  }

  return {
    ok: true as const,
    corpo: {
      ok: true,
      kpis: {
        revenue,
        expenses,
        net,
        margin,
      },
      breakdowns: {
        revenueByMethod,
        revenueByBank,
        expenseByCategory,
        topIn,
        topOut,
      },
      entries,
    },
  };
}