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;