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)`);