CREATE DATABASE IF NOT EXISTS DiamPark;

USE DiamPark;

CREATE TABLE IF NOT EXISTS user (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	pseudo VARCHAR(255) NOT NULL,
	password VARCHAR(255) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	UNIQUE KEY pseudo (pseudo) USING HASH,
	PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS DiamPark_Appli_Android (
	id INT NOT NULL AUTO_INCREMENT,
	Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	HashCodeUser TINYTEXT NOT NULL,
	Modalite TINYTEXT NOT NULL,
	JSON_resultats TEXT NOT NULL,
	Fichier_de_sortie MEDIUMTEXT,
	JSON_levo TEXT,
	traitement_offline_a_faire TINYINT DEFAULT 1,
	localJitter FLOAT,
	localabsoluteJitter FLOAT,
	rapJitter FLOAT,
	ppq5Jitter FLOAT,
	localShimmer FLOAT,
	localdbShimmer FLOAT,
	apq3Shimmer FLOAT,
	apq5Shimmer FLOAT,
	apq11Shimmer FLOAT,
	hnr38 FLOAT,
	user_id INT UNSIGNED NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (user_id) REFERENCES user(id)
);