-- Initial schema for Personal Consolidation Dashboard

-- Tasks table (Todoist)
CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    content TEXT NOT NULL,
    description TEXT,
    project_id TEXT,
    project_name TEXT,
    due_date DATETIME,
    priority INTEGER DEFAULT 1,
    completed BOOLEAN DEFAULT 0,
    labels TEXT, -- JSON array
    url TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(completed);
CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id);

-- Notes table (Obsidian)
CREATE TABLE IF NOT EXISTS notes (
    filename TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    modified DATETIME NOT NULL,
    path TEXT NOT NULL,
    tags TEXT, -- JSON array
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_notes_modified ON notes(modified DESC);

-- Meals table (PlanToEat)
CREATE TABLE IF NOT EXISTS meals (
    id TEXT PRIMARY KEY,
    recipe_name TEXT NOT NULL,
    date DATE NOT NULL,
    meal_type TEXT CHECK(meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')),
    recipe_url TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_meals_date ON meals(date);
CREATE INDEX IF NOT EXISTS idx_meals_type ON meals(meal_type);

-- Boards table (Trello)
CREATE TABLE IF NOT EXISTS boards (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Cards table (Trello)
CREATE TABLE IF NOT EXISTS cards (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    board_id TEXT NOT NULL,
    list_id TEXT,
    list_name TEXT,
    due_date DATETIME,
    url TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_cards_board_id ON cards(board_id);
CREATE INDEX IF NOT EXISTS idx_cards_due_date ON cards(due_date);