backend/database_schema.sql

-- =============================================================================
-- CONTRAVAULT DATABASE SCHEMA
-- =============================================================================
-- This SQL file contains all table definitions for the Contravault platform.
-- Run this after enabling the pgvector extension in Neon:
--   CREATE EXTENSION IF NOT EXISTS vector;
--   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- =============================================================================

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================================================
-- 1. USERS & AUTHENTICATION
-- =============================================================================

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR UNIQUE NOT NULL,
    password_hash VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    display_name VARCHAR,
    avatar_url VARCHAR,
    title VARCHAR,
    department VARCHAR,
    phone VARCHAR,
    role VARCHAR DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    preferences JSONB DEFAULT '{}',
    timezone VARCHAR DEFAULT 'UTC',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ
);

CREATE INDEX idx_users_email ON users(email);

CREATE TABLE IF NOT EXISTS teams (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS team_members (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR DEFAULT 'member',
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(team_id, user_id)
);

-- =============================================================================
-- 2. OPPORTUNITIES (SAM.gov Discovery)
-- =============================================================================

CREATE TABLE IF NOT EXISTS opportunities (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    solicitation_number VARCHAR UNIQUE NOT NULL,
    notice_id VARCHAR,
    title VARCHAR NOT NULL,
    agency VARCHAR,
    sub_agency VARCHAR,
    office VARCHAR,
    posted_date TIMESTAMPTZ,
    response_deadline TIMESTAMPTZ,
    archive_date TIMESTAMPTZ,
    description TEXT,
    description_plain TEXT,
    naics_code VARCHAR,
    naics_description VARCHAR,
    psc_code VARCHAR,
    set_aside VARCHAR,
    place_of_performance VARCHAR,
    contract_type VARCHAR,
    estimated_value FLOAT,
    pop_start TIMESTAMPTZ,
    pop_end TIMESTAMPTZ,
    link VARCHAR,
    resource_links JSONB,
    fit_score INTEGER DEFAULT 0,
    fit_score_breakdown JSONB,
    ai_summary TEXT,
    ai_tags VARCHAR[],
    status VARCHAR DEFAULT 'New',
    ignore_reason VARCHAR,
    notes TEXT,
    discovered_at TIMESTAMPTZ DEFAULT NOW(),
    last_checked TIMESTAMPTZ,
    change_history JSONB,
    project_id UUID
);

CREATE INDEX idx_opportunities_solicitation ON opportunities(solicitation_number);
CREATE INDEX idx_opportunities_notice ON opportunities(notice_id);
CREATE INDEX idx_opportunities_agency ON opportunities(agency);
CREATE INDEX idx_opportunities_deadline ON opportunities(response_deadline);
CREATE INDEX idx_opportunities_status ON opportunities(status);
CREATE INDEX idx_opportunities_naics ON opportunities(naics_code);
CREATE INDEX idx_opportunities_setaside ON opportunities(set_aside);
CREATE INDEX idx_opportunities_deadline_status ON opportunities(response_deadline, status);
CREATE INDEX idx_opportunities_naics_setaside ON opportunities(naics_code, set_aside);

-- =============================================================================
-- 3. PROJECTS
-- =============================================================================

CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    short_id VARCHAR UNIQUE,
    opportunity_id UUID REFERENCES opportunities(id) ON DELETE SET NULL,
    name VARCHAR NOT NULL,
    client VARCHAR,
    solicitation_number VARCHAR,
    contract_number VARCHAR,
    description TEXT,
    executive_summary TEXT,
    due_date TIMESTAMPTZ,
    questions_due TIMESTAMPTZ,
    award_date TIMESTAMPTZ,
    pop_start TIMESTAMPTZ,
    pop_end TIMESTAMPTZ,
    estimated_value FLOAT,
    bid_amount FLOAT,
    budget_allocated FLOAT,
    naics_code VARCHAR,
    set_aside VARCHAR,
    contract_type VARCHAR,
    status VARCHAR DEFAULT 'Created',
    stage VARCHAR DEFAULT 'discovery',
    health_score INTEGER DEFAULT 100,
    go_no_go_decision VARCHAR,
    go_no_go_date TIMESTAMPTZ,
    go_no_go_notes TEXT,
    owner_id UUID REFERENCES users(id) ON DELETE SET NULL,
    capture_manager VARCHAR,
    proposal_manager VARCHAR,
    tags VARCHAR[],
    custom_fields JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    archived_at TIMESTAMPTZ
);

CREATE INDEX idx_projects_short_id ON projects(short_id);
CREATE INDEX idx_projects_solicitation ON projects(solicitation_number);
CREATE INDEX idx_projects_due_date ON projects(due_date);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_status_due ON projects(status, due_date);

-- Add FK from opportunities back to projects
ALTER TABLE opportunities ADD CONSTRAINT fk_opportunities_project
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL;

-- Project team members (many-to-many)
CREATE TABLE IF NOT EXISTS project_team_members (
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR DEFAULT 'member',
    added_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (project_id, user_id)
);

-- =============================================================================
-- 4. DOCUMENTS
-- =============================================================================

CREATE TABLE IF NOT EXISTS documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    filename VARCHAR NOT NULL,
    original_filename VARCHAR,
    path VARCHAR NOT NULL,
    s3_key VARCHAR,
    file_type VARCHAR,
    mime_type VARCHAR,
    size_bytes BIGINT,
    page_count INTEGER,
    word_count INTEGER,
    category VARCHAR DEFAULT 'Other',
    subcategory VARCHAR,
    section_number VARCHAR,
    is_ingested BOOLEAN DEFAULT FALSE,
    is_shredded BOOLEAN DEFAULT FALSE,
    is_indexed BOOLEAN DEFAULT FALSE,
    ingestion_status VARCHAR DEFAULT 'pending',
    ingestion_error TEXT,
    ingestion_started_at TIMESTAMPTZ,
    ingestion_completed_at TIMESTAMPTZ,
    extracted_text TEXT,
    extracted_metadata JSONB,
    structure JSONB,
    md5_hash VARCHAR,
    sha256_hash VARCHAR,
    uploaded_by UUID REFERENCES users(id) ON DELETE SET NULL,
    uploaded_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_documents_project ON documents(project_id);
CREATE INDEX idx_documents_md5 ON documents(md5_hash);
CREATE INDEX idx_documents_project_category ON documents(project_id, category);

CREATE TABLE IF NOT EXISTS document_versions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    version_number INTEGER NOT NULL,
    path VARCHAR NOT NULL,
    s3_key VARCHAR,
    size_bytes BIGINT,
    md5_hash VARCHAR,
    change_summary TEXT,
    uploaded_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(document_id, version_number)
);

-- =============================================================================
-- 5. DOCUMENT CHUNKS (Vector Store for RAG)
-- =============================================================================

CREATE TABLE IF NOT EXISTS doc_chunks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    content_tokens INTEGER,
    chunk_index INTEGER,
    page_number INTEGER,
    section_ref VARCHAR,
    parent_chunk_id UUID REFERENCES doc_chunks(id) ON DELETE SET NULL,
    context_before TEXT,
    context_after TEXT,
    chunk_type VARCHAR,
    metadata JSONB,
    embedding vector(1536),  -- pgvector for OpenAI embeddings
    embedding_model VARCHAR DEFAULT 'text-embedding-3-small',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_chunks_document ON doc_chunks(document_id);
CREATE INDEX idx_chunks_document_page ON doc_chunks(document_id, page_number);

-- Vector similarity index (for semantic search)
CREATE INDEX idx_chunks_embedding ON doc_chunks
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- =============================================================================
-- 6. TAGS
-- =============================================================================

CREATE TABLE IF NOT EXISTS tags (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR UNIQUE NOT NULL,
    color VARCHAR DEFAULT '#3b82f6',
    category VARCHAR,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS requirement_tags (
    requirement_id UUID NOT NULL,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (requirement_id, tag_id)
);

CREATE TABLE IF NOT EXISTS document_tags (
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (document_id, tag_id)
);

-- =============================================================================
-- 7. REQUIREMENTS (Shredder Output / Compliance Matrix)
-- =============================================================================

CREATE TABLE IF NOT EXISTS requirements (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
    short_id VARCHAR,
    section_ref VARCHAR,
    requirement_text TEXT NOT NULL,
    source_text TEXT,
    category VARCHAR,
    subcategory VARCHAR,
    type VARCHAR DEFAULT 'Mandatory',
    volume VARCHAR,
    page_number INTEGER,
    line_number INTEGER,
    risk_level VARCHAR DEFAULT 'Low',
    risk_score INTEGER DEFAULT 0,
    risk_factors JSONB,
    status VARCHAR DEFAULT 'NotStarted',
    compliance_response TEXT,
    evidence TEXT,
    exceptions TEXT,
    assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
    assigned_by UUID REFERENCES users(id) ON DELETE SET NULL,
    assigned_at TIMESTAMPTZ,
    due_date TIMESTAMPTZ,
    priority VARCHAR DEFAULT 'medium',
    reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMPTZ,
    ai_suggestion TEXT,
    ai_confidence FLOAT,
    notes TEXT,
    internal_notes TEXT,
    parent_id UUID REFERENCES requirements(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_requirements_project ON requirements(project_id);
CREATE INDEX idx_requirements_short_id ON requirements(short_id);
CREATE INDEX idx_requirements_project_status ON requirements(project_id, status);
CREATE INDEX idx_requirements_assigned ON requirements(assigned_to, status);

-- Add FK for requirement_tags
ALTER TABLE requirement_tags ADD CONSTRAINT fk_requirement_tags_requirement
    FOREIGN KEY (requirement_id) REFERENCES requirements(id) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS requirement_responses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    requirement_id UUID NOT NULL REFERENCES requirements(id) ON DELETE CASCADE,
    version INTEGER DEFAULT 1,
    response_text TEXT,
    evidence TEXT,
    status VARCHAR DEFAULT 'draft',
    submitted_by UUID REFERENCES users(id) ON DELETE SET NULL,
    submitted_at TIMESTAMPTZ,
    reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMPTZ,
    review_notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(requirement_id, version)
);

-- =============================================================================
-- 8. PROPOSAL SECTIONS
-- =============================================================================

CREATE TABLE IF NOT EXISTS proposal_sections (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES proposal_sections(id) ON DELETE SET NULL,
    order_index INTEGER DEFAULT 0,
    title VARCHAR NOT NULL,
    section_number VARCHAR,
    volume VARCHAR,
    content_sfdt JSONB,
    content_html TEXT,
    content_markdown TEXT,
    content_plain TEXT,
    word_count INTEGER DEFAULT 0,
    page_estimate FLOAT,
    status VARCHAR DEFAULT 'draft',
    is_locked BOOLEAN DEFAULT FALSE,
    locked_by UUID REFERENCES users(id) ON DELETE SET NULL,
    locked_at TIMESTAMPTZ,
    assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
    due_date TIMESTAMPTZ,
    page_limit INTEGER,
    is_over_limit BOOLEAN DEFAULT FALSE,
    ai_generated BOOLEAN DEFAULT FALSE,
    ai_generation_prompt TEXT,
    ai_model_used VARCHAR,
    version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_edited_by UUID REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX idx_sections_project ON proposal_sections(project_id);
CREATE INDEX idx_sections_project_volume ON proposal_sections(project_id, volume);

CREATE TABLE IF NOT EXISTS proposal_section_versions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    section_id UUID NOT NULL REFERENCES proposal_sections(id) ON DELETE CASCADE,
    version_number INTEGER NOT NULL,
    content_sfdt JSONB,
    content_html TEXT,
    word_count INTEGER,
    change_summary TEXT,
    edited_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(section_id, version_number)
);

CREATE TABLE IF NOT EXISTS proposal_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR NOT NULL,
    description TEXT,
    category VARCHAR,
    content_sfdt JSONB,
    content_html TEXT,
    use_count INTEGER DEFAULT 0,
    tags VARCHAR[],
    is_public BOOLEAN DEFAULT FALSE,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- 9. PRICING
-- =============================================================================

CREATE TABLE IF NOT EXISTS pricing_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    clin_number VARCHAR,
    clin_description TEXT,
    parent_clin_id UUID REFERENCES pricing_items(id) ON DELETE SET NULL,
    quantity FLOAT,
    unit VARCHAR,
    unit_price FLOAT,
    extended_price FLOAT,
    labor_cost FLOAT,
    material_cost FLOAT,
    odc_cost FLOAT,
    indirect_cost FLOAT,
    profit FLOAT,
    labor_category VARCHAR,
    labor_hours FLOAT,
    labor_rate FLOAT,
    period VARCHAR,
    start_date TIMESTAMPTZ,
    end_date TIMESTAMPTZ,
    notes TEXT,
    assumptions TEXT,
    order_index INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS labor_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR NOT NULL,
    description TEXT,
    hourly_rate FLOAT,
    annual_salary FLOAT,
    burden_rate FLOAT,
    education_requirement VARCHAR,
    experience_years INTEGER,
    clearance_level VARCHAR,
    gsa_rate FLOAT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- 10. TASKS & WORKFLOW
-- =============================================================================

CREATE TABLE IF NOT EXISTS tasks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    title VARCHAR NOT NULL,
    description TEXT,
    status VARCHAR DEFAULT 'todo',
    priority VARCHAR DEFAULT 'medium',
    assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    due_date TIMESTAMPTZ,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    requirement_id UUID REFERENCES requirements(id) ON DELETE SET NULL,
    section_id UUID REFERENCES proposal_sections(id) ON DELETE SET NULL,
    estimated_hours FLOAT,
    actual_hours FLOAT,
    tags VARCHAR[],
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS comments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    requirement_id UUID REFERENCES requirements(id) ON DELETE CASCADE,
    section_id UUID REFERENCES proposal_sections(id) ON DELETE CASCADE,
    task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    content_html TEXT,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    mentions UUID[],
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_by UUID REFERENCES users(id) ON DELETE SET NULL,
    resolved_at TIMESTAMPTZ,
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR DEFAULT 'info',
    title VARCHAR NOT NULL,
    message TEXT,
    entity_type VARCHAR,
    entity_id UUID,
    action_url VARCHAR,
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMPTZ,
    email_sent BOOLEAN DEFAULT FALSE,
    email_sent_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ
);

-- =============================================================================
-- 11. AI & KNOWLEDGE
-- =============================================================================

CREATE TABLE IF NOT EXISTS ai_conversations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    title VARCHAR,
    context_type VARCHAR,
    context_id UUID,
    model VARCHAR DEFAULT 'gpt-4',
    temperature FLOAT DEFAULT 0.7,
    system_prompt TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ai_messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES ai_conversations(id) ON DELETE CASCADE,
    role VARCHAR NOT NULL,
    content TEXT NOT NULL,
    prompt_tokens INTEGER,
    completion_tokens INTEGER,
    total_tokens INTEGER,
    retrieved_chunks JSONB,
    rating INTEGER,
    feedback TEXT,
    model VARCHAR,
    latency_ms INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ai_generation_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    generation_type VARCHAR,
    prompt TEXT,
    output TEXT,
    provider VARCHAR,
    model VARCHAR,
    temperature FLOAT,
    prompt_tokens INTEGER,
    completion_tokens INTEGER,
    total_tokens INTEGER,
    cost_usd FLOAT,
    latency_ms INTEGER,
    was_accepted BOOLEAN,
    user_edits TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS company_knowledge (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category VARCHAR NOT NULL,
    subcategory VARCHAR,
    title VARCHAR NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB,
    tags VARCHAR[],
    embedding vector(1536),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Vector index for company knowledge
CREATE INDEX idx_company_knowledge_embedding ON company_knowledge
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 50);

-- =============================================================================
-- 12. ACTIVITY & AUDIT LOGS
-- =============================================================================

CREATE TABLE IF NOT EXISTS user_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_token VARCHAR UNIQUE,
    ip_address INET,
    user_agent TEXT,
    device_type VARCHAR,
    browser VARCHAR,
    os VARCHAR,
    country VARCHAR,
    city VARCHAR,
    started_at TIMESTAMPTZ DEFAULT NOW(),
    last_activity TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT TRUE,
    logout_reason VARCHAR
);

CREATE INDEX idx_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_sessions_user ON user_sessions(user_id);

CREATE TABLE IF NOT EXISTS activity_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    session_id UUID REFERENCES user_sessions(id) ON DELETE SET NULL,
    activity_type VARCHAR NOT NULL,
    action VARCHAR,
    entity_type VARCHAR,
    entity_id UUID,
    entity_name VARCHAR,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    description TEXT,
    details JSONB,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent VARCHAR,
    request_id VARCHAR,
    status VARCHAR DEFAULT 'success',
    error_message TEXT,
    duration_ms INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_activity_type ON activity_logs(activity_type);
CREATE INDEX idx_activity_entity ON activity_logs(entity_type, entity_id);
CREATE INDEX idx_activity_project ON activity_logs(project_id);
CREATE INDEX idx_activity_user_time ON activity_logs(user_id, created_at);
CREATE INDEX idx_activity_project_time ON activity_logs(project_id, created_at);
CREATE INDEX idx_activity_type_time ON activity_logs(activity_type, created_at);
CREATE INDEX idx_activity_created ON activity_logs(created_at);

CREATE TABLE IF NOT EXISTS audit_trails (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID,  -- No FK to preserve data if user deleted
    user_email VARCHAR,
    action VARCHAR NOT NULL,
    table_name VARCHAR NOT NULL,
    record_id UUID,
    old_data JSONB,
    new_data JSONB,
    changed_fields VARCHAR[],
    reason TEXT,
    ip_address INET,
    user_agent TEXT,
    request_path VARCHAR,
    request_method VARCHAR,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_table ON audit_trails(table_name);
CREATE INDEX idx_audit_record ON audit_trails(record_id);
CREATE INDEX idx_audit_table_record ON audit_trails(table_name, record_id);
CREATE INDEX idx_audit_user_time ON audit_trails(user_id, timestamp);
CREATE INDEX idx_audit_timestamp ON audit_trails(timestamp);

CREATE TABLE IF NOT EXISTS error_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    error_type VARCHAR NOT NULL,
    error_message TEXT NOT NULL,
    stack_trace TEXT,
    request_path VARCHAR,
    request_method VARCHAR,
    request_body JSONB,
    environment VARCHAR,
    server_hostname VARCHAR,
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_by UUID,
    resolved_at TIMESTAMPTZ,
    resolution_notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_errors_created ON error_logs(created_at);

-- =============================================================================
-- 13. ANALYTICS
-- =============================================================================

CREATE TABLE IF NOT EXISTS page_views (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    session_id UUID REFERENCES user_sessions(id) ON DELETE SET NULL,
    page_path VARCHAR NOT NULL,
    page_title VARCHAR,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    referrer VARCHAR,
    time_on_page_ms INTEGER,
    viewport_width INTEGER,
    viewport_height INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_pageview_path ON page_views(page_path);
CREATE INDEX idx_pageview_user_time ON page_views(user_id, created_at);
CREATE INDEX idx_pageview_page_time ON page_views(page_path, created_at);
CREATE INDEX idx_pageview_created ON page_views(created_at);

CREATE TABLE IF NOT EXISTS search_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    query VARCHAR NOT NULL,
    search_type VARCHAR,
    filters JSONB,
    result_count INTEGER,
    clicked_results JSONB,
    latency_ms INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS feature_usage (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    feature_name VARCHAR NOT NULL,
    action VARCHAR,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_feature_name ON feature_usage(feature_name);
CREATE INDEX idx_feature_name_time ON feature_usage(feature_name, created_at);

-- =============================================================================
-- 14. SETTINGS
-- =============================================================================

CREATE TABLE IF NOT EXISTS system_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key VARCHAR UNIQUE NOT NULL,
    value JSONB,
    value_type VARCHAR,
    category VARCHAR,
    description TEXT,
    is_secret BOOLEAN DEFAULT FALSE,
    is_editable BOOLEAN DEFAULT TRUE,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_settings_key ON system_settings(key);

CREATE TABLE IF NOT EXISTS user_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key VARCHAR NOT NULL,
    value JSONB,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, key)
);

CREATE TABLE IF NOT EXISTS pursuit_policies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    parameter_name VARCHAR UNIQUE NOT NULL,
    threshold_value VARCHAR NOT NULL,
    comparison_operator VARCHAR,
    weight FLOAT DEFAULT 1.0,
    category VARCHAR,
    description TEXT,
    is_mandatory BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- 15. SUBMISSION
-- =============================================================================

CREATE TABLE IF NOT EXISTS submission_packages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    version INTEGER DEFAULT 1,
    status VARCHAR DEFAULT 'draft',
    submission_method VARCHAR,
    submitted_at TIMESTAMPTZ,
    submitted_by UUID REFERENCES users(id) ON DELETE SET NULL,
    confirmation_number VARCHAR,
    is_validated BOOLEAN DEFAULT FALSE,
    validation_errors JSONB,
    is_sanitized BOOLEAN DEFAULT FALSE,
    sanitization_report JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS submission_files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    package_id UUID NOT NULL REFERENCES submission_packages(id) ON DELETE CASCADE,
    filename VARCHAR NOT NULL,
    path VARCHAR NOT NULL,
    s3_key VARCHAR,
    file_type VARCHAR,
    size_bytes BIGINT,
    volume VARCHAR,
    order_index INTEGER DEFAULT 0,
    page_count INTEGER,
    word_count INTEGER,
    is_compliant BOOLEAN,
    md5_hash VARCHAR,
    sha256_hash VARCHAR,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- 16. ANALYSIS & INTELLIGENCE
-- =============================================================================

CREATE TABLE IF NOT EXISTS analysis_results (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    analysis_type VARCHAR NOT NULL,
    result_json JSONB NOT NULL,
    overall_score FLOAT,
    confidence FLOAT,
    model_used VARCHAR,
    status VARCHAR DEFAULT 'complete',
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX idx_analysis_project ON analysis_results(project_id);
CREATE INDEX idx_analysis_type ON analysis_results(analysis_type);

CREATE TABLE IF NOT EXISTS competitors (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR NOT NULL,
    duns_number VARCHAR,
    cage_code VARCHAR,
    website VARCHAR,
    description TEXT,
    capabilities JSONB,
    proprietary_technologies JSONB,
    certifications JSONB,
    contract_vehicles JSONB,
    past_performance_agencies JSONB,
    incumbent_contracts JSONB,
    strengths JSONB,
    weaknesses JSONB,
    annual_revenue FLOAT,
    employee_count INTEGER,
    size_standard VARCHAR,
    socioeconomic_status JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_competitors_name ON competitors(name);

CREATE TABLE IF NOT EXISTS competitor_encounters (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    competitor_id UUID NOT NULL REFERENCES competitors(id) ON DELETE CASCADE,
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    opportunity_id UUID REFERENCES opportunities(id) ON DELETE CASCADE,
    role VARCHAR,
    confidence FLOAT,
    source VARCHAR,
    outcome VARCHAR,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- 17. INTEGRATIONS
-- =============================================================================

CREATE TABLE IF NOT EXISTS integration_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR UNIQUE NOT NULL,
    provider VARCHAR NOT NULL,
    config JSONB,
    credentials JSONB,
    is_enabled BOOLEAN DEFAULT TRUE,
    last_sync TIMESTAMPTZ,
    last_error TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webhook_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    direction VARCHAR NOT NULL,
    provider VARCHAR,
    event_type VARCHAR,
    url VARCHAR,
    method VARCHAR,
    headers JSONB,
    payload JSONB,
    status_code INTEGER,
    response_body JSONB,
    is_success BOOLEAN,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS scheduled_jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR NOT NULL,
    job_type VARCHAR NOT NULL,
    cron_expression VARCHAR,
    interval_minutes INTEGER,
    config JSONB,
    is_enabled BOOLEAN DEFAULT TRUE,
    last_run TIMESTAMPTZ,
    last_status VARCHAR,
    last_error TEXT,
    next_run TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS job_executions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    job_id UUID REFERENCES scheduled_jobs(id) ON DELETE CASCADE,
    job_name VARCHAR,
    started_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    duration_ms INTEGER,
    status VARCHAR,
    error_message TEXT,
    result JSONB,
    records_processed INTEGER
);

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