car_mms/app/lib/db.server.ts

import { PrismaClient } from '@prisma/client';

let prisma: PrismaClient;

declare global {
  var __db__: PrismaClient;
}

// This is needed because in development we don't want to restart
// the server with every change, but we want to make sure we don't
// create a new connection to the DB with every change either.
// In production, we'll have a single connection to the DB.
if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.__db__) {
    global.__db__ = new PrismaClient();
  }
  prisma = global.__db__;
  prisma.$connect();
}

export { prisma };

// Database utility functions
export async function createUser(data: {
  name: string;
  username: string;
  email: string;
  password: string;
  authLevel: number;
  status?: string;
}) {
  return prisma.user.create({
    data: {
      ...data,
      status: data.status || 'active',
    },
  });
}

export async function getUserByUsername(username: string) {
  return prisma.user.findUnique({
    where: { username },
  });
}

export async function getUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
  });
}

export async function getUserById(id: number) {
  return prisma.user.findUnique({
    where: { id },
    select: {
      id: true,
      name: true,
      username: true,
      email: true,
      status: true,
      authLevel: true,
      createdDate: true,
      editDate: true,
    },
  });
}

export async function updateUser(id: number, data: {
  name?: string;
  username?: string;
  email?: string;
  password?: string;
  authLevel?: number;
  status?: string;
}) {
  return prisma.user.update({
    where: { id },
    data,
  });
}

export async function deleteUser(id: number) {
  return prisma.user.delete({
    where: { id },
  });
}

export async function getUsers(authLevel?: number) {
  const where = authLevel ? { authLevel: { gte: authLevel } } : {};

  return prisma.user.findMany({
    where,
    select: {
      id: true,
      name: true,
      username: true,
      email: true,
      status: true,
      authLevel: true,
      createdDate: true,
      editDate: true,
    },
    orderBy: { createdDate: 'desc' },
  });
}

// Customer operations
export async function createCustomer(data: {
  name: string;
  phone?: string;
  email?: string;
  address?: string;
}) {
  return prisma.customer.create({ data });
}

export async function getCustomers() {
  return prisma.customer.findMany({
    include: {
      vehicles: true,
      _count: {
        select: {
          vehicles: true,
          maintenanceVisits: true,
        },
      },
    },
    orderBy: { createdDate: 'desc' },
  });
}

export async function getCustomerById(id: number) {
  return prisma.customer.findUnique({
    where: { id },
    include: {
      vehicles: true,
      maintenanceVisits: {
        include: {
          vehicle: true,
        },
        orderBy: { visitDate: 'desc' },
      },
    },
  });
}

export async function updateCustomer(id: number, data: {
  name?: string;
  phone?: string;
  email?: string;
  address?: string;
}) {
  return prisma.customer.update({
    where: { id },
    data,
  });
}

export async function deleteCustomer(id: number) {
  return prisma.customer.delete({
    where: { id },
  });
}

// Vehicle operations
export async function createVehicle(data: {
  plateNumber: string;
  bodyType: string;
  manufacturer: string;
  model: string;
  trim?: string;
  year: number;
  transmission: string;
  fuel: string;
  cylinders?: number;
  engineDisplacement?: number;
  useType: string;
  ownerId: number;
}) {
  return prisma.vehicle.create({ data });
}

export async function getVehicles() {
  return prisma.vehicle.findMany({
    include: {
      owner: true,
      _count: {
        select: {
          maintenanceVisits: true,
        },
      },
    },
    orderBy: { createdDate: 'desc' },
  });
}

export async function getVehicleById(id: number) {
  return prisma.vehicle.findUnique({
    where: { id },
    include: {
      owner: true,
      maintenanceVisits: {
        include: {
          income: true,
        },
        orderBy: { visitDate: 'desc' },
      },
    },
  });
}

export async function updateVehicle(id: number, data: {
  plateNumber?: string;
  bodyType?: string;
  manufacturer?: string;
  model?: string;
  trim?: string;
  year?: number;
  transmission?: string;
  fuel?: string;
  cylinders?: number;
  engineDisplacement?: number;
  useType?: string;
  ownerId?: number;
  lastVisitDate?: Date;
  suggestedNextVisitDate?: Date;
}) {
  return prisma.vehicle.update({
    where: { id },
    data,
  });
}

export async function deleteVehicle(id: number) {
  return prisma.vehicle.delete({
    where: { id },
  });
}

// Maintenance visit operations
export async function createMaintenanceVisit(data: {
  vehicleId: number;
  customerId: number;
  maintenanceType: string;
  description: string;
  cost: number;
  paymentStatus?: string;
  kilometers: number;
  visitDate?: Date;
  nextVisitDelay: number;
}) {
  return prisma.$transaction(async (tx) => {
    // Create the maintenance visit
    const visit = await tx.maintenanceVisit.create({
      data: {
        ...data,
        paymentStatus: data.paymentStatus || 'pending',
        visitDate: data.visitDate || new Date(),
      },
    });

    // Update vehicle's last visit date and calculate next visit date
    const nextVisitDate = new Date();
    nextVisitDate.setMonth(nextVisitDate.getMonth() + data.nextVisitDelay);

    await tx.vehicle.update({
      where: { id: data.vehicleId },
      data: {
        lastVisitDate: visit.visitDate,
        suggestedNextVisitDate: nextVisitDate,
      },
    });

    // Create corresponding income record
    await tx.income.create({
      data: {
        maintenanceVisitId: visit.id,
        amount: data.cost,
        incomeDate: visit.visitDate,
      },
    });

    return visit;
  });
}

export async function getMaintenanceVisits() {
  return prisma.maintenanceVisit.findMany({
    include: {
      vehicle: {
        include: {
          owner: true,
        },
      },
      customer: true,
      income: true,
    },
    orderBy: { visitDate: 'desc' },
  });
}

export async function getMaintenanceVisitById(id: number) {
  return prisma.maintenanceVisit.findUnique({
    where: { id },
    include: {
      vehicle: {
        include: {
          owner: true,
        },
      },
      customer: true,
      income: true,
    },
  });
}

export async function updateMaintenanceVisit(id: number, data: {
  vehicleId?: number;
  customerId?: number;
  maintenanceType?: string;
  description?: string;
  cost?: number;
  paymentStatus?: string;
  kilometers?: number;
  visitDate?: Date;
  nextVisitDelay?: number;
}) {
  return prisma.maintenanceVisit.update({
    where: { id },
    data,
  });
}

export async function deleteMaintenanceVisit(id: number) {
  return prisma.maintenanceVisit.delete({
    where: { id },
  });
}

// Financial operations
export async function createExpense(data: {
  description: string;
  category: string;
  amount: number;
  expenseDate?: Date;
}) {
  return prisma.expense.create({
    data: {
      ...data,
      expenseDate: data.expenseDate || new Date(),
    },
  });
}

export async function getExpenses() {
  return prisma.expense.findMany({
    orderBy: { expenseDate: 'desc' },
  });
}

export async function getIncome() {
  return prisma.income.findMany({
    include: {
      maintenanceVisit: {
        include: {
          vehicle: true,
          customer: true,
        },
      },
    },
    orderBy: { incomeDate: 'desc' },
  });
}

export async function getFinancialSummary(dateFrom?: Date, dateTo?: Date) {
  const where = dateFrom && dateTo ? {
    AND: [
      { createdDate: { gte: dateFrom } },
      { createdDate: { lte: dateTo } },
    ],
  } : {};

  const [totalIncome, totalExpenses] = await Promise.all([
    prisma.income.aggregate({
      where: dateFrom && dateTo ? {
        incomeDate: { gte: dateFrom, lte: dateTo },
      } : {},
      _sum: { amount: true },
    }),
    prisma.expense.aggregate({
      where: dateFrom && dateTo ? {
        expenseDate: { gte: dateFrom, lte: dateTo },
      } : {},
      _sum: { amount: true },
    }),
  ]);

  return {
    totalIncome: totalIncome._sum.amount || 0,
    totalExpenses: totalExpenses._sum.amount || 0,
    netProfit: (totalIncome._sum.amount || 0) - (totalExpenses._sum.amount || 0),
  };
}