CREATE TABLE IF NOT EXISTS travel_1_trip_invitations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    sent_by BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    accepted_at DATETIME NULL,
    revoked_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_trip_invitation_token (token_hash),
    KEY idx_trip_invitations_trip_user (trip_id, user_id),
    KEY idx_trip_invitations_expiry (expires_at),
    CONSTRAINT fk_trip_invitations_trip FOREIGN KEY (trip_id) REFERENCES travel_1_trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_invitations_user FOREIGN KEY (user_id) REFERENCES travel_1_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_invitations_sender FOREIGN KEY (sent_by) REFERENCES travel_1_users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
