-- 014_billing.sql — Subscription management and purchase history

-- Clean up any partial state from a previously failed run

CREATE TABLE IF NOT EXISTS subscriptions (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NOT NULL UNIQUE,
  status         ENUM('trial','active','expired','cancelled') NOT NULL DEFAULT 'trial',
  trial_ends_at  DATETIME NOT NULL,
  expires_at     DATETIME NULL,
  per_user_price DECIMAL(8,2) NOT NULL DEFAULT 10.00,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_subscriptions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE TABLE IF NOT EXISTS billing_purchases (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id             INT UNSIGNED NOT NULL,
  purchased_by          INT UNSIGNED NOT NULL,
  package_months        INT NOT NULL,
  user_count            INT NOT NULL,
  per_user_price        DECIMAL(8,2) NOT NULL,
  amount_total          DECIMAL(10,2) NOT NULL,
  currency              VARCHAR(3) NOT NULL DEFAULT 'usd',
  expires_at_before     DATETIME NULL,
  expires_at_after      DATETIME NULL,
  stripe_session_id     VARCHAR(255) NULL,
  stripe_payment_intent VARCHAR(255) NULL,
  status                ENUM('pending','succeeded','failed','expired') NOT NULL DEFAULT 'pending',
  created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_billing_purchases_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_billing_purchases_user   FOREIGN KEY (purchased_by) REFERENCES users(id)
);

-- Seed 30-day trials for all existing tenants (IGNORE silently skips duplicates)
INSERT IGNORE INTO subscriptions (tenant_id, status, trial_ends_at)
SELECT id, 'trial', DATE_ADD(NOW(), INTERVAL 30 DAY)
FROM tenants;
