/**
 * Slug resolver — look up category/tag by current slug or historical slug.
 *
 * Used by SSR route /deals/[...path].astro and API endpoints that accept slug params.
 * Lookup order: current slug first; on miss, check slug_history; on history hit,
 * resolve to current entity and set redirected=true so caller emits 301.
 */

import { executeRows, firstExecuteRow } from '../execute-rows.js';
import { and, eq, inArray } from 'drizzle-orm';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { dealTags, slugHistory } from '../schema.js';

// ─── Category resolver ────────────────────────────────────────────────────────

export interface CategoryResolve {
  id: string;
  canonicalSlug: string;
  nameHe: string;
  nameEn: string;
  redirected: boolean;
}

type CategorySlugRow = {
  id: string;
  slug: string;
  nameHe: string;
  nameEn: string;
};

type TagNameRow = {
  nameHe: string;
  nameEn: string;
};

export async function resolveCategorySlug(
  db: DrizzleClient,
  slug: string,
): Promise<CategoryResolve | null> {
  // Try current slug (with translation join)
  const current = await db.execute<CategorySlugRow>(sql`
    SELECT c.id,
           c.slug,
           COALESCE(he_t.name, '') AS "nameHe",
           COALESCE(en_t.name, he_t.name, '') AS "nameEn"
    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 en_t
      ON en_t.category_id = c.id AND en_t.locale = 'en'
    WHERE c.slug = ${slug} AND c.is_active = true
    LIMIT 1
  `);
  const cur = firstExecuteRow<CategorySlugRow>(current);
  if (cur) {
    return {
      id: cur.id,
      canonicalSlug: cur.slug,
      nameHe: cur.nameHe ?? '',
      nameEn: cur.nameEn ?? '',
      redirected: false,
    };
  }

  // History fallback — look up old_slug in slug_history
  const hist = await db
    .select({ entityId: slugHistory.entityId })
    .from(slugHistory)
    .where(and(eq(slugHistory.entityType, 'category'), eq(slugHistory.oldSlug, slug)))
    .limit(1);
  if (!hist[0]) return null;

  const final = await db.execute<CategorySlugRow>(sql`
    SELECT c.id,
           c.slug,
           COALESCE(he_t.name, '') AS "nameHe",
           COALESCE(en_t.name, he_t.name, '') AS "nameEn"
    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 en_t
      ON en_t.category_id = c.id AND en_t.locale = 'en'
    WHERE c.id = ${hist[0].entityId} AND c.is_active = true
    LIMIT 1
  `);
  const row = firstExecuteRow<CategorySlugRow>(final);
  if (!row) return null;
  return {
    id: row.id,
    canonicalSlug: row.slug,
    nameHe: row.nameHe ?? '',
    nameEn: row.nameEn ?? '',
    redirected: true,
  };
}

// ─── Tags resolver ────────────────────────────────────────────────────────────

export interface TagsResolve {
  ids: string[];
  canonicalSlugs: string[];
  namesHe: string[];
  namesEn: string[];
  redirected: boolean;
}

export async function resolveTagSlugs(
  db: DrizzleClient,
  slugs: string[],
): Promise<TagsResolve | null> {
  if (slugs.length === 0) {
    return { ids: [], canonicalSlugs: [], namesHe: [], namesEn: [], redirected: false };
  }

  let redirected = false;

  const curRows = await db
    .select({
      id: dealTags.id,
      slug: dealTags.slug,
    })
    .from(dealTags)
    .where(and(inArray(dealTags.slug, slugs), eq(dealTags.isActive, true)));
  const curBySlug = new Map(curRows.map((r) => [r.slug, r]));

  const missingSlugs = slugs.filter((slug) => !curBySlug.has(slug));
  const histByOldSlug = new Map<string, string>();
  if (missingSlugs.length > 0) {
    const histRows = await db
      .select({ entityId: slugHistory.entityId, oldSlug: slugHistory.oldSlug })
      .from(slugHistory)
      .where(and(eq(slugHistory.entityType, 'tag'), inArray(slugHistory.oldSlug, missingSlugs)));
    for (const row of histRows) {
      if (!histByOldSlug.has(row.oldSlug)) histByOldSlug.set(row.oldSlug, row.entityId);
    }
  }

  const histEntityIds = [...new Set(histByOldSlug.values())];
  const resolvedById = new Map<string, { id: string; slug: string }>();
  if (histEntityIds.length > 0) {
    const resolvedRows = await db
      .select({ id: dealTags.id, slug: dealTags.slug })
      .from(dealTags)
      .where(and(inArray(dealTags.id, histEntityIds), eq(dealTags.isActive, true)));
    for (const row of resolvedRows) resolvedById.set(row.id, row);
  }

  const allTagIds = new Set<string>();
  for (const slug of slugs) {
    const cur = curBySlug.get(slug);
    if (cur) {
      allTagIds.add(cur.id);
      continue;
    }
    const entityId = histByOldSlug.get(slug);
    if (!entityId) return null;
    const resolved = resolvedById.get(entityId);
    if (!resolved) return null;
    allTagIds.add(resolved.id);
  }

  type TagNameWithId = TagNameRow & { tagId: string };
  const namesById = new Map<string, TagNameRow>();
  if (allTagIds.size > 0) {
    const names = await db.execute<TagNameWithId>(sql`
      SELECT t.id AS "tagId",
             COALESCE(he_t.name, '') AS "nameHe",
             COALESCE(en_t.name, he_t.name, '') AS "nameEn"
      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 en_t ON en_t.tag_id = t.id AND en_t.locale = 'en'
      WHERE t.id IN (${sql.join(
        [...allTagIds].map((id) => sql`${id}`),
        sql`, `,
      )})
    `);
    for (const row of executeRows<TagNameWithId>(names)) {
      namesById.set(row.tagId, { nameHe: row.nameHe, nameEn: row.nameEn });
    }
  }

  const ids: string[] = [];
  const canonicalSlugs: string[] = [];
  const namesHe: string[] = [];
  const namesEn: string[] = [];

  for (const inputSlug of slugs) {
    const cur = curBySlug.get(inputSlug);
    if (cur) {
      const n = namesById.get(cur.id);
      ids.push(cur.id);
      canonicalSlugs.push(cur.slug);
      namesHe.push(n?.nameHe ?? '');
      namesEn.push(n?.nameEn ?? '');
      continue;
    }

    const entityId = histByOldSlug.get(inputSlug);
    if (!entityId) return null;

    const resolved = resolvedById.get(entityId);
    if (!resolved) return null;

    redirected = true;
    const n = namesById.get(resolved.id);
    ids.push(resolved.id);
    canonicalSlugs.push(resolved.slug);
    namesHe.push(n?.nameHe ?? '');
    namesEn.push(n?.nameEn ?? '');
  }

  return { ids, canonicalSlugs, namesHe, namesEn, redirected };
}