DROP DATABASE IF EXISTS rapports;

CREATE DATABASE IF NOT EXISTS rapports;

CREATE TABLE rapport (id INT UNSIGNED NOT NULL AUTO_INCREMENT,installation INT UNSIGNED NOT NULL,interdepannage INT UNSIGNED NOT NULL,visite INT UNSIGNED NOT NULL,recuperation INT UNSIGNED NOT NULL,autre INT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,id_user INT UNSIGNED NOT NULL,PRIMARY KEY (id));

CREATE TABLE user (id INT UNSIGNED NOT NULL AUTO_INCREMENT,first_name VARCHAR(255) NOT NULL,last_name VARCHAR(255) NOT NULL,username VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL,phone VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,role VARCHAR(255) NOT NULL,PRIMARY KEY (id));

INSERT INTO user (first_name, last_name, username, email, phone, password, role) VALUES('Bob', 'Smith', 'bobsmith', 'bobsmith@email.com', '555-2468', 'password', 'user');

INSERT INTO user (first_name, last_name, username, email, phone, password, role) VALUES('Jane', 'Doe', 'janedoe', 'janedoe@email.com', '555-5678', 'password', 'admin');

INSERT INTO rapport (installation, interdepannage, visite, recuperation, autre, created_at, id_user) VALUES(3, 1, 2, 0, 2, '2022-02-01 10:00:00', 2);

INSERT INTO rapport (installation, interdepannage, visite, recuperation, autre, created_at, id_user) VALUES (10, 2, 5, 3, 1, NOW(), 1);


CREATE TABLE `rapports`.`role` (`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(255) NOT NULL,PRIMARY KEY (`id`));