/* RAIET v1.40.5 — persistent platform relationship memory.
   Stores only a confirmed RAIET handoff between two current platform IDs.
   It is NOT a mirror of a provider friends list. */
CREATE TABLE IF NOT EXISTS user_platform_relationships (
    platform VARCHAR(32) NOT NULL,
    user_low_id BIGINT UNSIGNED NOT NULL,
    user_high_id BIGINT UNSIGNED NOT NULL,
    provider_low_id VARCHAR(128) NOT NULL,
    provider_high_id VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'confirmed',
    source VARCHAR(32) NOT NULL DEFAULT 'manual',
    confirmed_by_user_id BIGINT UNSIGNED NULL,
    confirmed_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (platform, user_low_id, user_high_id),
    KEY idx_platform_relationship_updated (platform, updated_at),
    CONSTRAINT fk_platform_relationship_low FOREIGN KEY (user_low_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_platform_relationship_high FOREIGN KEY (user_high_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_platform_relationship_confirmer FOREIGN KEY (confirmed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Preserve already-confirmed current handoffs where both platform accounts are linked.
   Canonical user ordering keeps one row per pair regardless of who was captain. */
INSERT INTO user_platform_relationships
    (platform, user_low_id, user_high_id, provider_low_id, provider_high_id,
     status, source, confirmed_by_user_id, confirmed_at, updated_at)
SELECT
    h.platform,
    LEAST(h.coordinator_user_id, h.participant_user_id),
    GREATEST(h.coordinator_user_id, h.participant_user_id),
    CASE WHEN h.coordinator_user_id < h.participant_user_id THEN c.provider_user_id ELSE p.provider_user_id END,
    CASE WHEN h.coordinator_user_id < h.participant_user_id THEN p.provider_user_id ELSE c.provider_user_id END,
    'confirmed',
    'legacy_handoff',
    COALESCE(h.state_actor_user_id, h.coordinator_user_id),
    COALESCE(h.updated_at, NOW()),
    NOW()
FROM lobby_platform_handoffs h
JOIN user_platform_accounts c
  ON c.user_id = h.coordinator_user_id AND c.provider = h.platform
JOIN user_platform_accounts p
  ON p.user_id = h.participant_user_id AND p.provider = h.platform
WHERE h.state = 'connected_confirmed'
  AND h.coordinator_user_id <> h.participant_user_id
ON DUPLICATE KEY UPDATE
    provider_low_id = VALUES(provider_low_id),
    provider_high_id = VALUES(provider_high_id),
    status = 'confirmed',
    source = VALUES(source),
    confirmed_by_user_id = VALUES(confirmed_by_user_id),
    confirmed_at = VALUES(confirmed_at),
    updated_at = NOW();
