import { NextResponse } from "next/server";
import { canCompany, getCompanyContext } from "@/lib/company-context";
import { createClient } from "@/lib/supabase-server";

const DAY_MS = 24 * 60 * 60 * 1000;

type CatalogOrderItemRow = {
  product_id: string | null;
  quantity: number | null;
  total_price: number | null;
};

type CatalogOrderRow = {
  id: string;
  status: "pending" | "approved" | "canceled";
  total: number | null;
  created_at: string;
  catalog_order_items?: CatalogOrderItemRow[] | null;
};

type CatalogEventRow = {
  event_name: string;
  session_id: string | null;
  product_id: string | null;
  category_id: string | null;
  search_term: string | null;
  metadata: unknown;
  created_at: string;
};

type ProductRow = {
  id: string;
  name: string;
  image_url: string | null;
  category_id: string | null;
  description: string | null;
  show_in_catalog: boolean | null;
  stock: number | null;
  track_inventory: boolean | null;
  active: boolean | null;
};

type CategoryRow = {
  id: string;
  name: string;
};

function toNumber(value: unknown) {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : 0;
}

function normalizeEventName(value: unknown) {
  return String(value || "").trim().toLowerCase();
}

function parsePeriodDays(raw: string | null) {
  const parsed = Number(raw || "7");
  if (parsed === 30) return 30;
  if (parsed === 90) return 90;
  return 7;
}

function dayKeyFromIso(iso: string) {
  const date = new Date(iso);
  if (Number.isNaN(date.getTime())) return "";
  return `${date.getUTCFullYear()}-${String(date.getUTCMonth() + 1).padStart(2, "0")}-${String(date.getUTCDate()).padStart(2, "0")}`;
}

function parseMetadataObject(input: unknown) {
  if (input && typeof input === "object" && !Array.isArray(input)) {
    return input as Record<string, unknown>;
  }
  return {};
}

export async function GET(req: Request) {
  const supabase = await createClient();
  const {
    data: { user },
  } = await supabase.auth.getUser();

  if (!user) {
    return NextResponse.json({ error: "Nao autenticado." }, { status: 401 });
  }

  const ctx = await getCompanyContext(supabase, user);
  if (!canCompany(ctx, user.id, "catalog.read")) {
    return NextResponse.json({ error: "Sem permissao para visualizar dados do catalogo." }, { status: 403 });
  }

  const tenantIds = (ctx.tenantIds?.length ? ctx.tenantIds : [user.id]).filter(
    (id): id is string => typeof id === "string" && id.trim().length > 0
  );

  if (!tenantIds.length) {
    return NextResponse.json({ error: "Nenhum tenant disponivel." }, { status: 400 });
  }

  const url = new URL(req.url);
  const periodDays = parsePeriodDays(url.searchParams.get("period"));
  const startDate = new Date(Date.now() - (periodDays - 1) * DAY_MS);
  startDate.setHours(0, 0, 0, 0);
  const startIso = startDate.toISOString();

  const [ordersResult, productsResult, categoriesResult, eventsResult] = await Promise.all([
    supabase
      .from("catalog_orders")
      .select("id,status,total,created_at,catalog_order_items(product_id,quantity,total_price)")
      .in("user_id", tenantIds)
      .gte("created_at", startIso)
      .order("created_at", { ascending: true }),
    supabase
      .from("products")
      .select("id,name,image_url,category_id,description,show_in_catalog,stock,track_inventory,active")
      .in("user_id", tenantIds),
    supabase
      .from("categories")
      .select("id,name")
      .in("user_id", tenantIds),
    supabase
      .from("catalog_events")
      .select("event_name,session_id,product_id,category_id,search_term,metadata,created_at")
      .in("user_id", tenantIds)
      .gte("created_at", startIso)
      .order("created_at", { ascending: true }),
  ]);

  if (ordersResult.error) {
    return NextResponse.json({ error: ordersResult.error.message }, { status: 400 });
  }
  if (productsResult.error) {
    return NextResponse.json({ error: productsResult.error.message }, { status: 400 });
  }
  if (categoriesResult.error) {
    return NextResponse.json({ error: categoriesResult.error.message }, { status: 400 });
  }

  const eventsMissing = Boolean(
    eventsResult.error && /catalog_events|does not exist|not find the table/i.test(String(eventsResult.error.message || ""))
  );
  if (eventsResult.error && !eventsMissing) {
    return NextResponse.json({ error: eventsResult.error.message }, { status: 400 });
  }

  const orders = ((ordersResult.data || []) as CatalogOrderRow[]).filter(Boolean);
  const events = eventsMissing ? [] : ((eventsResult.data || []) as CatalogEventRow[]);
  const products = ((productsResult.data || []) as ProductRow[]).filter(Boolean);
  const categories = ((categoriesResult.data || []) as CategoryRow[]).filter(Boolean);

  const productById = new Map(products.map((product) => [product.id, product]));
  const categoryById = new Map(categories.map((category) => [category.id, category]));

  const pageViews = events.filter((event) => {
    const eventName = normalizeEventName(event.event_name);
    return eventName === "page_view" || eventName === "product_view";
  });
  const productClicks = events.filter((event) => normalizeEventName(event.event_name) === "product_click");
  const addToCartEvents = events.filter((event) => normalizeEventName(event.event_name) === "add_to_cart");
  const checkoutStartedEvents = events.filter((event) => normalizeEventName(event.event_name) === "checkout_started");
  const orderCreatedEvents = events.filter((event) => normalizeEventName(event.event_name) === "order_created");
  const searchEvents = events.filter((event) => normalizeEventName(event.event_name) === "search");

  const sessionIds = new Set(
    events
      .map((event) => String(event.session_id || "").trim())
      .filter((sessionId) => Boolean(sessionId))
  );

  const approvedOrders = orders.filter((order) => order.status === "approved");
  const pendingOrders = orders.filter((order) => order.status === "pending");
  const validOrders = orders.filter((order) => order.status !== "canceled");

  const revenue = approvedOrders.reduce((sum, order) => sum + toNumber(order.total), 0);
  const averageOrderValue = approvedOrders.length > 0 ? revenue / approvedOrders.length : 0;

  const addToCartCount = Math.max(addToCartEvents.length, checkoutStartedEvents.length, orderCreatedEvents.length);
  const checkoutStartedCount = Math.max(checkoutStartedEvents.length, orderCreatedEvents.length);
  const orderCount = validOrders.length;
  const sessionCount = sessionIds.size;
  const pageViewCount = Math.max(pageViews.length, sessionCount);

  const conversionRate = sessionCount > 0 ? (orderCount / sessionCount) * 100 : 0;
  const addToCartRate = pageViewCount > 0 ? (addToCartCount / pageViewCount) * 100 : 0;
  const checkoutRate = addToCartCount > 0 ? (checkoutStartedCount / addToCartCount) * 100 : 0;

  const catalogProducts = products.filter((product) => product.show_in_catalog !== false && product.active !== false);
  const outOfStockCount = catalogProducts.filter(
    (product) => product.track_inventory !== false && toNumber(product.stock) <= 0
  ).length;
  const withoutImageCount = catalogProducts.filter((product) => !String(product.image_url || "").trim()).length;
  const withoutDescriptionCount = catalogProducts.filter((product) => !String(product.description || "").trim()).length;
  const withoutCategoryCount = catalogProducts.filter((product) => !String(product.category_id || "").trim()).length;

  const productStats = new Map<
    string,
    {
      product_id: string;
      product_name: string;
      image_url: string | null;
      add_to_cart: number;
      orders_count: number;
      quantity_sold: number;
      revenue: number;
    }
  >();

  for (const event of addToCartEvents) {
    const productId = String(event.product_id || "").trim();
    if (!productId) continue;
    const product = productById.get(productId);
    const current =
      productStats.get(productId) ||
      {
        product_id: productId,
        product_name: product?.name || "Produto removido",
        image_url: product?.image_url || null,
        add_to_cart: 0,
        orders_count: 0,
        quantity_sold: 0,
        revenue: 0,
      };
    current.add_to_cart += 1;
    productStats.set(productId, current);
  }

  const productOrderIds = new Map<string, Set<string>>();
  for (const order of validOrders) {
    const orderItems = Array.isArray(order.catalog_order_items) ? order.catalog_order_items : [];
    for (const item of orderItems) {
      const productId = String(item.product_id || "").trim();
      if (!productId) continue;
      const product = productById.get(productId);
      const current =
        productStats.get(productId) ||
        {
          product_id: productId,
          product_name: product?.name || "Produto removido",
          image_url: product?.image_url || null,
          add_to_cart: 0,
          orders_count: 0,
          quantity_sold: 0,
          revenue: 0,
        };
      current.quantity_sold += toNumber(item.quantity);
      if (order.status === "approved") {
        current.revenue += toNumber(item.total_price);
      }
      productStats.set(productId, current);

      const set = productOrderIds.get(productId) || new Set<string>();
      set.add(order.id);
      productOrderIds.set(productId, set);
    }
  }

  for (const [productId, orderIds] of productOrderIds.entries()) {
    const current = productStats.get(productId);
    if (!current) continue;
    current.orders_count = orderIds.size;
    productStats.set(productId, current);
  }

  const topProducts = Array.from(productStats.values())
    .map((item) => ({
      ...item,
      conversion_rate: item.add_to_cart > 0 ? (item.orders_count / item.add_to_cart) * 100 : 0,
    }))
    .sort((a, b) => {
      if (b.revenue !== a.revenue) return b.revenue - a.revenue;
      if (b.orders_count !== a.orders_count) return b.orders_count - a.orders_count;
      return b.add_to_cart - a.add_to_cart;
    })
    .slice(0, 8);

  const categoryStats = new Map<
    string,
    {
      category_id: string;
      category_name: string;
      product_clicks: number;
      orders_count: number;
      quantity_sold: number;
      revenue: number;
    }
  >();

  const ensureCategory = (categoryId: string) => {
    const category = categoryById.get(categoryId);
    return (
      categoryStats.get(categoryId) ||
      {
        category_id: categoryId,
        category_name: category?.name || "Sem categoria",
        product_clicks: 0,
        orders_count: 0,
        quantity_sold: 0,
        revenue: 0,
      }
    );
  };

  const categoryOrderIds = new Map<string, Set<string>>();

  for (const event of productClicks) {
    const eventCategoryId = String(event.category_id || "").trim();
    const fromProduct = !eventCategoryId
      ? productById.get(String(event.product_id || "").trim())?.category_id || ""
      : eventCategoryId;
    const categoryId = String(fromProduct || "").trim();
    if (!categoryId) continue;
    const current = ensureCategory(categoryId);
    current.product_clicks += 1;
    categoryStats.set(categoryId, current);
  }

  for (const order of validOrders) {
    const orderItems = Array.isArray(order.catalog_order_items) ? order.catalog_order_items : [];
    for (const item of orderItems) {
      const productId = String(item.product_id || "").trim();
      if (!productId) continue;
      const categoryId = String(productById.get(productId)?.category_id || "").trim();
      if (!categoryId) continue;
      const current = ensureCategory(categoryId);
      current.quantity_sold += toNumber(item.quantity);
      if (order.status === "approved") {
        current.revenue += toNumber(item.total_price);
      }
      categoryStats.set(categoryId, current);

      const set = categoryOrderIds.get(categoryId) || new Set<string>();
      set.add(order.id);
      categoryOrderIds.set(categoryId, set);
    }
  }

  for (const [categoryId, orderIds] of categoryOrderIds.entries()) {
    const current = categoryStats.get(categoryId);
    if (!current) continue;
    current.orders_count = orderIds.size;
    categoryStats.set(categoryId, current);
  }

  const topCategories = Array.from(categoryStats.values())
    .sort((a, b) => {
      if (b.revenue !== a.revenue) return b.revenue - a.revenue;
      if (b.orders_count !== a.orders_count) return b.orders_count - a.orders_count;
      return b.product_clicks - a.product_clicks;
    })
    .slice(0, 8);

  const searchStats = new Map<string, { term: string; count: number; avg_results: number; zero_results_count: number }>();
  for (const event of searchEvents) {
    const normalizedTerm = String(event.search_term || "").trim().toLowerCase();
    if (!normalizedTerm) continue;
    const metadata = parseMetadataObject(event.metadata);
    const resultCount = toNumber(metadata.results_count);
    const current = searchStats.get(normalizedTerm) || {
      term: normalizedTerm,
      count: 0,
      avg_results: 0,
      zero_results_count: 0,
    };
    const nextCount = current.count + 1;
    current.avg_results = ((current.avg_results * current.count) + resultCount) / nextCount;
    current.count = nextCount;
    if (resultCount <= 0) current.zero_results_count += 1;
    searchStats.set(normalizedTerm, current);
  }

  const topSearches = Array.from(searchStats.values())
    .sort((a, b) => b.count - a.count)
    .slice(0, 8);

  const rangeDays: string[] = [];
  const cursor = new Date(startDate);
  const today = new Date();
  today.setHours(23, 59, 59, 999);
  while (cursor <= today) {
    rangeDays.push(`${cursor.getUTCFullYear()}-${String(cursor.getUTCMonth() + 1).padStart(2, "0")}-${String(cursor.getUTCDate()).padStart(2, "0")}`);
    cursor.setUTCDate(cursor.getUTCDate() + 1);
  }

  const timeseriesMap = new Map<string, { sessions: Set<string>; page_views: number; orders: number; revenue: number }>();
  for (const dayKey of rangeDays) {
    timeseriesMap.set(dayKey, { sessions: new Set<string>(), page_views: 0, orders: 0, revenue: 0 });
  }

  for (const event of events) {
    const dayKey = dayKeyFromIso(event.created_at);
    const entry = timeseriesMap.get(dayKey);
    if (!entry) continue;
    const sessionId = String(event.session_id || "").trim();
    if (sessionId) entry.sessions.add(sessionId);
    const eventName = normalizeEventName(event.event_name);
    if (eventName === "page_view" || eventName === "product_view") {
      entry.page_views += 1;
    }
    timeseriesMap.set(dayKey, entry);
  }

  for (const order of validOrders) {
    const dayKey = dayKeyFromIso(order.created_at);
    const entry = timeseriesMap.get(dayKey);
    if (!entry) continue;
    entry.orders += 1;
    if (order.status === "approved") {
      entry.revenue += toNumber(order.total);
    }
    timeseriesMap.set(dayKey, entry);
  }

  const timeseries = rangeDays.map((dayKey) => {
    const entry = timeseriesMap.get(dayKey) || { sessions: new Set<string>(), page_views: 0, orders: 0, revenue: 0 };
    const [year, month, day] = dayKey.split("-");
    return {
      day: dayKey,
      label: `${day}/${month}`,
      sessions: entry.sessions.size,
      page_views: entry.page_views,
      orders: entry.orders,
      revenue: entry.revenue,
      year,
    };
  });

  const alerts: Array<{
    id: string;
    severity: "high" | "medium" | "low";
    title: string;
    description: string;
    action_href: string;
    action_label: string;
  }> = [];

  if (outOfStockCount > 0) {
    alerts.push({
      id: "stock-risk",
      severity: "high",
      title: "Produtos sem estoque no catalogo",
      description: `${outOfStockCount} produto(s) visivel(is) estao sem estoque e podem reduzir conversao.`,
      action_href: "/dashboard/catalog/products",
      action_label: "Ajustar produtos",
    });
  }

  if (sessionCount >= 20 && orderCount === 0) {
    alerts.push({
      id: "no-orders",
      severity: "high",
      title: "Trafego sem pedidos",
      description: `Foram registradas ${sessionCount} sessoes e nenhum pedido no periodo selecionado.`,
      action_href: "/dashboard/catalog/marketing",
      action_label: "Revisar oferta",
    });
  }

  if (addToCartCount > 0 && checkoutStartedCount === 0) {
    alerts.push({
      id: "checkout-drop",
      severity: "medium",
      title: "Abandono antes do checkout",
      description: `${addToCartCount} adicao(oes) ao carrinho sem inicio de checkout.`,
      action_href: "/dashboard/catalog/products",
      action_label: "Ajustar CTA",
    });
  }

  const zeroResultSearch = topSearches.find((search) => search.zero_results_count >= 3);
  if (zeroResultSearch) {
    alerts.push({
      id: "search-gap",
      severity: "medium",
      title: "Busca sem resultado recorrente",
      description: `O termo "${zeroResultSearch.term}" teve ${zeroResultSearch.zero_results_count} busca(s) sem resultado.`,
      action_href: "/dashboard/catalog/products",
      action_label: "Criar produto",
    });
  }

  const weakConversionProduct = topProducts.find(
    (product) => product.add_to_cart >= 5 && product.orders_count === 0
  );
  if (weakConversionProduct) {
    alerts.push({
      id: "product-conversion-gap",
      severity: "low",
      title: "Produto com interesse e sem venda",
      description: `${weakConversionProduct.product_name} recebeu ${weakConversionProduct.add_to_cart} adicoes ao carrinho sem pedidos.`,
      action_href: "/dashboard/catalog/products",
      action_label: "Revisar produto",
    });
  }

  const quickActions = [
    {
      id: "go-products",
      label: "Gerenciar produtos do catalogo",
      description: "Atualize preco, estoque e destaque de itens em um unico lugar.",
      href: "/dashboard/catalog/products",
    },
    {
      id: "go-orders",
      label: "Revisar pedidos pendentes",
      description: "Aprove rapidamente os pedidos que chegaram do checkout publico.",
      href: "/dashboard/catalog/orders",
    },
    {
      id: "go-marketing",
      label: "Ajustar campanha e cupons",
      description: "Otimize conversao com anuncios, faixas e cupons.",
      href: "/dashboard/catalog/marketing",
    },
  ];

  const response = {
    period_days: periodDays,
    generated_at: new Date().toISOString(),
    diagnostics: {
      events_enabled: !eventsMissing,
      events_count: events.length,
      tenants_count: tenantIds.length,
    },
    summary: {
      sessions: sessionCount,
      page_views: pageViewCount,
      add_to_cart: addToCartCount,
      checkout_started: checkoutStartedCount,
      orders: orderCount,
      approved_orders: approvedOrders.length,
      pending_orders: pendingOrders.length,
      revenue,
      average_order_value: averageOrderValue,
      conversion_rate: conversionRate,
      add_to_cart_rate: addToCartRate,
      checkout_rate: checkoutRate,
      catalog_products: catalogProducts.length,
      out_of_stock_products: outOfStockCount,
    },
    quality: {
      without_image: withoutImageCount,
      without_description: withoutDescriptionCount,
      without_category: withoutCategoryCount,
      out_of_stock: outOfStockCount,
    },
    funnel: [
      { id: "sessions", label: "Sessoes", value: sessionCount },
      { id: "product_click", label: "Cliques em produto", value: productClicks.length },
      { id: "add_to_cart", label: "Adicoes ao carrinho", value: addToCartCount },
      { id: "checkout_started", label: "Checkout iniciado", value: checkoutStartedCount },
      { id: "orders", label: "Pedidos", value: orderCount },
    ],
    top_products: topProducts,
    top_categories: topCategories,
    top_searches: topSearches,
    alerts,
    quick_actions: quickActions,
    timeseries,
  };

  return NextResponse.json(response);
}