Nivaran

-- Add priority and visibility_score columns to existing issues table
ALTER TABLE public.issues ADD COLUMN IF NOT EXISTS priority text DEFAULT 'MEDIUM';
ALTER TABLE public.issues ADD COLUMN IF NOT EXISTS visibility_score integer DEFAULT 42;

-- Function to calculate priority based on upvotes and downvotes
CREATE OR REPLACE FUNCTION public.calculate_issue_priority()
RETURNS TRIGGER AS $$
DECLARE
    v_score integer;
BEGIN
    -- Formula: 42 + (upvotes * 1.6 - downvotes * 1.1)
    v_score := ROUND(42 + (NEW.upvotes * 1.6 - NEW.downvotes * 1.1));
    
    -- Bound score between 0 and 100
    IF v_score > 100 THEN v_score := 100; END IF;
    IF v_score < 0 THEN v_score := 0; END IF;
    
    NEW.visibility_score := v_score;
    
    IF v_score > 80 THEN
        NEW.priority := 'CRITICAL';
    ELSIF v_score > 60 THEN
        NEW.priority := 'HIGH';
    ELSIF v_score > 40 THEN
        NEW.priority := 'MEDIUM';
    ELSE
        NEW.priority := 'LOW';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update priority and score on upvote/downvote changes
DROP TRIGGER IF EXISTS trigger_update_issue_priority ON public.issues;
CREATE TRIGGER trigger_update_issue_priority
BEFORE INSERT OR UPDATE OF upvotes, downvotes ON public.issues
FOR EACH ROW
EXECUTE FUNCTION public.calculate_issue_priority();

-- Run a one-time update for existing rows
UPDATE public.issues SET upvotes = COALESCE(upvotes, 0) WHERE priority IS NULL;

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
import { JWT } from 'https://esm.sh/google-auth-library@8'

serve(async (req) => {
  try {
    // 1. Get the notification row from the Webhook payload
    const payload = await req.json();
    const notification = payload.record;

    // 2. Initialize Supabase client to fetch the citizen's FCM token
    const supabaseClient = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    );

    const { data: profile } = await supabaseClient
      .from('profiles')
      .select('fcm_token')
      .eq('id', notification.user_id)
      .single();

    if (!profile?.fcm_token) {
      return new Response("Citizen has no FCM token. Skipping.", { status: 200 });
    }

    // 3. Authenticate with Firebase using your Service Account JSON
    // (You will set FIREBASE_SERVICE_ACCOUNT as a Supabase Secret later)
    const serviceAccount = JSON.parse(Deno.env.get('FIREBASE_SERVICE_ACCOUNT') ?? '{}');

    const jwtClient = new JWT({
      email: serviceAccount.client_email,
      key: serviceAccount.private_key.replace(/\\n/g, '\n'),
      scopes: ['https://www.googleapis.com/auth/firebase.messaging'],
    });
    const tokens = await jwtClient.getAccessToken();

    // 4. Send the push via FCM v1 API
    const fcmPayload = {
      message: {
        token: profile.fcm_token,
        notification: {
          title: notification.title,
          body: notification.body,
        },
        data: {
          issueId: notification.issue_id || "", 
        }
      }
    };

    const response = await fetch(
      `https://fcm.googleapis.com/v1/projects/${serviceAccount.project_id}/messages:send`,
      {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${tokens.token}`,
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(fcmPayload),
      }
    );

    return new Response(JSON.stringify({ success: true }), { status: 200 });
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), { status: 500 });
  }
})

-- 1. Add a JSONB column to track who voted what (e.g., {"user_id": true/false})
ALTER TABLE public.issues ADD COLUMN IF NOT EXISTS verification_votes jsonb DEFAULT '{}'::jsonb;

-- 2. Create the Consensus Engine (RPC Function)
-- … cut: comment line
CREATE OR REPLACE FUNCTION public.cast_verification_vote(p_issue_id UUID, p_is_fixed BOOLEAN)
RETURNS void AS $$
DECLARE
    v_issue RECORD;
    v_votes JSONB;
    v_yes_count INT := 0;
    v_no_count INT := 0;
    v_total_jury INT := 0;
BEGIN
    -- Get current issue
    SELECT * INTO v_issue FROM public.issues WHERE id = p_issue_id;

    -- Update the votes JSON array with the current user's vote
    v_votes := COALESCE(v_issue.verification_votes, '{}'::jsonb);
    v_votes := jsonb_set(v_votes, ARRAY[auth.uid()::text], to_jsonb(p_is_fixed));

    -- Save the vote
    UPDATE public.issues SET verification_votes = v_votes WHERE id = p_issue_id;

    -- RULE 1: If the original reporter says "Yes", it is instantly Verified.
    IF auth.uid() = v_issue.user_id AND p_is_fixed = true THEN
        UPDATE public.issues SET status = 'Verified' WHERE id = p_issue_id;
        RETURN;
    END IF;

    -- Calculate the current tallies
    SELECT 
        COUNT(*) FILTER (WHERE value::text = 'true'),
        COUNT(*) FILTER (WHERE value::text = 'false')
    INTO v_yes_count, v_no_count
    FROM jsonb_each(v_votes);

    -- Calculate total possible jury members (Reporter + Affected Users)
    v_total_jury := COALESCE(array_length(v_issue.affected_user_ids, 1), 0) + 1;

    -- RULE 2: If > 50% say YES, it's Verified.
    IF v_yes_count > (v_total_jury / 2.0) THEN
        UPDATE public.issues SET status = 'Verified' WHERE id = p_issue_id;
    -- RULE 3: If >= 50% say NO, it gets kicked back to In Progress!
    ELSIF v_no_count >= (v_total_jury / 2.0) THEN
        UPDATE public.issues SET status = 'In Progress' WHERE id = p_issue_id;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Update the Notification Trigger to alert the Jury
CREATE OR REPLACE FUNCTION public.notify_user_on_status_change()
RETURNS TRIGGER AS $$
DECLARE
    jury_id UUID;
BEGIN
  IF NEW.status <> OLD.status THEN
    -- A. Notify Original Reporter
    INSERT INTO public.notifications (user_id, issue_id, title, body, is_read)
    VALUES (NEW.user_id, NEW.id, 'Issue Update: ' || NEW.status, 'Your issue has been updated to ' || NEW.status || '.', false);

    -- B. If it's RESOLVED, notify the Jury!
    IF NEW.status = 'Resolved' AND NEW.affected_user_ids IS NOT NULL THEN
        FOREACH jury_id IN ARRAY NEW.affected_user_ids
        LOOP
            -- Don't double-notify the reporter if they are in both arrays
            IF jury_id <> NEW.user_id THEN
                INSERT INTO public.notifications (user_id, issue_id, title, body, is_read)
                VALUES (jury_id, NEW.id, 'Verification Required', 'An issue you confirmed visibility for has been marked Resolved by the officer. Please verify if it is actually fixed!', false);
            END IF;
        END LOOP;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;