import { query } from "../../db/pool.js";
import { SELECT_WITH_COMPUTED, type ProductRow } from "../products/products.service.js";

export interface DashboardRankingItem {
  label: string;
  count: number;
  total_spent?: number;
}

export interface DashboardCategoryItem {
  label: string;
  count: number;
  claimed_count: number;
}

export interface DashboardAttentionItem {
  label: string;
  average_rating: number;
  count: number;
}

export interface DashboardAttentionFeedbackItem {
  id: number;
  incident_number: string;
  title: string;
  store_label: string;
  brand_label: string;
  rating: number;
  notes: string;
  updated_at: string;
}

export interface ActiveIncidentItem {
  id: number;
  incident_number: string;
  title: string;
  status: string;
  product_name: string;
  product_brand: string;
  product_model: string;
  product_days_remaining: number;
  updated_at: string;
}

export interface ConsumerDashboardSummary {
  warranty: {
    total: number;
    active: number;
    warning: number;
    critical: number;
    expired: number;
    expiringSoon: ProductRow[];
  };
  claims: {
    total: number;
    active: number;
    resolved: number;
    closed: number;
    claimedProducts: number;
    claimRate: number;
    avgRating: number | null;
    topBrands: DashboardRankingItem[];
    topProducts: DashboardRankingItem[];
  };
  incidents: {
    active: number;
    items: ActiveIncidentItem[];
  };
  attention: {
    avgRating: number | null;
    ratedIncidents: number;
    topStores: DashboardAttentionItem[];
    topBrands: DashboardAttentionItem[];
    recentFeedback: DashboardAttentionFeedbackItem[];
    publicAvgRating: number | null;
    publicRatedIncidents: number;
    publicTopStores: DashboardAttentionItem[];
    publicTopBrands: DashboardAttentionItem[];
    publicFeedback: DashboardAttentionFeedbackItem[];
  };
  stores: {
    totalStores: number;
    topStores: DashboardRankingItem[];
  };
  consumer: {
    totalSpent: number;
    avgPurchasePrice: number | null;
    productsWithoutReceipt: number;
    extendedCoverage: number;
    insuranceCoverage: number;
    coverageMix: DashboardRankingItem[];
    categoryComparison: DashboardCategoryItem[];
    documentsTotal: number;
    documentsWithTracking: number;
  };
}

export function csvCell(value: unknown): string {
  const text = value === null || value === undefined ? "" : String(value);
  return `"${text.replace(/"/g, '""')}"`;
}

function toNumber(value: string | number | null | undefined): number {
  if (value === null || value === undefined) return 0;
  return Number(value);
}

function mapRanking(rows: Array<{ label: string; count: string | number; total_spent?: string | number }>): DashboardRankingItem[] {
  return rows.map((row) => ({
    label: row.label,
    count: toNumber(row.count),
    ...(row.total_spent === undefined ? {} : { total_spent: toNumber(row.total_spent) }),
  }));
}

function mapAttentionRanking(rows: Array<{ label: string; average_rating: string | number; count: string | number }>): DashboardAttentionItem[] {
  return rows.map((row) => ({
    label: row.label,
    average_rating: Number(row.average_rating),
    count: toNumber(row.count),
  }));
}

const NORMALIZE_TRANSLATION = "áéíóúÁÉÍÓÚñÑ";
const NORMALIZE_REPLACEMENT = "aeiouAEIOUnN";

function normalizeSqlExpression(valueSql: string): string {
  return `BTRIM(LOWER(REGEXP_REPLACE(TRANSLATE(COALESCE(NULLIF(TRIM(${valueSql}), ''), ''), '${NORMALIZE_TRANSLATION}', '${NORMALIZE_REPLACEMENT}'), '[^a-zA-Z0-9]+', ' ', 'g')))`;
}

export async function getConsumerDashboardSummary(userId: number): Promise<ConsumerDashboardSummary> {
  const [
    warrantyRows,
    expiringSoon,
    claimRows,
    activeIncidentRows,
    topBrands,
    topProducts,
    totalStoreRows,
    topStores,
    documentRows,
    coverageRows,
    categoryRows,
    attentionSummaryRows,
    attentionStoreRows,
    attentionBrandRows,
    attentionFeedbackRows,
    publicAttentionSummaryRows,
    publicAttentionStoreRows,
    publicAttentionBrandRows,
    publicAttentionFeedbackRows,
  ] = await Promise.all([
    query<{
      total: string;
      active: string;
      warning: string;
      critical: string;
      expired: string;
      total_spent: string | null;
      avg_purchase_price: string | null;
      products_without_receipt: string;
      extended_coverage: string;
      insurance_coverage: string;
    }>(`
      SELECT
        COUNT(*)::int AS total,
        COUNT(*) FILTER (WHERE status = 'active')::int AS active,
        COUNT(*) FILTER (WHERE status = 'warning')::int AS warning,
        COUNT(*) FILTER (WHERE status = 'critical')::int AS critical,
        COUNT(*) FILTER (WHERE status = 'expired')::int AS expired,
        COALESCE(SUM(purchase_price), 0)::numeric AS total_spent,
        ROUND(AVG(purchase_price)::numeric, 2) AS avg_purchase_price,
        COUNT(*) FILTER (WHERE receipt_image IS NULL)::int AS products_without_receipt,
        COUNT(*) FILTER (WHERE has_extended_warranty)::int AS extended_coverage,
        COUNT(*) FILTER (WHERE has_insurance)::int AS insurance_coverage
      FROM (${SELECT_WITH_COMPUTED} WHERE p.user_id = $1) products
    `, [userId]),
    query<ProductRow>(`
      SELECT *
      FROM (${SELECT_WITH_COMPUTED} WHERE p.user_id = $1) products
      WHERE products.status IN ('warning', 'critical')
      ORDER BY products.effective_end_date ASC, products.created_at DESC
      LIMIT 8
    `, [userId]),
    query<{
      total: string;
      active: string;
      resolved: string;
      closed: string;
      claimed_products: string;
      avg_rating: string | null;
    }>(`
      SELECT
        COUNT(*)::int AS total,
        COUNT(*) FILTER (WHERE status IN ('open', 'sent', 'received', 'diagnosing', 'awaiting_parts'))::int AS active,
        COUNT(*) FILTER (WHERE status IN ('repaired', 'replaced'))::int AS resolved,
        COUNT(*) FILTER (WHERE status IN ('closed', 'rejected'))::int AS closed,
        COUNT(DISTINCT product_id)::int AS claimed_products,
        ROUND(AVG(user_rating)::numeric, 2) AS avg_rating
      FROM repair_incidents
      WHERE user_id = $1
    `, [userId]),
    query<{
      id: string;
      incident_number: string;
      title: string;
      status: string;
      product_name: string;
      product_brand: string;
      product_model: string;
      product_days_remaining: string;
      updated_at: string;
    }>(`
      SELECT
        i.id,
        i.incident_number,
        COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
        i.status,
        COALESCE(NULLIF(TRIM(p.name), ''), 'Producto sin nombre') AS product_name,
        COALESCE(NULLIF(TRIM(p.brand), ''), 'Sin marca') AS product_brand,
        COALESCE(NULLIF(TRIM(p.model), ''), 'Sin modelo') AS product_model,
        COALESCE(
          (
            GREATEST(
              p.warranty_end_date,
              CASE WHEN p.has_extended_warranty THEN p.extended_warranty_end_date ELSE NULL END,
              CASE WHEN p.has_insurance THEN p.insurance_end_date ELSE NULL END
            ) - CURRENT_DATE
          )::int,
          0
        ) AS product_days_remaining,
        i.updated_at
      FROM repair_incidents i
      LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
      WHERE i.user_id = $1
        AND i.status IN ('open', 'sent', 'received', 'diagnosing', 'awaiting_parts')
      ORDER BY
        CASE i.status
          WHEN 'open' THEN 0
          WHEN 'sent' THEN 1
          WHEN 'received' THEN 2
          WHEN 'diagnosing' THEN 3
          WHEN 'awaiting_parts' THEN 4
          ELSE 5
        END,
        i.updated_at DESC,
        i.id DESC
      LIMIT 8
    `, [userId]),
    query<{ label: string; count: string }>(`
      WITH items AS (
        SELECT
          COALESCE(
            NULLIF(TRIM(pl.brand_raw), ''),
            NULLIF(TRIM(p.brand), ''),
            'Sin marca'
          ) AS raw_label,
          BTRIM(LOWER(REGEXP_REPLACE(TRANSLATE(COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), ''), 'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'), '[^a-zA-Z0-9]+', ' ', 'g'))) AS normalized_label
        FROM repair_incidents i
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        WHERE i.user_id = $1
      )
      SELECT COALESCE(e.display_name, alias_entry.display_name, items.raw_label) AS label, COUNT(*)::int AS count
      FROM items
      LEFT JOIN canonical_catalog_entries e ON e.kind = 'brand' AND e.normalized_name = items.normalized_label
      LEFT JOIN canonical_catalog_aliases a ON a.normalized_alias = items.normalized_label
      LEFT JOIN canonical_catalog_entries alias_entry ON alias_entry.id = a.entry_id AND alias_entry.kind = 'brand'
      GROUP BY label
      ORDER BY count DESC, label ASC
      LIMIT 50
    `, [userId]),
    query<{ label: string; count: string }>(`
      WITH items AS (
        SELECT
          TRIM(CONCAT(
            COALESCE(NULLIF(pl.name_raw, ''), NULLIF(p.name, ''), 'Producto sin nombre'),
            CASE WHEN NULLIF(TRIM(COALESCE(pl.brand_raw, p.brand)), '') IS NULL THEN '' ELSE CONCAT(' · ', TRIM(COALESCE(pl.brand_raw, p.brand))) END,
            CASE WHEN NULLIF(TRIM(COALESCE(pl.model_raw, p.model)), '') IS NULL THEN '' ELSE CONCAT(' ', TRIM(COALESCE(pl.model_raw, p.model))) END
          )) AS label
        FROM repair_incidents i
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        WHERE i.user_id = $1
      )
      SELECT label, COUNT(*)::int AS count
      FROM items
      GROUP BY label
      ORDER BY count DESC, label ASC
      LIMIT 50
    `, [userId]),
    query<{ total_stores: string }>(`
      WITH store_sources AS (
        SELECT COALESCE(NULLIF(TRIM(vendor), ''), 'Sin tienda') AS raw_label
        FROM products
        WHERE user_id = $1
        UNION ALL
        SELECT COALESCE(NULLIF(TRIM(vendor_canonical), ''), 'Sin tienda') AS raw_label
        FROM purchase_documents
        WHERE user_id = $1
      )
      SELECT COUNT(DISTINCT raw_label)::int AS total_stores
      FROM store_sources
    `, [userId]),
    query<{ label: string; count: string; total_spent: string }>(`
      WITH items AS (
        SELECT
          COALESCE(NULLIF(TRIM(vendor), ''), 'Sin tienda') AS raw_label,
          BTRIM(LOWER(REGEXP_REPLACE(TRANSLATE(COALESCE(vendor, ''), 'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'), '[^a-zA-Z0-9]+', ' ', 'g'))) AS normalized_label,
          purchase_price
        FROM products
        WHERE user_id = $1
        UNION ALL
        SELECT
          COALESCE(NULLIF(TRIM(vendor_canonical), ''), 'Sin tienda') AS raw_label,
          BTRIM(LOWER(REGEXP_REPLACE(TRANSLATE(COALESCE(NULLIF(TRIM(vendor_canonical), ''), ''), 'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'), '[^a-zA-Z0-9]+', ' ', 'g'))) AS normalized_label,
          total_amount AS purchase_price
        FROM purchase_documents
        WHERE user_id = $1
      )
      SELECT
        COALESCE(e.display_name, alias_entry.display_name, items.raw_label) AS label,
        COUNT(*)::int AS count,
        COALESCE(SUM(items.purchase_price), 0)::numeric AS total_spent
      FROM items
      LEFT JOIN canonical_catalog_entries e
        ON e.kind = 'store' AND (
          e.normalized_name = items.normalized_label
          OR items.normalized_label LIKE e.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases a
        ON a.normalized_alias = items.normalized_label
      LEFT JOIN canonical_catalog_entries alias_entry
        ON alias_entry.id = a.entry_id AND alias_entry.kind = 'store'
      GROUP BY label
      ORDER BY total_spent DESC, count DESC, label ASC
      LIMIT 50
    `, [userId]),
    query<{ total_documents: string; documents_with_tracking: string }>(`
      SELECT
        COUNT(*)::int AS total_documents,
        COUNT(*) FILTER (
          WHERE COALESCE(
            NULLIF(TRIM(metadata->>'trackingUrl'), ''),
            NULLIF(TRIM(metadata->>'trackingLabel'), ''),
            NULLIF(TRIM(metadata->>'trackingText'), ''),
            NULLIF(TRIM(metadata->>'qrUrl'), ''),
            NULLIF(TRIM(metadata->>'qrLabel'), ''),
            NULLIF(TRIM(metadata->>'qrText'), '')
          ) IS NOT NULL
        )::int AS documents_with_tracking
      FROM purchase_documents
      WHERE user_id = $1
    `, [userId]),
    query<{ label: string; count: string }>(`
      SELECT
        CASE effective_type
          WHEN 'insurance' THEN 'Seguro'
          WHEN 'extended' THEN 'Garantía ampliada'
          ELSE 'Garantía legal'
        END AS label,
        COUNT(*)::int AS count
      FROM (${SELECT_WITH_COMPUTED} WHERE p.user_id = $1) products
      GROUP BY label
      ORDER BY count DESC, label ASC
    `, [userId]),
    query<{ label: string; count: string; claimed_count: string }>(`
      WITH product_items AS (
        SELECT
          CASE
            WHEN NULLIF(TRIM(p.category), '') IS NULL THEN 'Sin categoría'
            WHEN p.category = 'electronics' THEN 'Electrónica'
            WHEN p.category = 'mobile' THEN 'Móvil'
            WHEN p.category = 'appliance' THEN 'Electrodoméstico'
            WHEN p.category = 'computer' THEN 'Informática'
            WHEN p.category = 'clothing' THEN 'Ropa'
            WHEN p.category = 'vehicle' THEN 'Vehículo'
            WHEN p.category = 'furniture' THEN 'Hogar'
            WHEN p.category = 'tool' THEN 'Herramienta'
            WHEN p.category = 'toy' THEN 'Juguete'
            WHEN p.category = 'digital' THEN 'Digital'
            WHEN p.category = 'secondhand' THEN 'Segunda mano'
            ELSE INITCAP(REPLACE(p.category, '_', ' '))
          END AS label,
          p.id
        FROM products p
        WHERE p.user_id = $1
      )
      SELECT
        product_items.label,
        COUNT(*)::int AS count,
        COUNT(DISTINCT i.id)::int AS claimed_count
      FROM product_items
      LEFT JOIN repair_incidents i ON i.product_id = product_items.id AND i.user_id = $1
      GROUP BY product_items.label
      ORDER BY count DESC, claimed_count DESC, label ASC
    `, [userId]),
    query<{ avg_rating: string | null; rated_incidents: string }>(`
      SELECT
        ROUND(AVG(user_rating)::numeric, 2) AS avg_rating,
        COUNT(*) FILTER (WHERE user_rating IS NOT NULL)::int AS rated_incidents
      FROM repair_incidents
      WHERE user_id = $1
    `, [userId]),
    query<{ label: string; average_rating: string; count: string }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.user_rating,
          i.user_rating_notes,
          i.updated_at,
          COALESCE(
            NULLIF(TRIM(pd.vendor_canonical), ''),
            NULLIF(TRIM(p.vendor), ''),
            'Sin tienda'
          ) AS raw_store,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pd.vendor_canonical), ''), NULLIF(TRIM(p.vendor), ''), 'Sin tienda')")} AS normalized_store
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
        WHERE i.user_id = $1
          AND i.user_rating IS NOT NULL
      )
      SELECT
        COALESCE(e.display_name, alias_entry.display_name, incident_context.raw_store) AS label,
        ROUND(AVG(incident_context.user_rating)::numeric, 2) AS average_rating,
        COUNT(*)::int AS count
      FROM incident_context
      LEFT JOIN canonical_catalog_entries e
        ON e.kind = 'store' AND (
          e.normalized_name = incident_context.normalized_store
          OR incident_context.normalized_store LIKE e.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases a
        ON a.normalized_alias = incident_context.normalized_store
      LEFT JOIN canonical_catalog_entries alias_entry
        ON alias_entry.id = a.entry_id AND alias_entry.kind = 'store'
      GROUP BY label
      ORDER BY average_rating DESC, count DESC, label ASC
      LIMIT 8
    `, [userId]),
    query<{ label: string; average_rating: string; count: string }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.user_rating,
          i.user_rating_notes,
          i.updated_at,
          COALESCE(
            NULLIF(TRIM(pl.brand_raw), ''),
            NULLIF(TRIM(p.brand), ''),
            'Sin marca'
          ) AS raw_brand,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), 'Sin marca')")} AS normalized_brand
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        WHERE i.user_id = $1
          AND i.user_rating IS NOT NULL
      )
      SELECT
        COALESCE(e.display_name, alias_entry.display_name, incident_context.raw_brand) AS label,
        ROUND(AVG(incident_context.user_rating)::numeric, 2) AS average_rating,
        COUNT(*)::int AS count
      FROM incident_context
      LEFT JOIN canonical_catalog_entries e
        ON e.kind = 'brand' AND (
          e.normalized_name = incident_context.normalized_brand
          OR incident_context.normalized_brand LIKE e.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases a
        ON a.normalized_alias = incident_context.normalized_brand
      LEFT JOIN canonical_catalog_entries alias_entry
        ON alias_entry.id = a.entry_id AND alias_entry.kind = 'brand'
      GROUP BY label
      ORDER BY average_rating DESC, count DESC, label ASC
      LIMIT 8
    `, [userId]),
    query<{
      id: string;
      incident_number: string;
      title: string;
      store_label: string;
      brand_label: string;
      rating: string;
      notes: string;
      updated_at: string;
    }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.incident_number,
          COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
          COALESCE(
            NULLIF(TRIM(pd.vendor_canonical), ''),
            NULLIF(TRIM(p.vendor), ''),
            'Sin tienda'
          ) AS raw_store,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pd.vendor_canonical), ''), NULLIF(TRIM(p.vendor), ''), 'Sin tienda')")} AS normalized_store,
          COALESCE(
            NULLIF(TRIM(pl.brand_raw), ''),
            NULLIF(TRIM(p.brand), ''),
            'Sin marca'
          ) AS raw_brand,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), 'Sin marca')")} AS normalized_brand,
          i.user_rating AS rating,
          COALESCE(NULLIF(TRIM(i.user_rating_notes), ''), NULL) AS notes,
          i.updated_at
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
        WHERE i.user_id = $1
          AND i.user_rating IS NOT NULL
      )
      SELECT
        incident_context.id,
        incident_context.incident_number,
        incident_context.title,
        COALESCE(store_entry.display_name, store_alias.display_name, incident_context.raw_store) AS store_label,
        COALESCE(brand_entry.display_name, brand_alias.display_name, incident_context.raw_brand) AS brand_label,
        incident_context.rating,
        incident_context.notes,
        incident_context.updated_at
      FROM incident_context
      LEFT JOIN canonical_catalog_entries store_entry
        ON store_entry.kind = 'store' AND (
          store_entry.normalized_name = incident_context.normalized_store
          OR incident_context.normalized_store LIKE store_entry.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases store_alias_map
        ON store_alias_map.normalized_alias = incident_context.normalized_store
      LEFT JOIN canonical_catalog_entries store_alias
        ON store_alias.id = store_alias_map.entry_id AND store_alias.kind = 'store'
      LEFT JOIN canonical_catalog_entries brand_entry
        ON brand_entry.kind = 'brand' AND (
          brand_entry.normalized_name = incident_context.normalized_brand
          OR incident_context.normalized_brand LIKE brand_entry.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases brand_alias_map
        ON brand_alias_map.normalized_alias = incident_context.normalized_brand
      LEFT JOIN canonical_catalog_entries brand_alias
        ON brand_alias.id = brand_alias_map.entry_id AND brand_alias.kind = 'brand'
      ORDER BY incident_context.updated_at DESC, incident_context.id DESC
      LIMIT 6
    `, [userId]),
    query<{ avg_rating: string | null; rated_incidents: string }>(`
      SELECT
        ROUND(AVG(user_rating)::numeric, 2) AS avg_rating,
        COUNT(*) FILTER (WHERE user_rating IS NOT NULL)::int AS rated_incidents
      FROM repair_incidents
      WHERE share_public_rating = TRUE
        AND user_rating IS NOT NULL
    `),
    query<{ label: string; average_rating: string; count: string }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.user_rating,
          COALESCE(
            NULLIF(TRIM(pd.vendor_canonical), ''),
            NULLIF(TRIM(p.vendor), ''),
            'Sin tienda'
          ) AS raw_store,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pd.vendor_canonical), ''), NULLIF(TRIM(p.vendor), ''), 'Sin tienda')")} AS normalized_store
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
        WHERE i.share_public_rating = TRUE
          AND i.user_rating IS NOT NULL
      )
      SELECT
        COALESCE(e.display_name, alias_entry.display_name, incident_context.raw_store) AS label,
        ROUND(AVG(incident_context.user_rating)::numeric, 2) AS average_rating,
        COUNT(*)::int AS count
      FROM incident_context
      LEFT JOIN canonical_catalog_entries e
        ON e.kind = 'store' AND (
          e.normalized_name = incident_context.normalized_store
          OR incident_context.normalized_store LIKE e.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases a
        ON a.normalized_alias = incident_context.normalized_store
      LEFT JOIN canonical_catalog_entries alias_entry
        ON alias_entry.id = a.entry_id AND alias_entry.kind = 'store'
      GROUP BY label
      ORDER BY average_rating DESC, count DESC, label ASC
      LIMIT 8
    `),
    query<{ label: string; average_rating: string; count: string }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.user_rating,
          COALESCE(
            NULLIF(TRIM(pl.brand_raw), ''),
            NULLIF(TRIM(p.brand), ''),
            'Sin marca'
          ) AS raw_brand,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), 'Sin marca')")} AS normalized_brand
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        WHERE i.share_public_rating = TRUE
          AND i.user_rating IS NOT NULL
      )
      SELECT
        COALESCE(e.display_name, alias_entry.display_name, incident_context.raw_brand) AS label,
        ROUND(AVG(incident_context.user_rating)::numeric, 2) AS average_rating,
        COUNT(*)::int AS count
      FROM incident_context
      LEFT JOIN canonical_catalog_entries e
        ON e.kind = 'brand' AND (
          e.normalized_name = incident_context.normalized_brand
          OR incident_context.normalized_brand LIKE e.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases a
        ON a.normalized_alias = incident_context.normalized_brand
      LEFT JOIN canonical_catalog_entries alias_entry
        ON alias_entry.id = a.entry_id AND alias_entry.kind = 'brand'
      GROUP BY label
      ORDER BY average_rating DESC, count DESC, label ASC
      LIMIT 8
    `),
    query<{
      id: string;
      incident_number: string;
      title: string;
      store_label: string;
      brand_label: string;
      rating: string;
      notes: string;
      updated_at: string;
    }>(`
      WITH incident_context AS (
        SELECT
          i.id,
          i.incident_number,
          COALESCE(NULLIF(TRIM(i.title), ''), NULLIF(TRIM(p.name), ''), 'Incidencia sin titulo') AS title,
          COALESCE(
            NULLIF(TRIM(pd.vendor_canonical), ''),
            NULLIF(TRIM(p.vendor), ''),
            'Sin tienda'
          ) AS raw_store,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pd.vendor_canonical), ''), NULLIF(TRIM(p.vendor), ''), 'Sin tienda')")} AS normalized_store,
          COALESCE(
            NULLIF(TRIM(pl.brand_raw), ''),
            NULLIF(TRIM(p.brand), ''),
            'Sin marca'
          ) AS raw_brand,
          ${normalizeSqlExpression("COALESCE(NULLIF(TRIM(pl.brand_raw), ''), NULLIF(TRIM(p.brand), ''), 'Sin marca')")} AS normalized_brand,
          i.user_rating AS rating,
          COALESCE(NULLIF(TRIM(i.user_rating_notes), ''), NULL) AS notes,
          i.updated_at
        FROM repair_incidents i
        LEFT JOIN products p ON p.id = i.product_id AND p.user_id = i.user_id
        LEFT JOIN purchase_document_lines pl ON pl.id = i.purchase_document_line_id AND pl.user_id = i.user_id
        LEFT JOIN purchase_documents pd ON pd.id = pl.document_id AND pd.user_id = i.user_id
        WHERE i.share_public_rating = TRUE
          AND i.user_rating IS NOT NULL
      )
      SELECT
        incident_context.id,
        incident_context.incident_number,
        incident_context.title,
        COALESCE(store_entry.display_name, store_alias.display_name, incident_context.raw_store) AS store_label,
        COALESCE(brand_entry.display_name, brand_alias.display_name, incident_context.raw_brand) AS brand_label,
        incident_context.rating,
        incident_context.notes,
        incident_context.updated_at
      FROM incident_context
      LEFT JOIN canonical_catalog_entries store_entry
        ON store_entry.kind = 'store' AND (
          store_entry.normalized_name = incident_context.normalized_store
          OR incident_context.normalized_store LIKE store_entry.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases store_alias_map
        ON store_alias_map.normalized_alias = incident_context.normalized_store
      LEFT JOIN canonical_catalog_entries store_alias
        ON store_alias.id = store_alias_map.entry_id AND store_alias.kind = 'store'
      LEFT JOIN canonical_catalog_entries brand_entry
        ON brand_entry.kind = 'brand' AND (
          brand_entry.normalized_name = incident_context.normalized_brand
          OR incident_context.normalized_brand LIKE brand_entry.normalized_name || ' %'
        )
      LEFT JOIN canonical_catalog_aliases brand_alias_map
        ON brand_alias_map.normalized_alias = incident_context.normalized_brand
      LEFT JOIN canonical_catalog_entries brand_alias
        ON brand_alias.id = brand_alias_map.entry_id AND brand_alias.kind = 'brand'
      ORDER BY incident_context.updated_at DESC, incident_context.id DESC
      LIMIT 6
    `),
  ]);

  const warranty = warrantyRows[0] ?? {
    total: "0",
    active: "0",
    warning: "0",
    critical: "0",
    expired: "0",
    total_spent: "0",
    avg_purchase_price: null,
    products_without_receipt: "0",
    extended_coverage: "0",
    insurance_coverage: "0",
  };
  const claims = claimRows[0] ?? {
    total: "0",
    active: "0",
    resolved: "0",
    closed: "0",
    claimed_products: "0",
    avg_rating: null,
  };
  const attentionSummary = attentionSummaryRows[0] ?? {
    avg_rating: null,
    rated_incidents: "0",
  };
  const totalProducts = toNumber(warranty.total);
  const claimedProducts = toNumber(claims.claimed_products);

  return {
    warranty: {
      total: totalProducts,
      active: toNumber(warranty.active),
      warning: toNumber(warranty.warning),
      critical: toNumber(warranty.critical),
      expired: toNumber(warranty.expired),
      expiringSoon,
    },
    claims: {
      total: toNumber(claims.total),
      active: toNumber(claims.active),
      resolved: toNumber(claims.resolved),
      closed: toNumber(claims.closed),
      claimedProducts,
      claimRate: totalProducts === 0 ? 0 : Math.round((claimedProducts / totalProducts) * 100),
      avgRating: claims.avg_rating === null ? null : toNumber(claims.avg_rating),
      topBrands: mapRanking(topBrands),
      topProducts: mapRanking(topProducts),
    },
    incidents: {
      active: toNumber(claims.active),
      items: activeIncidentRows.map((row) => ({
        id: toNumber(row.id),
        incident_number: row.incident_number,
        title: row.title,
        status: row.status,
        product_name: row.product_name,
        product_brand: row.product_brand,
        product_model: row.product_model,
        product_days_remaining: toNumber(row.product_days_remaining),
        updated_at: row.updated_at,
      })),
    },
    attention: {
      avgRating: attentionSummary.avg_rating === null ? null : toNumber(attentionSummary.avg_rating),
      ratedIncidents: toNumber(attentionSummary.rated_incidents),
      topStores: mapAttentionRanking(attentionStoreRows),
      topBrands: mapAttentionRanking(attentionBrandRows),
      recentFeedback: attentionFeedbackRows.map((row) => ({
        id: toNumber(row.id),
        incident_number: row.incident_number,
        title: row.title,
        store_label: row.store_label,
        brand_label: row.brand_label,
        rating: toNumber(row.rating),
        notes: row.notes,
        updated_at: row.updated_at,
      })),
      publicAvgRating: publicAttentionSummaryRows[0]?.avg_rating === null || publicAttentionSummaryRows[0] === undefined ? null : toNumber(publicAttentionSummaryRows[0].avg_rating),
      publicRatedIncidents: toNumber(publicAttentionSummaryRows[0]?.rated_incidents),
      publicTopStores: mapAttentionRanking(publicAttentionStoreRows),
      publicTopBrands: mapAttentionRanking(publicAttentionBrandRows),
      publicFeedback: publicAttentionFeedbackRows.map((row) => ({
        id: toNumber(row.id),
        incident_number: row.incident_number,
        title: row.title,
        store_label: row.store_label,
        brand_label: row.brand_label,
        rating: toNumber(row.rating),
        notes: row.notes,
        updated_at: row.updated_at,
      })),
    },
    stores: {
      totalStores: toNumber(totalStoreRows[0]?.total_stores),
      topStores: mapRanking(topStores),
    },
    consumer: {
      totalSpent: toNumber(warranty.total_spent),
      avgPurchasePrice: warranty.avg_purchase_price === null ? null : toNumber(warranty.avg_purchase_price),
      productsWithoutReceipt: toNumber(warranty.products_without_receipt),
      extendedCoverage: toNumber(warranty.extended_coverage),
      insuranceCoverage: toNumber(warranty.insurance_coverage),
      coverageMix: mapRanking(coverageRows),
      categoryComparison: categoryRows.map((row) => ({
        label: row.label,
        count: toNumber(row.count),
        claimed_count: toNumber(row.claimed_count),
      })),
      documentsTotal: toNumber(documentRows[0]?.total_documents),
      documentsWithTracking: toNumber(documentRows[0]?.documents_with_tracking),
    },
  };
}

export async function exportConsumerDashboardCsv(userId: number): Promise<string> {
  const summary = await getConsumerDashboardSummary(userId);
  const rows: Array<[string, string | number | null | undefined]> = [
    ["metric", "value"],
    ["total_products", summary.warranty.total],
    ["warranty_warning", summary.warranty.warning],
    ["warranty_critical", summary.warranty.critical],
    ["warranty_expired", summary.warranty.expired],
    ["claims_total", summary.claims.total],
    ["claims_active", summary.claims.active],
    ["claims_resolved", summary.claims.resolved],
    ["claims_closed", summary.claims.closed],
    ["claim_rate_percent", summary.claims.claimRate],
    ["avg_rating", summary.claims.avgRating],
    ["rated_incidents", summary.attention.ratedIncidents],
    ["attention_avg_rating", summary.attention.avgRating],
    ["stores_total", summary.stores.totalStores],
    ["documents_total", summary.consumer.documentsTotal],
    ["documents_with_tracking", summary.consumer.documentsWithTracking],
    ["total_spent", summary.consumer.totalSpent],
    ["avg_purchase_price", summary.consumer.avgPurchasePrice],
    ["products_without_receipt", summary.consumer.productsWithoutReceipt],
    ["extended_coverage", summary.consumer.extendedCoverage],
    ["insurance_coverage", summary.consumer.insuranceCoverage],
  ];

  const rankingRows: Array<[string, string, number, number | null | undefined]> = [];
  for (const item of summary.claims.topBrands) rankingRows.push(["top_brand", item.label, item.count, item.total_spent]);
  for (const item of summary.claims.topProducts) rankingRows.push(["top_product", item.label, item.count, item.total_spent]);
  for (const item of summary.stores.topStores) rankingRows.push(["top_store", item.label, item.count, item.total_spent]);
  for (const item of summary.consumer.categoryComparison) rankingRows.push(["category", item.label, item.count, item.claimed_count]);
  for (const item of summary.attention.topStores) rankingRows.push(["top_attention_store", item.label, item.count, item.average_rating]);
  for (const item of summary.attention.topBrands) rankingRows.push(["top_attention_brand", item.label, item.count, item.average_rating]);

  const lines = [
    rows.map((row) => row.map(csvCell).join(",")).join("\n"),
    "",
    ["ranking_type", "label", "count", "secondary_value"].map(csvCell).join(","),
    ...rankingRows.map((row) => row.map(csvCell).join(",")),
  ];

  return `${lines.join("\n")}\n`;
}