ALTER TABLE licenses
    ADD COLUMN delivery_uuid CHAR(36) NULL,
    ADD COLUMN delivery_manifest_json JSON NULL,
    ADD COLUMN delivery_guest_email VARCHAR(190) NULL,
    ADD COLUMN delivery_guest_token_hash CHAR(64) NULL,
    ADD COLUMN delivery_guest_token_expires_at DATETIME NULL,
    ADD COLUMN delivery_last_download_at DATETIME NULL;

UPDATE licenses
SET delivery_uuid = UUID()
WHERE delivery_uuid IS NULL;

UPDATE licenses
SET delivery_manifest_json = JSON_OBJECT(
        'bundle_version', 1,
        'license_id', id,
        'license_uuid', delivery_uuid,
        'license_key_censored', CONCAT(LEFT(license_key, 4), '-****-****-', RIGHT(license_key, 4)),
        'org_id', org_id,
        'subscription_id', subscription_id,
        'tier', tier,
        'seats', seats,
        'status', status,
        'expires_at', expires_at,
        'issued_at', created_at
    )
WHERE delivery_manifest_json IS NULL;

ALTER TABLE licenses
    ADD UNIQUE KEY uq_licenses_delivery_uuid (delivery_uuid),
    ADD KEY idx_licenses_delivery_guest_hash (delivery_guest_token_hash),
    ADD KEY idx_licenses_delivery_guest_expires (delivery_guest_token_expires_at);
