PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS households (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    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 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,
    visibility TEXT NOT NULL DEFAULT 'shared',
    kind TEXT NOT NULL CHECK (kind IN ('food', 'meal')),
    name TEXT NOT NULL,
    category TEXT,
    note TEXT,
    photo_filename TEXT,
    availability_state TEXT NOT NULL DEFAULT 'idea' CHECK (availability_state IN ('idea', 'home', 'archived')),
    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 (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,
    added_by INTEGER,
    checked_by 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
);

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

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