CREATE TABLE `blocked_slots` (
	`id` int AUTO_INCREMENT NOT NULL,
	`roomId` int NOT NULL,
	`startDate` date NOT NULL,
	`endDate` date NOT NULL,
	`reason` varchar(200),
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `blocked_slots_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `clients` (
	`id` int AUTO_INCREMENT NOT NULL,
	`firstName` varchar(100) NOT NULL,
	`lastName` varchar(100) NOT NULL,
	`email` varchar(320) NOT NULL,
	`phone` varchar(30),
	`nationality` varchar(50),
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `clients_id` PRIMARY KEY(`id`),
	CONSTRAINT `clients_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE TABLE `contact_messages` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(200) NOT NULL,
	`email` varchar(320) NOT NULL,
	`phone` varchar(30),
	`subject` varchar(200),
	`message` text NOT NULL,
	`isRead` boolean NOT NULL DEFAULT false,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `contact_messages_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `reservations` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reservationCode` varchar(20) NOT NULL,
	`roomId` int NOT NULL,
	`clientId` int,
	`guestFirstName` varchar(100) NOT NULL,
	`guestLastName` varchar(100) NOT NULL,
	`guestEmail` varchar(320) NOT NULL,
	`guestPhone` varchar(30),
	`guestNationality` varchar(50),
	`checkIn` date NOT NULL,
	`checkOut` date NOT NULL,
	`adults` int NOT NULL DEFAULT 1,
	`children` int NOT NULL DEFAULT 0,
	`totalPrice` decimal(10,2) NOT NULL,
	`status` enum('pending','confirmed','cancelled','completed') NOT NULL DEFAULT 'pending',
	`source` enum('direct','booking','airbnb','other') NOT NULL DEFAULT 'direct',
	`notes` text,
	`specialRequests` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `reservations_id` PRIMARY KEY(`id`),
	CONSTRAINT `reservations_reservationCode_unique` UNIQUE(`reservationCode`),
	CONSTRAINT `unique_room_dates` UNIQUE(`roomId`,`checkIn`)
);
--> statement-breakpoint
CREATE TABLE `reviews` (
	`id` int AUTO_INCREMENT NOT NULL,
	`authorName` varchar(100) NOT NULL,
	`authorNationality` varchar(50),
	`rating` int NOT NULL,
	`comment` text NOT NULL,
	`commentFr` text,
	`commentEn` text,
	`commentDe` text,
	`source` varchar(50) DEFAULT 'booking',
	`stayDate` varchar(20),
	`isVisible` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `reviews_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `rooms` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`type` enum('doppia','tripla','quadrupla') NOT NULL,
	`capacity` int NOT NULL,
	`pricePerNight` decimal(10,2) NOT NULL,
	`description` text,
	`descriptionEn` text,
	`descriptionFr` text,
	`descriptionDe` text,
	`amenities` text,
	`photos` text,
	`isActive` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `rooms_id` PRIMARY KEY(`id`)
);
