CREATE TABLE IF NOT EXISTS event_activities (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    summary VARCHAR(500) NULL,
    details_html MEDIUMTEXT NULL,
    starts_at DATETIME NULL,
    ends_at DATETIME NULL,
    location VARCHAR(255) NULL,
    has_capacity_limit TINYINT(1) NOT NULL DEFAULT 0,
    capacity INT UNSIGNED NULL,
    registration_opens_at DATETIME NULL,
    registration_closes_at DATETIME NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'published',
    created_by BIGINT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_event_activities_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    KEY idx_event_activities_event_status (event_id, status, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_activity_images (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    activity_id BIGINT UNSIGNED NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_event_activity_images_activity FOREIGN KEY (activity_id) REFERENCES event_activities(id) ON DELETE CASCADE,
    KEY idx_event_activity_images_sort (activity_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_activity_registrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    activity_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'registered',
    registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cancelled_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_activity_user (activity_id, user_id),
    CONSTRAINT fk_activity_registrations_activity FOREIGN KEY (activity_id) REFERENCES event_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_registrations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    KEY idx_activity_registrations_status (activity_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;