-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: May 31, 2019 at 05:05 PM
-- Server version: 5.6.41-84.1
-- PHP Version: 7.2.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `imediaco_geo`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`obgeo529`@`localhost` FUNCTION `numerodatafinalizacao` (`equipe_id` INT, `provafase_id` INT) RETURNS BIGINT(20) NO SQL
BEGIN
DECLARE numerodatafinalizacao BIGINT DEFAULT 60000101235959;

SELECT SUM((CASE WHEN sessao.datahorafinalizacao IS NOT NULL THEN (DATE_FORMAT(sessao.datahorafinalizacao, '%Y%m%d%H%i

%s')) ELSE 30000101235959 END)) INTO numerodatafinalizacao 
FROM imediaco_geo.sessao 
WHERE sessao.equipe_id = equipe_id AND (CASE WHEN provafase_id IS NULL OR provafase_id  = -1 THEN 1=1 ELSE 

sessao.provafase_id = provafase_id END);

IF(numerodatafinalizacao IS NULL) THEN RETURN 60000101235959; END IF;

RETURN numerodatafinalizacao;
END$$

CREATE DEFINER=`obgeo529`@`localhost` FUNCTION `pontuacao` (`equipe_id` INT, `provafase_id` INT) RETURNS DOUBLE NO SQL
BEGIN
DECLARE pontuacao REAL DEFAULT 0;

SELECT SUM(1 * (CASE WHEN categoria.id IS NOT NULL THEN categoria.peso ELSE 1 END)) INTO pontuacao 
FROM imediaco_geo.resposta
INNER JOIN imediaco_geo.questao ON questao.id = resposta.questao_id
INNER JOIN imediaco_geo.alternativa ON alternativa.questao_id = questao.id AND alternativa.correta = '1'
LEFT JOIN imediaco_geo.categoria ON categoria.id = questao.categoria_id
WHERE questao.status = 'ATIVO' AND resposta.equipe_id = equipe_id AND (CASE WHEN provafase_id IS NULL OR provafase_id  = -1 THEN 1=1 ELSE questao.provafase_id = provafase_id END) AND resposta.alternativa_id = alternativa.id;

IF(pontuacao IS NULL) THEN RETURN 0; END IF;

RETURN pontuacao;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `alternativa`
--

CREATE TABLE `alternativa` (
  `id` bigint(20) NOT NULL,
  `questao_id` bigint(20) NOT NULL,
  `descricao` blob NOT NULL,
  `letra` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `correta` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


--
-- Table structure for table `campocalculado`
--

CREATE TABLE `campocalculado` (
  `id` bigint(20) NOT NULL,
  `equipe_id` bigint(20) NOT NULL,
  `campo` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valor` decimal(40,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `categoria`
--

CREATE TABLE `categoria` (
  `id` bigint(20) NOT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ATIVO',
  `descricao` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `peso` double NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `categoria`
--

INSERT INTO `categoria` (`id`, `status`, `descricao`, `peso`) VALUES
(2, 'ATIVO', 'Geografia Humana', 1),
(3, 'ATIVO', 'Geografia Física', 1),
(4, 'ATIVO', 'Cartografia', 1);

-- --------------------------------------------------------

--
-- Table structure for table `equipe`
--

CREATE TABLE `equipe` (
  `id` bigint(20) NOT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ATIVO',
  `prova_id` bigint(20) NOT NULL,
  `nome` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `telefone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `senha` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `escola` text COLLATE utf8_unicode_ci,
  `tipoescola` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `codigoinep` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `professor` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `professorcpf` char(11) COLLATE utf8_unicode_ci NOT NULL,
  `professorsexo` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `professorcategorias` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `professoremail` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `professortelefone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `professorcelular` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `diretor` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `logradouro` text COLLATE utf8_unicode_ci,
  `bairro` text COLLATE utf8_unicode_ci,
  `complemento` text COLLATE utf8_unicode_ci,
  `municipio` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uf` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cep` char(9) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pago` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `colocacao` smallint(6) DEFAULT NULL,
  `medalha` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `equipepagamento`
--

CREATE TABLE `equipepagamento` (
  `id` bigint(20) NOT NULL,
  `equipe_id` bigint(20) NOT NULL,
  `datapagamento` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valor` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `dataregistro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `integranteequipe`
--

CREATE TABLE `integranteequipe` (
  `id` bigint(20) NOT NULL,
  `equipe_id` bigint(20) NOT NULL,
  `nome` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cpf` char(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datanascimento` date DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tipo` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `sexo` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `categorias` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `id` bigint(20) NOT NULL,
  `datahoraregistro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `usuario` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `operacao` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `query` text COLLATE utf8_unicode_ci NOT NULL,
  `identificacaotupla` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ip` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `prova`
--

CREATE TABLE `prova` (
  `id` bigint(20) NOT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ATIVO',
  `descricao` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `isentopagamento` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NÃO',
  `datainscricaoinicio1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datainscricaofim1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datainscricaoinicio2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datainscricaofim2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `prova`
--

INSERT INTO `prova` (`id`, `status`, `descricao`, `isentopagamento`, `datainscricaoinicio1`, `datainscricaofim1`, `datainscricaoinicio2`, `datainscricaofim2`) VALUES
(1, 'INATIVO', 'Olimpíada Brasileira de Geografia 2017', 'NÃO', '2017-05-30 03:00:00', '2017-07-01 02:59:59', '2017-07-01 03:00:00', '2017-08-01 02:59:59'),
(2, 'ATIVO', 'IV Olimpíada Geo-Brasil 2018 (IV OBG e II OBCT)', 'SIM', '2018-06-01 03:00:00', '2018-06-30 02:59:59', '2018-06-30 03:00:00', '2018-07-14 02:59:59');

-- --------------------------------------------------------

--
-- Table structure for table `provafase`
--

CREATE TABLE `provafase` (
  `id` int(11) NOT NULL,
  `prova_id` int(11) NOT NULL,
  `fase` tinyint(4) NOT NULL,
  `datainicio` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datafim` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `duracao` mediumint(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `provafase`
--

INSERT INTO `provafase` (`id`, `prova_id`, `fase`, `datainicio`, `datafim`, `duracao`) VALUES
(1, 1, 1, '2017-08-05 03:00:00', '2017-08-16 02:59:59', NULL),
(2, 1, 2, '2017-08-20 03:00:00', '2017-08-29 02:59:59', NULL),
(3, 2, 1, '2018-08-06 03:00:00', '2018-08-12 02:59:59', NULL),
(4, 2, 2, '2018-08-20 03:00:00', '2018-08-26 02:59:59', NULL),
(5, 2, 3, '2018-08-27 03:00:00', '2018-08-29 02:59:59', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `questao`
--

CREATE TABLE `questao` (
  `id` bigint(20) NOT NULL,
  `datahoraregistro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ATIVO',
  `provafase_id` int(11) NOT NULL,
  `descricao` blob NOT NULL,
  `categoria_id` bigint(20) DEFAULT NULL,
  `numero` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `resposta`
--

CREATE TABLE `resposta` (
  `id` bigint(20) NOT NULL,
  `datahoraregistro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alternativa_id` bigint(20) NOT NULL,
  `equipe_id` bigint(20) NOT NULL,
  `questao_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `sessao`
--

CREATE TABLE `sessao` (
  `id` bigint(20) NOT NULL,
  `datahora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `equipe_id` bigint(20) NOT NULL,
  `provafase_id` int(11) NOT NULL,
  `datahorafinalizacao` timestamp NULL DEFAULT NULL,
  `protocolo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



--
-- Table structure for table `usuario`
--

CREATE TABLE `usuario` (
  `id` bigint(20) NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `senha` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `nome` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `perfil` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `usuario`
--

INSERT INTO `usuario` (`id`, `email`, `senha`, `nome`, `perfil`) VALUES
(1, 'gustavo', 'a29d1598024f9e87beab4b98411d48ce', 'Gustavo Afonso', 'administrador'),
(2, 'clibsonsantos@gmail.com', '01f1fbcec4ad1347a744f6fb61fc7b8c', 'Clibson Alves dos Santos', 'administrador,elaboradorquestoes,editarequipes'),
(3, 'diogo.bioinformatica@gmail.com', '3183f4d2704e0253091f3716fb72fa65', 'Diogo', 'administrador'),
(4, 'agnaldo.tomaz@gmail.com', 'e10adc3949ba59abbe56e057f20f883e', 'Agnaldo Tomaz', 'confirmapagamento');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `alternativa`
--
ALTER TABLE `alternativa`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `questao_id_2` (`questao_id`,`letra`),
  ADD KEY `questao_id` (`questao_id`);

--
-- Indexes for table `campocalculado`
--
ALTER TABLE `campocalculado`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `equipe_id_2` (`equipe_id`,`campo`),
  ADD KEY `equipe_id` (`equipe_id`);

--
-- Indexes for table `categoria`
--
ALTER TABLE `categoria`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `key_categoria` (`descricao`);

--
-- Indexes for table `equipe`
--
ALTER TABLE `equipe`
  ADD PRIMARY KEY (`id`),
  ADD KEY `prova_id` (`prova_id`);

--
-- Indexes for table `equipepagamento`
--
ALTER TABLE `equipepagamento`
  ADD PRIMARY KEY (`id`),
  ADD KEY `equipe_id` (`equipe_id`);

--
-- Indexes for table `integranteequipe`
--
ALTER TABLE `integranteequipe`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `equipe_id_2` (`equipe_id`,`cpf`),
  ADD KEY `equipe_id` (`equipe_id`);

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `prova`
--
ALTER TABLE `prova`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `provafase`
--
ALTER TABLE `provafase`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `prova_id_2` (`prova_id`,`fase`),
  ADD KEY `prova_id` (`prova_id`);

--
-- Indexes for table `questao`
--
ALTER TABLE `questao`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `prova_id_2` (`provafase_id`,`numero`),
  ADD KEY `prova_id` (`provafase_id`),
  ADD KEY `categoria_id` (`categoria_id`);

--
-- Indexes for table `resposta`
--
ALTER TABLE `resposta`
  ADD PRIMARY KEY (`id`),
  ADD KEY `alternativa_id` (`alternativa_id`),
  ADD KEY `equipe_id` (`equipe_id`),
  ADD KEY `questao_id` (`questao_id`);

--
-- Indexes for table `sessao`
--
ALTER TABLE `sessao`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `equipe_id_2` (`equipe_id`,`provafase_id`),
  ADD KEY `provafase_id` (`provafase_id`),
  ADD KEY `equipe_id` (`equipe_id`);

--
-- Indexes for table `usuario`
--
ALTER TABLE `usuario`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `alternativa`
--
ALTER TABLE `alternativa`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=741;

--
-- AUTO_INCREMENT for table `campocalculado`
--
ALTER TABLE `campocalculado`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=129597;

--
-- AUTO_INCREMENT for table `categoria`
--
ALTER TABLE `categoria`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `equipe`
--
ALTER TABLE `equipe`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13255;

--
-- AUTO_INCREMENT for table `equipepagamento`
--
ALTER TABLE `equipepagamento`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2048;

--
-- AUTO_INCREMENT for table `integranteequipe`
--
ALTER TABLE `integranteequipe`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51194;

--
-- AUTO_INCREMENT for table `log`
--
ALTER TABLE `log`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1249602;

--
-- AUTO_INCREMENT for table `prova`
--
ALTER TABLE `prova`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `provafase`
--
ALTER TABLE `provafase`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `questao`
--
ALTER TABLE `questao`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=159;

--
-- AUTO_INCREMENT for table `resposta`
--
ALTER TABLE `resposta`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=503854;

--
-- AUTO_INCREMENT for table `sessao`
--
ALTER TABLE `sessao`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32823;

--
-- AUTO_INCREMENT for table `usuario`
--
ALTER TABLE `usuario`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `alternativa`
--
ALTER TABLE `alternativa`
  ADD CONSTRAINT `fi_alternativa_questao` FOREIGN KEY (`questao_id`) REFERENCES `questao` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `campocalculado`
--
ALTER TABLE `campocalculado`
  ADD CONSTRAINT `campocalculado_ibfk_1` FOREIGN KEY (`equipe_id`) REFERENCES `equipe` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `equipe`
--
ALTER TABLE `equipe`
  ADD CONSTRAINT `fk_equipe_prova` FOREIGN KEY (`prova_id`) REFERENCES `prova` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `equipepagamento`
--
ALTER TABLE `equipepagamento`
  ADD CONSTRAINT `equipepagamento_ibfk_1` FOREIGN KEY (`equipe_id`) REFERENCES `equipe` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `integranteequipe`
--
ALTER TABLE `integranteequipe`
  ADD CONSTRAINT `fk_integranteequipe_equipe` FOREIGN KEY (`equipe_id`) REFERENCES `equipe` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `questao`
--
ALTER TABLE `questao`
  ADD CONSTRAINT `fk_questao_categoria` FOREIGN KEY (`categoria_id`) REFERENCES `categoria` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_questao_provafase_id` FOREIGN KEY (`provafase_id`) REFERENCES `provafase` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `sessao`
--
ALTER TABLE `sessao`
  ADD CONSTRAINT `sessao_ibfk_1` FOREIGN KEY (`equipe_id`) REFERENCES `equipe` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sessao_ibfk_2` FOREIGN KEY (`provafase_id`) REFERENCES `provafase` (`id`) ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
