-- Migration 009: Secure Decentralized Messenger System
-- Creates tables for encrypted P2P messaging via IPFS/libp2p

-- Messenger profile for each user (one per user)
CREATE TABLE IF NOT EXISTS messenger_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    messaging_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    peer_id VARCHAR(100),                    -- libp2p peer ID (when connected)
    display_name VARCHAR(100),               -- Optional display name for messaging
    status VARCHAR(20) DEFAULT 'offline',    -- online, offline, away, busy, invisible
    status_message VARCHAR(255),             -- Custom status message
    last_seen_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User encryption keys (multiple keys per user for rotation)
CREATE TABLE IF NOT EXISTS messenger_keys (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_type VARCHAR(20) NOT NULL,           -- 'identity' (Ed25519), 'encryption' (X25519)
    public_key BYTEA NOT NULL,               -- Public key bytes
    encrypted_private_key BYTEA NOT NULL,    -- Private key encrypted with user's derived key
    key_salt BYTEA NOT NULL,                 -- Salt for key derivation
    is_active BOOLEAN DEFAULT TRUE,          -- Current active key
    expires_at TIMESTAMP WITH TIME ZONE,     -- Optional expiration for rotation
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    revoked_at TIMESTAMP WITH TIME ZONE,     -- When key was revoked
    UNIQUE(user_id, key_type, is_active) DEFERRABLE INITIALLY DEFERRED
);

-- Contacts/friends list with fine-grained control
CREATE TABLE IF NOT EXISTS messenger_contacts (
    id SERIAL PRIMARY KEY,
    owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    contact_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    contact_messaging_id UUID NOT NULL,      -- Cached messaging_id for fast lookups
    alias VARCHAR(100),                      -- Custom name for this contact
    status VARCHAR(20) DEFAULT 'pending',    -- pending, accepted, blocked, muted
    trust_level INTEGER DEFAULT 0,           -- 0-100 trust score
    notes TEXT,                              -- Private notes about contact
    group_ids INTEGER[],                     -- Array of group IDs this contact belongs to
    can_see_status BOOLEAN DEFAULT TRUE,     -- Can this contact see our status
    can_send_messages BOOLEAN DEFAULT TRUE,  -- Can this contact message us
    muted_until TIMESTAMP WITH TIME ZONE,    -- Temporary mute
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(owner_id, contact_user_id)
);

-- Contact groups for organization
CREATE TABLE IF NOT EXISTS messenger_contact_groups (
    id SERIAL PRIMARY KEY,
    owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7),                        -- Hex color for UI
    icon VARCHAR(50),                        -- Icon identifier
    description TEXT,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(owner_id, name)
);

-- Blocklist with detailed control
CREATE TABLE IF NOT EXISTS messenger_blocklist (
    id SERIAL PRIMARY KEY,
    owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    blocked_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    blocked_peer_id VARCHAR(100),            -- Can block by peer ID even without user
    blocked_messaging_id UUID,               -- Can block by messaging ID
    reason VARCHAR(255),
    block_type VARCHAR(20) DEFAULT 'full',   -- full, messages_only, status_only
    expires_at TIMESTAMP WITH TIME ZONE,     -- Temporary block
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(owner_id, blocked_user_id),
    CHECK (blocked_user_id IS NOT NULL OR blocked_peer_id IS NOT NULL OR blocked_messaging_id IS NOT NULL)
);

-- Conversations (1:1 and group chats)
CREATE TABLE IF NOT EXISTS messenger_conversations (
    id SERIAL PRIMARY KEY,
    conversation_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    type VARCHAR(20) NOT NULL,               -- 'direct', 'group', 'channel'
    name VARCHAR(100),                       -- Group name (NULL for direct)
    description TEXT,
    owner_id INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- Creator/admin
    avatar_ipfs_cid VARCHAR(100),            -- Group avatar on IPFS
    encrypted_group_key BYTEA,               -- Encrypted symmetric key for group
    settings JSONB DEFAULT '{}',             -- Group settings
    is_public BOOLEAN DEFAULT FALSE,         -- Public groups can be discovered
    max_members INTEGER DEFAULT 100,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Conversation participants
CREATE TABLE IF NOT EXISTS messenger_conversation_members (
    id SERIAL PRIMARY KEY,
    conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) DEFAULT 'member',       -- owner, admin, moderator, member
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    invited_by INTEGER REFERENCES users(id),
    can_send BOOLEAN DEFAULT TRUE,
    can_invite BOOLEAN DEFAULT FALSE,
    is_muted BOOLEAN DEFAULT FALSE,
    muted_until TIMESTAMP WITH TIME ZONE,
    last_read_at TIMESTAMP WITH TIME ZONE,   -- For unread tracking
    encrypted_member_key BYTEA,              -- Member's copy of group key
    UNIQUE(conversation_id, user_id)
);

-- Messages stored with IPFS CID reference
CREATE TABLE IF NOT EXISTS messenger_messages (
    id SERIAL PRIMARY KEY,
    message_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
    sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sender_messaging_id UUID NOT NULL,

    -- Content storage (encrypted content on IPFS)
    ipfs_cid VARCHAR(100),                   -- IPFS CID of encrypted content
    content_preview VARCHAR(100),            -- Optional encrypted preview for notifications
    content_type VARCHAR(50) DEFAULT 'text', -- text, image, file, audio, video, reaction

    -- Message metadata
    reply_to_id UUID REFERENCES messenger_messages(message_id),
    edited_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE,     -- Soft delete

    -- Delivery tracking
    signature BYTEA NOT NULL,                -- Ed25519 signature of message
    nonce BYTEA NOT NULL,                    -- Encryption nonce

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Message read receipts
CREATE TABLE IF NOT EXISTS messenger_read_receipts (
    id SERIAL PRIMARY KEY,
    message_id UUID NOT NULL REFERENCES messenger_messages(message_id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    read_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(message_id, user_id)
);

-- Peer ID registry for IPFS nodes (for relay/bootstrap management)
CREATE TABLE IF NOT EXISTS messenger_peer_registry (
    id SERIAL PRIMARY KEY,
    peer_id VARCHAR(100) NOT NULL UNIQUE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- NULL for relay nodes
    peer_type VARCHAR(20) NOT NULL,          -- 'user', 'relay', 'bootstrap'
    multiaddrs TEXT[],                       -- Known multiaddresses
    is_trusted BOOLEAN DEFAULT FALSE,        -- Manually trusted
    is_banned BOOLEAN DEFAULT FALSE,         -- Banned from network
    ban_reason VARCHAR(255),
    reputation INTEGER DEFAULT 50,           -- 0-100 reputation score
    last_seen_at TIMESTAMP WITH TIME ZONE,
    first_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    connection_count INTEGER DEFAULT 0,
    bytes_transferred BIGINT DEFAULT 0,
    metadata JSONB DEFAULT '{}'
);

-- Relay/bootstrap node configuration
CREATE TABLE IF NOT EXISTS messenger_relay_nodes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    peer_id VARCHAR(100) NOT NULL UNIQUE,
    multiaddrs TEXT[] NOT NULL,
    region VARCHAR(50),                      -- Geographic region
    priority INTEGER DEFAULT 50,             -- Higher = preferred
    is_active BOOLEAN DEFAULT TRUE,
    is_public BOOLEAN DEFAULT TRUE,          -- Public bootstrap or private relay
    max_connections INTEGER DEFAULT 1000,
    current_connections INTEGER DEFAULT 0,
    uptime_percent DECIMAL(5,2) DEFAULT 100,
    last_health_check TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_messaging_id ON messenger_profiles(messaging_id);
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_peer_id ON messenger_profiles(peer_id);
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_status ON messenger_profiles(status);

CREATE INDEX IF NOT EXISTS idx_messenger_keys_user_active ON messenger_keys(user_id, is_active);
CREATE INDEX IF NOT EXISTS idx_messenger_keys_type ON messenger_keys(key_type);

CREATE INDEX IF NOT EXISTS idx_messenger_contacts_owner ON messenger_contacts(owner_id);
CREATE INDEX IF NOT EXISTS idx_messenger_contacts_status ON messenger_contacts(status);
CREATE INDEX IF NOT EXISTS idx_messenger_contacts_messaging_id ON messenger_contacts(contact_messaging_id);

CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_owner ON messenger_blocklist(owner_id);
CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_blocked ON messenger_blocklist(blocked_user_id);
CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_peer ON messenger_blocklist(blocked_peer_id);

CREATE INDEX IF NOT EXISTS idx_messenger_conversations_type ON messenger_conversations(type);
CREATE INDEX IF NOT EXISTS idx_messenger_conversations_owner ON messenger_conversations(owner_id);

CREATE INDEX IF NOT EXISTS idx_messenger_members_conversation ON messenger_conversation_members(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messenger_members_user ON messenger_conversation_members(user_id);

CREATE INDEX IF NOT EXISTS idx_messenger_messages_conversation ON messenger_messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messenger_messages_sender ON messenger_messages(sender_id);
CREATE INDEX IF NOT EXISTS idx_messenger_messages_created ON messenger_messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messenger_messages_ipfs ON messenger_messages(ipfs_cid);

CREATE INDEX IF NOT EXISTS idx_messenger_peer_registry_type ON messenger_peer_registry(peer_type);
CREATE INDEX IF NOT EXISTS idx_messenger_peer_registry_trusted ON messenger_peer_registry(is_trusted);

CREATE INDEX IF NOT EXISTS idx_messenger_relay_active ON messenger_relay_nodes(is_active, priority DESC);

-- Trigger for updated_at timestamps
CREATE OR REPLACE FUNCTION update_messenger_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER messenger_profiles_updated
    BEFORE UPDATE ON messenger_profiles
    FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();

CREATE TRIGGER messenger_contacts_updated
    BEFORE UPDATE ON messenger_contacts
    FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();

CREATE TRIGGER messenger_contact_groups_updated
    BEFORE UPDATE ON messenger_contact_groups
    FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();

CREATE TRIGGER messenger_conversations_updated
    BEFORE UPDATE ON messenger_conversations
    FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();

CREATE TRIGGER messenger_relay_nodes_updated
    BEFORE UPDATE ON messenger_relay_nodes
    FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();

-- Comments for documentation
COMMENT ON TABLE messenger_profiles IS 'User profiles for the secure messenger system';
COMMENT ON TABLE messenger_keys IS 'Encryption and signing keys for E2E encrypted messaging';
COMMENT ON TABLE messenger_contacts IS 'User contact lists with fine-grained permissions';
COMMENT ON TABLE messenger_blocklist IS 'Blocked users/peers with granular control';
COMMENT ON TABLE messenger_conversations IS 'Direct and group conversations';
COMMENT ON TABLE messenger_messages IS 'Message metadata with IPFS content references';
COMMENT ON TABLE messenger_peer_registry IS 'Registry of all known IPFS peers';
COMMENT ON TABLE messenger_relay_nodes IS 'Dedicated relay and bootstrap nodes';