DATABASE_SCHEMA.sql

-- ============================================================================
-- Afterthought Database Schema
-- SQLite 3 with extensions: FTS5, JSON1, sqlite-vss
-- ============================================================================

-- Enable recommended PRAGMAs for performance and reliability
PRAGMA journal_mode=WAL;           -- Write-Ahead Logging for better concurrency
PRAGMA synchronous=NORMAL;         -- Faster writes, safe for local storage
PRAGMA foreign_keys=ON;            -- Enforce foreign key constraints
PRAGMA cache_size=-64000;          -- 64MB cache for better performance
PRAGMA temp_store=MEMORY;          -- Store temp tables in RAM
PRAGMA auto_vacuum=INCREMENTAL;    -- Gradually reclaim space

-- ============================================================================
-- Settings Table
-- Stores user preferences and configuration (single row enforced)
-- ============================================================================

CREATE TABLE settings (
    id INTEGER PRIMARY KEY CHECK (id = 1), -- Enforce only one row

    -- API Configuration
    openai_api_key TEXT,                   -- Encrypted, stored in OS keychain
    openai_model TEXT DEFAULT 'gpt-5-nano',   -- AI model to use

    -- Watched Folders (JSON array)
    watched_folders TEXT DEFAULT '[]',     -- e.g. ["/Users/x/Screenshots", "/Users/x/Desktop"]

    -- Processing Settings
    auto_process BOOLEAN DEFAULT TRUE,     -- Auto-process new screenshots
    batch_size INTEGER DEFAULT 5,          -- Max concurrent AI requests

    -- Notification Settings
    notifications_enabled BOOLEAN DEFAULT TRUE,
    notification_sound BOOLEAN DEFAULT TRUE,

    -- Default Values
    default_category TEXT DEFAULT 'other', -- Default category for ambiguous screenshots

    -- UI Preferences
    theme TEXT DEFAULT 'light',            -- 'light', 'dark', 'system'
    timeline_view TEXT DEFAULT 'grid',     -- 'grid', 'list'

    -- Timestamps
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Initialize settings with default values
INSERT INTO settings (id) VALUES (1);

-- ============================================================================
-- Screenshots Table
-- Main table storing screenshot metadata and AI analysis results
-- ============================================================================

CREATE TABLE screenshots (
    -- Primary Key
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))), -- UUID v4

    -- File Information
    file_path TEXT NOT NULL UNIQUE,        -- Absolute path to screenshot file
    filename TEXT NOT NULL,                -- Original filename
    file_size_bytes INTEGER,               -- File size in bytes
    file_format TEXT,                      -- 'png', 'jpg', 'jpeg', 'heic', 'webp'

    -- Timestamps
    taken_at TEXT NOT NULL,                -- When screenshot was taken (ISO 8601)
    uploaded_at TEXT DEFAULT CURRENT_TIMESTAMP, -- When added to database
    processed_at TEXT,                     -- When AI processing completed

    -- AI Analysis Results
    extracted_text TEXT,                   -- OCR text from screenshot
    category TEXT CHECK(category IN ('work', 'personal', 'reference', 'idea', 'other')),
    ai_summary TEXT,                       -- AI-generated 1-sentence summary
    entities TEXT,                         -- JSON array: [{"type": "person", "value": "John"}, ...]
    sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative')),
    urgency_score INTEGER CHECK(urgency_score BETWEEN 1 AND 5), -- 1=low, 5=urgent
    confidence_score REAL,                 -- AI confidence (0.0-1.0)

    -- Status Tracking
    status TEXT DEFAULT 'pending_ai' CHECK(status IN ('pending_ai', 'processing', 'processed', 'error')),
    error_message TEXT,                    -- Error details if processing failed
    retry_count INTEGER DEFAULT 0,         -- Number of processing retries

    -- User Actions
    is_archived BOOLEAN DEFAULT FALSE,     -- User archived this screenshot
    is_favorited BOOLEAN DEFAULT FALSE,    -- User starred this screenshot
    user_notes TEXT,                       -- User's custom notes

    -- Metadata
    device_type TEXT,                      -- 'macos', 'windows'
    screen_resolution TEXT,                -- e.g. "1920x1080"

    -- Timestamps
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for common queries
CREATE INDEX idx_screenshots_taken_at ON screenshots(taken_at DESC);
CREATE INDEX idx_screenshots_category ON screenshots(category) WHERE NOT is_archived;
CREATE INDEX idx_screenshots_status ON screenshots(status) WHERE status != 'processed';
CREATE INDEX idx_screenshots_archived ON screenshots(is_archived, taken_at DESC);
CREATE INDEX idx_screenshots_favorited ON screenshots(is_favorited, taken_at DESC) WHERE is_favorited = TRUE;

-- ============================================================================
-- Full-Text Search (FTS5)
-- Virtual table for fast text search across screenshots
-- ============================================================================

CREATE VIRTUAL TABLE screenshots_fts USING fts5(
    screenshot_id UNINDEXED,               -- Link to screenshots.id (not searchable)
    extracted_text,                        -- Main searchable content
    ai_summary,                            -- Searchable summary
    filename,                              -- Searchable filename
    user_notes,                            -- Searchable user notes
    content=screenshots,                   -- Content from screenshots table
    content_rowid=rowid,                   -- Sync with rowid
    tokenize='porter unicode61'            -- English stemming + Unicode support
);

-- Triggers to keep FTS index synchronized

-- Insert trigger
CREATE TRIGGER screenshots_fts_insert AFTER INSERT ON screenshots BEGIN
    INSERT INTO screenshots_fts(screenshot_id, extracted_text, ai_summary, filename, user_notes)
    VALUES (new.id, new.extracted_text, new.ai_summary, new.filename, new.user_notes);
END;

-- Update trigger
CREATE TRIGGER screenshots_fts_update AFTER UPDATE ON screenshots BEGIN
    UPDATE screenshots_fts
    SET extracted_text = new.extracted_text,
        ai_summary = new.ai_summary,
        filename = new.filename,
        user_notes = new.user_notes
    WHERE screenshot_id = new.id;
END;

-- Delete trigger
CREATE TRIGGER screenshots_fts_delete AFTER DELETE ON screenshots BEGIN
    DELETE FROM screenshots_fts WHERE screenshot_id = old.id;
END;

-- ============================================================================
-- Vector Embeddings (sqlite-vss)
-- Stores text embeddings for semantic search
-- ============================================================================

-- Vector storage table (1536 dimensions for OpenAI text-embedding-3-small)
CREATE VIRTUAL TABLE screenshot_embeddings USING vss0(
    embedding(1536)                        -- OpenAI text-embedding-3-small dimensions
);

-- Mapping table to link screenshots to their embeddings
CREATE TABLE screenshot_embedding_map (
    screenshot_id TEXT PRIMARY KEY REFERENCES screenshots(id) ON DELETE CASCADE,
    embedding_rowid INTEGER NOT NULL REFERENCES screenshot_embeddings(rowid),
    model_name TEXT DEFAULT 'text-embedding-3-small', -- Which embedding model was used
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_embedding_map_rowid ON screenshot_embedding_map(embedding_rowid);

-- ============================================================================
-- Tasks Table
-- Stores actionable items extracted from screenshots or manually created
-- ============================================================================

CREATE TABLE tasks (
    -- Primary Key
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),

    -- Link to Screenshot (nullable for manual tasks)
    screenshot_id TEXT REFERENCES screenshots(id) ON DELETE SET NULL,

    -- Task Details
    title TEXT NOT NULL,                   -- Task title (required)
    description TEXT,                      -- Detailed description (optional)
    category TEXT,                         -- 'work', 'personal', etc.

    -- Scheduling
    due_date TEXT,                         -- ISO 8601 date when task is due
    reminder_at TEXT,                      -- ISO 8601 datetime for reminder notification
    reminder_sent BOOLEAN DEFAULT FALSE,   -- Has reminder been sent?

    -- Status
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'done', 'archived')),
    completed_at TEXT,                     -- When task was marked done

    -- Priority
    priority INTEGER DEFAULT 0 CHECK(priority BETWEEN 0 AND 3), -- 0=none, 1=low, 2=medium, 3=high

    -- AI Metadata
    ai_confidence REAL,                    -- How confident AI is this is a task (0.0-1.0)
    ai_extracted BOOLEAN DEFAULT FALSE,    -- Was this extracted by AI or manually created?

    -- Timestamps
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for common queries
CREATE INDEX idx_tasks_screenshot ON tasks(screenshot_id);
CREATE INDEX idx_tasks_status_due ON tasks(status, due_date) WHERE status = 'pending';
CREATE INDEX idx_tasks_reminder ON tasks(reminder_at) WHERE reminder_at IS NOT NULL AND reminder_sent = FALSE;
CREATE INDEX idx_tasks_created ON tasks(created_at DESC);

-- ============================================================================
-- Tags Table
-- User-defined tags for organizing screenshots
-- ============================================================================

CREATE TABLE tags (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name TEXT NOT NULL UNIQUE,             -- Tag name (unique per user)
    color TEXT DEFAULT '#3B82F6',          -- Hex color code for UI
    description TEXT,                      -- Optional tag description
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Many-to-many relationship: screenshots ↔ tags
CREATE TABLE screenshot_tags (
    screenshot_id TEXT NOT NULL REFERENCES screenshots(id) ON DELETE CASCADE,
    tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (screenshot_id, tag_id)
);

CREATE INDEX idx_screenshot_tags_screenshot ON screenshot_tags(screenshot_id);
CREATE INDEX idx_screenshot_tags_tag ON screenshot_tags(tag_id);

-- ============================================================================
-- AI Processing Queue
-- Tracks pending AI processing jobs
-- ============================================================================

CREATE TABLE ai_queue (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    screenshot_id TEXT NOT NULL REFERENCES screenshots(id) ON DELETE CASCADE,

    -- Queue Metadata
    priority INTEGER DEFAULT 0,            -- Higher priority processed first
    retry_count INTEGER DEFAULT 0,         -- Number of retries attempted
    max_retries INTEGER DEFAULT 3,         -- Max retries before giving up

    -- Error Tracking
    last_error TEXT,                       -- Last error message
    last_attempt_at TEXT,                  -- When last processing was attempted

    -- Status
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'completed', 'failed')),

    -- Timestamps
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_ai_queue_status ON ai_queue(status, priority DESC, created_at);
CREATE INDEX idx_ai_queue_screenshot ON ai_queue(screenshot_id);

-- ============================================================================
-- User Feedback Table
-- Tracks user corrections and feedback to improve AI accuracy
-- ============================================================================

CREATE TABLE user_feedback (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    screenshot_id TEXT NOT NULL REFERENCES screenshots(id) ON DELETE CASCADE,

    -- Feedback Type
    feedback_type TEXT NOT NULL CHECK(feedback_type IN ('category_correction', 'task_added', 'task_removed', 'text_correction', 'general')),

    -- Feedback Details
    original_value TEXT,                   -- What AI predicted
    corrected_value TEXT,                  -- What user corrected it to
    comment TEXT,                          -- Optional user comment

    -- Metadata
    helpful BOOLEAN,                       -- Thumbs up/down on AI result

    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_feedback_screenshot ON user_feedback(screenshot_id);
CREATE INDEX idx_feedback_type ON user_feedback(feedback_type, created_at DESC);

-- ============================================================================
-- Analytics Table (Optional)
-- Tracks app usage metrics (local, privacy-focused)
-- ============================================================================

CREATE TABLE analytics_events (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),

    -- Event Details
    event_name TEXT NOT NULL,              -- e.g. 'screenshot_processed', 'task_created', 'search_performed'
    event_data TEXT,                       -- JSON metadata (no PII)

    -- Context
    session_id TEXT,                       -- Session identifier

    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_analytics_events_name ON analytics_events(event_name, created_at DESC);

-- ============================================================================
-- Database Maintenance Views
-- Useful views for monitoring and statistics
-- ============================================================================

-- View: Screenshot Statistics
CREATE VIEW screenshot_stats AS
SELECT
    COUNT(*) as total_screenshots,
    COUNT(CASE WHEN status = 'processed' THEN 1 END) as processed_count,
    COUNT(CASE WHEN status = 'pending_ai' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'error' THEN 1 END) as error_count,
    COUNT(CASE WHEN is_archived THEN 1 END) as archived_count,
    COUNT(CASE WHEN is_favorited THEN 1 END) as favorited_count,
    SUM(file_size_bytes) as total_storage_bytes
FROM screenshots;

-- View: Task Statistics
CREATE VIEW task_stats AS
SELECT
    COUNT(*) as total_tasks,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_tasks,
    COUNT(CASE WHEN status = 'done' THEN 1 END) as completed_tasks,
    COUNT(CASE WHEN status = 'archived' THEN 1 END) as archived_tasks,
    COUNT(CASE WHEN due_date IS NOT NULL AND due_date < datetime('now') AND status = 'pending' THEN 1 END) as overdue_tasks,
    COUNT(CASE WHEN ai_extracted THEN 1 END) as ai_extracted_tasks,
    COUNT(CASE WHEN NOT ai_extracted THEN 1 END) as manual_tasks
FROM tasks;

-- View: Category Distribution
CREATE VIEW category_distribution AS
SELECT
    category,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM screenshots WHERE NOT is_archived), 2) as percentage
FROM screenshots
WHERE NOT is_archived AND category IS NOT NULL
GROUP BY category
ORDER BY count DESC;

-- View: Recent Activity (last 7 days)
CREATE VIEW recent_activity AS
SELECT
    date(taken_at) as date,
    COUNT(*) as screenshots_count,
    COUNT(CASE WHEN status = 'processed' THEN 1 END) as processed_count
FROM screenshots
WHERE taken_at >= datetime('now', '-7 days')
GROUP BY date(taken_at)
ORDER BY date DESC;

-- ============================================================================
-- Database Triggers
-- Automatic updates and constraints
-- ============================================================================

-- Trigger: Update updated_at timestamp on screenshots update
CREATE TRIGGER update_screenshots_timestamp
AFTER UPDATE ON screenshots
FOR EACH ROW
BEGIN
    UPDATE screenshots SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Trigger: Update updated_at timestamp on tasks update
CREATE TRIGGER update_tasks_timestamp
AFTER UPDATE ON tasks
FOR EACH ROW
BEGIN
    UPDATE tasks SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Trigger: Set completed_at when task marked as done
CREATE TRIGGER set_task_completed_at
AFTER UPDATE OF status ON tasks
FOR EACH ROW
WHEN NEW.status = 'done' AND OLD.status != 'done'
BEGIN
    UPDATE tasks SET completed_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Trigger: Clear completed_at when task status changes from done to pending
CREATE TRIGGER clear_task_completed_at
AFTER UPDATE OF status ON tasks
FOR EACH ROW
WHEN NEW.status != 'done' AND OLD.status = 'done'
BEGIN
    UPDATE tasks SET completed_at = NULL WHERE id = NEW.id;
END;

-- Trigger: Cleanup AI queue when screenshot is deleted
CREATE TRIGGER cleanup_ai_queue
AFTER DELETE ON screenshots
FOR EACH ROW
BEGIN
    DELETE FROM ai_queue WHERE screenshot_id = OLD.id;
END;

-- ============================================================================
-- Useful Queries (Commented Examples)
-- ============================================================================

-- Example 1: Full-text search
-- SELECT s.*, rank
-- FROM screenshots s
-- JOIN screenshots_fts fts ON s.id = fts.screenshot_id
-- WHERE screenshots_fts MATCH 'invoice AND march'
-- ORDER BY rank
-- LIMIT 20;

-- Example 2: Semantic search (requires sqlite-vss)
-- SELECT s.*, vss_distance_l2(e.embedding, ?) as distance
-- FROM screenshots s
-- JOIN screenshot_embedding_map m ON s.id = m.screenshot_id
-- JOIN screenshot_embeddings e ON m.embedding_rowid = e.rowid
-- WHERE vss_search(e.embedding, vss_search_params(?, 20))
-- ORDER BY distance
-- LIMIT 20;

-- Example 3: Get tasks due today
-- SELECT * FROM tasks
-- WHERE status = 'pending'
--   AND due_date = date('now')
-- ORDER BY priority DESC, created_at;

-- Example 4: Get screenshots by tag
-- SELECT s.*
-- FROM screenshots s
-- JOIN screenshot_tags st ON s.id = st.screenshot_id
-- JOIN tags t ON st.tag_id = t.id
-- WHERE t.name = 'important'
--   AND NOT s.is_archived
-- ORDER BY s.taken_at DESC;

-- Example 5: Get pending reminders
-- SELECT * FROM tasks
-- WHERE reminder_at <= datetime('now')
--   AND NOT reminder_sent
--   AND status = 'pending'
-- ORDER BY reminder_at;

-- ============================================================================
-- Database Maintenance Queries
-- ============================================================================

-- Vacuum database to reclaim space
-- PRAGMA incremental_vacuum;

-- Analyze database for query optimization
-- ANALYZE;

-- Check database integrity
-- PRAGMA integrity_check;

-- Get database size
-- SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================