-- =========================
-- TICKET SYSTEM
-- =========================

CREATE TABLE IF NOT EXISTS ticket (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    assigned_user_id INT NULL,
    company_id INT NULL,
    station_id INT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT NULL,
    priority_id INT NULL,
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ticket_company_station (company_id, station_id),
    INDEX idx_ticket_user_status (user_id, status),
    INDEX idx_ticket_assigned_user (assigned_user_id)
);

CREATE TABLE IF NOT EXISTS ticket_reply (
    ticket_reply_id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ticket_reply_ticket (ticket_id),
    INDEX idx_ticket_reply_user (user_id)
);