<?php

namespace App\Services\Whatsapp;

use App\Models\ParentModel;
use App\Models\User;
use Illuminate\Support\Facades\DB;

class WhatsappInviteBundleService
{
    public function bundleForParent(int $pickedId, array $linkBySection, string $schoolYear, string $semester): ?array
    {
        $isPrimary = User::query()->whereKey($pickedId)->exists();
        $primaryId = $pickedId;

        if (! $isPrimary) {
            $map = ParentModel::query()
                ->select('firstparent_id', 'id')
                ->where('id', $pickedId)
                ->first();
            if (! $map || ! $map->firstparent_id) {
                return null;
            }
            $primaryId = (int) $map->firstparent_id;
        }

        $primary = User::query()
            ->select('id', 'firstname', 'lastname', 'email')
            ->whereKey($primaryId)
            ->first();
        if (! $primary) {
            return null;
        }

        $secondary = ParentModel::query()
            ->select('id', 'secondparent_firstname', 'secondparent_lastname', 'secondparent_email')
            ->where('firstparent_id', $primaryId)
            ->where('id', '>', 0)
            ->first();

        $sectionRows = DB::table('student_class as sc')
            ->select(DB::raw('DISTINCT sc.class_section_id'))
            ->join('students as s', 's.id', '=', 'sc.student_id')
            ->where('sc.school_year', $schoolYear)
            ->where('s.parent_id', $primaryId)
            ->get()
            ->all();

        $sections = [];
        $linksSet = [];
        foreach ($sectionRows as $row) {
            $cid = (int) ($row->class_section_id ?? 0);
            if (! $cid) {
                continue;
            }
            $link = (string) ($linkBySection[$cid]['invite_link'] ?? '');
            if ($link === '') {
                continue;
            }

            $sections[] = [
                'class_section_id' => $cid,
                'class_section_name' => (string) ($linkBySection[$cid]['class_section_name'] ?? ('Section '.$cid)),
                'invite_link' => $link,
            ];
            $linksSet[$link] = true;
        }

        if (empty($sections)) {
            return null;
        }

        $emails = [];
        if (! empty($primary->email)) {
            $emails[$primary->email] = true;
        }
        if (! empty($secondary?->secondparent_email)) {
            $emails[$secondary->secondparent_email] = true;
        }
        if (empty($emails)) {
            return null;
        }

        return [
            'parent' => [
                'id' => (int) $primary->id,
                'firstname' => (string) ($primary->firstname ?? ''),
                'lastname' => (string) ($primary->lastname ?? ''),
                'email' => (string) ($primary->email ?? ''),
                'source' => 'users',
            ],
            'secondary_parents' => $secondary && ! empty($secondary->id) ? [[
                'id' => (int) $secondary->id,
                'firstname' => (string) ($secondary->secondparent_firstname ?? ''),
                'lastname' => (string) ($secondary->secondparent_lastname ?? ''),
                'email' => (string) ($secondary->secondparent_email ?? ''),
                'source' => 'parents',
            ]] : [],
            'sections' => $sections,
            'links' => array_keys($linksSet),
            'emails' => array_keys($emails),
            'school_year' => $schoolYear,
            'semester' => $semester,
        ];
    }

    public function bundlesForClass(int $classSectionId, array $linkBySection, string $schoolYear, string $semester): array
    {
        $link = (string) ($linkBySection[$classSectionId]['invite_link'] ?? '');
        if ($link === '') {
            return [];
        }

        $studentRows = DB::table('student_class')
            ->select('student_id')
            ->where('school_year', $schoolYear)
            ->when($semester !== '', fn ($q) => $q->where('semester', $semester))
            ->where('class_section_id', $classSectionId)
            ->get()
            ->all();

        if (empty($studentRows)) {
            return [];
        }

        $studentIds = array_values(array_unique(array_map(fn ($r) => (int) ($r->student_id ?? 0), $studentRows)));
        if (empty($studentIds)) {
            return [];
        }

        $parentRows = DB::table('students')
            ->select('id', 'parent_id')
            ->whereIn('id', $studentIds)
            ->get()
            ->all();

        $parentIds = [];
        foreach ($parentRows as $row) {
            $pid = (int) ($row->parent_id ?? 0);
            if ($pid) {
                $parentIds[$pid] = true;
            }
        }
        $parentIds = array_keys($parentIds);
        if (empty($parentIds)) {
            return [];
        }

        $primaryRows = DB::table('users')
            ->select('id', 'firstname', 'lastname', 'email')
            ->whereIn('id', $parentIds)
            ->get()
            ->all();

        $profiles = [];
        foreach ($primaryRows as $row) {
            $uid = (int) ($row->id ?? 0);
            $profiles[$uid] = [
                'id' => $uid,
                'firstname' => (string) ($row->firstname ?? ''),
                'lastname' => (string) ($row->lastname ?? ''),
                'email' => (string) ($row->email ?? ''),
                'source' => 'users',
            ];
        }

        $secondaryRows = DB::table('parents')
            ->select('firstparent_id', 'id', 'secondparent_firstname', 'secondparent_lastname', 'secondparent_email')
            ->where('id', '>', 0)
            ->whereIn('firstparent_id', $parentIds)
            ->get()
            ->all();

        $secondaryByPrimary = [];
        foreach ($secondaryRows as $row) {
            $fp = (int) ($row->firstparent_id ?? 0);
            $secondaryByPrimary[$fp] = [
                'id' => (int) ($row->id ?? 0),
                'firstname' => (string) ($row->secondparent_firstname ?? ''),
                'lastname' => (string) ($row->secondparent_lastname ?? ''),
                'email' => (string) ($row->secondparent_email ?? ''),
                'source' => 'parents',
            ];
        }

        $bundles = [];
        foreach ($parentIds as $pid) {
            $profile = $profiles[$pid] ?? null;
            if (! $profile) {
                continue;
            }

            $emails = [];
            if (! empty($profile['email'])) {
                $emails[$profile['email']] = true;
            }
            if (! empty($secondaryByPrimary[$pid]['email'])) {
                $emails[$secondaryByPrimary[$pid]['email']] = true;
            }
            if (empty($emails)) {
                continue;
            }

            $bundles[] = [
                'parent' => $profile,
                'secondary_parents' => ! empty($secondaryByPrimary[$pid]['id']) ? [$secondaryByPrimary[$pid]] : [],
                'sections' => [[
                    'class_section_id' => $classSectionId,
                    'class_section_name' => (string) ($linkBySection[$classSectionId]['class_section_name'] ?? ('Section '.$classSectionId)),
                    'invite_link' => $link,
                ]],
                'links' => [$link],
                'emails' => array_keys($emails),
                'school_year' => $schoolYear,
                'semester' => $semester,
            ];
        }

        return $bundles;
    }

    public function bundlesForAll(array $linkBySection, string $schoolYear, string $semester): array
    {
        $sectionRows = DB::table('student_class')
            ->select(DB::raw('DISTINCT class_section_id'))
            ->where('school_year', $schoolYear)
            ->when($semester !== '', fn ($q) => $q->where('semester', $semester))
            ->orderBy('class_section_id', 'ASC')
            ->get()
            ->all();

        $bundles = [];
        foreach ($sectionRows as $row) {
            $cid = (int) ($row->class_section_id ?? 0);
            if (! $cid) {
                continue;
            }

            $classBundles = $this->bundlesForClass($cid, $linkBySection, $schoolYear, $semester);
            foreach ($classBundles as $bundle) {
                $bundles[] = $bundle;
            }
        }

        return $bundles;
    }

    public function consolidateBundles(array $payloads): array
    {
        $normalizeEmails = static function (array $list): array {
            $out = [];
            foreach ($list as $e) {
                $e = strtolower(trim((string) $e));
                if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) {
                    $out[$e] = true;
                }
            }

            return array_keys($out);
        };

        $mergeUnique = static function (array $a, array $b): array {
            $map = [];
            foreach (array_merge($a, $b) as $v) {
                $k = is_array($v) ? md5(json_encode($v)) : (string) $v;
                $map[$k] = $v;
            }

            return array_values($map);
        };

        $mergeSections = static function (array $a, array $b): array {
            $byKey = [];
            $put = static function (&$byKey, $row) {
                if (is_array($row)) {
                    $id = $row['class_section_id'] ?? null;
                    $name = $row['class_section_name'] ?? null;
                    $key = $id !== null ? ('id:'.(int) $id) : ('name:'.(string) $name);
                    $byKey[$key] = $row;
                } else {
                    $byKey['name:'.(string) $row] = $row;
                }
            };

            foreach ($a as $r) {
                $put($byKey, $r);
            }
            foreach ($b as $r) {
                $put($byKey, $r);
            }

            return array_values($byKey);
        };

        $mergeLinks = static function (array $a, array $b): array {
            $index = [];
            $add = static function (&$index, $item) {
                if (is_array($item)) {
                    $key = isset($item['class_section_id'])
                        ? 'id:'.(int) $item['class_section_id']
                        : ('url:'.(string) ($item['invite_link'] ?? json_encode($item)));
                    $index[$key] = $item;
                } else {
                    $index['url:'.(string) $item] = $item;
                }
            };

            foreach ($a as $it) {
                $add($index, $it);
            }
            foreach ($b as $it) {
                $add($index, $it);
            }

            return array_values($index);
        };

        $grouped = [];
        foreach ($payloads as $bundle) {
            $parent = $bundle['parent'] ?? [];
            $pid = (int) ($parent['id'] ?? 0);
            $primaryEmails = $normalizeEmails((array) ($bundle['emails'] ?? []));
            $groupKey = $pid > 0 ? ('pid:'.$pid) : ('mail:'.implode(',', $primaryEmails));

            if (! isset($grouped[$groupKey])) {
                $grouped[$groupKey] = [
                    'parent' => $parent,
                    'emails' => $primaryEmails,
                    'sections' => (array) ($bundle['sections'] ?? []),
                    'links' => (array) ($bundle['links'] ?? []),
                    'teachers' => (array) ($bundle['teachers'] ?? []),
                    'items' => (array) ($bundle['items'] ?? []),
                    'secondary_parents' => (array) ($bundle['secondary_parents'] ?? []),
                ];

                continue;
            }

            $grouped[$groupKey]['parent'] = ! empty($parent) ? $parent : $grouped[$groupKey]['parent'];
            $grouped[$groupKey]['emails'] = $normalizeEmails(array_merge($grouped[$groupKey]['emails'], $primaryEmails));
            $grouped[$groupKey]['sections'] = $mergeSections($grouped[$groupKey]['sections'], (array) ($bundle['sections'] ?? []));
            $grouped[$groupKey]['links'] = $mergeLinks($grouped[$groupKey]['links'], (array) ($bundle['links'] ?? []));
            $grouped[$groupKey]['teachers'] = $mergeUnique($grouped[$groupKey]['teachers'], (array) ($bundle['teachers'] ?? []));
            $grouped[$groupKey]['items'] = $mergeLinks($grouped[$groupKey]['items'], (array) ($bundle['items'] ?? []));
            $grouped[$groupKey]['secondary_parents'] = $mergeUnique($grouped[$groupKey]['secondary_parents'], (array) ($bundle['secondary_parents'] ?? []));
        }

        return array_values($grouped);
    }
}