CREATE TABLE newsletter_subscribers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  first_name VARCHAR(255) NULL,
  last_name VARCHAR(255) NULL,
  status ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
  unsub_token CHAR(64) NOT NULL,
  subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unsubscribed_at DATETIME NULL,
  INDEX (status),
  UNIQUE KEY (unsub_token)
);

-- Példa: felvételkor generálj tokent:
-- INSERT INTO newsletter_subscribers (email, name, unsub_token)
-- VALUES ('valaki@example.com','Valaki', SHA2(CONCAT(UUID(), RAND()), 256));
-- UPDATE newsletter_subscribers SET unsub_token=SHA2(CONCAT(UUID(), RAND()), 256) where id=4

CREATE TABLE newsletter_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscriber_id INT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  status ENUM('sent','failed') NOT NULL,
  error_message TEXT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE
);

