-- -----------------------------------------------------
-- Configuraciones Iniciales
-- -----------------------------------------------------

-- Guardar las configuraciones actuales
SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
SET @OLD_SQL_MODE = @@SQL_MODE;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

-- Crear y seleccionar la base de datos con codificación utf8mb4
CREATE DATABASE IF NOT EXISTS `administrador_citas` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `administrador_citas`;
-- Establecer la conexión a utf8mb4
SET NAMES utf8mb4;

-- -----------------------------------------------------
-- Creación de Tablas
-- -----------------------------------------------------

-- Tabla: users
CREATE TABLE IF NOT EXISTS `users` (
  `id_user` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(45) UNIQUE NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(15) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `registered_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `last_modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: companies
CREATE TABLE IF NOT EXISTS `companies` (
  `id_company` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: reviews
CREATE TABLE IF NOT EXISTS `reviews` (
  `id_review` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `rating` TINYINT NOT NULL CHECK (`rating` BETWEEN 1 AND 5),
  `comment` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`),
  INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id_role` INT AUTO_INCREMENT PRIMARY KEY,
  `role_name` VARCHAR(45) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: company_user_roles
CREATE TABLE IF NOT EXISTS `company_user_roles` (
  `company_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`company_id`, `user_id`, `role_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id_role`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: categories (Añadido company_id)
CREATE TABLE IF NOT EXISTS `categories` (
  `id_category` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  UNIQUE KEY `unique_category` (`company_id`, `name`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: subcategories (Añadido company_id)
CREATE TABLE IF NOT EXISTS `subcategories` (
  `id_subcategory` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  UNIQUE KEY `unique_subcategory` (`category_id`, `company_id`, `name`),
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id_category`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_category_id` (`category_id`),
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Eliminada tabla company_subcategories (Redundante debido a la inclusión de company_id en subcategories)

-- Tabla: company_operating_hours
CREATE TABLE IF NOT EXISTS `company_operating_hours` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `day_of_week` ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL,
  `open_time` TIME NULL,
  `close_time` TIME NULL,
  `interval_number` INT NOT NULL,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY `unique_company_day_interval` (`company_id`, `day_of_week`, `interval_number`),
  INDEX `idx_company_day` (`company_id`, `day_of_week`),
  CONSTRAINT `chk_open_before_close` CHECK (
    (`open_time` IS NULL AND `close_time` IS NULL) OR 
    (`open_time` < `close_time`)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: addresses
CREATE TABLE IF NOT EXISTS `addresses` (
  `id_address` INT AUTO_INCREMENT PRIMARY KEY,
  `street_address` VARCHAR(255) NOT NULL,
  `address_line_2` VARCHAR(255),
  `postal_code` VARCHAR(10) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `state` VARCHAR(100) NOT NULL,
  `country` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: user_addresses
CREATE TABLE IF NOT EXISTS `user_addresses` (
  `user_id` INT NOT NULL,
  `address_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `address_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id_address`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: company_addresses
CREATE TABLE IF NOT EXISTS `company_addresses` (
  `company_id` INT NOT NULL,
  `address_id` INT NOT NULL,
  PRIMARY KEY (`company_id`, `address_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id_address`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: customers
CREATE TABLE IF NOT EXISTS `customers` (
  `id_customer` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `additional_info` VARCHAR(255),
  UNIQUE KEY `unique_customer` (`user_id`, `company_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_user_company` (`user_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: employees
CREATE TABLE IF NOT EXISTS `employees` (
  `id_employee` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `specialty` VARCHAR(100),
  `commission_rate` DECIMAL(5,2) DEFAULT 0.00,
  UNIQUE KEY `unique_employee` (`user_id`, `company_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_user_company` (`user_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: products
CREATE TABLE IF NOT EXISTS `products` (
  `id_product` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `product_name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10,2) NOT NULL,
  `stock_quantity` INT NOT NULL,
  `commission_percentage` DECIMAL(5,2) DEFAULT 0.00,
  UNIQUE KEY `unique_product` (`company_id`, `product_name`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_images` (
  `id_product_image` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `sort_order` INT DEFAULT 0,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: services
CREATE TABLE IF NOT EXISTS `services` (
  `id_service` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `service_name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10,2) NOT NULL,
  `discount` DECIMAL(5,2) DEFAULT 0.00,
  `duration` TIME NOT NULL,
  `commission_percentage` DECIMAL(5,2) DEFAULT 0.00,
  UNIQUE KEY `unique_service` (`company_id`, `service_name`),
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service_images` (
  `id_service_image` INT AUTO_INCREMENT PRIMARY KEY,
  `service_id` INT NOT NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `sort_order` INT DEFAULT 0,
  FOREIGN KEY (`service_id`) REFERENCES `services` (`id_service`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_service_id` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Tabla: service_subcategories (Añadido company_id)
CREATE TABLE IF NOT EXISTS `service_subcategories` (
    `service_id` INT NOT NULL,
    `subcategory_id` INT NOT NULL,
    `company_id` INT NOT NULL,
    PRIMARY KEY (`service_id`, `subcategory_id`, `company_id`),
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id_service`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories` (`id_subcategory`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: product_subcategories
CREATE TABLE IF NOT EXISTS `product_subcategories` (
  `product_id` INT NOT NULL,
  `subcategory_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  PRIMARY KEY (`product_id`, `subcategory_id`, `company_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories` (`id_subcategory`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Tabla: appointments
CREATE TABLE IF NOT EXISTS `appointments` (
  `id_appointment` INT AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `appointment_datetime` DATETIME NOT NULL,
  `status` ENUM('pending', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
  `total_duration` TIME,
  `total_cost` DECIMAL(10,2),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id_customer`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_appointment_datetime` (`appointment_datetime`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: appointment_services (Añadido company_id)
CREATE TABLE IF NOT EXISTS `appointment_services` (
  `id_appointment_service` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `appointment_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  `employee_id` INT NULL,
  `company_id` INT NOT NULL,
  `status` ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',
  `service_start_datetime` DATETIME DEFAULT NULL,
  `service_end_datetime` DATETIME DEFAULT NULL,
  FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id_appointment`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services` (`id_service`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id_employee`) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_appointment_id` (`appointment_id`),
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_service_id` (`service_id`),
  INDEX `idx_company_id` (`company_id`),
  INDEX `idx_service_start_datetime` (`service_start_datetime`),
  INDEX `idx_service_end_datetime` (`service_end_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: appointment_products (Añadido company_id)
CREATE TABLE IF NOT EXISTS `appointment_products` (
  `id_appointment_product` INT AUTO_INCREMENT PRIMARY KEY,
  `appointment_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `employee_id` INT NULL,
  `company_id` INT NOT NULL,
  `status` ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',
  FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id_appointment`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id_employee`) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_appointment_id` (`appointment_id`),
  INDEX `idx_product_id` (`product_id`),
  INDEX `idx_employee_id` (`employee_id`),
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: employee_services (Añadido company_id)
CREATE TABLE IF NOT EXISTS `employee_services` (
  `employee_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  PRIMARY KEY (`employee_id`, `service_id`, `company_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id_employee`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services` (`id_service`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: schedule_types (Añadido company_id)
CREATE TABLE IF NOT EXISTS `schedule_types` (
  `schedule_type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `days_available` VARCHAR(255) NOT NULL,  -- Por ejemplo: 'Monday,Tuesday,Wednesday'
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY `unique_schedule_type` (`company_id`, `description`),
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: employee_schedule_types
CREATE TABLE IF NOT EXISTS `employee_schedule_types` (
  `employee_id` INT NOT NULL,
  `schedule_type_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  PRIMARY KEY (`employee_id`, `schedule_type_id`, `company_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id_employee`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`schedule_type_id`) REFERENCES `schedule_types` (`schedule_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: work_time_types (Añadido company_id)
CREATE TABLE IF NOT EXISTS `work_time_types` (
  `work_time_type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `lunch_start_time` TIME,
  `lunch_end_time` TIME,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `chk_time_validity` CHECK (`start_time` < `end_time`),
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: employee_work_times
CREATE TABLE IF NOT EXISTS `employee_work_times` (
  `employee_id` INT NOT NULL,
  `work_time_type_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  PRIMARY KEY (`employee_id`, `work_time_type_id`, `company_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id_employee`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`work_time_type_id`) REFERENCES `work_time_types` (`work_time_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: carts (Añadido company_id)
CREATE TABLE IF NOT EXISTS `carts` (
  `id_cart` INT AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id_customer`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_customer_company` (`customer_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: cart_items (Modificada)
CREATE TABLE IF NOT EXISTS `cart_items` (
  `id_cart_item` INT AUTO_INCREMENT PRIMARY KEY,
  `cart_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  `company_id` INT NOT NULL,
  FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id_cart`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id_customer`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_cart_id` (`cart_id`),
  INDEX `idx_customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tablas intermedias para manejar muchos a muchos entre cart_items y products/services

-- Tabla: cart_products
CREATE TABLE IF NOT EXISTS `cart_products` (
  `cart_item_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  PRIMARY KEY (`cart_item_id`, `product_id`),
  FOREIGN KEY (`cart_item_id`) REFERENCES `cart_items` (`id_cart_item`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: cart_services
CREATE TABLE IF NOT EXISTS `cart_services` (
  `cart_item_id` INT NOT NULL,
  `service_id` INT NOT NULL,
  PRIMARY KEY (`cart_item_id`, `service_id`),
  FOREIGN KEY (`cart_item_id`) REFERENCES `cart_items` (`id_cart_item`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`service_id`) REFERENCES `services` (`id_service`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_service_id` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Restaurar Configuraciones Originales
-- -----------------------------------------------------

SET SQL_MODE = @OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;
