CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";

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 "pgjwt" WITH SCHEMA "extensions";

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

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

CREATE FUNCTION "public"."create_user_and_contact"("name" "text", "email" "text", "role" "text", "user_id" "uuid") RETURNS "void"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
  -- Iniciamos una transacción implícita al insertar en las tablas
  BEGIN
      -- Insertamos en la tabla profiles
    INSERT INTO users (id, name, role, email)
    VALUES (user_id, name, role, email) 
    ON CONFLICT (id) DO NOTHING;

    -- Insertamos en la tabla contacts
    INSERT INTO contacts (user_id, name, url, username, icon)
    VALUES (user_id, 'Email', 'mailto:' || email, email, 'mailOpenOutline')ON CONFLICT (url) DO NOTHING
    
    ;

    -- Si todo fue exitoso, la transacción se confirma automáticamente
  EXCEPTION
    -- Si algo falla, se realizará rollback automáticamente
    WHEN others THEN
      RAISE EXCEPTION 'Error al crear el perfil y el contacto. Detalles: %', SQLERRM;
  END;
END;
$$;

ALTER FUNCTION "public"."create_user_and_contact"("name" "text", "email" "text", "role" "text", "user_id" "uuid") OWNER TO "postgres";

CREATE FUNCTION "public"."is_admin"() RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
DECLARE
    es_admin boolean;
BEGIN
    SELECT role = 'admin' INTO es_admin FROM users WHERE id = auth.uid();
    RETURN es_admin;
END;
$$;

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

CREATE FUNCTION "public"."is_client"() RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
DECLARE
    es_client boolean;
BEGIN
    SELECT role = 'client' INTO es_client FROM users WHERE id = auth.uid();
    RETURN es_client;
END;
$$;

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

CREATE FUNCTION "public"."is_trainer"() RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
DECLARE
    es_trainer boolean;
BEGIN
    SELECT role = 'trainer' INTO es_trainer FROM users WHERE id = auth.uid();
    RETURN es_trainer;
END;
$$;

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

CREATE FUNCTION "public"."is_user"() RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
DECLARE
    es_user boolean;
BEGIN
    SELECT role = 'user' INTO es_user FROM users WHERE id = auth.uid();
    RETURN es_user;
END;
$$;

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

CREATE FUNCTION "public"."remove_user"("user_id" "uuid") RETURNS "void"
    LANGUAGE "sql"
    AS $$delete from public.users where id=user_id$$;

ALTER FUNCTION "public"."remove_user"("user_id" "uuid") OWNER TO "postgres";

CREATE FUNCTION "public"."set_trainer_id"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
  IF (is_admin() OR is_trainer()) THEN
    NEW.trainer_id = auth.uid();
  ELSIF (is_client()) THEN
    NEW.user_id = auth.uid();
  ELSIF (is_user()) THEN
    NEW.user_id = auth.uid();
    NEW.privacy = 'public';
  END IF;
  RETURN NEW;
END;
$$;

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

SET default_tablespace = '';

SET default_table_access_method = "heap";

CREATE TABLE "public"."contacts" (
    "id" bigint NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "name" "text" NOT NULL,
    "icon" "text" NOT NULL,
    "url" "text" NOT NULL,
    "username" "text" NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "user_id" "uuid" NOT NULL
);

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

ALTER TABLE "public"."contacts" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."contacts_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."dietfoods" (
    "id" bigint NOT NULL,
    "cant" double precision NOT NULL,
    "food_id" bigint NOT NULL,
    "diet_id" bigint NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"()
);

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

CREATE TABLE "public"."foods" (
    "id" bigint NOT NULL,
    "name" "text" NOT NULL,
    "group" "text" NOT NULL,
    "Proteínas" double precision NOT NULL,
    "Calorías" double precision NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "unit" "text" DEFAULT '100 g'::"text" NOT NULL,
    "trainer_id" "uuid" DEFAULT "auth"."uid"(),
    "Carbohidratos" double precision NOT NULL,
    "Fibras" double precision NOT NULL,
    "Sodio" double precision NOT NULL,
    "Calcio" double precision NOT NULL,
    "Potasio" bigint NOT NULL,
    "Fósforo" double precision NOT NULL,
    "Hierro" double precision NOT NULL,
    "Zinc" double precision NOT NULL,
    "Tiamina" double precision NOT NULL,
    "Rivoflavina" double precision NOT NULL,
    "Niacina" double precision NOT NULL,
    "Vitamina_C" double precision NOT NULL,
    "Energía" double precision NOT NULL,
    "Agua" double precision NOT NULL,
    "Grasa" double precision NOT NULL,
    "Monoinsaturada" double precision NOT NULL,
    "Poliinsaturada" double precision NOT NULL,
    "Colesterol" double precision NOT NULL,
    "Saturada" double precision NOT NULL
);

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

CREATE VIEW "public"."dfview" AS
 SELECT "df"."id",
    "df"."cant",
    "df"."food_id",
    "df"."diet_id",
    "df"."created_at",
    "df"."updated_at",
    "f"."name" AS "food",
    (("df"."cant" / (100)::double precision) * "f"."Calcio") AS "Calcio",
    (("df"."cant" / (100)::double precision) * "f"."Sodio") AS "Sodio",
    (("df"."cant" / (100)::double precision) * ("f"."Potasio")::double precision) AS "Potasio",
    (("df"."cant" / (100)::double precision) * "f"."Calorías") AS "Calorías",
    (("df"."cant" / (100)::double precision) * "f"."Proteínas") AS "Proteínas",
    (("df"."cant" / (100)::double precision) * "f"."Carbohidratos") AS "Carbohidratos",
    (("df"."cant" / (100)::double precision) * "f"."Fibras") AS "Fibras",
    (("df"."cant" / (100)::double precision) * "f"."Fósforo") AS "Fósforo",
    (("df"."cant" / (100)::double precision) * "f"."Hierro") AS "Hierro",
    (("df"."cant" / (100)::double precision) * "f"."Zinc") AS "Zinc",
    (("df"."cant" / (100)::double precision) * "f"."Tiamina") AS "Tiamina",
    (("df"."cant" / (100)::double precision) * "f"."Rivoflavina") AS "Rivoflavina",
    (("df"."cant" / (100)::double precision) * "f"."Niacina") AS "Niacina",
    (("df"."cant" / (100)::double precision) * "f"."Vitamina_C") AS "Vitamina_C",
    (("df"."cant" / (100)::double precision) * "f"."Energía") AS "Energía",
    (("df"."cant" / (100)::double precision) * "f"."Agua") AS "Agua",
    (("df"."cant" / (100)::double precision) * "f"."Grasa") AS "Grasa",
    (("df"."cant" / (100)::double precision) * "f"."Monoinsaturada") AS "Monoinsaturada",
    (("df"."cant" / (100)::double precision) * "f"."Poliinsaturada") AS "Poliinsaturada",
    (("df"."cant" / (100)::double precision) * "f"."Colesterol") AS "Colesterol",
    (("df"."cant" / (100)::double precision) * "f"."Saturada") AS "Saturada"
   FROM ("public"."dietfoods" "df"
     JOIN "public"."foods" "f" ON (("df"."food_id" = "f"."id")));

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

ALTER TABLE "public"."dietfoods" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."dieetfoods_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."diets" (
    "id" bigint NOT NULL,
    "type" "text" NOT NULL,
    "goal" "text" NOT NULL,
    "proteins" double precision NOT NULL,
    "calories" double precision NOT NULL,
    "trainer_id" "uuid",
    "user_id" "uuid",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "name" "text" NOT NULL,
    "privacy" "text"
);

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

ALTER TABLE "public"."diets" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."diets_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."evalphotos" (
    "id" bigint NOT NULL,
    "eval_id" bigint,
    "path" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "deleted_at" timestamp with time zone
);

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

ALTER TABLE "public"."evalphotos" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."evalphotos_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."evals" (
    "id" bigint NOT NULL,
    "observation" "text",
    "date" "date" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "user_id" "uuid" NOT NULL,
    "trainer_id" "uuid"
);

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

ALTER TABLE "public"."evals" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."evals_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."favorites" (
    "id" bigint NOT NULL,
    "title" "text" NOT NULL,
    "fave_type" "text" NOT NULL,
    "user_id" "uuid" NOT NULL,
    "fave_id" bigint NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "image" "text"
);

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

ALTER TABLE "public"."favorites" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."favorites_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

ALTER TABLE "public"."foods" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."foods_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."measures" (
    "id" bigint NOT NULL,
    "key" "text" NOT NULL,
    "name" "text" NOT NULL,
    "value" double precision NOT NULL,
    "eval_id" bigint NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone
);

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

ALTER TABLE "public"."measures" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."measures_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."routines" (
    "id" bigint NOT NULL,
    "name" "text" NOT NULL,
    "description" "text" NOT NULL,
    "duration" "text",
    "goal" "text",
    "range" "text",
    "advantages" "text",
    "body" "text",
    "image" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "trainer_id" "uuid" DEFAULT "auth"."uid"(),
    "status" "text" DEFAULT 'Public'::"text"
);

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

ALTER TABLE "public"."routines" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."routines_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE "public"."users" (
    "name" "text" NOT NULL,
    "address" "text",
    "start_at" "date",
    "price" double precision DEFAULT '0'::double precision,
    "height" double precision,
    "birthdate" "date",
    "photo" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "deleted_at" timestamp with time zone,
    "role" "text" DEFAULT 'user'::"text" NOT NULL,
    "trainer_id" "uuid",
    "email" "text" NOT NULL,
    "id" "uuid" NOT NULL
);

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

ALTER TABLE ONLY "public"."contacts"
    ADD CONSTRAINT "contacts_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."contacts"
    ADD CONSTRAINT "contacts_url_key" UNIQUE ("url");

ALTER TABLE ONLY "public"."dietfoods"
    ADD CONSTRAINT "dieetfoods_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."diets"
    ADD CONSTRAINT "diets_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."evalphotos"
    ADD CONSTRAINT "evalphotos_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."evals"
    ADD CONSTRAINT "evals_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."favorites"
    ADD CONSTRAINT "favorites_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."foods"
    ADD CONSTRAINT "foods_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."measures"
    ADD CONSTRAINT "measures_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."routines"
    ADD CONSTRAINT "routines_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."users"
    ADD CONSTRAINT "users_pkey" PRIMARY KEY ("id");

CREATE TRIGGER "before_insert_diets" BEFORE INSERT ON "public"."diets" FOR EACH ROW EXECUTE FUNCTION "public"."set_trainer_id"();

ALTER TABLE ONLY "public"."contacts"
    ADD CONSTRAINT "contacts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

ALTER TABLE ONLY "public"."dietfoods"
    ADD CONSTRAINT "dietfoods_diet_id_fkey" FOREIGN KEY ("diet_id") REFERENCES "public"."diets"("id") ON DELETE CASCADE;

ALTER TABLE ONLY "public"."dietfoods"
    ADD CONSTRAINT "dietfoods_food_id_fkey" FOREIGN KEY ("food_id") REFERENCES "public"."foods"("id") ON DELETE CASCADE;

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

ALTER TABLE ONLY "public"."diets"
    ADD CONSTRAINT "diets_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE SET NULL;

ALTER TABLE ONLY "public"."evalphotos"
    ADD CONSTRAINT "evalphotos_eval_id_fkey" FOREIGN KEY ("eval_id") REFERENCES "public"."evals"("id") ON DELETE CASCADE;

ALTER TABLE ONLY "public"."evals"
    ADD CONSTRAINT "evals_trainer_id_fkey" FOREIGN KEY ("trainer_id") REFERENCES "auth"."users"("id") ON DELETE SET NULL;

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

ALTER TABLE ONLY "public"."favorites"
    ADD CONSTRAINT "favorites_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE;

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

ALTER TABLE ONLY "public"."measures"
    ADD CONSTRAINT "measures_eval_id_fkey" FOREIGN KEY ("eval_id") REFERENCES "public"."evals"("id") ON DELETE CASCADE;

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

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

ALTER TABLE ONLY "public"."users"
    ADD CONSTRAINT "users_trainer_id_fkey" FOREIGN KEY ("trainer_id") REFERENCES "auth"."users"("id");

CREATE POLICY "Admin and Trainer can insert food" ON "public"."foods" FOR INSERT TO "authenticated" WITH CHECK (("public"."is_admin"() OR "public"."is_trainer"()));

CREATE POLICY "Admin can delete contact" ON "public"."contacts" FOR DELETE TO "authenticated", "anon" USING ("public"."is_admin"());

CREATE POLICY "Admin can delete evalphoto" ON "public"."evalphotos" FOR DELETE TO "authenticated" USING ("public"."is_admin"());

CREATE POLICY "Admin can delete food" ON "public"."foods" FOR DELETE TO "authenticated" USING (("public"."is_admin"() OR ("trainer_id" = "auth"."uid"())));

CREATE POLICY "Admin can delete users" ON "public"."users" FOR DELETE TO "authenticated" USING ("public"."is_admin"());

CREATE POLICY "Admin can insert eval" ON "public"."evals" FOR INSERT TO "authenticated" WITH CHECK (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"()))));

CREATE POLICY "Admin can insert routine" ON "public"."routines" FOR INSERT TO "authenticated" WITH CHECK (("public"."is_admin"() OR "public"."is_trainer"()));

CREATE POLICY "Admin can update food" ON "public"."foods" FOR UPDATE TO "authenticated" USING ("public"."is_admin"());

CREATE POLICY "Admin or Trainer Prop can delete routine" ON "public"."routines" FOR DELETE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"()))));

CREATE POLICY "Admin or Trainer Proper can edit routine" ON "public"."routines" FOR UPDATE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"()))));

CREATE POLICY "Admin or Trainer can delete eval" ON "public"."evals" FOR DELETE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"()))));

CREATE POLICY "Admin or Trainer can delete measure" ON "public"."measures" FOR DELETE TO "authenticated" USING (("public"."is_admin"() OR "public"."is_trainer"()));

CREATE POLICY "Admin or Trainer can edit eval" ON "public"."evals" FOR UPDATE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"()))));

CREATE POLICY "Admin or Trainer or Proper can delete diet" ON "public"."diets" FOR DELETE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"())) OR ("user_id" = "auth"."uid"())));

CREATE POLICY "Admin or proper can edit contact" ON "public"."contacts" FOR UPDATE TO "authenticated" USING ((("user_id" = "auth"."uid"()) OR "public"."is_admin"()));

CREATE POLICY "Admin or proper can read diet" ON "public"."diets" FOR SELECT TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"())) OR ("public"."is_client"() AND (("user_id" = "auth"."uid"()) OR ("privacy" = 'public'::"text") OR (("privacy" = 'private'::"text") AND ("trainer_id" IN ( SELECT "users"."trainer_id"
   FROM "public"."users"
  WHERE ("users"."id" = "auth"."uid"())))))) OR ("public"."is_user"() AND ("privacy" = 'public'::"text"))));

CREATE POLICY "Admin or trainer proper can edit diet" ON "public"."diets" FOR UPDATE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"())) OR ("user_id" = "auth"."uid"())));

CREATE POLICY "Admin, trainer or proper can read eval" ON "public"."evals" FOR SELECT TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"())) OR ("user_id" = "auth"."uid"())));

CREATE POLICY "All users can insert diet" ON "public"."diets" FOR INSERT TO "authenticated" WITH CHECK (true);

CREATE POLICY "Anyone can insert user" ON "public"."users" FOR INSERT WITH CHECK (true);

CREATE POLICY "Anyone can't edit evalphotos" ON "public"."evalphotos" FOR UPDATE USING (false);

CREATE POLICY "Anyone insert contact" ON "public"."contacts" FOR INSERT WITH CHECK (true);

CREATE POLICY "Auth and Not User Can Insert" ON "public"."favorites" FOR INSERT TO "authenticated" WITH CHECK (("public"."is_admin"() OR "public"."is_client"() OR "public"."is_trainer"()));

CREATE POLICY "Auth can read contact" ON "public"."contacts" FOR SELECT USING (true);

CREATE POLICY "Auth can read evalphotos" ON "public"."evalphotos" FOR SELECT TO "authenticated" USING (true);

CREATE POLICY "Auth can read foods" ON "public"."foods" FOR SELECT TO "authenticated" USING (true);

CREATE POLICY "Auth can read measures" ON "public"."measures" FOR SELECT TO "authenticated" USING (true);

CREATE POLICY "Auth cand read dietfood" ON "public"."dietfoods" FOR SELECT TO "authenticated" USING (true);

CREATE POLICY "Auth or Trainer or Client can insert dietfood" ON "public"."dietfoods" FOR INSERT TO "authenticated" WITH CHECK (true);

CREATE POLICY "Auth or Trainer or Proper can delete dietfood" ON "public"."dietfoods" FOR DELETE TO "authenticated" USING ("public"."is_trainer"());

CREATE POLICY "Auth or Trainer or Proper can edit dietfood" ON "public"."dietfoods" FOR UPDATE TO "authenticated" USING (("public"."is_admin"() OR ("public"."is_trainer"() AND (( SELECT "diets"."trainer_id"
   FROM "public"."diets"
  WHERE ("diets"."id" = "dietfoods"."diet_id")
 LIMIT 1) = "auth"."uid"())) OR (( SELECT "diets"."user_id"
   FROM "public"."diets"
  WHERE ("diets"."id" = "dietfoods"."diet_id")
 LIMIT 1) = "auth"."uid"())));

CREATE POLICY "Auth or suscribed can read routines" ON "public"."routines" FOR SELECT TO "authenticated" USING (("public"."is_admin"() OR ("trainer_id" IN ( SELECT "users"."trainer_id"
   FROM "public"."users"
  WHERE ("users"."id" = "auth"."uid"()))) OR ("trainer_id" = "auth"."uid"()) OR ("status" = 'Public'::"text")));

CREATE POLICY "Enable read access for all users" ON "public"."contacts" TO "authenticated" USING ("public"."is_admin"());

CREATE POLICY "Proper can delete" ON "public"."favorites" FOR DELETE TO "authenticated" USING (("user_id" = "auth"."uid"()));

CREATE POLICY "Proper can read" ON "public"."favorites" FOR SELECT TO "authenticated" USING (("user_id" = "auth"."uid"()));

CREATE POLICY "Restrict editing users" ON "public"."users" FOR UPDATE TO "authenticated" USING ((("id" = "auth"."uid"()) OR "public"."is_admin"()));

CREATE POLICY "Restrict read users" ON "public"."users" FOR SELECT USING ((("public"."is_trainer"() AND ("trainer_id" = "auth"."uid"())) OR ("id" = "auth"."uid"()) OR "public"."is_admin"() OR ("auth"."uid"() IS NULL)));

CREATE POLICY "User can't edit measure" ON "public"."measures" FOR UPDATE TO "authenticated" USING ((NOT "public"."is_user"()));

CREATE POLICY "User can't insert evalphotos" ON "public"."evalphotos" FOR INSERT TO "authenticated" WITH CHECK ((NOT "public"."is_user"()));

CREATE POLICY "User can't insert measure" ON "public"."measures" FOR INSERT TO "authenticated" WITH CHECK ((NOT "public"."is_user"()));

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

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

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

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

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

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

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

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

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

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

REVOKE USAGE ON SCHEMA "public" FROM PUBLIC;
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"."create_user_and_contact"("name" "text", "email" "text", "role" "text", "user_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."create_user_and_contact"("name" "text", "email" "text", "role" "text", "user_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."create_user_and_contact"("name" "text", "email" "text", "role" "text", "user_id" "uuid") TO "service_role";

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

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

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

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

GRANT ALL ON FUNCTION "public"."remove_user"("user_id" "uuid") TO "anon";
GRANT ALL ON FUNCTION "public"."remove_user"("user_id" "uuid") TO "authenticated";
GRANT ALL ON FUNCTION "public"."remove_user"("user_id" "uuid") TO "service_role";

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

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

GRANT ALL ON SEQUENCE "public"."contacts_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."contacts_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."contacts_id_seq" TO "service_role";

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

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

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

GRANT ALL ON SEQUENCE "public"."dieetfoods_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."dieetfoods_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."dieetfoods_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."diets_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."diets_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."diets_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."evalphotos_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."evalphotos_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."evalphotos_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."evals_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."evals_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."evals_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."favorites_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."favorites_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."favorites_id_seq" TO "service_role";

GRANT ALL ON SEQUENCE "public"."foods_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."foods_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."foods_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."measures_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."measures_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."measures_id_seq" TO "service_role";

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

GRANT ALL ON SEQUENCE "public"."routines_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."routines_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."routines_id_seq" TO "service_role";

GRANT ALL ON TABLE "public"."users" TO "anon";
GRANT ALL ON TABLE "public"."users" TO "authenticated";
GRANT ALL ON TABLE "public"."users" 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";

RESET ALL;

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

  • Postgres is not only free and open-source but also it is highly extensible.
  • Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
  • ACID(Atomicity, Consistency, Isolation, Durability) compliant.
  • First DBMS which implemented Multi-version concurrency control (MVCC) feature.
  • It's the default database server for MacOS.
  • It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;