PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS party_emails (
  id TEXT PRIMARY KEY,
  party_id TEXT NOT NULL,
  email TEXT NOT NULL,
  is_primary INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_party_emails_email ON party_emails(email);
CREATE INDEX IF NOT EXISTS idx_party_emails_party_id ON party_emails(party_id);

CREATE TABLE IF NOT EXISTS newsletter_lists (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id TEXT PRIMARY KEY,
  party_id TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS newsletter_subscriptions (
  id TEXT PRIMARY KEY,
  subscriber_id TEXT NOT NULL,
  list_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  source TEXT,
  consent TEXT,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  confirmed_at TEXT,
  unsubscribed_at TEXT,
  UNIQUE (subscriber_id, list_id),
  FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE,
  FOREIGN KEY (list_id) REFERENCES newsletter_lists(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_newsletter_subscriptions_subscriber ON newsletter_subscriptions(subscriber_id);
CREATE INDEX IF NOT EXISTS idx_newsletter_subscriptions_list ON newsletter_subscriptions(list_id);

CREATE TABLE IF NOT EXISTS newsletter_tokens (
  token TEXT PRIMARY KEY,
  subscriber_id TEXT NOT NULL,
  list_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  consumed_at TEXT,
  FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE,
  FOREIGN KEY (list_id) REFERENCES newsletter_lists(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_newsletter_tokens_expires ON newsletter_tokens(expires_at);
