<?php

require_once BASE_PATH
    . '/config/database.php';


class GalleryItem
{
    private PDO $db;

    public function __construct()
    {
        $database =
            new Database();

        $this->db =
            $database->connect();
    }

    public function countForOwner(
        string $search = ''
    ): int {

        $params = [];
        $where = '1 = 1';

        if ($search !== '') {

            $where .= "
                AND (
                    title LIKE :search_title
                    OR caption LIKE :search_caption
                    OR category LIKE :search_category
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_caption'] = $searchValue;
            $params['search_category'] = $searchValue;
        }

        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)
                FROM gallery_items
                WHERE {$where}
            ");

        $stmt->execute($params);

        return (int)
            $stmt->fetchColumn();
    }

    public function getForOwner(
        string $search = '',
        int $limit = 20,
        int $offset = 0
    ): array {

        $params = [];
        $where = '1 = 1';

        if ($search !== '') {

            $where .= "
                AND (
                    title LIKE :search_title
                    OR caption LIKE :search_caption
                    OR category LIKE :search_category
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_title'] = $searchValue;
            $params['search_caption'] = $searchValue;
            $params['search_category'] = $searchValue;
        }

        $limit = max(1, $limit);
        $offset = max(0, $offset);

        $stmt =
            $this->db->prepare("
                SELECT *
                FROM gallery_items

                WHERE {$where}

                ORDER BY
                    sort_order ASC,
                    created_at DESC

                LIMIT {$limit}
                OFFSET {$offset}
            ");

        $stmt->execute($params);

        return $stmt->fetchAll();
    }

    public function findById(
        int $id
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT *
                FROM gallery_items
                WHERE id = :id
                LIMIT 1
            ");

        $stmt->execute([
            'id' => $id
        ]);

        $item = $stmt->fetch();

        return $item ?: null;
    }

    public function create(
        array $data
    ): int {

        $stmt =
            $this->db->prepare("
                INSERT INTO gallery_items (
                    title,
                    caption,
                    image_path,
                    category,
                    is_visible,
                    sort_order
                )
                VALUES (
                    :title,
                    :caption,
                    :image_path,
                    :category,
                    :is_visible,
                    :sort_order
                )
            ");

        $stmt->execute([
            'title' =>
                $data['title'] ?: null,

            'caption' =>
                $data['caption'] ?: null,

            'image_path' =>
                $data['image_path'],

            'category' =>
                $data['category'] ?: null,

            'is_visible' =>
                $data['is_visible'],

            'sort_order' =>
                $data['sort_order']
        ]);

        return (int)
            $this->db->lastInsertId();
    }

    public function updateItem(
        int $id,
        array $data
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE gallery_items

                SET
                    title = :title,
                    caption = :caption,
                    category = :category,
                    is_visible = :is_visible,
                    sort_order = :sort_order

                WHERE id = :id
            ");

        $stmt->execute([
            'title' =>
                $data['title'] ?: null,

            'caption' =>
                $data['caption'] ?: null,

            'category' =>
                $data['category'] ?: null,

            'is_visible' =>
                $data['is_visible'],

            'sort_order' =>
                $data['sort_order'],

            'id' =>
                $id
        ]);
    }

    public function toggle(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE gallery_items

                SET is_visible =
                    CASE
                        WHEN is_visible = 1
                        THEN 0
                        ELSE 1
                    END

                WHERE id = :id
            ");

        $stmt->execute([
            'id' => $id
        ]);
    }

    public function delete(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                DELETE FROM gallery_items
                WHERE id = :id
            ");

        $stmt->execute([
            'id' => $id
        ]);
    }
    public function getVisibleCategories(): array
{
    $stmt =
        $this->db->query("
            SELECT DISTINCT category

            FROM gallery_items

            WHERE is_visible = 1
              AND category IS NOT NULL
              AND category != ''

            ORDER BY category ASC
        ");


    return array_column(
        $stmt->fetchAll(),
        'category'
    );
}


public function countVisible(
    string $category = ''
): int {

    $params = [];

    $where = "
        is_visible = 1
    ";


    if ($category !== '') {

        $where .= "
            AND category = :category
        ";

        $params['category'] =
            $category;
    }


    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM gallery_items

            WHERE {$where}
        ");


    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn();
}


public function getVisible(
    string $category = '',
    int $limit = 20,
    int $offset = 0
): array {

    $params = [];

    $where = "
        is_visible = 1
    ";


    if ($category !== '') {

        $where .= "
            AND category = :category
        ";

        $params['category'] =
            $category;
    }


    $limit =
        max(1, $limit);

    $offset =
        max(0, $offset);


    $stmt =
        $this->db->prepare("
            SELECT *

            FROM gallery_items

            WHERE {$where}

            ORDER BY
                sort_order ASC,
                created_at DESC

            LIMIT {$limit}
            OFFSET {$offset}
        ");


    $stmt->execute($params);


    return $stmt->fetchAll();
}


public function setVisibility(
    int $id,
    bool $visible
): void {

    $stmt =
        $this->db->prepare("\n            UPDATE gallery_items\n\n            SET is_visible = :is_visible\n\n            WHERE id = :id\n        ");

    $stmt->execute([
        'is_visible' => $visible ? 1 : 0,
        'id' => $id
    ]);
}


public function countForAdmin(
    string $search = '',
    string $visibility = ''
): int {

    [$where, $params] =
        $this->buildAdminFilters(
            $search,
            $visibility
        );

    $stmt =
        $this->db->prepare("\n            SELECT COUNT(*)\n            FROM gallery_items g\n            WHERE {$where}\n        ");

    $stmt->execute($params);

    return (int) $stmt->fetchColumn();
}


public function getForAdmin(
    string $search = '',
    string $visibility = '',
    int $limit = 20,
    int $offset = 0
): array {

    [$where, $params] =
        $this->buildAdminFilters(
            $search,
            $visibility
        );

    $limit = max(1, $limit);
    $offset = max(0, $offset);

    $stmt =
        $this->db->prepare("\n            SELECT *\n            FROM gallery_items g\n            WHERE {$where}\n            ORDER BY g.sort_order ASC, g.created_at DESC\n            LIMIT {$limit} OFFSET {$offset}\n        ");

    $stmt->execute($params);

    return $stmt->fetchAll();
}


private function buildAdminFilters(
    string $search,
    string $visibility
): array {

    $where = '1 = 1';
    $params = [];

    if ($search !== '') {
        $where .= "\n            AND (\n                g.title LIKE :search_title\n                OR g.caption LIKE :search_caption\n                OR g.category LIKE :search_category\n            )\n        ";

        $searchValue = '%' . $search . '%';
        $params['search_title'] = $searchValue;
        $params['search_caption'] = $searchValue;
        $params['search_category'] = $searchValue;
    }

    if ($visibility === 'visible') {
        $where .= ' AND g.is_visible = 1';
    }

    if ($visibility === 'hidden') {
        $where .= ' AND g.is_visible = 0';
    }

    return [$where, $params];
}
}
