-- Profile Database Initialization

CREATE SCHEMA IF NOT EXISTS profile;

-- User profiles
CREATE TABLE IF NOT EXISTS profile.profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL UNIQUE,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url TEXT,
    banner_url TEXT,
    location VARCHAR(100),
    website VARCHAR(255),
    pronouns VARCHAR(50),
    birthday DATE,
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMPTZ,
    visibility VARCHAR(20) NOT NULL DEFAULT 'public',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Profile preferences
CREATE TABLE IF NOT EXISTS profile.preferences (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL UNIQUE,
    theme VARCHAR(20) DEFAULT 'system',
    language VARCHAR(10) DEFAULT 'en',
    notifications_email BOOLEAN DEFAULT TRUE,
    notifications_push BOOLEAN DEFAULT TRUE,
    notifications_sms BOOLEAN DEFAULT FALSE,
    privacy_show_online BOOLEAN DEFAULT TRUE,
    privacy_show_activity BOOLEAN DEFAULT TRUE,
    content_nsfw_blur BOOLEAN DEFAULT TRUE,
    content_autoplay BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Profile links (social, etc)
CREATE TABLE IF NOT EXISTS profile.links (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    type VARCHAR(50) NOT NULL,
    url TEXT NOT NULL,
    label VARCHAR(100),
    display_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, type, url)
);

-- Verification requests
CREATE TABLE IF NOT EXISTS profile.verification_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    type VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    documents JSONB DEFAULT '[]',
    admin_notes TEXT,
    reviewed_by UUID,
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_profiles_user ON profile.profiles(user_id);
CREATE INDEX IF NOT EXISTS idx_profiles_visibility ON profile.profiles(visibility);
CREATE INDEX IF NOT EXISTS idx_preferences_user ON profile.preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_links_user ON profile.links(user_id);
CREATE INDEX IF NOT EXISTS idx_verification_user ON profile.verification_requests(user_id);
CREATE INDEX IF NOT EXISTS idx_verification_status ON profile.verification_requests(status);

-- Permissions
GRANT ALL PRIVILEGES ON SCHEMA profile TO lilith;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA profile TO lilith;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA profile TO lilith;

DO $$ BEGIN RAISE NOTICE 'Profile database initialized'; END $$;