-- Migration: Add dev_projects table for tracking development projects
-- Syncs with TickTick "Dev Projects" list (id: 69cbd7138f0826bd72746074)
-- Date: 2026-03-31

CREATE TABLE IF NOT EXISTS dev_projects (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('planning', 'active', 'paused', 'completed', 'archived') NOT NULL DEFAULT 'planning',
    ticktick_task_id VARCHAR(100) DEFAULT NULL,
    ticktick_project_id VARCHAR(100) DEFAULT '69cbd7138f0826bd72746074',
    started_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    last_worked_on DATETIME DEFAULT NULL,
    total_sessions INT DEFAULT 0,
    tags JSON DEFAULT NULL,
    notes TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_dev_projects_status ON dev_projects(status);