-- Subscription Ops — run once via phpMyAdmin (or `mysql < schema.sql` locally).

CREATE TABLE IF NOT EXISTS subscribers (
  original_transaction_id VARCHAR(64) NOT NULL PRIMARY KEY,
  product_id VARCHAR(128) NOT NULL,
  environment ENUM('Sandbox','Production') NOT NULL,
  status ENUM('active','grace_period','billing_retry','expired','refunded') NOT NULL,
  auto_renew_status TINYINT(1) NULL,
  expires_date DATETIME NULL,
  original_purchase_date DATETIME NULL,
  last_price_cents INT NULL,
  currency VARCHAR(3) NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  notification_uuid VARCHAR(64) NOT NULL,
  notification_type VARCHAR(64) NOT NULL,
  subtype VARCHAR(64) NULL,
  original_transaction_id VARCHAR(64) NOT NULL,
  product_id VARCHAR(128) NULL,
  environment ENUM('Sandbox','Production') NOT NULL,
  price_cents INT NULL,
  currency VARCHAR(3) NULL,
  signed_date DATETIME NOT NULL,
  received_at DATETIME NOT NULL,
  raw_payload JSON NOT NULL,
  UNIQUE KEY uniq_notification_uuid (notification_uuid),
  INDEX idx_original_transaction_id (original_transaction_id),
  INDEX idx_notification_type (notification_type),
  INDEX idx_received_at (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;