<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::transaction(function () {
            $now = now();

            DB::table('company_profiles')->insertUsing(
                [
                    'account_id',
                    'name',
                    'employee_count',
                    'email',
                    'phone',
                    'phone_prefix',
                    'website_url',
                    'company_image',
                    'cover_image',
                    'cover_image_original',
                    'cover_image_size',
                    'description',
                    'instagram_url',
                    'linkedin_url',
                    'facebook_url',
                    'subtitle',
                    'location',
                    'is_active',
                    'created_at',
                    'updated_at',
                ],
                DB::table('accounts')->selectRaw(
                    'id as account_id,
                    COALESCE(profile_title, company) as name,
                    employee_count,
                    email,
                    phone,
                    phone_prefix,
                    website_url,
                    company_image,
                    cover_image,
                    cover_image_original,
                    cover_image_size,
                    description,
                    instagram_url,
                    linkedin_url,
                    facebook_url,
                    profile_subtitle as subtitle,
                    profile_location as location,
                    is_profile_active as is_active,
                    COALESCE(created_at, ?) as created_at,
                    COALESCE(updated_at, ?) as updated_at',
                    [$now, $now]
                )
            );

            DB::table('company_profile_company_specialisations')->insertUsing(
                [
                    'company_profile_id',
                    'company_specialisation_id',
                    'position',
                ],
                DB::table('account_company_specialisations as acs')
                    ->join('company_profiles as cp', 'cp.account_id', '=', 'acs.account_id')
                    ->select([
                        'cp.id as company_profile_id',
                        'acs.company_specialisation_id',
                        'acs.position',
                    ])
            );

            DB::table('job_offers as jo')
                ->join('company_profiles as cp', 'cp.account_id', '=', 'jo.account_id')
                ->whereNull('jo.company_profile_id')
                ->update(['jo.company_profile_id' => DB::raw('cp.id')]);

            DB::table('projects as p')
                ->join('company_profiles as cp', 'cp.account_id', '=', 'p.account_id')
                ->whereNull('p.company_profile_id')
                ->update(['p.company_profile_id' => DB::raw('cp.id')]);

            DB::table('offices as o')
                ->join('company_profiles as cp', 'cp.account_id', '=', 'o.account_id')
                ->whereNull('o.company_profile_id')
                ->update(['o.company_profile_id' => DB::raw('cp.id')]);

            DB::table('result_entities as re')
                ->join('company_profiles as cp', 'cp.account_id', '=', 're.account_id')
                ->whereNull('re.company_profile_id')
                ->update(['re.company_profile_id' => DB::raw('cp.id')]);

            DB::table('coordination_group_members as cgm')
                ->join('company_profiles as cp', 'cp.account_id', '=', 'cgm.account_id')
                ->whereNull('cgm.company_profile_id')
                ->update(['cgm.company_profile_id' => DB::raw('cp.id')]);

            DB::table('executing_party_group_companies as epgc')
                ->join('company_profiles as cp', 'cp.account_id', '=', 'epgc.account_id')
                ->whereNull('epgc.company_profile_id')
                ->update(['epgc.company_profile_id' => DB::raw('cp.id')]);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::transaction(function () {
            DB::table('job_offers as jo')
                ->join('company_profiles as cp', 'cp.id', '=', 'jo.company_profile_id')
                ->whereNull('jo.account_id')
                ->update(['jo.account_id' => DB::raw('cp.account_id')]);

            DB::table('projects as p')
                ->join('company_profiles as cp', 'cp.id', '=', 'p.company_profile_id')
                ->whereNull('p.account_id')
                ->update(['p.account_id' => DB::raw('cp.account_id')]);

            DB::table('offices as o')
                ->join('company_profiles as cp', 'cp.id', '=', 'o.company_profile_id')
                ->whereNull('o.account_id')
                ->update(['o.account_id' => DB::raw('cp.account_id')]);

            DB::table('result_entities as re')
                ->join('company_profiles as cp', 'cp.id', '=', 're.company_profile_id')
                ->whereNull('re.account_id')
                ->update(['re.account_id' => DB::raw('cp.account_id')]);

            DB::table('coordination_group_members as cgm')
                ->join('company_profiles as cp', 'cp.id', '=', 'cgm.company_profile_id')
                ->whereNull('cgm.account_id')
                ->update(['cgm.account_id' => DB::raw('cp.account_id')]);

            DB::table('executing_party_group_companies as epgc')
                ->join('company_profiles as cp', 'cp.id', '=', 'epgc.company_profile_id')
                ->whereNull('epgc.account_id')
                ->update(['epgc.account_id' => DB::raw('cp.account_id')]);

            DB::table('company_profile_company_specialisations')->truncate();
            DB::table('company_profiles')->truncate();
        });
    }
};