<?php

namespace App\Http\Controllers\Admin\Customer;

use App\Http\Controllers\Controller;
use App\Http\Requests\Customer\CustomerStoreRequest;
use App\Http\Requests\Customer\CustomerUpdateRequest;
use App\Models\Bank;
use App\Models\Client;
use App\Models\ClientContactPerson;
use App\Models\CustomerOpening;
use App\Models\CustomerOpeningDetail;
use App\Models\CustomerPaymentCollection;
use App\Models\MobileBanking;
use App\Service\Client\ClientService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;

class CustomerController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function code($table)
    {
        if (! $table) {
            return '00001';
        }
        if ($table) {
            $next_number = sprintf('%05d', (int) $table->code + 1);

            return $next_number;
        }
    }

    public function index()
    {
        return Inertia::render('Customer/Customer');
    }

    public function veiw(Request $request)
    {
        // When called with pagination/search params, return data in { data, total } shape
        $isPaginationRequest = $request->filled('page') || $request->filled('perPage') || $request->filled('search');

        if ($isPaginationRequest) {
            $page = (int) $request->get('page', 1);
            $perPage = (int) $request->get('perPage', 10);
            $search = (string) $request->get('search', '');

            $customers = $this->customersWithOpening();

            if ($search !== '') {
                $needle = mb_strtolower($search);
                $customers = $customers->filter(function ($customer) use ($needle) {
                    return mb_stripos($customer->name, $needle) !== false
                        || mb_stripos((string) $customer->code, $needle) !== false
                        || mb_stripos((string) $customer->number, $needle) !== false;
                })->values();
            }

            $total = $customers->count();
            $offset = max(0, ($page - 1) * $perPage);
            $paginated = $customers->slice($offset, $perPage)->values();

            return response()->json([
                'data' => $paginated,
                'total' => $total,
            ]);
        }

        // Legacy response used by frontend for initial code + list
        $code = $this->code(Client::orderbydesc('code')->first());

        return response()->json(['customer' => $this->customersWithOpening(), 'code' => $code]);
    }

    private function customersWithOpening()
    {
        $customers = Client::leftJoin('customer_openings', 'customer_openings.client_id', '=', 'clients.id')
            ->leftJoin('customer_opening_details', 'customer_opening_details.customer_opening_id', '=', 'customer_openings.id')
            ->select(
                'clients.id',
                'clients.name',
                'clients.code',
                'clients.number',
                'clients.nid',
                'clients.email',
                'clients.proprietor',
                'clients.address',
                'clients.user_type',
                'clients.status',
                'clients.invoice_type',
                'clients.created_at',
                'clients.updated_at',
                DB::raw('MAX(customer_opening_details.opening_balance) as opening_balance'),
                DB::raw('MAX(customer_opening_details.opening_date) as opening_date')
            )
            ->groupBy(
                'clients.id',
                'clients.name',
                'clients.code',
                'clients.number',
                'clients.nid',
                'clients.email',
                'clients.proprietor',
                'clients.address',
                'clients.user_type',
                'clients.status',
                'clients.invoice_type',
                'clients.created_at',
                'clients.updated_at'
            )
            ->latest('clients.id')
            ->get();

        // Load contact persons for each customer
        foreach ($customers as $customer) {
            $customer->contact_persons = ClientContactPerson::where('client_id', $customer->id)
                ->with(['designation', 'department'])
                ->get()
                ->map(function ($contact) {
                    return [
                        'id' => $contact->id,
                        'name' => $contact->name,
                        'designation_id' => $contact->designation_id,
                        'department_id' => $contact->department_id,
                        'email' => $contact->email,
                        'number' => $contact->number,
                    ];
                });
        }

        return $customers;
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(CustomerStoreRequest $request)
    {
        DB::beginTransaction();
        try {
            $customer = Client::create($request->except(['opening_date', 'opening_balance', 'contact_persons', 'credit', 'credit_limit']));

            // Handle contact persons
            if ($request->filled('contact_persons') && is_array($request->contact_persons)) {
                foreach ($request->contact_persons as $contactPerson) {
                    if (! empty($contactPerson['name'])) {
                        ClientContactPerson::create([
                            'client_id' => $customer->id,
                            'name' => $contactPerson['name'],
                            'designation_id' => $contactPerson['designation_id'] ?? null,
                            'department_id' => $contactPerson['department_id'] ?? null,
                            'email' => $contactPerson['email'] ?? null,
                            'number' => $contactPerson['number'] ?? null,
                        ]);
                    }
                }
            }

            if ($request->filled('opening_balance') || $request->filled('opening_date')) {
                $opening = CustomerOpening::create([
                    'client_id' => $customer->id,
                ]);

                CustomerOpeningDetail::create([
                    'customer_opening_id' => $opening->id,
                    'opening_balance' => $request->input('opening_balance', 0),
                    'opening_date' => $request->input('opening_date'),
                ]);
            }

            DB::commit();

            $code = $this->code(Client::orderbydesc('code')->first());

            return response()->json([
                'success' => 'Data is insert successfully',
                'customer' => $this->customersWithOpening(),
                'code' => $code,
            ]);
        } catch (\Exception $e) {
            DB::rollBack();

            return response()->json([
                'error' => 'Failed to create customer: '.$e->getMessage(),
            ], 500);
        }
    }

    /**
     * Display the specified resource.
     */
    public function show(string $id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(string $id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(CustomerUpdateRequest $request, string $id)
    {
        DB::beginTransaction();
        try {
            $client = Client::findOrFail($id);
            $client->update($request->except(['opening_balance', 'opening_date', 'contact_persons', 'credit', 'credit_limit']));

            // Handle contact persons - delete existing and create new ones
            if ($request->has('contact_persons')) {
                // Delete existing contact persons
                ClientContactPerson::where('client_id', $client->id)->delete();

                // Create new contact persons
                if (is_array($request->contact_persons)) {
                    foreach ($request->contact_persons as $contactPerson) {
                        if (! empty($contactPerson['name'])) {
                            ClientContactPerson::create([
                                'client_id' => $client->id,
                                'name' => $contactPerson['name'],
                                'designation_id' => $contactPerson['designation_id'] ?? null,
                                'department_id' => $contactPerson['department_id'] ?? null,
                                'email' => $contactPerson['email'] ?? null,
                                'number' => $contactPerson['number'] ?? null,
                            ]);
                        }
                    }
                }
            }

            if ($request->filled('opening_balance') || $request->filled('opening_date')) {

                $opening = CustomerOpening::firstOrCreate(
                    ['client_id' => $client->id]
                );

                $detail = CustomerOpeningDetail::where('customer_opening_id', $opening->id)->first();

                $payload = [
                    'opening_balance' => $request->input('opening_balance', 0),
                    'opening_date' => $request->input('opening_date'),
                ];

                if ($detail) {
                    $detail->update($payload);
                } else {
                    CustomerOpeningDetail::create(array_merge(
                        ['customer_opening_id' => $opening->id],
                        $payload
                    ));
                }
            }

            DB::commit();

            $code = $this->code(Client::orderbydesc('code')->first());

            return response()->json([
                'success' => 'Data is Update successfully',
                'customer' => $this->customersWithOpening(),
                'code' => $code,
            ]);
        } catch (\Exception $e) {
            DB::rollBack();

            return response()->json([
                'error' => 'Failed to update customer: '.$e->getMessage(),
            ], 500);
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(string $id)
    {
        DB::transaction(function () use ($id) {
            // Remove contact persons for this client
            ClientContactPerson::where('client_id', $id)->delete();

            // Remove opening details and openings for this client
            $openingIds = CustomerOpening::where('client_id', $id)->pluck('id');
            if ($openingIds->isNotEmpty()) {
                CustomerOpeningDetail::whereIn('customer_opening_id', $openingIds)->delete();
                CustomerOpening::whereIn('id', $openingIds)->delete();
            }

            // Finally remove the client record
            Client::where('id', $id)->delete();
        });
        $code = $this->code(Client::orderbydesc('code')->first());

        return response()->json(['success' => 'Data is Delete successfully', 'customer' => $this->customersWithOpening(), 'code' => $code]);
    }

    public function allData()
    {
        return CustomerPaymentCollection::selectRaw('
    DATE(date) as date,
    payment,
    customer_id as client_id,
    payment_id,
    id,
    order_no,
    payment_type,
    sales_person_id,
    collection_reference,
    warehouse_id,
    notes,
    COALESCE(
        (SELECT SUM(grand_total)
         FROM sales_masters
         WHERE location_id = customer_payment_collections.warehouse_id 
         AND client_id = customer_payment_collections.customer_id),
        0
    ) - COALESCE(
        (SELECT SUM(total)
         FROM sales_return_masters
         WHERE location_id = customer_payment_collections.warehouse_id 
         AND customer_id = customer_payment_collections.customer_id),
        0
    ) as total_sales,
    COALESCE(
        (
            SELECT SUM(details.opening_balance)
            FROM customer_openings AS opening
            INNER JOIN customer_opening_details AS details
                ON opening.id = details.customer_opening_id
            WHERE opening.warehouse_id = details.warehouse_id
              AND customer_payment_collections.warehouse_id = opening.warehouse_id
              AND opening.client_id = customer_payment_collections.customer_id
        ),
        0
    ) AS opening,
    COALESCE(
        (
            SELECT SUM(payment)
            FROM customer_payment_collections AS previous_payments
            WHERE previous_payments.customer_id = customer_payment_collections.customer_id
              AND previous_payments.warehouse_id = customer_payment_collections.warehouse_id
              AND previous_payments.id < customer_payment_collections.id
        ),
        0
    ) as prev_payment,
    -- Due = total_sales - (prev_payment + current payment)  (no opening balance)
    (
        COALESCE(
            (SELECT SUM(grand_total)
             FROM sales_masters
             WHERE location_id = customer_payment_collections.warehouse_id 
             AND client_id = customer_payment_collections.customer_id),
            0
        ) - COALESCE(
            (SELECT SUM(total)
             FROM sales_return_masters
             WHERE location_id = customer_payment_collections.warehouse_id 
             AND customer_id = customer_payment_collections.customer_id),
            0
        )
        - COALESCE(
            (
                SELECT SUM(payment)
                FROM customer_payment_collections AS previous_payments
                WHERE previous_payments.customer_id = customer_payment_collections.customer_id
                  AND previous_payments.warehouse_id = customer_payment_collections.warehouse_id
                  AND previous_payments.id < customer_payment_collections.id
            ),
            0
        )
        - payment
    ) as due_amount
')
            ->with('customer', 'salesPerson', 'location')
            ->whereNotNull('customer_id')
            ->latest()
            ->get();
    }

    public function viewDeuCollection()
    {

        $payments = $this->allData();

        return response()->json($payments);
    }

    public function search()
    {
        $customers = Client::where('name', 'like', '%'.trim(request()->data).'%')
            ->orWhere('code', request()->data)  // This was also 'date', should be 'data'
            ->orWhere('number', 'like', '%'.request()->data.'%')
            ->limit(30)
            ->get();

        return response()->json(['customer' => $customers]);
    }

    public function dueCollection()
    {
        $bank = Bank::get();
        $mobileBank = MobileBanking::get();

        return Inertia::render('Pos/DeuCollection', compact('bank', 'mobileBank'));
    }

    public function customerDue()
    {

        $customers = [];
        if (request()->warehouse_id) {
            $search = request()->data;
            $warehouseId = request()->warehouse_id;

            $customers = DB::select('
                SELECT 
                    c.id,
                    c.name,
                    c.code,
                    c.number,

                    COALESCE(od.total_opening, 0) AS opening,
                    COALESCE(p.total_payment, 0) AS payment,
                    COALESCE(r.total_return, 0) AS return_amount,
                    COALESCE(d.total_dues, 0) AS dues,

                    (COALESCE(od.total_opening, 0) + COALESCE(d.total_dues, 0)) 
                    - (COALESCE(p.total_payment, 0) + COALESCE(r.total_return, 0)) AS total_due

                FROM clients c

                LEFT JOIN (
                    SELECT co.client_id, cod.warehouse_id, SUM(cod.opening_balance) AS total_opening
                    FROM customer_openings co
                    JOIN customer_opening_details cod ON cod.customer_opening_id = co.id
                    WHERE cod.warehouse_id = ?
                    GROUP BY co.client_id, cod.warehouse_id
                ) od ON od.client_id = c.id

                LEFT JOIN (
                    SELECT customer_id, SUM(payment) AS total_payment
                    FROM customer_payment_collections
                    WHERE warehouse_id = ?
                    GROUP BY customer_id
                ) p ON p.customer_id = c.id

                LEFT JOIN (
                    SELECT customer_id, SUM(total) AS total_return
                    FROM sales_return_masters
                    WHERE location_id = ?
                    GROUP BY customer_id
                ) r ON r.customer_id = c.id

                LEFT JOIN (
                    SELECT client_id, SUM(grand_total) AS total_dues
                    FROM sales_masters
                    WHERE location_id = ?
                    GROUP BY client_id
                ) d ON d.client_id = c.id

                WHERE COALESCE(c.name, \'\') LIKE ? OR c.code = ? OR c.number = ?
                LIMIT 30
    ', [
                $warehouseId, // for customer_opening_details
                $warehouseId, // for payment_collections
                $warehouseId, // for sales_return_masters
                $warehouseId, // for sales_masters
                "%$search%",  // name
                $search,      // code
                $search,       // number
            ]);
        }

        return response()->json(['customer' => $customers]);
    }

    public function clientStore()
    {
        request()->validate([
            'number' => 'required|string|max:255',
        ]);

        $data = request()->merge(['code' => $this->code(Client::orderByDesc('code')->first())]);

        $client = Client::create([
            'name' => $data->name,
            'code' => $data->code,
            'number' => $data->number,
            'email' => $data->email,
            'address' => $data->address,
            'user_type' => $data->user_type,
            'status' => $data->status,
            'invoice_type' => $data->invoice_type,
        ]);

        return response()->json($client);
    }

    public function customerDueCollection()
    {

        ClientService::dueCollectionSore(request()->all());
        $payments = $this->allData();

        return response()->json(['success' => 'Date insert Successfully', 'payment' => $payments]);
    }

    public function customerDueCollectionEdit($id)
    {
        ClientService::dueCollectionUpdate(request()->all(), $id);
        $payments = $this->allData();

        return response()->json(['success' => 'Date Update Successfully', 'payment' => $payments]);
    }

    public function customerDueCollectionDelete($id)
    {
        ClientService::dueCollectionDelete($id);
        $payments = $this->allData();

        return response()->json(['success' => 'Date Delete Successfully', 'payment' => $payments]);
    }

    public function contactPersonsView($clientId)
    {
        $contactPersons = ClientContactPerson::where('client_id', $clientId)
            ->with(['designation', 'department'])
            ->get()
            ->map(function ($contact) {
                return [
                    'id' => $contact->id,
                    'name' => $contact->name,
                    'email' => $contact->email,
                    'number' => $contact->number,
                    'designation' => $contact->designation ? $contact->designation->name : null,
                    'department' => $contact->department ? $contact->department->name : null,
                ];
            });

        return response()->json(['contact_persons' => $contactPersons]);
    }
}
