import { PGlite } from '@electric-sql/pglite';
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { readImages } from '../src/db/images';
import { migrate } from '../src/db/migrate';
import { seed } from '../src/db/seed';
import { STORE_CURRENCY } from '../src/store/constants';
import { toMinor, formatMinor } from '../src/store/money';
import { verifyImageArtifacts } from './verify-images';

/**
 * Verification harness for the data layer.
 *
 * Runs the exact same migrations + idempotent seed against an in-process Postgres
 * (PGlite) so the acceptance criteria can be checked on a clean database without a
 * running server:
 *   • migrate + seed run clean (twice, proving idempotency)
 *   • select count(*) from product           = 93
 *   • select count(*) from category          = 14
 *   • select count(*) from category visible  = 11
 *   • product_image covers 93 products, every url exists on disk (docs/PLAN.md §11)
 *   • money is integer BYN minor units, no float
 */

function assertEqual(actual: unknown, expected: unknown, label: string): void {
  if (actual !== expected) {
    throw new Error(`verify: ${label} = ${String(actual)}, expected ${String(expected)}`);
  }
}

const db = new PGlite();

// Run twice: the second pass proves the seed is idempotent.
await migrate(db);
const first = await seed(db);
await migrate(db);
const second = await seed(db);

const productCount = (await db.query<{ c: number }>('select count(*)::int as c from product')).rows[0].c;
const categoryCount = (await db.query<{ c: number }>('select count(*)::int as c from category')).rows[0].c;
const visibleCount = (await db.query<{ c: number }>('select count(*)::int as c from category where visible = true')).rows[0].c;
const hiddenCount = (await db.query<{ c: number }>('select count(*)::int as c from category where visible = false')).rows[0].c;
const images = readImages();
const imageCount = (await db.query<{ c: number }>('select count(*)::int as c from product_image')).rows[0].c;
const imageProducts = (await db.query<{ c: number }>('select count(distinct product_id)::int as c from product_image')).rows[0].c;
const productsWithMain = (await db.query<{ c: number }>(
  'select count(*)::int as c from (select product_id from product_image where position = 1 group by product_id) t',
)).rows[0].c;
const badImageUrls = (await db.query<{ c: number }>(
  `select count(*)::int as c from product_image pi join product p on p.id = pi.product_id
   where pi.url <> '/images/products/' || p.slug || '/' || pi.position || '.jpg'`,
)).rows[0].c;
const variantCount = (await db.query<{ c: number }>('select count(*)::int as c from product_variant')).rows[0].c;
const orderCount = (await db.query<{ c: number }>('select count(*)::int as c from orders')).rows[0].c;
const admins = (await db.query<{ c: number }>('select count(*)::int as c from customer where role = $1', ['admin'])).rows[0].c;
const customers = (await db.query<{ c: number }>('select count(*)::int as c from customer where role = $1', ['customer'])).rows[0].c;

// Money must be integer minor units in BYN — reject any non-integer / non-BYN row.
const badMoney = (await db.query<{ c: number }>(
  `select count(*)::int as c from product
   where currency <> $1
      or price_minor <> floor(price_minor)
      or (old_price_minor is not null and old_price_minor <> floor(old_price_minor))`,
  [STORE_CURRENCY],
)).rows[0].c;
const badOrderMoney = (await db.query<{ c: number }>(
  `select count(*)::int as c from orders
   where currency <> $1 or total_minor <> floor(total_minor) or subtotal_minor <> floor(subtotal_minor)`,
  [STORE_CURRENCY],
)).rows[0].c;

assertEqual(STORE_CURRENCY, 'BYN', 'STORE_CURRENCY');
assertEqual(first.products, 93, 'seed products (first run)');
assertEqual(second.products, 93, 'seed products (second run)');
assertEqual(productCount, 93, 'select count(*) from product');
assertEqual(categoryCount, 14, 'select count(*) from category');
assertEqual(visibleCount, 11, 'select count(*) from category where visible = true');
assertEqual(hiddenCount, 3, 'hidden categories');
assertEqual(imageCount, images.length, 'product_image rows = db/seed/images.tsv rows');
assertEqual(imageProducts, 93, 'product_image distinct products');
assertEqual(productsWithMain, 93, 'products with a position = 1 image');
assertEqual(badImageUrls, 0, 'product_image urls follow /images/products/<slug>/<n>.jpg');
assertEqual(variantCount, 3, 'product_variant (15/19/25 роз)');
assertEqual(orderCount, 2, 'orders');
assertEqual(admins >= 1, true, 'at least one admin');
assertEqual(customers >= 1, true, 'at least one customer');
assertEqual(badMoney, 0, 'non-BYN / non-integer product prices');
assertEqual(badOrderMoney, 0, 'non-BYN / non-integer order amounts');
assertEqual(toMinor(71), 7100, 'toMinor(71)');
assertEqual(formatMinor(7100).includes('71'), true, 'formatMinor(7100) contains 71');

// Same checks again, but through the generated single-file SQL seed
// (db/seed/0001_seed.sql) used by the W4C Data Tables module (`run_sql`).
const sqlDb = new PGlite();
await migrate(sqlDb);
const seedSql = readFileSync(fileURLToPath(new URL('../db/seed/0001_seed.sql', import.meta.url)), 'utf8');
await sqlDb.exec(seedSql);
const sqlProducts = (await sqlDb.query<{ c: number }>('select count(*)::int as c from product')).rows[0].c;
const sqlCategories = (await sqlDb.query<{ c: number }>('select count(*)::int as c from category')).rows[0].c;
const sqlVisible = (await sqlDb.query<{ c: number }>('select count(*)::int as c from category where visible = true')).rows[0].c;
const sqlImages = (await sqlDb.query<{ c: number }>('select count(*)::int as c from product_image')).rows[0].c;
assertEqual(sqlProducts, 93, 'generated SQL seed: product');
assertEqual(sqlCategories, 14, 'generated SQL seed: category');
assertEqual(sqlVisible, 11, 'generated SQL seed: visible category');
assertEqual(sqlImages, images.length, 'generated SQL seed: product_image rows');
await sqlDb.close();

// The guard-safe variant feeds the W4C /db-forms module — prove it runs and
// carries the same product_image rows, with no `ON CONFLICT` / dotted aliases.
const guardDb = new PGlite();
await migrate(guardDb);
const guardSql = readFileSync(fileURLToPath(new URL('../db/seed/0001_seed.guardsafe.sql', import.meta.url)), 'utf8');
await guardDb.exec(guardSql);
const guardImages = (await guardDb.query<{ c: number }>('select count(*)::int as c from product_image')).rows[0].c;
assertEqual(guardImages, images.length, 'guard-safe SQL seed: product_image rows');
await guardDb.close();

// File-side acceptance: images.tsv ⇄ public/images/products/** ⇄ guard-safe SQL.
const artifacts = verifyImageArtifacts();
assertEqual(artifacts.products, 93, 'gallery: 93 products in db/seed/images.tsv');
assertEqual(artifacts.productsWithMain, 93, 'gallery: 93 products with a main photo');
assertEqual(artifacts.missing.length, 0, 'gallery: every url exists as a file');
assertEqual(artifacts.rows, artifacts.files, 'gallery: rows = files on disk');
assertEqual(artifacts.extra.length, 0, 'gallery: no orphan image files');
assertEqual(artifacts.guardsafeHasAllRows, true, 'guard-safe seed carries every product_image row');
assertEqual(artifacts.guardsafeHasOnConflict, false, 'guard-safe seed has no ON CONFLICT');

console.log('verify: OK');
console.log(`  select count(*) from product                       = ${productCount}`);
console.log(`  select count(*) from category where visible = true = ${visibleCount} (total ${categoryCount}, hidden ${hiddenCount})`);
console.log(`  generated SQL seed (db/seed/0001_seed.sql)         = product ${sqlProducts}, category ${sqlCategories} (visible ${sqlVisible})`);
console.log(`  guard-safe SQL seed                                = product_image ${guardImages} (no ON CONFLICT)`);
console.log(`  product_image=${imageCount}  files=${artifacts.files} (${(artifacts.bytes / (1024 * 1024)).toFixed(1)} MiB)  products with main photo=${productsWithMain}`);
console.log(`  product_variant=${variantCount}  orders=${orderCount}  admins=${admins}  customers=${customers}`);
console.log(`  money: integer ${STORE_CURRENCY} minor units (0 float rows)`);

await db.close();