USE [master]
GO
/****** Object:  Database [sistema_piter]    Script Date: 26/01/2026 1:16:52 ******/
CREATE DATABASE [sistema_piter]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'sistema_piter', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SERVER\MSSQL\DATA\sistema_piter.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'sistema_piter_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SERVER\MSSQL\DATA\sistema_piter_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [sistema_piter] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [sistema_piter].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [sistema_piter] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [sistema_piter] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [sistema_piter] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [sistema_piter] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [sistema_piter] SET ARITHABORT OFF 
GO
ALTER DATABASE [sistema_piter] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [sistema_piter] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [sistema_piter] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [sistema_piter] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [sistema_piter] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [sistema_piter] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [sistema_piter] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [sistema_piter] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [sistema_piter] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [sistema_piter] SET  DISABLE_BROKER 
GO
ALTER DATABASE [sistema_piter] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [sistema_piter] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [sistema_piter] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [sistema_piter] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [sistema_piter] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [sistema_piter] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [sistema_piter] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [sistema_piter] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [sistema_piter] SET  MULTI_USER 
GO
ALTER DATABASE [sistema_piter] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [sistema_piter] SET DB_CHAINING OFF 
GO
ALTER DATABASE [sistema_piter] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [sistema_piter] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [sistema_piter] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [sistema_piter] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
ALTER DATABASE [sistema_piter] SET QUERY_STORE = OFF
GO
USE [sistema_piter]
GO
ALTER ROLE [db_owner] ADD MEMBER [app_flutter]
GO
/****** Object:  Schema [Auditoria]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Auditoria]
GO
/****** Object:  Schema [Comunicaciones]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Comunicaciones]
GO
/****** Object:  Schema [DataWarehouse]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [DataWarehouse]
GO
/****** Object:  Schema [Fidelizacion]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Fidelizacion]
GO
/****** Object:  Schema [InternetCosas]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [InternetCosas]
GO
/****** Object:  Schema [Nucleo]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Nucleo]
GO
/****** Object:  Schema [Pagos]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Pagos]
GO
/****** Object:  Schema [Procesos]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Procesos]
GO
/****** Object:  Schema [Reservas]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Reservas]
GO
/****** Object:  Schema [SaaS_Gestion]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [SaaS_Gestion]
GO
/****** Object:  Schema [Seguridad]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Seguridad]
GO
/****** Object:  Schema [Ventas]    Script Date: 26/01/2026 1:16:53 ******/
CREATE SCHEMA [Ventas]
GO
/****** Object:  Table [Pagos].[Ingresos_Historial]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Pagos].[Ingresos_Historial](
	[id_ingresos] [uniqueidentifier] NOT NULL,
	[orden_id] [uniqueidentifier] NULL,
	[caja_diaria_id] [uniqueidentifier] NULL,
	[monto] [decimal](18, 2) NOT NULL,
	[metodo_pago] [nvarchar](50) NULL,
	[referencia_pago] [nvarchar](100) NULL,
	[SysStartTime] [datetime2](7) NOT NULL,
	[SysEndTime] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Index [ix_Ingresos_Historial]    Script Date: 26/01/2026 1:16:53 ******/
CREATE CLUSTERED INDEX [ix_Ingresos_Historial] ON [Pagos].[Ingresos_Historial]
(
	[SysEndTime] ASC,
	[SysStartTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Pagos_Ingresos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pagos_Ingresos](
	[id_ingresos] [uniqueidentifier] NOT NULL,
	[orden_id] [uniqueidentifier] NULL,
	[caja_diaria_id] [uniqueidentifier] NULL,
	[monto] [decimal](18, 2) NOT NULL,
	[metodo_pago] [nvarchar](50) NULL,
	[referencia_pago] [nvarchar](100) NULL,
	[SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id_ingresos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
	PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Pagos].[Ingresos_Historial])
)
GO
/****** Object:  Table [dbo].[Ventas_Ordenes]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Ordenes](
	[id_ordenes] [uniqueidentifier] NOT NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[cocinero_id] [uniqueidentifier] NULL,
	[cliente_id] [uniqueidentifier] NULL,
	[cliente_nombre] [nvarchar](100) NULL,
	[monto_total] [decimal](18, 2) NULL,
	[estado] [nvarchar](50) NULL,
	[es_fallida] [bit] NULL,
	[motivo_anulacion] [nvarchar](200) NULL,
	[creado_at] [datetimeoffset](7) NULL,
	[fecha_preparacion_inicio] [datetimeoffset](7) NULL,
	[fecha_termino_cocina] [datetimeoffset](7) NULL,
	[fecha_entrega_mesa] [datetimeoffset](7) NULL,
	[fecha_servido] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_ordenes] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Detalle_Orden]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Detalle_Orden](
	[id_detalleOrd] [uniqueidentifier] NOT NULL,
	[orden_id] [uniqueidentifier] NULL,
	[producto_id] [uniqueidentifier] NULL,
	[cantidad] [int] NOT NULL,
	[precio_aplicado] [decimal](18, 2) NOT NULL,
	[subtotal]  AS ([cantidad]*[precio_aplicado]) PERSISTED,
PRIMARY KEY CLUSTERED 
(
	[id_detalleOrd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Nucleo_Sucursales]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Nucleo_Sucursales](
	[id_sucursal] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](150) NULL,
	[direccion] [nvarchar](max) NULL,
	[ubicacion_gps] [geography] NULL,
	[activo] [bit] NULL,
	[latitud] [float] NULL,
	[longitud] [float] NULL,
	[imagen_url] [varchar](255) NULL,
	[telefono] [nvarchar](50) NULL,
	[gerente_encargado_id] [uniqueidentifier] NULL,
	[horarios] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_sucursal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Productos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Productos](
	[id_producto] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](150) NULL,
	[sku] [nvarchar](50) NULL,
	[categoria] [nvarchar](50) NULL,
	[imagen_url] [nvarchar](max) NULL,
	[es_inventariable] [bit] NULL,
	[tiempo_preparacion_estandar] [int] NULL,
	[atributos_json] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_producto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  View [dbo].[Ventas_Vista_Carga_Cocina]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   VIEW [dbo].[Ventas_Vista_Carga_Cocina] AS
SELECT 
    S.id_sucursal AS sucursal_id,
    COUNT(DISTINCT O.id_ordenes) AS ordenes_en_cola,
    SUM(P.tiempo_preparacion_estandar * D.cantidad) AS minutos_carga_total,
    (SUM(P.tiempo_preparacion_estandar * D.cantidad) / 3.0) + 5 AS tiempo_espera_real_minutos
FROM Ventas_Ordenes O
JOIN Ventas_Detalle_Orden D ON O.id_ordenes = D.orden_id
JOIN Ventas_Productos P ON D.producto_id = P.id_producto
JOIN Nucleo_Sucursales S ON O.sucursal_id = S.id_sucursal
WHERE O.estado IN ('PENDIENTE', 'COCINA')
GROUP BY S.id_sucursal;
GO
/****** Object:  Table [dbo].[Auditoria_Log_Acciones]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Auditoria_Log_Acciones](
	[id_log] [bigint] IDENTITY(1,1) NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[modulo] [nvarchar](100) NULL,
	[accion] [nvarchar](100) NULL,
	[entidad_afectada] [nvarchar](100) NULL,
	[id_entidad] [nvarchar](100) NULL,
	[datos_antes] [nvarchar](max) NULL,
	[datos_despues] [nvarchar](max) NULL,
	[descripcion] [nvarchar](max) NULL,
	[fecha] [datetimeoffset](7) NULL,
	[ip_origen] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_log] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Comunicaciones_Notificaciones]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Comunicaciones_Notificaciones](
	[id_notifi] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[titulo] [nvarchar](150) NULL,
	[mensaje] [nvarchar](max) NULL,
	[tipo] [nvarchar](50) NULL,
	[leido] [bit] NULL,
	[fecha] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_notifi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DataWarehouse_Dim_Rendimiento_Personal]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataWarehouse_Dim_Rendimiento_Personal](
	[id_dwD] [bigint] IDENTITY(1,1) NOT NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[rol_en_orden] [nvarchar](50) NULL,
	[total_ordenes_atendidas] [int] NULL,
	[monto_total_generado] [decimal](18, 2) NULL,
	[total_ventas_fallidas] [int] NULL,
	[promedio_tiempo_respuesta] [float] NULL,
	[promedio_tiempo_cocina] [float] NULL,
	[mes] [int] NULL,
	[anio] [int] NULL,
	[fecha_calculo] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_dwD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DataWarehouse_Fact_Ventas_Historico]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataWarehouse_Fact_Ventas_Historico](
	[id_dwF] [bigint] IDENTITY(1,1) NOT NULL,
	[fecha_venta] [date] NULL,
	[sucursal_nombre] [nvarchar](150) NULL,
	[producto_nombre] [nvarchar](150) NULL,
	[categoria] [nvarchar](50) NULL,
	[cantidad_vendida] [int] NULL,
	[total_ganado] [decimal](18, 2) NULL,
	[mes] [int] NULL,
	[anio] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_dwF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Fidelizacion_Niveles]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fidelizacion_Niveles](
	[id_fidel] [int] IDENTITY(1,1) NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](50) NULL,
	[descuento_porcentaje] [decimal](5, 2) NULL,
	[precio_suscripcion] [decimal](18, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_fidel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Identidad_Usuarios]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Identidad_Usuarios](
	[id_usuario_global] [uniqueidentifier] NOT NULL,
	[nombre_completo] [nvarchar](150) NOT NULL,
	[email] [nvarchar](150) NOT NULL,
	[telefono] [nvarchar](20) NULL,
	[password_hash] [nvarchar](255) NOT NULL,
	[email_verificado] [bit] NULL,
	[telefono_verificado] [bit] NULL,
	[proveedor_registro] [nvarchar](50) NULL,
	[activo] [bit] NULL,
	[creado_at] [datetimeoffset](7) NULL,
	[ultimo_login] [datetimeoffset](7) NULL,
	[rol_boton] [varchar](20) NULL,
	[tipo_documento] [varchar](50) NULL,
	[numero_documento] [varchar](20) NULL,
	[sexo] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_usuario_global] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_Identidad_Email] UNIQUE NONCLUSTERED 
(
	[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[InternetCosas_Dispositivos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InternetCosas_Dispositivos](
	[id_dispositivo] [nvarchar](100) NOT NULL,
	[recurso_id] [uniqueidentifier] NULL,
	[estado_bateria] [int] NULL,
	[ultimo_ping] [datetimeoffset](7) NULL,
	[mac] [nvarchar](100) NULL,
	[ruc] [varchar](20) NULL,
	[numero_mesa] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_dispositivo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[InternetCosas_Eventos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InternetCosas_Eventos](
	[id_evento] [bigint] IDENTITY(1,1) NOT NULL,
	[dispositivo_id] [nvarchar](100) NULL,
	[evento] [nvarchar](50) NULL,
	[fecha] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_evento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Nucleo_Empresas]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Nucleo_Empresas](
	[id_empresa] [uniqueidentifier] NOT NULL,
	[razon_social] [nvarchar](150) NOT NULL,
	[ruc_nit] [nvarchar](20) NULL,
	[tipo_negocio] [nvarchar](50) NULL,
	[estado] [nvarchar](20) NULL,
	[configuracion_json] [nvarchar](max) NULL,
	[creado_at] [datetimeoffset](7) NULL,
	[logo_url] [nvarchar](max) NULL,
	[plan_id] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_empresa] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Nucleo_Recursos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Nucleo_Recursos](
	[id_recurso] [uniqueidentifier] NOT NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](50) NULL,
	[tipo] [nvarchar](50) NULL,
	[estado] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_recurso] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Pagos_Cajas_Diarias]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pagos_Cajas_Diarias](
	[id_cajaDi] [uniqueidentifier] NOT NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[fecha_apertura] [datetimeoffset](7) NULL,
	[fecha_cierre] [datetimeoffset](7) NULL,
	[monto_inicial] [decimal](18, 2) NOT NULL,
	[monto_final_esperado]  AS ([monto_inicial]+[total_ingresos_efectivo]),
	[total_ingresos_efectivo] [decimal](18, 2) NULL,
	[monto_final_real] [decimal](18, 2) NULL,
	[estado] [nvarchar](20) NULL,
	[observaciones] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_cajaDi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Procesos_Cola_Eventos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Procesos_Cola_Eventos](
	[id_colaE] [bigint] IDENTITY(1,1) NOT NULL,
	[tipo_evento] [nvarchar](100) NULL,
	[payload] [nvarchar](max) NULL,
	[procesado] [bit] NULL,
	[fecha_creacion] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_colaE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Reservas_Reservaciones]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Reservas_Reservaciones](
	[id_reserva] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[cliente_id] [uniqueidentifier] NULL,
	[recurso_id] [uniqueidentifier] NULL,
	[fecha_inicio] [datetimeoffset](7) NOT NULL,
	[fecha_fin] [datetimeoffset](7) NOT NULL,
	[estado] [nvarchar](20) NULL,
	[observaciones] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_reserva] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_CaracteristicasPlan]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_CaracteristicasPlan](
	[id_ContiPlan] [uniqueidentifier] NOT NULL,
	[codigo] [nvarchar](50) NOT NULL,
	[nombre] [nvarchar](100) NOT NULL,
	[descripcion] [nvarchar](255) NULL,
	[activo] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_ContiPlan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[codigo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl](
	[id_EmpCaracteristicaPl] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NOT NULL,
	[feature_id] [uniqueidentifier] NOT NULL,
	[valor_over_habilitar] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id_EmpCaracteristicaPl] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_Empresa_Feature] UNIQUE NONCLUSTERED 
(
	[empresa_id] ASC,
	[feature_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_Facturacion_Servicio]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_Facturacion_Servicio](
	[id_Factura] [uniqueidentifier] NOT NULL,
	[suscripcion_id] [uniqueidentifier] NULL,
	[monto_pagado] [decimal](18, 2) NULL,
	[fecha_pago] [datetimeoffset](7) NULL,
	[metodo_pago] [nvarchar](50) NULL,
	[nro_operacion] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_Factura] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_Plan_ContenidoPlan]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan](
	[id_contenidoPl] [uniqueidentifier] NOT NULL,
	[id_plan] [uniqueidentifier] NOT NULL,
	[id_CaracteristicasPlan] [uniqueidentifier] NOT NULL,
	[valor] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id_contenidoPl] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_Plan_Feature] UNIQUE NONCLUSTERED 
(
	[id_plan] ASC,
	[id_CaracteristicasPlan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_Planes]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_Planes](
	[plan_id] [uniqueidentifier] NOT NULL,
	[nombre] [nvarchar](50) NOT NULL,
	[precio_mensual] [decimal](18, 2) NOT NULL,
	[limite_sucursales] [int] NULL,
	[limite_usuarios] [int] NULL,
	[acceso_iot] [bit] NULL,
	[activo] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[plan_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SaaS_Gestion_Suscripciones]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaaS_Gestion_Suscripciones](
	[id_subsP] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[plan_id] [uniqueidentifier] NULL,
	[fecha_inicio] [datetime] NOT NULL,
	[fecha_vencimiento] [datetime] NOT NULL,
	[estado] [nvarchar](20) NULL,
	[renovacion_auto] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_subsP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Seguridad_Perfil_Permisos]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Seguridad_Perfil_Permisos](
	[perfil_id] [int] NOT NULL,
	[permiso_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[perfil_id] ASC,
	[permiso_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Seguridad_Perfiles]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Seguridad_Perfiles](
	[id_perfil] [int] IDENTITY(1,1) NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](50) NOT NULL,
	[descripcion] [nvarchar](200) NULL,
	[turno_horario_inicio] [TIME] NULL,
    [turno_horario_fin] [TIME] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_perfil] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Seguridad_Permisos_Sistema]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Seguridad_Permisos_Sistema](
	[id_secur_perf] [int] IDENTITY(1,1) NOT NULL,
	[codigo_interno] [nvarchar](50) NULL,
	[descripcion] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_secur_perf] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[codigo_interno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Seguridad_Sesiones]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Seguridad_Sesiones](
	[id_sesiones] [uniqueidentifier] NOT NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[token_hash] [nvarchar](255) NULL,
	[fecha_expiracion] [datetimeoffset](7) NULL,
	[ip] [nvarchar](50) NULL,
	[user_agent] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_sesiones] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Seguridad_Usuarios]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Seguridad_Usuarios](
	[id_usuario] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[sucursal_asignada_id] [uniqueidentifier] NULL,
	[perfil_id] [int] NULL,
	[reporte_a_id] [uniqueidentifier] NULL,
	[nombre_completo] [nvarchar](100) NULL,
	[email] [nvarchar](255) NOT NULL,
	[password_hash] [nvarchar](max) NOT NULL,
	[estado] [nvarchar](20) NULL,
	[foto_url] [nvarchar](max) NULL,
	[color_preferido] [varchar](20) NULL,
	[color_fondo_preferido] [varchar](20) NULL,
	[dni] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_usuario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Clientes]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Clientes](
	[id_cliente] [uniqueidentifier] NOT NULL,
	[empresa_id] [uniqueidentifier] NULL,
	[nivel_fidelizacion_id] [int] NULL,
	[nombre_completo] [nvarchar](150) NULL,
	[documento_identidad] [nvarchar](20) NULL,
	[email] [nvarchar](150) NULL,
	[telefono] [nvarchar](20) NULL,
	[es_miembro_vip] [bit] NULL,
	[creado_at] [datetimeoffset](7) NULL,
	[usuario_global_id] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_cliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Listas_Precios]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Listas_Precios](
	[id_lista] [int] IDENTITY(1,1) NOT NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[nombre] [nvarchar](50) NULL,
	[fecha_inicio] [datetime] NULL,
	[fecha_fin] [datetime] NULL,
	[prioridad] [int] NULL,
	[activa] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_lista] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Movimientos_Stock]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Movimientos_Stock](
	[id_mov_stock] [bigint] IDENTITY(1,1) NOT NULL,
	[producto_id] [uniqueidentifier] NULL,
	[sucursal_id] [uniqueidentifier] NULL,
	[usuario_id] [uniqueidentifier] NULL,
	[tipo] [nvarchar](50) NULL,
	[cantidad] [int] NULL,
	[motivo] [nvarchar](200) NULL,
	[fecha] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_mov_stock] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Precios_Producto]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Precios_Producto](
	[id_precios] [int] NOT NULL,
	[producto_id] [uniqueidentifier] NOT NULL,
	[precio] [decimal](18, 2) NOT NULL,
	[moneda] [char](3) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_precios] ASC,
	[producto_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Ventas_Stock]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ventas_Stock](
	[producto_id] [uniqueidentifier] NOT NULL,
	[sucursal_id] [uniqueidentifier] NOT NULL,
	[cantidad_actual] [int] NOT NULL,
	[stock_minimo] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[producto_id] ASC,
	[sucursal_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Seguridad].[Seguridad_Perfiles]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Seguridad].[Seguridad_Perfiles](
	[id_perfil] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id_perfil] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[nombre] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Seguridad].[Seguridad_Usuarios]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Seguridad].[Seguridad_Usuarios](
	[id_usuario] [uniqueidentifier] NOT NULL,
	[email] [nvarchar](255) NOT NULL,
	[password_hash] [nvarchar](max) NOT NULL,
	[nombre_completo] [nvarchar](100) NULL,
	[perfil_id] [int] NULL,
	[activo] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[id_usuario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [Ventas].[Identidad_Usuarios]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Ventas].[Identidad_Usuarios](
	[id_usuario] [uniqueidentifier] NOT NULL,
	[email] [nvarchar](255) NOT NULL,
	[password_hash] [nvarchar](max) NULL,
	[nombre] [nvarchar](100) NULL,
	[apellido] [nvarchar](100) NULL,
	[dni] [nvarchar](20) NULL,
	[tipo_documento] [nvarchar](20) NULL,
	[telefono] [nvarchar](20) NULL,
	[foto_url] [nvarchar](max) NULL,
	[google_id] [nvarchar](100) NULL,
	[rol] [nvarchar](20) NULL,
	[activo] [bit] NULL,
	[creado_at] [datetimeoffset](7) NULL,
	[sexo] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[id_usuario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [UX_VentasClientes_Empresa_Usuario]    Script Date: 26/01/2026 1:16:53 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UX_VentasClientes_Empresa_Usuario] ON [dbo].[Ventas_Clientes]
(
	[empresa_id] ASC,
	[usuario_global_id] ASC
)
WHERE ([usuario_global_id] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Identidad_DNI]    Script Date: 26/01/2026 1:16:53 ******/
CREATE NONCLUSTERED INDEX [IX_Identidad_DNI] ON [Ventas].[Identidad_Usuarios]
(
	[dni] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_Identidad_Email]    Script Date: 26/01/2026 1:16:53 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Identidad_Email] ON [Ventas].[Identidad_Usuarios]
(
	[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Auditoria_Log_Acciones] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha]
GO
ALTER TABLE [dbo].[Comunicaciones_Notificaciones] ADD  DEFAULT (newid()) FOR [id_notifi]
GO
ALTER TABLE [dbo].[Comunicaciones_Notificaciones] ADD  DEFAULT ((0)) FOR [leido]
GO
ALTER TABLE [dbo].[Comunicaciones_Notificaciones] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha]
GO
ALTER TABLE [dbo].[DataWarehouse_Dim_Rendimiento_Personal] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha_calculo]
GO
ALTER TABLE [dbo].[Fidelizacion_Niveles] ADD  DEFAULT ((0)) FOR [descuento_porcentaje]
GO
ALTER TABLE [dbo].[Fidelizacion_Niveles] ADD  DEFAULT ((0)) FOR [precio_suscripcion]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT (newid()) FOR [id_usuario_global]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT ((0)) FOR [email_verificado]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT ((0)) FOR [telefono_verificado]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT ('LOCAL') FOR [proveedor_registro]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [dbo].[Identidad_Usuarios] ADD  DEFAULT (sysdatetimeoffset()) FOR [creado_at]
GO
ALTER TABLE [dbo].[InternetCosas_Eventos] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha]
GO
ALTER TABLE [dbo].[Nucleo_Empresas] ADD  DEFAULT (newid()) FOR [id_empresa]
GO
ALTER TABLE [dbo].[Nucleo_Empresas] ADD  DEFAULT ('ACTIVA') FOR [estado]
GO
ALTER TABLE [dbo].[Nucleo_Empresas] ADD  DEFAULT (sysdatetimeoffset()) FOR [creado_at]
GO
ALTER TABLE [dbo].[Nucleo_Recursos] ADD  DEFAULT (newid()) FOR [id_recurso]
GO
ALTER TABLE [dbo].[Nucleo_Recursos] ADD  DEFAULT ('LIBRE') FOR [estado]
GO
ALTER TABLE [dbo].[Nucleo_Sucursales] ADD  DEFAULT (newid()) FOR [id_sucursal]
GO
ALTER TABLE [dbo].[Nucleo_Sucursales] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias] ADD  DEFAULT (newid()) FOR [id_cajaDi]
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha_apertura]
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias] ADD  DEFAULT ((0)) FOR [total_ingresos_efectivo]
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias] ADD  DEFAULT ('ABIERTA') FOR [estado]
GO
ALTER TABLE [dbo].[Pagos_Ingresos] ADD  DEFAULT (newid()) FOR [id_ingresos]
GO
ALTER TABLE [dbo].[Procesos_Cola_Eventos] ADD  DEFAULT ((0)) FOR [procesado]
GO
ALTER TABLE [dbo].[Procesos_Cola_Eventos] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha_creacion]
GO
ALTER TABLE [dbo].[Reservas_Reservaciones] ADD  DEFAULT (newid()) FOR [id_reserva]
GO
ALTER TABLE [dbo].[Reservas_Reservaciones] ADD  DEFAULT ('CONFIRMADA') FOR [estado]
GO
ALTER TABLE [dbo].[SaaS_Gestion_CaracteristicasPlan] ADD  DEFAULT (newid()) FOR [id_ContiPlan]
GO
ALTER TABLE [dbo].[SaaS_Gestion_CaracteristicasPlan] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl] ADD  DEFAULT (newid()) FOR [id_EmpCaracteristicaPl]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Facturacion_Servicio] ADD  DEFAULT (newid()) FOR [id_Factura]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Facturacion_Servicio] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha_pago]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan] ADD  DEFAULT (newid()) FOR [id_contenidoPl]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Planes] ADD  DEFAULT (newid()) FOR [plan_id]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Planes] ADD  DEFAULT ((1)) FOR [limite_sucursales]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Planes] ADD  DEFAULT ((5)) FOR [limite_usuarios]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Planes] ADD  DEFAULT ((0)) FOR [acceso_iot]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Planes] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Suscripciones] ADD  DEFAULT (newid()) FOR [id_subsP]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Suscripciones] ADD  DEFAULT ('ACTIVO') FOR [estado]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Suscripciones] ADD  DEFAULT ((1)) FOR [renovacion_auto]
GO
ALTER TABLE [dbo].[Seguridad_Sesiones] ADD  DEFAULT (newid()) FOR [id_sesiones]
GO
ALTER TABLE [dbo].[Seguridad_Usuarios] ADD  DEFAULT (newid()) FOR [id_usuario]
GO
ALTER TABLE [dbo].[Seguridad_Usuarios] ADD  DEFAULT ('ACTIVO') FOR [estado]
GO
ALTER TABLE [dbo].[Seguridad_Usuarios] ADD  DEFAULT ('0xFFFF6B00') FOR [color_preferido]
GO
ALTER TABLE [dbo].[Seguridad_Usuarios] ADD  DEFAULT ('0xFF0B1121') FOR [color_fondo_preferido]
GO
ALTER TABLE [dbo].[Ventas_Clientes] ADD  DEFAULT (newid()) FOR [id_cliente]
GO
ALTER TABLE [dbo].[Ventas_Clientes] ADD  DEFAULT ((0)) FOR [es_miembro_vip]
GO
ALTER TABLE [dbo].[Ventas_Clientes] ADD  DEFAULT (sysdatetimeoffset()) FOR [creado_at]
GO
ALTER TABLE [dbo].[Ventas_Detalle_Orden] ADD  DEFAULT (newid()) FOR [id_detalleOrd]
GO
ALTER TABLE [dbo].[Ventas_Listas_Precios] ADD  DEFAULT ((0)) FOR [prioridad]
GO
ALTER TABLE [dbo].[Ventas_Listas_Precios] ADD  DEFAULT ((1)) FOR [activa]
GO
ALTER TABLE [dbo].[Ventas_Movimientos_Stock] ADD  DEFAULT (sysdatetimeoffset()) FOR [fecha]
GO
ALTER TABLE [dbo].[Ventas_Ordenes] ADD  DEFAULT (newid()) FOR [id_ordenes]
GO
ALTER TABLE [dbo].[Ventas_Ordenes] ADD  DEFAULT ((0)) FOR [monto_total]
GO
ALTER TABLE [dbo].[Ventas_Ordenes] ADD  DEFAULT ('PENDIENTE') FOR [estado]
GO
ALTER TABLE [dbo].[Ventas_Ordenes] ADD  DEFAULT ((0)) FOR [es_fallida]
GO
ALTER TABLE [dbo].[Ventas_Ordenes] ADD  DEFAULT (sysdatetimeoffset()) FOR [creado_at]
GO
ALTER TABLE [dbo].[Ventas_Precios_Producto] ADD  DEFAULT ('PEN') FOR [moneda]
GO
ALTER TABLE [dbo].[Ventas_Productos] ADD  DEFAULT (newid()) FOR [id_producto]
GO
ALTER TABLE [dbo].[Ventas_Productos] ADD  DEFAULT ((1)) FOR [es_inventariable]
GO
ALTER TABLE [dbo].[Ventas_Productos] ADD  DEFAULT ((15)) FOR [tiempo_preparacion_estandar]
GO
ALTER TABLE [dbo].[Ventas_Stock] ADD  DEFAULT ((0)) FOR [stock_minimo]
GO
ALTER TABLE [Seguridad].[Seguridad_Usuarios] ADD  DEFAULT (newid()) FOR [id_usuario]
GO
ALTER TABLE [Seguridad].[Seguridad_Usuarios] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [Ventas].[Identidad_Usuarios] ADD  DEFAULT (newid()) FOR [id_usuario]
GO
ALTER TABLE [Ventas].[Identidad_Usuarios] ADD  DEFAULT ('DNI') FOR [tipo_documento]
GO
ALTER TABLE [Ventas].[Identidad_Usuarios] ADD  DEFAULT ('cliente') FOR [rol]
GO
ALTER TABLE [Ventas].[Identidad_Usuarios] ADD  DEFAULT ((1)) FOR [activo]
GO
ALTER TABLE [Ventas].[Identidad_Usuarios] ADD  DEFAULT (sysdatetimeoffset()) FOR [creado_at]
GO
ALTER TABLE [dbo].[Auditoria_Log_Acciones]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Auditoria_Log_Acciones]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Comunicaciones_Notificaciones]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Comunicaciones_Notificaciones]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[DataWarehouse_Dim_Rendimiento_Personal]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[DataWarehouse_Dim_Rendimiento_Personal]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Fidelizacion_Niveles]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[InternetCosas_Dispositivos]  WITH CHECK ADD FOREIGN KEY([recurso_id])
REFERENCES [dbo].[Nucleo_Recursos] ([id_recurso])
GO
ALTER TABLE [dbo].[InternetCosas_Eventos]  WITH CHECK ADD FOREIGN KEY([dispositivo_id])
REFERENCES [dbo].[InternetCosas_Dispositivos] ([id_dispositivo])
GO
ALTER TABLE [dbo].[Nucleo_Recursos]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Nucleo_Sucursales]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Nucleo_Sucursales]  WITH CHECK ADD  CONSTRAINT [FK_Sucursal_Gerente] FOREIGN KEY([gerente_encargado_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Nucleo_Sucursales] CHECK CONSTRAINT [FK_Sucursal_Gerente]
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Pagos_Cajas_Diarias]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Pagos_Ingresos]  WITH CHECK ADD FOREIGN KEY([caja_diaria_id])
REFERENCES [dbo].[Pagos_Cajas_Diarias] ([id_cajaDi])
GO
ALTER TABLE [dbo].[Pagos_Ingresos]  WITH CHECK ADD FOREIGN KEY([orden_id])
REFERENCES [dbo].[Ventas_Ordenes] ([id_ordenes])
GO
ALTER TABLE [dbo].[Reservas_Reservaciones]  WITH CHECK ADD FOREIGN KEY([cliente_id])
REFERENCES [dbo].[Ventas_Clientes] ([id_cliente])
GO
ALTER TABLE [dbo].[Reservas_Reservaciones]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Reservas_Reservaciones]  WITH CHECK ADD FOREIGN KEY([recurso_id])
REFERENCES [dbo].[Nucleo_Recursos] ([id_recurso])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl]  WITH CHECK ADD  CONSTRAINT [FK_EmpresaFeatures_Empresa] FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl] CHECK CONSTRAINT [FK_EmpresaFeatures_Empresa]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl]  WITH CHECK ADD  CONSTRAINT [FK_EmpresaFeatures_Feature] FOREIGN KEY([feature_id])
REFERENCES [dbo].[SaaS_Gestion_CaracteristicasPlan] ([id_ContiPlan])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Empresa_CaracteristicaPl] CHECK CONSTRAINT [FK_EmpresaFeatures_Feature]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Facturacion_Servicio]  WITH CHECK ADD FOREIGN KEY([suscripcion_id])
REFERENCES [dbo].[SaaS_Gestion_Suscripciones] ([id_subsP])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan]  WITH CHECK ADD  CONSTRAINT [FK_ContenidoPlan_CaracteristicasPlan] FOREIGN KEY([id_CaracteristicasPlan])
REFERENCES [dbo].[SaaS_Gestion_CaracteristicasPlan] ([id_ContiPlan])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan] CHECK CONSTRAINT [FK_ContenidoPlan_CaracteristicasPlan]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan]  WITH CHECK ADD  CONSTRAINT [FK_ContenidoPlan_Plan] FOREIGN KEY([id_plan])
REFERENCES [dbo].[SaaS_Gestion_Planes] ([plan_id])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Plan_ContenidoPlan] CHECK CONSTRAINT [FK_ContenidoPlan_Plan]
GO
ALTER TABLE [dbo].[SaaS_Gestion_Suscripciones]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[SaaS_Gestion_Suscripciones]  WITH CHECK ADD FOREIGN KEY([plan_id])
REFERENCES [dbo].[SaaS_Gestion_Planes] ([plan_id])
GO
ALTER TABLE [dbo].[Seguridad_Perfil_Permisos]  WITH CHECK ADD FOREIGN KEY([perfil_id])
REFERENCES [dbo].[Seguridad_Perfiles] ([id_perfil])
GO
ALTER TABLE [dbo].[Seguridad_Perfil_Permisos]  WITH CHECK ADD FOREIGN KEY([permiso_id])
REFERENCES [dbo].[Seguridad_Permisos_Sistema] ([id_secur_perf])
GO
ALTER TABLE [dbo].[Seguridad_Perfiles]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Seguridad_Sesiones]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Seguridad_Usuarios]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Seguridad_Usuarios]  WITH CHECK ADD FOREIGN KEY([perfil_id])
REFERENCES [dbo].[Seguridad_Perfiles] ([id_perfil])
GO
ALTER TABLE [dbo].[Seguridad_Usuarios]  WITH CHECK ADD FOREIGN KEY([reporte_a_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Seguridad_Usuarios]  WITH CHECK ADD FOREIGN KEY([sucursal_asignada_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Ventas_Clientes]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Ventas_Clientes]  WITH CHECK ADD FOREIGN KEY([nivel_fidelizacion_id])
REFERENCES [dbo].[Fidelizacion_Niveles] ([id_fidel])
GO
ALTER TABLE [dbo].[Ventas_Clientes]  WITH CHECK ADD  CONSTRAINT [FK_VentasClientes_IdentidadUsuarios] FOREIGN KEY([usuario_global_id])
REFERENCES [dbo].[Identidad_Usuarios] ([id_usuario_global])
GO
ALTER TABLE [dbo].[Ventas_Clientes] CHECK CONSTRAINT [FK_VentasClientes_IdentidadUsuarios]
GO
ALTER TABLE [dbo].[Ventas_Detalle_Orden]  WITH CHECK ADD FOREIGN KEY([orden_id])
REFERENCES [dbo].[Ventas_Ordenes] ([id_ordenes])
GO
ALTER TABLE [dbo].[Ventas_Detalle_Orden]  WITH CHECK ADD FOREIGN KEY([producto_id])
REFERENCES [dbo].[Ventas_Productos] ([id_producto])
GO
ALTER TABLE [dbo].[Ventas_Listas_Precios]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Ventas_Movimientos_Stock]  WITH CHECK ADD FOREIGN KEY([producto_id])
REFERENCES [dbo].[Ventas_Productos] ([id_producto])
GO
ALTER TABLE [dbo].[Ventas_Movimientos_Stock]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Ventas_Movimientos_Stock]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Ventas_Ordenes]  WITH CHECK ADD FOREIGN KEY([cliente_id])
REFERENCES [dbo].[Ventas_Clientes] ([id_cliente])
GO
ALTER TABLE [dbo].[Ventas_Ordenes]  WITH CHECK ADD FOREIGN KEY([cocinero_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Ventas_Ordenes]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [dbo].[Ventas_Ordenes]  WITH CHECK ADD FOREIGN KEY([usuario_id])
REFERENCES [dbo].[Seguridad_Usuarios] ([id_usuario])
GO
ALTER TABLE [dbo].[Ventas_Precios_Producto]  WITH CHECK ADD FOREIGN KEY([id_precios])
REFERENCES [dbo].[Ventas_Listas_Precios] ([id_lista])
GO
ALTER TABLE [dbo].[Ventas_Precios_Producto]  WITH CHECK ADD FOREIGN KEY([producto_id])
REFERENCES [dbo].[Ventas_Productos] ([id_producto])
GO
ALTER TABLE [dbo].[Ventas_Productos]  WITH CHECK ADD FOREIGN KEY([empresa_id])
REFERENCES [dbo].[Nucleo_Empresas] ([id_empresa])
GO
ALTER TABLE [dbo].[Ventas_Stock]  WITH CHECK ADD FOREIGN KEY([producto_id])
REFERENCES [dbo].[Ventas_Productos] ([id_producto])
GO
ALTER TABLE [dbo].[Ventas_Stock]  WITH CHECK ADD FOREIGN KEY([sucursal_id])
REFERENCES [dbo].[Nucleo_Sucursales] ([id_sucursal])
GO
ALTER TABLE [Seguridad].[Seguridad_Usuarios]  WITH CHECK ADD FOREIGN KEY([perfil_id])
REFERENCES [Seguridad].[Seguridad_Perfiles] ([id_perfil])
GO
/****** Object:  StoredProcedure [dbo].[DataWarehouse_ETL_Ventas_Diarias]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [dbo].[DataWarehouse_ETL_Ventas_Diarias]
AS
BEGIN
    SET NOCOUNT ON;

    MERGE DataWarehouse_Fact_Ventas_Historico AS Target
    USING (
        SELECT 
            CAST(O.creado_at AS DATE) AS fecha,
            S.nombre AS sucursal_nombre,
            P.nombre AS producto_nombre,
            P.categoria,
            SUM(D.cantidad) AS cantidad,
            SUM(D.subtotal) AS total,
            MONTH(O.creado_at) AS mes,
            YEAR(O.creado_at) AS anio
        FROM Ventas_Ordenes O
        JOIN Ventas_Detalle_Orden D ON O.id_ordenes = D.orden_id
        JOIN Ventas_Productos P ON D.producto_id = P.id_producto
        JOIN Nucleo_Sucursales S ON O.sucursal_id = S.id_sucursal
        WHERE O.estado = 'PAGADO'
        GROUP BY 
            CAST(O.creado_at AS DATE),
            S.nombre,
            P.nombre,
            P.categoria,
            MONTH(O.creado_at),
            YEAR(O.creado_at)
    ) AS Source
    ON (
        Target.fecha_venta = Source.fecha
        AND Target.producto_nombre = Source.producto_nombre
        AND Target.sucursal_nombre = Source.sucursal_nombre
    )

    WHEN MATCHED THEN
        UPDATE SET 
            Target.cantidad_vendida = Source.cantidad,
            Target.total_ganado = Source.total,
            Target.mes = Source.mes,
            Target.anio = Source.anio

    WHEN NOT MATCHED THEN
        INSERT (
            fecha_venta,
            sucursal_nombre,
            producto_nombre,
            categoria,
            cantidad_vendida,
            total_ganado,
            mes,
            anio
        )
        VALUES (
            Source.fecha,
            Source.sucursal_nombre,
            Source.producto_nombre,
            Source.categoria,
            Source.cantidad,
            Source.total,
            Source.mes,
            Source.anio
        );
END;
GO
/****** Object:  StoredProcedure [dbo].[sp_Sistema_ActualizarPerfil]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 5. STORED PROCEDURE: ACTUALIZAR PERFIL (Para completar datos pos-Google)
CREATE   PROCEDURE [dbo].[sp_Sistema_ActualizarPerfil]
    @IdUsuario UNIQUEIDENTIFIER,
    @Dni NVARCHAR(20),
    @TipoDocumento NVARCHAR(20),
    @Telefono NVARCHAR(20),
    @Sexo NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Ventas.Identidad_Usuarios
    SET 
        dni = @Dni,
        tipo_documento = @TipoDocumento,
        telefono = @Telefono,
        sexo = @Sexo
    WHERE id_usuario = @IdUsuario;
END
GO
/****** Object:  StoredProcedure [dbo].[sp_Sistema_Login]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2. STORED PROCEDURE: LOGIN UNIFICADO
-- Busca primero en Staff, luego en Clientes
CREATE   PROCEDURE [dbo].[sp_Sistema_Login]
    @Email NVARCHAR(255),
    @Password NVARCHAR(MAX) -- En producción esto se compara con HASH
AS
BEGIN
    SET NOCOUNT ON;
    -- A. Intentar Login como STAFF (Seguridad_Usuarios)
    -- Nota: Asumimos que la contraseña ya viene hasheada o se compara directo (Inseguro para prod, pero funcional para demo)
    IF EXISTS (SELECT 1 FROM Seguridad.Seguridad_Usuarios WHERE email = @Email AND password_hash = @Password)
    BEGIN
        SELECT 
            u.id_usuario,
            u.email,
            u.nombre_completo AS nombre,
            '' AS apellido, -- Seguridad_Usuarios solo tiene nombre_completo
            p.nombre AS rol,
            'STAFF' AS tipo_origen -- Flag para frontend
        FROM Seguridad.Seguridad_Usuarios u
        JOIN Seguridad.Seguridad_Perfiles p ON u.perfil_id = p.id_perfil
        WHERE u.email = @Email AND u.password_hash = @Password;
        
        RETURN;
    END
    -- B. Intentar Login como CLIENTE (Identidad_Usuarios)
    IF EXISTS (SELECT 1 FROM Ventas.Identidad_Usuarios WHERE email = @Email AND password_hash = @Password)
    BEGIN
        SELECT 
            id_usuario,
            email,
            nombre,
            apellido,
            rol,
            dni,
            telefono,
            sexo, -- [NUEVO]
            foto_url,
            'CLIENTE' AS tipo_origen
        FROM Ventas.Identidad_Usuarios
        WHERE email = @Email AND password_hash = @Password;
        
        RETURN;
    END
    -- C. Fallo
    -- No devolvemos nada (o un código de error controlado)
END
GO
/****** Object:  StoredProcedure [dbo].[sp_Sistema_LoginGoogle]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 4. STORED PROCEDURE: LOGIN/REGISTRO GOOGLE
CREATE   PROCEDURE [dbo].[sp_Sistema_LoginGoogle]
    @Email NVARCHAR(255),
    @GoogleId NVARCHAR(100),
    @FotoUrl NVARCHAR(MAX),
    @Nombre NVARCHAR(100),
    @Apellido NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Existe?
    IF EXISTS (SELECT 1 FROM Ventas.Identidad_Usuarios WHERE email = @Email)
    BEGIN
        -- Actualizar Google ID si no lo tiene
        UPDATE Ventas.Identidad_Usuarios 
        SET google_id = @GoogleId, foto_url = @FotoUrl
        WHERE email = @Email;
        
        SELECT * FROM Ventas.Identidad_Usuarios WHERE email = @Email;
    END
    ELSE
    BEGIN
        -- Crear nuevo
        INSERT INTO Ventas.Identidad_Usuarios (email, nombre, apellido, google_id, foto_url, dni)
        OUTPUT Inserted.*
        VALUES (@Email, @Nombre, @Apellido, @GoogleId, @FotoUrl, 'PENDIENTE');
    END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_Sistema_RegistroCliente]    Script Date: 26/01/2026 1:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 3. STORED PROCEDURE: REGISTRO CLIENTE
CREATE   PROCEDURE [dbo].[sp_Sistema_RegistroCliente]
    @Email NVARCHAR(255),
    @Password NVARCHAR(MAX),
    @Nombre NVARCHAR(100),
    @Apellido NVARCHAR(100),
    @Dni NVARCHAR(20),
    @Telefono NVARCHAR(20),
    @Sexo NVARCHAR(20) -- [NUEVO]
AS
BEGIN
    SET NOCOUNT ON;
    -- Validar duplicidad
    IF EXISTS (SELECT 1 FROM Ventas.Identidad_Usuarios WHERE email = @Email)
    BEGIN
        THROW 50001, 'El correo ya está registrado.', 1;
    END
    INSERT INTO Ventas.Identidad_Usuarios (email, password_hash, nombre, apellido, dni, telefono, sexo)
    OUTPUT Inserted.id_usuario
    VALUES (@Email, @Password, @Nombre, @Apellido, @Dni, @Telefono, @Sexo);
END
GO
USE [master]
GO
ALTER DATABASE [sistema_piter] SET  READ_WRITE 
GO
CREATE TABLE operaciones_asignaciones_mesa (
    id INT IDENTITY(1,1) PRIMARY KEY,

    sucursal_id UNIQUEIDENTIFIER NOT NULL,
    perfil_id   int NOT NULL,   -- o usuario_id
    recurso_id  UNIQUEIDENTIFIER NOT NULL,   -- id de la mesa

    tipo_asignacion VARCHAR(50) NOT NULL, -- 'PERMANENTE', 'TEMPORAL', etc.

    fecha_asignacion DATETIME2 NOT NULL,
    fecha_fin DATETIME2 NULL,

    activo BIT NOT NULL DEFAULT 1,

    CONSTRAINT FK_op_asig_sucursal
        FOREIGN KEY (sucursal_id)
        REFERENCES Nucleo_Sucursales(id_sucursal),

    CONSTRAINT FK_op_asig_perfil
        FOREIGN KEY (perfil_id)
        REFERENCES Seguridad_perfiles(id_perfil),

    CONSTRAINT FK_op_asig_recurso
        FOREIGN KEY (recurso_id)
        REFERENCES Nucleo_Recursos(id_recurso)
);