-- Migration 016: Session Documentation System
-- Purpose: Migrate from file-based documentation (CLAUDE.md, plan files) to database
-- Dependencies: 010_sessions.sql (sessions table), 001_base_schema.sql (pgvector)

-- ============================================================================
-- SESSION NOTES: Structured notes within sessions
-- ============================================================================

CREATE TABLE IF NOT EXISTS session_notes (
    id SERIAL PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    note_type TEXT NOT NULL CHECK (note_type IN (
        'accomplishment',
        'decision',
        'gotcha',
        'next_steps',
        'context'
    )),
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    embedding vector(1024)  -- For semantic search of notes
);

-- Indexes for efficient querying
CREATE INDEX idx_session_notes_session ON session_notes(session_id);
CREATE INDEX idx_session_notes_type ON session_notes(note_type);
CREATE INDEX idx_session_notes_created ON session_notes(created_at DESC);

-- HNSW index for semantic similarity search
CREATE INDEX idx_session_notes_embedding ON session_notes
    USING hnsw (embedding vector_cosine_ops);

COMMENT ON TABLE session_notes IS 'Structured notes within sessions (replaces ad-hoc note files)';
COMMENT ON COLUMN session_notes.note_type IS 'Category of note: accomplishment, decision, gotcha, next_steps, context';
COMMENT ON COLUMN session_notes.embedding IS 'Vector embedding for semantic search across notes';

-- ============================================================================
-- SESSION PLANS: Plan mode plans with lifecycle tracking
-- ============================================================================

CREATE TABLE IF NOT EXISTS session_plans (
    id SERIAL PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    plan_file_name TEXT,  -- Original filename (e.g., "transient-forging-reddy.md")
    plan_content TEXT NOT NULL,
    status TEXT DEFAULT 'draft' CHECK (status IN (
        'draft',
        'approved',
        'executed',
        'abandoned'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    approved_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    embedding vector(1024)  -- For semantic search of plans
);

-- Indexes for efficient querying
CREATE INDEX idx_session_plans_session ON session_plans(session_id);
CREATE INDEX idx_session_plans_status ON session_plans(status);
CREATE INDEX idx_session_plans_created ON session_plans(created_at DESC);

-- HNSW index for semantic similarity search
CREATE INDEX idx_session_plans_embedding ON session_plans
    USING hnsw (embedding vector_cosine_ops);

COMMENT ON TABLE session_plans IS 'Plan mode plans with lifecycle tracking (replaces ~/.claude/plans/)';
COMMENT ON COLUMN session_plans.plan_file_name IS 'Original filename from plan mode (e.g., eloquent-yellow-cat.md)';
COMMENT ON COLUMN session_plans.status IS 'Plan lifecycle: draft → approved → executed/abandoned';
COMMENT ON COLUMN session_plans.embedding IS 'Vector embedding for semantic search across plans';

-- ============================================================================
-- PROJECT DOCUMENTATION: Persistent project-level docs
-- ============================================================================

CREATE TABLE IF NOT EXISTS project_documentation (
    id SERIAL PRIMARY KEY,
    project TEXT NOT NULL REFERENCES projects(key) ON DELETE CASCADE,
    doc_type TEXT NOT NULL CHECK (doc_type IN (
        'overview',
        'architecture',
        'guidelines',
        'history'
    )),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    last_updated_session TEXT REFERENCES sessions(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(project, doc_type)  -- One doc of each type per project
);

-- Indexes for efficient querying
CREATE INDEX idx_project_docs_project ON project_documentation(project);
CREATE INDEX idx_project_docs_type ON project_documentation(doc_type);
CREATE INDEX idx_project_docs_updated ON project_documentation(updated_at DESC);

COMMENT ON TABLE project_documentation IS 'Persistent project documentation (replaces CLAUDE.md sections)';
COMMENT ON COLUMN project_documentation.doc_type IS 'Type of documentation: overview, architecture, guidelines, history';
COMMENT ON COLUMN project_documentation.last_updated_session IS 'Session that last updated this documentation';

-- ============================================================================
-- SESSIONS TABLE ENHANCEMENTS
-- ============================================================================

-- Add documentation column for full markdown documentation
ALTER TABLE sessions
    ADD COLUMN IF NOT EXISTS documentation TEXT;

-- Add migration flag to track CLAUDE.md migration status
ALTER TABLE sessions
    ADD COLUMN IF NOT EXISTS claude_md_migrated BOOLEAN DEFAULT FALSE;

COMMENT ON COLUMN sessions.documentation IS 'Full markdown documentation for session (auto-generated at end)';
COMMENT ON COLUMN sessions.claude_md_migrated IS 'Flag indicating this session migrated from CLAUDE.md file';

-- ============================================================================
-- HELPER VIEWS
-- ============================================================================

-- View: Session with note counts
CREATE OR REPLACE VIEW session_documentation_summary AS
SELECT
    s.id,
    s.project,
    s.session_number,
    s.started_at,
    s.ended_at,
    s.status,
    s.summary,
    COUNT(DISTINCT sn.id) as note_count,
    COUNT(DISTINCT sp.id) as plan_count,
    ARRAY_AGG(DISTINCT sn.note_type) FILTER (WHERE sn.note_type IS NOT NULL) as note_types,
    MAX(sn.created_at) as last_note_at,
    MAX(sp.created_at) as last_plan_at
FROM sessions s
LEFT JOIN session_notes sn ON s.id = sn.session_id
LEFT JOIN session_plans sp ON s.id = sp.session_id
GROUP BY s.id, s.project, s.session_number, s.started_at, s.ended_at, s.status, s.summary;

COMMENT ON VIEW session_documentation_summary IS 'Sessions with note and plan counts for quick overview';

-- View: Latest project documentation
CREATE OR REPLACE VIEW latest_project_docs AS
SELECT
    pd.project,
    pd.doc_type,
    pd.title,
    LEFT(pd.content, 200) as content_preview,
    pd.updated_at,
    s.session_number as last_updated_session_number,
    s.started_at as last_updated_session_date
FROM project_documentation pd
LEFT JOIN sessions s ON pd.last_updated_session = s.id
ORDER BY pd.project, pd.doc_type;

COMMENT ON VIEW latest_project_docs IS 'Latest project documentation with session context';

-- ============================================================================
-- MAINTENANCE FUNCTIONS
-- ============================================================================

-- Function to clean up old session notes (optional retention policy)
CREATE OR REPLACE FUNCTION cleanup_old_session_notes(retention_days INTEGER DEFAULT 180)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM session_notes
    WHERE created_at < NOW() - (retention_days || ' days')::INTERVAL
      AND session_id IN (
          SELECT id FROM sessions WHERE status = 'completed'
      );

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_old_session_notes IS 'Clean up notes from completed sessions older than retention period (default 180 days)';

-- Function to generate session documentation stats
CREATE OR REPLACE FUNCTION session_documentation_stats(p_project TEXT DEFAULT NULL)
RETURNS TABLE(
    total_sessions BIGINT,
    sessions_with_notes BIGINT,
    sessions_with_plans BIGINT,
    total_notes BIGINT,
    total_plans BIGINT,
    avg_notes_per_session NUMERIC,
    avg_plans_per_session NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(DISTINCT s.id) as total_sessions,
        COUNT(DISTINCT CASE WHEN sn.id IS NOT NULL THEN s.id END) as sessions_with_notes,
        COUNT(DISTINCT CASE WHEN sp.id IS NOT NULL THEN s.id END) as sessions_with_plans,
        COUNT(sn.id) as total_notes,
        COUNT(sp.id) as total_plans,
        ROUND(COUNT(sn.id)::NUMERIC / NULLIF(COUNT(DISTINCT s.id), 0), 2) as avg_notes_per_session,
        ROUND(COUNT(sp.id)::NUMERIC / NULLIF(COUNT(DISTINCT s.id), 0), 2) as avg_plans_per_session
    FROM sessions s
    LEFT JOIN session_notes sn ON s.id = sn.session_id
    LEFT JOIN session_plans sp ON s.id = sp.session_id
    WHERE (p_project IS NULL OR s.project = p_project)
      AND s.status = 'completed';
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION session_documentation_stats IS 'Generate statistics about session documentation usage';