-- Migration 005: Platform Identities
-- Phase 5: Cross-platform account linking
-- Created: 2026-02-01

-- Platform identities table
-- Links platform-specific user IDs to a unified User record
CREATE TABLE IF NOT EXISTS platform_identities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform VARCHAR(50) NOT NULL,
    platform_user_id VARCHAR(255) NOT NULL,
    platform_username VARCHAR(255),
    platform_display_name VARCHAR(255),

    -- Metadata
    is_primary BOOLEAN DEFAULT FALSE,
    linked_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC'),
    last_used_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC'),
    is_active BOOLEAN DEFAULT TRUE,

    -- Verification
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,

    -- Constraints
    CONSTRAINT uq_platform_user UNIQUE (platform, platform_user_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_platform_identities_user_id ON platform_identities(user_id);
CREATE INDEX IF NOT EXISTS idx_platform_identities_platform_user_id ON platform_identities(platform_user_id);

-- Linking tokens table
-- Temporary tokens for linking accounts across platforms
CREATE TABLE IF NOT EXISTS linking_tokens (
    id SERIAL PRIMARY KEY,

    -- Source platform
    source_platform VARCHAR(50) NOT NULL,
    source_platform_user_id VARCHAR(255) NOT NULL,

    -- Token details
    token VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC'),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,

    -- Usage tracking
    is_used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMP WITH TIME ZONE,
    used_by_platform VARCHAR(50),
    used_by_platform_user_id VARCHAR(255),

    -- Result
    linked_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_linking_tokens_token ON linking_tokens(token);
CREATE INDEX IF NOT EXISTS idx_linking_tokens_linked_user_id ON linking_tokens(linked_user_id);
CREATE INDEX IF NOT EXISTS idx_linking_tokens_expires_at ON linking_tokens(expires_at);

-- Comments
COMMENT ON TABLE platform_identities IS 'Links platform-specific user identifiers to unified User records for cross-platform account linking';
COMMENT ON TABLE linking_tokens IS 'Temporary tokens for verifying and linking accounts across platforms';

COMMENT ON COLUMN platform_identities.platform IS 'Platform type: discord, web, or cli';
COMMENT ON COLUMN platform_identities.platform_user_id IS 'Platform-specific user identifier (e.g., Discord ID, email)';
COMMENT ON COLUMN platform_identities.is_primary IS 'Whether this is the primary identity for the user';
COMMENT ON COLUMN platform_identities.is_verified IS 'Whether this identity has been verified (for Web/CLI)';

COMMENT ON COLUMN linking_tokens.token IS 'Unique token for linking accounts (8-12 characters, alphanumeric)';
COMMENT ON COLUMN linking_tokens.expires_at IS 'Token expiration time (typically 15 minutes from creation)';
COMMENT ON COLUMN linking_tokens.is_used IS 'Whether the token has been used';