/*
 TechStackBase – Company Subscriptions
 Version: v1.0.0
 Purpose: Track subscription lifecycle per company

 DESIGN PRINCIPLES
 -----------------
 • No hard deletes (audit-safe)
 • Clear state machine (pending → active → cancelled/expired)
 • One active subscription per company enforced in logic
 • Billing & entitlement authoritative
*/

/* ============================================================
   COMPANY SUBSCRIPTIONS TABLE
   ============================================================ */

CREATE TABLE company_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    /* Ownership */
    company_id BIGINT UNSIGNED NOT NULL,

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

    /* Lifecycle status */
    status ENUM(
        'pending',    -- Created, awaiting payment
        'active',     -- Paid & enabled
        'cancelled',  -- Cancelled by admin or superseded
        'expired'     -- Expired naturally
    ) NOT NULL DEFAULT 'pending',

    /* Dates */
    started_at DATETIME NULL,
    expires_at DATETIME NULL,

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

    /* Indexes */
    INDEX idx_company (company_id),
    INDEX idx_plan (plan_code),
    INDEX idx_status (status),
    INDEX idx_expires (expires_at),

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

    CONSTRAINT fk_company_subscriptions_plan
        FOREIGN KEY (plan_code)
        REFERENCES subscription_plans(code)
        ON DELETE RESTRICT
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='Company subscription lifecycle table';
