<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of forum
 *
 * @author Lenovo
 */
class Forum {

    // database connection and table name
    private $conn;
    private $forum_topic = "forum_topic";
    private $forum_topic_response = "forum_topic_response";
    // object properties
    public $topic_id;
    public $user_id;
    public $company_id;
    public $topic_name;
    public $topic_title;
    public $topic_desc;
    public $created_ts;
    public $last_updated_ts;
    public $last_updated_user;
    public $status;
    //
    public $response_id;
    public $response_desc;

    // constructor
    public function __construct() {
        $this->conn = DBConnection();
    }

    public function get_topic_names($user_id) {
        try {
            $query = "select distinct co.course_name from course_offering as co, course_assignment as ca, user_account as ua "
                    . "where co.status!='Inactive' AND ca.status IS NULL AND ca.user_id=:user_id AND ca.user_id=ua.user_id AND ca.offering_id=co.offering_id "
                    . "AND co.company_id=(select company_id from user_account where user_id=:user_id1) order by course_name asc";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id1", $user_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_forum_topic_name($user_id) {
        try {
            $query = "select distinct topic_name from forum_topic as f "
                    . "JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id2) ORDER BY CASE WHEN topic_name = 'General' THEN '1' ELSE topic_name END ASC ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id2", $user_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_user_detail() {
        try {
            $query = "SELECT u.user_image FROM user_account as u WHERE u.user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_all_questions($from_record_num, $records_per_page) {
        try {
            $query = "SELECT DISTINCT(f.topic_id) as topic_id,u.user_id,u.first_name,u.last_name,u.user_image,f.user_id,"
                    . "f.topic_title,f.topic_desc,f.created_ts,f.last_updated_ts,f.topic_name,f.status,"
                    . "IF(ftr.topic_id IS NULL and f.user_id=:user_id, FALSE, TRUE) as reply "
                    . "FROM forum_topic as f "
                    . "LEFT JOIN forum_topic_response ftr ON ftr.topic_id = f.topic_id AND ftr.status='Active'"
                    . "LEFT JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id2) "
                    . "where u.user_id=f.user_id AND f.status='Active' order by created_ts desc "
                    . "LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getCount() {
        try {
            $query = "SELECT COUNT(DISTINCT(f.topic_id)) as topic_id "
            . "FROM forum_topic as f "
            . "LEFT JOIN forum_topic_response ftr ON ftr.topic_id = f.topic_id AND ftr.status='Active' "
            . "LEFT JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id) "
            . "where u.user_id=f.user_id AND f.status='Active' ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_questions_topic_user($topic_name, $id, $from_record_num, $records_per_page) {
        try {
            $query = "SELECT DISTINCT(f.topic_id) as topic_id,u.user_id,u.first_name,u.last_name,u.user_image,"
                    . "f.user_id,f.topic_desc,f.created_ts,f.last_updated_ts,f.topic_name,f.topic_title,f.status, "
                    . "IF(ftr.topic_id IS NULL and f.user_id=:user_id, FALSE, TRUE) as reply "
                    . "FROM forum_topic as f "
                    . "LEFT JOIN forum_topic_response ftr ON ftr.topic_id = f.topic_id AND ftr.status='Active' "
                    . "LEFT JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id2) "
                    . "where u.user_id=f.user_id AND f.status='Active' AND f.topic_name=:topic_name and f.user_id=:user_id1 "
                    . "order by created_ts desc "
                    . "LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":topic_name", $topic_name, PDO::PARAM_STR);
            $stmt->bindParam(":user_id1", $id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_questions_topic($topic_name, $from_record_num, $records_per_page) {
        try {
            $query = "SELECT DISTINCT(f.topic_id) as topic_id,u.user_id,u.first_name,u.last_name,u.user_image,"
                    . "f.user_id,f.topic_desc,f.created_ts,f.last_updated_ts,f.topic_name,f.topic_title,f.status, "
                    . "IF(ftr.topic_id IS NULL and f.user_id=:user_id, FALSE, TRUE) as reply "
                    . "FROM forum_topic as f "
                    . "LEFT JOIN forum_topic_response ftr ON ftr.topic_id = f.topic_id AND ftr.status='Active'"
                    . "LEFT JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id2) "
                    . "where u.user_id=f.user_id  AND f.status='Active' AND f.topic_name=:topic_name order by created_ts desc "
                    . "LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":topic_name", $topic_name, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_questions_user($id, $from_record_num, $records_per_page) {
        try {
            $query = "SELECT DISTINCT(f.topic_id) as topic_id,u.user_id,u.first_name,u.last_name,u.user_image,"
                    . "f.user_id,f.topic_desc,f.created_ts,f.last_updated_ts,f.topic_name,f.topic_title,f.status, "
                    . "IF(ftr.topic_id IS NULL and f.user_id=:user_id, FALSE, TRUE) as reply "
                    . "FROM forum_topic as f "
                    . "LEFT JOIN forum_topic_response ftr ON ftr.topic_id = f.topic_id AND ftr.status='Active' "
                    . "LEFT JOIN user_account u on f.user_id=u.user_id AND f.company_id=(SELECT company_id from user_account where user_id=:user_id2) "
                    . "where u.user_id=f.user_id and f.user_id=:user_id1 AND f.status='Active' order by created_ts desc "
                    . "LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id1", $id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_responses() {
        try {
                 $query = "SELECT u.user_id, u.first_name, u.last_name, u.user_image,
       f.response_desc, f.user_id, f.created_ts, f.last_updated_ts, f.response_id,
       IF(f.user_id =:user_id, FALSE, TRUE) AS user
            FROM forum_topic_response AS f
            LEFT JOIN user_account AS u ON u.user_id = f.user_id
            WHERE f.topic_id =:topic_id AND f.status = 'Active'
            ORDER BY f.created_ts ASC";
            // $query = "SELECT u.user_id,u.first_name,u.last_name,u.user_image,f.response_desc,f.user_id,f.created_ts,f.last_updated_ts,f.response_id,"
            //         . "IF(f.user_id=:user_id, FALSE, TRUE) as user "
            //         . "FROM forum_topic_response as f,user_account as u "
            //         . "where topic_id=:topic_id and u.user_id=f.user_id AND f.status='Active' order by created_ts asc";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":topic_id", $this->topic_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function insert_question() {
        try {
            $query = "INSERT INTO
                forum_topic
            SET
                user_id = :user_id,
                company_id = (select company_id from user_account where user_id=:user_id1),
                department_id = (select department_id from user_account where user_id=:user_id2),
                topic_name = :topic_name,
                topic_title = :topic_title,
                topic_desc = :topic_desc,
                status = :status,
                created_ts = :created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":user_id1", $this->user_id);
            $stmt->bindParam(":user_id2", $this->user_id);
            $stmt->bindParam(":topic_name", $this->topic_name);
            $stmt->bindParam(":topic_title", $this->topic_title);
            $stmt->bindParam(":topic_desc", $this->topic_desc);
            $stmt->bindParam(":status", $this->status);
            $stmt->bindParam(":created_ts", $this->created_ts);
            $stmt->bindParam(":last_updated_user", $this->last_updated_user);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function edit_question() {
        try {
            $query = "UPDATE
                forum_topic
            SET
                topic_title = :topic_title,
                topic_desc = :topic_desc,
                last_updated_ts=:last_updated_ts
           WHERE 
                topic_id=:topic_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_title", $this->topic_title);
            $stmt->bindParam(":topic_desc", $this->topic_desc);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            $stmt->bindParam(":topic_id", $this->topic_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function insert_question_response() {
        try {
            $query = "INSERT INTO
                forum_topic_response
            SET
                topic_id=:topic_id,
                user_id = :user_id,
                response_desc = :response_desc,
                status = :status,
                created_ts = :created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_id", $this->topic_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":response_desc", $this->response_desc);
            $stmt->bindParam(":status", $this->status);
            $stmt->bindParam(":created_ts", $this->created_ts);
            $stmt->bindParam(":last_updated_user", $this->last_updated_user);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function edit_question_response() {
        try {
            $query = "UPDATE
                forum_topic_response
            SET
                response_desc = :response_desc,
                last_updated_ts=:last_updated_ts
           WHERE 
                response_id=:response_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":response_desc", $this->response_desc);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            $stmt->bindParam(":response_id", $this->response_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function delete_question() {
        try {
            $query = "DELETE from forum_topic WHERE topic_id=:topic_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_id", $this->topic_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function delete_question_spam() {
        try {
            $query = "DELETE from forum_abuse WHERE topic_id=:topic_id AND response_id='0'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_id", $this->topic_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function delete_question_response() {
        try {
            $query = "DELETE from forum_topic_response WHERE response_id=:response_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":response_id", $this->response_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function delete_question_response_spam() {
        try {
            $query = "DELETE from forum_abuse WHERE response_id=:response_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":response_id", $this->response_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_spam_topic($topic_id, $id1) {
        try {
            $query = "SELECT topic_id FROM forum_abuse WHERE topic_id=:topic_id AND user_id=:user_id AND response_id='0' AND status='Open'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":topic_id", $topic_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $id1, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_spam_reply($response_id, $id2) {
        try {
            $query = "SELECT response_id FROM forum_abuse WHERE response_id=:response_id AND user_id=:user_id  AND status='Open'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":response_id", $response_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $id2, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function remove_span_topic() {
        try {
            $query = "DELETE from forum_abuse WHERE topic_id=:topic_id AND user_id=:user_id AND response_id='0'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_id", $this->topic_id);
            $stmt->bindParam(":user_id", $this->user_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function report_span_topic_reply() {
        try {
            $query = "INSERT INTO
                forum_abuse
            SET
                topic_id=:topic_id,
                user_id = :user_id,
                response_id = :response_id,
                status = :status,
                created_ts = :created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":topic_id", $this->topic_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":response_id", $this->response_id);
            $stmt->bindParam(":status", $this->status);
            $stmt->bindParam(":created_ts", $this->created_ts);
            $stmt->bindParam(":last_updated_user", $this->last_updated_user);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function remove_span_reply() {
        try {
            $query = "DELETE from forum_abuse WHERE response_id=:response_id AND user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":response_id", $this->response_id);
            $stmt->bindParam(":user_id", $this->user_id);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    //added on 08-12-2025 for AI feature
    public function  get_course_content_grouped($user_id)
    {
        try {
            $query = "SELECT 
            co.course_name, 
            co.course_id,
            co.offering_id, 
            cd.contentdtl_id
        FROM course_offering AS co
        JOIN course_assignment AS ca ON ca.offering_id = co.offering_id
        JOIN user_account AS ua ON ca.user_id = ua.user_id
        JOIN course_content AS cc ON cc.course_id = co.course_id
        JOIN content_details AS cd ON cd.content_id = cc.content_id
        WHERE co.status != 'Inactive'
          AND ca.status IS NULL
          AND ca.user_id = :user_id
          AND co.company_id = (
              SELECT company_id 
              FROM user_account 
              WHERE user_id = :user_id1
          )
        ORDER BY co.course_name ASC;
            ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id1", $user_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    public function insert_question_ai()
    {
        try {
            $query = "INSERT INTO
                forum_topic
            SET
                user_id = :user_id,
                company_id = (select company_id from user_account where user_id=:user_id1),
                department_id = (select department_id from user_account where user_id=:user_id2),
                topic_name = :topic_name,
                topic_title = null,
                topic_desc = :topic_desc,
                status = :status,
                created_ts = :created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":user_id1", $this->user_id);
            $stmt->bindParam(":user_id2", $this->user_id);
            $stmt->bindParam(":topic_name", $this->topic_name);
            $stmt->bindParam(":topic_desc", $this->topic_desc);
            $stmt->bindParam(":status", $this->status);
            $stmt->bindParam(":created_ts", $this->created_ts);
            $stmt->bindParam(":last_updated_user", $this->last_updated_user);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return $this->conn->lastInsertId();
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
}
