<?php

namespace App\Services;

use App\Models\Account;
use App\Models\AccountMember;
use App\Models\Address;
use App\Models\CompanyProfile;
use App\Models\CompanySpecialisation;
use App\Models\Country;
use App\Models\Office;
use App\Models\ProcedureType;
use App\Models\Project;
use App\Models\ProjectImage;
use App\Models\SearchProfile;
use App\Models\Subscription;
use App\Models\SubscriptionPlan;
use App\Models\User;
use Carbon\Carbon;
use GuzzleHttp\Client;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;

class PimcoreCustomerImportService
{
    /*
    const SUBSCRIPTION_TYPE_MAPPING = [
        69 => null,
        70 => 13,
        942 => 14,
        79867 => 13
    ];
    */

    const ANNOUNCEMENT_TYPE_MAPPING = [
        1 => 26, // Offener Wettbewerb
        2 => 27, // Nicht offener Wettbewerb
        3 => 28, // Einladungswettbewerb
        4 => 214, // Sonstiger Wettbewerb
        5 => 215, // Online Wettbewerb
        6 => 216, // Studentenwettbewerb
        7 => 217, // Stipendienvergabe
        8 => 218, // Award
        9 => 219, // Verhandlungsverfahren
        10 => 220, // Offenes Verfahren
        11 => 221, // Nicht offenes Verfahren
        12 => 222, // Beschleunigtes Verhandlungsverfahren
        13 => 223, // Beschleunigtes, nicht offenes Verfahren
        15 => 28002, // Verfarhren zur Vorinformation oder regelmäßigen Bekanntmachung
    ];

    /**
     * The database connection for Pimcore.
     *
     * @var \Illuminate\Database\Connection
     */
    protected $connection;
    protected $pimcoreAdminSid;
    protected $companySpecialisationsMapping;
    protected $subscriptionPlans;
    protected $procedureTypeMapping;
    protected $subscriptionsMapping;

    /**
     * Create a new PimcoreImportService instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->connection = DB::connection('pimcore');

        $this->companySpecialisationsMapping = CompanySpecialisation::select(['id', 'pimcore_id'])
            ->whereNotNull('pimcore_id')
            ->get()
            ->keyBy('pimcore_id');

        $this->procedureTypeMapping = ProcedureType::get()->map(function ($procedureType) {
            if ($procedureType->migration_type_id && isset(self::ANNOUNCEMENT_TYPE_MAPPING[$procedureType->migration_type_id])) {
                return [
                    'id' => $procedureType->id,
                    'pimcore_id' => self::ANNOUNCEMENT_TYPE_MAPPING[$procedureType->migration_type_id],
                ];
            }
            return null;
        })->filter()->keyBy('pimcore_id');

        $this->subscriptionsMapping = SubscriptionPlan::select(['id', 'pimcore_id'])
            ->whereNotNull('pimcore_id')
            ->get()
            ->keyBy('pimcore_id');
    }

    /**
     * Import all customers from Pimcore.
     *
     * @return void
     */
    public function importAllCustomers(string $pimcoreAdminSid)
    {
        $this->pimcoreAdminSid = $pimcoreAdminSid;

        $customers = $this->connection->table('object_query_9')
            ->select('oo_id')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('objects')
                    ->whereColumn('objects.o_id', 'object_query_9.oo_id')
                    ->where('objects.o_published', 1);
            })
            ->get();

        Log::info("Found " . count($customers) . " customers to import from Pimcore.");
        foreach ($customers as $customer) {
            $this->importCustomer($customer->oo_id);
        }
    }

    /**
     * Import customers from Pimcore within a specific ID range.
     *
     * @return void
     */
    public function importCustomers(int $fromId, int $toId, string $pimcoreAdminSid)
    {
        $this->pimcoreAdminSid = $pimcoreAdminSid;

        $customers = $this->connection->table('object_query_9')
            ->select('oo_id')
            ->whereBetween('oo_id', [$fromId, $toId])
            ->get();

        Log::info("Found " . count($customers) . " customers to import from Pimcore.");
        foreach ($customers as $customer) {
            $this->importCustomer($customer->oo_id);
        }
    }

    public function importCustomer(int $memberId)
    {
        $sourceMember = $this->connection->table('object_query_9')
            ->where('oo_id', $memberId)
            ->first();
        $sourceObject = $this->connection->table('objects')->where('o_id', $memberId)->first();
        // $sourceSubscriptions = $this->connection->table('object_collection_abo_9')
        //    ->where('o_id', $memberId)
        //    ->get();
        $sourceProfile = $this->connection->table('object_brick_query_UserProfile_9')
            ->where('o_id', $memberId)
            ->first();
        // $sourceNewsletter = $this->connection->table('object_brick_query_UserNewsletter_9')
        //    ->where('o_id', $memberId)
        //    ->first();

        if (!$sourceMember) {
            Log::warning("Member with ID $memberId not found in Pimcore.");
            return;
        }

        $email = $sourceMember->email;

        $user = User::where('email', $email)->first();

        if ($user) {
            Log::info("User with email $email already exists. Skipping import for member ID $memberId.");
            return;
        }

        DB::beginTransaction();
        try {
            $user = $this->createUser($sourceMember, $sourceObject);

            if ($sourceProfile || $sourceMember->abotyp__id != 69) {
                DB::beginTransaction();
                try {
                    $account = $this->createAccount($user, $sourceMember, $sourceProfile);
                    DB::commit();
                } catch (\Exception $e) {
                    Log::error("Failed to create account for member ID $memberId with email $email. Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
                    DB::rollBack();
                    throw $e;
                }
                DB::beginTransaction();
                try {
                    $this->createSubscription($account, $sourceMember);
                    DB::commit();
                } catch (\Exception $e) {
                    Log::error("Failed to create subscription for member ID $memberId with email $email. Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
                    DB::rollBack();
                    throw $e;
                }
            }

            // For now we won't import saved searches and newsletters since there is no clear mapping
            // if ($sourceNewsletter) {
            //    $this->createSavedSearch($user, $sourceNewsletter, true);
            // }

            DB::commit();
            Log::info("Successfully imported member ID $memberId with email $email.");
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Failed to import member ID $memberId with email $email. Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
        }
    }

    protected function createUser($sourceMember, $sourceObject): User
    {
        $user = new User();
        $user->email = $sourceMember->email;
        $user->first_name = $sourceMember->firstname == 'N' ? null : $sourceMember->firstname;
        $user->last_name = $sourceMember->lastname == 'N' ? null : $sourceMember->lastname;
        $user->password = $sourceMember->password ? $sourceMember->password : Str::random(32);
        $user->is_legacy_password = true;
        $user->wa_customer_id = $sourceMember->customerId;
        $user->legacy_id = $sourceObject->o_key;
        $user->status = User::STATUS_ACTIVE; // TODO: check if we can set it to active directly
        $user->registration_step = User::REGISTRATION_STEP_COMPLETED;
        $user->salutation = $sourceMember->title ? mb_substr($sourceMember->title, 0, 100) : null;
        if ($sourceMember->registerDate) {
            $user->registered_at = Carbon::createFromTimestamp($sourceMember->registerDate);
        }
        if ($sourceMember->lastLogin) {
            $user->last_login = Carbon::createFromTimestamp($sourceMember->lastLogin);
        }
        $user->save();

        return $user;
    }

    protected function createAccount($user, $sourceMember, $sourceProfile): ?Account
    {
        $account = new Account();
        $account->type = Account::TYPE_REGULAR;
        $account->owner()->associate($user);

        $account->company = $sourceMember->company ? mb_substr($sourceMember->company, 0, 255) : null;
        $account->tax_id = $sourceMember->ustid ? mb_substr($sourceMember->ustid, 0, 45) : null;
        $account->save();

        $profile = new CompanyProfile();
        $profile->account()->associate($account);
        $profile->name = $sourceMember->company ? mb_substr($sourceMember->company, 0, 255) : null;
        $profile->email = $sourceMember->email ? mb_substr($sourceMember->email, 0, 255) : null;
        $profile->phone = $sourceMember->fon ? mb_substr($sourceMember->fon, 0, 20) : null;
        $profile->is_active = false;
        $profile->save();

        $address = new Address();
        $address->name = $sourceMember->company ? mb_substr($sourceMember->company, 0, 255) : $user->first_name . ' ' . $user->last_name;
        $address->address = $sourceMember->streetNo ? mb_substr($sourceMember->streetNo, 0, 255) : '';
        $address->floor = $sourceMember->addressMore ? mb_substr($sourceMember->addressMore, 0, 255) : '';

        $address->postal_code = $sourceMember->plz ? mb_substr($sourceMember->plz, 0, 20) : null;
        $address->location = $sourceMember->city ? mb_substr($sourceMember->city, 0, 255) : null;
        if ($sourceMember->country) {
            $countryId = Country::where('iso2', $sourceMember->country)->first()->id;
            $address->country_id = $countryId;
        }
        $address->phone = $sourceMember->fon ? mb_substr($sourceMember->fon, 0, 20) : null;
        $address->save();

        $account->address()->associate($address);
        $account->save();

        $member = new AccountMember();
        $member->invitation_first_name = $user->first_name;
        $member->invitation_last_name = $user->last_name;
        $member->invitation_email = $user->email;
        $member->is_admin = true;
        $member->status = AccountMember::STATUS_ACCEPTED;
        $member->account()->associate($account);
        $member->user()->associate($user);
        $member->save();

        if ($sourceProfile) {
            $office = new Office();
            $office->companyProfile()->associate($profile);

            $office->name = $sourceProfile->profileTitle ? mb_substr($sourceProfile->profileTitle, 0, 255) : '';
            $office->address = $sourceProfile->profileStreetNo ? mb_substr($sourceProfile->profileStreetNo, 0, 255) : '';
            $office->floor = '';
            $office->postal_code = $sourceProfile->profilePlz ? mb_substr($sourceProfile->profilePlz, 0, 45) : null;
            $office->location = $sourceProfile->profileCity ? mb_substr($sourceProfile->profileCity, 0, 255) : null;
            $office->is_main_office = true;
            $office->email = $sourceProfile->profileEmail ? mb_substr($sourceProfile->profileEmail, 0, 255) : null;
            $office->phone = $sourceProfile->profileFon ? mb_substr($sourceProfile->profileFon, 0, 20) : null;
            $office->fax = $sourceProfile->profileFax ? mb_substr($sourceProfile->profileFax, 0, 20) : null;
            $office->website_url = $sourceProfile->profileWeb ? mb_substr($sourceProfile->profileWeb, 0, 255) : null;

            $profile->website_url = $office->website_url;
            $profile->name = $sourceProfile->profileTitle ? mb_substr($sourceProfile->profileTitle, 0, 255) : $profile->name;
            $profile->subtitle = $sourceProfile->profileSlogan ? mb_substr($sourceProfile->profileSlogan, 0, 255) : null;
            $profile->location = $sourceProfile->profileLocation ? mb_substr($sourceProfile->profileLocation, 0, 255) : null;
            $profile->description = $this->toRichTextDescription($sourceProfile->profileDescription);
            $profile->is_active = $sourceProfile->profileActive ? true : false;

            if ($sourceProfile->profileCountry) {
                $countryId = Country::where('iso2', $sourceProfile->profileCountry)->first()->id;
                $office->country_id = $countryId;
            }

            if ($sourceProfile->profileImage) {
                try {
                    $profileImage = $this->importImage($sourceProfile->profileImage, config('path.internal.company_images'));
                    $profile->company_image = $profileImage['filename'];
                } catch (\Exception $e) {
                    Log::error("Failed to import profile image for member ID " . $sourceMember->oo_id . ": " . $e->getMessage());
                }
            }

            if ($sourceProfile->profileProfessions) {
                $professions = explode(',', $sourceProfile->profileProfessions);
                $professionIds = [];
                foreach ($professions as $profession) {
                    $profession = trim($profession);
                    if (empty($profession)) {
                        continue;
                    }
                    $profession = explode('|', $profession)[1];
                    $professionIds[] = $this->companySpecialisationsMapping[$profession]->id ?? null;
                }
                $profile->companySpecialisations()->sync($professionIds);
            }

            $office->save();

            // Projects
            $sourceProjects = $this->connection->table('object_query_52')
                ->where('projectOwnerId', $sourceMember->oo_id)
                ->get();
            foreach ($sourceProjects as $sourceProject) {
                $project = new Project();
                $project->name = $sourceProject->projectTitle ? mb_substr($sourceProject->projectTitle, 0, 255) : '';
                $project->content = $this->toRichTextDescription($sourceProject->projectDescription);
                $project->companyProfile()->associate($profile);
                $project->user()->associate($user);
                $project->project_date = $sourceProject->projectDate ? Carbon::createFromTimestamp($sourceProject->projectDate) : null;
                $project->save();

                if ($sourceProject->projectImages) {
                    $imageIds = explode(',', $sourceProject->projectImages);
                    $position = 0;
                    foreach ($imageIds as $imageId) {
                        if (!$imageId) {
                            continue;
                        }
                        try {
                            $imageId = explode('|', $imageId)[1];

                            $importedImage = $this->importImage($imageId, config('path.internal.project_images'));
                            $projectImage = new ProjectImage();
                            $projectImage->image = $importedImage['filename'];
                            $projectImage->image_original = $importedImage['originalFilename'];
                            $projectImage->width = $importedImage['width'];
                            $projectImage->height = $importedImage['height'];
                            $projectImage->position = $position++;
                            $projectImage->project()->associate($project);
                            $projectImage->save();
                        } catch (\Exception $e) {
                            Log::error("Failed to import project image with ID $imageId for project ID " . $sourceProject->oo_id . ": " . $e->getMessage());
                        }
                    }
                }
            }
        }

        if ($sourceProfile) {
            $profile->save();
        }

        $account->save();
        return $account;
    }

    protected function createSubscription($account, $sourceMember)
    {
        // $targetSubscriptionPlanId = self::SUBSCRIPTION_TYPE_MAPPING[$sourceMember->abotyp__id] ?? null;

        $targetSubscriptionPlanId = $this->subscriptionsMapping[$sourceMember->abotyp__id]->id ?? null;

        if ($sourceMember->abotyp__id == 69) {
            return;
        }

        if (!$targetSubscriptionPlanId) {
            Log::warning("No subscription plan mapping found for abo type ID " . $sourceMember->abotyp__id . " for member ID " . $sourceMember->oo_id);
            return;
        }
        $subscription = new Subscription();

        if (!$sourceMember->aboStartDate) {
            Log::warning("Unable to create subscription for imported member " . $sourceMember->oo_id . " due to missing start date.");
            return;
        }

        $subscription->start_date = Carbon::createFromTimestamp($sourceMember->aboStartDate);
        $subscription->subscription_plan_id = $targetSubscriptionPlanId;
        $subscription->account_id = $account->id;
        $subscription->seats = 3; // default value, as we don't have this information in Pimcore
        $subscription->price = 0;

        if ($sourceMember->abotyp__id == 79867) {
            $subscription->is_trial = true;
            $subscription->trial_end_date = Carbon::createFromTimestamp($sourceMember->aboEndDate);
            $subscription->end_date = Carbon::createFromTimestamp($sourceMember->aboEndDate)->addDays(364);
        } else if ($sourceMember->aboEndDate) {
            $subscription->end_date = Carbon::createFromTimestamp($sourceMember->aboEndDate);
        } else {
            $subscription->end_date = $this->getNextSubscriptionAnniversary($sourceMember->aboStartDate);
        }

        $subscription->status = Subscription::STATUS_ACTIVE; // TODO: check if we can set it to active directly
        $subscription->account()->associate($account);
        $subscription->save();
    }

    protected function createSavedSearch(User $user, Object $filterData, bool $isNewsletter): SearchProfile
    {
        $profileSearch = new SearchProfile();

        $profileSearch->name = $isNewsletter ? 'Newsletter' : 'Saved Filter';
        $profileSearch->type = SearchProfile::TYPE_COMPETITIONS;
        $profileSearch->email_updates = $filterData->newsletterActive ? true : false;

        $baseFieldname = $isNewsletter ? 'newsletter' : 'filter';
        $announcementTypes = $filterData->{$baseFieldname . 'Announcement'};

        $filters = [];

        if ($announcementTypes) {
            $announcementTypes = $announcementTypes ? explode(',', $announcementTypes) : [];
            $announcementTypes = array_map(fn($item) => explode('|', $item)[1] ?? null, $announcementTypes);
            $announcementTypes = array_filter($announcementTypes);

            $procedureTypesIds = array_map(function ($announcementType) {
                return $this->procedureTypeMapping[$announcementType]['id'] ?? null;
            }, $announcementTypes);
            $procedureTypesIds = array_filter($procedureTypesIds);

            $filters['typeOfAnnouncement'] = array_values($procedureTypesIds);
        }

        $profileSearch->filters = $filters;
        $profileSearch->user()->associate($user);
        $profileSearch->save();

        return $profileSearch;
    }

    protected function getNextSubscriptionAnniversary(int $startTimestamp): Carbon
    {
        $startDate = Carbon::createFromTimestamp($startTimestamp);
        $now = Carbon::now($startDate->getTimezone());

        $year = $now->year;
        $candidate = $this->createDateForYearWithStartMonthDay($startDate, $year);

        if ($candidate->lessThanOrEqualTo($now)) {
            $candidate = $this->createDateForYearWithStartMonthDay($startDate, $year + 1);
        }

        return $candidate->copy()->subDay();
    }

    protected function createDateForYearWithStartMonthDay(Carbon $startDate, int $year): Carbon
    {
        $daysInMonth = Carbon::create($year, $startDate->month, 1, 0, 0, 0, $startDate->getTimezone())->daysInMonth;
        $day = min($startDate->day, $daysInMonth);

        return Carbon::create(
            $year,
            $startDate->month,
            $day,
            $startDate->hour,
            $startDate->minute,
            $startDate->second,
            $startDate->getTimezone()
        );
    }

    protected function toRichTextDescription(?string $description): ?string
    {
        if ($description === null || trim($description) === '') {
            return null;
        }

        $escaped = e($description);

        $withStrong = preg_replace('/__([^_]+)__|_([^_]+)_/u', '<strong>$1$2</strong>', $escaped);
        $lines = preg_split('/\r\n|\r|\n/', $withStrong);

        return implode('', array_map(fn($line) => '<p>' . ($line ? $line : '<br>') . '</p>', $lines));
    }

    protected function importImage($assetId, $targetFolder)
    {
        $asset = $this->connection->table('assets')->where('id', $assetId)->first();

        if (!$asset) {
            Log::warning('Image import -> Asset not found for ID ' . $assetId);
            return null;
        }

        $filename = $asset->filename;
        $extension = pathinfo($filename, PATHINFO_EXTENSION);
        $targetFilename = Str::random(16) . ".$extension";

        if (
            !in_array($extension, config('backoffice.media.allowedExts.image')) &&
            !in_array($extension, config('backoffice.media.allowedExts.document'))
        ) {
            Log::warning('File type (' . $extension . ') not allowed for ID ' . $assetId);
            return null;
        }

        $client = new Client();
        // Add the cookie to the request
        $response = $client->get("https://www.wettbewerbe-aktuell.de/admin/asset/download/id/$assetId", [
            'allow_redirects' => false,
            'headers' => [
                'Cookie' => "pimcore_admin_sid=$this->pimcoreAdminSid",
            ],
        ]);

        if ($response->getStatusCode() !== 200) {
            Log::error('Failed to download asset with ID ' . $assetId . ': ' . $response->getReasonPhrase());
            throw new \Exception('Failed to download asset with ID ' . $assetId . ': ' . $response->getReasonPhrase());
        }
        $body = $response->getBody();

        Storage::put($targetFolder . '/' . $targetFilename, $body->getContents());

        $settings = unserialize($asset->customSettings);

        return [
            'filename' => $targetFilename,
            'originalFilename' => $asset->filename,
            'width' => isset($settings['imageWidth']) ? $settings['imageWidth'] : null,
            'height' => isset($settings['imageHeight']) ? $settings['imageHeight'] : null,
        ];
    }
}