<?php

include_once 'config.php';

class posts_model
{

    public $link;

    public function __construct()
    {
        $db_connection = new dbConnection();
        $this->link = $db_connection->connect();
    }

    public function countActivePosts() {
        $sql = "SELECT COUNT(*) AS total FROM hrdc_posts WHERE post_status = 'publish'";
        $stmt = $this->link->prepare($sql);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row['total'];
    }

    public function countNumcategories() {
        $sql = "SELECT COUNT(*) AS total FROM hrdc_categories";
        $stmt = $this->link->prepare($sql);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row['total'];
    }
    public function count_releases() {
        $sql = "SELECT COUNT(*) AS total FROM hrdc_resources";
        $stmt = $this->link->prepare($sql);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row['total'];
    }

    public function get_all_posts()
    {
        $sql = "SELECT * FROM hrdc_posts WHERE post_status IN('publish', 'draft') ORDER BY ID DESC";
        $stmt = $this->link->prepare($sql);
        $stmt->execute();
        $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $posts;
    }   

    public function get_comment_count_by_post($post_id) {
        
        
       
        $sql = "SELECT COUNT(*) AS comment_count FROM hrdc_comments WHERE comment_post_ID = :post_id";
        
        // Prepare the query
        $stmt = $this->link->prepare($sql);
        
        // Bind the post_id parameter to the query
        $stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
        
        // Execute the query
        $stmt->execute();
        
        // Fetch the result
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Return the comment count
        return $result['comment_count'];
    }

    public function get_comment_status($post_id) {
        // Prepare the SQL query to fetch the comment status of the post
        $query = "SELECT comment_status FROM hrdc_posts WHERE ID = :post_id";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':post_id', $post_id);
        $stmt->execute();

        // Fetch the comment status
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $row['comment_status'] ?? 'closed'; // Default to 'closed' if no result found
    }

    public function update_comment_status($post_id, $new_status) {
        // Prepare the SQL query to update the comment status of the post
        $query = "UPDATE hrdc_posts SET comment_status = :comment_status WHERE ID = :post_id";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':post_id', $post_id);
        $stmt->bindParam(':comment_status', $new_status);

        // Execute the query and return success status
        return $stmt->execute();
    }

    public function get_post_by_id($id) {
        $query = "SELECT * FROM hrdc_posts WHERE ID = :id";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);  // Return post data
    }

    // Get comments for a post
    public function get_comments_by_post($post_id) {
        $query = "SELECT * FROM hrdc_comments WHERE comment_post_ID = :post_id";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);  // Return comments as an array
    }

    public function get_categories() {
    $query = "SELECT 
                cat.*, 
                us.user_fullname AS operator,
                COUNT(post.post_category) AS total
            FROM 
                hrdc_categories AS cat
            LEFT JOIN 
                hrdc_users AS us 
            ON cat.created_by = us.ID
            LEFT JOIN 
                hrdc_posts AS post 
            ON cat.category_id = post.post_category
            AND post.post_status IN('publish', 'draft')  -- Moved condition to ON clause
            GROUP BY 
                cat.category_id, us.user_fullname";

    $stmt = $this->link->query($query);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}



    public function add_post($author_id, $post_date, $content, $title, $category_id, $post_status, $comment_status, $post_name, $image) {
        

        // Prepare the SQL query
        $query = "INSERT INTO hrdc_posts (post_author, post_date, post_content, post_title, post_category, post_status, comment_status, post_name, image)
                  VALUES (:author_id, :post_date, :content, :title, :category_id, :post_status, :comment_status, :post_name, :image)";
        
        // Prepare statement
        $stmt = $this->link->prepare($query);

        // Bind parameters
        $stmt->bindParam(':author_id', $author_id);
        $stmt->bindParam(':post_date', $post_date);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':category_id', $category_id);
        $stmt->bindParam(':post_status', $post_status);
        $stmt->bindParam(':comment_status', $comment_status);
        $stmt->bindParam(':post_name', $post_name); 
        $stmt->bindParam(':image', $image);

        // Execute the query
        if ($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

    public function mark_post_as_deleted($postId) {
        // SQL query to update the post status to 'deleted'
        $query = "UPDATE hrdc_posts SET post_status = 'deleted' WHERE ID = :postId";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':postId', $postId, PDO::PARAM_INT);

        return $stmt->execute();
    }

    // Fetch category details by category_id
    public function get_category_by_id($category_id) {
            $query = "SELECT 
                        cat.*, 
                        us.user_fullname AS operator,
                        COUNT(post.post_category) AS total
                    FROM 
                        hrdc_categories AS cat
                    LEFT JOIN 
                        hrdc_users AS us ON cat.created_by = us.ID
                    LEFT JOIN 
                        hrdc_posts AS post ON cat.category_id = post.post_category
                    WHERE 
                        cat.category_id = :category_id
                        AND post.post_status IN('publish', 'draft')
                    GROUP BY 
                        cat.category_id";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':category_id', $category_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Fetch posts associated with a specific category
    public function get_posts_by_category($category_id) {
        $query = "SELECT post_title, post_date, post_status, ID
                FROM hrdc_posts
                WHERE post_category = :category_id AND post_status IN('publish', 'draft')
                ORDER BY post_date DESC";
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':category_id', $category_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function update_category($category_id, $category_name) {
        // Prepare the SQL query to update the category
        $query = "UPDATE hrdc_categories SET category_name = :category_name WHERE category_id = :category_id";

        // Prepare and bind parameters
        $stmt = $this->link->prepare($query);
        $stmt->bindParam(':category_name', $category_name, PDO::PARAM_STR);
        $stmt->bindParam(':category_id', $category_id, PDO::PARAM_INT);

        // Execute the query
        return $stmt->execute();
    }

        public function add_category($category_name, $date_created, $created_by) {
        
        
            $query = "INSERT INTO hrdc_categories (category_name, date_created, created_by) 
                    VALUES (:category_name, :date_created, :created_by)";

            // Prepare the statement
            $stmt = $this->link->prepare($query);

            // Bind the parameters
            $stmt->bindParam(':category_name', $category_name, PDO::PARAM_STR);
            $stmt->bindParam(':date_created', $date_created, PDO::PARAM_STR);
            $stmt->bindParam(':created_by', $created_by, PDO::PARAM_STR);

            // Execute the query and return the result (true if successful, false if failed)
            return $stmt->execute();
        }

    public function update_post($postId, $title, $content, $categoryId, $status, $commentStatus, $post_modified, $image = null) {
        // Start the SQL query with required fields
        $sql = "UPDATE hrdc_posts SET 
                    post_content = :post_content, 
                    post_title = :post_title,  
                    post_category = :category, 
                    post_status = :post_status, 
                    comment_status = :comment_status, 
                    post_modified = :post_modified";
        
        // If image is provided, update it
        if ($image !== null) {
            $sql .= ", image = :image";
        }

        // Add the WHERE clause
        $sql .= " WHERE ID = :post_id";

        // Prepare the statement
        $stmt = $this->link->prepare($sql);

        // Bind the parameters
        $stmt->bindParam(':post_title', $title, PDO::PARAM_STR);
        $stmt->bindParam(':post_content', $content, PDO::PARAM_STR);
        $stmt->bindParam(':category', $categoryId, PDO::PARAM_INT);
        $stmt->bindParam(':post_status', $status, PDO::PARAM_STR);
        $stmt->bindParam(':comment_status', $commentStatus, PDO::PARAM_STR);
        $stmt->bindParam(':post_modified', $post_modified, PDO::PARAM_STR);

        // Bind the image parameter only if it is not NULL
        if ($image !== null) {
            $stmt->bindParam(':image', $image, PDO::PARAM_STR);
        }

        // Bind the post ID
        $stmt->bindParam(':post_id', $postId, PDO::PARAM_INT);

        // Execute the statement
        return $stmt->execute();
    }


    public function get_press_releases(){
        $sql = "SELECT * FROM hrdc_resources WHERE post_state IN (1,2)";
        $smtp = $this->link->prepare($sql);
        $smtp->execute();
        $data = $smtp->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }







}