<?php

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

    public $id_ticket;
    public $reporter_name;
    public $problem_description;
    public $category; // New
    public $unit; // New
    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; // New: To avoid deprecation warning for dynamic property creation

    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,
                    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->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(":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
    public function readAll($date_filter = '', $status_filter = '', $unit_filter = '') {
        $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)) {
            // Assuming date_filter is in 'YYYY-MM-DD' format
            $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";

        $stmt = $this->conn->prepare($query);
        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->execute();
        return $stmt;
    }

    // Read single ticket by ID
    public function readOne() {
        $query = "SELECT 
                    ht.id_ticket, ht.category, ht.unit, ht.reporter_name, 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']; // New
            $this->unit = $row['unit']; // New
            $this->reporter_name = $row['reporter_name'];
            $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'];
            // Removed asset-related properties
            $this->assigned_to_name = $row['assigned_to_name'] ?? null;
            return true;
        }
        return false;
    }

    // Update a ticket
    public function update() {
        $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
        $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->response_at = $this->response_at ? htmlspecialchars(strip_tags($this->response_at)) : null;
        $this->resolved_at = $this->resolved_at ? htmlspecialchars(strip_tags($this->resolved_at)) : null;
        $this->resolution_details = $this->resolution_details ? htmlspecialchars(strip_tags($this->resolution_details)) : null;
        $this->id_ticket = htmlspecialchars(strip_tags($this->id_ticket));

        // Bind 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);

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