<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Api\DataExcel\DataExcelController;
use App\Models\DataExcel;
use App\Models\Category;
use App\Models\SubCategory;
use App\Exports\FilteredDataExport;
use Illuminate\Http\Request;
use App\Http\Requests\UpdateData;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Validation\Rule;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index(Request $request)
    {
        $categories = Category::orderBy('name')->get();
        
        // Get all subcategories but we'll filter them in the view with JS
        $subcategories = Subcategory::orderBy('name')->get();
        
        // Get unique countries
        $countries = DB::table('data_excels')
                      ->select('country')
                      ->distinct()
                      ->whereNotNull('country')
                      ->where('country', '!=', '')
                      ->pluck('country');
        
        $query = DataExcel::with(['category', 'subcategory']);
        
        // Apply category filter
        if ($request->has('categories') && is_array($request->categories)) {
            $query->whereIn('category_id', $request->categories);
        }
        
        // Apply subcategory filter
        if ($request->has('subcategories') && is_array($request->subcategories)) {
            $query->whereIn('subcategory_id', $request->subcategories);
        }
        
        // Apply country filter - ensure correct matching
        if ($request->has('countries') && is_array($request->countries) && !empty($request->countries)) {
            $query->whereIn('country', $request->countries);
        }
        
        // Apply search
        if ($request->has('search') && !empty($request->search)) {
            $searchTerm = '%' . $request->search . '%';
            $query->where(function($q) use ($searchTerm) {
                $q->where('destination_name', 'like', $searchTerm)
                  ->orWhere('address', 'like', $searchTerm)
                  ->orWhere('phone', 'like', $searchTerm)
                  ->orWhere('phone2', 'like', $searchTerm)
                  ->orWhere('email', 'like', $searchTerm)
                  ->orWhere('country', 'like', $searchTerm)
                  ->orWhereHas('category', function($subq) use ($searchTerm) {
                      $subq->where('name', 'like', $searchTerm);
                  })
                  ->orWhereHas('subcategory', function($subq) use ($searchTerm) {
                      $subq->where('name', 'like', $searchTerm);
                  });
            });
        }
        
        $datas = $query->orderBy('id', 'desc')->paginate(8);
        
        // Pass a flag to easily check if we have data
        $hasData = $datas->total() > 0;
        
        return view('home', compact('datas', 'categories', 'subcategories', 'countries', 'hasData'));
    }

    public function show($id)
    {
        $row_data = DataExcel::findOrFail($id);
    
       return view('dashboard.excel.show',compact('row_data'));
    }

    public function edit(Request $request)
    {
        $row = DataExcel::findOrFail($request->id);
        $categories = Category::all();
        $subcategories = Subcategory::all();

        return view('dashboard.excel.edit',compact('row','categories','subcategories'));
    }

    public function update(UpdateData $request)
    {
        DB::beginTransaction();
        try {
            $row = DataExcel::findOrFail($request->id);
    
            if ($request->category_id) {
                $category = Category::findOrFail($request->category_id);
                $category->update(['name' => $category->name]);
            }
    
            if (is_array($request->subcategory_id)) {
                $subcategory = SubCategory::findOrFail($request->subcategory_id[0]);
                $subcategory->update([
                    'name' => $subcategory->name,
                    'category_id' => $category->id ?? null
                ]);
            }
    
            $row->update([
                'destination_name' => $request->destination_name,
                'address' => $request->address,
                'phone' => ltrim($request->phone, '-'), // إزالة `-` من البداية
                'phone2' => ltrim($request->phone2, '-'),
                'email' => $request->email,
                'country' => $request->country,
                'website' => $request->website,
                'google_map' => $request->google_map,
                'responsible_name' => $request->responsible_name,
                'category_id'=>$category->id,
                'subcategory_id'=>$subcategory->id
            ]);
    
            DB::commit();
            toastr()->success('The data has been updated successfully');
            return redirect()->route('home.index');
    
        } catch (\Exception $e) {
            DB::rollBack();
            return back()->with('error', 'Error updating data: ' . $e->getMessage());
        }
    }
    
    
    
    
    public function destroy(Request $request)
    {
    //    dd($request);
        DataExcel::where('id',$request->id)->delete();
        Category::where('id',$request->data_excel_id)->delete();
        return redirect()->route('home.index');
        toastr()->success('Data of Excel are Deleted successfully');

    }

    public function exportFiltered(Request $request)
    {
       // Start with a base query
    $query = DataExcel::with(['category', 'subcategory']);
    
    // Apply category filter (multiple)
    if ($request->has('categories') && is_array($request->categories)) {
        $query->whereIn('category_id', $request->categories);
    }
    
    // Apply subcategory filter (multiple)
    if ($request->has('subcategories') && is_array($request->subcategories)) {
        $query->whereIn('subcategory_id', $request->subcategories);
    }
    
    // Apply country filter (multiple)
    if ($request->has('countries') && is_array($request->countries)) {
        $query->whereIn('country', $request->countries);
    }
    
    // Apply search across multiple columns
    if ($request->has('search') && !empty($request->search)) {
        $searchTerm = '%' . $request->search . '%';
        $query->where(function($q) use ($searchTerm) {
            $q->where('destination_name', 'like', $searchTerm)
              ->orWhere('address', 'like', $searchTerm)
              ->orWhere('phone', 'like', $searchTerm)
              ->orWhere('phone2', 'like', $searchTerm)
              ->orWhere('email', 'like', $searchTerm)
              ->orWhere('country', 'like', $searchTerm)
              ->orWhereHas('category', function($subq) use ($searchTerm) {
                  $subq->where('name', 'like', $searchTerm);
              })
              ->orWhereHas('subcategory', function($subq) use ($searchTerm) {
                  $subq->where('name', 'like', $searchTerm);
              });
        });
    }
    
    // Get the filtered data
    $filteredData = $query->get();
    
    // Generate a filename based on current filters
    $filename = 'filtered_data_' . date('Y-m-d_His') . '.xlsx';
    
    // Return the Excel download
    return Excel::download(new DataExport($filteredData), $filename);
    }

    public function delete_all(Request $request)
    {
       
        $delete_all_id = explode(",", $request->delete_all_id);

        DataExcel::whereIn('id', $delete_all_id)->Delete();
        toastr()->success('the Rows that you choosed are deleted successfully');
        return redirect()->route('home.index');
    }
    // public function sendEmail(Request $request)
    // {

    //     $ids = explode(",", $request->send_email_ids);
    //     $Datas = DataExcel::whereIn('id', $ids)->get();
    //     return view('dashboard.Messages.create',compact('Datas'));
    // }

    

    


    

    

    
    
}
