-- One SQLite file per operator. Small on purpose: a trip, its prospects, and
-- what a human did with each one. No CRM, no accounts, no sync.

CREATE TABLE trip (
  id            INTEGER PRIMARY KEY,
  city          TEXT NOT NULL,
  starts_on     TEXT NOT NULL,
  ends_on       TEXT NOT NULL,
  icp_json      TEXT NOT NULL,      -- titles, headcount, keywords as asked for
  target_count  INTEGER NOT NULL,
  created_at    TEXT NOT NULL
);

CREATE TABLE prospect (
  id                    INTEGER PRIMARY KEY,
  trip_id               INTEGER NOT NULL REFERENCES trip(id),
  apollo_id             TEXT UNIQUE,        -- dedupe across trips
  first_name TEXT, last_name TEXT, title TEXT, seniority TEXT,
  company TEXT, domain TEXT, headcount INTEGER, city TEXT, state TEXT,
  linkedin_url          TEXT,
  email                 TEXT,               -- null until enrichment is spent
  email_status          TEXT DEFAULT 'not_requested',

  icp_score             INTEGER,            -- 1-5, mechanical
  personalization_score INTEGER,            -- 1-5, strength of the evidence
  reason                TEXT,               -- why this person is worth a meeting

  trigger_kind          TEXT,               -- funding | launch | partnership | ...
  trigger_summary       TEXT,
  trigger_source        TEXT,               -- a URL, or the row is flagged
  trigger_date          TEXT,

  icebreaker            TEXT,
  email_subject         TEXT,
  email_body            TEXT,               -- 60-100 words, checked
  linkedin_message      TEXT,               -- 30-60 words, checked

  status                TEXT NOT NULL,      -- ready | flagged | approved | rejected
  flag_reason           TEXT,
  approved_at           TEXT,               -- nothing leaves without this
  approved_by           TEXT,
  pushed_heyreach_at    TEXT,
  exported_email_at     TEXT
);

CREATE INDEX prospect_trip ON prospect(trip_id, status);

-- Every outbound API call, so credit spend is auditable after the fact.
CREATE TABLE api_call (
  id         INTEGER PRIMARY KEY,
  at         TEXT NOT NULL,
  service    TEXT NOT NULL,   -- apollo | heyreach
  endpoint   TEXT NOT NULL,
  credits    INTEGER DEFAULT 0,
  ok         INTEGER,
  note       TEXT
);