/*
 TechStackBase – Payments
 Version: v1.0.0
 Purpose: Record and track all payment transactions

 DESIGN PRINCIPLES
 -----------------
 • Server-authoritative (amount & currency)
 • Gateway-agnostic (PayFast now, others later)
 • Idempotent (reference is unique)
 • Audit-friendly (no hard deletes)
 • Subscription-linked
*/

/* ============================================================
   PAYMENTS TABLE
   ============================================================ */

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    /* Ownership */
    company_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,

    /* Gateway reference (authoritative) */
    reference VARCHAR(64) NOT NULL,

    /* Plan snapshot */
    plan_code VARCHAR(64) NOT NULL,

    /* Financials (authoritative) */
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'ZAR',

    /* Gateway */
    gateway VARCHAR(32) NOT NULL DEFAULT 'payfast',

    /* Payment status */
    status ENUM(
        'pending',     -- Created, awaiting gateway response
        'paid',        -- Confirmed paid (ITN verified)
        'failed',      -- Failed at gateway
        'cancelled',   -- Cancelled by user
        'reversed'     -- Reversed/refunded
    ) NOT NULL DEFAULT 'pending',

    /* Gateway metadata (raw ITN or response) */
    gateway_payload JSON NULL,

    /* Metadata */
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    /* Indexes */
    UNIQUE KEY uq_payments_reference (reference),
    INDEX idx_payments_company (company_id),
    INDEX idx_payments_subscription (subscription_id),
    INDEX idx_payments_status (status),
    INDEX idx_payments_created (created_at),

    /* Foreign keys (soft integrity) */
    CONSTRAINT fk_payments_company
        FOREIGN KEY (company_id)
        REFERENCES companies(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_payments_subscription
        FOREIGN KEY (subscription_id)
        REFERENCES company_subscriptions(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_payments_plan
        FOREIGN KEY (plan_code)
        REFERENCES subscription_plans(code)
        ON DELETE RESTRICT
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='Payment transactions (gateway authoritative)';
