-- ============================================================
-- 08_create_users.sql
-- Tabel profil user (extension dari Supabase Auth)
-- Supabase Auth sudah membuat auth.users, tabel ini untuk data tambahan
-- ============================================================

CREATE TABLE IF NOT EXISTS profiles (
    id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    name            TEXT,                        -- Nama tampilan
    avatar_initial  TEXT DEFAULT 'H',            -- Huruf avatar (H = Hayfala)
    city            TEXT,                        -- Kota user
    prayer_method   TEXT DEFAULT 'kemenag',      -- Metode hitung sholat
    theme           TEXT DEFAULT 'light',        -- light / dark
    font_size       TEXT DEFAULT 'normal',       -- small / normal / large
    qari_preference TEXT DEFAULT 'alafasy',      -- Qari favorit
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Index
CREATE INDEX IF NOT EXISTS idx_profiles_name ON profiles(name);

-- RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- User bisa baca profil sendiri
CREATE POLICY "profiles_read_own" ON profiles
    FOR SELECT USING (auth.uid() = id);

-- User bisa update profil sendiri
CREATE POLICY "profiles_update_own" ON profiles
    FOR UPDATE USING (auth.uid() = id);

-- User bisa insert profil sendiri (saat register)
CREATE POLICY "profiles_insert_own" ON profiles
    FOR INSERT WITH CHECK (auth.uid() = id);

-- Auto-create profile saat user baru register
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO profiles (id, name, avatar_initial)
    VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'name', 'User'),
        UPPER(LEFT(COALESCE(NEW.raw_user_meta_data->>'name', 'H'), 1))
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger: jalankan function saat ada user baru
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Function untuk auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER profiles_updated_at
    BEFORE UPDATE ON profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();