-- =============================================================================
-- Genesis Unified Schema
-- Supplemental tables not covered by rlm_create_tables.sql,
-- alpha_evolve_create_tables.sql, or aiva_rlm_schema.sql.
-- Safe to run multiple times (all CREATE TABLE IF NOT EXISTS).
-- Date: 2026-02-23
-- =============================================================================

-- Tradie leads pipeline
CREATE TABLE IF NOT EXISTS tradie_leads (
    id SERIAL PRIMARY KEY,
    business_name TEXT,
    phone TEXT UNIQUE,
    email TEXT,
    website TEXT,
    suburb TEXT,
    city TEXT,
    trade_type TEXT,
    rating FLOAT,
    review_count INT,
    has_website BOOLEAN,
    website_score INT,
    priority_score FLOAT,
    outreach_status TEXT DEFAULT 'pending',
    demo_url TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tradie_leads_status ON tradie_leads(outreach_status);
CREATE INDEX IF NOT EXISTS idx_tradie_leads_city ON tradie_leads(city);
CREATE INDEX IF NOT EXISTS idx_tradie_leads_priority ON tradie_leads(priority_score DESC);
CREATE INDEX IF NOT EXISTS idx_tradie_leads_trade ON tradie_leads(trade_type);
CREATE INDEX IF NOT EXISTS idx_tradie_leads_created ON tradie_leads(created_at DESC);

-- Agent execution log
CREATE TABLE IF NOT EXISTS agent_execution_log (
    id SERIAL PRIMARY KEY,
    agent_id TEXT,
    agent_type TEXT,
    task_description TEXT,
    status TEXT,
    tokens_used INT,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    output_summary TEXT
);

CREATE INDEX IF NOT EXISTS idx_agent_exec_agent_id ON agent_execution_log(agent_id);
CREATE INDEX IF NOT EXISTS idx_agent_exec_status ON agent_execution_log(status);
CREATE INDEX IF NOT EXISTS idx_agent_exec_started ON agent_execution_log(started_at DESC);

-- Product evolution log
CREATE TABLE IF NOT EXISTS product_evolution_log (
    id SERIAL PRIMARY KEY,
    product_id TEXT,
    version TEXT,
    what_changed TEXT,
    what_improved TEXT,
    agent_id TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_product_evo_product ON product_evolution_log(product_id);
CREATE INDEX IF NOT EXISTS idx_product_evo_created ON product_evolution_log(created_at DESC);

-- Session memory (for RLM bloodstream)
CREATE TABLE IF NOT EXISTS session_memory (
    id SERIAL PRIMARY KEY,
    session_id TEXT,
    key_learning TEXT,
    category TEXT,
    surprise_score FLOAT,
    source TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_session_memory_session ON session_memory(session_id);
CREATE INDEX IF NOT EXISTS idx_session_memory_category ON session_memory(category);
CREATE INDEX IF NOT EXISTS idx_session_memory_surprise ON session_memory(surprise_score DESC);
CREATE INDEX IF NOT EXISTS idx_session_memory_created ON session_memory(created_at DESC);

-- Content calendar
CREATE TABLE IF NOT EXISTS content_calendar (
    id SERIAL PRIMARY KEY,
    platform TEXT,
    content TEXT,
    topic TEXT,
    scheduled_at TIMESTAMP,
    posted_at TIMESTAMP,
    status TEXT DEFAULT 'scheduled'
);

CREATE INDEX IF NOT EXISTS idx_content_cal_platform ON content_calendar(platform);
CREATE INDEX IF NOT EXISTS idx_content_cal_status ON content_calendar(status);
CREATE INDEX IF NOT EXISTS idx_content_cal_scheduled ON content_calendar(scheduled_at);
