-- ============================================================
-- 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);

-- ─── CAMPAIGN_RECIPIENTS ────────────────────────────────────

CREATE TABLE campaign_recipients (
  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,
  status      recipient_status NOT NULL DEFAULT 'pending',
  track_token text UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(16), 'hex'),
  sent_at     timestamptz,
  opened_at   timestamptz,
  clicked_at  timestamptz,
  UNIQUE(campaign_id, contact_id)
);

CREATE INDEX idx_cr_campaign ON campaign_recipients(campaign_id);
CREATE INDEX idx_cr_contact  ON campaign_recipients(contact_id);
CREATE INDEX idx_cr_token    ON campaign_recipients(track_token);

-- ─── 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_LINKS ───────────────────────────────────────────

CREATE TABLE tracked_links (
  id              uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  campaign_id     uuid NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
  original_url    text NOT NULL,
  tracking_token  text UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(16), 'hex'),
  click_count     integer NOT NULL DEFAULT 0,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_tl_token    ON tracked_links(tracking_token);
CREATE INDEX idx_tl_campaign ON tracked_links(campaign_id);

-- ─── 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()
);

-- ─── APP SETTINGS ────────────────────────────────────────────

CREATE TABLE app_settings (
  key        text PRIMARY KEY,
  value      jsonb NOT NULL DEFAULT 'null',
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO app_settings (key, value) VALUES
  ('smtp_host',          '"ec-mailhog"'),
  ('smtp_port',          '1025'),
  ('smtp_user',          '""'),
  ('smtp_pass',          '""'),
  ('smtp_secure',        'false'),
  ('default_from_name',  '"Email Campaigner"'),
  ('default_from_email', '"noreply@example.com"'),
  ('company_name',       '"My Company"'),
  ('app_base_url',       '"http://localhost:3002"');

-- ─── 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();

-- ─── CAMPAIGN STATS VIEW ─────────────────────────────────────

CREATE VIEW campaign_stats AS
SELECT
  c.id                                                                             AS campaign_id,
  c.name,
  c.status,
  c.sent_at,
  COUNT(DISTINCT cr.id)                                                            AS total_recipients,
  COUNT(DISTINCT cr.id) FILTER (WHERE cr.status = 'sent')                         AS sent_count,
  COUNT(DISTINCT cr.id) FILTER (WHERE cr.opened_at IS NOT NULL)                   AS open_count,
  COUNT(DISTINCT cr.id) FILTER (WHERE cr.clicked_at IS NOT NULL)                  AS click_count,
  COUNT(DISTINCT cr.id) FILTER (WHERE cr.status = 'bounced')                      AS bounce_count,
  COUNT(DISTINCT ee.id) FILTER (WHERE ee.type = 'unsubscribed')                   AS unsubscribe_count,
  ROUND(
    100.0 * COUNT(DISTINCT cr.id) FILTER (WHERE cr.opened_at IS NOT NULL)
    / NULLIF(COUNT(DISTINCT cr.id) FILTER (WHERE cr.status = 'sent'), 0), 2
  )                                                                                AS open_rate,
  ROUND(
    100.0 * COUNT(DISTINCT cr.id) FILTER (WHERE cr.clicked_at IS NOT NULL)
    / NULLIF(COUNT(DISTINCT cr.id) FILTER (WHERE cr.status = 'sent'), 0), 2
  )                                                                                AS click_rate
FROM campaigns c
LEFT JOIN campaign_recipients cr ON cr.campaign_id = c.id
LEFT JOIN email_events ee        ON ee.campaign_id = c.id
GROUP BY c.id, c.name, c.status, c.sent_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;
