import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import { notSoldOutSqlD } from '@/server/db/queries/sold-out-filter.js';

export interface RelatedDealRow {
  id: string;
  title: string;
  dealType: string;
  minPrice: string | number;
  maxDiscountPercent: string | number;
  stockRemaining: string | number | null;
  quantityTotal: string | number;
  windowEnd: string | Date | null;
  vendorName: string;
  city: string | null;
  imageSrc: string | null;
  heSlug?: string | null;
}

// Safe: PUBLIC_VENDOR_STATES is a codebase constant, not user input.
const STATES_LITERAL = PUBLIC_VENDOR_STATES.map((s) => `'${s}'`).join(',');

// Correlated subquery: first approved primary image (or any approved image) for a deal.
// d.id is a column reference from the outer query — safe as sql.raw.
const IMAGE_SUBQ = sql.raw(`COALESCE(
  (SELECT url FROM deal_images WHERE deal_id = d.id AND is_primary = true AND approval_status = 'APPROVED' LIMIT 1),
  (SELECT url FROM deal_images WHERE deal_id = d.id AND approval_status = 'APPROVED' LIMIT 1),
  ''
)`);

// Correlated subquery: sum of active SKU quantities for a deal (deals table has no quantity_total column).
const QUANTITY_SUBQ = sql.raw(
  `(SELECT COALESCE(SUM(ds.quantity_total), 0) FROM deal_skus ds WHERE ds.deal_id = d.id AND ds.is_active = true)::int`,
);

// Correlated subquery: city from vendor_addresses (vendors table has no city column).
const CITY_SUBQ = sql.raw(
  `(SELECT va.city FROM vendor_addresses va WHERE va.vendor_id = v.id AND va.is_public = true ORDER BY va.id LIMIT 1)`,
);

export async function queryMoreFromVendor(
  db: DrizzleClient,
  { dealId, vendorId, limit }: { dealId: string; vendorId: string; limit: number },
): Promise<RelatedDealRow[]> {
  const result = (await db.execute(sql`
    SELECT d.id, d.title, d.deal_type AS "dealType",
           d.min_price AS "minPrice",
           d.max_discount_percent AS "maxDiscountPercent",
           d.stock_remaining AS "stockRemaining",
           ${QUANTITY_SUBQ} AS "quantityTotal",
           d.window_end AS "windowEnd",
           v.display_name AS "vendorName",
           ${CITY_SUBQ} AS "city",
           ${IMAGE_SUBQ} AS "imageSrc",
           dt.slug AS "heSlug"
    FROM deals d
    JOIN vendors v ON v.id = d.vendor_id
    LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = 'he'
    WHERE d.vendor_id = ${vendorId}
      AND d.id != ${dealId}
      AND d.deal_state = 'ACTIVE'
      AND (d.window_end IS NULL OR d.window_end > NOW())
      AND v.account_state IN (${sql.raw(STATES_LITERAL)})
      AND ${notSoldOutSqlD()}
    ORDER BY d.created_at DESC
    LIMIT ${limit}
  `)) as { rows: RelatedDealRow[] };
  return result.rows;
}

export async function querySimilarDeals(
  db: DrizzleClient,
  {
    dealId,
    categoryId,
    tagIds,
    limit,
  }: { dealId: string; categoryId: string | null; tagIds: string[]; limit: number },
): Promise<RelatedDealRow[]> {
  if (!categoryId && tagIds.length === 0) return [];

  if (tagIds.length === 0) {
    // Category-only fallback: no tag overlap, just return same-category deals ordered by recency.
    const result = (await db.execute(sql`
      SELECT d.id, d.title, d.deal_type AS "dealType",
             d.min_price AS "minPrice",
             d.max_discount_percent AS "maxDiscountPercent",
             d.stock_remaining AS "stockRemaining",
             ${QUANTITY_SUBQ} AS "quantityTotal",
             d.window_end AS "windowEnd",
             v.display_name AS "vendorName",
             ${CITY_SUBQ} AS "city",
             ${IMAGE_SUBQ} AS "imageSrc",
             dt.slug AS "heSlug"
      FROM deals d
      JOIN vendors v ON v.id = d.vendor_id
      LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = 'he'
      WHERE d.id != ${dealId}
        AND d.category_id = ${categoryId}
        AND d.deal_state = 'ACTIVE'
        AND (d.window_end IS NULL OR d.window_end > NOW())
        AND v.account_state IN (${sql.raw(STATES_LITERAL)})
        AND ${notSoldOutSqlD()}
      ORDER BY d.created_at DESC
      LIMIT ${limit}
    `)) as { rows: RelatedDealRow[] };
    return result.rows;
  }

  // Tag-overlap scoring: COUNT(matching tag assignments) per deal.
  const tagArray = sql.join(
    tagIds.map((t) => sql`${t}`),
    sql`, `,
  );
  const catFilter = categoryId
    ? sql`AND (d.category_id = ${categoryId} OR dta.tag_id IS NOT NULL)`
    : sql`AND dta.tag_id IS NOT NULL`;

  const result = (await db.execute(sql`
    SELECT d.id, d.title, d.deal_type AS "dealType",
           d.min_price AS "minPrice",
           d.max_discount_percent AS "maxDiscountPercent",
           d.stock_remaining AS "stockRemaining",
           ${QUANTITY_SUBQ} AS "quantityTotal",
           d.window_end AS "windowEnd",
           v.display_name AS "vendorName",
           ${CITY_SUBQ} AS "city",
           ${IMAGE_SUBQ} AS "imageSrc",
           COUNT(dta.tag_id) AS "tagOverlapScore",
           dt.slug AS "heSlug"
    FROM deals d
    LEFT JOIN deal_tag_assignments dta
      ON dta.deal_id = d.id
      AND dta.tag_id = ANY(ARRAY[${tagArray}]::uuid[])
    JOIN vendors v ON v.id = d.vendor_id
    LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = 'he'
    WHERE d.id != ${dealId}
      AND d.deal_state = 'ACTIVE'
      AND (d.window_end IS NULL OR d.window_end > NOW())
      AND v.account_state IN (${sql.raw(STATES_LITERAL)})
      AND ${notSoldOutSqlD()}
      ${catFilter}
    GROUP BY d.id, v.id, dt.slug
    ORDER BY "tagOverlapScore" DESC, d.created_at DESC
    LIMIT ${limit}
  `)) as { rows: RelatedDealRow[] };
  return result.rows;
}

export async function queryBuyersAlsoBought(
  db: DrizzleClient,
  { dealId, limit }: { dealId: string; limit: number },
): Promise<RelatedDealRow[]> {
  const result = (await db.execute(sql`
    SELECT d.id, d.title, d.deal_type AS "dealType",
           d.min_price AS "minPrice",
           d.max_discount_percent AS "maxDiscountPercent",
           d.stock_remaining AS "stockRemaining",
           ${QUANTITY_SUBQ} AS "quantityTotal",
           d.window_end AS "windowEnd",
           v.display_name AS "vendorName",
           ${CITY_SUBQ} AS "city",
           ${IMAGE_SUBQ} AS "imageSrc",
           dt.slug AS "heSlug"
    FROM (
      SELECT ds2.deal_id, COUNT(DISTINCT o2.buyer_user_id) AS score
      FROM "order" o1
      JOIN order_line ol1 ON ol1.order_id = o1.id
      JOIN deal_skus ds1 ON ds1.id = ol1.variant_id
      JOIN "order" o2 ON o2.buyer_user_id = o1.buyer_user_id
      JOIN order_line ol2 ON ol2.order_id = o2.id
      JOIN deal_skus ds2 ON ds2.id = ol2.variant_id AND ds2.deal_id != ds1.deal_id
      WHERE ds1.deal_id = ${dealId}
        AND o1.buyer_user_id IS NOT NULL
      GROUP BY ds2.deal_id
      ORDER BY score DESC
      LIMIT ${limit}
    ) co
    JOIN deals d ON d.id = co.deal_id
    JOIN vendors v ON v.id = d.vendor_id
    LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = 'he'
    WHERE d.deal_state = 'ACTIVE'
      AND (d.window_end IS NULL OR d.window_end > NOW())
      AND v.account_state IN (${sql.raw(STATES_LITERAL)})
      AND ${notSoldOutSqlD()}
    ORDER BY co.score DESC
  `)) as { rows: RelatedDealRow[] };
  return result.rows;
}