<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Http\Traits\CrulRequest;
use Illuminate\Support\Collection;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class IntegrateServiceProducts extends Command
{
    use CrulRequest;

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'service-products:save {sku?} {id?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Save turboly service products into local db';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    protected $url = '/api/v1/service_products';

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->info('========================================');
        $this->info('Starting Service Products Sync');
        $this->info('Time: ' . date('Y-m-d H:i:s'));
        $this->info('========================================');
        
        $this->syncServiceProducts(true);  // Sync active products
        $this->syncServiceProducts(false); // Sync inactive products
        $this->syncPackageServices();      // Sync packages
        
        
        $this->info('========================================');
        $this->info('Service products sync completed successfully!');
        $this->info('Time: ' . date('Y-m-d H:i:s'));
        $this->info('========================================');
    }

    /**
     * Sync service products based on active status
     *
     * @param bool $activeStatus
     * @return void
     */
    private function syncServiceProducts($activeStatus)
    {
        $statusLabel = $activeStatus ? 'ACTIVE' : 'INACTIVE';
        $this->info('');
        $this->info('--- Syncing ' . $statusLabel . ' Service Products ---');
        
        $message = '';
        $filter = [];

        // Check for specific SKU or ID filter
        if (null !== $this->argument('sku')) {
            $filter['sku'] = $this->argument('sku');
            $this->info('Filter by SKU: ' . $this->argument('sku'));
        }
        
        if (null !== $this->argument('id')) {
            $filter['id'] = $this->argument('id');
            $this->info('Filter by ID: ' . $this->argument('id'));
        }

        // Set default filters if no specific filter provided
        if (empty($filter)) {
            $filter = [
                'page_limit' => 50000,
                'active' => $activeStatus ? 'true' : 'false',
            ];
        }

        // Reduce payload: use latest local updated_at as modified_after
        $filter['modified_after'] = $this->getModifiedAfterServiceProducts();
        $this->info('Filter parameters: ' . json_encode($filter));
        $this->info('Latest modified_after date: ' . $filter['modified_after']);

        $this->info('Making initial API request to get total pages...');
        $serviceProducts = $this->sendRequest('', [], $filter, 'GET');
        
        if ($serviceProducts['status'] == '200') {
            $totalPages = $serviceProducts['response']['pages'];
            $totalRecords = $serviceProducts['response']['records'];
            $this->info('? API Response OK');
            $this->info('Total pages: ' . $totalPages);
            $this->info('Total records: ' . $totalRecords);
            
            for ($x = 1; $x <= $totalPages; $x++) {
                $this->info('');
                $this->info('[Page ' . $x . '/' . $totalPages . '] Processing...');
                
                $filter['page'] = $x;
                $serviceProducts = $this->sendRequest('', [], $filter, 'GET');
                
                if ($serviceProducts['status'] == '200') {
                    $data = Collection::make($serviceProducts['response']['service_products']);
                    $recordCount = $data->count();
                    $this->info('[Page ' . $x . '] Retrieved ' . $recordCount . ' service products');
                    
                    // Step 1: Sync Categories
                    $this->info('[Page ' . $x . '] Step 1: Syncing categories...');
                    $this->syncCategories($data);
                    
                    // Step 2: Get category mappings
                    $this->info('[Page ' . $x . '] Step 2: Loading category mappings...');
                    $categoryMap = $this->getCategoryMapping();
                    
                    // Step 3: Sync service products
                    $this->info('[Page ' . $x . '] Step 3: Syncing service products to database...');
                    $this->syncServiceProductsData($data, $categoryMap);
                    
                    $this->info('[Page ' . $x . '] ? Completed successfully');
                    
                    if (empty($message)) {
                        $message .= 'success';
                    }
                } else {
                    $message .= 'error ' . $serviceProducts['status'];
                    $this->error('[Page ' . $x . '] ? API Error: ' . $message);
                }
                
                // Sleep between pages to avoid rate limiting
                if ($x < $totalPages) {
                    $this->info('[Page ' . $x . '] Waiting 3 seconds before next page...');
                    sleep(3);
                }
            }
            
            $this->info('');
            $this->info('? Completed ' . $statusLabel . ' products sync');
        } else {
            $message .= 'error ' . $serviceProducts['status'];
            $this->error('? Initial API request failed: ' . $message);
        }
    }

    /**
     * Sync service product categories
     *
     * @param Collection $data
     * @return void
     */
    private function syncCategories($data)
    {
        $categoryBank = [];
        $chunkIndex = 0;

        $data->chunk(1000)->each(function ($ch) use ($categoryBank, &$chunkIndex) {
            $chunkIndex++;
            $inputCat = '';
            $newCategoriesCount = 0;

            foreach ($ch as $key => $value) {
                if (!empty($value['service_product_type'])) {
                    if (!in_array($value['service_product_type'], $categoryBank)) {
                        // Push value into category bank
                        array_push($categoryBank, $value['service_product_type']);
                        $newCategoriesCount++;
                        
                        // Add input
                        $inputCat .= "('" . $value['service_product_type'] . "','" . $value['service_product_type'] . "','" . date('Y-m-d H:i:s') . "','" . date('Y-m-d H:i:s') . "'),";
                    }
                }
            }

            $inputCat = rtrim($inputCat, ",");

            if (!empty($inputCat)) {
                try {
                    $this->info('  ? Upserting ' . $newCategoriesCount . ' categories (chunk ' . $chunkIndex . ')');
                    \DB::connection('mysql2')->statement("INSERT INTO medical_categories (`category_name`, `category_description`, `created_at`, `updated_at`) VALUES $inputCat ON DUPLICATE KEY UPDATE `category_name`=VALUES(`category_name`), `updated_at`=VALUES(`updated_at`)");
                    $this->info('  ? Categories upserted successfully');
                } catch (\Exception $e) {
                    $this->error('  ? Category sync error: ' . $e->getMessage());
                }
            } else {
                $this->info('  ? No new categories to sync in chunk ' . $chunkIndex);
            }
        });
    }

    /**
     * Get category mapping (name => id)
     *
     * @return array
     */
    private function getCategoryMapping()
    {
        $categories = \DB::connection('mysql2')->table('medical_categories')
            ->select('category_id', 'category_name')
            ->get();

        $categoryMap = [];
        foreach ($categories as $row) {
            $categoryMap[$row->category_name] = $row->category_id;
        }
        
        $this->info('  ? Loaded ' . count($categoryMap) . ' category mappings');
        return $categoryMap;
    }

    /**
     * Sync service products data
     *
     * @param Collection $data
     * @param array $categoryMap
     * @return void
     */
    private function syncServiceProductsData($data, $categoryMap)
    {
        $chunkIndex = 0;
        $totalProcessed = 0;
        
        $data->chunk(1000)->each(function ($ch) use ($categoryMap, &$chunkIndex, &$totalProcessed) {
            $chunkIndex++;
            $input = '';
            $search = ["'"];
            $replace = ["\'"];
            $recordsInChunk = $ch->count();

            foreach ($ch as $key => $value) {
                // Set product category
                $categoryId = 1;
                if (!empty($categoryMap[$value['service_product_type']])) {
                    $categoryId = $categoryMap[$value['service_product_type']];
                }

                // Calculate price including tax for medical_price field
                $taxRate = isset($value['tax_rate']) ? $value['tax_rate'] : 0;
                $priceExTax = isset($value['price_ex_tax']) ? $value['price_ex_tax'] : 0;
                $priceIncTax = $priceExTax * (1 + $taxRate);

                // Prepare field values
                $turbolyProductId = $value['id'];
                $sku = $value['sku'];
                $medicalName = str_replace($search, $replace, $value['name']);
                $medicalDesc = str_replace($search, $replace, $value['description']);
                $taxName = isset($value['tax_name']) ? $value['tax_name'] : '';
                $productType = isset($value['service_product_type']) ? $value['service_product_type'] : '';
                $productTags = isset($value['service_product_tags']) ? $value['service_product_tags'] : '';
                $active = (int) ($value['active'] ?? 1);
                $turbolyUpdatedAt = $value['updated_at'];
                $currentTimestamp = date('Y-m-d H:i:s');

                //          medical_price, retail_price, company_supply_price, tax_rate, tax_name, 
                //          turboly_updated_at, product_type, product_tags, active, is_package, created_at, updated_at
                $input .= "(" 
                    . $turbolyProductId . ","
                    . "'" . $categoryId . "',"
                    . "'" . $sku . "',"
                    . "'" . $medicalName . "',"
                    . "'" . $medicalDesc . "',"
                    . $priceIncTax . ","
                    . $priceExTax . ","
                    . $priceExTax . ","
                    . $taxRate . ","
                    . "'" . $taxName . "',"
                    . "'" . $turbolyUpdatedAt . "',"
                    . "'" . $productType . "',"
                    . "'" . $productTags . "',"
                    . $active . ","
                    . "0," // is_package = 0
                    . "'" . $currentTimestamp . "',"
                    . "'" . $currentTimestamp . "'"
                    . "),";
            }

            $input = rtrim($input, ",");

            if (!empty($input)) {
                try {
                    $this->info('  ? Upserting ' . $recordsInChunk . ' service products (chunk ' . $chunkIndex . ')');
                    
                    // Upsert Service Products
                    \DB::connection('mysql2')->statement(
                        "INSERT INTO medicals 
                        (`turboly_product_id`, `medical_category_id`, `sku`, `medical_name`, `medical_desc`, 
                         `medical_price`, `retail_price`, `company_supply_price`, `tax_rate`, `tax_name`, 
                         `turboly_updated_at`, `product_type`, `product_tags`, `active`, `is_package`, `created_at`, `updated_at`) 
                        VALUES $input 
                        ON DUPLICATE KEY UPDATE 
                            `medical_category_id`=VALUES(`medical_category_id`),
                            `sku`=VALUES(`sku`), 
                            `medical_name`=VALUES(`medical_name`), 
                            `medical_desc`=VALUES(`medical_desc`), 
                            `medical_price`=VALUES(`medical_price`), 
                            `retail_price`=VALUES(`retail_price`), 
                            `company_supply_price`=VALUES(`company_supply_price`), 
                            `tax_rate`=VALUES(`tax_rate`), 
                            `tax_name`=VALUES(`tax_name`), 
                            `turboly_updated_at`=VALUES(`turboly_updated_at`), 
                            `product_type`=VALUES(`product_type`), 
                            `product_tags`=VALUES(`product_tags`), 
                            `active`=VALUES(`active`), 
                            `is_package`=VALUES(`is_package`),
                            `updated_at`=VALUES(`updated_at`)"
                    );
                    
                    $totalProcessed += $recordsInChunk;
                    $this->info('  ? Service products upserted successfully (Total: ' . $totalProcessed . ')');
                } catch (\Exception $e) {
                    $this->error('  ? Service products sync error (chunk ' . $chunkIndex . '): ' . $e->getMessage());
                }
            }
        });
        
        $this->info('  ? Total service products processed: ' . $totalProcessed);
    }

    /**
     * Get the latest modified_after date for incremental sync
     *
     * @return string
     */
    private function getModifiedAfterServiceProducts()
    {
        try {
            $latestUpdate = DB::connection('mysql2')
                ->table('medicals')
                ->max('updated_at');
            
            if (!empty($latestUpdate)) {
                $modifiedAfter = date('Y-m-d', strtotime($latestUpdate));
                $this->info('Using latest update date from database: ' . $modifiedAfter);
                return $modifiedAfter;
            } else {
                $this->info('No existing records found, using default date: 2015-02-14');
            }
        } catch (\Throwable $e) {
            $this->error('Error getting modified_after date: ' . $e->getMessage());
            $this->info('Falling back to default date: 2015-02-14');
        }
        
        return '2015-02-14';
    }

    /**
     * Sync service packages
     *
     * @return void
     */
    private function syncPackageServices()
    {
        $this->info('');
        $this->info('--- Syncing Service Packages ---');

        $message = '';
        $filter = [
            'page_limit' => 50000,
        ];
        
        // Add modified_after for package services if needed, currently reusing logic or separate method
        // For simplicity, fetching all active packages or filtered by date if improved
        // $filter['modified_after'] = ...

        $this->info('Making initial API request to get total pages for packages...');
        
        // Ensure "Service Package" category exists (ID 9999)
        $pkgCatId = 9999;
        $pkgCatName = 'Service Package';
        $catExists = DB::connection('mysql2')->table('medical_categories')->where('category_id', $pkgCatId)->exists();
        
        if (!$catExists) {
            $this->info("Creating category ID $pkgCatId ($pkgCatName)...");
            DB::connection('mysql2')->table('medical_categories')->insert([
                'category_id' => $pkgCatId,
                'category_name' => $pkgCatName,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]);
        }

        $url = env('TURBOLY_URL') . '/api/v1/package_services';
        $packageServices = $this->sendRequest($url, [], $filter, 'GET');

        if ($packageServices['status'] == '200') {
            $totalPages = $packageServices['response']['pages'];
            $totalRecords = $packageServices['response']['records'];
            $this->info('? Package API Response OK');
            $this->info('Total pages: ' . $totalPages);
            $this->info('Total records: ' . $totalRecords);

            for ($x = 1; $x <= $totalPages; $x++) {
                $this->info('');
                $this->info('[Package Page ' . $x . '/' . $totalPages . '] Processing...');
                
                $filter['page'] = $x;
                $packageServices = $this->sendRequest($url, [], $filter, 'GET');

                if ($packageServices['status'] == '200') {
                    $data = Collection::make($packageServices['response']['package_services']);
                    $recordCount = $data->count();
                    $this->info('[Package Page ' . $x . '] Retrieved ' . $recordCount . ' package services');

                    $this->syncPackageData($data);
                    
                    $this->info('[Package Page ' . $x . '] ? Completed successfully');
                } else {
                    $this->error('[Package Page ' . $x . '] ? API Error: ' . $packageServices['status']);
                }

                if ($x < $totalPages) {
                    sleep(3);
                }
            }
            $this->info('');
            $this->info('? Completed Package Service sync');
        } else {
             $this->error('? Initial Package API request failed: ' . $packageServices['status']);
        }
    }

    /**
     * Sync package data to database
     * 
     * @param Collection $data
     * @return void
     */
    private function syncPackageData($data)
    {
        $chunkIndex = 0;
        $totalProcessed = 0;
        $categoryMap = $this->getCategoryMapping();

        // Pre-fetch all service product IDs in this chunk to minimize DB queries for descriptions
        $allServiceIds = [];
        foreach ($data as $pkg) {
            if (!empty($pkg['services'])) {
                foreach ($pkg['services'] as $svc) {
                    $allServiceIds[] = $svc['service_product_id'];
                }
            }
        }
        $allServiceIds = array_unique($allServiceIds);

        // Fetch service details for these IDs
        $serviceDetails = [];
        if (!empty($allServiceIds)) {
            $servicesDb = \DB::connection('mysql2')->table('medicals')
                ->whereIn('turboly_product_id', $allServiceIds)
                ->select('turboly_product_id', 'medical_name', 'medical_desc')
                ->get();
            
            foreach ($servicesDb as $s) {
                // Store description or name if description empty
                $serviceDetails[$s->turboly_product_id] = !empty($s->medical_desc) ? $s->medical_desc : $s->medical_name;
            }
        }

        $data->chunk(1000)->each(function ($ch) use ($categoryMap, &$chunkIndex, &$totalProcessed, $serviceDetails) {
            $chunkIndex++;
            $input = '';
            $search = ["'"];
            $replace = ["\'"];
            $recordsInChunk = $ch->count();

            foreach ($ch as $value) {
                // Hardcoded category for packages
                $categoryId = 9999; 
                // Logic for price: Package price includes PPN 11%
                $priceIncTax = isset($value['price_ex_tax']) ? $value['price_ex_tax'] : 0;
                $taxRate = 0.11;
                $priceExTax = $priceIncTax / (1 + $taxRate);

                // Use negative ID for packages to avoid collision with services (Package ID 1 -> -1)
                $turbolyProductId = $value['id'] * -1;
                $sku = $value['sku'];
                $medicalName = str_replace($search, $replace, $value['name']);
                $medicalDesc = str_replace($search, $replace, $value['description']);
                $taxName = isset($value['tax_name']) ? $value['tax_name'] : '';
                $turbolyUpdatedAt = $value['updated_at'];
                $currentTimestamp = date('Y-m-d H:i:s');
                $active = (int) ($value['active'] ?? 1);

                // Prepare Package Services JSON
                $packageServices = [];
                if (!empty($value['services'])) {
                    foreach ($value['services'] as $svc) {
                        $svcId = $svc['service_product_id'];
                        $qty = $svc['quantity'];
                        $desc = $serviceDetails[$svcId] ?? 'Unknown Service'; // Lookup from prefetched data
                        
                        $packageServices[] = [
                            'id' => $svcId,
                            'quantity' => $qty,
                            'service_description' => $desc
                        ];
                    }
                }
                $packageServicesJson = json_encode($packageServices);
                $packageServicesJson = str_replace($search, $replace, $packageServicesJson); // Escape quotes for SQL

                $input .= "(" 
                    . $turbolyProductId . ","
                    . "'" . $categoryId . "',"
                    . "'" . $sku . "',"
                    . "'" . $medicalName . "',"
                    . "'" . $medicalDesc . "',"
                    . $priceIncTax . ","
                    . $priceExTax . ","
                    . $priceExTax . ","
                    . $taxRate . ","
                    . "'" . $taxName . "',"
                    . "'" . $turbolyUpdatedAt . "',"
                    . $active . ","
                    . "1," // is_package = 1
                    . "'" . $packageServicesJson . "'," // package_services_json
                    . "'" . $currentTimestamp . "',"
                    . "'" . $currentTimestamp . "'"
                    . "),";
            }

            $input = rtrim($input, ",");

            if (!empty($input)) {
                try {
                    $this->info('  ? Upserting ' . $recordsInChunk . ' packages (chunk ' . $chunkIndex . ')');
                    
                    \DB::connection('mysql2')->statement(
                        "INSERT INTO medicals 
                        (`turboly_product_id`, `medical_category_id`, `sku`, `medical_name`, `medical_desc`, 
                         `medical_price`, `retail_price`, `company_supply_price`, `tax_rate`, `tax_name`, 
                         `turboly_updated_at`, `active`, `is_package`, `package_services_json`, `created_at`, `updated_at`) 
                        VALUES $input 
                        ON DUPLICATE KEY UPDATE 
                            `medical_category_id`=VALUES(`medical_category_id`),
                            `sku`=VALUES(`sku`), 
                            `medical_name`=VALUES(`medical_name`), 
                            `medical_desc`=VALUES(`medical_desc`), 
                            `medical_price`=VALUES(`medical_price`), 
                            `retail_price`=VALUES(`retail_price`), 
                            `company_supply_price`=VALUES(`company_supply_price`), 
                            `tax_rate`=VALUES(`tax_rate`), 
                            `tax_name`=VALUES(`tax_name`), 
                            `turboly_updated_at`=VALUES(`turboly_updated_at`), 
                            `active`=VALUES(`active`),
                            `is_package`=VALUES(`is_package`),
                            `package_services_json`=VALUES(`package_services_json`),
                            `updated_at`=VALUES(`updated_at`)"
                    );
                    
                    $totalProcessed += $recordsInChunk;
                } catch (\Exception $e) {
                    $this->error('  ? Package sync error (chunk ' . $chunkIndex . '): ' . $e->getMessage());
                }
            }
        });
        $this->info('  ? Total packages processed: ' . $totalProcessed);
    }
}