CREATE TABLE IF NOT EXISTS customer_tickets
(
    id BIGINT NOT NULL,
    ticket_no VARCHAR(64) NOT NULL,
    tenant_id BIGINT NOT NULL,
    requester_user_id BIGINT,
    category VARCHAR(64) NOT NULL,
    priority VARCHAR(32) NOT NULL,
    status VARCHAR(32) NOT NULL,
    assignee VARCHAR(64),
    source VARCHAR(64),
    subject VARCHAR(200) NOT NULL,
    description TEXT,
    related_ref VARCHAR(128),
    first_response_at TIMESTAMP,
    resolved_at TIMESTAMP,
    due_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_customer_tickets_no (ticket_no),
    KEY idx_customer_tickets_tenant_status (tenant_id, status),
    KEY idx_customer_tickets_due (due_at, status)
);
