<?php

namespace App\Libs\Services;

use App\Libs\Database\Eloquent\Collection\ContentTypeCollection;
use App\Models\Committee;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class CommitteeService {

    public function list(array $searchData): ContentTypeCollection {
        $query = Committee::full()
            ->select('committees.*', 'committee_languages.*', 'website_objects.*')
            ->where('website_objects.status', 'published');

            $userData = Auth::user();

            $hasLegalOpinionsPermission = $userData->member->user_permission_level
                ? $userData->legal_opinion_permission
                : $userData->member->legal_opinions;

            $hasFinanceSubCommitteePermission = $userData->finance_sub_committee_permission;

            if ($userData->member->member_type !== 'noType') {
                $query->where(function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                    $query->whereExists(function ($query) use ($userData) {
                            $query->select(DB::raw(1))
                                ->from('committee_member')
                                ->whereRaw('committee_member.committee_id = committees.id')
                                ->where('committee_member.member_id', $userData->member_id);
                        });

                    if ($userData->member->legal_committee) {
                        $query->orWhere('committees.type', 'legalCommittee');
                    }

                    if ($hasLegalOpinionsPermission) {
                        $query->orWhere('committees.type', 'legalOpinions');
                    }

                    if ($hasFinanceSubCommitteePermission) {
                        $query->orWhere('committees.type', 'financeSubCommittee');
                    }
                });
            } else {
                $query->where('committees.type', 'legalOpinions');
            }

            if ($searchData['workGroupsFlag']) {
                $query->with([
                    'workGroups' => function ($query) {
                        $query->full()->where('status', 'published');
                    },
                ]);
            }

            $query->orderBy('committee_languages.title', 'asc');

        return $query->get();
    }

    public function findBySlug(string $slug): ?Committee {
        $userData = Auth::user();

        $hasLegalOpinionsPermission = $userData->member->user_permission_level
            ? $userData->legal_opinion_permission
            : $userData->member->legal_opinions;

        $hasFinanceSubCommitteePermission = $userData->finance_sub_committee_permission;

        $query = Committee::full()
            ->where('status', 'published')
            ->where('lang_slug', $slug);

            if ($userData->member->member_type !== 'noType') {
                $query->where(function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                    $query->where('committees.type', 'standard');

                    if ($userData->member->legal_committee) {
                        $query->orWhere('committees.type', 'legalCommittee');
                    }

                    if ($hasLegalOpinionsPermission) {
                        $query->orWhere('committees.type', 'legalOpinions');
                    }

                    if ($hasFinanceSubCommitteePermission) {
                        $query->orWhere('committees.type', 'financeSubCommittee');
                    }
                });
            } else {
                $query->where('committees.type', 'legalOpinions');
            }


            $query->with(['documentLists' => function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                $query
                    ->full()
                    ->with(['documents' => function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                        $query->full()
                            ->where('status', 'published')
                            ->where(function ($query) {
                                $query->where('publish_date', '<=', now()->tz('Europe/Berlin'));
                            })->where(function ($query) {
                                $query->whereNull('expiration_date')
                                    ->orWhere('expiration_date', '>', now()->tz('Europe/Berlin'));
                            })
                            ->join('committees', 'committees.id', 'documents.committee_id')
                            ->where(function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                                $query->where('committees.type', 'standard');

                                if ($userData->member->legal_committee) {
                                    $query->orWhere('committees.type', 'legalCommittee');
                                }

                                if ($hasLegalOpinionsPermission) {
                                    $query->orWhere('committees.type', 'legalOpinions');
                                }

                                if ($hasFinanceSubCommitteePermission) {
                                    $query->orWhere('committees.type', 'financeSubCommittee');
                                }
                            })
                            ->orderBy('publish_date', 'desc');
                    }])
                    ->orderBy('pivot_position', 'asc');
            }, 'documentListsArchive' => function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                $query
                    ->full()
                    ->with(['documents' => function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                        $query->full()
                            ->where('status', 'published')
                            ->where(function ($query) {
                                $query->where('publish_date', '<=', now()->tz('Europe/Berlin'));
                            })->where(function ($query) {
                                $query->whereNull('expiration_date')
                                    ->orWhere('expiration_date', '>', now()->tz('Europe/Berlin'));
                            })
                            ->join('committees', 'committees.id', 'documents.committee_id')
                            ->where(function ($query) use ($userData, $hasLegalOpinionsPermission, $hasFinanceSubCommitteePermission) {
                                $query->where('committees.type', 'standard');

                                if ($userData->member->legal_committee) {
                                    $query->orWhere('committees.type', 'legalCommittee');
                                }

                                if ($hasLegalOpinionsPermission) {
                                    $query->orWhere('committees.type', 'legalOpinions');
                                }

                                if ($hasFinanceSubCommitteePermission) {
                                    $query->orWhere('committees.type', 'financeSubCommittee');
                                }
                            })
                            ->orderBy('publish_date', 'desc');
                    }])
                    ->orderBy('pivot_position', 'asc');
            }]);

        return $query->first();
    }

    public function getEmails(array $data): array
    {
        $committeeId = $data['id'];
        $type = $data['type']; // 'to' or 'cc'

        $committee = Committee::find($committeeId);

        if (!$committee) {
            return [];
        }

        return $committee->users()
            ->wherePivot('type', $type)
            ->whereNotNull('email')
            ->where('email', '!=', '')
            ->where('state', 'active')
            ->orderBy('email', 'asc')
            ->pluck('email')
            ->toArray();
    }
}