81 lines
2.7 KiB
MySQL
81 lines
2.7 KiB
MySQL
|
-- 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 $$;
|