import { executeRows } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import type { DealType } from '@/lib/deal-types';
import { DEAL_TYPES } from '@/lib/deal-types';
import { HISTO_BUCKETS } from '@/lib/deals/histo-buckets';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import { notSoldOutSqlD } from './sold-out-filter.js';
import { buildDealTextPredicate } from './deals-search-predicate.js';

/** Number of price-histogram bars rendered under the slider (shared by all surfaces). */
export { HISTO_BUCKETS } from '@/lib/deals/histo-buckets';

export interface DealsFacetInput {
  catId?: string;
  type?: DealType;
  tagIds?: string[];
  priceMin?: number;
  priceMax?: number;
  tagLimit?: number;
  q?: string;
  cityCode?: string;
  geo?: { lat: number; lng: number; km: number };
  /** Domain floor (shekels) for the price histogram — must match slider floor when histoMax is set. */
  histoMin?: number;
  /** Domain ceiling (shekels) for the price histogram. Omit → no histogram ([]). */
  histoMax?: number;
  /** Bucket count for the price histogram. Defaults to HISTO_BUCKETS. */
  histoBuckets?: number;
  locale?: string;
  searchConfig?: string;
  minDiscount?: number;
  /** When true, scope facet base + histogram to urgency set (expiring <24h OR stock 1–9). */
  whatsLeft?: boolean;
}

export interface FacetCategory {
  id: string;
  slug: string;
  nameHe: string;
  nameEn: string;
  dealCount: number;
}

export interface FacetTag {
  id: string;
  slug: string;
  nameHe: string;
  nameEn: string;
  usageCount: number;
}

export interface DealsFacetCounts {
  categories: FacetCategory[];
  types: Record<DealType, number>;
  tags: FacetTag[];
  /** Deal counts per price bucket, length = histoBuckets; [] when histoMax not requested. */
  priceHistogram: number[];
}

const publicVendorStatesSql = sql`(${sql.join(
  PUBLIC_VENDOR_STATES.map((s) => sql`${s}`),
  sql`, `,
)})`;

function tagExistsOnFiltered(tagIds: string[] | undefined) {
  if (!tagIds?.length) return sql``;
  return sql`AND (
    SELECT COUNT(DISTINCT dta.tag_id)
    FROM deal_tag_assignments dta
    WHERE dta.deal_id = f.id
      AND dta.tag_id IN (${sql.join(
        tagIds.map((id) => sql`${id}::uuid`),
        sql`, `,
      )})
  ) = ${tagIds.length}`;
}

interface FacetRow {
  dim: string;
  key: string;
  slug: string | null;
  name_he: string | null;
  name_en: string | null;
  sort_order: number | null;
  cnt: number | string;
}

export interface HistogramRow {
  bucket: number;
  cnt: number | string;
}

/**
 * Fold raw width_bucket rows into a fixed-length counts array.
 * width_bucket returns 0 for x<lo (underflow) and (n+1) for x>=hi (overflow).
 * Buckets 1..histoBuckets map to indices 0..histoBuckets-1; underflow is clamped
 * into index 0 and overflow into index histoBuckets-1 (price == histoMax lands in
 * the overflow bucket). Deals remain filterable because max-handle==ceiling means
 * no upper bound.
 */
export function foldHistogramRows(rows: HistogramRow[], histoBuckets: number): number[] {
  const out = new Array<number>(histoBuckets).fill(0);
  for (const r of rows) {
    let idx = Number(r.bucket) - 1;
    if (idx < 0) idx = 0;
    else if (idx >= histoBuckets) idx = histoBuckets - 1;
    out[idx] = (out[idx] ?? 0) + Number(r.cnt);
  }
  return out;
}

export async function getDealsFacetCounts(
  db: DrizzleClient,
  input: DealsFacetInput,
): Promise<DealsFacetCounts> {
  const tagLimit = input.tagLimit && input.tagLimit > 0 ? input.tagLimit : 30;

  const priceMinFilter =
    input.priceMin !== undefined ? sql`AND d.min_price >= ${String(input.priceMin)}` : sql``;
  const priceMaxFilter =
    input.priceMax !== undefined ? sql`AND d.min_price <= ${String(input.priceMax)}` : sql``;

  const pred = buildDealTextPredicate({
    q: input.q,
    locale: input.locale,
    searchConfig: input.searchConfig,
  });
  const textQueryFilter = pred ? sql`AND ${pred}` : sql``;

  const minDiscountFilter =
    input.minDiscount !== undefined
      ? sql`AND d.max_discount_percent >= ${input.minDiscount}`
      : sql``;

  const hasGeoOrCity = !!(input.geo || input.cityCode);
  const vendorAddressJoin = hasGeoOrCity
    ? sql`INNER JOIN vendor_addresses va ON va.vendor_id = d.vendor_id AND va.is_public = true`
    : sql``;

  let geoFilter = sql``;
  if (input.geo) {
    const { lat, lng, km } = input.geo;
    const degDelta = km / 111.0;
    geoFilter = sql`AND va.lat::float BETWEEN ${lat - degDelta} AND ${lat + degDelta}
        AND va.lng::float BETWEEN ${lng - degDelta} AND ${lng + degDelta}
        AND 6371.0 * acos(LEAST(1.0, GREATEST(-1.0,
          cos(radians(${lat})) * cos(radians(va.lat::float)) *
          cos(radians(va.lng::float) - radians(${lng})) +
          sin(radians(${lat})) * sin(radians(va.lat::float))
        ))) <= ${km}`;
  }

  const cityCodeFilter = input.cityCode ? sql`AND va.city_code = ${input.cityCode}` : sql``;

  const whatsLeftSql = input.whatsLeft
    ? sql`AND (
        (d.window_end < NOW() + INTERVAL '24 hours' AND d.window_end > NOW())
        OR (d.stock_remaining IS NOT NULL AND d.stock_remaining > 0 AND d.stock_remaining < 10)
      )`
    : sql``;

  const catDimTypeFilter = input.type ? sql`AND f.deal_type = ${input.type}` : sql``;
  const catDimTagFilter = tagExistsOnFiltered(input.tagIds);

  const typeDimCatFilter = input.catId ? sql`AND f.category_id = ${input.catId}::uuid` : sql``;
  const typeDimTagFilter = tagExistsOnFiltered(input.tagIds);

  const tagDimCatFilter = input.catId ? sql`AND f.category_id = ${input.catId}::uuid` : sql``;
  const tagDimTypeFilter = input.type ? sql`AND f.deal_type = ${input.type}` : sql``;

  const result = await db.execute(sql`
    WITH filtered AS (
      SELECT d.id, d.deal_type, d.category_id
      FROM deals d
      INNER JOIN vendors v
        ON v.id = d.vendor_id
       AND v.account_state IN ${publicVendorStatesSql}
      ${vendorAddressJoin}
      WHERE d.deal_state = 'ACTIVE'
        AND (d.window_end IS NULL OR d.window_end > NOW())
        AND d.min_price IS NOT NULL
        AND ${notSoldOutSqlD()}
        ${whatsLeftSql}
        ${priceMinFilter}
        ${priceMaxFilter}
        ${minDiscountFilter}
        ${textQueryFilter}
        ${geoFilter}
        ${cityCodeFilter}
    ),
    cat_counts AS (
      SELECT
        'category'::text                                              AS dim,
        c.id::text                                                    AS key,
        c.slug                                                        AS slug,
        COALESCE(he_t.name, he_fb.name, '')                           AS name_he,
        COALESCE(en_t.name, he_fb.name, '')                           AS name_en,
        c.sort_order                                                  AS sort_order,
        COUNT(DISTINCT f.id)                                          AS cnt
      FROM deal_categories c
      LEFT JOIN category_translations he_t
        ON he_t.category_id = c.id AND he_t.locale = 'he'
      LEFT JOIN category_translations he_fb
        ON he_fb.category_id = c.id AND he_fb.locale = 'he'
      LEFT JOIN category_translations en_t
        ON en_t.category_id = c.id AND en_t.locale = 'en'
      LEFT JOIN filtered f
        ON f.category_id = c.id
        ${catDimTypeFilter}
        ${catDimTagFilter}
      WHERE c.is_active = true
      GROUP BY c.id, c.slug, c.sort_order, he_t.name, he_fb.name, en_t.name
    ),
    type_counts AS (
      SELECT
        'type'::text                                                  AS dim,
        f.deal_type::text                                             AS key,
        NULL::text                                                    AS slug,
        NULL::text                                                    AS name_he,
        NULL::text                                                    AS name_en,
        NULL::int                                                     AS sort_order,
        COUNT(DISTINCT f.id)                                          AS cnt
      FROM filtered f
      WHERE TRUE
        ${typeDimCatFilter}
        ${typeDimTagFilter}
      GROUP BY f.deal_type
    ),
    tag_counts AS (
      SELECT
        'tag'::text                                                   AS dim,
        t.id::text                                                    AS key,
        t.slug                                                        AS slug,
        COALESCE(he_t.name, he_fb.name, '')                           AS name_he,
        COALESCE(en_t.name, he_fb.name, '')                           AS name_en,
        NULL::int                                                     AS sort_order,
        COUNT(DISTINCT f.id)                                          AS cnt
      FROM deal_tags t
      LEFT JOIN tag_translations he_t
        ON he_t.tag_id = t.id AND he_t.locale = 'he'
      LEFT JOIN tag_translations he_fb
        ON he_fb.tag_id = t.id AND he_fb.locale = 'he'
      LEFT JOIN tag_translations en_t
        ON en_t.tag_id = t.id AND en_t.locale = 'en'
      INNER JOIN deal_tag_assignments a
        ON a.tag_id = t.id
      INNER JOIN filtered f
        ON f.id = a.deal_id
        ${tagDimCatFilter}
        ${tagDimTypeFilter}
      WHERE t.is_active = true
      GROUP BY t.id, t.slug, he_t.name, he_fb.name, en_t.name
      HAVING COUNT(DISTINCT f.id) > 0
      ORDER BY cnt DESC, COALESCE(he_t.name, he_fb.name, '') ASC
      LIMIT ${tagLimit}
    )
    SELECT dim, key, slug, name_he, name_en, sort_order, cnt FROM cat_counts
    UNION ALL
    SELECT dim, key, slug, name_he, name_en, sort_order, cnt FROM type_counts
    UNION ALL
    SELECT dim, key, slug, name_he, name_en, sort_order, cnt FROM tag_counts
  `);

  const rows = executeRows<FacetRow>(result);

  const categories: FacetCategory[] = rows
    .filter((r) => r.dim === 'category')
    .map((r) => ({
      id: r.key,
      slug: r.slug ?? '',
      nameHe: r.name_he ?? '',
      nameEn: r.name_en ?? '',
      dealCount: Number(r.cnt),
      sortOrder: r.sort_order ?? 0,
    }))
    .sort((a, b) => {
      if (a.sortOrder !== b.sortOrder) return a.sortOrder - b.sortOrder;
      return a.nameHe.localeCompare(b.nameHe);
    })
    .map(({ sortOrder: _sortOrder, ...cat }) => cat);

  const types = Object.fromEntries(DEAL_TYPES.map((t) => [t, 0])) as Record<DealType, number>;
  for (const r of rows) {
    if (r.dim === 'type' && r.key in types) {
      types[r.key as DealType] = Number(r.cnt);
    }
  }

  const tags: FacetTag[] = rows
    .filter((r) => r.dim === 'tag')
    .map((r) => ({
      id: r.key,
      slug: r.slug ?? '',
      nameHe: r.name_he ?? '',
      nameEn: r.name_en ?? '',
      usageCount: Number(r.cnt),
    }))
    .sort((a, b) => b.usageCount - a.usageCount || a.nameHe.localeCompare(b.nameHe));

  // Price histogram — excludes the price filter (exclude-own-dim) so dragging a
  // handle never collapses the bars. Applies every OTHER active filter.
  let priceHistogram: number[] = [];
  if (input.histoMax !== undefined && input.histoMax > 0 && input.histoMin !== undefined) {
    const histoBuckets =
      input.histoBuckets && input.histoBuckets > 0 ? input.histoBuckets : HISTO_BUCKETS;

    const histoCatFilter = input.catId ? sql`AND d.category_id = ${input.catId}::uuid` : sql``;
    const histoTypeFilter = input.type ? sql`AND d.deal_type = ${input.type}` : sql``;
    const histoTagFilter = input.tagIds?.length
      ? sql`AND (
          SELECT COUNT(DISTINCT dta.tag_id)
          FROM deal_tag_assignments dta
          WHERE dta.deal_id = d.id
            AND dta.tag_id IN (${sql.join(
              input.tagIds.map((id) => sql`${id}::uuid`),
              sql`, `,
            )})
        ) = ${input.tagIds.length}`
      : sql``;

    const histoMin = input.histoMin;

    const histoResult = await db.execute(sql`
      SELECT
        width_bucket(d.min_price::float, ${histoMin}, ${input.histoMax}, ${histoBuckets}) AS bucket,
        COUNT(*)::int AS cnt
      FROM deals d
      INNER JOIN vendors v
        ON v.id = d.vendor_id
       AND v.account_state IN ${publicVendorStatesSql}
      ${vendorAddressJoin}
      WHERE d.deal_state = 'ACTIVE'
        AND (d.window_end IS NULL OR d.window_end > NOW())
        AND d.min_price IS NOT NULL
        AND ${notSoldOutSqlD()}
        ${whatsLeftSql}
        ${histoCatFilter}
        ${histoTypeFilter}
        ${histoTagFilter}
        ${minDiscountFilter}
        ${textQueryFilter}
        ${geoFilter}
        ${cityCodeFilter}
      GROUP BY bucket
    `);

    priceHistogram = foldHistogramRows(executeRows<HistogramRow>(histoResult), histoBuckets);
  }

  return { categories, types, tags, priceHistogram };
}