thoughts/infrastructure/migrations/001_schema.sql

-- nightly — Vollständiges Datenbankschema mit Row Level Security
-- Wird einmalig nach dem ersten Supabase-Start ausgeführt

-- ── Enums ──────────────────────────────────────────────────────────────────

DO $$ BEGIN
  CREATE TYPE post_mood AS ENUM ('still', 'unruhig', 'melancholisch', 'aufgedreht');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE review_status AS ENUM ('ok', 'urgent', 'hidden', 'approved', 'removed');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE report_reason AS ENUM ('hate', 'harassment', 'selfharm', 'illegal', 'spam', 'other');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ── Tabellen ────────────────────────────────────────────────────────────────

-- Erweiterung des Supabase-Auth-Schemas
CREATE TABLE IF NOT EXISTS public.profiles (
    id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    username        TEXT UNIQUE NOT NULL CHECK (username ~ '^[a-z0-9_.]{3,20}$'),
    display_name    TEXT NOT NULL,
    bio             TEXT,
    avatar_url      TEXT,
    push_token      TEXT,
    is_pro          BOOLEAN DEFAULT FALSE NOT NULL,
    is_admin        BOOLEAN DEFAULT FALSE NOT NULL,
    anon_slots_used INT DEFAULT 0 NOT NULL,
    anon_slots_reset_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE IF NOT EXISTS public.posts (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    content       TEXT NOT NULL CHECK (LENGTH(TRIM(content)) BETWEEN 1 AND 280),
    mood          post_mood,
    is_anonymous  BOOLEAN DEFAULT FALSE NOT NULL,
    review_status review_status DEFAULT 'ok' NOT NULL,
    deleted_at    TIMESTAMPTZ,  -- soft delete: Post bleibt im Tagebuch
    created_at    TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE IF NOT EXISTS public.resonances (
    post_id    UUID NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    PRIMARY KEY (post_id, user_id)
);

CREATE TABLE IF NOT EXISTS public.whispers (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    to_user_id   UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    post_id      UUID REFERENCES public.posts(id) ON DELETE SET NULL,
    content      TEXT NOT NULL CHECK (LENGTH(TRIM(content)) BETWEEN 1 AND 140),
    read_at      TIMESTAMPTZ,
    created_at   TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    CHECK (from_user_id != to_user_id)
);

CREATE TABLE IF NOT EXISTS public.follows (
    follower_id  UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    following_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at   TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)
);

CREATE TABLE IF NOT EXISTS public.reports (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id     UUID NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
    reporter_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    reason      report_reason NOT NULL,
    details     TEXT CHECK (LENGTH(details) <= 500),
    resolved_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    UNIQUE (post_id, reporter_id)
);

-- ── Indizes ─────────────────────────────────────────────────────────────────

CREATE INDEX IF NOT EXISTS idx_posts_user_id    ON public.posts(user_id);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON public.posts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_review     ON public.posts(review_status)
    WHERE review_status != 'ok';
CREATE INDEX IF NOT EXISTS idx_resonances_post  ON public.resonances(post_id);
CREATE INDEX IF NOT EXISTS idx_follows_follower ON public.follows(follower_id);
CREATE INDEX IF NOT EXISTS idx_whispers_to      ON public.whispers(to_user_id, read_at);

-- ── View: Feed Posts ────────────────────────────────────────────────────────
-- Wird in der iOS-App direkt abgefragt — joins alles zusammen

CREATE OR REPLACE VIEW public.feed_posts AS
SELECT
    p.id,
    p.content,
    p.mood,
    p.is_anonymous,
    p.created_at,
    -- Autor (für anonyme Posts: null)
    CASE WHEN p.is_anonymous THEN NULL ELSE p.user_id    END AS author_id,
    CASE WHEN p.is_anonymous THEN NULL ELSE pr.username  END AS author_username,
    CASE WHEN p.is_anonymous THEN NULL ELSE pr.display_name END AS author_display_name,
    CASE WHEN p.is_anonymous THEN NULL ELSE pr.avatar_url   END AS author_avatar_url,
    -- Resonance-Zähler
    COALESCE((
        SELECT COUNT(*) FROM public.resonances r WHERE r.post_id = p.id
    ), 0)::int AS resonance_count
FROM public.posts p
JOIN public.profiles pr ON pr.id = p.user_id
WHERE p.deleted_at IS NULL
  AND p.review_status IN ('ok', 'approved')
  AND p.created_at > NOW() - INTERVAL '14 hours';

-- ── Trigger: Profil automatisch anlegen ─────────────────────────────────────

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO public.profiles (id, username, display_name)
    VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'username', 'user_' || SUBSTR(NEW.id::text, 1, 8)),
        COALESCE(NEW.raw_user_meta_data->>'display_name', 'User')
    )
    ON CONFLICT (id) DO NOTHING;
    RETURN NEW;
END;
$$;

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 public.handle_new_user();

-- ── Trigger: Auto-Hidden bei >= 5 Reports ───────────────────────────────────

CREATE OR REPLACE FUNCTION public.handle_new_report()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_count int;
BEGIN
    SELECT COUNT(*) INTO v_count FROM public.reports WHERE post_id = NEW.post_id;
    IF v_count >= 5 THEN
        UPDATE public.posts
        SET review_status = 'hidden'
        WHERE id = NEW.post_id AND review_status = 'ok';
    END IF;
    IF NEW.reason IN ('selfharm', 'illegal') THEN
        UPDATE public.posts SET review_status = 'urgent'
        WHERE id = NEW.post_id AND review_status = 'ok';
    END IF;
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS on_new_report ON public.reports;
CREATE TRIGGER on_new_report
    AFTER INSERT ON public.reports
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_report();

-- ── Funktion: Username-Login ─────────────────────────────────────────────────
-- Gibt die E-Mail zu einem Username zurück (nur für Auth-Flow)

CREATE OR REPLACE FUNCTION public.get_email_by_username(p_username text)
RETURNS text LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_email text;
BEGIN
    SELECT u.email INTO v_email
    FROM auth.users u
    JOIN public.profiles p ON p.id = u.id
    WHERE LOWER(p.username) = LOWER(p_username);
    RETURN v_email;
END;
$$;

-- Funktion: Account vollständig löschen (DSGVO)
CREATE OR REPLACE FUNCTION public.delete_my_account()
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    DELETE FROM auth.users WHERE id = auth.uid();
END;
$$;

-- ── Row Level Security (RLS) ────────────────────────────────────────────────

ALTER TABLE public.profiles  ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.posts      ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.resonances ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.whispers   ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.follows    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.reports    ENABLE ROW LEVEL SECURITY;

-- profiles: jeder darf lesen, nur eigenes darf geändert werden
CREATE POLICY "profiles_select" ON public.profiles FOR SELECT USING (true);
CREATE POLICY "profiles_update" ON public.profiles FOR UPDATE USING (auth.uid() = id);

-- posts: Feed-Sichtbarkeit (über View geregelt), eigene Verwaltung
CREATE POLICY "posts_select" ON public.posts FOR SELECT
    USING (
        deleted_at IS NULL
        AND review_status IN ('ok', 'approved')
        AND (
            NOT is_anonymous
            OR user_id = auth.uid()
        )
    );
CREATE POLICY "posts_insert" ON public.posts FOR INSERT
    WITH CHECK (auth.uid() = user_id);
CREATE POLICY "posts_update_own" ON public.posts FOR UPDATE
    USING (auth.uid() = user_id AND deleted_at IS NULL);
-- Soft-Delete: nur eigene Posts dürfen als gelöscht markiert werden
CREATE POLICY "posts_softdelete" ON public.posts FOR UPDATE
    USING (auth.uid() = user_id)
    WITH CHECK (deleted_at IS NOT NULL);

-- resonances
CREATE POLICY "resonances_select" ON public.resonances FOR SELECT USING (true);
CREATE POLICY "resonances_insert" ON public.resonances FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "resonances_delete" ON public.resonances FOR DELETE USING (auth.uid() = user_id);

-- whispers: nur Absender und Empfänger
CREATE POLICY "whispers_select" ON public.whispers FOR SELECT
    USING (auth.uid() = from_user_id OR auth.uid() = to_user_id);
CREATE POLICY "whispers_insert" ON public.whispers FOR INSERT
    WITH CHECK (auth.uid() = from_user_id);

-- follows
CREATE POLICY "follows_select" ON public.follows FOR SELECT USING (true);
CREATE POLICY "follows_insert" ON public.follows FOR INSERT WITH CHECK (auth.uid() = follower_id);
CREATE POLICY "follows_delete" ON public.follows FOR DELETE USING (auth.uid() = follower_id);

-- reports: nur eigene einsehen
CREATE POLICY "reports_insert" ON public.reports FOR INSERT WITH CHECK (auth.uid() = reporter_id);
CREATE POLICY "reports_select_own" ON public.reports FOR SELECT USING (auth.uid() = reporter_id);