import { createAdminClient } from "@/lib/supabase-server";

export type ReportMetric =
  | "total_in"
  | "total_out"
  | "net_total"
  | "transactions"
  | "avg_in_ticket"
  | "avg_out_ticket";

export type ReportDimension =
  | "month"
  | "date"
  | "origin"
  | "type"
  | "payment_method"
  | "bank_account"
  | "category"
  | "channel"
  | "client"
  | "product"
  | "seller"
  | "status";

export type FinancialReportConfig = {
  startDate: string;
  endDate: string;
  metrics: ReportMetric[];
  dimensions: ReportDimension[];
  filters?: {
    origin?: string[];
    type?: string[];
    paymentMethod?: string[];
    channel?: string[];
    status?: string[];
    bankAccountId?: string[];
    categoryId?: string[];
  };
};

export type FinancialEntry = {
  date: string;
  month: string;
  origin: string;
  type: "IN" | "OUT";
  amount: number;
  paymentMethod: string | null;
  bankAccountId: string | null;
  bankAccount: string | null;
  categoryId: string | null;
  category: string | null;
  channel: string | null;
  client: string | null;
  product: string | null;
  seller: string | null;
  status: string | null;
  description: string;
};

function toMonth(date: string) {
  return date.slice(0, 7);
}

function normalizeMethod(value: string | null | undefined) {
  const v = (value || "").trim().toLowerCase();
  if (!v) return "sem_forma";
  if (v === "credit_card") return "cartao_credito";
  if (v === "debit_card") return "cartao_debito";
  if (v === "cash") return "dinheiro";
  if (v === "pix") return "pix";
  if (v === "transfer") return "transferencia";
  return v;
}

function normalizeChannel(quoteTitle: string | null | undefined, orderNotes: string | null | undefined) {
  const title = String(quoteTitle || "").toLowerCase();
  const notes = String(orderNotes || "").toLowerCase();
  if (title.includes("venda pdv") || notes.includes("pdv")) return "pdv";
  if (title.includes("catálogo") || title.includes("catalogo") || notes.includes("catalogo")) return "catalogo";
  return "comercial";
}

function inList(value: string | null, list?: string[]) {
  if (!list || list.length === 0) return true;
  const v = (value || "").toLowerCase();
  return list.some((i) => i.toLowerCase() === v);
}

async function loadEntries(
  admin: ReturnType<typeof createAdminClient>,
  companyId: string,
  config: FinancialReportConfig
) {
  const { data: flowRows, error: flowError } = await admin
    .from("cash_flow")
    .select("id,date,type,origin,reference_id,description,amount,bank_account_id")
    .eq("company_id", companyId)
    .gte("date", config.startDate)
    .lte("date", config.endDate)
    .order("date", { ascending: true });

  if (flowError) throw flowError;
  const rows = flowRows ?? [];

  const paymentIds = Array.from(
    new Set(
      rows
        .filter((r) => r.origin === "PEDIDO" && typeof r.reference_id === "string")
        .map((r) => r.reference_id as string)
    )
  );
  const payableIds = Array.from(
    new Set(
      rows
        .filter((r) => r.origin === "PAGAR" && typeof r.reference_id === "string")
        .map((r) => r.reference_id as string)
    )
  );
  const receiptIds = Array.from(
    new Set(
      rows
        .filter((r) => r.origin === "RECEBER" && typeof r.reference_id === "string")
        .map((r) => r.reference_id as string)
    )
  );
  const bankIds = Array.from(
    new Set(rows.map((r) => r.bank_account_id).filter((id): id is string => typeof id === "string" && id.length > 0))
  );

  const bankMap = new Map<string, string>();
  if (bankIds.length > 0) {
    const { data } = await admin.from("bank_accounts").select("id,name,bank_name").in("id", bankIds);
    (data ?? []).forEach((b) => {
      bankMap.set(String(b.id), String(b.name || b.bank_name || "Conta bancária"));
    });
  }

  const paymentsMap = new Map<
    string,
    { payment_method: string | null; order_id: string | null }
  >();
  const orderIds = new Set<string>();
  if (paymentIds.length > 0) {
    const { data } = await admin
      .from("payments")
      .select("id,payment_method,order_id")
      .in("id", paymentIds);
    (data ?? []).forEach((p) => {
      paymentsMap.set(String(p.id), {
        payment_method: p.payment_method ?? null,
        order_id: p.order_id ?? null,
      });
      if (p.order_id) orderIds.add(String(p.order_id));
    });
  }

  const ordersMap = new Map<
    string,
    {
      status: string | null;
      quote_id: string | null;
      primary_seller_user_id: string | null;
      internal_notes: string | null;
    }
  >();
  const quoteIds = new Set<string>();
  const sellerIds = new Set<string>();
  if (orderIds.size > 0) {
    const { data } = await admin
      .from("orders")
      .select("id,status,quote_id,primary_seller_user_id,internal_notes")
      .in("id", Array.from(orderIds));
    (data ?? []).forEach((o) => {
      ordersMap.set(String(o.id), {
        status: o.status ?? null,
        quote_id: o.quote_id ?? null,
        primary_seller_user_id: o.primary_seller_user_id ?? null,
        internal_notes: o.internal_notes ?? null,
      });
      if (o.quote_id) quoteIds.add(String(o.quote_id));
      if (o.primary_seller_user_id) sellerIds.add(String(o.primary_seller_user_id));
    });
  }

  const quoteMap = new Map<string, { title: string | null; client_id: string | null }>();
  const clientIds = new Set<string>();
  if (quoteIds.size > 0) {
    const { data } = await admin.from("quotes").select("id,title,client_id").in("id", Array.from(quoteIds));
    (data ?? []).forEach((q) => {
      quoteMap.set(String(q.id), { title: q.title ?? null, client_id: q.client_id ?? null });
      if (q.client_id) clientIds.add(String(q.client_id));
    });
  }

  const clientMap = new Map<string, string>();
  if (clientIds.size > 0) {
    const { data } = await admin.from("clients").select("id,name,company_name").in("id", Array.from(clientIds));
    (data ?? []).forEach((c) => {
      const label = String(c.company_name || c.name || "Cliente");
      clientMap.set(String(c.id), label);
    });
  }

  const productByOrderMap = new Map<string, string>();
  if (orderIds.size > 0) {
    const { data } = await admin.from("order_items").select("order_id,title").in("order_id", Array.from(orderIds));
    const grouped = new Map<string, Set<string>>();
    (data ?? []).forEach((i) => {
      const key = String(i.order_id);
      const current = grouped.get(key) ?? new Set<string>();
      current.add(String(i.title || "Item"));
      grouped.set(key, current);
    });
    grouped.forEach((titles, orderId) => {
      productByOrderMap.set(orderId, Array.from(titles).slice(0, 3).join(", "));
    });
  }

  const sellerMap = new Map<string, string>();
  if (sellerIds.size > 0) {
    const { data } = await admin
      .from("company_users")
      .select("user_id,email")
      .eq("company_id", companyId)
      .in("user_id", Array.from(sellerIds));
    (data ?? []).forEach((s) => {
      sellerMap.set(String(s.user_id), String(s.email || "Vendedor"));
    });
  }

  const payableMap = new Map<string, { status: string | null; payment_method: string | null; category_id: string | null }>();
  const receivableMap = new Map<
    string,
    { payment_method: string | null; receivable_id: string | null; category_id: string | null; client: string | null; status: string | null }
  >();
  const categoryIds = new Set<string>();

  if (payableIds.length > 0) {
    const { data } = await admin
      .from("accounts_payable")
      .select("id,status,payment_method,category_id")
      .in("id", payableIds);
    (data ?? []).forEach((p) => {
      payableMap.set(String(p.id), {
        status: p.status ?? null,
        payment_method: p.payment_method ?? null,
        category_id: p.category_id ?? null,
      });
      if (p.category_id) categoryIds.add(String(p.category_id));
    });
  }

  if (receiptIds.length > 0) {
    const { data } = await admin
      .from("receivable_receipts")
      .select("id,payment_method,receivable_id,accounts_receivable(id,client_name,client_company,status,category_id)")
      .in("id", receiptIds);
    (data ?? []).forEach((r) => {
      const ar = Array.isArray(r.accounts_receivable) ? r.accounts_receivable[0] : r.accounts_receivable;
      const client = String(ar?.client_company || ar?.client_name || "").trim() || null;
      receivableMap.set(String(r.id), {
        payment_method: r.payment_method ?? null,
        receivable_id: ar?.id ? String(ar.id) : null,
        category_id: ar?.category_id ? String(ar.category_id) : null,
        client,
        status: ar?.status ? String(ar.status) : null,
      });
      if (ar?.category_id) categoryIds.add(String(ar.category_id));
    });
  }

  const categoryMap = new Map<string, string>();
  if (categoryIds.size > 0) {
    const { data } = await admin.from("financial_categories").select("id,name").in("id", Array.from(categoryIds));
    (data ?? []).forEach((c) => categoryMap.set(String(c.id), String(c.name || "Sem categoria")));
  }

  const entries: FinancialEntry[] = rows
    .map((row) => {
      const type = String(row.type || "").toUpperCase() as "IN" | "OUT";
      if (type !== "IN" && type !== "OUT") return null;
      const origin = String(row.origin || "UNKNOWN").toUpperCase();
      const amount = Number(row.amount || 0);
      const date = String(row.date || "");
      if (!date) return null;

      const base: FinancialEntry = {
        date,
        month: toMonth(date),
        origin,
        type,
        amount,
        paymentMethod: null,
        bankAccountId: row.bank_account_id ? String(row.bank_account_id) : null,
        bankAccount: row.bank_account_id ? bankMap.get(String(row.bank_account_id)) || null : null,
        categoryId: null,
        category: null,
        channel: null,
        client: null,
        product: null,
        seller: null,
        status: null,
        description: String(row.description || ""),
      };

      const ref = row.reference_id ? String(row.reference_id) : null;
      if (origin === "PEDIDO" && ref) {
        const payment = paymentsMap.get(ref);
        const orderId = payment?.order_id || null;
        const order = orderId ? ordersMap.get(orderId) : undefined;
        const quote = order?.quote_id ? quoteMap.get(order.quote_id) : undefined;
        const client = quote?.client_id ? clientMap.get(quote.client_id) : null;
        base.paymentMethod = normalizeMethod(payment?.payment_method);
        base.channel = normalizeChannel(quote?.title, order?.internal_notes);
        base.client = client || null;
        base.product = orderId ? productByOrderMap.get(orderId) || null : null;
        base.seller = order?.primary_seller_user_id ? sellerMap.get(order.primary_seller_user_id) || null : null;
        base.status = order?.status || null;
      } else if (origin === "PAGAR" && ref) {
        const payable = payableMap.get(ref);
        base.paymentMethod = normalizeMethod(payable?.payment_method);
        base.categoryId = payable?.category_id || null;
        base.category = payable?.category_id ? categoryMap.get(payable.category_id) || null : null;
        base.status = payable?.status || null;
      } else if (origin === "RECEBER" && ref) {
        const receipt = receivableMap.get(ref);
        base.paymentMethod = normalizeMethod(receipt?.payment_method);
        base.categoryId = receipt?.category_id || null;
        base.category = receipt?.category_id ? categoryMap.get(receipt.category_id) || null : null;
        base.client = receipt?.client || null;
        base.status = receipt?.status || null;
      } else if (origin === "AJUSTE" || origin === "MANUAL") {
        base.channel = "manual";
      }
      return base;
    })
    .filter((entry): entry is FinancialEntry => Boolean(entry));

  return entries;
}

function applyFilters(entries: FinancialEntry[], config: FinancialReportConfig) {
  const filters = config.filters || {};
  return entries.filter((entry) => {
    if (!inList(entry.origin, filters.origin)) return false;
    if (!inList(entry.type, filters.type)) return false;
    if (!inList(entry.paymentMethod, filters.paymentMethod)) return false;
    if (!inList(entry.channel, filters.channel)) return false;
    if (!inList(entry.status, filters.status)) return false;
    if (filters.bankAccountId && filters.bankAccountId.length > 0) {
      if (!entry.bankAccountId || !filters.bankAccountId.includes(entry.bankAccountId)) return false;
    }
    if (filters.categoryId && filters.categoryId.length > 0) {
      if (!entry.categoryId || !filters.categoryId.includes(entry.categoryId)) return false;
    }
    return true;
  });
}

function getDimensionValue(entry: FinancialEntry, dimension: ReportDimension) {
  switch (dimension) {
    case "month":
      return entry.month;
    case "date":
      return entry.date;
    case "origin":
      return entry.origin;
    case "type":
      return entry.type;
    case "payment_method":
      return entry.paymentMethod || "sem_forma";
    case "bank_account":
      return entry.bankAccount || "sem_conta";
    case "category":
      return entry.category || "sem_categoria";
    case "channel":
      return entry.channel || "sem_canal";
    case "client":
      return entry.client || "sem_cliente";
    case "product":
      return entry.product || "sem_produto";
    case "seller":
      return entry.seller || "sem_vendedor";
    case "status":
      return entry.status || "sem_status";
    default:
      return "n/a";
  }
}

function aggregate(entries: FinancialEntry[], config: FinancialReportConfig) {
  const groups = new Map<
    string,
    {
      dimensions: Record<string, string>;
      totalIn: number;
      totalOut: number;
      inCount: number;
      outCount: number;
      txCount: number;
    }
  >();

  for (const entry of entries) {
    const dimensions: Record<string, string> = {};
    for (const d of config.dimensions) {
      dimensions[d] = getDimensionValue(entry, d);
    }
    const key = JSON.stringify(dimensions);
    const current =
      groups.get(key) ??
      {
        dimensions,
        totalIn: 0,
        totalOut: 0,
        inCount: 0,
        outCount: 0,
        txCount: 0,
      };
    current.txCount += 1;
    if (entry.type === "IN") {
      current.totalIn += entry.amount;
      current.inCount += 1;
    } else {
      current.totalOut += entry.amount;
      current.outCount += 1;
    }
    groups.set(key, current);
  }

  return Array.from(groups.values()).map((group) => {
    const values: Record<ReportMetric, number> = {
      total_in: group.totalIn,
      total_out: group.totalOut,
      net_total: group.totalIn - group.totalOut,
      transactions: group.txCount,
      avg_in_ticket: group.inCount > 0 ? group.totalIn / group.inCount : 0,
      avg_out_ticket: group.outCount > 0 ? group.totalOut / group.outCount : 0,
    };
    const metricPayload: Record<string, number> = {};
    for (const metric of config.metrics) {
      metricPayload[metric] = values[metric];
    }
    return {
      ...group.dimensions,
      ...metricPayload,
    };
  });
}

function buildKpis(entries: FinancialEntry[]) {
  const totalIn = entries.filter((e) => e.type === "IN").reduce((sum, e) => sum + e.amount, 0);
  const totalOut = entries.filter((e) => e.type === "OUT").reduce((sum, e) => sum + e.amount, 0);
  const inCount = entries.filter((e) => e.type === "IN").length;
  const outCount = entries.filter((e) => e.type === "OUT").length;
  const net = totalIn - totalOut;
  return {
    total_in: totalIn,
    total_out: totalOut,
    net_total: net,
    margin_pct: totalIn > 0 ? (net / totalIn) * 100 : 0,
    total_transactions: entries.length,
    in_transactions: inCount,
    out_transactions: outCount,
    avg_in_ticket: inCount > 0 ? totalIn / inCount : 0,
    avg_out_ticket: outCount > 0 ? totalOut / outCount : 0,
  };
}

export async function runFinancialReport(companyId: string, config: FinancialReportConfig) {
  const admin = createAdminClient();
  const loaded = await loadEntries(admin, companyId, config);
  const filtered = applyFilters(loaded, config);
  const rows = aggregate(filtered, config);
  const kpis = buildKpis(filtered);
  return {
    config,
    kpis,
    rows,
    entries_count: filtered.length,
    generated_at: new Date().toISOString(),
  };
}