<?php

/**
 * i-doit
 *
 * Custom Fields Module Dao
 *
 * @package     i-doit
 * @subpackage  Modules
 * @author      Dennis Stücken <dstuecken@i-doit.org>
 * @author      Van Quyen Hoang <qhoang@i-doit.org>
 * @version     1.0
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 *
 */
class isys_custom_fields_dao extends isys_component_dao
{
    /**
     * @return string[]
     */
    public static function getMultipleSelectableFieldTypes()
    {
        return [
            'dialog_plus',
            'checkboxes'
        ];
    }

    /**
     * Deletes a custom field and its content.
     *
     * @param   integer $p_id
     *
     * @return  boolean
     */
    public function delete($p_id)
    {
        $l_config = $this->get_config($p_id);

        $l_sql = 'DELETE FROM isysgui_catg_custom WHERE isysgui_catg_custom__id = ' . $this->convert_sql_id($p_id) . ';';

        /** @var $l_relation_dao isys_cmdb_dao_category_g_relation */
        $l_relation_dao = isys_cmdb_dao_category_g_relation::instance($this->get_database_component());
        $l_has_relation_field = $l_relation_dao->has_relation_field('isys_catg_custom_fields_list');

        if ($l_has_relation_field) {
            // Delete Relations
            $l_sql_delete = 'SELECT isys_catg_relation_list__isys_obj__id
				FROM `isys_catg_custom_fields_list`
				INNER JOIN isys_catg_relation_list ON isys_catg_relation_list__id = `isys_catg_custom_fields_list__isys_catg_relation_list__id`
				WHERE `isys_catg_custom_fields_list__isysgui_catg_custom__id` = ' . $this->convert_sql_id($p_id);
            $l_res = $this->retrieve($l_sql_delete);

            while ($l_row = $l_res->get_row()) {
                $l_relation_dao->delete_object_and_relations($l_row['isys_catg_relation_list__isys_obj__id']);
            }
        }

        if ($this->update($l_sql) && $this->apply_update()) {
            /* Clear dialog content */
            if (is_array($l_config)) {
                foreach ($l_config as $l_c) {
                    if ($l_c["type"] == "f_popup" && ($l_c["popup"] == "dialog" || $l_c["popup"] == "dialog_plus")) {
                        $l_identifier = $l_c["identifier"];
                        // First check if there are no entries left
                        if (!$this->dialogIdentifierExists($l_identifier)) {
                            $this->delete_dialog_content($l_identifier);
                        }
                    }
                }
            }

            $l_upd_prop = isys_factory::get_instance('isys_update_property_migration');

            $l_upd_prop->set_database($this->get_database_component())
                ->reset_property_table(C__CMDB__CATEGORY__TYPE_CUSTOM)
                ->collect_category_data(C__CMDB__CATEGORY__TYPE_CUSTOM)
                ->prepare_sql_queries('g_custom', false)
                ->execute_sql();

            isys_application::instance()->container->signals->emit('mod.cmdb.refreshTableConfigurations');

            return true;
        }

        return false;
    }

    /**
     * @param      $p_custom_category_id
     * @param null $p_object_id
     *
     * @return int
     */
    public function count_values($p_custom_category_id, $p_object_id = null)
    {
        $l_condition = '';
        if ($p_object_id) {
            $l_condition = ' AND isys_catg_custom_fields_list__isys_obj__id = ' . $this->convert_sql_id($p_object_id);
        }

        return $this->retrieve('SELECT COUNT(*) AS `c_count` FROM isys_catg_custom_fields_list WHERE isys_catg_custom_fields_list__isysgui_catg_custom__id = ' .
            $this->convert_sql_id($p_custom_category_id) . $l_condition)
            ->get_row_value('c_count');
    }

    /**
     * @param      $p_custom_category_id
     * @param null $p_object_id
     *
     * @return int
     */
    public function count($p_custom_category_id, $p_object_id = null)
    {
        $l_condition = '';
        if ($p_object_id) {
            $l_condition = ' AND isys_catg_custom_fields_list__isys_obj__id = ' . $this->convert_sql_id($p_object_id);
        }

        return $this->retrieve('SELECT COUNT(DISTINCT isys_catg_custom_fields_list__isys_obj__id) AS `c_count` FROM isys_catg_custom_fields_list WHERE isys_catg_custom_fields_list__isysgui_catg_custom__id = ' .
            $this->convert_sql_id($p_custom_category_id) . $l_condition)
            ->get_row_value('c_count');
    }

    /**
     * Delete contents of corresponding dialog identifier.
     *
     * @param   string $p_identifier
     *
     * @return  boolean
     */
    public function delete_dialog_content($p_identifier)
    {
        return $this->update('DELETE FROM isys_dialog_plus_custom WHERE isys_dialog_plus_custom__identifier = ' . $this->convert_sql_text($p_identifier) . ';') &&
            $this->apply_update();
    }

    /**
     * Checks if the dialog identifier is being used in any custom category
     *
     * @param $dialogIdentifier
     *
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function dialogIdentifierExists($dialogIdentifier)
    {
        $query = 'SELECT isysgui_catg_custom__config FROM isysgui_catg_custom';
        $result = $this->retrieve($query);
        while ($configArr = $result->get_row()) {
            $config = unserialize($configArr['isysgui_catg_custom__config']);

            foreach ($config as $configKey => $configData) {
                if ($configData["type"] == "f_popup" && ($configData["popup"] == "dialog" || $configData["popup"] == "dialog_plus")) {
                    if ($configData['identifier'] === $dialogIdentifier) {
                        return true;
                    }
                }
            }
        }

        return false;
    }

    /**
     * Get all custom categories
     *
     * @param   integer $p_id
     * @param   string  $p_title
     *
     * @return  isys_component_dao_result
     */
    public function get_data($p_id = null, $p_title = null)
    {
        $l_sql = 'SELECT * FROM isysgui_catg_custom WHERE TRUE';

        if ($p_id !== null) {
            if (is_numeric($p_id)) {
                $l_sql .= ' AND isysgui_catg_custom__id = ' . $this->convert_sql_id($p_id);
            } else {
                $l_sql .= ' AND isysgui_catg_custom__const = ' . $this->convert_sql_text($p_id);
            }
        }

        if ($p_title) {
            $l_sql .= ' AND isysgui_catg_custom__title = ' . $this->convert_sql_text($p_title);
        }

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

    /**
     * Validates given arguments, returns false if one argument is empty
     *
     * @return bool
     */
    public function validate()
    {
        $l_argv = func_get_args();

        foreach ($l_argv as $l_arg) {
            if ($l_arg == "-1" || $l_arg == "" || is_null($l_arg)) {
                return false;
            }
        }

        return true;
    }

    /**
     * Validate category constant
     *
     * @param   string  $p_category_constant
     *
     * @return  bool
     */
    private function validateCategoryConstant($p_category_constant)
    {
        return $this->validate($p_category_constant) && (strlen($p_category_constant) > 1) && !preg_match('/[^A-Za-z_\w]/', $p_category_constant) &&
            !defined($p_category_constant);
    }

    /**
     * @param   string  $constant
     * @param   integer $excludeId
     *
     * @return  string
     * @throws isys_exception_database
     */
    private function prepareCategoryConstant($constant, $excludeId = 0)
    {
        // @see  ID-6492  Remove "C__CATG__CUSTOM_FIELDS_" if it's already there (can happen during QCW profile import).
        if (strpos($constant, 'C__CATG__CUSTOM_FIELDS_') === 0) {
            $constant = substr($constant, strlen('C__CATG__CUSTOM_FIELDS_'));
        }

        $constant = strtoupper($constant);
        $constant = preg_replace('~(\s|-)+~', '_', $constant);
        $constant = preg_replace('~_{2,}~', '_', $constant);
        $constant = preg_replace('~[^a-z0-9_]~i', '', $constant);

        $counter = 1;
        $finalConstant = 'C__CATG__CUSTOM_FIELDS_' . $constant;

        // Find a valid constant, that has not yet been taken.
        while (! $this->isCategoryConstantAvailable($finalConstant, $excludeId)) {
            $finalConstant = 'C__CATG__CUSTOM_FIELDS_' . $constant . '_' . $counter;
            $counter ++;

            if ($counter > 10) {
                break;
            }
        }

        return $finalConstant;
    }

    /**
     * Find a free/unused constant.
     *
     * @param string  $constant
     * @param integer $excludeId
     *
     * @return bool
     * @throws isys_exception_database
     */
    private function isCategoryConstantAvailable($constant, $excludeId = 0)
    {
        $sql = 'SELECT isysgui_catg_custom__id 
            FROM isysgui_catg_custom 
            WHERE isysgui_catg_custom__const = ' . $this->convert_sql_text($constant);

        if ($excludeId > 0) {
            $sql .= ' AND isysgui_catg_custom__id != ' . $this->convert_sql_id($excludeId);
        }

        return $this->retrieve($sql . ' LIMIT 1;')->get_row_value('isysgui_catg_custom__id') === null;
    }

    /**
     * Extracts config and returns it
     *
     * @param  integer $p_id
     *
     * @return array
     */
    public function get_config($p_id)
    {
        $l_data = $this->get_data($p_id)
            ->get_row_value('isysgui_catg_custom__config');

        if ($l_data !== null && is_string($l_data)) {
            $l_data = unserialize($l_data);

            if (is_array($l_data)) {
                return $l_data;
            }
        }

        return [];
    }

    /**
     * Clears all custom categoriy to object type assignments.
     *
     * @param   integer $p_custom_id
     *
     * @return  bool
     */
    public function clear_assignments($p_custom_id)
    {
        $l_sql = 'DELETE FROM isys_obj_type_2_isysgui_catg_custom
			WHERE isys_obj_type_2_isysgui_catg_custom__isysgui_catg_custom__id = ' . $this->convert_sql_id($p_custom_id) . ';';

        return $this->update($l_sql) && $this->apply_update();
    }

    /**
     * Get all custom category to object type assignments.
     *
     * @param   integer $p_isysgui_id
     * @param   integer $p_object_type
     *
     * @return  isys_component_dao_result
     */
    public function get_assignments($p_isysgui_id = null, $p_object_type = null, $p_overview_only = false)
    {
        $l_sql = 'SELECT * FROM isys_obj_type AS main ';

        if ($p_overview_only === true) {
            $l_sql .= ' INNER JOIN isys_obj_type_2_isysgui_catg_custom_overview AS oc ON oc.isys_obj_type__id = main.isys_obj_type__id ' .
                'INNER JOIN isysgui_catg_custom AS cc ON oc.isysgui_catg_custom__id = cc.isysgui_catg_custom__id';
            $l_order_by = ' ORDER BY isys_obj_type_2_isysgui_catg_custom_overview__sort;';
        } else {
            $l_sql .= 'INNER JOIN isys_obj_type_2_isysgui_catg_custom AS oc ON oc.isys_obj_type_2_isysgui_catg_custom__isys_obj_type__id = main.isys_obj_type__id ' .
                'INNER JOIN isysgui_catg_custom AS cc ON oc.isys_obj_type_2_isysgui_catg_custom__isysgui_catg_custom__id = cc.isysgui_catg_custom__id';
            $l_order_by = ' ORDER BY cc.isysgui_catg_custom__sort;';
        }

        $l_sql .= ' WHERE TRUE ';

        if ($p_isysgui_id !== null) {
            $l_sql .= ' AND cc.isysgui_catg_custom__id = ' . $this->convert_sql_id($p_isysgui_id);
        }

        if ($p_object_type !== null) {
            $l_sql .= ' AND main.isys_obj_type__id = ' . $this->convert_sql_id($p_object_type);
        }

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

    /**
     * Assigns a custom category to an object type.
     *
     * @param   integer $p_isysgui_id
     * @param   integer $p_obj_type_id
     *
     * @return  boolean
     */
    public function assign($p_isysgui_id, $p_obj_type_id)
    {
        $l_sql = "DELETE FROM isys_obj_type_2_isysgui_catg_custom
			WHERE isys_obj_type_2_isysgui_catg_custom__isys_obj_type__id = " . $this->convert_sql_id($p_obj_type_id) . "
			AND isys_obj_type_2_isysgui_catg_custom__isysgui_catg_custom__id = " . $this->convert_sql_id($p_isysgui_id) . ";";

        if (($l_ret = $this->update($l_sql))) {
            $l_sql = "INSERT INTO isys_obj_type_2_isysgui_catg_custom SET
   			    isys_obj_type_2_isysgui_catg_custom__isys_obj_type__id = " . $this->convert_sql_id($p_obj_type_id) . ",
   			    isys_obj_type_2_isysgui_catg_custom__isysgui_catg_custom__id = " . $this->convert_sql_id($p_isysgui_id) . ";";

            $l_ret = ($this->update($l_sql) && $this->apply_update());
        }

        return $l_ret;
    }

    /**
     * Saves configuration of a custom category.
     *
     * @param   integer $p_id
     * @param   string  $p_title
     * @param   array   $p_config
     * @param   integer $p_parent
     * @param   integer $p_sort
     * @param   integer $p_multivalued
     * @param   string  $p_constant
     *
     * @throws Exception
     * @return  boolean
     */
    public function save($p_id, $p_title, $p_config, $p_parent, $p_sort, $p_multivalued, $p_constant)
    {
        if ($p_constant === null || empty($p_constant) || !$this->validateCategoryConstant($p_constant)) {
            $p_constant = time();
        }

        $p_constant = $this->prepareCategoryConstant($p_constant, $p_id);

        // Get old constant of category
        $oldConstant = $this->get_data($p_id)->get_row_value('isysgui_catg_custom__const');

        if ($p_constant !== $oldConstant) {
            isys_application::instance()->container->get('notify')->warning(isys_application::instance()->container->get('language')
                ->get('LC__CMDB__CUSTOM_CATEGORIES_INVALID_CONSTANT_WARNING', [$oldConstant, $p_constant]), ['sticky' => true]);
        }

        if ($this->validate($p_title)) {
            $l_sql = 'UPDATE isysgui_catg_custom SET
	   		    isysgui_catg_custom__title = ' . $this->convert_sql_text($p_title) . ',
	   		    isysgui_catg_custom__type = ' . $this->convert_sql_int(isys_cmdb_dao_category::TYPE_EDIT) . ',
	   		    isysgui_catg_custom__parent = ' . $this->convert_sql_int($p_parent) . ',
	   		    isysgui_catg_custom__config = ' . $this->convert_sql_text(serialize($p_config)) . ',
	   		    isysgui_catg_custom__sort = ' . $this->convert_sql_int($p_sort) . ',
	   		    isysgui_catg_custom__const = ' . $this->convert_sql_text($p_constant) . ',
	   		    isysgui_catg_custom__list_multi_value = ' . $this->convert_sql_boolean($p_multivalued) . '
	   		    WHERE (isysgui_catg_custom__id = ' . $this->convert_sql_id($p_id) . ');';

            if ($this->update($l_sql) && $this->apply_update()) {
                $l_config_keys = array_keys($p_config);

                /** @var $l_dao_relation isys_cmdb_dao_category_g_relation */
                $l_dao_relation = isys_cmdb_dao_category_g_relation::instance($this->m_db);

                if ($l_dao_relation->has_relation_field('isys_catg_custom_fields_list')) {
                    $l_sql = "SELECT isys_catg_custom_fields_list__id, isys_catg_custom_fields_list__isys_catg_relation_list__id
						FROM isys_catg_custom_fields_list
						WHERE isys_catg_custom_fields_list__isysgui_catg_custom__id = " . $this->convert_sql_id($p_id) . "
						AND isys_catg_custom_fields_list__field_type != 'commentary'
						AND isys_catg_custom_fields_list__field_key NOT IN ('" . rtrim(implode("','", $l_config_keys), "','") . "')";

                    $l_res = $this->retrieve($l_sql);

                    if ($l_res->num_rows() > 0) {
                        $l_delete = $l_delete_rel = [];

                        while ($l_row = $l_res->get_row()) {
                            $l_delete[] = $l_row['isys_catg_custom_fields_list__id'];
                            if (!empty($l_row['isys_catg_custom_fields_list__isys_catg_relation_list__id'])) {
                                $l_delete_rel[] = $l_row['isys_catg_custom_fields_list__isys_catg_relation_list__id'];
                            }
                        }
                        $l_delete_query = 'DELETE FROM isys_catg_custom_fields_list ' . 'WHERE isys_catg_custom_fields_list__id IN (' . rtrim(implode(',', $l_delete), ',') .
                            ')';
                        $this->update($l_delete_query) && $this->apply_update();

                        if (is_countable($l_delete_rel) && count($l_delete_rel) > 0) {
                            // delete relation
                            foreach ($l_delete_rel as $l_rel_id) {
                                $l_dao_relation->delete_relation($l_rel_id);
                            }
                        }
                    }
                }

                /**
                 * @var $l_upd_prop isys_update_property_migration
                 */
                $l_upd_prop = isys_factory::get_instance('isys_update_property_migration');

                $l_upd_prop->set_database($this->get_database_component())
                    ->reset_property_table(C__CMDB__CATEGORY__TYPE_CUSTOM)
                    ->collect_category_data(C__CMDB__CATEGORY__TYPE_CUSTOM)
                    ->prepare_sql_queries('g_custom', false)
                    ->execute_sql();

                isys_application::instance()->container->signals->emit('mod.cmdb.refreshTableConfigurations');

                return true;
            } else {
                return false;
            }
        } else {
            throw new Exception("Please make sure that category title is set " . "and category constant contains at least two characters and " .
                "includes only alphabetic character (a-z and A-Z) or numbers (0-9) and underscores (_).");
        }
    }

    /**
     * Creates a custom category
     *
     * @param string $p_title
     * @param array  $p_config
     * @param int    $p_parent
     * @param int    $p_sort
     * @param        $p_multivalued
     * @param param string $p_constant
     *
     * @throws Exception
     *
     * @return mixed[int|false]
     */
    public function create($p_title, $p_config, $p_parent, $p_sort, $p_multivalued, $p_constant = null)
    {
        if ($p_constant === null || empty($p_constant) || !$this->validateCategoryConstant($p_constant)) {
            $oldConstant = $p_constant;

            $p_constant = time();

            isys_application::instance()->container->get('notify')->warning(isys_application::instance()->container->get('language')
                ->get('LC__CMDB__CUSTOM_CATEGORIES_INVALID_CONSTANT_WARNING', [$oldConstant, $p_constant]), ['sticky' => true]);
        }

        $p_constant = $this->prepareCategoryConstant($p_constant);

        if ($this->validate($p_title)) {
            $l_sql = 'INSERT INTO isysgui_catg_custom SET
	   		    isysgui_catg_custom__title = ' . $this->convert_sql_text($p_title) . ',
	   		    isysgui_catg_custom__type = ' . $this->convert_sql_int(isys_cmdb_dao_category::TYPE_EDIT) . ',
	   		    isysgui_catg_custom__parent = ' . $this->convert_sql_int($p_parent) . ',
	   		    isysgui_catg_custom__config = ' . $this->convert_sql_text(serialize($p_config)) . ',
	   		    isysgui_catg_custom__const = ' . $this->convert_sql_text($p_constant) . ',
	   		    isysgui_catg_custom__sort = ' . $this->convert_sql_int($p_sort) . ',
	   		    isysgui_catg_custom__status = ' . C__RECORD_STATUS__NORMAL . ',
	   		    isysgui_catg_custom__list_multi_value = ' . $this->convert_sql_boolean($p_multivalued) . ';';

            if ($this->update($l_sql) && $this->apply_update()) {
                $l_last_id = $this->get_last_insert_id();

                $l_upd_prop = isys_factory::get_instance('isys_update_property_migration');

                $l_upd_prop->set_database($this->get_database_component())
                    ->reset_property_table(C__CMDB__CATEGORY__TYPE_CUSTOM)
                    ->collect_category_data(C__CMDB__CATEGORY__TYPE_CUSTOM)
                    ->prepare_sql_queries('g_custom', false)
                    ->execute_sql();

                return $l_last_id;
            } else {
                return false;
            }
        } else {
            throw new Exception("Not all required fields are filled.");
        }
    }

    /**
     * Method for counting all orphaned data.
     *
     * @return integer
     */
    public function countOrphanedData()
    {
        try {
            $usedAttributes = [];

            // Step one: collect all "in-use" attributes of all custom categories.
            $categoryResult = $this->get_data();

            while ($categoryRow = $categoryResult->get_row()) {
                $categoryDefinition = unserialize($categoryRow['isysgui_catg_custom__config']);

                if (is_array($categoryDefinition)) {
                    $usedAttributes = array_merge($usedAttributes, array_keys($categoryDefinition));
                }
            }

            $usedAttributes = array_map(function ($key) {
                return $this->convert_sql_text($key);
            }, $usedAttributes);

            // Step two: Count all data of all attributes, that are not in our collection.
            $countQuery = 'SELECT COUNT(*) as orphanCount 
                FROM isys_catg_custom_fields_list 
                WHERE isys_catg_custom_fields_list__field_key NOT IN (' . implode(',', $usedAttributes) . ') 
                AND isys_catg_custom_fields_list__field_key NOT LIKE "C__CMDB__CAT__COMMENTARY_%";';

            return (int) $this->retrieve($countQuery)->get_row_value('orphanCount');
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * Method for deleting all orphaned data.
     *
     * @return boolean
     */
    public function removeOrphanedData()
    {
        try {
            $usedAttributes = [];

            // Step one: collect all "in-use" attributes of all custom categories.
            $categoryResult = $this->get_data();

            while ($categoryRow = $categoryResult->get_row()) {
                $categoryDefinition = unserialize($categoryRow['isysgui_catg_custom__config']);

                if (is_array($categoryDefinition)) {
                    $usedAttributes = array_merge($usedAttributes, array_keys($categoryDefinition));
                }
            }

            $usedAttributes = array_map(function ($key) {
                return $this->convert_sql_text($key);
            }, $usedAttributes);

            // Step two: Delete all orphaned data.
            $deleteQuery = 'DELETE FROM isys_catg_custom_fields_list 
                WHERE isys_catg_custom_fields_list__field_key NOT IN (' . implode(',', $usedAttributes) . ') 
                AND isys_catg_custom_fields_list__field_key NOT LIKE "C__CMDB__CAT__COMMENTARY_%";';

            return $this->update($deleteQuery) && $this->apply_update();
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * @param string $fieldConst
     *
     * @return string
     * @throws Exception
     */
    public function getFieldTitleByConst(string $fieldConst) : string
    {
        $db = $this->get_database_component();

        $sql = "SELECT isys_catg_custom_fields_list__isysgui_catg_custom__id as id FROM isys_catg_custom_fields_list
                            WHERE isys_catg_custom_fields_list__field_key LIKE '" . $fieldConst . "' LIMIT 1";
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        $catgCustomId = $row['id'];

        $sql = "SELECT isysgui_catg_custom__config as config FROM isysgui_catg_custom 
                            WHERE isysgui_catg_custom__id = " . $catgCustomId . " LIMIT 1";
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        $fieldsSerializedData = $row['config'];
        $fieldsArray = unserialize($fieldsSerializedData);
        $fieldTitle = $fieldsArray[$fieldConst]['title'];

        return $fieldTitle;
    }
}
