<?php

namespace App\Http\Controllers;

use App\Http\Requests\PersonRequest;
use App\Models\Budget;
use App\Models\BudgetDetail;
use App\Models\CreditLine;
use App\Models\peopleRole;
use App\Models\Person;
use App\Models\PersonPhone;
use App\Models\PersonTypeDetail;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Response;
use Inertia\Inertia;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Carbon\Carbon;

class PersonController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        $query = Person::select(['people.*',
            'departments.name as departaments_name',
            'provinces.name as provinces_name',
            'districts.name as districts_name',
            'doc_types.description as docdesc',
            'credit_line.total',
            'credit_line.used',
            'credit_line.residue',
            DB::raw('(SELECT vouchers.created_at FROM vouchers WHERE vouchers.person_id = people.id ORDER BY vouchers.created_at DESC LIMIT 1) as last_voucher_created')
        ])
            ->join('doc_types', 'people.doc_types', '=', 'doc_types.id')
            ->join('departments', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 2), "0000")'), '=', 'departments.id', 'left')
            ->join('provinces', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 4), "00")'), '=', 'provinces.id', 'left')
            ->join('districts', 'people.ubigeo', '=', 'districts.id', 'left')
            ->join('credit_line', 'people.id', '=', 'credit_line.person_id', 'left')
            ->with('persontype')
            ->with('personPhones')
            ->latest('people.id');

        if (request()->input('legal_name')) {
            $query->where('legal_name', 'LIKE', '%' . request()->input('legal_name') . '%');
        }

        if (request()->input('docdesc')) {
            $query->where('document_number', 'LIKE', '%' . request()->input('docdesc') . '%');
        }

        if (request()->input('perdesc')) {
            $query->whereHas('persontype', function ($q) {
                $q->where("description", "LIKE", "%" . request()->input('perdesc') . "%");
            });
        }

        $people = $query->paginate(10)->withQueryString();

        return Inertia::render('People/Index', [
            'people' => $people
        ]);
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(PersonRequest $request)
    {
        \DB::transaction(function () use ($request) {
            $data = new Person($request->input());
            $data->status = 1;
            $data->save();

            foreach ($request->input()['persontype'] as $value) {
                $person_type_detail = new PersonTypeDetail(['person_id' => $data->id, 'person_type_id' => $value]);
                $person_type_detail->save();

                if ($value == '1') {
                    $creditLine = new CreditLine([
                        'person_id' => $data->id,
                        'total' => $request->input()['total'],
                        'used' => 0,
                        'residue' => $request->input()['total'],
                    ]);
                    $creditLine->save();
                }

            }

            if ($request->has('phones') && is_array($request->input('phones'))) {
                foreach ($request->input('phones') as $phone) {
                    if ($phone != null) {
                        $personPhone = new personPhone([
                            'person_id' => $data->id,
                            'phone' => $phone
                        ]);
                        $personPhone->save();
                    }
                }
            }

            if ($request->has('people_role') && is_array($request->input('people_role'))) {
                foreach ($request->input('people_role') as $value) {
                    $peopleRol = new peopleRole([
                        'people_id' => $data->id,
                        'role_id' => $value
                    ]);
                    $peopleRol->save();
                }
            }

        });

        return redirect('people');
    }

    /**
     * Display the specified resource.
     */
    public function show(Person $person)
    {
        $person->load('district.province.department');
        $person->load('persontype');
        $person->load('people_role.role');
        $person->load('personPhones');

        return $person;
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(PersonRequest $request, Person $person)
    {
        \DB::transaction(function () use ($request, $person) {
            $person->update($request->input());
            $person->persontype()->detach();
            $person->personPhones()->update(['status' => 0]);
            $person->personPhones()->delete();

            $person->people_role()->update(['people_role.status' => 0]);
            $person->people_role()->delete();

            foreach ($request->input()['persontype'] as $value) {
                $person_type_detail = new PersonTypeDetail(['person_id' => $person->id, 'person_type_id' => $value]);
                $person_type_detail->save();


                $data = CreditLine::where('person_id', $person->id)->first();

                if ($value == '1') {
                    if ($data) {
                        $newResidue = $data->residue + ($request->input('total') - $data->total);
                        $data->update([
                            'total' => $request->input('total'),
                            'used' => $data->used,
                            'residue' => $newResidue,
                        ]);
                    } else {
                        $creditLine = new CreditLine([
                            'person_id' => $person->id,
                            'total' => $request->input('total'),
                            'used' => 0,
                            'residue' => $request->input('total'),
                        ]);
                        $creditLine->save();
                    }
                }

            }

            if ($request->has('phones') && is_array($request->input('phones'))) {
                foreach ($request->input('phones') as $phone) {
                    if ($phone != null) {
                        $personPhone = new personPhone([
                            'person_id' => $person->id,
                            'phone' => $phone
                        ]);
                        $personPhone->save();
                    }
                }
            }

            if ($request->has('people_role') && is_array($request->input('people_role'))) {
                foreach ($request->input()['people_role'] as $value) {
                    $peopleRol = new peopleRole([
                        'people_id' => $person->id,
                        'role_id' => $value
                    ]);
                    $peopleRol->save();
                }
            }

        });

        return redirect('people');
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(Person $person)
    {
        $person->status = 0;
        $person->save();
        $person->delete();
        return redirect('people');
    }


    public function downloadCustomers(Request $request)
    {
        $selectedPersonTypes = $request->input('selectedPersonTypes');
        $query = Person::select(['people.*', 'doc_types.description as docdesc', 'credit_line.total'])
            ->join('doc_types', 'people.doc_types', '=', 'doc_types.id')
            ->join('person_type_detail', 'people.id', '=', 'person_type_detail.person_id')
            ->join('person_types', 'person_type_detail.person_type_id', '=', 'person_types.id')
            ->join('credit_line', 'people.id', '=', 'credit_line.person_id', 'left');

        if (!empty($selectedPersonTypes)) {
            $query->whereIn('person_types.id', $selectedPersonTypes);
        }
        $data = $query->latest('people.id')->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $headerCellStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['argb' => 'FFBF5700']
            ],
            'font' => [
                'bold' => true,
                'color' => ['argb' => 'FFFFFFFF'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $sheet->setCellValue('B3', 'ITEM');
        $sheet->setCellValue('C3', 'RAZON SOCIAL');
        $sheet->setCellValue('C3', 'TIPO DOCUMENTO');
        $sheet->setCellValue('D3', 'DOCUMENTO');
        $sheet->setCellValue('E3', 'DIRECCION');
        $sheet->setCellValue('F3', 'LINEA DE CREDITO');
        $sheet->getStyle('B3:F3')->applyFromArray($headerCellStyle);
        $sheet->getColumnDimension('B')->setWidth(5);
        $sheet->getColumnDimension('C')->setWidth(60);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);

        $itemCellStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];

        $productCellStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT,
            ],
        ];

        $quantityCellStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_RIGHT,
            ],
        ];

        $row = 4;
        foreach ($data as $index => $detail) {
            $sheet->setCellValue('B' . $row, $index + 1);
            $sheet->setCellValue('C' . $row, $detail->legal_name);
            $sheet->setCellValue('D' . $row, $detail->docdesc);
            $sheet->setCellValue('E' . $row, $detail->document_number);
            $sheet->setCellValue('F' . $row, $detail->total);
            $row++;
        }

        $sheet->getStyle('B4:B' . ($row - 1))->applyFromArray($itemCellStyle);
        $sheet->getStyle('C4:C' . ($row - 1))->applyFromArray($productCellStyle);
        $sheet->getStyle('D4:D' . ($row - 1))->applyFromArray($itemCellStyle);
        $sheet->getStyle('E4:D' . ($row - 1))->applyFromArray($quantityCellStyle);
        $sheet->getStyle('F4:D' . ($row - 1))->applyFromArray($quantityCellStyle);
        $writer = new Xlsx($spreadsheet);
        $filename = 'Clientes.xlsx';
        return Response::stream(
            function () use ($writer) {
                $writer->save('php://output');
            },
            200,
            [
                'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                'Content-Disposition' => "attachment; filename=\"{$filename}\"",
            ]
        );

    }

    public function getClientsLastMonth()
    {
        $currentMonthStart = Carbon::now()->startOfMonth()->format('Y-m-d H:i:s');
        $currentMonthEnd = Carbon::now()->endOfMonth()->format('Y-m-d H:i:s');
        $previousMonthStart = Carbon::now()->subMonth()->startOfMonth()->format('Y-m-d H:i:s');
        $previousMonthEnd = Carbon::now()->subMonth()->endOfMonth()->format('Y-m-d H:i:s');
        $currentMonthClients = Person::select([
            'people.*',
            'departments.name as departaments_name',
            'provinces.name as provinces_name',
            'districts.name as districts_name',
            'doc_types.description as docdesc',
            'credit_line.total',
            'credit_line.used',
            'credit_line.residue',
            DB::raw('(SELECT vouchers.created_at FROM vouchers WHERE vouchers.person_id = people.id ORDER BY vouchers.created_at DESC LIMIT 1) as last_voucher_created')
        ])
            ->join('doc_types', 'people.doc_types', '=', 'doc_types.id')
            ->join('departments', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 2), "0000")'), '=', 'departments.id', 'left')
            ->join('provinces', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 4), "00")'), '=', 'provinces.id', 'left')
            ->join('districts', 'people.ubigeo', '=', 'districts.id', 'left')
            ->join('credit_line', 'people.id', '=', 'credit_line.person_id', 'left')
            ->with('persontype')
            ->with('personPhones')
            ->where('people.created_at', '>=', $currentMonthStart)
            ->where('people.created_at', '<=', $currentMonthEnd)
            ->latest('people.id')
            ->get();

        $previousMonthClients = Person::select([
            'people.*',
            'departments.name as departaments_name',
            'provinces.name as provinces_name',
            'districts.name as districts_name',
            'doc_types.description as docdesc',
            'credit_line.total',
            'credit_line.used',
            'credit_line.residue',
            DB::raw('(SELECT vouchers.created_at FROM vouchers WHERE vouchers.person_id = people.id ORDER BY vouchers.created_at DESC LIMIT 1) as last_voucher_created')
        ])
            ->join('doc_types', 'people.doc_types', '=', 'doc_types.id')
            ->join('departments', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 2), "0000")'), '=', 'departments.id', 'left')
            ->join('provinces', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 4), "00")'), '=', 'provinces.id', 'left')
            ->join('districts', 'people.ubigeo', '=', 'districts.id', 'left')
            ->join('credit_line', 'people.id', '=', 'credit_line.person_id', 'left')
            ->with('persontype')
            ->with('personPhones')
            ->where('people.created_at', '>=', $previousMonthStart)
            ->where('people.created_at', '<=', $previousMonthEnd)
            ->latest('people.id')
            ->get();
        return response()->json([
            'currentMonthClients' => $currentMonthClients,
            'previousMonthClients' => $previousMonthClients
        ], 200);
    }


    public function getTodaysBirthdays()
    {
        $query = Person::select(['people.*',
            'departments.name as departaments_name',
            'provinces.name as provinces_name',
            'districts.name as districts_name',
            'doc_types.description as docdesc',
            'credit_line.total',
            'credit_line.used',
            'credit_line.residue',
            DB::raw('(SELECT vouchers.created_at FROM vouchers WHERE vouchers.person_id = people.id ORDER BY vouchers.created_at DESC LIMIT 1) as last_voucher_created')
        ])
            ->join('doc_types', 'people.doc_types', '=', 'doc_types.id')
            ->join('departments', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 2), "0000")'), '=', 'departments.id', 'left')
            ->join('provinces', DB::raw('CONCAT(SUBSTRING(people.ubigeo, 1, 4), "00")'), '=', 'provinces.id', 'left')
            ->join('districts', 'people.ubigeo', '=', 'districts.id', 'left')
            ->join('credit_line', 'people.id', '=', 'credit_line.person_id', 'left')
            ->with('persontype')
            ->with('personPhones')
            ->whereRaw('MONTH(people.date_of_birth) = MONTH(CURRENT_DATE) AND DAY(people.date_of_birth) = DAY(CURRENT_DATE)')
            ->latest('people.id')->get();

        return response()->json([
            'todaysBirthdays' => $query,
        ], 200);

    }
}
