BEGIN;

CREATE TABLE IF NOT EXISTS user_profiles (
    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    full_name VARCHAR(150) NOT NULL,
    display_name VARCHAR(100),
    phone VARCHAR(50),
    avatar_url TEXT,
    job_title VARCHAR(120),
    employee_no VARCHAR(60),
    bio TEXT,
    timezone VARCHAR(64) DEFAULT 'Asia/Jakarta',
    locale VARCHAR(16) DEFAULT 'id-ID',
    preferences JSONB NOT NULL DEFAULT '{}'::jsonb,
    notification_prefs JSONB NOT NULL DEFAULT '{}'::jsonb,
    last_seen_at TIMESTAMP WITHOUT TIME ZONE,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_user_profiles_phone ON user_profiles(phone);
CREATE INDEX IF NOT EXISTS idx_user_profiles_employee_no ON user_profiles(employee_no);
CREATE INDEX IF NOT EXISTS idx_user_profiles_prefs_gin ON user_profiles USING GIN (preferences);
CREATE INDEX IF NOT EXISTS idx_user_profiles_notif_gin ON user_profiles USING GIN (notification_prefs);

CREATE TRIGGER trg_user_profiles_updated_at
    BEFORE UPDATE ON user_profiles
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMIT;