PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS app_meta (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS households (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    shopping_weekday INTEGER NOT NULL DEFAULT 5,
    shopping_prep_days INTEGER NOT NULL DEFAULT 1,
    shopping_reminder_time TEXT NOT NULL DEFAULT '18:00',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER,
    username TEXT NOT NULL UNIQUE,
    email TEXT,
    display_name TEXT,
    role TEXT NOT NULL DEFAULT 'member',
    is_active INTEGER NOT NULL DEFAULT 1,
    password_hash TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE RESTRICT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email_unique
ON users (email)
WHERE email IS NOT NULL AND email != '';

CREATE TABLE IF NOT EXISTS household_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    builder_key TEXT NOT NULL DEFAULT 'neutral',
    sort_order INTEGER NOT NULL DEFAULT 100,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (household_id, name),
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_settings (
    user_id INTEGER PRIMARY KEY,
    reminders_enabled INTEGER NOT NULL DEFAULT 1,
    push_enabled INTEGER NOT NULL DEFAULT 0,
    notification_channel TEXT NOT NULL DEFAULT 'in_app',
    suggestion_style TEXT NOT NULL DEFAULT 'balanced',
    energy_preference TEXT NOT NULL DEFAULT 'neutral',
    protein_preference TEXT NOT NULL DEFAULT 'mixed',
    remind_before_shopping INTEGER NOT NULL DEFAULT 1,
    remind_on_shopping_day INTEGER NOT NULL DEFAULT 1,
    show_missing_for_upcoming_week INTEGER NOT NULL DEFAULT 1,
    show_planned_not_shopped INTEGER NOT NULL DEFAULT 1,
    remind_tomorrow_if_sparse INTEGER NOT NULL DEFAULT 1,
    remind_week_if_sparse INTEGER NOT NULL DEFAULT 1,
    push_missing_breakfast INTEGER NOT NULL DEFAULT 0,
    push_missing_lunch INTEGER NOT NULL DEFAULT 0,
    push_missing_dinner INTEGER NOT NULL DEFAULT 0,
    push_small_snack INTEGER NOT NULL DEFAULT 0,
    suggest_home_for_today INTEGER NOT NULL DEFAULT 1,
    remind_small_snack INTEGER NOT NULL DEFAULT 0,
    remind_nuts INTEGER NOT NULL DEFAULT 0,
    show_meal_balancing INTEGER NOT NULL DEFAULT 1,
    suggest_templates INTEGER NOT NULL DEFAULT 1,
    suggest_patterns INTEGER NOT NULL DEFAULT 1,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS push_subscriptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    endpoint TEXT NOT NULL UNIQUE,
    p256dh TEXT NOT NULL,
    auth TEXT NOT NULL,
    user_agent TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_test_at TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS reminder_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    event_key TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, event_key),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hidden_generated_suggestions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    suggestion_key TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, suggestion_key),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS dayparts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    sort_order INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER,
    owner_user_id INTEGER,
    target_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    kind TEXT NOT NULL CHECK (kind IN ('food', 'meal')),
    name TEXT NOT NULL,
    category TEXT,
    base_type TEXT NOT NULL DEFAULT 'neutral',
    flavor_profile TEXT NOT NULL DEFAULT 'neutral',
    suggestion_role TEXT NOT NULL DEFAULT 'base',
    suggestion_priority TEXT NOT NULL DEFAULT 'normal',
    can_be_meal_core INTEGER NOT NULL DEFAULT 0,
    meal_type TEXT,
    meal_tags TEXT NOT NULL DEFAULT '',
    energy_density TEXT NOT NULL DEFAULT 'neutral',
    note TEXT,
    photo_filename TEXT,
    availability_state TEXT NOT NULL DEFAULT 'idea' CHECK (availability_state IN ('idea', 'home', 'archived')),
    is_archived INTEGER NOT NULL DEFAULT 0,
    is_quick_added INTEGER NOT NULL DEFAULT 0,
    created_by INTEGER,
    updated_by INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS item_dayparts (
    item_id INTEGER NOT NULL,
    daypart_id INTEGER NOT NULL,
    PRIMARY KEY (item_id, daypart_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (daypart_id) REFERENCES dayparts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS meal_components (
    meal_item_id INTEGER NOT NULL,
    food_item_id INTEGER NOT NULL,
    PRIMARY KEY (meal_item_id, food_item_id),
    FOREIGN KEY (meal_item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (food_item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS shopping_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    item_id INTEGER NOT NULL,
    shopping_note TEXT NOT NULL DEFAULT '',
    added_by INTEGER,
    checked_by INTEGER,
    needed_for_date TEXT,
    needed_for_daypart_id INTEGER,
    is_checked INTEGER NOT NULL DEFAULT 0,
    added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    checked_at TEXT,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (checked_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (needed_for_daypart_id) REFERENCES dayparts(id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_shopping_entries_open_item
ON shopping_entries (item_id, COALESCE(shopping_note, ''))
WHERE is_checked = 0;

CREATE TABLE IF NOT EXISTS shopping_needs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER NOT NULL,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    item_id INTEGER NOT NULL,
    source_item_id INTEGER,
    needed_for_date TEXT NOT NULL,
    needed_for_daypart_id INTEGER,
    activation_date TEXT NOT NULL,
    is_activated INTEGER NOT NULL DEFAULT 0,
    activated_at TEXT,
    created_by INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (item_id, source_item_id, needed_for_date, needed_for_daypart_id, visibility),
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (source_item_id) REFERENCES items(id) ON DELETE SET NULL,
    FOREIGN KEY (needed_for_daypart_id) REFERENCES dayparts(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS plan_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    plan_date TEXT NOT NULL,
    daypart_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    note TEXT,
    created_by INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (daypart_id) REFERENCES dayparts(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS day_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER NOT NULL,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    name TEXT NOT NULL,
    description TEXT,
    last_used_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS day_template_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    day_template_id INTEGER NOT NULL,
    daypart_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 100,
    FOREIGN KEY (day_template_id) REFERENCES day_templates(id) ON DELETE CASCADE,
    FOREIGN KEY (daypart_id) REFERENCES dayparts(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS week_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER NOT NULL,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    name TEXT NOT NULL,
    description TEXT,
    last_used_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS week_template_days (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    week_template_id INTEGER NOT NULL,
    weekday_index INTEGER NOT NULL,
    day_template_id INTEGER NOT NULL,
    UNIQUE (week_template_id, weekday_index),
    FOREIGN KEY (week_template_id) REFERENCES week_templates(id) ON DELETE CASCADE,
    FOREIGN KEY (day_template_id) REFERENCES day_templates(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_sets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    household_id INTEGER NOT NULL,
    owner_user_id INTEGER,
    visibility TEXT NOT NULL DEFAULT 'shared',
    name TEXT NOT NULL,
    description TEXT,
    last_used_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS item_set_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_set_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 100,
    UNIQUE (item_set_id, item_id),
    FOREIGN KEY (item_set_id) REFERENCES item_sets(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_items_kind_name
ON items (kind, name);

CREATE INDEX IF NOT EXISTS idx_items_household_visibility
ON items (household_id, visibility, availability_state);

CREATE INDEX IF NOT EXISTS idx_items_household_visibility_archived
ON items (household_id, visibility, is_archived, availability_state);

CREATE INDEX IF NOT EXISTS idx_items_target_user
ON items (target_user_id);

CREATE INDEX IF NOT EXISTS idx_item_dayparts_daypart_item
ON item_dayparts (daypart_id, item_id);

CREATE INDEX IF NOT EXISTS idx_plan_entries_plan_date_daypart
ON plan_entries (plan_date, daypart_id);

CREATE INDEX IF NOT EXISTS idx_plan_entries_household_visibility
ON plan_entries (household_id, visibility, plan_date);

CREATE INDEX IF NOT EXISTS idx_shopping_entries_household_visibility
ON shopping_entries (household_id, visibility, is_checked);

CREATE INDEX IF NOT EXISTS idx_shopping_needs_household_activation
ON shopping_needs (household_id, activation_date, is_activated);

CREATE INDEX IF NOT EXISTS idx_day_templates_household_visibility
ON day_templates (household_id, visibility, name);

CREATE INDEX IF NOT EXISTS idx_week_templates_household_visibility
ON week_templates (household_id, visibility, name);

CREATE INDEX IF NOT EXISTS idx_item_sets_household_visibility
ON item_sets (household_id, visibility, name);