-- Enhanced Features Migration
-- Adds support for user management, task enhancements, comments, attachments,
-- dependencies, milestones, and filter presets

PRAGMA foreign_keys = ON;

-- Users table for authentication and multi-tenancy
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user', -- 'admin', 'user', 'viewer'
    tenant_id TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- Create index on email for faster lookups
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id);

-- Task dependencies table
CREATE TABLE IF NOT EXISTS task_dependencies (
    id TEXT PRIMARY KEY NOT NULL,
    task_id TEXT NOT NULL,
    depends_on_task_id TEXT NOT NULL,
    dependency_type TEXT NOT NULL DEFAULT 'blocks', -- 'blocks', 'requires', 'related'
    created_at TEXT NOT NULL,
    FOREIGN KEY (task_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    FOREIGN KEY (depends_on_task_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    UNIQUE(task_id, depends_on_task_id)
);

CREATE INDEX IF NOT EXISTS idx_task_deps_task ON task_dependencies(task_id);
CREATE INDEX IF NOT EXISTS idx_task_deps_depends ON task_dependencies(depends_on_task_id);

-- Task comments table
CREATE TABLE IF NOT EXISTS task_comments (
    id TEXT PRIMARY KEY NOT NULL,
    task_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (task_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_comments_task ON task_comments(task_id);
CREATE INDEX IF NOT EXISTS idx_comments_user ON task_comments(user_id);

-- Task attachments table
CREATE TABLE IF NOT EXISTS task_attachments (
    id TEXT PRIMARY KEY NOT NULL,
    task_id TEXT NOT NULL,
    filename TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    mime_type TEXT NOT NULL,
    storage_path TEXT NOT NULL,
    uploaded_by TEXT NOT NULL,
    uploaded_at TEXT NOT NULL,
    FOREIGN KEY (task_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_attachments_task ON task_attachments(task_id);
CREATE INDEX IF NOT EXISTS idx_attachments_user ON task_attachments(uploaded_by);

-- Filter presets table for saved filters
CREATE TABLE IF NOT EXISTS filter_presets (
    id TEXT PRIMARY KEY NOT NULL,
    user_id TEXT NOT NULL,
    name TEXT NOT NULL,
    filters_json TEXT NOT NULL, -- JSON-encoded filter configuration
    is_default BOOLEAN NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_presets_user ON filter_presets(user_id);

-- Milestones table for project milestones
CREATE TABLE IF NOT EXISTS milestones (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    due_date TEXT,
    completed BOOLEAN NOT NULL DEFAULT 0,
    completed_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_milestones_project ON milestones(project_id);
CREATE INDEX IF NOT EXISTS idx_milestones_due ON milestones(due_date);

-- Add new columns to checklist_items table
-- Note: SQLite doesn't support adding foreign keys to existing tables via ALTER TABLE
-- So we'll add these as regular columns and enforce relationships in application code

ALTER TABLE checklist_items ADD COLUMN assignee_id TEXT;
ALTER TABLE checklist_items ADD COLUMN deadline TEXT;
ALTER TABLE checklist_items ADD COLUMN parent_task_id TEXT;
ALTER TABLE checklist_items ADD COLUMN is_milestone BOOLEAN NOT NULL DEFAULT 0;
ALTER TABLE checklist_items ADD COLUMN recurrence_rule TEXT; -- JSON-encoded recurrence config
ALTER TABLE checklist_items ADD COLUMN priority TEXT DEFAULT 'medium'; -- 'low', 'medium', 'high', 'critical'
ALTER TABLE checklist_items ADD COLUMN tags TEXT; -- JSON array of tags
ALTER TABLE checklist_items ADD COLUMN estimated_hours REAL;
ALTER TABLE checklist_items ADD COLUMN actual_hours REAL;

-- Create indexes for new checklist_items columns
CREATE INDEX IF NOT EXISTS idx_checklist_assignee ON checklist_items(assignee_id);
CREATE INDEX IF NOT EXISTS idx_checklist_deadline ON checklist_items(deadline);
CREATE INDEX IF NOT EXISTS idx_checklist_parent ON checklist_items(parent_task_id);
CREATE INDEX IF NOT EXISTS idx_checklist_milestone ON checklist_items(is_milestone);

-- Session tokens table for JWT session management
CREATE TABLE IF NOT EXISTS session_tokens (
    id TEXT PRIMARY KEY NOT NULL,
    user_id TEXT NOT NULL,
    token_hash TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    revoked BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_tokens_user ON session_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_tokens_hash ON session_tokens(token_hash);
CREATE INDEX IF NOT EXISTS idx_tokens_expires ON session_tokens(expires_at);
