File size: 2,346 Bytes
507973e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- Phase V migration: Add task organization fields + stats/events tables
-- Run against Neon PostgreSQL once before deploying Phase V backend
-- Usage: psql $DATABASE_URL -f scripts/migrate_phase5.sql

-- 1. Add new columns to todo table (idempotent with IF NOT EXISTS)
ALTER TABLE todo
    ADD COLUMN IF NOT EXISTS priority VARCHAR DEFAULT 'medium',
    ADD COLUMN IF NOT EXISTS tags TEXT[] DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS due_date TIMESTAMP,
    ADD COLUMN IF NOT EXISTS reminder_at TIMESTAMP,
    ADD COLUMN IF NOT EXISTS recurrence VARCHAR DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS parent_id INTEGER REFERENCES todo(id);

-- 2. Create indexes for new todo columns
CREATE INDEX IF NOT EXISTS idx_todo_user_priority ON todo(user_id, priority);
CREATE INDEX IF NOT EXISTS idx_todo_user_due_date ON todo(user_id, due_date);
CREATE INDEX IF NOT EXISTS idx_todo_tags ON todo USING GIN(tags);

-- 3. Create daily_completion table
CREATE TABLE IF NOT EXISTS daily_completion (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR NOT NULL REFERENCES "user"(id),
    date DATE NOT NULL,
    count INTEGER DEFAULT 0,
    UNIQUE(user_id, date)
);
CREATE INDEX IF NOT EXISTS idx_daily_completion_user_date ON daily_completion(user_id, date);

-- 4. Create user_stats table
CREATE TABLE IF NOT EXISTS user_stats (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR NOT NULL UNIQUE REFERENCES "user"(id),
    total_completed INTEGER DEFAULT 0,
    current_streak INTEGER DEFAULT 0,
    longest_streak INTEGER DEFAULT 0,
    last_active_date DATE,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 5. Create achievement table
CREATE TABLE IF NOT EXISTS achievement (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR NOT NULL REFERENCES "user"(id),
    achievement_type VARCHAR NOT NULL,
    unlocked_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, achievement_type)
);

-- 6. Create task_event table
CREATE TABLE IF NOT EXISTS task_event (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR NOT NULL,
    task_id INTEGER NOT NULL,
    user_id VARCHAR NOT NULL REFERENCES "user"(id),
    timestamp TIMESTAMP DEFAULT NOW(),
    payload JSONB DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_task_event_user_ts ON task_event(user_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_task_event_task ON task_event(task_id);

SELECT 'Phase V migration complete' AS status;