<?php

class Ticket {
    private $conn;
    private $table_name = "tickets";
    private $updates_table_name = "ticket_updates";

    public $id_ticket;
    public $vendor_system;
    public $request_type;
    public $subject;
    public $description;
    public $priority;
    public $status;
    public $requester;
    public $created_at;
    public $updated_at;

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

    // Create a new ticket
    public function create() {
        $query = "INSERT INTO " . $this->table_name . "
                  SET
                    vendor_system=:vendor_system,
                    request_type=:request_type,
                    subject=:subject,
                    description=:description,
                    priority=:priority,
                    status=:status,
                    requester=:requester";

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

        // Sanitize
        $this->vendor_system = htmlspecialchars(strip_tags($this->vendor_system));
        $this->request_type = htmlspecialchars(strip_tags($this->request_type));
        $this->subject = htmlspecialchars(strip_tags($this->subject));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->priority = htmlspecialchars(strip_tags($this->priority));
        $this->status = htmlspecialchars(strip_tags($this->status));
        $this->requester = htmlspecialchars(strip_tags($this->requester));

        // Bind values
        $stmt->bindParam(":vendor_system", $this->vendor_system);
        $stmt->bindParam(":request_type", $this->request_type);
        $stmt->bindParam(":subject", $this->subject);
        $stmt->bindParam(":description", $this->description);
        $stmt->bindParam(":priority", $this->priority);
        $stmt->bindParam(":status", $this->status);
        $stmt->bindParam(":requester", $this->requester);

        if ($stmt->execute()) {
            $this->id_ticket = $this->conn->lastInsertId();
            return true;
        }
        return false;
    }

    // Read all tickets
    public function read() {
        $query = "SELECT * FROM " . $this->table_name . " ORDER BY updated_at DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    // Read a single ticket
    public function readOne() {
        $query = "SELECT * FROM " . $this->table_name . " WHERE 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->vendor_system = $row['vendor_system'];
            $this->request_type = $row['request_type'];
            $this->subject = $row['subject'];
            $this->description = $row['description'];
            $this->priority = $row['priority'];
            $this->status = $row['status'];
            $this->requester = $row['requester'];
            $this->created_at = $row['created_at'];
            $this->updated_at = $row['updated_at'];
            return true;
        }
        return false;
    }
    
    // Get all updates for a ticket
    public function getUpdates() {
        $query = "SELECT * FROM " . $this->updates_table_name . " WHERE id_ticket = ? ORDER BY created_at ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id_ticket);
        $stmt->execute();
        return $stmt;
    }
    
    // Add an update to a ticket
    public function addUpdate($id_ticket, $update_description, $updated_by) {
        $query = "INSERT INTO " . $this->updates_table_name . "
                  SET
                    id_ticket=:id_ticket,
                    update_description=:update_description,
                    updated_by=:updated_by";
        
        $stmt = $this->conn->prepare($query);

        // Sanitize
        $id_ticket = htmlspecialchars(strip_tags($id_ticket));
        $update_description = htmlspecialchars(strip_tags($update_description));
        $updated_by = htmlspecialchars(strip_tags($updated_by));

        // Bind
        $stmt->bindParam(":id_ticket", $id_ticket);
        $stmt->bindParam(":update_description", $update_description);
        $stmt->bindParam(":updated_by", $updated_by);
        
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // Update ticket status
    public function updateStatus($id_ticket, $new_status) {
        $query = "UPDATE " . $this->table_name . " SET status = :status WHERE id_ticket = :id_ticket";
        
        $stmt = $this->conn->prepare($query);

        // Sanitize
        $id_ticket = htmlspecialchars(strip_tags($id_ticket));
        $new_status = htmlspecialchars(strip_tags($new_status));

        // Bind
        $stmt->bindParam(":status", $new_status);
        $stmt->bindParam(":id_ticket", $id_ticket);
        
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }
}
