<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class UpdateAdbViews extends AbstractMigration
{
    public function up(): void
    {
        if($this->getEnvironment() == "thetool") {
          
        }
        
        if($this->getEnvironment() == "addressdb") {
            $this->query("CREATE SQL SECURITY INVOKER
                VIEW `view_hausnummer_new` AS
                    SELECT
                        `Netzgebiet`.`id` AS `netzgebiet_id`,
                        `Netzgebiet`.`name` AS `netzgebiet`,
                        `Netzgebiet`.`extref` AS `netzgebiet_extref`,
                        `Gemeinde`.`id` AS `gemeinde_id`,
                        `Gemeinde`.`kennziffer` AS `gemeinde_kennziffer`,
                        `Gemeinde`.`code` AS `gemeinde_code`,
                        `Gemeinde`.`name` AS `gemeinde`,
                        `Ortschaft`.`id` AS `ortschaft_id`,
                        `Ortschaft`.`kennziffer` AS `ortschaft_kennziffer`,
                        `Ortschaft`.`name` AS `ortschaft`,
                        `Plz`.`plz` AS `plz`,
                        `Strasse`.`id` AS `strasse_id`,
                        `Strasse`.`kennziffer` AS `strasse_kennziffer`,
                        `Strasse`.`name` AS `strasse`,
                        `Hausnummer`.`id` AS `hausnummer_id`,
                        `Hausnummer`.`status_id` AS `status_id`,
                        `Hausnummer`.`oaid` AS `oaid`,
                        `Hausnummer`.`adrcd` AS `adrcd`,
                        `Hausnummer`.`subcd` AS `subcd`,
                        `Hausnummer`.`hausnummer` AS `hausnummer`,
                        `Hausnummer`.`stiege` AS `hausnummer_stiege`,
                        `Hausnummer`.`extref` AS `hausnummer_extref`,
                        `Hausnummer`.`unit_count` AS `unit_count`,
                        `Hausnummer`.`gps_lat` AS `gps_lat`,
                        `Hausnummer`.`gps_long` AS `gps_long`,
                        `Hausnummer`.`grund_nr` AS `grund_nr`,
                        `Hausnummer`.`gdaeigenschaft` AS `gdaeigenschaft`,
                        `Hausnummer`.`rw` AS `rw`,
                        `Hausnummer`.`hw` AS `hw`,
                        `Hausnummer`.`meridian` AS `meridian`,
                        `Hausnummer`.`rollout` AS `rollout`,
                        `Hausnummer`.`rollout_info` AS `rollout_info`,
                        `Hausnummer`.`freigabe` AS `freigabe`,
                        `Hausnummer`.`visibility` AS `visibility`,
                        `Hausnummer`.`rimo_id` AS `rimo_id`,
                        `Hausnummer`.`rimo_type` AS `rimo_type`,
                        `Hausnummer`.`rimo_ex_state` AS `rimo_ex_state`,
                        `Hausnummer`.`rimo_op_state` AS `rimo_op_state`,
                        `Hausnummer`.`rimo_fcp_name` AS `rimo_fcp_name`,
                        `Hausnummer`.`borderpoint_lat` AS `borderpoint_lat`,
                        `Hausnummer`.`borderpoint_long` AS `borderpoint_long`
                     FROM `Hausnummer` 
                        LEFT JOIN `Netzgebiet` ON (`Hausnummer`.`netzgebiet_id` = `Netzgebiet`.`id`) 
                        LEFT JOIN `Plz` ON (`Plz`.`id` = `Hausnummer`.`plz_id`) 
                        LEFT JOIN `Strasse` ON (`Strasse`.`id` = `Hausnummer`.`strasse_id`) 
                        LEFT JOIN `Gemeinde` ON (`Gemeinde`.`id` = `Strasse`.`gemeinde_id`) 
                        LEFT JOIN `Ortschaft` ON (`Ortschaft`.`id` = `Hausnummer`.`ortschaft_id`)"
            );

            $this->query("CREATE SQL SECURITY INVOKER
                VIEW `view_wohneinheit_new` AS
                    SELECT
                        `Netzgebiet`.`id` AS `netzgebiet_id`,
                        `Netzgebiet`.`name` AS `netzgebiet`,
                        `Netzgebiet`.`extref` AS `netzgebiet_extref`,
                        `Gemeinde`.`id` AS `gemeinde_id`,
                        `Gemeinde`.`kennziffer` AS `gemeinde_kennziffer`,
                        `Gemeinde`.`code` AS `gemeinde_code`,
                        `Gemeinde`.`name` AS `gemeinde`,
                        `Ortschaft`.`id` AS `ortschaft_id`,
                        `Ortschaft`.`kennziffer` AS `ortschaft_kennziffer`,
                        `Ortschaft`.`name` AS `ortschaft`,
                        `Plz`.`plz` AS `plz`,
                        `Strasse`.`id` AS `strasse_id`,
                        `Strasse`.`kennziffer` AS `strasse_kennziffer`,
                        `Strasse`.`name` AS `strasse`,
                        `Hausnummer`.`id` AS `hausnummer_id`,
                        `Hausnummer`.`status_id` AS `status_id`,
                        `Hausnummer`.`oaid` AS `oaid`,
                        `Hausnummer`.`adrcd` AS `adrcd`,
                        `Hausnummer`.`subcd` AS `subcd`,
                        `Hausnummer`.`hausnummer` AS `hausnummer`,
                        `Hausnummer`.`stiege` AS `hausnummer_stiege`,
                        `Hausnummer`.`extref` AS `hausnummer_extref`,
                        `Hausnummer`.`unit_count` AS `unit_count`,
                        `Hausnummer`.`gps_lat` AS `gps_lat`,
                        `Hausnummer`.`gps_long` AS `gps_long`,
                        `Hausnummer`.`grund_nr` AS `grund_nr`,
                        `Hausnummer`.`gdaeigenschaft` AS `gdaeigenschaft`,
                        `Hausnummer`.`rw` AS `rw`,
                        `Hausnummer`.`hw` AS `hw`,
                        `Hausnummer`.`meridian` AS `meridian`,
                        `Hausnummer`.`rollout` AS `rollout`,
                        `Hausnummer`.`rollout_info` AS `rollout_info`,
                        `Hausnummer`.`freigabe` AS `freigabe`,
                        `Hausnummer`.`visibility` AS `visibility`,
                        `Hausnummer`.`rimo_id` AS `hausnummer_rimo_id`,
                        `Hausnummer`.`rimo_type` AS `hausnummer_rimo_type`,
                        `Hausnummer`.`rimo_ex_state` AS `hausnummer_rimo_ex_state`,
                        `Hausnummer`.`rimo_op_state` AS `hausnummer_rimo_op_state`,
                        `Hausnummer`.`rimo_fcp_name` AS `rimo_fcp_name`,
                        `Hausnummer`.`borderpoint_lat` AS `borderpoint_lat`,
                        `Hausnummer`.`borderpoint_long` AS `borderpoint_long`,
                        `Wohneinheit`.`id` AS `wohneinheit_id`,
                        `Wohneinheit`.`status_id` AS `wohneinheit_status_id`,
                        `Wohneinheit`.`oaid` AS `wohneinheit_oaid`,
                        `Wohneinheit`.`extref` AS `wohneinheit_extref`,
                        `Wohneinheit`.`num` AS `num`,
                        `Wohneinheit`.`block` AS `block`,
                        `Wohneinheit`.`stiege` AS `stiege`,
                        `Wohneinheit`.`stock` AS `stock`,
                        `Wohneinheit`.`tuer` AS `tuer`,
                        `Wohneinheit`.`bezeichner` AS `bezeichner`,
                        `Wohneinheit`.`zusatz` AS `zusatz`,
                        `Wohneinheit`.`nutzung` AS `nutzung`,
                        `Wohneinheit`.`rimo_ex_state` AS `wohneinheit_rimo_ex_state`,
                        `Wohneinheit`.`rimo_op_state` AS `wohneinheit_rimo_op_state`,
                        `Wohneinheit`.`patch_cluster` AS `patch_cluster`,
                        `Wohneinheit`.`patch_shelf` AS `patch_shelf`,
                        `Wohneinheit`.`patch_module` AS `patch_module`,
                        `Wohneinheit`.`patch_port` AS `patch_port`
                    FROM `Hausnummer` 
                        LEFT JOIN `Netzgebiet` ON (`Hausnummer`.`netzgebiet_id` = `Netzgebiet`.`id`)
                        LEFT JOIN `Plz` ON (`Plz`.`id` = `Hausnummer`.`plz_id`)
                        LEFT JOIN `Strasse` ON (`Strasse`.`id` = `Hausnummer`.`strasse_id`)
                        LEFT JOIN `Ortschaft` ON (`Ortschaft`.`id` = `Hausnummer`.`ortschaft_id`)
                        LEFT JOIN `Gemeinde` ON (`Gemeinde`.`id` = `Strasse`.`gemeinde_id`)
                        LEFT JOIN `Wohneinheit` ON (`Wohneinheit`.`hausnummer_id` = `Hausnummer`.`id`)"
            );

            $this->query("RENAME TABLE `view_hausnummer` TO `view_hausnummer_old`");
            $this->query("RENAME TABLE `view_hausnummer_new` TO `view_hausnummer`");

            $this->query("RENAME TABLE `view_wohneinheit` TO `view_wohneinheit_old`");
            $this->query("RENAME TABLE `view_wohneinheit_new` TO `view_wohneinheit`");
        }
    }

    public function down(): void
    {
        if($this->getEnvironment() == "thetool") {
          
        }
        
        if($this->getEnvironment() == "addressdb") {
          
        }
    }
}