-- ============================================================
--  RÉGAL Coffee & Lounge — Schema Completo v1.0
-- ============================================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

CREATE TABLE IF NOT EXISTS `usuarios` (
    `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `nombre`    VARCHAR(100) NOT NULL,
    `email`     VARCHAR(150) NOT NULL UNIQUE,
    `password`  VARCHAR(255) NOT NULL,
    `rol`       ENUM('admin','hostess','mesero','cocina','caja') NOT NULL DEFAULT 'mesero',
    `activo`    TINYINT(1) NOT NULL DEFAULT 1,
    `creado_en` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mesas` (
    `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `numero`    TINYINT UNSIGNED NOT NULL UNIQUE,
    `nombre`    VARCHAR(60) NOT NULL,
    `zona`      ENUM('salon','kiosko','sala_juntas') NOT NULL DEFAULT 'salon',
    `capacidad` TINYINT UNSIGNED NOT NULL DEFAULT 4,
    `estatus`   ENUM('libre','ocupada','reservada','limpieza') NOT NULL DEFAULT 'libre',
    `token`     VARCHAR(64) NULL UNIQUE,
    `creado_en` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;

-- ============================================================
--  DATOS INICIALES
-- ============================================================
-- password para todos: password (cambiar en producción)
INSERT INTO `usuarios` (`nombre`,`email`,`password`,`rol`) VALUES
('Administrador','admin@regal.com',   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','admin'),
('Hostess',      'hostess@regal.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','hostess'),
('Cocina',       'cocina@regal.com',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','cocina'),
('Caja',         'caja@regal.com',    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','caja'),
('Mesero 1',     'mesero@regal.com',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','mesero');

INSERT INTO `mesas` (`numero`,`nombre`,`zona`,`capacidad`,`token`) VALUES
(1,'Mesa 1','salon',4,MD5(UUID())),(2,'Mesa 2','salon',4,MD5(UUID())),
(3,'Mesa 3','salon',4,MD5(UUID())),(4,'Mesa 4','salon',4,MD5(UUID())),
(5,'Mesa 5','salon',4,MD5(UUID())),(6,'Mesa 6','salon',4,MD5(UUID())),
(7,'Mesa 7','salon',4,MD5(UUID())),(8,'Mesa 8','salon',4,MD5(UUID())),
(9,'Mesa 9','salon',4,MD5(UUID())),(10,'Mesa 10','salon',6,MD5(UUID())),
(11,'Mesa 11','salon',4,MD5(UUID())),(12,'Mesa 12','salon',4,MD5(UUID())),
(13,'Mesa 13','salon',4,MD5(UUID())),(14,'Mesa 14','salon',4,MD5(UUID())),
(15,'Mesa 15','salon',4,MD5(UUID())),(16,'Mesa 16','salon',6,MD5(UUID())),
(17,'Mesa 17','salon',4,MD5(UUID())),(18,'Mesa 18','salon',4,MD5(UUID())),
(19,'Mesa 19','salon',4,MD5(UUID())),(20,'Mesa 20','salon',4,MD5(UUID())),
(21,'Kiosko','kiosko',10,MD5(UUID())),
(99,'Sala de Juntas','sala_juntas',20,MD5(UUID()));

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;