<?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 Folder extends Base
{
    private const FIELDS = [
        'isys_category_folders__id'                       => 'id',
        'isys_category_folders__parent'                   => 'parent',
        'isys_category_folders__isys_category_config__id' => 'config',
        'isys_category_folders__title'                    => 'title',
        'isys_category_folders__order'                    => 'order',
    ];

    /**
     * @param int|null $parentId
     * @param int      $configId
     * @param string   $title
     * @param int|null $order
     *
     * @return int|null
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    public function create(?int $parentId, int $configId, string $title, ?int $order = null): ?int
    {
        if ($order === null) {
            $order = 0;

            // @see ID-10004 Move all previously created folders down by one, so that new folders will always appear on top.
            $sql = "UPDATE isys_category_folders
                SET isys_category_folders__order = isys_category_folders__order + 1
                WHERE isys_category_folders__isys_category_config__id = {$configId};";

            $this->update($sql);

            // Do the same to categories, so they don't get mixed up.
            $sql = "UPDATE isys_category
                SET isys_category__order = isys_category__order + 1
                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}
                );";

            $this->update($sql);
        }

        $parentId = $this->convert_sql_id($parentId);
        $title = $this->convert_sql_text($title);
        $order = $this->convert_sql_int($order);

        $sql = "INSERT INTO isys_category_folders SET
            isys_category_folders__parent = {$parentId},
            isys_category_folders__isys_category_config__id = {$configId},
            isys_category_folders__title = {$title},
            isys_category_folders__order = {$order};";

        if ($this->update($sql) && $this->apply_update()) {
            return (int) $this
                ->retrieve("SELECT MAX(isys_category_folders__id) AS id FROM isys_category_folders WHERE isys_category_folders__isys_category_config__id = {$configId} LIMIT 1;")
                ->get_row_value('id');
        }

        return null;
    }

    /**
     * @param int         $folderId
     * @param int|null    $parentFolderId
     * @param string|null $title
     * @param int|null    $order
     *
     * @return bool
     * @throws isys_exception_database
     */
    public function updateFolder(int $folderId, ?int $parentFolderId = null, ?string $title = null, ?int $order = null): bool
    {
        $values = [];

        if ($parentFolderId !== null) {
            $values[] = "isys_category_folders__parent = {$parentFolderId}";
        }

        if ($title !== null) {
            $title = $this->convert_sql_text($title);
            $values[] = "isys_category_folders__title = {$title}";
        }

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

        if (count($values)) {
            $sql = "UPDATE isys_category_folders SET
                " . implode(', ', $values) . "
                WHERE isys_category_folders__id = {$folderId}
                LIMIT 1;";

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

        return true;
    }

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

        $sql = "SELECT {$fields}
            FROM isys_category_folders
            WHERE isys_category_folders__parent = {$parentId}
            ORDER BY isys_category_folders__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);

        $sql = "SELECT {$fields}
            FROM isys_category_folders
            WHERE isys_category_folders__isys_category_config__id = {$configId}
            ORDER BY isys_category_folders__order ASC;";

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

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

        $sql = "SELECT {$fields}
            FROM isys_category_folders
            WHERE isys_category_folders__isys_category_config__id = {$configId}
            AND isys_category_folders__parent IS NULL;";

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

    /**
     * @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_folders WHERE isys_category_folders__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_folders WHERE isys_category_folders__isys_category_config__id = {$configId};";

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

    /**
     * @param int $folderId
     *
     * @return bool
     * @throws isys_exception_dao
     */
    public function deleteById(int $folderId): bool
    {
        $sql = "DELETE FROM isys_category_folders WHERE isys_category_folders__id = {$folderId} LIMIT 1;";

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