-- ============================================================
-- AI enrichment, campaign settings, inbox tracking
-- ============================================================

CREATE TYPE enriched_email_status AS ENUM (
  'pending_review',
  'approved',
  'rejected',
  'sent',
  'failed'
);

CREATE TYPE inbox_message_class AS ENUM (
  'unsubscribe',
  'reply',
  'other'
);

-- Extra LLM / Apollo context per contact
ALTER TABLE contacts
  ADD COLUMN IF NOT EXISTS enrichment_data jsonb NOT NULL DEFAULT '{}';

-- One row per campaign (enrichment + mailbox behaviour)
CREATE TABLE campaign_settings (
  campaign_id                    uuid PRIMARY KEY REFERENCES campaigns(id) ON DELETE CASCADE,
  enrichment_enabled             boolean NOT NULL DEFAULT false,
  enrichment_tone                text,
  enrichment_instructions      text,
  send_from_email                text,
  send_from_name                 text,
  reply_unsubscribe_enabled      boolean NOT NULL DEFAULT false,
  unsubscribe_response_template text,
  enrich_unsubscribe_response    boolean NOT NULL DEFAULT false,
  auto_forward_emails            text[] NOT NULL DEFAULT '{}',
  auto_bcc_emails                text[] NOT NULL DEFAULT '{}',
  created_at                     timestamptz NOT NULL DEFAULT now(),
  updated_at                     timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER campaign_settings_updated_at
  BEFORE UPDATE ON campaign_settings
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE campaign_enriched_emails (
  id                uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  campaign_id       uuid NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
  contact_id        uuid NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  original_subject  text NOT NULL,
  original_body     text NOT NULL,
  enriched_subject  text,
  enriched_body     text,
  status            enriched_email_status NOT NULL DEFAULT 'pending_review',
  reviewed_at       timestamptz,
  sent_at           timestamptz,
  error_message       text,
  outbound_message_id text,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (campaign_id, contact_id)
);

CREATE INDEX idx_cen_campaign ON campaign_enriched_emails(campaign_id);
CREATE INDEX idx_cen_status   ON campaign_enriched_emails(status);

CREATE TRIGGER campaign_enriched_emails_updated_at
  BEFORE UPDATE ON campaign_enriched_emails
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE inbox_messages (
  id             uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  campaign_id    uuid REFERENCES campaigns(id) ON DELETE SET NULL,
  contact_id     uuid REFERENCES contacts(id) ON DELETE SET NULL,
  message_id     text,
  in_reply_to    text,
  subject        text,
  body_text      text,
  classified_as  inbox_message_class NOT NULL DEFAULT 'other',
  forwarded_to   text[] NOT NULL DEFAULT '{}',
  processed_at   timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_inbox_campaign ON inbox_messages(campaign_id);
CREATE INDEX idx_inbox_contact  ON inbox_messages(contact_id);
CREATE INDEX idx_inbox_created  ON inbox_messages(created_at);

GRANT SELECT, INSERT, UPDATE, DELETE ON campaign_settings TO anon, authenticated, service_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON campaign_enriched_emails TO anon, authenticated, service_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON inbox_messages TO anon, authenticated, service_role;
