CREATE TABLE todo_lists (
    id      TEXT PRIMARY KEY,
    tenant  TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name    TEXT NOT NULL
);

CREATE INDEX idx_todo_lists_tenant ON todo_lists(tenant);

CREATE TABLE todo_items (
    id        TEXT PRIMARY KEY,
    tenant    TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    title     TEXT NOT NULL,
    notes     TEXT NOT NULL DEFAULT '',
    status    TEXT NOT NULL DEFAULT 'open',
    -- Empty means unassigned. Membership in the tenant is enforced by the
    -- store, not a foreign key, so removing a member doesn't break items.
    assignee  TEXT NOT NULL DEFAULT ''
);

CREATE INDEX idx_todo_items_tenant ON todo_items(tenant);

CREATE TABLE todo_list_items (
    tenant  TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    list    TEXT NOT NULL REFERENCES todo_lists(id) ON DELETE CASCADE,
    item    TEXT NOT NULL REFERENCES todo_items(id) ON DELETE CASCADE,
    PRIMARY KEY (list, item)
);

CREATE INDEX idx_todo_list_items_item ON todo_list_items(item);
CREATE INDEX idx_todo_list_items_tenant ON todo_list_items(tenant);