import type { User } from "@supabase/supabase-js";
import { createAdminClient } from "@/lib/supabase-server";
import { getCompanyContext, type CompanyContext } from "@/lib/company-context";
import { calculateEffectiveQuantity } from "@/lib/price-tiers";
import type {
  ReportChart,
  ReportCompanyInfo,
  ReportCustomConfig,
  ReportCustomField,
  ReportCustomMetric,
  ReportCustomUniverse,
  ReportKpi,
  ReportOptionsPayload,
  ReportPreset,
  ReportPresetInput,
  ReportRequest,
  ReportResponse,
  ReportTabKey,
  ReportTableColumn,
  ReportTableRow,
} from "@/lib/reports/contracts";
import {
  asArray,
  buildSeriesMap,
  endOfToday,
  firstOrNull,
  formatValueByType,
  humanizeEnumLabel,
  matchesFilter,
  matchesTextSearch,
  monthLabel,
  paginateRows,
  periodKey,
  safeJson,
  sortRows,
  startOfMonth,
  takeTopSeries,
  toNumber,
  uniqueOptions,
} from "@/lib/reports/utils";

export type ReportRuntimeContext = {
  ctx: CompanyContext;
  user: User;
  admin: ReturnType<typeof createAdminClient>;
  company: ReportCompanyInfo;
};

type SalesBaseRow = {
  orderId: string;
  orderNumber: number;
  orderDate: string;
  orderStatus: string | null;
  financialStatus: string | null;
  clientId: string | null;
  clientName: string;
  city: string | null;
  state: string | null;
  sellerId: string | null;
  sellerName: string | null;
  paymentMethod: string | null;
  orderTotal: number;
  paidAmount: number;
  quoteId: string | null;
  quoteStatus: string | null;
  quoteTitle: string | null;
  productId: string | null;
  productName: string;
  categoryId: string | null;
  categoryName: string | null;
  quantity: number;
  lineTotal: number;
  lineCost: number;
  profit: number;
};

type FinancialEntry = {
  date: string;
  origin: string;
  type: "IN" | "OUT";
  amount: number;
  receiptEffect: number;
  description: string;
  paymentMethod: string | null;
  clientName: string | null;
  status: string | null;
  bankAccountId: string | null;
  bankAccountName: string | null;
  categoryId: string | null;
  categoryName: string | null;
  channel: string | null;
  sellerName: string | null;
};

type AccountsReceivableRow = {
  id: string;
  due_date: string | null;
  total_amount: number | null;
  paid_amount: number | null;
  client_name: string | null;
  client_company: string | null;
  status: string | null;
  description: string | null;
  bank_account_id: string | null;
  category_id: string | null;
};

type FinancialReceiptEntry = {
  date: string;
  amount: number;
  clientName: string | null;
  paymentMethod: string | null;
  bankAccountId: string | null;
  bankAccountName: string | null;
  categoryId: string | null;
  categoryName: string | null;
  status: string | null;
  description: string;
};

/* Os itens do PEDIDO guardam o total em `total_price`; quem tem `line_total` e o
   item do ORCAMENTO. Trocar um pelo outro derrubava a consulta inteira e o
   relatorio de produtos zerava sem avisar. */
type OrderItemLite = {
  id: string;
  order_id: string;
  product_id: string | null;
  title: string | null;
  quantity: number | null;
  unit_price: number | null;
  total_price: number | null;
  unit_type: string | null;
  width_m: number | null;
  height_m: number | null;
  length_m: number | null;
  calculated_cost: number | null;
};

function getDefaultPeriod(filters: Record<string, unknown>) {
  const startDate = typeof filters.startDate === "string" && filters.startDate ? filters.startDate : startOfMonth();
  const endDate = typeof filters.endDate === "string" && filters.endDate ? filters.endDate : endOfToday();
  return { startDate, endDate };
}

type QueryErrorLike = {
  message?: string | null;
  details?: string | null;
  hint?: string | null;
  code?: string | null;
};

function isMissingOptionalRelation(error: QueryErrorLike | null | undefined) {
  const message = String(error?.message || "").toLowerCase();
  const details = String(error?.details || "").toLowerCase();
  return (
    error?.code === "PGRST200" ||
    error?.code === "PGRST201" ||
    error?.code === "PGRST205" ||
    error?.code === "42P01" ||
    error?.code === "42703" ||
    message.includes("schema cache") ||
    message.includes("could not find") ||
    message.includes("does not exist") ||
    details.includes("relationship") ||
    details.includes("schema cache")
  );
}

function describeQueryError(scope: string, error: QueryErrorLike | null | undefined) {
  const message = String(error?.message || "").trim();
  const details = String(error?.details || "").trim();
  const hint = String(error?.hint || "").trim();
  const code = String(error?.code || "").trim();
  const extras = [message, details, hint, code ? `codigo ${code}` : ""].filter(Boolean).join(" | ");
  return extras ? `Falha ao carregar ${scope}. ${extras}` : `Falha ao carregar ${scope}.`;
}

function normalizeMethod(method: string | null | undefined) {
  const value = String(method || "").trim();
  if (!value) return "Sem forma de pagamento";
  return humanizeEnumLabel(value);
}

function normalizeStatus(status: string | null | undefined) {
  const value = String(status || "").trim();
  if (!value) return "Sem status";
  return humanizeEnumLabel(value);
}

function normalizeFinancialChannel(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("catalogo") || title.includes("catálogo") || notes.includes("catalogo")) return "Catalogo";
  return "Comercial";
}

function getOpenReceivableBalance(item: AccountsReceivableRow) {
  return Math.max(0, toNumber(item.total_amount) - toNumber(item.paid_amount));
}

function buildChronologicalSeries(points: Array<{ date: string | null | undefined; value: number }>) {
  const grouped = new Map<string, number>();
  for (const point of points) {
    const rawDate = String(point.date || "").slice(0, 10);
    if (!rawDate) continue;
    const key = periodKey(rawDate);
    grouped.set(key, (grouped.get(key) || 0) + point.value);
  }
  return Array.from(grouped.entries())
    .sort((a, b) => a[0].localeCompare(b[0]))
    .map(([key, value]) => ({
      label: monthLabel(key),
      value,
    }));
}

function buildFinancialDistributionSeries(points: Array<{ label: string | null | undefined; value: number }>) {
  const grouped = new Map<string, number>();
  for (const point of points) {
    const label = String(point.label || "").trim() || "Sem forma de pagamento";
    grouped.set(label, (grouped.get(label) || 0) + point.value);
  }
  return Array.from(grouped.entries())
    .map(([label, value]) => ({ label, value }))
    .sort((a, b) => b.value - a.value || a.label.localeCompare(b.label, "pt-BR"));
}

function getFinancialReceivableStatusLabel(item: AccountsReceivableRow, endDate: string) {
  const balance = getOpenReceivableBalance(item);
  if (balance <= 0) return null;

  const dueDate = String(item.due_date || "").slice(0, 10);
  if (dueDate && dueDate < endDate) return "Em atraso";

  const normalizedStatus = normalizeStatus(item.status);
  if (normalizedStatus === "Parcialmente pago") return normalizedStatus;
  if (normalizedStatus === "Pendente") return normalizedStatus;
  if (normalizedStatus === "Sem status") return "Pendente";
  return normalizedStatus;
}

function buildFinancialReceivableStatusSeries(receivables: AccountsReceivableRow[], endDate: string) {
  const grouped = new Map<string, number>();
  for (const item of receivables) {
    const label = getFinancialReceivableStatusLabel(item, endDate);
    if (!label) continue;
    grouped.set(label, (grouped.get(label) || 0) + getOpenReceivableBalance(item));
  }

  const order = new Map([
    ["Pendente", 0],
    ["Parcialmente pago", 1],
    ["Em atraso", 2],
  ]);

  return Array.from(grouped.entries())
    .map(([label, value]) => ({ label, value }))
    .sort(
      (a, b) =>
        (order.get(a.label) ?? 99) - (order.get(b.label) ?? 99) ||
        b.value - a.value ||
        a.label.localeCompare(b.label, "pt-BR")
    );
}

function compareDateRange(date: string | null, startDate: string, endDate: string) {
  if (!date) return false;
  return date >= startDate && date <= endDate;
}

const COMMERCIAL_ORDER_DEFAULT_STATUS = "pending_production";
const COMMERCIAL_INELIGIBLE_ORDER_STATUSES = new Set(["canceled", "cancelled", "draft"]);
const COMMERCIAL_QUOTE_DEFAULT_STATUS = "draft";
const COMMERCIAL_INELIGIBLE_QUOTE_STATUSES = new Set(["draft"]);
const COMMERCIAL_VISIBLE_ORDER_STATUSES = [
  COMMERCIAL_ORDER_DEFAULT_STATUS,
  "in_production",
  "finished",
  "delivered",
] as const;

function normalizeCommercialOrderStatus(status: string | null | undefined) {
  const value = String(status || "").trim().toLowerCase();
  return value || COMMERCIAL_ORDER_DEFAULT_STATUS;
}

function isCommerciallyValidOrderStatus(status: string | null | undefined) {
  return !COMMERCIAL_INELIGIBLE_ORDER_STATUSES.has(normalizeCommercialOrderStatus(status));
}

function normalizeCommercialQuoteStatus(status: string | null | undefined) {
  const value = String(status || "").trim().toLowerCase();
  return value || COMMERCIAL_QUOTE_DEFAULT_STATUS;
}

function isCommerciallyEligibleQuoteStatus(status: string | null | undefined) {
  return !COMMERCIAL_INELIGIBLE_QUOTE_STATUSES.has(normalizeCommercialQuoteStatus(status));
}

function isCommercialOrderOriginatedFromQuote(order: { quote_id?: string | null | undefined }) {
  return typeof order.quote_id === "string" && order.quote_id.trim().length > 0;
}

function isCommerciallyConvertedOrder(order: { quote_id?: string | null | undefined; status?: string | null | undefined }) {
  return isCommercialOrderOriginatedFromQuote(order) && isCommerciallyValidOrderStatus(order.status);
}

function getCommercialOrderStatusOptions() {
  return uniqueOptions(COMMERCIAL_VISIBLE_ORDER_STATUSES.map(normalizeStatus));
}

function matchesCommercialConversionFilters(
  quote: {
    client_id?: string | null;
    primary_seller_user_id?: string | null;
    title?: string | null;
  },
  filters: ReportRequest["filters"]
) {
  const clientFilter = asArray(filters.client);
  const sellerFilter = asArray(filters.seller);
  const channelFilter = asArray(filters.channel);

  if (clientFilter.length > 0) {
    const clientId = typeof quote.client_id === "string" ? quote.client_id : null;
    if (!clientId || !clientFilter.includes(clientId)) return false;
  }

  if (sellerFilter.length > 0) {
    const sellerId = typeof quote.primary_seller_user_id === "string" ? quote.primary_seller_user_id : null;
    if (!sellerId || !sellerFilter.includes(sellerId)) return false;
  }

  if (channelFilter.length > 0) {
    const channel = normalizeFinancialChannel(quote.title, null);
    if (!matchesFilter(channel, channelFilter)) return false;
  }

  return true;
}

async function loadCompanyInfo(runtime: { ctx: CompanyContext; admin: ReturnType<typeof createAdminClient> }) {
  const { ctx, admin } = runtime;
  const companyProfile = await admin
    .from("profiles")
    .select("id,store_name,logo_url,catalog_logo_url,cnpj")
    .eq("id", ctx.companyId)
    .maybeSingle();

  const companyRaw = await admin
    .from("companies")
    .select("id,name")
    .eq("id", ctx.companyId)
    .maybeSingle();

  const companyName =
    String(companyProfile.data?.store_name || companyRaw.data?.name || "Empresa").trim() || "Empresa";

  return {
    companyId: ctx.companyId,
    companyName,
    companyDocument: companyProfile.data?.cnpj ?? null,
    companyLogoUrl: companyProfile.data?.logo_url ?? companyProfile.data?.catalog_logo_url ?? null,
  } satisfies ReportCompanyInfo;
}

export async function createReportRuntime(supabase: Awaited<ReturnType<typeof import("@/lib/supabase-server").createClient>>) {
  const {
    data: { user },
    error,
  } = await supabase.auth.getUser();

  if (error || !user) {
    throw new Error("Não autenticado.");
  }

  const ctx = await getCompanyContext(supabase, user);
  return createReportRuntimeFor({ ctx, user, admin: createAdminClient() });
}

/** O mesmo contexto, para quem já sabe a pessoa e a empresa (o assistente). */
export async function createReportRuntimeFor(params: { ctx: CompanyContext; user: User; admin: ReturnType<typeof createAdminClient> }) {
  const { ctx, user, admin } = params;
  const company = await loadCompanyInfo({ ctx, admin });

  return { ctx, user, admin, company } satisfies ReportRuntimeContext;
}

async function loadSellerMap(runtime: ReportRuntimeContext) {
  const { admin, ctx } = runtime;
  const { data } = await admin
    .from("company_users")
    .select("user_id,email")
    .eq("company_id", ctx.companyId)
    .eq("active", true);

  const userIds = (data ?? []).map((item) => item.user_id).filter((item): item is string => typeof item === "string");
  const profileRows = userIds.length
    ? (
        await admin
          .from("employee_profiles")
          .select("user_id,full_name")
          .in("user_id", userIds)
      ).data ?? []
    : [];

  const profileMap = new Map<string, string>();
  for (const row of profileRows) {
    if (row.user_id && row.full_name) {
      profileMap.set(String(row.user_id), String(row.full_name));
    }
  }

  return new Map(
    (data ?? []).map((item) => [
      String(item.user_id),
      profileMap.get(String(item.user_id)) || String(item.email || "Vendedor"),
    ])
  );
}

async function loadOptions(runtime: ReportRuntimeContext) {
  const { admin, ctx } = runtime;

  const [
    clientsResult,
    productsResult,
    categoriesResult,
    suppliersResult,
    sellerMap,
    paymentsResult,
    receivableReceiptsResult,
    payablesResult,
    bankAccountsResult,
    financialCategoriesResult,
  ] =
    await Promise.all([
      admin
        .from("clients")
        .select("id,name,company_name,address_city,address_state")
        .in("user_id", ctx.tenantIds),
      admin
        .from("products")
        .select("id,name,category_id")
        .in("user_id", ctx.tenantIds),
      admin
        .from("categories")
        .select("id,name")
        .in("user_id", ctx.tenantIds),
      /* Pedia status "active", em ingles. A coluna so grava "ativo",
         "pendente" e "inativo", entao a lista vinha vazia para todas as
         empresas desde que o modulo nasceu. Alem do status certo, o filtro
         passa a esconder os fornecedores removidos. */
      admin
        .from("suppliers")
        .select("id,name")
        .eq("company_id", ctx.companyId)
        .eq("status", "ativo")
        .is("deleted_at", null)
        .order("name", { ascending: true }),
      loadSellerMap(runtime),
      admin.from("payments").select("payment_method").in("user_id", ctx.tenantIds).limit(500),
      admin.from("receivable_receipts").select("payment_method").eq("company_id", ctx.companyId).limit(500),
      admin.from("accounts_payable").select("payment_method").eq("company_id", ctx.companyId).limit(500),
      admin.from("bank_accounts").select("id,name,bank_name,active").eq("company_id", ctx.companyId).order("name"),
      admin.from("financial_categories").select("id,name").eq("company_id", ctx.companyId).order("name"),
    ]);

  const clients = (clientsResult.data ?? []).map((row) => ({
    value: String(row.id),
    label: String(row.company_name || row.name || "Cliente"),
  }));

  const categories = (categoriesResult.data ?? []).map((row) => ({
    value: String(row.id),
    label: String(row.name || "Categoria"),
  }));

  const suppliers = (suppliersResult.data ?? []).map((row) => ({
    value: String(row.id),
    label: String(row.name || "Fornecedor"),
  }));

  const sellers = Array.from(sellerMap.entries())
    .map(([value, label]) => ({ value, label }))
    .sort((a, b) => a.label.localeCompare(b.label, "pt-BR", { sensitivity: "base" }));

  const paymentMethods = uniqueOptions((paymentsResult.data ?? []).map((item) => normalizeMethod(item.payment_method)));
  const financialPaymentMethods = uniqueOptions([
    ...(paymentsResult.data ?? []).map((item) => normalizeMethod(item.payment_method)),
    ...(receivableReceiptsResult.data ?? []).map((item) => normalizeMethod(item.payment_method)),
    ...(payablesResult.data ?? []).map((item) => normalizeMethod(item.payment_method)),
  ]);
  const statuses = getCommercialOrderStatusOptions();
  const financialStatuses = uniqueOptions(["paid", "partially_paid", "pending", "overdue", "canceled"].map(normalizeStatus));
  const bankAccounts = (bankAccountsResult.data ?? [])
    .filter((row) => row.active !== false)
    .map((row) => ({
      value: String(row.id),
      label: String(row.name || row.bank_name || "Conta"),
    }));
  const financialCategories = (financialCategoriesResult.data ?? []).map((row) => ({
    value: String(row.id),
    label: String(row.name || "Categoria financeira"),
  }));
  const channels = uniqueOptions(["Comercial", "PDV", "Catalogo", "Manual"]);

  const cities = uniqueOptions((clientsResult.data ?? []).map((item) => item.address_city));
  const states = uniqueOptions((clientsResult.data ?? []).map((item) => item.address_state));

  return {
    clients,
    categories,
    suppliers,
    sellers,
    paymentMethods,
    statuses,
    financialPaymentMethods,
    financialStatuses,
    bankAccounts,
    financialCategories,
    channels,
    cities,
    states,
    products: productsResult.data ?? [],
  };
}

const CUSTOM_UNIVERSE_DEFAULT_COLUMNS: Record<ReportCustomUniverse, string[]> = {
  sales: ["date", "client", "seller", "status", "paymentMethod", "value"],
  products: ["product", "category", "quantity", "value", "profit", "saleStatus"],
  customers: ["client", "city", "state", "newClient", "activityStatus", "recurrenceStatus", "orderCount", "value"],
  financialCash: ["date", "recordType", "client", "paymentMethod", "bankAccount", "financialCategory", "value"],
  financialReceivables: ["date", "client", "financialCategory", "status", "type", "value"],
};

const CUSTOM_UNIVERSE_DEFAULT_METRICS: Record<ReportCustomUniverse, string[]> = {
  sales: ["orders", "ticket", "conversion", "soldValue"],
  products: ["soldQty", "revenue", "profit", "margin"],
  customers: ["newCustomers", "activeCustomers", "inactiveCustomers", "recurringCustomers", "avgTicket"],
  financialCash: ["received", "cashFlowNet"],
  financialReceivables: ["accountsReceivable", "overdue"],
};

const customFields: ReportCustomField[] = [
  {
    id: "date",
    label: "Data",
    type: "date",
    tabKeys: ["sales", "products", "customers", "financial", "custom"],
    universes: ["sales", "financialCash", "financialReceivables"],
  },
  {
    id: "client",
    label: "Cliente",
    type: "text",
    tabKeys: ["sales", "customers", "financial", "custom"],
    universes: ["sales", "customers", "financialCash", "financialReceivables"],
  },
  { id: "seller", label: "Vendedor", type: "text", tabKeys: ["sales", "custom"], universes: ["sales"] },
  {
    id: "status",
    label: "Status",
    type: "badge",
    tabKeys: ["sales", "financial", "custom"],
    universes: ["sales", "financialCash", "financialReceivables"],
  },
  {
    id: "paymentMethod",
    label: "Forma de pagamento",
    type: "text",
    tabKeys: ["sales", "financial", "custom"],
    universes: ["sales", "financialCash"],
  },
  {
    id: "value",
    label: "Valor",
    type: "currency",
    tabKeys: ["sales", "products", "customers", "financial", "custom"],
    universes: ["sales", "products", "customers", "financialCash", "financialReceivables"],
  },
  { id: "product", label: "Produto", type: "text", tabKeys: ["products", "custom"], universes: ["products"] },
  { id: "category", label: "Categoria", type: "text", tabKeys: ["products", "custom"], universes: ["products"] },
  { id: "quantity", label: "Quantidade", type: "number", tabKeys: ["products", "custom"], universes: ["products"] },
  { id: "profit", label: "Lucro", type: "currency", tabKeys: ["products", "custom"], universes: ["products"] },
  { id: "saleStatus", label: "Status de venda", type: "badge", tabKeys: ["products", "custom"], universes: ["products"] },
  { id: "city", label: "Cidade", type: "text", tabKeys: ["customers", "custom"], universes: ["customers"] },
  { id: "state", label: "Estado", type: "text", tabKeys: ["customers", "custom"], universes: ["customers"] },
  { id: "newClient", label: "Base", type: "badge", tabKeys: ["customers", "custom"], universes: ["customers"] },
  { id: "activityStatus", label: "Status de atividade", type: "badge", tabKeys: ["customers", "custom"], universes: ["customers"] },
  { id: "recurrenceStatus", label: "Recorrência", type: "badge", tabKeys: ["customers", "custom"], universes: ["customers"] },
  { id: "orderCount", label: "Pedidos no período", type: "number", tabKeys: ["customers", "custom"], universes: ["customers"] },
  {
    id: "recordType",
    label: "Base financeira",
    type: "badge",
    tabKeys: ["financial", "custom"],
    universes: ["financialCash", "financialReceivables"],
  },
  { id: "description", label: "Descrição", type: "text", tabKeys: ["financial", "custom"], universes: ["financialCash", "financialReceivables"] },
  { id: "origin", label: "Origem", type: "badge", tabKeys: ["financial", "custom"], universes: ["financialCash"] },
  { id: "bankAccount", label: "Conta bancária", type: "text", tabKeys: ["financial", "custom"], universes: ["financialCash", "financialReceivables"] },
  {
    id: "financialCategory",
    label: "Categoria financeira",
    type: "text",
    tabKeys: ["financial", "custom"],
    universes: ["financialCash", "financialReceivables"],
  },
  { id: "type", label: "Tipo", type: "badge", tabKeys: ["financial", "custom"], universes: ["financialCash", "financialReceivables"] },
  {
    id: "channel",
    label: "Canal",
    type: "text",
    tabKeys: ["customers", "financial", "custom"],
    universes: ["customers", "financialCash"],
  },
];

const customMetrics: ReportCustomMetric[] = [
  { id: "orders", label: "Total de pedidos", type: "number", universes: ["sales"] },
  { id: "ticket", label: "Ticket médio", type: "currency", universes: ["sales"] },
  { id: "conversion", label: "Taxa de conversão", type: "percent", universes: ["sales"] },
  { id: "soldValue", label: "Faturamento total", type: "currency", universes: ["sales"] },
  { id: "soldQty", label: "Quantidade vendida", type: "number", universes: ["products"] },
  { id: "revenue", label: "Receita gerada", type: "currency", universes: ["products"] },
  { id: "profit", label: "Lucro", type: "currency", universes: ["products"] },
  { id: "margin", label: "Margem média", type: "percent", universes: ["products"] },
  { id: "newCustomers", label: "Novos clientes", type: "number", universes: ["customers"] },
  { id: "activeCustomers", label: "Clientes ativos", type: "number", universes: ["customers"] },
  { id: "inactiveCustomers", label: "Clientes inativos", type: "number", universes: ["customers"] },
  { id: "recurringCustomers", label: "Clientes recorrentes", type: "number", universes: ["customers"] },
  { id: "avgTicket", label: "Ticket médio por cliente", type: "currency", universes: ["customers"] },
  { id: "received", label: "Valor recebido", type: "currency", universes: ["financialCash"] },
  { id: "cashFlowNet", label: "Fluxo de caixa liquido", type: "currency", universes: ["financialCash"] },
  { id: "accountsReceivable", label: "Contas a receber", type: "currency", universes: ["financialReceivables"] },
  { id: "overdue", label: "Contas em atraso", type: "currency", universes: ["financialReceivables"] },
];

export async function listReportPresets(runtime: ReportRuntimeContext) {
  const { admin, ctx, user } = runtime;
  const { data, error } = await admin
    .from("report_presets")
    .select("id,name,report_key,scope,is_favorite,config_version,config,created_at,updated_at,user_id")
    .eq("company_id", ctx.companyId)
    .is("deleted_at", null)
    .order("is_favorite", { ascending: false })
    .order("sort_order", { ascending: true })
    .order("created_at", { ascending: false });

  if (error) {
    throw new Error("Falha ao carregar modelos de relatório.");
  }

  return (data ?? [])
    .filter((item) => item.scope === "company" || !item.user_id || item.user_id === user.id)
    .map<ReportPreset>((item) => ({
      id: String(item.id),
      name: String(item.name),
      reportKey: item.report_key as ReportTabKey,
      scope: item.scope === "user" ? "user" : "company",
      isFavorite: Boolean(item.is_favorite),
      configVersion: Number(item.config_version || 1),
      config: safeJson(item.config, {}),
      createdAt: String(item.created_at),
      updatedAt: String(item.updated_at),
    }));
}

export async function saveReportPreset(runtime: ReportRuntimeContext, input: ReportPresetInput) {
  const { admin, ctx, user } = runtime;
  const payload = {
    company_id: ctx.companyId,
    user_id: input.scope === "user" ? user.id : null,
    report_key: input.reportKey,
    name: input.name.trim(),
    scope: input.scope,
    is_favorite: input.isFavorite === true,
    config_version: 1,
    config: input.config,
    created_by: user.id,
    updated_at: new Date().toISOString(),
  };

  if (input.id) {
    const { error } = await admin
      .from("report_presets")
      .update(payload)
      .eq("id", input.id)
      .eq("company_id", ctx.companyId);
    if (error) throw new Error("Falha ao atualizar modelo.");
  } else {
    const { error } = await admin.from("report_presets").insert(payload);
    if (error) throw new Error("Falha ao salvar modelo.");
  }

  return listReportPresets(runtime);
}

export async function deleteReportPreset(runtime: ReportRuntimeContext, presetId: string) {
  const { admin, ctx } = runtime;
  const { error } = await admin
    .from("report_presets")
    .update({ deleted_at: new Date().toISOString() })
    .eq("id", presetId)
    .eq("company_id", ctx.companyId);

  if (error) {
    throw new Error("Falha ao remover modelo.");
  }

  return listReportPresets(runtime);
}

export async function getReportOptions(runtime: ReportRuntimeContext): Promise<ReportOptionsPayload> {
  const optionData = await loadOptions(runtime);
  const presets = await listReportPresets(runtime);

  return {
    company: runtime.company,
    clients: optionData.clients,
    paymentMethods: optionData.paymentMethods,
    statuses: optionData.statuses,
    financialPaymentMethods: optionData.financialPaymentMethods,
    financialStatuses: optionData.financialStatuses,
    bankAccounts: optionData.bankAccounts,
    financialCategories: optionData.financialCategories,
    channels: optionData.channels,
    sellers: optionData.sellers,
    categories: optionData.categories,
    suppliers: optionData.suppliers,
    cities: optionData.cities,
    states: optionData.states,
    presets,
    customFields,
    customMetrics,
  };
}

async function loadSalesBase(runtime: ReportRuntimeContext, request: ReportRequest) {
  const { admin, ctx } = runtime;
  const { startDate, endDate } = getDefaultPeriod(request.filters);
  const sellerMap = await loadSellerMap(runtime);

  const ordersResult = await admin
    .from("orders")
    .select("id,number,status,total_amount,paid_amount,financial_status,created_at,client_id,quote_id,primary_seller_user_id")
    .in("user_id", ctx.tenantIds)
    .gte("created_at", `${startDate}T00:00:00`)
    .lte("created_at", `${endDate}T23:59:59`)
    .order("created_at", { ascending: false });

  if (ordersResult.error) throw new Error("Falha ao carregar pedidos.");
  const orders = (ordersResult.data ?? []).filter((order) => isCommerciallyValidOrderStatus(order.status));
  const orderIds = orders.map((item) => String(item.id));
  const clientIds = Array.from(new Set(orders.map((item) => item.client_id).filter((item): item is string => typeof item === "string")));
  const quoteIds = Array.from(new Set(orders.map((item) => item.quote_id).filter((item): item is string => typeof item === "string")));

  const [clientRows, quoteRows, paymentRows, itemRows] = await Promise.all([
    clientIds.length
      ? admin.from("clients").select("id,name,company_name,address_city,address_state").in("id", clientIds)
      : Promise.resolve({ data: [], error: null }),
    quoteIds.length
      ? admin.from("quotes").select("id,status,title,total,primary_seller_user_id,created_at").in("id", quoteIds)
      : Promise.resolve({ data: [], error: null }),
    orderIds.length
      ? admin.from("payments").select("id,order_id,payment_method").in("order_id", orderIds)
      : Promise.resolve({ data: [], error: null }),
    orderIds.length
      ? admin
          .from("order_items")
          .select(
            "id,order_id,product_id,title,quantity,unit_price,total_price,unit_type,width_m,height_m,length_m,calculated_cost"
          )
          .in("order_id", orderIds)
      : Promise.resolve({ data: [], error: null }),
  ]);

  /* Sem esta conferencia, uma consulta recusada pelo banco virava "nenhum item"
     e o relatorio saia todo zerado, como se a loja nao tivesse vendido nada. */
  if (clientRows.error) throw new Error(describeQueryError("clientes dos pedidos", clientRows.error));
  if (quoteRows.error) throw new Error(describeQueryError("orcamentos dos pedidos", quoteRows.error));
  if (paymentRows.error) throw new Error(describeQueryError("pagamentos dos pedidos", paymentRows.error));
  if (itemRows.error) throw new Error(describeQueryError("itens dos pedidos", itemRows.error));

  const productIds = Array.from(
    new Set((itemRows.data ?? []).map((item) => item.product_id).filter((item): item is string => typeof item === "string"))
  );

  const productRows = productIds.length
    ? await admin.from("products").select("id,name,category_id,cost_total").in("id", productIds)
    : { data: [], error: null };

  if (productRows.error) throw new Error(describeQueryError("produtos vendidos", productRows.error));

  const categoryIds = Array.from(
    new Set((productRows.data ?? []).map((item) => item.category_id).filter((item): item is string => typeof item === "string"))
  );
  const categoryRows = categoryIds.length
    ? await admin.from("categories").select("id,name").in("id", categoryIds)
    : { data: [], error: null };

  const clientMap = new Map(
    (clientRows.data ?? []).map((item) => [
      String(item.id),
      {
        name: String(item.company_name || item.name || "Cliente"),
        city: item.address_city ? String(item.address_city) : null,
        state: item.address_state ? String(item.address_state) : null,
      },
    ])
  );
  const quoteMap = new Map(
    (quoteRows.data ?? []).map((item) => [
      String(item.id),
      {
        status: item.status ? String(item.status) : null,
        title: item.title ? String(item.title) : null,
        sellerId: item.primary_seller_user_id ? String(item.primary_seller_user_id) : null,
      },
    ])
  );

  const paymentMap = new Map<string, string>();
  for (const item of paymentRows.data ?? []) {
    const key = String(item.order_id || "");
    if (!key || paymentMap.has(key)) continue;
    paymentMap.set(key, normalizeMethod(item.payment_method));
  }

  const productMap = new Map(
    (productRows.data ?? []).map((item) => [
      String(item.id),
      {
        name: String(item.name || "Produto"),
        categoryId: item.category_id ? String(item.category_id) : null,
        unitCost: toNumber(item.cost_total),
      },
    ])
  );

  const categoryMap = new Map(
    (categoryRows.data ?? []).map((item) => [String(item.id), String(item.name || "Categoria")])
  );

  const normalizedItemRows = (itemRows.data ?? []) as OrderItemLite[];
  const itemMap = new Map<string, OrderItemLite[]>();
  for (const item of normalizedItemRows) {
    const key = String(item.order_id);
    itemMap.set(key, [...(itemMap.get(key) ?? []), item]);
  }

  const baseRows: SalesBaseRow[] = [];
  for (const order of orders) {
    const orderId = String(order.id);
    const client = order.client_id ? clientMap.get(String(order.client_id)) : null;
    const quote = order.quote_id ? quoteMap.get(String(order.quote_id)) : null;
    const sellerId =
      typeof order.primary_seller_user_id === "string" && order.primary_seller_user_id
        ? String(order.primary_seller_user_id)
        : quote?.sellerId || null;
    const items = itemMap.get(orderId) ?? [];

    if (items.length === 0) {
      baseRows.push({
        orderId,
        orderNumber: Number(order.number || 0),
        orderDate: String(order.created_at).slice(0, 10),
        orderStatus: order.status ? String(order.status) : null,
        financialStatus: order.financial_status ? String(order.financial_status) : null,
        clientId: order.client_id ? String(order.client_id) : null,
        clientName: client?.name || "Cliente",
        city: client?.city || null,
        state: client?.state || null,
        sellerId,
        sellerName: sellerId ? sellerMap.get(sellerId) || "Vendedor" : null,
        paymentMethod: paymentMap.get(orderId) || null,
        orderTotal: toNumber(order.total_amount),
        paidAmount: toNumber(order.paid_amount),
        quoteId: order.quote_id ? String(order.quote_id) : null,
        quoteStatus: quote?.status || null,
        quoteTitle: quote?.title || null,
        productId: null,
        productName: "Sem item",
        categoryId: null,
        categoryName: null,
        quantity: 0,
        lineTotal: 0,
        lineCost: 0,
        profit: 0,
      });
      continue;
    }

    for (const item of items) {
      const product = item.product_id ? productMap.get(String(item.product_id)) : null;
      const quantity = toNumber(item.quantity);
      /* O que se cobra: peca vezes area (m2) ou vezes comprimento (metro linear).
         O custo cadastrado tambem e por m2 / por metro, entao os dois lados da
         conta precisam usar a mesma medida, senao o lucro sai inflado. */
      const billableQuantity = calculateEffectiveQuantity({
        quantity,
        unit_type: item.unit_type,
        width_m: item.width_m,
        height_m: item.height_m,
        length_m: item.length_m,
      });
      const lineTotal = item.total_price === null || item.total_price === undefined
        ? toNumber(item.unit_price) * billableQuantity
        : toNumber(item.total_price);
      /* Custo da venda: vale o que foi apurado na hora (calculated_cost). Zero ou
         vazio ali quer dizer que ninguem apurou, entao cai no custo atual do
         produto em vez de fingir que a peca saiu de graca. */
      const snapshotCost = toNumber(item.calculated_cost);
      const unitCost = snapshotCost > 0 ? snapshotCost : toNumber(product?.unitCost);
      const lineCost = unitCost * billableQuantity;
      baseRows.push({
        orderId,
        orderNumber: Number(order.number || 0),
        orderDate: String(order.created_at).slice(0, 10),
        orderStatus: order.status ? String(order.status) : null,
        financialStatus: order.financial_status ? String(order.financial_status) : null,
        clientId: order.client_id ? String(order.client_id) : null,
        clientName: client?.name || "Cliente",
        city: client?.city || null,
        state: client?.state || null,
        sellerId,
        sellerName: sellerId ? sellerMap.get(sellerId) || "Vendedor" : null,
        paymentMethod: paymentMap.get(orderId) || null,
        orderTotal: toNumber(order.total_amount),
        paidAmount: toNumber(order.paid_amount),
        quoteId: order.quote_id ? String(order.quote_id) : null,
        quoteStatus: quote?.status || null,
        quoteTitle: quote?.title || null,
        productId: item.product_id ? String(item.product_id) : null,
        productName: product?.name || String(item.title || "Produto"),
        categoryId: product?.categoryId || null,
        categoryName: product?.categoryId ? categoryMap.get(product.categoryId) || null : null,
        quantity,
        lineTotal,
        lineCost,
        profit: lineTotal - lineCost,
      });
    }
  }

  const sellerFilter = asArray(request.filters.seller);
  const clientFilter = asArray(request.filters.client);
  const statusFilter = asArray(request.filters.status).map((item) => item.toLowerCase());
  const categoryFilter = asArray(request.filters.category);
  const cityFilter = asArray(request.filters.city);
  const stateFilter = asArray(request.filters.state);
  const paymentFilter = asArray(request.filters.paymentMethod);

  return baseRows.filter((row) => {
    if (sellerFilter.length > 0 && (!row.sellerId || !sellerFilter.includes(row.sellerId))) return false;
    if (clientFilter.length > 0 && (!row.clientId || !clientFilter.includes(row.clientId))) return false;
    if (statusFilter.length > 0 && !statusFilter.includes(normalizeStatus(row.orderStatus).toLowerCase())) return false;
    if (categoryFilter.length > 0 && (!row.categoryId || !categoryFilter.includes(row.categoryId))) return false;
    if (cityFilter.length > 0 && !matchesFilter(row.city, cityFilter)) return false;
    if (stateFilter.length > 0 && !matchesFilter(row.state, stateFilter)) return false;
    if (paymentFilter.length > 0 && !matchesFilter(row.paymentMethod, paymentFilter)) return false;
    return true;
  });
}

async function buildFinancialReport(runtime: ReportRuntimeContext, request: ReportRequest): Promise<ReportResponse> {
  const { admin, ctx, company } = runtime;
  const { startDate, endDate } = getDefaultPeriod(request.filters);

  const flowResult = await admin
    .from("cash_flow")
    .select("date,type,origin,reference_id,description,amount,bank_account_id")
    .eq("company_id", ctx.companyId)
    .gte("date", startDate)
    .lte("date", endDate)
    .order("date", { ascending: false });

  const receivablesResult = await admin
    .from("accounts_receivable")
    .select("id,due_date,total_amount,paid_amount,client_name,client_company,status,description,bank_account_id,category_id")
    .eq("company_id", ctx.companyId);

  if (flowResult.error) {
    throw new Error(describeQueryError("dados do fluxo financeiro", flowResult.error));
  }

  const flowRows = flowResult.data ?? [];
  const receivables = receivablesResult.error
    ? isMissingOptionalRelation(receivablesResult.error)
      ? []
      : (() => {
          throw new Error(describeQueryError("contas a receber", receivablesResult.error));
        })()
    : ((receivablesResult.data ?? []) as AccountsReceivableRow[]);

  const paymentIds = Array.from(
    new Set(
      flowRows
        .filter((item) => item.origin === "PEDIDO" && typeof item.reference_id === "string")
        .map((item) => String(item.reference_id))
    )
  );
  const payableIds = Array.from(
    new Set(
      flowRows
        .filter((item) => item.origin === "PAGAR" && typeof item.reference_id === "string")
        .map((item) => String(item.reference_id))
    )
  );
  const receiptIds = Array.from(
    new Set(
      flowRows
        .filter(
          (item) =>
            (item.origin === "RECEBER" || item.origin === "AJUSTE") &&
            typeof item.reference_id === "string"
        )
        .map((item) => String(item.reference_id))
    )
  );

  const [payments, payables, receiptsResult, periodReceiptsResult, bankAccounts, sellerMap] = await Promise.all([
    paymentIds.length
      ? admin.from("payments").select("id,order_id,payment_method").in("id", paymentIds)
      : Promise.resolve({ data: [], error: null }),
    payableIds.length
      ? admin.from("accounts_payable").select("id,status,description,payment_method,category_id").in("id", payableIds)
      : Promise.resolve({ data: [], error: null }),
    receiptIds.length
      ? admin.from("receivable_receipts").select("id,payment_method,receivable_id,bank_account_id").in("id", receiptIds)
      : Promise.resolve({ data: [], error: null }),
    admin
      .from("receivable_receipts")
      .select("id,receivable_id,amount,received_at,payment_method,bank_account_id")
      .eq("company_id", ctx.companyId)
      .gte("received_at", startDate)
      .lte("received_at", endDate)
      .order("received_at", { ascending: false }),
    admin.from("bank_accounts").select("id,name,bank_name").eq("company_id", ctx.companyId),
    loadSellerMap(runtime),
  ]);

  if (payments.error) throw new Error(describeQueryError("pagamentos do fluxo", payments.error));
  if (payables.error) throw new Error(describeQueryError("contas a pagar", payables.error));
  if (bankAccounts.error) throw new Error(describeQueryError("contas bancarias", bankAccounts.error));

  const receiptRows = receiptsResult.error
    ? isMissingOptionalRelation(receiptsResult.error)
      ? []
      : (() => {
          throw new Error(describeQueryError("recebimentos de contas a receber", receiptsResult.error));
        })()
    : (receiptsResult.data ?? []);

  const periodReceiptRows = periodReceiptsResult.error
    ? isMissingOptionalRelation(periodReceiptsResult.error)
      ? []
      : (() => {
          throw new Error(describeQueryError("recebimentos do periodo", periodReceiptsResult.error));
        })()
    : (periodReceiptsResult.data ?? []);

  const linkedReceivableIds = Array.from(
    new Set(
      [...receiptRows, ...periodReceiptRows]
        .map((item) => ("receivable_id" in item ? item.receivable_id : null))
        .filter((item): item is string => typeof item === "string" && item.length > 0)
    )
  );

  const linkedReceivablesResult = linkedReceivableIds.length
    ? await admin
        .from("accounts_receivable")
        .select("id,client_name,client_company,status,description,category_id,bank_account_id")
        .eq("company_id", ctx.companyId)
        .in("id", linkedReceivableIds)
    : { data: [], error: null };

  const linkedReceivableMap = new Map(
    (linkedReceivablesResult.error && isMissingOptionalRelation(linkedReceivablesResult.error)
      ? []
      : linkedReceivablesResult.data ?? []
    ).map((item) => [String(item.id), item])
  );

  if (linkedReceivablesResult.error && !isMissingOptionalRelation(linkedReceivablesResult.error)) {
    throw new Error(describeQueryError("detalhes das contas a receber", linkedReceivablesResult.error));
  }

  const categoryIds = Array.from(
    new Set(
      [
        ...(payables.data ?? []).map((item) => item.category_id),
        ...receivables.map((item) => item.category_id),
        ...(linkedReceivablesResult.data ?? []).map((item) => item.category_id),
      ].filter((item): item is string => typeof item === "string" && item.length > 0)
    )
  );
  const financialCategoriesResult = categoryIds.length
    ? await admin.from("financial_categories").select("id,name").in("id", categoryIds)
    : { data: [], error: null };

  if (financialCategoriesResult.error) {
    throw new Error(describeQueryError("categorias financeiras", financialCategoriesResult.error));
  }

  const orderIds = Array.from(
    new Set((payments.data ?? []).map((item) => item.order_id).filter((item): item is string => typeof item === "string"))
  );
  const orderRows = orderIds.length
    ? await admin
        .from("orders")
        .select("id,status,primary_seller_user_id,quote_id,internal_notes")
        .in("id", orderIds)
    : { data: [], error: null };
  const quoteIds = Array.from(
    new Set((orderRows.data ?? []).map((item) => item.quote_id).filter((item): item is string => typeof item === "string"))
  );
  const quoteRows = quoteIds.length
    ? await admin.from("quotes").select("id,title,client_id").in("id", quoteIds)
    : { data: [], error: null };
  const clientIds = Array.from(
    new Set((quoteRows.data ?? []).map((item) => item.client_id).filter((item): item is string => typeof item === "string"))
  );
  const clientRows = clientIds.length
    ? await admin.from("clients").select("id,name,company_name").in("id", clientIds)
    : { data: [], error: null };

  if (orderRows.error) throw new Error(describeQueryError("pedidos vinculados", orderRows.error));
  if (quoteRows.error) throw new Error(describeQueryError("orcamentos vinculados", quoteRows.error));
  if (clientRows.error) throw new Error(describeQueryError("clientes vinculados", clientRows.error));

  const paymentMap = new Map((payments.data ?? []).map((item) => [String(item.id), item]));
  const payableMap = new Map((payables.data ?? []).map((item) => [String(item.id), item]));
  const receiptMap = new Map((receiptRows ?? []).map((item) => [String(item.id), item]));
  const bankMap = new Map(
    (bankAccounts.data ?? []).map((item) => [String(item.id), String(item.name || item.bank_name || "Conta")])
  );
  const categoryMap = new Map(
    (financialCategoriesResult.data ?? []).map((item) => [String(item.id), String(item.name || "Categoria financeira")])
  );
  const orderMap = new Map((orderRows.data ?? []).map((item) => [String(item.id), item]));
  const quoteMap = new Map((quoteRows.data ?? []).map((item) => [String(item.id), item]));
  const clientMap = new Map(
    (clientRows.data ?? []).map((item) => [String(item.id), String(item.company_name || item.name || "Cliente")])
  );

  const entries: FinancialEntry[] = flowRows.map((row) => {
    const type = String(row.type || "").toUpperCase() === "OUT" ? "OUT" : "IN";
    const origin = String(row.origin || "MANUAL").toUpperCase();
    const base: FinancialEntry = {
      date: String(row.date || ""),
      origin,
      type,
      amount: toNumber(row.amount),
      receiptEffect: 0,
      description: String(row.description || origin),
      paymentMethod: null,
      clientName: null,
      status: null,
      bankAccountId: row.bank_account_id ? String(row.bank_account_id) : null,
      bankAccountName: row.bank_account_id ? bankMap.get(String(row.bank_account_id)) || null : null,
      categoryId: null,
      categoryName: null,
      channel: null,
      sellerName: null,
    };

    if (origin === "PEDIDO" && row.reference_id) {
      const payment = paymentMap.get(String(row.reference_id));
      const order = payment?.order_id ? orderMap.get(String(payment.order_id)) : null;
      const quote = order?.quote_id ? quoteMap.get(String(order.quote_id)) : null;
      base.paymentMethod = normalizeMethod(payment?.payment_method);
      base.status = normalizeStatus(order?.status ?? null);
      base.clientName = quote?.client_id ? clientMap.get(String(quote.client_id)) || null : null;
      base.sellerName =
        order?.primary_seller_user_id ? sellerMap.get(String(order.primary_seller_user_id)) || "Vendedor" : null;
      base.channel = normalizeFinancialChannel(quote?.title, order?.internal_notes);
    }

    if (origin === "PAGAR" && row.reference_id) {
      const payable = payableMap.get(String(row.reference_id));
      base.paymentMethod = normalizeMethod(payable?.payment_method);
      base.status = normalizeStatus(payable?.status ?? null);
      base.categoryId = payable?.category_id ? String(payable.category_id) : null;
      base.categoryName = payable?.category_id ? categoryMap.get(String(payable.category_id)) || null : null;
      base.description = String(payable?.description || base.description);
    }

    if ((origin === "RECEBER" || origin === "AJUSTE") && row.reference_id) {
      const receipt = receiptMap.get(String(row.reference_id));
      if (receipt) {
        const receivable = receipt.receivable_id ? linkedReceivableMap.get(String(receipt.receivable_id)) ?? null : null;
        base.paymentMethod = normalizeMethod(receipt.payment_method);
        base.bankAccountId =
          receipt.bank_account_id && typeof receipt.bank_account_id === "string" ? String(receipt.bank_account_id) : base.bankAccountId;
        base.bankAccountName =
          receipt.bank_account_id && typeof receipt.bank_account_id === "string"
            ? bankMap.get(String(receipt.bank_account_id)) || base.bankAccountName
            : base.bankAccountName;
        base.clientName = receivable ? String(receivable.client_company || receivable.client_name || "").trim() || null : null;
        base.status = receivable ? normalizeStatus(String(receivable.status || "")) : null;
        base.categoryId = receivable?.category_id ? String(receivable.category_id) : null;
        base.categoryName = receivable?.category_id ? categoryMap.get(String(receivable.category_id)) || null : null;
        base.description = receivable ? String(receivable.description || base.description) : base.description;
        base.receiptEffect = type === "IN" ? base.amount : -base.amount;
      }
    }

    if (origin === "AJUSTE" || origin === "MANUAL") {
      base.channel = "Manual";
    }

    return base;
  });

  const clientFilter = asArray(request.filters.client);
  const paymentFilter = asArray(request.filters.paymentMethod);
  const statusFilter = asArray(request.filters.status);
  const bankAccountFilter = asArray(request.filters.bankAccount);
  const financialCategoryFilter = asArray(request.filters.financialCategory);
  const channelFilter = asArray(request.filters.channel);

  const receivedEntries: FinancialReceiptEntry[] = periodReceiptRows.map((receipt) => {
    const receivable = receipt.receivable_id ? linkedReceivableMap.get(String(receipt.receivable_id)) ?? null : null;
    return {
      date: String(receipt.received_at || "").slice(0, 10),
      amount: toNumber(receipt.amount),
      clientName: receivable ? String(receivable.client_company || receivable.client_name || "").trim() || null : null,
      paymentMethod: normalizeMethod(receipt.payment_method),
      bankAccountId: receipt.bank_account_id ? String(receipt.bank_account_id) : null,
      bankAccountName: receipt.bank_account_id ? bankMap.get(String(receipt.bank_account_id)) || null : null,
      categoryId: receivable?.category_id ? String(receivable.category_id) : null,
      categoryName: receivable?.category_id ? categoryMap.get(String(receivable.category_id)) || null : null,
      status: receivable ? normalizeStatus(String(receivable.status || "")) : null,
      description: receivable ? String(receivable.description || "Recebimento") : "Recebimento",
    };
  });

  const filteredCashEntries = entries.filter((entry) => {
    if (clientFilter.length > 0 && !matchesFilter(entry.clientName, clientFilter)) return false;
    if (paymentFilter.length > 0 && !matchesFilter(entry.paymentMethod, paymentFilter)) return false;
    if (bankAccountFilter.length > 0 && (!entry.bankAccountId || !bankAccountFilter.includes(entry.bankAccountId))) return false;
    if (financialCategoryFilter.length > 0 && (!entry.categoryId || !financialCategoryFilter.includes(entry.categoryId))) return false;
    if (channelFilter.length > 0 && !matchesFilter(entry.channel, channelFilter)) return false;
    return true;
  });

  const filteredReceipts = receivedEntries.filter((entry) => {
    if (clientFilter.length > 0 && !matchesFilter(entry.clientName, clientFilter)) return false;
    if (paymentFilter.length > 0 && !matchesFilter(entry.paymentMethod, paymentFilter)) return false;
    if (bankAccountFilter.length > 0 && (!entry.bankAccountId || !bankAccountFilter.includes(entry.bankAccountId))) return false;
    if (financialCategoryFilter.length > 0 && (!entry.categoryId || !financialCategoryFilter.includes(entry.categoryId))) return false;
    return true;
  });

  const openReceivables = receivables.filter((item) => {
    const status = String(item.status || "").toUpperCase();
    if (status === "PAID" || status === "CANCELED") return false;

    const clientName = String(item.client_company || item.client_name || "").trim() || null;
    if (clientFilter.length > 0 && !matchesFilter(clientName, clientFilter)) return false;
    const derivedStatus = getFinancialReceivableStatusLabel(item, endDate);
    if (statusFilter.length > 0 && !matchesFilter(derivedStatus, statusFilter)) return false;
    if (financialCategoryFilter.length > 0 && (!item.category_id || !financialCategoryFilter.includes(String(item.category_id)))) {
      return false;
    }
    return true;
  });
  const pendingValue = openReceivables.reduce(
    (sum, item) => sum + getOpenReceivableBalance(item),
    0
  );
  const overdueValue = openReceivables
    .filter((item) => item.due_date && item.due_date < endDate)
    .reduce((sum, item) => sum + getOpenReceivableBalance(item), 0);

  const totalReceived = filteredCashEntries.reduce((sum, item) => sum + item.receiptEffect, 0);
  const cashFlowNet = filteredCashEntries.reduce((sum, item) => sum + (item.type === "IN" ? item.amount : -item.amount), 0);

  const columns: ReportTableColumn[] = [
    { id: "recordType", label: "Base", type: "badge", sortable: true, width: 150, visibleByDefault: true },
    { id: "date", label: "Data", type: "date", sortable: true, width: 120, visibleByDefault: true },
    { id: "clientName", label: "Cliente", type: "text", sortable: true, width: 220, visibleByDefault: true },
    { id: "description", label: "Descrição", type: "text", sortable: true, width: 320, visibleByDefault: true },
    { id: "origin", label: "Origem", type: "badge", sortable: true, width: 130, visibleByDefault: true },
    { id: "channel", label: "Canal", type: "text", sortable: true, width: 140, visibleByDefault: false },
    { id: "bankAccount", label: "Conta bancária", type: "text", sortable: true, width: 180, visibleByDefault: false },
    { id: "category", label: "Categoria financeira", type: "text", sortable: true, width: 200, visibleByDefault: false },
    { id: "paymentMethod", label: "Forma de pagamento", type: "text", sortable: true, width: 180, visibleByDefault: true },
    { id: "status", label: "Status", type: "badge", sortable: true, width: 160, visibleByDefault: true },
    { id: "type", label: "Tipo", type: "badge", sortable: true, width: 100, visibleByDefault: true },
    { id: "amount", label: "Valor", type: "currency", sortable: true, width: 140, align: "right", visibleByDefault: true },
  ];

  const rows: ReportTableRow[] = [
    ...filteredReceipts.map((entry) => ({
      recordType: "Recebimento",
      date: entry.date,
      clientName: entry.clientName || "—",
      description: entry.description,
      origin: "Recebimento",
      channel: "—",
      bankAccount: entry.bankAccountName || "—",
      category: entry.categoryName || "—",
      paymentMethod: entry.paymentMethod || "—",
      status: entry.status || "—",
      type: "Entrada",
      amount: entry.amount,
    })),
    ...filteredCashEntries.map((entry) => ({
      recordType: "Fluxo de caixa",
      date: entry.date,
      clientName: entry.clientName || "—",
      description: entry.description,
      origin: normalizeStatus(entry.origin),
      channel: entry.channel || "—",
      bankAccount: entry.bankAccountName || "—",
      category: entry.categoryName || "—",
      paymentMethod: entry.paymentMethod || "—",
      status: entry.status || "—",
      type: humanizeEnumLabel(entry.type),
      amount: entry.type === "IN" ? entry.amount : -entry.amount,
    })),
    ...openReceivables.map((item) => ({
      recordType: "Carteira",
      date: String(item.due_date || ""),
      clientName: String(item.client_company || item.client_name || "").trim() || "—",
      description: String(item.description || "Titulo em aberto"),
      origin: "Carteira",
      channel: "—",
      bankAccount: item.bank_account_id ? bankMap.get(String(item.bank_account_id)) || "—" : "—",
      category: item.category_id ? categoryMap.get(String(item.category_id)) || "—" : "—",
      paymentMethod: "—",
      status: getFinancialReceivableStatusLabel(item, endDate) || "—",
      type: "Saldo",
      amount: getOpenReceivableBalance(item),
    })),
  ];

  const searchedRows = rows.filter((row) => matchesTextSearch(row, request.search));
  const sortedRows = sortRows(searchedRows, columns, request.sort);
  const pagedRows = request.exportAllRows ? sortedRows : paginateRows(sortedRows, request.page, request.pageSize);

  const charts: ReportChart[] = [
    {
      id: "receipts-period",
      title: "Recebimentos por período",
      type: "column",
      format: "currency",
      series: buildChronologicalSeries(
        filteredReceipts.map((entry) => ({
          date: entry.date,
          value: entry.amount,
        }))
      ),
    },
    {
      id: "cash-flow-period",
      title: "Fluxo de caixa por período",
      type: "line",
      format: "currency",
      series: buildChronologicalSeries(filteredCashEntries.map((entry) => ({
        date: entry.date,
        value: entry.type === "IN" ? entry.amount : -entry.amount,
      }))),
    },
    {
      id: "receipt-payment-methods",
      title: "Distribuição do recebido por forma de pagamento",
      type: "donut",
      format: "currency",
      series: buildFinancialDistributionSeries(
        filteredReceipts.map((entry) => ({
          label: entry.paymentMethod,
          value: entry.amount,
        }))
      ),
    },
    {
      id: "receivables-status",
      title: "Carteira por status",
      type: "donut",
      format: "currency",
      series: buildFinancialReceivableStatusSeries(openReceivables, endDate),
    },
  ];

  const kpis: ReportKpi[] = [
    { id: "received", label: "Valor Recebido", value: totalReceived, type: "currency", tone: "positive" },
    { id: "accountsReceivable", label: "Contas a Receber", value: pendingValue, type: "currency", tone: "warning" },
    { id: "overdue", label: "Contas em Atraso", value: overdueValue, type: "currency", tone: "danger" },
    {
      id: "cashFlowNet",
      label: "Fluxo de Caixa Liquido",
      value: cashFlowNet,
      type: "currency",
      tone: cashFlowNet >= 0 ? "positive" : "danger",
    },
  ];

  return {
    tab: "financial",
    title: "Relatórios Financeiros",
    generatedAt: new Date().toISOString(),
    company,
    appliedFilters: request.filters,
    kpis,
    charts,
    columns,
    rows: pagedRows,
    totalRows: sortedRows.length,
    page: request.page,
    pageSize: request.pageSize,
  };
}

async function buildSalesReport(runtime: ReportRuntimeContext, request: ReportRequest): Promise<ReportResponse> {
  const baseRows = await loadSalesBase(runtime, request);
  const { startDate, endDate } = getDefaultPeriod(request.filters);
  const quotesResult = await runtime.admin
    .from("quotes")
    .select("id,status,title,total,created_at,client_id,primary_seller_user_id")
    .in("user_id", runtime.ctx.tenantIds)
    .gte("created_at", `${startDate}T00:00:00`)
    .lte("created_at", `${endDate}T23:59:59`);

  if (quotesResult.error) throw new Error("Falha ao carregar orcamentos.");

  const uniqueOrders = Array.from(
    new Map(
      baseRows.map((row) => [
        row.orderId,
        {
          orderId: row.orderId,
          orderNumber: row.orderNumber,
          orderDate: row.orderDate,
          clientId: row.clientId,
          clientName: row.clientName,
          sellerName: row.sellerName || "—",
          paymentMethod: row.paymentMethod || "—",
          orderStatus: normalizeStatus(row.orderStatus),
          orderTotal: toNumber(row.orderTotal),
          quoteStatus: normalizeStatus(row.quoteStatus),
        },
      ])
    ).values()
  );
  const totalOrders = uniqueOrders.length;
  const soldValue = uniqueOrders.reduce((sum, row) => sum + row.orderTotal, 0);
  const ticket = totalOrders > 0 ? soldValue / totalOrders : 0;
  const filteredQuoteRows = (quotesResult.data ?? []).filter(
    (item) => isCommerciallyEligibleQuoteStatus(item.status) && matchesCommercialConversionFilters(item, request.filters)
  );
  const eligibleQuoteIds = filteredQuoteRows.map((item) => String(item.id));
  const convertedOrdersResult = eligibleQuoteIds.length
    ? await runtime.admin
        .from("orders")
        .select("id,status,quote_id")
        .in("user_id", runtime.ctx.tenantIds)
        .in("quote_id", eligibleQuoteIds)
    : { data: [], error: null };

  if (convertedOrdersResult.error) throw new Error("Falha ao carregar pedidos convertidos.");

  const convertedQuoteIds = new Set(
    (convertedOrdersResult.data ?? [])
      .filter((item) => isCommerciallyConvertedOrder(item))
      .map((item) => String(item.quote_id))
  );
  const conversionBase = filteredQuoteRows.length;
  const convertedQuotes = convertedQuoteIds.size;
  const conversionRate = conversionBase > 0 ? (convertedQuotes / conversionBase) * 100 : 0;

  const rankingMap = new Map<string, { clientName: string; value: number }>();
  for (const row of uniqueOrders) {
    const rankingKey = row.clientId || `sem-id:${row.clientName}`;
    const current = rankingMap.get(rankingKey) ?? { clientName: row.clientName, value: 0 };
    current.value += row.orderTotal;
    rankingMap.set(rankingKey, current);
  }

  const columns: ReportTableColumn[] = [
    { id: "orderNumber", label: "Pedido", type: "number", sortable: true, width: 100, visibleByDefault: true },
    { id: "orderDate", label: "Data", type: "date", sortable: true, width: 120, visibleByDefault: true },
    { id: "clientName", label: "Cliente", type: "text", sortable: true, width: 220, visibleByDefault: true },
    { id: "sellerName", label: "Vendedor", type: "text", sortable: true, width: 180, visibleByDefault: true },
    { id: "paymentMethod", label: "Forma de pagamento", type: "text", sortable: true, width: 180, visibleByDefault: true },
    { id: "orderStatus", label: "Status", type: "badge", sortable: true, width: 160, visibleByDefault: true },
    { id: "orderTotal", label: "Faturamento", type: "currency", sortable: true, width: 150, align: "right", visibleByDefault: true },
    { id: "quoteStatus", label: "Status orçamento", type: "badge", sortable: true, width: 170, visibleByDefault: true },
  ];

  const uniqueOrderRows = uniqueOrders.map(
    (row) =>
      ({
        orderNumber: row.orderNumber,
        orderDate: row.orderDate,
        clientName: row.clientName,
        sellerName: row.sellerName,
        paymentMethod: row.paymentMethod,
        orderStatus: row.orderStatus,
        orderTotal: row.orderTotal,
        quoteStatus: row.quoteStatus,
      }) satisfies ReportTableRow
  );

  const searchedRows = uniqueOrderRows.filter((row) => matchesTextSearch(row, request.search));
  const sortedRows = sortRows(searchedRows, columns, request.sort);
  const pagedRows = request.exportAllRows ? sortedRows : paginateRows(sortedRows, request.page, request.pageSize);

  return {
    tab: "sales",
    title: "Relatórios de Vendas",
    generatedAt: new Date().toISOString(),
    company: runtime.company,
    appliedFilters: request.filters,
    kpis: [
      { id: "orders", label: "Total de pedidos", value: totalOrders, type: "number" },
      { id: "ticket", label: "Ticket médio", value: ticket, type: "currency" },
      { id: "conversion", label: "Taxa de conversão", value: conversionRate, type: "percent", tone: "positive" },
      { id: "soldValue", label: "Faturamento total", value: soldValue, type: "currency" },
    ],
    charts: [
      {
        id: "sales-evolution",
        title: "Evolução de vendas",
        type: "area",
        format: "currency",
        series: buildChronologicalSeries(uniqueOrders.map((row) => ({ date: row.orderDate, value: row.orderTotal }))),
      },
      {
        id: "quote-conversion",
        title: "Conversão de orçamentos",
        type: "compare",
        format: "number",
        series: [
          { label: "Convertidos", value: convertedQuotes, secondaryValue: conversionBase },
          { label: "Base elegível", value: conversionBase },
        ],
      },
      {
        id: "customers-ranking",
        title: "Ranking de clientes",
        type: "ranking",
        format: "currency",
        series: takeTopSeries(
          Array.from(rankingMap.values())
            .map((item) => ({ label: item.clientName, value: item.value }))
            .sort((a, b) => b.value - a.value),
          8
        ),
      },
    ],
    columns,
    rows: pagedRows,
    totalRows: sortedRows.length,
    page: request.page,
    pageSize: request.pageSize,
  };
}

async function buildProductsReport(runtime: ReportRuntimeContext, request: ReportRequest): Promise<ReportResponse> {
  const baseRows = await loadSalesBase(runtime, request);
  const options = await loadOptions(runtime);
  const soldRows = baseRows.filter((row) => row.productId);
  const productMetrics = new Map<
    string,
    {
      productId: string;
      productName: string;
      categoryName: string | null;
      quantity: number;
      revenue: number;
      cost: number;
      profit: number;
    }
  >();

  for (const row of soldRows) {
    if (!row.productId) continue;
    const current =
      productMetrics.get(row.productId) ??
      {
        productId: row.productId,
        productName: row.productName,
        categoryName: row.categoryName,
        quantity: 0,
        revenue: 0,
        cost: 0,
        profit: 0,
      };
    current.quantity += row.quantity;
    current.revenue += row.lineTotal;
    current.cost += row.lineCost;
    current.profit += row.profit;
    productMetrics.set(row.productId, current);
  }

  const supplierFilter = asArray(request.filters.supplier);
  let supplierProductIds = new Set<string>();
  if (supplierFilter.length > 0) {
    const productMaterials = await runtime.admin
      .from("product_materials")
      .select("product_id,material_id,materials(name)")
      .in("product_id", options.products.map((item) => item.id));
    const supplierMaterials = await runtime.admin
      .from("supplier_materials")
      .select("supplier_id,name")
      .eq("company_id", runtime.ctx.companyId)
      .in("supplier_id", supplierFilter);

    const namesBySupplier = new Set(
      (supplierMaterials.data ?? []).map((item) => String(item.name || "").trim().toLowerCase()).filter(Boolean)
    );
    supplierProductIds = new Set(
      (productMaterials.data ?? [])
        .filter((item) => {
          const material = firstOrNull(item.materials as unknown as { name?: string } | Array<{ name?: string }> | null);
          return material?.name && namesBySupplier.has(String(material.name).trim().toLowerCase());
        })
        .map((item) => String(item.product_id))
    );
  }

  const categoryNameById = new Map(options.categories.map((item) => [item.value, item.label]));
  const baseProductRows = options.products
    .filter((product) => {
      if (supplierFilter.length > 0 && !supplierProductIds.has(String(product.id))) return false;
      if (asArray(request.filters.category).length > 0 && !asArray(request.filters.category).includes(String(product.category_id || ""))) {
        return false;
      }
      return true;
    })
    .map((product) => {
      const metric = productMetrics.get(String(product.id));
      const revenue = metric?.revenue || 0;
      const profit = metric?.profit || 0;
      const quantity = metric?.quantity || 0;
      const hasSales = quantity > 0 || revenue > 0;
      const categoryName =
        metric?.categoryName ||
        categoryNameById.get(String(product.category_id || "")) ||
        "Sem categoria";
      return {
        productId: String(product.id),
        rawProductName: metric?.productName || String(product.name || "Produto"),
        categoryName,
        quantity,
        revenue,
        profit,
        margin: revenue > 0 ? (profit / revenue) * 100 : 0,
        saleStatus: hasSales ? "Com venda" : "Sem venda",
        hasSales,
      };
    });

  const productNameCounts = new Map<string, number>();
  const productNameCategoryCounts = new Map<string, number>();
  for (const row of baseProductRows) {
    const productNameKey = String(row.rawProductName);
    const categoryKey = `${productNameKey}::${String(row.categoryName || "")}`;
    productNameCounts.set(productNameKey, (productNameCounts.get(productNameKey) || 0) + 1);
    productNameCategoryCounts.set(categoryKey, (productNameCategoryCounts.get(categoryKey) || 0) + 1);
  }

  const normalizedRows = baseProductRows.map((row) => {
    let productName = String(row.rawProductName);
    if ((productNameCounts.get(productName) || 0) > 1) {
      const categorySuffix = row.categoryName && row.categoryName !== "Sem categoria" ? ` (${row.categoryName})` : "";
      const categoryScopedName = `${productName}${categorySuffix}`;
      const categoryScopedKey = `${productName}::${String(row.categoryName || "")}`;
      productName =
        (productNameCategoryCounts.get(categoryScopedKey) || 0) > 1 || !categorySuffix
          ? `${categoryScopedName || productName} (#${row.productId.slice(0, 8)})`
          : categoryScopedName;
    }

    return {
      productId: row.productId,
      hasSales: row.hasSales,
      productName,
      categoryName: row.categoryName,
      quantity: row.quantity,
      revenue: row.revenue,
      profit: row.profit,
      margin: row.margin,
      saleStatus: row.saleStatus,
    };
  });

  const sortedProductRows = [
    ...normalizedRows
      .filter((row) => row.hasSales)
      .sort(
        (a, b) =>
          toNumber(b.revenue) - toNumber(a.revenue) ||
          toNumber(b.quantity) - toNumber(a.quantity) ||
          String(a.productName).localeCompare(String(b.productName), "pt-BR", { sensitivity: "base" })
      ),
    ...normalizedRows
      .filter((row) => !row.hasSales)
      .sort((a, b) => String(a.productName).localeCompare(String(b.productName), "pt-BR", { sensitivity: "base" })),
  ];

  const stripProductMetadata = (row: (typeof sortedProductRows)[number]): ReportTableRow => ({
    productName: row.productName,
    categoryName: row.categoryName,
    quantity: row.quantity,
    revenue: row.revenue,
    profit: row.profit,
    margin: row.margin,
    saleStatus: row.saleStatus,
  });

  const rows: ReportTableRow[] = sortedProductRows.map(stripProductMetadata);
  const soldReportRows = sortedProductRows.filter((row) => row.hasSales).map(stripProductMetadata);

  const columns: ReportTableColumn[] = [
    { id: "productName", label: "Produto", type: "text", sortable: true, width: 240, visibleByDefault: true },
    { id: "categoryName", label: "Categoria", type: "text", sortable: true, width: 180, visibleByDefault: true },
    { id: "quantity", label: "Quantidade vendida", type: "number", sortable: true, width: 140, align: "right", visibleByDefault: true },
    { id: "revenue", label: "Receita gerada", type: "currency", sortable: true, width: 160, align: "right", visibleByDefault: true },
    { id: "profit", label: "Lucro", type: "currency", sortable: true, width: 140, align: "right", visibleByDefault: true },
    { id: "margin", label: "Margem", type: "percent", sortable: true, width: 120, align: "right", visibleByDefault: true },
    { id: "saleStatus", label: "Status", type: "badge", sortable: true, width: 130, visibleByDefault: true },
  ];

  const searchedRows = rows.filter((row) => matchesTextSearch(row, request.search));
  const sortedRows = sortRows(searchedRows, columns, request.sort);
  const pagedRows = request.exportAllRows ? sortedRows : paginateRows(sortedRows, request.page, request.pageSize);
  const totalQuantity = rows.reduce((sum, row) => sum + toNumber(row.quantity), 0);
  const totalRevenue = rows.reduce((sum, row) => sum + toNumber(row.revenue), 0);
  const totalProfit = rows.reduce((sum, row) => sum + toNumber(row.profit), 0);
  const averageMargin = totalRevenue > 0 ? (totalProfit / totalRevenue) * 100 : 0;
  const quantityRows = [...soldReportRows].sort(
    (a, b) =>
      toNumber(b.quantity) - toNumber(a.quantity) ||
      toNumber(b.revenue) - toNumber(a.revenue) ||
      String(a.productName).localeCompare(String(b.productName), "pt-BR", { sensitivity: "base" })
  );
  const revenueRows = [...soldReportRows].sort(
    (a, b) =>
      toNumber(b.revenue) - toNumber(a.revenue) ||
      toNumber(b.quantity) - toNumber(a.quantity) ||
      String(a.productName).localeCompare(String(b.productName), "pt-BR", { sensitivity: "base" })
  );
  const totalRevenueForCurve = revenueRows.reduce((sum, row) => sum + toNumber(row.revenue), 0);
  let cumulative = 0;
  const abcSeries = revenueRows.slice(0, 8).map((row) => {
    cumulative += toNumber(row.revenue);
    return {
      label: String(row.productName),
      value: totalRevenueForCurve > 0 ? (cumulative / totalRevenueForCurve) * 100 : 0,
    };
  });

  return {
    tab: "products",
    title: "Relatórios de Produtos",
    generatedAt: new Date().toISOString(),
    company: runtime.company,
    appliedFilters: request.filters,
    kpis: [
      { id: "soldQty", label: "Quantidade vendida", value: totalQuantity, type: "number" },
      { id: "revenue", label: "Receita gerada", value: totalRevenue, type: "currency" },
      { id: "profit", label: "Lucro", value: totalProfit, type: "currency", tone: "positive" },
      { id: "margin", label: "Margem média", value: averageMargin, type: "percent" },
    ],
    charts: [
      {
        id: "products-top-quantity",
        title: "Top produtos por quantidade",
        type: "ranking",
        format: "number",
        series: quantityRows.slice(0, 8).map((row) => ({
          label: String(row.productName),
          value: toNumber(row.quantity),
        })),
      },
      {
        id: "products-top-revenue",
        title: "Top produtos por receita",
        type: "ranking",
        format: "currency",
        series: revenueRows.slice(0, 8).map((row) => ({
          label: String(row.productName),
          value: toNumber(row.revenue),
        })),
      },
      {
        id: "abc-curve",
        title: "Curva ABC",
        type: "line",
        format: "percent",
        series: abcSeries,
      },
      {
        id: "categories-share",
        title: "Participação por categoria",
        type: "donut",
        format: "currency",
        series: takeTopSeries(
          buildSeriesMap(
            soldReportRows.map((row) => ({ key: String(row.categoryName || "Sem categoria"), value: toNumber(row.revenue) }))
          ),
          8
        ),
      },
    ],
    columns,
    rows: pagedRows,
    totalRows: sortedRows.length,
    page: request.page,
    pageSize: request.pageSize,
  };
}

async function buildCustomersReport(runtime: ReportRuntimeContext, request: ReportRequest): Promise<ReportResponse> {
  const { admin, ctx, company } = runtime;
  const { startDate, endDate } = getDefaultPeriod(request.filters);

  const [clientsResult, ordersResult] = await Promise.all([
    admin
      .from("clients")
      .select("id,name,company_name,address_city,address_state,active,created_at")
      .in("user_id", ctx.tenantIds),
    admin
      .from("orders")
      .select("id,client_id,total_amount,created_at,status,quote_id,primary_seller_user_id,internal_notes")
      .in("user_id", ctx.tenantIds)
      .order("created_at", { ascending: true }),
  ]);

  if (clientsResult.error || ordersResult.error) {
    throw new Error("Falha ao carregar clientes.");
  }

  const validOrders = (ordersResult.data ?? []).filter(
    (order) => isCommerciallyValidOrderStatus(order.status) && typeof order.client_id === "string" && order.client_id.length > 0
  );
  const quoteIds = Array.from(
    new Set(validOrders.map((order) => order.quote_id).filter((quoteId): quoteId is string => typeof quoteId === "string" && quoteId.length > 0))
  );
  const quotesResult = quoteIds.length
    ? await admin.from("quotes").select("id,title,primary_seller_user_id").in("id", quoteIds)
    : { data: [], error: null };

  if (quotesResult.error) {
    throw new Error("Falha ao carregar clientes.");
  }

  const quoteMap = new Map(
    (quotesResult.data ?? []).map((quote) => [
      String(quote.id),
      {
        title: typeof quote.title === "string" ? quote.title : null,
        sellerId: typeof quote.primary_seller_user_id === "string" ? quote.primary_seller_user_id : null,
      },
    ])
  );
  const cityFilter = asArray(request.filters.city);
  const stateFilter = asArray(request.filters.state);
  const clientFilter = asArray(request.filters.client);
  const sellerFilter = asArray(request.filters.seller);
  const channelFilter = asArray(request.filters.channel);
  const filteredClients = (clientsResult.data ?? []).filter((client) => {
    if (client.active === false) return false;
    if (clientFilter.length > 0 && !clientFilter.includes(String(client.id))) return false;
    if (cityFilter.length > 0 && !matchesFilter(client.address_city, cityFilter)) return false;
    if (stateFilter.length > 0 && !matchesFilter(client.address_state, stateFilter)) return false;
    return true;
  });
  const filteredClientMap = new Map(
    filteredClients.map((client) => [
      String(client.id),
      {
        baseName: String(client.company_name || client.name || "Cliente"),
        city: client.address_city ? String(client.address_city) : null,
        state: client.address_state ? String(client.address_state) : null,
      },
    ])
  );
  const ordersByClient = new Map<string, Array<{ date: string; total: number }>>();
  for (const order of validOrders) {
    const clientId = String(order.client_id);
    if (!filteredClientMap.has(clientId)) continue;
    const quote = order.quote_id ? quoteMap.get(String(order.quote_id)) ?? null : null;
    const sellerId =
      typeof order.primary_seller_user_id === "string" && order.primary_seller_user_id
        ? String(order.primary_seller_user_id)
        : quote?.sellerId || null;
    if (sellerFilter.length > 0 && (!sellerId || !sellerFilter.includes(sellerId))) continue;
    const channel = normalizeFinancialChannel(quote?.title, order.internal_notes);
    if (channelFilter.length > 0 && !matchesFilter(channel, channelFilter)) continue;
    ordersByClient.set(clientId, [
      ...(ordersByClient.get(clientId) ?? []),
      { date: String(order.created_at).slice(0, 10), total: toNumber(order.total_amount) },
    ]);
  }

  const inactivityThresholdDate = new Date(`${endDate}T00:00:00`);
  inactivityThresholdDate.setDate(inactivityThresholdDate.getDate() - 90);
  const inactivityThreshold = inactivityThresholdDate.toISOString().slice(0, 10);
  const rows = Array.from(ordersByClient.entries())
    .flatMap(([clientId, clientOrders]) => {
      const client = filteredClientMap.get(clientId);
      if (!client) return [];
      const orderedPurchases = [...clientOrders].sort((left, right) => left.date.localeCompare(right.date));
      const firstPurchase = orderedPurchases[0]?.date || null;
      const lastPurchase = orderedPurchases[orderedPurchases.length - 1]?.date || null;
      const periodOrders = orderedPurchases.filter((order) => compareDateRange(order.date, startDate, endDate));
      const periodRevenue = periodOrders.reduce((sum, order) => sum + order.total, 0);
      const periodOrderCount = periodOrders.length;
      const isNew = Boolean(firstPurchase && compareDateRange(firstPurchase, startDate, endDate));
      const isActive = periodOrderCount > 0;
      const isInactive = !isActive && Boolean(lastPurchase && lastPurchase < inactivityThreshold);
      const recurrenceStatus = orderedPurchases.length >= 2 ? "Recorrente" : "Pontual";
      const averageFrequencyDays =
        orderedPurchases.length >= 2
          ? orderedPurchases.slice(1).reduce((sum, order, index) => {
              const previousDate = new Date(`${orderedPurchases[index].date}T00:00:00`);
              const currentDate = new Date(`${order.date}T00:00:00`);
              const diffDays = Math.max(0, Math.round((currentDate.getTime() - previousDate.getTime()) / 86400000));
              return sum + diffDays;
            }, 0) /
            (orderedPurchases.length - 1)
          : null;

      return [
        {
          clientName: `${client.baseName} (#${clientId.slice(0, 8)})`,
          city: client.city || "—",
          state: client.state || "—",
          newClient: isNew ? "Novo" : "Base",
          activityStatus: isActive ? "Ativo" : isInactive ? "Inativo" : "Sem compra no período",
          recurrenceStatus,
          orderCount: periodOrderCount,
          lifetimeOrderCount: orderedPurchases.length,
          totalSpent: periodRevenue,
          ticket: periodOrderCount > 0 ? periodRevenue / periodOrderCount : 0,
          purchaseFrequency: averageFrequencyDays,
          firstPurchase,
          lastPurchase,
        } satisfies ReportTableRow,
      ];
    })
    .sort(
      (left, right) =>
        toNumber(right.totalSpent) - toNumber(left.totalSpent) ||
        toNumber(right.orderCount) - toNumber(left.orderCount) ||
        String(right.lastPurchase || "").localeCompare(String(left.lastPurchase || "")) ||
        String(left.clientName).localeCompare(String(right.clientName), "pt-BR", { sensitivity: "base" })
    );

  const columns: ReportTableColumn[] = [
    { id: "clientName", label: "Cliente", type: "text", sortable: true, width: 220, visibleByDefault: true },
    { id: "city", label: "Cidade", type: "text", sortable: true, width: 160, visibleByDefault: true },
    { id: "state", label: "Estado", type: "text", sortable: true, width: 100, visibleByDefault: true },
    { id: "newClient", label: "Base", type: "badge", sortable: true, width: 100, visibleByDefault: true },
    { id: "activityStatus", label: "Status", type: "badge", sortable: true, width: 150, visibleByDefault: true },
    { id: "recurrenceStatus", label: "Recorrência", type: "badge", sortable: true, width: 140, visibleByDefault: true },
    { id: "orderCount", label: "Pedidos no período", type: "number", sortable: true, width: 120, align: "right", visibleByDefault: true },
    { id: "lifetimeOrderCount", label: "Pedidos válidos", type: "number", sortable: true, width: 120, align: "right", visibleByDefault: false },
    { id: "totalSpent", label: "Valor comprado", type: "currency", sortable: true, width: 150, align: "right", visibleByDefault: true },
    { id: "ticket", label: "Ticket por pedido", type: "currency", sortable: true, width: 150, align: "right", visibleByDefault: true },
    { id: "purchaseFrequency", label: "Frequência média (dias)", type: "number", sortable: true, width: 150, align: "right", visibleByDefault: true },
    { id: "firstPurchase", label: "Primeira compra", type: "date", sortable: true, width: 140, visibleByDefault: false },
    { id: "lastPurchase", label: "Última compra", type: "date", sortable: true, width: 140, visibleByDefault: true },
  ];

  const searchedRows = rows.filter((row) => matchesTextSearch(row, request.search));
  const sortedRows = sortRows(searchedRows, columns, request.sort);
  const pagedRows = request.exportAllRows ? sortedRows : paginateRows(sortedRows, request.page, request.pageSize);

  const newCustomers = rows.filter((row) => row.newClient === "Novo").length;
  const activeCustomers = rows.filter((row) => row.activityStatus === "Ativo").length;
  const inactiveCustomers = rows.filter((row) => row.activityStatus === "Inativo").length;
  const recurringCustomers = rows.filter(
    (row) => row.activityStatus === "Ativo" && row.recurrenceStatus === "Recorrente"
  ).length;
  const activeRows = rows.filter((row) => row.activityStatus === "Ativo");
  const avgTicket =
    activeRows.reduce((sum, row) => sum + toNumber(row.totalSpent), 0) / Math.max(1, activeRows.length);
  const rankingRows = rows.filter((row) => toNumber(row.totalSpent) > 0);

  return {
    tab: "customers",
    title: "Relatórios de Clientes",
    generatedAt: new Date().toISOString(),
    company,
    appliedFilters: request.filters,
    kpis: [
      { id: "newCustomers", label: "Novos clientes", value: newCustomers, type: "number" },
      { id: "activeCustomers", label: "Clientes ativos", value: activeCustomers, type: "number", tone: "positive" },
      { id: "inactiveCustomers", label: "Clientes inativos", value: inactiveCustomers, type: "number", tone: "warning" },
      { id: "recurringCustomers", label: "Clientes recorrentes", value: recurringCustomers, type: "number" },
      { id: "avgTicket", label: "Ticket médio por cliente", value: avgTicket, type: "currency" },
    ],
    charts: [
      {
        id: "customer-acquisition",
        title: "Novos clientes por período",
        type: "column",
        format: "number",
        series: buildChronologicalSeries(
          rows
            .filter((row) => row.newClient === "Novo" && typeof row.firstPurchase === "string")
            .map((row) => ({
              date: String(row.firstPurchase),
              value: 1,
            }))
        ),
      },
      {
        id: "customer-retention",
        title: "Ativos vs Inativos",
        type: "compare",
        format: "number",
        series: [
          { label: "Ativos", value: activeCustomers },
          { label: "Inativos", value: inactiveCustomers },
        ],
      },
      {
        id: "customer-ranking",
        title: "Ranking de clientes",
        type: "ranking",
        format: "currency",
        series: takeTopSeries(
          rankingRows
            .map((row) => ({ label: String(row.clientName), value: toNumber(row.totalSpent) }))
            .sort((a, b) => b.value - a.value || a.label.localeCompare(b.label, "pt-BR", { sensitivity: "base" })),
          8
        ),
      },
      {
        id: "customer-recurrence",
        title: "Recorrência de compra",
        type: "compare",
        format: "number",
        series: [
          { label: "Recorrentes", value: recurringCustomers },
          { label: "Pontuais", value: Math.max(0, activeCustomers - recurringCustomers) },
        ],
      },
      {
        id: "customer-frequency",
        title: "Frequência de compra",
        type: "ranking",
        format: "number",
        series: takeTopSeries(
          rankingRows
            .filter((row) => toNumber(row.orderCount) > 0)
            .map((row) => ({ label: String(row.clientName), value: toNumber(row.orderCount) }))
            .sort((a, b) => b.value - a.value || a.label.localeCompare(b.label, "pt-BR", { sensitivity: "base" })),
          8
        ),
      },
    ],
    columns,
    rows: pagedRows,
    totalRows: sortedRows.length,
    page: request.page,
    pageSize: request.pageSize,
  };
}

function isCustomUniverse(value: string | null | undefined): value is ReportCustomUniverse {
  return (
    value === "sales" ||
    value === "products" ||
    value === "customers" ||
    value === "financialCash" ||
    value === "financialReceivables"
  );
}

function inferCustomUniverse(config: Partial<ReportCustomConfig> | null | undefined): ReportCustomUniverse {
  const explicitUniverse = typeof config?.universe === "string" ? config.universe : null;
  if (isCustomUniverse(explicitUniverse)) {
    return explicitUniverse;
  }

  const scores = new Map<ReportCustomUniverse, number>([
    ["sales", 0],
    ["products", 0],
    ["customers", 0],
    ["financialCash", 0],
    ["financialReceivables", 0],
  ]);
  const identifiers = [
    ...(Array.isArray(config?.columns) ? config.columns : []),
    ...(Array.isArray(config?.metrics) ? config.metrics : []),
    typeof config?.groupBy === "string" ? config.groupBy : null,
  ].filter((item): item is string => typeof item === "string" && item.length > 0);

  for (const identifier of identifiers) {
    const field = customFields.find((item) => item.id === identifier);
    if (field?.universes?.length) {
      for (const universe of field.universes) {
        scores.set(universe, (scores.get(universe) || 0) + 1);
      }
    }

    const metric = customMetrics.find((item) => item.id === identifier);
    if (metric?.universes?.length) {
      for (const universe of metric.universes) {
        scores.set(universe, (scores.get(universe) || 0) + 2);
      }
    }
  }

  return Array.from(scores.entries()).sort((left, right) => right[1] - left[1])[0]?.[0] || "sales";
}

function getCustomFieldsForUniverse(universe: ReportCustomUniverse) {
  return customFields.filter((field) => field.universes?.includes(universe));
}

function getCustomMetricsForUniverse(universe: ReportCustomUniverse) {
  return customMetrics.filter((metric) => metric.universes.includes(universe));
}

function aggregateCustomRows(
  rows: ReportTableRow[],
  groupBy: string | null,
  fieldMap: Map<string, ReportCustomField>
) {
  if (!groupBy) return rows;
  const grouped = new Map<string, ReportTableRow>();
  for (const row of rows) {
    const key = String(row[groupBy] || "Sem valor");
    const current =
      grouped.get(key) ??
      Object.fromEntries(
        Object.keys(row).map((columnKey) => [columnKey, columnKey === groupBy ? key : 0])
      );
    for (const [columnId, value] of Object.entries(row)) {
      if (columnId === groupBy) continue;
      const field = fieldMap.get(columnId);
      if (typeof value === "number" && (field?.type === "number" || field?.type === "currency")) {
        current[columnId] = toNumber(current[columnId]) + value;
      } else if (current[columnId] === 0) {
        current[columnId] = value;
      }
    }
    grouped.set(key, current);
  }
  return Array.from(grouped.values());
}

async function buildCustomReport(runtime: ReportRuntimeContext, request: ReportRequest): Promise<ReportResponse> {
  const requestedConfig = request.custom ?? null;
  const universe = inferCustomUniverse(requestedConfig);
  const effectiveFilters = { ...request.filters, ...(requestedConfig?.filters ?? {}) };
  const config: ReportCustomConfig = {
    universe,
    columns:
      requestedConfig?.columns?.filter((columnId) => getCustomFieldsForUniverse(universe).some((field) => field.id === columnId)) ||
      CUSTOM_UNIVERSE_DEFAULT_COLUMNS[universe],
    metrics:
      requestedConfig?.metrics?.filter((metricId) => getCustomMetricsForUniverse(universe).some((metric) => metric.id === metricId)) ||
      CUSTOM_UNIVERSE_DEFAULT_METRICS[universe],
    filters: effectiveFilters,
    groupBy:
      requestedConfig?.groupBy && getCustomFieldsForUniverse(universe).some((field) => field.id === requestedConfig.groupBy)
        ? requestedConfig.groupBy
        : null,
    sort: requestedConfig?.sort ?? request.sort,
    name: requestedConfig?.name,
  };

  const sourceRequest: ReportRequest = {
    ...request,
    tab: universe === "financialCash" || universe === "financialReceivables" ? "financial" : universe,
    filters: effectiveFilters,
    page: 1,
    pageSize: 5000,
    search: "",
    sort: null,
    custom: null,
    exportAllRows: true,
  };

  const sourceReport =
    universe === "sales"
      ? await buildSalesReport(runtime, sourceRequest)
      : universe === "products"
        ? await buildProductsReport(runtime, sourceRequest)
        : universe === "customers"
          ? await buildCustomersReport(runtime, sourceRequest)
          : await buildFinancialReport(runtime, sourceRequest);

  const fieldMap = new Map(getCustomFieldsForUniverse(universe).map((field) => [field.id, field]));
  const selectedColumnIds =
    config.columns.length > 0 ? config.columns : CUSTOM_UNIVERSE_DEFAULT_COLUMNS[universe];
  const tableColumns = selectedColumnIds
    .map<ReportTableColumn | null>((fieldId) => {
      const field = fieldMap.get(fieldId);
      if (!field) return null;
      return {
        id: fieldId,
        label: field.label,
        type: field.type,
        sortable: true,
        width: field.type === "text" ? 180 : 130,
        visibleByDefault: true,
      };
    })
    .filter((column): column is ReportTableColumn => Boolean(column));

  const detailedRows: ReportTableRow[] =
    universe === "sales"
      ? sourceReport.rows.map((row) => ({
          date: row.orderDate ?? null,
          client: row.clientName ?? null,
          seller: row.sellerName ?? null,
          status: row.orderStatus ?? null,
          paymentMethod: row.paymentMethod ?? null,
          value: toNumber(row.orderTotal),
        }))
      : universe === "products"
        ? sourceReport.rows.map((row) => ({
            product: row.productName ?? null,
            category: row.categoryName ?? null,
            quantity: toNumber(row.quantity),
            value: toNumber(row.revenue),
            profit: toNumber(row.profit),
            saleStatus: row.saleStatus ?? null,
          }))
        : universe === "customers"
          ? sourceReport.rows.map((row) => ({
            client: row.clientName ?? null,
            city: row.city ?? null,
            state: row.state ?? null,
            newClient: row.newClient ?? null,
            activityStatus: row.activityStatus ?? null,
            recurrenceStatus: row.recurrenceStatus ?? null,
            orderCount: toNumber(row.orderCount),
            value: toNumber(row.totalSpent),
          }))
          : sourceReport.rows
              .filter((row) =>
                universe === "financialCash"
                  ? row.recordType === "Recebimento" || row.recordType === "Fluxo de caixa"
                  : row.recordType === "Carteira"
              )
              .map((row) => ({
                date: row.date ?? null,
                client: row.clientName ?? null,
                description: row.description ?? null,
                recordType: row.recordType ?? null,
                origin: row.origin ?? null,
                channel: row.channel ?? null,
                bankAccount: row.bankAccount ?? null,
                financialCategory: row.category ?? null,
                paymentMethod: row.paymentMethod ?? null,
                status: row.status ?? null,
                type: row.type ?? null,
                value: toNumber(row.amount),
              }));

  const projectedRows = detailedRows.map((row) =>
    Object.fromEntries(selectedColumnIds.map((columnId) => [columnId, row[columnId] ?? null]))
  );
  const groupedRows = aggregateCustomRows(projectedRows, config.groupBy, fieldMap);
  const searchedRows = groupedRows.filter((row) => matchesTextSearch(row, request.search));
  const sortedRows = sortRows(searchedRows, tableColumns, config.sort || request.sort);
  const pagedRows = request.exportAllRows ? sortedRows : paginateRows(sortedRows, request.page, request.pageSize);
  const selectedMetricIds =
    config.metrics.length > 0 ? config.metrics : CUSTOM_UNIVERSE_DEFAULT_METRICS[universe];
  const kpis = sourceReport.kpis.filter((kpi) => selectedMetricIds.includes(kpi.id));
  const defaultLabelField =
    config.groupBy ||
    (
      [
        "client",
        "product",
        "category",
        "seller",
        "city",
        "state",
        "status",
        "saleStatus",
        "activityStatus",
        "origin",
        "financialCategory",
        "recordType",
        "paymentMethod",
      ] as const
    ).find((fieldId) =>
      selectedColumnIds.includes(fieldId)
    ) ||
    (universe === "sales"
      ? "client"
      : universe === "products"
        ? "product"
        : universe === "financialCash" || universe === "financialReceivables"
          ? "recordType"
          : "client");
  const defaultMetricField =
    (["value", "quantity", "profit", "orderCount"] as const).find((fieldId) => selectedColumnIds.includes(fieldId)) ||
    (universe === "products" ? "quantity" : "value");
  const labelField = fieldMap.has(defaultLabelField) ? defaultLabelField : null;
  const metricField = fieldMap.has(defaultMetricField) ? defaultMetricField : null;
  const metricType = metricField ? fieldMap.get(metricField)?.type ?? "number" : "number";
  const chartFieldLabel = labelField ? fieldMap.get(labelField)?.label || labelField : "Dimensão";
  const metricFieldLabel = metricField ? fieldMap.get(metricField)?.label || metricField : "Valor";
  const chartSeries =
    labelField && metricField && fieldMap.get(labelField)?.type === "date"
      ? buildChronologicalSeries(
          groupedRows.map((row) => ({
            date: typeof row[labelField] === "string" ? String(row[labelField]) : null,
            value: toNumber(row[metricField]),
          }))
        )
      : labelField && metricField
        ? takeTopSeries(
            groupedRows
              .map((row) => ({
                label: String(row[labelField] || "Sem valor"),
                value: toNumber(row[metricField]),
              }))
              .sort((left, right) => right.value - left.value || left.label.localeCompare(right.label, "pt-BR", { sensitivity: "base" })),
            8
          )
        : [];
  const chartType = labelField && fieldMap.get(labelField)?.type === "date" ? "column" : "ranking";
  const chartFormat = metricType === "currency" ? "currency" : metricType === "number" ? "number" : "percent";
  const universeTitle =
    universe === "sales"
      ? "Vendas"
      : universe === "products"
        ? "Produtos"
        : universe === "customers"
          ? "Clientes"
          : universe === "financialCash"
            ? "Financeiro Caixa"
            : "Financeiro Carteira";

  return {
    tab: "custom",
    title: config.name?.trim() || `Relatórios Personalizados - ${universeTitle}`,
    generatedAt: new Date().toISOString(),
    company: runtime.company,
    appliedFilters: effectiveFilters,
    kpis,
    charts: [
      {
        id: "custom-distribution",
        title: `${metricFieldLabel} por ${chartFieldLabel}`,
        type: chartType,
        format: chartFormat,
        series: chartSeries,
      },
    ],
    columns: tableColumns,
    rows: pagedRows,
    totalRows: sortedRows.length,
    page: request.page,
    pageSize: request.pageSize,
  };
}

export async function runReport(runtime: ReportRuntimeContext, request: ReportRequest) {
  if (request.tab === "financial") return buildFinancialReport(runtime, request);
  if (request.tab === "sales") return buildSalesReport(runtime, request);
  if (request.tab === "products") return buildProductsReport(runtime, request);
  if (request.tab === "customers") return buildCustomersReport(runtime, request);
  return buildCustomReport(runtime, request);
}

export function getReportPrintSummary(report: ReportResponse) {
  return {
    indicators: report.kpis.map((item) => ({
      label: item.label,
      value: formatValueByType(item.value, item.type === "currency" ? "currency" : item.type === "percent" ? "percent" : "number"),
    })),
    rows: report.rows,
  };
}