-- Alpha Evolve Daemon -- Database Schema
-- Run against Elestio PostgreSQL (genesis database)

CREATE SCHEMA IF NOT EXISTS genesis;

-- Main asset table: all evolvable assets
CREATE TABLE IF NOT EXISTS genesis.evolution_assets (
    asset_id        VARCHAR(255) PRIMARY KEY,
    asset_type      VARCHAR(50) NOT NULL,  -- email_template, voice_prompt, widget_config, execution_script
    content         JSONB NOT NULL,        -- The actual template/prompt/config
    version         INTEGER NOT NULL DEFAULT 1,
    parent_id       VARCHAR(255),          -- NULL for original assets
    fitness_score   FLOAT NOT NULL DEFAULT 0.0,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
        -- active, archived, superseded, mutation
    metrics         JSONB NOT NULL DEFAULT '{}',
    metadata        JSONB NOT NULL DEFAULT '{}',
        -- campaign_id, assistant_id, widget_id, etc.
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ,
    archived_at     TIMESTAMPTZ,
    archived_reason VARCHAR(100),
    superseded_by   VARCHAR(255),

    CONSTRAINT valid_status CHECK (status IN ('active', 'archived', 'superseded', 'mutation')),
    CONSTRAINT valid_asset_type CHECK (asset_type IN ('email_template', 'voice_prompt', 'widget_config', 'execution_script'))
);

CREATE INDEX IF NOT EXISTS idx_evo_assets_type_status ON genesis.evolution_assets(asset_type, status);
CREATE INDEX IF NOT EXISTS idx_evo_assets_fitness ON genesis.evolution_assets(fitness_score DESC);
CREATE INDEX IF NOT EXISTS idx_evo_assets_parent ON genesis.evolution_assets(parent_id);

-- Evolution cycle history
CREATE TABLE IF NOT EXISTS genesis.evolution_cycles (
    cycle_id            VARCHAR(20) PRIMARY KEY,  -- YYYYMMDD
    started_at          TIMESTAMPTZ NOT NULL,
    completed_at        TIMESTAMPTZ NOT NULL,
    assets_scored       INTEGER NOT NULL DEFAULT 0,
    assets_culled       INTEGER NOT NULL DEFAULT 0,
    mutations_generated INTEGER NOT NULL DEFAULT 0,
    mutations_tested    INTEGER NOT NULL DEFAULT 0,
    promotions          INTEGER NOT NULL DEFAULT 0,
    major_improvements  JSONB DEFAULT '[]',
    errors              JSONB DEFAULT '[]',
    dry_run             BOOLEAN NOT NULL DEFAULT FALSE,
    cycle_version       VARCHAR(20) NOT NULL
);

-- Mutation test results (populated by Jules or automated testing)
CREATE TABLE IF NOT EXISTS genesis.mutation_test_results (
    mutation_id         VARCHAR(255) PRIMARY KEY,
    parent_id           VARCHAR(255) NOT NULL,
    asset_type          VARCHAR(50) NOT NULL,
    mutation_content    JSONB NOT NULL,
    mutation_fitness    FLOAT,
    parent_fitness      FLOAT NOT NULL,
    test_verdict        VARCHAR(20),  -- win, lose, inconclusive
    test_details        JSONB DEFAULT '{}',
    promoted            BOOLEAN,
    promoted_asset_id   VARCHAR(255),
    cycle_id            VARCHAR(20) NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    evaluated_at        TIMESTAMPTZ,
    status              VARCHAR(20) NOT NULL DEFAULT 'pending'
        -- pending, completed, failed
);

CREATE INDEX IF NOT EXISTS idx_mut_results_status ON genesis.mutation_test_results(status);
CREATE INDEX IF NOT EXISTS idx_mut_results_cycle ON genesis.mutation_test_results(cycle_id);

-- Call insights (Telnyx webhook data -- may already exist)
CREATE TABLE IF NOT EXISTS genesis.call_insights (
    id                  SERIAL PRIMARY KEY,
    assistant_id        VARCHAR(255) NOT NULL,
    call_id             VARCHAR(255),
    duration_seconds    FLOAT,
    sentiment_score     FLOAT,
    booking_made        BOOLEAN DEFAULT FALSE,
    transcript_summary  TEXT,
    caller_number       VARCHAR(30),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_call_insights_assistant ON genesis.call_insights(assistant_id, created_at);

-- Widget events (may already exist)
CREATE TABLE IF NOT EXISTS genesis.widget_events (
    id                  SERIAL PRIMARY KEY,
    widget_id           VARCHAR(255) NOT NULL,
    event_type          VARCHAR(30) NOT NULL,  -- impression, interaction, lead_captured
    event_data          JSONB DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_widget_events_widget ON genesis.widget_events(widget_id, created_at);
