<?php

namespace App\Services;

use App\Enums\FeatureEnum;
use App\Enums\ResultDateEnum;
use App\Enums\WatchlistTypeEnum;
use App\Exceptions\ApiHttpException;
use App\Models\City;
use App\Models\Result;
use App\Models\State;
use App\Models\TrackedResult;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Mcamara\LaravelLocalization\Facades\LaravelLocalization;

class ResultsService
{
    public function getResults(array $params, User $user = null)
    {
        $itemsPerPage = $params['itemsPerPage'] ?? 5;
        $page = $params['page'] ?? 1;
        $filters = $params['filters'] ?? [];
        $orderDir = $params['orderDir'] ?? 'desc';
        $orderBy = $params['orderBy'] ?? 'id';

        $query = Result::full()
            ->select(['results.id', 'slug', 'uri', 'results.competition_id', 'result_languages.title', 'result_date', 'updated_date', 'is_public', 'result_languages.language_id', 'translation_id',
                'image_id'])
            ->with(['competition' => function ($query) use ($user) {
                $query->full()
                    ->select([
                        'competitions.id', 'wa_id', 'slug', 'uri', 'competition_languages.title', 'location_country_id', 'location_city', 'publication_date', 'nuts3_region_id',
                        'submission_until', 'promoter', 'official_number', 'is_public', 'competition_languages.competition_id', 'competition_languages.language_id', 'translation_id'
                    ])->with(['locationCountry', 'nuts3Region' => function ($query) {
                        $query->full();
                    }, 'trackedResult' => function ($query) {
                        $query->full()->select(['tracked_results.id', 'competition_id', 'slug', 'uri', 'image_id', 'tracked_result_languages.title', 'tracked_result_languages.language_id', 'translation_id']);
                    }]);
                },
                'image'
            ])
            ->where('status', 'published');

        $query->with(['watchlists' => function ($query) use ($user) {
            $query->where('user_id', $user ? $user->id : null)
                ->where('type', WatchlistTypeEnum::RESULTS->value);
        }]);

        $query = $this->_applyResultsPreFilters($query, $filters);

        if (array_key_exists('resultDate', $filters)) {
            switch ($filters['resultDate']['type']) {
                case ResultDateEnum::SINCE->value:
                    $query->where('result_date', '>=', $filters['resultDate']['value']);
                    break;
                case ResultDateEnum::LAST_7->value:
                    $query->where('result_date', '>=', Carbon::now()->subDays(7));
                    break;
                case ResultDateEnum::LAST_14->value:
                    $query->where('result_date', '>=', Carbon::now()->subDays(14));
                    break;
            }
        }

        switch ($orderBy) {
            case 'id':
                $query->orderBy('results.' . $orderBy, $orderDir);
                break;
            case 'updatedDate':
                $query->orderBy('results.updated_date', $orderDir)
                    ->orderBy('results.id', $orderDir);
                break;
            case 'relevance':
                if (!empty($filters['search'])) {
                    $query->orderByRaw("MATCH(website_objects_search.search_content)
                        AGAINST (? IN NATURAL LANGUAGE MODE) DESC", [$filters['search']]);
                }
                break;
        }

        $results = $query->where('status', 'published')
            ->paginate($itemsPerPage, ['*'], 'page', $page);

        return $results;
    }

    public function getResultBySlug(string $slug, User $user = null)
    {
        $result = Result::full()
            ->with(['watchlists' => function ($query) use ($user) {
                $query->where('user_id', $user ? $user->id : null)
                    ->where('type', WatchlistTypeEnum::RESULTS->value);
            }])
            ->with([
                'competition' => function ($query) use ($user) {
                    $query->full()->with([
                        'locationCountry',
                        'cpvs' => function ($query) {
                            $query->full()->with('departments');
                        }, 'trackedResult']
                    );
                },
                'winners' => function ($query) {
                    $query->full()->with([
                        'entities' => function ($query) {
                            $query->full()->with([
                                'authors' => function ($query) {
                                    $query->full()
                                        ->with(['user'])
                                        ->orderBy('position');
                                },
                                'contributors' => function ($query) {
                                    $query->full()
                                    ->with(['user'])
                                    ->orderBy('position');
                                },

                            ]);
                        },
                        'images' => function ($query) {
                            $query->orderBy('position');
                        },
                    ])->orderBy('position');
                },
                'otherDates' => function ($query) {
                    $query->full()->orderBy('position');
                },
                'image',
                'images' => function ($query) {
                    $query->orderBy('position');
                },
                'coordinationGroups' => function ($query) {
                    $query->full()
                    ->with([
                        'coordinationGroupMembers' => function ($query) {
                            $query->full()
                            ->with(['user', 'companyProfile'])
                            ->orderBy('position');
                        }
                    ])
                    ->orderBy('position');
                },
                'buildingTypes' => function ($query) {
                    $query->full();
                },
            ])
            ->where('slug', $slug)
            ->where('status', 'published')
            ->first();

        if (!$result) {
            return null;
        }

        if (!$result->is_public) {
            // User not logged or doesn't have any competition permissions
            if (!$user || !$user->hasFeature(FeatureEnum::FULL_COMPETITIONS)) {
                throw new ApiHttpException($user ? 403 : 401, 'access_denied');
            }
        }

        return $result;
    }

    public function getTrackedResultBySlug(string $slug, User $user = null)
    {
        $trackedResult = TrackedResult::full()
            ->with([
                'competition' => function ($query) {
                    $query->full()->with(['locationCountry', 'cpvs' => function ($query) {
                        $query->full()->with('departments');
                    }]);
                },
                'image',
                'images' => function ($query) {
                    $query->orderBy('position');
                },
                'manufacturerGroups' => function ($query) {
                    $query->full()->orderBy('position');
                },
                'executingPartyGroups' => function($query) {
                    $query->full()
                    ->with([
                        'executingPartyGroupCompany' => function ($query) {
                            $query->full()
                            ->with(['companyProfile'])
                            ->orderBy('position');
                        },
                        'executingPartyGroupMember' => function ($query) {
                            $query->full()
                            ->with(['user'])
                            ->orderBy('position');
                        },
                    ])
                    ->orderBy('position');
                }
            ])
            ->where('slug', $slug)
            ->where('status', 'published')
            ->first();

        if (!$trackedResult) {
            return null;
        }

        if (($trackedResult->competition->result && !$trackedResult->competition->result->is_public)
            || (!$trackedResult->competition->result && !$trackedResult->competition->is_public)) {
            // User not logged or doesn't have any competition permissions
            if (!$user || !$user->hasFeature(FeatureEnum::FULL_COMPETITIONS)) {
                throw new ApiHttpException($user ? 403 : 401, 'access_denied');
            }
        }

        return $trackedResult;
    }

    public function getFiltersCount(array $params)
    {
        $filters = $params['filters'] ?? [];

        $query = Result::full()->where('status', 'published');

        $this->_applyResultsPreFilters($query, $filters);

        $counts = $query->selectRaw("
            MAX(results.id) as id,
            COUNT(*) AS count_all,
            COUNT(CASE WHEN result_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) AS count_last_7,
            COUNT(CASE WHEN result_date >= DATE_SUB(NOW(), INTERVAL 14 DAY) THEN 1 END) AS count_last_14
        ")->groupByRaw("NULL")
            ->first();

        return [
            'resultDate' => [
                'all' => $counts ? $counts->count_all : 0,
                'last7' => $counts ? $counts->count_last_7 : 0,
                'last14' => $counts ? $counts->count_last_14 : 0,
            ],
        ];
    }

    private function _applyResultsPreFilters(Builder $query, array $filters)
    {
        $lang = LaravelLocalization::getCurrentLocale();

        if (!empty($filters['typeOfAnnouncement'])) {
            $query->whereHas('competition', function ($query) use ($filters) {
                $query->whereHas('procedureTypes', function ($query) use ($filters) {
                    $query->whereIn('procedure_types.id', $filters['typeOfAnnouncement']);
                });
            });
        }

        if (!empty($filters['typeOfBuilding'])) {
            $query->whereHas('buildingTypes', function ($query) use ($filters) {
                $query->whereIn('id', $filters['typeOfBuilding']);
            });
        }

        if (!empty($filters['locations'])) {
            $cityIds = array_column($filters['locations'], 'id');
            $stateIds = array_column($filters['locations'], 'stateId');

            $cities = City::whereIn('id', $cityIds)->get()->keyBy('id');
            $states = State::whereIn('id', $stateIds)->get()->keyBy('id');

            $locationInfo = array_filter(array_map(function($location) use ($cities) {
                if (isset($location['id']) && $cities->has($location['id'])) {
                    return [
                        'location' => $cities[$location['id']]->location ?? null,
                        'radius'   => $location['radius']
                    ];
                }
            }, $filters['locations']));

            $query->where(function($query) use ($states, $locationInfo) {
                if (!empty($locationInfo)) {
                    $query->orWhereHas('competition.nuts3Region', function($query) use ($locationInfo) {
                        $query->where(function ($query) use ($locationInfo) {
                            foreach ($locationInfo as $locationData) {
                                $lat = $locationData['location']->latitude;
                                $lon = $locationData['location']->longitude;
                                $radius = $locationData['radius']; // KM
                                $query->orWhereRaw("ST_Distance_Sphere(location, POINT(?, ?)) <= ?",
                                    [$lon, $lat, $radius * 1000]
                                );
                            }
                        });
                    });
                }

                if ($states->count()) {
                    $stateNutsCodes = $states->pluck('nuts_code');
                    $query->orWhereHas('competition.nuts3Region', function($query) use ($stateNutsCodes) {
                        $query->where(function ($query) use ($stateNutsCodes) {
                            foreach ($stateNutsCodes as $nutsCode) {
                                if (!$nutsCode) {
                                    // If a state has no NUTS code assigned, fakes a non-match
                                    $query->orWhere('code', 'like', 'FAKE_NUTS_CODE');
                                    continue;
                                }
                                $query->orWhere('code', 'like', "{$nutsCode}" . '%');
                            }
                        });
                    });
                }
            });
        }

        if (!empty($filters['countries'])) {
            $query->whereHas('competition', function($query) use($filters) {
                $query->whereIn('location_country_id', $filters['countries']);
            });
        }

        if (!empty($filters['isMagazineOnly'])) {
            $query->whereNotNull('wa_magazin');
        }

        if (!empty($filters['hasTrackedResult'])) {
            $query->whereHas('competition', function ($query) use ($filters) {
                $query->whereHas('trackedResult');
            });
        }

        if (!empty($filters['search'])) {
            $query->join("website_objects_search", "website_objects.website_object_id", "=", "website_objects_search.website_object_id")
                ->whereRaw("website_objects.website_object_id = website_objects_search.website_object_id")
                ->where(function ($query) use ($filters) {
                    $searchTerm = $filters['search'];
                    $query->whereRaw("MATCH(search_content)
                        AGAINST (? IN NATURAL LANGUAGE MODE)", array($searchTerm))
                        ->orWhere('result_languages.title', 'like', '%' . $searchTerm . '%');
                })
                ->where("website_objects_search.language_id", $lang);
        }

        return $query;
    }
}