1085a54761
- Spalte umbenannt (idempotent ALTER TABLE) - FK-Constraint zu categories hinzugefügt - Seed befüllt kategorie_id per Kategoriename-Lookup (unabhängig von UUIDs) - Route prompt-styles.js angepasst Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
1129 lines
56 KiB
JavaScript
Executable File
1129 lines
56 KiB
JavaScript
Executable File
const { query } = require('./db');
|
||
|
||
async function migrateCore() {
|
||
// Rename _se → _sv (Swedish ISO 639-1 correction)
|
||
const renames = [
|
||
['words', 'titel_se', 'titel_sv'],
|
||
['categories', 'titel_se', 'titel_sv'],
|
||
['questions', 'sentence_se', 'sentence_sv'],
|
||
['statements', 'negative_sentence_se', 'negative_sentence_sv'],
|
||
['statements', 'positive_sentence_se', 'positive_sentence_sv'],
|
||
];
|
||
for (const [table, from, to] of renames) {
|
||
await query(`ALTER TABLE ${table} RENAME COLUMN ${from} TO ${to}`).catch(() => {});
|
||
}
|
||
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS pictures (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'uploaded'
|
||
CHECK (status IN ('uploaded', 'reviewed', 'published', 'blocked')),
|
||
blocked_reason VARCHAR(20) CHECK (blocked_reason IN ('regenerate', 'not_to_use')),
|
||
generation_prompt TEXT,
|
||
generation_timestamp TIMESTAMPTZ,
|
||
generation_duration_s NUMERIC(10,3),
|
||
published_timestamp TIMESTAMPTZ,
|
||
blocked_timestamp TIMESTAMPTZ,
|
||
blurhash TEXT,
|
||
picture_link TEXT,
|
||
design TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
CREATE OR REPLACE FUNCTION update_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
|
||
$$ LANGUAGE plpgsql
|
||
`);
|
||
|
||
await query(`ALTER TABLE pictures DROP CONSTRAINT IF EXISTS pictures_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD CONSTRAINT pictures_status_check CHECK (status IN ('uploaded', 'reviewed', 'published', 'blocked'))`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS pictures_updated_at ON pictures;
|
||
CREATE TRIGGER pictures_updated_at
|
||
BEFORE UPDATE ON pictures
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// words
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS words (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
titel_de TEXT,
|
||
titel_en TEXT,
|
||
titel_sv TEXT,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'requested'
|
||
CHECK (status IN ('requested', 'translated', 'generated', 'blocked', 'published')),
|
||
difficulty_level SMALLINT CHECK (difficulty_level BETWEEN 1 AND 50),
|
||
requested_at TIMESTAMPTZ,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS words_updated_at ON words;
|
||
CREATE TRIGGER words_updated_at
|
||
BEFORE UPDATE ON words
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// M2M: words <-> pictures
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS word_pictures (
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
picture_id UUID NOT NULL REFERENCES pictures(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (word_id, picture_id)
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS categories (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
titel_de TEXT,
|
||
titel_en TEXT,
|
||
titel_sv TEXT,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'requested'
|
||
CHECK (status IN ('requested', 'blocked', 'published')),
|
||
difficulty_level SMALLINT CHECK (difficulty_level BETWEEN 1 AND 50),
|
||
requested_at TIMESTAMPTZ,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
// Felder nachrüsten falls Tabelle schon als Platzhalter existiert
|
||
const catCols = ['titel_de TEXT', 'titel_en TEXT', 'titel_sv TEXT',
|
||
"status VARCHAR(20) NOT NULL DEFAULT 'requested'",
|
||
'difficulty_level SMALLINT', 'requested_at TIMESTAMPTZ',
|
||
'published_at TIMESTAMPTZ', 'blocked_at TIMESTAMPTZ'];
|
||
for (const col of catCols) {
|
||
const name = col.split(' ')[0];
|
||
await query(`ALTER TABLE categories ADD COLUMN IF NOT EXISTS ${col}`).catch(() => {});
|
||
// CHECK constraint nur wenn noch nicht vorhanden
|
||
if (name === 'status') {
|
||
await query(`ALTER TABLE categories DROP CONSTRAINT IF EXISTS categories_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE categories ADD CONSTRAINT categories_status_check CHECK (status IN ('requested', 'blocked', 'published'))`).catch(() => {});
|
||
}
|
||
}
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS categories_updated_at ON categories;
|
||
CREATE TRIGGER categories_updated_at
|
||
BEFORE UPDATE ON categories
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// M2M: words <-> categories
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS word_categories (
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (word_id, category_id)
|
||
)
|
||
`);
|
||
|
||
// Feste Alltags-Taxonomie seeden (de/en/sv, published). Basis für die automatische
|
||
// Wort-Kategorisierung (src/lib/classifyWords.js) und die Kategorie-Punkte im Profil.
|
||
// Idempotent: bestehende Kategorie (z. B. "Tiere") wird wiederverwendet, keine Dubletten.
|
||
const CATEGORY_TAXONOMY = [
|
||
['Lebensmittel', 'Food', 'Mat'],
|
||
['Tiere', 'Animals', 'Djur'],
|
||
['Körper', 'Body', 'Kropp'],
|
||
['Kleidung', 'Clothing', 'Kläder'],
|
||
['Familie & Menschen','Family & People', 'Familj & människor'],
|
||
['Beruf & Arbeit', 'Job & Work', 'Jobb & arbete'],
|
||
['Haushalt', 'Household', 'Hushåll'],
|
||
['Wohnen & Möbel', 'Home & Furniture', 'Hem & möbler'],
|
||
['Natur & Pflanzen', 'Nature & Plants', 'Natur & växter'],
|
||
['Wetter', 'Weather', 'Väder'],
|
||
['Verkehr & Reisen', 'Transport & Travel', 'Transport & resor'],
|
||
['Stadt & Gebäude', 'City & Buildings', 'Stad & byggnader'],
|
||
['Schule & Bildung', 'School & Education', 'Skola & utbildning'],
|
||
['Technik & Geräte', 'Technology & Devices','Teknik & apparater'],
|
||
['Sport & Freizeit', 'Sports & Leisure', 'Sport & fritid'],
|
||
['Gefühle', 'Emotions', 'Känslor'],
|
||
['Farben', 'Colors', 'Färger'],
|
||
['Zahlen & Zeit', 'Numbers & Time', 'Tal & tid'],
|
||
['Werkzeuge', 'Tools', 'Verktyg'],
|
||
['Eigenschaften', 'Properties', 'Egenskaper'],
|
||
['Verben & Handlungen','Verbs & Actions', 'Verb & handlingar'],
|
||
['Sonstiges', 'Other', 'Övrigt'],
|
||
];
|
||
for (const [de, en, sv] of CATEGORY_TAXONOMY) {
|
||
await query(
|
||
`INSERT INTO categories (titel_de, titel_en, titel_sv, status, requested_at, published_at)
|
||
SELECT $1, $2, $3, 'published', NOW(), NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE lower(titel_de) = lower($1))`,
|
||
[de, en, sv]
|
||
).catch(() => {});
|
||
}
|
||
// Bestehende Treffer auf published heben (z. B. die alte "Tiere"-Kategorie)
|
||
await query(
|
||
`UPDATE categories
|
||
SET status = 'published', published_at = COALESCE(published_at, NOW())
|
||
WHERE lower(titel_de) = ANY($1) AND status <> 'published'`,
|
||
[CATEGORY_TAXONOMY.map(([de]) => de.toLowerCase())]
|
||
).catch(() => {});
|
||
|
||
// Asynchroner Kategorisierungs-Batch (Message Batches API) — Status über Boots/Redeploys merken
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS category_batches (
|
||
batch_id TEXT PRIMARY KEY,
|
||
status TEXT NOT NULL DEFAULT 'submitted',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS questions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
CHECK (status IN ('draft', 'reviewed', 'blocked', 'published')),
|
||
sentence_de TEXT,
|
||
sentence_en TEXT,
|
||
sentence_sv TEXT,
|
||
blocked_topic TEXT,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
const questionCols = [
|
||
"status VARCHAR(20) NOT NULL DEFAULT 'draft'",
|
||
'sentence_de TEXT', 'sentence_en TEXT', 'sentence_sv TEXT',
|
||
'blocked_topic TEXT', 'published_at TIMESTAMPTZ', 'blocked_at TIMESTAMPTZ',
|
||
];
|
||
for (const col of questionCols)
|
||
await query(`ALTER TABLE questions ADD COLUMN IF NOT EXISTS ${col}`).catch(() => {});
|
||
await query(`ALTER TABLE questions DROP CONSTRAINT IF EXISTS questions_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE questions ADD CONSTRAINT questions_status_check CHECK (status IN ('draft', 'reviewed', 'blocked', 'published'))`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS questions_updated_at ON questions;
|
||
CREATE TRIGGER questions_updated_at
|
||
BEFORE UPDATE ON questions
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS statements (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
CHECK (status IN ('draft', 'reviewed', 'blocked', 'published')),
|
||
negative_sentence_de TEXT,
|
||
negative_sentence_en TEXT,
|
||
negative_sentence_sv TEXT,
|
||
positive_sentence_de TEXT,
|
||
positive_sentence_en TEXT,
|
||
positive_sentence_sv TEXT,
|
||
blocked_topic TEXT,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
const stmtCols = [
|
||
"status VARCHAR(20) NOT NULL DEFAULT 'draft'",
|
||
'negative_sentence_de TEXT', 'negative_sentence_en TEXT', 'negative_sentence_sv TEXT',
|
||
'positive_sentence_de TEXT', 'positive_sentence_en TEXT', 'positive_sentence_sv TEXT',
|
||
'blocked_topic TEXT', 'published_at TIMESTAMPTZ', 'blocked_at TIMESTAMPTZ',
|
||
];
|
||
for (const col of stmtCols)
|
||
await query(`ALTER TABLE statements ADD COLUMN IF NOT EXISTS ${col}`).catch(() => {});
|
||
await query(`ALTER TABLE statements DROP CONSTRAINT IF EXISTS statements_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE statements ADD CONSTRAINT statements_status_check CHECK (status IN ('draft', 'reviewed', 'blocked', 'published'))`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS statements_updated_at ON statements;
|
||
CREATE TRIGGER statements_updated_at
|
||
BEFORE UPDATE ON statements
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// M2M: statements <-> words (positive)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS statement_positive_words (
|
||
statement_id UUID NOT NULL REFERENCES statements(id) ON DELETE CASCADE,
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (statement_id, word_id)
|
||
)
|
||
`);
|
||
|
||
// M2M: statements <-> words (negative)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS statement_negative_words (
|
||
statement_id UUID NOT NULL REFERENCES statements(id) ON DELETE CASCADE,
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (statement_id, word_id)
|
||
)
|
||
`);
|
||
|
||
// pairs
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS pairs (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
CHECK (status IN ('draft', 'reviewed', 'blocked', 'published')),
|
||
difficulty_level SMALLINT CHECK (difficulty_level BETWEEN 1 AND 50),
|
||
answer_type VARCHAR(20) NOT NULL
|
||
CHECK (answer_type IN ('yes_no', 'text', 'word')),
|
||
blocked_topic TEXT,
|
||
question_id UUID REFERENCES questions(id) ON DELETE SET NULL,
|
||
positive_statement_id UUID REFERENCES statements(id) ON DELETE SET NULL,
|
||
negative_statement_id UUID REFERENCES statements(id) ON DELETE SET NULL,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
// Spalten nachrüsten falls Platzhalter-Tabelle bereits existiert
|
||
const pairCols = [
|
||
"status VARCHAR(20) NOT NULL DEFAULT 'draft'",
|
||
'difficulty_level SMALLINT',
|
||
"answer_type VARCHAR(20)",
|
||
'blocked_topic TEXT',
|
||
'question_id UUID',
|
||
'positive_statement_id UUID',
|
||
'negative_statement_id UUID',
|
||
'published_at TIMESTAMPTZ',
|
||
'blocked_at TIMESTAMPTZ',
|
||
];
|
||
for (const col of pairCols) {
|
||
const name = col.split(' ')[0];
|
||
await query(`ALTER TABLE pairs ADD COLUMN IF NOT EXISTS ${col}`).catch(() => {});
|
||
}
|
||
await query(`ALTER TABLE pairs DROP CONSTRAINT IF EXISTS pairs_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT pairs_status_check CHECK (status IN ('draft', 'reviewed', 'blocked', 'published'))`).catch(() => {});
|
||
await query(`ALTER TABLE pairs DROP CONSTRAINT IF EXISTS pairs_answer_type_check`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT pairs_answer_type_check CHECK (answer_type IN ('yes_no', 'text', 'word'))`).catch(() => {});
|
||
await query(`ALTER TABLE pairs DROP CONSTRAINT IF EXISTS pairs_difficulty_level_check`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT pairs_difficulty_level_check CHECK (difficulty_level BETWEEN 1 AND 50)`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT IF NOT EXISTS pairs_question_id_fkey FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE SET NULL`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT IF NOT EXISTS pairs_positive_statement_id_fkey FOREIGN KEY (positive_statement_id) REFERENCES statements(id) ON DELETE SET NULL`).catch(() => {});
|
||
await query(`ALTER TABLE pairs ADD CONSTRAINT IF NOT EXISTS pairs_negative_statement_id_fkey FOREIGN KEY (negative_statement_id) REFERENCES statements(id) ON DELETE SET NULL`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS pairs_updated_at ON pairs;
|
||
CREATE TRIGGER pairs_updated_at
|
||
BEFORE UPDATE ON pairs
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// objects
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS objects (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
CHECK (status IN ('draft', 'reviewed', 'blocked', 'published')),
|
||
selections JSONB,
|
||
notes TEXT,
|
||
blocked_topic TEXT,
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`ALTER TABLE objects DROP CONSTRAINT IF EXISTS objects_status_check`).catch(() => {});
|
||
await query(`ALTER TABLE objects ADD CONSTRAINT objects_status_check CHECK (status IN ('draft', 'reviewed', 'blocked', 'published'))`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS objects_updated_at ON objects;
|
||
CREATE TRIGGER objects_updated_at
|
||
BEFORE UPDATE ON objects
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// M2M: objects <-> words
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS object_words (
|
||
object_id UUID NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (object_id, word_id)
|
||
)
|
||
`);
|
||
|
||
// M2M: objects <-> pictures
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS object_pictures (
|
||
object_id UUID NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
|
||
picture_id UUID NOT NULL REFERENCES pictures(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (object_id, picture_id)
|
||
)
|
||
`);
|
||
|
||
// Bounding-box columns for object highlights (percentage 0–1 of image size)
|
||
await query(`ALTER TABLE object_pictures ADD COLUMN IF NOT EXISTS bbox_x FLOAT`);
|
||
await query(`ALTER TABLE object_pictures ADD COLUMN IF NOT EXISTS bbox_y FLOAT`);
|
||
await query(`ALTER TABLE object_pictures ADD COLUMN IF NOT EXISTS bbox_w FLOAT`);
|
||
await query(`ALTER TABLE object_pictures ADD COLUMN IF NOT EXISTS bbox_h FLOAT`);
|
||
|
||
// M2M: objects <-> pairs (Platzhalter)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS object_pairs (
|
||
object_id UUID NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
|
||
pair_id UUID NOT NULL REFERENCES pairs(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (object_id, pair_id)
|
||
)
|
||
`);
|
||
|
||
// M2M: pairs <-> categories — abgeleitet aus den verknüpften Wörtern (Statements + Objekte).
|
||
// Wird beim Publish materialisiert (src/lib/pairCategories.js). Basis für die Kategorie-Punkte im Profil.
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS pair_categories (
|
||
pair_id UUID NOT NULL REFERENCES pairs(id) ON DELETE CASCADE,
|
||
category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (pair_id, category_id)
|
||
)
|
||
`);
|
||
|
||
// Backfill: Kategorien für bereits veröffentlichte Pairs ableiten. Idempotent (ON CONFLICT DO NOTHING),
|
||
// nach dem Erstlauf praktisch leer, da neue Pairs ihre Kategorien beim Publish selbst materialisieren.
|
||
await query(`
|
||
INSERT INTO pair_categories (pair_id, category_id)
|
||
SELECT DISTINCT pid, category_id FROM (
|
||
SELECT p.id AS pid, wc.category_id
|
||
FROM pairs p
|
||
JOIN (
|
||
SELECT statement_id, word_id FROM statement_positive_words
|
||
UNION
|
||
SELECT statement_id, word_id FROM statement_negative_words
|
||
) sw ON sw.statement_id IN (p.positive_statement_id, p.negative_statement_id)
|
||
JOIN word_categories wc ON wc.word_id = sw.word_id
|
||
WHERE p.status = 'published'
|
||
UNION
|
||
SELECT op.pair_id AS pid, wc.category_id
|
||
FROM object_pairs op
|
||
JOIN pairs p2 ON p2.id = op.pair_id AND p2.status = 'published'
|
||
JOIN object_words ow ON ow.object_id = op.object_id
|
||
JOIN word_categories wc ON wc.word_id = ow.word_id
|
||
) src
|
||
WHERE category_id IS NOT NULL
|
||
ON CONFLICT (pair_id, category_id) DO NOTHING
|
||
`).catch(() => {});
|
||
|
||
// pairs.answer_type → single TEXT (was TEXT[], now back to single value + new 'question' type)
|
||
await query(`ALTER TABLE pairs DROP CONSTRAINT IF EXISTS pairs_answer_type_check`).catch(() => {});
|
||
await query(`
|
||
ALTER TABLE pairs ALTER COLUMN answer_type TYPE TEXT
|
||
USING (CASE
|
||
WHEN answer_type IS NULL OR array_length(answer_type::TEXT[], 1) IS NULL THEN 'text'
|
||
ELSE (answer_type::TEXT[])[1]
|
||
END)
|
||
`).catch(() => {});
|
||
await query(`
|
||
ALTER TABLE pairs ADD CONSTRAINT pairs_answer_type_check
|
||
CHECK (answer_type IN ('yes_no', 'text', 'question', 'word'))
|
||
`).catch(() => {});
|
||
|
||
// statements.answer — boolean nullable (for yes/no correct answer)
|
||
await query(`ALTER TABLE statements ADD COLUMN IF NOT EXISTS answer BOOLEAN`);
|
||
|
||
// objects_created on pictures
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS objects_created BOOLEAN NOT NULL DEFAULT false`);
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS objects_created_at TIMESTAMPTZ`);
|
||
|
||
// language_native_id on users
|
||
await query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS language_native_id UUID REFERENCES languages(id) ON DELETE SET NULL`);
|
||
|
||
// Seed German language (idempotent)
|
||
await query(`
|
||
INSERT INTO languages (titel_en, titel_de, titel_sv, short_en, status)
|
||
SELECT 'German', 'Deutsch', 'Tyska', 'de', 'published'
|
||
WHERE NOT EXISTS (SELECT 1 FROM languages WHERE short_en = 'de')
|
||
`);
|
||
|
||
// Set all users without a native language to German
|
||
await query(`
|
||
UPDATE users SET language_native_id = (SELECT id FROM languages WHERE short_en = 'de' LIMIT 1)
|
||
WHERE language_native_id IS NULL
|
||
`);
|
||
|
||
// blocklist
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS blocklist (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
is_blocked BOOLEAN NOT NULL DEFAULT true,
|
||
username TEXT,
|
||
email TEXT,
|
||
phone TEXT,
|
||
ip INET,
|
||
blocked_at TIMESTAMPTZ,
|
||
unblocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS blocklist_updated_at ON blocklist;
|
||
CREATE TRIGGER blocklist_updated_at
|
||
BEFORE UPDATE ON blocklist
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
await query(`CREATE INDEX IF NOT EXISTS blocklist_email_idx ON blocklist (lower(email)) WHERE email IS NOT NULL`);
|
||
await query(`CREATE INDEX IF NOT EXISTS blocklist_username_idx ON blocklist (lower(username)) WHERE username IS NOT NULL`);
|
||
await query(`CREATE INDEX IF NOT EXISTS blocklist_phone_idx ON blocklist (phone) WHERE phone IS NOT NULL`);
|
||
await query(`CREATE INDEX IF NOT EXISTS blocklist_ip_idx ON blocklist (ip) WHERE ip IS NOT NULL`);
|
||
|
||
// users
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
email TEXT NOT NULL UNIQUE,
|
||
password_hash TEXT NOT NULL,
|
||
role VARCHAR(20) NOT NULL DEFAULT 'end-user'
|
||
CHECK (role IN ('end-user', 'admin')),
|
||
is_active BOOLEAN NOT NULL DEFAULT true,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`CREATE UNIQUE INDEX IF NOT EXISTS users_email_idx ON users (lower(email))`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS users_updated_at ON users;
|
||
CREATE TRIGGER users_updated_at
|
||
BEFORE UPDATE ON users
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// languages
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS languages (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
titel_en TEXT,
|
||
titel_de TEXT,
|
||
titel_sv TEXT,
|
||
short_en VARCHAR(10),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
CHECK (status IN ('draft', 'blocked', 'published')),
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
blocked_topic TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS languages_updated_at ON languages;
|
||
CREATE TRIGGER languages_updated_at
|
||
BEFORE UPDATE ON languages
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// Begrüßung pro Sprache (in der Sprache selbst, z. B. sv = "Hej") — für die persönliche Profil-Anrede
|
||
await query(`ALTER TABLE languages ADD COLUMN IF NOT EXISTS greeting TEXT`).catch(() => {});
|
||
|
||
// user_names
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS user_names (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
username_lowercase TEXT NOT NULL UNIQUE,
|
||
username TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`CREATE UNIQUE INDEX IF NOT EXISTS user_names_lowercase_idx ON user_names (username_lowercase)`);
|
||
|
||
// users_public
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS users_public (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
username_id UUID REFERENCES user_names(id) ON DELETE SET NULL,
|
||
language_native_id UUID REFERENCES languages(id) ON DELETE SET NULL,
|
||
language_target_id UUID REFERENCES languages(id) ON DELETE SET NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS users_public_updated_at ON users_public;
|
||
CREATE TRIGGER users_public_updated_at
|
||
BEFORE UPDATE ON users_public
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// Link users_public ↔ users (1:1, app-profile per auth user)
|
||
await query(`ALTER TABLE users_public ADD COLUMN IF NOT EXISTS user_id UUID`).catch(() => {});
|
||
await query(`ALTER TABLE users_public ADD CONSTRAINT users_public_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE`).catch(() => {});
|
||
await query(`CREATE UNIQUE INDEX IF NOT EXISTS users_public_user_id_idx ON users_public (user_id) WHERE user_id IS NOT NULL`);
|
||
|
||
// Gamification: EP-Total, Streak, letzter Übungstag
|
||
await query(`ALTER TABLE users_public ADD COLUMN IF NOT EXISTS total_ep INTEGER NOT NULL DEFAULT 0`).catch(() => {});
|
||
await query(`ALTER TABLE users_public ADD COLUMN IF NOT EXISTS streak_days INTEGER NOT NULL DEFAULT 0`).catch(() => {});
|
||
await query(`ALTER TABLE users_public ADD COLUMN IF NOT EXISTS last_practice_at TIMESTAMPTZ`).catch(() => {});
|
||
|
||
// Seed languages (de exists, add en + sv)
|
||
// Full unique constraint (not partial) so ON CONFLICT works cleanly
|
||
await query(`CREATE UNIQUE INDEX IF NOT EXISTS languages_short_en_idx ON languages (short_en)`).catch(() => {});
|
||
await query(`
|
||
INSERT INTO languages (short_en, titel_de, titel_en, titel_sv, greeting, status, published_at)
|
||
VALUES
|
||
('en', 'Englisch', 'English', 'Engelska', 'Hi', 'published', NOW()),
|
||
('sv', 'Schwedisch', 'Swedish', 'Svenska', 'Hej', 'published', NOW())
|
||
ON CONFLICT (short_en) DO UPDATE SET
|
||
status = EXCLUDED.status,
|
||
published_at = COALESCE(languages.published_at, EXCLUDED.published_at),
|
||
greeting = COALESCE(languages.greeting, EXCLUDED.greeting)
|
||
`).catch(() => {});
|
||
// Begrüßung robust nachtragen (das ON-CONFLICT-Update oben greift bei bereits
|
||
// existierenden en/sv-Zeilen nicht zuverlässig → hier explizit, idempotent).
|
||
await query(`
|
||
UPDATE languages
|
||
SET greeting = CASE short_en
|
||
WHEN 'de' THEN 'Hallo'
|
||
WHEN 'en' THEN 'Hi'
|
||
WHEN 'sv' THEN 'Hej'
|
||
END
|
||
WHERE short_en IN ('de', 'en', 'sv') AND greeting IS NULL
|
||
`).catch(() => {});
|
||
|
||
// Seed bbox for watermelon test object (only if bbox_x is still NULL)
|
||
await query(`
|
||
UPDATE object_pictures
|
||
SET bbox_x = 0.08, bbox_y = 0.10, bbox_w = 0.78, bbox_h = 0.76
|
||
WHERE object_id = '67a609af-55c9-4560-ba63-c8ef93429ec0'
|
||
AND picture_id = 'fa776286-1df1-4b47-a29c-fc6e83e6e2da'
|
||
AND bbox_x IS NULL
|
||
`).catch(() => {});
|
||
|
||
// user_pair_progress
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS user_pair_progress (
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
pair_id UUID NOT NULL REFERENCES pairs(id) ON DELETE CASCADE,
|
||
seen_count INTEGER NOT NULL DEFAULT 1,
|
||
correct_count INTEGER NOT NULL DEFAULT 0,
|
||
wrong_count INTEGER NOT NULL DEFAULT 0,
|
||
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (user_id, pair_id)
|
||
)
|
||
`);
|
||
|
||
await query(`ALTER TABLE user_pair_progress ADD COLUMN IF NOT EXISTS earned_points INTEGER NOT NULL DEFAULT 0`).catch(() => {});
|
||
|
||
await query(`
|
||
CREATE OR REPLACE FUNCTION update_last_seen_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN NEW.last_seen_at = NOW(); RETURN NEW; END;
|
||
$$ LANGUAGE plpgsql
|
||
`);
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS user_pair_progress_last_seen_at ON user_pair_progress;
|
||
CREATE TRIGGER user_pair_progress_last_seen_at
|
||
BEFORE UPDATE ON user_pair_progress
|
||
FOR EACH ROW EXECUTE FUNCTION update_last_seen_at()
|
||
`);
|
||
|
||
// user_daily_activity — Tagesverlauf für Streak-Kalender, Wochengraph, Tagesziel
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS user_daily_activity (
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
activity_date DATE NOT NULL,
|
||
ep_earned INTEGER NOT NULL DEFAULT 0,
|
||
cards_done INTEGER NOT NULL DEFAULT 0,
|
||
correct_count INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (user_id, activity_date)
|
||
)
|
||
`);
|
||
|
||
// Tagesziel (EP/Tag) auf dem App-Profil
|
||
await query(`ALTER TABLE users_public ADD COLUMN IF NOT EXISTS daily_goal_ep INTEGER NOT NULL DEFAULT 30`).catch(() => {});
|
||
|
||
// Freigeschaltete Erfolge je User (ein Eintrag pro Erfolg, dedup-sicher)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS user_achievements (
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
achievement_key VARCHAR(40) NOT NULL,
|
||
unlocked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (user_id, achievement_key)
|
||
)
|
||
`);
|
||
|
||
// audios
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS audios (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
status VARCHAR(20) NOT NULL DEFAULT 'generated'
|
||
CHECK (status IN ('generated', 'published', 'blocked')),
|
||
text TEXT,
|
||
audio_link TEXT,
|
||
alignment JSONB,
|
||
voice_id TEXT,
|
||
model_id TEXT,
|
||
speed NUMERIC(4,2),
|
||
stability NUMERIC(4,2),
|
||
similarity_boost NUMERIC(4,2),
|
||
style NUMERIC(4,2),
|
||
published_at TIMESTAMPTZ,
|
||
blocked_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
// Verknüpfung Audio → Quelle (Wort/Frage/Statement) + Sprache.
|
||
// source_field: 'titel' | 'sentence' | 'positive_sentence' | 'negative_sentence'
|
||
await query(`ALTER TABLE audios ADD COLUMN IF NOT EXISTS source_table TEXT`).catch(() => {});
|
||
await query(`ALTER TABLE audios ADD COLUMN IF NOT EXISTS source_id UUID`).catch(() => {});
|
||
await query(`ALTER TABLE audios ADD COLUMN IF NOT EXISTS source_field TEXT`).catch(() => {});
|
||
await query(`ALTER TABLE audios ADD COLUMN IF NOT EXISTS language VARCHAR(10)`).catch(() => {});
|
||
await query(`
|
||
CREATE UNIQUE INDEX IF NOT EXISTS audios_source_uq
|
||
ON audios (source_table, source_id, source_field, language)
|
||
WHERE source_table IS NOT NULL
|
||
`).catch(() => {});
|
||
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS audios_updated_at ON audios;
|
||
CREATE TRIGGER audios_updated_at
|
||
BEFORE UPDATE ON audios
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// tts_settings — Stimme + Parameter pro Sprache (zentral konfigurierbar)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS tts_settings (
|
||
language VARCHAR(10) PRIMARY KEY,
|
||
voice_id TEXT NOT NULL,
|
||
model_id TEXT NOT NULL DEFAULT 'eleven_multilingual_v2',
|
||
speed NUMERIC(4,2) NOT NULL DEFAULT 1.0,
|
||
stability NUMERIC(4,2) NOT NULL DEFAULT 0.5,
|
||
similarity_boost NUMERIC(4,2) NOT NULL DEFAULT 0.75,
|
||
style NUMERIC(4,2) NOT NULL DEFAULT 0.0,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS tts_settings_updated_at ON tts_settings;
|
||
CREATE TRIGGER tts_settings_updated_at
|
||
BEFORE UPDATE ON tts_settings
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
// Seed-Stimmen (nur einfügen wenn fehlt — manuelle Änderungen bleiben erhalten)
|
||
await query(`
|
||
INSERT INTO tts_settings (language, voice_id) VALUES
|
||
('de', 'rKiu7lQ4c5P3az3745s3'),
|
||
('en', 'cVd39cx0VtXNC13y5Y7z'),
|
||
('sv', 'XB0fDUnXU5powFXDhCwa')
|
||
ON CONFLICT (language) DO NOTHING
|
||
`).catch(() => {});
|
||
// Defekte sv-Seed-Voice ersetzen: 'XXCqsM8I9KhqA7jLGj1U' existiert bei ElevenLabs nicht
|
||
// (voice_not_found) — dadurch schlug jede schwedische Audio-Generierung fehl.
|
||
// 'XB0fDUnXU5powFXDhCwa' = Premade-Voice "Charlotte" (schwedischer Akzent), in jedem Account verfügbar.
|
||
await query(`
|
||
UPDATE tts_settings SET voice_id = 'XB0fDUnXU5powFXDhCwa'
|
||
WHERE language = 'sv' AND voice_id = 'XXCqsM8I9KhqA7jLGj1U'
|
||
`).catch(() => {});
|
||
|
||
// ── Content-Pipeline: Job-Tracking direkt auf der Picture-Zeile ──────────────
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_status TEXT NOT NULL DEFAULT 'none'`).catch(() => {});
|
||
await query(`ALTER TABLE pictures DROP CONSTRAINT IF EXISTS pictures_pipeline_status_check`).catch(() => {});
|
||
await query(`
|
||
ALTER TABLE pictures ADD CONSTRAINT pictures_pipeline_status_check
|
||
CHECK (pipeline_status IN ('none', 'queued', 'running', 'failed', 'ready', 'published'))
|
||
`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_step TEXT`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_progress JSONB`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_error TEXT`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_started_at TIMESTAMPTZ`).catch(() => {});
|
||
await query(`ALTER TABLE pictures ADD COLUMN IF NOT EXISTS pipeline_finished_at TIMESTAMPTZ`).catch(() => {});
|
||
|
||
// app_settings — generischer Key/Value-Store (JSONB) für Konfiguration
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS app_settings (
|
||
key TEXT PRIMARY KEY,
|
||
value JSONB NOT NULL,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS app_settings_updated_at ON app_settings;
|
||
CREATE TRIGGER app_settings_updated_at
|
||
BEFORE UPDATE ON app_settings
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
await query(`
|
||
INSERT INTO app_settings (key, value) VALUES ('pipeline.pairs_per_object', '5'::jsonb)
|
||
ON CONFLICT (key) DO NOTHING
|
||
`).catch(() => {});
|
||
|
||
// ── Brysbaert-Erweiterungen ─────────────────────────────────────────────────
|
||
|
||
// parent_id auf categories (self-referential, Oberkategorie → Unterkategorie)
|
||
await query(`ALTER TABLE categories ADD COLUMN IF NOT EXISTS parent_id UUID REFERENCES categories(id) ON DELETE SET NULL`).catch(() => {});
|
||
|
||
// Unterkategorien seeden. Die bestehenden 22 Einträge sind die Oberkategorien (parent_id = NULL).
|
||
const SUBCATEGORY_TAXONOMY = [
|
||
// Lebensmittel
|
||
['Obst', 'Fruit', 'Frukt', 'Lebensmittel'],
|
||
['Gemüse', 'Vegetables', 'Grönsaker', 'Lebensmittel'],
|
||
['Fleisch & Fisch', 'Meat & Fish', 'Kött & fisk', 'Lebensmittel'],
|
||
['Backwaren & Getreide', 'Baked Goods & Grains', 'Bröd & spannmål', 'Lebensmittel'],
|
||
['Milchprodukte', 'Dairy', 'Mejeriprodukter', 'Lebensmittel'],
|
||
['Getränke', 'Drinks', 'Drycker', 'Lebensmittel'],
|
||
['Gewürze & Kräuter', 'Spices & Herbs', 'Kryddor & örter', 'Lebensmittel'],
|
||
['Süßigkeiten & Snacks', 'Sweets & Snacks', 'Sötsaker & snacks', 'Lebensmittel'],
|
||
// Tiere
|
||
['Haustiere', 'Pets', 'Husdjur', 'Tiere'],
|
||
['Wildtiere', 'Wild Animals', 'Vilda djur', 'Tiere'],
|
||
['Vögel', 'Birds', 'Fåglar', 'Tiere'],
|
||
['Reptilien & Amphibien', 'Reptiles & Amphibians', 'Reptiler & amfibier', 'Tiere'],
|
||
['Insekten & Spinnen', 'Insects & Spiders', 'Insekter & spindlar', 'Tiere'],
|
||
['Meerestiere', 'Sea Animals', 'Havsdjur', 'Tiere'],
|
||
// Körper
|
||
['Kopf & Gesicht', 'Head & Face', 'Huvud & ansikte', 'Körper'],
|
||
['Rumpf', 'Torso', 'Bål', 'Körper'],
|
||
['Arme & Beine', 'Arms & Legs', 'Armar & ben', 'Körper'],
|
||
['Innere Organe', 'Internal Organs', 'Inre organ', 'Körper'],
|
||
['Körperpflege', 'Personal Care', 'Kroppsvård', 'Körper'],
|
||
// Kleidung
|
||
['Oberbekleidung', 'Tops & Outerwear', 'Överkläder', 'Kleidung'],
|
||
['Unterbekleidung', 'Underwear', 'Underkläder', 'Kleidung'],
|
||
['Kopfbedeckung', 'Headwear', 'Huvudbonader', 'Kleidung'],
|
||
['Schuhe & Socken', 'Shoes & Socks', 'Skor & strumpor', 'Kleidung'],
|
||
['Accessoires', 'Accessories', 'Accessoarer', 'Kleidung'],
|
||
// Familie & Menschen
|
||
['Familienmitglieder', 'Family Members', 'Familjemedlemmar', 'Familie & Menschen'],
|
||
['Berufe & Titel', 'Professions & Titles', 'Yrken & titlar', 'Familie & Menschen'],
|
||
['Beziehungen', 'Relationships', 'Relationer', 'Familie & Menschen'],
|
||
// Haushalt
|
||
['Küchenutensilien', 'Kitchen Utensils', 'Köksredskap', 'Haushalt'],
|
||
['Reinigung & Pflege', 'Cleaning & Care', 'Rengöring & vård', 'Haushalt'],
|
||
['Verpackung & Behälter', 'Packaging & Containers', 'Förpackningar & behållare','Haushalt'],
|
||
// Wohnen & Möbel
|
||
['Zimmer & Räume', 'Rooms & Spaces', 'Rum & utrymmen', 'Wohnen & Möbel'],
|
||
['Möbel', 'Furniture', 'Möbler', 'Wohnen & Möbel'],
|
||
['Beleuchtung & Elektro', 'Lighting & Electronics', 'Belysning & el', 'Wohnen & Möbel'],
|
||
// Natur & Pflanzen
|
||
['Pflanzen & Blumen', 'Plants & Flowers', 'Växter & blommor', 'Natur & Pflanzen'],
|
||
['Bäume & Sträucher', 'Trees & Shrubs', 'Träd & buskar', 'Natur & Pflanzen'],
|
||
['Landschaftsmerkmale', 'Landscape Features', 'Landskapsdrag', 'Natur & Pflanzen'],
|
||
['Gesteine & Böden', 'Rocks & Soils', 'Stenar & jordar', 'Natur & Pflanzen'],
|
||
// Verkehr & Reisen
|
||
['Fahrzeuge (Land)', 'Land Vehicles', 'Landfordon', 'Verkehr & Reisen'],
|
||
['Fahrzeuge (Wasser & Luft)', 'Water & Air Vehicles', 'Vatten- & luftfordon', 'Verkehr & Reisen'],
|
||
['Straße & Infrastruktur', 'Roads & Infrastructure', 'Vägar & infrastruktur', 'Verkehr & Reisen'],
|
||
// Stadt & Gebäude
|
||
['Gebäude & Orte', 'Buildings & Places', 'Byggnader & platser', 'Stadt & Gebäude'],
|
||
['Innenräume & Bereiche', 'Indoor Spaces & Areas', 'Inomhusutrymmen', 'Stadt & Gebäude'],
|
||
// Technik & Geräte
|
||
['Haushaltsgeräte', 'Household Appliances', 'Hushållsapparater', 'Technik & Geräte'],
|
||
['Elektronik & Computer', 'Electronics & Computers', 'Elektronik & datorer', 'Technik & Geräte'],
|
||
['Werkzeuge & Maschinen', 'Tools & Machines', 'Verktyg & maskiner', 'Technik & Geräte'],
|
||
// Sport & Freizeit
|
||
['Sport & Bewegung', 'Sports & Exercise', 'Sport & rörelse', 'Sport & Freizeit'],
|
||
['Spiele & Spielzeug', 'Games & Toys', 'Spel & leksaker', 'Sport & Freizeit'],
|
||
['Kunst & Musik', 'Arts & Music', 'Konst & musik', 'Sport & Freizeit'],
|
||
];
|
||
for (const [de, en, sv, parentDe] of SUBCATEGORY_TAXONOMY) {
|
||
await query(
|
||
`INSERT INTO categories (titel_de, titel_en, titel_sv, status, published_at, parent_id)
|
||
SELECT $1, $2, $3, 'published', NOW(),
|
||
(SELECT id FROM categories WHERE lower(titel_de) = lower($4) AND parent_id IS NULL LIMIT 1)
|
||
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE lower(titel_de) = lower($1))`,
|
||
[de, en, sv, parentDe]
|
||
).catch(() => {});
|
||
}
|
||
|
||
// Neue Spalten auf words (Brysbaert-Import + Anreicherung)
|
||
await query(`ALTER TABLE words ADD COLUMN IF NOT EXISTS conc_m NUMERIC(4,2)`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD COLUMN IF NOT EXISTS dom_pos VARCHAR(20)`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD COLUMN IF NOT EXISTS level VARCHAR(5)`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD COLUMN IF NOT EXISTS themenfeld_id UUID`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD CONSTRAINT words_themenfeld_id_fkey FOREIGN KEY (themenfeld_id) REFERENCES categories(id) ON DELETE SET NULL`).catch(() => {});
|
||
await query(`ALTER TABLE words DROP CONSTRAINT IF EXISTS words_dom_pos_check`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD CONSTRAINT words_dom_pos_check CHECK (dom_pos IN ('noun', 'verb', 'adjective', 'other'))`).catch(() => {});
|
||
await query(`ALTER TABLE words DROP CONSTRAINT IF EXISTS words_level_check`).catch(() => {});
|
||
await query(`ALTER TABLE words ADD CONSTRAINT words_level_check CHECK (level IN ('A1', 'A2', 'B1'))`).catch(() => {});
|
||
|
||
// Unique-Index auf titel_en — Voraussetzung für ON CONFLICT im CSV-Import.
|
||
// Partiell (WHERE IS NOT NULL) damit bestehende NULL-Zeilen den Index nicht blockieren.
|
||
// Doppelte non-null titel_en erst bereinigen, dann Index anlegen.
|
||
await query(`
|
||
DELETE FROM words w
|
||
USING (
|
||
SELECT titel_en, MAX(created_at) AS keep_at
|
||
FROM words WHERE titel_en IS NOT NULL
|
||
GROUP BY titel_en HAVING COUNT(*) > 1
|
||
) dup
|
||
WHERE w.titel_en = dup.titel_en AND w.created_at < dup.keep_at
|
||
`).catch(() => {});
|
||
await query(
|
||
`CREATE UNIQUE INDEX IF NOT EXISTS words_titel_en_key ON words (titel_en) WHERE titel_en IS NOT NULL`
|
||
);
|
||
|
||
// enrich_batches — Status-Tracking für Wort-Anreicherungs-Batches (analog category_batches)
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS enrich_batches (
|
||
batch_id TEXT PRIMARY KEY,
|
||
status TEXT NOT NULL DEFAULT 'submitted',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
// word_generative — Pipeline für KI-generierte Wort-Bilder
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS word_generative (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
prompt TEXT,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
CHECK (status IN ('pending', 'generating', 'generated', 'accepted', 'rejected')),
|
||
picture_link TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
await query(`
|
||
DROP TRIGGER IF EXISTS word_generative_updated_at ON word_generative;
|
||
CREATE TRIGGER word_generative_updated_at
|
||
BEFORE UPDATE ON word_generative
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at()
|
||
`);
|
||
|
||
// ── Migrate old {{uuid}} placeholders → new {{label.w:uuid}} / {{label.o:uuid}} ──
|
||
await migratePlaceholders();
|
||
|
||
console.log('Migration complete');
|
||
}
|
||
|
||
// UUID regex — matches bare {{uuid}} but NOT already-migrated {{label.w:uuid}}
|
||
const UUID_RE = /\{\{([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})\}\}/gi;
|
||
|
||
async function migratePlaceholders() {
|
||
const textCols = {
|
||
questions: ['sentence_de', 'sentence_en', 'sentence_sv'],
|
||
statements: [
|
||
'positive_sentence_de', 'positive_sentence_en', 'positive_sentence_sv',
|
||
'negative_sentence_de', 'negative_sentence_en', 'negative_sentence_sv',
|
||
],
|
||
};
|
||
|
||
const uuidSet = new Set();
|
||
const affected = {};
|
||
|
||
for (const [table, cols] of Object.entries(textCols)) {
|
||
const whereClause = cols
|
||
.map(c => `${c} ~ '\\{\\{[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\\}\\}'`)
|
||
.join(' OR ');
|
||
const { rows } = await query(`SELECT id, ${cols.join(', ')} FROM ${table} WHERE ${whereClause}`);
|
||
if (rows.length) {
|
||
affected[table] = rows;
|
||
rows.forEach(row => cols.forEach(col => {
|
||
for (const m of (row[col] || '').matchAll(UUID_RE)) uuidSet.add(m[1]);
|
||
}));
|
||
}
|
||
}
|
||
|
||
if (uuidSet.size === 0) return;
|
||
|
||
const uuids = [...uuidSet];
|
||
const labelMap = {};
|
||
|
||
// Words first
|
||
const { rows: wordRows } = await query(
|
||
`SELECT id, titel_de, titel_en FROM words WHERE id = ANY($1::uuid[])`, [uuids]
|
||
);
|
||
wordRows.forEach(w => { labelMap[w.id] = { label: w.titel_de || w.titel_en || 'Wort', type: 'w' }; });
|
||
|
||
// Remaining → objects
|
||
const missing = uuids.filter(id => !labelMap[id]);
|
||
if (missing.length) {
|
||
const { rows: objRows } = await query(
|
||
`SELECT o.id, w.titel_de, w.titel_en
|
||
FROM objects o
|
||
LEFT JOIN object_words ow ON ow.object_id = o.id
|
||
LEFT JOIN words w ON w.id = ow.word_id
|
||
WHERE o.id = ANY($1::uuid[])`, [missing]
|
||
);
|
||
const seen = new Set();
|
||
objRows.forEach(r => {
|
||
if (!seen.has(r.id)) {
|
||
seen.add(r.id);
|
||
labelMap[r.id] = { label: r.titel_de || r.titel_en || 'Objekt', type: 'o' };
|
||
}
|
||
});
|
||
}
|
||
|
||
// UPDATE affected rows
|
||
for (const [table, rows] of Object.entries(affected)) {
|
||
const cols = textCols[table];
|
||
for (const row of rows) {
|
||
const updates = {};
|
||
for (const col of cols) {
|
||
const text = row[col];
|
||
if (!text) continue;
|
||
const replaced = text.replace(UUID_RE, (_, uuid) => {
|
||
const info = labelMap[uuid];
|
||
return info ? `{{${info.label}.${info.type}:${uuid}}}` : `{{${uuid}}}`;
|
||
});
|
||
if (replaced !== text) updates[col] = replaced;
|
||
}
|
||
if (Object.keys(updates).length) {
|
||
const setClauses = Object.keys(updates).map((k, i) => `${k} = $${i + 2}`).join(', ');
|
||
await query(`UPDATE ${table} SET ${setClauses} WHERE id = $1`, [row.id, ...Object.values(updates)]);
|
||
}
|
||
}
|
||
}
|
||
|
||
const count = Object.values(affected).reduce((s, r) => s + r.length, 0);
|
||
if (count > 0) console.log(`Placeholder migration: updated ${count} rows`);
|
||
}
|
||
|
||
// ── Prompt-Styles & Picture-Jobs ──────────────────────────────────────────────
|
||
|
||
async function migratePromptStyles() {
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS prompt_styles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
type VARCHAR(20) NOT NULL CHECK (type IN ('fix', 'atmosphere', 'setting')),
|
||
kategorie_id UUID,
|
||
text_en TEXT NOT NULL
|
||
)
|
||
`);
|
||
|
||
// Umbenennung themenfeld_id → kategorie_id (idempotent)
|
||
await query(`ALTER TABLE prompt_styles RENAME COLUMN themenfeld_id TO kategorie_id`).catch(() => {});
|
||
|
||
// FK auf categories nachrüsten (idempotent)
|
||
await query(`
|
||
ALTER TABLE prompt_styles
|
||
ADD CONSTRAINT prompt_styles_kategorie_fk
|
||
FOREIGN KEY (kategorie_id) REFERENCES categories(id) ON DELETE SET NULL
|
||
`).catch(() => {});
|
||
|
||
// Seed-Daten aus prompt_styles.csv (idempotent per id, kategorie_id zunächst null)
|
||
const seeds = [
|
||
{ id: 'b0f5c2a4-a95d-426f-a01c-0edc53e719b8', type: 'fix', text_en: 'hyperrealistic photography, natural unposed moment, shot on Canon EOS R5, ambient natural light, no color grading, razor sharp details, photorealistic textures, each object clearly visible and spatially separated, 8k' },
|
||
{ id: '62015070-1fbe-40b8-b293-8c39ae5994c3', type: 'atmosphere', text_en: 'misty autumn morning, golden hour light breaking through cool gray clouds, frost on the ground, dew on surfaces' },
|
||
{ id: 'd644f215-25b9-49be-87ea-629d7d8acb78', type: 'atmosphere', text_en: 'bright summer midday, harsh direct sunlight, vivid colors, dry warm air' },
|
||
{ id: 'da0a5339-37f5-47be-ba63-1fbf6c1e9f90', type: 'atmosphere', text_en: 'overcast spring day, soft diffused light, fresh green tones, slightly cool atmosphere' },
|
||
{ id: '11a8edb4-90a3-48a8-8407-31056644b55a', type: 'atmosphere', text_en: 'golden winter afternoon, low sun casting long shadows, bare trees, cold crisp air' },
|
||
{ id: '97bad727-6555-4f48-9a68-17dd5ce85535', type: 'atmosphere', text_en: 'early morning blue hour, soft cool light, calm and quiet atmosphere, slight fog' },
|
||
{ id: '6de167ef-5a87-4333-9325-cc31ccd9db05', type: 'atmosphere', text_en: 'warm summer evening, golden orange glow, long shadows, relaxed atmosphere' },
|
||
{ id: '082cc098-4c26-4d9a-b3a1-209dd9e507ea', type: 'setting', text_en: 'open green meadow with wooden fence, rolling hills in soft background, natural habitat' },
|
||
{ id: 'f0ef007a-c763-4c40-99c0-1bd17901739e', type: 'setting', text_en: 'dense forest edge with dappled light, mossy ground, wild and untouched environment' },
|
||
{ id: 'b809f859-2592-4207-8111-7da05e7057c9', type: 'setting', text_en: 'cozy living room corner, warm home environment, soft natural light from window' },
|
||
{ id: '28dac228-c335-46d2-9b40-481dc9e2b373', type: 'setting', text_en: 'shallow clear river bank, rocky ground, water reflections, natural wetland' },
|
||
{ id: '89cfbdf7-7fbc-439a-9265-73f18124e372', type: 'setting', text_en: 'rustic wooden kitchen counter, natural light from nearby window, linen cloth underneath' },
|
||
{ id: 'e7faf2ec-78e1-43bc-b870-c363f7ec2032', type: 'setting', text_en: 'outdoor farmers market stall, weathered wooden crates, morning light, earthy atmosphere' },
|
||
{ id: '45dc2aee-d223-4952-943d-cdbe86b7e8c3', type: 'setting', text_en: 'garden harvest scene, soil and greenery visible, freshly picked produce on ground' },
|
||
{ id: '5589aa12-ee74-4041-9443-40e9cfa538fd', type: 'setting', text_en: 'simple white kitchen table, clean minimal background, soft indoor daylight' },
|
||
{ id: '738365f1-b000-4dde-8e99-9b90f6984b79', type: 'setting', text_en: 'neutral light studio setting, clean background, soft natural sidelight, medical clarity' },
|
||
{ id: '98f1c118-b333-43ba-9167-870af883b5ae', type: 'setting', text_en: 'warm bathroom environment, mirror and soft light, everyday personal care setting' },
|
||
{ id: '2b81a5c9-7328-41e9-b08e-0d98d9a5c78f', type: 'setting', text_en: 'flat lay on light wooden surface, natural window light, clean and minimal styling' },
|
||
{ id: '2a3a4eed-ba32-4b21-8dad-1cf5679b00fb', type: 'setting', text_en: 'cozy bedroom setting, clothes laid out on bed, soft morning light' },
|
||
{ id: 'c816e95e-5edc-4ae9-8c0d-9c71a5a4dfb6', type: 'setting', text_en: 'outdoor market rack, hangers visible, casual everyday atmosphere' },
|
||
{ id: '33af0241-c19d-4429-91b5-0359c1f973e4', type: 'setting', text_en: 'warm living room, family home atmosphere, soft afternoon light through curtains' },
|
||
{ id: '153e70c4-f011-42af-ba0f-8ab82bf920ab', type: 'setting', text_en: 'outdoor garden or backyard, relaxed family setting, natural daylight' },
|
||
{ id: '9fe7fc4a-6578-4ee0-8a8e-a885e89e58c1', type: 'setting', text_en: 'bright kitchen countertop, clean and organized, natural window light' },
|
||
{ id: '46dab63b-7b3d-45e7-9ea9-4a4a67e9fabd', type: 'setting', text_en: 'utility room or bathroom shelf, everyday cleaning supplies visible, practical setting' },
|
||
{ id: '28246e90-4ac8-444f-be23-de401365d38d', type: 'setting', text_en: 'cozy Scandinavian living room, warm tones, natural materials, soft indirect light' },
|
||
{ id: '5143c10f-d717-4698-88f5-f1598d0eeef9', type: 'setting', text_en: 'bright airy bedroom, white walls, minimal furniture, morning sunlight' },
|
||
{ id: 'd23d7050-dc22-4226-8a5b-79e75f11de8b', type: 'setting', text_en: 'open countryside landscape, wide sky, natural untouched terrain, peaceful atmosphere' },
|
||
{ id: '34c6a784-7a32-4f84-a06d-f546c9c9fbea', type: 'setting', text_en: 'forest floor close-up, mossy rocks, fallen leaves, soft filtered light through canopy' },
|
||
{ id: '1fc61dd9-57c6-4eba-8328-37cbf5fc135e', type: 'setting', text_en: 'garden bed with rich dark soil, plants at various growth stages, earthy tones' },
|
||
{ id: '3244f090-f2a2-4806-875a-88038598fc5e', type: 'setting', text_en: 'quiet suburban street, cobblestone or asphalt road, parked vehicles, everyday scene' },
|
||
{ id: '36d80c19-13ea-4672-b2e9-8ceedb4ab178', type: 'setting', text_en: 'rural road with open fields, minimal traffic, wide sky, natural light' },
|
||
{ id: '98957b0a-f415-4282-9b3d-863a9bf03a77', type: 'setting', text_en: 'busy European city street, historic buildings in background, natural daylight' },
|
||
{ id: '66fa361a-e062-4adc-9c9a-3e01ac8dbbe0', type: 'setting', text_en: 'quiet town square, fountain or bench visible, calm everyday atmosphere' },
|
||
{ id: '2dba4303-c743-419f-a7e8-06b6d54ba91d', type: 'setting', text_en: 'clean modern workspace, desk surface, natural sidelight, organized tools' },
|
||
{ id: 'a78df43b-8897-40dd-9ccf-de29ff9bf5da', type: 'setting', text_en: 'garage or workshop setting, workbench with tools, practical everyday environment' },
|
||
{ id: '949774d1-0678-4683-9b8e-e5568f648ba8', type: 'setting', text_en: 'outdoor park or sports field, open space, natural daylight, active atmosphere' },
|
||
{ id: '9b35a717-03dd-41aa-a60e-90dff8bc5aaf', type: 'setting', text_en: 'cozy indoor hobby room, soft warm light, creative materials visible' },
|
||
];
|
||
|
||
for (const s of seeds) {
|
||
await query(
|
||
`INSERT INTO prompt_styles (id, type, text_en)
|
||
SELECT $1, $2, $3
|
||
WHERE NOT EXISTS (SELECT 1 FROM prompt_styles WHERE id = $1)`,
|
||
[s.id, s.type, s.text_en]
|
||
).catch(() => {});
|
||
}
|
||
|
||
// kategorie_id per Kategoriename befüllen (idempotent, unabhängig von Category-UUIDs)
|
||
const THEME_MAP = [
|
||
{ en: 'Animals', ids: ['082cc098-4c26-4d9a-b3a1-209dd9e507ea', 'f0ef007a-c763-4c40-99c0-1bd17901739e', 'b809f859-2592-4207-8111-7da05e7057c9', '28dac228-c335-46d2-9b40-481dc9e2b373'] },
|
||
{ en: 'Food', ids: ['89cfbdf7-7fbc-439a-9265-73f18124e372', 'e7faf2ec-78e1-43bc-b870-c363f7ec2032', '45dc2aee-d223-4952-943d-cdbe86b7e8c3', '5589aa12-ee74-4041-9443-40e9cfa538fd'] },
|
||
{ en: 'Body', ids: ['738365f1-b000-4dde-8e99-9b90f6984b79', '98f1c118-b333-43ba-9167-870af883b5ae'] },
|
||
{ en: 'Clothing', ids: ['2b81a5c9-7328-41e9-b08e-0d98d9a5c78f', '2a3a4eed-ba32-4b21-8dad-1cf5679b00fb', 'c816e95e-5edc-4ae9-8c0d-9c71a5a4dfb6'] },
|
||
{ en: 'Family & People', ids: ['33af0241-c19d-4429-91b5-0359c1f973e4', '153e70c4-f011-42af-ba0f-8ab82bf920ab'] },
|
||
{ en: 'Household', ids: ['9fe7fc4a-6578-4ee0-8a8e-a885e89e58c1', '46dab63b-7b3d-45e7-9ea9-4a4a67e9fabd'] },
|
||
{ en: 'Home & Furniture', ids: ['28246e90-4ac8-444f-be23-de401365d38d', '5143c10f-d717-4698-88f5-f1598d0eeef9'] },
|
||
{ en: 'Nature & Plants', ids: ['d23d7050-dc22-4226-8a5b-79e75f11de8b', '34c6a784-7a32-4f84-a06d-f546c9c9fbea', '1fc61dd9-57c6-4eba-8328-37cbf5fc135e'] },
|
||
{ en: 'Transport & Travel',ids: ['3244f090-f2a2-4806-875a-88038598fc5e', '36d80c19-13ea-4672-b2e9-8ceedb4ab178'] },
|
||
{ en: 'City & Buildings', ids: ['98957b0a-f415-4282-9b3d-863a9bf03a77', '66fa361a-e062-4adc-9c9a-3e01ac8dbbe0'] },
|
||
{ en: 'Tools', ids: ['2dba4303-c743-419f-a7e8-06b6d54ba91d', 'a78df43b-8897-40dd-9ccf-de29ff9bf5da'] },
|
||
{ en: 'Sports & Leisure', ids: ['949774d1-0678-4683-9b8e-e5568f648ba8', '9b35a717-03dd-41aa-a60e-90dff8bc5aaf'] },
|
||
];
|
||
|
||
for (const { en, ids } of THEME_MAP) {
|
||
await query(
|
||
`UPDATE prompt_styles
|
||
SET kategorie_id = (SELECT id FROM categories WHERE lower(titel_en) = lower($1) LIMIT 1)
|
||
WHERE id = ANY($2::uuid[])
|
||
AND kategorie_id IS DISTINCT FROM
|
||
(SELECT id FROM categories WHERE lower(titel_en) = lower($1) LIMIT 1)`,
|
||
[en, ids]
|
||
).catch(() => {});
|
||
}
|
||
}
|
||
|
||
async function migratePictureJobs() {
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS picture_jobs (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
kategorie_id UUID REFERENCES categories(id) ON DELETE SET NULL,
|
||
prompt_fix UUID REFERENCES prompt_styles(id) ON DELETE SET NULL,
|
||
prompt_atmosphere UUID REFERENCES prompt_styles(id) ON DELETE SET NULL,
|
||
prompt_setting UUID REFERENCES prompt_styles(id) ON DELETE SET NULL,
|
||
prompt_final TEXT,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
CHECK (status IN ('pending', 'generating', 'done', 'failed')),
|
||
picture_id UUID REFERENCES pictures(id) ON DELETE SET NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
)
|
||
`);
|
||
|
||
await query(`
|
||
CREATE TABLE IF NOT EXISTS picture_job_words (
|
||
picture_job_id UUID NOT NULL REFERENCES picture_jobs(id) ON DELETE CASCADE,
|
||
word_id UUID NOT NULL REFERENCES words(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (picture_job_id, word_id)
|
||
)
|
||
`);
|
||
}
|
||
|
||
async function migrate() {
|
||
await migrateCore();
|
||
await migratePromptStyles();
|
||
await migratePictureJobs();
|
||
}
|
||
|
||
module.exports = migrate;
|