Module · Branches & Multi-Tenancy

const BranchSchema = z.object({
  name:       z.string().min(2),
  city:       z.string().min(2),
  state:      z.string().optional(),
  address:    z.string().min(5),
  phone:      z.string().regex(/^[6-9]\d{9}$/),
  email:      z.string().email(),
  established_year: z.number().int().min(1900).max(new Date().getFullYear()),
})

create policy "branches_insert_super_only" on public.branches
  for insert with check (public.is_super_admin());
 
create policy "branches_update_super_only" on public.branches
  for update using (public.is_super_admin());

create or replace function public.branch_summary()
returns table (
  branch_id        uuid,
  branch_name      text,
  city             text,
  total_students   bigint,
  total_teachers   bigint,
  pending_fees     numeric,
  fees_paid_mtd    numeric,  -- Month-to-date
  attendance_pct_mtd numeric
)
language sql
stable
security invoker
as $$
  select
    b.id,
    b.name,
    b.city,
    count(distinct s.id) filter (where s.deleted_at is null),
    count(distinct t.id),
    coalesce(sum(fp.amount) filter (where fp.status = 'pending'), 0),
    coalesce(sum(fp.amount) filter (where fp.status = 'completed' and fp.paid_at >= date_trunc('month', now())), 0),
    round(
      100.0 * count(a.id) filter (where a.status = 'present' and a.date >= date_trunc('month', now()))
      / nullif(count(a.id) filter (where a.date >= date_trunc('month', now())), 0),
      1
    )
  from public.branches b
  left join public.students s on s.branch_id = b.id
  left join public.profiles t on t.branch_id = b.id and t.role = 'teacher'
  left join public.fee_payments fp on fp.branch_id = b.id
  left join public.attendance a on a.branch_id = b.id
  where b.deleted_at is null
  group by b.id, b.name, b.city
  order by b.name
$$;

const { data: branches } = await supabase.rpc('branch_summary')

'use client'
import { useBranchContext } from '@/stores/branch-context'
 
export function BranchSwitcher() {
  const { allBranches, currentBranchId, setCurrentBranchId } = useBranchContext()
  if (allBranches.length <= 1) return null  // Only show for super admin
 
  return (
    <select
      value={currentBranchId ?? ''}
      onChange={e => setCurrentBranchId(e.target.value || null)}
      className="px-3 py-1.5 border rounded-lg text-sm"
    >
      <option value="">All branches</option>
      {allBranches.map(b => <option key={b.id} value={b.id}>{b.name}</option>)}
    </select>
  )
}

// src/stores/branch-context.ts
import { create } from 'zustand'
import { persist } from 'zustand/middleware'
 
interface BranchContextState {
  allBranches: { id: string; name: string }[]
  currentBranchId: string | null
  setAllBranches: (b: { id: string; name: string }[]) => void
  setCurrentBranchId: (id: string | null) => void
}
 
export const useBranchContext = create<BranchContextState>()(
  persist(
    (set) => ({
      allBranches: [],
      currentBranchId: null,
      setAllBranches: (b) => set({ allBranches: b }),
      setCurrentBranchId: (id) => set({ currentBranchId: id }),
    }),
    { name: 'sahinov-branch-context' }
  )
)

let q = supabase.from('students').select('*').is('deleted_at', null)
if (currentBranchId) q = q.eq('branch_id', currentBranchId)
const { data } = await q

const TABLES = ['branches', 'students', 'teachers', 'parents', 'attendance', 'exams', 'exam_marks', 'fee_payments', 'circulars'] as const
 
export default function RlsAuditPage() {
  const supabase = createBrowserClient()
 
  return (
    <div>
      <h1>RLS audit — current user view</h1>
      <p className="text-sm text-gray-600">Counts visible to you, logged in right now.</p>
      <ul>
        {TABLES.map(t => <AuditRow key={t} table={t} />)}
      </ul>
    </div>
  )
}
 
function AuditRow({ table }: { table: string }) {
  const { data, isLoading } = useQuery({
    queryKey: ['rls-audit', table],
    queryFn: async () => {
      const supabase = createBrowserClient()
      const { count } = await supabase.from(table).select('*', { count: 'exact', head: true })
      return count
    },
  })
  return <li>{table}: {isLoading ? '…' : data ?? '?'}</li>
}