<?php

class Helpdesk {
    private $conn;
    private $table_name = "helpdesk_tickets";

    public $id_ticket;
    public $reporter_name;
    public $problem_description;
    public $category;
    public $unit;
    public $reporter_contact;
    public $status;
    public $priority;
    public $assigned_to_user_id;
    public $created_at;
    public $updated_at;
    public $response_at;
    public $resolved_at;
    public $resolution_details;
    public $assigned_to_name; // To avoid deprecation warning
    public $is_read;

    public function __construct($db) {
        $this->conn = $db;
    }

    // Create a new helpdesk ticket
    public function create() {
        $query = "INSERT INTO " . $this->table_name . "
                  SET
                    category = :category,
                    unit = :unit,
                    reporter_name = :reporter_name,
                    reporter_contact = :reporter_contact,
                    problem_description = :problem_description,
                    status = :status,
                    priority = :priority";

        $stmt = $this->conn->prepare($query);

        // Sanitize
        $this->category = htmlspecialchars(strip_tags($this->category));
        $this->unit = htmlspecialchars(strip_tags($this->unit));
        $this->reporter_name = htmlspecialchars(strip_tags($this->reporter_name));
        $this->reporter_contact = htmlspecialchars(strip_tags($this->reporter_contact));
        $this->problem_description = htmlspecialchars(strip_tags($this->problem_description));
        $this->status = htmlspecialchars(strip_tags($this->status));
        $this->priority = htmlspecialchars(strip_tags($this->priority));

        // Bind values
        $stmt->bindParam(":category", $this->category);
        $stmt->bindParam(":unit", $this->unit);
        $stmt->bindParam(":reporter_name", $this->reporter_name);
        $stmt->bindParam(":reporter_contact", $this->reporter_contact);
        $stmt->bindParam(":problem_description", $this->problem_description);
        $stmt->bindParam(":status", $this->status);
        $stmt->bindParam(":priority", $this->priority);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // Read all tickets with optional filtering and pagination
    public function readAll($date_filter = '', $status_filter = '', $unit_filter = '', $page = 1, $records_per_page = 10) {
        $query = "SELECT 
                    ht.id_ticket, ht.category, ht.unit, ht.reporter_name, ht.problem_description, 
                    ht.status, ht.priority, ht.created_at, ht.updated_at, ht.response_at, ht.resolved_at,
                    u.nama_lengkap AS assigned_to_name
                  FROM " . $this->table_name . " ht
                  LEFT JOIN users u ON ht.assigned_to_user_id = u.id_user";
        
        $where_clauses = [];
        $params = [];

        if (!empty($date_filter)) {
            $where_clauses[] = "DATE(ht.created_at) = :date_filter";
            $params[':date_filter'] = $date_filter;
        }
        if (!empty($status_filter)) {
            $where_clauses[] = "ht.status = :status_filter";
            $params[':status_filter'] = $status_filter;
        }
        if (!empty($unit_filter)) {
            $where_clauses[] = "ht.unit LIKE :unit_filter";
            $params[':unit_filter'] = "%" . $unit_filter . "%";
        }

        if (!empty($where_clauses)) {
            $query .= " WHERE " . implode(" AND ", $where_clauses);
        }

        $query .= " ORDER BY ht.created_at DESC";

        // Pagination
        $from_record_num = ($records_per_page * $page) - $records_per_page;
        $query .= " LIMIT :from_record_num, :records_per_page";

        $stmt = $this->conn->prepare($query);
        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
        $stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);

        $stmt->execute();
        return $stmt;
    }

    // Count all tickets with optional filtering
    public function countAll($date_filter = '', $status_filter = '', $unit_filter = '') {
        $query = "SELECT COUNT(*) as total_rows
                  FROM " . $this->table_name . " ht";
        
        $where_clauses = [];
        $params = [];

        if (!empty($date_filter)) {
            $where_clauses[] = "DATE(ht.created_at) = :date_filter";
            $params[':date_filter'] = $date_filter;
        }
        if (!empty($status_filter)) {
            $where_clauses[] = "ht.status = :status_filter";
            $params[':status_filter'] = $status_filter;
        }
        if (!empty($unit_filter)) {
            $where_clauses[] = "ht.unit LIKE :unit_filter";
            $params[':unit_filter'] = "%" . $unit_filter . "%";
        }

        if (!empty($where_clauses)) {
            $query .= " WHERE " . implode(" AND ", $where_clauses);
        }

        $stmt = $this->conn->prepare($query);
        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row['total_rows'];
    }

    // Read single ticket by ID
    public function readOne() {
        $query = "SELECT 
                    ht.id_ticket, ht.category, ht.unit, ht.reporter_name, ht.reporter_contact, ht.problem_description, 
                    ht.status, ht.priority, ht.assigned_to_user_id, ht.created_at, ht.updated_at, ht.response_at, ht.resolved_at, ht.resolution_details,
                    u.nama_lengkap AS assigned_to_name
                  FROM " . $this->table_name . " ht
                  LEFT JOIN users u ON ht.assigned_to_user_id = u.id_user
                  WHERE ht.id_ticket = ?
                  LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id_ticket);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row) {
            $this->id_ticket = $row['id_ticket'];
            $this->category = $row['category'];
            $this->unit = $row['unit'];
            $this->reporter_name = $row['reporter_name'];
            $this->reporter_contact = $row['reporter_contact'];
            $this->problem_description = $row['problem_description'];
            $this->status = $row['status'];
            $this->priority = $row['priority'];
            $this->assigned_to_user_id = $row['assigned_to_user_id'];
            $this->created_at = $row['created_at'];
            $this->updated_at = $row['updated_at'];
            $this->response_at = $row['response_at'];
            $this->resolved_at = $row['resolved_at'];
            $this->resolution_details = $row['resolution_details'];
            $this->assigned_to_name = $row['assigned_to_name'] ?? null;
            return true;
        }
        return false;
    }

    // Update a ticket and log history
    public function update($user_id_making_change) {
        // 1. Get current state of the ticket before updating
        $old_ticket = new Helpdesk($this->conn);
        $old_ticket->id_ticket = $this->id_ticket;
        if (!$old_ticket->readOne()) {
            return false; // Ticket not found
        }

        // 2. Prepare the update query
        $query = "UPDATE " . $this->table_name . "
                  SET
                    status = :status,
                    priority = :priority,
                    assigned_to_user_id = :assigned_to_user_id,
                    response_at = :response_at,
                    resolved_at = :resolved_at,
                    resolution_details = :resolution_details
                  WHERE
                    id_ticket = :id_ticket";

        $stmt = $this->conn->prepare($query);

        // Sanitize new values
        $this->status = htmlspecialchars(strip_tags($this->status));
        $this->priority = htmlspecialchars(strip_tags($this->priority));
        $this->assigned_to_user_id = $this->assigned_to_user_id ? htmlspecialchars(strip_tags($this->assigned_to_user_id)) : null;
        $this->resolution_details = $this->resolution_details ? htmlspecialchars(strip_tags($this->resolution_details)) : null;
        $this->id_ticket = htmlspecialchars(strip_tags($this->id_ticket));

        // Bind new values
        $stmt->bindParam(":status", $this->status);
        $stmt->bindParam(":priority", $this->priority);
        $stmt->bindParam(":assigned_to_user_id", $this->assigned_to_user_id);
        $stmt->bindParam(":response_at", $this->response_at);
        $stmt->bindParam(":resolved_at", $this->resolved_at);
        $stmt->bindParam(":resolution_details", $this->resolution_details);
        $stmt->bindParam(":id_ticket", $this->id_ticket);

        // 3. Execute the update
        if (!$stmt->execute()) {
            return false;
        }

        // 4. Log changes if the update was successful
        if ($old_ticket->status !== $this->status) {
            $this->logHistory($this->id_ticket, $user_id_making_change, 'Status', $old_ticket->status, $this->status);
        }
        if ($old_ticket->priority !== $this->priority) {
            $this->logHistory($this->id_ticket, $user_id_making_change, 'Prioritas', $old_ticket->priority, $this->priority);
        }
        if ($old_ticket->assigned_to_user_id !== $this->assigned_to_user_id) {
            // To make logs more readable, we can fetch user names
            $old_assignee_name = $old_ticket->assigned_to_name ?? 'Tidak ada';
            
            $new_assignee_name = 'Tidak ada';
            if ($this->assigned_to_user_id) {
                $user = new User($this->conn);
                $user->id_user = $this->assigned_to_user_id;
                $user->getById(); // Correct method call
                $new_assignee_name = $user->nama_lengkap ?? 'ID Pengguna: ' . $this->assigned_to_user_id;
            }
            $this->logHistory($this->id_ticket, $user_id_making_change, 'Ditugaskan ke', $old_assignee_name, $new_assignee_name);
        }
        if ($old_ticket->resolution_details !== $this->resolution_details) {
            $this->logHistory($this->id_ticket, $user_id_making_change, 'Detail Resolusi', $old_ticket->resolution_details, $this->resolution_details);
        }
        
        return true;
    }

    // Get all history for a specific ticket
    public function getHistory($ticket_id) {
        $history_query = "SELECT h.*, u.nama_lengkap 
                          FROM helpdesk_ticket_history h
                          LEFT JOIN users u ON h.user_id = u.id_user
                          WHERE h.ticket_id = ? 
                          ORDER BY h.changed_at DESC";
        $stmt = $this->conn->prepare($history_query);
        $stmt->bindParam(1, $ticket_id);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    // Private function to log a change
    private function logHistory($ticket_id, $user_id, $field, $old_value, $new_value) {
        $log_query = "INSERT INTO helpdesk_ticket_history 
                      (ticket_id, user_id, field_changed, old_value, new_value)
                      VALUES (:ticket_id, :user_id, :field_changed, :old_value, :new_value)";
        $stmt = $this->conn->prepare($log_query);

        $stmt->bindParam(':ticket_id', $ticket_id);
        $stmt->bindParam(':user_id', $user_id);
        $stmt->bindParam(':field_changed', $field);
        $stmt->bindParam(':old_value', $old_value);
        $stmt->bindParam(':new_value', $new_value);

        $stmt->execute();
    }

    // Count all unread helpdesk tickets
    public function countUnread() {
        $query = "SELECT COUNT(*) as unread_count FROM " . $this->table_name . " WHERE is_read = FALSE";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row['unread_count'];
    }

    // Mark all unread tickets as read
    public function markAllAsRead() {
        $query = "UPDATE " . $this->table_name . " SET is_read = TRUE WHERE is_read = FALSE";
        $stmt = $this->conn->prepare($query);
        if ($stmt->execute()) {
            return $stmt->rowCount(); // Returns the number of tickets that were updated
        }
        return false;
    }
}

