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),
    "idSinal" integer DEFAULT nextval('sequence'::regclass) NOT NULL,
    idtask integer DEFAULT 0,
    "idSelo" integer,
    version integer DEFAULT 0,
    nome character varying(30),
    classe character varying(30),
    frase character varying(255),
    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;