<?php

namespace idoit\Module\SyneticsJdisc\Model;

use idoit\Model\Dao\Base;
use isys_exception_dao;
use isys_exception_database;

/**
 * JDisc -> i-doit mapping.
 *
 * Allow to identify JDisc device and map it to i-doit object.
 *
 * @copyright i-doit GmbH
 * @license   http://www.i-doit.com/license
 */
class Mapping extends Base
{
    /**
     * @param int $referenceId
     * @param array $jdiscData
     * @param string $type
     *
     *
     * @return void
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function createMapping($referenceId, $jdiscData, $type = 'object'): void
    {
        // at the moment the only reasonable mapping is to use serial number or mac address
        if (!$referenceId || empty($jdiscData['type']) || (empty($jdiscData['serialnumber']) && empty($jdiscData['mac']))) {
            return;
        }

        if (!$this->isUniqueReferenceId($referenceId, $type)) {
            $this->updateMapping($referenceId, $jdiscData, $type);
            return;
        }

        $referenceId               = $this->convert_sql_id($referenceId);
        $type                      = $this->m_db->escape_string($type);
        $jdiscData['type']         = $this->convert_sql_int($jdiscData['type']);
        $jdiscData['serialnumber'] = $this->m_db->escape_string($jdiscData['serialnumber']);

        $sql = "
            INSERT INTO isys_jdisc_mapping
            SET
                isys_jdisc_mapping__type              = '{$type}',
                isys_jdisc_mapping__reference_id      = {$referenceId},
                isys_jdisc_mapping__jdisc_device_type = {$jdiscData['type']}
        ";

        if (!empty($jdiscData['serialnumber'])) {
            $sql .= ",isys_jdisc_mapping__jdisc_serial_number = '{$jdiscData['serialnumber']}'";
        }

        if (!empty($jdiscData['mac'])) {
            $mac = $this->m_db->escape_string(implode(',', $jdiscData['mac']));
            $sql .= ",isys_jdisc_mapping__jdisc_mac = '{$mac}'";
        }

        $this->update($sql);
        $this->apply_update();
    }

    /**
     * @param array $jdiscData
     * @param string $type
     *
     * @return int|null
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function findReferenceId($jdiscData, $type = 'object'): ?int
    {
        // at the moment the only reasonable search is to search by serial
        // number or mac address
        if (empty($jdiscData['type']) || (empty($jdiscData['serialnumber']) && empty($jdiscData['mac']))) {
            return null;
        }

        $type                      = $this->m_db->escape_string($type);
        $jdiscData['type']         = $this->convert_sql_int($jdiscData['type']);
        $jdiscData['serialnumber'] = $this->m_db->escape_string($jdiscData['serialnumber']);
        $macConditions = $serialCondition = '';
        $conditions = [];

        if (!empty($jdiscData['serialnumber'])) {
            $serialCondition = " AND isys_jdisc_mapping__jdisc_serial_number = '{$jdiscData['serialnumber']}'";
        }

        if (!empty($jdiscData['mac'])) {
            foreach ($jdiscData['mac'] as $mac_address) {
                if (trim($mac_address) === '' || $mac_address === null) {
                    continue;
                }
                $mac_address  = $this->m_db->escape_string($mac_address);
                $conditions[] = "isys_jdisc_mapping__jdisc_mac LIKE '%{$mac_address}%'";
            }
        }

        if (!empty($conditions)) {
            $macConditions = "AND (" . implode(' OR ', $conditions) . ")";
        }

        $sql = "
            SELECT isys_jdisc_mapping__reference_id
            FROM isys_jdisc_mapping
            WHERE
                isys_jdisc_mapping__type = '{$type}'
                AND isys_jdisc_mapping__jdisc_device_type = {$jdiscData['type']}
                {$serialCondition}
                {$macConditions}
            LIMIT 1
        ";

        $result = $this->retrieve($sql)->get_row();

        return $result ? $result['isys_jdisc_mapping__reference_id'] : null;
    }

    /**
     * @param int $referenceId
     * @param string $type
     *
     * @return array|null
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function findMapping($referenceId, $type = 'object'): ?array
    {
        if (!$referenceId) {
            return null;
        }

        $referenceId = $this->convert_sql_id($referenceId);
        $type        = $this->m_db->escape_string($type);

        $sql = "
            SELECT
                isys_jdisc_mapping__jdisc_device_type,
                isys_jdisc_mapping__jdisc_serial_number,
                isys_jdisc_mapping__jdisc_mac
            FROM isys_jdisc_mapping
            WHERE
                isys_jdisc_mapping__type = '{$type}'
                AND isys_jdisc_mapping__reference_id = {$referenceId}
            LIMIT 1
        ";

        $result = $this->retrieve($sql)->get_row();
        if (!$result) {
            return null;
        }

        $jdiscData = [
            'type'         => $result['isys_jdisc_mapping__jdisc_device_type'],
            'serialnumber' => $result['isys_jdisc_mapping__jdisc_serial_number'],
            'mac'          => [],
        ];
        if (!empty($result['isys_jdisc_mapping__jdisc_mac'])) {
            $jdiscData['mac'] = explode(',', $result['isys_jdisc_mapping__jdisc_mac']);
        }

        return $jdiscData;
    }

    /**
     * @param int $referenceId
     * @param string $type
     *
     * @return void
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function deleteMappingByReferenceId($referenceId, $type = 'object'): void
    {
        if (!$referenceId) {
            return;
        }

        $referenceId = $this->convert_sql_id($referenceId);
        $type        = $this->m_db->escape_string($type);

        $sql = "
            DELETE FROM isys_jdisc_mapping
            WHERE
                isys_jdisc_mapping__type = '{$type}'
                AND isys_jdisc_mapping__reference_id = {$referenceId}
        ";

        $this->update($sql);
        $this->apply_update();
    }

    /**
     * @param array $jdiscData
     * @param string $type
     *
     * @return void
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function deleteMappingByMatchingData($jdiscData, $type = 'object'): void
    {
        $referenceId = $this->findReferenceId($jdiscData, $type);
        if ($referenceId) {
            $this->deleteMappingByReferenceId($referenceId, $type);
        }
    }

    /**
     * @param int $referenceId
     * @param array $jdiscData
     * @param string $type
     *
     *
     * @return void
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function updateMapping($referenceId, $jdiscData, $type = 'object'): void
    {
        // at the moment the only reasonable mapping is to use serial number or mac address
        if (!$referenceId || empty($jdiscData['type']) || (empty($jdiscData['serialnumber']) && empty($jdiscData['mac']))) {
            return;
        }

        $referenceId               = $this->convert_sql_id($referenceId);
        $type                      = $this->m_db->escape_string($type);
        $jdiscData['type']         = $this->convert_sql_int($jdiscData['type']);
        $jdiscData['serialnumber'] = $this->m_db->escape_string($jdiscData['serialnumber']);

        $sql = "
            UPDATE isys_jdisc_mapping
            SET
                isys_jdisc_mapping__jdisc_device_type = {$jdiscData['type']}
        ";

        if (!empty($jdiscData['serialnumber'])) {
            $sql .= ",isys_jdisc_mapping__jdisc_serial_number = '{$jdiscData['serialnumber']}'";
        } else {
            $sql .= ",isys_jdisc_mapping__jdisc_serial_number = NULL";
        }

        if (!empty($jdiscData['mac'])) {
            $mac = $this->m_db->escape_string(implode(',', $jdiscData['mac']));
            $sql .= ",isys_jdisc_mapping__jdisc_mac = '{$mac}'";
        } else {
            $sql .= ",isys_jdisc_mapping__jdisc_mac = NULL";
        }

        $sql .= "
            WHERE
                isys_jdisc_mapping__reference_id = {$referenceId}
                AND isys_jdisc_mapping__type = '{$type}'
        ";

        $this->update($sql);
        $this->apply_update();
    }

    /**
     * @param int $referenceId
     * @param string $type
     *
     * @return bool
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function isUniqueReferenceId($referenceId, $type = 'object'): bool
    {
        $referenceId = $this->convert_sql_id($referenceId);
        $type        = $this->m_db->escape_string($type);

        $sql = "
            SELECT isys_jdisc_mapping__reference_id
            FROM isys_jdisc_mapping
            WHERE
                isys_jdisc_mapping__reference_id = {$referenceId}
                AND isys_jdisc_mapping__type = '{$type}'
            LIMIT 1
        ";
        $result = $this->retrieve($sql)->get_row();

        if ($result && $result['isys_jdisc_mapping__reference_id']) {
            return false;
        }
        return true;
    }

    /**
     * @return Mapping
     *
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function invalidateMapping(): Mapping
    {
        $query = "SELECT isys_jdisc_mapping__id
            FROM isys_jdisc_mapping
            WHERE isys_jdisc_mapping__type = 'object'
              AND isys_jdisc_mapping__reference_id IN (
                SELECT id
                FROM (SELECT isys_jdisc_mapping__reference_id as id
                      FROM isys_jdisc_mapping
                      WHERE isys_jdisc_mapping__type = 'object'
                        AND isys_jdisc_mapping__reference_id NOT IN (SELECT isys_obj__id FROM isys_obj)) as t
            );";

        $result = $this->retrieve($query);
        if ($result->count() > 0) {
            $ids = [];
            while($row = $result->get_row()) {
                $ids[] = $row['isys_jdisc_mapping__id'];
            }

            $deleteQuery = sprintf("DELETE FROM isys_jdisc_mapping WHERE isys_jdisc_mapping__id IN (%s)", implode(',', $ids));
            $this->update($deleteQuery) && $this->apply_update();
        }

        return $this;
    }
}
