<?php

/**
 * i-doit
 *
 * Helper methods for installation process
 *
 * @package     i-doit
 * @subpackage  Helper
 * @author      Denis Koroliov <dkorolov@i-doit.com>
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 * @since       1.18
 */
class isys_helper_install
{
    /**
     * Preparing persons list data array
     *
     * @return array[]
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function getDataForPersonsList()
    {
        return [
                    [
                        'isys_cats_person_list__id' => 1,
                        'isys_cats_person_list__isys_connection__id' => 1,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'guest',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'guest',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'guest',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 4,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 2,
                        'isys_cats_person_list__isys_connection__id' => 2,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'reader',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'reader',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'reader',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 5,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 3,
                        'isys_cats_person_list__isys_connection__id' => 3,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'editor',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'editor',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'editor',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 6,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 4,
                        'isys_cats_person_list__isys_connection__id' => 4,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'author',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'author',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'author',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 7,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 5,
                        'isys_cats_person_list__isys_connection__id' => 5,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'archivar',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'archivar',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'archivar',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 8,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 6,
                        'isys_cats_person_list__isys_connection__id' => 6,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'admin',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'admin',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'admin',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 9,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => 'NOW()',
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 7,
                        'isys_cats_person_list__isys_connection__id' => null,
                        'isys_cats_person_list__isys_ldap__id' => null,
                        'isys_cats_person_list__title' => 'systemapi',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => null,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'systemapi',
                        'isys_cats_person_list__last_name' => 'System',
                        'isys_cats_person_list__first_name' => 'Api',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => null,
                        'isys_cats_person_list__photo' => null,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => null,
                        'isys_cats_person_list__function' => null,
                        'isys_cats_person_list__service_designation' => null,
                        'isys_cats_person_list__city' => null,
                        'isys_cats_person_list__zip_code' => null,
                        'isys_cats_person_list__street' => null,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 22,
                        'isys_cats_person_list__isys_catg_relation_list__id' => null,
                        'isys_cats_person_list__pager' => null,
                        'isys_cats_person_list__salutation' => null,
                        'isys_cats_person_list__last_login' => null,
                        'isys_cats_person_list__custom1' => null,
                        'isys_cats_person_list__custom2' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom3' => null,
                        'isys_cats_person_list__custom4' => null,
                        'isys_cats_person_list__custom6' => null,
                        'isys_cats_person_list__custom7' => null,
                        'isys_cats_person_list__custom8' => null,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ]
            ];
    }

    /**
     * Generate value part of the query for persons list
     *
     * @param $data
     *
     * @return string
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function movePersonsListDataToQueryValues($data)
    {
        $valuesString = '';
        foreach ($data as $person) {
            $valuesString .= self::generateValuesStringForQuery($person).',';
        }
        return rtrim($valuesString, ',');
    }

    /**
     * Fill the persons list table in DB
     *
     * @param $db_host
     * @param $db_username
     * @param $db_password
     * @param $db_database_name
     * @param $db_port
     *
     * @return array
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function insertPersons($db_host, $db_username, $db_password, $db_database_name, $db_port)
    {
        try {
            $dbLink = new mysqli(
                $db_host,
                $db_username,
                $db_password,
                $db_database_name,
                $db_port
            );
            $dbLink->query("SET sql_mode=''");
            $query = self::generatePersonsListDataQuery();
            $dbLink->query($query);
            $dbLink->close();
        } catch (Exception  $e) {
            return [
                'result' => false,
                'message' => $e->getMessage()
            ];
        }
        return [
            'result' => true,
            'message' => 'Success'
        ];
    }

    /**
     * Genereate SQL query for persons list
     *
     * @return string
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function generatePersonsListDataQuery()
    {
        $persons = self::getDataForPersonsList();
        $valuesToInsert = self::movePersonsListDataToQueryValues($persons);
        $dbKeys = array_keys($persons[0]);

        $query = "INSERT INTO isys_cats_person_list (" . implode(',', $dbKeys) . ") VALUES " . $valuesToInsert .";";

        return $query;
    }

    /**
     * Generate values string from data to insert persons
     *
     * @param $data
     *
     * @return string
     */
    public static function generateValuesStringForQuery($data)
    {
        $data['isys_cats_person_list__user_pass'] = isys_helper_crypt::encryptPassword($data['isys_cats_person_list__user_pass']);

        $stringKeys = [
            'isys_cats_person_list__ldap_dn',
            'isys_cats_person_list__personnel_number'
        ];

        $queryParts = [];
        foreach ($data as $key => $value) {
            if (!empty($value) || (is_numeric($value) && $value >= 0)) {
                $queryParts[] = "'" . $value . "'";
                continue;
            }

            $queryParts[] = in_array($key, $stringKeys) ? "''" : 'NULL';
        }

        return "(" . implode(',', $queryParts) . ")";
    }
}
