65 lines
No EOL
2.8 KiB
SQL
65 lines
No EOL
2.8 KiB
SQL
CREATE TABLE `adminNotifications` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`userId` int,
|
|
`type` enum('nouvelle_demande','demande_en_retard','dossier_incomplet','nouvelle_association','systeme') NOT NULL,
|
|
`titre` varchar(255) NOT NULL,
|
|
`message` text NOT NULL,
|
|
`lien` varchar(512),
|
|
`lu` boolean DEFAULT false,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `adminNotifications_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `auditLog` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`userId` int NOT NULL,
|
|
`action` varchar(100) NOT NULL,
|
|
`entityType` varchar(50) NOT NULL,
|
|
`entityId` int,
|
|
`details` text,
|
|
`ipAddress` varchar(45),
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `auditLog_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `portalSettings` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`cle` varchar(100) NOT NULL,
|
|
`valeur` text NOT NULL,
|
|
`description` text,
|
|
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `portalSettings_id` PRIMARY KEY(`id`),
|
|
CONSTRAINT `portalSettings_cle_unique` UNIQUE(`cle`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `requestHistory` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`requestId` int NOT NULL,
|
|
`action` enum('creation','soumission','assignation','changement_statut','ajout_commentaire','modification','validation','refus') NOT NULL,
|
|
`ancienStatut` enum('brouillon','soumise','en_cours_traitement','information_complementaire','validee','refusee'),
|
|
`nouveauStatut` enum('brouillon','soumise','en_cours_traitement','information_complementaire','validee','refusee'),
|
|
`commentaire` text,
|
|
`userId` int NOT NULL,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `requestHistory_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `responseTemplates` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`nom` varchar(255) NOT NULL,
|
|
`type` enum('validation','refus','information_complementaire','autre') NOT NULL,
|
|
`sujet` varchar(255),
|
|
`contenu` text NOT NULL,
|
|
`actif` boolean DEFAULT true,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `responseTemplates_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE `users` MODIFY COLUMN `role` enum('user','admin','super_admin') NOT NULL DEFAULT 'user';--> statement-breakpoint
|
|
ALTER TABLE `associations` ADD `isActive` boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE `requestTemplates` ADD `delaiTraitementJours` int DEFAULT 30;--> statement-breakpoint
|
|
ALTER TABLE `requests` ADD `priority` enum('basse','normale','haute','urgente') DEFAULT 'normale' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE `requests` ADD `dateLimiteTraitement` timestamp;--> statement-breakpoint
|
|
ALTER TABLE `requests` ADD `assigneA` int;--> statement-breakpoint
|
|
ALTER TABLE `users` ADD `isActive` boolean DEFAULT true NOT NULL; |