PRAGMA foreign_keys = ON;

CREATE TABLE catalog_products (
    id INTEGER PRIMARY KEY,
    ean TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    generic_name TEXT NOT NULL DEFAULT '',
    brand TEXT NOT NULL DEFAULT '',
    quantity TEXT NOT NULL DEFAULT '',
    serving_size TEXT NOT NULL DEFAULT '',
    carbs_per_100g REAL,
    carbs_per_serving REAL,
    fat_per_100g REAL,
    countries TEXT NOT NULL DEFAULT '',
    category TEXT NOT NULL DEFAULT '',
    retailer TEXT NOT NULL DEFAULT '',
    image_url TEXT NOT NULL DEFAULT '',
    source_url TEXT NOT NULL DEFAULT ''
);

CREATE TABLE catalog_meta (
    meta_key TEXT PRIMARY KEY,
    value_json TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE catalog_source_files (
    relative_path TEXT PRIMARY KEY,
    source_sha256 TEXT NOT NULL,
    byte_size INTEGER NOT NULL,
    record_count INTEGER NOT NULL DEFAULT 0,
    migration_status TEXT NOT NULL,
    checked_at TEXT NOT NULL
);

-- Die produktive Datenbank ergänzt Indizes auf EAN, Name, Marke, Kategorie
-- und Händler sowie eine FTS5-Tabelle für die lokale Volltextsuche.