/**
 * Axis-B catalog migration + one-time seed from existing deals data.
 * Run: npx tsx apps/web/src/server/catalog/push-and-seed.ts
 *
 * Idempotent — all INSERTs use ON CONFLICT DO NOTHING.
 */
import { scriptOutput } from '../lib/script-output.js';
import { pathToFileURL } from 'node:url';
import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import { and, eq, inArray, isNotNull, sql } from 'drizzle-orm';
import ws from 'ws';
import { pushSchema, type CatalogSchema } from '@platform-modules/commerce-catalog';
import { pushReviewsSchema } from '@platform-modules/commerce-reviews';
import type { Querier } from '@platform-modules/db';
import * as hostSchema from '../db/schema.js';
import { composedSchema } from '../db/schema-composed.js';
import {
  insertCategory,
  insertProduct,
  insertProductCategory,
  insertVariant,
  insertVariantPrice,
} from '../db/queries/catalog-push-and-seed.js';
import { toReviewsDb } from '../reviews/reviews-platform.js';

type ProductKind = 'voucher' | 'physical' | 'digital';
type ProductStatus = 'draft' | 'active' | 'archived';

function mapKind(row: typeof hostSchema.deals.$inferSelect): ProductKind {
  if (row.dealType === 'COUPON' || row.isVoucher) return 'voucher';
  if (row.isPhysical) return 'physical';
  return 'digital';
}

function mapStatus(dealState: string): ProductStatus {
  if (dealState === 'ACTIVE') return 'active';
  if (dealState === 'DRAFT') return 'draft';
  return 'archived';
}

/** Convert deal_skus numeric ILS price (e.g. "99.90") to agorot bigint. */
function toAgorot(price: string): bigint {
  const [whole, frac = ''] = price.split('.');
  const agorotFrac = (frac + '00').slice(0, 2);
  return BigInt(`${whole}${agorotFrac}`);
}

export async function main() {
  const DATABASE_URL = process.env['DATABASE_URL'];
  if (!DATABASE_URL) throw new Error('DATABASE_URL env var required');

  neonConfig.webSocketConstructor = ws;
  const pool = new Pool({ connectionString: DATABASE_URL });
  const fullSchema = composedSchema;
  const db = drizzle(pool, { schema: fullSchema });
  const catalogDb = db as unknown as Querier<CatalogSchema>;

  scriptOutput('push-and-seed: pushing catalog schema…');
  await pushSchema(catalogDb);

  scriptOutput('push-and-seed: pushing reviews schema…');
  await pushReviewsSchema(toReviewsDb(db));

  scriptOutput('push-and-seed: seeding STANDARD reviews…');
  const standardReviews = await db
    .select()
    .from(hostSchema.reviews)
    .where(
      and(
        eq(hostSchema.reviews.reviewType, 'STANDARD'),
        isNotNull(hostSchema.reviews.rating),
        isNotNull(hostSchema.reviews.orderLineId),
      ),
    );

  if (standardReviews.length > 0) {
    // Direct insert to avoid port call during seed
    // NOTE: we use raw SQL insert to match the platform schema
    for (const r of standardReviews) {
      await db.execute(sql`
        INSERT INTO review (id, product_id, user_id, purchase_id, vendor_id, rating, body, status, vendor_reply, created_at, updated_at)
        VALUES (
          ${r.id}::uuid,
          ${r.dealId}::text,
          ${r.userId}::text,
          ${r.orderLineId}::text,
          ${r.vendorId}::text,
          ${r.rating},
          ${r.body},
          CASE WHEN ${r.isVisible} THEN 'approved' ELSE 'rejected' END,
          ${r.vendorReply},
          ${r.createdAt},
          ${r.createdAt}
        )
        ON CONFLICT (user_id, product_id, purchase_id) DO NOTHING
      `);
    }
    scriptOutput(`push-and-seed: seeded ${standardReviews.length} STANDARD reviews`);
  }

  const allDeals = await db.select().from(hostSchema.deals);
  const allSkus = await db.select().from(hostSchema.dealSkus);
  const allCategories = await db.select().from(hostSchema.dealCategories);

  scriptOutput(
    `push-and-seed: seeding ${allDeals.length} products, ${allSkus.length} variants, ${allCategories.length} categories…`,
  );

  const dealIds = allDeals.map((d) => d.id);
  const categoryIds = allCategories.map((c) => c.id);

  // Prefetch he-preferred slugs/names in one query each; per-row selects over
  // serverless Neon made this seed take minutes.
  const slugRows = dealIds.length
    ? await db
        .select({
          dealId: hostSchema.dealTranslations.dealId,
          slug: hostSchema.dealTranslations.slug,
          locale: hostSchema.dealTranslations.locale,
        })
        .from(hostSchema.dealTranslations)
        .where(inArray(hostSchema.dealTranslations.dealId, dealIds))
    : [];
  const slugByDeal = new Map<string, string>();
  for (const r of slugRows) {
    if (!r.slug) continue;
    if (!slugByDeal.has(r.dealId) || r.locale === 'he') slugByDeal.set(r.dealId, r.slug);
  }

  const nameRows = categoryIds.length
    ? await db
        .select({
          categoryId: hostSchema.categoryTranslations.categoryId,
          name: hostSchema.categoryTranslations.name,
          locale: hostSchema.categoryTranslations.locale,
        })
        .from(hostSchema.categoryTranslations)
        .where(inArray(hostSchema.categoryTranslations.categoryId, categoryIds))
    : [];
  const nameByCategory = new Map<string, string>();
  for (const r of nameRows) {
    if (!r.name) continue;
    if (!nameByCategory.has(r.categoryId) || r.locale === 'he')
      nameByCategory.set(r.categoryId, r.name);
  }

  await insertCategory(
    db,
    allCategories.map((cat) => ({
      id: cat.id,
      name: nameByCategory.get(cat.id) ?? cat.slug,
      slug: cat.slug,
      vendorId: null,
    })),
  );

  await insertProduct(
    db,
    allDeals.map((deal) => ({
      id: deal.id,
      kind: mapKind(deal),
      vendorId: deal.vendorId,
      slug: slugByDeal.get(deal.id) ?? deal.id,
      title: deal.title,
      description: deal.description || undefined,
      status: mapStatus(deal.dealState),
      media: [],
      tags: [],
    })),
  );

  await insertProductCategory(
    db,
    allDeals
      .filter((deal) => deal.categoryId)
      .map((deal) => ({ productId: deal.id, categoryId: deal.categoryId! })),
  );

  await insertVariant(
    db,
    allSkus.map((sku) => ({
      id: sku.id,
      productId: sku.dealId,
      sku: sku.id,
      attributes: {},
    })),
  );

  await insertVariantPrice(
    db,
    allSkus.map((sku) => ({
      variantId: sku.id,
      currency: 'ILS',
      amount: toAgorot(sku.discountedPrice),
      priceMode: 'inclusive',
    })),
  );

  scriptOutput('push-and-seed: done');
  await pool.end();
}

if (process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href) {
  main()
    .then(() => process.exit(0))
    .catch((err) => {
      console.error('push-and-seed failed:', err);
      process.exit(1);
    });
}