CREATE TABLE `etablissements` (
	`id` int AUTO_INCREMENT NOT NULL,
	`nom` varchar(255) NOT NULL,
	`description` text,
	`logoUrl` text,
	`actif` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `etablissements_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mots_cles` (
	`id` int AUTO_INCREMENT NOT NULL,
	`label` varchar(100) NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `mots_cles_id` PRIMARY KEY(`id`),
	CONSTRAINT `mots_cles_label_unique` UNIQUE(`label`)
);
--> statement-breakpoint
CREATE TABLE `podcast_mots_cles` (
	`podcastId` int NOT NULL,
	`motCleId` int NOT NULL,
	CONSTRAINT `podcast_mots_cles_podcastId_motCleId_pk` PRIMARY KEY(`podcastId`,`motCleId`)
);
--> statement-breakpoint
CREATE TABLE `podcasts` (
	`id` int AUTO_INCREMENT NOT NULL,
	`titre` varchar(255) NOT NULL,
	`resume` text NOT NULL,
	`etablissementId` int NOT NULL,
	`audioUrl` text,
	`audioKey` text,
	`dureeSecondes` int,
	`statut` enum('brouillon','publie') NOT NULL DEFAULT 'brouillon',
	`auteurId` int,
	`imageUrl` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `podcasts_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
ALTER TABLE `podcast_mots_cles` ADD CONSTRAINT `podcast_mots_cles_podcastId_podcasts_id_fk` FOREIGN KEY (`podcastId`) REFERENCES `podcasts`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `podcast_mots_cles` ADD CONSTRAINT `podcast_mots_cles_motCleId_mots_cles_id_fk` FOREIGN KEY (`motCleId`) REFERENCES `mots_cles`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `podcasts` ADD CONSTRAINT `podcasts_etablissementId_etablissements_id_fk` FOREIGN KEY (`etablissementId`) REFERENCES `etablissements`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `podcasts` ADD CONSTRAINT `podcasts_auteurId_users_id_fk` FOREIGN KEY (`auteurId`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;