#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------


#------------------------------------------------------------
# Table: transportation
#------------------------------------------------------------

CREATE TABLE transportation(
        idtransport   Int  Auto_increment  NOT NULL ,
        transporttype Varchar (20) NOT NULL
	,CONSTRAINT transportation_PK PRIMARY KEY (idtransport)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: client
#------------------------------------------------------------

CREATE TABLE client(
        idclient           Int  Auto_increment  NOT NULL ,
        nameclient         Varchar (90) NOT NULL ,
        surnameclient      Varchar (90) NOT NULL ,
        emailclient        Varchar (120) NOT NULL ,
        phonenumberclient1 Varchar (10) NOT NULL ,
        phonenumberclient2 Varchar (10) NOT NULL ,
        gender             Varchar (1) NOT NULL ,
        birthdateclient    Date NOT NULL
	,CONSTRAINT client_PK PRIMARY KEY (idclient)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: employees
#------------------------------------------------------------

CREATE TABLE employees(
        idemployees           Int  Auto_increment  NOT NULL ,
        nameemployees         Varchar (90) NOT NULL ,
        surnameemployees      Varchar (90) NOT NULL ,
        emailemployees        Varchar (120) NOT NULL ,
        phonenumberemployees1 Varchar (10) NOT NULL ,
        phonenumberemployees2 Varchar (10) NOT NULL ,
        hiringdate            Date NOT NULL
	,CONSTRAINT employees_PK PRIMARY KEY (idemployees)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: adress
#------------------------------------------------------------

CREATE TABLE adress(
        idadress                Int  Auto_increment  NOT NULL ,
        number                  Varchar (10) NOT NULL ,
        city                    Varchar (120) NOT NULL ,
        streetname              Varchar (120) NOT NULL ,
        residencename           Varchar (100) NOT NULL ,
        buildingname            Varchar (100) NOT NULL ,
        floor                   Varchar (10) NOT NULL ,
        postalcode              Varchar (5) NOT NULL ,
        idemployees             Int NOT NULL ,
        idclientbilling       Int NOT NULL ,
        idclientdelivery     Int NOT NULL
	,CONSTRAINT adress_PK PRIMARY KEY (idadress)

	,CONSTRAINT adress_employees_FK FOREIGN KEY (idemployees) REFERENCES employees(idemployees)
	,CONSTRAINT adress_client0_FK FOREIGN KEY (idclientbilling) REFERENCES client(idclient)
	,CONSTRAINT adress_client1_FK FOREIGN KEY (idclientdelivery) REFERENCES client(idclient)
	,CONSTRAINT adress_employees_AK UNIQUE (idemployees)
	,CONSTRAINT adress_client0_AK UNIQUE (idclientbilling)
	,CONSTRAINT adress_client1_AK UNIQUE (idclientdelivery)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: othertraveler
#------------------------------------------------------------

CREATE TABLE othertraveler(
        idtraveler        Int  Auto_increment  NOT NULL ,
        birthdatetraveler Date NOT NULL ,
        nametraveler      Varchar (90) NOT NULL ,
        surnametraveler   Varchar (90) NOT NULL ,
        gendertraveler    Varchar (1) NOT NULL ,
        idclient          Int NOT NULL
	,CONSTRAINT othertraveler_PK PRIMARY KEY (idtraveler)

	,CONSTRAINT othertraveler_client_FK FOREIGN KEY (idclient) REFERENCES client(idclient)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: paymentmethod
#------------------------------------------------------------

CREATE TABLE paymentmethod(
        idmethod      Int  Auto_increment  NOT NULL ,
        paymentmethod Varchar (20) NOT NULL
	,CONSTRAINT paymentmethod_PK PRIMARY KEY (idmethod)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: city
#------------------------------------------------------------

CREATE TABLE city(
        idcity Int  Auto_increment  NOT NULL ,
        city   Varchar (120) NOT NULL
	,CONSTRAINT city_PK PRIMARY KEY (idcity)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: cataloguecity
#------------------------------------------------------------

CREATE TABLE cataloguecity(
        idcataloguecity Int  Auto_increment  NOT NULL ,
        expirationdate  Date NOT NULL ,
        idcity          Int NOT NULL
	,CONSTRAINT cataloguecity_PK PRIMARY KEY (idcataloguecity)

	,CONSTRAINT cataloguecity_city_FK FOREIGN KEY (idcity) REFERENCES city(idcity)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: travel
#------------------------------------------------------------

CREATE TABLE travel(
        idtravel                Int  Auto_increment  NOT NULL ,
        departuredate           Datetime NOT NULL ,
        arrivaldate             Datetime NOT NULL ,
        idemployees             Int NOT NULL ,
        idclient                Int NOT NULL ,
        idcataloguecitydeparture  Int NOT NULL ,
        idcataloguecityarrival Int NOT NULL
	,CONSTRAINT travel_PK PRIMARY KEY (idtravel)

	,CONSTRAINT travel_employees_FK FOREIGN KEY (idemployees) REFERENCES employees(idemployees)
	,CONSTRAINT travel_client0_FK FOREIGN KEY (idclient) REFERENCES client(idclient)
	,CONSTRAINT travel_cataloguecity1_FK FOREIGN KEY (idcataloguecitydeparture) REFERENCES cataloguecity(idcataloguecity)
	,CONSTRAINT travel_cataloguecity2_FK FOREIGN KEY (idcataloguecityarrival) REFERENCES cataloguecity(idcataloguecity)
)ENGINE=InnoDB;

#------------------------------------------------------------
# Table: payment
#------------------------------------------------------------

CREATE TABLE payment(
        idpayment     Int  Auto_increment  NOT NULL ,
        travelprice   DECIMAL (15,3)  NOT NULL ,
        paymentdate   Date NOT NULL ,
        paymentamount DECIMAL (15,3)  NOT NULL ,
        dateorder     Date NOT NULL ,
        idtravel      Int NOT NULL ,
        idmethod      Int NOT NULL
	,CONSTRAINT payment_PK PRIMARY KEY (idpayment)

	,CONSTRAINT payment_travel_FK FOREIGN KEY (idtravel) REFERENCES travel(idtravel)
	,CONSTRAINT payment_paymentmethod0_FK FOREIGN KEY (idmethod) REFERENCES paymentmethod(idmethod)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: distance
#------------------------------------------------------------

CREATE TABLE distance(
        iddistance         Int  Auto_increment  NOT NULL ,
        distance           Float NOT NULL ,
        idcitystart             Int NOT NULL ,
        idcityend Int NOT NULL
	,CONSTRAINT distance_PK PRIMARY KEY (iddistance)

	,CONSTRAINT distance_city_FK FOREIGN KEY (idcitystart) REFERENCES city(idcity)
	,CONSTRAINT distance_city0_FK FOREIGN KEY (idcityend) REFERENCES city(idcity)
)ENGINE=InnoDB;

#------------------------------------------------------------
# Table: stage
#------------------------------------------------------------
CREATE TABLE stage(
        idstage                  Int  Auto_increment  NOT NULL ,
        idtravel                 Int NOT NULL ,
        idtransport              Int NOT NULL ,
        idcataloguecitystartstage          Int NOT NULL ,
        idcataloguecityendstage Int NOT NULL ,
        iddistance               Int NOT NULL
	,CONSTRAINT stage_PK PRIMARY KEY (idstage)

	,CONSTRAINT stage_travel_FK FOREIGN KEY (idtravel) REFERENCES travel(idtravel)
	,CONSTRAINT stage_transportation0_FK FOREIGN KEY (idtransport) REFERENCES transportation(idtransport)
	,CONSTRAINT stage_cataloguecity1_FK FOREIGN KEY (idcataloguecitystartstage) REFERENCES cataloguecity(idcataloguecity)
	,CONSTRAINT stage_cataloguecity2_FK FOREIGN KEY (idcataloguecityendstage) REFERENCES cataloguecity(idcataloguecity)
	,CONSTRAINT stage_distance3_FK FOREIGN KEY (iddistance) REFERENCES distance(iddistance)
)ENGINE=InnoDB;