car_mms/app/lib/customer-management.server.ts

import { prisma } from "./db.server";
import type { Customer } from "@prisma/client";
import type { CreateCustomerData, UpdateCustomerData, CustomerWithVehicles } from "~/types/database";

// Get all customers with search and pagination
export async function getCustomers(
    searchQuery?: string,
    page: number = 1,
    limit: number = 10
): Promise<{
    customers: CustomerWithVehicles[];
    total: number;
    totalPages: number;
}> {
    const offset = (page - 1) * limit;

    // Build where clause for search
    const whereClause: any = {};

    if (searchQuery) {
        const searchLower = searchQuery.toLowerCase();
        whereClause.OR = [
            { name: { contains: searchLower } },
            { phone: { contains: searchLower } },
            { email: { contains: searchLower } },
            { address: { contains: searchLower } },
        ];
    }

    const [customers, total] = await Promise.all([
        prisma.customer.findMany({
            where: whereClause,
            include: {
                vehicles: {
                    select: {
                        id: true,
                        plateNumber: true,
                        manufacturer: true,
                        model: true,
                        year: true,
                        lastVisitDate: true,
                        suggestedNextVisitDate: true,
                    },
                },
                maintenanceVisits: {
                    select: {
                        id: true,
                        visitDate: true,
                        cost: true,
                        maintenanceJobs: true,
                    },
                    orderBy: { visitDate: 'desc' },
                    take: 5, // Only get last 5 visits for performance
                },
            },
            orderBy: { createdDate: 'desc' },
            skip: offset,
            take: limit,
        }),
        prisma.customer.count({ where: whereClause }),
    ]);

    return {
        customers,
        total,
        totalPages: Math.ceil(total / limit),
    };
}

// Get customer by ID with full relationships
export async function getCustomerById(id: number): Promise<CustomerWithVehicles | null> {
    return await prisma.customer.findUnique({
        where: { id },
        include: {
            vehicles: {
                orderBy: { createdDate: 'desc' },
            },
            maintenanceVisits: {
                include: {
                    vehicle: {
                        select: {
                            id: true,
                            plateNumber: true,
                            manufacturer: true,
                            model: true,
                            year: true,
                        },
                    },
                },
                orderBy: { visitDate: 'desc' },
                take: 3, // Only get latest 3 visits for the enhanced view
            },
        },
    });
}

// Create new customer
export async function createCustomer(
    customerData: CreateCustomerData
): Promise<{ success: boolean; customer?: Customer; error?: string }> {
    try {
        // Check if customer with same phone or email already exists (if provided)
        if (customerData.phone || customerData.email) {
            const existingCustomer = await prisma.customer.findFirst({
                where: {
                    OR: [
                        customerData.phone ? { phone: customerData.phone } : {},
                        customerData.email ? { email: customerData.email } : {},
                    ].filter(condition => Object.keys(condition).length > 0),
                },
            });

            if (existingCustomer) {
                if (existingCustomer.phone === customerData.phone) {
                    return { success: false, error: "رقم الهاتف موجود بالفعل" };
                }
                if (existingCustomer.email === customerData.email) {
                    return { success: false, error: "البريد الإلكتروني موجود بالفعل" };
                }
            }
        }

        // Create customer
        const customer = await prisma.customer.create({
            data: {
                name: customerData.name.trim(),
                phone: customerData.phone?.trim() || null,
                email: customerData.email?.trim() || null,
                address: customerData.address?.trim() || null,
            },
        });

        return { success: true, customer };
    } catch (error) {
        console.error("Error creating customer:", error);
        return { success: false, error: "حدث خطأ أثناء إنشاء العميل" };
    }
}

// Update customer
export async function updateCustomer(
    id: number,
    customerData: UpdateCustomerData
): Promise<{ success: boolean; customer?: Customer; error?: string }> {
    try {
        // Check if customer exists
        const existingCustomer = await prisma.customer.findUnique({
            where: { id },
        });

        if (!existingCustomer) {
            return { success: false, error: "العميل غير موجود" };
        }

        // Check for phone/email conflicts with other customers
        if (customerData.phone || customerData.email) {
            const conflictCustomer = await prisma.customer.findFirst({
                where: {
                    AND: [
                        { id: { not: id } },
                        {
                            OR: [
                                customerData.phone ? { phone: customerData.phone } : {},
                                customerData.email ? { email: customerData.email } : {},
                            ].filter(condition => Object.keys(condition).length > 0),
                        },
                    ],
                },
            });

            if (conflictCustomer) {
                if (conflictCustomer.phone === customerData.phone) {
                    return { success: false, error: "رقم الهاتف موجود بالفعل" };
                }
                if (conflictCustomer.email === customerData.email) {
                    return { success: false, error: "البريد الإلكتروني موجود بالفعل" };
                }
            }
        }

        // Prepare update data
        const updateData: any = {};
        if (customerData.name !== undefined) updateData.name = customerData.name.trim();
        if (customerData.phone !== undefined) updateData.phone = customerData.phone?.trim() || null;
        if (customerData.email !== undefined) updateData.email = customerData.email?.trim() || null;
        if (customerData.address !== undefined) updateData.address = customerData.address?.trim() || null;

        // Update customer
        const customer = await prisma.customer.update({
            where: { id },
            data: updateData,
        });

        return { success: true, customer };
    } catch (error) {
        console.error("Error updating customer:", error);
        return { success: false, error: "حدث خطأ أثناء تحديث العميل" };
    }
}

// Delete customer with relationship handling
export async function deleteCustomer(
    id: number
): Promise<{ success: boolean; error?: string }> {
    try {
        // Check if customer exists
        const existingCustomer = await prisma.customer.findUnique({
            where: { id },
            include: {
                vehicles: true,
                maintenanceVisits: true,
            },
        });

        if (!existingCustomer) {
            return { success: false, error: "العميل غير موجود" };
        }

        // Check if customer has vehicles or maintenance visits
        if (existingCustomer.vehicles.length > 0) {
            return {
                success: false,
                error: `لا يمكن حذف العميل لأنه يملك ${existingCustomer.vehicles.length} مركبة. يرجى حذف المركبات أولاً`
            };
        }

        if (existingCustomer.maintenanceVisits.length > 0) {
            return {
                success: false,
                error: `لا يمكن حذف العميل لأنه لديه ${existingCustomer.maintenanceVisits.length} زيارة صيانة. يرجى حذف الزيارات أولاً`
            };
        }

        // Delete customer
        await prisma.customer.delete({
            where: { id },
        });

        return { success: true };
    } catch (error) {
        console.error("Error deleting customer:", error);
        return { success: false, error: "حدث خطأ أثناء حذف العميل" };
    }
}

// Get customers for dropdown/select options
export async function getCustomersForSelect(): Promise<{ id: number; name: string; phone?: string | null }[]> {
    return await prisma.customer.findMany({
        select: {
            id: true,
            name: true,
            phone: true,
        },
        orderBy: { name: 'asc' },
    });
}

// Get customer statistics
export async function getCustomerStats(customerId: number): Promise<{
    totalVehicles: number;
    totalVisits: number;
    totalSpent: number;
    lastVisitDate?: Date;
} | null> {
    const customer = await prisma.customer.findUnique({
        where: { id: customerId },
        include: {
            vehicles: {
                select: { id: true },
            },
            maintenanceVisits: {
                select: {
                    cost: true,
                    visitDate: true,
                },
                orderBy: { visitDate: 'desc' },
            },
        },
    });

    if (!customer) return null;

    const totalSpent = customer.maintenanceVisits.reduce((sum, visit) => sum + visit.cost, 0);
    const lastVisitDate = customer.maintenanceVisits.length > 0
        ? customer.maintenanceVisits[0].visitDate
        : undefined;

    return {
        totalVehicles: customer.vehicles.length,
        totalVisits: customer.maintenanceVisits.length,
        totalSpent,
        lastVisitDate,
    };
}

// Search customers by name or phone (for autocomplete)
export async function searchCustomers(query: string, limit: number = 10): Promise<{
    id: number;
    name: string;
    phone?: string | null;
    email?: string | null;
}[]> {
    if (!query || query.trim().length < 2) {
        return [];
    }

    const searchLower = query.toLowerCase();

    return await prisma.customer.findMany({
        where: {
            OR: [
                { name: { contains: searchLower } },
                { phone: { contains: searchLower } },
                { email: { contains: searchLower } },
            ],
        },
        select: {
            id: true,
            name: true,
            phone: true,
            email: true,
        },
        orderBy: { name: 'asc' },
        take: limit,
    });
}