la-rose/scripts/generate-seed-sql.ts

import { readFileSync, writeFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { CATEGORIES, readCatalog } from '../src/db/catalog';
import { imagePath, readImages } from '../src/db/images';
import { DEMO_PASSWORD_HASHES } from '../src/db/seed-accounts';
import { CONTENT_PAGES } from '../src/db/seed';
import { DELIVERY, STORE, STORE_CURRENCY } from '../src/store/constants';

/**
 * Emit a single idempotent SQL script (db/seed/0001_seed.sql) that mirrors
 * src/db/seed.ts. It is meant for the W4C Data Tables module (`/db-forms`,
 * DataTables Expert `run_sql`), which can apply it in one statement to the
 * platform `tenant_1` database without a Node process.
 *
 * The TypeScript seed remains the canonical source; run `pnpm db:sql` to refresh.
 */

const OUTPUT = fileURLToPath(new URL('../db/seed/0001_seed.sql', import.meta.url));
const GUARDSAFE = fileURLToPath(new URL('../db/seed/0001_seed.guardsafe.sql', import.meta.url));

/** Quote a value as a SQL literal. */
function q(value: string | null | undefined): string {
  if (value === null || value === undefined) return 'NULL';
  return `'${value.replace(/'/g, "''")}'`;
}

function qi(value: number | null): string {
  return value === null ? 'NULL' : String(value);
}

function occasionFor(categories: string[]): string {
  if (categories.includes('svadebnye-bukety-nevesty')) return 'Невесте';
  if (categories.includes('monobuket')) return 'Любимой';
  if (
    categories.includes('kompozitsii') ||
    categories.includes('tsvety-v-korzine') ||
    categories.includes('tsvety-v-yashchike')
  ) {
    return 'Коллеге, Партнёру';
  }
  return 'Любимой, Маме, Другу';
}

const products = readCatalog();
const images = readImages();
// Guard-safe constraints: no `ON CONFLICT`, no dotted aliases — plain VALUES rows.
const imageValues = images
  .map(
    (image) =>
      `  ((SELECT id FROM product WHERE slug = ${q(image.slug)}), ${q(imagePath(image.slug, image.position))}, ${q(image.alt)}, ${image.position})`,
  )
  .join(',\n');
const imageSection = [
  'INSERT INTO product_image (product_id, url, alt, position) VALUES',
  imageValues,
  ';',
];
const lines: string[] = [];

lines.push('-- ⚠️  GENERATED FILE — do not edit by hand.');
lines.push('-- Source: src/db/seed.ts + db/seed/catalog.tsv. Regenerate with `pnpm db:sql`.');
lines.push('-- Idempotent: safe to run repeatedly against the tenant_1 schema.');
lines.push('-- Currency: BYN; money is integer minor units (BYN * 100) — no floats (R2/R4).');
lines.push('BEGIN;');
lines.push('');

// 1. cleanup of stray demo / placeholder rows
lines.push('-- 1. clean up stray demo / placeholder rows');
lines.push(
  `DELETE FROM product WHERE slug = 'form-save-test' OR slug LIKE 'demo-%';`,
);
lines.push(
  `DELETE FROM category WHERE slug IN ('placeholder-category-11','placeholder-category','hidden-1','hidden-2','hidden-3');`,
);
lines.push('');

// 2. categories
lines.push('-- 2. categories: 11 visible + 3 hidden = 14 (R13)');
lines.push('INSERT INTO category (slug, name, description, position, visible) VALUES');
lines.push(
  CATEGORIES.map(
    (c) => `  (${q(c.slug)}, ${q(c.name)}, ${q(c.description)}, ${c.position}, ${c.visible})`,
  ).join(',\n'),
);
lines.push('ON CONFLICT (slug) DO UPDATE SET');
lines.push('  name = EXCLUDED.name, description = EXCLUDED.description,');
lines.push('  position = EXCLUDED.position, visible = EXCLUDED.visible;');
lines.push('');

// 3. catalog via a temp table, then a set-based upsert
lines.push('-- 3. products: 93 rows from docs/CATALOG.md §3 (R14)');
lines.push('CREATE TEMP TABLE _seed_catalog (');
lines.push('  slug text PRIMARY KEY, name text, description text, price_minor integer,');
lines.push('  old_price_minor integer, currency text, badge text, stock integer, status text,');
lines.push('  composition text, stems integer, height_cm integer, care text, delivery_time text,');
lines.push('  category_slugs text[], occasion text');
lines.push(') ON COMMIT DROP;');
lines.push('INSERT INTO _seed_catalog VALUES');
lines.push(
  products
    .map((p) => {
      const slugs = [...p.categories];
      if (/роз/i.test(p.name) && !slugs.includes('bukety-iz-roz')) slugs.push('bukety-iz-roz');
      const array = `ARRAY[${slugs.map((s) => q(s)).join(', ')}]::text[]`;
      return `  (${q(p.slug)}, ${q(p.name)}, ${q(p.description)}, ${p.priceMinor}, ${qi(p.oldPriceMinor)}, ${q(p.currency)}, ${q(p.badge)}, ${p.stock}, ${q(p.status)}, ${q(p.composition)}, ${qi(p.stems)}, ${p.heightCm}, ${q(p.care)}, ${q(p.deliveryTime)}, ${array}, ${q(occasionFor(p.categories))})`;
    })
    .join(',\n'),
);
lines.push(';');
lines.push('');
lines.push('INSERT INTO product');
lines.push('  (slug, name, description, price_minor, old_price_minor, currency, badge, stock,');
lines.push('   status, composition, stems, height_cm, care, delivery_time)');
lines.push('SELECT slug, name, description, price_minor, old_price_minor, currency, badge, stock,');
lines.push('       status, composition, stems, height_cm, care, delivery_time FROM _seed_catalog');
lines.push('ON CONFLICT (slug) DO UPDATE SET');
lines.push('  name = EXCLUDED.name, description = EXCLUDED.description,');
lines.push('  price_minor = EXCLUDED.price_minor, old_price_minor = EXCLUDED.old_price_minor,');
lines.push('  currency = EXCLUDED.currency, badge = EXCLUDED.badge, stock = EXCLUDED.stock,');
lines.push('  status = EXCLUDED.status, composition = EXCLUDED.composition, stems = EXCLUDED.stems,');
lines.push('  height_cm = EXCLUDED.height_cm, care = EXCLUDED.care, delivery_time = EXCLUDED.delivery_time;');
lines.push('');

// 4. cross-listing
lines.push('-- 4. cross-listing (ProductCategory)');
lines.push('DELETE FROM product_category pc USING _seed_catalog s, product p');
lines.push('  WHERE pc.product_id = p.id AND p.slug = s.slug;');
lines.push('INSERT INTO product_category (product_id, category_id, position)');
lines.push('SELECT p.id, c.id, ord::int');
lines.push('FROM _seed_catalog s');
lines.push('JOIN product p ON p.slug = s.slug');
lines.push('CROSS JOIN LATERAL unnest(s.category_slugs) WITH ORDINALITY AS u(slug, ord)');
lines.push('JOIN category c ON c.slug = u.slug');
lines.push('ON CONFLICT (product_id, category_id) DO UPDATE SET position = EXCLUDED.position;');
lines.push('');

// 5. images
lines.push('-- 5. images: 1–4 originals per product from db/seed/images.tsv (docs/PLAN.md §11)');
lines.push('DELETE FROM product_image pi USING _seed_catalog s, product p');
lines.push('  WHERE pi.product_id = p.id AND p.slug = s.slug;');
lines.push(...imageSection);
lines.push('');

// 6. features
lines.push('-- 6. features (PDP «Кому дарим цветы»)');
lines.push('INSERT INTO product_feature (product_id, key, value)');
lines.push("SELECT p.id, 'Кому дарим цветы', s.occasion");
lines.push('FROM _seed_catalog s JOIN product p ON p.slug = s.slug');
lines.push('ON CONFLICT (product_id, key) DO UPDATE SET value = EXCLUDED.value;');
lines.push('');

// 7. variants
lines.push('-- 7. variants (source PDP «Букет из красных роз с эвкалиптом»: 15/19/25 роз)');
lines.push('DELETE FROM product_variant pv USING _seed_catalog s, product p');
lines.push('  WHERE pv.product_id = p.id AND p.slug = s.slug;');
lines.push('INSERT INTO product_variant (product_id, label, price_minor, stock)');
lines.push('SELECT p.id, v.label, v.price_minor, v.stock');
lines.push("FROM (VALUES ('buket-iz-krasnykh-roz-s-evkaliptom','15 роз',19800,10),");
lines.push("             ('buket-iz-krasnykh-roz-s-evkaliptom','19 роз',24000,6),");
lines.push("             ('buket-iz-krasnykh-roz-s-evkaliptom','25 роз',32500,4)) AS v(slug,label,price_minor,stock)");
lines.push('JOIN product p ON p.slug = v.slug;');
lines.push('');

// 8. customers
lines.push('-- 8. seeded accounts (demo credentials, documented in README)');
const adminHash = DEMO_PASSWORD_HASHES.admin;
const customerHash = DEMO_PASSWORD_HASHES.customer;
lines.push('INSERT INTO customer (email, name, phone, password_hash, role) VALUES');
lines.push(`  (${q('admin@la-rose.by')}, ${q('Администратор')}, ${q('+375290000001')}, ${q(adminHash)}, 'admin'),`);
lines.push(`  (${q('customer@example.com')}, ${q('Иван Петров')}, ${q('+375290000002')}, ${q(customerHash)}, 'customer')`);
lines.push('ON CONFLICT (email) DO UPDATE SET');
lines.push('  name = EXCLUDED.name, phone = EXCLUDED.phone, role = EXCLUDED.role,');
lines.push('  password_hash = EXCLUDED.password_hash;');
lines.push('');

// 9. address
lines.push('-- 9. address for the demo customer');
lines.push("DELETE FROM address WHERE customer_id = (SELECT id FROM customer WHERE email = 'customer@example.com');");
lines.push('INSERT INTO address (customer_id, label, city, street, building, apartment, postal_code, is_default)');
lines.push("VALUES ((SELECT id FROM customer WHERE email = 'customer@example.com'),");
lines.push("        'Дом', 'Минск', 'ул. Независимости', '10', '25', '220030', true);");
lines.push('');

// 10. promo codes
lines.push('-- 10. promo codes');
lines.push('INSERT INTO promo_code (code, discount_percent, min_total_minor, active) VALUES');
lines.push("  ('ROSE10', 10, 0, true)");
lines.push('ON CONFLICT (code) DO UPDATE SET discount_percent = EXCLUDED.discount_percent,');
lines.push('  min_total_minor = EXCLUDED.min_total_minor, active = EXCLUDED.active;');
lines.push('INSERT INTO promo_code (code, discount_percent, min_total_minor, active, expires_at) VALUES');
lines.push("  ('LOVE20', 20, 10000, true, '2026-12-31T20:59:59Z')");
lines.push('ON CONFLICT (code) DO UPDATE SET discount_percent = EXCLUDED.discount_percent,');
lines.push('  min_total_minor = EXCLUDED.min_total_minor, active = EXCLUDED.active, expires_at = EXCLUDED.expires_at;');
lines.push('');

// 11. content pages — single source of truth: CONTENT_PAGES in src/db/seed.ts.
const pages: Array<[string, string, string]> = CONTENT_PAGES.map(({ slug, title, body }) => [
  slug,
  title,
  body,
]);
lines.push('-- 11. content pages (R11)');
lines.push('INSERT INTO content_page (slug, title, body, published) VALUES');
lines.push(pages.map((p) => `  (${q(p[0])}, ${q(p[1])}, ${q(p[2])}, true)`).join(',\n'));
lines.push('ON CONFLICT (slug) DO UPDATE SET title = EXCLUDED.title, body = EXCLUDED.body, published = true;');
lines.push('');

// 12. delivery options
lines.push('-- 12. delivery options (R7)');
lines.push('INSERT INTO delivery_option (id, name, price_minor, free_over_minor) VALUES');
lines.push(`  ('todoor', ${q('Доставка цветов по Минску')}, ${DELIVERY.courierFeeMinor}, ${DELIVERY.freeOverMinor}),`);
lines.push(`  ('pickup', ${q('Самовывоз из салона')}, 0, NULL)`);
lines.push('ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name,');
lines.push('  price_minor = EXCLUDED.price_minor, free_over_minor = EXCLUDED.free_over_minor;');
lines.push('');

// 13. orders + items + payments
lines.push('-- 13. two orders (>= 1 admin, >= 1 customer, >= 2 orders)');
lines.push("DELETE FROM order_item WHERE order_id = (SELECT id FROM orders WHERE number = 'LR-1001');");
lines.push("DELETE FROM payment WHERE order_id = (SELECT id FROM orders WHERE number = 'LR-1001');");
lines.push("DELETE FROM order_item WHERE order_id = (SELECT id FROM orders WHERE number = 'LR-1002');");
lines.push("DELETE FROM payment WHERE order_id = (SELECT id FROM orders WHERE number = 'LR-1002');");
lines.push('INSERT INTO orders');
lines.push('  (number, status, customer_id, address_id, subtotal_minor, shipping_minor, discount_minor,');
lines.push('   total_minor, currency, gift_note, delivery_date, delivery_time, promo_code_id) VALUES');
lines.push("  ('LR-1001', 'paid', (SELECT id FROM customer WHERE email = 'customer@example.com'),");
lines.push("   (SELECT id FROM address ORDER BY id DESC LIMIT 1), 19800, 0, 1980, 17820, 'BYN',");
lines.push("   'С днём рождения!', '2026-10-05', '18:00', (SELECT id FROM promo_code WHERE code = 'ROSE10')),");
lines.push("  ('LR-1002', 'pending', (SELECT id FROM customer WHERE email = 'customer@example.com'),");
lines.push("   (SELECT id FROM address ORDER BY id DESC LIMIT 1), 7100, 1500, 0, 8600, 'BYN',");
lines.push("   NULL, '2026-10-06', '12:00', NULL)");
lines.push('ON CONFLICT (number) DO UPDATE SET');
lines.push('  status = EXCLUDED.status, customer_id = EXCLUDED.customer_id, address_id = EXCLUDED.address_id,');
lines.push('  subtotal_minor = EXCLUDED.subtotal_minor, shipping_minor = EXCLUDED.shipping_minor,');
lines.push('  discount_minor = EXCLUDED.discount_minor, total_minor = EXCLUDED.total_minor,');
lines.push('  currency = EXCLUDED.currency, gift_note = EXCLUDED.gift_note,');
lines.push('  delivery_date = EXCLUDED.delivery_date, delivery_time = EXCLUDED.delivery_time,');
lines.push('  promo_code_id = EXCLUDED.promo_code_id;');
lines.push('INSERT INTO order_item (order_id, product_id, name, unit_price_minor, quantity) VALUES');
lines.push("  ((SELECT id FROM orders WHERE number = 'LR-1001'),");
lines.push("   (SELECT id FROM product WHERE slug = 'buket-iz-krasnykh-roz-s-evkaliptom'),");
lines.push("   'Букет из красных роз с эвкалиптом', 19800, 1),");
lines.push("  ((SELECT id FROM orders WHERE number = 'LR-1002'),");
lines.push("   (SELECT id FROM product WHERE slug = 'buket-nostalgiya'), '«Летний»', 7100, 1);");
lines.push('INSERT INTO payment (order_id, provider, provider_ref, status, amount_minor) VALUES');
lines.push("  ((SELECT id FROM orders WHERE number = 'LR-1001'), 'bepaid', 'PAY-DEMO-0001', 'succeeded', 17820),");
lines.push("  ((SELECT id FROM orders WHERE number = 'LR-1002'), 'cash', NULL, 'pending', 8600);");
lines.push('');

// 14. cart
lines.push('-- 14. demo cart');
lines.push('INSERT INTO cart (session_token, customer_id) VALUES');
lines.push("  ('demo-session-token-0001', (SELECT id FROM customer WHERE email = 'customer@example.com'))");
lines.push('ON CONFLICT (session_token) DO UPDATE SET customer_id = EXCLUDED.customer_id, updated_at = now();');
lines.push("DELETE FROM cart_item WHERE cart_id = (SELECT id FROM cart WHERE session_token = 'demo-session-token-0001');");
lines.push('INSERT INTO cart_item (cart_id, product_id, quantity, unit_price_minor) VALUES');
lines.push("  ((SELECT id FROM cart WHERE session_token = 'demo-session-token-0001'),");
lines.push("   (SELECT id FROM product WHERE slug = 'buket-nostalgiya'), 1, 7100),");
lines.push("  ((SELECT id FROM cart WHERE session_token = 'demo-session-token-0001'),");
lines.push("   (SELECT id FROM product WHERE slug = 'sovershenstvo'), 2, 12500);");
lines.push('');

lines.push('COMMIT;');
lines.push('');

writeFileSync(OUTPUT, lines.join('\n'), 'utf8');

/**
 * The guard-safe variant (used by the W4C `/db-forms` module) is hand-maintained,
 * but its image block must stay identical to the generated seed — /db-forms
 * rejects `ON CONFLICT` and dotted aliases. Replace just that block in place.
 */
function syncGuardsafeImages(): void {
  let text: string;
  try {
    text = readFileSync(GUARDSAFE, 'utf8');
  } catch {
    return;
  }
  const block = text.split('\n');
  const start = block.findIndex((line) => line.startsWith('-- 10. images'));
  const end = block.findIndex((line) => line.startsWith('-- 11.'));
  if (start < 0 || end <= start) return;
  block.splice(
    start,
    end - start,
    '-- 10. images: 1–4 originals per product from db/seed/images.tsv (docs/PLAN.md §11)',
    ...imageSection,
    '',
  );
  writeFileSync(GUARDSAFE, block.join('\n'), 'utf8');
  console.log(`  guard-safe images synced: ${GUARDSAFE} (${images.length} rows)`);
}

syncGuardsafeImages();

console.log(`generate-seed-sql: wrote ${OUTPUT} (${products.length} products, ${CATEGORIES.length} categories)`);
console.log(`  images: ${images.length} product_image rows (db/seed/images.tsv)`);
console.log(`  currency=${STORE_CURRENCY} (integer minor units, no floats)`);