/**
* Database schema for todos
* This should be integrated into your existing storage.ts file
*/
import Database from 'better-sqlite3';
export function createTodoTable(db: Database.Database): void {
db.exec(`
CREATE TABLE IF NOT EXISTS todos (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
status TEXT NOT NULL DEFAULT 'pending',
priority TEXT NOT NULL DEFAULT 'medium',
tags TEXT, -- JSON array of tags
due_date TEXT, -- ISO 8601 format
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
completed_at TEXT,
project_id TEXT,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_todos_status ON todos(status);
CREATE INDEX IF NOT EXISTS idx_todos_priority ON todos(priority);
CREATE INDEX IF NOT EXISTS idx_todos_due_date ON todos(due_date);
CREATE INDEX IF NOT EXISTS idx_todos_project_id ON todos(project_id);
CREATE INDEX IF NOT EXISTS idx_todos_created_at ON todos(created_at);
`);
}
