<?php
class Ticket
{
    public $id = null;
    public $ticket_id = '';
    public $task_id = null;
    public $bitrix_user_id = null;
    public $ticket_user_id = null;
    public $project_id = null;
    public $user_email = '';
    public $user_phone = '';
    public $user_name = '';
    public $issue_type = '';
    public $description = '';
    public $created_at = null;
    public $updated_at = null;
    public $status = '';
    public $priority = '';

    private $db = null;

    public function __construct($data = null)
    {
        $this->db = Database::getInstance();
        return $this;
    }
    public static function delete($id): bool 
    {
        $self = new static;
        $stmt = $self->db->prepare("DELETE FROM tickets WHERE id = ?");
        return $stmt->execute([$id]);
    }
    
    public static function findAll($bitrix24Id): array
{
    // Sanitize input
    $bitrix24Id = (int)$bitrix24Id;

    // Prepare SQL without COLLATE
    $sql = "
    SELECT 
        task_id,
        source,
        subject,
        description,
        bitrix_user_id,
        user_name,
        user_email,
        project_type,
        responsible_name,
        status,
created_at
    FROM (
        -- External Tickets
        SELECT 
            et.task_id AS task_id,
            'external user' AS source,
            et.subject AS subject,
            et.description AS description,
            et.bitrix_user_id,
            et.created_at as created_at,
            et.status as status,
            u.name AS user_name,
            u.email AS user_email,
            p.project_type AS project_type,
            pru.responsible_name AS responsible_name
        FROM 
            external_ticket et
        LEFT JOIN 
            users u ON et.ticket_user_id = u.id
        LEFT JOIN 
            projects p ON et.project_id = p.project_id
        LEFT JOIN 
            project_responsible_users pru ON et.responsible_id = pru.responsible_bitrix_id
        WHERE 
            et.bitrix_user_id = {$bitrix24Id}

        UNION ALL

        -- Internal Tickets
        SELECT 
            it.task_id AS task_id,
            'internal user' AS source,
            it.ticket_subject AS subject,
            it.ticket_description AS description,
            it.bitrix24_user_id,
            it.created_at as created_at,
            it.status as status,
            u.name AS user_name,
            u.email AS user_email,
            p.project_type AS project_type,
            pru.responsible_name AS responsible_name
        FROM 
            internal_ticket it
        LEFT JOIN 
            users u ON it.bitrix24_user_id = u.bitrix24_user_id
        LEFT JOIN 
            projects p ON it.project_id = p.project_id
        LEFT JOIN 
            project_responsible_users pru ON it.responsible_id = pru.responsible_bitrix_id
        WHERE 
            it.bitrix24_user_id = {$bitrix24Id}
    ) AS combined_tickets
    LIMIT 0, 25;
    ";

    // Execute query
    $tickets = [];
    $self = new static;

    // ✅ Ensure DB charset is set to utf8mb4 before the query
    $self->db->set_charset('utf8mb4');

    $res = $self->db->query($sql);

    if (!$res) {
        error_log("DB QUERY ERROR: " . $self->db->error);
        return [];
    }

    while ($row = $res->fetch_object()) {
        $ticket = new static;
        $ticket->populateObject($row);
        $tickets[] = $ticket;
    }

    return $tickets;
}

    public static function findStatusNew($bitrix24Id): array
    {
        $sql = "SELECT 
            tickets.*, 
            users.name AS user_name, 
            users.email AS user_email,
            users.phone AS user_phone
        FROM 
            tickets
        LEFT JOIN 
            users ON tickets.ticket_user_id = users.id
        WHERE 
           tickets.status=1 AND tickets.bitrix_user_id = {$bitrix24Id}
           ";
        $tickets = [];
        $self = new static;
        $res = $self->db->query($sql);

        if ($res->num_rows < 1) {
            return [];
        }

        while ($row = $res->fetch_object()) {
            $ticket = new static;
            $ticket->populateObject($row);
            $tickets[] = $ticket;
        }

        return $tickets;
    }
    public static function findStatusProgress($bitrix24Id): array
    {
        $sql = "SELECT 
            tickets.*, 
            users.name AS user_name, 
            users.email AS user_email,
            users.phone AS user_phone
        FROM 
            tickets
        LEFT JOIN 
            users ON tickets.ticket_user_id = users.id
        WHERE 
           tickets.status=3 AND tickets.bitrix_user_id = {$bitrix24Id}";
        $tickets = [];
        $self = new static;
        $res = $self->db->query($sql);

        if ($res->num_rows < 1) {
            return [];
        }

        while ($row = $res->fetch_object()) {
            $ticket = new static;
            $ticket->populateObject($row);
            $tickets[] = $ticket;
        }

        return $tickets;
    }
    public static function findStatusPending($bitrix24Id): array
    {
        $sql = "SELECT 
            tickets.*, 
            users.name AS user_name, 
            users.email AS user_email,
            users.phone AS user_phone
        FROM 
            tickets
        LEFT JOIN 
            users ON tickets.ticket_user_id = users.id
        WHERE 
           tickets.status=2 AND tickets.bitrix_user_id = {$bitrix24Id}
           ";
        $tickets = [];
        $self = new static;
        $res = $self->db->query($sql);

        if ($res->num_rows < 1) {
            return [];
        }

        while ($row = $res->fetch_object()) {
            $ticket = new static;
            $ticket->populateObject($row);
            $tickets[] = $ticket;
        }

        return $tickets;
    }
    public static function findStatusClose($bitrix24Id): array
    {
        $sql = "SELECT 
            tickets.*, 
            users.name AS user_name, 
            users.email AS user_email,
            users.phone AS user_phone
        FROM 
            tickets
        LEFT JOIN 
            users ON tickets.ticket_user_id = users.id
        WHERE 
           tickets.status=5 AND tickets.bitrix_user_id = {$bitrix24Id}
           ";
        $tickets = [];
        $self = new static;
        $res = $self->db->query($sql);

        if ($res->num_rows < 1) {
            return [];
        }

        while ($row = $res->fetch_object()) {
            $ticket = new static;
            $ticket->populateObject($row);
            $tickets[] = $ticket;
        }

        return $tickets;
    }


    public static function findByStatus($status): array
    {
        $sql = "SELECT * FROM ticket WHERE status = '$status' ORDER BY id DESC";
        $self = new static;
        $tickets = [];
        $res = $self->db->query($sql);

        while ($row = $res->fetch_object()) {
            $tickets[] = $row;
        }

        return $tickets;
    }

    public function unassigned()
    {
        $sql = "SELECT * FROM ticket WHERE team_member = '' ORDER BY id DESC";
        $self = new static;
        $tickets = [];
        $res = $self->db->query($sql);

        while ($row = $res->fetch_object()) {
            $tickets[] = $row;
        }

        return $tickets;
    }

    public static function findByMember($member)
    {
        $sql = "SELECT * FROM ticket WHERE team_member = '$member' ORDER BY id DESC";
        $self = new static;
        $tickets = [];
        $res = $self->db->query($sql);

        while($row = $res->fetch_object()) {
            $ticket = new static;
            $ticket->populateObject($row);
            $tickets[] = $ticket;
        }

        return $tickets;
    }

    public function displayStatusBadge(): string
    {
        $badgeType = '';
        if ($this->status == 'open') {
            $badgeType = 'danger';
        } else if ($this->status == 'pending') {
            $badgeType = 'warning';
        } else if ($this->status == 'solved') {
            $badgeType = 'success';
        } else if ($this->status == 'closed') {
            $badgeType = 'info';
        }

        return '<div class="badge badge-' . $badgeType . '" role="badge"> ' . ucfirst($this->status) . '</div>';
    }

    public function populateObject($object): void
    {
        foreach ($object as $key => $property) {
            $this->$key = $property;
        }
    }
}
