


SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


COMMENT ON SCHEMA "public" IS 'standard public schema';



CREATE EXTENSION IF NOT EXISTS "hypopg" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "index_advisor" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";






CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";






CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";






CREATE OR REPLACE FUNCTION "public"."get_user_team_id"() RETURNS "uuid"
    LANGUAGE "sql" STABLE SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
    SELECT team_id FROM public.profiles WHERE id = auth.uid();
$$;


ALTER FUNCTION "public"."get_user_team_id"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_new_auth_user"() RETURNS "trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
DECLARE
  new_team_id UUID;
  team_name TEXT;
  admin_flag BOOLEAN;
  admin_team_id UUID;
BEGIN
  -- Read metadata from user creation
  team_name := COALESCE(
    NULLIF(TRIM(NEW.raw_user_meta_data->>'team_name'), ''),
    split_part(NEW.email, '@', 1)
  );
  admin_flag := COALESCE(
    (NEW.raw_user_meta_data->>'is_admin')::boolean,
    false
  );

  IF admin_flag THEN
    -- Admins: reuse existing "Administrateurs" team or create one
    SELECT id INTO admin_team_id
    FROM public.teams
    WHERE is_admin_team = true
    LIMIT 1;
    
    IF admin_team_id IS NULL THEN
      INSERT INTO public.teams (name, is_admin_team) 
      VALUES ('Administrateurs', true) 
      RETURNING id INTO admin_team_id;
    END IF;
    
    new_team_id := admin_team_id;
  ELSE
    -- Regular team users: create a new team for this account
    INSERT INTO public.teams (name, is_admin_team) 
    VALUES (team_name, false) 
    RETURNING id INTO new_team_id;
  END IF;

  -- Create profile linking user to team
  INSERT INTO public.profiles (id, team_id, display_name, is_admin)
  VALUES (NEW.id, new_team_id, team_name, admin_flag);

  RETURN NEW;
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'handle_new_auth_user failed for user %: %', NEW.id, SQLERRM;
    RAISE;
END;
$$;


ALTER FUNCTION "public"."handle_new_auth_user"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_updated_at"() RETURNS "trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;


ALTER FUNCTION "public"."handle_updated_at"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."is_user_admin"() RETURNS boolean
    LANGUAGE "sql" STABLE SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
    SELECT COALESCE(
        (SELECT is_admin FROM public.profiles WHERE id = auth.uid()),
        false
    );
$$;


ALTER FUNCTION "public"."is_user_admin"() OWNER TO "postgres";

SET default_tablespace = '';

SET default_table_access_method = "heap";


CREATE TABLE IF NOT EXISTS "public"."canvas_data" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "team_id" "uuid" NOT NULL,
    "canvas_id" "text" NOT NULL,
    "fields" "jsonb" DEFAULT '{}'::"jsonb",
    "elements" "jsonb" DEFAULT '[]'::"jsonb",
    "metadata" "jsonb" DEFAULT '{}'::"jsonb",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"()
);

ALTER TABLE ONLY "public"."canvas_data" REPLICA IDENTITY FULL;


ALTER TABLE "public"."canvas_data" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."profiles" (
    "id" "uuid" NOT NULL,
    "team_id" "uuid" NOT NULL,
    "display_name" "text",
    "is_admin" boolean DEFAULT false,
    "created_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."profiles" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."teams" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "name" "text" NOT NULL,
    "is_admin_team" boolean DEFAULT false,
    "created_at" timestamp with time zone DEFAULT "now"()
);


ALTER TABLE "public"."teams" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."week_locks" (
    "id" "uuid" DEFAULT "extensions"."uuid_generate_v4"() NOT NULL,
    "week" integer NOT NULL,
    "is_locked" boolean DEFAULT false NOT NULL,
    "locked_by" "uuid",
    "locked_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    CONSTRAINT "week_locks_week_check" CHECK ((("week" >= 1) AND ("week" <= 3)))
);


ALTER TABLE "public"."week_locks" OWNER TO "postgres";


ALTER TABLE ONLY "public"."canvas_data"
    ADD CONSTRAINT "canvas_data_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."canvas_data"
    ADD CONSTRAINT "canvas_data_team_id_canvas_id_key" UNIQUE ("team_id", "canvas_id");



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."teams"
    ADD CONSTRAINT "teams_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."week_locks"
    ADD CONSTRAINT "week_locks_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."week_locks"
    ADD CONSTRAINT "week_locks_week_key" UNIQUE ("week");



CREATE INDEX "idx_canvas_data_canvas_id" ON "public"."canvas_data" USING "btree" ("canvas_id");



CREATE INDEX "idx_canvas_data_team_canvas" ON "public"."canvas_data" USING "btree" ("team_id", "canvas_id");



CREATE INDEX "idx_canvas_data_team_id" ON "public"."canvas_data" USING "btree" ("team_id");



CREATE INDEX "idx_profiles_team_id" ON "public"."profiles" USING "btree" ("team_id");



CREATE OR REPLACE TRIGGER "on_canvas_data_updated" BEFORE UPDATE ON "public"."canvas_data" FOR EACH ROW EXECUTE FUNCTION "public"."handle_updated_at"();



ALTER TABLE ONLY "public"."canvas_data"
    ADD CONSTRAINT "canvas_data_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "public"."teams"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_id_fkey" FOREIGN KEY ("id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."profiles"
    ADD CONSTRAINT "profiles_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "public"."teams"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."week_locks"
    ADD CONSTRAINT "week_locks_locked_by_fkey" FOREIGN KEY ("locked_by") REFERENCES "auth"."users"("id");



CREATE POLICY "Admins can update week locks" ON "public"."week_locks" FOR UPDATE USING ((EXISTS ( SELECT 1
   FROM "public"."profiles"
  WHERE (("profiles"."id" = "auth"."uid"()) AND ("profiles"."is_admin" = true)))));



CREATE POLICY "Anyone can view week locks" ON "public"."week_locks" FOR SELECT USING (true);



CREATE POLICY "Users can delete canvas data" ON "public"."canvas_data" FOR DELETE USING ((("public"."is_user_admin"() = true) OR ("team_id" = ( SELECT "public"."get_user_team_id"() AS "get_user_team_id"))));



CREATE POLICY "Users can insert canvas data" ON "public"."canvas_data" FOR INSERT WITH CHECK ((("public"."is_user_admin"() = true) OR ("team_id" = ( SELECT "public"."get_user_team_id"() AS "get_user_team_id"))));



CREATE POLICY "Users can update canvas data" ON "public"."canvas_data" FOR UPDATE USING ((("public"."is_user_admin"() = true) OR ("team_id" = ( SELECT "public"."get_user_team_id"() AS "get_user_team_id")))) WITH CHECK ((("public"."is_user_admin"() = true) OR ("team_id" = ( SELECT "public"."get_user_team_id"() AS "get_user_team_id"))));



CREATE POLICY "Users can update own profile" ON "public"."profiles" FOR UPDATE USING (("id" = ( SELECT "auth"."uid"() AS "uid"))) WITH CHECK (("id" = ( SELECT "auth"."uid"() AS "uid")));



CREATE POLICY "Users can view canvas data" ON "public"."canvas_data" FOR SELECT USING ((("public"."is_user_admin"() = true) OR ("team_id" = ( SELECT "public"."get_user_team_id"() AS "get_user_team_id"))));



CREATE POLICY "Users can view profiles" ON "public"."profiles" FOR SELECT USING ((("id" = ( SELECT "auth"."uid"() AS "uid")) OR ("public"."is_user_admin"() = true)));



CREATE POLICY "Users can view teams" ON "public"."teams" FOR SELECT USING ((("public"."is_user_admin"() = true) OR ("id" IN ( SELECT "profiles"."team_id"
   FROM "public"."profiles"
  WHERE ("profiles"."id" = ( SELECT "auth"."uid"() AS "uid"))))));



ALTER TABLE "public"."canvas_data" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."profiles" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."teams" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."week_locks" ENABLE ROW LEVEL SECURITY;




ALTER PUBLICATION "supabase_realtime" OWNER TO "postgres";






ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."canvas_data";



GRANT USAGE ON SCHEMA "public" TO "postgres";
GRANT USAGE ON SCHEMA "public" TO "anon";
GRANT USAGE ON SCHEMA "public" TO "authenticated";
GRANT USAGE ON SCHEMA "public" TO "service_role";





























































































































































































GRANT ALL ON FUNCTION "public"."get_user_team_id"() TO "anon";
GRANT ALL ON FUNCTION "public"."get_user_team_id"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."get_user_team_id"() TO "service_role";



GRANT ALL ON FUNCTION "public"."handle_new_auth_user"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_new_auth_user"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_new_auth_user"() TO "service_role";



GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "service_role";



GRANT ALL ON FUNCTION "public"."is_user_admin"() TO "anon";
GRANT ALL ON FUNCTION "public"."is_user_admin"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."is_user_admin"() TO "service_role";
























GRANT ALL ON TABLE "public"."canvas_data" TO "anon";
GRANT ALL ON TABLE "public"."canvas_data" TO "authenticated";
GRANT ALL ON TABLE "public"."canvas_data" TO "service_role";



GRANT ALL ON TABLE "public"."profiles" TO "anon";
GRANT ALL ON TABLE "public"."profiles" TO "authenticated";
GRANT ALL ON TABLE "public"."profiles" TO "service_role";



GRANT ALL ON TABLE "public"."teams" TO "anon";
GRANT ALL ON TABLE "public"."teams" TO "authenticated";
GRANT ALL ON TABLE "public"."teams" TO "service_role";



GRANT ALL ON TABLE "public"."week_locks" TO "anon";
GRANT ALL ON TABLE "public"."week_locks" TO "authenticated";
GRANT ALL ON TABLE "public"."week_locks" TO "service_role";









ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "service_role";































