/**
 * Axis-B cart migration from host cart_items to platform cart + cart_line.
 * Run: npx tsx apps/web/src/server/cart/cart-migration.ts
 *
 * Idempotent — all INSERTs use ON CONFLICT DO NOTHING.
 */
import { scriptOutput } from '../lib/script-output.js';
import { fileURLToPath } from 'url';
import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import { sql } from 'drizzle-orm';
import ws from 'ws';
import { pushSchema, cartSchema } from '@platform-modules/commerce-cart/store-db';
import type { DrizzleDb } from '@/server/db/client.js';
import { getOrCreateCartForUser, toCartDb } from '@/server/cart/cart-platform.js';
import * as hostSchema from '../db/schema.js';
import { asCartId, asUserId, toModuleRef } from '@/server/platform-seams/ids.js';

export async function runCartMigration(db: DrizzleDb): Promise<{ seeded: number }> {
  await pushSchema(toCartDb(db));

  await db.execute(sql`
    CREATE UNIQUE INDEX IF NOT EXISTS cart_line_cart_id_variant_id_key
    ON cart_line (cart_id, variant_id)
  `);
  await db.execute(sql`
    CREATE UNIQUE INDEX IF NOT EXISTS cart_user_id_key
    ON cart (user_id) WHERE user_id IS NOT NULL
  `);

  const usersResult = await db.execute(sql`
    SELECT DISTINCT user_id FROM cart_items WHERE user_id IS NOT NULL
  `);
  const userIds = usersResult.rows.map((row) => String((row as { user_id: string }).user_id));

  let seeded = 0;
  for (const userId of userIds) {
    const cartId = await getOrCreateCartForUser(db, userId);
    const cartRef = toModuleRef(asCartId(cartId));
    const userRef = toModuleRef(asUserId(userId));

    const insertResult = await db.execute(sql`
      INSERT INTO cart_line (cart_id, variant_id, qty, amount, currency, price_mode, vendor_id)
      SELECT ${cartRef}::uuid, ci.deal_sku_id, ci.qty,
             ROUND(dsk.discounted_price * 100)::bigint,
             'ILS', 'exclusive', d.vendor_id::text
      FROM cart_items ci
      INNER JOIN deal_skus dsk ON dsk.id = ci.deal_sku_id
      INNER JOIN deals d ON d.id = dsk.deal_id
      WHERE ci.user_id = ${userRef}::uuid
      ON CONFLICT (cart_id, variant_id) DO NOTHING
      RETURNING id
    `);

    const inserted = insertResult.rows.length;
    seeded += inserted;
    scriptOutput(`cart-migration: user ${userId} → cart ${cartId}, seeded ${inserted} lines`);
  }

  scriptOutput(`cart-migration: seeded ${seeded} cart_line rows across ${userIds.length} users`);

  return { seeded };
}

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 schema = { ...hostSchema, ...cartSchema };
  const db = drizzle(pool, { schema }) as unknown as DrizzleDb;

  try {
    await runCartMigration(db);
  } finally {
    await pool.end();
  }
}

if (process.argv[1] === fileURLToPath(import.meta.url)) {
  main()
    .then(() => process.exit(0))
    .catch((err) => {
      console.error('cart-migration failed:', err);
      process.exit(1);
    });
}