79 lines
2.7 KiB
SQL
79 lines
2.7 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
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,
|
|
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 (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,
|
|
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 (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,
|
|
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 (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
|
|
);
|