/* @BD_Vuelta_createBD.sql */

-- Eliminar la BD actual, si existe
@BD_Vuelta_dropBD.sql

-- Crear las tablas y aņadir algunas restricciones

CREATE TABLE Director (
  acr_Dir   VARCHAR(6)  CONSTRAINT acr_Dir_PK    PRIMARY KEY,
  nombre    VARCHAR(50) CONSTRAINT nombreDir_UN  UNIQUE
                        CONSTRAINT nombreDir_NN  NOT NULL,
  pais      VARCHAR(6)  CONSTRAINT paisDir_NN     NOT NULL
);

CREATE TABLE Equipo (
  idEquipo   VARCHAR(4)  CONSTRAINT idEquipo_PK    PRIMARY KEY,
  nombEquipo VARCHAR(40) CONSTRAINT nombEquipo_UN  UNIQUE
                         CONSTRAINT nombEquipo_NN  NOT NULL,
  pais       VARCHAR(6)  CONSTRAINT paisEquipo_NN  NOT NULL,
  director   VARCHAR(6)  CONSTRAINT directorEq_FK  REFERENCES Director(acr_Dir)
                                                   ON DELETE SET NULL
);

CREATE TABLE Patrocinio (
  idEquipo     VARCHAR(4) CONSTRAINT equipoPatr_FK  REFERENCES Equipo(idEquipo),
  patrocinador VARCHAR(40),
  CONSTRAINT patrocinio_PK PRIMARY KEY (idEquipo, patrocinador)
);

CREATE TABLE Puerto (
  idPuerto   NUMBER(2)    CONSTRAINT idPuerto_PK   PRIMARY KEY,
  nombPuerto VARCHAR(40)  CONSTRAINT nombPuerto_UN UNIQUE
                          CONSTRAINT nombPuerto_NN NOT NULL,
  categoria  NUMBER(1)    CONSTRAINT categoria_NN  NOT NULL,
  altitud    FLOAT        CONSTRAINT altitud_NN    NOT NULL
);

CREATE TABLE Etapa (
  idEtapa  NUMBER(2)   CONSTRAINT idEtapa_PK   PRIMARY KEY,    
  tpEtapa  VARCHAR(16) CONSTRAINT tpEtapa_NN   NOT NULL,
  origen   VARCHAR(40) CONSTRAINT origen_NN    NOT NULL,
  destino  VARCHAR(40) CONSTRAINT destino_NN   NOT NULL,
  fecha    DATE        CONSTRAINT fecha_NN     NOT NULL,
  kmEtapa  FLOAT       CONSTRAINT kmEtapa_NN   NOT NULL
);

CREATE TABLE Hito (
  idEtapa  NUMBER(2)    CONSTRAINT etapaHito_FK   REFERENCES Etapa(idEtapa)
                                                  ON DELETE CASCADE,
  idHito   NUMBER(2),
  tpHito   VARCHAR(6)   CONSTRAINT tipoHito_NN    NOT NULL,
  alt      NUMBER(4)    CONSTRAINT altHito_NN     NOT NULL,
  km       FLOAT        CONSTRAINT kmHito_NN      NOT NULL,
  info     VARCHAR(255),
  idPuerto NUMBER(2)    CONSTRAINT puertoHito_FK  REFERENCES Puerto(idPuerto)
                                                  ON DELETE CASCADE,
  CONSTRAINT hitoEtapa_PK  PRIMARY KEY (idHito, idEtapa)
);

CREATE TABLE Corredor (
  dorsal   NUMBER(3)   CONSTRAINT dorsal_PK      PRIMARY KEY,
  nombre   VARCHAR(40) CONSTRAINT nombreCorr_NN  NOT NULL
                       CONSTRAINT nombreCorr_UN  UNIQUE,
  pais     VARCHAR(6)  CONSTRAINT paisCorr_NN    NOT NULL,
  idEquipo VARCHAR(4)  CONSTRAINT equipoCorr_FK  REFERENCES Equipo(idEquipo)
                                                 ON DELETE CASCADE,
  etapasG  NUMBER(2)   CONSTRAINT etapasG_NN     NOT NULL
);

CREATE TABLE Llegada (
  idEtapa   NUMBER(2)   CONSTRAINT etapaLlegada_FK  REFERENCES Etapa(idEtapa)
                                                    ON DELETE CASCADE,
  ordenMeta NUMBER(3)   CONSTRAINT ordenMeta_NN     NOT NULL,
  dorsal    NUMBER(3)   CONSTRAINT dorsaletapa_FK   REFERENCES Corredor(dorsal)
                                                    ON DELETE CASCADE,
  tiempo    DATE        CONSTRAINT tiempo_NN        NOT NULL,
  CONSTRAINT Llegada_PK PRIMARY KEY (idEtapa, dorsal),
  CONSTRAINT ordenLlegada_UN UNIQUE (idEtapa, ordenMeta)
);

CREATE TABLE Sprint (
  idEtapa    NUMBER(2),
  idHito     NUMBER(2),
  dorsal     NUMBER(3)   CONSTRAINT dorsalSprint_FK  REFERENCES Corredor(dorsal)
                                                     ON DELETE CASCADE,
  ordenPaso  NUMBER(3)   CONSTRAINT ordenSprint_NN   NOT NULL,
  puntos     NUMBER(2)   CONSTRAINT puntos_Sprint_NN NOT NULL,
  CONSTRAINT hitoSprint_FK  FOREIGN KEY (idEtapa, idHito) REFERENCES Hito(idEtapa, idHito)
                                                          ON DELETE CASCADE,
  CONSTRAINT Sprint_PK      PRIMARY KEY (idEtapa, idHito, dorsal),
  CONSTRAINT ordenSprint_UN UNIQUE (idEtapa, idHito, ordenPaso)
);

CREATE TABLE Coronar (
  idEtapa    NUMBER(2),
  idHito     NUMBER(2),
  dorsal     NUMBER(3)   CONSTRAINT dorsalPuerto_FK       REFERENCES Corredor(dorsal)
                                                          ON DELETE CASCADE,
  ordenPaso  NUMBER(3)   CONSTRAINT ordenPuerto_NN        NOT NULL,
  puntos     NUMBER(2)   CONSTRAINT puntos_Puerto_NN      NOT NULL,
  CONSTRAINT hitoPuerto_FK  FOREIGN KEY (idEtapa, idHito) REFERENCES Hito(idEtapa, idHito)
                                                          ON DELETE CASCADE,
  CONSTRAINT coronarPuerto_PK      PRIMARY KEY (idEtapa, idHito, dorsal),
  CONSTRAINT ordenPuerto_UN UNIQUE (idEtapa, idHito, ordenPaso)
);

CREATE TABLE tblPt_P (
  categoria  NUMBER(1),
  ordenPaso  NUMBER(2),
  puntos     NUMBER(2)   CONSTRAINT puntos_tblPtP_NN      NOT NULL,
  info       VARCHAR(40),
  CONSTRAINT cat_orden_tblPt_P_PK PRIMARY KEY (categoria, ordenPaso)
);

CREATE TABLE tblPt_S (
  ordenPaso  NUMBER(2)   CONSTRAINT ordenPaso_tblPt_S_PK  PRIMARY KEY,
  puntos     NUMBER(2)   CONSTRAINT puntos_tblPtS_NN      NOT NULL
);

CREATE TABLE tblPt_M (
  ordenPaso  NUMBER(2)   CONSTRAINT ordenPaso_tblPt_M_PK  PRIMARY KEY,
  puntos     NUMBER(2)   CONSTRAINT puntos_tblPtM_NN      NOT NULL
);

-- crear los disparadores ideados para simplificar las consultas
@BD_Vuelta_trigger.sql