-- ============================================
-- SCHEMA BASE DE DONNÉES - SYSTÈME DE RÉSERVATION
-- AutoDetailing - PHP/MySQL
-- ============================================


-- Base de données
CREATE DATABASE IF NOT EXISTS `resa_detailing` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `resa_detailing`;

-- ============================================
-- TABLE: businesses (Entreprises)
-- ============================================
CREATE TABLE `businesses` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `logo` VARCHAR(500) DEFAULT NULL,
  `banner` VARCHAR(500) DEFAULT NULL,
  `favicon` VARCHAR(500) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `address_street` VARCHAR(255) DEFAULT NULL,
  `address_city` VARCHAR(255) DEFAULT NULL,
  `address_postal_code` VARCHAR(50) DEFAULT NULL,
  `address_country` VARCHAR(100) DEFAULT 'France',
  `currency` VARCHAR(10) DEFAULT 'EUR',
  `website` VARCHAR(500) DEFAULT NULL,
  `social_facebook` VARCHAR(500) DEFAULT NULL,
  `social_instagram` VARCHAR(500) DEFAULT NULL,
  `social_tiktok` VARCHAR(500) DEFAULT NULL,
  `main_color` VARCHAR(50) DEFAULT '#0ea5e9',
  `page_title` VARCHAR(255) DEFAULT NULL,
  `page_description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: vehicle_types (Types de véhicules)
-- ============================================
CREATE TABLE `vehicle_types` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `display_order` INT(11) DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_business` (`business_id`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: packs (Packs de nettoyage)
-- ============================================
CREATE TABLE `packs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `display_order` INT(11) DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_business` (`business_id`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: pack_pricing (Tarifs des packs par véhicule)
-- ============================================
CREATE TABLE `pack_pricing` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `pack_id` INT(11) NOT NULL,
  `vehicle_type_id` INT(11) NOT NULL,
  `price_ht` DECIMAL(10,2) NOT NULL,
  `price_ttc` DECIMAL(10,2) NOT NULL,
  `duration` DECIMAL(4,2) NOT NULL COMMENT 'Durée en heures',
  `available` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_pack_vehicle` (`pack_id`, `vehicle_type_id`),
  KEY `idx_pack` (`pack_id`),
  KEY `idx_vehicle` (`vehicle_type_id`),
  FOREIGN KEY (`pack_id`) REFERENCES `packs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vehicle_type_id`) REFERENCES `vehicle_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: options (Options supplémentaires)
-- ============================================
CREATE TABLE `options` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `icon` VARCHAR(100) DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `display_order` INT(11) DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_business` (`business_id`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: option_pricing (Tarifs des options par véhicule)
-- ============================================
CREATE TABLE `option_pricing` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `option_id` INT(11) NOT NULL,
  `vehicle_type_id` INT(11) NOT NULL,
  `price_ht` DECIMAL(10,2) NOT NULL,
  `price_ttc` DECIMAL(10,2) NOT NULL,
  `duration` DECIMAL(4,2) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_option_vehicle` (`option_id`, `vehicle_type_id`),
  KEY `idx_option` (`option_id`),
  KEY `idx_vehicle` (`vehicle_type_id`),
  FOREIGN KEY (`option_id`) REFERENCES `options`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vehicle_type_id`) REFERENCES `vehicle_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: option_availability (Disponibilité options par véhicule)
-- ============================================
CREATE TABLE `option_availability` (
  `option_id` INT(11) NOT NULL,
  `vehicle_type_id` INT(11) NOT NULL,
  PRIMARY KEY (`option_id`, `vehicle_type_id`),
  FOREIGN KEY (`option_id`) REFERENCES `options`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vehicle_type_id`) REFERENCES `vehicle_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: working_hours (Horaires d'ouverture)
-- ============================================
CREATE TABLE `working_hours` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `day_of_week` INT(11) NOT NULL COMMENT '0=Lundi, 6=Dimanche',
  `open_time` TIME DEFAULT NULL,
  `close_time` TIME DEFAULT NULL,
  `closed` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_business_day` (`business_id`, `day_of_week`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: blocked_dates (Dates bloquées)
-- ============================================
CREATE TABLE `blocked_dates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_business_date` (`business_id`, `date`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: clients (Clients)
-- ============================================
CREATE TABLE `clients` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `address_street` VARCHAR(255) DEFAULT NULL,
  `address_city` VARCHAR(255) DEFAULT NULL,
  `address_postal_code` VARCHAR(50) DEFAULT NULL,
  `total_spent` DECIMAL(10,2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_business` (`business_id`),
  KEY `idx_email` (`email`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: reservations (Réservations)
-- ============================================
CREATE TABLE `reservations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `client_id` INT(11) DEFAULT NULL,
  `vehicle_type_id` INT(11) NOT NULL,
  `vehicle_details` TEXT DEFAULT NULL,
  `pack_id` INT(11) NOT NULL,
  `location_type` VARCHAR(100) DEFAULT 'local',
  `location_address` TEXT DEFAULT NULL,
  `appointment_date` DATE NOT NULL,
  `appointment_start_time` TIME NOT NULL,
  `appointment_end_time` TIME NOT NULL,
  `subtotal_ht` DECIMAL(10,2) NOT NULL,
  `subtotal_ttc` DECIMAL(10,2) NOT NULL,
  `deposit_percent` DECIMAL(5,2) DEFAULT 15.00,
  `deposit_ht` DECIMAL(10,2) NOT NULL,
  `deposit_ttc` DECIMAL(10,2) NOT NULL,
  `remaining_ht` DECIMAL(10,2) NOT NULL,
  `remaining_ttc` DECIMAL(10,2) NOT NULL,
  `deposit_paid` TINYINT(1) DEFAULT 0,
  `deposit_payment_id` VARCHAR(255) DEFAULT NULL,
  `full_payment_paid` TINYINT(1) DEFAULT 0,
  `full_payment_id` VARCHAR(255) DEFAULT NULL,
  `payment_method` VARCHAR(100) DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, confirmed, completed, cancelled',
  `special_instructions` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_business` (`business_id`),
  KEY `idx_client` (`client_id`),
  KEY `idx_date` (`appointment_date`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`vehicle_type_id`) REFERENCES `vehicle_types`(`id`),
  FOREIGN KEY (`pack_id`) REFERENCES `packs`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: reservation_options (Options d'une réservation)
-- ============================================
CREATE TABLE `reservation_options` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `reservation_id` INT(11) NOT NULL,
  `option_id` INT(11) NOT NULL,
  `price_ht` DECIMAL(10,2) NOT NULL,
  `price_ttc` DECIMAL(10,2) NOT NULL,
  `duration` DECIMAL(4,2) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_reservation` (`reservation_id`),
  FOREIGN KEY (`reservation_id`) REFERENCES `reservations`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`option_id`) REFERENCES `options`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: email_templates (Modèles d'emails)
-- ============================================
CREATE TABLE `email_templates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `business_id` INT(11) NOT NULL,
  `template_type` VARCHAR(100) NOT NULL COMMENT 'confirmation, reminder1h, reminder1d, reminder3d',
  `enabled` TINYINT(1) DEFAULT 1,
  `subject` VARCHAR(500) NOT NULL,
  `body` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_business_template` (`business_id`, `template_type`),
  FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: email_logs (Logs d'envoi d'emails)
-- ============================================
CREATE TABLE `email_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `reservation_id` INT(11) NOT NULL,
  `template_type` VARCHAR(100) NOT NULL,
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `status` VARCHAR(50) DEFAULT 'sent',
  `error_message` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reservation` (`reservation_id`),
  FOREIGN KEY (`reservation_id`) REFERENCES `reservations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DONNÉES D'EXEMPLE
-- ============================================

-- Entreprise exemple
INSERT INTO `businesses` (`slug`, `name`, `phone`, `email`, `address_street`, `address_city`, `address_postal_code`, `address_country`, `main_color`, `page_title`, `page_description`) VALUES
('cleantech-autodetail', 'CleanTech - AutoDetailing', '0750097950', 'contact@cleantech.fr', '117 Route de Briare', 'Gien', '45500', 'France', '#0ea5e9', 'Réservation - CleanTech AutoDetailing', 'Réservez votre prestation de detailing automobile de qualité professionnelle');

SET @business_id = LAST_INSERT_ID();

-- Types de véhicules
INSERT INTO `vehicle_types` (`business_id`, `name`, `slug`, `description`, `display_order`) VALUES
(@business_id, '2 roues', '2-roues', 'Scooters, motos', 1),
(@business_id, 'Citadine', 'citadine', 'Petits véhicules (Mini, Fiat 500)', 2),
(@business_id, 'Berline', 'berline', 'Voitures standards (Corolla, Civic)', 3),
(@business_id, 'Sportive', 'sportive', 'Voitures de sport (Porsche, Ferrari)', 4),
(@business_id, 'SUV', 'suv', 'Véhicules plus grands (RAV4, CR-V)', 5),
(@business_id, 'Monospace 5 places', 'monospace-5', 'Espace, Scenic, C4 Picasso', 6),
(@business_id, 'Monospace 7 places', 'monospace-7', 'Grand Espace, Grand Scenic, C8', 7),
(@business_id, 'Truck', 'truck', 'Silverado, Tacoma...', 8),
(@business_id, 'Van', 'van', 'Minivans, fourgonnettes', 9);

-- Packs
INSERT INTO `packs` (`business_id`, `name`, `slug`, `description`, `display_order`) VALUES
(@business_id, 'Pack Bronze', 'bronze', 'Nettoyage intérieur et extérieur de base', 1),
(@business_id, 'Pack Gold', 'gold', 'Nettoyage complet avec traitement premium', 2),
(@business_id, 'Pack Concession', 'concession', 'Prestation haut de gamme avec finitions professionnelles', 3);

-- Horaires d'ouverture
INSERT INTO `working_hours` (`business_id`, `day_of_week`, `open_time`, `close_time`, `closed`) VALUES
(@business_id, 0, '09:00', '20:00', 0), -- Lundi
(@business_id, 1, '09:00', '20:00', 0), -- Mardi
(@business_id, 2, '09:00', '20:00', 0), -- Mercredi
(@business_id, 3, '09:00', '20:00', 0), -- Jeudi
(@business_id, 4, '09:00', '20:00', 0), -- Vendredi
(@business_id, 5, '09:00', '18:00', 0), -- Samedi
(@business_id, 6, '09:00', '18:00', 1); -- Dimanche (fermé)

-- Modèles d'emails
INSERT INTO `email_templates` (`business_id`, `template_type`, `enabled`, `subject`, `body`) VALUES
(@business_id, 'confirmation', 1, 'Confirmation de votre réservation - {{business_name}}', 'Bonjour {{client_first_name}},\n\nVotre réservation a été confirmée !\n\nDétails :\n- Service : {{pack_name}}\n- Date : {{appointment_date}}\n- Heure : {{appointment_time}}\n- Total : {{total_ttc}} € TTC\n- Acompte : {{deposit_ttc}} € TTC\n\nÀ bientôt !'),
(@business_id, 'reminder1h', 1, 'Rappel : Votre rendez-vous dans 1 heure', 'Bonjour {{client_first_name}},\n\nRappel : Votre rendez-vous est prévu dans 1 heure.\n\nDate : {{appointment_date}} à {{appointment_time}}\n\nÀ très bientôt !'),
(@business_id, 'reminder1d', 1, 'Rappel : Votre rendez-vous demain', 'Bonjour {{client_first_name}},\n\nRappel : Votre rendez-vous est prévu demain.\n\nDate : {{appointment_date}} à {{appointment_time}}\n\nÀ bientôt !'),
(@business_id, 'reminder3d', 1, 'Rappel : Votre rendez-vous dans 3 jours', 'Bonjour {{client_first_name}},\n\nRappel : Votre rendez-vous est prévu dans 3 jours.\n\nDate : {{appointment_date}} à {{appointment_time}}\n\nÀ bientôt !');
