iistwin/server/storage/billing.storage.ts

import { users, organizations, forms, tasks } from "@shared/schema";
import { db } from "../db";
import { eq, desc, sql } from "drizzle-orm";
import { TaskMetaStorage } from "./task-meta.storage";
export class BillingStorage extends TaskMetaStorage {
  // Billing
  // =====================

  async getBillingSettings(organizationId: number) {
    const { organizationBilling } = await import('@shared/schema');
    const [row] = await db.select().from(organizationBilling).where(eq(organizationBilling.organizationId, organizationId));
    return row ?? null;
  }

  async getOrCreateBillingSettings(organizationId: number) {
    const { organizationBilling } = await import('@shared/schema');
    const existing = await this.getBillingSettings(organizationId);
    if (existing) return existing;
    const nextBillingDate = new Date();
    nextBillingDate.setMonth(nextBillingDate.getMonth() + 1);
    const dateStr = nextBillingDate.toISOString().split('T')[0];
    const [row] = await db.insert(organizationBilling).values({
      organizationId,
      pricePerUser: "0",
      balance: "0",
      currency: "RUB",
      nextBillingDate: dateStr,
    }).returning();
    return row;
  }

  async updateBillingSettings(organizationId: number, updates: Partial<import('@shared/schema').OrganizationBilling>) {
    const { organizationBilling } = await import('@shared/schema');
    await this.getOrCreateBillingSettings(organizationId);
    const [row] = await db.update(organizationBilling)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(organizationBilling.organizationId, organizationId))
      .returning();
    return row;
  }

  async addBillingCredit(organizationId: number, amount: number, description: string, createdBy?: number) {
    const { billingTransactions } = await import('@shared/schema');
    const billing = await this.getOrCreateBillingSettings(organizationId);
    const newBalance = (parseFloat(billing.balance) + amount).toFixed(2);
    await this.updateBillingSettings(organizationId, { balance: newBalance });
    const [tx] = await db.insert(billingTransactions).values({
      organizationId,
      amount: amount.toFixed(2),
      type: 'credit',
      description,
      createdBy: createdBy ?? null,
    }).returning();
    if (amount > 0) {
      const org = await this.getOrganization(organizationId);
      if (org?.billingBlocked) {
        await this.setBillingBlocked(organizationId, false);
      }
    }
    return tx;
  }

  async getBillingTransactions(organizationId: number, limit = 20) {
    const { billingTransactions } = await import('@shared/schema');
    return db.select().from(billingTransactions)
      .where(eq(billingTransactions.organizationId, organizationId))
      .orderBy(desc(billingTransactions.createdAt))
      .limit(limit);
  }

  async setBillingBlocked(organizationId: number, blocked: boolean) {
    await db.update(organizations)
      .set({ billingBlocked: blocked, updatedAt: new Date() })
      .where(eq(organizations.id, organizationId));
    if (blocked) {
      await this.updateBillingSettings(organizationId, { blockedAt: new Date() });
    } else {
      await this.updateBillingSettings(organizationId, { blockedAt: null });
    }
  }

  async getBillingSummary(organizationId: number) {
    const billing = await this.getOrCreateBillingSettings(organizationId);
    const org = await this.getOrganization(organizationId);
    const [userStats] = await db.select({
      active: sql<number>`count(*) filter (where ${users.isActive} = true)::int`,
    }).from(users).where(eq(users.organizationId, organizationId));
    const activeUsers = userStats?.active ?? 0;
    const pricePerUser = parseFloat(billing.pricePerUser);
    const estimatedCharge = (activeUsers * pricePerUser).toFixed(2);
    return {
      balance: billing.balance,
      pricePerUser: billing.pricePerUser,
      currency: billing.currency,
      nextBillingDate: billing.nextBillingDate,
      billingBlocked: org?.billingBlocked ?? false,
      activeUsers,
      estimatedCharge,
    };
  }

  async processBillingCycle() {
    const { billingTransactions } = await import('@shared/schema');
    const today = new Date().toISOString().split('T')[0];
    const allOrgs = await db.select().from(organizations).where(eq(organizations.isActive, true));
    for (const org of allOrgs) {
      try {
        const billing = await this.getBillingSettings(org.id);
        if (!billing) continue;
        if (!billing.nextBillingDate || billing.nextBillingDate > today) continue;

        const [userStats] = await db.select({
          active: sql<number>`count(*) filter (where ${users.isActive} = true)::int`,
        }).from(users).where(eq(users.organizationId, org.id));
        const activeUsers = userStats?.active ?? 0;
        const pricePerUser = parseFloat(billing.pricePerUser);
        const charge = activeUsers * pricePerUser;

        const nextDate = new Date(billing.nextBillingDate);
        nextDate.setMonth(nextDate.getMonth() + 1);
        const nextDateStr = nextDate.toISOString().split('T')[0];

        if (charge === 0) {
          await this.updateBillingSettings(org.id, { nextBillingDate: nextDateStr });
        } else if (parseFloat(billing.balance) >= charge) {
          const newBalance = (parseFloat(billing.balance) - charge).toFixed(2);
          await this.updateBillingSettings(org.id, { balance: newBalance, nextBillingDate: nextDateStr });
          await db.insert(billingTransactions).values({
            organizationId: org.id,
            amount: charge.toFixed(2),
            type: 'debit',
            description: `Списание за ${activeUsers} активных пользователей × ${pricePerUser} ${billing.currency}`,
          });
        } else {
          await db.insert(billingTransactions).values({
            organizationId: org.id,
            amount: parseFloat(billing.balance).toFixed(2),
            type: 'debit',
            description: 'Частичное списание (недостаточно средств) — организация заблокирована',
          });
          await this.updateBillingSettings(org.id, { balance: "0" });
          await this.setBillingBlocked(org.id, true);
        }
      } catch (err) {
        console.error(`Billing cycle error for org ${org.id}:`, err);
      }
    }
  }

  async getOrganizationDetailedStats(organizationId: number) {
    const [formStats] = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(forms)
      .where(eq(forms.organizationId, organizationId));

    const [taskStats] = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(tasks)
      .innerJoin(forms, eq(tasks.formId, forms.id))
      .where(eq(forms.organizationId, organizationId));

    const [userStats] = await db
      .select({
        total: sql<number>`count(*)::int`,
        active: sql<number>`count(*) filter (where ${users.isActive} = true)::int`,
      })
      .from(users)
      .where(eq(users.organizationId, organizationId));

    const [[latestTaskRow], [latestLoginRow]] = await Promise.all([
      db.select({ at: sql<string | null>`max(${tasks.createdAt})` })
        .from(tasks)
        .innerJoin(forms, eq(tasks.formId, forms.id))
        .where(eq(forms.organizationId, organizationId)),
      db.select({ at: sql<string | null>`max(${users.lastLogin})` })
        .from(users)
        .where(eq(users.organizationId, organizationId)),
    ]);
    const lastTaskAt = latestTaskRow?.at ? new Date(latestTaskRow.at) : null;
    const lastLoginAt = latestLoginRow?.at ? new Date(latestLoginRow.at) : null;
    const lastActivity = lastTaskAt && lastLoginAt
      ? (lastTaskAt > lastLoginAt ? lastTaskAt : lastLoginAt)
      : (lastTaskAt ?? lastLoginAt);

    return {
      forms: formStats?.count ?? 0,
      tasks: taskStats?.count ?? 0,
      totalUsers: userStats?.total ?? 0,
      activeUsers: userStats?.active ?? 0,
      lastActivity,
    };
  }
}