-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 05-01-2023 a las 03:05:30
-- Versión del servidor: 10.1.40-MariaDB
-- Versión de PHP: 7.3.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Plantilla maestra de base de datos: `siho_pms_master`
--

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

--
-- Estructura de tabla para la tabla `apartar`
--

CREATE TABLE `apartar` (
  `idApartar` int(11) NOT NULL,
  `Num_Hab` int(11) NOT NULL,
  `fecIn` datetime NOT NULL,
  `horIn` time NOT NULL,
  `fecOut` datetime NOT NULL,
  `horOut` time NOT NULL,
  `IdCliente` int(11) NOT NULL,
  `IdUsuario` int(11) NOT NULL,
  `cantTarifa` int(11) NOT NULL,
  `tarifaReserva` varchar(60) COLLATE latin1_spanish_ci DEFAULT NULL,
  `preTarifa` decimal(18,2) NOT NULL,
  `medioAdelanto` varchar(60) COLLATE latin1_spanish_ci DEFAULT NULL,
  `montoAdelanto` decimal(18,2) DEFAULT NULL,
  `metodoAdelanto` varchar(60) COLLATE latin1_spanish_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `audiciones`
--

CREATE TABLE `audiciones` (
  `idAudicion` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `IdUsuario` int(11) NOT NULL,
  `Num_Hab` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `audiciones_detalle`
--

CREATE TABLE `audiciones_detalle` (
  `idAudicionDetalle` int(11) NOT NULL,
  `idConAud` int(11) NOT NULL,
  `estAudDet` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `comAudDet` text COLLATE utf8_unicode_ci,
  `imgAudDet` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `estAudDet2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `idAudicion` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `caja`
--

CREATE TABLE `caja` (
  `codCaja` int(11) NOT NULL,
  `horaApertura` time NOT NULL,
  `fechaApertura` date NOT NULL,
  `montoApertura` decimal(18,2) NOT NULL,
  `horaCierre` time DEFAULT NULL,
  `fechaCierre` date DEFAULT NULL,
  `montoCierre` decimal(18,2) DEFAULT NULL,
  `montoEsperado` decimal(18,2) DEFAULT NULL,
  `diferenciaCierre` decimal(18,2) DEFAULT NULL,
  `estadoCuadre` varchar(20) DEFAULT NULL,
  `observacionCierre` varchar(500) DEFAULT NULL,
  `codUsuario` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `categoria`
--

CREATE TABLE `categoria` (
  `IdCategoria` int(11) NOT NULL,
  `Denominacion` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `categoria` (`IdCategoria`, `Denominacion`) VALUES
(11, 'Bebidas'),
(12, 'Desechables'),
(13, 'Cigarros'),
(14, 'Preservativos'),
(15, 'Productos Varios'),
(16, 'Botanas'),
(17, 'Cerveza'),
(18, 'Alimentos');

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

--
-- Estructura de tabla para la tabla `categoria_aud`
--

CREATE TABLE `categoria_aud` (
  `idCatAud` int(11) NOT NULL,
  `catAud` varchar(75) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

INSERT INTO `categoria_aud` (`idCatAud`, `catAud`) VALUES
(1, 'Habitación'),
(2, 'Baño'),
(3, 'General'),
(4, 'Servicios');

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

--
-- Estructura de tabla para la tabla `cliente`
--

CREATE TABLE `cliente` (
  `IdCliente` int(11) NOT NULL,
  `Nombre` varchar(40) NOT NULL,
  `Apellido` varchar(50) DEFAULT NULL,
  `Celular` varchar(15) DEFAULT NULL,
  `Correo` varchar(45) DEFAULT NULL,
  `TipDocumento` varchar(20) NOT NULL,
  `NumDocumento` varchar(15) DEFAULT NULL,
  `Direccion` varchar(40) DEFAULT NULL,
  `nroMatricula` varchar(50) DEFAULT NULL,
  `captura` varchar(191) DEFAULT NULL,
  `nombre_normalizado` varchar(191) DEFAULT NULL,
  `documento_normalizado` varchar(80) DEFAULT NULL,
  `correo_normalizado` varchar(100) DEFAULT NULL,
  `placa_normalizada` varchar(40) DEFAULT NULL,
  `celular_normalizado` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `cliente` (`IdCliente`, `Nombre`, `Apellido`, `Celular`, `Correo`, `TipDocumento`, `NumDocumento`, `Direccion`, `nroMatricula`, `captura`, `nombre_normalizado`, `documento_normalizado`, `correo_normalizado`, `placa_normalizada`, `celular_normalizado`) VALUES
(478, 'SIN ESPECIFICAR', NULL, NULL, NULL, 'SIN DOC', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

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

--
-- Estructura de tabla para la tabla `consulta_aud`
--

CREATE TABLE `consulta_aud` (
  `idConAud` int(11) NOT NULL,
  `conAud` text COLLATE utf8_unicode_ci NOT NULL,
  `idCatAud` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `consumo`
--

CREATE TABLE `consumo` (
  `IdConsumo` int(11) NOT NULL,
  `Cantidad` int(11) NOT NULL,
  `precioVenta` decimal(18,2) DEFAULT NULL,
  `Total` decimal(18,2) NOT NULL,
  `Estado` varchar(20) NOT NULL,
  `FechConsumo` date NOT NULL,
  `horaConsumo` time DEFAULT NULL,
  `IdProducto` int(11) NOT NULL,
  `FechaPago` datetime DEFAULT NULL,
  `metodoPago` varchar(50) DEFAULT NULL,
  `idUsuario` int(11) DEFAULT NULL,
  `IdReserva` int(11) NOT NULL,
  `codCaja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Estructura de tabla para la tabla `datos_hotel`
--

CREATE TABLE `datos_hotel` (
  `IdHotel` int(11) NOT NULL,
  `nombre` varchar(50) NOT NULL,
  `direccion` varchar(50) NOT NULL,
  `telefono` varchar(20) NOT NULL,
  `logo` varchar(80) NOT NULL,
  `ruc` varchar(15) DEFAULT NULL,
  `color_primario` varchar(7) NOT NULL DEFAULT '#17A2B8',
  `color_secundario` varchar(7) NOT NULL DEFAULT '#001F3F',
  `color_acento` varchar(7) NOT NULL DEFAULT '#28A745',
  `color_fondo` varchar(7) NOT NULL DEFAULT '#F4F6F9',
  `color_texto` varchar(7) NOT NULL DEFAULT '#212529',
  `retiro_automatico_activo` tinyint(1) NOT NULL DEFAULT 0,
  `retiro_automatico_monto` decimal(18,2) NOT NULL DEFAULT 0,
  `retiro_automatico_fondo` decimal(18,2) NOT NULL DEFAULT 0,
  `retencion_datos_activa` tinyint(1) NOT NULL DEFAULT 0,
  `retencion_datos_anios` tinyint unsigned NOT NULL DEFAULT 3,
  `sesiones_limite_recepcion` tinyint unsigned NOT NULL DEFAULT 1,
  `sesiones_limite_caja` tinyint unsigned NOT NULL DEFAULT 1,
  `sesiones_limite_gerencia` tinyint unsigned NOT NULL DEFAULT 1,
  `sesiones_limite_ama_llaves` tinyint unsigned NOT NULL DEFAULT 1,
  `sesiones_limite_socio` tinyint unsigned NOT NULL DEFAULT 1,
  `sesiones_limite_admin` tinyint unsigned NOT NULL DEFAULT 1,
  `tolerancia_diferencia_caja` decimal(18,2) NOT NULL DEFAULT 0.00,
  `sesiones_inactividad_minutos` smallint unsigned NOT NULL DEFAULT 120
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `datos_hotel` (`IdHotel`, `nombre`, `direccion`, `telefono`, `logo`, `ruc`, `color_primario`, `color_secundario`, `color_acento`, `color_fondo`, `color_texto`, `retiro_automatico_activo`, `retiro_automatico_monto`, `retiro_automatico_fondo`, `retencion_datos_activa`, `retencion_datos_anios`, `sesiones_limite_recepcion`, `sesiones_limite_caja`, `sesiones_limite_gerencia`, `sesiones_limite_ama_llaves`, `sesiones_limite_socio`, `sesiones_limite_admin`, `tolerancia_diferencia_caja`, `sesiones_inactividad_minutos`) VALUES
(1, 'HOTEL POR CONFIGURAR', 'DIRECCION POR CONFIGURAR', '0000000000', '', NULL, '#17A2B8', '#001F3F', '#28A745', '#F4F6F9', '#212529', 0, 0.00, 0.00, 0, 3, 1, 1, 2, 1, 2, 0, 0.00, 120);

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

--
-- Estructura de tabla para la tabla `detalle_ingreso`
--

CREATE TABLE `detalle_ingreso` (
  `iddetalle_ingreso` int(11) NOT NULL,
  `idingreso` int(11) NOT NULL,
  `IdProducto` int(11) NOT NULL,
  `valorUMedida` int(11) NOT NULL,
  `cantidad` int(11) NOT NULL,
  `precioCompra` decimal(18,2) NOT NULL,
  `precioVenta` decimal(18,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

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

--
-- Estructura de tabla para la tabla `detalle_servicio`
--

CREATE TABLE `detalle_servicio` (
  `idDetalle_servicio` int(11) NOT NULL,
  `fdServicio` date DEFAULT NULL,
  `precioDS` decimal(18,2) DEFAULT NULL,
  `cantidadDS` int(11) DEFAULT NULL,
  `metPagDse` varchar(35) COLLATE latin1_spanish_ci DEFAULT NULL,
  `codCaja` int(11) DEFAULT NULL,
  `idServicio` int(11) NOT NULL,
  `IdReserva` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `detalle_venta`
--

CREATE TABLE `detalle_venta` (
  `codDetalle_venta` int(11) NOT NULL,
  `cantidad` int(11) NOT NULL,
  `precioVenta` decimal(18,2) NOT NULL,
  `descuento` decimal(18,2) DEFAULT NULL,
  `codVenta` int(11) NOT NULL,
  `IdProducto` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `det_limpieza`
--

CREATE TABLE `det_limpieza` (
  `idDetLim` int(11) NOT NULL,
  `fechaDetLim` date NOT NULL,
  `horaDetLim` time NOT NULL,
  `idPer` int(11) NOT NULL,
  `idLim` int(11) NOT NULL,
  `Num_Hab` int(11) NOT NULL,
  `IdReserva` int(11) DEFAULT NULL,
  `tipo_operacion` varchar(30) NOT NULL DEFAULT 'SALIDA',
  `estado_registro` varchar(30) NOT NULL DEFAULT 'REGISTRADO',
  `observacion` varchar(250) DEFAULT NULL,
  `IdUsuario` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `egreso_caja`
--

CREATE TABLE `egreso_caja` (
  `codEgreso` int(11) NOT NULL,
  `horaEgreso` time NOT NULL,
  `fechaEgreso` date NOT NULL,
  `tipo` varchar(30) COLLATE latin1_spanish_ci NOT NULL,
  `entregadoA` varchar(90) COLLATE latin1_spanish_ci DEFAULT NULL,
  `motivo` varchar(90) COLLATE latin1_spanish_ci DEFAULT NULL,
  `importe` decimal(18,2) NOT NULL,
  `estado` varchar(25) COLLATE latin1_spanish_ci NOT NULL,
  `codUsuario` int(11) NOT NULL,
  `codCaja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `habitacion`
--

CREATE TABLE `habitacion` (
  `Num_Hab` int(11) NOT NULL,
  `Descripcion` varchar(250) DEFAULT NULL,
  `Estado` varchar(30) NOT NULL,
  `Precio` decimal(18,2) NOT NULL,
  `fecMan` datetime DEFAULT NULL,
  `resMan` varchar(91) DEFAULT NULL,
  `motMan` text,
  `idDetLim` int(11) DEFAULT NULL,
  `IdTipoHabitacion` int(11) NOT NULL,
  `IdNivel` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Estructura de tabla para la tabla `ingreso`
--

CREATE TABLE `ingreso` (
  `idingreso` int(11) NOT NULL,
  `idpro` int(11) NOT NULL,
  `IdUsuario` int(11) NOT NULL,
  `fecha` date NOT NULL,
  `hora` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

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

--
-- Estructura de tabla para la tabla `ingreso_caja`
--

CREATE TABLE `ingreso_caja` (
  `codIngreso` int(11) NOT NULL,
  `horaIngreso` time NOT NULL,
  `fechaIngreso` date NOT NULL,
  `recibidoDe` varchar(90) COLLATE latin1_spanish_ci DEFAULT NULL,
  `motivo` varchar(90) COLLATE latin1_spanish_ci DEFAULT NULL,
  `importe` decimal(18,2) NOT NULL,
  `estado` varchar(25) COLLATE latin1_spanish_ci NOT NULL,
  `codUsuario` int(11) NOT NULL,
  `codCaja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `mantenimiento`
--

CREATE TABLE `mantenimiento` (
  `idMantenimiento` int(11) NOT NULL,
  `motMan` text COLLATE latin1_spanish_ci NOT NULL,
  `resMan` varchar(91) COLLATE latin1_spanish_ci NOT NULL,
  `fecMan` datetime NOT NULL,
  `fecManOpe` datetime DEFAULT NULL,
  `Num_Hab` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `nivel`
--

CREATE TABLE `nivel` (
  `IdNivel` int(11) NOT NULL,
  `Denominacion` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `nivel` (`IdNivel`, `Denominacion`) VALUES
(4, 'Primer Piso');

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

--
-- Estructura de tabla para la tabla `observacion`
--

CREATE TABLE `observacion` (
  `idObservacion` int(11) NOT NULL,
  `fecObs` date NOT NULL,
  `horObs` time NOT NULL,
  `detObs` text COLLATE latin1_spanish_ci NOT NULL,
  `IdCliente` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `pago`
--

CREATE TABLE `pago` (
  `IdPago` int(11) NOT NULL,
  `TipoComprobante` varchar(20) DEFAULT NULL,
  `NumComprobante` varchar(12) DEFAULT NULL,
  `Igv` decimal(18,2) DEFAULT NULL,
  `Pago2` decimal(18,2) DEFAULT NULL,
  `TotalPago` decimal(18,2) NOT NULL,
  `FechaEmision` date DEFAULT NULL,
  `horaSalida_o` time DEFAULT NULL,
  `departure_date` datetime NOT NULL,
  `FechaPago` datetime NOT NULL,
  `Estado` varchar(20) DEFAULT NULL,
  `Penalidad` decimal(18,2) DEFAULT NULL,
  `IdReserva` int(11) NOT NULL,
  `codCaja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Estructura de tabla para la tabla `personal`
--

CREATE TABLE `personal` (
  `idPer` int(11) NOT NULL,
  `nomPer` varchar(181) COLLATE latin1_spanish_ci NOT NULL,
  `telPer` varchar(25) COLLATE latin1_spanish_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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


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

--
-- Estructura de tabla para la tabla `producto`
--

CREATE TABLE `producto` (
  `IdProducto` int(11) NOT NULL,
  `NombProducto` varchar(40) NOT NULL,
  `barcode` varchar(64) DEFAULT NULL,
  `internal_code` varchar(64) DEFAULT NULL,
  `Imagen` varchar(200) DEFAULT NULL,
  `Precio` decimal(18,2) DEFAULT NULL,
  `Descripcion` varchar(50) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  `tener` int(11) NOT NULL,
  `IdCategoria` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Estructura de tabla para la tabla `proveedor`
--

CREATE TABLE `proveedor` (
  `idpro` int(11) NOT NULL,
  `nomPro` varchar(45) COLLATE utf8_spanish_ci NOT NULL,
  `phone` varchar(15) COLLATE utf8_spanish_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

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


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

--
-- Estructura de tabla para la tabla `renovacion`
--

CREATE TABLE `renovacion` (
  `idRenovacion` int(11) NOT NULL,
  `fRenovacion` datetime NOT NULL,
  `fIniRen` datetime NOT NULL,
  `fFinRen` datetime NOT NULL,
  `tarRen` varchar(50) COLLATE latin1_spanish_ci NOT NULL,
  `canRen` int(11) NOT NULL,
  `metPagRen` varchar(50) COLLATE latin1_spanish_ci NOT NULL,
  `cosRen` decimal(18,2) NOT NULL,
  `codCaja` int(11) NOT NULL,
  `IdReserva` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `reserva`
--

CREATE TABLE `reserva` (
  `IdReserva` int(11) NOT NULL,
  `FechReserva` date DEFAULT NULL,
  `FechEntrada` date DEFAULT NULL,
  `HoraEntrada` time DEFAULT NULL,
  `FechSalida` date NOT NULL,
  `entry_date` datetime NOT NULL,
  `CostoAlojamiento` decimal(18,2) NOT NULL,
  `descuento` decimal(18,2) DEFAULT NULL,
  `Observacion` varchar(200) DEFAULT NULL,
  `Estado` varchar(20) NOT NULL,
  `horaSalida` time DEFAULT NULL,
  `toalla` varchar(30) DEFAULT NULL,
  `servicio` varchar(80) DEFAULT NULL,
  `cantMes` int(11) DEFAULT NULL,
  `metodoPago` varchar(50) DEFAULT NULL,
  `regPago` tinyint(1) NOT NULL,
  `IdCliente` int(11) NOT NULL,
  `Num_Hab` int(11) NOT NULL,
  `IdUsuario` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Estructura de tabla para la tabla `resp_alquiler`
--

CREATE TABLE `resp_alquiler` (
  `idResAlq` int(11) NOT NULL,
  `IdReserva` int(11) NOT NULL,
  `fechEntrada` date NOT NULL,
  `horaEntrada` time NOT NULL,
  `fechSalida` date NOT NULL,
  `horaSalida` time NOT NULL,
  `costoAlojamiento` decimal(18,2) DEFAULT NULL,
  `numHab` int(11) NOT NULL,
  `IdCliente` int(11) NOT NULL,
  `IdUsuario` int(11) NOT NULL,
  `motElim` text COLLATE latin1_spanish_ci NOT NULL,
  `fechElim` date NOT NULL,
  `horaElim` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `resp_venta`
--

CREATE TABLE `resp_venta` (
  `idResVen` int(11) NOT NULL,
  `nroVen` int(11) NOT NULL,
  `IdCliente` int(11) NOT NULL,
  `fecVen` datetime NOT NULL,
  `totVen` decimal(18,2) NOT NULL,
  `fechElimVen` datetime NOT NULL,
  `IdUsuario` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `resp_ventahos`
--

CREATE TABLE `resp_ventahos` (
  `idResVenH` int(11) NOT NULL,
  `nroCon` int(11) NOT NULL,
  `Num_Hab` int(11) NOT NULL,
  `fecVenH` datetime NOT NULL,
  `totVenH` decimal(18,2) NOT NULL,
  `fechElimVenH` datetime NOT NULL,
  `IdUsuario` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Estructura de tabla para la tabla `servicio`
--

CREATE TABLE `servicio` (
  `idServicio` int(11) NOT NULL,
  `nombreS` varchar(191) COLLATE latin1_spanish_ci NOT NULL,
  `precioS` decimal(18,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

INSERT INTO `servicio` (`idServicio`, `nombreS`, `precioS`) VALUES
(5, 'Persona Extra', '120.00');

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

--
-- Estructura de tabla para la tabla `surtir`
--

CREATE TABLE `surtir` (
  `idSurtir` int(11) NOT NULL,
  `IdProducto` int(11) NOT NULL,
  `cantSur` int(11) NOT NULL,
  `fecSur` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

--
-- Estructura de tabla para la tabla `tipohabitacion`
--

CREATE TABLE `tipohabitacion` (
  `IdTipoHabitacion` int(11) NOT NULL,
  `Denominacion` varchar(50) NOT NULL,
  `Descripcion` varchar(250) DEFAULT NULL,
  `precioHora3` decimal(18,2) NOT NULL,
  `precioHora` decimal(18,2) DEFAULT NULL,
  `precioHora6` decimal(18,2) DEFAULT NULL,
  `precioHora8` decimal(18,2) DEFAULT NULL,
  `precioNoche` decimal(18,2) DEFAULT NULL,
  `precioMes` decimal(18,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `tipohabitacion` (`IdTipoHabitacion`, `Denominacion`, `Descripcion`, `precioHora3`, `precioHora`, `precioHora6`, `precioHora8`, `precioNoche`, `precioMes`) VALUES
(11, 'Sencilla Ventilador', 'Sencilla Ventilador', '0.00', '160.00', '0.00', '0.00', '260.00', '0.00'),
(12, 'Sencilla AA', 'Sencilla AA', '0.00', '180.00', '0.00', '0.00', '280.00', '0.00'),
(13, 'Sencilla Remodelada', 'Sencilla Remodelada', '0.00', '200.00', '0.00', '0.00', '0.00', '0.00'),
(14, 'Doble Sencilla', 'Doble Sencilla', '0.00', '0.00', '0.00', '0.00', '300.00', '0.00');

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

--
-- Estructura de tabla para la tabla `tipo_documento`
--

CREATE TABLE `tipo_documento` (
  `idTipDoc` int(11) NOT NULL,
  `nomTipDoc` varchar(45) COLLATE latin1_spanish_ci NOT NULL,
  `longitud` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

INSERT INTO `tipo_documento` (`idTipDoc`, `nomTipDoc`, `longitud`) VALUES
(1, 'LICENCIA', 9),
(2, 'INE', 10),
(3, 'PASAPORTE', 15);

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

--
-- Estructura de tabla para la tabla `tipo_limpieza`
--

CREATE TABLE `tipo_limpieza` (
  `idLim` int(11) NOT NULL,
  `nomLim` varchar(181) COLLATE latin1_spanish_ci NOT NULL,
  `tieLim` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

INSERT INTO `tipo_limpieza` (`idLim`, `nomLim`, `tieLim`) VALUES
(1, 'PROFUNDA', 60),
(2, 'RAPIDA', 30);

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

--
-- Estructura de tabla para la tabla `unidad_medida`
--

CREATE TABLE `unidad_medida` (
  `idUnidadMedida` int(11) NOT NULL,
  `nombreUM` varchar(191) COLLATE latin1_spanish_ci NOT NULL,
  `valorUM` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

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

INSERT INTO `unidad_medida` (`idUnidadMedida`, `nombreUM`, `valorUM`) VALUES
(7, 'PZ', 1),
(8, 'DOCENA', 12);

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

--
-- Estructura de tabla para la tabla `usuario`
--

CREATE TABLE `usuario` (
  `IdUsuario` int(11) NOT NULL,
  `NumDocumento` varchar(15) NOT NULL,
  `Nombre` varchar(50) NOT NULL,
  `Apellido` varchar(50) DEFAULT NULL,
  `password` varchar(250) NOT NULL,
  `Estado` varchar(30) NOT NULL,
  `Celular` varchar(15) DEFAULT NULL,
  `email` varchar(45) NOT NULL,
  `tipo` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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


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

--
-- Estructura de tabla para la tabla `venta`
--

CREATE TABLE `venta` (
  `codVenta` int(11) NOT NULL,
  `fechaVenta` date NOT NULL,
  `horaVenta` time NOT NULL,
  `totalVenta` decimal(18,2) NOT NULL,
  `estado` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `metodoPago` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `IdCliente` int(11) NOT NULL,
  `codCaja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `apartar`
--
ALTER TABLE `apartar`
  ADD PRIMARY KEY (`idApartar`),
  ADD KEY `Num_Hab` (`Num_Hab`),
  ADD KEY `IdCliente` (`IdCliente`),
  ADD KEY `IdUsuario` (`IdUsuario`);

--
-- Indices de la tabla `audiciones`
--
ALTER TABLE `audiciones`
  ADD PRIMARY KEY (`idAudicion`),
  ADD KEY `IdUsuario` (`IdUsuario`),
  ADD KEY `Num_Hab` (`Num_Hab`);

--
-- Indices de la tabla `audiciones_detalle`
--
ALTER TABLE `audiciones_detalle`
  ADD PRIMARY KEY (`idAudicionDetalle`),
  ADD KEY `idAudicion` (`idAudicion`);

--
-- Indices de la tabla `caja`
--
ALTER TABLE `caja`
  ADD PRIMARY KEY (`codCaja`),
  ADD KEY `codUsuario` (`codUsuario`);

--
-- Indices de la tabla `categoria`
--
ALTER TABLE `categoria`
  ADD PRIMARY KEY (`IdCategoria`);

--
-- Indices de la tabla `categoria_aud`
--
ALTER TABLE `categoria_aud`
  ADD PRIMARY KEY (`idCatAud`);

--
-- Indices de la tabla `cliente`
--
ALTER TABLE `cliente`
  ADD PRIMARY KEY (`IdCliente`),
  ADD UNIQUE KEY `uq_cliente_documento_normalizado` (`documento_normalizado`),
  ADD UNIQUE KEY `uq_cliente_correo_normalizado` (`correo_normalizado`),
  ADD UNIQUE KEY `uq_cliente_placa_normalizada` (`placa_normalizada`),
  ADD UNIQUE KEY `uq_cliente_nombre_normalizado` (`nombre_normalizado`),
  ADD KEY `idx_cliente_celular_normalizado` (`celular_normalizado`);

--
-- Indices de la tabla `consulta_aud`
--
ALTER TABLE `consulta_aud`
  ADD PRIMARY KEY (`idConAud`),
  ADD KEY `idCatAud` (`idCatAud`);

--
-- Indices de la tabla `consumo`
--
ALTER TABLE `consumo`
  ADD PRIMARY KEY (`IdConsumo`),
  ADD KEY `fk_1` (`IdProducto`),
  ADD KEY `codCaja` (`codCaja`),
  ADD KEY `IdReserva` (`IdReserva`);

--
-- Indices de la tabla `datos_hotel`
--
ALTER TABLE `datos_hotel`
  ADD PRIMARY KEY (`IdHotel`);

--
-- Indices de la tabla `detalle_ingreso`
--
ALTER TABLE `detalle_ingreso`
  ADD PRIMARY KEY (`iddetalle_ingreso`),
  ADD KEY `idingreso` (`idingreso`),
  ADD KEY `IdProducto` (`IdProducto`);

--
-- Indices de la tabla `detalle_servicio`
--
ALTER TABLE `detalle_servicio`
  ADD PRIMARY KEY (`idDetalle_servicio`),
  ADD KEY `idServicio` (`idServicio`),
  ADD KEY `IdReserva` (`IdReserva`);

--
-- Indices de la tabla `detalle_venta`
--
ALTER TABLE `detalle_venta`
  ADD PRIMARY KEY (`codDetalle_venta`),
  ADD KEY `codVenta` (`codVenta`),
  ADD KEY `IdProducto` (`IdProducto`);

--
-- Indices de la tabla `det_limpieza`
--
ALTER TABLE `det_limpieza`
  ADD PRIMARY KEY (`idDetLim`),
  ADD KEY `idPer` (`idPer`),
  ADD KEY `idLim` (`idLim`),
  ADD KEY `Num_Hab` (`Num_Hab`),
  ADD KEY `idx_det_limpieza_reserva` (`IdReserva`),
  ADD KEY `idx_det_limpieza_fecha_tipo` (`fechaDetLim`,`tipo_operacion`),
  ADD UNIQUE KEY `uq_aseo_estancia_diario` (`IdReserva`,`fechaDetLim`,`tipo_operacion`);

--
-- Indices de la tabla `egreso_caja`
--
ALTER TABLE `egreso_caja`
  ADD PRIMARY KEY (`codEgreso`),
  ADD KEY `codUsuario` (`codUsuario`),
  ADD KEY `codCaja` (`codCaja`);

--
-- Indices de la tabla `habitacion`
--
ALTER TABLE `habitacion`
  ADD PRIMARY KEY (`Num_Hab`),
  ADD KEY `fk_3` (`IdTipoHabitacion`),
  ADD KEY `fk_4` (`IdNivel`);

--
-- Indices de la tabla `ingreso`
--
ALTER TABLE `ingreso`
  ADD PRIMARY KEY (`idingreso`),
  ADD KEY `idpro` (`idpro`),
  ADD KEY `IdUsuario` (`IdUsuario`);

--
-- Indices de la tabla `ingreso_caja`
--
ALTER TABLE `ingreso_caja`
  ADD PRIMARY KEY (`codIngreso`),
  ADD KEY `codUsuario` (`codUsuario`),
  ADD KEY `codCaja` (`codCaja`);

--
-- Indices de la tabla `mantenimiento`
--
ALTER TABLE `mantenimiento`
  ADD PRIMARY KEY (`idMantenimiento`);

--
-- Indices de la tabla `nivel`
--
ALTER TABLE `nivel`
  ADD PRIMARY KEY (`IdNivel`);

--
-- Indices de la tabla `observacion`
--
ALTER TABLE `observacion`
  ADD PRIMARY KEY (`idObservacion`),
  ADD KEY `IdCliente` (`IdCliente`);

--
-- Indices de la tabla `pago`
--
ALTER TABLE `pago`
  ADD PRIMARY KEY (`IdPago`),
  ADD KEY `IdReserva` (`IdReserva`),
  ADD KEY `codCaja` (`codCaja`);

--
-- Indices de la tabla `personal`
--
ALTER TABLE `personal`
  ADD PRIMARY KEY (`idPer`);

--
-- Indices de la tabla `producto`
--
ALTER TABLE `producto`
  ADD PRIMARY KEY (`IdProducto`),
  ADD UNIQUE KEY `uq_producto_barcode` (`barcode`),
  ADD UNIQUE KEY `uq_producto_internal_code` (`internal_code`),
  ADD KEY `idx_producto_nombre_stock` (`NombProducto`,`stock`),
  ADD KEY `fk_6` (`IdCategoria`);

--
-- Indices de la tabla `proveedor`
--
ALTER TABLE `proveedor`
  ADD PRIMARY KEY (`idpro`);

--
-- Indices de la tabla `renovacion`
--
ALTER TABLE `renovacion`
  ADD PRIMARY KEY (`idRenovacion`),
  ADD KEY `IdReserva` (`IdReserva`),
  ADD KEY `codCaja` (`codCaja`);

--
-- Indices de la tabla `reserva`
--
ALTER TABLE `reserva`
  ADD PRIMARY KEY (`IdReserva`),
  ADD KEY `fk_7` (`IdCliente`),
  ADD KEY `fk_8` (`Num_Hab`),
  ADD KEY `fk_9` (`IdUsuario`);

--
-- Indices de la tabla `resp_alquiler`
--
ALTER TABLE `resp_alquiler`
  ADD PRIMARY KEY (`idResAlq`),
  ADD KEY `IdCliente` (`IdCliente`),
  ADD KEY `IdUsuario` (`IdUsuario`);

--
-- Indices de la tabla `resp_venta`
--
ALTER TABLE `resp_venta`
  ADD PRIMARY KEY (`idResVen`),
  ADD KEY `IdCliente` (`IdCliente`),
  ADD KEY `IdUsuario` (`IdUsuario`);

--
-- Indices de la tabla `resp_ventahos`
--
ALTER TABLE `resp_ventahos`
  ADD PRIMARY KEY (`idResVenH`),
  ADD KEY `IdUsuario` (`IdUsuario`);

--
-- Indices de la tabla `servicio`
--
ALTER TABLE `servicio`
  ADD PRIMARY KEY (`idServicio`);

--
-- Indices de la tabla `surtir`
--
ALTER TABLE `surtir`
  ADD PRIMARY KEY (`idSurtir`);

--
-- Indices de la tabla `tipohabitacion`
--
ALTER TABLE `tipohabitacion`
  ADD PRIMARY KEY (`IdTipoHabitacion`);

--
-- Indices de la tabla `tipo_documento`
--
ALTER TABLE `tipo_documento`
  ADD PRIMARY KEY (`idTipDoc`);

--
-- Indices de la tabla `tipo_limpieza`
--
ALTER TABLE `tipo_limpieza`
  ADD PRIMARY KEY (`idLim`);

--
-- Indices de la tabla `unidad_medida`
--
ALTER TABLE `unidad_medida`
  ADD PRIMARY KEY (`idUnidadMedida`);

--
-- Indices de la tabla `usuario`
--
ALTER TABLE `usuario`
  ADD PRIMARY KEY (`IdUsuario`);

--
-- Indices de la tabla `venta`
--
ALTER TABLE `venta`
  ADD PRIMARY KEY (`codVenta`),
  ADD KEY `IdCliente` (`IdCliente`),
  ADD KEY `codCaja` (`codCaja`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `apartar`
--
ALTER TABLE `apartar`
  MODIFY `idApartar` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `audiciones`
--
ALTER TABLE `audiciones`
  MODIFY `idAudicion` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT de la tabla `audiciones_detalle`
--
ALTER TABLE `audiciones_detalle`
  MODIFY `idAudicionDetalle` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=485;

--
-- AUTO_INCREMENT de la tabla `caja`
--
ALTER TABLE `caja`
  MODIFY `codCaja` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=487;

--
-- AUTO_INCREMENT de la tabla `categoria`
--
ALTER TABLE `categoria`
  MODIFY `IdCategoria` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT de la tabla `categoria_aud`
--
ALTER TABLE `categoria_aud`
  MODIFY `idCatAud` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de la tabla `cliente`
--
ALTER TABLE `cliente`
  MODIFY `IdCliente` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4378;

--
-- AUTO_INCREMENT de la tabla `consulta_aud`
--
ALTER TABLE `consulta_aud`
  MODIFY `idConAud` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT de la tabla `consumo`
--
ALTER TABLE `consumo`
  MODIFY `IdConsumo` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2433;

--
-- AUTO_INCREMENT de la tabla `datos_hotel`
--
ALTER TABLE `datos_hotel`
  MODIFY `IdHotel` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT de la tabla `detalle_ingreso`
--
ALTER TABLE `detalle_ingreso`
  MODIFY `iddetalle_ingreso` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `detalle_servicio`
--
ALTER TABLE `detalle_servicio`
  MODIFY `idDetalle_servicio` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=95;

--
-- AUTO_INCREMENT de la tabla `detalle_venta`
--
ALTER TABLE `detalle_venta`
  MODIFY `codDetalle_venta` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2476;

--
-- AUTO_INCREMENT de la tabla `det_limpieza`
--
ALTER TABLE `det_limpieza`
  MODIFY `idDetLim` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4076;

--
-- AUTO_INCREMENT de la tabla `egreso_caja`
--
ALTER TABLE `egreso_caja`
  MODIFY `codEgreso` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=154;

--
-- AUTO_INCREMENT de la tabla `ingreso`
--
ALTER TABLE `ingreso`
  MODIFY `idingreso` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `ingreso_caja`
--
ALTER TABLE `ingreso_caja`
  MODIFY `codIngreso` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT de la tabla `mantenimiento`
--
ALTER TABLE `mantenimiento`
  MODIFY `idMantenimiento` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT de la tabla `nivel`
--
ALTER TABLE `nivel`
  MODIFY `IdNivel` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de la tabla `observacion`
--
ALTER TABLE `observacion`
  MODIFY `idObservacion` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT de la tabla `pago`
--
ALTER TABLE `pago`
  MODIFY `IdPago` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4129;

--
-- AUTO_INCREMENT de la tabla `personal`
--
ALTER TABLE `personal`
  MODIFY `idPer` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT de la tabla `producto`
--
ALTER TABLE `producto`
  MODIFY `IdProducto` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=157;

--
-- AUTO_INCREMENT de la tabla `proveedor`
--
ALTER TABLE `proveedor`
  MODIFY `idpro` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT de la tabla `renovacion`
--
ALTER TABLE `renovacion`
  MODIFY `idRenovacion` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=648;

--
-- AUTO_INCREMENT de la tabla `reserva`
--
ALTER TABLE `reserva`
  MODIFY `IdReserva` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4133;

--
-- AUTO_INCREMENT de la tabla `resp_alquiler`
--
ALTER TABLE `resp_alquiler`
  MODIFY `idResAlq` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;

--
-- AUTO_INCREMENT de la tabla `resp_venta`
--
ALTER TABLE `resp_venta`
  MODIFY `idResVen` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT de la tabla `resp_ventahos`
--
ALTER TABLE `resp_ventahos`
  MODIFY `idResVenH` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `servicio`
--
ALTER TABLE `servicio`
  MODIFY `idServicio` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT de la tabla `surtir`
--
ALTER TABLE `surtir`
  MODIFY `idSurtir` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6316;

--
-- AUTO_INCREMENT de la tabla `tipohabitacion`
--
ALTER TABLE `tipohabitacion`
  MODIFY `IdTipoHabitacion` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT de la tabla `tipo_documento`
--
ALTER TABLE `tipo_documento`
  MODIFY `idTipDoc` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT de la tabla `tipo_limpieza`
--
ALTER TABLE `tipo_limpieza`
  MODIFY `idLim` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `unidad_medida`
--
ALTER TABLE `unidad_medida`
  MODIFY `idUnidadMedida` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT de la tabla `usuario`
--
ALTER TABLE `usuario`
  MODIFY `IdUsuario` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT de la tabla `venta`
--
ALTER TABLE `venta`
  MODIFY `codVenta` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2330;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `apartar`
--
ALTER TABLE `apartar`
  ADD CONSTRAINT `apartar_ibfk_1` FOREIGN KEY (`Num_Hab`) REFERENCES `habitacion` (`Num_Hab`),
  ADD CONSTRAINT `apartar_ibfk_2` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`),
  ADD CONSTRAINT `apartar_ibfk_3` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `audiciones`
--
ALTER TABLE `audiciones`
  ADD CONSTRAINT `audiciones_ibfk_1` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`),
  ADD CONSTRAINT `audiciones_ibfk_2` FOREIGN KEY (`Num_Hab`) REFERENCES `habitacion` (`Num_Hab`);

--
-- Filtros para la tabla `audiciones_detalle`
--
ALTER TABLE `audiciones_detalle`
  ADD CONSTRAINT `audiciones_detalle_ibfk_1` FOREIGN KEY (`idAudicion`) REFERENCES `audiciones` (`idAudicion`);

--
-- Filtros para la tabla `caja`
--
ALTER TABLE `caja`
  ADD CONSTRAINT `caja_ibfk_1` FOREIGN KEY (`codUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `consulta_aud`
--
ALTER TABLE `consulta_aud`
  ADD CONSTRAINT `consulta_aud_ibfk_1` FOREIGN KEY (`idCatAud`) REFERENCES `categoria_aud` (`idCatAud`);

--
-- Filtros para la tabla `consumo`
--
ALTER TABLE `consumo`
  ADD CONSTRAINT `consumo_ibfk_1` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`),
  ADD CONSTRAINT `consumo_ibfk_2` FOREIGN KEY (`IdReserva`) REFERENCES `reserva` (`IdReserva`),
  ADD CONSTRAINT `fk_1` FOREIGN KEY (`IdProducto`) REFERENCES `producto` (`IdProducto`);

--
-- Filtros para la tabla `detalle_ingreso`
--
ALTER TABLE `detalle_ingreso`
  ADD CONSTRAINT `detalle_ingreso_ibfk_1` FOREIGN KEY (`idingreso`) REFERENCES `ingreso` (`idingreso`),
  ADD CONSTRAINT `detalle_ingreso_ibfk_2` FOREIGN KEY (`IdProducto`) REFERENCES `producto` (`IdProducto`);

--
-- Filtros para la tabla `detalle_servicio`
--
ALTER TABLE `detalle_servicio`
  ADD CONSTRAINT `detalle_servicio_ibfk_1` FOREIGN KEY (`idServicio`) REFERENCES `servicio` (`idServicio`),
  ADD CONSTRAINT `detalle_servicio_ibfk_2` FOREIGN KEY (`IdReserva`) REFERENCES `reserva` (`IdReserva`);

--
-- Filtros para la tabla `detalle_venta`
--
ALTER TABLE `detalle_venta`
  ADD CONSTRAINT `detalle_venta_ibfk_1` FOREIGN KEY (`codVenta`) REFERENCES `venta` (`codVenta`),
  ADD CONSTRAINT `detalle_venta_ibfk_2` FOREIGN KEY (`IdProducto`) REFERENCES `producto` (`IdProducto`);

--
-- Filtros para la tabla `det_limpieza`
--
ALTER TABLE `det_limpieza`
  ADD CONSTRAINT `det_limpieza_ibfk_1` FOREIGN KEY (`idPer`) REFERENCES `personal` (`idPer`),
  ADD CONSTRAINT `det_limpieza_ibfk_2` FOREIGN KEY (`idLim`) REFERENCES `tipo_limpieza` (`idLim`),
  ADD CONSTRAINT `det_limpieza_ibfk_3` FOREIGN KEY (`Num_Hab`) REFERENCES `habitacion` (`Num_Hab`);

--
-- Filtros para la tabla `egreso_caja`
--
ALTER TABLE `egreso_caja`
  ADD CONSTRAINT `egreso_caja_ibfk_1` FOREIGN KEY (`codUsuario`) REFERENCES `usuario` (`IdUsuario`),
  ADD CONSTRAINT `egreso_caja_ibfk_2` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`);

--
-- Filtros para la tabla `habitacion`
--
ALTER TABLE `habitacion`
  ADD CONSTRAINT `fk_3` FOREIGN KEY (`IdTipoHabitacion`) REFERENCES `tipohabitacion` (`IdTipoHabitacion`),
  ADD CONSTRAINT `fk_4` FOREIGN KEY (`IdNivel`) REFERENCES `nivel` (`IdNivel`);

--
-- Filtros para la tabla `ingreso`
--
ALTER TABLE `ingreso`
  ADD CONSTRAINT `ingreso_ibfk_1` FOREIGN KEY (`idpro`) REFERENCES `proveedor` (`idpro`),
  ADD CONSTRAINT `ingreso_ibfk_2` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `ingreso_caja`
--
ALTER TABLE `ingreso_caja`
  ADD CONSTRAINT `ingreso_caja_ibfk_1` FOREIGN KEY (`codUsuario`) REFERENCES `usuario` (`IdUsuario`),
  ADD CONSTRAINT `ingreso_caja_ibfk_2` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`);

--
-- Filtros para la tabla `observacion`
--
ALTER TABLE `observacion`
  ADD CONSTRAINT `observacion_ibfk_1` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`);

--
-- Filtros para la tabla `pago`
--
ALTER TABLE `pago`
  ADD CONSTRAINT `pago_ibfk_1` FOREIGN KEY (`IdReserva`) REFERENCES `reserva` (`IdReserva`),
  ADD CONSTRAINT `pago_ibfk_2` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`);

--
-- Filtros para la tabla `producto`
--
ALTER TABLE `producto`
  ADD CONSTRAINT `fk_6` FOREIGN KEY (`IdCategoria`) REFERENCES `categoria` (`IdCategoria`);

--
-- Filtros para la tabla `renovacion`
--
ALTER TABLE `renovacion`
  ADD CONSTRAINT `renovacion_ibfk_1` FOREIGN KEY (`IdReserva`) REFERENCES `reserva` (`IdReserva`),
  ADD CONSTRAINT `renovacion_ibfk_2` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`);

--
-- Filtros para la tabla `reserva`
--
ALTER TABLE `reserva`
  ADD CONSTRAINT `fk_7` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`),
  ADD CONSTRAINT `fk_8` FOREIGN KEY (`Num_Hab`) REFERENCES `habitacion` (`Num_Hab`),
  ADD CONSTRAINT `fk_9` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `resp_alquiler`
--
ALTER TABLE `resp_alquiler`
  ADD CONSTRAINT `resp_alquiler_ibfk_1` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`),
  ADD CONSTRAINT `resp_alquiler_ibfk_2` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `resp_venta`
--
ALTER TABLE `resp_venta`
  ADD CONSTRAINT `resp_venta_ibfk_1` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`),
  ADD CONSTRAINT `resp_venta_ibfk_2` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `resp_ventahos`
--
ALTER TABLE `resp_ventahos`
  ADD CONSTRAINT `resp_ventaHos_ibfk_1` FOREIGN KEY (`IdUsuario`) REFERENCES `usuario` (`IdUsuario`);

--
-- Filtros para la tabla `venta`
--
ALTER TABLE `venta`
  ADD CONSTRAINT `venta_ibfk_1` FOREIGN KEY (`IdCliente`) REFERENCES `cliente` (`IdCliente`),
  ADD CONSTRAINT `venta_ibfk_2` FOREIGN KEY (`codCaja`) REFERENCES `caja` (`codCaja`);

-- SIHO PMS MASTER - FASE 4
-- Reservas robustas y arquitectura preparada para canales externos.

ALTER TABLE `reserva`
  ADD COLUMN `start_at` DATETIME NULL AFTER `entry_date`,
  ADD COLUMN `end_at` DATETIME NULL AFTER `start_at`,
  ADD COLUMN `source` VARCHAR(30) NOT NULL DEFAULT 'DIRECT' AFTER `IdUsuario`,
  ADD COLUMN `external_reservation_id` VARCHAR(120) NULL AFTER `source`,
  ADD COLUMN `external_status` VARCHAR(30) NULL AFTER `external_reservation_id`,
  ADD COLUMN `channel_payload_hash` CHAR(64) NULL AFTER `external_status`,
  ADD UNIQUE KEY `uq_reserva_source_external` (`source`, `external_reservation_id`),
  ADD KEY `idx_reserva_room_interval` (`Num_Hab`, `start_at`, `end_at`),
  ADD KEY `idx_reserva_status_dates` (`Estado`, `FechReserva`, `FechSalida`);

CREATE TABLE IF NOT EXISTS `channel_room_mappings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider` VARCHAR(30) NOT NULL,
  `external_room_id` VARCHAR(120) NOT NULL,
  `external_rate_id` VARCHAR(120) NULL,
  `IdTipoHabitacion` INT(11) NULL,
  `Num_Hab` INT(11) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_channel_room_mapping` (`provider`, `external_room_id`, `external_rate_id`),
  KEY `idx_channel_room_type` (`IdTipoHabitacion`),
  KEY `idx_channel_room_number` (`Num_Hab`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channel_reservation_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider` VARCHAR(30) NOT NULL,
  `external_reservation_id` VARCHAR(120) NOT NULL,
  `event_type` VARCHAR(30) NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `error_message` TEXT NULL,
  `processed_at` DATETIME NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_channel_event_external` (`provider`, `external_reservation_id`),
  KEY `idx_channel_event_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
CREATE TABLE IF NOT EXISTS `siho_operation_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `operation_token` char(36) NOT NULL,
  `operation_type` varchar(60) NOT NULL,
  `user_id` int DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'PROCESSING',
  `result_id` bigint DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_operation` (`operation_token`,`operation_type`),
  KEY `idx_siho_operation_status` (`status`),
  KEY `idx_siho_operation_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `caja`
  ADD COLUMN `open_guard` tinyint unsigned DEFAULT NULL AFTER `codUsuario`,
  ADD UNIQUE KEY `uq_caja_single_open` (`open_guard`);

UPDATE `caja` SET `open_guard` = NULL WHERE `montoCierre` IS NOT NULL;
SET @open_box := (SELECT MIN(`codCaja`) FROM `caja` WHERE `montoCierre` IS NULL);
UPDATE `caja` SET `open_guard` = IF(`codCaja` = @open_box, 1, NULL) WHERE `montoCierre` IS NULL;

-- SIHO PHASE 7: bitácora de retención automática
CREATE TABLE IF NOT EXISTS `siho_retention_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `started_at` DATETIME NOT NULL,
  `finished_at` DATETIME NULL,
  `cutoff_at` DATETIME NOT NULL,
  `retention_years` TINYINT UNSIGNED NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `manual` TINYINT(1) NOT NULL DEFAULT 0,
  `user_id` INT NULL,
  `archive_path` VARCHAR(255) NULL,
  `records_archived` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `records_deleted` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `details` LONGTEXT NULL,
  `error_message` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_retention_status_started` (`status`, `started_at`),
  KEY `idx_retention_cutoff` (`cutoff_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- SIHO PMS PHASE 8 - CONTROL DE SESIONES SIMULTÁNEAS
CREATE TABLE IF NOT EXISTS `siho_user_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` VARCHAR(191) NOT NULL,
  `user_id` INT NOT NULL,
  `role` VARCHAR(40) NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(500) NULL,
  `last_activity_at` DATETIME NOT NULL,
  `revoked_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_user_session_id` (`session_id`),
  KEY `idx_siho_user_sessions_user_active` (`user_id`, `revoked_at`, `last_activity_at`),
  CONSTRAINT `fk_siho_user_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `usuario` (`IdUsuario`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `room_rate_plans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `room_type_id` int NOT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(80) NOT NULL,
  `rate_type` enum('HOURS','NIGHT','DAY','MONTH') NOT NULL,
  `duration_value` int unsigned NOT NULL DEFAULT 1,
  `duration_unit` enum('HOUR','DAY','MONTH') NOT NULL,
  `price` decimal(18,2) NOT NULL DEFAULT 0.00,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `checkout_time` time DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_room_rate_code` (`room_type_id`,`code`),
  KEY `idx_room_rate_active` (`room_type_id`,`active`,`sort_order`),
  CONSTRAINT `fk_room_rate_type` FOREIGN KEY (`room_type_id`) REFERENCES `tipohabitacion` (`IdTipoHabitacion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `reserva`
  ADD COLUMN `rate_plan_id` bigint unsigned NULL,
  ADD COLUMN `rate_plan_code` varchar(50) NULL,
  ADD COLUMN `rate_plan_name` varchar(80) NULL,
  ADD COLUMN `rate_plan_type` varchar(20) NULL,
  ADD COLUMN `rate_duration_value` int NULL,
  ADD COLUMN `rate_unit_price` decimal(18,2) NULL;

ALTER TABLE `renovacion`
  ADD COLUMN `rate_plan_id` bigint unsigned NULL,
  ADD COLUMN `rate_plan_code` varchar(50) NULL,
  ADD COLUMN `rate_plan_type` varchar(20) NULL,
  ADD COLUMN `rate_duration_value` int NULL,
  ADD COLUMN `rate_unit_price` decimal(18,2) NULL;

ALTER TABLE `apartar`
  ADD COLUMN `rate_plan_id` bigint unsigned NULL,
  ADD COLUMN `rate_plan_code` varchar(50) NULL,
  ADD COLUMN `rate_plan_type` varchar(20) NULL,
  ADD COLUMN `rate_duration_value` int NULL,
  ADD COLUMN `rate_unit_price` decimal(18,2) NULL;

INSERT IGNORE INTO `room_rate_plans`
(`room_type_id`,`code`,`name`,`rate_type`,`duration_value`,`duration_unit`,`price`,`active`,`sort_order`,`checkout_time`,`created_at`,`updated_at`)
SELECT `IdTipoHabitacion`,'H3','3 HORAS','HOURS',3,'HOUR',`precioHora3`,IF(`precioHora3`>0,1,0),10,NULL,NOW(),NOW() FROM `tipohabitacion`;
INSERT IGNORE INTO `room_rate_plans`
SELECT NULL, `IdTipoHabitacion`,'H4','4 HORAS','HOURS',4,'HOUR',`precioHora`,IF(`precioHora`>0,1,0),20,NULL,NOW(),NOW() FROM `tipohabitacion`;
INSERT IGNORE INTO `room_rate_plans`
SELECT NULL, `IdTipoHabitacion`,'H6','6 HORAS','HOURS',6,'HOUR',`precioHora6`,IF(`precioHora6`>0,1,0),30,NULL,NOW(),NOW() FROM `tipohabitacion`;
INSERT IGNORE INTO `room_rate_plans`
SELECT NULL, `IdTipoHabitacion`,'H8','8 HORAS','HOURS',8,'HOUR',`precioHora8`,IF(`precioHora8`>0,1,0),40,NULL,NOW(),NOW() FROM `tipohabitacion`;
INSERT IGNORE INTO `room_rate_plans`
SELECT NULL, `IdTipoHabitacion`,'NIGHT','TODA LA NOCHE','NIGHT',1,'DAY',`precioNoche`,IF(`precioNoche`>0,1,0),50,'12:00:00',NOW(),NOW() FROM `tipohabitacion`;
INSERT IGNORE INTO `room_rate_plans`
SELECT NULL, `IdTipoHabitacion`,'MONTH','POR MES','MONTH',1,'MONTH',`precioMes`,IF(`precioMes`>0,1,0),60,'12:00:00',NOW(),NOW() FROM `tipohabitacion`;


-- SIHO PMS PHASE 14 - BITÁCORA DE AUDITORÍA
CREATE TABLE IF NOT EXISTS `siho_audit_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_uuid` char(36) NOT NULL,
  `event_type` varchar(40) NOT NULL,
  `module` varchar(80) DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `auditable_type` varchar(255) DEFAULT NULL,
  `auditable_id` varchar(100) DEFAULT NULL,
  `table_name` varchar(128) DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `user_role` varchar(80) DEFAULT NULL,
  `session_id` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `route_name` varchar(255) DEFAULT NULL,
  `request_method` varchar(10) DEFAULT NULL,
  `request_path` varchar(1000) DEFAULT NULL,
  `before_values` longtext DEFAULT NULL,
  `after_values` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_audit_event_uuid` (`event_uuid`),
  KEY `idx_siho_audit_created_at` (`created_at`),
  KEY `idx_siho_audit_user` (`user_id`,`created_at`),
  KEY `idx_siho_audit_module` (`module`,`created_at`),
  KEY `idx_siho_audit_record` (`table_name`,`auditable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- SIHO PMS PHASE 15 - PAPELERA INTELIGENTE
-- Ejecutar una sola vez por instalación existente.

ALTER TABLE `cliente` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_cliente_deleted_at` (`deleted_at`);
ALTER TABLE `producto` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_producto_deleted_at` (`deleted_at`);
ALTER TABLE `proveedor` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_proveedor_deleted_at` (`deleted_at`);
ALTER TABLE `habitacion` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_habitacion_deleted_at` (`deleted_at`);
ALTER TABLE `tipohabitacion` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_tipohabitacion_deleted_at` (`deleted_at`);
ALTER TABLE `room_rate_plans` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_room_rate_deleted_at` (`deleted_at`);
ALTER TABLE `servicio` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_servicio_deleted_at` (`deleted_at`);
ALTER TABLE `usuario` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_usuario_deleted_at` (`deleted_at`);
ALTER TABLE `mantenimiento` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_mantenimiento_deleted_at` (`deleted_at`);
ALTER TABLE `categoria` ADD COLUMN `deleted_at` timestamp NULL DEFAULT NULL, ADD COLUMN `deleted_by` int NULL, ADD COLUMN `delete_reason` varchar(500) NULL, ADD INDEX `idx_categoria_deleted_at` (`deleted_at`);

CREATE TABLE `siho_trash_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `model_type` varchar(191) NOT NULL,
  `table_name` varchar(100) NOT NULL,
  `record_id` varchar(100) NOT NULL,
  `record_label` varchar(255) NULL,
  `action` varchar(30) NOT NULL,
  `reason` varchar(500) NULL,
  `performed_by` int NULL,
  `performed_role` varchar(50) NULL,
  `ip_address` varchar(45) NULL,
  `metadata` longtext NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_trash_record` (`table_name`,`record_id`),
  KEY `idx_trash_action_date` (`action`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SIHO PMS PHASE 16 - CONFIGURACIÓN CENTRALIZADA Y UUID
CREATE TABLE IF NOT EXISTS `siho_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `setting_group` varchar(60) NOT NULL DEFAULT 'general',
  `setting_key` varchar(150) NOT NULL,
  `setting_value` longtext NULL,
  `setting_type` varchar(20) NOT NULL DEFAULT 'string',
  `is_secret` tinyint(1) NOT NULL DEFAULT 0,
  `updated_by` bigint unsigned NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_settings_uuid` (`uuid`),
  UNIQUE KEY `uq_siho_settings_key` (`setting_key`),
  KEY `idx_siho_settings_group` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `cliente` ADD COLUMN `uuid` char(36) NULL AFTER `IdCliente`, ADD UNIQUE KEY `uq_cliente_uuid` (`uuid`);
ALTER TABLE `reserva` ADD COLUMN `uuid` char(36) NULL AFTER `IdReserva`, ADD UNIQUE KEY `uq_reserva_uuid` (`uuid`);
ALTER TABLE `venta` ADD COLUMN `uuid` char(36) NULL AFTER `codVenta`, ADD UNIQUE KEY `uq_venta_uuid` (`uuid`);
ALTER TABLE `pago` ADD COLUMN `uuid` char(36) NULL AFTER `IdPago`, ADD UNIQUE KEY `uq_pago_uuid` (`uuid`);
ALTER TABLE `habitacion` ADD COLUMN `uuid` char(36) NULL AFTER `Num_Hab`, ADD UNIQUE KEY `uq_habitacion_uuid` (`uuid`);
ALTER TABLE `usuario` ADD COLUMN `uuid` char(36) NULL AFTER `IdUsuario`, ADD UNIQUE KEY `uq_usuario_uuid` (`uuid`);
ALTER TABLE `caja` ADD COLUMN `uuid` char(36) NULL AFTER `codCaja`, ADD UNIQUE KEY `uq_caja_uuid` (`uuid`);
ALTER TABLE `producto` ADD COLUMN `uuid` char(36) NULL AFTER `IdProducto`, ADD UNIQUE KEY `uq_producto_uuid` (`uuid`);
CREATE TABLE IF NOT EXISTS `siho_folio_definitions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity` VARCHAR(40) NOT NULL,
  `prefix` VARCHAR(20) NOT NULL DEFAULT '',
  `format` VARCHAR(80) NOT NULL DEFAULT '{PREFIX}-{YEAR}-{SEQ}',
  `padding` TINYINT UNSIGNED NOT NULL DEFAULT 6,
  `reset_policy` ENUM('NEVER','YEARLY','MONTHLY') NOT NULL DEFAULT 'YEARLY',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_siho_folio_entity` (`entity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `siho_folio_sequences` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `definition_id` BIGINT UNSIGNED NOT NULL,
  `period_key` VARCHAR(20) NOT NULL,
  `current_value` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_siho_folio_period` (`definition_id`,`period_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `siho_folio_issued` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `definition_id` BIGINT UNSIGNED NOT NULL,
  `entity` VARCHAR(40) NOT NULL,
  `period_key` VARCHAR(20) NOT NULL,
  `sequence_value` BIGINT UNSIGNED NOT NULL,
  `folio` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_siho_folio_issued` (`folio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO `siho_folio_definitions` (`entity`,`prefix`,`format`,`padding`,`reset_policy`,`active`,`created_at`,`updated_at`) VALUES
('RESERVATION','RES','{PREFIX}-{YEAR}-{SEQ}',6,'YEARLY',1,NOW(),NOW()),
('CUSTOMER','CLI','{PREFIX}-{SEQ}',6,'NEVER',1,NOW(),NOW()),
('SALE','VEN','{PREFIX}-{YEAR}-{SEQ}',6,'YEARLY',1,NOW(),NOW()),
('CASH','CAJ','{PREFIX}-{YEAR}-{SEQ}',6,'YEARLY',1,NOW(),NOW());
ALTER TABLE `reserva` ADD COLUMN `folio` VARCHAR(100) NULL AFTER `uuid`;
ALTER TABLE `cliente` ADD COLUMN `folio` VARCHAR(100) NULL AFTER `uuid`;
ALTER TABLE `venta` ADD COLUMN `folio` VARCHAR(100) NULL AFTER `uuid`;
ALTER TABLE `caja` ADD COLUMN `folio` VARCHAR(100) NULL AFTER `uuid`;
CREATE UNIQUE INDEX `uq_reserva_folio` ON `reserva` (`folio`);
CREATE UNIQUE INDEX `uq_cliente_folio` ON `cliente` (`folio`);
CREATE UNIQUE INDEX `uq_venta_folio` ON `venta` (`folio`);
CREATE UNIQUE INDEX `uq_caja_folio` ON `caja` (`folio`);
CREATE TABLE IF NOT EXISTS `siho_role_module_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` VARCHAR(40) NOT NULL,
  `module_key` VARCHAR(60) NOT NULL,
  `can_view` TINYINT(1) NOT NULL DEFAULT 0,
  `can_create` TINYINT(1) NOT NULL DEFAULT 0,
  `can_update` TINYINT(1) NOT NULL DEFAULT 0,
  `can_delete` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_role_module` (`role`,`module_key`),
  KEY `idx_siho_role_module_role` (`role`),
  KEY `idx_siho_role_module_module` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- SIHO PMS MASTER - PHASE 21
-- Expediente digital: identificación capturada desde teléfono.

CREATE TABLE IF NOT EXISTS `siho_client_capture_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `token_hash` char(64) NOT NULL,
  `public_id` char(36) NOT NULL,
  `document_type` varchar(60) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `expires_at` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `IdCliente` int(11) DEFAULT NULL,
  `front_document_id` bigint unsigned DEFAULT NULL,
  `back_document_id` bigint unsigned DEFAULT NULL,
  `profile_filename` varchar(191) DEFAULT NULL,
  `last_activity_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_client_capture_public_id` (`public_id`),
  KEY `idx_client_capture_expiry` (`status`,`expires_at`),
  KEY `idx_client_capture_client` (`IdCliente`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `client_documents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `IdCliente` int(11) DEFAULT NULL,
  `capture_session_id` bigint unsigned DEFAULT NULL,
  `document_type` varchar(60) NOT NULL,
  `side` varchar(10) NOT NULL,
  `storage_path` varchar(255) NOT NULL,
  `mime_type` varchar(80) NOT NULL DEFAULT 'image/jpeg',
  `size_bytes` int unsigned NOT NULL DEFAULT 0,
  `sha256` char(64) NOT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `source` varchar(30) NOT NULL DEFAULT 'MOBILE_QR',
  `is_profile_source` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_client_document_uuid` (`uuid`),
  KEY `idx_client_documents_client` (`IdCliente`,`document_type`),
  KEY `idx_client_documents_session` (`capture_session_id`),
  KEY `idx_client_documents_hash` (`sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- SIHO PHASE 23: PLATFORM MANIFEST AND INSTALLATION REGISTRY
CREATE TABLE IF NOT EXISTS `siho_installation_registry` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `installation_code` VARCHAR(80) NOT NULL,
  `hotel_code` VARCHAR(80) DEFAULT NULL,
  `module_code` VARCHAR(80) NOT NULL DEFAULT 'siho-pms',
  `installed_version` VARCHAR(80) NOT NULL,
  `build_code` VARCHAR(80) DEFAULT NULL,
  `central_min_version` VARCHAR(80) DEFAULT NULL,
  `license_status` VARCHAR(30) NOT NULL DEFAULT 'UNVERIFIED',
  `last_central_contact_at` DATETIME DEFAULT NULL,
  `metadata` LONGTEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_installation_registry_uuid` (`uuid`),
  UNIQUE KEY `uq_siho_installation_registry_code_module` (`installation_code`,`module_code`),
  KEY `idx_siho_installation_registry_version` (`installed_version`),
  KEY `idx_siho_installation_registry_contact` (`last_central_contact_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `siho_domain_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_uuid` CHAR(36) NOT NULL,
  `event_name` VARCHAR(150) NOT NULL,
  `aggregate_type` VARCHAR(80) NOT NULL,
  `aggregate_id` VARCHAR(100) NULL,
  `installation_uuid` CHAR(36) NULL,
  `user_id` BIGINT NULL,
  `payload` LONGTEXT NOT NULL,
  `occurred_at` DATETIME NOT NULL,
  `published_at` DATETIME NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_error` VARCHAR(1000) NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_domain_events_uuid` (`event_uuid`),
  KEY `idx_siho_domain_events_name_status` (`event_name`,`status`),
  KEY `idx_siho_domain_events_aggregate` (`aggregate_type`,`aggregate_id`),
  KEY `idx_siho_domain_events_occurred` (`occurred_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- RC8 ADMINISTRATIVE DELETION EVENTS
-- SIHO PMS RC8 - ELIMINACIÓN ADMINISTRATIVA LIMITADA A CAJA ACTIVA
-- Idempotente. No elimina información existente.

CREATE TABLE IF NOT EXISTS `siho_financial_deletion_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_uuid` char(36) NOT NULL,
  `entity_type` varchar(30) NOT NULL,
  `entity_id` bigint NOT NULL,
  `cashbox_id` int NOT NULL,
  `reason` varchar(500) NOT NULL,
  `snapshot_json` longtext NOT NULL,
  `deleted_by` int NOT NULL,
  `deleted_by_role` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_siho_financial_deletion_uuid` (`event_uuid`),
  KEY `idx_siho_financial_deletion_entity` (`entity_type`,`entity_id`),
  KEY `idx_siho_financial_deletion_cashbox` (`cashbox_id`,`created_at`),
  KEY `idx_siho_financial_deletion_user` (`deleted_by`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;