<?php

namespace App\Http\Controllers;

use App\Models\ScheduledVisit;
use App\Models\Client;
use App\Models\User;
use App\Mail\VisitScheduledMail;
use Illuminate\Http\Request;
use Illuminate\Validation\Rule;
use App\Models\scheduled_visitsReport;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Mail;

class VisitController extends Controller
{
    // List all visits (ordered by oldest date first)
    public function index()
    {
        return response()->json(ScheduledVisit::with(['client', 'location'])
            ->orderBy('scheduled_date', 'asc')
            ->orderBy('scheduled_time', 'asc')
            ->get());
    }

   public function visits_forvisite()
{
    // Get the logged-in user's full name (from `name` column)
    $userName = Auth::user()->name;

    // Retrieve all visits assigned to that staff (ordered by oldest date first)
    $visits = ScheduledVisit::with(['client', 'location'])
        ->where('responsible_staff', $userName)
        ->orderBy('scheduled_date', 'asc')
        ->orderBy('scheduled_time', 'asc')
        ->get();

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

public function getFullVisitReports()
{
    // Load main visit reports with relationships
    $reports = scheduled_visitsReport::with([
        'client.account',
        'client.creditRequests',
        'client.person.location',      
        'client.organization.location', 
        'client.organization.representatives',
        'scheduled',
        'creator',
    ])->get();

    // Map data
    $data = $reports->map(function ($report) {
        $client = $report->client;
        $isPerson = $client->type === 'person';
        $profile = $isPerson ? $client->person : $client->organization;

        // Fetch additional fields from scheduledSecond_visitsReport
        $secondaryFields = \DB::table('scheduledSecond_visitsReport')
            ->where('firstvisite', $report->id)
            ->where('client_id', $client->id)
            ->get()
            ->map(function ($field) {
                return [
                    'id'          => $field->id,
                    'areaSize'    => $field->areaSize,
                    'perimeter'   => $field->perimeter,
                    'location'    => $field->PropartieLocation,
                    'coordinates' => json_decode($field->coordinates, true),
                ];
            });

        // Include the main field from scheduled_visitsReport
        $mainField = [
            'id'          => $report->id,
            'areaSize'    => $report->areaSize,
            'perimeter'   => $report->perimeter,
            'location'    => $report->PropartieLocation,
            'coordinates' => json_decode($report->coordinates, true),
        ];

        // Combine all fields
        $allFields = collect([$mainField])->merge($secondaryFields);

        return [
            'visit_id'       => $report->id,
            'scheduled_id'   => $report->scheduled_id,
            'fields'         => $allFields, // multiple fields per visit
            'client_type'    => $client->type,
            'account'        => $client->account,
            'credits'        => $client->creditRequests,
            'profile'        => $profile,
            'location_info'  => optional($profile->location)->only(['colline', 'commune', 'province', 'region']),
            'created_by'     => optional($report->creator)->name,
        ];
    });

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

public function report(Request $request)
{
    $seedId = $request->query('seed_id');
    $clientId = $request->query('client_id');

    $report = DB::table('orders as o')
        ->join('scheduled_visitsReport as svr', 'svr.client_id', '=', DB::raw($clientId))
        ->where('o.seed_id', $seedId)
        ->select([
            'o.id as order_id',
            'o.seed_id',
            'o.instructions',
            'o.total',
            'o.createdby as order_createdby',
            'o.created_at as order_created_at',
            'o.updated_at as order_updated_at',
            'o.totalMaintenance',
            'svr.id as visit_report_id',
            'svr.scheduled_id',
            'svr.client_id',
            'svr.areaSize',
            'svr.perimeter',
            'svr.PropartieLocation',
            'svr.coordinates',
            'svr.createdby as visit_createdby',
            'svr.created_at as visit_created_at',
            'svr.updated_at as visit_updated_at',
        ])
        ->get();

    return response()->json($report);
}
public function combinedReport(Request $request)
{
    $seedId = $request->query('seed_id');
    $clientId = $request->query('client_id');

    // 1. Get visit reports with relationships
    $visitReports = scheduled_visitsReport::with([
        'client.account',
        'client.creditRequests',
        'client.person.location',      
        'client.organization.location', 
        'client.organization.representatives',
        'scheduled',
        'creator',
    ])
    ->where('client_id', $clientId)
    ->get()
    ->map(function ($report) {
        $client = $report->client;
        $isPerson = $client->type === 'person';
        $profile = $isPerson ? $client->person : $client->organization;

        return [
            'visit_id'       => $report->id,
            'scheduled_id'   => $report->scheduled_id,
            'location'       => $report->PropartieLocation,
            'coordinates'    => json_decode($report->coordinates),
            'areaSize'       => $report->areaSize,
            'perimeter'      => $report->perimeter,
            'client_type'    => $client->type,
            'account'        => $client->account,
            'credits'        => $client->creditRequests,
            'profile'        => $profile,
            'location_info'  => optional($profile->location)->only(['colline', 'commune', 'province', 'region']),
        ];
    });

    // 2. Get the order for the given seed_id
    $orders = DB::table('orders as o')
        ->join('inputsActivitiesCostsSeeds as s', 'o.seed_id', '=', 's.id')
        ->leftJoin('users as u', 'o.createdby', '=', 'u.id')
        ->select(
            'o.id as order_id',
            'o.seed_id',
            's.seeds_name',
            'o.instructions',
            'o.total as order_total',
            'o.createdby',
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as creator_name"),
            'o.created_at',
            'o.updated_at',
            's.seeds_source',
            's.description as seed_description'
        )
        ->where('o.seed_id', $seedId)
        ->get();

    // 3. Get order items grouped by order_id
    $orderItems = DB::table('order_items as oi')
        ->join('inputsActivitiesCostsneeded as n', 'oi.needed_id', '=', 'n.id')
        ->select(
            'oi.order_id',
            'oi.category_id',
            'oi.item_name',
            'oi.unite',
            'oi.quantity',
            'oi.unit_price',
            'oi.total_price',
            'n.neededname',
            'n.unit as needed_unit',
            'n.quantity as needed_quantity',
            'n.unitPrice as needed_unit_price',
            'n.totalPrice as needed_total_price',
            'n.category as needed_category'
        )
        ->get()
        ->groupBy('order_id');

    // 4. Get maintenance works for the seed_id
    $maintenanceWorks = DB::table('order_Maintenancework')
        ->where('seeds_id', $seedId)
        ->get();

    // 5. Merge items and maintenance into orders
    $orders = $orders->map(function ($order) use ($orderItems, $maintenanceWorks) {
        $items = $orderItems[$order->order_id] ?? collect();

        $order->fertilizer_items = $items->where('category_id', 1)->values();
        $order->phytosanitary_items = $items->where('category_id', 0)->values();

        // Assign all maintenance work for this seed_id
        $order->maintenance_works = $maintenanceWorks;

        return $order;
    });

    // 6. Return the response
    return response()->json([
        'order' => $orders->first(), // One order per seed_id
        'visit_reports' => $visitReports
    ]);
}
public function combinedReportcontribution(Request $request)
{
    $seedId = $request->query('seed_id');
    $clientId = $request->query('client_id');

    // 1. Get visit reports with relationships
    $visitReports = scheduled_visitsReport::with([
        'client.account',
        'client.creditRequests',
        'client.person.location',
        'client.organization.location',
        'client.organization.representatives',
        'scheduled',
        'creator',
    ])
        ->where('client_id', $clientId)
        ->get()
        ->map(function ($report) {
            $client = $report->client;
            $isPerson = $client->type === 'person';
            $profile = $isPerson ? $client->person : $client->organization;

            return [
                'visit_id'       => $report->id,
                'scheduled_id'   => $report->scheduled_id,
                'location'       => $report->PropartieLocation,
                'coordinates'    => json_decode($report->coordinates),
                'areaSize'       => $report->areaSize,
                'perimeter'      => $report->perimeter,
                'client_type'    => $client->type,
                'account'        => $client->account,
                'credits'        => $client->creditRequests,
                'profile'        => $profile,
                'location_info'  => optional($profile->location)->only(['colline', 'commune', 'province', 'region']),
            ];
        });

    // 2. Get the order for the given seed_id
    $orders = DB::table('orders as o')
        ->join('inputsActivitiesCostsSeeds as s', 'o.seed_id', '=', 's.id')
        ->leftJoin('users as u', 'o.createdby', '=', 'u.id')
        ->select(
            'o.id as order_id',
            'o.seed_id',
            's.seeds_name',
            'o.instructions',
            'o.total as order_total',
            'o.createdby',
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as creator_name"),
            'o.created_at',
            'o.updated_at',
            's.seeds_source',
            's.description as seed_description'
        )
        ->where('o.seed_id', $seedId)
        ->get();

    // 3. Get order items grouped by order_id
    $orderItems = DB::table('order_items as oi')
        ->join('inputsActivitiesCostsneeded as n', 'oi.needed_id', '=', 'n.id')
        ->select(
            'oi.order_id',
            'oi.category_id',
            'oi.item_name',
            'oi.unite',
            'oi.quantity',
            'oi.unit_price',
            'oi.total_price',
            'n.neededname',
            'n.unit as needed_unit',
            'n.quantity as needed_quantity',
            'n.unitPrice as needed_unit_price',
            'n.totalPrice as needed_total_price',
            'n.category as needed_category'
        )
        ->get()
        ->groupBy('order_id');

    // 4. Get maintenance works for the seed_id
    $maintenanceWorks = DB::table('order_Maintenancework')
        ->where('seeds_id', $seedId)
        ->get();

    // 5. Get all contribution summaries
    $contributionSummaries = DB::table('client_contribution_summaries as ccs')
        ->join('orders as o', 'ccs.order_id', '=', 'o.id')
        ->join('inputsActivitiesCostsSeeds as s', 'ccs.seed_id', '=', 's.id')
        ->leftJoin('users as u', 'ccs.created_by', '=', 'u.id')
        ->select(
            'ccs.*',
            'o.instructions as order_instructions',
            'o.total as order_total',
            's.seeds_name',
            's.seeds_source',
            's.description as seed_description',
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as creator_name")
        )
        ->where('ccs.client_id', $clientId)
        ->where('ccs.seed_id', $seedId)
        ->get();

    // 6. Get additional costs (joined by client_id & seed_id)
    $additionalCosts = DB::table('order_additional_costs')
        ->where('client_id', $clientId)
        ->where('seed_id', $seedId)
        ->get()
        ->groupBy('order_id');

    // 7. Merge all into orders
    $orders = $orders->map(function ($order) use ($orderItems, $maintenanceWorks, $contributionSummaries, $additionalCosts) {
        $items = $orderItems[$order->order_id] ?? collect();

        $order->fertilizer_items = $items->where('category_id', 1)->values();
        $order->phytosanitary_items = $items->where('category_id', 0)->values();
        $order->maintenance_works = $maintenanceWorks;

        // Attach all summaries related to this order
        $order->contribution_details = $contributionSummaries->where('order_id', $order->order_id)->values();

        // Attach additional costs
        $order->additional_costs = $additionalCosts[$order->order_id] ?? collect();

        return $order;
    });

    // 8. Return the response
    return response()->json([
        'order' => $orders->first(),
        'visit_reports' => $visitReports
    ]);
}

/*
public function combinedReportcontribution(Request $request)
{
    $seedId = $request->query('seed_id');
    $clientId = $request->query('client_id');

    // 1. Get visit reports with relationships
    $visitReports = scheduled_visitsReport::with([
        'client.account',
        'client.creditRequests',
        'client.person.location',      
        'client.organization.location', 
        'client.organization.representatives',
        'scheduled',
        'creator',
    ])
    ->where('client_id', $clientId)
    ->get()
    ->map(function ($report) {
        $client = $report->client;
        $isPerson = $client->type === 'person';
        $profile = $isPerson ? $client->person : $client->organization;

        return [
            'visit_id'       => $report->id,
            'scheduled_id'   => $report->scheduled_id,
            'location'       => $report->PropartieLocation,
            'coordinates'    => json_decode($report->coordinates),
            'areaSize'       => $report->areaSize,
            'perimeter'      => $report->perimeter,
            'client_type'    => $client->type,
            'account'        => $client->account,
            'credits'        => $client->creditRequests,
            'profile'        => $profile,
            'location_info'  => optional($profile->location)->only(['colline', 'commune', 'province', 'region']),
        ];
    });

    // 2. Get the order for the given seed_id
    $orders = DB::table('orders as o')
        ->join('inputsActivitiesCostsSeeds as s', 'o.seed_id', '=', 's.id')
        ->leftJoin('users as u', 'o.createdby', '=', 'u.id')
        ->select(
            'o.id as order_id',
            'o.seed_id',
            's.seeds_name',
            'o.instructions',
            'o.total as order_total',
            'o.createdby',
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as creator_name"),
            'o.created_at',
            'o.updated_at',
            's.seeds_source',
            's.description as seed_description'
        )
        ->where('o.seed_id', $seedId)
        ->get();

    // 3. Get order items grouped by order_id
    $orderItems = DB::table('order_items as oi')
        ->join('inputsActivitiesCostsneeded as n', 'oi.needed_id', '=', 'n.id')
        ->select(
            'oi.order_id',
            'oi.category_id',
            'oi.item_name',
            'oi.unite',
            'oi.quantity',
            'oi.unit_price',
            'oi.total_price',
            'n.neededname',
            'n.unit as needed_unit',
            'n.quantity as needed_quantity',
            'n.unitPrice as needed_unit_price',
            'n.totalPrice as needed_total_price',
            'n.category as needed_category'
        )
        ->get()
        ->groupBy('order_id');

    // 4. Get maintenance works for the seed_id
    $maintenanceWorks = DB::table('order_Maintenancework')
        ->where('seeds_id', $seedId)
        ->get();

    // 5. Get all contribution summaries with related order and seed info
    $contributionSummaries = DB::table('client_contribution_summaries as ccs')
        ->join('orders as o', 'ccs.order_id', '=', 'o.id')
        ->join('inputsActivitiesCostsSeeds as s', 'ccs.seed_id', '=', 's.id')
        ->leftJoin('users as u', 'ccs.created_by', '=', 'u.id')
        ->select(
            'ccs.*',
            'o.instructions as order_instructions',
            'o.total as order_total',
            's.seeds_name',
            's.seeds_source',
            's.description as seed_description',
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as creator_name")
        )
        ->where('ccs.client_id', $clientId)
        ->where('ccs.seed_id', $seedId)
        ->get();

    // 6. Merge items, maintenance, and contributions into orders
    $orders = $orders->map(function ($order) use ($orderItems, $maintenanceWorks, $contributionSummaries) {
        $items = $orderItems[$order->order_id] ?? collect();

        $order->fertilizer_items = $items->where('category_id', 1)->values();
        $order->phytosanitary_items = $items->where('category_id', 0)->values();
        $order->maintenance_works = $maintenanceWorks;

        // Attach all summaries related to this order
        $order->contribution_details = $contributionSummaries->where('order_id', $order->order_id)->values();

        return $order;
    });

    // 7. Return the response
    return response()->json([
        'order' => $orders->first(),
        'visit_reports' => $visitReports
    ]);
}
*/

 

    // Store a new visit
   // Store a new visit
public function store(Request $request)
{
    $validated = $request->validate([
        'client_id' => 'required|exists:clients,id',
        'visit_purpose' => 'required|in:credit_assessment,monitoring,recovery,training,other',
        'scheduled_date' => 'required|date',
        'scheduled_time' => 'required|date_format:H:i',
        'province' => 'required|string|max:100',
        'commune' => 'required|string|max:100',
        'colline' => 'required|string|max:100',
        'responsible_staff' => 'required|string|max:100',
        'notes' => 'nullable|string',
        'next_followup_date' => 'nullable|date',
    ]);

    $validated['status'] = 'Scheduled';
    $visit = ScheduledVisit::with(['client'])->create($validated);

    // Send email notification to assigned employee
    $employee = User::where('name', $visit->responsible_staff)
        ->orWhereRaw("CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,'')) = ?", [trim($visit->responsible_staff)])
        ->first();
    if ($employee && $employee->email) {
        try {
            Mail::to($employee->email)->send(new VisitScheduledMail($visit, 'scheduled'));
        } catch (\Exception $e) {
            \Log::warning('Failed to send visit notification email: ' . $e->getMessage());
        }
    }

    return response()->json([
        'message' => 'Visit created successfully',
        'data' => $visit
    ], 201);
}


    // Show a single visit
    public function show($id)
    {
        $visit = ScheduledVisit::with(['client', 'location'])->findOrFail($id);
        return response()->json($visit);
    }

    // Update an existing visit
public function update(Request $request, $id)
{
    $visit = ScheduledVisit::findOrFail($id);

    $validated = $request->validate([
        'visit_purpose' => ['sometimes', Rule::in(['credit_assessment', 'monitoring', 'recovery', 'training', 'other'])],
        'scheduled_date' => 'sometimes|date',
        'scheduled_time' => 'sometimes|nullable',
        'province' => 'sometimes|required|string|max:100',
        'commune' => 'sometimes|required|string|max:100',
        'colline' => 'sometimes|required|string|max:100',
        'responsible_staff' => 'sometimes|string|max:100',
        'notes' => 'nullable|string',
        'next_followup_date' => 'nullable|date',
        'actual_visit_date' => 'nullable|date',
        'actual_visit_time' => 'nullable|date_format:H:i',
        'outcome' => ['nullable', Rule::in(['completed', 'client_absent', 'rescheduled', 'cancelled'])],
        'status' => ['sometimes', Rule::in(['Scheduled', 'Completed', 'Rescheduled', 'Cancelled'])],
    ]);

    // Format scheduled_time if provided
    if (!empty($validated['scheduled_time'])) {
        $validated['scheduled_time'] = date('H:i', strtotime($validated['scheduled_time']));
    }

    $visit->update($validated);

    return response()->json($visit);
}
    // Reschedule a visit
    public function reschedule(Request $request, $id)
    {
        $visit = ScheduledVisit::findOrFail($id);

        $validated = $request->validate([
            'scheduled_date' => 'required|date',
            'scheduled_time' => 'required|date_format:H:i',
            'reschedule_reason' => 'nullable|string|max:500',
            'status' => 'sometimes|in:Scheduled,Rescheduled',
        ]);

        $visit->update([
            'scheduled_date' => $validated['scheduled_date'],
            'scheduled_time' => date('H:i', strtotime($validated['scheduled_time'])),
            'notes' => ($visit->notes ? $visit->notes . "\n" : '') . 'Rescheduled: ' . ($validated['reschedule_reason'] ?? ''),
            'status' => $validated['status'] ?? 'Rescheduled',
        ]);

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

    // Mark visit as complete
    public function complete(Request $request, $id)
    {
        $visit = ScheduledVisit::findOrFail($id);

        $validated = $request->validate([
            'actual_visit_date' => 'required|date',
            'actual_visit_time' => 'required|date_format:H:i',
            'outcome' => 'required|in:completed,client_absent,rescheduled,cancelled',
            'notes' => 'nullable|string',
            'next_followup_date' => 'nullable|date',
            'status' => 'required|in:Scheduled,Completed,Rescheduled,Cancelled',
        ]);

        $visit->update($validated);

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

    // Delete a visit
    public function destroy($id)
    {
        $visit = ScheduledVisit::findOrFail($id);
        $visit->delete();

        return response()->json(['message' => 'Visit deleted successfully']);
    }


    public function searchCreditClients(Request $request)
{
    $query = $request->input('query', '');

    if (empty(trim($query))) {
        return response()->json([]);
    }

    $clients = Client::query()
        ->with([
            'person',
            'organization',
            'account',
            'creditRequests' => function ($q) {
                $q->latest('created_at')->limit(1); // only the last credit request
            }
        ])
        // must have at least one credit request
        ->whereHas('creditRequests')

        // exclude clients who have a scheduled visit still "Scheduled"
        ->whereDoesntHave('scheduledVisits', function ($q) {
            $q->where('status', 'Scheduled');
        })

        // apply search
        ->where(function ($q) use ($query) {
            $q->whereHas('person', function ($sub) use ($query) {
                $sub->where('first_name', 'like', "%{$query}%")
                    ->orWhere('last_name', 'like', "%{$query}%")
                    ->orWhere('national_id', 'like', "%{$query}%")
                    ->orWhere('phone', 'like', "%{$query}%");
            })
            ->orWhereHas('organization', function ($sub) use ($query) {
                $sub->where('company_name', 'like', "%{$query}%")
                    ->orWhere('nif', 'like', "%{$query}%")
                    ->orWhere('rc', 'like', "%{$query}%");
            })
            ->orWhereHas('account', function ($sub) use ($query) {
                $sub->where('account_number', 'like', "%{$query}%");
            });
        })
        ->get();

    // format output
    $clients->transform(function ($client) {
        if ($client->person) {
            $client->display_name = $client->person->first_name . ' ' . $client->person->last_name;
            $client->identifier   = $client->person->national_id;
            $client->phone        = $client->person->phone;
        } elseif ($client->organization) {
            $client->display_name = $client->organization->company_name;
            $client->identifier   = $client->organization->nif;
        }

        $client->account_number = optional($client->account)->account_number;

        $lastRequest = $client->creditRequests->first();
        $client->last_credit_status = optional($lastRequest)->status;
        $client->last_credit_date   = optional($lastRequest)->request_date;
        $client->last_credit_amount = optional($lastRequest)->requested_amount;

        return $client;
    });

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

public function visits_employee(){
    $users = \App\Models\User::whereIn('role_id', [2, 3])->get();

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