/**
 * Category + tag mutation/read queries — translation sidecar source-of-truth.
 *
 * Display names live in `category_translations` / `tag_translations`. Legacy
 * columns `name_he`/`name_en` were dropped on `deal_categories` + `deal_tags`.
 *
 * Public read functions return `nameHe`/`nameEn` for wire-format compatibility
 * with existing API consumers (admin UI, public category pickers); the values
 * are joined from the translation sidecars at query time.
 */

import { eq, asc, count, sql } from 'drizzle-orm';
import { sha256 } from '@platform-modules/util/crypto';
import type { DrizzleClient, TxDrizzleClient } from '../client.js';
import { executeRows, firstExecuteRow } from '../execute-rows.js';
import {
  dealCategories,
  categoryTranslations,
  tagTranslations,
  languages,
  dealTags,
  dealTagAssignments,
  deals,
} from '../schema.js';
import { kebabSlug, dedupeSlug } from './tag-slug.js';
import { invalidateCatalog } from '@/server/cache/invalidate.js';

async function resolveCategorySlug(
  db: DrizzleClient,
  nameEn: string,
  nameHe: string,
  providedSlug?: string,
): Promise<string> {
  const base =
    providedSlug || kebabSlug(nameEn, nameHe) || `cat-${Math.random().toString(36).slice(2, 8)}`;
  const existing = await db.execute<{ slug: string }>(
    sql`SELECT slug FROM deal_categories WHERE slug LIKE ${base + '%'}`,
  );
  const taken = new Set<string>(executeRows<{ slug: string }>(existing).map((r) => r.slug));
  return dedupeSlug(base, taken);
}

// ---------------------------------------------------------------------------
// Read queries
// ---------------------------------------------------------------------------

/**
 * List all active categories. Names are joined from
 * `category_translations` (he+en). When a locale row is missing, falls back
 * to the Hebrew default-locale row.
 * @deprecated dealType param removed — categories are now universal.
 */
export async function listActiveCategories(db: DrizzleClient) {
  const rows = await db.execute<{
    id: string;
    sortOrder: number;
    nameHe: string | null;
    nameEn: string | null;
  }>(sql`
    SELECT
      c.id,
      c.sort_order                               AS "sortOrder",
      COALESCE(he_t.name, he_fb.name)            AS "nameHe",
      COALESCE(en_t.name, he_fb.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 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'
    WHERE c.is_active = true
    ORDER BY c.sort_order ASC, "nameHe" ASC
  `);
  return executeRows<{
    id: string;
    sortOrder: number;
    nameHe: string | null;
    nameEn: string | null;
  }>(rows).map((r) => ({
    id: r.id,
    sortOrder: r.sortOrder,
    nameHe: r.nameHe ?? '',
    nameEn: r.nameEn ?? '',
  }));
}

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

export async function listActiveCategoriesWithCounts(
  db: DrizzleClient,
): Promise<CategoryWithCount[]> {
  const rows = await db.execute<{
    id: string;
    slug: string;
    sortOrder: number;
    nameHe: string | null;
    nameEn: string | null;
    dealCount: number | string;
  }>(sql`
    SELECT
      c.id,
      c.slug,
      c.sort_order                               AS "sortOrder",
      COALESCE(he_t.name, he_fb.name)            AS "nameHe",
      COALESCE(en_t.name, he_fb.name)            AS "nameEn",
      COUNT(d.id)                                AS "dealCount"
    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 deals d
      ON d.category_id = c.id AND d.deal_state = 'ACTIVE'
    WHERE c.is_active = true
    GROUP BY c.id, c.slug, c.sort_order, he_t.name, he_fb.name, en_t.name
    ORDER BY c.sort_order ASC, "nameHe" ASC
  `);
  return executeRows<{
    id: string;
    slug: string;
    sortOrder: number;
    nameHe: string | null;
    nameEn: string | null;
    dealCount: number | string;
  }>(rows).map((r) => ({
    id: r.id,
    slug: r.slug ?? '',
    sortOrder: r.sortOrder,
    nameHe: r.nameHe ?? '',
    nameEn: r.nameEn ?? '',
    dealCount: Number(r.dealCount),
  }));
}

/**
 * List all categories (active + inactive). Names from sidecar.
 */
export async function listAllCategories(db: DrizzleClient) {
  const rows = await db.execute<{
    id: string;
    slug: string;
    dealType: string;
    sortOrder: number;
    isActive: boolean;
    createdAt: Date;
    nameHe: string | null;
    nameEn: string | null;
  }>(sql`
    SELECT
      c.id,
      c.slug,
      c.deal_type                                AS "dealType",
      c.sort_order                               AS "sortOrder",
      c.is_active                                AS "isActive",
      c.created_at                               AS "createdAt",
      COALESCE(he_t.name, he_fb.name)            AS "nameHe",
      COALESCE(en_t.name, he_fb.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 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'
    ORDER BY c.sort_order ASC, "nameHe" ASC
  `);
  return executeRows<{
    id: string;
    slug: string;
    dealType: string;
    sortOrder: number;
    isActive: boolean;
    createdAt: Date;
    nameHe: string | null;
    nameEn: string | null;
  }>(rows).map((r) => ({
    id: r.id,
    slug: r.slug ?? '',
    dealType: r.dealType,
    sortOrder: r.sortOrder,
    isActive: r.isActive,
    createdAt: r.createdAt,
    nameHe: r.nameHe ?? '',
    nameEn: r.nameEn ?? '',
  }));
}

export interface LocalisedCategory {
  id: string;
  dealType: string | null;
  sortOrder: number;
  isActive: boolean;
  name: string;
}

/**
 * Locale-aware category reader. Reads name from `category_translations` sidecar.
 * Prefers `locale`; falls back to the default-language row when the requested
 * locale is absent. Returns all active categories (universal, not scoped to deal type).
 */
export async function listLocalisedCategories(
  db: DrizzleClient,
  locale: string,
): Promise<LocalisedCategory[]> {
  const defaultLocale = db
    .select({ code: languages.code })
    .from(languages)
    .where(eq(languages.isDefault, true))
    .limit(1);

  const req = db
    .$with('req')
    .as(
      db
        .select({ categoryId: categoryTranslations.categoryId, name: categoryTranslations.name })
        .from(categoryTranslations)
        .where(eq(categoryTranslations.locale, locale)),
    );

  const def = db.$with('def').as(
    db
      .select({ categoryId: categoryTranslations.categoryId, name: categoryTranslations.name })
      .from(categoryTranslations)
      .where(eq(categoryTranslations.locale, sql`(${defaultLocale})`)),
  );

  const rows = await db
    .with(req, def)
    .select({
      id: dealCategories.id,
      dealType: dealCategories.dealType,
      sortOrder: dealCategories.sortOrder,
      isActive: dealCategories.isActive,
      name: sql<string>`COALESCE(${req.name}, ${def.name})`,
    })
    .from(dealCategories)
    .leftJoin(req, eq(req.categoryId, dealCategories.id))
    .leftJoin(def, eq(def.categoryId, dealCategories.id))
    .where(eq(dealCategories.isActive, true))
    .orderBy(asc(dealCategories.sortOrder));

  return rows as LocalisedCategory[];
}

// ---------------------------------------------------------------------------
// Write queries — write to `deal_categories`/`deal_tags` parent + the two
// translation rows (he, en) inside a single transaction.
// ---------------------------------------------------------------------------

export async function createCategory(
  db: TxDrizzleClient,
  data: { nameHe: string; nameEn: string; sortOrder?: number; slug?: string },
) {
  const slug = await resolveCategorySlug(db, data.nameEn, data.nameHe, data.slug);
  const result = await db.transaction(async (tx) => {
    const [row] = await tx
      .insert(dealCategories)
      .values({ slug, dealType: 'COUPON', sortOrder: data.sortOrder })
      .returning();
    if (!row) throw new Error('createCategory: insert returned no row');

    await tx.insert(categoryTranslations).values([
      {
        categoryId: row.id,
        locale: 'he',
        name: data.nameHe,
        nameSourceHash: await sha256(data.nameHe),
        nameManualOverride: true,
      },
      {
        categoryId: row.id,
        locale: 'en',
        name: data.nameEn,
        nameSourceHash: await sha256(data.nameEn),
        nameManualOverride: true,
      },
    ]);

    return {
      id: row.id,
      dealType: row.dealType,
      sortOrder: row.sortOrder,
      isActive: row.isActive,
      createdAt: row.createdAt,
      nameHe: data.nameHe,
      nameEn: data.nameEn,
    };
  });
  await invalidateCatalog(db, { scope: 'category', categorySlug: slug });
  return result;
}

export async function updateCategory(
  db: TxDrizzleClient,
  id: string,
  data: { nameHe?: string; nameEn?: string; sortOrder?: number; isActive?: boolean; slug?: string },
) {
  let categorySlug: string | undefined;
  const result = await db.transaction(async (tx) => {
    const parentPatch: Partial<{ sortOrder: number; isActive: boolean; slug: string }> = {};
    if (data.sortOrder !== undefined) parentPatch.sortOrder = data.sortOrder;
    if (data.isActive !== undefined) parentPatch.isActive = data.isActive;
    if (data.slug !== undefined) {
      // Resolve slug through deduplication in case of conflict (excluding current row).
      const existing = await tx.execute<{ slug: string }>(
        sql`SELECT slug FROM deal_categories WHERE slug LIKE ${data.slug + '%'} AND id != ${id}`,
      );
      const taken = new Set<string>(executeRows<{ slug: string }>(existing).map((r) => r.slug));
      parentPatch.slug = dedupeSlug(data.slug, taken);
    }

    let row;
    if (Object.keys(parentPatch).length > 0) {
      const [r] = await tx
        .update(dealCategories)
        .set(parentPatch)
        .where(eq(dealCategories.id, id))
        .returning();
      row = r;
    } else {
      const [r] = await tx.select().from(dealCategories).where(eq(dealCategories.id, id)).limit(1);
      row = r;
    }
    if (!row) return null;
    categorySlug = row.slug ?? undefined;

    if (data.nameHe !== undefined) {
      await tx
        .insert(categoryTranslations)
        .values({
          categoryId: id,
          locale: 'he',
          name: data.nameHe,
          nameSourceHash: await sha256(data.nameHe),
          nameManualOverride: true,
        })
        .onConflictDoUpdate({
          target: [categoryTranslations.categoryId, categoryTranslations.locale],
          set: {
            name: data.nameHe,
            nameSourceHash: await sha256(data.nameHe),
            nameManualOverride: true,
            updatedAt: sql`now()`,
          },
        });
    }
    if (data.nameEn !== undefined) {
      await tx
        .insert(categoryTranslations)
        .values({
          categoryId: id,
          locale: 'en',
          name: data.nameEn,
          nameSourceHash: await sha256(data.nameEn),
          nameManualOverride: true,
        })
        .onConflictDoUpdate({
          target: [categoryTranslations.categoryId, categoryTranslations.locale],
          set: {
            name: data.nameEn,
            nameSourceHash: await sha256(data.nameEn),
            nameManualOverride: true,
            updatedAt: sql`now()`,
          },
        });
    }

    // Return canonical shape (re-resolve names from sidecar to reflect updates).
    const resolved = await tx
      .execute(
        sql`
      SELECT
        c.id,
        c.deal_type    AS "dealType",
        c.sort_order   AS "sortOrder",
        c.is_active    AS "isActive",
        c.created_at   AS "createdAt",
        COALESCE(he_t.name, he_fb.name) AS "nameHe",
        COALESCE(en_t.name, he_fb.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 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'
      WHERE c.id = ${id}
      LIMIT 1
    `,
      )
      .then((r) =>
        firstExecuteRow<{
          id: string;
          dealType: string;
          sortOrder: number;
          isActive: boolean;
          createdAt: Date;
          nameHe: string | null;
          nameEn: string | null;
        }>(r),
      );

    if (!resolved) return null;
    return {
      ...resolved,
      nameHe: resolved.nameHe ?? '',
      nameEn: resolved.nameEn ?? '',
    };
  });
  if (result) {
    if (categorySlug) {
      await invalidateCatalog(db, { scope: 'category', categorySlug });
    } else {
      await invalidateCatalog(db, { scope: 'global' });
    }
  }
  return result;
}

export async function createTag(
  db: TxDrizzleClient,
  data: { nameHe: string; nameEn: string; slug?: string; isActive?: boolean },
) {
  // Resolve slug: explicit → auto-generated → collision-dedupe
  let slug = data.slug;
  if (!slug) {
    const base =
      kebabSlug(data.nameEn, data.nameHe) || `tag-${Math.random().toString(36).slice(2, 8)}`;
    const existing = await db.execute<{ slug: string }>(
      sql`SELECT slug FROM deal_tags WHERE slug LIKE ${base + '%'}`,
    );
    const taken = new Set<string>(executeRows<{ slug: string }>(existing).map((r) => r.slug));
    slug = dedupeSlug(base, taken);
  } else {
    const clash = await db.execute(sql`SELECT 1 FROM deal_tags WHERE slug = ${slug} LIMIT 1`);
    if (executeRows(clash).length > 0) throw new Error(`SLUG_CONFLICT:${slug}`);
  }

  const result = await db.transaction(async (tx) => {
    const [row] = await tx
      .insert(dealTags)
      .values({ slug, isActive: data.isActive ?? true })
      .returning();
    if (!row) throw new Error('createTag: insert returned no row');

    await tx.insert(tagTranslations).values([
      {
        tagId: row.id,
        locale: 'he',
        name: data.nameHe,
        nameSourceHash: await sha256(data.nameHe),
        nameManualOverride: true,
      },
      {
        tagId: row.id,
        locale: 'en',
        name: data.nameEn,
        nameSourceHash: await sha256(data.nameEn),
        nameManualOverride: true,
      },
    ]);

    return {
      id: row.id,
      slug: row.slug,
      isActive: row.isActive,
      createdAt: row.createdAt,
      nameHe: data.nameHe,
      nameEn: data.nameEn,
    };
  });
  await invalidateCatalog(db, { scope: 'global' });
  return result;
}

export async function updateTag(
  db: TxDrizzleClient,
  id: string,
  data: { nameHe?: string; nameEn?: string; slug?: string; isActive?: boolean },
) {
  // Slug uniqueness check before entering transaction
  if (data.slug !== undefined) {
    const clash = await db.execute(
      sql`SELECT 1 FROM deal_tags WHERE slug = ${data.slug} AND id <> ${id} LIMIT 1`,
    );
    if (executeRows(clash).length > 0) throw new Error(`SLUG_CONFLICT:${data.slug}`);
  }

  const result = await db.transaction(async (tx) => {
    const parentPatch: Partial<{ slug: string; isActive: boolean }> = {};
    if (data.slug !== undefined) parentPatch.slug = data.slug;
    if (data.isActive !== undefined) parentPatch.isActive = data.isActive;

    let row;
    if (Object.keys(parentPatch).length > 0) {
      const [r] = await tx.update(dealTags).set(parentPatch).where(eq(dealTags.id, id)).returning();
      row = r;
    } else {
      const [r] = await tx.select().from(dealTags).where(eq(dealTags.id, id)).limit(1);
      row = r;
    }
    if (!row) return null;

    if (data.nameHe !== undefined) {
      await tx
        .insert(tagTranslations)
        .values({
          tagId: id,
          locale: 'he',
          name: data.nameHe,
          nameSourceHash: await sha256(data.nameHe),
          nameManualOverride: true,
        })
        .onConflictDoUpdate({
          target: [tagTranslations.tagId, tagTranslations.locale],
          set: {
            name: data.nameHe,
            nameSourceHash: await sha256(data.nameHe),
            nameManualOverride: true,
            updatedAt: sql`now()`,
          },
        });
    }
    if (data.nameEn !== undefined) {
      await tx
        .insert(tagTranslations)
        .values({
          tagId: id,
          locale: 'en',
          name: data.nameEn,
          nameSourceHash: await sha256(data.nameEn),
          nameManualOverride: true,
        })
        .onConflictDoUpdate({
          target: [tagTranslations.tagId, tagTranslations.locale],
          set: {
            name: data.nameEn,
            nameSourceHash: await sha256(data.nameEn),
            nameManualOverride: true,
            updatedAt: sql`now()`,
          },
        });
    }

    const resolved = await tx
      .execute(
        sql`
      SELECT
        t.id,
        t.slug,
        t.is_active    AS "isActive",
        t.created_at   AS "createdAt",
        COALESCE(he_t.name, he_fb.name) AS "nameHe",
        COALESCE(en_t.name, he_fb.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 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'
      WHERE t.id = ${id}
      LIMIT 1
    `,
      )
      .then((r) =>
        firstExecuteRow<{
          id: string;
          slug: string;
          isActive: boolean;
          createdAt: Date;
          nameHe: string | null;
          nameEn: string | null;
        }>(r),
      );

    if (!resolved) return null;
    return {
      ...resolved,
      nameHe: resolved.nameHe ?? '',
      nameEn: resolved.nameEn ?? '',
    };
  });
  if (result) {
    await invalidateCatalog(db, { scope: 'global' });
  }
  return result;
}

// ---------------------------------------------------------------------------
// Tag-assignment helpers — unchanged
// ---------------------------------------------------------------------------

export async function setDealTags(db: DrizzleClient, dealId: string, tagIds: string[]) {
  await db.delete(dealTagAssignments).where(eq(dealTagAssignments.dealId, dealId));
  if (tagIds.length > 0) {
    await db.insert(dealTagAssignments).values(tagIds.map((tagId) => ({ dealId, tagId })));
  }
  await invalidateCatalog(db, { scope: 'deal', dealId });
}

export async function setCategoryOnDeal(
  db: DrizzleClient,
  dealId: string,
  categoryId: string | null,
  tagIds: string[],
): Promise<void> {
  await db.update(deals).set({ categoryId }).where(eq(deals.id, dealId));
  await db.delete(dealTagAssignments).where(eq(dealTagAssignments.dealId, dealId));
  if (tagIds.length > 0) {
    await db.insert(dealTagAssignments).values(tagIds.map((tagId) => ({ dealId, tagId })));
  }
  await invalidateCatalog(db, { scope: 'deal', dealId });
}

export async function getDealTagIds(db: DrizzleClient, dealId: string): Promise<string[]> {
  const rows = await db
    .select({ tagId: dealTagAssignments.tagId })
    .from(dealTagAssignments)
    .where(eq(dealTagAssignments.dealId, dealId));
  return rows.map((r) => r.tagId);
}

export async function deleteCategoryIfEmpty(
  db: DrizzleClient,
  id: string,
): Promise<{ deleted: true } | { error: 'has_deals'; count: number } | { error: 'not_found' }> {
  const [row] = await db
    .select({ dealCount: count(deals.id) })
    .from(deals)
    .where(eq(deals.categoryId, id));
  const dealCount = row?.dealCount ?? 0;
  if (dealCount > 0) return { error: 'has_deals', count: dealCount };
  const [deleted] = await db
    .delete(dealCategories)
    .where(eq(dealCategories.id, id))
    .returning({ id: dealCategories.id });
  if (!deleted) return { error: 'not_found' };
  await invalidateCatalog(db, { scope: 'global' });
  return { deleted: true };
}

export async function deleteTagIfEmpty(
  db: DrizzleClient,
  id: string,
): Promise<
  { deleted: true } | { error: 'has_assignments'; count: number } | { error: 'not_found' }
> {
  const [row] = await db
    .select({ assignCount: count(dealTagAssignments.dealId) })
    .from(dealTagAssignments)
    .where(eq(dealTagAssignments.tagId, id));
  const assignCount = row?.assignCount ?? 0;
  if (assignCount > 0) return { error: 'has_assignments', count: assignCount };
  const [deleted] = await db
    .delete(dealTags)
    .where(eq(dealTags.id, id))
    .returning({ id: dealTags.id });
  if (!deleted) return { error: 'not_found' };
  await invalidateCatalog(db, { scope: 'global' });
  return { deleted: true };
}