<?php

namespace App\Repositories\Dashboard;

use App\Enums\PropertyStatus;
use App\Enums\SalesType;
use App\Enums\StagesOpportunity;
use App\Models\Activity;
use App\Models\Customer;
use App\Models\Conversation;
use App\Models\Opportunity;
use App\Models\Property;
use App\Models\Quote;
use App\Models\Sales;
use App\Models\User;
use Carbon\Carbon;

class DashboardRepository implements DashboardRepositoryInterface
{
    // count customer
    public function countCustomer($fromDate, $toDate)
    {
        return Customer::whereBetween('created_at', [$fromDate, $toDate])->count();
    }

    // count customer
    public function countConversation($fromDate, $toDate)
    {
        return Conversation::whereBetween('created_at', [$fromDate, $toDate])->count();
    }

    // count oportunidad
    public function countOpportunities($fromDate, $toDate)
    {
        return Opportunity::whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    // count oportunidad por projecto
    public function countOpportunitiesProject($fromDate, $toDate, $project_id)
    {
        return Opportunity::whereBetween('created_at', [$fromDate, $toDate])
        ->whereHas('property', function ($query) use ($project_id) {
            $query->where('project_id', $project_id);
        })
        ->count();
    }

    // LeadsRepository
    public function countOpportunityStage($fromDate, $toDate, $stage_id)
    {
        return Opportunity::where('stage_id', $stage_id)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countOpportunityStageProject($fromDate, $toDate, $stage_id, $project_id)
    {
        return Opportunity::where('stage_id', $stage_id)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->whereHas('property', function ($query) use ($project_id) {
                $query->where('project_id', $project_id);
            })
            ->count();
    }

    public function averageClosureTimeProject($fromDate, $toDate, $project_id)
    {
        $totalTime = Opportunity::where('stage_id', StagesOpportunity::COMPLETED->value())
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->whereHas('property', function ($query) use ($project_id) {
                $query->where('project_id', $project_id);
            })
            ->get()
            ->reduce(function ($carry, $opportunity) {
                $createdAt = Carbon::parse($opportunity->created_at);
                // $completedAt = Carbon::parse($opportunity->sales->first()->created_at);
                $completedAt = Carbon::parse($opportunity->updated_at);
                $differenceInDays = $createdAt->diffInDays($completedAt);
                return $carry + $differenceInDays;
            }, 0);

        $opportunityCount = Opportunity::where('stage_id', StagesOpportunity::COMPLETED->value())
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->whereHas('property', function ($query) use ($project_id) {
                $query->where('project_id', $project_id);
            })
            ->count();
        if ($opportunityCount === 0) {
            return 0;
        }
        $averageTime = $totalTime / $opportunityCount;
        return round($averageTime, 2);
    }

    public function averageClosureTime($fromDate, $toDate)
    {
        $totalTime = Opportunity::where('stage_id', StagesOpportunity::COMPLETED->value())
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->get()
            ->reduce(function ($carry, $opportunity) {
                $createdAt = Carbon::parse($opportunity->created_at);
                $completedAt = Carbon::parse($opportunity->updated_at);
                // $completedAt = Carbon::parse($opportunity->sales->first()->created_at);
                $differenceInDays = $createdAt->diffInDays($completedAt);
                return $carry + $differenceInDays;
            }, 0);
        // dd($totalTime);

        $opportunityCount = Opportunity::where('stage_id', StagesOpportunity::COMPLETED->value())
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();

        if ($opportunityCount === 0) {
            return 0;
        }
        $averageTime = $totalTime / $opportunityCount;
        return round($averageTime, 2);
    }

    // SalesRepository
    //TODO: EL FILTRADO DEBERA DE SER POR PROYECTO

    // Número de prospectos: Cantidad de personas interesadas en el proyecto.
    // Número de visitas: Cantidad de visitas al proyecto o a la sala de ventas.
    // Número de propuestas enviadas: Cantidad de propuestas comerciales enviadas a los clientes.  No sabemos si se le enviaron a los clientes. mejor seria aprobado por el cliente.
    // Número de negociaciones: Cantidad de negociaciones activas.
    // Número de ventas cerradas: Cantidad de ventas concretadas.

    public function countActivityVisit($fromDate, $toDate)
    {
        return Activity::where('type_activity_id', 1)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countQuote($fromDate, $toDate)
    {
        return Quote::whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countQuoteApproved($fromDate, $toDate, $approved)
    {
        return Quote::whereBetween('created_at', [$fromDate, $toDate])
            ->where($approved, true)
            ->count();
    }

    public function countSale($fromDate, $toDate)
    {
        return Sales::whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->count();
    }

    public function countSaleState($fromDate, $toDate, $state)
    {
        return Sales::whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->where('status', $state)
            ->count();
    }

    public function countActivityVisitProject($fromDate, $toDate,  $projectId)
    {
        return Activity::where(function ($query) use ($projectId) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($projectId) {
                    $query->whereHas('property', function ($query) use ($projectId) {
                        $query->where('project_id', $projectId);
                    });
                });
        })->where('type_activity_id', 1)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countQuoteProject($fromDate, $toDate, $projectId)
    {
        return Quote::where(function ($query) use ($projectId) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($projectId) {
                    $query->whereHas('property', function ($query) use ($projectId) {
                        $query->where('project_id', $projectId);
                    });
                });
        })
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countQuoteProjectApproved($fromDate, $toDate, $approved, $projectId)
    {
        return Quote::where(function ($query) use ($projectId) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($projectId) {
                    $query->whereHas('property', function ($query) use ($projectId) {
                        $query->where('project_id', $projectId);
                    });
                });
        })
             ->where($approved, true)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function countSaleProject($fromDate, $toDate, $projectId)
    {
        return Sales::where(function ($query) use ($projectId) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($projectId) {
                    $query->whereHas('property', function ($query) use ($projectId) {
                        $query->where('project_id', $projectId);
                    });
                });
        })
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->count();
    }

    public function countSaleStateProject($fromDate, $toDate, $state, $projectId)
    {
        return Sales::where(function ($query) use ($projectId) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($projectId) {
                    $query->whereHas('property', function ($query) use ($projectId) {
                        $query->where('project_id', $projectId);
                    });
                });
        })
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->where('status', $state)
            ->count();
    }


    // NegotiationsRepository
    // Reporte por vendedores:
    // Objetivo: Valorar el rendimiento de los vendedores
    // KPI: Número de leads por estado de ventas.
    // Visualización: Gráfico de barras apiladas: Permite comparar la distribución de leads en cada etapa del embudo del vendedor.

    // [
    //     { "asesor_id": 1, "total": 40 },
    //     { "asesor_id": 2, "total": 25 },
    //     { "asesor_id": 3, "total": 55 }
    // ]

    // public function sellerReport($fromDate, $toDate)
    // {
    //     // Número de ventas cerradas: Cantidad de ventas concretadas.
    //     $users = User::whereHas('roles', function ($query) {
    //         $query->where('name', 'ASESOR');
    //     })->get();

    //     $data = [];
    //     $users->map(function ($user) {
    //         $opportunity =  Opportunity::where('user_id', $user->id)->where()->count();
    //         return $user;
    //     });
    //     return $users;
    // }

    public function sellerReport($fromDate, $toDate)
    {
        $users = User::whereHas('roles', function ($query) {
            $query->where('name', 'ASESOR');
        })->get();

        $data = $users->map(function ($user) use ($fromDate, $toDate) {
            $opportunityCount = $user->opportunities()
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $leadCount = $user->opportunities()->where('stage_id', StagesOpportunity::LEAD->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $prospectCount = $user->opportunities()->where('stage_id', StagesOpportunity::PROSPECT->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $presaleCount = $user->opportunities()->where('stage_id', StagesOpportunity::PRESALE->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $saleCount = $user->opportunities()->where('stage_id', StagesOpportunity::SALE->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $deliveryCount = $user->opportunities()->where('stage_id', StagesOpportunity::DELIVERY->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $lossCount = $user->opportunities()->where('stage_id', StagesOpportunity::LOSS->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            $completeCount = $user->opportunities()->where('stage_id', StagesOpportunity::COMPLETED->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->count();
            return [
                'user' => $user->only(['id', 'name', 'email']),
                'opportunities_count' => $opportunityCount,
                'lead_count' => $leadCount,
                'prospect_count' => $prospectCount,
                'presale_count' => $presaleCount,
                'sale_count' => $saleCount,
                'delivery_count' => $deliveryCount,
                'loss_count' => $lossCount,
                'complete_count' => $completeCount,
            ];
        });

        return $data;
    }

    public function sellerReportProject($fromDate, $toDate, $project_id)
    {
        $users = User::whereHas('roles', function ($query) {
            $query->where('name', 'ASESOR');
        })->get();

        $data = $users->map(function ($user) use ($fromDate, $toDate, $project_id) {
            $opportunityCount = $user->opportunities()
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $leadCount = $user->opportunities()->where('stage_id', StagesOpportunity::LEAD->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $prospectCount = $user->opportunities()->where('stage_id', StagesOpportunity::PROSPECT->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $presaleCount = $user->opportunities()->where('stage_id', StagesOpportunity::PRESALE->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $saleCount = $user->opportunities()->where('stage_id', StagesOpportunity::SALE->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $deliveryCount = $user->opportunities()->where('stage_id', StagesOpportunity::DELIVERY->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $lossCount = $user->opportunities()->where('stage_id', StagesOpportunity::LOSS->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            $completeCount = $user->opportunities()->where('stage_id', StagesOpportunity::COMPLETED->value())
                ->whereBetween('created_at', [$fromDate, $toDate])
                ->whereHas('property', function ($query) use ($project_id) {
                    $query->where('project_id', $project_id);
                })
                ->count();
            return [
                'user' => $user->only(['id', 'name', 'email']),
                'opportunities_count' => $opportunityCount,
                'lead_count' => $leadCount,
                'prospect_count' => $prospectCount,
                'presale_count' => $presaleCount,
                'sale_count' => $saleCount,
                'delivery_count' => $deliveryCount,
                'loss_count' => $lossCount,
                'complete_count' => $completeCount,
            ];
        });
        return $data;
    }

    //properties

    public function totalpropertyProject($fromDate, $toDate, $project_id)
    {
        return Property::where('project_id', $project_id)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function propertyAveragePriceProject($fromDate, $toDate, $project_id)
    {
        $averagePrice = Property::where('project_id', $project_id)
            ->where('status', PropertyStatus::SALE)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->avg('base_price');
        return round($averagePrice, 2) ?? 0; // Si no hay datos, devolver 0
    }
    public function propertyStatusProject($fromDate, $toDate, $status, $project_id)
    {
        return Property::where('status', $status)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }

    public function totalproperty($fromDate, $toDate)
    {
        return Property::whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }
    public function propertyAveragePrice($fromDate, $toDate)
    {
        $averagePrice = Property::where('status', PropertyStatus::SALE)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->avg('base_price');
        return round($averagePrice, 2) ?? 0; // Si no hay datos, devolver 0
    }
    public function propertyStatus($fromDate, $toDate, $status)
    {
        return Property::where('status', $status)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->count();
    }



    public function totalpropertyTypeProject($project_id, $propertyType)
    {
        return Property::where('project_id', $project_id)
            ->where('property_type_id', $propertyType)
            ->count();
    }
    public function propertyTypeAveragePriceProject($fromDate, $toDate, $project_id, $propertyType)
    {
        $averagePrice = Property::where('project_id', $project_id)
            ->where('status', PropertyStatus::SALE)
            ->where('property_type_id', $propertyType)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->avg('base_price');
        return round($averagePrice, 2) ?? 0; // Si no hay datos, devolver 0
    }

    public function propertyTypeStatusProject($status, $project_id, $propertyType)
    {
        return Property::where('project_id', $project_id)
            ->where('status', $status)
            ->where('property_type_id', $propertyType)
            ->count();
    }

    public function totalpropertyType( $propertyType)
    {
        return Property::where('property_type_id', $propertyType)
            ->count();
    }
    public function propertyTypeAveragePrice($fromDate, $toDate, $propertyType)
    {
        $averagePrice = Property::where('status', PropertyStatus::SALE)
            ->where('property_type_id', $propertyType)
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->avg('base_price');
        return round($averagePrice, 2) ?? 0; // Si no hay datos, devolver 0
    }
    public function propertyTypeStatus( $status, $propertyType)
    {
        return Property::where('status', $status)
            ->where('property_type_id', $propertyType)
            ->count();
    }

    public function totalAmountSaleProject($fromDate, $toDate, $project_id)
    {
        //todo la suma debe de ser solo venta sin entrega
        $totalsale =  Sales::where(function ($query) use ($project_id) {
            $query->whereNull('opportunity_id')
                ->orWhereHas('opportunity', function ($query) use ($project_id) {
                    $query->whereHas('property', function ($query) use ($project_id) {
                        $query->where('project_id', $project_id);
                    });
                });
        })
            ->whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->sum('amount');
        return round($totalsale, 2);
    }

    public function totalAmountSale($fromDate, $toDate)
    {
        $totalsale =  Sales::whereBetween('created_at', [$fromDate, $toDate])
            ->where('status', "!=", SalesType::DISCARD->name)
            ->sum('amount');
        return round($totalsale, 2);
    }

    public function countActivity($fromDate, $toDate)
    {
        return Activity::whereBetween('scheduled_at', [$fromDate, $toDate])
            ->count();
    }

    public function countActivityType($fromDate, $toDate, $type_id)
    {
        return Activity::where('type_activity_id', $type_id)
            ->whereBetween('scheduled_at', [$fromDate, $toDate])
            ->count();
    }

    public function countActivityStage($fromDate, $toDate, $stage)
    {
        return Activity::where('state_activity', $stage)
            ->whereBetween('scheduled_at', [$fromDate, $toDate])
            ->count();
    }
}
