-- Phase 12 — Stripe Online Payments
-- Run AFTER 011_receipts.sql
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS stripe_session_id   VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS stripe_payment_url  TEXT         DEFAULT NULL;

CREATE TABLE IF NOT EXISTS payment_transactions (
  id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id               INT UNSIGNED  NOT NULL,
  invoice_id              INT UNSIGNED  NOT NULL,
  provider                ENUM('stripe') DEFAULT 'stripe',
  provider_session_id     VARCHAR(255)  DEFAULT NULL,
  provider_payment_intent VARCHAR(255)  DEFAULT NULL,
  amount                  DECIMAL(15,2) NOT NULL,
  currency                VARCHAR(3)    DEFAULT 'USD',
  status                  ENUM('pending','succeeded','failed','expired') DEFAULT 'pending',
  checkout_url            TEXT          DEFAULT NULL,
  created_at              DATETIME      DEFAULT NOW(),
  updated_at              DATETIME      ON UPDATE NOW(),
  INDEX idx_tenant    (tenant_id),
  INDEX idx_invoice   (invoice_id),
  INDEX idx_session   (provider_session_id),
  CONSTRAINT fk_pt_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_pt_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
