CREATE TYPE endereco AS (
    pais character varying(45),
    rua character varying(255),
    cidade character varying(45),
    estado character varying(45),
    cep character varying(45)
);

CREATE TYPE localizacao AS (
    pais character varying(45),
    rua character varying(255),
    cidade character varying(45),
    estado character varying(45),
    cep character varying(45),
    latitude numeric(22,16),
    longitude numeric(22,16)
);

CREATE TABLE public.grupo (
    id serial PRIMARY KEY,
    nome character varying(150) NOT NULL
);


ALTER TABLE public.grupo OWNER TO postgres;


CREATE TABLE public.grupo_permissao (
    id serial PRIMARY KEY,
    grupo_id integer NOT NULL,
    permissao_id integer NOT NULL
);


ALTER TABLE public.grupo_permissao OWNER TO postgres;


CREATE TABLE public.permissao (
    id serial PRIMARY KEY,
    nome character varying(255) NOT NULL,
    codinome character varying(100) NOT NULL
);


ALTER TABLE public.permissao OWNER TO postgres;


CREATE TABLE public.agressao (
    id serial PRIMARY KEY,
    nivel_de_confianca numeric(6,3),
    "hora" time NOT NULL,
    data date NOT NULL,
    usuaria_app_id integer,
    localizacao localizacao NOT NULL
);


ALTER TABLE public.agressao OWNER TO postgres;


CREATE TABLE public.usuaria_app (
    id serial PRIMARY KEY,
    nome_completo character varying(255) NOT NULL,
    data_nascimento date,
    endereco endereco
);


ALTER TABLE public.usuaria_app OWNER TO postgres;


CREATE TABLE public.usuario_portal (
    id serial PRIMARY KEY,
    senha character varying(128) NOT NULL,
    logado boolean NOT NULL,
    eh_superusuario boolean NOT NULL,
    usuario character varying(150) NOT NULL,
    email character varying(254) NOT NULL,
    e_equipe boolean NOT NULL,
    data_criacao timestamp NOT NULL,
    nome character varying(255) NOT NULL,
    endereco endereco
);


ALTER TABLE public.usuario_portal OWNER TO postgres;


CREATE TABLE public.usuario_portal_grupos (
    id serial PRIMARY KEY,
    usuario_id integer NOT NULL,
    grupo_id integer NOT NULL
);


ALTER TABLE public.usuario_portal_grupos OWNER TO postgres;


CREATE TABLE public.usuario_portal_usuario_permissao (
    id serial PRIMARY KEY,
    usuario_id integer NOT NULL,
    permissao_id integer NOT NULL
);


ALTER TABLE public.usuario_portal_usuario_permissao OWNER TO postgres;


ALTER TABLE ONLY public.grupo_permissao
    ADD CONSTRAINT grupo_permissao_permissao_id_fk_perm FOREIGN KEY (permissao_id) REFERENCES public.permissao(id) DEFERRABLE INITIALLY DEFERRED;


ALTER TABLE ONLY public.grupo_permissao
    ADD CONSTRAINT grupo_permissao_grupo_id_fk_grupo_id FOREIGN KEY (grupo_id) REFERENCES public.grupo(id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE ONLY public.usuario_portal_grupos
    ADD CONSTRAINT usuario_portal_grupos_grupo_id_fk_grupo_id FOREIGN KEY (grupo_id) REFERENCES public.grupo(id) DEFERRABLE INITIALLY DEFERRED;


ALTER TABLE ONLY public.usuario_portal_grupos
    ADD CONSTRAINT usuario_portal_grupos_usuario_id_fk_usuario_portal_id FOREIGN KEY (usuario_id) REFERENCES public.usuario_portal(id) DEFERRABLE INITIALLY DEFERRED;


ALTER TABLE ONLY public.usuario_portal_usuario_permissao
    ADD CONSTRAINT usuario_portal_usuario_perm_permissao_id_fk_perm FOREIGN KEY (permissao_id) REFERENCES public.permissao(id) DEFERRABLE INITIALLY DEFERRED;


ALTER TABLE ONLY public.usuario_portal_usuario_permissao
    ADD CONSTRAINT usuario_portal_usuario_permissao_usuario_id_fk_usuario_portal_id FOREIGN KEY (usuario_id) REFERENCES public.usuario_portal(id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE ONLY public.agressao
    ADD CONSTRAINT agressao_usuaria_app_id_fk_usuaria_app_id FOREIGN KEY (usuaria_app_id) REFERENCES public.usuaria_app(id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE ONLY public.usuario_portal
    ADD CONSTRAINT usuario_portal_email_unico UNIQUE (email);

ALTER TABLE ONLY public.usuario_portal
    ADD CONSTRAINT usuario_portal_usuario_unico UNIQUE (usuario);

INSERT INTO public.permissao (nome, codinome)
VALUES
('pode adicionar permissao', 'adicionar_permissao'),
('pode mudar permissao', 'mudar_permissao'),
('pode deletar permissao', 'deletar_permissao'),
('pode ver permissao', 'ver_permissao'),
('pode adicionar agressao', 'adicionar_agressao'),
('pode mudar agressao', 'mudar_agressao'),
('pode deletar agressao', 'deletar_agressao'),
('pode ver agressao', 'ver_agressao'),
('pode adicionar usuario', 'adicionar_usuario'),
('pode mudar usuario', 'mudar_usuario'),
('pode deletar usuario', 'deletar_usuario'),
('pode ver usuario', 'ver_usuario')
;

INSERT INTO public.grupo (nome)
VALUES
('delegado'),
('pesquisador'),
('admin')
;

INSERT INTO public.grupo_permissao (grupo_id, permissao_id)
VALUES
((SELECT id FROM public.grupo WHERE nome='delegado'), (SELECT id FROM public.permissao WHERE codinome='ver_agressao')),
((SELECT id FROM public.grupo WHERE nome='pesquisador'), (SELECT id FROM public.permissao WHERE codinome='adicionar_agressao')),
((SELECT id FROM public.grupo WHERE nome='admin'), (SELECT id FROM public.permissao WHERE codinome='deletar_usuario'))
;

INSERT INTO public.usuario_portal (senha, logado, eh_superusuario, usuario, email, e_equipe, data_criacao, nome, endereco)
VALUES
('12345678', TRUE, TRUE, 'usuarioteste 1', 'usuarioteste1@gmail.com', TRUE, '2009-04-30 02:33:21', 'teste um', ROW('pais 1', 'rua 1', 'cidade 1', 'estado 1', 'zipcode 1')),
('12345678', TRUE, TRUE, 'usuarioteste 2', 'usuarioteste2@gmail.com', TRUE, '2018-02-04 12:45:44', 'teste dois', ROW('pais 2', 'rua 2', 'cidade 2', 'estado 2', 'zipcode 2')),
('12345678', TRUE, TRUE, 'usuarioteste 3', 'usuarioteste3@gmail.com', TRUE, '2014-12-15 10:23:59', 'teste tres', ROW('pais 3', 'rua 3', 'cidade 3', 'estado 3', 'zipcode 3'))
;

INSERT INTO public.usuaria_app ( nome_completo, data_nascimento, endereco)
VALUES
('teste 1', '2019-10-11',  ROW('pais 1', 'rua 1', 'cidade 1', 'estado 1', 'zipcode 1')),
('teste 2', '2019-10-11',  ROW('pais 2', 'rua 2', 'cidade 2', 'estado 2', 'zipcode 2')),
('teste 3', '2019-10-11',  ROW('pais 3', 'rua 3', 'cidade 3', 'estado 3', 'zipcode 3'))
;


INSERT INTO public.agressao ( nivel_de_confianca, hora, data, usuaria_app_id, localizacao)
VALUES
(0.738, '14:30:00', '2019-10-10', (SELECT id FROM public.usuaria_app WHERE nome_completo='teste 1'), ROW('pais 1', 'rua 1', 'cidade 1', 'estado 1', 'zipcode 1', -8.144846, -34.905322))
;

--('teste 2', '2019-10-11',  ROW('pais 2', 'rua 2', 'cidade 2', 'estado 2', 'zipcode 2')),
--('teste 3', '2019-10-11',  ROW('pais 3', 'rua 3', 'cidade 3', 'estado 3', 'zipcode 3'))