<?php

namespace App\Imports;

use App\Models\City;
use App\Models\Business;
use App\Models\Category;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Str;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class BusinessesImport implements ToCollection, WithChunkReading, WithHeadingRow, ShouldQueue
{
    protected $adminId;
    protected $categoryCache = [];
    protected $citiesCache = [];


    public function __construct($adminId)
    {
        $this->adminId = $adminId;
    }

    public function collection(Collection $rows)
    {
        //  Log::info('BusinessesImport total rows: '.count($rows));
        $businessCategories = [];
        $businessCities = [];
        $this->citiesCache = City::pluck('id', 'name')->toArray();
        $count=0;
        foreach ($rows as $row) {
            if (!isset($row['address'])) {
                Log::error('Address is missing for row: ' . json_encode($row));
                continue;
            }
            $address = explode('|', trim($row['address']))[0];
            $addressComponents = explode(',', trim($address));
            $cityName = trim(end($addressComponents));

            // $cityName = trim(end(explode('|', trim($row['address']))));
            $city_id = $this->citiesCache[$cityName] ?? null;
    
            if (!$city_id) {
                $city = City::firstOrCreate(['name' => 'Other'], ['slug' => Str::slug('Other')]);
                $city_id = $city->id;
                $this->citiesCache['Other'] = $city_id; // Update cache
            }
            
            $categories = explode('|', trim($row['category']));
            // $existingBusiness = DB::table('businesses')->where('website', trim($row['website_link']))->first();
            $address = str_replace('#', ' No ', trim($address));
            $name = trim($row['business_title']) ?? '';
            
            $website = trim($row['website_link']);
            $website = !empty($website) ? $website : null; // Convert empty string to NULL
            
            // 1️⃣ First, check if a business already exists with the same website (if website is not null)
            if (!is_null($website)) {
                $existingBusinessByWebsite = DB::table('businesses')->where('website', $website)->first();
                if ($existingBusinessByWebsite) {
                    Log::info('Skipping duplicate business with website: ' . $website);
                    continue; // Skip this record
                }
            }
            
            // 2️⃣ Then, check if a business already exists with the same name AND address
            $existingBusinessByNameAndAddress = DB::table('businesses')
                ->where('name', trim($row['business_title']))
                ->where('address', $address)
                ->first();
            
            if ($existingBusinessByNameAndAddress) {
                Log::info('Skipping duplicate business with name and address: ' . trim($row['business_title']) . ' - ' . $address);
                continue; // Skip this record
            }
            $businessId='';
            // 3️⃣ Insert the business if it's unique
            try {
                $businessId = DB::table('businesses')->insertGetId([
                    'name' => trim($row['business_title']) ?? '',
                    'slug' => $row['business_title'] ? $this->generateUniqueSlug($row['business_title'], $city_id): '',
                    'address' => $address ?? '',
                    'phone' => trim($row['telephone']) ?? '',
                    'website' => $website,  
                    'status' => 1,
                    'is_feature' => rand(0,1),
                    'admin_id' => $this->adminId,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                if ($businessId) {
                    $count++;
                    // Log::info('Business added: ' . (trim($row['business_title']) ?? 'N/A'));
                }
            } catch (\Exception $e) {
                Log::error('Failed to insert business: ' . $e->getMessage());
            }

            $businessCities[] = [
                'business_id' => $businessId,
                'city_id' => $city_id,
            ];
            foreach ($categories as $categoryName) {
                // Remove leading/trailing whitespace
                $categoryName = trim($categoryName);
                
                // Replace multiple spaces with a single space
                $categoryName = preg_replace('/\s+/', ' ', $categoryName);
            
                $slug = Str::slug($categoryName);
            
                if (!isset($this->categoryCache[$categoryName])) {
                    $category = Category::firstOrCreate(
                        ['name' => $categoryName],
                        ['slug' => $slug]
                    );
            
                    // Cache the category id by name
                    $this->categoryCache[$categoryName] = $category->id;
                }
            
                $businessCategories[] = [
                    'business_id' => $businessId,
                    'category_id' => $this->categoryCache[$categoryName],
                ];
            }


            // Insert business-category relationships in batches
            if (count($businessCategories) >= 1000) {
                $this->insertBusinessCategories($businessCategories);
                $businessCategories = [];
            }
            if (count($businessCities) >= 1000) {
                $this->insertBusinessCities($businessCities);
                $businessCities = [];
            }
        }

        // Insert any remaining business-category relationships
        if (!empty($businessCategories)) {
            $this->insertBusinessCategories($businessCategories);
        }
        if (!empty($businessCities)) {
            $this->insertBusinessCities($businessCities);
        }
        Log::info('Total Business added: '. $count);
    }
    protected function generateUniqueSlug($name, $cityId)
    {
        $slug = Str::slug($name);
        $city = City::find($cityId);
        $cityName = $city ? Str::slug($city->name) : null;
    
        // Step 1: Check if the initial slug is unique
        if (!Business::where('slug', $slug)->exists()) {
            return $slug;
        }
    
        // Step 2: Append the city name
        if ($cityName && $cityName !== 'other') {
            $slugWithCity = $slug . '-' . $cityName;
            if (!Business::where('slug', $slugWithCity)->exists()) {
                return $slugWithCity;
            }
        }
    
        // Step 3: Handle "Other" city or fallback with random numbers
        do {
            $uniqueSlug = $slug . '-' . rand(100, 999);
        } while (Business::where('slug', $uniqueSlug)->exists());
    
        return $uniqueSlug;
    }

    protected function insertBusinessCategories(array $businessCategories)
    {
        DB::table('business_category')->insert($businessCategories);
    }
    protected function insertBusinessCities(array $businessCities)
    {
        DB::table('business_city')->insert($businessCities);
    }
    public function chunkSize(): int
    {
        return 1000;
    }
}