USE ProjetWEB;

CREATE TABLE IF NOT EXISTS `user` (
	`id` INTEGER NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(255) NOT NULL UNIQUE,
	`email` VARCHAR(255) NOT NULL,
	`password` VARCHAR(255) NOT NULL,
	`token` VARCHAR(64) DEFAULT NULL,
    `etat` VARCHAR(255) DEFAULT NULL,
    `role` VARCHAR(255) DEFAULT NULL,
    `date_creation` TIMESTAMP NOT NULL DEFAULT now(),
	PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `url` (
	`id` INTEGER NOT NULL AUTO_INCREMENT,
    `url_raccourci` VARCHAR(255) NOT NULL UNIQUE,
    `url_destination` VARCHAR(255) NOT NULL,
    `nb_clics` INTEGER NOT NULL DEFAULT 0,
    `etat` VARCHAR(255) DEFAULT NULL,
    `date_expiration` TIMESTAMP DEFAULT NULL,
    `categorie` VARCHAR(255) DEFAULT NULL,
    `dernier_acces` TIMESTAMP DEFAULT NULL,
    `id_user` INTEGER NOT NULL,
	PRIMARY KEY(`id`),
    FOREIGN KEY (`id_user`) REFERENCES `user`(`id`) ON DELETE CASCADE
);

