SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS ad_products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  placement_key VARCHAR(64) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  monthly_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  stripe_product_id VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ad_products_active (is_active, sort_order),
  KEY idx_ad_products_key (placement_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_product_id INT UNSIGNED NOT NULL,
  subscriber_name VARCHAR(180) NOT NULL,
  subscriber_email VARCHAR(255) NOT NULL,
  business_name VARCHAR(255) DEFAULT NULL,
  notes VARCHAR(1000) DEFAULT NULL,
  stripe_customer_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL UNIQUE,
  status VARCHAR(40) NOT NULL DEFAULT 'incomplete',
  current_period_start DATETIME DEFAULT NULL,
  current_period_end DATETIME DEFAULT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ad_subs_status (status, current_period_end),
  KEY idx_ad_subs_email (subscriber_email),
  KEY idx_ad_subs_product (ad_product_id),
  CONSTRAINT fk_ad_subs_product FOREIGN KEY (ad_product_id) REFERENCES ad_products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_revenue_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_subscription_id BIGINT UNSIGNED NOT NULL,
  stripe_invoice_id VARCHAR(255) DEFAULT NULL UNIQUE,
  amount_cents INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'eur',
  paid_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ad_revenue_paid_at (paid_at),
  KEY idx_ad_revenue_sub (ad_subscription_id),
  CONSTRAINT fk_ad_revenue_sub FOREIGN KEY (ad_subscription_id) REFERENCES ad_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;