import { createHash } from "node:crypto";
import { and, eq, isNull, sql } from "drizzle-orm";
import { db } from "@/db";
import { menuItemSbisLinks, menuItems, menuItemVariants, sbisCatalogItems, sbisCatalogSnapshots, sbisSyncState } from "@/db/schema";
import { getBotCatalog } from "@/lib/bot-api";

const SYNC_TTL_MS = 45 * 1000;
const SYNC_LOCK_ID = 481739;
const AUTO_ARCHIVE_AFTER_MISSES = 2;
export const SBIS_STALE_AFTER_MS = 3 * 60 * 1000;
let lastSyncAt = 0;
let syncInFlight: Promise<void> | null = null;

type SyncStatePatch = Partial<typeof sbisSyncState.$inferInsert>;

/** Persists freshness counters (§6.2) outside the projection transaction. */
async function touchSyncState(patch: SyncStatePatch) {
  if (!db) return;
  try {
    await db.insert(sbisSyncState).values({ id: 1, ...patch }).onConflictDoUpdate({ target: sbisSyncState.id, set: { ...patch, updatedAt: new Date() } });
  } catch {
    // Freshness counters are observability; never fail the sync for them.
  }
}

function sanitisedError(error: unknown) {
  if (error instanceof Error) return error.message.slice(0, 500);
  return String(error).slice(0, 500);
}

function isCompleteCatalog(value: Awaited<ReturnType<typeof getBotCatalog>>) {
  return Boolean(value && typeof value.updated_at === "string" && Array.isArray(value.categories) && value.categories.every((category) => category && Array.isArray(category.items)));
}

function snapshotId(catalog: Awaited<ReturnType<typeof getBotCatalog>>) {
  return createHash("sha256").update(JSON.stringify(catalog)).digest("hex").slice(0, 32);
}

/** Refreshes the local storefront projection from a validated bot catalog. */
export async function syncSbisCatalog() {
  if (!db || Date.now() - lastSyncAt < SYNC_TTL_MS) return;
  if (syncInFlight) return syncInFlight;

  syncInFlight = (async () => {
    const startedAt = Date.now();
    try {
      await touchSyncState({ lastAttemptAt: new Date() });
      const catalog = await getBotCatalog();
      if (!isCompleteCatalog(catalog)) throw new Error("Catalog response is not a complete snapshot");
      const id = snapshotId(catalog);
      const incoming = catalog.categories.flatMap((category) => category.items.map((item) => ({ ...item, categoryId: String(category.id), categoryName: category.name })));
      const identityTransitions = new Map<string, string>();
      for (const item of incoming) {
        if (item.position_id === undefined || item.position_id === null) continue;
        const positionId = String(item.position_id);
        const nomenclatureId = String(item.sbis_id);
        if (!positionId || positionId === nomenclatureId) continue;
        const existing = identityTransitions.get(positionId);
        if (existing && existing !== nomenclatureId) throw new Error(`Catalog position ${positionId} maps to multiple nomenclature ids`);
        identityTransitions.set(positionId, nomenclatureId);
      }
      const receivedAt = new Date();
      const freshness = { lastSuccessAt: receivedAt, lastDurationMs: Date.now() - startedAt, lastError: null, lastSnapshotId: id, sourceVersion: catalog.updated_at, itemsCount: incoming.length, categoriesCount: catalog.categories.length };
      const applied = await db.transaction(async (tx) => {
        // Transaction-level locks are released by PostgreSQL on this same
        // pinned connection. A session lock through the pool can be released
        // by a different connection and leak indefinitely.
        const [lock] = await tx.execute(sql`SELECT pg_try_advisory_xact_lock(${SYNC_LOCK_ID})`) as unknown as [{ pg_try_advisory_xact_lock?: boolean }];
        if (lock?.pg_try_advisory_xact_lock !== true) return false;
        const [knownSnapshot] = await tx.select({ id: sbisCatalogSnapshots.id }).from(sbisCatalogSnapshots).where(eq(sbisCatalogSnapshots.snapshotId, id)).limit(1);
        if (!knownSnapshot) {
          await tx.insert(sbisCatalogSnapshots).values({ snapshotId: id, receivedAt, sourceVersion: catalog.updated_at, isComplete: true, categoriesCount: catalog.categories.length, itemsCount: incoming.length, durationMs: Date.now() - startedAt }).onConflictDoNothing({ target: sbisCatalogSnapshots.snapshotId });
          for (const item of incoming) {
            const availabilityKnown = item.available !== null;
            const catalogFields = { categoryId: item.categoryId, categoryName: item.categoryName, name: item.name, description: item.description ?? null, imageUrl: item.image ?? null, priceKopecks: Math.round(item.price * 100), inCurrentPrice: true, missingSnapshotCount: 0, lastSeenAt: receivedAt, variantOf: item.variant_of ?? null, snapshotId: id, updatedAt: receivedAt };
            // `available=null` means SBIS did not return a stop-list. New
            // products remain orderable-but-unknown; existing products keep
            // the last successful availability projection instead of being
            // silently reset to available.
            const availabilityFields = availabilityKnown ? { stockLeft: item.stock_left ?? null, available: item.available !== false, unavailableText: item.unavailable_text ?? null } : {};
            await tx.insert(sbisCatalogItems).values({ sbisId: String(item.sbis_id), ...catalogFields, stockLeft: availabilityKnown ? item.stock_left ?? null : null, available: availabilityKnown ? item.available !== false : true, unavailableText: availabilityKnown ? item.unavailable_text ?? null : null }).onConflictDoUpdate({ target: sbisCatalogItems.sbisId, set: { ...catalogFields, ...availabilityFields } });
          }
          await tx.update(sbisCatalogItems).set({ inCurrentPrice: false, missingSnapshotCount: sql`${sbisCatalogItems.missingSnapshotCount} + 1`, updatedAt: receivedAt }).where(sql`${sbisCatalogItems.snapshotId} IS DISTINCT FROM ${id}`);
        }

        // Older bot versions exposed the price-list row Id as sbis_id.  The
        // bot now supplies the real nomenclature id plus that legacy value in
        // position_id. Preserve the audit trail by closing the old link and
        // creating a new one; abort on ambiguity instead of silently linking
        // a menu card to a different product.
        for (const [positionId, nomenclatureId] of identityTransitions) {
          const legacyLinks = await tx.select().from(menuItemSbisLinks).where(and(eq(menuItemSbisLinks.sbisId, positionId), isNull(menuItemSbisLinks.unlinkedAt)));
          if (!legacyLinks.length) continue;
          if (legacyLinks.length > 1) throw new Error(`Multiple active links for catalog position ${positionId}`);
          const [legacyLink] = legacyLinks;
          const targetLinks = await tx.select().from(menuItemSbisLinks).where(and(eq(menuItemSbisLinks.sbisId, nomenclatureId), isNull(menuItemSbisLinks.unlinkedAt)));
          if (targetLinks.some((link) => link.menuItemId !== legacyLink.menuItemId)) throw new Error(`Nomenclature ${nomenclatureId} is already linked to another menu item`);
          await tx.update(menuItemSbisLinks).set({ unlinkedAt: receivedAt, unlinkReason: "catalog_position_id_migrated" }).where(and(eq(menuItemSbisLinks.id, legacyLink.id), isNull(menuItemSbisLinks.unlinkedAt)));
          if (!targetLinks.length) {
            await tx.insert(menuItemSbisLinks).values({ menuItemId: legacyLink.menuItemId, sbisId: nomenclatureId, linkedBy: "catalog-identity-migration", localFieldsHash: legacyLink.localFieldsHash, sbisFieldsHash: legacyLink.sbisFieldsHash, lastPullAt: legacyLink.lastPullAt, lastPushAt: legacyLink.lastPushAt, lastResult: "catalog_identity_migrated", lastError: null });
          }
        }

        const missing = await tx.select({ menuItemId: menuItemSbisLinks.menuItemId, sbisId: menuItemSbisLinks.sbisId }).from(menuItemSbisLinks).innerJoin(sbisCatalogItems, eq(sbisCatalogItems.sbisId, menuItemSbisLinks.sbisId)).where(sql`${menuItemSbisLinks.unlinkedAt} IS NULL AND ${sbisCatalogItems.missingSnapshotCount} >= ${AUTO_ARCHIVE_AFTER_MISSES}`);
        for (const link of missing) {
          await tx.update(menuItems).set({ showOnWebsite: false, isAvailable: false, archivedAt: receivedAt, updatedAt: receivedAt }).where(eq(menuItems.id, link.menuItemId));
          await tx.update(menuItemVariants).set({ isAvailable: false, updatedAt: receivedAt }).where(eq(menuItemVariants.itemId, link.menuItemId));
          await tx.update(menuItemSbisLinks).set({ unlinkedAt: receivedAt, unlinkReason: "removed_from_sbis_price" }).where(eq(menuItemSbisLinks.menuItemId, link.menuItemId));
        }
        return true;
      });
      if (!applied) return;
      await touchSyncState(freshness);
      lastSyncAt = Date.now();
    } catch (error) {
      // Network/API failures leave the last successful projection untouched and
      // are throttled so a missing bot key cannot slow every page request.
      await touchSyncState({ lastAttemptAt: new Date(), lastDurationMs: Date.now() - startedAt, lastError: sanitisedError(error) });
      lastSyncAt = Date.now();
    } finally {
      syncInFlight = null;
    }
  })();
  return syncInFlight;
}