<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

/**
 * Gallery Model
 *
 * Handles photo gallery items for the Gmind Institute website.
 * Uses fallback queries when optional columns (sort_order, created_at) are missing.
 */
class Gallery extends Model
{
    protected string $table = 'gallery';

    /**
     * Get all active gallery items ordered by sort_order.
     *
     * @return array<int, array<string, mixed>>
     */
    public function getActive(): array
    {
        try {
            $stmt = $this->db->query(
                'SELECT * FROM `' . $this->table . '` WHERE `active` = 1 ORDER BY `sort_order` ASC, `created_at` DESC'
            );
            return $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {
            // Fallback: try without sort_order/created_at (columns might not exist yet)
            try {
                $stmt = $this->db->query('SELECT * FROM `' . $this->table . '` WHERE `active` = 1 ORDER BY `id` DESC');
                return $stmt->fetchAll() ?: [];
            } catch (\Throwable $e2) {
                // Last resort: get all rows
                try {
                    $stmt = $this->db->query('SELECT * FROM `' . $this->table . '` ORDER BY `id` DESC');
                    return $stmt->fetchAll() ?: [];
                } catch (\Throwable $e3) {
                    return [];
                }
            }
        }
    }

    /**
     * Get active gallery items for homepage display.
     *
     * @param int $limit Maximum number of items
     * @return array<int, array<string, mixed>>
     */
    public function getForHome(int $limit = 8): array
    {
        try {
            $stmt = $this->db->prepare(
                'SELECT * FROM `' . $this->table . '` WHERE `active` = 1 ORDER BY `sort_order` ASC, `created_at` DESC LIMIT :limit'
            );
            $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {
            // Fallback: try without sort_order/created_at
            try {
                $stmt = $this->db->prepare('SELECT * FROM `' . $this->table . '` WHERE `active` = 1 ORDER BY `id` DESC LIMIT :limit');
                $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
                $stmt->execute();
                return $stmt->fetchAll() ?: [];
            } catch (\Throwable $e2) {
                // Last resort: get all without WHERE
                try {
                    $stmt = $this->db->prepare('SELECT * FROM `' . $this->table . '` ORDER BY `id` DESC LIMIT :limit');
                    $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
                    $stmt->execute();
                    return $stmt->fetchAll() ?: [];
                } catch (\Throwable $e3) {
                    return [];
                }
            }
        }
    }

    /**
     * Get distinct gallery categories.
     *
     * @return array<int, string>
     */
    public function getCategories(): array
    {
        try {
            $stmt = $this->db->query(
                'SELECT DISTINCT `category` FROM `' . $this->table . '` WHERE `active` = 1 AND `category` IS NOT NULL AND `category` != "" ORDER BY `category` ASC'
            );
            $rows = $stmt->fetchAll() ?: [];
            return array_column($rows, 'category');
        } catch (\Throwable $e) {
            // Fallback: try without active filter
            try {
                $stmt = $this->db->query(
                    'SELECT DISTINCT `category` FROM `' . $this->table . '` WHERE `category` IS NOT NULL AND `category` != "" ORDER BY `category` ASC'
                );
                $rows = $stmt->fetchAll() ?: [];
                return array_column($rows, 'category');
            } catch (\Throwable $e2) {
                return [];
            }
        }
    }

    /**
     * Get gallery items by category.
     *
     * @param string $category Category filter
     * @return array<int, array<string, mixed>>
     */
    public function getByCategory(string $category): array
    {
        try {
            $stmt = $this->db->prepare(
                'SELECT * FROM `' . $this->table . '` WHERE `active` = 1 AND `category` = :category ORDER BY `sort_order` ASC, `created_at` DESC'
            );
            $stmt->execute([':category' => $category]);
            return $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {
            // Fallback
            try {
                $stmt = $this->db->prepare('SELECT * FROM `' . $this->table . '` WHERE `category` = :category ORDER BY `id` DESC');
                $stmt->execute([':category' => $category]);
                return $stmt->fetchAll() ?: [];
            } catch (\Throwable $e2) {
                return [];
            }
        }
    }
}
