/*
 TechStackBase – Subscription Plans
 Version: v1.0.0
 Purpose: Define subscription plans & pricing

 DESIGN PRINCIPLES
 -----------------
 • Server-authoritative pricing
 • Stable plan codes (never reused)
 • Billing-cycle aware
 • Subscription-safe
 • UI & API aligned
*/

/* ============================================================
   SUBSCRIPTION PLANS TABLE
   ============================================================ */

CREATE TABLE subscription_plans (
    /* Stable identifier */
    code VARCHAR(64) PRIMARY KEY,

    /* Display */
    name VARCHAR(128) NOT NULL,
    description TEXT NULL,

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

    /* Billing */
    billing_cycle ENUM(
        'monthly',
        'yearly'
    ) NOT NULL DEFAULT 'monthly',

    /* Status */
    active TINYINT(1) NOT NULL DEFAULT 1,

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

    /* Indexes */
    INDEX idx_subscription_plans_active (active),
    INDEX idx_subscription_plans_cycle (billing_cycle)
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='Subscription plans (pricing authoritative)';
