<?php

class WorkorderMphCompanyController extends WorkorderMphBaseController
{
    protected string $headerTitle = 'Meine MPH Arbeitsaufträge';
    protected bool $createText = false;
    protected array $permissionCheck = ['RMLCompany'];

    protected array $columns = [
        ['key' => 'id', 'text' => 'Auftrags-Nr.', 'table' => ['sortable' => true, 'filter' => 'numberRange']],
        ['key' => 'hausnummerInfo', 'text' => 'Adresse', 'modal' => false, 'table' => ['filter' => 'search', 'sortable' => false]],
        ['key' => 'netzgebietName', 'text' => 'Netzgebiet', 'modal' => false, 'table' => ['filter' => 'select', 'sortable' => false]],
        ['key' => 'rimoFcpName', 'text' => 'FCP', 'modal' => false, 'table' => ['filter' => 'search', 'sortable' => false]],
        ['key' => 'additionalInfo', 'text' => 'Notiz', 'modal' => false, 'table' => ['sortable' => false]],
        ['key' => 'deadlineDate', 'text' => 'Deadline', 'modal' => false, 'table' => ['filter' => 'date', 'sortable' => true]],
        ['key' => 'appointmentDate', 'text' => 'Termin', 'modal' => false, 'table' => ['filter' => 'date', 'sortable' => true]],
    ];
    protected array $additionalJSVariables = ['COMPANY_ID' => '0', 'IS_COMPANY_VIEW' => true];

    protected function prepareCrudConfig()
    {
        $hausnummerInfoColIdx = array_search('hausnummerInfo', array_column($this->columns, 'key'));
        array_splice($this->columns, $hausnummerInfoColIdx + 1, 0, [$this->statusColumn]);

        $company = WorkorderCompanyModel::getFirst(['addressId' => $this->user->address_id]);
        $this->additionalJSVariables['COMPANY_ID'] = $company ? $company->id : 0;

        // Populate netzgebiet filter options for this company's workorders
        $netzgebietColIdx = array_search('netzgebietName', array_column($this->columns, 'key'));
        if ($netzgebietColIdx !== false && $company) {
            $db = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
            $fronkDbName = FRONKDB_DBNAME;

            $sql = "SELECT DISTINCT ng.id, ng.name FROM Netzgebiet ng
                    INNER JOIN Hausnummer hn ON ng.id = hn.netzgebiet_id
                    INNER JOIN `$fronkDbName`.`WorkorderMph` wm ON wm.hausnummerId = hn.id
                    WHERE ng.name IS NOT NULL AND ng.name != '' AND wm.companyId = " . intval($company->id) . "
                    ORDER BY ng.name ASC";
            $result = $db->query($sql);
            $netzgebiete = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
            $this->columns[$netzgebietColIdx]['table']['filterOptions'] = array_map(fn($ng) => ['value' => $ng['id'], 'text' => $ng['name']], $netzgebiete);
        }
    }

    protected function getAction()
    {
        $pagination = $this->postData['pagination'] ?? ['page' => 1, 'per_page' => 10];
        $filters = $this->postData['filters'] ?? [];
        $order = $this->postData['order'] ?? [];

        $company = WorkorderCompanyModel::getFirst(['addressId' => $this->user->address_id]);
        if (!$company) {
            self::returnJson(['rows' => [], 'pagination' => array_merge($pagination, ['total_rows' => 0, 'total_pages' => 0, 'filtered_available' => 0])]);
            return;
        }

        $db = FronkDB::singleton();
        $fronkDbName = FRONKDB_DBNAME;
        $addressDbName = defined('ADDRESSDB_DBNAME') ? ADDRESSDB_DBNAME : 'addressdb';

        $whereClauses = "WHERE w.companyId = " . intval($company->id);

        if (empty($filters['status'])) {
            $whereClauses .= " AND w.status NOT IN ('completed', 'cancelled', 'archived')";
        } else {
            $whereClauses .= Helper::generateFilterCondition($filters['status'], 'w.status', true);
        }

        if (!empty($filters['id'])) $whereClauses .= Helper::generateFilterCondition($filters['id'], 'w.id', true);
        if (!empty($filters['hausnummerInfo'])) {
            $searchColumns = "str.name|hn.hausnummer|hn.stiege|plz.plz|ort.name|w.additionalInfo";
            $whereClauses .= Helper::generateFilterCondition($filters['hausnummerInfo'], $searchColumns);
        }
        if (!empty($filters['netzgebietName'])) $whereClauses .= Helper::generateFilterCondition($filters['netzgebietName'], 'ng.id');
        if (!empty($filters['rimoFcpName'])) $whereClauses .= Helper::generateFilterCondition($filters['rimoFcpName'], 'hn.rimo_fcp_name');
        if (!empty($filters['deadlineDate'])) $whereClauses .= Helper::generateFilterCondition($filters['deadlineDate'], 'w.deadlineDate');
        if (!empty($filters['appointmentDate'])) $whereClauses .= Helper::generateFilterCondition($filters['appointmentDate'], 'w.appointmentDate');
        if (!empty($filters['additionalInfo'])) $whereClauses .= Helper::generateFilterCondition($filters['additionalInfo'], 'w.additionalInfo');

        $sql = "
            SELECT
                w.id, w.status, w.deadlineDate, w.appointmentDate, w.additionalInfo,
                CONCAT_WS(' ', str.name, hn.hausnummer, hn.stiege) as hausnummerInfo,
                str.name as street, hn.hausnummer, hn.stiege, plz.plz, ort.name as city,
                ng.id as netzgebietName,
                hn.rimo_fcp_name as rimoFcpName,
                (SELECT COUNT(*) FROM `$addressDbName`.`Wohneinheit` we WHERE we.hausnummer_id = hn.id) as wohneinheitCount
            FROM `$fronkDbName`.`WorkorderMph` w
            LEFT JOIN `$addressDbName`.`Hausnummer` hn ON w.hausnummerId = hn.id
            LEFT JOIN `$addressDbName`.`Strasse` str ON hn.strasse_id = str.id
            LEFT JOIN `$addressDbName`.`Plz` plz ON hn.plz_id = plz.id
            LEFT JOIN `$addressDbName`.`Ortschaft` ort ON hn.ortschaft_id = ort.id
            LEFT JOIN `$addressDbName`.`Netzgebiet` ng ON hn.netzgebiet_id = ng.id
            $whereClauses
        ";

        $orderBy = "";
        if (!empty($order['key'])) {
            $sortableColumns = ['id', 'status', 'deadlineDate', 'appointmentDate'];
            if (in_array($order['key'], $sortableColumns)) {
                $sortOrder = (strtoupper($order['order']) === 'DESC') ? 'DESC' : 'ASC';
                $orderBy = " ORDER BY " . $db->escape($order['key']) . " " . $sortOrder;
            }
        }
        if (empty($orderBy)) $orderBy = " ORDER BY CASE WHEN w.deadlineDate IS NULL THEN 1 ELSE 0 END, w.deadlineDate ASC";

        $sql .= $orderBy;

        // Get total count
        $countSql = "SELECT COUNT(*) as count FROM `$fronkDbName`.`WorkorderMph` w
                     LEFT JOIN `$addressDbName`.`Hausnummer` hn ON w.hausnummerId = hn.id
                     LEFT JOIN `$addressDbName`.`Strasse` str ON hn.strasse_id = str.id
                     LEFT JOIN `$addressDbName`.`Plz` plz ON hn.plz_id = plz.id
                     LEFT JOIN `$addressDbName`.`Ortschaft` ort ON hn.ortschaft_id = ort.id
                     LEFT JOIN `$addressDbName`.`Netzgebiet` ng ON hn.netzgebiet_id = ng.id
                     $whereClauses";
        $totalCount = (int)$db->query($countSql)->fetch_assoc()['count'];

        // Add pagination
        if ($pagination['per_page'] !== null) {
            $sql .= " LIMIT " . intval($pagination['per_page']) . " OFFSET " . intval(($pagination['page'] - 1) * $pagination['per_page']);
        }

        $result = $db->query($sql);
        $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

        self::returnJson([
            'rows' => $rows,
            'pagination' => [
                'page' => (int)$pagination['page'],
                'per_page' => (int)$pagination['per_page'],
                'total_rows' => $totalCount,
                'total_pages' => (int)ceil($totalCount / $pagination['per_page']),
                'filtered_available' => $totalCount
            ]
        ]);
    }

    public function getWorkorderByIdAction()
    {
        if (empty($this->request->id)) self::sendError("ID fehlt");
        $workorder = WorkorderMphModel::get($this->request->id);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden");
        self::returnJson((array)$workorder);
    }

    protected function scheduleAppointmentAction()
    {
        if (empty($this->postData['workorderId']) || empty($this->postData['appointmentDate'])) self::sendError("Erforderliche Felder fehlen.");
        $workorder = WorkorderMphModel::get($this->postData['workorderId']);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden");
        if ((int)date('H', $this->postData['appointmentDate']) >= 23 || (int)date('H', $this->postData['appointmentDate']) < 1) self::sendError("Bitte geben Sie eine Uhrzeit an!");

        $oldStatus = $workorder->status;
        $workorder->appointmentDate = $this->postData['appointmentDate'];
        $workorder->status = 'scheduled';
        WorkorderMphModel::update((array)$workorder);

        WorkorderMphJournalModel::create([
            'workorderMphId' => $workorder->id,
            'text' => 'Termin festgelegt auf: ' . date('d.m.Y H:i', $this->postData['appointmentDate']),
            'statusChange' => $oldStatus !== 'scheduled' ? $this->getStatusText($oldStatus) . " -> " . $this->getStatusText('scheduled') : null,
            'create' => time(),
            'createBy' => $this->user->id,
        ]);
        self::returnJson(['success' => true, 'message' => 'Termin erfolgreich gespeichert.']);
    }

    protected function rescheduleAppointmentAction()
    {
        if (empty($this->postData['workorderId']) || empty($this->postData['appointmentDate']) || empty($this->postData['reason'])) self::sendError("Erforderliche Felder fehlen.");
        $workorder = WorkorderMphModel::get($this->postData['workorderId']);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden.");
        if ((int)date('H', $this->postData['appointmentDate']) >= 23 || (int)date('H', $this->postData['appointmentDate']) < 1) self::sendError("Bitte geben Sie eine Uhrzeit an!");

        $oldDateFormatted = $workorder->appointmentDate ? date('d.m.Y H:i', $workorder->appointmentDate) : 'N/A';
        $newDateFormatted = date('d.m.Y H:i', $this->postData['appointmentDate']);
        $workorder->appointmentDate = $this->postData['appointmentDate'];
        WorkorderMphModel::update((array)$workorder);

        WorkorderMphJournalModel::create([
            'workorderMphId' => $workorder->id,
            'text' => "Termin verschoben von {$oldDateFormatted} auf {$newDateFormatted}. Grund: " . $this->postData['reason'],
            'create' => time(),
            'createBy' => $this->user->id,
        ]);
        self::returnJson(['success' => true, 'message' => 'Termin erfolgreich verschoben.']);
    }

    protected function startWorkAction()
    {
        if (empty($this->postData['workorderId'])) self::sendError("Arbeitsauftrags-ID fehlt.");
        $workorder = WorkorderMphModel::get($this->postData['workorderId']);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden.");

        $oldStatus = $workorder->status;
        $workorder->status = 'in_progress';
        WorkorderMphModel::update((array)$workorder);

        WorkorderMphJournalModel::create([
            'workorderMphId' => $workorder->id,
            'text' => 'Arbeit begonnen.',
            'statusChange' => $this->getStatusText($oldStatus) . " -> " . $this->getStatusText('in_progress'),
            'create' => time(),
            'createBy' => $this->user->id,
        ]);
        self::returnJson(['success' => true, 'message' => 'Arbeit wurde gestartet.']);
    }

    protected function completeWorkorderAction()
    {
        if (empty($this->postData['workorderId'])) self::sendError("Arbeitsauftrags-ID fehlt.");
        $workorder = WorkorderMphModel::get($this->postData['workorderId']);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden.");

        $oldStatus = $workorder->status;
        $workorder->status = 'documented';
        WorkorderMphModel::update((array)$workorder);

        WorkorderMphJournalModel::create([
            'workorderMphId' => $workorder->id,
            'text' => 'Arbeitsauftrag abgeschlossen und dokumentiert.',
            'statusChange' => $this->getStatusText($oldStatus) . " -> " . $this->getStatusText('documented'),
            'create' => time(),
            'createBy' => $this->user->id,
        ]);
        self::returnJson(['success' => true, 'message' => 'Arbeitsauftrag erfolgreich abgeschlossen.']);
    }

    protected function uploadDocumentationAction()
    {
        if (empty($_FILES['file']) || empty($_POST['workorderMphId'])) self::sendError("Datei und Arbeitsauftrags-ID sind erforderlich.");

        $workorderMphId = intval($_POST['workorderMphId']);
        $workorder = WorkorderMphModel::get($workorderMphId);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden.");

        $documentType = $_POST['documentType'] ?? 'photo';
        $description = $_POST['description'] ?? null;

        // Upload file using mfUpload
        $upload = new mfUpload($_FILES['file']);
        if (!$upload->upload()) {
            self::sendError("Datei-Upload fehlgeschlagen.");
        }

        $file = $upload->getFile();

        WorkorderMphDocumentationModel::create([
            'workorderMphId' => $workorderMphId,
            'fileId' => $file->id,
            'description' => $description,
            'documentType' => $documentType,
            'create' => time(),
            'createBy' => $this->user->id
        ]);

        self::returnJson(['success' => true, 'message' => 'Dokument erfolgreich hochgeladen.', 'fileId' => $file->id]);
    }

    protected function deleteDocumentationAction()
    {
        if (empty($this->postData['documentationId'])) self::sendError("Dokumentations-ID fehlt.");

        $doc = WorkorderMphDocumentationModel::get($this->postData['documentationId']);
        if (!$doc) self::sendError("Dokumentation nicht gefunden.");

        WorkorderMphDocumentationModel::delete($doc->id);
        self::returnJson(['success' => true, 'message' => 'Dokumentation gelöscht.']);
    }

    protected function updateAdditionalInfoAction()
    {
        if (empty($this->postData['workorderMphId'])) self::sendError("Arbeitsauftrags-ID fehlt.");

        $workorder = WorkorderMphModel::get($this->postData['workorderMphId']);
        if (!$workorder) self::sendError("Arbeitsauftrag nicht gefunden.");

        // Verify company access
        $company = WorkorderCompanyModel::getFirst(['addressId' => $this->user->address_id]);
        if (!$company || $workorder->companyId != $company->id) {
            self::sendError("Keine Berechtigung für diesen Arbeitsauftrag.");
        }

        $oldInfo = $workorder->additionalInfo;
        $newInfo = $this->postData['additionalInfo'] ?? '';
        $workorder->additionalInfo = $newInfo;
        WorkorderMphModel::update((array)$workorder);

        if ($oldInfo !== $newInfo) {
            WorkorderMphJournalModel::create([
                'workorderMphId' => $workorder->id,
                'text' => "Notiz geändert: " . ($newInfo ?: '(leer)'),
                'create' => time(),
                'createBy' => $this->user->id,
            ]);
        }

        self::returnJson(['success' => true, 'message' => 'Notiz aktualisiert.', 'newInfo' => $newInfo]);
    }
}