<?php

namespace idoit\Module\SyneticsJdisc\Model;

use idoit\Component\Helper\Ip;
use idoit\Module\Cmdb\Model\Matcher\Ci\MatchKeyword;
use idoit\Module\Cmdb\Model\Matcher\Identifier\Fqdn;
use idoit\Module\Cmdb\Model\Matcher\Identifier\Hostname;
use idoit\Module\Cmdb\Model\Matcher\Identifier\IpAddress;
use idoit\Module\Cmdb\Model\Matcher\Identifier\Mac;
use idoit\Module\Cmdb\Model\Matcher\Identifier\ModelSerial;
use idoit\Module\Cmdb\Model\Matcher\Identifier\ObjectTitle;
use idoit\Module\SyneticsJdisc\Controller\Table\DeviceListSearchParams;
use idoit\Module\SyneticsJdisc\Enum\SyncStatus;
use idoit\Module\SyneticsJdisc\Graphql\Connector;
use idoit\Module\SyneticsJdisc\Graphql\Mutation\DeleteDevice;
use idoit\Module\SyneticsJdisc\Graphql\Query\DeviceList;
use idoit\Module\SyneticsJdisc\Graphql\Query\GetDevice;
use isys_application;
use isys_component_database;
use isys_component_database_pdo;
use isys_helper_color;
use isys_jdisc_dao_data;
use isys_jdisc_dao_devices;
use isys_jdisc_dao_matching;
use isys_module_synetics_jdisc;

class DeviceListDao extends AbstractJDiscDao
{
    private const MIN_DEVICES_PER_LOAD = 100000;

    /**
     * @param DeviceListSearchParams $search
     * @return array ['rows' => [], 'total' => 0]
     */
    public function getDevices(DeviceListSearchParams $search): array
    {
        /** @var \idoit\Component\Settings\User $userSettings */
        $userSettings = isys_application::instance()->container->get('settingsUser');
        $hiddenColumns = $userSettings->get('jdisc.device-list.hidden-columns', []);

        $connector = Connector::instance($this->serverId);
        $connector->connect();

        $syncStatus     = $search->getSyncStatus();
        $devices        = [];
        $data           = $this->getDeviceListData($connector, $search, $syncStatus);
        $importData     = $this->getImportData($data['rows'], $this->serverId);
        $timeZoneOffset = 0;
        foreach ($data['rows'] as $row) {
            $dataRow = $this->processDeviceRow($row, $importData, $data['columns'], $timeZoneOffset, $data['types']);

            if ($this->filterDevicesBySyncStatus($dataRow, $syncStatus)) {
                continue;
            }

            foreach ($hiddenColumns as $column) {
                unset($dataRow[$column]);
            }

            $devices[] = $dataRow;
        }

        $offset = ($search->getPage() - 1) * $search->getPerPage();

        return [
            'rows'  => $syncStatus === SyncStatus::ANY ? $devices : array_slice($devices, $offset, $search->getPerPage()),
            'total' => $syncStatus === SyncStatus::ANY ? $data['visibleRows'] : count($devices),
        ];
    }

    /**
     * @param int $id
     * @return array
     */
    public function get(int $id): array
    {
        $connector = Connector::instance($this->serverId);
        $connector->connect();

        $query = new GetDevice();
        $query->setId($id);

        return $connector->query($query);
    }

    /**
     * @param int $id
     * @return bool
     */
    public function delete(int $id): bool
    {
        $connector = Connector::instance($this->serverId);
        $connector->connect();

        $query = new DeleteDevice();
        $query->setId($id);

        $result = $connector->query($query);
        return $result['deleteDevice'];
    }

    /**
     * @param Connector $connector
     * @param DeviceListSearchParams $search
     * @param SyncStatus $syncStatus
     * @param int $loadMoreDevicesStart
     * @return array
     */
    private function getDeviceListData(Connector $connector, DeviceListSearchParams $search, SyncStatus $syncStatus): array
    {
        $query = new DeviceList();
        $query->useSearch($search);
        // get max devices if sync status is set
        if ($syncStatus !== SyncStatus::ANY) {
            $query->setRowCount(self::MIN_DEVICES_PER_LOAD);
            $query->setStart(0);
        }
        return $connector->query($query);
    }

    /**
     * @param array $row
     * @param array $importData
     * @param array $columns
     * @param int $timeZoneOffset
     * @param array $deviceTypes
     * @return array
     */
    private function processDeviceRow(array $row, array $importData, array $columns, int $timeZoneOffset, array $deviceTypes): array
    {
        $dataRow['id'] = $row[0];

        if (!empty($importData[$row[0]])) {
            $dataRow['status']      = 'synced';
            $dataRow['importDate']  = $importData[$row[0]]['date'];
            $dataRow['objectId']    = $importData[$row[0]]['objID'];
            $dataRow['objectTitle'] = $importData[$row[0]]['title'];
            $dataRow['objectColor'] = $importData[$row[0]]['color'];
        } else {
            $dataRow['status']      = 'unsynced';
            $dataRow['importDate']  = null;
            $dataRow['objectId']    = null;
            $dataRow['objectTitle'] = null;
            $dataRow['objectColor'] = null;
        }

        foreach ($columns as $key => $column) {
            if ($column['fieldDescription']['valueType'] == 'Timestamp') {
                $time = (int) ($row[$key] / 1000 - $timeZoneOffset * 60);
                $dataRow[$column['fieldDescription']['fieldId']] = date('Y-m-d H:i:s', $time);
            } elseif ($column['fieldDescription']['valueType'] == 'ShortFormattedDuration') {
                $pastTimestamp = (int) $row[$key];

                $days    = floor($pastTimestamp / (1000 * 60 * 60 * 24));
                $hours   = floor(($pastTimestamp % (1000 * 60 * 60 * 24)) / (1000 * 60 * 60));
                $minutes = floor(($pastTimestamp % (1000 * 60 * 60)) / (1000 * 60));
                $seconds = floor(($pastTimestamp % (1000 * 60)) / 1000);

                $deviceDate = sprintf("%d days %02d:%02d:%02d", $days, $hours, $minutes, $seconds);

                $dataRow[$column['fieldDescription']['fieldId']] = $deviceDate;
            } else {
                $dataRow[$column['fieldDescription']['fieldId']] = $row[$key];
            }

            if ($column['field'] === 'Name') {
                $dataRow[$column['fieldDescription']['fieldId']] = $row[$key] ?? "[Device#{$row[0]}]";
            }

            if ($column['field'] === 'deviceType') {
                $dataRow[$column['fieldDescription']['fieldId']] = $deviceTypes[$row[$key]]['displayName'];
                $dataRow['deviceTypeId'] = $deviceTypes[$row[$key]]['code'];
            }
        }

        return $dataRow;
    }

    /**
     * @param array $dataRow
     * @param SyncStatus $syncStatus
     * @return bool
     */
    private function filterDevicesBySyncStatus(array $dataRow, SyncStatus $syncStatus): bool
    {
        if ($syncStatus === SyncStatus::SYNCED && $dataRow['status'] === 'unsynced') {
            return true;
        }

        if ($syncStatus === SyncStatus::UNSYNCED && $dataRow['status'] !== 'unsynced') {
            return true;
        }

        return false;
    }

    /**
     * @param array $rows
     * @param int $serverId
     * @return array
     */
    private function getImportData(array $rows, int $serverId): array
    {
        if (empty($rows)) {
            return [];
        }

        $deviceIds    = array_column($rows, 0);
        $db           = isys_application::instance()->container->get('database');
        $mapping      = Mapping::instance($db)->invalidateMapping();
        $jdiscPdo     = $this->getJDiscPdo($serverId);
        $jdiscDaoData = new isys_jdisc_dao_data($db, isys_application::instance()->container->get('jdisc_pdo'));
        $daoDevices   = isys_jdisc_dao_devices::instance($jdiscPdo);

        isys_jdisc_dao_matching::initialize(
            1, // default matcher
            $serverId,
            $jdiscDaoData
        );

        $matchingData = $this->getMatchingData($deviceIds, $jdiscPdo, $daoDevices);

        $idMatchings  = $this->findMatchingIds($rows, $serverId, $mapping, $matchingData);

        return $this->getImportDetails($db, $idMatchings);
    }

    /**
     * @param int $serverId
     * @return isys_component_database_pdo
     */
    private function getJDiscPdo(int $serverId): isys_component_database_pdo
    {
        $jdisc = isys_module_synetics_jdisc::factory();
        $jdisc->switch_database($serverId);
        return $jdisc->m_dao->get_connection();
    }

    /**
     * @param array $deviceIds
     * @param isys_component_database_pdo $jdiscPdo
     * @param isys_jdisc_dao_devices $daoDevices
     * @return array
     */
    private function getMatchingData(array $deviceIds, isys_component_database_pdo $jdiscPdo, isys_jdisc_dao_devices $daoDevices): array
    {
        $uniqueIds       = [];
        $deviceGroups    = [];
        $deviceTypes     = [];
        $deviceMacs      = [];
        $deviceData      = [];
        $deviceKeywords  = [];
        $deviceIdsString = implode(',', $deviceIds);
        $ref             = $jdiscPdo->query(
            <<<SQL
            SELECT
                d.id, d.uniqueid, d.type, d.name, d.serialnumber,
                STRING_AGG(DISTINCT dgroup.name, ',') AS group_name,
                STRING_AGG(DISTINCT m.ifphysaddress, ',') AS ifphysaddress,
                STRING_AGG(DISTINCT ip.fqdn, ',') AS fqdn,
                STRING_AGG(DISTINCT CAST(ip.address AS varchar(20)), ',') AS address
            FROM device d
            LEFT JOIN devicegroupdevicerelation AS dg ON dg.deviceid = d.id
            LEFT JOIN devicegroup AS dgroup ON dgroup.id = dg.devicegroupid
            LEFT JOIN mac AS m ON m.deviceid = d.id AND m.useforidentification = TRUE AND m.ifphysaddress IS NOT NULL
            LEFT JOIN (SELECT fqdn, address, deviceid FROM ip4transport WHERE ip4transport.isdiscoverytransport = TRUE) AS ip ON ip.deviceid = d.id
            WHERE d.id IN ({$deviceIdsString})
            GROUP BY d.id
            ORDER BY d.id
            SQL
        );
        while ($row = $jdiscPdo->fetch_row_assoc($ref)) {
            $deviceTypes[$row['id']]  = $row['type'];
            $uniqueIds[$row['id']]    = $row['uniqueid'];
            $deviceGroups[$row['id']] = !empty($row['group_name']) ? array_unique(explode(',', $row['group_name'])) : [];
            $deviceMacs[$row['id']]   = !empty($row['ifphysaddress']) ? array_unique(explode(',', $row['ifphysaddress'])) : [];

            $deviceData[$row['id']] = [
                'name'         => $row['name'] ?? '',
                'serialnumber' => $row['serialnumber'],
                'fqdn'         => current(explode(',', $row['fqdn'])),
                'address'      => current(explode(',', $row['address'])),
                'hostname'     => null,
                'mac'          => [],
            ];
            if ($deviceData[$row['id']]['fqdn']) {
                $fqdn = $deviceData[$row['id']]['fqdn'];
                $fqdnArr = explode('.', $fqdn);
                if (count($fqdnArr) >= 3) {
                    $deviceData[$row['id']]['hostname'] = $fqdnArr[0];
                } else {
                    $deviceData[$row['id']]['hostname'] = $fqdn;
                }
            }

            $deviceKeywords[$row['id']] = [];

            $deviceKeywords[$row['id']][] = new MatchKeyword(ObjectTitle::KEY, $deviceData[$row['id']]['name']);
            if ($daoDevices->isResolveFqdnName() && $daoDevices->isValidFqdn($deviceData[$row['id']]['name'])) {
                $deviceKeywords[$row['id']][] = new MatchKeyword(
                    ObjectTitle::KEY,
                    $daoDevices->extractHostFromFqdn($deviceData[$row['id']]['name'])
                );
            }
            $deviceKeywords[$row['id']][] = new MatchKeyword(ModelSerial::KEY, $deviceData[$row['id']]['serialnumber']);
            $deviceKeywords[$row['id']][] = new MatchKeyword(Hostname::KEY, $deviceData[$row['id']]['hostname']);
            $deviceKeywords[$row['id']][] = new MatchKeyword(IpAddress::KEY, $deviceData[$row['id']]['address']);
            $deviceKeywords[$row['id']][] = new MatchKeyword(Fqdn::KEY, $deviceData[$row['id']]['fqdn']);
        }
        $ref = $jdiscPdo->query(
            <<<SQL
            SELECT DISTINCT(m.ifphysaddress) AS macaddr, m.deviceid AS id FROM mac AS m
            INNER JOIN interfacetypelookup as iftl on iftl.id = m.iftype
            WHERE
                m.ifoperstatus != 2
                AND m.ifphysaddress IS NOT NULL
                AND (SELECT COUNT(*) AS cnt FROM mac WHERE ifphysaddress = m.ifphysaddress GROUP BY ifphysaddress) = 1
                AND iftl.name != '{isys_jdisc_dao_network::C_FILTER_JDISC__VIRTUAL}'
                AND m.deviceid IN ({$deviceIdsString})
            SQL
        );
        while ($row = $jdiscPdo->fetch_row_assoc($ref)) {
            if (empty($deviceData[$row['id']]['macaddr'])) {
                $deviceData[$row['id']]['macaddr'][] = $row['macaddr'];

                $deviceKeywords[$row['id']][] = new MatchKeyword(Mac::KEY, current($deviceData[$row['id']]['macaddr']));
            }
        }

        return [
            'uniqueIds'      => $uniqueIds,
            'deviceGroups'   => $deviceGroups,
            'deviceTypes'    => $deviceTypes,
            'deviceMacs'     => $deviceMacs,
            'deviceKeywords' => $deviceKeywords,
        ];
    }

    /**
     * @param array $rows
     * @param int $serverId
     * @param Mapping $mapping
     * @param array $matchingData
     * @return array
     */
    private function findMatchingIds(array $rows, int $serverId, Mapping $mapping, array $matchingData): array
    {
        $matching = isys_jdisc_dao_matching::instance();

        $idMatchings = [];
        foreach ($rows as &$row) {
            $id = $row[0];
            $serialNumber = $row[9];

            if (empty($idMatchings[$id])) {
                if (count($matchingData['deviceGroups'][$id]) > 1) {
                    $candidates = [];
                    foreach ($matchingData['deviceGroups'][$id] as $group) {
                        $objId = $matching->find_object_id($id, 'deviceid-' . $serverId, $matchingData['deviceKeywords'][$id], $group);
                        if ($objId) {
                            $candidates[$objId]++;
                        }
                    }

                    $candidatesAmount = count($candidates);
                    if ($candidatesAmount > 0) {
                        if ($candidatesAmount === 1) {
                            $idMatchings[$id] = key($candidates);
                        } else {
                            $foundCounter = max($candidates);
                            $possibleCandidates = array_keys($candidates, $foundCounter);
                            $idMatchings[$id] = end($possibleCandidates);
                        }
                    }
                } else {
                    $idMatchings[$id] = $matching->find_object_id($id, 'deviceid-' . $serverId, $matchingData['deviceKeywords'][$id], $matchingData['deviceGroups'][$id][0]);
                }
            }

            $uniqueId = $matchingData['uniqueIds'][$id] ?? null;
            if (empty($idMatchings[$id]) && !empty($uniqueId)) {
                $idMatchings[$id] = $matching->getIdentifierDataByUniqueId($uniqueId)['objectId'];
            }

            if (empty($idMatchings[$id])) {
                $mappingData = [
                    'type'         => $matchingData['deviceTypes'][$id],
                    'serialnumber' => $serialNumber,
                    'mac'          => $matchingData['deviceMacs'][$id],
                ];
                $objId = $mapping->findReferenceId($mappingData);

                if ($objId && isys_application::instance()->container->get('cmdb_dao')->get_object_status_by_id($objId) === C__RECORD_STATUS__NORMAL
                ) {
                    $idMatchings[$id] = $objId;
                }
            }
        }

        return $idMatchings;
    }

    /**
     * @param isys_component_database $db
     * @param array $idMatchings
     * @return array
     */
    private function getImportDetails(isys_component_database $db, array $idMatchings): array
    {
        $data   = [];
        $objIds = [];
        foreach ($idMatchings as $id => $objId) {
            if ($objId) {
                $objIds[] = (int) $objId;
            }
        }
        $objIds = array_unique($objIds);
        if ($objIds) {
            $objIdsString = implode(',', $objIds);
            $ref = $db->query(
                <<<SQL
                SELECT
                    isys_catg_jdisc_device_information_list__import_date AS import_date,
                    isys_catg_jdisc_device_information_list__isys_obj__id AS objID,
                    isys_obj__title AS title,
                    isys_cmdb_status__color AS color
                FROM isys_catg_jdisc_device_information_list
                LEFT JOIN isys_obj ON isys_obj__id = isys_catg_jdisc_device_information_list__isys_obj__id
                LEFT JOIN isys_cmdb_status ON isys_cmdb_status__id = isys_obj__isys_cmdb_status__id
                WHERE isys_catg_jdisc_device_information_list__isys_obj__id IN ({$objIdsString})
                SQL
            );
            while ($row = $db->fetch_row_assoc($ref)) {
                if (!empty($row['objID'])) {
                    foreach ($idMatchings as $id => $objId) {
                        if ($objId == $row['objID']) {
                            $data[$id] = [
                                'date'  => $row['import_date'],
                                'objID' => $row['objID'],
                                'title' => $row['title'],
                                'color' => isys_helper_color::unifyHexColor((string) $row['color']),
                            ];
                        }
                    }
                }
            }
        }

        return $data;
    }

    /**
     * @param int $profileId
     * @param string $importMode
     * @param int|null $group
     * @return array
     */
    public function getStatistics(int $profileId, string $importMode, ?int $group): array
    {
        $jdisc = isys_module_synetics_jdisc::factory();
        $jdisc->switch_database($this->serverId);
        $pdo = $jdisc->m_dao->get_connection();

        $devices    = [];
        $deviceData = [];
        $res = $jdisc->retrieve_object_result($group, $profileId);
        while ($device = $pdo->fetch_row_assoc($res)) {
            $devices[] = [
                'id'   => $device['id'],
                'name' => $device['name'],
                'type' => $device['type_name'],
            ];
            $deviceData[] = [
                0 => $device['id'],
                9 => $device['serialnumber'],
            ];
        }

        $deviceIps  = $this->getDeviceIps(array_column($devices, 'id'));
        $importData = $this->getImportData($deviceData, $this->serverId);

        foreach ($devices as &$device) {
            $device['ip'] = $deviceIps[$device['id']] ?? null;
            if (!empty($importData[$device['id']]['objID'])) {
                $device['result'] = ($importMode === 'create_only_new_devices')
                    ? 'none'
                    : 'updated';
            } else {
                $device['result'] = ($importMode === 'update_existing_only')
                    ? 'none'
                    : 'created';
            }
        }

        return $devices;
    }

    /**
     * @param array $ids
     * @return array|null
     */
    private function getDeviceIps(array $ids): array
    {
        if (empty($ids)) {
            return [];
        }
        $deviceIdsString = implode(',', $ids);
        $jdiscPdo        = $this->getJDiscPdo($this->serverId);

        $ref = $jdiscPdo->query(
            <<<SQL
            SELECT
                d.id,
                STRING_AGG(DISTINCT CAST(ip.address AS varchar(20)), ',') AS address
            FROM
                device d
            LEFT JOIN
                ip4transport ip ON ip.deviceid = d.id AND ip.isdiscoverytransport = TRUE
            WHERE
                d.id IN ({$deviceIdsString})
            GROUP BY
                d.id
            ORDER BY
                d.id;
            SQL
        );
        $deviceIps = [];
        while ($row = $jdiscPdo->fetch_row_assoc($ref)) {
            $deviceIps[$row['id']] = $row['address'] ? Ip::long2ip($row['address']) : null;
        }
        return $deviceIps;
    }
}
