migrations/0024_subscriptions.sql

-- 0024_subscriptions.sql
-- Subscription & Recurring Billing Engine for Rtix
-- Supports SaaS merchants offering recurring payment products.

CREATE TABLE IF NOT EXISTS subscription_plans (
    id              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    merchant_id     VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    name            VARCHAR(255) NOT NULL,
    description     TEXT,
    price_inr       NUMERIC(12, 2) NOT NULL CHECK (price_inr > 0),
    interval_days   INT NOT NULL CHECK (interval_days > 0),  -- e.g. 30 = monthly, 365 = annual
    trial_days      INT NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id                  VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    merchant_id         VARCHAR(36) NOT NULL REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    plan_id             VARCHAR(36) NOT NULL REFERENCES subscription_plans(id),
    subscriber_email    VARCHAR(255) NOT NULL,
    subscriber_phone    VARCHAR(20),
    subscriber_name     VARCHAR(255) NOT NULL,
    status              VARCHAR(20) NOT NULL DEFAULT 'TRIAL'
                            CHECK (status IN ('TRIAL', 'ACTIVE', 'PAST_DUE', 'CANCELLED', 'EXPIRED')),
    current_period_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    current_period_end   TIMESTAMPTZ NOT NULL,
    cancelled_at        TIMESTAMPTZ,
    cancel_reason       TEXT,
    metadata            JSONB DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS subscription_billing_events (
    id              VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    subscription_id VARCHAR(36) NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
    merchant_id     VARCHAR(36) NOT NULL,
    amount_inr      NUMERIC(12, 2) NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'PENDING'
                        CHECK (status IN ('PENDING', 'SUCCESS', 'FAILED', 'REFUNDED')),
    transaction_id  VARCHAR(36),    -- links to orders.transaction_id on success
    attempt_count   INT NOT NULL DEFAULT 1,
    billed_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    settled_at      TIMESTAMPTZ,
    failure_reason  TEXT
);

CREATE INDEX IF NOT EXISTS idx_subscriptions_merchant ON subscriptions(merchant_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_subscriptions_period_end ON subscriptions(current_period_end) WHERE status = 'ACTIVE';
CREATE INDEX IF NOT EXISTS idx_billing_events_subscription ON subscription_billing_events(subscription_id);
CREATE INDEX IF NOT EXISTS idx_billing_events_merchant ON subscription_billing_events(merchant_id);
CREATE INDEX IF NOT EXISTS idx_subscription_plans_merchant ON subscription_plans(merchant_id);