import { createHash } from "node:crypto";
import { eq, 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)));
}

/**
 * Contract §3: available=null means the stop-list is temporarily unavailable.
 * Only an explicit false blocks the storefront; unknown availability is left
 * to the bot, which queues ambiguous orders for manual moderation.
 */
function availability(item: { available: boolean | null }) {
  return item.available !== false;
}

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();
    let lockAcquired = false;
    try {
      const [lock] = await db.execute(sql`SELECT pg_try_advisory_lock(${SYNC_LOCK_ID})`) as unknown as [{ pg_try_advisory_lock?: boolean }];
      lockAcquired = lock?.pg_try_advisory_lock === true;
      if (!lockAcquired) return;

      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 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 [knownSnapshot] = await db.select({ id: sbisCatalogSnapshots.id }).from(sbisCatalogSnapshots).where(eq(sbisCatalogSnapshots.snapshotId, id)).limit(1);
      if (knownSnapshot) { await touchSyncState(freshness); lastSyncAt = Date.now(); return; }
      await db.transaction(async (tx) => {
        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) {
          await tx.insert(sbisCatalogItems).values({ sbisId: String(item.sbis_id), categoryId: item.categoryId, categoryName: item.categoryName, name: item.name, description: item.description ?? null, imageUrl: item.image ?? null, priceKopecks: Math.round(item.price * 100), stockLeft: item.stock_left ?? null, available: availability(item), inCurrentPrice: true, missingSnapshotCount: 0, lastSeenAt: receivedAt, unavailableText: item.unavailable_text ?? null, variantOf: item.variant_of ?? null, snapshotId: id }).onConflictDoUpdate({ target: sbisCatalogItems.sbisId, set: { categoryId: item.categoryId, categoryName: item.categoryName, name: item.name, description: item.description ?? null, imageUrl: item.image ?? null, priceKopecks: Math.round(item.price * 100), stockLeft: item.stock_left ?? null, available: availability(item), inCurrentPrice: true, missingSnapshotCount: 0, lastSeenAt: receivedAt, unavailableText: item.unavailable_text ?? null, variantOf: item.variant_of ?? null, snapshotId: id, updatedAt: receivedAt } });
        }
        await tx.update(sbisCatalogItems).set({ inCurrentPrice: false, missingSnapshotCount: sql`${sbisCatalogItems.missingSnapshotCount} + 1`, updatedAt: receivedAt }).where(sql`${sbisCatalogItems.snapshotId} IS DISTINCT FROM ${id}`);
        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));
        }
      });
      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 {
      if (lockAcquired) await db.execute(sql`SELECT pg_advisory_unlock(${SYNC_LOCK_ID})`).catch(() => undefined);
      syncInFlight = null;
    }
  })();
  return syncInFlight;
}