<?php

use idoit\Component\Helper\Ip;

/**
 * i-doit
 *
 * DAO: global category for physical network ports
 *
 * @package     i-doit
 * @subpackage  CMDB_Categories
 * @author      Leonard Fischer <lfischer@i-doit.org>
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 */
class isys_cmdb_dao_category_g_network_port_overview extends isys_cmdb_dao_category_global
{
    /**
     * Category's name. Will be used for the identifier, constant, main table, and many more.
     *
     * @var  string
     */
    protected $m_category = 'network_port_overview';

    /**
     * This variable holds the language constant of the current category.
     *
     * @var string
     */
    protected $categoryTitle = 'LC__CMDB__CATG__NETWORK_PORT_OVERVIEW';

    /**
     * Category's constant
     *
     * @var    string
     * @fixme  No standard behavior!
     */
    protected $m_category_const = 'C__CATG__NETWORK_PORT_OVERVIEW';

    /**
     * Category's identifier
     *
     * @var    integer
     * @fixme  No standard behavior!
     */
    protected $m_category_id;

    /**
     * Is category multi-valued or single-valued?
     *
     * @var  boolean
     */
    protected $m_multivalued = false;

    /**
     * Main table where properties are stored persistently
     *
     * @var    string
     * @fixme  No standard behavior!
     */
    protected $m_table = 'isys_catg_port_list';

    /**
     * Category's template.
     *
     * @var    string
     * @fixme  No standard behavior!
     */
    protected $m_tpl = 'catg__port_overview.tpl';

    /**
     * Helper function so that we don´t have to change the parameter list of the get_data
     *
     * @param int|null       $p_catg_list_id
     * @param int|array|null $p_obj_id
     * @param string         $p_condition
     * @param string|null    $p_filter
     * @param int|null       $p_status
     * @param string         $p_order_by
     *
     * @return isys_component_dao_result
     * @throws Exception
     * @throws isys_exception_database
     */
    public function get_port_overview_result($p_catg_list_id = null, $p_obj_id = null, $p_condition = '', $p_filter = null, $p_status = null, $p_order_by = '')
    {
        $l_sql = "SELECT isys_catg_ip_list.*, isys_catg_netp_list__title, isys_catg_hba_list__title, isys_obj.*, isys_catg_connector_list.*, isys_catg_port_list.*
			FROM isys_catg_port_list
			LEFT JOIN isys_obj ON isys_catg_port_list__isys_obj__id = isys_obj__id
			LEFT JOIN isys_catg_ip_list ON isys_catg_ip_list__isys_catg_port_list__id = isys_catg_port_list__id
			LEFT JOIN isys_catg_netp_list ON isys_catg_port_list__isys_catg_netp_list__id = isys_catg_netp_list__id
			LEFT JOIN isys_catg_hba_list ON isys_catg_port_list__isys_catg_hba_list__id = isys_catg_hba_list__id
			LEFT JOIN isys_catg_connector_list ON isys_catg_connector_list__id = isys_catg_port_list__isys_catg_connector_list__id
			WHERE TRUE " . $p_condition . " " . $this->prepare_filter($p_filter) . " ";

        if ($p_obj_id !== null) {
            $l_sql .= $this->get_object_condition($p_obj_id);
        }

        if ($p_status !== null) {
            $l_sql .= " AND isys_catg_port_list__status = " . $this->convert_sql_int($p_status) . " ";
        }

        $l_sql .= " GROUP BY isys_catg_port_list__id ";

        if ($p_order_by) {
            $l_order = ' ORDER BY ' . $p_order_by;
        } else {
            $l_order = '';
        }

        return $this->retrieve($l_sql . $l_order);
    }

    /**
     * Loads the data for the port overview (will be called by ajax, through the "isys_ajax_handler_ports" class).
     *
     * @param int $p_obj_id
     * @param int $p_limit
     * @param int $p_offset
     * @return array
     * @throws isys_exception_database
     */
    public function get_port_overview($p_obj_id, $p_limit = null, $p_offset = null)
    {
        $l_data = [];
        $l_layer2_dao = isys_cmdb_dao_category_s_layer2_net::instance($this->get_database_component());
        $l_layer2_assigned_ports_dao = isys_cmdb_dao_category_s_layer2_net_assigned_ports::instance($this->get_database_component());
        $l_connector_dao = isys_cmdb_dao_category_g_connector::instance($this->get_database_component());

        $emptyValue = isys_tenantsettings::get('gui.empty_value', '-');

        $l_port_res = $this->get_port_overview_result(null, $p_obj_id, '', null, C__RECORD_STATUS__NORMAL);

        $l_port_mode_res = isys_cmdb_dao_category_g_network_port::instance($this->get_database_component())
            ->get_port_modes();

        while ($l_port_mode_row = $l_port_mode_res->get_row()) {
            $l_port_modes[$l_port_mode_row['isys_port_mode__id']] = $l_port_mode_row['isys_port_mode__title'];
        }

        $l_counter = 1;
        while ($l_port_row = $l_port_res->get_row()) {
            // This "should" not happen, but we'll never know...
            if ($l_port_row['isys_catg_connector_list__id'] === null) {
                continue;
            }

            $portEntryId = (int)$l_port_row['isys_catg_port_list__id'];

            // Here we assign the variable, which will be passed to the template later on.
            $l_first = $l_last = [
                'interface'  => $emptyValue,
                'layer2'     => $emptyValue,
                'layer3'     => $emptyValue,
                'ip_address' => $emptyValue,
                'mode'       => $emptyValue,
                'link'       => $emptyValue
            ];

            $l_last_conn = null;

            $l_connector_res = $l_connector_dao->get_first_and_last_cable_run_object($l_port_row['isys_catg_connector_list__id']);

            $l_link = isys_helper_link::create_url([
                C__CMDB__GET__OBJECT   => $p_obj_id,
                C__CMDB__GET__VIEWMODE => C__CMDB__VIEW__CATEGORY,
                C__CMDB__GET__TREEMODE => C__CMDB__VIEW__TREE_OBJECT,
                C__CMDB__GET__CATG     => defined_or_default('C__CATG__NETWORK_PORT'),
                C__CMDB__GET__CATLEVEL => $portEntryId
            ]);

            preg_match('~.*?(\d+).*?~', $l_port_row['isys_catg_port_list__title'], $l_match);

            if (isset($l_match[1])) {
                $l_key = str_pad($l_match[1], 5, 0, STR_PAD_LEFT);
            } else {
                $l_key = $l_port_row['isys_catg_port_list__title'];
            }

            $l_key .= $l_counter++;

            $l_first['sort'] = $l_port_row['isys_catg_port_list__title'];

            $l_first['link'] = '<a href="' . $l_link . '">' .
                ($l_port_row['isys_catg_port_list__title'] !== '' ? $l_port_row['isys_catg_port_list__title'] : $emptyValue) . '</a>';

            if (!empty($l_port_row['isys_catg_netp_list__title'])) {
                $l_first['interface'] = ($l_port_row['isys_catg_netp_list__title']);
            } elseif (!empty($l_port_row['isys_catg_hba_list__title'])) {
                $l_first['interface'] = ($l_port_row['isys_catg_hba_list__title']);
            }

            if (array_key_exists($l_port_row['isys_catg_port_list__isys_port_mode__id'], $l_port_modes)) {
                $l_first['mode'] = ($l_port_modes[$l_port_row['isys_catg_port_list__isys_port_mode__id']]);
            }

            if ($l_connector_res['first']['CONNECTOR_ID'] != $l_connector_res['last']['CONNECTOR_ID']) {
                $l_last_conn = $l_connector_dao->get_data($l_connector_res['last']['CONNECTOR_ID'])
                    ->get_row();

                $l_link = isys_helper_link::create_url([
                    C__CMDB__GET__OBJECT   => $l_last_conn['isys_obj__id'],
                    C__CMDB__GET__VIEWMODE => C__CMDB__VIEW__CATEGORY,
                    C__CMDB__GET__TREEMODE => C__CMDB__VIEW__TREE_OBJECT,
                    C__CMDB__GET__CATG     => defined_or_default('C__CATG__NETWORK_PORT'),
                    C__CMDB__GET__CATLEVEL => $portEntryId
                ]);

                $l_last['link'] = '<a href="' . $l_link . '">' . (isset($l_connector_res['last']['CONNECTOR_TITLE']) &&
                    $l_connector_res['last']['CONNECTOR_TITLE'] !== '' ? $l_connector_res['last']['CONNECTOR_TITLE'] : $emptyValue) .
                    '</a>';

                $l_last['obj_link'] = '<a href="' . isys_helper_link::create_url([C__CMDB__GET__OBJECT => $l_last_conn['isys_obj__id']]) . '">' .
                    (isset($l_last_conn['isys_obj__title']) &&
                    $l_last_conn['isys_obj__title'] !== '' ? $l_last_conn['isys_obj__title'] : $emptyValue) . '</a>';
            }

            // Get the assigned layer2 nets.
            $l_layer2 = [];
            $l_layer2_res = $l_layer2_assigned_ports_dao->get_data(
                null,
                null,
                'AND isys_catg_port_list.isys_catg_port_list__isys_obj__id = ' . $this->convert_sql_id($p_obj_id) . '
					AND isys_catg_port_list.isys_catg_port_list__id = ' . $portEntryId .
                (isys_tenantsettings::get('cmdb.limits.port-overview-default-vlan-only', 0) ? ' AND isys_cats_layer2_net_assigned_ports_list__default = 1' : ''),
                null,
                C__RECORD_STATUS__NORMAL
            );

            $i = 0;
            $l_max = isys_tenantsettings::get('cmdb.limits.port-lists-vlans', 5);
            while ($l_layer2_row = $l_layer2_res->get_row()) {
                if ($i++ === $l_max) {
                    $l_layer2[] = '...';
                    break;
                }

                $l_layer2_obj_row = $l_layer2_dao->get_data(null, $l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id'], '', null, C__RECORD_STATUS__NORMAL)
                    ->get_row();

                // We prepare a user-friendly link.
                $l_layer2[] = '<a href="' . isys_helper_link::create_url([C__CMDB__GET__OBJECT => $l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id']]) .
                    '"' . ($l_layer2_row['isys_cats_layer2_net_assigned_ports_list__default'] ? ' class="bold"' : '') . '>' .
                    $l_layer2_assigned_ports_dao->get_obj_name_by_id_as_string($l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id']) . ' (' .
                    $l_layer2_obj_row['isys_cats_layer2_net_list__ident'] . ')' . '</a>';
            }

            $l_first['layer2'] = implode('<br />', array_unique($l_layer2));

            $l_first['layer2'] = (empty($l_first['layer2']) ? $emptyValue : $l_first['layer2']);

            // Get the host-address for this port.
            $l_first['ip_address'] = $emptyValue;

            // @see ID-12265 Show all assigned IP addresses.
            $hostAddressResult = $this->getHostaddressData($portEntryId);

            if ($hostAddressResult !== null) {
                $l_first['layer3'] = $hostAddressResult[0];
                $l_first['ip_address'] = $hostAddressResult[1];
            }

            if (!empty($l_last_conn)) {
                $l_port_row = $this->get_data(
                    null,
                    $l_last_conn['isys_obj__id'],
                    'AND isys_catg_port_list__isys_catg_connector_list__id = ' . $this->convert_sql_id($l_last_conn['isys_catg_connector_list__id'])
                )
                    ->get_row();

                $targetPortEntryId = (int)$l_port_row['isys_catg_port_list__id'];

                if (!empty($l_port_row['isys_catg_netp_list__title'])) {
                    $l_last['interface'] = $l_port_row['isys_catg_netp_list__title'];
                } elseif (!empty($l_port_row['isys_catg_hba_list__title'])) {
                    $l_last['interface'] = $l_port_row['isys_catg_hba_list__title'];
                }
                $l_last['mode'] = array_key_exists($l_port_row['isys_catg_port_list__isys_port_mode__id'], $l_port_modes)
                    ? $l_port_modes[$l_port_row['isys_catg_port_list__isys_port_mode__id']]
                    : $emptyValue;

                // @see ID-12265 Show all assigned IP addresses.
                $hostAddressResult = $this->getHostaddressData($targetPortEntryId);

                if ($hostAddressResult !== null) {
                    $l_last['layer3'] = $hostAddressResult[0];
                    $l_last['ip_address'] = $hostAddressResult[1];
                }

                // Get the assigned layer2 nets.
                $l_layer2 = [];
                $l_layer2_res = $l_layer2_assigned_ports_dao->get_data(
                    null,
                    null,
                    'AND isys_catg_port_list.isys_catg_port_list__isys_obj__id = ' . $this->convert_sql_id($l_port_row['isys_obj__id']) . ' ' .
                    'AND isys_catg_port_list.isys_catg_port_list__id = ' . $targetPortEntryId,
                    null,
                    C__RECORD_STATUS__NORMAL
                );

                while ($l_layer2_row = $l_layer2_res->get_row()) {
                    $l_layer2_obj_row = $l_layer2_dao->get_data(
                        null,
                        $l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id'],
                        '',
                        null,
                        C__RECORD_STATUS__NORMAL
                    )
                        ->get_row();

                    // We prepare a user-friendly link.
                    $l_layer2[] = '<a href="' .
                        isys_helper_link::create_url([C__CMDB__GET__OBJECT => $l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id']]) . '"' .
                        ($l_layer2_row['isys_cats_layer2_net_assigned_ports_list__default'] ? ' class="bold"' : '') . '>' .
                        $l_layer2_assigned_ports_dao->get_obj_name_by_id_as_string($l_layer2_row['isys_cats_layer2_net_assigned_ports_list__isys_obj__id']) . ' ' . '(' .
                        $l_layer2_obj_row['isys_cats_layer2_net_list__ident'] . ')' . '</a>';
                }

                $l_last['layer2'] = implode('<br />', array_unique($l_layer2));

                $l_last['layer2'] = (empty($l_last['layer2']) ? $emptyValue : $l_last['layer2']);
            }

            $l_data[$l_key] = [
                '_first' => $l_first,
                '_last'  => $l_last
            ];
        }

        if (isys_tenantsettings::get('gui.nat-sort.port-list', 1)) {
            usort($l_data, function ($a, $b) {
                return strnatcasecmp($a['_first']['sort'], $b['_first']['sort']);
            });
        }

        return array_values($l_data);
    }

    /**
     * @param int $portEntryId
     * @return array|null
     * @throws isys_exception_database
     */
    private function getHostaddressData(int $portEntryId): ?array
    {
        $dao = isys_cmdb_dao_category_s_net_ip_addresses::instance($this->get_database_component());

        $assignedIpQuery = "SELECT
                isys_cats_net_ip_addresses_list__id AS id,
                isys_cats_net_ip_addresses_list__title AS address,
                isys_obj__title AS netTitle,
                isys_cats_net_list__isys_net_type__id netType,
                isys_cats_net_list__address AS netAddress,
                isys_cats_net_list__cidr_suffix AS netCidr
            FROM isys_catg_ip_list
            INNER JOIN isys_cats_net_ip_addresses_list ON isys_catg_ip_list__isys_cats_net_ip_addresses_list__id = isys_cats_net_ip_addresses_list.isys_cats_net_ip_addresses_list__id
            INNER JOIN isys_obj ON isys_obj__id = isys_cats_net_ip_addresses_list__isys_obj__id
            INNER JOIN isys_cats_net_list ON isys_cats_net_list__isys_obj__id = isys_cats_net_ip_addresses_list__isys_obj__id
            WHERE isys_catg_ip_list__isys_catg_port_list__id = {$portEntryId};";

        $result = $dao->retrieve($assignedIpQuery);

        if (count($result) === 0) {
            return null;
        }

        $layer3 = [];
        $ipAddress = [];

        while ($hostAddressRow = $result->get_row()) {
            if ($hostAddressRow['netType'] == defined_or_default('C__CATS_NET_TYPE__IPV6')) {
                $hostAddressRow['netAddress'] = Ip::validate_ipv6($hostAddressRow['netAddress'], true);
            }

            $layer3[] = "{$hostAddressRow['netTitle']} ({$hostAddressRow['netAddress']} / {$hostAddressRow['netCidr']})";
            $ipAddress[] = $hostAddressRow['address'];
        }

        return [implode('<br />', $layer3), implode('<br />', $ipAddress)];
    }

    /**
     * Get-Count method for highlighting the category.
     *
     * @param   integer $p_obj_id
     *
     * @see     ID-2721
     * @return  integer
     */
    public function get_count($p_obj_id = null)
    {
        return count($this->get_port_overview_result(null, $p_obj_id, '', null, C__RECORD_STATUS__NORMAL));
    }

    /**
     * Method for retrieving data - Strongly modified SQL taken from isys_cmdb_dao_category_g_network_port->get_ports().
     *
     * @param   integer $p_catg_list_id
     * @param   mixed   $p_obj_id
     * @param   string  $p_condition
     * @param   mixed   $p_filter
     * @param   integer $p_status
     *
     * @return  isys_component_dao_result
     * @author  Leonard Fischer <lfischer@i-doit.org>
     */
    public function get_data($p_catg_list_id = null, $p_obj_id = null, $p_condition = '', $p_filter = null, $p_status = null)
    {
        return $this->get_port_overview_result($p_catg_list_id, $p_obj_id, $p_condition, $p_filter, $p_status);
    }

    /**
     * Get UI method.
     *
     * @return  isys_cmdb_ui_category_g_network_port_overview
     * @author  Leonard Fischer <lfischer@i-doit.org>
     */
    public function &get_ui()
    {
        return new isys_cmdb_ui_category_g_network_port_overview(isys_application::instance()->template);
    }

    /**
     * Method for returning the properties.
     *
     * @return  array
     */
    protected function properties()
    {
        return [];
    }

    /**
     * isys_cmdb_dao_category_g_network_port_overview constructor.
     *
     * @param isys_component_database $p_db
     */
    public function __construct(isys_component_database $p_db)
    {
        $this->m_category_id = defined_or_default('C__CATG__NETWORK_PORT_OVERVIEW');
        parent::__construct($p_db);
    }
}
