-- Forum Votes & Reputation Migration for BroadcastBeat
-- Adds upvote/downvote on threads and replies, and reputation points on user_profiles

-- ============================================================
-- 1. ADD REPUTATION COLUMN TO USER_PROFILES
-- ============================================================
ALTER TABLE public.user_profiles
ADD COLUMN IF NOT EXISTS reputation INTEGER DEFAULT 0;

-- ============================================================
-- 2. FORUM VOTES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS public.forum_votes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
    target_type TEXT NOT NULL CHECK (target_type IN ('thread', 'reply')),
    target_id UUID NOT NULL,
    vote_value SMALLINT NOT NULL CHECK (vote_value IN (1, -1)),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Unique: one vote per user per target
CREATE UNIQUE INDEX IF NOT EXISTS idx_forum_votes_unique
ON public.forum_votes (user_id, target_type, target_id);

CREATE INDEX IF NOT EXISTS idx_forum_votes_target
ON public.forum_votes (target_type, target_id);

CREATE INDEX IF NOT EXISTS idx_forum_votes_user_id
ON public.forum_votes (user_id);

-- ============================================================
-- 3. ADD VOTE SCORE COLUMNS TO THREADS AND REPLIES
-- ============================================================
ALTER TABLE public.forum_threads
ADD COLUMN IF NOT EXISTS upvotes INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS downvotes INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS vote_score INTEGER DEFAULT 0;

ALTER TABLE public.forum_replies
ADD COLUMN IF NOT EXISTS upvotes INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS downvotes INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS vote_score INTEGER DEFAULT 0;

-- ============================================================
-- 4. FUNCTIONS
-- ============================================================

-- Recalculate vote counts on thread/reply after vote change
CREATE OR REPLACE FUNCTION public.update_vote_counts()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_target_type TEXT;
    v_target_id UUID;
    v_up INTEGER;
    v_down INTEGER;
    v_author_id UUID;
    v_old_vote SMALLINT := 0;
    v_new_vote SMALLINT := 0;
    v_rep_delta INTEGER := 0;
BEGIN
    -- Determine target info
    IF TG_OP = 'DELETE' THEN
        v_target_type := OLD.target_type;
        v_target_id := OLD.target_id;
        v_old_vote := OLD.vote_value;
    ELSIF TG_OP = 'INSERT' THEN
        v_target_type := NEW.target_type;
        v_target_id := NEW.target_id;
        v_new_vote := NEW.vote_value;
    ELSIF TG_OP = 'UPDATE' THEN
        v_target_type := NEW.target_type;
        v_target_id := NEW.target_id;
        v_old_vote := OLD.vote_value;
        v_new_vote := NEW.vote_value;
    END IF;

    -- Recalculate vote totals
    SELECT
        COALESCE(SUM(CASE WHEN vote_value = 1 THEN 1 ELSE 0 END), 0),
        COALESCE(SUM(CASE WHEN vote_value = -1 THEN 1 ELSE 0 END), 0)
    INTO v_up, v_down
    FROM public.forum_votes
    WHERE target_type = v_target_type AND target_id = v_target_id;

    -- Update the appropriate table
    IF v_target_type = 'thread' THEN
        UPDATE public.forum_threads
        SET upvotes = v_up,
            downvotes = v_down,
            vote_score = v_up - v_down,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = v_target_id;

        SELECT author_id INTO v_author_id
        FROM public.forum_threads WHERE id = v_target_id LIMIT 1;
    ELSIF v_target_type = 'reply' THEN
        UPDATE public.forum_replies
        SET upvotes = v_up,
            downvotes = v_down,
            vote_score = v_up - v_down,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = v_target_id;

        SELECT author_id INTO v_author_id
        FROM public.forum_replies WHERE id = v_target_id LIMIT 1;
    END IF;

    -- Update author reputation (only for authenticated authors)
    IF v_author_id IS NOT NULL THEN
        -- Calculate reputation delta: upvote = +10, downvote = -2
        v_rep_delta := (v_new_vote - v_old_vote) * CASE
            WHEN (v_new_vote > 0 OR v_old_vote > 0) THEN 10
            ELSE 2
        END;

        -- More precise: +10 per upvote gained, -10 per upvote lost, -2 per downvote gained, +2 per downvote lost
        v_rep_delta := 0;
        IF v_new_vote = 1 AND v_old_vote = 0 THEN v_rep_delta := 10; END IF;
        IF v_new_vote = 0 AND v_old_vote = 1 THEN v_rep_delta := -10; END IF;
        IF v_new_vote = -1 AND v_old_vote = 0 THEN v_rep_delta := -2; END IF;
        IF v_new_vote = 0 AND v_old_vote = -1 THEN v_rep_delta := 2; END IF;
        IF v_new_vote = 1 AND v_old_vote = -1 THEN v_rep_delta := 12; END IF;
        IF v_new_vote = -1 AND v_old_vote = 1 THEN v_rep_delta := -12; END IF;

        IF v_rep_delta != 0 THEN
            UPDATE public.user_profiles
            SET reputation = GREATEST(0, COALESCE(reputation, 0) + v_rep_delta)
            WHERE id = v_author_id;
        END IF;
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$;

-- ============================================================
-- 5. ENABLE RLS
-- ============================================================
ALTER TABLE public.forum_votes ENABLE ROW LEVEL SECURITY;

-- ============================================================
-- 6. RLS POLICIES
-- ============================================================

-- Public can read all votes (for counts)
DROP POLICY IF EXISTS "public_read_forum_votes" ON public.forum_votes;
CREATE POLICY "public_read_forum_votes"
ON public.forum_votes FOR SELECT TO public USING (true);

-- Authenticated users can insert their own votes
DROP POLICY IF EXISTS "authenticated_insert_forum_votes" ON public.forum_votes;
CREATE POLICY "authenticated_insert_forum_votes"
ON public.forum_votes FOR INSERT TO authenticated
WITH CHECK (user_id = auth.uid());

-- Users can update their own votes
DROP POLICY IF EXISTS "users_update_own_forum_votes" ON public.forum_votes;
CREATE POLICY "users_update_own_forum_votes"
ON public.forum_votes FOR UPDATE TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

-- Users can delete their own votes
DROP POLICY IF EXISTS "users_delete_own_forum_votes" ON public.forum_votes;
CREATE POLICY "users_delete_own_forum_votes"
ON public.forum_votes FOR DELETE TO authenticated
USING (user_id = auth.uid());

-- Service role full access
DROP POLICY IF EXISTS "service_role_manage_forum_votes" ON public.forum_votes;
CREATE POLICY "service_role_manage_forum_votes"
ON public.forum_votes FOR ALL TO service_role USING (true) WITH CHECK (true);

-- ============================================================
-- 7. TRIGGERS
-- ============================================================
DROP TRIGGER IF EXISTS on_forum_vote_change ON public.forum_votes;
CREATE TRIGGER on_forum_vote_change
AFTER INSERT OR UPDATE OR DELETE ON public.forum_votes
FOR EACH ROW EXECUTE FUNCTION public.update_vote_counts();