-- Apply this on existing databases that already have the main schema but are missing
-- later compatibility fixes bundled in the source package.

CREATE TABLE IF NOT EXISTS badges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NOT NULL,
    badge_no VARCHAR(100) NOT NULL,
    badge_template_name VARCHAR(150) NULL,
    qr_code_path VARCHAR(255) NULL,
    printed_at DATETIME NULL,
    printed_by BIGINT UNSIGNED NULL,
    status ENUM('generated','printed','reprinted') NOT NULL DEFAULT 'generated',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_badges_badge_no (badge_no),
    UNIQUE KEY uq_badges_event_attendee (event_id, attendee_id),
    KEY idx_badges_event_id (event_id),
    KEY idx_badges_attendee_id (attendee_id),
    KEY idx_badges_printed_by (printed_by),
    CONSTRAINT fk_badges_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_badges_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_badges_printed_by FOREIGN KEY (printed_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;