michangarrito/database/schemas/10-subscriptions.sql

-- =============================================================================
-- MICHANGARRITO - 10 SUBSCRIPTIONS
-- =============================================================================
-- Planes, suscripciones y tokens IA
-- =============================================================================

-- Planes
CREATE TABLE IF NOT EXISTS subscriptions.plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,

    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'MXN',

    included_tokens INTEGER NOT NULL,
    features JSONB,

    max_products INTEGER,
    max_users INTEGER DEFAULT 1,
    whatsapp_own_number BOOLEAN DEFAULT false,

    status VARCHAR(20) DEFAULT 'active',

    stripe_price_id_monthly VARCHAR(100),
    stripe_price_id_yearly VARCHAR(100),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER update_plans_updated_at
    BEFORE UPDATE ON subscriptions.plans
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Suscripciones
CREATE TABLE IF NOT EXISTS subscriptions.subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    plan_id UUID NOT NULL REFERENCES subscriptions.plans(id),

    billing_cycle VARCHAR(10) DEFAULT 'monthly',
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end TIMESTAMPTZ NOT NULL,

    status VARCHAR(20) DEFAULT 'active',
    cancel_at_period_end BOOLEAN DEFAULT false,
    cancelled_at TIMESTAMPTZ,

    payment_method VARCHAR(20),

    stripe_subscription_id VARCHAR(100),
    stripe_customer_id VARCHAR(100),

    trial_ends_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_tenant ON subscriptions.subscriptions(tenant_id);
CREATE INDEX idx_subscriptions_stripe ON subscriptions.subscriptions(stripe_subscription_id);

CREATE TRIGGER update_subscriptions_updated_at
    BEFORE UPDATE ON subscriptions.subscriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Actualizar FK en tenants
ALTER TABLE public.tenants
ADD CONSTRAINT fk_tenants_plan
FOREIGN KEY (current_plan_id) REFERENCES subscriptions.plans(id);

-- Paquetes de tokens
CREATE TABLE IF NOT EXISTS subscriptions.token_packages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name VARCHAR(50) NOT NULL,
    tokens INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'MXN',

    bonus_tokens INTEGER DEFAULT 0,

    stripe_price_id VARCHAR(100),

    status VARCHAR(20) DEFAULT 'active',

    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Uso de tokens
CREATE TABLE IF NOT EXISTS subscriptions.token_usage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    tokens_used INTEGER NOT NULL,

    action VARCHAR(50) NOT NULL,
    description TEXT,

    model VARCHAR(50),
    input_tokens INTEGER,
    output_tokens INTEGER,

    reference_type VARCHAR(20),
    reference_id UUID,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_token_usage_tenant ON subscriptions.token_usage(tenant_id);
CREATE INDEX idx_token_usage_date ON subscriptions.token_usage(created_at);

-- Balance de tokens por tenant
CREATE TABLE IF NOT EXISTS subscriptions.tenant_token_balance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    available_tokens INTEGER DEFAULT 0,
    used_tokens INTEGER DEFAULT 0,

    last_reset_at TIMESTAMPTZ,

    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);