-- Billing/paywall support
-- Price model: $500/month, sold in blocks of 3, 6, or 12 months.

ALTER TABLE tenants
  ADD COLUMN IF NOT EXISTS billing_paid_until DATETIME AFTER trial_ends_at,
  ADD COLUMN IF NOT EXISTS stripe_customer_id VARCHAR(255) AFTER billing_paid_until,
  ADD COLUMN IF NOT EXISTS stripe_default_payment_method VARCHAR(255) AFTER stripe_customer_id,
  ADD COLUMN IF NOT EXISTS billing_currency CHAR(3) NOT NULL DEFAULT 'usd' AFTER stripe_default_payment_method,
  ADD COLUMN IF NOT EXISTS billing_price_per_month_cents INT UNSIGNED NOT NULL DEFAULT 50000 AFTER billing_currency;

-- Normalize historical trial windows to the new 30-day offer if empty.
UPDATE tenants
SET trial_ends_at = DATE_ADD(created_at, INTERVAL 30 DAY)
WHERE trial_ends_at IS NULL;

CREATE TABLE IF NOT EXISTS billing_payments (
  id                         CHAR(36)          NOT NULL DEFAULT (UUID()),
  tenant_id                  CHAR(36)          NOT NULL,
  user_id                    CHAR(36),
  stripe_payment_intent_id   VARCHAR(255)      NOT NULL,
  stripe_charge_id           VARCHAR(255),
  amount_cents               INT UNSIGNED      NOT NULL,
  currency                   CHAR(3)           NOT NULL DEFAULT 'usd',
  months_purchased           SMALLINT UNSIGNED NOT NULL,
  status                     ENUM('requires_payment_method','requires_confirmation','requires_action','processing','succeeded','canceled','failed') NOT NULL,
  failure_reason             TEXT,
  receipt_url                TEXT,
  paid_until_before          DATETIME,
  paid_until_after           DATETIME,
  paid_at                    DATETIME,
  created_at                 DATETIME          NOT NULL DEFAULT NOW(),
  updated_at                 DATETIME          NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_billing_payment_intent (stripe_payment_intent_id),
  INDEX idx_billing_tenant_created (tenant_id, created_at),
  CONSTRAINT fk_billing_payments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_billing_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;