-- Lightweight projects (exactly one per task) and label colors for
-- doot-native tasks. Labels themselves already exist as a JSON column on
-- native_tasks; this only adds color metadata for them, plus the new
-- projects concept.
CREATE TABLE projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    archived BOOLEAN DEFAULT 0
);

CREATE TABLE labels (
    name TEXT PRIMARY KEY,
    color TEXT NOT NULL
);

ALTER TABLE native_tasks ADD COLUMN project_id TEXT DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_native_tasks_project ON native_tasks(project_id);