CREATE TABLE customerAccounts (

	 customerId int(11) NOT NULL AUTO_INCREMENT,
	 surename varchar(150) NOT NULL,
	 Name varchar(150) NOT NULL,
	 street varchar(200) NOT NULL,
	 zipCode varchar(20) NOT NULL,
	 birthday timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	 phoneNumber int(50) NOT NULL,
	 customerStatus varchar(50) NOT NULL DEFAULT 'default',

	 PRIMARY KEY (customerId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterModels (

	 modelId int(11) NOT NULL AUTO_INCREMENT,
	 modelName varchar(100) DEFAULT NULL,
	 modelType varchar(100) NOT NULL,
	 minimalAge int(11) NOT NULL DEFAULT 18,
	 PRIMARY KEY (modelId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooter (

	 scooterId int(11) NOT NULL AUTO_INCREMENT,
	 modelId int(11) NOT NULL,
	 mark varchar(100) NOT NULL,
	 inUse char(1) NOT NULL DEFAULT 'N',

	 PRIMARY KEY (scooterId),
	 FOREIGN KEY (modelId) REFERENCES eScooterModels(modelId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE driversLicense (

	 customerId int(11) NOT NULL,
	 vehicletype varchar(10) NOT NULL,

	 FOREIGN KEY (customerId) REFERENCES customerAccounts(customerId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterAttribute (

	 scooterId int(11) NOT NULL,
	 color varchar(50) NOT NULL,

	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterDamaged (

	 scooterId int(11) NOT NULL,
	 damageHappened timestamp NOT NULL DEFAULT current_timestamp(),
	 DamagePosition varchar(100) NOT NULL,
	 typeOfDamage varchar(150) NOT NULL,
	 comments varchar(500) NOT NULL,

	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterLocation (

	 scooterId int(11) NOT NULL,
	 zipCode varchar(20) NOT NULL,
	 coordinatesY double(6,6) NOT NULL,
	 coordinatesX double(6,6) NOT NULL,
	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterMetaData (

	 scooterId int(11) NOT NULL,
	 acquistion datetime NOT NULL,
	 drived double(7,1) DEFAULT NULL,

	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eScooterUses (

	 scooterId int(11) NOT NULL,
	 customerId int(11) NOT NULL,
	 useStart datetime NOT NULL DEFAULT current_timestamp(),
	 useEnd datetime DEFAULT NULL,
	 useStatus varchar(150) DEFAULT NULL,
	 drived double(7,1) NOT NULL,

	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId),
     FOREIGN KEY (customerId) REFERENCES customerAccounts(customerId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE prices (

	 scooterId int(11) NOT NULL,
	 priceProDay double(7,2) NOT NULL,
	 priceProWeek double(7,2) NOT NULL,

	 FOREIGN KEY (scooterId) REFERENCES eScooter(scooterId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE zipCodes (

	 zipCode varchar(20) NOT NULL,
	 city varchar(20) NOT NULL,
	 land varchar(150) NOT NULL,
	 province varchar(150) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;