Module · Payments

-- Fee schedule per branch + grade
create table public.fee_plans (
  id          uuid primary key default gen_random_uuid(),
  branch_id   uuid not null references public.branches(id),
  grade       text not null,
  category    text not null check (category in ('tuition', 'admission', 'transport', 'examination', 'misc')),
  amount      numeric(10, 2) not null check (amount >= 0),
  period      text not null check (period in ('one_time', 'monthly', 'quarterly', 'annual')),
  due_date    date,            -- For one-time fees only
  description text,
  created_at  timestamptz not null default now()
);
 
-- A specific fee owed by a specific student
create table public.fee_payments (
  id                 uuid primary key default gen_random_uuid(),
  student_id         uuid not null references public.students(id),
  branch_id          uuid not null references public.branches(id),
  fee_plan_id        uuid not null references public.fee_plans(id),
  period_label       text not null,   -- e.g. "April 2026"
  amount             numeric(10, 2) not null,
  status             text not null default 'pending' check (status in ('pending', 'completed', 'failed', 'refunded')),
  razorpay_order_id  text,
  razorpay_payment_id text,
  paid_at            timestamptz,
  created_at         timestamptz not null default now()
);
 
-- One-row guarantee: a student-period-plan combination is unique
create unique index fee_payments_unique on public.fee_payments (student_id, fee_plan_id, period_label);

Parent clicks "Pay April fee"
         ↓
1. Create order   ──→  Edge function calls Razorpay /orders   ──→  Returns order_id
         ↓
2. Open Razorpay checkout (client)
         ↓                                    ┌── User cancels ──→ status stays 'pending'
   User completes payment                     │
         ↓                                    │
3. Webhook fires (server-to-server)  ──→  Verifies signature  ──→  Marks fee paid
         ↓
   Email + SMS receipt to parent

// supabase/functions/create-fee-order/index.ts
import { createClient } from "@supabase/supabase-js@2.45.0";
 
const RAZORPAY_KEY_ID     = Deno.env.get("RAZORPAY_KEY_ID")!;
const RAZORPAY_KEY_SECRET = Deno.env.get("RAZORPAY_KEY_SECRET")!;
const SUPABASE_URL        = Deno.env.get("SUPABASE_URL")!;
const SERVICE_ROLE_KEY    = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
 
Deno.serve(async (req) => {
  // 1. Auth check — verify the caller's JWT and get their user id
  const authHeader = req.headers.get("authorization");
  if (!authHeader) return new Response("Unauthorized", { status: 401 });
 
  const userClient = createClient(SUPABASE_URL, Deno.env.get("SUPABASE_ANON_KEY")!, {
    global: { headers: { Authorization: authHeader } },
  });
  const { data: { user } } = await userClient.auth.getUser();
  if (!user) return new Response("Unauthorized", { status: 401 });
 
  // 2. Get { fee_payment_id } from body and look up the fee server-side
  const { fee_payment_id } = await req.json();
  const admin = createClient(SUPABASE_URL, SERVICE_ROLE_KEY);
  const { data: fee } = await admin
    .from("fee_payments")
    .select("id, amount, status, student_id, period_label, students!inner(parent_id, full_name)")
    .eq("id", fee_payment_id)
    .single();
 
  if (!fee) return new Response("Fee not found", { status: 404 });
  if (fee.status === "completed") return new Response("Already paid", { status: 409 });
  // Check the parent making the request actually owns this child
  if ((fee.students as any).parent_id !== user.id) return new Response("Forbidden", { status: 403 });
 
  // 3. Create Razorpay order
  const auth = btoa(`${RAZORPAY_KEY_ID}:${RAZORPAY_KEY_SECRET}`);
  const orderRes = await fetch("https://api.razorpay.com/v1/orders", {
    method: "POST",
    headers: { "Content-Type": "application/json", "Authorization": `Basic ${auth}` },
    body: JSON.stringify({
      amount: Math.round(Number(fee.amount) * 100),  // paise
      currency: "INR",
      receipt: fee.id,
      notes: { fee_payment_id: fee.id, student_id: fee.student_id },
    }),
  });
  const order = await orderRes.json();
  if (!orderRes.ok) {
    console.error("Razorpay error:", order);
    return new Response("Failed to create order", { status: 500 });
  }
 
  // 4. Store order id on the fee record
  await admin.from("fee_payments").update({ razorpay_order_id: order.id }).eq("id", fee.id);
 
  return new Response(JSON.stringify({
    order_id: order.id,
    amount:   order.amount,
    currency: order.currency,
    key_id:   RAZORPAY_KEY_ID,
  }), { headers: { "Content-Type": "application/json" } });
});

'use client'
import { loadScript } from '@/lib/loadScript'
 
export function PayButton({ feeId }: { feeId: string }) {
  const supabase = createBrowserClient()
 
  async function handlePay() {
    // Load Razorpay's checkout script
    await loadScript('https://checkout.razorpay.com/v1/checkout.js')
 
    // Call our edge function to create an order
    const { data: session } = await supabase.auth.getSession()
    const res = await fetch(`${SUPABASE_URL}/functions/v1/create-fee-order`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        Authorization: `Bearer ${session?.session?.access_token}`,
      },
      body: JSON.stringify({ fee_payment_id: feeId }),
    })
    const order = await res.json()
 
    // Open Razorpay's hosted checkout
    const rzp = new (window as any).Razorpay({
      key:      order.key_id,
      amount:   order.amount,
      currency: order.currency,
      order_id: order.order_id,
      name:     'Aurora Public School',
      description: 'Tuition fee',
      handler: function () {
        // User completes; redirect to "thank you"
        window.location.href = '/parent/fees?paid=1'
      },
      modal: {
        ondismiss: () => console.log('Cancelled'),
      },
    })
    rzp.open()
  }
 
  return <button onClick={handlePay} className="bg-indigo text-white px-4 py-2 rounded-lg">Pay now</button>
}

// supabase/functions/razorpay-webhook/index.ts
import { createClient } from "@supabase/supabase-js@2.45.0";
import { crypto } from "https://deno.land/std@0.224.0/crypto/mod.ts";
 
const RAZORPAY_WEBHOOK_SECRET = Deno.env.get("RAZORPAY_WEBHOOK_SECRET")!;
 
Deno.serve(async (req) => {
  const rawBody = await req.text();
  const signature = req.headers.get("x-razorpay-signature") ?? "";
 
  // 1. Verify signature — HMAC-SHA256 of raw body using the webhook secret
  const key = await crypto.subtle.importKey(
    "raw",
    new TextEncoder().encode(RAZORPAY_WEBHOOK_SECRET),
    { name: "HMAC", hash: "SHA-256" },
    false,
    ["sign"],
  );
  const sig = await crypto.subtle.sign("HMAC", key, new TextEncoder().encode(rawBody));
  const computed = Array.from(new Uint8Array(sig)).map(b => b.toString(16).padStart(2, "0")).join("");
 
  if (computed !== signature) {
    console.error("Signature mismatch");
    return new Response("Invalid signature", { status: 401 });
  }
 
  // 2. Process event
  const event = JSON.parse(rawBody);
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
  );
 
  if (event.event === "payment.captured") {
    const { order_id, id: payment_id, status, amount } = event.payload.payment.entity;
 
    const { data: fee } = await supabase
      .from("fee_payments")
      .select("id, status, amount")
      .eq("razorpay_order_id", order_id)
      .single();
 
    if (!fee) return new Response("Fee not found", { status: 404 });
    if (fee.status === "completed") return new Response("Already processed", { status: 200 });  // Idempotency
 
    // 3. Verify amount matches what we expected
    const expectedPaise = Math.round(Number(fee.amount) * 100);
    if (amount !== expectedPaise) {
      console.error(`Amount mismatch: expected ${expectedPaise}, got ${amount}`);
      return new Response("Amount mismatch", { status: 400 });
    }
 
    // 4. Mark paid
    await supabase
      .from("fee_payments")
      .update({
        status: "completed",
        razorpay_payment_id: payment_id,
        paid_at: new Date().toISOString(),
      })
      .eq("id", fee.id);
 
    // 5. Trigger receipt email (fire and forget)
    fetch(`${Deno.env.get("SUPABASE_URL")}/functions/v1/send-fee-receipt`, {
      method: "POST",
      headers: { Authorization: `Bearer ${Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")}` },
      body: JSON.stringify({ fee_payment_id: fee.id }),
    }).catch(console.error);
  }
 
  if (event.event === "payment.failed") {
    const { order_id } = event.payload.payment.entity;
    await supabase
      .from("fee_payments")
      .update({ status: "failed" })
      .eq("razorpay_order_id", order_id);
  }
 
  return new Response("ok", { status: 200 });
});