<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Testimonial Model
 *
 * Handles student testimonials/reviews data retrieval with multiple
 * fallback strategies for different column naming conventions.
 */
class Testimonial extends Model
{
    protected string $table = 'testimonials';

    /**
     * Get approved/active testimonials.
     */
    public function getApproved(int $limit = 6): array
    {
        // Strategy 1: Try `approved` = 1 (our schema column)
        try {
            $sql = sprintf('SELECT * FROM `%s` WHERE `approved` = 1 AND `active` = 1 ORDER BY `created_at` DESC LIMIT :limit', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            if (!empty($results)) return $results;
        } catch (\Throwable $e) { }

        // Strategy 2: Try `status` = 'approved'
        try {
            $sql = sprintf('SELECT * FROM `%s` WHERE `status` = :status ORDER BY `created_at` DESC LIMIT :limit', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':status', 'approved', PDO::PARAM_STR);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            if (!empty($results)) return $results;
        } catch (\Throwable $e) { }

        // Strategy 3: Try `is_approved` = 1
        try {
            $sql = sprintf('SELECT * FROM `%s` WHERE `is_approved` = 1 ORDER BY `created_at` DESC LIMIT :limit', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            if (!empty($results)) return $results;
        } catch (\Throwable $e) { }

        // Strategy 4: Try just `active` = 1
        try {
            $sql = sprintf('SELECT * FROM `%s` WHERE `active` = 1 ORDER BY `id` DESC LIMIT :limit', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            if (!empty($results)) return $results;
        } catch (\Throwable $e) { }

        // Strategy 5: Get all testimonials
        try {
            $sql = sprintf('SELECT * FROM `%s` ORDER BY `id` DESC LIMIT :limit', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Get the average rating of all approved testimonials.
     */
    public function getAverageRating(): float
    {
        try {
            $sql = sprintf('SELECT AVG(`rating`) AS avg_rating FROM `%s` WHERE `approved` = 1', $this->table);
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return (float) ($result['avg_rating'] ?? 0.0);
        } catch (\Throwable $e) {
            try {
                $sql = sprintf('SELECT AVG(`rating`) AS avg_rating FROM `%s`', $this->table);
                $stmt = $this->db->prepare($sql);
                $stmt->execute();
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                return (float) ($result['avg_rating'] ?? 0.0);
            } catch (\Throwable $e2) {
                return 0.0;
            }
        }
    }
}
