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

export const prospectMigrations: readonly Migration[] = [
  {
    id: '2026-05-12_prospect_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.prospect (
        handle              TEXT NOT NULL,
        device_id           UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        tier                TEXT NOT NULL CHECK (tier IN ('BLOCK','PEER','PROSPECT','REGULAR','VIP')),
        heat                TEXT NOT NULL CHECK (heat IN ('HOT','WARM','COLD','DEAD')),
        metro               TEXT,
        first_contact_at    TIMESTAMPTZ,
        last_contact_at     TIMESTAMPTZ,
        last_inbound_at     TIMESTAMPTZ,
        last_outbound_at    TIMESTAMPTZ,
        msg_count_in        INT NOT NULL DEFAULT 0,
        msg_count_out       INT NOT NULL DEFAULT 0,
        rate_balk_count     INT NOT NULL DEFAULT 0,
        vip_flag            BOOL NOT NULL DEFAULT false,
        peer_flag           BOOL NOT NULL DEFAULT false,
        regular_flag        BOOL NOT NULL DEFAULT false,
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        PRIMARY KEY (handle, device_id)
      );
      CREATE INDEX IF NOT EXISTS idx_prospect_tier_heat ON macsync.prospect(tier, heat);
      CREATE INDEX IF NOT EXISTS idx_prospect_device_last ON macsync.prospect(device_id, last_contact_at DESC);
    `,
  },
];
