-- Create project_archives table for database-backed archival system
-- Replaces filesystem archives with searchable, semantic-enabled storage

-- Drop if exists to recreate with correct schema
DROP TABLE IF EXISTS project_archives;

CREATE TABLE project_archives (
    id SERIAL PRIMARY KEY,
    project_key TEXT NOT NULL REFERENCES projects(key),
    archive_type VARCHAR(50) NOT NULL,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    original_path VARCHAR(1000),
    file_size INTEGER,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    archived_by_session VARCHAR(100),
    metadata JSONB DEFAULT '{}',
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_archives_project ON project_archives(project_key);
CREATE INDEX idx_archives_type ON project_archives(archive_type);
CREATE INDEX idx_archives_archived_at ON project_archives(archived_at);
CREATE INDEX idx_archives_embedding ON project_archives USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX idx_archives_metadata ON project_archives USING gin(metadata);

-- Archive types: session, research, audit, investigation, completed, migration
COMMENT ON TABLE project_archives IS 'Database-backed archive storage with semantic search';
COMMENT ON COLUMN project_archives.archive_type IS 'Type: session, research, audit, investigation, completed, migration';
COMMENT ON COLUMN project_archives.metadata IS 'JSONB for frontmatter, tags, etc';