-- ============================================================
-- Email Campaigner — Application Schema
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ─── ENUMS ──────────────────────────────────────────────────

CREATE TYPE campaign_status AS ENUM (
  'draft', 'scheduled', 'sending', 'sent', 'paused'
);

CREATE TYPE recipient_status AS ENUM (
  'pending', 'sent', 'delivered', 'bounced', 'failed'
);

CREATE TYPE email_event_type AS ENUM (
  'sent', 'opened', 'clicked', 'bounced', 'unsubscribed'
);

CREATE TYPE transactional_status AS ENUM (
  'queued', 'sent', 'failed'
);

-- ─── CONTACTS ───────────────────────────────────────────────

CREATE TABLE contacts (
  id            uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  email         text NOT NULL UNIQUE,
  first_name    text,
  last_name     text,
  custom_fields jsonb NOT NULL DEFAULT '{}',
  subscribed    boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_contacts_email      ON contacts(email);
CREATE INDEX idx_contacts_subscribed ON contacts(subscribed);

-- ─── LISTS ──────────────────────────────────────────────────

CREATE TABLE lists (
  id          uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        text NOT NULL,
  description text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- ─── LIST_CONTACTS ───────────────────────────────────────────

CREATE TABLE list_contacts (
  list_id       uuid NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
  contact_id    uuid NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  subscribed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (list_id, contact_id)
);

CREATE INDEX idx_list_contacts_contact ON list_contacts(contact_id);

-- ─── TEMPLATES ──────────────────────────────────────────────

CREATE TABLE templates (
  id             uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name           text NOT NULL,
  grapesjs_json  jsonb,
  compiled_html  text,
  thumbnail_url  text,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

-- ─── CAMPAIGNS ──────────────────────────────────────────────

CREATE TABLE campaigns (
  id           uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name         text NOT NULL,
  subject      text NOT NULL,
  from_name    text NOT NULL,
  from_email   text NOT NULL,
  reply_to     text,
  list_id      uuid REFERENCES lists(id) ON DELETE SET NULL,
  template_id  uuid REFERENCES templates(id) ON DELETE SET NULL,
  status       campaign_status NOT NULL DEFAULT 'draft',
  scheduled_at timestamptz,
  sent_at      timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_campaigns_status  ON campaigns(status);
CREATE INDEX idx_campaigns_list_id ON campaigns(list_id);

-- ─── EMAIL_EVENTS ────────────────────────────────────────────

CREATE TABLE email_events (
  id          uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  campaign_id uuid REFERENCES campaigns(id) ON DELETE CASCADE,
  contact_id  uuid REFERENCES contacts(id) ON DELETE CASCADE,
  type        email_event_type NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_ee_campaign ON email_events(campaign_id);
CREATE INDEX idx_ee_contact  ON email_events(contact_id);
CREATE INDEX idx_ee_type     ON email_events(type);

-- ─── TRACKED_LINK_CLICKS ─────────────────────────────────────

CREATE TABLE tracked_link_clicks (
  id              uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tracked_link_id uuid NOT NULL REFERENCES tracked_links(id) ON DELETE CASCADE,
  recipient_id    uuid REFERENCES campaign_recipients(id) ON DELETE CASCADE,
  clicked_at      timestamptz NOT NULL DEFAULT now(),
  user_agent      text,
  ip_address      text
);

CREATE INDEX idx_tlc_link ON tracked_link_clicks(tracked_link_id);

-- ─── TRANSACTIONAL_EMAILS ────────────────────────────────────

CREATE TABLE transactional_emails (
  id         uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  to_email   text NOT NULL,
  subject    text NOT NULL,
  from_email text NOT NULL,
  html_body  text,
  status     transactional_status NOT NULL DEFAULT 'queued',
  sent_at    timestamptz,
  metadata   jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);

-- ─── UPDATED_AT TRIGGERS ─────────────────────────────────────

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER contacts_updated_at  BEFORE UPDATE ON contacts  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER templates_updated_at BEFORE UPDATE ON templates FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER campaigns_updated_at BEFORE UPDATE ON campaigns FOR EACH ROW EXECUTE FUNCTION set_updated_at();

GRANT SELECT ON campaign_stats TO anon, authenticated, service_role;

-- ─── DEFAULT GRANTS FOR FUTURE TABLES ────────────────────────

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO anon, authenticated, service_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;
