/**
 * Deal-translation read queries.
 *
 * All customer-facing reads MUST go through this module to guarantee that:
 *   1. The visibility predicate (`dealsVisibleWhere`) is applied.
 *   2. Locale/slug lookups are parameterized (no raw string concat).
 *   3. Source-language fallback is applied consistently.
 *
 * Plan 3 — read path / routing / SEO.
 */

import { executeRows, firstExecuteRow } from '../execute-rows.js';
import { sql, eq, and } from 'drizzle-orm';
import { getDb } from '@/server/db/client.js';
import { env } from '@/server/env.js';
import {
  deals,
  dealTranslations,
  dealSlugRedirects,
  dealImages,
  vendors,
} from '@/server/db/schema.js';
import type { DrizzleClient } from '@/server/db/client.js';
import type { InferSelectModel } from 'drizzle-orm';

export type DealTranslationRow = InferSelectModel<typeof dealTranslations>;

export interface DealHit {
  deal: {
    id: string;
    sourceLanguage: string;
    dealType: string;
    originalPrice: string | null;
    discountedPrice: string | null;
    quantityTotal: number | null;
    quantitySold: number | null;
    stockRemaining: number | null;
    windowEnd: Date | null;
    vendorId: string;
    primaryImageUrl: string | null;
    vendorName: string | null;
  };
  translation: {
    id: string;
    locale: string;
    slug: string;
    title: string;
    description: string;
    specialInstructions: string | null;
    pickupAddress: string;
    status: string;
    translatedAt: Date | null;
    modelId: string | null;
  };
  isSourceFallback: boolean;
}

export interface LocaleSlugPair {
  locale: string;
  slug: string;
}

export interface DealListItem {
  id: string;
  sourceLanguage: string;
  categoryId: string | null;
  dealType: string;
  originalPrice: string | null;
  discountedPrice: string | null;
  quantityTotal: number | null;
  quantitySold: number | null;
  windowEnd: Date | null;
  vendorId: string;
  title: string;
  description: string;
  slug: string | null;
  isSourceFallback: boolean;
  qtyTierTop: { minQty: number; discountPercent: number } | null;
  skuCount: number;
}

export interface SearchResult {
  dealId: string;
  locale: string;
  slug: string;
  title: string;
  rank: number;
}

export async function listDealTranslations(
  db: DrizzleClient,
  dealId: string,
): Promise<DealTranslationRow[]> {
  return db.select().from(dealTranslations).where(eq(dealTranslations.dealId, dealId));
}

function db() {
  return getDb({ DATABASE_URL: env.DATABASE_URL });
}

export async function findDealByLocaleSlug(locale: string, slug: string): Promise<DealHit | null> {
  const d = db();
  const rows = await d.execute(sql`
    SELECT
      d.id                   AS deal_id,
      d.source_language      AS source_language,
      d.deal_type            AS deal_type,
      (SELECT s.original_price FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS original_price,
      (SELECT s.discounted_price FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS discounted_price,
      (SELECT s.quantity_total FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS quantity_total,
      (SELECT s.quantity_sold FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS quantity_sold,
      d.stock_remaining      AS stock_remaining,
      d.window_end           AS window_end,
      d.vendor_id            AS vendor_id,
      (SELECT di.url FROM ${dealImages} di WHERE di.deal_id = d.id AND di.is_primary = true LIMIT 1) AS primary_image_url,
      v.display_name         AS vendor_name,
      t.id                   AS translation_id,
      t.locale               AS locale,
      t.slug                 AS slug,
      t.title                AS title,
      t.description          AS description,
      t.special_instructions AS special_instructions,
      t.pickup_address       AS pickup_address,
      t.status               AS status,
      t.translated_at        AS translated_at,
      t.model_id             AS model_id,
      false                  AS is_source_fallback
    FROM ${deals} d
    JOIN ${vendors} v ON v.id = d.vendor_id
    JOIN ${dealTranslations} t
      ON t.deal_id = d.id
      AND t.locale = ${locale}
      AND t.slug   = ${slug}
      AND t.status = 'OK'
    WHERE d.deal_state IN ('ACTIVE', 'SOLD_OUT', 'PAUSED')
      AND (
        d.source_language = ${locale}
        OR EXISTS (
          SELECT 1 FROM ${dealTranslations} t2
          WHERE t2.deal_id = d.id
            AND t2.locale  = ${locale}
            AND t2.status  = 'OK'
            AND t2.title       <> ''
            AND t2.description <> ''
        )
      )
    LIMIT 1
  `);

  const row = firstExecuteRow(rows) as Record<string, unknown> | undefined;
  if (!row) return null;
  return rowToDealHit(row);
}

export async function findRedirectByLocaleSlug(
  locale: string,
  oldSlug: string,
): Promise<string | null> {
  const d = db();
  const rows = await d.execute(sql`
    SELECT t.slug AS current_slug
    FROM ${dealSlugRedirects} r
    JOIN ${dealTranslations} t
      ON t.deal_id = r.deal_id
      AND t.locale  = r.locale
      AND t.status  = 'OK'
    WHERE r.locale   = ${locale}
      AND r.old_slug = ${oldSlug}
    LIMIT 1
  `);

  const row = firstExecuteRow(rows) as Record<string, unknown> | undefined;
  return row ? (row['current_slug'] as string) : null;
}

export async function findLocaleSlugPairsForDeal(dealId: string): Promise<LocaleSlugPair[]> {
  const d = db();
  const rows = await d.execute(sql`
    SELECT locale, slug
    FROM ${dealTranslations}
    WHERE deal_id = ${dealId}
      AND status  = 'OK'
    ORDER BY locale
  `);

  return executeRows<Record<string, unknown>>(rows).map((r) => ({
    locale: r['locale'] as string,
    slug: r['slug'] as string,
  }));
}

export async function findDealListForLocale(
  locale: string,
  opts: { limit: number; offset: number; categoryId?: string },
): Promise<DealListItem[]> {
  const d = db();
  const categoryFilter = opts.categoryId ? sql`AND d.category_id = ${opts.categoryId}` : sql``;

  const rows = await d.execute(sql`
    SELECT
      d.id              AS id,
      d.source_language AS source_language,
      d.category_id     AS category_id,
      d.deal_type       AS deal_type,
      (SELECT s.original_price FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS original_price,
      (SELECT s.discounted_price FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS discounted_price,
      (SELECT s.quantity_total FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS quantity_total,
      (SELECT s.quantity_sold FROM deal_skus s WHERE s.deal_id = d.id AND s.option_ids_hash = 'default' LIMIT 1) AS quantity_sold,
      (SELECT json_build_object('minQty', tt.min_qty, 'discountPercent', tt.discount_percent)
       FROM sku_qty_tiers tt JOIN deal_skus ss ON ss.id = tt.deal_sku_id
       WHERE ss.deal_id = d.id
       ORDER BY tt.discount_percent DESC, tt.min_qty ASC LIMIT 1)        AS qty_tier_top,
      (SELECT COUNT(*)::int FROM deal_skus s3 WHERE s3.deal_id = d.id)   AS sku_count,
      d.window_end      AS window_end,
      d.vendor_id       AS vendor_id,
      COALESCE(NULLIF(t.title, ''), d.title)              AS title,
      COALESCE(NULLIF(t.description, ''), d.description)  AS description,
      t.slug                                               AS slug,
      (t.deal_id IS NULL)                                  AS is_source_fallback
    FROM ${deals} d
    LEFT JOIN ${dealTranslations} t
      ON t.deal_id = d.id
      AND t.locale  = ${locale}
      AND t.status  = 'OK'
    WHERE d.deal_state = 'ACTIVE'
      AND (t.deal_id IS NOT NULL OR d.source_language = ${locale})
    ${categoryFilter}
    ORDER BY d.created_at DESC
    LIMIT ${opts.limit}
    OFFSET ${opts.offset}
  `);

  return executeRows<Record<string, unknown>>(rows).map((r) => ({
    id: r['id'] as string,
    sourceLanguage: r['source_language'] as string,
    categoryId: r['category_id'] as string | null,
    dealType: r['deal_type'] as string,
    originalPrice: r['original_price'] as string,
    discountedPrice: r['discounted_price'] as string,
    quantityTotal: r['quantity_total'] as number,
    quantitySold: r['quantity_sold'] as number,
    windowEnd: r['window_end'] as Date | null,
    vendorId: r['vendor_id'] as string,
    title: r['title'] as string,
    description: r['description'] as string,
    slug: (r['slug'] as string | null) ?? null,
    isSourceFallback: Boolean(r['is_source_fallback']),
    qtyTierTop:
      r['qty_tier_top'] == null
        ? null
        : typeof r['qty_tier_top'] === 'string'
          ? JSON.parse(r['qty_tier_top'] as string)
          : (r['qty_tier_top'] as { minQty: number; discountPercent: number }),
    skuCount: Number(r['sku_count'] ?? 1),
  }));
}

export async function searchDealTranslations(
  locale: string,
  regconfig: string,
  q: string,
  limit = 20,
): Promise<SearchResult[]> {
  const d = db();
  const rows = await d.execute(sql`
    SELECT
      t.deal_id AS deal_id,
      t.locale  AS locale,
      t.slug    AS slug,
      t.title   AS title,
      ts_rank(
        to_tsvector(${regconfig}::regconfig, t.title || ' ' || t.description),
        plainto_tsquery(${regconfig}::regconfig, ${q})
      ) AS rank
    FROM ${dealTranslations} t
    JOIN ${deals} d ON d.id = t.deal_id
    WHERE t.locale = ${locale}
      AND t.status = 'OK'
      AND d.deal_state = 'ACTIVE'
      AND (
        d.source_language = ${locale}
        OR EXISTS (
          SELECT 1 FROM ${dealTranslations} t2
          WHERE t2.deal_id = d.id
            AND t2.locale  = ${locale}
            AND t2.status  = 'OK'
            AND t2.title       <> ''
            AND t2.description <> ''
        )
      )
      AND to_tsvector(${regconfig}::regconfig, t.title || ' ' || t.description)
          @@ plainto_tsquery(${regconfig}::regconfig, ${q})
    ORDER BY rank DESC
    LIMIT ${limit}
  `);

  return executeRows<Record<string, unknown>>(rows).map((r) => ({
    dealId: r['deal_id'] as string,
    locale: r['locale'] as string,
    slug: r['slug'] as string,
    title: r['title'] as string,
    rank: Number(r['rank']),
  }));
}

export async function updateEnglishDealTranslation(
  db: DrizzleClient,
  dealId: string,
  values: Partial<typeof dealTranslations.$inferInsert>,
): Promise<void> {
  await db
    .update(dealTranslations)
    .set(values)
    .where(and(eq(dealTranslations.dealId, dealId), eq(dealTranslations.locale, 'en')));
}

export async function insertEnglishDealTranslation(
  db: DrizzleClient,
  values: typeof dealTranslations.$inferInsert,
): Promise<void> {
  await db.insert(dealTranslations).values(values);
}

export async function insertDealTranslation(
  db: DrizzleClient,
  values: typeof dealTranslations.$inferInsert,
): Promise<void> {
  await db.insert(dealTranslations).values(values);
}

function rowToDealHit(row: Record<string, unknown>): DealHit {
  return {
    deal: {
      id: row['deal_id'] as string,
      sourceLanguage: row['source_language'] as string,
      dealType: row['deal_type'] as string,
      originalPrice: (row['original_price'] as string | null) ?? null,
      discountedPrice: (row['discounted_price'] as string | null) ?? null,
      quantityTotal: (row['quantity_total'] as number | null) ?? null,
      quantitySold: (row['quantity_sold'] as number | null) ?? null,
      stockRemaining: (row['stock_remaining'] as number | null) ?? null,
      windowEnd: row['window_end'] as Date | null,
      vendorId: row['vendor_id'] as string,
      primaryImageUrl: (row['primary_image_url'] as string | null) ?? null,
      vendorName: (row['vendor_name'] as string | null) ?? null,
    },
    translation: {
      id: row['translation_id'] as string,
      locale: row['locale'] as string,
      slug: row['slug'] as string,
      title: row['title'] as string,
      description: row['description'] as string,
      specialInstructions: row['special_instructions'] as string | null,
      pickupAddress: row['pickup_address'] as string,
      status: row['status'] as string,
      translatedAt: row['translated_at'] as Date | null,
      modelId: row['model_id'] as string | null,
    },
    isSourceFallback: Boolean(row['is_source_fallback']),
  };
}