<?php

namespace App\Http\Controllers;

use App\Models\HelpdeskTicket;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

class HelpdeskTicketController extends Controller
{

    public function index(HelpdeskTicket $helpdeskTicket, Request $request)
    {
        $helpdeskTicket = $helpdeskTicket->with('category', 'assigne_by:id,name,email', 'created_by:id,name,email');
        
        // Add comprehensive search functionality if search parameter is provided
        if ($request->filled('search')) {
            $searchTerm = $request->input('search');
            $helpdeskTicket->where(function ($q) use ($searchTerm) {
                // Search in direct ticket columns
                $q->where('ticket_id', 'like', '%' . $searchTerm . '%')  // Ticket ID
                    ->orWhere('email', 'like', '%' . $searchTerm . '%')  // Email
                    ->orWhere('subject', 'like', '%' . $searchTerm . '%')  // Subject
                    ->orWhere('status', 'like', '%' . $searchTerm . '%')  // Status
                    ->orWhere('name', 'like', '%' . $searchTerm . '%')  // Name field
                    ->orWhere('descriptions', 'like', '%' . $searchTerm . '%')  // Description
                    // Search in created_at date (format: Y-m-d or partial date)
                    ->orWhereRaw("DATE_FORMAT(created_at, '%Y-%m-%d') LIKE ?", ['%' . $searchTerm . '%'])
                    ->orWhereRaw("DATE_FORMAT(created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchTerm . '%'])
                    ->orWhereRaw("DATE_FORMAT(created_at, '%m/%d/%Y') LIKE ?", ['%' . $searchTerm . '%'])
                    // Search in Assigned To (user_id -> assigne_by relationship)
                    ->orWhereHas('assigne_by', function ($subquery) use ($searchTerm) {
                        $subquery->where(function ($nameQuery) use ($searchTerm) {
                            $nameQuery->where('name', 'like', '%' . $searchTerm . '%')
                                ->orWhere('email', 'like', '%' . $searchTerm . '%');
                        });
                    })
                    // Search in Created By (created_by relationship)
                    ->orWhereHas('created_by', function ($subquery) use ($searchTerm) {
                        $subquery->where(function ($nameQuery) use ($searchTerm) {
                            $nameQuery->where('name', 'like', '%' . $searchTerm . '%')
                                ->orWhere('email', 'like', '%' . $searchTerm . '%');
                        });
                    })
                    // Search in Category (category relationship)
                    ->orWhereHas('category', function ($subquery) use ($searchTerm) {
                        $subquery->where('name', 'like', '%' . $searchTerm . '%');
                    });
            });
        }
        
        $helpdeskTicket = $helpdeskTicket->latest()->get();
        return $this->withCount($helpdeskTicket, "Helpdesk Ticket retrieved successfully.");
    }

    public function showCustomerTickets(Request $request)
    {
        $user = Auth::user();
        $ticket = HelpdeskTicket::query();
        $ids = $this->getCustomerAndWorkspaceIds();
        
        // Determine which relation to load based on user type
        if ($ids['flag'] == 'customer') {
            // For customers, load user relation
            $ticket = $ticket->with(['created_by:id,name', 'category']);
        } else {
            // For employees, load employee relation
            $ticket = $ticket->with(['createdByEmployee', 'category']);
        }
        
        $ticket = $ticket->where(function ($query) use ($user) {
            $query->where('created_by', $user->id)
                ->orWhere('user_id', $user->id);
        });
        
        if ($request->filled('search')) {
            $searchTerm = $request->search;
            $ticket = $ticket->where(function ($q) use ($searchTerm) {
                // Search in Ticket ID
                $q->where('ticket_id', 'like', '%' . $searchTerm . '%')
                  // Search in Email
                  ->orWhere('email', 'like', '%' . $searchTerm . '%')
                  // Search in Subject
                  ->orWhere('subject', 'like', '%' . $searchTerm . '%')
                  // Search in Created By (relationship)
                  ->orWhereHas('created_by', function ($subquery) use ($searchTerm) {
                      $subquery->where(function ($nameQuery) use ($searchTerm) {
                          $nameQuery->where('name', 'like', '%' . $searchTerm . '%')
                                    ->orWhere('email', 'like', '%' . $searchTerm . '%');
                      });
                  })
                  // Search in Category (relationship)
                  ->orWhereHas('category', function ($subquery) use ($searchTerm) {
                      $subquery->where('name', 'like', '%' . $searchTerm . '%');
                  });
            });
        }
        
        $ticket = $ticket->latest()->get();
        return $this->success($ticket, "Ticket list retrieved successfully.");
    }
    public function store(Request $request)
    {
        // dd($request->all());
        return $this->storeHelpdeskTicket($request);
    }
    public function show($category)
    {
        return $this->showHelpdesk($category);
    }
    public function update(Request $request)
    {
        return $this->updateHelpdesk($request);
    }
    public function destroy($category)
    {
        return $this->destroyHelpdesk($category);
    }

    public function reply(Request $request)
    {
        return $this->replyHelpdesk($request);
    }
    public function search(Request $request)
    {
        return $this->searchHelpdesk($request);
    }

    public function customerHelpdeskList()
    {
        return $this->getCustomerHelpdesk();
    }

    public function download($id)
    {
        // return $this->downloadFile('HelpdeskTicket', 'attachments', 5);
        return $this->downloadHelpdesk($id);
    }

    // public function exportCVS()
    // {
    //    // Retrieve data from the database
    //     $users = DB::table('users')->get();

    //     // Ensure there is data to export
    //     if ($users->isEmpty()) {
    //         return response()->json(['message' => 'No data available for export'], 404);
    //     }

    //     // Write data to CSV
    //     $csvFileName = 'user.csv';
    //     $csvFilePath = public_path($csvFileName); // Save in the public directory
    //     $csvFile = fopen($csvFilePath, 'w');

    //     // Get the column headers from the first row
    //     $headers = array_keys((array) $users[0]);
    //     fputcsv($csvFile, $headers);

    //     // Write each user's data to the CSV
    //     foreach ($users as $user) {
    //         fputcsv($csvFile, (array) $user);
    //     }

    //     fclose($csvFile);

    //     // Download the CSV file and delete it after sending
    //     return response()->download($csvFilePath)->deleteFileAfterSend(true);
    //  }




}
