Thread.php

<?php

require_once("model/DBInit.php");

class Thread {

    public static function getAllThreads() {
        $db = DBInit::getInstance();
        $stmt = $db->prepare("SELECT threads.*, users.username AS author_username,
                /*COALESCE((SELECT SUM(value) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_total,*/
                COALESCE((SELECT SUM(CASE WHEN value = 1 THEN 1 ELSE 0 END) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_plus,
                COALESCE((SELECT SUM(CASE WHEN value = -1 THEN 1 ELSE 0 END) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_minus
            FROM threads
            JOIN users ON threads.user_id = users.id
            ORDER BY created_at DESC");
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getThreadById($id) {
        $db = DBInit::getInstance();
        $stmt = $db->prepare("SELECT threads.*, users.username AS author_username,
                /*COALESCE((SELECT SUM(value) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_total,*/
                COALESCE((SELECT SUM(CASE WHEN value = 1 THEN 1 ELSE 0 END) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_plus,
                COALESCE((SELECT SUM(CASE WHEN value = -1 THEN 1 ELSE 0 END) FROM thread_votes WHERE thread_votes.thread_id = threads.id), 0) AS vote_minus
            FROM threads
            JOIN users ON threads.user_id = users.id
            WHERE threads.id = :id");
        $stmt->execute(array(":id" => $id));
        return $stmt->fetch();
    }

    public static function createThread($title, $content, $user_id) {
        $db = DBInit::getInstance();
        $stmt = $db->prepare("INSERT INTO threads (title, content, user_id) VALUES (:title, :content, :user_id)");
        $stmt->execute(array(
            ":title" => $title,
            ":content" => $content,
            ":user_id" => $user_id
        ));
        return self::getThreadById($db->lastInsertId());
    }

    public static function deleteThread($id) {
        $db = DBInit::getInstance();
        $stmt = $db->prepare("DELETE FROM threads WHERE id = :id");
        $stmt->execute(array(":id" => $id));
    }

    public static function getThreadVoteByUser($thread_id, $user_id) {
        $db = DBInit::getInstance();
        $stmt = $db->prepare("SELECT value FROM thread_votes WHERE thread_id = :thread_id AND user_id = :user_id");
        $stmt->execute(array(
            ":thread_id" => $thread_id,
            ":user_id" => $user_id
        ));
        $row = $stmt->fetch();
        return $row ? (int)$row["value"] : 0;
    }

    public static function voteThread($thread_id, $user_id, $value) {
        if ($value !== 1 && $value !== -1) {
            return;
        }

        $db = DBInit::getInstance();
        $stmt = $db->prepare("SELECT id, value FROM thread_votes WHERE thread_id = :thread_id AND user_id = :user_id");
        $stmt->execute(array(
            ":thread_id" => $thread_id,
            ":user_id" => $user_id
        ));
        $existing = $stmt->fetch();

        if ($existing) {
            if ((int)$existing["value"] === $value) {
                $delete = $db->prepare("DELETE FROM thread_votes WHERE id = :id");
                $delete->execute(array(":id" => $existing["id"]));
            } else {
                $update = $db->prepare("UPDATE thread_votes SET value = :value WHERE id = :id");
                $update->execute(array(
                    ":value" => $value,
                    ":id" => $existing["id"]
                ));
            }
        } else {
            $insert = $db->prepare("INSERT INTO thread_votes (thread_id, user_id, value) VALUES (:thread_id, :user_id, :value)");
            $insert->execute(array(
                ":thread_id" => $thread_id,
                ":user_id" => $user_id,
                ":value" => $value
            ));
        }
    }
}