<?php

namespace App\Helpers;

use DateTime;

class TPADataCompareHelper2
{
    // fields compared by default
    public const COMPARE_FIELDS = ['name', 'dob', 'gender', 'relation'];

    // candidate unique id fields from MediAssist / Excel — prefer these when present
    public const MEDIASSIST_UNIQUE_IDS = [
        'benefMediAssistID',
        'benefSlNoasperInsurer',
        'benefNIAPersonID'
    ];

    /* -------------------------
       Normalization utilities
       ------------------------- */

    public static function parseDateToYmd(?string $date): ?string
    {
        if ($date === null || $date === '') return null;
        $formats = ['d/m/Y H:i:s','d/m/Y','Y-m-d H:i:s','Y-m-d','d-m-Y','d-m-Y H:i:s'];
        foreach ($formats as $f) {
            $dt = DateTime::createFromFormat($f, $date);
            if ($dt !== false) return $dt->format('Y-m-d');
        }
        $ts = strtotime($date);
        return $ts ? date('Y-m-d', $ts) : null;
    }

    public static function normalizeName(?string $name): ?string
    {
        if ($name === null) return null;
        $s = trim($name);
        // $s = preg_replace('/\.+/', ' ', $s);
        // $s = preg_replace('/\s+/', ' ', $s);
        return mb_strtolower($s);
    }

    public static function normalizeEmpId(?string $id): ?string
    {
        if ($id === null) return null;
        $s = trim($id);
        // $s = preg_replace('/[\s\-\/]/', '', $s);
        // $s = preg_replace('/[^\p{L}\p{N}]/u', '', $s);
        return $s === '' ? null : $s;
    }

    public static function normalizeGender(?string $g): ?string
    {
        if ($g === null) return null;
        $u = strtoupper(trim($g));
        if (in_array($u, ['M','MALE'])) return 'M';
        if (in_array($u, ['F','FEMALE'])) return 'F';
        return $u;
    }

    public static function normalizeRelation(?string $r): ?string
    {
        if ($r === null) return null;
        return strtoupper(trim($r));
    }

    /* -------------------------------------
       Normalize rows -> canonical structure
       Returns array of items (not keyed), each item contains:
         - 'pk'        (DB primary key or null)
         - 'emp_id'    (employee code)
         - 'name','dob','gender','relation' (normalized)
         - '__raw'     original row
         - '__rowid'   generated unique row id for API rows (if needed)
       ------------------------------------- */
    public static function normalizeRows(array $rows, array $keyMap, ?string $pkKey = null): array
    {
        $out = [];
        $idx = 0;
        foreach ($rows as $r) {
            $item = [
                'pk' => ($pkKey && isset($r[$pkKey])) ? $r[$pkKey] : null,
                '__raw' => $r,
                '__rowid' => null,
            ];

            // fill canonical fields
            foreach ($keyMap as $canonical => $candidates) {
                $val = null;
                foreach ($candidates as $k) {
                    if (is_array($r) && array_key_exists($k, $r) && $r[$k] !== '') {
                        $val = $r[$k];
                        break;
                    }
                }
                switch ($canonical) {
                    case 'emp_id': $item['emp_id'] = self::normalizeEmpId((string)$val); break;
                    case 'name':   $item['name'] = self::normalizeName((string)$val); break;
                    case 'dob':    $item['dob'] = self::parseDateToYmd((string)$val); break;
                    case 'gender': $item['gender'] = self::normalizeGender((string)$val); break;
                    case 'relation': $item['relation'] = self::normalizeRelation((string)$val); break;
                    default: $item[$canonical] = $val;
                }
            }

            // create a stable row id (for API rows without PK) so they remain unique
            $item['__rowid'] = $item['pk'] ?? ('api_row_' . $idx++);

            $out[] = $item;
        }

        return $out;
    }

    /* -------------------------
       Build grouped index by emp_code:
       returns [ emp_code => [ item1, item2, ... ] ]
       Use '__no_emp' key for rows without emp_id
       ------------------------- */
    public static function groupByEmpCode(array $items): array
    {
        $groups = [];
        foreach ($items as $it) {
            $key = $it['emp_id'] ?? '__no_emp';
            if ($key === null || $key === '') $key = '__no_emp';
            $groups[$key][] = $it;
        }
        return $groups;
    }

    /* -------------------------
       Find a match for a DB row inside a mediassist group (array of items).
       Matching priority:
         1) unique ids present in mediassist (if db raw has any of those too)
         2) exact name + dob
         3) exact name + relation
         4) fuzzy name + dob (threshold %)
       If found, returns index of matched item in $group array; else null.
       Note: $group is passed by reference so matched item can be removed by caller.
       ------------------------- */
    public static function findMatchInGroup(array $dbRow, array $group, int $fuzzyThreshold = 85): ?int
    {
        // 1) match on unique ids if DB row contains any of those (rare)
        // foreach (self::MEDIASSIST_UNIQUE_IDS as $uidField) {
        //     $dbVal = $dbRow['__raw'][$uidField] ?? null;
        //     if ($dbVal) {
        //         foreach ($group as $i => $gItem) {
        //             $gVal = $gItem['__raw'][$uidField] ?? null;
        //             if ($gVal && (string)$gVal === (string)$dbVal) return $i;
        //         }
        //     }
        // }

        // 2) match exact name + dob (both normalized)
        if (!empty($dbRow['name']) && !empty($dbRow['dob'])) {
            foreach ($group as $i => $gItem) {
                if (!empty($gItem['name']) && !empty($gItem['dob'])
                    && $dbRow['name'] === $gItem['name']
                    && $dbRow['dob'] === $gItem['dob']) {
                    return $i;
                }
            }
        }

        // 3) match exact name + relation (if dob missing or unreliable)
        if (!empty($dbRow['name']) && !empty($dbRow['relation'])) {
            foreach ($group as $i => $gItem) {
                if (!empty($gItem['name']) && !empty($gItem['relation'])
                    && $dbRow['name'] === $gItem['name']
                    && $dbRow['relation'] === $gItem['relation']) {
                    return $i;
                }
            }
        }

        // 4) fuzzy name + dob (if dob present)
        if ($fuzzyThreshold > 0 && !empty($dbRow['name'])) {
            foreach ($group as $i => $gItem) {
                if (empty($gItem['name'])) continue;
                $score = 0.0;
                similar_text($dbRow['name'], $gItem['name'], $score); // percent
                $dobMatches = (!empty($dbRow['dob']) && !empty($gItem['dob']) && $dbRow['dob'] === $gItem['dob']);
                // require DOB match OR higher threshold if dob missing
                if ($dobMatches && $score >= max(60, $fuzzyThreshold - 10)) { // slightly relaxed
                    return $i;
                }
                if (!$dobMatches && $score >= $fuzzyThreshold) {
                    // if names are very similar even w/o dob, accept (cautious)
                    return $i;
                }
            }
        }

        // no match
        return null;
    }

    /* -------------------------
       Main comparison routine (grouped matching)
       Options:
         - fuzzy_threshold (default 85)
         - try_global_search_if_emp_missing (bool, default false)
       ------------------------- */
    public static function compareDbVsMediassistGrouped(
        array $dbRows,
        array $mediassistAPIdata,
        string $dbPkKey = 'id',
        array $options = [],
        array $dbKeyMap = null,
        array $mediassistKeyMap = null,
    ): array {


        $dbKeyMap = [
            'emp_id'   => ['emp_code'],
            'name'     => ['name'],
            'dob'      => ['dob'],
            'gender'   => ['gender'],
            'relation' => ['relationship'],
        ];

        $mediassistKeyMap = [
            'emp_id'   => ['priBenefEmpCode'],
            'name'     => ['benefName', 'priBeneficiaryName'],
            'dob'      => ['benefDOB'],
            'gender'   => ['benefSex'],
            'relation' => ['relName'],
        ];


        $fuzzyThreshold = $options['fuzzy_threshold'] ?? 85;
        $tryGlobalSearch = $options['try_global_search_if_emp_missing'] ?? false;

        // normalize to lists
        $normDb = self::normalizeRows($dbRows, $dbKeyMap, $dbPkKey);
        $normApi = self::normalizeRows($mediassistAPIdata, $mediassistKeyMap, null);
        // dd($normApi);
        // group by emp_code
        $dbGroups = self::groupByEmpCode($normDb);
        $apiGroups = self::groupByEmpCode($normApi);

        // prepare report
        $report = [
            'summary' => [
                'total_db' => count($normDb),
                'total_mediassist' => count($normApi),
                'matched' => 0,
                'mismatched' => 0,
                'only_in_db' => 0,
                'only_in_mediassist' => 0,
            ],
            'exact_matches' => [],
            'field_mismatches' => [],
            'only_in_db' => [],
            'only_in_mediassist' => [],
        ];

        // For each db group (emp_code), try match each dbRow with api group entries
        foreach ($dbGroups as $empCode => $dbItems) {
            $apiItems = $apiGroups[$empCode] ?? [];

            // We'll mutate a local copy of apiItems to mark matched items (so we can remove)
            $remainingApi = $apiItems;

            foreach ($dbItems as $dbRow) {
                $matchedIndex = null;
                if (!empty($empCode) && !empty($remainingApi)) {
                    $matchedIndex = self::findMatchInGroup($dbRow, $remainingApi, $fuzzyThreshold);
                }

                // If empCode not present in API and global search allowed, try searching all API rows (costly)
                if ($matchedIndex === null && $tryGlobalSearch && empty($apiItems)) {
                    // attempt global search across all api groups (can be heavy)
                    foreach ($apiGroups as $gKey => $gList) {
                        $mi = self::findMatchInGroup($dbRow, $gList, $fuzzyThreshold);
                        if ($mi !== null) {
                            // found in another group
                            $matchedIndex = $mi;
                            // use that group as remainingApi reference so we can remove matched later
                            $remainingApi = $apiGroups[$gKey];
                            break;
                        }
                    }
                }

                if ($matchedIndex !== null) {
                    // found match — use that api row
                    $apiMatch = $remainingApi[$matchedIndex];

                    // compare fields
                    $diff = [];
                    foreach (self::COMPARE_FIELDS as $f) {
                        $vDb = $dbRow[$f] ?? null;
                        $vApi = $apiMatch[$f] ?? null;
                        if ($vDb !== $vApi) {
                            $diff[$f] = ['db' => $vDb, 'mediassist' => $vApi];
                        }
                    }

                    if (empty($diff)) {
                        $report['exact_matches'][] = [
                            'pk' => $dbRow['pk'],
                            'emp_id' => $dbRow['emp_id'],
                            'db_rowid' => $dbRow['__rowid'],
                            'api_rowid' => $apiMatch['__rowid'],
                        ];
                        $report['summary']['matched']++;
                    } else {
                        $report['field_mismatches'][] = [
                            'pk' => $dbRow['pk'],
                            'emp_id' => $dbRow['emp_id'],
                            'db_rowid' => $dbRow['__rowid'],
                            'api_rowid' => $apiMatch['__rowid'],
                            'differences' => $diff
                        ];
                        $report['summary']['mismatched']++;
                    }

                    // remove matched api item from remainingApi (so it's not matched again)
                    array_splice($remainingApi, $matchedIndex, 1);
                } else {
                    // no match found for this dbRow inside api group => only_in_db
                    $report['only_in_db'][] = [
                        'pk' => $dbRow['pk'],
                        'emp_id' => $dbRow['emp_id'],
                        'db_rowid' => $dbRow['__rowid'],
                        'data' => $dbRow
                    ];
                }
            } // end each dbRow

            // after processing all dbRows in this empCode group, any remainingApi are only_in_mediassist
            foreach ($remainingApi as $leftApi) {
                $report['only_in_mediassist'][] = [
                    'pk' => null,
                    'emp_id' => $leftApi['emp_id'],
                    'api_rowid' => $leftApi['__rowid'],
                    'data' => $leftApi
                ];
            }
        } // end each emp group

        // Also handle any api groups whose emp_code does not exist in DB at all
        foreach ($apiGroups as $empCode => $apiItems) {
            if (isset($dbGroups[$empCode])) continue; // already handled
            foreach ($apiItems as $ai) {
                $report['only_in_mediassist'][] = [
                    'pk' => null,
                    'emp_id' => $ai['emp_id'],
                    'api_rowid' => $ai['__rowid'],
                    'data' => $ai
                ];
            }
        }

        // summary counts
        $report['summary']['only_in_db'] = count($report['only_in_db']);
        $report['summary']['only_in_mediassist'] = count($report['only_in_mediassist']);

        return $report;
    }
}