pagodera/lib/prisma/queries/subscription-tiers.ts

/**
 * Queries para gerenciamento de subscription tiers
 */

import { Prisma } from "@prisma/client";
import { prisma } from "../app";

export interface SubscriptionTierSummary {
  subscription_tier_id: number;
  name: string;
  slug: string;
  description: string | null;
  level: number;
  price_monthly: Prisma.Decimal | null;
  price_yearly: Prisma.Decimal | null;
  is_active: boolean;
  created_at: Date;
  updated_at: Date;
  features?: Array<{
    feature_id: number;
    feature_key: string;
    label: string;
    description: string | null;
    sort_order: number;
  }>;
}

export interface CreateSubscriptionTierInput {
  name: string;
  slug: string;
  description?: string | null;
  level?: number;
  price_monthly?: number | null;
  price_yearly?: number | null;
  is_active?: boolean;
  features?: Array<{
    feature_key: string;
    label: string;
    description?: string | null;
    sort_order?: number;
  }>;
}

export interface UpdateSubscriptionTierInput {
  name?: string;
  slug?: string;
  description?: string | null;
  level?: number;
  price_monthly?: number | null;
  price_yearly?: number | null;
  is_active?: boolean;
  features?: Array<{
    feature_id?: number;
    feature_key: string;
    label: string;
    description?: string | null;
    sort_order?: number;
    _delete?: boolean;
  }>;
}

const tierSelect = {
  subscription_tier_id: true,
  name: true,
  slug: true,
  description: true,
  level: true,
  price_monthly: true,
  price_yearly: true,
  is_active: true,
  created_at: true,
  updated_at: true,
};

const featureSelect = {
  feature_id: true,
  feature_key: true,
  label: true,
  description: true,
  sort_order: true,
};

export async function getAllSubscriptionTiers(
  includeFeatures = false,
): Promise<SubscriptionTierSummary[]> {
  const tiers = await prisma.subscriptionTier.findMany({
    select: {
      ...tierSelect,
      features: includeFeatures
        ? {
            select: featureSelect,
            orderBy: { sort_order: "asc" },
          }
        : false,
    },
    orderBy: [{ level: "asc" }, { subscription_tier_id: "asc" }],
  });

  return tiers as SubscriptionTierSummary[];
}

export async function getSubscriptionTierBySlug(
  slug: string,
  includeFeatures = false,
): Promise<SubscriptionTierSummary | null> {
  const tier = await prisma.subscriptionTier.findUnique({
    where: { slug },
    select: {
      ...tierSelect,
      features: includeFeatures
        ? {
            select: featureSelect,
            orderBy: { sort_order: "asc" },
          }
        : false,
    },
  });

  return tier as SubscriptionTierSummary | null;
}

export async function getSubscriptionTierById(
  tierId: number,
  includeFeatures = false,
): Promise<SubscriptionTierSummary | null> {
  const tier = await prisma.subscriptionTier.findUnique({
    where: { subscription_tier_id: tierId },
    select: {
      ...tierSelect,
      features: includeFeatures
        ? {
            select: featureSelect,
            orderBy: { sort_order: "asc" },
          }
        : false,
    },
  });

  return tier as SubscriptionTierSummary | null;
}

export async function getDefaultSubscriptionTier(): Promise<SubscriptionTierSummary | null> {
  const tier = await prisma.subscriptionTier.findFirst({
    where: { slug: "free" },
    select: tierSelect,
  });

  return tier as SubscriptionTierSummary | null;
}

export async function createSubscriptionTier(
  input: CreateSubscriptionTierInput,
): Promise<SubscriptionTierSummary> {
  const tier = await prisma.subscriptionTier.create({
    data: {
      name: input.name,
      slug: input.slug,
      description: input.description ?? null,
      level: input.level ?? 0,
      price_monthly:
        input.price_monthly !== undefined && input.price_monthly !== null
          ? new Prisma.Decimal(input.price_monthly)
          : null,
      price_yearly:
        input.price_yearly !== undefined && input.price_yearly !== null
          ? new Prisma.Decimal(input.price_yearly)
          : null,
      is_active: input.is_active ?? true,
      features: input.features
        ? {
            create: input.features.map((feature, index) => ({
              feature_key: feature.feature_key,
              label: feature.label,
              description: feature.description ?? null,
              sort_order: feature.sort_order ?? index,
            })),
          }
        : undefined,
    },
    select: {
      ...tierSelect,
      features: {
        select: featureSelect,
        orderBy: { sort_order: "asc" },
      },
    },
  });

  return tier as SubscriptionTierSummary;
}

export async function updateSubscriptionTier(
  tierId: number,
  input: UpdateSubscriptionTierInput,
): Promise<SubscriptionTierSummary> {
  const _tier = await prisma.subscriptionTier.update({
    where: { subscription_tier_id: tierId },
    data: {
      name: input.name ?? undefined,
      slug: input.slug ?? undefined,
      description:
        input.description !== undefined ? input.description : undefined,
      level: input.level ?? undefined,
      price_monthly:
        input.price_monthly !== undefined
          ? input.price_monthly === null
            ? null
            : new Prisma.Decimal(input.price_monthly)
          : undefined,
      price_yearly:
        input.price_yearly !== undefined
          ? input.price_yearly === null
            ? null
            : new Prisma.Decimal(input.price_yearly)
          : undefined,
      is_active: input.is_active ?? undefined,
    },
    select: {
      ...tierSelect,
      features: {
        select: featureSelect,
        orderBy: { sort_order: "asc" },
      },
    },
  });

  if (input.features) {
    await prisma.subscriptionTierFeature.deleteMany({
      where: { subscription_tier_id: tierId },
    });

    if (input.features.length > 0) {
      await prisma.subscriptionTierFeature.createMany({
        data: input.features
          .filter((feature) => !feature._delete)
          .map((feature, index) => ({
            subscription_tier_id: tierId,
            feature_key: feature.feature_key,
            label: feature.label,
            description: feature.description ?? null,
            sort_order: feature.sort_order ?? index,
          })),
      });
    }
  }

  const updatedTier = await prisma.subscriptionTier.findUnique({
    where: { subscription_tier_id: tierId },
    select: {
      ...tierSelect,
      features: {
        select: featureSelect,
        orderBy: { sort_order: "asc" },
      },
    },
  });

  return updatedTier as SubscriptionTierSummary;
}

export async function deleteSubscriptionTier(tierId: number): Promise<void> {
  await prisma.subscriptionTier.delete({
    where: { subscription_tier_id: tierId },
  });
}

export async function assignUserToSubscriptionTier(
  userId: number,
  tierId: number | null,
): Promise<void> {
  await prisma.user.update({
    where: { user_id: userId },
    data:
      tierId === null
        ? {
            subscription_tier: {
              disconnect: true,
            },
          }
        : {
            subscription_tier: {
              connect: {
                subscription_tier_id: tierId,
              },
            },
          },
  });
}

export async function countUsersInSubscriptionTier(
  tierId: number,
): Promise<number> {
  return prisma.user.count({
    where: { subscription_tier_id: tierId },
  });
}