CREATE TABLE IF NOT EXISTS payment_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  stripe_invoice_id VARCHAR(255) NULL,
  stripe_subscription_id VARCHAR(255) NULL,
  stripe_payment_intent_id VARCHAR(255) NULL,
  plan_key VARCHAR(50) NULL,
  amount_cents INT NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'usd',
  status ENUM('succeeded','failed','pending','refunded') NOT NULL,
  failure_reason TEXT NULL,
  payment_date DATETIME NOT NULL,
  billing_period_start DATETIME NULL,
  billing_period_end DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT NOW(),
  INDEX idx_payment_user (user_id),
  INDEX idx_payment_invoice (stripe_invoice_id),
  CONSTRAINT fk_payment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
