<?php

require_once(__DIR__ . "/../../config/config.php");

define('FRONKDB_SQLDEBUG', false);
error_reporting(E_ALL & ~(E_NOTICE | E_STRICT | E_DEPRECATED));

require_once(LIBDIR . "/mvcfronk/mfRouter/mfRouter.php");
require_once(LIBDIR . "/mvcfronk/mfBase/mfBaseModel.php");
require_once(LIBDIR . "/mvcfronk/mfBase/mfBaseController.php");

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

define("INTERNAL_USER_ID", 1);
$me = new User(INTERNAL_USER_ID);

$terminations = TerminationModel::getAll();
$Terminations = [];
foreach ($terminations as $termination) {
    if (strpos($termination->code, '8354-') !== false) {
        $Terminations[$termination->code] = $termination->id;
    }
}

$Workflowvalues = [];
foreach (WorkflowvalueModel::getAll() as $workflowvalue) {
    $Workflowvalues[$workflowvalue->object_id][$workflowvalue->item_id] = $workflowvalue;
}

function buildBranchContext(Spreadsheet $book, array $parentCtx = []): array
{
    $ctx = [];
    foreach ($book->getAllSheets() as $sheet) {
        $g1 = $sheet->getCell('G1')->getValue() ?? '';
        [, $rack] = strpos($g1, ':') ? explode(':', $g1, 2) : ['', ''];
        $a1 = $sheet->getCell('A1')->getValue() ?? '';
        $currentCable = strpos($a1, ':') ? trim(explode(':', $a1, 2)[1]) : '';
        $d1 = $sheet->getCell('D1')->getValue() ?? '';
        if (strpos($d1, ':')) {
            [, $v] = explode(':', $d1, 2);
            list($ct, $fibercount) = explode('-', trim($v), 2);
            [, $cabletype] = explode('x', trim($ct), 2);
        } else {
            $cabletype = '';
            $fibercount = 0;
        }

        foreach ($sheet->getRowIterator(4) as $row) {
            $i = $row->getRowIndex();
            if ($sheet->getCell("L{$i}")->getValue() !== 'Abzweigkabel') continue;


            $src = trim($sheet->getCell("M{$i}")->getValue());
            $srcparent = trim($sheet->getCell("B{$i}")->getValue());
            $customerEndType = trim($sheet->getCell("V{$i}")->getValue() ?? '');
            $customerCable = trim($sheet->getCell("W{$i}")->getValue() ?? '');
            $customerFiber = trim($sheet->getCell("X{$i}")->getValue() ?? '');

            $dest = trim($sheet->getCell("O{$i}")->getValue());
            $heVal = trim($sheet->getCell("I{$i}")->getValue() ?? '');
            $pVal = (int)trim($sheet->getCell("A{$i}")->getValue() ?? '0');
            $fVal = (int)ltrim(trim($sheet->getCell("B{$i}")->getValue() ?? ''), ':');

            $parent = $parentCtx[$currentCable][$srcparent] ?? [];

            if (!$parent) {
                if (!isset($ctx[$src][$dest])) {
                    $ctx[$src][$dest] = [
                        'he' => $heVal ?: ($parent['he'] ?? ''),
                        'rack' => trim($rack) ?: ($parent['rack'] ?? ''),
                        'cabletype' => $cabletype ?: ($parent['cabletype'] ?? ''),
                        'fibercount' => $fibercount ?: ($parent['fibercount'] ?? 0),
                        'plugMin' => $pVal,
                        'plugMax' => $pVal,
                        'fiberMin' => $fVal,
                        'fiberMax' => $fVal,
                        'customerEndType' => $customerEndType ?: ($parent['customerEndType'] ?? ''),
                        'customerCable' => $customerCable ?: ($parent['customerCable'] ?? ''),
                        'customerFiber' => $customerFiber ?: ($parent['customerfiber'] ?? ''),
                        'cable' => $currentCable ?: ($parent['cable'] ?? ''),

                        'main_cable' => $parent['main_cable'] ?? $currentCable,
                        'main_he' => $parent['main_he'] ?? $heVal,
                        'main_rack' => $parent['main_rack'] ?? trim($rack),
                        'main_cabletype' => $parent['main_cabletype'] ?? $cabletype,
                        'main_fibercount' => $parent['main_fibercount'] ?? $fibercount,
                    ];
                } else {
                    $e = &$ctx[$src][$dest];
                    $e['plugMin'] = min($e['plugMin'], $pVal);
                    $e['plugMax'] = max($e['plugMax'], $pVal);
                    $e['fiberMin'] = min($e['fiberMin'], $fVal);
                    $e['fiberMax'] = max($e['fiberMax'], $fVal);
                }
            } else {

                $pVal = $parent['plugMin'];
                $fVal = $parent['fiberMin'];
                if (!isset($ctx[$src][$dest])) {
                    $ctx[$src][$dest] = [
                        'he' => ($parent['he'] ?? ''),
                        'rack' => ($parent['rack'] ?? ''),
                        'cabletype' => ($parent['cabletype'] ?? ''),
                        'fibercount' => ($parent['fibercount'] ?? 0),
                        'plugMin' => $pVal,
                        'plugMax' => $pVal,
                        'fiberMin' => $fVal,
                        'fiberMax' => $fVal,
                        'customerEndType' => ($customerEndType ?? ''),
                        'customerCable' => ($customerCable ?? ''),
                        'customerFiber' => ($customerFiber ?? ''),
                        'cable' => ($parent['cable'] ?? ''),

                        'main_cable' => $parent['main_cable'] ?? $currentCable,
                        'main_he' => $parent['main_he'] ?? $heVal,
                        'main_rack' => $parent['main_rack'] ?? trim($rack),
                        'main_cabletype' => $parent['main_cabletype'] ?? $cabletype,
                        'main_fibercount' => $parent['main_fibercount'] ?? $fibercount,
                    ];
                } else {
                    $e = &$ctx[$src][$dest];
                    $e['plugMin'] = min($e['plugMin'], $pVal);
                    $e['plugMax'] = max($e['plugMax'], $pVal);
                    $e['fiberMin'] = min($e['fiberMin'], $fVal);
                    $e['fiberMax'] = max($e['fiberMax'], $fVal);
                }
            }

        }
    }
    return $ctx;
}

function parseSpreadsheet(Spreadsheet $book, array $Terminations, array $Workflowvalues, string $mode = 'main', array $context = [], string $branchName = '')
{
    foreach ($book->getAllSheets() as $sheet) {
        if ($mode === 'branch') {
            $data = [];
            foreach ($sheet->getRowIterator() as $row) {
                $i = $row->getRowIndex();
                if ($i === 1) {
                    $a = $sheet->getCell("A1")->getValue() ?? '';
                    if (strpos($a, ':') !== false) {
                        [, $v] = explode(':', $a, 2);
                        $currentCable = trim($v);
                    }
                    continue;
                }
                if ($i <= 3) continue;
                $oanid = trim($sheet->getCell("G{$i}")->getValue() ?? '');
                $fiberplug = trim($sheet->getCell("B{$i}")->getValue() ?? '');
                if (!$oanid || strpos($oanid, 'AT-') === false) continue;
                if (!isset($data[$oanid])) {
                    $data[$oanid] = [
                        'stecker' => trim($sheet->getCell("A{$i}")->getValue() ?? ''),
                        'bundel' => trim($sheet->getCell("C{$i}")->getValue() ?? ''),
                        'customerEndType' => trim($sheet->getCell("V{$i}")->getValue() ?? ''),
                        'customerCable' => trim($sheet->getCell("W{$i}")->getValue() ?? ''),
                        'customerFiber' => trim($sheet->getCell("X{$i}")->getValue() ?? ''),
                        'fiberplug' => $fiberplug,
                        'fibercount' => 0,

                        'main_he' => $context[$fiberplug]['main_he'] ?? '',
                        'main_rack' => $context[$fiberplug]['main_rack'] ?? '',
                        'main_cabletype' => $context[$fiberplug]['main_cabletype'] ?? '',
                        'main_fibercount' => $context[$fiberplug]['main_fibercount'] ?? 0,
                    ];
                } else {
                    $data[$oanid]['fibercount']++;
                }
            }
            foreach ($data as $oanid => $info) {
                $clearFiberplug = str_replace(':', '', $info['fiberplug']);
                $clearFiberplug = $clearFiberplug + $info['fibercount'];
                $clearFiberplug = ":" . $clearFiberplug;
                $entry = $context[$info['fiberplug']] ?? [];
                $rack = $entry['rack'] ?? $info['main_rack'];
                $he = $entry['he'] ?? $info['main_he'];
                $cabletype = $entry['cabletype'] ?? $info['main_cabletype'];
                $fibercount = $entry['fibercount'] ?? $info['main_fibercount'];
                $fibercount = trim($fibercount);
                $cable = $entry['cable'] ?? $info['cable'];

                $plugMin = $entry['plugMin'] ?? null;
                $plugMax = $context[$clearFiberplug]['plugMax'] ?? null;
                $fiberMin = $entry['fiberMin'] ?? null;
                $fiberMax = $context[$clearFiberplug]['fiberMax'] ?? null;
                $pr = ($plugMin !== null && $plugMax !== null)
                    ? ($plugMin === $plugMax ? $plugMin : "{$plugMin}-{$plugMax}")
                    : '';
                $fr = ($fiberMin !== null && $fiberMax !== null)
                    ? ($fiberMin === $fiberMax ? $fiberMin : "{$fiberMin}-{$fiberMax}")
                    : '';

                echo implode("\t", [
                    $currentCable,
                    "oanid:{$oanid}",
                    "{$cable} ({$fibercount})",
                    $cabletype,
                    "rack:{$rack}",
                    $he,
                    $pr,
                    $fr,
                    $info['customerEndType'],
                    $info['customerCable'],
                    $info['customerFiber'],
                ]);

                $termKey = "{$oanid}.001";
                if (isset($Terminations[$termKey])) {
                    $objId = $Terminations[$termKey];
                    echo " OK {$objId}";
                    $importarray = [
                        20 => '80',
                        21 => $rack,
                        22 => $he,
                        24 => '0',
                        25 => $pr,
                        26 => trim($info['customerEndType']),
                        27 => "{$cable} ({$fibercount})",
                        28 => $fr,
                        29 => trim($info['customerCable']),
                        30 => trim($info['customerFiber']),
                        57 => $cabletype
                    ];
                    foreach ($importarray as $key => $import) {
                        if (!isset($Workflowvalues[$objId][$key])) {
                            $data = [
                                'object_id' => $objId,
                                'item_id' => $key,
                                'value_string' => $import,
                                'create_by' => INTERNAL_USER_ID,
                                'edit_by' => INTERNAL_USER_ID,
                                'changed_by' => INTERNAL_USER_ID,
                                'changed' => time(),
                                'create' => time(),
                            ];
                            $wfValue = WorkflowvalueModel::create($data);
                            $wfValue->save();
                        } else if (isset($Workflowvalues[$objId][$key]) && $Workflowvalues[$objId][$key]->value_string != $import) {
                            $Workflowvalues[$objId][$key]->value_string = $import;
                            $Workflowvalues[$objId][$key]->changed = time();
                            $Workflowvalues[$objId][$key]->changed_by = INTERNAL_USER_ID;
                            $Workflowvalues[$objId][$key]->edit_by = INTERNAL_USER_ID;
                            $Workflowvalues[$objId][$key]->save();
                            echo " UPDATED";
                        }
                    }
                } else {
                    echo "\t{$termKey}\t";
                }
                echo "\n";
            }
            continue;
        }

        $oldOan = '';
        $oldRack = '';
        $oldHe = '';
        $oldCable = '';
        $currentCable = '';
        $cabletype = '';
        $plugMin = $plugMax = null;
        $fibMin = $fibMax = null;

        foreach ($sheet->getRowIterator() as $row) {
            $i = $row->getRowIndex();
            if ($i === 1) {
                $a = $sheet->getCell("A1")->getValue() ?? '';
                if (strpos($a, ':') !== false) {
                    [, $currentCable] = explode(':', $a, 2);
                    $currentCable = trim($currentCable);
                }
                $g = $sheet->getCell("G1")->getValue() ?? '';
                if (strpos($g, ':') !== false) {
                    [, $oldRack] = explode(':', $g, 2);
                    $oldRack = trim($oldRack);
                }
                $e = $sheet->getCell("D1")->getValue() ?? '';
                if (strpos($e, ':') !== false) {
                    [, $v] = explode(':', $e, 2);
                    list($ct, $fibercount) = explode('-', trim($v), 2);
                    [, $cabletype] = explode('x', trim($ct), 2);
                    $fibercount = trim($fibercount);
                }
                continue;
            }
            if ($i <= 3) continue;

            $oanid = trim($sheet->getCell("G{$i}")->getValue() ?? '');
            if (!$oanid || strpos($oanid, 'AT-') === false) continue;
            $he = trim($sheet->getCell("I{$i}")->getValue() ?? '');
            $plug = trim($sheet->getCell("A{$i}")->getValue() ?? '');
            $fiber = ltrim(trim($sheet->getCell("B{$i}")->getValue() ?? ''), ':');
            $customerEndType = trim($sheet->getCell("V{$i}")->getValue() ?? '');
            $customerCable = trim($sheet->getCell("W{$i}")->getValue() ?? '');
            $customerFiber = trim($sheet->getCell("X{$i}")->getValue() ?? '');

            $changed = ($oldOan !== $oanid);
            if ($changed) {
                if ($oldOan && $oldCable) {
                    $pr = $plugMin !== null
                        ? ($plugMin === $plugMax ? $plugMin : "$plugMin-$plugMax")
                        : '';
                    $fr = $fibMin !== null
                        ? ($fibMin === $fibMax ? $fibMin : "$fibMin-$fibMax")
                        : '';
                    echo implode("\t", [
                        $oldCable,
                        "oanid:{$oldOan}",
                        "{$oldCable} ({$fibercount})",
                        $cabletype,
                        "rack:{$oldRack}",
                        $oldHe,
                        $pr,
                        $fr,
                        $oldcustomerEndType,
                        $oldcustomerCable,
                        $oldcustomerFiber
                    ]);
                    $termKey = "{$oldOan}.001";
                    if (isset($Terminations[$termKey])) {
                        $objId = $Terminations[$termKey];
                        echo " OK {$objId}";
                        $importarray = [
                            20 => '80',
                            21 => $oldRack,
                            22 => $oldHe,
                            24 => '0',
                            25 => $pr,
                            26 => trim($oldcustomerEndType),
                            27 => "{$oldCable} ({$fibercount})",
                            28 => $fr,
                            29 => trim($oldcustomerCable),
                            30 => trim($oldcustomerFiber),
                            57 => $cabletype
                        ];
                        foreach ($importarray as $key => $import) {
                            if (!isset($Workflowvalues[$objId][$key])) {
                                $data = [
                                    'object_id' => $objId,
                                    'item_id' => $key,
                                    'value_string' => $import,
                                    'create_by' => INTERNAL_USER_ID,
                                    'edit_by' => INTERNAL_USER_ID,
                                    'changed_by' => INTERNAL_USER_ID,
                                    'changed' => time(),
                                    'create' => time(),
                                ];
                                $wfValue = WorkflowvalueModel::create($data);
                                $wfValue->save();
                            } else if (isset($Workflowvalues[$objId][$key]) && $Workflowvalues[$objId][$key]->value_string != $import) {
                                $Workflowvalues[$objId][$key]->value_string = $import;
                                $Workflowvalues[$objId][$key]->changed = time();
                                $Workflowvalues[$objId][$key]->changed_by = INTERNAL_USER_ID;
                                $Workflowvalues[$objId][$key]->edit_by = INTERNAL_USER_ID;
                                $Workflowvalues[$objId][$key]->save();
                                echo " UPDATED";
                            }
                        }
                    } else {
                        echo "\t{$termKey}\t";
                    }
                    echo "\n";
                }
                $plugMin = $plug ? (int)$plug : null;
                $plugMax = $plug ? (int)$plug : null;
                $fibMin = $fiber ? (int)$fiber : null;
                $fibMax = $fiber ? (int)$fiber : null;
                $oldHe = $he ?: $oldHe;
                $oldOan = $oanid;
                $oldCable = $currentCable ?: $oldCable;
                $oldcustomerEndType = $customerEndType ?: '';
                $oldcustomerCable = $customerCable ?: '';
                $oldcustomerFiber = $customerFiber ?: '';
                continue;
            }
            if ($plug) {
                $p = (int)$plug;
                $plugMin = $plugMin === null ? $p : min($plugMin, $p);
                $plugMax = $plugMax === null ? $p : max($plugMax, $p);
            }
            if ($fiber) {
                $f = (int)$fiber;
                $fibMin = $fibMin === null ? $f : min($fibMin, $f);
                $fibMax = $fibMax === null ? $f : max($fibMax, $f);
            }
        }

        if ($oldOan && $oldCable) {
            $pr = $plugMin !== null
                ? ($plugMin === $plugMax ? $plugMin : "$plugMin-$plugMax")
                : '';
            $fr = $fibMin !== null
                ? ($fibMin === $fibMax ? $fibMin : "$fibMin-$fibMax")
                : '';
            echo implode("\t", [
                $oldCable,
                "oanid:{$oldOan}",
                "{$oldCable} ({$fibercount})",
                $cabletype,
                "rack:{$oldRack}",
                $oldHe,
                $pr,
                $fr,
                $oldcustomerEndType,
                $oldcustomerCable,
                $oldcustomerFiber
            ]);
            $termKey = "{$oldOan}.001";
            if (isset($Terminations[$termKey])) {
                $objId = $Terminations[$termKey];
                echo " OK {$objId}";
                $importarray = [
                    20 => '80',
                    21 => $oldRack,
                    22 => $oldHe,
                    24 => '0',
                    25 => $pr,
                    26 => trim($oldcustomerEndType),
                    27 => "{$oldCable} ({$fibercount})",
                    28 => $fr,
                    29 => trim($oldcustomerCable),
                    30 => trim($oldcustomerFiber),
                    57 => $cabletype
                ];
                foreach ($importarray as $key => $import) {
                    if (!isset($Workflowvalues[$objId][$key])) {
                        $data = [
                            'object_id' => $objId,
                            'item_id' => $key,
                            'value_string' => $import,
                            'create_by' => INTERNAL_USER_ID,
                            'edit_by' => INTERNAL_USER_ID,
                            'changed_by' => INTERNAL_USER_ID,
                            'changed' => time(),
                            'create' => time(),
                        ];
                        $wfValue = WorkflowvalueModel::create($data);
                        $wfValue->save();
                    }     else if  (isset($Workflowvalues[$objId][$key]) && $Workflowvalues[$objId][$key]->value_string != $import) {
                        $Workflowvalues[$objId][$key]->value_string = $import;
                        $Workflowvalues[$objId][$key]->changed = time();
                        $Workflowvalues[$objId][$key]->changed_by = INTERNAL_USER_ID;
                        $Workflowvalues[$objId][$key]->edit_by = INTERNAL_USER_ID;
                        $Workflowvalues[$objId][$key]->save();
                        echo " UPDATED";
                    }
                }
            } else {
                echo "\t{$termKey}\t";
            }
            echo "\n";
        }
    }
}


function processBranchFiles(array $context, array $Terminations, array $Workflowvalues)
{
    foreach ($context as $src => $map) {
        foreach (glob(__DIR__ . "/xlsx/{$src}_*.xlsx") as $file) {
            $ss = IOFactory::load($file);

            parseSpreadsheet($ss, $Terminations, $Workflowvalues, 'branch', $map, $src);

            $subCtx = buildBranchContext($ss, $context);

            if (!empty($subCtx)) {
                processBranchFiles($subCtx, $Terminations, $Workflowvalues);
            }
        }
    }
}

$mainfiles = [
    'xlsx/K001_LWL_24x12_288f.xlsx',
    'xlsx/K002_LWL_8x24_192f.xlsx',
    'xlsx/K007_LWL_8x12_96f.xlsx',
    'xlsx/K008_LWL_2x12_24f.xlsx',
    'xlsx/K009_LWL_2x12_24f.xlsx',
];

foreach ($mainfiles as $mainFile) {
    $mainSS = IOFactory::load($mainFile);
    $mainCtx = buildBranchContext($mainSS, []);
    parseSpreadsheet($mainSS, $Terminations, $Workflowvalues, 'main');
    processBranchFiles($mainCtx, $Terminations, $Workflowvalues);
}