SET NAMES utf8;

DROP DATABASE IF EXISTS gpsteal;
CREATE DATABASE gpsteal;
USE gpsteal;

DROP TABLE IF EXISTS info_client;

CREATE TABLE info_client (
	id_client INT UNSIGNED NOT NULL AUTO_INCREMENT,
	email VARCHAR(60) NOT NULL,
	nom VARCHAR(40) NOT NULL,
	prenom VARCHAR(40) NOT NULL,
	telephone VARCHAR(30) NOT NULL,
	rue VARCHAR(100) NOT NULL,
	pays VARCHAR(30) NOT NULL,
	ville VARCHAR(50) NOT NULL,
	code_postal VARCHAR(20) NOT NULL,
	mp VARCHAR(100) NOT NULL,
	PRIMARY KEY (id_client),
	UNIQUE INDEX ind_email (email),
	INDEX ind_nom_prenom (nom, prenom)
) ENGINE=InnoDB DEFAULT charset=utf8;

DROP TABLE IF EXISTS info_voiture;

CREATE TABLE info_voiture (
	immatriculation VARCHAR(15) NOT NULL,
	id_client INT UNSIGNED NOT NULL,
	marque VARCHAR(30) NOT NULL,
	nom_voiture VARCHAR(30) NOT NULL,
	couleur VARCHAR(30) NOT NULL,
	statut TINYINT NOT NULL,
	dernier_emplacement VARCHAR(70),
	date_dernier_emplacement DATETIME,
	date_vol DATETIME,
	latitude FLOAT,
	longitude FLOAT,
	commentaire TEXT,
	PRIMARY KEY (immatriculation),
	INDEX ind_marque_nom (marque, nom_voiture)
) ENGINE=InnoDB DEFAULT charset=utf8;
