import type { Migration } from '@/shared/db';

export const outreachMigrations: readonly Migration[] = [
  {
    id: '2026-05-06_outreach_initial',
    sql: `
      CREATE SCHEMA IF NOT EXISTS outreach;

      CREATE TABLE IF NOT EXISTS outreach.heartbeat (
        loop_name     TEXT PRIMARY KEY,
        last_seen_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
        last_event_id UUID,
        version       TEXT NOT NULL DEFAULT 'unknown'
      );

      CREATE TABLE IF NOT EXISTS outreach.prospect_state (
        prospect_id           BIGINT PRIMARY KEY,
        handle                TEXT NOT NULL,
        status                TEXT NOT NULL DEFAULT 'idle',
        last_proposed_hash    TEXT,
        send_queue_id         UUID,
        awaiting_quinn_since  TIMESTAMPTZ,
        ttl_at                TIMESTAMPTZ,
        price_floor_violated  BOOLEAN NOT NULL DEFAULT false,
        created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_ps_status ON outreach.prospect_state(status);
      CREATE INDEX IF NOT EXISTS idx_ps_handle ON outreach.prospect_state(handle);

      CREATE TABLE IF NOT EXISTS outreach.event_log (
        id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        prospect_id   BIGINT,
        loop_name     TEXT NOT NULL,
        event_type    TEXT NOT NULL,
        intent_hash   TEXT,
        payload       JSONB NOT NULL DEFAULT '{}'::jsonb,
        error_message TEXT,
        created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_el_prospect ON outreach.event_log(prospect_id, created_at DESC);
      CREATE INDEX IF NOT EXISTS idx_el_type     ON outreach.event_log(event_type, created_at DESC);
      CREATE INDEX IF NOT EXISTS idx_el_hash     ON outreach.event_log(intent_hash) WHERE intent_hash IS NOT NULL;
    `,
  },
];
