/**
 * Laboratorio de Base de Dados
 * Profa. Dra. Cristina Dutra de Aguiar Ciferri
 * Script de criacao de dados no esquema campeonatos de futebol
*/

--Drop em todas as tabelas, eliminando tambem suas restricoes
DROP TABLE patrocinador CASCADE CONSTRAINTS;
DROP TABLE patrocinio CASCADE CONSTRAINTS;
DROP TABLE clube CASCADE CONSTRAINTS;
DROP TABLE clubePossuiEst CASCADE CONSTRAINTS;
DROP TABLE estadio CASCADE CONSTRAINTS;
DROP TABLE equipe CASCADE CONSTRAINTS;
DROP TABLE partida CASCADE CONSTRAINTS;
DROP TABLE campeonato CASCADE CONSTRAINTS;
DROP TABLE eqInscreveCamp CASCADE CONSTRAINTS;
DROP TABLE arbitro CASCADE CONSTRAINTS;
DROP TABLE arbitra CASCADE CONSTRAINTS;
DROP TABLE membro CASCADE CONSTRAINTS;
DROP TABLE treinador CASCADE CONSTRAINTS;
DROP TABLE jogador CASCADE CONSTRAINTS;
DROP TABLE participa CASCADE CONSTRAINTS;

/**
 * Tabela Patrocinador
 * @cnpjPat 	chave primaria
 * @nomePat 	razao social da empresa
 * @apelidoPat 	nome fantasia da empresa
 * @PK_PATROCINADOR restricao de chave primaria
 */
CREATE TABLE patrocinador (
	cnpjPat VARCHAR2(18) NOT NULL,
	nomePat VARCHAR2(50),
	apelidoPat VARCHAR2(50),
	CONSTRAINT PK_PATROCINADOR PRIMARY KEY (cnpjPat)
);


/**
 * Tabela de clubes
 * @cnpjClube 		chave primaria
 * @nomeClube 		razao social do clube
 * @apelidoClube 	nome fantasia do clube
 * @PK_CLUBE restricao de chave primaria
 */
CREATE TABLE clube (
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeClube VARCHAR2(50),
	apelidoClube VARCHAR2(50),
	CONSTRAINT PK_CLUBE PRIMARY KEY (cnpjClube)
);

/**
 * Tabela de patrocinios entre patrocinadores e clubes, deve-se inserir tanto a data de inicio do contrato como a data prevista para o termino
 * @cnpjPat, cnpjClube, dataInicPc 	chaves primarias de patrocinio
 * @dataFimPc 						data prevista para termino do patrocinio
 * @valorPc							valor a ser pago pelo patrocinador anualmente
 * @PK_PATROCINIO					restricao de chave primaria
 * @FK_PATROCINIO1					restricao de chave estrangeira com a tabela patrocinador, ao ser removido o patrocinador a tupla e tambem removida
 * @FK_PATROCINIO2					restricao de chave estrangeira com a tabela clube, ao ser removido o clube a tupla e tambem removida
 * @CH_PATROCINIO1					checagem de valor de patrocinio positivo
 * @CH_PATROCINIO2					checagem de data de inicio menor que data de tErmino
 */
CREATE TABLE patrocinio(
	cnpjPat VARCHAR2(18) NOT NULL,
	cnpjClube VARCHAR2(18) NOT NULL,
	dataInicPc DATE NOT NULL,
	dataFimPc DATE,
	valorPc NUMBER(10,2),
	CONSTRAINT PK_PATROCINIO PRIMARY KEY (cnpjPat,cnpjClube,dataInicPc),
	CONSTRAINT FK_PATROCINIO1 FOREIGN KEY (cnpjPat) REFERENCES patrocinador(cnpjPat)
		ON DELETE CASCADE,
	CONSTRAINT FK_PATROCINIO2 FOREIGN KEY (cnpjClube) REFERENCES clube(cnpjClube)
		ON DELETE CASCADE,
	CONSTRAINT CH_PATROCINIO1 CHECK (valorPc >= 0.0),
	CONSTRAINT CH_PATROCINIO2 CHECK (dataFimPc > dataInicPc)
);


/**
 * Tabela de estadios
 * @nomeEst			nome oficial do estadio
 * @capacidadeEst	lotacao maxima de espectadores
 * @valorAluguelEst	valor a ser pago ao proprietario do estadio
 * @PK_ESTADIO		restricao de chave primaria
 * @CH_ESTADIO1		checagem de capacidade positiva e maior que zero
 * @CH_ESTADIO2		checagem de valor de aluguel positiva
 */
CREATE TABLE estadio(
	nomeEst VARCHAR2(50) NOT NULL,
	capacidadeEst NUMBER(7),
	valorAluguelEst NUMBER(10,2),
	CONSTRAINT PK_ESTADIO PRIMARY KEY (nomeEst),
	CONSTRAINT CH_ESTADIO1 CHECK (capacidadeEst > 0),
	CONSTRAINT CH_ESTADIO2 CHECK (valorAluguelEst >= 0)
);


/**
 * Tabela de clubes que possuem estadios
 * @cnpjClube, nomeEst	chaves primarias
 * @PK_CLUBEPOSSUIEST	restricao de chave primaria
 * @FK_CLUBEPOSSUIEST1	restricao de chave estrangeira com a tabela clube, ao ser removido um clube a tupla E tambEm removida
 * @FK_CLUBEPOSSUIEST2	restricao de chave estrangeira com a tabela estadio, ao ser removido um estadio a tupla E tambEm removida
 */
CREATE TABLE clubePossuiEst(
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEst VARCHAR2(50) NOT NULL,
	CONSTRAINT PK_CLUBEPOSSUIEST PRIMARY KEY (cnpjClube, nomeEst),
	CONSTRAINT FK_CLUBEPOSSUIEST1 FOREIGN KEY (cnpjClube) REFERENCES clube(cnpjClube)
		ON DELETE CASCADE,
	CONSTRAINT FK_CLUBEPOSSUIEST2 FOREIGN KEY (nomeEst) REFERENCES estadio(nomeEst)
		ON DELETE CASCADE
);


/**
 * Tabela de equipes
 * @cnpjClube, nomeEq	chaves primarias, podem ser nomeEq "Equipe Profissional", "Equipe Junior" e outros
 * @nroJogadoresEq		quantidade de jogadores em cada equipe, pode ser obtida atravEs da contagem na tabela jogador
 * @nroTitulosEq		quantidade de titulos obtidos pela equipe, pode ser obtida pela contagem na tabela campeonato
 * @PK_EQUIPE			restricao de chave primaria
 * @FK_EQUIPE			restricao de chave estrangeira com a tabela clube
 * @CH_EQUIPE1			checagem de numero de jogadores positivo
 * @CH_EQUIPE2			checagem de numero de titulos positivo
 */
CREATE TABLE equipe(
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEq VARCHAR2(50) NOT NULL,
	nroJogadoresEq NUMBER(2),
	nroTitulosEq NUMBER(3),
	CONSTRAINT PK_EQUIPE PRIMARY KEY (cnpjClube, nomeEq),
	CONSTRAINT FK_EQUIPE FOREIGN KEY (cnpjClube) REFERENCES clube(cnpjClube)
		ON DELETE CASCADE,
	CONSTRAINT CH_EQUIPE1 CHECK (nroJogadoresEq >= 0),
	CONSTRAINT CH_EQUIPE2 CHECK (nroTitulosEq >= 0)
);


/**
 * Tabela de campeonatos
 * @idCamp							chave primaria, sendo um numero sequencial obtido automaticamente pelo SGBD
 * @nomeCamp, anoCamp, divisaoCamp	chaves secundarias, onde a divisao do campeonato pode ser "A", "B", "B1"...
 * @abrangenciaCamp					abrangencia do campeonato em "ESTADUAL", "NACIONAL", "CONTINENTAL" e "MUNDIAL"
 * @cnpjClubeVence, nomeEqVence		equipe vencedora do campeonato
 * @pontosVitoria					valor de pontos ganhos em uma vitoria no campeonato
 * @pontosEmpate					valor de pontos ganhos em um empate no campeonato
 * @PK_CAMPEONATO					restricao de chave primaria
 * @UN_CAMPEONATO					restricao de chave secundaria
 * @FK_CAMPEONATO					restricao de chave estrangeira com a tabela equipe, ao ser removida a equipe campeao, e setado NULL
 * @CH_CAMPEONATO					checagem de atributo abrangenciaCamp pertencentes aos possiveis valores
 */
CREATE TABLE campeonato(
	idCamp NUMBER(3) NOT NULL,
	nomeCamp VARCHAR2(50) NOT NULL,
	anoCamp DATE NOT NULL,
	divisaoCamp CHAR(2) NOT NULL,
	abrangenciaCamp VARCHAR2(11),
	cnpjClubeVence VARCHAR2(18),
	nomeEqVence VARCHAR2(50),
	pontosVitoria NUMBER(2),
	pontosEmpate NUMBER(2),
	CONSTRAINT PK_CAMPEONATO PRIMARY KEY (idCamp),
	CONSTRAINT UN_CAMPEONATO UNIQUE (nomeCamp, anoCamp, divisaoCamp),
	CONSTRAINT FK_CAMPEONATO FOREIGN KEY (cnpjClubeVence,nomeEqVence) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE SET NULL,
	CONSTRAINT CH_CAMPEONATO CHECK (UPPER(abrangenciaCamp) IN ('ESTADUAL','NACIONAL','CONTINENTAL','MUNDIAL'))
);


/**
 * Tabela de partidas
 * @idCamp, nroPartida					chaves primarias, sendo nroPartida um numero sequencial obtido automaticamente pelo SGBD
 * @cnpjClubeMandante, nomeEqMandante	equipe mandante do jogo, caso a equipe mandante nao seja proprietaria do estadio, deve-se pagar o valor de aluguel
 * @cnpjClubeVisitante, nomeEqVisitante	equipe visitante
 * @dataPartida							data da partida
 * @horaPartida							horario da partida
 * @valorIngresso						valor do ingresso
 * @nroTorcedores						numero de jogos presentes no estadio
 * @nomeEst								estadio onde a partida foi realizada
 * @PK_PARTIDA							restricao de chave primaria
 * @FK_PARTIDA1							restricao de chave estrangeira com a tabela campeonato, caso o campeonato seja removido a tupla e tambem removida
 * @FK_PARTIDA2, FK_PARTIDA3			restricao de chave estrangeira com a tabela equipe, caso a equipe seja removida, E setado NULL
 * @FK_PARTIDA4							restricao de chave estrangeira com a tabela estadio, caso o estadio seja removido, E setado NULL
 * @CH_PARTIDA1							checagem de valor do ingresso positivo
 */
CREATE TABLE partida (
	idCamp NUMBER(3) NOT NULL,
	nroPartida NUMBER(3) NOT NULL,
	cnpjClubeMandante VARCHAR2(18),
	nomeEqMandante VARCHAR2(50),
	cnpjClubeVisitante VARCHAR2(18),
	nomeEqVisitante VARCHAR2(50),
	dataPartida DATE,
	horaPartida TIMESTAMP,
	valorIngresso NUMBER(6,2),
	nroTorcedores NUMBER(7),
	nomeEst VARCHAR2(50),
	CONSTRAINT PK_PARTIDA PRIMARY KEY (idCamp, nroPartida),
	CONSTRAINT FK_PARTIDA1 FOREIGN KEY (idCamp) REFERENCES campeonato(idCamp)
		ON DELETE CASCADE,
	CONSTRAINT FK_PARTIDA2 FOREIGN KEY (cnpjClubeMandante,nomeEqMandante) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE SET NULL,
	CONSTRAINT FK_PARTIDA3 FOREIGN KEY (cnpjClubeVisitante,nomeEqVisitante) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE SET NULL,
	CONSTRAINT FK_PARTIDA4 FOREIGN KEY (nomeEst) REFERENCES estadio(nomeEst)
		ON DELETE SET NULL,
	CONSTRAINT CH_PARTIDA1 CHECK (valorIngresso >= 0.0)
);


/**
 * Tabela de equipes inscritas nos campeonatos
 * @cnpjClube, nomeEq, idCamp	chave primaria
 * @pontos						pontos obtidos no campeonato pela equipe, pode ser obtida pelas partidas realizadas pela equipe no campeonato
 * @saldoGols					saldo de gols da equipe no campeonato, pode ser obtida pelas partidas realizadas pela equipe no campeonato (gols a favor - gols tomados)
 * @lucro						lucro obtido no campeonato pela equipe, considerando os ingressos vendidos e se houver, aluguel de estadio
 * @PK_EQINSCREVECAMP			restricao de chave primaria
 * @FK_EQINSCREVECAMP1			restricao de chave estrangeira com a tabela equipe, caso a equipe seja removida a tupla E removida
 * @FK_EQINSCREVECAMP2			restricao de chave estrangeira com a tabela campeonato, caso o campeonato seja removida a tupla E removida
 */
CREATE TABLE eqInscreveCamp(
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEq VARCHAR2(50) NOT NULL,
	idCamp NUMBER(3) NOT NULL,
	pontos NUMBER(3) DEFAULT 0,
	saldoGols NUMBER(3) DEFAULT 0,
	lucro NUMBER(12,2) DEFAULT 0.0,
	CONSTRAINT PK_EQINSCREVECAMP PRIMARY KEY (cnpjClube, nomeEq, idCamp),
	CONSTRAINT FK_EQINSCREVECAMP1 FOREIGN KEY (cnpjClube,nomeEq) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE CASCADE,
	CONSTRAINT FK_EQINSCREVECAMP2 FOREIGN KEY (idCamp) REFERENCES campeonato(idCamp)
		ON DELETE CASCADE
);

/**
 * Tabela de arbitros
 * @cpfArb		chave primaria
 * @nomeArb		nome completo do arbitro
 * @apelidoArb	apelido do arbitro
 * @PK_ARBITRO	restricao de chave primaria
 */
CREATE TABLE arbitro (
	cpfArb VARCHAR2(14) NOT NULL,
	nomeArb VARCHAR2(50),
	apelidoArb VARCHAR2(50),
	CONSTRAINT PK_ARBITRO PRIMARY KEY (cpfArb)
);


/**
 * Tabela de partidas e seus respectivos arbitros
 * @cpfArb, idCamp, nroPartida	chaves primarias
 * @funcao						funcao realizada pelo arbitro, podendo ser "PRINCIPAL", "ASSISTENTE" e "RESERVA"
 * @pagamento					valor pago ao arbitro pela partida
 * @PK_ARBITRA					restricao de chave primaria
 * @FK_ARBITRA1					restricao de chave estrangeira com a tabela arbitro, caso o arbitro seja removida a tupla E tambEm removida
 * @FK_ARBITRA2					restricao de chave estrangeira com a tabela partida, caso a partida seja removida a tupla E tambEm removida
 * @CH_ARBITRA1					checagem do atributo funcao pertencentes aos possiveis valores
 * @CH_ARBITRA2					checagem de pagamento positivo
 */
CREATE TABLE arbitra(
	cpfArb VARCHAR2(14) NOT NULL,
	idCamp NUMBER(3) NOT NULL,
	nroPartida NUMBER(3) NOT NULL,
	funcao VARCHAR2(10),
	pagamento NUMBER(8,2),
	CONSTRAINT PK_ARBITRA PRIMARY KEY (cpfArb, idCamp, nroPartida),
	CONSTRAINT FK_ARBITRA1 FOREIGN KEY (cpfArb) REFERENCES arbitro(cpfArb)
		ON DELETE CASCADE,
	CONSTRAINT FK_ARBITRA2 FOREIGN KEY (idCamp,nroPartida) REFERENCES partida(idCamp,nroPartida)
		ON DELETE CASCADE,
	CONSTRAINT CH_ARBITRA1 CHECK (UPPER(funcao) IN ('PRINCIPAL','ASSISTENTE','RESERVA')),
	CONSTRAINT CH_ARBITRA2 CHECK (pagamento >= 0.0)
);


/**
 * Tabela dos membros das equipes
 * @cpfMembro		chave primaria
 * @nomeMembro		nome completo do membro
 * @apelidoMembro	apelido do membro
 * @tipoTecnico		booleano indicando se o membro E tEcnico
 * @tipoAtleta		booleano indicando se o mebmro E atleta
 * @PK_MEMBRO		restricao de chave primaria
 * @CH_MEMBRO1,2	restricao dos atributos tipoAtleta e tipoTecnico pertencentes a "TRUE" ou "FALSE"
 */
CREATE TABLE membro(
	cpfMembro VARCHAR2(14) NOT NULL,
	nomeMembro VARCHAR2(50),
	apelidoMembro VARCHAR2(50),
	tipoTecnico VARCHAR2(5),
	tipoAtleta VARCHAR2(5),
	CONSTRAINT PK_MEMBRO PRIMARY KEY (cpfMembro),
	CONSTRAINT CH_MEMBRO1 CHECK (UPPER(tipoTecnico) IN ('TRUE','FALSE')),
	CONSTRAINT CH_MEMBRO2 CHECK (UPPER(tipoAtleta) IN ('TRUE','FALSE'))
);


/**
 * Tabela de treinadores e equipe que participa, deve-se ser inseridas tanto a data de inicio do contrato, como tambEm a data prevista para o tErmino
 * @cpfTreinador, cnpjClube, nomeEq, dataInicTreina	chaves primarias
 * @dataFimTreina									data prevista para tErmino do contrato
 * @salarioTreina									salario mensal do treinador
 * @funcaoTreina									atividade realizada na equipe ("TECNICO", "AUXILIAR TECNICO", "TREINADOR DE GOLEIRO" ou "OUTRO")
 * @PK_TREINADOR									restricao de chave primaria
 * @FK_TREINADOR1									restricao de chave estrangeira com a tabela membro, caso o membro seja removido a tupla E tambEm removida
 * @FK_TREINADOR2									restricao de chave estrangeira com a tabela equipe, caso a equipe seja removida a tupla E tambEm removida
 * @CH_TREINADOR1									checagem de atributo funcaoTreina pertencente aos possiveis valores
 * @CH_TREINADOR2									checagem de data de inicio menor que data de tErmino
 */
CREATE TABLE treinador(
	cpfTreinador VARCHAR2(14) NOT NULL,
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEq VARCHAR2(50) NOT NULL,
	dataInicTreina DATE NOT NULL,
	dataFimTreina DATE,
	salarioTreina NUMBER(10,2),
	funcaoTreina VARCHAR2(18),
	CONSTRAINT PK_TREINADOR PRIMARY KEY (cpfTreinador, cnpjClube, nomeEq, dataInicTreina),
	CONSTRAINT FK_TREINADOR1 FOREIGN KEY (cpfTreinador) REFERENCES membro(cpfMembro)
		ON DELETE CASCADE,
	CONSTRAINT FK_TREINADOR2 FOREIGN KEY (cnpjClube,nomeEq) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE CASCADE,
	CONSTRAINT CH_TREINADOR1 CHECK (UPPER(funcaoTreina) IN ('TECNICO','AUXILIAR TECNICO','TREINADOR DE GOLEIRO','OUTRO')),
	CONSTRAINT CH_TREINADOR2 CHECK (dataFimTreina > dataInicTreina)
);


/**
 * Tabela de jogadores e equipe que participa, deve-se ser inseridas tanto a data de inicio do contrato, como tambEm a data prevista para o tErmino
 * @cpfJogador, cnpjClube, nomeEq, dataInicJog	chaves primarias
 * @dataFimJog									data prevista para tErmino do contrato
 * @salarioJog									salario mensal do jogador
 * @PK_JOGADOR									restricao de chave primaria
 * @FK_JOGADOR1									restricao de chave estrangeira com a tabela membro, caso o membro seja removida a tupla E tambEm removida
 * @FK_JOGADOR2									restricao de chave estrangeira com a tabela equipe, caso a equipe seja removida a tupla E tambEm removida
 * @CH_JOGADOR1									checagem de data de inicio menor que data de tErmino
 * @CH_JOGADOR2									checagem de salario positivo
 */
CREATE TABLE jogador (
	cpfJogador VARCHAR2(14) NOT NULL,
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEq VARCHAR2(50) NOT NULL,
	dataInicJog DATE NOT NULL,
	dataFimJog DATE,
	salarioJog NUMBER(10,2),
	CONSTRAINT PK_JOGADOR PRIMARY KEY (cpfJogador, cnpjClube, nomeEq, dataInicJog),
	CONSTRAINT FK_JOGADOR1 FOREIGN KEY (cpfJogador) REFERENCES membro(cpfMembro)
		ON DELETE CASCADE,
	CONSTRAINT FK_JOGADOR2 FOREIGN KEY (cnpjClube,nomeEq) REFERENCES equipe(cnpjClube,nomeEq)
		ON DELETE CASCADE,
	CONSTRAINT CH_JOGADOR1 CHECK (dataFimJog > dataInicJog),
	CONSTRAINT CH_JOGADOR2 CHECK (salarioJog >= 0.0)
);


/**
 * Tabela de jogadores que participaram em uma partida
 * @cpfJogador, cnpjClube, nomeEq,		chaves primarias
	dataInicJog, idCamp, nroPartida	
 * @nroFaltas							quantidade de faltas realizadas pelo jogador na partida
 * @nroCamisa							numero da camisa utilizada na partida
 * @posicao								posicao atuada na partida
 * @golsPro								quantidade de gols a favor realizados pelo jogador na partida
 * @golsContra							quantidade de gols contra realizados pelo jogador na partida
 * @cartaoAmarelo						quantidade de cartoes amarelos tomados pelo jogador, nao deve ser maior que 2
 * @cartaoVermelho						quantidade de cartoes vermelhos tomados pelo jogador, nao deve ser maior que 1
 * @PK_PARTICIPA						restricao de chave primaria
 * @FK_PARTICIPA1						restricao de chave estrangeira com a tabela jogador, caso o jogador seja removido a tupla E tambEm removida
 * @FK_PARTICIPA2						restricao de chave estrangeira com a tabela partida, caso a partida seja removida a tupla E tambEm removida
 * @CH_PARTICIPA1,2						checagem se a quantidade de gols E positiva
 * @CH_PARTICIPA3,4						checagem se a quantidade de cartoes E valida
 */
CREATE TABLE participa(
	cpfJogador VARCHAR2(14) NOT NULL,
	cnpjClube VARCHAR2(18) NOT NULL,
	nomeEq VARCHAR2(50) NOT NULL,
	dataInicJog DATE NOT NULL,
	idCamp NUMBER(3) NOT NULL,
	nroPartida NUMBER(3) NOT NULL,
	nroFaltas NUMBER(2),
	nroCamisa NUMBER(2),
	posicao VARCHAR2(18),
	golsPro NUMBER(2) DEFAULT 0,
	golsContra NUMBER(2) DEFAULT 0,
	cartaoAmarelo NUMBER(1) DEFAULT 0,
	cartaoVermelho NUMBER(1) DEFAULT 0,
	CONSTRAINT PK_PARTICIPA PRIMARY KEY (cpfJogador, cnpjClube, nomeEq, dataInicJog, idCamp, nroPartida),
	CONSTRAINT FK_PARTICIPA1 FOREIGN KEY (cpfJogador, cnpjClube, nomeEq, dataInicJog) REFERENCES jogador(cpfJogador, cnpjClube, nomeEq, dataInicJog)
		ON DELETE CASCADE,
	CONSTRAINT FK_PARTICIPA2 FOREIGN KEY (idCamp, nroPartida) REFERENCES partida (idCamp, nroPartida)
		ON DELETE CASCADE,
	CONSTRAINT CH_PARTICIPA1 CHECK (golsPro >= 0),
	CONSTRAINT CH_PARTICIPA2 CHECK (golsContra >= 0),
	CONSTRAINT CH_PARTICIPA3 CHECK (cartaoAmarelo BETWEEN 0 and 2),
	CONSTRAINT CH_PARTICIPA4 CHECK (cartaoVermelho BETWEEN 0 and 1)
);