<?php

class ConstructionConsentController extends mfBaseController {

    protected function init() : void
    {
        $this->needlogin = true;
        $me = new User();
        $me->loadMe();
        $this->me = $me;
        $this->layout()->set("me", $me);

        if ($this->me->is("Admin")) $this->constructionConsentProjects = array_column(ConstructionConsentProject::getAll(), 'id');
        else {
            $constructionConsentProjects = json_decode((new WorkerFlag($this->me->id, "constructionConsent_projects"))->value() ?? '[]');
            empty($constructionConsentProjects) ? $this->redirect("Dashboard") : $this->constructionConsentProjects = $constructionConsentProjects;
        }
    }

    protected function indexAction() : void {
        $this->layout()->setTemplate("ConstructionConsent/Index");

        if($this->request->resetFilter) {
            unset($_SESSION[MFAPPNAME . '-ConstructionConsent-filter']);
            foreach($_SESSION as $key => $s) {
                if(preg_match('/^' . MFAPPNAME . '-ConstructionConsent-filter-project-\d+$/', $key)) {
                    unset($_SESSION[$key]);
                }
            }
        }

        $filter = [];
        $hasFaultyEntries = false;

        if(is_array($this->request->filter)) {
            $filter = $this->request->filter;

            if(isset($this->request->filter["project_id"]) && $this->request->filter["project_id"]) {
                $filter_p_id = $filter["project_id"];
                if(count($this->request->filter) == 1 && isset($_SESSION[MFAPPNAME . '-ConstructionConsent-filter-project-' . $filter_p_id])) {
                    $filter = $_SESSION[MFAPPNAME . '-ConstructionConsent-filter-project-' . $filter_p_id];
                } else {
                    $_SESSION[MFAPPNAME . '-ConstructionConsent-filter-project-' . $filter["project_id"]] = $filter;
                }
                $hasFaultyEntries = ConstructionConsentProject::hasFaultyOwnerEntries($filter["project_id"]);
            } else {
                $_SESSION[MFAPPNAME . '-ConstructionConsent-filter'] = $filter;
            }
        } else {
            if(array_key_exists(MFAPPNAME . '-ConstructionConsent-filter', $_SESSION) && count($_SESSION[MFAPPNAME . '-ConstructionConsent-filter'])) {
                $filter = $_SESSION[MFAPPNAME . '-ConstructionConsent-filter'];
                if(isset($filter['project_id'])) {
                    $filter_p_id = $filter['project_id'];
                    if(isset($_SESSION[MFAPPNAME . '-ConstructionConsent-filter-project-' . $filter_p_id])) {
                        $filter = $_SESSION[MFAPPNAME . '-ConstructionConsent-filter-project-' . $filter_p_id];
                    }
                }
            }
        }

        //var_dump($_SESSION, $filter);exit;

        $this->layout->set("allowed_projects", $this->constructionConsentProjects);
        $this->layout->set("hasFaultyEntries", $hasFaultyEntries);
        $this->layout->set("filter", $filter);
        $filter = $this->getPreparedFilter($filter);

        // pagination defaults
        $pagination = [];
        $pagination['start'] = 0;
        $pagination['count'] = 20;
        $pagination['maxItems'] = 0;

        if (is_numeric($this->request->s)) {
            $pagination['start'] = intval($this->request->s);
        }
        //var_dump($filter);exit;
        $pagination['maxItems'] = ConstructionConsent::count($filter);
        $this->layout()->set("pagination", $pagination);

        $items = ConstructionConsent::search($filter, $pagination);
        $this->layout->set("items", $items);
        $this->layout()->set("stats", $this->generateStats($filter));

        if(array_key_exists("project_id", $filter) && $filter["project_id"]) {
            $project = new ConstructionConsentProject($filter["project_id"]);
            if($project->id) {
                $this->layout()->set("project", $project);
            }
        }

    }

    private function getPreparedFilter($filter) : array
    {
        $new_filter = [];

        if (is_array($filter) && count($filter)) {
            foreach ($filter as $name => $value) {
                $new_filter[$name] = $value;
            }
        }

        if (isset($new_filter['project_id']) && !in_array($new_filter['project_id'], $this->constructionConsentProjects) && strlen($new_filter['project_id']) > 0) {
            $this->redirect("ConstructionConsent");
        }

        if (empty($new_filter['project_id'])) {
            $new_filter['project_id'] = $this->constructionConsentProjects;
        }

        return $new_filter;
    }

    protected function viewAction() : void {
        $this->layout()->setTemplate("ConstructionConsent/View");
        $this->layout()->set("is_admin", $this->me->isAdmin());

        $id = $this->request->id;
        if(!is_numeric($id) || $id < 1) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item = new ConstructionConsent($id);
        if(!$item || !$item->id || !in_array($item->constructionconsentproject_id, $this->constructionConsentProjects)) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $this->layout()->set("item", $item);
    }

    protected function addAction() : void {
        $this->layout()->setTemplate("ConstructionConsent/Form");
        if($this->request->project_id) {
            $this->layout()->set("project_id", $this->request->project_id);
        }
        $this->layout()->set("allowed_projects", $this->constructionConsentProjects);
    }

    protected function editAction() : void {
        $id = $this->request->id;
        if(!is_numeric($id) || $id < 1) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item = new ConstructionConsent($id);
        if(!$item || !$item->id) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $this->layout()->set("item", $item);
        $this->addAction();
    }

    protected function generatePdf(array $owners, ConstructionConsent $consent): ?string
    {
        if (empty($owners)) return null;

        $this->layout()->setTemplate("ConstructionConsent/Consentform.pdf");
        $this->layout()->set("ressourcePathPrefix", MFFANCYBASEURL);
        $this->layout()->set("owners", $owners);
        $this->layout()->set("consent", $consent);

        return $consent->createConsentFormPdf($owners); // Might need adjustment for multiple
    }

    protected function downloadAction()
    {
        $owner_id = $this->request->owner_id;
        $open = $this->request->open;

        if (!is_numeric($owner_id) || $owner_id < 1 || !($owner = new ConstructionConsentOwner($owner_id))->id) {
            $this->layout()->setFlash("Besitzer nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        if (!($cc = $owner->consent)) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden.", "error");
            $this->redirect("ConstructionConsent");
        }

        if (!($filename = $this->generatePdf([$owner], $cc))) {
            $this->layout()->setFlash("PDF-Erstellung fehlgeschlagen", "error");
            $this->redirect("ConstructionConsent", "View", ["id" => $cc->id]);
        }

        $this->sendPdfResponse($filename, "Zustimmungserklärung-{$cc->id}-{$owner_id}.pdf", $open === 'true');
    }

    protected function downloadMultipleAction()
    {
        $owners = [];
        foreach (ConstructionConsent::search($this->getPreparedFilter($this->request->filter), []) as $consent)
            $owners = array_merge($owners, $consent->owners ?: []);

        if (empty($owners)) {
            $this->layout()->setFlash("Keine Besitzer gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        if ($this->request->markAsSent === '1') {
            foreach ($owners as $owner) {
                $update = $owner->status === 'new' || $owner->status === null;
                if ($owner->status === 'new') $owner->status = 'sent';
                if ($owner->result === null) $owner->result = 'open';
                if ($update) {
                    $owner->save();
                    $history = ConstructionConsentHistory::create([
                        "constructionconsent_id" => $owner->consent->id,
                        "key" => "status",
                        "old_value" => 'new',
                        "new_value" => 'sent'
                    ]);
                    $history->save();
                }
            }
        }

        if (!($filename = $this->generatePdf($owners, $owners[0]->consent))) {
            $this->layout()->setFlash("PDF-Erstellung fehlgeschlagen", "error");
            $this->redirect("ConstructionConsent");
        }

        $this->sendPdfResponse($filename, "Zustimmungserklärungen-".date('Y-m-d').".pdf");
    }

    private function sendPdfResponse(string $filename, string $downloadName, bool $open = false): void
    {
        header('Content-Type: ' . mime_content_type($filename));
        if ($open === true) header('Content-disposition: inline; filename="' . rawurlencode($downloadName) . '"');
        else header('Content-disposition: attachment; filename="' . rawurlencode($downloadName) . '"');

        header('Content-Length: ' . filesize($filename));
        readfile($filename);
        exit;
    }

    protected function saveAction() {
        $r = $this->request;

        $id = $r->id;
        if(is_numeric($id) && $id > 0) {
            //var_dump($id);exit;
            $mode = "edit";
            $item = new ConstructionConsent($id);
            if(!$item->id) {
                $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
                $this->redirect("ConstructionConsent");
            }
        } else {
            $id = false;
            $mode = "add";
        }

        if (!in_array($r->constructionconsentproject_id, $this->constructionConsentProjects)) {
            $this->layout()->setFlash("Sie haben keine Berechtigung für dieses Projekt", "error");
            $this->redirect("ConstructionConsent");
        }
//        if mode is add and object type is building check if adb_hausnummer_id is set and if object type is street check if adb_strasse_id is set

        if($r->object_type == "building" && !$r->adb_hausnummer_id) {
            $this->layout()->setFlash("Kein Gebäude ausgewählt", "error");
            return $this->addAction();
        }

        if($r->object_type == "street" && !$r->adb_strasse_id) {
            $this->layout()->setFlash("Keine Straße ausgewählt", "error");
            return $this->addAction();
        }


        $data = [];
        $data["constructionconsentproject_id"] = $r->constructionconsentproject_id;
        $data["netzgebiet_id"] = (isset($r->netzgebiet_id) && is_int($r->netzgebiet_id)) ? $r->netzgebiet_id : null;
        $data["object_type"] = $r->object_type;
        $data["name"] = $r->name;
        $data["adb_hausnummer_id"] = $r->adb_hausnummer_id;
        $data["adb_strasse_id"] = $r->adb_strasse_id;
        $data["ez"] = $r->ez;
        $data["kg"] = $r->kg;
        $data["gst"] = $r->gst;
        $data["gstnr"] = $r->gstnr;
        $data["rimo_gn"] = $r->rimo_gn;
        $data["usage_length"] = $r->usage_length ?: null;
        $data["usage_pipe_on_plot"] = $r->usage_pipe_on_plot ? 1 : 0;
        $data["usage_pipe_in_building"] = $r->usage_pipe_in_building ? 1 : 0;
        $data["usage_manhole"] = $r->usage_manhole ? 1 : 0;
        $data["usage_owner"] = $r->usage_owner ? 1 : 0;

        if($r->object_type == "building") {
            $data["usage_pipe_on_plot"] = 1;
            $data["usage_pipe_in_building"] = 1;
            $data["usage_manhole"] = 1;
            $data["usage_owner"] = 1;
        }

        if($mode == "add") {
            $item = ConstructionConsent::create($data);
        } else {
            $item->update($data);
        }
        $this->layout()->set("item", $item);

        $project = new ConstructionConsentProject($data["constructionconsentproject_id"]);
        if(!$project->id) {
            $this->layout()->setFlash("Projekt nicht gefunden", "error");
            return $this->addAction();
        }

        if(!$data["object_type"]) {
            $this->layout()->setFlash("Bitte alle benötigten Felder ausfüllen", "error");
            return $this->addAction();
        }

        if($data["object_type"] == "address") {
            if(!$data["adb_hausnummer_id"]) {
                $this->layout()->setFlash("Kein Gebäude ausgewählt", "error");
                return $this->addAction();
            }
            unset($data["adb_strasse_id"]);
        }

        if($data["object_type"] == "street") {
            if(!$data["adb_strasse_id"]) {
                $this->layout()->setFlash("Keine Straße ausgewählt", "error");
                return $this->addAction();
            }
            unset($data["adb_hausnummer_id"]);
        }

        //var_dump($data, $item);exit;
        if(!$item->save()) {
            $this->layout()->setFlash("Fehler beim Speichern", "error");
            return $this->addAction();
        }

        if(is_array($_FILES) && array_key_exists("consent_plan_image", $_FILES) && !$_FILES['consent_plan_image']['error']) {
            try {
                // returns File object or throws Exception on error
                $file = mfUpload::handleFormUpload("consent_plan_image", false, TT_CONSTRUCTIONCONSENT_FILE_UPLOAD_SUBFOLDER);
            } catch (Exception $ex) {
                $this->layout()->setFlash("Fehler beim Hochladen: " . $ex->getMessage(), "warning");
                return $this->editAction();
            }

            $history = ConstructionConsentHistory::create([
                "constructionconsent_id" => $item->id,
                "key" => "plan_upload",
                "old_value" => ($item->file) ? $item->file->file->id : null,
                "new_value" => $file->id
            ]);

        } else {
            if($r->submit_plan_file_id) {
                $file = new File($r->submit_plan_file_id);
                $history = ConstructionConsentHistory::create([
                    "constructionconsent_id" => $item->id,
                    "key" => "rimo_plan",
                    "old_value" => ($item->file) ? $item->file->file->id : null,
                    "new_value" => $file->id
                ]);
            }
        }

        if($file && $file->id) {
            $ccf = ConstructionConsentFile::create([
                'constructionconsent_id' => $item->id,
                'file_id' => $file->id,
                'filename' => "zustimmungserklärung-" . $item->id . "-plan.png",
            ]);

            // delete previous image
            $img = ConstructionConsentFile::getFirst(["constructionconsent_id" => $item->id]);
            if ($img) {
                $img->file->delete();
                $img->delete();
            }

            if (!$ccf->save()) {
                $this->layout()->setFlash("Fehler beim Speichern des Plans", "warning");
            }

            $history->save();
        }


        $this->layout()->setFlash("Zustimmungserklärung erfolgreich gespeichert", "success");
        $this->redirect("ConstructionConsent", "View", ["id" => $item->id]);

    }

    protected function deleteAction() {
        $id = $this->request->id;
        if(!is_numeric($id) || $id < 1) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item = new ConstructionConsent($id);
        if(!$item->id) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item->delete();

        $this->layout()->setFlash("Zustimmungserklärung erfolgreich gelöscht", "success");
        $this->redirect("ConstructionConsent");
    }

    protected function saveDate() {
        $r = $this->request;

        $id = $r->consent_id;
        if(is_numeric($id) && $id > 0) {
            $mode = "edit";
            $item = new ConstructionConsent($id);
            if(!$item->id) {
                $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
                $this->redirect("ConstructionConsent");
            }
        } else {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $data = [];
        if($r->inspection_date_planner) {
            $data["inspection_date_planner"] = Layout::dateToInt($r->inspection_date_planner);
        }
        if($r->inspection_date_electrician) {
            $data["inspection_date_electrician"] = Layout::dateToInt($r->inspection_date_electrician);
        }

        if(count($data)) {
            $item->update($data);

            if(!$item->save()) {
                $this->layout()->setFlash("Fehler beim Speichern", "error");
                return $this->addAction();
            }
        }


        $this->layout()->setFlash("Zustimmungserklärung erfolgreich gespeichert", "success");
        $this->redirect("ConstructionConsent", "View", ["id" => $item->id]);
    }

    protected function uploadFileAction() {
        $id = $this->request->consent_id;
        if(!is_numeric($id) || $id < 1) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item = new ConstructionConsent($id);
        if(!$item || !$item->id) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $upload_type = $this->request->upload_type;
        if($upload_type != "planner" && $upload_type != "electrician") {
            $this->layout()->setFlash("Falscher Dateityp", "error");
            $this->redirect("ConstructionConsent", "View", ["id" => $item->id]);
        }

        if(is_array($_FILES) && array_key_exists("inspection_protocol_file", $_FILES) && !$_FILES['inspection_protocol_file']['error']) {
            try {
                // returns File object or throws Exception on error
                $file = mfUpload::handleFormUpload("inspection_protocol_file", false, TT_CONSTRUCTIONCONSENT_FILE_UPLOAD_SUBFOLDER);
            } catch (Exception $ex) {
                $this->layout()->setFlash("Fehler beim Hochladen: " . $ex->getMessage(), "warning");
                $this->redirect("ConstructionConsent", "View", ["id" => $item->id]);
            }

            $history = ConstructionConsentHistory::create([
                "constructionconsent_id" => $item->id,
                "key" => "inspection_protocol_$upload_type",
                "old_value" => ($item->{"inspection_protocol_$upload_type"}) ? $item->{"inspection_protocol_$upload_type"}->file->id : null,
                "new_value" => $file->id
            ]);


            $ccf = ConstructionConsentFile::create([
                'constructionconsent_id' => $item->id,
                'file_id' => $file->id,
                'filename' => "inspection_protocol_$upload_type",
            ]);

            // delete previous image
            if($item->{"inspection_protocol_$upload_type"}) {
                $item->{"inspection_protocol_$upload_type"}->file->delete();
                $item->{"inspection_protocol_$upload_type"}->delete();
            }

            if (!$ccf->save()) {
                $this->layout()->setFlash("Fehler beim Speichern des Begehungsprotokolls", "warning");
            }

            $history->save();

            $this->layout()->setFlash("Begehungsprotokoll erfolgreich hochgeladen", "success");
        }

        $this->redirect("ConstructionConsent", "View", ["id" => $item->id]);
    }

    protected function deleteFileAction()
    {
        $id = $this->request->id;
        if (!is_numeric($id) || $id < 1) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $item = new ConstructionConsent($id);
        if (!$item || !$item->id) {
            $this->layout()->setFlash("Zustimmungserklärung nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $file_id = $this->request->file_id;

        $file = ConstructionConsentFile::getFirst(["constructionconsent_id" => $id, "file_id" => $file_id]);
        if(!$file) {
            $this->layout()->setFlash("Datei nicht gefunden", "error");
            $this->redirect("ConstructionConsent", "View", ["id" => $id]);
        }

        $file->file->delete();
        $file->delete();

        $this->layout()->setFlash("Datei gelöscht", "success");
        $this->redirect("ConstructionConsent", "View", ["id" => $id]);
    }

    protected function apiAction() {
        $do = $this->request->do;
        $data = [];

        switch($do) {
            case "findStreet":
                $return = $this->findStreetApi();
                break;
            case "findAddress":
                $return = $this->findAddressApi();
                break;
            case "getNetzgebieteApi":
                $return = $this->getNetzgebieteApi();
                break;
            case "getRimoPlanPreview":
                $return = $this->getRimoPlanPreviewApi();
                break;
            case "saveRimoPlanPreview":
                $return = $this->saveRimoPlanPreviewApi();
                break;
            case "saveCheckbox":
                $return = $this->saveCheckboxApi();
                break;
            case "deleteRimoPlan":
                $return = $this->deleteRimoPlanApi();
                break;
            case "getEz":
                $return = $this->getEzApi();
                break;
            default:
                $this->log->warn(__METHOD__ . ": Called API function '$do' does not exist");
                $return = false;
        }

        if(!is_array($return) || !count($return)) {
            $data = ["status" => "error"];
            $this->returnJson($data);
        }
        $data['status'] = "OK";
        $data['result'] = $return;
        $this->returnJson($data);
    }

    private function getEzApi() {
        $kg = trim($this->request->kg);
        $gst = trim($this->request->gst);

        if(!$kg || !$gst) return false;

        $ez = ADBGwrgst::getFirst(["kgnr" => $kg, "gstnr" => $gst]);
        //var_dump($ez);exit;
        if(!$ez) {
            return ["ez" => ""];
        }

        return ["ez" => $ez->ez];
    }


    private function findStreetApi() {
        $addresses = [];
        $search = trim($this->request->q);
        $project_id = $this->request->project_id;

        $scluster_ids = [];

        if($project_id) {
            $project = new ConstructionConsentProject($project_id);
            if(!$project->id) {
                header("Content-Type: application/json");
                echo json_encode(["results" => []]);
                exit;
            }

            foreach($project->adb_networks as $network) {
                $scluster_ids[] = $network->id;
            }
        } else {
            // get all salesclusters
            foreach(ADBNetzgebietModel::getAll() as $network) {
                $scluster_ids[] = $network->id;
            }
        }

        $results = [];

        $search_parts = explode(" ", $search);

        $ort_search = $strasse_search = $plz_search = $hausnummer_search = $gst_search = [];

        foreach($search_parts as $p) {
            $p = $this->db->escape(trim($p));
            if(!$p) continue;
            $ort_search[] = "ortschaft like '$p%'";
            $strasse_search[] = "strasse like '$p%'";
            $plz_search[] = "plz like '%$p%'";
            $hausnummer_search[] = "hausnummer like '%$p%'";
            $gst_search[] = "grund_nr like '%$p%'";
        }

        $where = "1=1";
        if(count($scluster_ids)) {
            $where .= " AND netzgebiet_id IN (".implode(', ',$scluster_ids).")";
        }

        /*if($include_gst) {
            $sql = "SELECT * FROM view_hausnummer WHERE $where AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search).")  OR (".implode(" OR ", $gst_search).") ) ORDER BY strasse, LENGTH(hausnummer), hausnummer";
        } else {
            $sql = "SELECT * FROM view_hausnummer WHERE $where AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search).")) ORDER BY strasse, LENGTH(hausnummer), hausnummer";
        }*/

        $sql = "SELECT strasse_id,plz,ortschaft,strasse FROM view_hausnummer
         WHERE $where 
         AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search)."))
         GROUP BY plz,ortschaft,strasse,strasse_id
         ORDER BY strasse
         ";

        $this->log->debug($sql);

        $adb = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
        $res = $adb->query($sql);

        if(!$adb->num_rows($res)) {
            header("Content-Type: application/json");
            echo json_encode(["results" => []]);
            exit;
        }

        while($data = $adb->fetch_object($res)) {
            //$address_string = $data->plz." ".$data->ortschaft.", ".$data->strasse." ".$data->hausnummer;
            $address_string = $data->plz." ".$data->ortschaft.", ".$data->strasse;
            /*if($include_gst) {
                $address_string .= " | GST: ".$data->grund_nr;
            }*/
            $sort_key = $data->plz." ".$data->ortschaft." ".$data->strasse;

            $address = [];
            $address['id'] = $data->strasse_id;
            $address["text"] = $address_string;
            $address['sort_key'] = $sort_key;
            $addresses[] = $address;
        }


        // sort results by most occurences of search strings
        $sort = [];
        foreach($addresses as $key => $address) {
            $includes_int = false;
            $count = 0;
            foreach($search_parts as $p) {
                $p = $this->db->escape(trim($p));
                if(!$p) continue;
                if(is_numeric(($p))) {
                    $includes_int = true;
                    if(substr_count(strtolower($address['text']), strtolower($p))) {
                        $count++;
                    }
                } else {
                    $count += substr_count(strtolower($address['text']), strtolower($p));
                }
            }
            unset($address['sort_key']);
            //echo $address['text']." $p $count<br />\n";

            if($includes_int && (($count + 1) - count($search_parts) ) < 1) {
                continue;
            }
            if(!array_key_exists($count, $sort)) {
                $sort[$count] = [];
            }
            $sort[$count][] = $address;
        }

        ksort($sort, SORT_NUMERIC);
        $sort = array_reverse($sort, true);
        //var_dump($sort);exit;

        foreach($sort as $res) {
            foreach($res as $a) {
                $results[] = $a;
            }
        }

        header("Content-Type: application/json");
        echo json_encode(["results" => $results]);
        exit;

    }

    private function findAddressApi() {
        $addresses = [];
        $search = trim($this->request->q);
        $project_id = $this->request->project_id;
        $include_gst = $this->request->include_gst ? $this->request->include_gst : false;

        $scluster_ids = [];

        if($project_id) {
            $project = new ConstructionConsentProject($project_id);
            if(!$project->id) {
                header("Content-Type: application/json");
                echo json_encode(["results" => []]);
                exit;
            }

            foreach($project->adb_networks as $network) {
                $scluster_ids[] = $network->id;
            }
        } else {
            // get all salesclusters
            foreach(ADBNetzgebietModel::getAll() as $network) {
                $scluster_ids[] = $network->id;
            }
        }

        $results = [];

        $search_parts = explode(" ", $search);

        $ort_search = $strasse_search = $plz_search = $hausnummer_search = $gst_search = [];

        foreach($search_parts as $p) {
            $p = $this->db->escape(trim($p));
            if(!$p) continue;
            $ort_search[] = "ortschaft like '$p%'";
            $strasse_search[] = "strasse like '$p%'";
            $plz_search[] = "plz like '%$p%'";
            $hausnummer_search[] = "hausnummer like '%$p%'";
            $gst_search[] = "grund_nr like '%$p%'";
        }

        $where = "1=1";
        if(count($scluster_ids)) {
            $where .= " AND netzgebiet_id IN (".implode(', ',$scluster_ids).")";
        }

        if($include_gst) {
            $sql = "SELECT * FROM view_hausnummer WHERE $where AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search).")  OR (".implode(" OR ", $gst_search).") ) ORDER BY strasse, LENGTH(hausnummer), hausnummer";
        } else {
            $sql = "SELECT * FROM view_hausnummer WHERE $where AND ((".implode(" OR ", $ort_search).") OR (".implode(" OR ", $strasse_search).") OR (".implode(" OR ", $plz_search).") OR (".implode(" OR ", $hausnummer_search).")) ORDER BY strasse, LENGTH(hausnummer), hausnummer";
        }


        $this->log->debug($sql);

        $adb = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
        $res = $adb->query($sql);
        $this->log->debug("done");

        if(!$adb->num_rows($res)) {
            header("Content-Type: application/json");
            echo json_encode(["results" => []]);
            exit;
        }

        while($data = $adb->fetch_object($res)) {
            $address_string = $data->plz." ".$data->ortschaft.", ".$data->strasse." ".$data->hausnummer;
            if($include_gst) {
                $address_string .= " | GST: ".$data->grund_nr;
            }
            $sort_key = $data->plz." ".$data->ortschaft." ".$data->strasse;

            $address = [];
            $address['id'] = $data->hausnummer_id;
            $address["text"] = $address_string;
            $address['sort_key'] = $sort_key;
            $addresses[] = $address;
        }


        // sort results by most occurences of search strings
        $sort = [];
        foreach($addresses as $key => $address) {
            $includes_int = false;
            $count = 0;
            foreach($search_parts as $p) {
                $p = $this->db->escape(trim($p));
                if(!$p) continue;
                if(is_numeric(($p))) {
                    $includes_int = true;
                    if(substr_count(strtolower($address['text']), strtolower($p))) {
                        $count++;
                    }
                } else {
                    $count += substr_count(strtolower($address['text']), strtolower($p));
                }
            }
            unset($address['sort_key']);
            //echo $address['text']." $p $count<br />\n";

            if($includes_int && (($count + 1) - count($search_parts) ) < 1) {
                continue;
            }
            if(!array_key_exists($count, $sort)) {
                $sort[$count] = [];
            }
            $sort[$count][] = $address;
        }

        ksort($sort, SORT_NUMERIC);
        $sort = array_reverse($sort, true);
        //var_dump($sort);exit;

        foreach($sort as $res) {
            foreach($res as $a) {
                $results[] = $a;
            }
        }

        header("Content-Type: application/json");
        echo json_encode(["results" => $results]);
        exit;

    }

    private function getRimoPlanPreviewApi() {
        $adb_hausnummer_id = $this->request->building_id;
        $include_home_trench = $this->request->include_home_trench ? true : false;

        $hausnummer = new ADBHausnummer($adb_hausnummer_id);
        if(!$hausnummer->id) {
            return false;
        }

        $filename = "consent_plan_map_h{$adb_hausnummer_id}";

        /*$bpi_file = PreorderFile::getFirst(["preorder_id" => $this->id, "filename" => $filename]);
        if($bpi_file) {
            return $bpi_file;
        }*/

        // get trenches from rimo
        $geodataResponse = Rimoapi::getBuildingGeoJson($hausnummer->rimo_id);
        $this->log->debug(__METHOD__.": ".print_r($geodataResponse, true));
        //return false;

        if (is_object($geodataResponse)) {
            if (property_exists($geodataResponse, "homeSection")) {
                if (property_exists($geodataResponse->homeSection, "features") && is_array($geodataResponse->homeSection->features)) {
                    foreach ($geodataResponse->homeSection->features as $feature) {
                        $home_trench = [];
                        foreach ($feature->geometry->coordinates as $coords) {
                            $long = $coords[0];
                            $lat = $coords[1];
                            $home_trench[] = [$lat, $long];
                        }
                        if ($hausnummer->home_trench != $home_trench) {
                            $hausnummer->home_trench = json_encode($home_trench);
                            $hausnummer->save();
                        }

                    }
                }
            }
            if (property_exists($geodataResponse, "borderPoint")) {
                if (property_exists($geodataResponse->borderPoint, "features") && is_array($geodataResponse->borderPoint->features)) {
                    foreach ($geodataResponse->borderPoint->features as $feature) {
                        $coords = $feature->geometry->coordinates;
                        $long = $coords[0];
                        $lat = $coords[1];
                        if ($hausnummer->borderpoint_lat != $lat || $hausnummer->borderpoint_long != $long) {
                            $hausnummer->borderpoint_lat = $lat;
                            $hausnummer->borderpoint_long = $long;
                            $hausnummer->save();
                        }
                    }
                }
            }
            if (property_exists($geodataResponse, "trenches") && $geodataResponse->trenches->features) {
                $trenches = [];
                foreach ($geodataResponse->trenches->features as $feature) {
                    $feature_coords = [];
                    foreach ($feature->geometry->coordinates as $coords) {
                        $long = $coords[0];
                        $lat = $coords[1];
                        $feature_coords[] = [$lat, $long];
                    }
                    $trenches[] = $feature_coords;
                }
                if (count($trenches)) {
                    $hausnummer->trenches = json_encode($trenches);
                    $hausnummer->save();
                }

            }
        }

        // get new Borderpoint Image from Mapbox API
        $params = [
            "pin" => null,
            "gps_lat" => $hausnummer->gps_lat,
            "gps_long" => $hausnummer->gps_long,
            "zoom" => 19,
            "size_x" => 640,
            "size_y" => 640,
            "style" => "satellite-streets-v12",
            "paths" => "",
            "access_token" => TT_MAPBOX_TILE_API_TOKEN
        ];

        if($hausnummer->trenches) {
            $trenches = [];
            if($include_home_trench && $hausnummer->home_trench) {
                $trenches = [json_decode($hausnummer->home_trench)];
            }

            $street_trenches = json_decode($hausnummer->trenches);
            foreach($street_trenches as $t) {
                if(is_array($t) && count($t)) {
                    $trenches[] = $t;
                }
            }
            $this->log->debug(print_r($trenches, true));

            $params["paths"] = [
                "line_width" => 5,
                "line_color" => "ff0000",
                "line_opacity" => 1,
                "line_fill_color" => "ff0000",
                "line_fill_opacity" => 1,
                "coords" => $trenches
            ];
        }

        $image_content = Mapbox_StaticImageApi::getImageFileContent($params);
        if(!$image_content) {
            return false;
        }

        $fs_filename = "$filename.jpg";
        if(!file_put_contents(MFUPLOAD_FILE_SAVE_PATH."/".TT_CONSTRUCTIONCONSENT_FILE_UPLOAD_SUBFOLDER."/$fs_filename", $image_content)) {
            $this->log->error(__METHOD__.": Error saving Borderpoint Static Map Image File");
            return false;
        }


        $file = FileModel::create([
            "name" => "consent_plan_map",
            "description" => $adb_hausnummer_id,
            "filename" => "$filename.jpg",
            "orig_filename" => "$filename.jpg",
            "store_filename" => $fs_filename,
            "subfolder" => TT_CONSTRUCTIONCONSENT_FILE_UPLOAD_SUBFOLDER,
        ]);
        if(!$file->save()) {
            $this->log->error(__METHOD__.": Error saving File Object");
            return false;
        }

        $file->mimetype = $file->getMimetype();
        $file->save();

        return ["image_mimetype" => $file->mimetype, "image_base64" => base64_encode($image_content), "file_id" => $file->id];

    }

    private function deleteRimoPlanApi() {
        $consent_id = $this->request->id;

        $consent = new ConstructionConsent($consent_id);
        if(!$consent->id) return false;

        if($consent->file && $consent->file->file_id) {
            $consent->file->delete();
        }

        return ["message" => "Plan deleted successfully"];
    }

    private function saveCheckboxApi() {
        $r = $this->request;
        $consent_id = $r->id;

        $consent = new ConstructionConsent($consent_id);
        if(!$consent->id) return false;

        $data = [];
        if($r->isset("inspection_planner")) {
            $data["inspection_planner"] = ($r->inspection_planner > 0) ? date("U") : 0;
        }
        if($r->isset("inspection_electrician")) {
            $data["inspection_electrician"] = ($r->inspection_electrician > 0) ? date("U") : 0;
        }
        if($r->isset("conduit_installed_building")) {
            $data["conduit_installed_building"] = ($r->conduit_installed_building > 0) ? date("U") : 0;
        }
        if($r->isset("conduit_installed_ftu")) {
            $data["conduit_installed_ftu"] = ($r->conduit_installed_ftu > 0) ? date("U") : 0;
        }
        if($r->isset("inhouse_cabling")) {
            $data["inhouse_cabling"] = ($r->inhouse_cabling > 0) ? date("U") : 0;
        }
        if(count($data)) {
            $consent->update($data);
            $consent->save();
        }


        return ["message" => "ConstructionConsent saved successfully"];
    }

    private function generateStats($baseFilter = array()): array {
        function getFilteredCount($wantedFilter, $filterValue, $baseFilter) {
            if ($wantedFilter !== 'deferred' && !empty($baseFilter[$wantedFilter]) && $baseFilter[$wantedFilter] != $filterValue) return 0;
            return ConstructionConsent::count(array_merge($baseFilter, [$wantedFilter => $filterValue]));
        }

        $baseFilter["deferred"] = "NULL";

        return [
            "all" => ConstructionConsent::count($baseFilter),
            "street" => getFilteredCount("object_type", "street", $baseFilter),
            "building" => getFilteredCount("object_type", "building", $baseFilter),
            "inspection_planner" => getFilteredCount("inspection_planner", "!NULL", $baseFilter),
            "conduit_installed_building" => getFilteredCount("conduit_installed_building", "!NULL", $baseFilter),
            "conduit_installed_ftu" => getFilteredCount("conduit_installed_ftu", "!NULL", $baseFilter),
            "inhouse_cabling" => getFilteredCount("inhouse_cabling", "!NULL", $baseFilter),
            "status_light_blue" => getFilteredCount("status_light", "blue", $baseFilter),
            "status_light_red" => getFilteredCount("status_light", "red", $baseFilter),
            "status_light_yellow" => getFilteredCount("status_light", "yellow", $baseFilter),
            "status_light_green" => getFilteredCount("status_light", "green", $baseFilter),
            "status_deferred" => getFilteredCount("deferred", "!NULL", $baseFilter),
        ];
    }

    protected function approveOverrideAction() {
        $consent_id = $this->request->consent_id;
        $checked = $this->request->checked;

        $consent = new ConstructionConsent($consent_id);
        if(!$consent->id) {
            $this->returnJson(["status" => "error"]);
        }

        $consent->update(["approve_override" => $checked]);
        $consent->save();

        $this->returnJson(["status" => "OK"]);

    }

    protected function deferAction() {
        $consent_id = $this->request->consent_id;
        $checked = $this->request->checked;

        $consent = new ConstructionConsent($consent_id);
        if(!$consent->id) {
            $this->returnJson(["status" => "error"]);
        }

        $consent->update(["deferred" => $checked]);
        $consent->save();

        $this->returnJson(["status" => "OK"]);
    }

    protected function prioritizeAction() {
        $consent_id = $this->request->consent_id;
        $checked = $this->request->checked;

        $consent = new ConstructionConsent($consent_id);
        if(!$consent->id) {
            $this->returnJson(["status" => "error"]);
        }

        $consent->update(["prioritize" => $checked]);
        $consent->save();

        $this->returnJson(["status" => "OK"]);
    }

    protected function importConstructionConsentOwnersAction() {
        $projectId = $this->request->project_id;
        $importData = json_decode(file_get_contents('php://input'), true);

        // save full post request and project id to file as log with metadata like user id etc as json file
//        MFUPLOAD_FILE_SAVE_PATH . /ConstructionConsentImports

        $logData = [
            "user_id" => $this->me->id,
            "project_id" => $projectId,
            "import_data" => $importData,
            "timestamp" => date("Y-m-d H:i:s")
        ];
        $logFileName = MFUPLOAD_FILE_SAVE_PATH . "/ConstructionConsentImports/import_" . date("Ymd_His") . "_user_{$this->me->id}_project_{$projectId}.json";
        if (!file_exists(dirname($logFileName))) {
            mkdir(dirname($logFileName), 0777, true);
        }
        file_put_contents($logFileName, json_encode($logData, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));


        if (empty($importData) || !is_array($importData)) { $this->layout()->setFlash("Keine Daten gefunden", "error"); return $this->redirect("ConstructionConsent"); }
        if (!is_numeric($projectId) || $projectId < 1) { $this->layout()->setFlash("Projekt nicht gefunden", "error"); return $this->redirect("ConstructionConsent"); }
        if (!($consentProject = new ConstructionConsentProject($projectId))->id) { $this->layout()->setFlash("Projekt nicht gefunden", "error"); return $this->redirect("ConstructionConsent"); }

        $counts = ["created" => 0, "updated" => 0, "skipped" => 0, "skippedBecauseNoConsent" => 0, "skippedBecauseAlreadyExists" => 0, "skippedBecauseFirstnameAndLastname" => 0];

        foreach ($importData as $ownerData) {
            $consentFilter = ["constructionconsentproject_id" => $consentProject->id, "kg" => $ownerData["KG-EZ"] ?? null, "ez" => $ownerData["EZ"] ?? null, "lnr" => $ownerData["LNR"] ?? null];
            if (!($consentRecords = ConstructionConsent::search(array_filter($consentFilter)))) {
                $counts["skippedBecauseNoConsent"]++;
                $counts["skipped"]++; continue;
            }

            foreach ($consentRecords as $consentRecord) {
                $birthdate = (isset($ownerData["GEB"]) && preg_match("/^\d{4}-\d{2}-\d{2}$/", $ownerData["GEB"])) ? $ownerData["GEB"] : null;
                $ownerFilter = ["constructionconsent_id" => $consentRecord->id];
                if (isset($ownerData["VN"])) { $ownerFilter["firstname"] = $ownerData["VN"]; }
                if (isset($ownerData["NN"])) { $ownerFilter["lastname"] = $ownerData["NN"]; }
                if (isset($ownerData["BEZ"])) { $ownerFilter["company"] = $ownerData["BEZ"]; }

                if ($birthdate) { $ownerFilter["birthdate"] = $birthdate; }

                $isNew = !($ownerRecord = ConstructionConsentOwner::getFirst(array_filter($ownerFilter)));
                if ($isNew) {
                    $ownerRecord = new ConstructionConsentOwner();
                    $ownerRecord->constructionconsent_id = $consentRecord->id;
                    $ownerRecord->create_by = $this->me->id;
                } else {
                    $counts["skippedBecauseAlreadyExists"]++;
                    $counts["skipped"]++;
                    continue;
                }

                $addressKey = null; foreach(array_keys($ownerData) as $k) { if(strpos($k,"ADR") === 0) {$addressKey=$k; break;} }
                $street = $city = $postcode = $country = null;
                if ($addressKey && isset($ownerData[$addressKey])) {
                    $addressParts = explode(", ", str_replace('"', '', $ownerData[$addressKey]));
                    $street = $addressParts[0] ?? null;
                    if (isset($addressParts[1])) { list($postcode, $city) = array_pad(explode(" ", trim($addressParts[1]), 2), 2, null); }
                    $country = $addressParts[2] ?? null;
                }


                $ownerRecord->firstname = $ownerData["VN"] ?? null;
                $ownerRecord->lastname = $ownerData["NN"] ?? null;
                $ownerRecord->company = $ownerData["BEZ"] ?? null;
                $ownerRecord->birthdate = $birthdate;
                $ownerRecord->street = $street;
                $ownerRecord->city = $city;
                $ownerRecord->zip = $postcode;
                $ownerRecord->country = $country;
                $ownerRecord->edit_by = $this->me->id;

                $firstname = $ownerRecord->firstname;
                $lastname = $ownerRecord->lastname;
                $company = $ownerRecord->company;
                if (empty($firstname) && empty($lastname) && empty($company)) {
                    $counts["skippedBecauseFirstnameAndLastname"]++;
                    $counts["skipped"]++;
                    continue;
                }

                $ownerRecord->save();

                $journal = ConstructionConsentJournal::create([
                    "constructionconsent_id" => $consentRecord->id,
                    "text" =>
                        $ownerRecord->company ?
                            "Import: Eigentümer $ownerRecord->company wurde hinzugefügt" :
                            "Import: Eigentümer $firstname $lastname wurde hinzugefügt"
                ]);
                $journal->save();

                $counts[$isNew ? "created" : "updated"]++;

            }
        }

        self::returnJson(["status" => "OK", "counts" => $counts]);
        return true;
    }

    private function getNetzgebieteApi() {
        $project_id = $this->request->project_id;
        if(!is_numeric($project_id) || $project_id < 1) self::sendError("Invalid project id");

        $project = new ConstructionConsentProject($project_id);
        if(!$project->id) self::sendError("Project not found");

        $parsedNetworks = array_map(function($network) {
            return [
                "value" => $network->id,
                "text" => $network->name,
            ];
        }, array_values($project->adb_networks));

        self::returnJson(["results" => $parsedNetworks]);
    }

    protected function downloadCSVAction() {
        $filter = $this->request->filter;
        if(!is_array($filter)) {
            $this->layout()->setFlash("Fehler beim Erstellen der CSV", "error");
            return $this->redirect("ConstructionConsent");
        }

        $filter = $this->getPreparedFilter($filter);
        $items = ConstructionConsent::search($filter);
        $filename = 'Zustimmungserklärungen_' . date('Ymd_His') . '.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '"');
        header('Cache-Control: no-cache, no-store, must-revalidate');
        header('Pragma: no-cache');
        header('Expires: 0');

        $output = fopen('php://output', 'w');
        fwrite($output, chr(0xEF) . chr(0xBB) . chr(0xBF)); // UTF-8 BOM for Excel

        $headers = [
            'Projekt',
            'Objekttyp',
            'Objektadresse',
            'Name',
            'EZ',
            'KG',
            'GST',
            'GSTNR',
            'RIMO GN',
            'Wohneinheiten',
            'Bestellungen',
            'Anfrageresultat',
            'Nutzungslänge',
            'Leerrohr am Grundstück',
            'Leerrohr im Gebäude',
            'Schacht',
            'Eigentümer',
            'Planer Besichtigung',
            'Elektriker Besichtigung',
            'Leerverrohrung im Gebäude installiert',
            'Leerverrohrung bis FTU installiert',
            'Inhouse Verkabelung',
            'Anzahl Eigentümer',
            'Notiz',
            'Erstellt von',
            'Geändert von',
            'Erstellt am',
            'Geändert am',
            'Genehmigung überschrieben'
        ];
        fputcsv($output, $headers, ';');

        if ($items) {
            foreach ($items as $item) {
                $createUser = UserModel::getOne($item->create_by);
                $editUser = UserModel::getOne($item->edit_by);


                $address = "";
                if($item->object_type == "street") {
                    $address = $item->adb_strasse->name . ", " . $item->adb_strasse->ortschaft->name . " " . $item->adb_strasse->gemeinde->name;
                } else {
                    $address = $item->adb_hausnummer->strasse->name . " " . $item->adb_hausnummer->hausnummer . ($item->adb_hausnummer->stiege ? "/".$item->adb_hausnummer->stiege : "") . " " .
                        $item->adb_hausnummer->plz->plz . " " . $item->adb_hausnummer->ortschaft->name . " " .
                        $item->adb_hausnummer->strasse->gemeinde->name;
                }

                $status_class = 'Blau';
                $approve_override = $item->approve_override;
                if (isset($approve_override) && $approve_override) {
                    $status_class = 'Grün';
                } elseif (isset($item->owner_result_counts['denied']) && $item->owner_result_counts['denied'] > 0) {
                    $status_class = 'Rot';
                } elseif (
                    (isset($item->owner_result_counts['unresolvable']) && $item->owner_result_counts['unresolvable'] > 0) ||
                    (isset($item->owner_result_counts['moved']) && $item->owner_result_counts['moved'] > 0) ||
                    (isset($item->owner_result_counts['open']) && $item->owner_result_counts['open'] > 0)
                ) {
                    $status_class = 'Gelb'; // Yellow if at least one unresolvable or moved
                } elseif (isset($item->owner_result_counts['accepted']) &&
                    $item->owner_result_counts['accepted'] === count($item->owners)) {
                    $status_class = 'Grün'; // Green if all accepted
                }

                $row = [
                    $item->project->name,
                    $item->object_type === 'street' ? 'Straße' : 'Gebäude',
                    $address,
                    $item->name,
                    $item->ez,
                    $item->kg,
                    $item->gst,
                    $item->gstnr,
                    $item->rimo_gn,
                    $item->adb_hausnummer->unit_count ?? '',
                    $item->preorder_count,
                    $status_class,
                    $item->usage_length,
                    $item->usage_pipe_on_plot == 1 ? 'Ja' : 'Nein',
                    $item->usage_pipe_in_building == 1 ? 'Ja' : 'Nein',
                    $item->usage_manhole == 1 ? 'Ja' : 'Nein',
                    $item->usage_owner == 1 ? 'Ja' : 'Nein',
                    $item->inspection_planner > 0 ? date('d.m.Y H:i:s', $item->inspection_planner) : '',
                    $item->inspection_electrician > 0 ? date('d.m.Y H:i:s', $item->inspection_electrician) : '',
                    $item->conduit_installed_building > 0 ? date('d.m.Y H:i:s', $item->conduit_installed_building) : '',
                    $item->conduit_installed_ftu > 0 ? date('d.m.Y H:i:s', $item->conduit_installed_ftu) : '',
                    $item->inhouse_cabling > 0 ? date('d.m.Y H:i:s', $item->inhouse_cabling) : '',
                    $item->owners !== null ? count($item->owners) : '',
                    $item->note,
                    $createUser ? $createUser->name : '',
                    $editUser ? $editUser->name : '',
                    date('d.m.Y H:i:s', $item->create),
                    date('d.m.Y H:i:s', $item->edit),
                    $item->approve_override,
                ];
                fputcsv($output, $row, ';');
            }
        }

        fclose($output);
        exit();
    }

    protected function searchTabletAction() {
        $name = '';
        $filter = [];

        // Handle POST data - support both form data and JSON input
        if($_SERVER['REQUEST_METHOD'] === 'POST') {
            $postData = [];

            // Check for JSON input (Axios sends JSON)
            $inputJSON = file_get_contents('php://input');
            $jsonData = json_decode($inputJSON, true);

            if($jsonData !== null) {
                $postData = $jsonData;
            } else {
                $postData = $_POST;
            }

            if(isset($postData['name'])) {
                $name = $postData['name'];
            }

            if(isset($postData['filter']) && is_array($postData['filter'])) {
                $filter = $postData['filter'];
            }
        }

        $results = ConstructionConsent::searchConstructionConsentBasedOnName($name, $filter);

        self::returnJson([
            'success' => true,
            'count' => count($results),
            'results' => $results
        ]);
    }

    protected function signTabletAction() {
            $JSGlobals = ["BASE_PATH" => self::getUrl(""),
                "DASHBOARD_URL" => self::getUrl("Dashboard"),
                "MFAPPNAME" => MFAPPNAME_SLUG,
                "PAGE_TITLE" => "Zustimmungserklärungen unterzeichnen",
                "PATH" => [
                    ["text" => MFAPPNAME_SLUG, "href" => self::getUrl("Dashboard")],
                    ["text" => "Zustimmungserklärungen unterzeichnen", "href" => self::getUrl("ConstructionConsent", "SignTablet")],
                ],
                "IS_ADMIN" => $this->me->is("Admin"),
            ];

            $this->layout()->set("vueViewName", "ConstructionConsentSignTablet");
            $this->layout()->set("JSGlobals", $JSGlobals);
            $this->layout()->setTemplate("VueViews/Vue");
    }

    protected function signAction() {
        $POST = json_decode(file_get_contents('php://input'), true);

        $owner_id = $POST['owner_id'] ?? null;
        $signature = $POST['signature'] ?? null;
        $signature_name = $POST['signature_name'] ?? null;
        $signature_date = $POST['signature_date'] ?? null;

        if(!$owner_id || !$signature) {
            self::sendError("Invalid request");
        }

        $owner = new ConstructionConsentOwner($owner_id);
        if(!$owner->id) self::sendError("Owner not found");

        if($signature_name) $owner->signature_name = $signature_name;
        if($signature_date) $owner->signature_date = date("U", strtotime($signature_date));
        if($signature) $owner->signature = $signature;

        $owner->status = 'returned';
        $owner->result = 'accepted';

        if(!$owner->save()) self::sendError("Error saving signature");
        self::returnJson(["success" => true]);
    }

    protected function faultyEntriesAction() {
        // Get project ID from request if available
        $projectId = isset($_GET['project_id']) ? intval($_GET['project_id']) : null;
        if ($projectId === null) {
            $this->layout()->setFlash("Projekt nicht gefunden", "error");
            $this->redirect("ConstructionConsentProject");
            return;
        }


        // Get faulty owner entries
        $faultyEntries = ConstructionConsentProject::getFaultyOwnerEntries($projectId);

        $JSGlobals = [
            "BASE_URL" => self::getUrl(""),
            "DASHBOARD_URL" => self::getUrl("Dashboard"),
            "MFAPPNAME" => MFAPPNAME_SLUG,
            "PAGE_TITLE" => "Fehlerhafte Einträge - Zustimmungserklärungen",
            "PATH" => [
                ["text" => MFAPPNAME_SLUG, "href" => self::getUrl("Dashboard")],
                ["text" => "Zustimmungserklärungen", "href" => self::getUrl("ConstructionConsent")],
                ["text" => "Fehlerhafte Einträge", "href" => self::getUrl("ConstructionConsent", "faultyEntries")],
            ],
            "FAULTY_ENTRIES" => $faultyEntries,
            "SELECTED_PROJECT" => $projectId,
            "IS_ADMIN" => $this->me->is("Admin"),
        ];

        $this->layout()->set("vueViewName", "ConstructionConsentFaultyEntries");
        $this->layout()->set("JSGlobals", $JSGlobals);
        $this->layout()->setTemplate("VueViews/Vue");
    }

    private function createFileFromFile(string $sourcePath, string $filename, string $subfolder, string $name = null): ?File {
        if (!file_exists($sourcePath)) {
            $this->log->error(__METHOD__ . ": Source file does not exist at path: " . $sourcePath);
            return null;
        }

        $savePath = rtrim(MFUPLOAD_FILE_SAVE_PATH, '/') . '/' . trim($subfolder, '/');
        if (!is_dir($savePath)) {
            if (!mkdir($savePath, 0777, true)) {
                $this->log->error(__METHOD__ . ": Could not create save directory: " . $savePath);
                return null;
            }
        }

        $store_filename = uniqid() . '-' . basename($filename);
        $destinationPath = $savePath . '/' . $store_filename;

        if (!copy($sourcePath, $destinationPath)) {
            $this->log->error(__METHOD__ . ": Could not copy file from '$sourcePath' to '$destinationPath'");
            return null;
        }

        $fileData = [
            "name" => $name ?? $filename,
            "description" => "Generated signed consent form",
            "filename" => $filename,
            "orig_filename" => $filename,
            "store_filename" => $store_filename,
            "subfolder" => $subfolder,
        ];

        $file = FileModel::create($fileData);
        if (!$file->save()) {
            $this->log->error(__METHOD__ . ": Error saving File Object");
            unlink($destinationPath); // Clean up copied file
            return null;
        }

        $file->mimetype = $file->getMimetype();
        $file->save();

        return $file;
    }

    protected function generateSignedPdfsAction() {
        $this->layout()->setTemplate(null); // No view needed

        $signedOwners = ConstructionConsentOwner::search([
            'add-where' => "AND signature IS NOT NULL AND signature != '' AND signature_name IS NOT NULL AND signature_name != '' AND signature_date IS NOT NULL"
        ]);

        if (empty($signedOwners)) {
            $this->layout()->setFlash("Keine unterschriebenen Erklärungen zum Generieren gefunden.", "info");
            $this->redirect("ConstructionConsent");
        }

        $successCount = 0;
        $errorCount = 0;

        foreach ($signedOwners as $owner) {
            $desiredFilename = "Zustimmungserklaerung-unterschrieben-{$owner->id}.pdf";

            // Check if a signed PDF already exists to avoid re-generation
            $existingFile = ConstructionConsentOwnerFile::getFirst([
                "constructionconsentowner_id" => $owner->id,
                "filename" => $desiredFilename
            ]);
            if ($existingFile) {
                continue;
            }

            $pdfPath = $owner->consent->createConsentFormPdf([$owner]);

            if (!$pdfPath || !file_exists($pdfPath)) {
                $this->log->error("PDF generation failed for owner ID: {$owner->id}");
                $errorCount++;
                continue;
            }

            $file = $this->createFileFromFile(
                $pdfPath,
                $desiredFilename,
                TT_CONSTRUCTIONCONSENT_FILE_UPLOAD_SUBFOLDER,
                $desiredFilename
            );

            unlink($pdfPath);

            if (!$file) {
                $this->log->error("File record creation failed for owner ID: {$owner->id}");
                $errorCount++;
                continue;
            }

            $ccof = ConstructionConsentOwnerFile::create([
                'constructionconsentowner_id' => $owner->id,
                'file_id' => $file->id,
                'filename' => $desiredFilename,
            ]);

            if (!$ccof->save()) {
                $this->log->error("ConstructionConsentOwnerFile record creation failed for owner ID: {$owner->id}. Deleting created file record.");
                $file->file->delete();
                $file->delete();
                $errorCount++;
            } else {
                $successCount++;
            }
        }

        $message = "PDF-Generierung abgeschlossen. $successCount Dokument(e) erfolgreich erstellt.";
        if ($errorCount > 0) {
            $message .= " $errorCount Fehler sind aufgetreten.";
            $this->layout()->setFlash($message, "warning");
        } else {
            $this->layout()->setFlash($message, "success");
        }

        $this->redirect("ConstructionConsent");
    }

    protected function downloadProjectPdfsAction() {
        $projectId = $this->request->project_id;
        if (!is_numeric($projectId) || $projectId < 1) {
            $this->layout()->setFlash("Projekt nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $project = new ConstructionConsentProject($projectId);
        if (!$project->id) {
            $this->layout()->setFlash("Projekt nicht gefunden", "error");
            $this->redirect("ConstructionConsent");
        }

        $consents = ConstructionConsent::search(['project_id' => $projectId]);

        if (empty($consents)) {
            $this->layout()->setFlash("Keine Zustimmungserklärungen für dieses Projekt gefunden.", "info");
            $this->redirect("ConstructionConsent");
        }

        $pdf_files = [];

        foreach ($consents as $consent) {
            $owners = $consent->owners;
            if (empty($owners)) {
                continue;
            }
            foreach ($owners as $owner) {
                $ownerFiles = $owner->files;
                if (empty($ownerFiles)) {
                    continue;
                }
                foreach ($ownerFiles as $ownerFile) {
                    $file = $ownerFile->file;
                    if ($file && strtolower(pathinfo($file->store_filename, PATHINFO_EXTENSION)) === 'pdf') {
                        if ($file->fileExists()) {
                            $filePath = rtrim(MFUPLOAD_FILE_SAVE_PATH, '/') . '/' . trim($file->subfolder, '/') . '/' . $file->store_filename;
                            $pdf_files[] = $filePath;
                        }
                    }
                }
            }
        }

        $pdf_files = array_unique($pdf_files);

        if (empty($pdf_files)) {
            $this->layout()->setFlash("Keine hochgeladenen PDFs für dieses Projekt gefunden.", "error");
            $this->redirect("ConstructionConsent");
        }

        $tempDir = rtrim(BASEDIR, '/') . "/var/temp/";
        $finalPdfPath = $tempDir . "Zustimmungserklaerungen-Projekt-{$projectId}-" . date('Ymd_His') . ".pdf";

        $escaped_files = array_map('escapeshellarg', $pdf_files);
        $pdf_unite_cmd = PDFUNITE_BIN_PATH . " " . implode(' ', $escaped_files) . " " . escapeshellarg($finalPdfPath);

        shell_exec($pdf_unite_cmd);

        if (!file_exists($finalPdfPath) || filesize($finalPdfPath) === 0) {
            $this->log->error("pdfunite command failed or produced an empty file. Command: " . $pdf_unite_cmd);
            $this->layout()->setFlash("PDF-Zusammenführung fehlgeschlagen", "error");
            $this->redirect("ConstructionConsent");
        }

        $downloadName = "Zustimmungserklärungen-{$project->name}-" . date('Y-m-d') . ".pdf";
        $this->sendPdfResponse($finalPdfPath, $downloadName);

        unlink($finalPdfPath);
        exit;
    }

}