-- https://www.phpmyadmin.net/
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 31-01-2026 a las 13:30:35
-- Versión del servidor: 10.4.32-MariaDB
-- Versión de PHP: 8.2.12

START TRANSACTION;



--
-- Base de datos: `petra_pos`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `categories`
--

CREATE TABLE `categories` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `color` varchar(7) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `categories`
--

INSERT INTO `categories` (`id`, `name`, `description`, `color`, `sort_order`, `is_active`, `created_at`, `updated_at`) VALUES
('ec6eab57-fb34-11f0-aeca-9c6b008de4f7', 'Main Courses', 'Primary dishes and entrees', '#4ECDC4', 2, 1, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec6eabae-fb34-11f0-aeca-9c6b008de4f7', 'Beverages', 'Drinks, sodas, and refreshments', '#45B7D1', 3, 1, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec6eabf2-fb34-11f0-aeca-9c6b008de4f7', 'Salads', 'Fresh salads and healthy options', '#FECA57', 5, 1, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec6eac06-fb34-11f0-aeca-9c6b008de4f7', 'Pizza', 'Various pizza options', '#FF9FF3', 6, 1, '2026-01-27 04:01:47', '2026-01-27 04:01:47');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `comisiones`
--

CREATE TABLE `comisiones` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `mesero_id` char(36) NOT NULL,
  `periodo_inicio` date NOT NULL,
  `periodo_fin` date NOT NULL,
  `total_ventas` decimal(10,2) NOT NULL DEFAULT 0.00,
  `porcentaje_comision` decimal(5,2) NOT NULL DEFAULT 0.00,
  `monto_comision` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estado` enum('pendiente','pagada','cancelada') DEFAULT 'pendiente',
  `notas` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `configuracion_comisiones`
--

CREATE TABLE `configuracion_comisiones` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `mesero_id` char(36) NOT NULL,
  `porcentaje_base` decimal(5,2) NOT NULL DEFAULT 5.00,
  `meta_mensual` decimal(10,2) DEFAULT NULL,
  `bono_extra` decimal(5,2) DEFAULT 0.00,
  `activo` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `dining_tables`
--

CREATE TABLE `dining_tables` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `table_number` varchar(20) NOT NULL,
  `seating_capacity` int(11) DEFAULT 4,
  `location` varchar(50) DEFAULT NULL,
  `is_occupied` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `dining_tables`
--

INSERT INTO `dining_tables` (`id`, `table_number`, `seating_capacity`, `location`, `is_occupied`, `created_at`, `updated_at`) VALUES
('ec7082e6-fb34-11f0-aeca-9c6b008de4f7', 'T01', 2, 'Main Floor', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec70844e-fb34-11f0-aeca-9c6b008de4f7', 'T02', 4, 'Main Floor', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec7084d5-fb34-11f0-aeca-9c6b008de4f7', 'T03', 4, 'Main Floor', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec708506-fb34-11f0-aeca-9c6b008de4f7', 'T04', 6, 'Main Floor', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec708532-fb34-11f0-aeca-9c6b008de4f7', 'T05', 2, 'Main Floor', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec70855f-fb34-11f0-aeca-9c6b008de4f7', 'T06', 4, 'Window Side', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec708587-fb34-11f0-aeca-9c6b008de4f7', 'T07', 4, 'Window Side', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec7085ad-fb34-11f0-aeca-9c6b008de4f7', 'T08', 8, 'Private Room', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec7085d6-fb34-11f0-aeca-9c6b008de4f7', 'T09', 2, 'Patio', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec7085fe-fb34-11f0-aeca-9c6b008de4f7', 'T10', 4, 'Patio', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec708624-fb34-11f0-aeca-9c6b008de4f7', 'BAR01', 1, 'Bar Counter', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec70864e-fb34-11f0-aeca-9c6b008de4f7', 'BAR02', 1, 'Bar Counter', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec708676-fb34-11f0-aeca-9c6b008de4f7', 'BAR03', 1, 'Bar Counter', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47'),
('ec70869f-fb34-11f0-aeca-9c6b008de4f7', 'TAKEOUT', 1, 'Takeout Counter', 0, '2026-01-27 04:01:47', '2026-01-27 04:01:47');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `inventory`
--

CREATE TABLE `inventory` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `product_id` char(36) NOT NULL,
  `current_stock` int(11) NOT NULL DEFAULT 0,
  `minimum_stock` int(11) DEFAULT 0,
  `maximum_stock` int(11) DEFAULT 0,
  `unit_cost` decimal(10,2) DEFAULT NULL,
  `last_restocked_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `orders`
--

CREATE TABLE `orders` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `order_number` varchar(20) NOT NULL,
  `table_id` char(36) DEFAULT NULL,
  `user_id` char(36) DEFAULT NULL,
  `customer_name` varchar(100) DEFAULT NULL,
  `order_type` enum('dine_in','takeout','delivery') NOT NULL,
  `status` enum('pending','confirmed','preparing','ready','served','completed','cancelled') NOT NULL DEFAULT 'pending',
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `served_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `mesero_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `order_items`
--

CREATE TABLE `order_items` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `order_id` char(36) NOT NULL,
  `product_id` char(36) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `special_instructions` text DEFAULT NULL,
  `status` enum('pending','preparing','ready','served') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `order_status_history`
--

CREATE TABLE `order_status_history` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `order_id` char(36) NOT NULL,
  `previous_status` varchar(20) DEFAULT NULL,
  `new_status` varchar(20) NOT NULL,
  `changed_by` char(36) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `payments`
--

CREATE TABLE `payments` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `order_id` char(36) NOT NULL,
  `payment_method` enum('cash','credit_card','debit_card','digital_wallet') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `status` enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `processed_by` char(36) DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `products`
--

CREATE TABLE `products` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `category_id` char(36) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `barcode` varchar(50) DEFAULT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `preparation_time` int(11) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `users`
--

CREATE TABLE `users` (
  `id` char(36) NOT NULL DEFAULT uuid(),
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `role` enum('admin','manager','server','counter','kitchen') NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `first_name`, `last_name`, `role`, `is_active`, `created_at`, `updated_at`) VALUES
('626bb758-fb38-11f0-aeca-9c6b008de4f7', 'petra_admin', 'admin@petra.com', '$2a$10$i3CGbB2AMn7p.oTVOuUUUuUKFZVRxCiP1OriDE.YAeqvfm0JqMW2m', 'Petra', 'Admin', 'admin', 1, '2026-01-27 04:26:34', '2026-01-27 04:26:34');

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `vista_ventas_barra`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `vista_ventas_barra` (
`fecha` date
,`total_pedidos` bigint(21)
,`total_ventas` decimal(32,2)
,`total_impuestos` decimal(32,2)
,`ventas_netas` decimal(33,2)
,`ticket_promedio` decimal(14,6)
);

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `vista_ventas_mesero`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `vista_ventas_mesero` (
`mesero_id` char(36)
,`username` varchar(50)
,`nombre_completo` varchar(101)
,`fecha` date
,`total_pedidos` bigint(21)
,`total_ventas` decimal(32,2)
,`ventas_netas` decimal(33,2)
,`porcentaje_comision` decimal(5,2)
,`comision_estimada` decimal(42,8)
);

-- --------------------------------------------------------

--
-- Estructura para la vista `vista_ventas_barra`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vista_ventas_barra`  AS SELECT cast(`orders`.`created_at` as date) AS `fecha`, count(`orders`.`id`) AS `total_pedidos`, sum(`orders`.`total_amount`) AS `total_ventas`, sum(`orders`.`tax_amount`) AS `total_impuestos`, sum(`orders`.`total_amount` - `orders`.`tax_amount`) AS `ventas_netas`, avg(`orders`.`total_amount`) AS `ticket_promedio` FROM `orders` WHERE `orders`.`status` = 'completed' GROUP BY cast(`orders`.`created_at` as date) ORDER BY cast(`orders`.`created_at` as date) DESC ;

-- --------------------------------------------------------

--
-- Estructura para la vista `vista_ventas_mesero`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vista_ventas_mesero`  AS SELECT `u`.`id` AS `mesero_id`, `u`.`username` AS `username`, concat(`u`.`first_name`,' ',`u`.`last_name`) AS `nombre_completo`, cast(`o`.`created_at` as date) AS `fecha`, count(`o`.`id`) AS `total_pedidos`, sum(`o`.`total_amount`) AS `total_ventas`, sum(`o`.`total_amount` - `o`.`tax_amount`) AS `ventas_netas`, `c`.`porcentaje_base` AS `porcentaje_comision`, sum(`o`.`total_amount` - `o`.`tax_amount`) * `c`.`porcentaje_base` / 100 AS `comision_estimada` FROM ((`users` `u` left join `orders` `o` on(`u`.`id` = `o`.`mesero_id` and `o`.`status` = 'completed')) left join `configuracion_comisiones` `c` on(`u`.`id` = `c`.`mesero_id`)) WHERE `u`.`role` = 'mesero' GROUP BY `u`.`id`, `u`.`username`, `u`.`first_name`, `u`.`last_name`, cast(`o`.`created_at` as date), `c`.`porcentaje_base` ;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`);

--
-- Indices de la tabla `comisiones`
--
ALTER TABLE `comisiones`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mesero_periodo` (`mesero_id`,`periodo_inicio`,`periodo_fin`),
  ADD KEY `idx_estado` (`estado`);

--
-- Indices de la tabla `configuracion_comisiones`
--
ALTER TABLE `configuracion_comisiones`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_mesero` (`mesero_id`);

--
-- Indices de la tabla `dining_tables`
--
ALTER TABLE `dining_tables`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `table_number` (`table_number`);

--
-- Indices de la tabla `inventory`
--
ALTER TABLE `inventory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_inventory_product_id` (`product_id`);

--
-- Indices de la tabla `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `order_number` (`order_number`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_orders_status` (`status`),
  ADD KEY `idx_orders_created_at` (`created_at`),
  ADD KEY `idx_orders_table_id` (`table_id`),
  ADD KEY `idx_mesero_orders` (`mesero_id`,`created_at`);

--
-- Indices de la tabla `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_items_order_id` (`order_id`),
  ADD KEY `idx_order_items_product_id` (`product_id`);

--
-- Indices de la tabla `order_status_history`
--
ALTER TABLE `order_status_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `changed_by` (`changed_by`);

--
-- Indices de la tabla `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `processed_by` (`processed_by`),
  ADD KEY `idx_payments_order_id` (`order_id`);

--
-- Indices de la tabla `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `idx_products_category_id` (`category_id`),
  ADD KEY `idx_products_is_available` (`is_available`);

--
-- Indices de la tabla `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `comisiones`
--
ALTER TABLE `comisiones`
  ADD CONSTRAINT `comisiones_ibfk_1` FOREIGN KEY (`mesero_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `configuracion_comisiones`
--
ALTER TABLE `configuracion_comisiones`
  ADD CONSTRAINT `configuracion_comisiones_ibfk_1` FOREIGN KEY (`mesero_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `inventory`
--
ALTER TABLE `inventory`
  ADD CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_mesero` FOREIGN KEY (`mesero_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `dining_tables` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Filtros para la tabla `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `order_status_history`
--
ALTER TABLE `order_status_history`
  ADD CONSTRAINT `order_status_history_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_status_history_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Filtros para la tabla `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Filtros para la tabla `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;
COMMIT;

