DROP TABLE Clube cascade constraints purge;   -- Não apaga ??
DROP Table Equipa cascade constraints purge;
Drop Table Jogo cascade constraints purge;
Drop Table Estatistica cascade constraints purge;
Drop Table Jogador cascade constraints purge;
Drop Table ArbitroPrincipal cascade constraints purge;
Drop Table RelatorioJogo cascade constraints purge;
Drop Table Substituição cascade constraints purge;
Drop Table Convocado cascade constraints purge;
Drop Table Golo cascade constraints purge;
Drop Table Convocado_CartãoAmarelo cascade constraints purge;
Drop Table Convocado_CartãoVermelho cascade constraints purge;
Drop Table CartãoAmarelo cascade constraints purge;
Drop Table CartãoVermelho cascade constraints purge;


CREATE TABLE Clube(
clubeID numeric(10) primary key,
nomeClube VARCHAR(20) not null
);

CREATE TABLE Equipa(
equipaID numeric(10) primary key,
nomeEquipa VARCHAR(20) not null,
pais VARCHAR(20) not null,
nomeEstadio VARCHAR(20) not null,
tempoPosseBola NUMERIC(20),
numeroGolos numeric(20),
numeroFaltas NUMERIC(10),
numeroRemates NUMERIC(10),
numeroForasJogo NUMERIC(10),
numeroCantos NUMERIC(10),
clubeID numeric(10) References Clube(clubeID)
);

CREATE TABLE ArbitroPrincipal(
arbitroPrincipalID numeric(10),
nomeArbitro VARCHAR(20),
Primary Key (arbitroPrincipalID, nomeArbitro));

CREATE TABLE Jogo(
codigoIdentificação numeric(10),
dataJogo DATE, -- Date type
horaJogo number(4,2), -- Time type
FaseDaCompetição varchar(20),
equipaID numeric(10) references Equipa(equipaID),
equipaID2 numeric(10) references Equipa(equipaID),
arbitroPrincipalID numeric(10) references ArbitroPrincipal(arbitroPrincipalID),
Constraint PK_JOGO primary key(codigoIdentificação, equipaID, equipaID2)
);

CREATE TABLE Estatistica(
estatisticaID numeric(10),
equipaID numeric(10),
equipaID2 numeric(10),
codigoIdentificação numeric(10) not null,
constraint FK_Estatistica foreign key(codigoIdentificação,equipaID,equipaID2) references Jogo(codigoIdentificação,equipaID,equipaID2),
constraint PK_Estatistica primary key(estatisticaID)
);

CREATE TABLE Jogador(
jogadorID numeric(10) primary key,
equipaID numeric(10) references Equipa(equipaID),
nomeCompleto varchar(40),
nacionalidade varchar(30),
dataEntrada DATE NOT NULL,
numeroCamisola numeric(2),
numeroGolos numeric(2),
numeroJogos numeric(3),
tempoJogo numeric(3)
);

CREATE TABLE RelatorioJogo(
codigoIdentificação numeric(10) primary key,
golosA numeric(10),
golosB numeric(10),
dataJogo DATE,
horaJogo DATE,
equipaID numeric(10),
equipaID2 numeric(10),
arbitroPrincipalID numeric(10),
nomeArbitro VARCHAR(20),
constraint FK_RelatorioJogoArbitro foreign key(arbitroPrincipalID, nomeArbitro) references ArbitroPrincipal(arbitroPrincipalID, nomeArbitro),
constraint FK_RelatorioJogo foreign key(codigoIdentificação,equipaID,equipaID2) references Jogo(codigoIdentificação, equipaID, equipaID2)
);

CREATE TABLE Substituição(
substituiçãoID numeric(10) primary key,
jogadorID numeric(10) references Jogador(jogadorID),
jogadorID2 numeric(10) references Jogador(jogadorID),
codigoIdentificação numeric(10) references RelatorioJogo(codigoIdentificação)
);

CREATE TABLE Convocado(
jogadorID numeric(10) references Jogador(jogadorID),
equipaID numeric(10),
equipaID2 numeric(10),
codigoIdentificação numeric(10),
constraint FK_Convocado foreign key(jogadorID, equipaID, equipaID2) references Jogo(codigoIdentificação, equipaID, equipaID2),
constraint PK_Convocado primary key(jogadorID, codigoIdentificação)
);

CREATE TABLE Golo(
goloID numeric(10),
jogadorID numeric(10) references Jogador(jogadorID),
equipaID numeric(10),
equipaID2 numeric(10),
codigoIdentificação numeric(10),
constraint FK_Golo foreign key(codigoIdentificação,equipaID, equipaID2) references Jogo(codigoIdentificação, equipaID, equipaID2),
constraint PK_Golo Primary Key(goloID,jogadorID,codigoIdentificação)
);


CREATE TABLE CartãoAmarelo(
cartãoAmareloID numeric(10),
Primary Key (cartãoAmareloID)
);

CREATE TABLE CartãoVermelho(
cartãoVermelhoID numeric(10),
Primary Key (cartãoVermelhoID)
);

CREATE TABLE Convocado_CartãoAmarelo(
jogadorID numeric(10) references Jogador(jogadorID),
equipaID numeric(10),
equipaID2 numeric(10),
codigoIdentificação numeric(10),
constraint FK_Convocado_CartãoAmarelo foreign key(codigoIdentificação, equipaID, equipaID2) references Jogo(codigoIdentificação, equipaID, equipaID2),
cartãoAmareloID numeric(10) references CartãoAmarelo(cartãoAmareloID),
constraint PK_Convocado_CartãoAmarelo primary key(jogadorID, codigoIdentificação, cartãoAmareloID)
);

CREATE TABLE Convocado_CartãoVermelho(
jogadorID numeric(10) references Jogador(jogadorID),
equipaID numeric(10),
equipaID2 numeric(10),
codigoIdentificação numeric(10),
constraint FK_Convocado_CartãoVermelho foreign key(codigoIdentificação, equipaID, equipaID2) references Jogo(codigoIdentificação, equipaID, equipaID2),
cartãoVermelhoID numeric(10) references CartãoVermelho(cartãoVermelhoID),
constraint PK_Convocado_CartãoVermelho primary key(jogadorID, codigoIdentificação, cartãoVermelhoID)
);

INSERT INTO Clube VALUES(001, 'FCP');
INSERT INTO Clube VALUES(002, 'SLB');
INSERT INTO Clube VALUES(003, 'SCB');
INSERT INTO Clube VALUES(004, 'RMF');
INSERT INTO Clube VALUES(005, 'BFC');
INSERT INTO Clube VALUES(006, 'CGD');
INSERT INTO Equipa(equipaID, nomeEquipa, pais, nomeEstadio, tempoPosseBola, numeroFaltas, numeroRemates, numeroForasJogo, numeroCantos, clubeID)
VALUES (001, 'A','Portugal', 'Dragão', 144, 5, 10, 3, 7,001);
INSERT INTO Equipa(equipaID, nomeEquipa, pais, nomeEstadio, tempoPosseBola, numeroFaltas, numeroRemates, numeroForasJogo, numeroCantos, clubeID)
VALUES (002, 'B','Portugal', 'Luz', 103, 5, 10, 3, 7,002);
INSERT INTO Equipa(equipaID, nomeEquipa, pais, nomeEstadio, tempoPosseBola, numeroFaltas, numeroRemates, numeroForasJogo, numeroCantos, clubeID)
VALUES (003, 'C','Portugal', 'Alvalade', 100, 5, 10, 3, 7,003);
INSERT INTO  Equipa(equipaID, nomeEquipa, pais, nomeEstadio, tempoPosseBola, numeroFaltas, numeroRemates, numeroForasJogo, numeroCantos, clubeID)
VALUES (004, 'D','Espanha', 'Madrid', 160, 5, 10, 3, 7,004);


INSERT INTO Estatistica VALUES (1);
INSERT INTO Estatistica VALUES (2);
INSERT INTO Estatistica VALUES (3);
INSERT INTO Jogo(codigoIdentificação, dataJogo, horaJogo, FaseDaCompetição, equipaID, equipaID2, arbitroPrincipalID)
VALUES (145, to_date('15102004','DDMMYYYY'), 10.00, 'Oitavos-Final',001,002,001);
INSERT INTO Jogo(codigoIdentificação, dataJogo, horaJogo, FaseDaCompetição, equipaID, equipaID2, arbitroPrincipalID)
VALUES (146, to_date('16102004','DDMMYYYY'), 10.00, 'Oitavos-Final',002,003,002);
INSERT INTO Jogo(codigoIdentificação, dataJogo, horaJogo, FaseDaCompetição, equipaID, equipaID2, arbitroPrincipalID)
VALUES (147, to_date('17102004','DDMMYYYY'), 10.00, 'Oitavos-Final',003,004,003);
INSERT INTO Jogo(codigoIdentificação, dataJogo, horaJogo, FaseDaCompetição, equipaID, equipaID2, arbitroPrincipalID)
VALUES (148, to_date('18102004','DDMMYYYY'), 10.00, 'Oitavos-Final',001,003,004);
INSERT INTO Jogo VALUES (001, 14-08-2004, '10:00', "Grupos");
INSERT INTO Jogo VALUES (002, 15-10-2004, '10:00', "Grupos");
INSERT INTO Jogo VALUES (002, 15-10-2004, '10:00', "Grupos");
INSERT INTO ArbitroPrincipal VALUES (1);
INSERT INTO ArbitroPrincipal VALUES (2);
INSERT INTO ArbitroPrincipal VALUES (3);
INSERT INTO ArbitroPrincipal VALUES (4);
INSERT INTO Golo VALUES (3);
INSERT INTO Golo VALUES (2);
INSERT INTO Golo VALUES (1);
-- Jogadores do Benfica
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (148, 002 ,'Nuno Freire', 'Portuguesa', to_date('15102004','DDMMYYYY'), 18, 50, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (140, 002 ,'Carlos Parreira', 'Portuguesa', to_date('15102004','DDMMYYYY'), 10, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (141, 002 ,'Eusébio', 'Portuguesa', to_date('15102004','DDMMYYYY'), 11, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (142, 002 ,'Rui Costa', 'Portuguesa', to_date('15102004','DDMMYYYY'), 12, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (143, 002 ,'Rui Sousa', 'Portuguesa', to_date('15102004','DDMMYYYY'), 13, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (144, 002 ,'Pedro Jorge', 'Portuguesa', to_date('15102004','DDMMYYYY'), 14, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (145, 002 ,'Rui Silva', 'Portuguesa', to_date('15102004','DDMMYYYY'), 15, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (146, 002 ,'Mantorras', 'Portuguesa', to_date('15102004','DDMMYYYY'), 16, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (147, 002 ,'Paulo Jorge', 'Portuguesa', to_date('15102004','DDMMYYYY'), 17, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (149, 002 ,'Paulo Sousa', 'Portuguesa', to_date('15102004','DDMMYYYY'), 19, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (150, 002 ,'Luisão', 'Portuguesa', to_date('15102004','DDMMYYYY'), 20, 3, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (151, 002 ,'Nuno Gomes', 'Portuguesa', to_date('15102004','DDMMYYYY'), 21, 3, 101);
-- Jogadores do Porto
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (152, 001 ,'Bruno Alves', 'Portuguesa', to_date('15102004','DDMMYYYY'), 21, 10, 101);
INSERT INTO Jogador(jogadorID, equipaID, nomeCompleto, nacionalidade, dataEntrada, numeroCamisola, numeroGolos, numeroJogos)
VALUES (153, 001 ,'Deco', 'Portuguesa', to_date('15102004','DDMMYYYY'), 21, 30, 101);

INSERT INTO Jogador VALUES (123, "Quaresma", "Portuguesa", 5-04-2005, 7, 3, 96);
INSERT INTO Jogador VALUES (001, "Roger Phelps", "Inglesa", 5-05-2008, 21, 3, 46);
INSERT INTO Jogador VALUES (002, "Tom Cruise", "Americana", 8-08-2010, 4, 3, 75);
INSERT INTO Jogador VALUES (003, "Jared Padalecki", "Polaca", 5-10-2011, 19, 3, 95);
INSERT INTO Jogador VALUES (004, "Ivan Metrovich", "Russa", 8-02-2014, 1, 3, 98);
INSERT INTO Substituicao VALUES (35);
INSERT INTO Substituicao VALUES (23);
INSERT INTO Substituicao VALUES (01);
INSERT INTO Substituicao VALUES (02);
INSERT INTO Substituicao VALUES (03);
INSERT INTO Substituicao VALUES (45);
INSERT INTO CartaoAmarelo VALUES (75);
INSERT INTO CartaoAmarelo VALUES (01);
INSERT INTO CartaoAmarelo VALUES (02);
INSERT INTO CartaoAmarelo VALUES (03);
INSERT INTO CartaoAmarelo VALUES (04);
INSERT INTO CartaoVermelho VALUES (05);
INSERT INTO CartaoVermelho VALUES (01);
INSERT INTO CartaoVermelho VALUES (02);
INSERT INTO CartaoVermelho VALUES (03);
INSERT INTO RelatorioJogo VALUES (4-5, 14-5-2004, '16:00');
INSERT INTO RelatorioJogo VALUES (0-0, 21-5-2004, '20:00');
INSERT INTO RelatorioJogo VALUES (1-2, 30-5-2004, '17:00');
INSERT INTO RelatorioJogo VALUES (3-1, 10-5-2004, '16:00');
INSERT INTO RelatorioJogo VALUES (2-0, 10-5-2004, '18:00');
INSERT INTO RelatorioJogo VALUES (2-2, 11-5-2004, '21:00');
INSERT INTO RelatorioJogo VALUES (1-2, 14-5-2004, '16:00');


----||PROCEDURE QUE RECEBE COMO PARAMETROS (NOME ARBITRO, JOGO, DATA REALIZAVCAO JOGO E REGISTO RELATORIO JOGO)||--
DROP SEQUENCE Recebeparametros;

CREATE OR REPLACE PROCEDURE Recebeparametros(
p_nomeArbitro ARBITROPRINCIPAL.NOME%TYPE,
p_jogo JOGO.CODIGOIDENTIFICACAO%TYPE,
p_dataJogo JOGO.DATAJOGO%TYPE)
IS
BEGIN
INSERT INTO RELATORIOJOGO (códigoIdentificação,golosA, golosB, dataJogo, horasJogo, equipaID, equipaID2, arbitroPrincipalID, arbitronome)
values (p_jogo, null, null, p_dataJogo, null, null, null,null, p_nomeArbitro);
END RecebeParametros;

/*
--a)
SELECT DISTINCT J.NOMECOMPLETO
FROM EQUIPA E, JOGADOR J, JOGO JG, CLUBE C, CONVOCADO CV
WHERE E.EQUIPAID=JG.EQUIPAID
      AND C.CLUBEID=E.CLUBEID
      AND J.JOGADORID=CV.JOGADORID
      AND JG.CODIGOIDENTIFICACAO=CV.CODIGOIDENTIFICACAO;

--b)
SELECT AVG(E.TEMPOPOSSEBOLA)
FROM EQUIPA E, JOGO JG, CLUBE C
WHERE E.EQUIPAID=JG.EQUIPAID
    AND C.CLUBEID=E.CLUBEID
    AND C.NOMECLUBE='RMF';

--c)


SELECT DISTINCT J.NOMECOMPLETO
FROM EQUIPA E, CLUBE C, JOGADOR J, CONVOCADO CV
WHERE J.JOGADORID=CV.JOGADORID
      AND E.JOGADORID=J.JOGADORID
      AND C.CLUBEID=E.CLUBEID
      AND J.NUMEROGOLOS  > (SELECT MAX(J.NUMEROGOLOS)
                            FROM EQUIPA E, CLUBE C, JOGADOR J, CONVOCADO CV
                            AND J.JOGADOR ID=CV.JOGADORID
                            AND C.CLUBEID=E.CLUBEID
                            AND C.CLUBEID='Benfica');

--d)

Select J.NOME
FROM JOGADOR J
WHERE J.TEMPOJOGO > (Select MIN(J.TempoJogo)
                     From JOGADOR J, equipa E, JOGO JO, RELATORIOJOGO RJ
                     WHERE J.EQUIPAID = E.equipaID
                           AND (E.EQUIPAID = JO.EQUIPAID AND RJ.GOLOSA > RJ.GOLOSB OR E.EQUIPAID = JO.EQUIPAID2 AND RJ.GOLOSB>RJ.GOLOSA)
                           AND JO."FASEDACOMPETIÇÃO"='Final'
                           AND JO."CODIGOIDENTIFICAÇÃO"=RJ."CODIGOIDENTIFICAÇÃO"
                     );

--e)
SELECT J.NOMECOMPLETO
FROM JOGADOR J, EQUIPA E
WHERE E.NUMEROGOLOS = (SELECT MAX(E.NUMEROGOLOS)
                       FROM EQUIPA E
                       WHERE E.EQUIPAID=E.EQUIPAID);

-- f)
SELECT DISTINCT C.NOMECLUBE
FROM CLUBE C, EQUIPA E
WHERE C.CLUBEID==E.CLUBEID
      AND E.NUMEROREMATES= (SELECT MAX(AVG(E.NUMEROREMATES))
                            FROM CLUBE C, EQUIPA E
                            WHERE C.CLUBEID=E.CLUBEID);
*/



CREATE OR REPLACE PACKAGE pkgLC AS
  TYPE ref_cursor IS REF CURSOR;
END pkgLC;

CREATE OR REPLACE PROCEDURE addRelatorioJogo(nomeArbitro VARCHAR, codigoIdentificação NUMBER, dataJogo DATE)
AS
BEGIN
  INSERT INTO RELATORIOJOGO (codigoIdentificação,golosA, golosB, dataJogo, horaJogo, equipaID, equipaID2, arbitroPrincipalID, nomearbitro)
  VALUES(codigoIdentificação, null, null, dataJogo, '', null, null, null, nomeArbitro);
END;