backend/deep_research_schema.sql

-- 1. Sources: The list of Canadian websites
CREATE TABLE IF NOT EXISTS rfp_sources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    scraper_strategy TEXT DEFAULT 'standard_html', -- 'standard_html', 'playwright_js', 'pdf_index'
    last_scraped_at TIMESTAMP,
    status TEXT DEFAULT 'active'
);

-- 2. Raw Scrape Logs: Saving the "Raw Data" before processing
CREATE TABLE IF NOT EXISTS scrape_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_id UUID, -- Using UUID but not a hard fk to source_id if it's url in logic
    scraped_at TIMESTAMP DEFAULT NOW(),
    raw_html_content TEXT, -- Compressed storage of the full page
    status TEXT -- 'success', 'failed', 'blocked'
);

-- 3. Opportunities: The actual RFPs found
CREATE TABLE IF NOT EXISTS rfp_opportunities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_id UUID,
    external_id TEXT, -- The unique ID from the source site (e.g., SEAO-12345)
    title TEXT NOT NULL,
    description TEXT,
    publication_date TIMESTAMP,
    closing_date TIMESTAMP,
    link_to_docs TEXT,
    
    -- AI Filtering Status
    ai_fit_score INTEGER, -- 0-100 score based on your company profile
    ai_analysis_summary TEXT,
    status TEXT DEFAULT 'new' -- 'new', 'drafting', 'submitted', 'rejected'
);

-- 4. AI Audit Trail: Tracking which model did what
CREATE TABLE IF NOT EXISTS ai_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rfp_id UUID,
    model_used TEXT NOT NULL, -- e.g., 'google/gemma-3-27b-it'
    provider TEXT NOT NULL, -- 'openrouter', 'cerebras'
    prompt_sent TEXT,
    response_received TEXT,
    execution_time_ms INTEGER,
    timestamp TIMESTAMP DEFAULT NOW()
);