database.sql

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 "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";
SET default_tablespace = '';
SET default_table_access_method = "heap";

CREATE TABLE IF NOT EXISTS "public"."Cases" (
    "id" bigint NOT NULL,
    "player_id" bigint NOT NULL,
    "date_occurred" timestamp without time zone NOT NULL,
    "title" "text" NOT NULL,
    "time_remaining" bigint NOT NULL,
    "description" "text" NOT NULL,
    "location" "text" NOT NULL,
    "explanation_case_solved" "text" NOT NULL,
    "difficult" "text" NOT NULL,
    "image" "bytea"
);


ALTER TABLE "public"."Cases" OWNER TO "postgres";
ALTER TABLE "public"."Cases" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."Casos_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE IF NOT EXISTS "public"."Characters" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "name" "text" NOT NULL,
    "role" "text" NOT NULL,
    "description" "text" NOT NULL,
    "genre" "text" NOT NULL,
    "state_emotional" "text" NOT NULL,
    "state" "text" NOT NULL
);

ALTER TABLE "public"."Characters" OWNER TO "postgres";
ALTER TABLE "public"."Characters" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."Characters_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE IF NOT EXISTS "public"."Evidences" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "name" "text" NOT NULL,
    "description" "text" NOT NULL,
    "type" "text" NOT NULL,
    "location" "text" NOT NULL,
    "analysis" "text" NOT NULL
);

ALTER TABLE "public"."Evidences" OWNER TO "postgres";
ALTER TABLE "public"."Evidences" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."Evidences_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE IF NOT EXISTS "public"."Players" (
    "id" bigint NOT NULL,
    "name" character varying NOT NULL,
    "state" character varying NOT NULL,
    "progress" character varying NOT NULL
);

ALTER TABLE "public"."Players" OWNER TO "postgres";
ALTER TABLE "public"."Players" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."Jugadores_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE IF NOT EXISTS "public"."Messages" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "player_id" bigint NOT NULL,
    "message" "text" NOT NULL,
    "role" "text" NOT NULL
);


ALTER TABLE "public"."Messages" OWNER TO "postgres";
ALTER TABLE "public"."Messages" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."Messages_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE IF NOT EXISTS "public"."Timeline" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "date" "date" NOT NULL,
    "hour" time without time zone NOT NULL,
    "event" "text" NOT NULL
);

ALTER TABLE "public"."Timeline" OWNER TO "postgres";
ALTER TABLE "public"."Timeline" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "public"."TImeline_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

ALTER TABLE ONLY "public"."Cases"
    ADD CONSTRAINT "Casos_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Characters"
    ADD CONSTRAINT "Characters_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Evidences"
    ADD CONSTRAINT "Evidences_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Players"
    ADD CONSTRAINT "Jugadores_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Messages"
    ADD CONSTRAINT "Messages_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Timeline"
    ADD CONSTRAINT "TImeline_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."Cases"
    ADD CONSTRAINT "Casos_player_id_fkey" FOREIGN KEY ("player_id") REFERENCES "public"."Players"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY "public"."Characters"
    ADD CONSTRAINT "Characters_case_id_fkey" FOREIGN KEY ("case_id") REFERENCES "public"."Cases"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY "public"."Evidences"
    ADD CONSTRAINT "Evidences_case_id_fkey" FOREIGN KEY ("case_id") REFERENCES "public"."Cases"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY "public"."Messages"
    ADD CONSTRAINT "Messages_case_id_fkey" FOREIGN KEY ("case_id") REFERENCES "public"."Cases"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY "public"."Messages"
    ADD CONSTRAINT "Messages_player_id_fkey" FOREIGN KEY ("player_id") REFERENCES "public"."Players"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY "public"."Timeline"
    ADD CONSTRAINT "TImeline_case_id_fkey" FOREIGN KEY ("case_id") REFERENCES "public"."Cases"("id") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "public"."Cases" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "public"."Characters" ENABLE ROW LEVEL SECURITY;


CREATE POLICY "Enable insert for authenticated cases only" ON "public"."Cases" FOR INSERT TO "authenticated" WITH CHECK (true);
CREATE POLICY "Enable read access for all cases" ON "public"."Cases" FOR SELECT USING (true);

CREATE POLICY "Enable read access for all players" ON "public"."Players" FOR SELECT USING (true);

CREATE POLICY "Enable update for users based on cases" ON "public"."Cases" FOR UPDATE USING (true) WITH CHECK (true);

ALTER TABLE "public"."Evidences" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "public"."Messages" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "public"."Players" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "public"."Timeline" ENABLE ROW LEVEL SECURITY;
ALTER PUBLICATION "supabase_realtime" OWNER TO "postgres";


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 TABLE "public"."Cases" TO "anon";
GRANT ALL ON TABLE "public"."Cases" TO "authenticated";
GRANT ALL ON TABLE "public"."Cases" TO "service_role";
GRANT ALL ON SEQUENCE "public"."Casos_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."Casos_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."Casos_id_seq" TO "service_role";
GRANT ALL ON TABLE "public"."Characters" TO "anon";
GRANT ALL ON TABLE "public"."Characters" TO "authenticated";
GRANT ALL ON TABLE "public"."Characters" TO "service_role";
GRANT ALL ON SEQUENCE "public"."Characters_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."Characters_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."Characters_id_seq" TO "service_role";
GRANT ALL ON TABLE "public"."Evidences" TO "anon";
GRANT ALL ON TABLE "public"."Evidences" TO "authenticated";
GRANT ALL ON TABLE "public"."Evidences" TO "service_role";
GRANT ALL ON SEQUENCE "public"."Evidences_id_seq" TO "anon";
GRANT ALL ON SEQUENCE "public"."Evidences_id_seq" TO "authenticated";
GRANT ALL ON SEQUENCE "public"."Evidences_id_seq" TO "service_role";


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


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

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

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

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

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