<?php

namespace idoit\Module\Pro\Model\CategoryFolders;

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

/**
 * Folder model
 *
 * @copyright synetics GmbH
 * @license   http://www.i-doit.com/license
 */
class Category extends Base
{
    private const FIELDS = [
        'isys_category__id'                        => 'id',
        'isys_category__isys_category_folders__id' => 'folder',
        'isys_category__isysgui_catg__id'          => 'global',
        'isys_category__isysgui_catg_custom__id'   => 'custom',
        'isys_category__isysgui_cats__id'          => 'specific',
        'isys_category__order'                     => 'order',
    ];

    private const FOLDER_FIELDS = [
        'isys_category_folders__id'                       => 'f_id',
        'isys_category_folders__parent'                   => 'f_parent',
        'isys_category_folders__isys_category_config__id' => 'f_config',
        'isys_category_folders__title'                    => 'f_title',
        'isys_category_folders__order'                    => 'f_order',
    ];

    private const ISYSGUI_FIELDS = [
        // Global category.
        'isysgui_catg__id'                  => 'g_id',
        'isysgui_catg__title'               => 'g_title',
        'isysgui_catg__const'               => 'g_const',
        'isysgui_catg__source_table'        => 'g_source_table',
        'isysgui_catg__class_name'          => 'g_class_name',
        // Custom category.
        'isysgui_catg_custom__id'           => 'c_id',
        'isysgui_catg_custom__title'        => 'c_title',
        'isysgui_catg_custom__const'        => 'c_const',
        'isysgui_catg_custom__source_table' => 'c_source_table',
        'isysgui_catg_custom__class_name'   => 'c_class_name',
        // Specific category.
        'isysgui_cats__id'                  => 's_id',
        'isysgui_cats__title'               => 's_title',
        'isysgui_cats__const'               => 's_const',
        'isysgui_cats__source_table'        => 's_source_table',
        'isysgui_cats__class_name'          => 's_class_name',
    ];

    /**
     * @param int      $folderId
     * @param int|null $global
     * @param int|null $custom
     * @param int|null $specific
     * @param int|null $order
     *
     * @return int|null
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function create(int $folderId, ?int $global, ?int $custom, ?int $specific, int $order): ?int
    {
        $global = $this->convert_sql_id($global);
        $custom = $this->convert_sql_id($custom);
        $specific = $this->convert_sql_id($specific);
        $order = $this->convert_sql_int($order);

        $sql = "INSERT INTO isys_category SET
            isys_category__isys_category_folders__id = {$folderId},
            isys_category__isysgui_catg__id = {$global},
            isys_category__isysgui_catg_custom__id = {$custom},
            isys_category__isysgui_cats__id = {$specific},
            isys_category__order = {$order};";

        if ($this->update($sql) && $this->apply_update()) {
            return (int) $this
                ->retrieve("SELECT isys_category__id AS id FROM isys_category WHERE isys_category__isys_category_folders__id = {$folderId} LIMIT 1;")
                ->get_row_value('id');
        }

        return null;
    }

    /**
     * @param int      $categoryId
     * @param int|null $folderId
     * @param int|null $order
     *
     * @return bool
     * @throws isys_exception_database
     */
    public function updateCategory(int $categoryId, int $folderId = null, int $order = null): bool
    {
        $values = [];

        if ($folderId !== null) {
            $values[] = "isys_category__isys_category_folders__id = {$folderId}";
        }

        if ($order !== null) {
            $values[] = "isys_category__order = {$order}";
        }

        if (count($values)) {
            $sql = "UPDATE isys_category SET
                " . implode(', ', $values) . "
                WHERE isys_category__id = {$categoryId}
                LIMIT 1;";

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

        return true;
    }

    /**
     * @param int $folderId
     *
     * @return isys_component_dao_result
     * @throws isys_exception_database
     */
    public function getAllByFolder(int $folderId): isys_component_dao_result
    {
        $fields = $this->selectImplode(self::FIELDS + self::ISYSGUI_FIELDS);

        $sql = "SELECT {$fields}
            FROM isys_category
            LEFT JOIN isysgui_catg ON isysgui_catg__id = isys_category__isysgui_catg__id
            LEFT JOIN isysgui_catg_custom ON isysgui_catg_custom__id = isys_category__isysgui_catg_custom__id
            LEFT JOIN isysgui_cats ON isysgui_cats__id = isys_category__isysgui_cats__id
            WHERE isys_category__isys_category_folders__id = {$folderId}
            ORDER BY isys_category__order ASC;";

        return $this->retrieve($sql);
    }

    /**
     * @param int $configId
     *
     * @return isys_component_dao_result
     * @throws isys_exception_database
     */
    public function getAllByConfig(int $configId): isys_component_dao_result
    {
        $fields = $this->selectImplode(self::FIELDS + self::FOLDER_FIELDS + self::ISYSGUI_FIELDS);

        $sql = "SELECT {$fields}
            FROM isys_category
            INNER JOIN isys_category_folders ON isys_category_folders__id = isys_category__isys_category_folders__id
            LEFT JOIN isysgui_catg ON isysgui_catg__id = isys_category__isysgui_catg__id
            LEFT JOIN isysgui_catg_custom ON isysgui_catg_custom__id = isys_category__isysgui_catg_custom__id
            LEFT JOIN isysgui_cats ON isysgui_cats__id = isys_category__isysgui_cats__id
            WHERE isys_category_folders__isys_category_config__id = {$configId}
            ORDER BY isys_category__order ASC, isys_category_folders__order ASC;";

        return $this->retrieve($sql);
    }

    /**
     * @param int $id
     *
     * @return array|null
     * @throws isys_exception_database
     */
    public function getById(int $id): ?array
    {
        $fields = $this->selectImplode(self::FIELDS);

        $sql = "SELECT {$fields} FROM isys_category WHERE isys_category__id = {$id} LIMIT 1;";

        return $this->retrieve($sql)->get_row();
    }

    /**
     * @param int $configId
     *
     * @return bool
     * @throws isys_exception_dao
     */
    public function deleteAllByConfig(int $configId): bool
    {
        $sql = "DELETE FROM isys_category
            WHERE isys_category__isys_category_folders__id IN (
                SELECT isys_category_folders__id FROM isys_category_folders WHERE isys_category_folders__isys_category_config__id = {$configId}
            );";

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