<?php
declare(strict_types=1);

namespace App\Models;

use App\Lib\Database;
use App\Util\{Helper};
class Reviews
{
    protected $db;
    protected Helper $helper;
    private string $tableName = 'customer_review';

    public function __construct()
    {
        $this->db = Database::connectDB();
        $this->helper = new Helper;
    }

    public function countAll(int $restaurant_id)
    {
        $stmt = $this->db->fetch("SELECT COUNT(*) AS total, COUNT(IF(rating_seen = 'false', 1, NULL)) AS total_unseen FROM {$this->tableName} WHERE restaurant_id = ?", $restaurant_id);
        return $stmt;
    }

    public function getById(int $review_id, int $restaurant_id, string $selection = '*')
    {
        $stmt = $this->db->fetch("SELECT {$selection} FROM {$this->tableName} WHERE review_id = ? AND restaurant_id = ?", $review_id, $restaurant_id);
        return $stmt;
    }

    public function getAll(int $restaurant_id, $limit = null, $offset = null)
    {
        $query = "SELECT r.*, rr.reply_text FROM customer_review r LEFT JOIN customer_review_reply rr ON r.review_id = rr.review_id WHERE r.restaurant_id = ? ORDER BY r.created_at DESC";
        if (!is_null($limit)) {
            $query .= " LIMIT " . intval($limit);

            if (!is_null($offset)) {
                $query .= " OFFSET " . intval($offset);
            }
        }

        $stmt = $this->db->fetchAll($query, $restaurant_id);
        return $stmt;
    }

    public function getAllReply(int $review_id)
    {
        $stmt = $this->db->fetchAll("SELECT * FROM {$this->tableName}_reply WHERE review_id = ?", $review_id);
        return $stmt;
    }

    public function getStatistic(int $restaurant_id)
    {
        $query = "SELECT 
        (SELECT COUNT(rating_value) FROM customer_review WHERE restaurant_id = {$restaurant_id}) AS total_rating,
        (SELECT AVG(rating_value) FROM customer_review WHERE restaurant_id = {$restaurant_id}) AS average_rating,
        ROUND((SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id} AND rating_value >= 1 AND rating_value < 2) / (SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id}) * 100) AS percent_rating_1,
        ROUND((SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id} AND rating_value >= 2 AND rating_value < 3) / (SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id}) * 100) AS percent_rating_2,
        ROUND((SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id} AND rating_value >= 3 AND rating_value < 4) / (SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id}) * 100) AS percent_rating_3,
        ROUND((SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id} AND rating_value >= 4 AND rating_value < 5) / (SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id}) * 100) AS percent_rating_4,
        ROUND((SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id} AND rating_value = 5) / (SELECT COUNT(*) FROM customer_review WHERE restaurant_id = {$restaurant_id}) * 100) AS percent_rating_5 ";
        return $this->db->fetch($query);
    }

    public function setAsRead(int $review_id, int $restaurant_id): void
    {
        $this->db->query("UPDATE {$this->tableName} SET rating_seen = 'true' WHERE (review_id = ? AND restaurant_id = ?) AND rating_seen = 'false' ", $review_id, $restaurant_id);
    }

    public function addReply(string $reply_text, int $review_id, int $restaurant_id): void
    {
        $this->db->query("INSERT INTO {$this->tableName}_reply", [
            'reply_id' => NULL,
            'review_id' => $review_id,
            'restaurant_id' => $restaurant_id,
            'reply_text' => $reply_text
        ]);
    }
}
