-- ============================================================
--  RÉGAL Coffee & Lounge — Schema Completo v1.0
-- ============================================================
SET foreign_key_checks = 0;

CREATE TABLE IF NOT EXISTS `reservas` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `mesa_id`        INT UNSIGNED NULL,
    `nombre_cliente` VARCHAR(150) NOT NULL,
    `telefono`       VARCHAR(20) NOT NULL,
    `email`          VARCHAR(150) NULL,
    `fecha`          DATE NOT NULL,
    `hora`           TIME NOT NULL,
    `comensales`     TINYINT UNSIGNED NOT NULL DEFAULT 2,
    `ocasion`        VARCHAR(100) NULL,
    `notas`          TEXT NULL,
    `estatus`        ENUM('pendiente','confirmada','sentada','cancelada','no_show') NOT NULL DEFAULT 'pendiente',
    `creado_por`     INT UNSIGNED NULL,
    `creado_en`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`mesa_id`) REFERENCES `mesas`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`creado_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `categorias_regal` (
    `id`     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `nombre` VARCHAR(80) NOT NULL,
    `orden`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `activo` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `productos_regal` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `categoria_id`  INT UNSIGNED NOT NULL,
    `nombre`        VARCHAR(150) NOT NULL,
    `descripcion`   VARCHAR(400) NULL,
    `precio_normal` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `precio_cambio` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `es_proteina`   TINYINT(1) NOT NULL DEFAULT 0,
    `disponible`    TINYINT(1) NOT NULL DEFAULT 1,
    `orden`         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `creado_en`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`categoria_id`) REFERENCES `categorias_regal`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_programado` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `fecha`       DATE NOT NULL,
    `turno`       ENUM('desayuno','comida') NOT NULL,
    `precio_base` DECIMAL(8,2) NOT NULL DEFAULT 120.00,
    `activo`      TINYINT(1) NOT NULL DEFAULT 1,
    `creado_por`  INT UNSIGNED NULL,
    `creado_en`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `fecha_turno` (`fecha`, `turno`),
    FOREIGN KEY (`creado_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_tiempos` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `menu_id`     INT UNSIGNED NOT NULL,
    `tipo`        ENUM('sopa','plato_fuerte','guarnicion','postre','bebida','extra') NOT NULL,
    `descripcion` VARCHAR(200) NOT NULL,
    `orden`       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (`menu_id`) REFERENCES `menu_programado`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_proteinas` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `menu_id`       INT UNSIGNED NOT NULL,
    `nombre`        VARCHAR(150) NOT NULL,
    `es_base`       TINYINT(1) NOT NULL DEFAULT 0,
    `precio_cambio` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `disponible`    TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (`menu_id`) REFERENCES `menu_programado`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ordenes` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `folio`          VARCHAR(20) NOT NULL UNIQUE,
    `mesa_id`        INT UNSIGNED NULL,
    `reserva_id`     INT UNSIGNED NULL,
    `origen`         ENUM('mesa','kiosko','caja') NOT NULL DEFAULT 'mesa',
    `turno`          ENUM('desayuno','comida') NOT NULL,
    `comensales`     TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `estatus`        ENUM('abierta','en_cocina','lista','entregada','pagada','cancelada') NOT NULL DEFAULT 'abierta',
    `pago_metodo`    ENUM('tarjeta','efectivo','pendiente') NOT NULL DEFAULT 'pendiente',
    `subtotal`       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `total`          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `notas`          TEXT NULL,
    `atendido_por`   INT UNSIGNED NULL,
    `creado_en`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`mesa_id`) REFERENCES `mesas`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`reserva_id`) REFERENCES `reservas`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`atendido_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orden_comensales` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `orden_id`    INT UNSIGNED NOT NULL,
    `numero`      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `nombre`      VARCHAR(80) NULL,
    `menu_id`     INT UNSIGNED NULL,
    `proteina_id` INT UNSIGNED NULL,
    `subtotal`    DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`orden_id`) REFERENCES `ordenes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`menu_id`) REFERENCES `menu_programado`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`proteina_id`) REFERENCES `menu_proteinas`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orden_rechazos` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `comensal_id` INT UNSIGNED NOT NULL,
    `tiempo_id`   INT UNSIGNED NOT NULL,
    FOREIGN KEY (`comensal_id`) REFERENCES `orden_comensales`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tiempo_id`) REFERENCES `menu_tiempos`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orden_adicionales` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `comensal_id`    INT UNSIGNED NOT NULL,
    `producto_id`    INT UNSIGNED NOT NULL,
    `tipo_precio`    ENUM('normal','cambio_proteina') NOT NULL DEFAULT 'normal',
    `precio_unitario` DECIMAL(8,2) NOT NULL,
    `cantidad`       TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `notas`          VARCHAR(200) NULL,
    FOREIGN KEY (`comensal_id`) REFERENCES `orden_comensales`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`producto_id`) REFERENCES `productos_regal`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `categorias_regal` (`nombre`,`orden`) VALUES
('Palomas',1),('Papas',2),('Snacks',3),('Bebidas',4),('Especiales',5);

INSERT INTO `productos_regal` (`categoria_id`,`nombre`,`precio_normal`,`precio_cambio`,`es_proteina`,`orden`) VALUES
(1,'Paloma Mantequilla',25.00,15.00,0,1),(1,'Paloma Natural',25.00,15.00,0,2),
(1,'Paloma Queso',25.00,15.00,0,3),(1,'Paloma Jalapeño',25.00,15.00,0,4),
(1,'Paloma Caramelo',30.00,20.00,0,5),
(2,'Papa Natural',35.00,25.00,0,1),(2,'Papa Adobada',35.00,25.00,0,2),
(3,'Minibon',35.00,25.00,0,1),(3,'Nathans',60.00,40.00,1,2),
(3,'Cono Boneless & Fries',55.00,40.00,1,3),(3,'Sushi',110.00,80.00,1,4),
(4,'ICEE',60.00,45.00,0,1),(4,'Dippin Dots',65.00,50.00,0,2);

SET foreign_key_checks = 1;
