CREATE TABLE IF NOT EXISTS sources (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  feed_url VARCHAR(500) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_feed_url (feed_url)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  source_id INT NOT NULL,
  guid_hash CHAR(64) NOT NULL,
  title VARCHAR(500) NOT NULL,
  url VARCHAR(800) NOT NULL,
  domain VARCHAR(200) NOT NULL,
  published_at DATETIME NULL,
  summary MEDIUMTEXT NULL,
  image_url VARCHAR(800) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_guid_hash (guid_hash),
  KEY idx_published_at (published_at),
  KEY idx_domain (domain),
  CONSTRAINT fk_items_sources FOREIGN KEY (source_id) REFERENCES sources(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscribers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) NOT NULL,
  name VARCHAR(120) NULL,
  status ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
  unsub_token CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unsubscribed_at DATETIME NULL,
  UNIQUE KEY uniq_email (email),
  UNIQUE KEY uniq_unsub_token (unsub_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS send_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  subscriber_id BIGINT NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('sent','failed') NOT NULL,
  error_message VARCHAR(600) NULL,
  CONSTRAINT fk_sendlogs_subscribers FOREIGN KEY (subscriber_id) REFERENCES subscribers(id),
  KEY idx_period (period_start, period_end),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
