SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE selo (
    "idSelo" integer NOT NULL,
    "idSinal" integer,
    data character varying(30),
    estado character varying(30),
    cidade character varying(30)
);

ALTER TABLE public.selo OWNER TO wikilibras;

CREATE SEQUENCE sequence
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.sequence OWNER TO wikilibras;

CREATE TABLE sinal (
    data character varying(30),
    -- TODO create table users
    "idUsuario" integer,
    usuario character varying(30),
    version integer DEFAULT 0,
    "idSinal" integer DEFAULT nextval('sequence'::regclass) NOT NULL,
    "idSelo" integer,
    nome character varying(30),
    classe character varying(30),
    frase character varying(100),
    estado character varying(30),
    cidade character varying(30),
    file character varying(100),
    blender character varying(100),
    avatar character varying(100)
);

ALTER TABLE public.sinal OWNER TO wikilibras;

CREATE TABLE "tipoSelo" (
    "nomeSelo" character varying(40),
    "idSelo" integer DEFAULT nextval('sequence'::regclass) NOT NULL
);

ALTER TABLE public."tipoSelo" OWNER TO wikilibras;

SELECT pg_catalog.setval('sequence', 1, false);

INSERT INTO "tipoSelo" VALUES ('wikilibras', 1);
INSERT INTO "tipoSelo" VALUES ('especialista', 2);
INSERT INTO "tipoSelo" VALUES ('invalido_wikilibras', 3);
INSERT INTO "tipoSelo" VALUES ('invalido_especialista', 4);
INSERT INTO "tipoSelo" VALUES ('animadores', 5);
INSERT INTO "tipoSelo" VALUES ('invalido_animadores', 6);
INSERT INTO "tipoSelo" VALUES ('null', 7);

ALTER TABLE ONLY sinal
    ADD CONSTRAINT "idSinal" PRIMARY KEY ("idSinal");

ALTER TABLE ONLY "tipoSelo"
    ADD CONSTRAINT "tipoSelo_pkey" PRIMARY KEY ("idSelo");

ALTER TABLE ONLY selo
    ADD CONSTRAINT "selo_idSelo_fkey" FOREIGN KEY ("idSelo") REFERENCES "tipoSelo"("idSelo");

ALTER TABLE ONLY selo
    ADD CONSTRAINT "selo_idSinal_fkey" FOREIGN KEY ("idSinal") REFERENCES sinal("idSinal");

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;