supabase-setup.sql

-- Profiles (auto-created by trigger on signup)
CREATE TABLE IF NOT EXISTS profiles (
  id         UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username   TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Flowcharts
CREATE TABLE IF NOT EXISTS flowcharts (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title         TEXT NOT NULL DEFAULT 'Untitled' CHECK (char_length(title) <= 100),
  language      TEXT NOT NULL DEFAULT 'javascript'
                  CHECK (language IN ('javascript', 'typescript', 'python')),
  is_public     BOOLEAN NOT NULL DEFAULT false,
  share_id      TEXT UNIQUE,
  created_at    TIMESTAMPTZ DEFAULT now(),
  updated_at    TIMESTAMPTZ DEFAULT now()
);

-- Version history
CREATE TABLE IF NOT EXISTS flowchart_versions (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flowchart_id   UUID NOT NULL REFERENCES flowcharts(id) ON DELETE CASCADE,
  code           TEXT NOT NULL CHECK (char_length(code) <= 50000),
  version_number INTEGER NOT NULL,
  created_at     TIMESTAMPTZ DEFAULT now()
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS flowcharts_updated_at ON flowcharts;
CREATE TRIGGER flowcharts_updated_at
  BEFORE UPDATE ON flowcharts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, username)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1)));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- RLS
ALTER TABLE profiles           ENABLE ROW LEVEL SECURITY;
ALTER TABLE flowcharts         ENABLE ROW LEVEL SECURITY;
ALTER TABLE flowchart_versions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "own_profile"     ON profiles;
DROP POLICY IF EXISTS "owners_full"     ON flowcharts;
DROP POLICY IF EXISTS "public_read"     ON flowcharts;
DROP POLICY IF EXISTS "owner_versions"  ON flowchart_versions;
DROP POLICY IF EXISTS "public_versions" ON flowchart_versions;

CREATE POLICY "own_profile"    ON profiles          USING (auth.uid() = id);
CREATE POLICY "owners_full"    ON flowcharts        USING (auth.uid() = user_id);
CREATE POLICY "public_read"    ON flowcharts        FOR SELECT USING (is_public = true);
CREATE POLICY "owner_versions" ON flowchart_versions
  USING (flowchart_id IN (SELECT id FROM flowcharts WHERE user_id = auth.uid()));
-- Without this, /share/[shareId] and GET /api/flowcharts/[id] always return
-- code: '' for non-owners: flowcharts.is_public grants public read on the
-- flowchart row, but flowchart_versions (where the actual code lives) had no
-- matching policy, so RLS silently returned zero rows to anyone but the owner.
CREATE POLICY "public_versions" ON flowchart_versions
  FOR SELECT USING (
    flowchart_id IN (SELECT id FROM flowcharts WHERE is_public = true)
  );