<?php

global $g_comp_database, $g_comp_database_system;

// Set migrationId
$migrationIdentifier = 'migrate_status_fields';

// Check whether migration was executed before
if ($this->is_migration_done($migrationIdentifier)) {
    $g_migration_log[] = '<span class="bold">Stats field migration has already been processed</span>';
} else {
    $g_migration_log[] = '<span class="bold">Unify status fields to match specific format...</span>';

    $dao = new \isys_cmdb_dao($g_comp_database);

    $missingTables = 0;
    $missingTableFields = 0;
    $migratedTables = 0;
    $notMigratedTables = 0;
    $errors = 0;

    // This list comes from our setup-dump it should represent the base-set of status fields.
    $statusFields = [
        'isys_ac_air_quantity_unit__status',
        'isys_ac_refrigerating_capacity_unit__status',
        'isys_ac_type__status',
        'isys_access_type__status',
        'isys_account__status',
        'isys_agent__status',
        'isys_application_manufacturer__status',
        'isys_auth__status',
        'isys_backup_cycle__status',
        'isys_backup_type__status',
        'isys_business_unit__status',
        'isys_cable_colour__status',
        'isys_cable_occupancy__status',
        'isys_cable_type__status',
        'isys_calendar__status',
        'isys_catd_drive_type__status',
        'isys_catg_access_list__status',
        'isys_catg_accounting_cost_unit__status',
        'isys_catg_accounting_list__status',
        'isys_catg_accounting_procurement__status',
        'isys_catg_address_list__status',
        'isys_catg_aircraft_list__status',
        'isys_catg_ap_controller_list__status',
        'isys_catg_application_list__status',
        'isys_catg_application_priority__status',
        'isys_catg_application_type__status',
        'isys_catg_assigned_cards_list__status',
        'isys_catg_assigned_subscriptions_list__status',
        'isys_catg_assigned_users_list__status',
        'isys_catg_audit_list__status',
        'isys_catg_audit_type__status',
        'isys_catg_backup_list__status',
        'isys_catg_cable_list__status',
        'isys_catg_cards_list__status',
        'isys_catg_cards_list_2_isys_obj__status',
        'isys_catg_certificate_list__status',
        'isys_catg_cloud_subscriptions_list__status',
        'isys_catg_cluster_adm_service_list__status',
        'isys_catg_cluster_list__status',
        'isys_catg_cluster_members_list__status',
        'isys_catg_cluster_service_list__status',
        'isys_catg_computing_resources_list__status',
        'isys_catg_connection_endpoint_list__status',
        'isys_catg_connector_list__status',
        'isys_catg_contact_list__status',
        'isys_catg_contract_assignment_list__status',
        'isys_catg_controller_list__status',
        'isys_catg_cpu_list__status',
        'isys_catg_cpu_manufacturer__status',
        'isys_catg_cpu_type__status',
        'isys_catg_custom_fields_list__status',
        'isys_catg_database_assignment_list__status',
        'isys_catg_database_list__status',
        'isys_catg_database_sa_list__status',
        'isys_catg_database_table_list__status',
        'isys_catg_drive_list__status',
        'isys_catg_emergency_plan_list__status',
        'isys_catg_fc_port_list__status',
        'isys_catg_fiber_lead_list__status',
        'isys_catg_file_list__status',
        'isys_catg_formfactor_list__status',
        'isys_catg_formfactor_type__status',
        'isys_catg_global_category__status',
        'isys_catg_global_list__status',
        'isys_catg_graphic_list__status',
        'isys_catg_guest_systems_list__status',
        'isys_catg_hba_list__status',
        'isys_catg_identifier_list__status',
        'isys_catg_identifier_type__status',
        'isys_catg_image_list__status',
        'isys_catg_images_list__status',
        'isys_catg_invoice_list__status',
        'isys_catg_ip_list__status',
        'isys_catg_its_components_list__status',
        'isys_catg_its_type_list__status',
        'isys_catg_jdisc_ca_list__status',
        'isys_catg_jdisc_device_information_list__status',
        'isys_catg_last_login_user_list__status',
        'isys_catg_ldap_dn_list__status',
        'isys_catg_ldevclient_list__status',
        'isys_catg_ldevserver_list__status',
        'isys_catg_location_list__status',
        'isys_catg_log_port_list__status',
        'isys_catg_log_port_list_2_isys_obj__status',
        'isys_catg_logb_list__status',
        'isys_catg_logical_unit_list__status',
        'isys_catg_mail_addresses_list__status',
        'isys_catg_manual_list__status',
        'isys_catg_memory_list__status',
        'isys_catg_model_list__status',
        'isys_catg_monitoring_list__status',
        'isys_catg_net_connector_list__status',
        'isys_catg_net_type_list__status',
        'isys_catg_net_zone_options_list__status',
        'isys_catg_netp_list__status',
        'isys_catg_overview_list__status',
        'isys_catg_password_list__status',
        'isys_catg_pc_list__status',
        'isys_catg_planning_list__status',
        'isys_catg_port_list__status',
        'isys_catg_power_supplier_list__status',
        'isys_catg_qinq_list__status',
        'isys_catg_raid_list__status',
        'isys_catg_relation_list__status',
        'isys_catg_rm_controller_list__status',
        'isys_catg_sanpool_list__status',
        'isys_catg_service_list__status',
        'isys_catg_share_access_list__status',
        'isys_catg_shares_list__status',
        'isys_catg_sim_card_list__status',
        'isys_catg_sim_list__status',
        'isys_catg_sla_list__status',
        'isys_catg_smartcard_certificate_list__status',
        'isys_catg_snmp_list__status',
        'isys_catg_soa_components_list__status',
        'isys_catg_soa_stacks_list__status',
        'isys_catg_sound_list__status',
        'isys_catg_stack_member_list__status',
        'isys_catg_stacking_list__status',
        'isys_catg_stor_list__status',
        'isys_catg_support_entitlement_list__status',
        'isys_catg_telephone_fax_list__status',
        'isys_catg_tsi_service_list__status',
        'isys_catg_ui_list__status',
        'isys_catg_vehicle_list__status',
        'isys_catg_version_list__status',
        'isys_catg_virtual_device_list__status',
        'isys_catg_virtual_host_list__status',
        'isys_catg_virtual_list__status',
        'isys_catg_virtual_machine_list__status',
        'isys_catg_virtual_switch_list__status',
        'isys_catg_voip_phone_line_list__status',
        'isys_catg_voip_phone_list__status',
        'isys_catg_vrrp_list__status',
        'isys_catg_vrrp_member_list__status',
        'isys_catg_wan_list__status',
        'isys_cats_ac_list__status',
        'isys_cats_access_point_list__status',
        'isys_cats_app_variant_list__status',
        'isys_cats_application_list__status',
        'isys_cats_building_list__status',
        'isys_cats_chassis_list__status',
        'isys_cats_chassis_slot_list__status',
        'isys_cats_chassis_view_list__status',
        'isys_cats_client_list__status',
        'isys_cats_contract_list__status',
        'isys_cats_cp_contract_list__status',
        'isys_cats_database_access_list__status',
        'isys_cats_database_gateway_list__status',
        'isys_cats_database_instance_list__status',
        'isys_cats_database_objects_list__status',
        'isys_cats_database_schema_list__status',
        'isys_cats_dbms_list__status',
        'isys_cats_emergency_plan_list__status',
        'isys_cats_enclosure_list__status',
        'isys_cats_eps_list__status',
        'isys_cats_eps_type__status',
        'isys_cats_file_list__status',
        'isys_cats_group_list__status',
        'isys_cats_group_type_list__status',
        'isys_cats_krypto_card_list__status',
        'isys_cats_layer2_net_assigned_ports_list__status',
        'isys_cats_layer2_net_list__status',
        'isys_cats_lic_list__status',
        'isys_cats_mobile_phone_list__status',
        'isys_cats_monitor_list__status',
        'isys_cats_net_dhcp_list__status',
        'isys_cats_net_ip_addresses_list__status',
        'isys_cats_net_list__status',
        'isys_cats_net_zone_list__status',
        'isys_cats_organization_list__status',
        'isys_cats_pdu_branch_list__status',
        'isys_cats_pdu_list__status',
        'isys_cats_person_group_list__status',
        'isys_cats_person_list__status',
        'isys_cats_prt_emulation__status',
        'isys_cats_prt_list__status',
        'isys_cats_prt_paper__status',
        'isys_cats_prt_type__status',
        'isys_cats_relpool_list__status',
        'isys_cats_relpool_list_2_isys_obj__status',
        'isys_cats_replication_list__status',
        'isys_cats_replication_partner_list__status',
        'isys_cats_room_list__status',
        'isys_cats_router_list__status',
        'isys_cats_san_list__status',
        'isys_cats_san_zoning_list__status',
        'isys_cats_service_list__status',
        'isys_cats_switch_fc_list__status',
        'isys_cats_switch_net_list__status',
        'isys_cats_tapelib_list__status',
        'isys_cats_ups_list__status',
        'isys_cats_virtual__status',
        'isys_cats_virtual_list__status',
        'isys_cats_wan_list__status',
        'isys_cats_ws_net_type_list__status',
        'isys_certificate_type__status',
        'isys_chassis_connector_type__status',
        'isys_chassis_role__status',
        'isys_client_type__status',
        'isys_cluster_type__status',
        'isys_cmdb_status__status',
        'isys_command_queue__status',
        'isys_connection_type__status',
        'isys_contact__status',
        'isys_contact_tag__status',
        'isys_contract_end_type__status',
        'isys_contract_notice_period_type__status',
        'isys_contract_payment_period__status',
        'isys_contract_reaction_rate__status',
        'isys_contract_status__status',
        'isys_contract_type__status',
        'isys_controller_manufacturer__status',
        'isys_controller_model__status',
        'isys_controller_type__status',
        'isys_cp_contract_type__status',
        'isys_currency__status',
        'isys_database_instance_type__status',
        'isys_database_objects__status',
        'isys_database_schema__status',
        'isys_dbms__status',
        'isys_dependency__status',
        'isys_depth_unit__status',
        'isys_dialog_plus_custom__status',
        'isys_fc_port_medium__status',
        'isys_fc_port_type__status',
        'isys_fiber_category__status',
        'isys_fiber_wave_length__status',
        'isys_file_category__status',
        'isys_file_physical__status',
        'isys_file_version__status',
        'isys_filesystem_type__status',
        'isys_frequency_unit__status',
        'isys_graphic_manufacturer__status',
        'isys_guarantee_period_unit__status',
        'isys_hba_type__status',
        'isys_iface_manufacturer__status',
        'isys_iface_model__status',
        'isys_import_type__status',
        'isys_installation_type__status',
        'isys_interface__status',
        'isys_interval__status',
        'isys_ip_assignment__status',
        'isys_ipv6_assignment__status',
        'isys_ipv6_scope__status',
        'isys_its_type__status',
        'isys_jdisc_ca_type__status',
        'isys_jdisc_device_type__status',
        'isys_jdisc_status_list__status',
        'isys_layer2_iphelper_type__status',
        'isys_layer2_net_subtype__status',
        'isys_layer2_net_type__status',
        'isys_ldap__status',
        'isys_ldap_directory__status',
        'isys_ldev_multipath__status',
        'isys_logbook__status',
        'isys_logbook_configuration__status',
        'isys_logbook_event__status',
        'isys_logbook_event_class__status',
        'isys_logbook_lc_parameter__status',
        'isys_logbook_level__status',
        'isys_logbook_reason__status',
        'isys_logbook_source__status',
        'isys_maintenance_contract_type__status',
        'isys_maintenance_reaction_rate__status',
        'isys_maintenance_status__status',
        'isys_memory_manufacturer__status',
        'isys_memory_title__status',
        'isys_memory_type__status',
        'isys_memory_unit__status',
        'isys_model_manufacturer__status',
        'isys_model_title__status',
        'isys_module__status',
        'isys_monitor_resolution__status',
        'isys_monitor_type__status',
        'isys_monitor_unit__status',
        'isys_monitoring_export_config__status',
        'isys_net_dhcp_type__status',
        'isys_net_dhcpv6_type__status',
        'isys_net_dns_domain__status',
        'isys_net_dns_server__status',
        'isys_net_protocol__status',
        'isys_net_protocol_layer_5__status',
        'isys_net_type__status',
        'isys_net_type_title__status',
        'isys_netp_ifacel__status',
        'isys_netp_ifacel_standard__status',
        'isys_network_provider__status',
        'isys_netx_ifacel_type__status',
        'isys_notification__status',
        'isys_notification_type__status',
        'isys_obj__status',
        'isys_obj_type__status',
        'isys_obj_type_group__status',
        'isys_organisation_intern_iop__status',
        'isys_p_mode__status',
        'isys_pc_manufacturer__status',
        'isys_pc_model__status',
        'isys_pc_title__status',
        'isys_pobj_type__status',
        'isys_port_duplex__status',
        'isys_port_mode__status',
        'isys_port_negotiation__status',
        'isys_port_speed__status',
        'isys_port_standard__status',
        'isys_port_type__status',
        'isys_pos_gps__status',
        'isys_power_connection_type__status',
        'isys_power_fuse_ampere__status',
        'isys_power_fuse_type__status',
        'isys_purpose__status',
        'isys_qr_code_configuration__status',
        'isys_raid_type__status',
        'isys_relation_type__status',
        'isys_replication_mechanism__status',
        'isys_replication_type__status',
        'isys_report_category__status',
        'isys_room_type__status',
        'isys_routing_protocol__status',
        'isys_san_capacity_unit__status',
        'isys_service_alias__status',
        'isys_service_category__status',
        'isys_service_manufacturer__status',
        'isys_service_type__status',
        'isys_site__status',
        'isys_sla_service_level__status',
        'isys_snmp_community__status',
        'isys_sound_manufacturer__status',
        'isys_stor_con_type__status',
        'isys_stor_lto_type__status',
        'isys_stor_manufacturer__status',
        'isys_stor_model__status',
        'isys_stor_raid_level__status',
        'isys_stor_type__status',
        'isys_stor_unit__status',
        'isys_switch_role__status',
        'isys_switch_spanning_tree__status',
        'isys_tag__status',
        'isys_tapelib_type__status',
        'isys_telephone_fax_type__status',
        'isys_telephone_rate__status',
        'isys_temp_unit__status',
        'isys_tierclass__status',
        'isys_tree_group__status',
        'isys_tts_type__status',
        'isys_ui_con_type__status',
        'isys_ui_plugtype__status',
        'isys_unit_of_time__status',
        'isys_ups_battery_type__status',
        'isys_ups_type__status',
        'isys_user_locale__status',
        'isys_user_mydoit__status',
        'isys_user_session__status',
        'isys_virtual_network_type__status',
        'isys_virtual_storage_type__status',
        'isys_vlan_management_protocol__status',
        'isys_vm_type__status',
        'isys_voip_phone_button_template__status',
        'isys_voip_phone_softkey_template__status',
        'isys_volume_unit__status',
        'isys_vrrp_type__status',
        'isys_wan_capacity_unit__status',
        'isys_wan_role__status',
        'isys_wan_type__status',
        'isys_wato_folder__status',
        'isys_weight_unit__status',
        'isys_weighting__status',
        'isys_wlan_auth__status',
        'isys_wlan_channel__status',
        'isys_wlan_encryption__status',
        'isys_wlan_function__status',
        'isys_wlan_standard__status',
        'isysgui_catg__status',
        'isysgui_catg_custom__status',
        'isysgui_cats__status',
    ];

    foreach ($statusFields as $statusField) {
        [$table] = explode('__', $statusField);

        if (!$dao->table_exists($table)) {
            $missingTables++;
            continue;
        }

        $field = $dao
            ->getTableFieldInfo($table, $statusField)
            ->get_row();

        if (empty($field)) {
            $missingTableFields++;
            continue;
        }

        if (str_contains(strtolower($field['Type']), 'tinyint') && strtolower($field['Null']) === 'no' && in_array($field['Default'], ['1', '2'])) {
            $notMigratedTables++;
            continue;
        }

        try {
            $defaultValue = $field['Default'] == '2' ? 2 : 1;

            $setDefaultValuesQuery = "UPDATE {$table} SET {$statusField} = {$defaultValue} WHERE {$statusField} IS NULL;";
            $dao->update($setDefaultValuesQuery);
            $dao->apply_update();

            $updateQuery = "ALTER TABLE {$table} MODIFY {$statusField} TINYINT unsigned NOT NULL DEFAULT {$defaultValue};";
            $dao->update($updateQuery);
            $dao->apply_update();

            $g_migration_log[] = '<span class="indent">Succesfully unified field "' . $statusField . '" in table "' . $table . '".';
            $migratedTables++;
        } catch (Exception $e) {
            $g_migration_log[] = '<span class="indent text-red">Error: ' . $e->getMessage() . '</span>';
            $errors++;
        }
    }

    if ($missingTables > 0) {
        $g_migration_log[] = '<span class="bold">' . $missingTables . ' tables did not exist in your database (this is not a problem).</span>';
    }

    if ($missingTableFields > 0) {
        $g_migration_log[] = '<span class="bold">' . $missingTableFields . ' status fields did not exist in their table.</span>';
    }

    if ($migratedTables > 0) {
        $g_migration_log[] = '<span class="bold text-green">Migrated ' . $migratedTables . ' tables.</span>';
    }

    $g_migration_log[] = '<span class="bold text-green">' . $notMigratedTables . ' tables did not need to be updated.</span>';

    if ($errors > 0) {
        $g_migration_log[] = '<span class="indent bold text-red">' . $errors . ' tables could not be updated.</span>';
    }

    $g_migration_log[] = '<span class="bold">Migration finished!</span>';

    $this->migration_done($migrationIdentifier);
}
