<?php

namespace App\Repositories\Reports;

use App\Models\Asset;
use App\Models\AssetHistory;
use App\Models\PurgeAssetHistory;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class DuplicateAssetsRepository
{

    /**
     * Get assets list with matching serial numbers
     * 
     * @return object
     */
    public function getAssetsListWithMatchingSerialNumber()
    {
        $query = Asset::where('serial_no', '!=', '')
            ->with(['airwatch', 'jamf', 'chromebook', 'intune', 'kandjiDevices', 'mobileIron'])
            ->where(DB::raw("SUBSTRING(serial_no, -8)"), '!=', '')
            ->select(
                'assets.id',
                'assets.asset_tag',
                'assets.serial_no',
                'assets.asset_type_id',
                'assets.location_id',
                'assets.user_id',
                'assets.make_and_model_id',
                'assets.technical_spec_id',
                DB::raw('count(*) as total'),
                'asset_statuses.name as asset_status_name',
                'asset_statuses.slug as asset_status_slug',
                'asset_types.name as asset_type_name',
                'users.first_name as user_first_name',
                'users.last_name as user_last_name',
                'users.email as user_email',
                'locations.room_name as location_room_name',
                'make_and_models.name as make_and_model_name',
                'technical_specs.details as technical_spec_details',
                DB::raw("GROUP_CONCAT(assets.serial_no SEPARATOR ',') as matching_serials"),
                DB::raw("GROUP_CONCAT(assets.id SEPARATOR ',') as matching_assets"),
                DB::raw("GROUP_CONCAT(assets.location_id SEPARATOR ',') as matching_locations"),
                DB::raw("GROUP_CONCAT(assets.user_id SEPARATOR ',') as matching_users"),
                DB::raw("GROUP_CONCAT(assets.asset_status_id SEPARATOR ',') as matching_statuses"),
                DB::raw(1 . ' as is_parent')
            )
            ->leftJoin('asset_statuses', 'assets.asset_status_id', '=', 'asset_statuses.id')
            ->leftJoin('asset_types', 'assets.asset_type_id', '=', 'asset_types.id')
            ->leftJoin('users', 'assets.user_id', '=', 'users.id')
            ->leftJoin('locations', 'assets.location_id', '=', 'locations.id')
            ->leftJoin('make_and_models', 'assets.make_and_model_id', '=', 'make_and_models.id')
            ->leftJoin('technical_specs', 'assets.technical_spec_id', '=', 'technical_specs.id')
            ->groupBy(DB::raw("SUBSTRING(serial_no, -8)"))
            ->having("total",  ">", 1)
            ->orderBy('id');

        return $query;
    }

    /**
     * Get assets list with matching asset tags
     * @return 
     */
    public function getAssetsListWithMatchingAssetTag()
    {
        $query = Asset::where('asset_tag', '!=', '')
            ->with(['airwatch', 'jamf', 'chromebook', 'intune', 'kandjiDevices', 'mobileIron'])
            ->where(DB::raw("SUBSTRING(asset_tag, -8)"), '!=', '')
            ->select(
                'assets.id',
                'assets.asset_tag',
                'assets.serial_no',
                'assets.asset_type_id',
                'assets.location_id',
                'assets.user_id',
                'assets.make_and_model_id',
                'assets.technical_spec_id',
                DB::raw('count(*) as total'),
                'asset_statuses.name as asset_status_name',
                'asset_statuses.slug as asset_status_slug',
                'asset_types.name as asset_type_name',
                'users.first_name as user_first_name',
                'users.last_name as user_last_name',
                'users.email as user_email',
                'locations.room_name as location_room_name',
                'make_and_models.name as make_and_model_name',
                'technical_specs.details as technical_spec_details',
                DB::raw("GROUP_CONCAT(assets.asset_tag SEPARATOR ',') as matching_tags"),
                DB::raw("GROUP_CONCAT(assets.id SEPARATOR ',') as matching_assets"),
                DB::raw("GROUP_CONCAT(assets.location_id SEPARATOR ',') as matching_locations"),
                DB::raw("GROUP_CONCAT(assets.user_id SEPARATOR ',') as matching_users"),
                DB::raw("GROUP_CONCAT(assets.asset_status_id SEPARATOR ',') as matching_statuses"),
                DB::raw(1 . ' as is_parent')
            )
            ->leftJoin('asset_statuses', 'assets.asset_status_id', '=', 'asset_statuses.id')
            ->leftJoin('asset_types', 'assets.asset_type_id', '=', 'asset_types.id')
            ->leftJoin('users', 'assets.user_id', '=', 'users.id')
            ->leftJoin('locations', 'assets.location_id', '=', 'locations.id')
            ->leftJoin('make_and_models', 'assets.make_and_model_id', '=', 'make_and_models.id')
            ->leftJoin('technical_specs', 'assets.technical_spec_id', '=', 'technical_specs.id')
            ->groupBy(DB::raw("SUBSTRING(asset_tag, -8)"))
            ->having("total",  ">", 1)
            ->orderBy('id');

        return $query;
    }

    /**
     * Get assets data with matching serial numbers
     * 
     * @param string $serialNo
     * 
     * @return object
     */
    public function getDuplicatedChilds($childAssets)
    {
        return Asset::select('id', 'serial_no', 'asset_tag', 'location_id', 'user_id', 'asset_type_id', 'asset_status_id', 'make_and_model_id', 'technical_spec_id')
            ->with([
                'assetType',
                'assetStatus:id,name,slug,has_user,has_location',
                'user:id,first_name,last_name,email',
                'location:id,room_name',
                'makeAndModel:id,name',
                'technicalSpec:id,details',
                'airwatch:id,asset_id,last_seen',
                'intune:id,asset_id,checkin_date',
                'jamf:id,checkin_date',
                'kandjiDevices:id,asset_id,last_checkin',
                'mobileIron:id,asset_id,last_checkin',
                'chromebook:id,asset_id,last_sync',
            ])
            ->whereIn('id', $childAssets)->get();
    }

    /**
     * Delete duplicated assets
     * 
     * @param int $assetId
     * 
     * @return void
     */
    public function deleteDuplicatedAsset($assetId)
    {
        $asset = Asset::find($assetId);
        $assetData = [
            'user_id'           => Auth::id(),
            'asset_type_id'     => $asset->asset_type_id,
            'make_and_model_id' => $asset->make_and_model_id,
            'technical_spec_id' => $asset->technical_spec_id,
            'asset_tag'         => $asset->asset_tag,
            'serial_no'         => $asset->serial_no,
        ];
        PurgeAssetHistory::create($assetData);
        $asset->delete();
        AssetHistory::where('asset_id', $assetId)->delete();
    }

    /**
     * Filters the query to find records with duplicate serial numbers or asset tags.
     *
     * This function checks whether the last 8 characters of the given field match
     * the last 8 characters of the provided value(s). It is designed to prevent SQL injection
     * by using Laravel's query bindings instead of direct string concatenation.
     *
     * @param \Illuminate\Database\Query\Builder $query The query builder instance.
     * @param string $field The database column to be filtered (e.g., 'serial_no' or 'asset_tag').
     * @param string|array $value A single value or an array of values to compare.
     * 
     * @return \Illuminate\Database\Query\Builder The modified query builder with the applied filter.
     * 
     * @throws \Exception If the provided field is not allowed to prevent SQL injection risks.
     */
    public function filterWithDuplicatedSerialOrTag($query, $field, $value)
    {
        if ($value != '') {
            if (is_array($value)) {
                $query = $query->where(function ($query) use ($field, $value) {
                    foreach ($value as $v) {
                        // Secure substring comparison using bindings.
                        $query->orWhereRaw("SUBSTRING($field, -8) = SUBSTRING(?, -8)", [$v]);
                    }
                });
            } else {
                // Secure single-value comparison using bindings.
                $query = $query->whereRaw("SUBSTRING($field, -8) = SUBSTRING(?, -8)", [$value]);
            }
        }

        return $query;
    }

    /**
     * Filters the query to find records with duplicated fields using FIND_IN_SET(). (Commented as not working properly)
     *
     * This function ensures the input parameters are safely handled to prevent SQL injection.
     *
     * @param \Illuminate\Database\Query\Builder $query The query builder instance.
     * @param string $field The database column to filter.
     * @param string $value The value to search within the field.
     * 
     * @return \Illuminate\Database\Query\Builder The modified query builder with the applied filter.
     * 
     * @throws \Exception If the provided field is not allowed to prevent SQL injection risks.
     */

    public function filterWithDuplicatedFields($query, $field, $value)
    {
        // Validate input to prevent SQL injection.
        if (empty($value)) {
            return $query;
        }

        // Convert query builder to raw SQL safely using bindings.
        $subquery = DB::table(DB::raw("({$query->toSql()}) as sub"))
        ->mergeBindings($query);

        return $subquery->whereRaw("FIND_IN_SET(?, {$field})", [$value]);
    }


    /**
     * get Asset data with id 
     * 
     * @param int $assetId
     * 
     * @return object
     */
    public function getAssetData($assetId)
    {
        return Asset::with(['assetType', 'assetStatus'])->find($assetId);
    }

    /**
     * Merge duplicate asset data to primary
     * 
     * @param object $secondaryAssetData
     * @param int $primaryAssetId
     * 
     * @return void
     */
    public function mergeDuplicateDataToPrimary($secondaryAssetData, $primaryAssetId)
    {
        $assetData = $this->getMergeAssetData($secondaryAssetData);

        return Asset::where('id', $primaryAssetId)->update($assetData);
    }

    /**
     * Merge duplicate asset's history to main asset before delete
     * 
     * @param object $primaryAssetData
     * @param object $secondaryAssettData
     * 
     * @return void
     */
    public function mergeDuplicateAssetHistoryToPrimary($primaryAssetData, $secondaryAssetData)
    {
        $histories = AssetHistory::where('asset_id', $secondaryAssetData->id)->get();


        foreach ($histories as $history) {
            $description = str_replace('href="/assets/' . $secondaryAssetData->id . '"', 'href="/assets/' . $primaryAssetData->id . '"', $history->description);
            $description = str_replace($secondaryAssetData->serial_no . ' </a>', $primaryAssetData->serial_no . ' </a>', $description);

            AssetHistory::where('id', $history->id)->update([
                'asset_id' => $primaryAssetData->id,
                'description' => $description
            ]);
        }
    }

    /**
     * get data to merge duplicate asset data to primary
     * 
     * @param object $secondaryAssetData
     * 
     * @return array
     */
    private function getMergeAssetData($secondaryAssetData)
    {
        $assetData = [
            "certificate_added_date" => convert_to_db_date($secondaryAssetData->certificate_added_date),
            "ewaste_certificate" => $secondaryAssetData->ewaste_certificate,
            'asset_status_id' => $secondaryAssetData->asset_status_id
        ];

        if ($secondaryAssetData->assetStatus->slug == "end_of_life_data_wiped") {
            $assetData['location_id'] = "";
        }

        return $assetData;
    }
}
