-- Additive migration. Deploy tables before enabling ENABLE_RETENTION_SOCIAL.
CREATE TABLE IF NOT EXISTS tpl_app_challenges (
 token VARCHAR(64) PRIMARY KEY, owner_device VARCHAR(128) NOT NULL, creator_device VARCHAR(128) NOT NULL, request_id VARCHAR(64) NOT NULL,
 record_id BIGINT NOT NULL, score DOUBLE NOT NULL, display_name VARCHAR(64) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL,
 UNIQUE KEY uq_creator_request(creator_device,request_id), KEY ix_creator_created(creator_device,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tpl_app_challenge_entries (
 token VARCHAR(64) NOT NULL, device_id VARCHAR(128) NOT NULL, record_id BIGINT NOT NULL, score DOUBLE NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(token,device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tpl_app_photo_likes (
 record_id BIGINT NOT NULL, device_id VARCHAR(128) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(record_id,device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tpl_app_social_events (
 id BIGINT PRIMARY KEY AUTO_INCREMENT, recipient VARCHAR(128) NOT NULL, kind VARCHAR(24) NOT NULL,
 challenge_token VARCHAR(64), event_key VARCHAR(255), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_event(event_key), KEY ix_recipient_id(recipient,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;