Supabase Backend

CREATE TABLE class_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  name text NOT NULL,
  description text,
  instructor text,
  room text,
  section text NOT NULL,
  day_of_week int4 NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
  start_time time NOT NULL,
  end_time time NOT NULL,
  color text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  is_deleted bool DEFAULT false
);

CREATE TABLE user_settings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  key text NOT NULL,
  value jsonb NOT NULL,
  updated_at timestamptz DEFAULT now(),
  UNIQUE(user_id, key)
);

CREATE TABLE user_devices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  device_name text,
  device_platform text,         -- 'ios', 'android'
  push_token text,
  last_active_at timestamptz DEFAULT now(),
  is_revoked bool DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- Standard SELECT policy (applied to all user-scoped tables)
CREATE POLICY "Users can view own data"
  ON class_items FOR SELECT
  USING (auth.uid() = user_id);

-- Standard INSERT policy
CREATE POLICY "Users can insert own data"
  ON class_items FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Standard UPDATE policy
CREATE POLICY "Users can update own data"
  ON class_items FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Soft delete only (no hard deletes via API)
CREATE POLICY "Users can soft-delete own data"
  ON class_items FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id AND is_deleted = true);

-- Example: Delete a section and cascade soft-delete its classes
CREATE OR REPLACE FUNCTION delete_section_cascade(p_section_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- Verify ownership
  IF NOT EXISTS (
    SELECT 1 FROM sections
    WHERE id = p_section_id AND user_id = auth.uid()
  ) THEN
    RAISE EXCEPTION 'Section not found or access denied';
  END IF;

  -- Soft-delete all classes in section
  UPDATE class_items
  SET is_deleted = true, updated_at = now()
  WHERE section = p_section_id AND user_id = auth.uid();

  -- Soft-delete the section
  UPDATE sections
  SET is_deleted = true, updated_at = now()
  WHERE id = p_section_id;
END;
$$;

// Fetch all active classes for a section
final response = await supabase
    .from('class_items')
    .select()
    .eq('section', sectionId)
    .eq('is_deleted', false)
    .order('day_of_week')
    .order('start_time');

final classes = (response as List)
    .map((json) => ClassItem.fromJson(json))
    .toList();

// Call a SECURITY DEFINER function
await supabase.rpc('delete_section_cascade', params: {
  'p_section_id': sectionId,
});

// Listen for changes to class items
final subscription = supabase
    .from('class_items')
    .stream(primaryKey: ['id'])
    .eq('user_id', currentUserId)
    .listen((List<Map<String, dynamic>> data) {
      // Update local state
      _updateClassList(data);
    });

// Example Edge Function: send-push-notification
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

serve(async (req) => {
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  );

  const { userId, title, body } = await req.json();

  // Get user's push tokens
  const { data: devices } = await supabase
    .from("user_devices")
    .select("push_token")
    .eq("user_id", userId)
    .eq("is_revoked", false)
    .not("push_token", "is", null);

  // Send to each device...
  return new Response(JSON.stringify({ sent: devices?.length ?? 0 }));
});

class AuthStateManager {
  final _eventQueue = StreamController<AuthChangeEvent>();
  bool _processing = false;

  void initialize() {
    supabase.auth.onAuthStateChange.listen((data) {
      _eventQueue.add(data.event);
      _processQueue();
    });
  }

  Future<void> _processQueue() async {
    if (_processing) return;
    _processing = true;

    await for (final event in _eventQueue.stream) {
      switch (event) {
        case AuthChangeEvent.signedIn:
          await _handleSignIn();
          break;
        case AuthChangeEvent.signedOut:
          await _handleSignOut();
          break;
        case AuthChangeEvent.tokenRefreshed:
          await _handleTokenRefresh();
          break;
      }
    }

    _processing = false;
  }
}