/*
  Esquema consolidado - Plataforma de Desarrolladores Agua Hillo
  Generado a partir de las definiciones proporcionadas.

  Decisiones aplicadas:
  - Usuarios -> Sis_Usuarios.
  - Monedas -> Gral_Monedas.
  - GPS_ZonasHorarias -> ZonasHorarias.
  - Nom_Sat_Bancos -> Sat_Bancos.
  - Se omite todo lo relacionado con CveEmpresa/CveEmpresaCRM.
  - Se eliminan Comp_Proveedores, Banc_Creditos y Vta_Cortes junto con sus campos dependientes acordados.
  - Las FK se crean después de todas las tablas para evitar fallas por orden y ciclos.
*/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET XACT_ABORT ON;
GO

/* ==================== TABLAS ==================== */

CREATE TABLE [dbo].[Sis_Roles] (
  [CveRol] int IDENTITY(1,1) NOT NULL,
  [NombreRol] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Descripcion] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [Activo] bit CONSTRAINT [DF_Sis_Roles_Activo] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Roles_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_Roles]
        PRIMARY KEY CLUSTERED ([CveRol]),
  CONSTRAINT [UQ_Sis_Roles_NombreRol]
        UNIQUE NONCLUSTERED ([NombreRol])
)
;
GO

CREATE TABLE [dbo].[Sis_Plataformas] (
  [CvePlataforma] int IDENTITY(1,1) NOT NULL,
  [NombrePlataforma] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Descripcion] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [Activo] bit CONSTRAINT [DF_Sis_Plataformas_Activo] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Plataformas_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_Plataformas]
        PRIMARY KEY CLUSTERED ([CvePlataforma]),
  CONSTRAINT [UQ_Sis_Plataformas_NombrePlataforma]
        UNIQUE NONCLUSTERED ([NombrePlataforma])
)
;
GO

CREATE TABLE [dbo].[Sis_Permisos] (
  [CvePermiso] int IDENTITY(1,1) NOT NULL,
  [CodigoPermiso] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [NombrePermiso] varchar(150) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Descripcion] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [Activo] bit CONSTRAINT [DF_Sis_Permisos_Activo] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Permisos_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_Permisos]
        PRIMARY KEY CLUSTERED ([CvePermiso]),
  CONSTRAINT [UQ_Sis_Permisos_CodigoPermiso]
        UNIQUE NONCLUSTERED ([CodigoPermiso])
)
;
GO

CREATE TABLE [dbo].[Sis_Usuarios] (
  [CveUsuario] int IDENTITY(1,1) NOT NULL,
  [NombreUsuario] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [HashContrasena] varchar(500) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [CorreoElectronico] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [CveRol] int NOT NULL,
  [Activo] bit CONSTRAINT [DF_Sis_Usuarios_Activo] DEFAULT ((1)) NOT NULL,
  [FechaUltimaConexion] datetime NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Usuarios_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  [DebeCambiarContrasena] bit CONSTRAINT [DF_Sis_Usuarios_DebeCambiarContrasena] DEFAULT ((0)) NOT NULL,
  [IntentosFallidosInicioSesion] int CONSTRAINT [DF_Sis_Usuarios_IntentosFallidos] DEFAULT ((0)) NOT NULL,
  [BloqueadoHasta] datetime NULL,
  [FechaUltimoInicioSesion] datetime NULL,
  CONSTRAINT [PK_Sis_Usuarios]
        PRIMARY KEY CLUSTERED ([CveUsuario]),
  CONSTRAINT [UQ_Sis_Usuarios_NombreUsuario]
        UNIQUE NONCLUSTERED ([NombreUsuario])
)
;
GO

CREATE TABLE [dbo].[Sis_Modulos] (
  [CveModulo] int IDENTITY(1,1) NOT NULL,
  [CveModuloPadre] int NULL,
  [CvePlataforma] int NOT NULL,
  [NombreModulo] varchar(150) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Descripcion] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [Url] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
  [ClaseCssIcono] varchar(150) COLLATE Modern_Spanish_CS_AI NULL,
  [ClaseCssEstilo] varchar(150) COLLATE Modern_Spanish_CS_AI NULL,
  [OrdenVisualizacion] int CONSTRAINT [DF_Sis_Modulos_OrdenVisualizacion] DEFAULT ((0)) NOT NULL,
  [Visible] bit CONSTRAINT [DF_Sis_Modulos_Visible] DEFAULT ((1)) NOT NULL,
  [Activo] bit CONSTRAINT [DF_Sis_Modulos_Activo] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Modulos_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_Modulos]
        PRIMARY KEY CLUSTERED ([CveModulo])
)
;
GO

CREATE TABLE [dbo].[Sis_RolesPermisos] (
  [CveRolPermiso] int IDENTITY(1,1) NOT NULL,
  [CveRol] int NOT NULL,
  [CvePermiso] int NOT NULL,
  [Permitido] bit CONSTRAINT [DF_Sis_RolesPermisos_Permitido] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_RolesPermisos_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_RolesPermisos]
        PRIMARY KEY CLUSTERED ([CveRolPermiso]),
  CONSTRAINT [UQ_Sis_RolesPermisos]
        UNIQUE NONCLUSTERED ([CveRol], [CvePermiso])
)
;
GO

CREATE TABLE [dbo].[Sis_RolesModulos] (
  [CveRolModulo] int IDENTITY(1,1) NOT NULL,
  [CveRol] int NOT NULL,
  [CveModulo] int NOT NULL,
  [PuedeVer] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeVer] DEFAULT ((1)) NOT NULL,
  [PuedeCrear] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeCrear] DEFAULT ((0)) NOT NULL,
  [PuedeModificar] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeModificar] DEFAULT ((0)) NOT NULL,
  [PuedeEliminar] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeEliminar] DEFAULT ((0)) NOT NULL,
  [PuedeImprimir] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeImprimir] DEFAULT ((0)) NOT NULL,
  [PuedeExportar] bit CONSTRAINT [DF_Sis_RolesModulos_PuedeExportar] DEFAULT ((0)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_RolesModulos_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_RolesModulos]
        PRIMARY KEY CLUSTERED ([CveRolModulo]),
  CONSTRAINT [UQ_Sis_RolesModulos]
        UNIQUE NONCLUSTERED ([CveRol], [CveModulo])
)
;
GO

CREATE TABLE [dbo].[Sis_UsuariosPermisos] (
  [CveUsuarioPermiso] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NOT NULL,
  [CvePermiso] int NOT NULL,
  [Permitido] bit CONSTRAINT [DF_Sis_UsuariosPermisos_Permitido] DEFAULT ((1)) NOT NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_UsuariosPermisos_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_UsuariosPermisos]
        PRIMARY KEY CLUSTERED ([CveUsuarioPermiso]),
  CONSTRAINT [UQ_Sis_UsuariosPermisos]
        UNIQUE NONCLUSTERED ([CveUsuario], [CvePermiso])
)
;
GO

CREATE TABLE [dbo].[Sis_UsuariosSesiones] (
  [CveUsuarioSesion] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NOT NULL,
  [TokenSesion] varchar(200) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [DireccionIP] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
  [AgenteUsuario] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
  [NombreDominio] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaInicio] datetime CONSTRAINT [DF_Sis_UsuariosSesiones_FechaInicio] DEFAULT (GETDATE()) NOT NULL,
  [FechaUltimaActividad] datetime NULL,
  [FechaFin] datetime NULL,
  [Activo] bit CONSTRAINT [DF_Sis_UsuariosSesiones_Activo] DEFAULT ((1)) NOT NULL,
  CONSTRAINT [PK_Sis_UsuariosSesiones]
        PRIMARY KEY CLUSTERED ([CveUsuarioSesion]),
  CONSTRAINT [UQ_Sis_UsuariosSesiones_TokenSesion]
        UNIQUE NONCLUSTERED ([TokenSesion])
)
;
GO

CREATE TABLE [dbo].[Sis_UsuariosAutorizaciones] (
  [CveUsuarioAutorizacion] int IDENTITY(1,1) NOT NULL,
  [CvePermiso] int NOT NULL,
  [CveUsuarioSolicitante] int NOT NULL,
  [CveUsuarioAutorizador] int NULL,
  [EstadoAutorizacion] varchar(30) COLLATE Modern_Spanish_CS_AI
        CONSTRAINT [DF_Sis_UsuariosAutorizaciones_Estado]
        DEFAULT ('Pendiente') NOT NULL,
  [ComentarioSolicitud] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
  [ComentarioAutorizacion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaSolicitud] datetime
        CONSTRAINT [DF_Sis_UsuariosAutorizaciones_FechaSolicitud]
        DEFAULT (GETDATE()) NOT NULL,
  [FechaAutorizacion] datetime NULL,
  CONSTRAINT [PK_Sis_UsuariosAutorizaciones]
        PRIMARY KEY CLUSTERED ([CveUsuarioAutorizacion]),
  CONSTRAINT [CK_Sis_UsuariosAutorizaciones_Estado]
        CHECK
        (
            [EstadoAutorizacion] IN
            (
                'Pendiente',
                'Aprobada',
                'Rechazada',
                'Cancelada'
            )
        )
)
;
GO

CREATE TABLE [dbo].[Sis_UsuariosSuscripcionesPush] (
  [CveSuscripcionPush] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NOT NULL,
  [Endpoint] varchar(1000) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [P256DH] varchar(500) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [ClaveAutenticacion] varchar(500) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Activo] bit CONSTRAINT [DF_Sis_UsuariosSuscripcionesPush_Activo] DEFAULT ((1)) NOT NULL,
  [AgenteUsuario] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
  [Plataforma] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
  [Navegador] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
  [NombreDominio] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaUltimaConexion] datetime NULL,
  [FechaCreacion] datetime
        CONSTRAINT [DF_Sis_UsuariosSuscripcionesPush_FechaCreacion]
        DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_UsuariosSuscripcionesPush]
        PRIMARY KEY CLUSTERED ([CveSuscripcionPush])
)
;
GO

CREATE TABLE [dbo].[Sis_Notificaciones] (
  [CveNotificacion] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NOT NULL,
  [TipoNotificacion] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [EstadoNotificacion] varchar(30) COLLATE Modern_Spanish_CS_AI
        CONSTRAINT [DF_Sis_Notificaciones_Estado]
        DEFAULT ('Pendiente') NOT NULL,
  [Titulo] varchar(200) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Mensaje] varchar(max) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Url] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
  [Leida] bit CONSTRAINT [DF_Sis_Notificaciones_Leida] DEFAULT ((0)) NOT NULL,
  [MostradaEnCampana] bit CONSTRAINT [DF_Sis_Notificaciones_MostradaEnCampana] DEFAULT ((0)) NOT NULL,
  [FechaEnvio] datetime NULL,
  [FechaLectura] datetime NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Notificaciones_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  [FechaActualizacion] datetime NULL,
  CONSTRAINT [PK_Sis_Notificaciones]
        PRIMARY KEY CLUSTERED ([CveNotificacion]),
  CONSTRAINT [CK_Sis_Notificaciones_Estado]
        CHECK
        (
            [EstadoNotificacion] IN
            (
                'Pendiente',
                'Enviada',
                'Fallida',
                'Cancelada'
            )
        ),
  CONSTRAINT [CK_Sis_Notificaciones_Tipo]
        CHECK
        (
            [TipoNotificacion] IN
            (
                'Local',
                'WhatsApp',
                'SignalR',
                'Correo',
                'Push',
                'Campana'
            )
        )
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE [dbo].[Sis_NotificacionesEnvios] (
  [CveEnvioNotificacion] int IDENTITY(1,1) NOT NULL,
  [CveNotificacion] int NOT NULL,
  [CanalEnvio] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [EstadoEnvio] varchar(30) COLLATE Modern_Spanish_CS_AI
        CONSTRAINT [DF_Sis_NotificacionesEnvios_Estado]
        DEFAULT ('Pendiente') NOT NULL,
  [RespuestaProveedor] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
  [MensajeError] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaEnvio] datetime NULL,
  [FechaCreacion] datetime
        CONSTRAINT [DF_Sis_NotificacionesEnvios_FechaCreacion]
        DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_NotificacionesEnvios]
        PRIMARY KEY CLUSTERED ([CveEnvioNotificacion]),
  CONSTRAINT [CK_Sis_NotificacionesEnvios_Estado]
        CHECK
        (
            [EstadoEnvio] IN
            (
                'Pendiente',
                'Enviado',
                'Fallido',
                'Cancelado'
            )
        ),
  CONSTRAINT [CK_Sis_NotificacionesEnvios_Canal]
        CHECK
        (
            [CanalEnvio] IN
            (
                'Local',
                'WhatsApp',
                'SignalR',
                'Correo',
                'Push',
                'Campana'
            )
        )
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE [dbo].[Sis_IntentosInicioSesion] (
  [CveIntentoInicioSesion] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NULL,
  [NombreUsuario] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Exitoso] bit NOT NULL,
  [MotivoFallo] varchar(200) COLLATE Modern_Spanish_CS_AI NULL,
  [DireccionIP] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
  [AgenteUsuario] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
  [NombreDominio] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaCreacion] datetime
        CONSTRAINT [DF_Sis_IntentosInicioSesion_FechaCreacion]
        DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_IntentosInicioSesion]
        PRIMARY KEY CLUSTERED ([CveIntentoInicioSesion])
)
;
GO

CREATE TABLE [dbo].[Sis_Auditorias] (
  [CveAuditoria] int IDENTITY(1,1) NOT NULL,
  [CveUsuario] int NULL,
  [TipoEvento] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [NombreEntidad] varchar(150) COLLATE Modern_Spanish_CS_AI NULL,
  [LlaveEntidad] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
  [Descripcion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
  [DireccionIP] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
  [AgenteUsuario] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
  [NombreDominio] varchar(255) COLLATE Modern_Spanish_CS_AI NULL,
  [FechaCreacion] datetime CONSTRAINT [DF_Sis_Auditorias_FechaCreacion] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT [PK_Sis_Auditorias]
        PRIMARY KEY CLUSTERED ([CveAuditoria])
)
;
GO

CREATE TABLE [dbo].[Sis_AuditoriasDetalles] (
  [CveAuditoriaDetalle] int IDENTITY(1,1) NOT NULL,
  [CveAuditoria] int NOT NULL,
  [NombreCampo] varchar(150) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [ValorAnterior] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
  [ValorNuevo] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
  CONSTRAINT [PK_Sis_AuditoriasDetalles]
        PRIMARY KEY CLUSTERED ([CveAuditoriaDetalle])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY];
GO

CREATE TABLE [dbo].[Gral_SAT_ProductosServicios] (
  [CveSATPS] int  IDENTITY(1,1) NOT NULL,
  [SATClavePS] nvarchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Descripcion] nvarchar(600) COLLATE Modern_Spanish_CS_AI  NULL,
  [IncluirIVAtrasladado] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [IncluirIEPStrasladado] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [ComplementoIncluir] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [IncluirIVATrasladado2] bit DEFAULT ((0)) NOT NULL,
  [IncluirIESTrasladado2] bit DEFAULT ((0)) NOT NULL,
  [FechaInicioVigencia] date  NULL,
  [FechaFinVigencia] date  NULL,
  [EstimuloFranjaFronteriza] bit  NULL,
  [PalabrasSimilares] varchar(600) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [AplicaCartaPorte] bit  NULL,
  [MaterialPeligroso] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Gral_SAT_ProductosServicios_1] PRIMARY KEY CLUSTERED ([CveSATPS])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_Categorias] (
  [CveCategoria] int  IDENTITY(1,1) NOT NULL,
  [Categoria] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [Tipo] tinyint DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Categorias] PRIMARY KEY CLUSTERED ([CveCategoria])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UK_Gral_Categorias] UNIQUE NONCLUSTERED ([Categoria] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Clasificaciones] (
  [CveClasificacion] int  IDENTITY(1,1) NOT NULL,
  [Codigo] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Clasificacion] varchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estado] bit DEFAULT ((1)) NOT NULL,
  [Afectable] bit DEFAULT ((1)) NOT NULL,
  [CveCategoria] int  NULL,
  [UsoClasificacion] int DEFAULT ((1)) NULL,
  [Fecha] date  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [RequiereSerieLote] bit  NULL,
  [CveSATPS] int  NULL,
  [RequiereAFCompra] bit DEFAULT ((0)) NULL,
  [RequiereAFInsumo] bit DEFAULT ((0)) NULL,
  [RequierePersonal] bit DEFAULT ((0)) NULL,
  [EsAcumulador] bit DEFAULT ((0)) NULL,
  [TopVentas] bit  NULL,
  [TopCompras] bit  NULL,
  [PrecioBaseCosto] money  NULL,
  [PorMaximoDescuento] smallmoney  NULL,
  [AplicaDepositoCaja] bit DEFAULT ((0)) NULL,
  CONSTRAINT [PK_Clasificaciones] PRIMARY KEY CLUSTERED ([CveClasificacion])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Clasificacion] UNIQUE NONCLUSTERED ([Clasificacion] ASC, [Codigo] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Banc_BancosCuentas] (
  [CveBancoCuenta] int  IDENTITY(1,1) NOT NULL,
  [CveBanco] int  NOT NULL,
  [CveTipoCuenta] int  NOT NULL,
  [NumCuenta] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveMoneda] int DEFAULT ((1)) NOT NULL,
  [CLABE] varchar(18) COLLATE Modern_Spanish_CS_AI  NULL,
  [DiaCorte] int DEFAULT ((0)) NULL,
  [FInicial] int  NOT NULL,
  [FActual] int  NOT NULL,
  [FFinal] int  NOT NULL,
  [LimiteCredito] money DEFAULT ((0)) NOT NULL,
  [UsaLayout] bit DEFAULT ((0)) NOT NULL,
  [FormatoCheque] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ConvenioPagoElectronico] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [PrefijoConta] varchar(2) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [Fecha] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [UsaTarjeta] bit  NULL,
  [SaldoInicial] money  NULL,
  [ConvenioPagoElectronicoGrupos] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [UsaLayoutGrupos] bit  NULL,
  [TieneTerminal] bit DEFAULT ((0)) NOT NULL,
  [CveBancoCuentaPadre] int  NULL,
  [CveSubProyecto] int  NULL,
  [NumeroSucursal] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [EsCajas] bit DEFAULT ((0)) NOT NULL,
  [ConvenioPagoElectronicoNomina] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_BancosCuentas] PRIMARY KEY CLUSTERED ([CveBancoCuenta])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Banc_BancosCuentas] UNIQUE NONCLUSTERED ([CveBanco] ASC, [NumCuenta] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_CodigosAgrupadoresSAT] (
  [CveAgrupadorSAT] int  IDENTITY(1,1) NOT NULL,
  [Codigo] nvarchar(20) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Nombre] nvarchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Tipo] nvarchar(1) COLLATE Modern_Spanish_CS_AI  NULL,
  [CtaMayor] int  NULL,
  [IdRubro] int  NULL,
  [CveAgrupadorPadre] int  NULL,
  [Nivel] smallint  NULL,
  CONSTRAINT [PK__Conta_Co__53FE69089E7C3639] PRIMARY KEY CLUSTERED ([CveAgrupadorSAT])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_Cuentas] (
  [CveCuenta] int  IDENTITY(1,1) NOT NULL,
  [CveClasificacionCuenta] int  NOT NULL,
  [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Nombre] varchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Afectable] bit  NOT NULL,
  [TipoCuenta] int  NOT NULL,
  [Estatus] bit  NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CveCuentaPadre] int  NULL,
  [CveMoneda] int  NULL,
  [CveAgrupadorSAT] int  NULL,
  [DigAgrup] int  NULL,
  [NomIdioma] varchar(250) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveConceptoAdmin] int  NULL,
  [CveCliente] int  NULL,
  [PorcentajeImpuesto] money  NULL,
  [CveBancoCuenta] int  NULL,
  [CodigoVinculacion] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CodigoAgrupacion] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Conta_Cuentas] PRIMARY KEY CLUSTERED ([CveCuenta])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [CK_Conta_Cuentas_Padre] CHECK ([CveCuentaPadre]<>[CveCuenta]),
  CONSTRAINT [CK_Conta_Cuentas_Padre_Requerido] CHECK ([CveCuentaPadre] IS NOT NULL OR ([Codigo]='_ORDEN' OR [Codigo]='_ESTADISTICAS' OR [Codigo]='_GLOBAL'))
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Clientes] (
  [CveCliente] int  IDENTITY(1,1) NOT NULL,
  [Nombre] varchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [NombreCorto] varchar(30) COLLATE Modern_Spanish_CS_AI  NULL,
  [NombreComercial] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  [ApellidoPaterno] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ApellidoMaterno] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Nombres] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Sucursal] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Direccion] varchar(90) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [NoExt] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [NoInt] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Colonia] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Localidad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Ciudad] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estado] varchar(35) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveSATEstado] int  NOT NULL,
  [Pais] varchar(50) COLLATE Modern_Spanish_CS_AI DEFAULT ('México') NOT NULL,
  [RFC] varchar(15) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CP] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Telefono] varchar(25) COLLATE Modern_Spanish_CS_AI  NULL,
  [TelefonoExtension] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [TelefonoOtros] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Contacto] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [Mail] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [Pagina] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Giro] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [EnviarFactura] bit DEFAULT ((1)) NOT NULL,
  [NotificarA] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSucursal] int  NULL,
  [RetIVA] float(53) DEFAULT ((0)) NOT NULL,
  [RetISR] float(53) DEFAULT ((0)) NOT NULL,
  [RetIVAFletes] bit DEFAULT ((0)) NOT NULL,
  [ConvenioCIE] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [LimiteCredito] float(53) DEFAULT ((0)) NOT NULL,
  [PlazoCobranza] int DEFAULT ((0)) NOT NULL,
  [PlazoCobranzaRemision] smallint DEFAULT ((0)) NOT NULL,
  [RecepcionPagos] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoPago] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Vendedor] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveVendedor] int  NULL,
  [Tipo] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [FormaFacturacion] varchar(13) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [Fecha] date  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [NumCtaPago] varchar(30) COLLATE Modern_Spanish_CS_AI  NULL,
  [BKEliminarCveMetodoPago] int  NULL,
  [AplicaFEMSA] bit DEFAULT ((0)) NOT NULL,
  [FEMSA_NumSociedad] varchar(4) COLLATE Modern_Spanish_CS_AI  NULL,
  [FEMSA_NumProveedor] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [FEMSA_Mail] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [AplicaDetallista] bit DEFAULT ((0)) NOT NULL,
  [Detallista_GLNComprador] varchar(18) COLLATE Modern_Spanish_CS_AI  NULL,
  [Detallista_Departamento] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Detallista_GLNVendedor] varchar(18) COLLATE Modern_Spanish_CS_AI  NULL,
  [Detallista_NumProveedor] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [SevenEleven_NumProveedor] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [AplicaFord] bit DEFAULT ((0)) NOT NULL,
  [Ford_GSDB] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [CvePrecioLista] int  NULL,
  [CveCondicionEntrega] int  NULL,
  [PermiteCredito] bit  NULL,
  [MailCotizacion] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveClienteClasificacion] int  NULL,
  [CveUsuarioResponsable] int  NULL,
  [CveUsuarioModifico] int  NULL,
  [ImprimeNombreSucursal] bit DEFAULT ((0)) NULL,
  [TipoCliente] int DEFAULT ((1)) NULL,
  [CveZonaHoraria] int  NULL,
  [CobranzaContacto] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [CobranzaTelefono] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [CobranzaExt] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [CobranzaCorreo] varchar(2000) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSubProyecto] int  NULL,
  [Horario] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Documentacion] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [PortalFacturacion] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSucursal2] int  NULL,
  [DiasPago] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [UsuarioPortal] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [PasswordPortal] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [DiasRevision] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ObservacionRevision] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [ReferenciaFiscal] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [Celular] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [ExtOtros] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveUsoCFDI] tinyint  NULL,
  [CveFormaPago] tinyint  NULL,
  [CveMetodoPago] tinyint  NULL,
  [CveClienteCorporativo] int  NULL,
  [DesglosaIEPS] bit DEFAULT ((1)) NOT NULL,
  [AplicaComercioExterior] bit DEFAULT ((0)) NULL,
  [CveTipoOperacion] int  NULL,
  [CvePedimento] int  NULL,
  [CveIncoterm] int  NULL,
  [CvePais] int  NULL,
  [CveEstado] int  NULL,
  [CveMunicipio] int  NULL,
  [CveColonia] int  NULL,
  [NumRegIdTrib] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveMotivoTraslado] tinyint  NULL,
  [ComExtEstado] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ComExtMunicipio] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ComExtColonia] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [RequiereOC] bit DEFAULT ((0)) NOT NULL,
  [EsDistribuidor] bit DEFAULT ((0)) NOT NULL,
  [BonificacionUsado] bit DEFAULT ((0)) NOT NULL,
  [CargoUsado] bit DEFAULT ((0)) NOT NULL,
  [CveTipoRetencion] tinyint  NULL,
  [CorreoEntregaCompras] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CorreoEntregaAlmacen] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Punto] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveAdminTrayecto] tinyint  NULL,
  [NoDesglosaIVA] bit DEFAULT ((0)) NULL,
  [CorreoCumplimiento] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveRegimenFiscal] tinyint  NULL,
  [RegimenSociedad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoPersona] bit  NULL,
  [RepresentanteLegal] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaLimiteCredito] date  NULL,
  [ComExtCalle] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ComExtNumExt] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ComExtCP] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Genero] bit  NULL,
  [CveAlmacenAsignado] int  NULL,
  [CveAlmacenAlterno] int  NULL,
  [CveAlmacenConsignacion] int  NULL,
  [UsaProductoAlterno] bit DEFAULT ((0)) NULL,
  [EnDemanda] bit DEFAULT ((0)) NULL,
  [EnQuebranto] bit DEFAULT ((0)) NOT NULL,
  [CreditoRegistroFirmas] varchar(250) COLLATE Modern_Spanish_CS_AI  NULL,
  [GastosEnvio] smallmoney  NULL,
  [EsFilial] bit DEFAULT ((0)) NOT NULL,
  [Aseguradora] bit DEFAULT ((0)) NULL,
  [CveSubProyectoCobranza] int  NULL,
  [InformacionCompleta] bit DEFAULT ((1)) NULL,
  [VentasComplementos] varchar(max) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Clientes] PRIMARY KEY CLUSTERED ([CveCliente])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [CK_Clientes_RFC_Requerido] CHECK ([RFC]<>'')
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_TiposPoliza] (
  [CveTipoPoliza] int  NOT NULL,
  [TipoPoliza] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Conta_TiposPoliza] PRIMARY KEY CLUSTERED ([CveTipoPoliza])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_ContaTipoPoliza] UNIQUE NONCLUSTERED ([TipoPoliza] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_PlantillasPolizas] (
  [CvePlantillaPoliza] int  NOT NULL,
  [PlantillaPoliza] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [TipoPlantilla] bit DEFAULT ((0)) NOT NULL,
  [TipoPoliza] smallint  NOT NULL,
  [Orden] smallint DEFAULT ((0)) NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Conta_PlantillasPolizas] PRIMARY KEY CLUSTERED ([CvePlantillaPoliza])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_Monedas] (
  [CveMoneda] int  IDENTITY(1,1) NOT NULL,
  [Moneda] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Abreviatura] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [AbreviaturaSAT] varchar(5) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [LayoutBBVA] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Alias1] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alias2] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alias3] varchar(30) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [Fecha] datetime DEFAULT (getdate()) NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Monedas] PRIMARY KEY CLUSTERED ([CveMoneda])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Moneda] UNIQUE NONCLUSTERED ([Moneda] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_PrePolizas] (
  [CvePrePoliza] int  IDENTITY(1,1) NOT NULL,
  [CveCliente] int  NULL,
  [Modulo] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CvePlantillaPoliza] int  NOT NULL,
  [CveTipoPoliza] int  NOT NULL,
  [Cargos] money DEFAULT ((0)) NOT NULL,
  [Abonos] money DEFAULT ((0)) NOT NULL,
  [CveMoneda] int  NOT NULL,
  [Fecha] date  NOT NULL,
  [Concepto] varchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [NumeroPoliza] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [FolioPoliza] int  NULL,
  [FolioFijo] int  NULL,
  [FechaConta] datetime  NULL,
  [IdPoliza] int  NULL,
  [CodigoNumeroPoliza] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CvePrePolizaUnificada] int  NULL,
  [CvePrePolizaPadre] int  NULL,
  [CveUsuarioModifico] int  NULL,
  [AnioMes] AS (CONVERT([varchar](6),[Fecha],(112))) PERSISTED,
  [TipoContable] bit DEFAULT ((1)) NOT NULL,
  CONSTRAINT [PK_Conta_PrePolizas] PRIMARY KEY CLUSTERED ([CvePrePoliza])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [CK_Conta_PrePolizas_Concepto] CHECK (rtrim([Concepto])<>''),
  CONSTRAINT [CK_Conta_PrePolizas_Fecha_2000_2050] CHECK ([Fecha]>='2000-01-01' AND [Fecha]<='2050/12/31'),
  CONSTRAINT [CK_Conta_PrePolizas_FolioPoliza] CHECK ([IdPoliza]>(0) AND isnull([FolioPoliza],(0))>(0) OR ([IdPoliza] IS NULL OR [IdPoliza]=(0)))
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_UnidadesMedida] (
  [CveSATUnidadMedida] int  IDENTITY(1,1) NOT NULL,
  [SATClaveUnidadMedida] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Nombre] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Descripcion] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [Simbolo] nvarchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Gral_SAT_UnidadesMedida] PRIMARY KEY CLUSTERED ([CveSATUnidadMedida])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UK_Gral_SAT_UnidadesMedida] UNIQUE NONCLUSTERED ([SATClaveUnidadMedida] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_CP_MaterialPeligroso] (
  [CveMaterialPeligroso] int  IDENTITY(1,1) NOT NULL,
  [ClaveMaterialPeligroso] varchar(10) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [MaterialPeligroso] varchar(390) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [ClaseoDiv] varchar(8) COLLATE Modern_Spanish_CS_AI  NULL,
  [PeligroSecundario] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [NombreTecnico] varchar(400) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaDeInicioDeVigencia] date  NULL,
  [Version] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Gral_SAT_CP_MaterialPeligroso] PRIMARY KEY CLUSTERED ([CveMaterialPeligroso])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[UnidadesMedida] (
  [CveUnidadMedida] int  IDENTITY(1,1) NOT NULL,
  [UnidadMedida] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Abreviatura] varchar(15) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Alias1] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alias2] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alias3] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alias4] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSATUnidadMedida] int  NULL,
  [Estado] bit DEFAULT ((1)) NOT NULL,
  [Fecha] date  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CveUnidadMedidaSAT] int  NULL,
  [CveImpEmpaque] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveImpUM] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_UnidadesMedida] PRIMARY KEY CLUSTERED ([CveUnidadMedida])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_UnidadesMedida] UNIQUE NONCLUSTERED ([Abreviatura] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_ObjetoImpuesto] (
  [CveObjetoImpuesto] tinyint  NOT NULL,
  [ObjetoImpuesto] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CodigoObjetoImpuesto] varchar(2) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  CONSTRAINT [PK_Gral_ObjetoImpuesto] PRIMARY KEY CLUSTERED ([CveObjetoImpuesto])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductosServicios] (
  [CvePS] int  IDENTITY(1,1) NOT NULL,
  [CveClasificacion] int  NOT NULL,
  [CveUnidadMedida] int  NOT NULL,
  [CveClasificacionCosto] int  NULL,
  [CveClasificacionPresupuesto] int  NULL,
  [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [NumeroParte] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CodigoBarras] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [PS] varchar(846) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [PSExtranjero] varchar(846) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveMarca] int  NULL,
  [Precio] float(53) DEFAULT ((0)) NOT NULL,
  [IVA] float(53)  NULL,
  [RetIVA] float(53) DEFAULT ((0)) NOT NULL,
  [MargenGanancia] float(53) DEFAULT ((0)) NOT NULL,
  [Estado] bit DEFAULT ((1)) NOT NULL,
  [Fecha] smalldatetime  NOT NULL,
  [IdCtaContVentas] int DEFAULT ((0)) NOT NULL,
  [IdCtaContCompras] int DEFAULT ((0)) NOT NULL,
  [IdCtaContInventarios] int DEFAULT ((0)) NOT NULL,
  [AsientoContableVentas] int DEFAULT ((1)) NOT NULL,
  [AsientoContableCompras] int DEFAULT ((1)) NOT NULL,
  [CveTipoPS] int DEFAULT ((1)) NOT NULL,
  [CveUsuario] int  NOT NULL,
  [Borrame] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSegmentoNegocio] int  NULL,
  [AplicaParaConcepto] bit DEFAULT ((1)) NOT NULL,
  [Inv_CveMetodoInventario] smallint DEFAULT ((0)) NULL,
  [CostoPromedio] money DEFAULT ((0)) NULL,
  [AlmacenSeccion] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [AplicaSerieLote] bit  NULL,
  [AplicaParaVenta] bit DEFAULT ((0)) NOT NULL,
  [AplicaCaducidad] bit DEFAULT ((0)) NULL,
  [AplicaLote] bit DEFAULT ((0)) NOT NULL,
  [AplicaSerie] bit DEFAULT ((0)) NOT NULL,
  [AplicaSeccion] bit DEFAULT ((0)) NOT NULL,
  [AplicaPesoKg] bit DEFAULT ((0)) NOT NULL,
  [EsPaquete] bit DEFAULT ((0)) NULL,
  [CveMoneda] int DEFAULT ((1)) NULL,
  [Imagen] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [VentaSinExistencia] bit DEFAULT ((0)) NULL,
  [FuePorXML] bit DEFAULT ((1)) NULL,
  [EsAdminPAQ] bit DEFAULT ((0)) NULL,
  [PendienteEnOC] bit  NULL,
  [Estado2] smallint  NULL,
  [CveClasificacion2] int  NULL,
  [CodigoPS] AS (case when len([Codigo])>(0) then concat([Codigo],' - ',[PS]) else [PS] end),
  [AplicaIEPS] bit  NULL,
  [IEPSGrabaIVA] bit  NULL,
  [IEPSPorcentaje] float(53)  NULL,
  [CveConceptoContable] int  NULL,
  [CveCompuesto] int  NULL,
  [CveSATPS] int  NULL,
  [Inventariable] bit DEFAULT ((0)) NOT NULL,
  [VentaMinima] smallmoney  NULL,
  [RetISR] float(53)  NULL,
  [PrecioMinimo] money  NULL,
  [CveTipoFactor] int DEFAULT ((1)) NULL,
  [CveTipoFactorIEPS] tinyint  NULL,
  [CveFraccionArancelaria] int  NULL,
  [CveUsuarioModifico] int  NULL,
  [FechaUltimaModificacion] datetime  NULL,
  [CveClasificacionDetalle] int  NULL,
  [CveTamaño] int  NULL,
  [ClaveImpuestoEmpaque] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveImpuestoUM] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveCategoriaToxicidad] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveImpuestoTipoProducto] varchar(3) COLLATE Modern_Spanish_CS_AI  NULL,
  [CvePSAdicional] int  NULL,
  [PrecioMinimoAnterior] money  NULL,
  [Litraje] float(53)  NULL,
  [Code17] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [EsOutSourcing] bit DEFAULT ((0)) NOT NULL,
  [TiempoCaducidad] smallint  NULL,
  [CantidadEmpaque] smallmoney  NULL,
  [PrecioLibre] bit DEFAULT ((0)) NOT NULL,
  [PesoKg] smallmoney  NULL,
  [CveMaterialPeligroso] int  NULL,
  [CveEmbalaje] int  NULL,
  [Dimensiones] varchar(25) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveObjetoImpuesto] tinyint DEFAULT ((2)) NOT NULL,
  [RegistroRSCO] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaVigenciaRSCO] date  NULL,
  [VigenciaIndeterminadaRSCO] bit  NULL,
  [NombreComercial] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [NotasAdicionales] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [EcommerceEtiquetas] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  [ActivoEcommerce] bit DEFAULT ((0)) NOT NULL,
  [EcommercePrecio] smallmoney  NULL,
  [CveClasificacionEcommerce] int  NULL,
  [EcommerceCantidad] smallmoney  NULL,
  [CodigoAlterno] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [PSAlterno] varchar(846) COLLATE Modern_Spanish_CS_AI  NULL,
  [ProductoExhibicion] bit  NULL,
  [PorcentajeMinimoUtilidad] smallmoney  NULL,
  [CveSelectiva] int  NULL,
  [UltimoCosto] numeric(14,6)  NULL,
  [UltimoCostoFecha] date  NULL,
  [UltimoCostoExtranjera] numeric(14,6)  NULL,
  [CveTipoRotacion] tinyint  NULL,
  CONSTRAINT [PK_ProductosServicios] PRIMARY KEY CLUSTERED ([CvePS])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [CK_ProductosServicios_IVA] CHECK ([IVA]=(-1) OR [IVA]=(0) OR [IVA] IS NULL),
  CONSTRAINT [CK_AsientoContableCompras] CHECK ([AsientoContableCompras]=(3) OR [AsientoContableCompras]=(2) OR [AsientoContableCompras]=(1)),
  CONSTRAINT [CK_AsientoContableVentas] CHECK ([AsientoContableVentas]=(2) OR [AsientoContableVentas]=(1))
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Inv_TiposAlmacenes] (
  [CveTipoAlmacen] int  NOT NULL,
  [TipoAlmacen] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] date  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [DeSistema] bit DEFAULT ((0)) NOT NULL,
  CONSTRAINT [PK_INV_TiposAlmacenes] PRIMARY KEY CLUSTERED ([CveTipoAlmacen])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_INV_TiposAlmacenes] UNIQUE NONCLUSTERED ([TipoAlmacen] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Inv_Almacenes] (
  [CveAlmacen] int  IDENTITY(1,1) NOT NULL,
  [Almacen] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveSubProyecto] int  NOT NULL,
  [CveTipoAlmacen] int  NOT NULL,
  [AlmacenAbreviatura] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] date  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [Modalidad] tinyint DEFAULT ((0)) NULL,
  [Inventariable] bit DEFAULT ((0)) NOT NULL,
  [Orden] smallint DEFAULT ((0)) NOT NULL,
  [CveUsuarioAutoriza] int  NULL,
  [CveAlmacenVirtual] int  NULL,
  [EsAlmacenPT] bit DEFAULT ((0)) NULL,
  [CveAlmacenRef1] int  NULL,
  [CveAlmacenRef2] int  NULL,
  [CveAlmacenRef3] int  NULL,
  [CveAlmacenRef4] int  NULL,
  [CveAlmacenRef5] int  NULL,
  CONSTRAINT [PK_INV_Almacenes] PRIMARY KEY CLUSTERED ([CveAlmacen])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_INV_Almacenes] UNIQUE NONCLUSTERED ([Almacen] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vta_ClientesSucursales] (
  [CveClienteSucursal] int  IDENTITY(1,1) NOT NULL,
  [CveCliente] int  NOT NULL,
  [SucursalCliente] varchar(100) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [Ciudad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Colonia] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CP] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [Direccion] varchar(90) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estado] varchar(35) COLLATE Modern_Spanish_CS_AI  NULL,
  [Localidad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Pais] varchar(50) COLLATE Modern_Spanish_CS_AI DEFAULT ('Mexico') NULL,
  [NoExt] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [NoInt] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Telefono] varchar(25) COLLATE Modern_Spanish_CS_AI  NULL,
  [Correo] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [Contacto] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveVendedor] int  NULL,
  [CorreoCotizacion] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [NumCtaPago] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveMetodoPago] int  NULL,
  [LimiteCredito] money  NULL,
  [CveResponsableCobranza] int  NULL,
  [CveMetodoPago33] tinyint  NULL,
  [CveFormaPago33] tinyint  NULL,
  [CveUsoCFDI] tinyint  NULL,
  [Punto] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [RFC] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveAlmacenAsignado] int  NULL,
  [CveAlmacenAlterno] int  NULL,
  [CveAlmacenConsignacion] int  NULL,
  CONSTRAINT [PK_ClientesSucursal] PRIMARY KEY CLUSTERED ([CveClienteSucursal])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Vta_ClientesSucursales] UNIQUE NONCLUSTERED ([CveCliente] ASC, [SucursalCliente] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]

GO

CREATE TABLE [dbo].[Vta_Procedimientos] (
  [CveProcedimiento] int  IDENTITY(1,1) NOT NULL,
  [Procedimiento] varchar(150) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit  NOT NULL,
  [FechaIng] datetime DEFAULT (getdate()) NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Vta_Procedimientos] PRIMARY KEY CLUSTERED ([CveProcedimiento])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ventas] (
  [CveVenta] int  IDENTITY(1,1) NOT NULL,
  [FolioFiscal] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoDocto] varchar(3) COLLATE Modern_Spanish_CS_AI DEFAULT ('Ing') NOT NULL,
  [Serie] varchar(10) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Folio] int  NOT NULL,
  [SerieFolio] AS (concat(case when [Serie]<>'' then [Serie]+'-' else '' end,[Folio])),
  [Fecha] datetime  NOT NULL,
  [FechaTimbrado] datetime  NULL,
  [CveCliente] int  NULL,
  [CveClienteSucursal] int  NULL,
  [SubTotal] float(53)  NOT NULL,
  [Descuento] float(53) DEFAULT ((0)) NOT NULL,
  [Amortizacion] float(53) DEFAULT ((0)) NOT NULL,
  [IVA] float(53)  NOT NULL,
  [IEPS] float(53) DEFAULT ((0.00)) NULL,
  [RetIVA] float(53) DEFAULT ((0)) NOT NULL,
  [RetISR] float(53) DEFAULT ((0)) NOT NULL,
  [ImpuestoLocal] float(53) DEFAULT ((0)) NULL,
  [Total] float(53)  NOT NULL,
  [CveMoneda] int DEFAULT ((1)) NOT NULL,
  [TipoCambio] float(53) DEFAULT ((1)) NOT NULL,
  [CveMetodoPago] int  NULL,
  [CondicionesPago] int DEFAULT ((0)) NOT NULL,
  [FormaPago] int  NULL,
  [MotivoDescuento] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [Observaciones] varchar(2000) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveEstatusFactura] int DEFAULT ((2)) NOT NULL,
  [EnvioMail] int DEFAULT ((0)) NOT NULL,
  [SeTimbro] bit DEFAULT ((0)) NOT NULL,
  [MotivoCancelacion] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaEjecucion] datetime  NOT NULL,
  [CveSubProyecto] int DEFAULT ((0)) NOT NULL,
  [CveVendedor] int DEFAULT ((0)) NULL,
  [Alfa1] varchar(500) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alfa2] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [Alfa3] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [Num1] float(53)  NULL,
  [Num2] float(53)  NULL,
  [Num3] float(53)  NULL,
  [Fech1] date  NULL,
  [Fech2] date  NULL,
  [Fech3] date  NULL,
  [SelloCancelacion] varchar(800) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaCancelacion] datetime  NULL,
  [EstatusCancelacion] tinyint  NULL,
  [IdPoliza] int  NULL,
  [IdPolizaCancelacion] int  NULL,
  [CveVentaPadre] int  NULL,
  [NumeroPersonas] tinyint DEFAULT ((0)) NOT NULL,
  [FechaCierre] datetime  NULL,
  [Propina] float(53)  NULL,
  [CveVentaTerminal] int  NULL,
  [CvePrePoliza] int  NULL,
  [AtencionA] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveUsuario] int  NOT NULL,
  [CveContrato] int  NULL,
  [FechaIng] datetime  NULL,
  [Det_OrdenCompra] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Det_FechaOrdenCompra] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [Otros] money DEFAULT ((0)) NULL,
  [EsFacturaPrueba] bit DEFAULT ((0)) NOT NULL,
  [Ref] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [NumeroCuenta] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaVencimiento2] date  NULL,
  [DiasVencimiento] AS (datediff(day,[FechaVencimiento2],getdate())),
  [CveVentaFacturada] int  NULL,
  [CvePAC] int  NULL,
  [Vigencia] date  NULL,
  [Entrega] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [TiempoEntrega] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveAlmacenTraspasoDestino] int  NULL,
  [CveAlmacenTraspasoOrigen] int  NULL,
  [EsDevolucionPedido] bit  NULL,
  [EsDevolucionVenta] tinyint DEFAULT ((0)) NOT NULL,
  [CveDireccionEnvio] int  NULL,
  [CveOC] int  NULL,
  [RemisionFacturada] bit DEFAULT ((0)) NOT NULL,
  [CvePACCancelacion] int  NULL,
  [TelefonoGenerico] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveCondicionEntrega] int  NULL,
  [PedidoContrato] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [SATValidadoTimbrado] bit DEFAULT ((0)) NULL,
  [SATValidadoCancelado] bit DEFAULT ((0)) NULL,
  [ObservacionesAlmacen] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [CotizacionFacturada] bit  NULL,
  [CveSubProyectoEjecucion] int  NULL,
  [CveSubProyectoCobranza] int  NULL,
  [CveCobranzaEstatus] int  NULL,
  [FechaCobranza] datetime  NULL,
  [CveUsuarioCobranza] int  NULL,
  [CobranzaObservacion] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveCobranzaEstatusTurno] int  NULL,
  [TipoProceso] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoComite] varchar(255) COLLATE Modern_Spanish_CS_AI  NULL,
  [IdContabilidad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveFormaPago33] tinyint  NULL,
  [CveMetodoPago33] tinyint  NULL,
  [CveUsoCFDI] tinyint  NULL,
  [OrdenCompra] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveCuentaTerminal] int  NULL,
  [CveTipoRelacion] int  NULL,
  [ImporteRelacion] money  NULL,
  [CveTipoAmortizacionAnticipo] int  NULL,
  [CveVentaRelacion] int  NULL,
  [Pedido2Facturado] bit DEFAULT ((0)) NULL,
  [EsInterno] bit  NULL,
  [CveAcercamiento] int  NULL,
  [TipoCambioOriginal] float(53)  NULL,
  [TipoCambioValuacion] float(53)  NULL,
  [DiasCredito] int  NULL,
  [EsDiasCreditoFijo] bit DEFAULT ((0)) NULL,
  [SeCancelaRemision] bit DEFAULT ((0)) NULL,
  [CveEstatusRefacturacion] tinyint  NULL,
  [AnticipoEspecial] bit DEFAULT ((0)) NOT NULL,
  [CveUsuarioCambioEstatus] int  NULL,
  [FechaCambioEstatus] datetime  NULL,
  [RealizarDevolucionAlmacen] bit  NULL,
  [CveAlmacenDevolucionVenta] int  NULL,
  [CveMovSalidaAlmacen] tinyint DEFAULT ((1)) NOT NULL,
  [Kilometraje] float(53)  NULL,
  [EsAmortizacion] bit  NULL,
  [CveTipoRetencion] tinyint  NULL,
  [CorreoNotificacion] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [FormaPaqueteria] int  NULL,
  [VentaDesglosaIEPS] bit  NULL,
  [FacturarSucursal] bit  NULL,
  [CveVentaAutorizacion] int  NULL,
  [CveAlmacenSurtio] int  NULL,
  [ServicioDomicilio] bit  NULL,
  [RFCReceptor] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Adjunto] varchar(150) COLLATE Modern_Spanish_CS_AI  NULL,
  [DevolucionImportePagado] money  NULL,
  [CveMotivoCancelacion] int  NULL,
  [EsGarantia] bit DEFAULT ((0)) NOT NULL,
  [EstadoCancelacion] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveEstadoCancelacion] tinyint  NULL,
  [CveOPAgropecuaria] int  NULL,
  [SelloSolicitudCancelacion] varchar(800) COLLATE Modern_Spanish_CS_AI  NULL,
  [CFDIVersion] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveLineaAdicional] int  NULL,
  [CveLineaAdicionalEstatus] int  NULL,
  [LineaAdicionalComprobante] varchar(250) COLLATE Modern_Spanish_CS_AI  NULL,
  [DescripcionVenta] varchar(300) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveMedico] int  NULL,
  [CveProcedimiento] int  NULL,
  [CveTipoVenta] int  NULL,
  [RequiereOS] bit  NULL,
  [FechaParaSurtir] datetime  NOT NULL,
  [Paciente] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Episodio] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [EsFacturaGlobal] bit DEFAULT ((0)) NULL,
  [CveSATPeriodicidad] int  NULL,
  [CveSATMes] int  NULL,
  [SATAnio] int  NULL,
  [AnticipoAutorizado] int  NULL,
  [CveMotivoDevolucion] int  NULL,
  [Addenda] varchar(2000) COLLATE Modern_Spanish_CS_AI  NULL,
  [Bloqueada] bit DEFAULT ((0)) NULL,
  [CveUsuarioModifico] int  NULL,
  [CveComplementoTimbrado] int  NULL,
  [ComplementoSAT] varchar(2000) COLLATE Modern_Spanish_CS_AI  NULL,
  [Procesando] bit DEFAULT ((0)) NULL,
  CONSTRAINT [PK_Ventas] PRIMARY KEY CLUSTERED ([CveVenta])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UK_Ventas_TipoDocto_Serie_Folio_CveSubProyecto] UNIQUE NONCLUSTERED ([CveSubProyecto] ASC, [TipoDocto] ASC, [Serie] ASC, [Folio] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [CK_Ventas] CHECK ([TipoDocto]='Egs' OR [TipoDocto]='Dev' OR [TipoDocto]='Ing' OR [TipoDocto]='Cot' OR [TipoDocto]='Rem' OR [TipoDocto]='Ped' OR [TipoDocto]='DPe' OR [TipoDocto]='Com' OR [TipoDocto]='OP' OR [TipoDocto]='Nom' OR [TipoDocto]='Ant' OR [TipoDocto]='PeC' OR [TipoDocto]='Pd2' OR [TipoDocto]='PA' OR [TipoDocto]='RNC' OR [TipoDocto]='Rmm'),
  CONSTRAINT [CK_Ventas_TMp_AlmacenInactivo] CHECK ([CveAlmacenTraspasoDestino]<>(172) AND [CveVenta]>(1012251) OR [CveVenta]<=(1012251)),
  CONSTRAINT [CK_Ventas_Dev_CveVentaPadre] CHECK ([TipoDocto]='Dev' AND [CveVentaPadre]>(0) OR [TipoDocto]<>'Dev'),
  CONSTRAINT [CK_Ventas_Moneda_TipoCambio] CHECK ([CveMoneda]>=(2) AND [TipoCambio]>=(10) OR [CveMoneda]=(1) AND [TipoCambio]=(1)),
  CONSTRAINT [CHK_VentasMail] CHECK ([EnvioMail]=(3) OR [EnvioMail]=(2) OR [EnvioMail]=(1) OR [EnvioMail]=(0)),
  CONSTRAINT [CK_Ventas_1] CHECK ([CveCliente]>(0)),
  CONSTRAINT [CK_Ventas_FechaEjecucion_MinMax] CHECK ([FechaEjecucion]>'1900-01-01' AND [FechaEjecucion]<'2900-12-31'),
  CONSTRAINT [CK_Ventas_Total] CHECK (datepart(year,[Fecha])>=(2018) AND abs(round([Total],(2))-round(((((((isnull([SubTotal],(0))+isnull([IVA],(0)))+isnull([IEPS],(0)))-isnull([Amortizacion],(0)))-isnull([RetIVA],(0)))-isnull([RetISR],(0)))-isnull([Descuento],(0)))-isnull([ImpuestoLocal],(0)),(2)))<(200.02) OR datepart(year,[Fecha])<(2018))
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_PrePolizasDetalle] (
  [CvePrePolizaDetalle] int  IDENTITY(1,1) NOT NULL,
  [CvePrePoliza] int  NOT NULL,
  [CvePrePolizaOriginal] int  NULL,
  [CveConceptoContable] int  NOT NULL,
  [TipoMovto] bit  NOT NULL,
  [Importe] money  NOT NULL,
  [ImporteME] money  NULL,
  [ImporteMN] money  NULL,
  [TC] decimal(12,9) DEFAULT ((1)) NULL,
  [TCPago] decimal(12,9) DEFAULT ((1)) NULL,
  [Contabilizable] bit DEFAULT ((1)) NOT NULL,
  [Bloqueado] bit DEFAULT ((0)) NOT NULL,
  [IdCtaContable] int DEFAULT ((0)) NOT NULL,
  [CveCuenta] int  NULL,
  [Referencia] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  [Concepto] varchar(200) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveCliente] int  NULL,
  [CveVenta] int  NULL,
  [CveVentaAmortizacion] int  NULL,
  [CveCompra] int  NULL,
  [CveCompraAmortizacion] int  NULL,
  [CveClasificacion] int  NULL,
  [CveTipoGasto] int  NULL,
  [CveSubProyecto] int  NULL,
  [CveIE] int  NULL,
  [CveIEDevolucion] int  NULL,
  [CveMovimientoAlmacen] int  NULL,
  [CvePersonal] int  NULL,
  [CveActivoFijo] int  NULL,
  [CveCreditoDetalle] int  NULL,
  [CveOC] int  NULL,
  [CveOCDetalle] int  NULL,
  [Agrupacion] int  NULL,
  [PorcentajeImpuesto] smallmoney  NULL,
  [CveNominaMovtoPermanente] int  NULL,
  [Orden] smallint  NULL,
  [CveUsuarioModifico] int  NULL,
  [CveColectiva] smallint  NULL,
  [CveNominaConcepto] int  NULL,
  CONSTRAINT [PK_Conta_PrePolizasDetalle] PRIMARY KEY CLUSTERED ([CvePrePolizaDetalle])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_Localidad] (
  [CveLocalidad] int  IDENTITY(1,1) NOT NULL,
  [ClaveSATLocalidad] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [ClaveSATEstado] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [NombreLocalidad] varchar(100) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  CONSTRAINT [PK_Gral_SAT_Localidad] PRIMARY KEY CLUSTERED ([CveLocalidad])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_Estado] (
  [CveEstado] int  IDENTITY(1,1) NOT NULL,
  [ClaveSATEstado] varchar(5) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [ClaveSATPais] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [NombreEstado] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [ClaveImpuestoEstado] varchar(2) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveEstadoCorteva] int  NULL,
  CONSTRAINT [PK_Gral_SAT_Estado] PRIMARY KEY CLUSTERED ([CveEstado])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Proyectos] (
  [CveProyecto] int  IDENTITY(1,1) NOT NULL,
  [Proyecto] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CveSucursal] int  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] date DEFAULT (getdate()) NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CveTipoNomina] int DEFAULT ((0)) NOT NULL,
  CONSTRAINT [PK_Proyectos] PRIMARY KEY CLUSTERED ([CveProyecto])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Proyectos] UNIQUE NONCLUSTERED ([Proyecto] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubProyectos] (
  [CveSubProyecto] int  IDENTITY(1,1) NOT NULL,
  [CveProyecto] int  NOT NULL,
  [ClaveSubProyecto] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [SubProyecto] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [IdSegNeg] int DEFAULT ((1)) NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] date DEFAULT (getdate()) NOT NULL,
  [CveUsuario] int  NOT NULL,
  [IdCuentaGasto] int  NULL,
  [CuentaGasto] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSubPrograma] int  NULL,
  [CveClasificacionGeografica] int  NULL,
  [CveSucursal] int  NULL,
  [CveUsuarioNomina] int  NULL,
  [Serie] varchar(15) COLLATE Modern_Spanish_CS_AI  NULL,
  [IVA] float(53)  NULL,
  [Calle] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [NoExt] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [NoInt] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Colonia] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Localidad] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Municipio] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Pais] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [CP] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveSubProyectoPadre] int  NULL,
  [Estado] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [FormatoFactura] varchar(25) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoFormato] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoVenta] tinyint  NULL,
  [Telefono] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [TipoSucursal] tinyint  NULL,
  [EsSucursal] tinyint  NOT NULL,
  [CvePais] int  NULL,
  [CveEstado] int  NULL,
  [CveMunicipio] int  NULL,
  [CveColonia] int  NULL,
  [AplicaComercioExterior] bit  NULL,
  [Correo] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [UTCOffsetVerano] int  NULL,
  [UTCOffsetInvierno] int  NULL,
  [NoVerificaAlmacen] bit  NULL,
  [CodigoSucursal] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [Acum_CentroServicio] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Orden] smallint  NULL,
  [CveLocalidad] int  NULL,
  [Acum_PortalUsuario] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Acum_PortalContrasena] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [DeshabilitaAutorizacion] bit  NULL,
  [CveClientePublicoGral] int  NULL,
  CONSTRAINT [PK_SubProyectos] PRIMARY KEY CLUSTERED ([CveSubProyecto])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_SubProyectos] UNIQUE NONCLUSTERED ([CveProyecto] ASC, [SubProyecto] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_SAT_UsosCFDI] (
  [CveUsoCFDI] tinyint  NOT NULL,
  [UsoCFDI] varchar(5) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Descripcion] varchar(100) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [AplicaPersonaFisica] bit DEFAULT ((0)) NOT NULL,
  [AplicaPersonaMoral] bit DEFAULT ((0)) NOT NULL,
  [RegimenFiscalReceptor] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  CONSTRAINT [PK_Gral_SAT_UsosCFDI] PRIMARY KEY CLUSTERED ([CveUsoCFDI])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vta_Vendedores] (
  [CveVendedor] int  IDENTITY(1,1) NOT NULL,
  [Codigo] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Vendedor] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Correo] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Telefono] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CveEsquemaComision] int  NULL,
  [Celular] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [TituloDocumentos] varchar(150) COLLATE Modern_Spanish_CS_AI  NULL,
  [EsSupervisor] bit  NULL,
  [CveSupervisor] int  NULL,
  [VendedorTipo] tinyint  NULL,
  [CveSuperior] int  NULL,
  [CveSubProyecto] int  NULL,
  [CorreoAdicionalNotificaciones] varchar(200) COLLATE Modern_Spanish_CS_AI  NULL,
  [FechaModificacion] datetime  NULL,
  CONSTRAINT [PK_Vta_Vendedores] PRIMARY KEY CLUSTERED ([CveVendedor])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vta_FormaPago] (
  [CveFormaPago] tinyint  NOT NULL,
  [FormaPago] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveFormaPagoSAT] varchar(2) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveUsuario] int  NOT NULL,
  [FechaIng] date  NULL,
  CONSTRAINT [PK_Vta_FormaPago] PRIMARY KEY CLUSTERED ([CveFormaPago])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sis_PAC] (
  [CvePAC] int  NOT NULL,
  [PAC] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Sis_PAC] PRIMARY KEY CLUSTERED ([CvePAC])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vta_MetodoPago] (
  [CveMetodoPago] tinyint  NOT NULL,
  [MetodoPago] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [ClaveMetodoPagoSAT] varchar(5) COLLATE Modern_Spanish_CS_AI  NULL,
  [CveUsuario] int  NULL,
  [FechaIng] date  NULL,
  CONSTRAINT [PK_Vta_MetodoPago] PRIMARY KEY CLUSTERED ([CveMetodoPago])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vta_TipoDocumentos] (
  [CveTipoDocumento] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [TipoDocumento] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit  NULL,
  [FechaIng] date  NULL,
  [Multiplicador] smallmoney DEFAULT ((1)) NULL,
  [Contable] smallint DEFAULT ((0)) NOT NULL,
  CONSTRAINT [PK_Vta_TipoDocumentos] PRIMARY KEY CLUSTERED ([CveTipoDocumento])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[EstatusFactura] (
  [CveEstatusFactura] int  NOT NULL,
  [EstatusFactura] varchar(30) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estado] bit DEFAULT ((1)) NOT NULL,
  [Fecha] date DEFAULT (getdate()) NOT NULL,
  [CveUsuario] int  NOT NULL,
  [TipoDocto] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [TipoEstatus] smallint DEFAULT ((1)) NULL,
  [Origen] smallmoney DEFAULT ((1)) NULL,
  [EnProcesoCancelacion] bit DEFAULT ((0)) NOT NULL,
  CONSTRAINT [PK_EstatusFactura] PRIMARY KEY CLUSTERED ([CveEstatusFactura])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Gral_RegimenesFiscales] (
  [CveRegimenFiscal] tinyint  NOT NULL,
  [RegimenFiscal] varchar(100) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [CodigoRegimenFiscal] varchar(3) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [AplicaPersonaFisica] bit  NOT NULL,
  [AplicaPersonaMoral] bit  NOT NULL,
  CONSTRAINT [PK_Gral_RegimenesFiscales] PRIMARY KEY CLUSTERED ([CveRegimenFiscal])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[ZonasHorarias] (
  --esta reemplaza GPS_ZonasHorarias
  [CveZonaHoraria] int  NOT NULL,
  [Abreviacion] varchar(7) COLLATE Modern_Spanish_CS_AI  NULL,
  [ZonaHoraria] varchar(100) COLLATE Modern_Spanish_CS_AI  NULL,
  [UTC] varchar(20) COLLATE Modern_Spanish_CS_AI  NULL,
  [UTCOffset] int  NULL,
  CONSTRAINT [PK_ZonasHorarias] PRIMARY KEY CLUSTERED ([CveZonaHoraria])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sat_Bancos] (
  [CveSATBanco] int  IDENTITY(1,1) NOT NULL,
  [CveBanco] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Banco] varchar(150) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [RazonSocial] varchar(300) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Sat_Bancos] PRIMARY KEY CLUSTERED ([CveSATBanco])
WITH (PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UQ_Sat_Bancos_CveBanco] UNIQUE NONCLUSTERED ([CveBanco] ASC)
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Banc_Bancos] (
  [CveBanco] int  IDENTITY(1,1) NOT NULL,
  [Banco] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Ejecutivo] varchar(50) COLLATE Modern_Spanish_CS_AI  NULL,
  [Telefono] varchar(10) COLLATE Modern_Spanish_CS_AI  NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [Fecha] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  [CveSATBanco] int  NULL,
  CONSTRAINT [PK_Bancos] PRIMARY KEY CLUSTERED ([CveBanco])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_Bancos] UNIQUE NONCLUSTERED ([Banco] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Banc_CreditosDetalle] (
  [CveCreditoDetalle] int  IDENTITY(1,1) NOT NULL,
  [Fecha] date  NOT NULL,
  [Estatus] smallint DEFAULT ((0)) NOT NULL,
  [Importe] money DEFAULT ((0)) NOT NULL,
  [Capital] money DEFAULT ((0)) NOT NULL,
  [Intereses] money DEFAULT ((0)) NOT NULL,
  [Seguro] money DEFAULT ((0)) NOT NULL,
  [IVACapital] money DEFAULT ((0)) NOT NULL,
  [IVAIntereses] money DEFAULT ((0)) NOT NULL,
  [IVASeguro] money DEFAULT ((0)) NOT NULL,
  [IVA] AS (([IVACapital]+[IVAIntereses])+[IVASeguro]),
  [Total] AS ((((([Capital]+[Intereses])+[Seguro])+[IVACapital])+[IVAIntereses])+[IVASeguro]),
  [ImporteGarantia] money  NULL,
  [Programado] money  NULL,
  [CveUsuario] int  NULL,
  [ImporteCapital] money  NULL,
  [ImporteInteres] money  NULL,
  CONSTRAINT [PK_Banc_CreditosDetalle] PRIMARY KEY CLUSTERED ([CveCreditoDetalle])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Banc_TiposCuenta] (
  [CveTipoCuenta] int  NOT NULL,
  [TipoCuenta] varchar(50) COLLATE Modern_Spanish_CS_AI  NOT NULL,
  [Estatus] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime  NOT NULL,
  [CveUsuario] int  NOT NULL,
  CONSTRAINT [PK_Banc_TiposCuenta] PRIMARY KEY CLUSTERED ([CveTipoCuenta])
WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY],
  CONSTRAINT [UN_BancTipoCuenta] UNIQUE NONCLUSTERED ([TipoCuenta] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Conta_ConceptosContables] (
  [CveConceptoContable] int NOT NULL,
  [Tipo] varchar(5) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [ConceptoContable] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
  [Estado] bit DEFAULT ((1)) NOT NULL,
  [FechaIng] datetime NOT NULL,
  [CveUsuario] int NOT NULL,
  [TipoConcepto] tinyint DEFAULT ((0)) NOT NULL,
  [Naturaleza] tinyint DEFAULT ((1)) NOT NULL,
  [CveTipoSaldo] int DEFAULT ((0)) NOT NULL,
  [CveParejaContable] int NULL,
  [AplicaTC] bit DEFAULT ((0)) NOT NULL,
  [AplicaSubProyecto] bit DEFAULT ((0)) NOT NULL,
  [AplicaCliente] bit DEFAULT ((0)) NOT NULL,
  [AplicaVenta] bit DEFAULT ((0)) NOT NULL,
  [AplicaCompra] bit DEFAULT ((0)) NOT NULL,
  [AplicaPersonal] bit DEFAULT ((0)) NOT NULL,
  [AplicaClasificacion] bit DEFAULT ((0)) NOT NULL,
  [AplicaBancos] bit DEFAULT ((0)) NOT NULL,
  [AplicaAlmacen] bit DEFAULT ((0)) NOT NULL,
  [EsCuentaUnica] bit DEFAULT ((0)) NOT NULL,
  [EsMonedaExtranjera] bit DEFAULT ((0)) NOT NULL,
  [EsSistema] bit DEFAULT ((0)) NOT NULL,
  [AplicaActivoFijo] bit DEFAULT ((0)) NOT NULL,
  [AplicaNomina] bit DEFAULT ((0)) NOT NULL,
  [EsGlobal] bit DEFAULT ((0)) NOT NULL,
  [AplicaImpuesto] bit DEFAULT ((0)) NOT NULL,
  [EsComplementaria] bit DEFAULT ((0)) NOT NULL,
  [AplicaCredito] bit DEFAULT ((0)) NOT NULL,
  [AplicaMovtoPermanente] bit DEFAULT ((0)) NOT NULL,
  [AplicaColectiva] bit DEFAULT ((0)) NOT NULL,
  [EsPago] bit DEFAULT ((0)) NOT NULL,
  [AplicaTipoGasto] bit DEFAULT ((0)) NOT NULL,
  CONSTRAINT [PK_Conta_ConceptosContables] PRIMARY KEY CLUSTERED ([CveConceptoContable])
  WITH (PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
  CONSTRAINT [UN_Conta_ConceptosContables] UNIQUE NONCLUSTERED ([ConceptoContable] ASC, [Tipo] ASC)
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO

/* ==================== LLAVES FORANEAS ==================== */

ALTER TABLE [dbo].[Sis_Usuarios] ADD CONSTRAINT [FK_Sis_Usuarios_Roles]
        FOREIGN KEY ([CveRol])
        REFERENCES [dbo].[Sis_Roles] ([CveRol])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_Modulos] ADD CONSTRAINT [FK_Sis_Modulos_ModuloPadre]
        FOREIGN KEY ([CveModuloPadre])
        REFERENCES [dbo].[Sis_Modulos] ([CveModulo])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_Modulos] ADD CONSTRAINT [FK_Sis_Modulos_Plataformas]
        FOREIGN KEY ([CvePlataforma])
        REFERENCES [dbo].[Sis_Plataformas] ([CvePlataforma])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_RolesPermisos] ADD CONSTRAINT [FK_Sis_RolesPermisos_Roles]
        FOREIGN KEY ([CveRol])
        REFERENCES [dbo].[Sis_Roles] ([CveRol])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_RolesPermisos] ADD CONSTRAINT [FK_Sis_RolesPermisos_Permisos]
        FOREIGN KEY ([CvePermiso])
        REFERENCES [dbo].[Sis_Permisos] ([CvePermiso])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_RolesModulos] ADD CONSTRAINT [FK_Sis_RolesModulos_Roles]
        FOREIGN KEY ([CveRol])
        REFERENCES [dbo].[Sis_Roles] ([CveRol])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_RolesModulos] ADD CONSTRAINT [FK_Sis_RolesModulos_Modulos]
        FOREIGN KEY ([CveModulo])
        REFERENCES [dbo].[Sis_Modulos] ([CveModulo])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosPermisos] ADD CONSTRAINT [FK_Sis_UsuariosPermisos_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosPermisos] ADD CONSTRAINT [FK_Sis_UsuariosPermisos_Permisos]
        FOREIGN KEY ([CvePermiso])
        REFERENCES [dbo].[Sis_Permisos] ([CvePermiso])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosSesiones] ADD CONSTRAINT [FK_Sis_UsuariosSesiones_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosAutorizaciones] ADD CONSTRAINT [FK_Sis_UsuariosAutorizaciones_Permisos]
        FOREIGN KEY ([CvePermiso])
        REFERENCES [dbo].[Sis_Permisos] ([CvePermiso])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosAutorizaciones] ADD CONSTRAINT [FK_Sis_UsuariosAutorizaciones_UsuarioSolicitante]
        FOREIGN KEY ([CveUsuarioSolicitante])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosAutorizaciones] ADD CONSTRAINT [FK_Sis_UsuariosAutorizaciones_UsuarioAutorizador]
        FOREIGN KEY ([CveUsuarioAutorizador])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_UsuariosSuscripcionesPush] ADD CONSTRAINT [FK_Sis_UsuariosSuscripcionesPush_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_Notificaciones] ADD CONSTRAINT [FK_Sis_Notificaciones_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_NotificacionesEnvios] ADD CONSTRAINT [FK_Sis_NotificacionesEnvios_Notificaciones]
        FOREIGN KEY ([CveNotificacion])
        REFERENCES [dbo].[Sis_Notificaciones] ([CveNotificacion])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_IntentosInicioSesion] ADD CONSTRAINT [FK_Sis_IntentosInicioSesion_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_Auditorias] ADD CONSTRAINT [FK_Sis_Auditorias_Usuarios]
        FOREIGN KEY ([CveUsuario])
        REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_AuditoriasDetalles] ADD CONSTRAINT [FK_Sis_AuditoriasDetalles_Auditorias]
        FOREIGN KEY ([CveAuditoria])
        REFERENCES [dbo].[Sis_Auditorias] ([CveAuditoria])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Gral_Categorias] ADD CONSTRAINT [FK_Categorias_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clasificaciones] ADD CONSTRAINT [FK_Clasificaciones_Categoria] FOREIGN KEY ([CveCategoria]) REFERENCES [dbo].[Gral_Categorias] ([CveCategoria]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clasificaciones] ADD CONSTRAINT [FK_Clasificaciones_Gral_SAT_ProductosServicios] FOREIGN KEY ([CveSATPS]) REFERENCES [dbo].[Gral_SAT_ProductosServicios] ([CveSATPS]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clasificaciones] ADD CONSTRAINT [FK_Clasificaciones_Usuarios_CveUsuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_SubProyectos] FOREIGN KEY ([CveSubProyecto]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_Banc_BancosCuentas1] FOREIGN KEY ([CveBancoCuentaPadre]) REFERENCES [dbo].[Banc_BancosCuentas] ([CveBancoCuenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_Banc_Bancos] FOREIGN KEY ([CveBanco]) REFERENCES [dbo].[Banc_Bancos] ([CveBanco]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_Monedas] FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_BancosCuentas] ADD CONSTRAINT [FK_Banc_BancosCuentas_Banc_TiposCuenta] FOREIGN KEY ([CveTipoCuenta]) REFERENCES [dbo].[Banc_TiposCuenta] ([CveTipoCuenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_Cuentas_Clientes] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_CuentasCodigosAgrupadoresSAT] FOREIGN KEY ([CveAgrupadorSAT]) REFERENCES [dbo].[Conta_CodigosAgrupadoresSAT] ([CveAgrupadorSAT]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_Cuentas_CveCuentaPadre] FOREIGN KEY ([CveCuentaPadre]) REFERENCES [dbo].[Conta_Cuentas] ([CveCuenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_Cuentas_BancosCuentas] FOREIGN KEY ([CveBancoCuenta]) REFERENCES [dbo].[Banc_BancosCuentas] ([CveBancoCuenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Inv_Almacenes] FOREIGN KEY ([CveAlmacenAsignado]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Inv_Almacenes1] FOREIGN KEY ([CveAlmacenAlterno]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Inv_Almacenes2] FOREIGN KEY ([CveAlmacenConsignacion]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Gral_SAT_UsosCFDI] FOREIGN KEY ([CveUsoCFDI]) REFERENCES [dbo].[Gral_SAT_UsosCFDI] ([CveUsoCFDI]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Vta_FormaPago] FOREIGN KEY ([CveFormaPago]) REFERENCES [dbo].[Vta_FormaPago] ([CveFormaPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Vta_MetodoPago] FOREIGN KEY ([CveMetodoPago]) REFERENCES [dbo].[Vta_MetodoPago] ([CveMetodoPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Gral_SAT_Estado] FOREIGN KEY ([CveSATEstado]) REFERENCES [dbo].[Gral_SAT_Estado] ([CveEstado]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Gral_RegimenesFiscales] FOREIGN KEY ([CveRegimenFiscal]) REFERENCES [dbo].[Gral_RegimenesFiscales] ([CveRegimenFiscal]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_GPS_ZonasHorarias] FOREIGN KEY ([CveZonaHoraria]) REFERENCES [dbo].[ZonasHorarias] ([CveZonaHoraria]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Vta_Vendedores_CveVendedor] FOREIGN KEY ([CveVendedor]) REFERENCES [dbo].[Vta_Vendedores] ([CveVendedor]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_TiposPoliza] ADD CONSTRAINT [FK_Conta_TiposPoliza_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PlantillasPolizas] ADD CONSTRAINT [FK_Conta_PlantillasPolizas_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Gral_Monedas] ADD CONSTRAINT [FK_Monedas_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Clientes] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Conta_TiposPoliza] FOREIGN KEY ([CveTipoPoliza]) REFERENCES [dbo].[Conta_TiposPoliza] ([CveTipoPoliza]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Conta_PlantillasPolizas] FOREIGN KEY ([CvePlantillaPoliza]) REFERENCES [dbo].[Conta_PlantillasPolizas] ([CvePlantillaPoliza]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Monedas] FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Conta_PrePolizas] FOREIGN KEY ([CvePrePolizaUnificada]) REFERENCES [dbo].[Conta_PrePolizas] ([CvePrePoliza]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_UsuariosModifico] FOREIGN KEY ([CveUsuarioModifico]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizas] ADD CONSTRAINT [FK_Conta_PrePolizas_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[UnidadesMedida] ADD CONSTRAINT [FK_UnidadesMedida_Gral_SAT_UnidadesMedida] FOREIGN KEY ([CveSATUnidadMedida]) REFERENCES [dbo].[Gral_SAT_UnidadesMedida] ([CveSATUnidadMedida]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[UnidadesMedida] ADD CONSTRAINT [FK_UnidadesMedida_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_UnidadesMedida] FOREIGN KEY ([CveUnidadMedida]) REFERENCES [dbo].[UnidadesMedida] ([CveUnidadMedida]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Clasificaciones1] FOREIGN KEY ([CveClasificacion2]) REFERENCES [dbo].[Clasificaciones] ([CveClasificacion]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Clasificaciones] FOREIGN KEY ([CveClasificacion]) REFERENCES [dbo].[Clasificaciones] ([CveClasificacion]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Usuarios_CveUsuarioModifico] FOREIGN KEY ([CveUsuarioModifico]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Gral_SAT_CP_MaterialPeligroso] FOREIGN KEY ([CveMaterialPeligroso]) REFERENCES [dbo].[Gral_SAT_CP_MaterialPeligroso] ([CveMaterialPeligroso]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Gral_ObjetoImpuesto] FOREIGN KEY ([CveObjetoImpuesto]) REFERENCES [dbo].[Gral_SAT_ObjetoImpuesto] ([CveObjetoImpuesto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Monedas_CveMoneda] FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_TiposAlmacenes] ADD CONSTRAINT [FK_INV_TiposAlmacenes_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_AlmacenesRef1] FOREIGN KEY ([CveAlmacenRef1]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_AlmacenesRef2] FOREIGN KEY ([CveAlmacenRef2]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_AlmacenesRef3] FOREIGN KEY ([CveAlmacenRef3]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_AlmacenesRef4] FOREIGN KEY ([CveAlmacenRef4]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_AlmacenesRef5] FOREIGN KEY ([CveAlmacenRef5]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Sis_Usuarios_CveUsuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_SubProyectos] FOREIGN KEY ([CveSubProyecto]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Sis_Usuarios_CveUsuarioAutoriza] FOREIGN KEY ([CveUsuarioAutoriza]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_Inv_Almacenes_Inv_Almacenes] FOREIGN KEY ([CveAlmacenVirtual]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Inv_Almacenes] ADD CONSTRAINT [FK_INV_Almacenes_INV_TiposAlmacenes] FOREIGN KEY ([CveTipoAlmacen]) REFERENCES [dbo].[Inv_TiposAlmacenes] ([CveTipoAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Vta_FormaPago] FOREIGN KEY ([CveFormaPago33]) REFERENCES [dbo].[Vta_FormaPago] ([CveFormaPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Vta_MetodoPago] FOREIGN KEY ([CveMetodoPago33]) REFERENCES [dbo].[Vta_MetodoPago] ([CveMetodoPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Gral_SAT_UsosCFDI] FOREIGN KEY ([CveUsoCFDI]) REFERENCES [dbo].[Gral_SAT_UsosCFDI] ([CveUsoCFDI]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Vta_Vendedores] FOREIGN KEY ([CveVendedor]) REFERENCES [dbo].[Vta_Vendedores] ([CveVendedor]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Inv_Almacenes] FOREIGN KEY ([CveAlmacenAsignado]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Inv_Almacenes2] FOREIGN KEY ([CveAlmacenAlterno]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Inv_Almacenes3] FOREIGN KEY ([CveAlmacenConsignacion]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_Vta_ClientesSucursales_Clientes_CveCliente] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_ClientesSucursales] ADD CONSTRAINT [FK_SucursalCliente_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_Procedimientos] ADD CONSTRAINT [FK_Vta_Procedimientos_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Inv_Almacenes] FOREIGN KEY ([CveAlmacenTraspasoDestino]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Inv_Almacenes_TraspasoOrigen] FOREIGN KEY ([CveAlmacenTraspasoOrigen]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Inv_Almacenes1] FOREIGN KEY ([CveAlmacenSurtio]) REFERENCES [dbo].[Inv_Almacenes] ([CveAlmacen]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Usuarios1] FOREIGN KEY ([CveUsuarioCobranza]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_Procedimientos] FOREIGN KEY ([CveProcedimiento]) REFERENCES [dbo].[Vta_Procedimientos] ([CveProcedimiento]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_ClientesSucursales] FOREIGN KEY ([CveClienteSucursal]) REFERENCES [dbo].[Vta_ClientesSucursales] ([CveClienteSucursal]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Gral_SAT_UsosCFDI] FOREIGN KEY ([CveUsoCFDI]) REFERENCES [dbo].[Gral_SAT_UsosCFDI] ([CveUsoCFDI]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_Vendedores] FOREIGN KEY ([CveVendedor]) REFERENCES [dbo].[Vta_Vendedores] ([CveVendedor]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_FormaPago] FOREIGN KEY ([CveFormaPago33]) REFERENCES [dbo].[Vta_FormaPago] ([CveFormaPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_EstatusFactura] FOREIGN KEY ([CveEstatusFactura]) REFERENCES [dbo].[EstatusFactura] ([CveEstatusFactura]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Ventas] FOREIGN KEY ([CveVentaFacturada]) REFERENCES [dbo].[Ventas] ([CveVenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Monedas] FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Sis_PACCancelacion] FOREIGN KEY ([CvePACCancelacion]) REFERENCES [dbo].[Sis_PAC] ([CvePAC]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Sis_PAC] FOREIGN KEY ([CvePAC]) REFERENCES [dbo].[Sis_PAC] ([CvePAC]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_MetodoPago] FOREIGN KEY ([CveMetodoPago33]) REFERENCES [dbo].[Vta_MetodoPago] ([CveMetodoPago]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Ventas1] FOREIGN KEY ([CveVenta]) REFERENCES [dbo].[Ventas] ([CveVenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Vta_TipoDocumentos] FOREIGN KEY ([TipoDocto]) REFERENCES [dbo].[Vta_TipoDocumentos] ([CveTipoDocumento]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_SubProyectos2] FOREIGN KEY ([CveSubProyectoCobranza]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Usuarios_CveUsuarioCambioEstatus] FOREIGN KEY ([CveUsuarioCambioEstatus]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_SubProyectos] FOREIGN KEY ([CveSubProyecto]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_SubProyectos1] FOREIGN KEY ([CveSubProyectoEjecucion]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Conta_PrePolizas] FOREIGN KEY ([CvePrePoliza]) REFERENCES [dbo].[Conta_PrePolizas] ([CvePrePoliza]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Clientes] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Clasificaciones] FOREIGN KEY ([CveClasificacion]) REFERENCES [dbo].[Clasificaciones] ([CveClasificacion]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Banc_CreditosDetalle] FOREIGN KEY ([CveCreditoDetalle]) REFERENCES [dbo].[Banc_CreditosDetalle] ([CveCreditoDetalle]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Usuarios] FOREIGN KEY ([CveUsuarioModifico]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Conta_ConceptosContables] FOREIGN KEY ([CveConceptoContable]) REFERENCES [dbo].[Conta_ConceptosContables] ([CveConceptoContable]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Conta_Cuentas] FOREIGN KEY ([CveCuenta]) REFERENCES [dbo].[Conta_Cuentas] ([CveCuenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Clientes] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Ventas] FOREIGN KEY ([CveVenta]) REFERENCES [dbo].[Ventas] ([CveVenta]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Proyectos] ADD CONSTRAINT [FK_Proyectos_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Proyectos] FOREIGN KEY ([CveProyecto]) REFERENCES [dbo].[Proyectos] ([CveProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_SubProyectoPadre] FOREIGN KEY ([CveSubProyectoPadre]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Gral_SAT_Localidad] FOREIGN KEY ([CveLocalidad]) REFERENCES [dbo].[Gral_SAT_Localidad] ([CveLocalidad]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Gral_SAT_Estado] FOREIGN KEY ([CveEstado]) REFERENCES [dbo].[Gral_SAT_Estado] ([CveEstado]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Sis_Usuarios_CveUsuarioNomina] FOREIGN KEY ([CveUsuarioNomina]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Sis_Usuarios_CveUsuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[SubProyectos] ADD CONSTRAINT [FK_SubProyectos_Clientes] FOREIGN KEY ([CveClientePublicoGral]) REFERENCES [dbo].[Clientes] ([CveCliente]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_Vendedores] ADD CONSTRAINT [FK_Vta_Vendedores_SubProyectos] FOREIGN KEY ([CveSubProyecto]) REFERENCES [dbo].[SubProyectos] ([CveSubProyecto]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_Vendedores] ADD CONSTRAINT [FK_Vta_Vendedores_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_FormaPago] ADD CONSTRAINT [FK_Vta_FormaPago_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sis_PAC] ADD CONSTRAINT [FK_Sis_PAC_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Vta_MetodoPago] ADD CONSTRAINT [FK_Vta_MetodoPago_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[EstatusFactura] ADD CONSTRAINT [FK_EstatusFactura_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Sat_Bancos] ADD CONSTRAINT [FK_Sat_Bancos_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_Bancos] ADD CONSTRAINT [FK_Banc_Bancos_Sat_Bancos] FOREIGN KEY ([CveSATBanco]) REFERENCES [dbo].[Sat_Bancos] ([CveSATBanco]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_Bancos] ADD CONSTRAINT [FK_Banc_Bancos_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_CreditosDetalle] ADD CONSTRAINT [FK_Banc_CreditosDetalle_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Banc_TiposCuenta] ADD CONSTRAINT [FK_Banc_TiposCuenta_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_ConceptosContables] ADD CONSTRAINT [FK_Conta_ConceptosContables_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_ConceptosContables] ADD CONSTRAINT [FK_Conta_ConceptosContables_ConceptoPareja] FOREIGN KEY ([CveParejaContable]) REFERENCES [dbo].[Conta_ConceptosContables] ([CveConceptoContable]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Conta_PrePolizas] FOREIGN KEY ([CvePrePoliza]) REFERENCES [dbo].[Conta_PrePolizas] ([CvePrePoliza]);
GO

ALTER TABLE [dbo].[Conta_CodigosAgrupadoresSAT] ADD CONSTRAINT [FK_Conta_CodigosAgrupadoresSAT_Padre] FOREIGN KEY ([CveAgrupadorPadre]) REFERENCES [dbo].[Conta_CodigosAgrupadoresSAT] ([CveAgrupadorSAT]);
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_Cuentas_Sis_Usuarios] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Conta_Cuentas] ADD CONSTRAINT [FK_Conta_Cuentas_Gral_Monedas] FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]);
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Sis_Usuarios_CveUsuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Sis_Usuarios_CveUsuarioResponsable] FOREIGN KEY ([CveUsuarioResponsable]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Clientes] ADD CONSTRAINT [FK_Clientes_Sis_Usuarios_CveUsuarioModifico] FOREIGN KEY ([CveUsuarioModifico]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Ventas] ADD CONSTRAINT [FK_Ventas_Sis_Usuarios_CveUsuarioModifico] FOREIGN KEY ([CveUsuarioModifico]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[EstatusFactura] ADD CONSTRAINT [FK_EstatusFactura_Vta_TipoDocumentos] FOREIGN KEY ([TipoDocto]) REFERENCES [dbo].[Vta_TipoDocumentos] ([CveTipoDocumento]);
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Conta_ConceptosContables] FOREIGN KEY ([CveConceptoContable]) REFERENCES [dbo].[Conta_ConceptosContables] ([CveConceptoContable]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductosServicios] ADD CONSTRAINT [FK_ProductosServicios_Gral_SAT_ProductosServicios] FOREIGN KEY ([CveSATPS]) REFERENCES [dbo].[Gral_SAT_ProductosServicios] ([CveSATPS]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

/*
  Módulo de fraccionamientos, trámites, ventas y pagos.

  Este archivo se ejecuta después de Esquema_BD_AguaHillo.sql.

  Nombres reemplazados:
    Des_Desarrolladoras    -> Clientes
    Des_Desarrollos        -> Fraccionamientos
    Des_EtapasDesarrollo   -> FraccionamientosEtapas
    Des_Lotes              -> FraccionamientosLotes
    Sis_Users              -> Sis_Usuarios

  Regla del fondo inicial:
    - Se registra una Venta con TipoDocto = 'Ant'.
    - La venta usa un producto genérico de ProductosServicios, por ejemplo "Fondo inicial".
    - El detalle del fondo no se relaciona con una solicitud específica.
    - Sólo el dinero efectivamente registrado en Vta_Pagos queda disponible.
    - Vta_AplicacionesSaldoFavor distribuye ese dinero entre trámites posteriores.
    - Ventas, pagos y aplicaciones pueden generar su propia Conta_PrePoliza.
*/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET XACT_ABORT ON;
GO

/* ==================== FRACCIONAMIENTOS ==================== */

CREATE TABLE [dbo].[Fraccionamientos] (
    [CveFraccionamiento] int IDENTITY(1,1) NOT NULL,
    [CveCliente] int NOT NULL,
    [Fraccionamiento] varchar(200) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Hectareas] decimal(18,4) NULL,
    [Ubicacion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
    [Estatus] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
    [EsActivo] bit CONSTRAINT [DF_Fraccionamientos_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Fraccionamientos_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Fraccionamientos] PRIMARY KEY CLUSTERED ([CveFraccionamiento]),
    CONSTRAINT [UQ_Fraccionamientos_Cliente_Nombre] UNIQUE NONCLUSTERED ([CveCliente], [Fraccionamiento]),
    CONSTRAINT [UQ_Fraccionamientos_Cliente] UNIQUE NONCLUSTERED ([CveFraccionamiento], [CveCliente]),
    CONSTRAINT [CK_Fraccionamientos_Hectareas] CHECK ([Hectareas] IS NULL OR [Hectareas] >= (0))
);
GO

CREATE TABLE [dbo].[FraccionamientosEtapas] (
    [CveFraccionamientoEtapa] int IDENTITY(1,1) NOT NULL,
    [CveFraccionamiento] int NOT NULL,
    [Etapa] varchar(150) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Hectareas] decimal(18,4) NULL,
    [Orden] int CONSTRAINT [DF_FraccionamientosEtapas_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_FraccionamientosEtapas_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_FraccionamientosEtapas_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_FraccionamientosEtapas] PRIMARY KEY CLUSTERED ([CveFraccionamientoEtapa]),
    CONSTRAINT [UQ_FraccionamientosEtapas_Fraccionamiento_Etapa] UNIQUE NONCLUSTERED ([CveFraccionamiento], [Etapa]),
    CONSTRAINT [UQ_FraccionamientosEtapas_Fraccionamiento] UNIQUE NONCLUSTERED ([CveFraccionamientoEtapa], [CveFraccionamiento]),
    CONSTRAINT [CK_FraccionamientosEtapas_Hectareas] CHECK ([Hectareas] IS NULL OR [Hectareas] >= (0))
);
GO

CREATE TABLE [dbo].[FraccionamientosLotes] (
    [CveFraccionamientoLote] int IDENTITY(1,1) NOT NULL,
    [CveFraccionamientoEtapa] int NOT NULL,
    [Tipo] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
    [Calle] varchar(200) COLLATE Modern_Spanish_CS_AI NULL,
    [Catastro] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
    [MetrosCuadrados] decimal(18,4) NULL,
    [Numero] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
    [Duplicador] varchar(50) COLLATE Modern_Spanish_CS_AI NULL,
    [EsActivo] bit CONSTRAINT [DF_FraccionamientosLotes_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_FraccionamientosLotes_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_FraccionamientosLotes] PRIMARY KEY CLUSTERED ([CveFraccionamientoLote]),
    CONSTRAINT [CK_FraccionamientosLotes_MetrosCuadrados] CHECK ([MetrosCuadrados] IS NULL OR [MetrosCuadrados] >= (0))
);
GO

/* ==================== CATÁLOGOS DE TRÁMITES ==================== */

CREATE TABLE [dbo].[Tra_TiposTramite] (
    [CveTipoTramite] int IDENTITY(1,1) NOT NULL,
    [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [TipoTramite] varchar(200) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Descripcion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
    [Orden] int CONSTRAINT [DF_Tra_TiposTramite_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_TiposTramite_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_TiposTramite_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Tra_TiposTramite] PRIMARY KEY CLUSTERED ([CveTipoTramite]),
    CONSTRAINT [UQ_Tra_TiposTramite_Codigo] UNIQUE NONCLUSTERED ([Codigo])
);
GO

CREATE TABLE [dbo].[Tra_EstatusSolicitud] (
    [CveEstatusSolicitud] int IDENTITY(1,1) NOT NULL,
    [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [EstatusSolicitud] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Orden] int CONSTRAINT [DF_Tra_EstatusSolicitud_Orden] DEFAULT ((0)) NOT NULL,
    [EsFinal] bit CONSTRAINT [DF_Tra_EstatusSolicitud_EsFinal] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_EstatusSolicitud_EsActivo] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Tra_EstatusSolicitud] PRIMARY KEY CLUSTERED ([CveEstatusSolicitud]),
    CONSTRAINT [UQ_Tra_EstatusSolicitud_Codigo] UNIQUE NONCLUSTERED ([Codigo])
);
GO

CREATE TABLE [dbo].[Tra_TiposDocumento] (
    [CveTipoDocumento] int IDENTITY(1,1) NOT NULL,
    [TipoDocumento] varchar(200) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Descripcion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
    [ExtensionesPermitidas] varchar(200) COLLATE Modern_Spanish_CS_AI NULL,
    [TamanoMaximoMb] int NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_TiposDocumento_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_TiposDocumento_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Tra_TiposDocumento] PRIMARY KEY CLUSTERED ([CveTipoDocumento]),
    CONSTRAINT [UQ_Tra_TiposDocumento_Nombre] UNIQUE NONCLUSTERED ([TipoDocumento]),
    CONSTRAINT [CK_Tra_TiposDocumento_Tamano] CHECK ([TamanoMaximoMb] IS NULL OR [TamanoMaximoMb] > (0))
);
GO

CREATE TABLE [dbo].[Tra_TiposPregunta] (
    [CveTipoPregunta] int IDENTITY(1,1) NOT NULL,
    [Codigo] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [TipoPregunta] varchar(100) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_TiposPregunta_EsActivo] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Tra_TiposPregunta] PRIMARY KEY CLUSTERED ([CveTipoPregunta]),
    CONSTRAINT [UQ_Tra_TiposPregunta_Codigo] UNIQUE NONCLUSTERED ([Codigo])
);
GO

CREATE TABLE [dbo].[Tra_Preguntas] (
    [CvePregunta] int IDENTITY(1,1) NOT NULL,
    [CveTipoPregunta] int NOT NULL,
    [Pregunta] varchar(500) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Descripcion] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
    [EsRequerida] bit CONSTRAINT [DF_Tra_Preguntas_EsRequerida] DEFAULT ((0)) NOT NULL,
    [Orden] int CONSTRAINT [DF_Tra_Preguntas_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_Preguntas_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_Preguntas_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Tra_Preguntas] PRIMARY KEY CLUSTERED ([CvePregunta])
);
GO

CREATE TABLE [dbo].[Tra_OpcionesPregunta] (
    [CveOpcionPregunta] int IDENTITY(1,1) NOT NULL,
    [CvePregunta] int NOT NULL,
    [TextoOpcion] varchar(300) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [ValorOpcion] varchar(300) COLLATE Modern_Spanish_CS_AI NULL,
    [Orden] int CONSTRAINT [DF_Tra_OpcionesPregunta_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_OpcionesPregunta_EsActivo] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Tra_OpcionesPregunta] PRIMARY KEY CLUSTERED ([CveOpcionPregunta]),
    CONSTRAINT [UQ_Tra_OpcionesPregunta_Pregunta_Texto] UNIQUE NONCLUSTERED ([CvePregunta], [TextoOpcion])
);
GO

/* ==================== SOLICITUDES Y FLUJO ==================== */

CREATE TABLE [dbo].[Tra_Solicitudes] (
    [CveSolicitud] int IDENTITY(1,1) NOT NULL,
    [Folio] varchar(50) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [CveCliente] int NOT NULL,
    [CveFraccionamiento] int NOT NULL,
    [CveFraccionamientoEtapa] int NOT NULL,
    [CveTipoTramite] int NOT NULL,
    [CveEstatusSolicitud] int NOT NULL,
    [CveUsuarioSolicita] int NULL,
    [FechaInicio] datetime NULL,
    [FechaEnvio] datetime NULL,
    [FechaAutorizacion] datetime NULL,
    [FechaRechazo] datetime NULL,
    [ComentariosCliente] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
    [ComentariosAdministrativos] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_Solicitudes_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    CONSTRAINT [PK_Tra_Solicitudes] PRIMARY KEY CLUSTERED ([CveSolicitud]),
    CONSTRAINT [UQ_Tra_Solicitudes_Folio] UNIQUE NONCLUSTERED ([Folio]),
    CONSTRAINT [UQ_Tra_Solicitudes_Etapa_TipoTramite] UNIQUE NONCLUSTERED ([CveFraccionamientoEtapa], [CveTipoTramite])
);
GO

CREATE TABLE [dbo].[Tra_SolicitudesDependencias] (
    [CveSolicitudDependencia] int IDENTITY(1,1) NOT NULL,
    [CveSolicitud] int NOT NULL,
    [CveSolicitudRequerida] int NOT NULL,
    [EsObligatoria] bit CONSTRAINT [DF_Tra_SolicitudesDependencias_EsObligatoria] DEFAULT ((1)) NOT NULL,
    [Condicion] varchar(500) COLLATE Modern_Spanish_CS_AI NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_SolicitudesDependencias_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Tra_SolicitudesDependencias] PRIMARY KEY CLUSTERED ([CveSolicitudDependencia]),
    CONSTRAINT [UQ_Tra_SolicitudesDependencias] UNIQUE NONCLUSTERED ([CveSolicitud], [CveSolicitudRequerida]),
    CONSTRAINT [CK_Tra_SolicitudesDependencias_Diferentes] CHECK ([CveSolicitud] <> [CveSolicitudRequerida])
);
GO

CREATE TABLE [dbo].[Tra_HistorialEstatusSolicitud] (
    [CveHistorialEstatusSolicitud] int IDENTITY(1,1) NOT NULL,
    [CveSolicitud] int NOT NULL,
    [CveEstatusSolicitud] int NOT NULL,
    [CveUsuario] int NULL,
    [Comentarios] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_HistorialEstatusSolicitud_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    CONSTRAINT [PK_Tra_HistorialEstatusSolicitud] PRIMARY KEY CLUSTERED ([CveHistorialEstatusSolicitud])
);
GO

CREATE TABLE [dbo].[Tra_DocumentosRequeridos] (
    [CveDocumentoRequerido] int IDENTITY(1,1) NOT NULL,
    [CveTipoTramite] int NOT NULL,
    [CveTipoDocumento] int NOT NULL,
    [EsRequerido] bit CONSTRAINT [DF_Tra_DocumentosRequeridos_EsRequerido] DEFAULT ((1)) NOT NULL,
    [Orden] int CONSTRAINT [DF_Tra_DocumentosRequeridos_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_DocumentosRequeridos_EsActivo] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Tra_DocumentosRequeridos] PRIMARY KEY CLUSTERED ([CveDocumentoRequerido]),
    CONSTRAINT [UQ_Tra_DocumentosRequeridos] UNIQUE NONCLUSTERED ([CveTipoTramite], [CveTipoDocumento])
);
GO

CREATE TABLE [dbo].[Tra_DocumentosSolicitud] (
    [CveDocumentoSolicitud] int IDENTITY(1,1) NOT NULL,
    [CveSolicitud] int NOT NULL,
    [CveTipoDocumento] int NOT NULL,
    [NombreArchivo] varchar(255) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [NombreArchivoFisico] varchar(255) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [RutaArchivo] varchar(1000) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [ContentType] varchar(100) COLLATE Modern_Spanish_CS_AI NULL,
    [TamanoBytes] bigint NULL,
    [ComentarioCliente] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
    [ComentarioAdministrativo] varchar(1000) COLLATE Modern_Spanish_CS_AI NULL,
    [EsAprobado] bit NULL,
    [CveUsuarioCarga] int NULL,
    [CveUsuarioRevisa] int NULL,
    [FechaRevision] datetime NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_DocumentosSolicitud_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    CONSTRAINT [PK_Tra_DocumentosSolicitud] PRIMARY KEY CLUSTERED ([CveDocumentoSolicitud]),
    CONSTRAINT [CK_Tra_DocumentosSolicitud_Tamano] CHECK ([TamanoBytes] IS NULL OR [TamanoBytes] >= (0))
);
GO

CREATE TABLE [dbo].[Tra_PreguntasTramite] (
    [CvePreguntaTramite] int IDENTITY(1,1) NOT NULL,
    [CveTipoTramite] int NOT NULL,
    [CvePregunta] int NOT NULL,
    [EsRequerida] bit CONSTRAINT [DF_Tra_PreguntasTramite_EsRequerida] DEFAULT ((0)) NOT NULL,
    [Orden] int CONSTRAINT [DF_Tra_PreguntasTramite_Orden] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_PreguntasTramite_EsActivo] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Tra_PreguntasTramite] PRIMARY KEY CLUSTERED ([CvePreguntaTramite]),
    CONSTRAINT [UQ_Tra_PreguntasTramite] UNIQUE NONCLUSTERED ([CveTipoTramite], [CvePregunta])
);
GO

CREATE TABLE [dbo].[Tra_RespuestasSolicitud] (
    [CveRespuestaSolicitud] int IDENTITY(1,1) NOT NULL,
    [CveSolicitud] int NOT NULL,
    [CvePregunta] int NOT NULL,
    [CveOpcionPregunta] int NULL,
    [Valor] varchar(max) COLLATE Modern_Spanish_CS_AI NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_RespuestasSolicitud_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NULL,
    CONSTRAINT [PK_Tra_RespuestasSolicitud] PRIMARY KEY CLUSTERED ([CveRespuestaSolicitud]),
    CONSTRAINT [CK_Tra_RespuestasSolicitud_Valor] CHECK ([CveOpcionPregunta] IS NOT NULL OR [Valor] IS NOT NULL)
);
GO

CREATE TABLE [dbo].[Tra_CostosTramite] (
    [CveCostoTramite] int IDENTITY(1,1) NOT NULL,
    [CveTipoTramite] int NOT NULL,
    [CvePS] int NOT NULL,
    [CveMoneda] int NOT NULL,
    [Descripcion] varchar(300) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [Importe] decimal(18,2) CONSTRAINT [DF_Tra_CostosTramite_Importe] DEFAULT ((0)) NOT NULL,
    [FechaInicioVigencia] date NULL,
    [FechaFinVigencia] date NULL,
    [EsActivo] bit CONSTRAINT [DF_Tra_CostosTramite_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_CostosTramite_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [FechaModificacion] datetime NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Tra_CostosTramite] PRIMARY KEY CLUSTERED ([CveCostoTramite]),
    CONSTRAINT [UQ_Tra_CostosTramite_Tipo_Producto_Inicio] UNIQUE NONCLUSTERED ([CveTipoTramite], [CvePS], [FechaInicioVigencia]),
    CONSTRAINT [CK_Tra_CostosTramite_Importe] CHECK ([Importe] >= (0)),
    CONSTRAINT [CK_Tra_CostosTramite_Vigencia] CHECK ([FechaFinVigencia] IS NULL OR [FechaInicioVigencia] IS NULL OR [FechaFinVigencia] >= [FechaInicioVigencia])
);
GO

CREATE TABLE [dbo].[Tra_NotasSolicitud] (
    [CveNotaSolicitud] int IDENTITY(1,1) NOT NULL,
    [CveSolicitud] int NOT NULL,
    [CveUsuario] int NULL,
    [TipoNota] varchar(50) COLLATE Modern_Spanish_CS_AI CONSTRAINT [DF_Tra_NotasSolicitud_TipoNota] DEFAULT ('General') NOT NULL,
    [Comentario] varchar(max) COLLATE Modern_Spanish_CS_AI NOT NULL,
    [EsVisibleParaCliente] bit CONSTRAINT [DF_Tra_NotasSolicitud_Visible] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Tra_NotasSolicitud_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    CONSTRAINT [PK_Tra_NotasSolicitud] PRIMARY KEY CLUSTERED ([CveNotaSolicitud])
);
GO

/* ==================== VENTAS, PAGOS Y SALDO A FAVOR ==================== */

CREATE TABLE [dbo].[VentasDetalle] (
    [CveVentaDetalle] int IDENTITY(1,1) NOT NULL,
    [CveVenta] int NOT NULL,
    [CvePS] int NOT NULL,
    [CveSolicitud] int NULL,
    [CveCostoTramite] int NULL,
    [Cantidad] decimal(18,4) CONSTRAINT [DF_VentasDetalle_Cantidad] DEFAULT ((1)) NOT NULL,
    [PrecioUnitario] decimal(18,2) CONSTRAINT [DF_VentasDetalle_PrecioUnitario] DEFAULT ((0)) NOT NULL,
    [Descuento] decimal(18,2) CONSTRAINT [DF_VentasDetalle_Descuento] DEFAULT ((0)) NOT NULL,
    [IVA] decimal(18,2) CONSTRAINT [DF_VentasDetalle_IVA] DEFAULT ((0)) NOT NULL,
    [Importe] decimal(18,2) CONSTRAINT [DF_VentasDetalle_Importe] DEFAULT ((0)) NOT NULL,
    [EsFondoInicial] bit CONSTRAINT [DF_VentasDetalle_EsFondoInicial] DEFAULT ((0)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_VentasDetalle_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_VentasDetalle] PRIMARY KEY CLUSTERED ([CveVentaDetalle]),
    CONSTRAINT [CK_VentasDetalle_Importes] CHECK ([Cantidad] > (0) AND [PrecioUnitario] >= (0) AND [Descuento] >= (0) AND [IVA] >= (0) AND [Importe] >= (0)),
    CONSTRAINT [CK_VentasDetalle_FondoInicial] CHECK ([EsFondoInicial] = (0) OR ([CveSolicitud] IS NULL AND [CveCostoTramite] IS NULL))
);
GO

CREATE TABLE [dbo].[Vta_Pagos] (
    [CvePago] int IDENTITY(1,1) NOT NULL,
    [CveCliente] int NOT NULL,
    [CveVenta] int NOT NULL,
    [CveSolicitud] int NULL,
    [CveFormaPago] tinyint NOT NULL,
    [CveMoneda] int NOT NULL,
    [CvePrePoliza] int NULL,
    [FechaPago] datetime NOT NULL,
    [Importe] decimal(18,2) NOT NULL,
    [Referencia] varchar(200) COLLATE Modern_Spanish_CS_AI NULL,
    [EsSaldoFavor] bit CONSTRAINT [DF_Vta_Pagos_EsSaldoFavor] DEFAULT ((0)) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Vta_Pagos_EsActivo] DEFAULT ((1)) NOT NULL,
    [FechaAlta] datetime CONSTRAINT [DF_Vta_Pagos_FechaAlta] DEFAULT (GETDATE()) NOT NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Vta_Pagos] PRIMARY KEY CLUSTERED ([CvePago]),
    CONSTRAINT [CK_Vta_Pagos_Importe] CHECK ([Importe] > (0)),
    CONSTRAINT [CK_Vta_Pagos_SaldoFavor] CHECK ([EsSaldoFavor] = (0) OR [CveSolicitud] IS NULL)
);
GO

CREATE TABLE [dbo].[Vta_AplicacionesSaldoFavor] (
    [CveAplicacionSaldoFavor] int IDENTITY(1,1) NOT NULL,
    [CvePagoOrigen] int NOT NULL,
    [CveVentaDestino] int NOT NULL,
    [CveVentaDetalleDestino] int NULL,
    [CveSolicitud] int NOT NULL,
    [CvePrePoliza] int NULL,
    [Importe] decimal(18,2) NOT NULL,
    [FechaAplicacion] datetime CONSTRAINT [DF_Vta_AplicacionesSaldoFavor_Fecha] DEFAULT (GETDATE()) NOT NULL,
    [EsActivo] bit CONSTRAINT [DF_Vta_AplicacionesSaldoFavor_EsActivo] DEFAULT ((1)) NOT NULL,
    [CveUsuario] int NOT NULL,
    CONSTRAINT [PK_Vta_AplicacionesSaldoFavor] PRIMARY KEY CLUSTERED ([CveAplicacionSaldoFavor]),
    CONSTRAINT [CK_Vta_AplicacionesSaldoFavor_Importe] CHECK ([Importe] > (0))
);
GO

/* Relación exacta entre los renglones contables y el movimiento que los originó. */
ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD
    [CveSolicitud] int NULL,
    [CvePago] int NULL,
    [CveAplicacionSaldoFavor] int NULL;
GO

/* ==================== LLAVES FORÁNEAS ==================== */

ALTER TABLE [dbo].[Fraccionamientos] ADD CONSTRAINT [FK_Fraccionamientos_Clientes]
    FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]);
GO
ALTER TABLE [dbo].[Fraccionamientos] ADD CONSTRAINT [FK_Fraccionamientos_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[FraccionamientosEtapas] ADD CONSTRAINT [FK_FraccionamientosEtapas_Fraccionamientos]
    FOREIGN KEY ([CveFraccionamiento]) REFERENCES [dbo].[Fraccionamientos] ([CveFraccionamiento]);
GO
ALTER TABLE [dbo].[FraccionamientosEtapas] ADD CONSTRAINT [FK_FraccionamientosEtapas_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[FraccionamientosLotes] ADD CONSTRAINT [FK_FraccionamientosLotes_FraccionamientosEtapas]
    FOREIGN KEY ([CveFraccionamientoEtapa]) REFERENCES [dbo].[FraccionamientosEtapas] ([CveFraccionamientoEtapa]);
GO
ALTER TABLE [dbo].[FraccionamientosLotes] ADD CONSTRAINT [FK_FraccionamientosLotes_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_TiposTramite] ADD CONSTRAINT [FK_Tra_TiposTramite_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[Tra_TiposDocumento] ADD CONSTRAINT [FK_Tra_TiposDocumento_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[Tra_Preguntas] ADD CONSTRAINT [FK_Tra_Preguntas_Tra_TiposPregunta]
    FOREIGN KEY ([CveTipoPregunta]) REFERENCES [dbo].[Tra_TiposPregunta] ([CveTipoPregunta]);
GO
ALTER TABLE [dbo].[Tra_Preguntas] ADD CONSTRAINT [FK_Tra_Preguntas_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[Tra_OpcionesPregunta] ADD CONSTRAINT [FK_Tra_OpcionesPregunta_Tra_Preguntas]
    FOREIGN KEY ([CvePregunta]) REFERENCES [dbo].[Tra_Preguntas] ([CvePregunta]);
GO

ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_Clientes]
    FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]);
GO
ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_Fraccionamientos_Cliente]
    FOREIGN KEY ([CveFraccionamiento], [CveCliente]) REFERENCES [dbo].[Fraccionamientos] ([CveFraccionamiento], [CveCliente]);
GO
ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_FraccionamientosEtapas]
    FOREIGN KEY ([CveFraccionamientoEtapa], [CveFraccionamiento]) REFERENCES [dbo].[FraccionamientosEtapas] ([CveFraccionamientoEtapa], [CveFraccionamiento]);
GO
ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_Tra_TiposTramite]
    FOREIGN KEY ([CveTipoTramite]) REFERENCES [dbo].[Tra_TiposTramite] ([CveTipoTramite]);
GO
ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_Tra_EstatusSolicitud]
    FOREIGN KEY ([CveEstatusSolicitud]) REFERENCES [dbo].[Tra_EstatusSolicitud] ([CveEstatusSolicitud]);
GO
ALTER TABLE [dbo].[Tra_Solicitudes] ADD CONSTRAINT [FK_Tra_Solicitudes_Sis_Usuarios]
    FOREIGN KEY ([CveUsuarioSolicita]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_SolicitudesDependencias] ADD CONSTRAINT [FK_Tra_SolicitudesDependencias_Solicitud]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_SolicitudesDependencias] ADD CONSTRAINT [FK_Tra_SolicitudesDependencias_Requerida]
    FOREIGN KEY ([CveSolicitudRequerida]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_SolicitudesDependencias] ADD CONSTRAINT [FK_Tra_SolicitudesDependencias_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_HistorialEstatusSolicitud] ADD CONSTRAINT [FK_Tra_HistorialEstatusSolicitud_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_HistorialEstatusSolicitud] ADD CONSTRAINT [FK_Tra_HistorialEstatusSolicitud_Estatus]
    FOREIGN KEY ([CveEstatusSolicitud]) REFERENCES [dbo].[Tra_EstatusSolicitud] ([CveEstatusSolicitud]);
GO
ALTER TABLE [dbo].[Tra_HistorialEstatusSolicitud] ADD CONSTRAINT [FK_Tra_HistorialEstatusSolicitud_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_DocumentosRequeridos] ADD CONSTRAINT [FK_Tra_DocumentosRequeridos_TiposTramite]
    FOREIGN KEY ([CveTipoTramite]) REFERENCES [dbo].[Tra_TiposTramite] ([CveTipoTramite]);
GO
ALTER TABLE [dbo].[Tra_DocumentosRequeridos] ADD CONSTRAINT [FK_Tra_DocumentosRequeridos_TiposDocumento]
    FOREIGN KEY ([CveTipoDocumento]) REFERENCES [dbo].[Tra_TiposDocumento] ([CveTipoDocumento]);
GO
ALTER TABLE [dbo].[Tra_DocumentosSolicitud] ADD CONSTRAINT [FK_Tra_DocumentosSolicitud_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_DocumentosSolicitud] ADD CONSTRAINT [FK_Tra_DocumentosSolicitud_TiposDocumento]
    FOREIGN KEY ([CveTipoDocumento]) REFERENCES [dbo].[Tra_TiposDocumento] ([CveTipoDocumento]);
GO
ALTER TABLE [dbo].[Tra_DocumentosSolicitud] ADD CONSTRAINT [FK_Tra_DocumentosSolicitud_UsuarioCarga]
    FOREIGN KEY ([CveUsuarioCarga]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[Tra_DocumentosSolicitud] ADD CONSTRAINT [FK_Tra_DocumentosSolicitud_UsuarioRevisa]
    FOREIGN KEY ([CveUsuarioRevisa]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_PreguntasTramite] ADD CONSTRAINT [FK_Tra_PreguntasTramite_TiposTramite]
    FOREIGN KEY ([CveTipoTramite]) REFERENCES [dbo].[Tra_TiposTramite] ([CveTipoTramite]);
GO
ALTER TABLE [dbo].[Tra_PreguntasTramite] ADD CONSTRAINT [FK_Tra_PreguntasTramite_Preguntas]
    FOREIGN KEY ([CvePregunta]) REFERENCES [dbo].[Tra_Preguntas] ([CvePregunta]);
GO
ALTER TABLE [dbo].[Tra_RespuestasSolicitud] ADD CONSTRAINT [FK_Tra_RespuestasSolicitud_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_RespuestasSolicitud] ADD CONSTRAINT [FK_Tra_RespuestasSolicitud_Preguntas]
    FOREIGN KEY ([CvePregunta]) REFERENCES [dbo].[Tra_Preguntas] ([CvePregunta]);
GO
ALTER TABLE [dbo].[Tra_RespuestasSolicitud] ADD CONSTRAINT [FK_Tra_RespuestasSolicitud_Opciones]
    FOREIGN KEY ([CveOpcionPregunta]) REFERENCES [dbo].[Tra_OpcionesPregunta] ([CveOpcionPregunta]);
GO
ALTER TABLE [dbo].[Tra_RespuestasSolicitud] ADD CONSTRAINT [FK_Tra_RespuestasSolicitud_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Tra_CostosTramite] ADD CONSTRAINT [FK_Tra_CostosTramite_TiposTramite]
    FOREIGN KEY ([CveTipoTramite]) REFERENCES [dbo].[Tra_TiposTramite] ([CveTipoTramite]);
GO
ALTER TABLE [dbo].[Tra_CostosTramite] ADD CONSTRAINT [FK_Tra_CostosTramite_ProductosServicios]
    FOREIGN KEY ([CvePS]) REFERENCES [dbo].[ProductosServicios] ([CvePS]);
GO
ALTER TABLE [dbo].[Tra_CostosTramite] ADD CONSTRAINT [FK_Tra_CostosTramite_Gral_Monedas]
    FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]);
GO
ALTER TABLE [dbo].[Tra_CostosTramite] ADD CONSTRAINT [FK_Tra_CostosTramite_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO
ALTER TABLE [dbo].[Tra_NotasSolicitud] ADD CONSTRAINT [FK_Tra_NotasSolicitud_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Tra_NotasSolicitud] ADD CONSTRAINT [FK_Tra_NotasSolicitud_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[VentasDetalle] ADD CONSTRAINT [FK_VentasDetalle_Ventas]
    FOREIGN KEY ([CveVenta]) REFERENCES [dbo].[Ventas] ([CveVenta]);
GO
ALTER TABLE [dbo].[VentasDetalle] ADD CONSTRAINT [FK_VentasDetalle_ProductosServicios]
    FOREIGN KEY ([CvePS]) REFERENCES [dbo].[ProductosServicios] ([CvePS]);
GO
ALTER TABLE [dbo].[VentasDetalle] ADD CONSTRAINT [FK_VentasDetalle_Tra_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[VentasDetalle] ADD CONSTRAINT [FK_VentasDetalle_Tra_CostosTramite]
    FOREIGN KEY ([CveCostoTramite]) REFERENCES [dbo].[Tra_CostosTramite] ([CveCostoTramite]);
GO
ALTER TABLE [dbo].[VentasDetalle] ADD CONSTRAINT [FK_VentasDetalle_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Clientes]
    FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Ventas]
    FOREIGN KEY ([CveVenta]) REFERENCES [dbo].[Ventas] ([CveVenta]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Tra_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Vta_FormaPago]
    FOREIGN KEY ([CveFormaPago]) REFERENCES [dbo].[Vta_FormaPago] ([CveFormaPago]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Gral_Monedas]
    FOREIGN KEY ([CveMoneda]) REFERENCES [dbo].[Gral_Monedas] ([CveMoneda]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Conta_PrePolizas]
    FOREIGN KEY ([CvePrePoliza]) REFERENCES [dbo].[Conta_PrePolizas] ([CvePrePoliza]);
GO
ALTER TABLE [dbo].[Vta_Pagos] ADD CONSTRAINT [FK_Vta_Pagos_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_PagoOrigen]
    FOREIGN KEY ([CvePagoOrigen]) REFERENCES [dbo].[Vta_Pagos] ([CvePago]);
GO
ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_VentaDestino]
    FOREIGN KEY ([CveVentaDestino]) REFERENCES [dbo].[Ventas] ([CveVenta]);
GO
ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_DetalleDestino]
    FOREIGN KEY ([CveVentaDetalleDestino]) REFERENCES [dbo].[VentasDetalle] ([CveVentaDetalle]);
GO
ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_Solicitud]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_PrePoliza]
    FOREIGN KEY ([CvePrePoliza]) REFERENCES [dbo].[Conta_PrePolizas] ([CvePrePoliza]);
GO
ALTER TABLE [dbo].[Vta_AplicacionesSaldoFavor] ADD CONSTRAINT [FK_Vta_AplicacionesSaldoFavor_Sis_Usuarios]
    FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]);
GO

ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Tra_Solicitudes]
    FOREIGN KEY ([CveSolicitud]) REFERENCES [dbo].[Tra_Solicitudes] ([CveSolicitud]);
GO
ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Vta_Pagos]
    FOREIGN KEY ([CvePago]) REFERENCES [dbo].[Vta_Pagos] ([CvePago]);
GO
ALTER TABLE [dbo].[Conta_PrePolizasDetalle] ADD CONSTRAINT [FK_Conta_PrePolizasDetalle_Vta_AplicacionesSaldoFavor]
    FOREIGN KEY ([CveAplicacionSaldoFavor]) REFERENCES [dbo].[Vta_AplicacionesSaldoFavor] ([CveAplicacionSaldoFavor]);
GO

/* ==================== ÍNDICES DE OPERACIÓN ==================== */

CREATE NONCLUSTERED INDEX [IX_Tra_Solicitudes_Cliente_Fraccionamiento_Etapa]
    ON [dbo].[Tra_Solicitudes] ([CveCliente], [CveFraccionamiento], [CveFraccionamientoEtapa]);
GO
CREATE NONCLUSTERED INDEX [IX_Tra_Solicitudes_Estatus]
    ON [dbo].[Tra_Solicitudes] ([CveEstatusSolicitud], [FechaAlta]);
GO
CREATE NONCLUSTERED INDEX [IX_Tra_HistorialEstatusSolicitud_Solicitud_Fecha]
    ON [dbo].[Tra_HistorialEstatusSolicitud] ([CveSolicitud], [FechaAlta]);
GO
CREATE NONCLUSTERED INDEX [IX_VentasDetalle_Solicitud]
    ON [dbo].[VentasDetalle] ([CveSolicitud]) INCLUDE ([CveVenta], [CvePS], [Importe]);
GO
CREATE NONCLUSTERED INDEX [IX_Vta_Pagos_Cliente_Venta]
    ON [dbo].[Vta_Pagos] ([CveCliente], [CveVenta], [EsActivo]);
GO
CREATE NONCLUSTERED INDEX [IX_Vta_AplicacionesSaldoFavor_PagoOrigen]
    ON [dbo].[Vta_AplicacionesSaldoFavor] ([CvePagoOrigen], [EsActivo]) INCLUDE ([Importe], [CveSolicitud]);
GO

/* El saldo nunca se almacena: se calcula del pago recibido menos sus aplicaciones activas. */
CREATE VIEW [dbo].[Vta_SaldosFavor]
AS
    SELECT
        P.[CvePago],
        P.[CveCliente],
        P.[CveVenta],
        P.[CveMoneda],
        P.[Importe] AS [ImporteRecibido],
        ISNULL(SUM(CASE WHEN A.[EsActivo] = (1) THEN A.[Importe] ELSE (0) END), (0)) AS [ImporteAplicado],
        P.[Importe] - ISNULL(SUM(CASE WHEN A.[EsActivo] = (1) THEN A.[Importe] ELSE (0) END), (0)) AS [SaldoDisponible]
    FROM [dbo].[Vta_Pagos] AS P
    LEFT JOIN [dbo].[Vta_AplicacionesSaldoFavor] AS A
        ON A.[CvePagoOrigen] = P.[CvePago]
    WHERE P.[EsSaldoFavor] = (1)
      AND P.[EsActivo] = (1)
    GROUP BY P.[CvePago], P.[CveCliente], P.[CveVenta], P.[CveMoneda], P.[Importe];
GO

/* ==================== BORRADO LÓGICO Y LIMPIEZA CONTROLADA ==================== */
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET XACT_ABORT ON;
GO

/*
    Borrado lógico y limpieza controlada.

    Convención:
      Borrado = 0 y FechaBorrado IS NULL: registro vigente.
      Borrado = 1 y FechaBorrado IS NOT NULL: registro borrado lógicamente.

    La limpieza física está deshabilitada por defecto. Cada tabla debe registrarse
    expresamente mediante dbo.Sis_ConfigurarLimpieza y la ejecución inicia siempre
    en modo simulación.
*/

IF OBJECT_ID(N'dbo.Sis_PoliticasLimpieza', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Sis_PoliticasLimpieza]
    (
        [Esquema] sysname NOT NULL,
        [Tabla] sysname NOT NULL,
        [DiasRetencion] int CONSTRAINT [DF_Sis_PoliticasLimpieza_DiasRetencion] DEFAULT ((365)) NOT NULL,
        [TamanoLote] int CONSTRAINT [DF_Sis_PoliticasLimpieza_TamanoLote] DEFAULT ((500)) NOT NULL,
        [Habilitada] bit CONSTRAINT [DF_Sis_PoliticasLimpieza_Habilitada] DEFAULT ((0)) NOT NULL,
        [FechaUltimaLimpieza] datetime2(0) NULL,
        [Borrado] bit CONSTRAINT [DF_Sis_PoliticasLimpieza_Borrado] DEFAULT ((0)) NOT NULL,
        [FechaBorrado] datetime2(0) NULL,
        CONSTRAINT [PK_Sis_PoliticasLimpieza] PRIMARY KEY CLUSTERED ([Esquema], [Tabla]),
        CONSTRAINT [CK_Sis_PoliticasLimpieza_DiasRetencion] CHECK ([DiasRetencion] BETWEEN (30) AND (3650)),
        CONSTRAINT [CK_Sis_PoliticasLimpieza_TamanoLote] CHECK ([TamanoLote] BETWEEN (1) AND (10000)),
        CONSTRAINT [CK_Sis_PoliticasLimpieza_Borrado] CHECK
        (
            ([Borrado] = (0) AND [FechaBorrado] IS NULL)
            OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL)
        )
    );
END;
GO

/* Agrega la convención a todas las tablas de usuario existentes. */
DECLARE @Esquema sysname;
DECLARE @Tabla sysname;
DECLARE @Objeto nvarchar(517);
DECLARE @Sql nvarchar(max);
DECLARE @Errores TABLE ([Esquema] sysname, [Tabla] sysname, [Mensaje] nvarchar(4000));

DECLARE TablasBorradoLogico CURSOR LOCAL FAST_FORWARD FOR
    SELECT S.[name], T.[name]
    FROM sys.tables AS T
    INNER JOIN sys.schemas AS S ON S.[schema_id] = T.[schema_id]
    WHERE T.[is_ms_shipped] = (0)
      AND T.[temporal_type] = (0)
    ORDER BY S.[name], T.[name];

OPEN TablasBorradoLogico;
FETCH NEXT FROM TablasBorradoLogico INTO @Esquema, @Tabla;

WHILE @@FETCH_STATUS = (0)
BEGIN
    SET @Objeto = QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla);

    BEGIN TRY

    IF COL_LENGTH(@Esquema + N'.' + @Tabla, N'Borrado') IS NULL
    BEGIN
        SET @Sql = N'ALTER TABLE ' + @Objeto
                 + N' ADD [Borrado] bit NOT NULL DEFAULT ((0)) WITH VALUES;';
        EXEC sys.sp_executesql @Sql;
    END;

    IF COL_LENGTH(@Esquema + N'.' + @Tabla, N'FechaBorrado') IS NULL
    BEGIN
        SET @Sql = N'ALTER TABLE ' + @Objeto
                 + N' ADD [FechaBorrado] datetime2(0) NULL;';
        EXEC sys.sp_executesql @Sql;
    END;

    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.check_constraints AS C
        WHERE C.[parent_object_id] = OBJECT_ID(@Esquema + N'.' + @Tabla)
          AND C.[name] = N'CK_' + @Tabla + N'_BorradoLogico'
    )
    AND LEN(N'CK_' + @Tabla + N'_BorradoLogico') <= 128
    BEGIN
        SET @Sql = N'ALTER TABLE ' + @Objeto
                 + N' ADD CONSTRAINT ' + QUOTENAME(N'CK_' + @Tabla + N'_BorradoLogico')
                 + N' CHECK (([Borrado] = (0) AND [FechaBorrado] IS NULL)'
                 + N' OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL));';
        EXEC sys.sp_executesql @Sql;
    END;
    END TRY
    BEGIN CATCH
        INSERT INTO @Errores ([Esquema], [Tabla], [Mensaje])
        VALUES (@Esquema, @Tabla, ERROR_MESSAGE());
    END CATCH;

    FETCH NEXT FROM TablasBorradoLogico INTO @Esquema, @Tabla;
END;

CLOSE TablasBorradoLogico;
DEALLOCATE TablasBorradoLogico;

IF EXISTS (SELECT 1 FROM @Errores)
    SELECT [Esquema], [Tabla], [Mensaje] FROM @Errores ORDER BY [Esquema], [Tabla];
GO

CREATE OR ALTER PROCEDURE [dbo].[Sis_ConfigurarLimpieza]
    @Esquema sysname = N'dbo',
    @Tabla sysname,
    @DiasRetencion int = 365,
    @TamanoLote int = 500,
    @Habilitada bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @DiasRetencion NOT BETWEEN 30 AND 3650
        THROW 51000, N'Los días de retención deben estar entre 30 y 3650.', 1;

    IF @TamanoLote NOT BETWEEN 1 AND 10000
        THROW 51001, N'El tamaño de lote debe estar entre 1 y 10000.', 1;

    IF @Esquema = N'dbo' AND @Tabla = N'Sis_PoliticasLimpieza'
        THROW 51002, N'No se permite configurar la limpieza de la tabla de políticas.', 1;

    IF OBJECT_ID(QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla), N'U') IS NULL
        THROW 51003, N'La tabla indicada no existe.', 1;

    IF COL_LENGTH(@Esquema + N'.' + @Tabla, N'Borrado') IS NULL
       OR COL_LENGTH(@Esquema + N'.' + @Tabla, N'FechaBorrado') IS NULL
        THROW 51004, N'La tabla no tiene las columnas de borrado lógico.', 1;

    UPDATE [dbo].[Sis_PoliticasLimpieza]
    SET [DiasRetencion] = @DiasRetencion,
        [TamanoLote] = @TamanoLote,
        [Habilitada] = @Habilitada,
        [Borrado] = (0),
        [FechaBorrado] = NULL
    WHERE [Esquema] = @Esquema
      AND [Tabla] = @Tabla;

    IF @@ROWCOUNT = (0)
    BEGIN
        INSERT INTO [dbo].[Sis_PoliticasLimpieza]
            ([Esquema], [Tabla], [DiasRetencion], [TamanoLote], [Habilitada])
        VALUES
            (@Esquema, @Tabla, @DiasRetencion, @TamanoLote, @Habilitada);
    END;

    DECLARE @NombreIndice sysname = N'IX_BorradoLogico_FechaBorrado';
    DECLARE @SqlIndice nvarchar(max);

    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.indexes
        WHERE [object_id] = OBJECT_ID(QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla))
          AND [name] = @NombreIndice
    )
    BEGIN
        SET @SqlIndice = N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(@NombreIndice)
                       + N' ON ' + QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla)
                       + N' ([FechaBorrado]) WHERE [Borrado] = (1);';
        EXEC sys.sp_executesql @SqlIndice;
    END;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[Sis_LimpiarRegistrosBorrados]
    @SoloSimular bit = 1,
    @ConfirmarBorradoFisico bit = 0,
    @MaximoLotesPorTabla int = 100
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @MaximoLotesPorTabla NOT BETWEEN 1 AND 10000
        THROW 51010, N'El máximo de lotes debe estar entre 1 y 10000.', 1;

    IF @SoloSimular = (0) AND @ConfirmarBorradoFisico = (0)
        THROW 51011, N'Para borrar físicamente debe enviar @ConfirmarBorradoFisico = 1.', 1;

    DECLARE @Resultados TABLE
    (
        [Esquema] sysname NOT NULL,
        [Tabla] sysname NOT NULL,
        [FilasElegibles] bigint NOT NULL,
        [FilasEliminadas] bigint NOT NULL,
        [Resultado] nvarchar(4000) NOT NULL
    );

    DECLARE @Esquema sysname;
    DECLARE @Tabla sysname;
    DECLARE @DiasRetencion int;
    DECLARE @TamanoLote int;
    DECLARE @Objeto nvarchar(517);
    DECLARE @Sql nvarchar(max);
DECLARE @Errores TABLE ([Esquema] sysname, [Tabla] sysname, [Mensaje] nvarchar(4000));
    DECLARE @Elegibles bigint;
    DECLARE @Eliminadas bigint;
    DECLARE @FilasLote int;
    DECLARE @NumeroLote int;

    DECLARE Politicas CURSOR LOCAL FAST_FORWARD FOR
        SELECT [Esquema], [Tabla], [DiasRetencion], [TamanoLote]
        FROM [dbo].[Sis_PoliticasLimpieza]
        WHERE [Habilitada] = (1)
          AND [Borrado] = (0)
        ORDER BY [Esquema], [Tabla];

    OPEN Politicas;
    FETCH NEXT FROM Politicas INTO @Esquema, @Tabla, @DiasRetencion, @TamanoLote;

    WHILE @@FETCH_STATUS = (0)
    BEGIN
        SET @Objeto = QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla);

    BEGIN TRY
        SET @Elegibles = (0);
        SET @Eliminadas = (0);

        BEGIN TRY
            IF OBJECT_ID(@Objeto, N'U') IS NULL
                THROW 51012, N'La tabla configurada ya no existe.', 1;

            SET @Sql = N'SELECT @Cantidad = COUNT_BIG(*) FROM ' + @Objeto
                     + N' WHERE [Borrado] = (1)'
                     + N' AND [FechaBorrado] < DATEADD(DAY, -@Dias, SYSDATETIME());';

            EXEC sys.sp_executesql
                @Sql,
                N'@Dias int, @Cantidad bigint OUTPUT',
                @Dias = @DiasRetencion,
                @Cantidad = @Elegibles OUTPUT;

            IF @SoloSimular = (1)
            BEGIN
                INSERT INTO @Resultados
                    ([Esquema], [Tabla], [FilasElegibles], [FilasEliminadas], [Resultado])
                VALUES
                    (@Esquema, @Tabla, @Elegibles, (0), N'Simulación; no se eliminó información.');
            END
            ELSE
            BEGIN
                SET @NumeroLote = (0);
                SET @FilasLote = (1);

                WHILE @FilasLote > (0) AND @NumeroLote < @MaximoLotesPorTabla
                BEGIN
                    SET @Sql = N'DELETE TOP (@Lote) FROM ' + @Objeto
                             + N' WHERE [Borrado] = (1)'
                             + N' AND [FechaBorrado] < DATEADD(DAY, -@Dias, SYSDATETIME());'
                             + N' SET @Filas = @@ROWCOUNT;';

                    EXEC sys.sp_executesql
                        @Sql,
                        N'@Lote int, @Dias int, @Filas int OUTPUT',
                        @Lote = @TamanoLote,
                        @Dias = @DiasRetencion,
                        @Filas = @FilasLote OUTPUT;

                    SET @Eliminadas += @FilasLote;
                    SET @NumeroLote += (1);
                END;

                UPDATE [dbo].[Sis_PoliticasLimpieza]
                SET [FechaUltimaLimpieza] = SYSDATETIME()
                WHERE [Esquema] = @Esquema
                  AND [Tabla] = @Tabla;

                INSERT INTO @Resultados
                    ([Esquema], [Tabla], [FilasElegibles], [FilasEliminadas], [Resultado])
                VALUES
                    (@Esquema, @Tabla, @Elegibles, @Eliminadas, N'Limpieza física ejecutada.');
            END;
        END TRY
        BEGIN CATCH
            INSERT INTO @Resultados
                ([Esquema], [Tabla], [FilasElegibles], [FilasEliminadas], [Resultado])
            VALUES
                (@Esquema, @Tabla, @Elegibles, @Eliminadas,
                 CONCAT(N'No se eliminó la tabla: ', ERROR_MESSAGE()));
        END CATCH;

        FETCH NEXT FROM Politicas INTO @Esquema, @Tabla, @DiasRetencion, @TamanoLote;
    END;

    CLOSE Politicas;
    DEALLOCATE Politicas;

    SELECT [Esquema], [Tabla], [FilasElegibles], [FilasEliminadas], [Resultado]
    FROM @Resultados
    ORDER BY [Esquema], [Tabla];
END;
GO

/*
    Ejemplos (no se ejecutan automáticamente):

    -- Borrado lógico desde un repositorio/servicio:
    UPDATE dbo.Fraccionamientos
       SET Borrado = 1, FechaBorrado = SYSDATETIME()
     WHERE CveFraccionamiento = @CveFraccionamiento;

    -- Restauración:
    UPDATE dbo.Fraccionamientos
       SET Borrado = 0, FechaBorrado = NULL
     WHERE CveFraccionamiento = @CveFraccionamiento;

    -- Habilitar limpieza de una tabla después de 365 días:
    EXEC dbo.Sis_ConfigurarLimpieza
         @Esquema = N'dbo', @Tabla = N'Fraccionamientos',
         @DiasRetencion = 365, @TamanoLote = 500, @Habilitada = 1;

    -- Revisar qué se eliminaría (modo predeterminado y seguro):
    EXEC dbo.Sis_LimpiarRegistrosBorrados;

    -- Ejecutar la limpieza física confirmada:
    EXEC dbo.Sis_LimpiarRegistrosBorrados
         @SoloSimular = 0, @ConfirmarBorradoFisico = 1;
*/

/* ==================== PROCEDIMIENTOS DE SEGURIDAD DE LA APLICACIÓN ==================== */
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioGetByName]
    @NombreUsuario varchar(100),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        SELECT TOP (1)
            U.[CveUsuario], U.[NombreUsuario], U.[HashContrasena], U.[CorreoElectronico],
            U.[CveRol], R.[NombreRol], U.[Activo], U.[DebeCambiarContrasena],
            U.[IntentosFallidosInicioSesion], U.[BloqueadoHasta],
            U.[FechaUltimoInicioSesion], U.[FechaUltimaConexion]
        FROM [dbo].[Sis_Usuarios] AS U
        INNER JOIN [dbo].[Sis_Roles] AS R ON R.[CveRol] = U.[CveRol]
        WHERE U.[NombreUsuario] = @NombreUsuario
          AND U.[Borrado] = (0)
          AND R.[Borrado] = (0);

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioGetByName'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioLoginSuccessUpdate]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_Usuarios]
        SET [FechaUltimoInicioSesion] = GETDATE(),
            [FechaUltimaConexion] = GETDATE(),
            [IntentosFallidosInicioSesion] = (0),
            [BloqueadoHasta] = NULL,
            [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario
          AND [Borrado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'El usuario ya no está disponible.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al actualizar el inicio de sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioLoginSuccessUpdate'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioLoginFailedUpdate]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_Usuarios]
        SET [IntentosFallidosInicioSesion] = [IntentosFallidosInicioSesion] + (1),
            [BloqueadoHasta] = CASE
                WHEN [IntentosFallidosInicioSesion] + (1) >= (5)
                    THEN DATEADD(MINUTE, (15), GETDATE())
                ELSE [BloqueadoHasta]
            END,
            [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario
          AND [Borrado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'El usuario ya no está disponible.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al registrar el intento de acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioLoginFailedUpdate'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_IntentoInicioSesionAdd]
    @CveUsuario int = NULL,
    @NombreUsuario varchar(100),
    @Exitoso bit,
    @MotivoFallo varchar(200) = NULL,
    @DireccionIP varchar(50) = NULL,
    @AgenteUsuario varchar(1000) = NULL,
    @NombreDominio varchar(255) = NULL,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        INSERT INTO [dbo].[Sis_IntentosInicioSesion]
            ([CveUsuario], [NombreUsuario], [Exitoso], [MotivoFallo],
             [DireccionIP], [AgenteUsuario], [NombreDominio])
        VALUES
            (@CveUsuario, @NombreUsuario, @Exitoso, @MotivoFallo,
             @DireccionIP, @AgenteUsuario, @NombreDominio);

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al registrar el intento de acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_IntentoInicioSesionAdd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSesionAdd]
    @CveUsuario int,
    @TokenSesion varchar(200),
    @DireccionIP varchar(50) = NULL,
    @AgenteUsuario varchar(1000) = NULL,
    @NombreDominio varchar(255) = NULL,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios]
            WHERE [CveUsuario] = @CveUsuario AND [Activo] = (1) AND [Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'El usuario no está disponible para iniciar sesión.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        INSERT INTO [dbo].[Sis_UsuariosSesiones]
            ([CveUsuario], [TokenSesion], [DireccionIP], [AgenteUsuario],
             [NombreDominio], [FechaUltimaActividad])
        VALUES
            (@CveUsuario, @TokenSesion, @DireccionIP, @AgenteUsuario,
             @NombreDominio, GETDATE());

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al crear la sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioSesionAdd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSesionEnd]
    @TokenSesion varchar(200),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        UPDATE [dbo].[Sis_UsuariosSesiones]
        SET [FechaFin] = GETDATE(), [Activo] = (0)
        WHERE [TokenSesion] = @TokenSesion
          AND [Borrado] = (0);

        /* Cerrar una sesión inexistente o ya cerrada es una operación idempotente. */
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al cerrar la sesión; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_UsuarioSesionEnd'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_PermisoCheck]
    @CodigoPermiso varchar(100),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        DECLARE @CveRol int;
        DECLARE @PermisoUsuario bit;

        SELECT @CveRol = [CveRol]
        FROM [dbo].[Sis_Usuarios]
        WHERE [CveUsuario] = @CveUsuarioEjecuta
          AND [Activo] = (1)
          AND [Borrado] = (0);

        SELECT TOP (1) @PermisoUsuario = UP.[Permitido]
        FROM [dbo].[Sis_UsuariosPermisos] AS UP
        INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = UP.[CvePermiso]
        WHERE UP.[CveUsuario] = @CveUsuarioEjecuta
          AND P.[CodigoPermiso] = @CodigoPermiso
          AND P.[Activo] = (1)
          AND UP.[Borrado] = (0)
          AND P.[Borrado] = (0);

        IF @PermisoUsuario IS NOT NULL
            SELECT CAST(@PermisoUsuario AS bit) AS [Permitido];
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_RolesPermisos] AS RP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = RP.[CvePermiso]
            WHERE RP.[CveRol] = @CveRol
              AND P.[CodigoPermiso] = @CodigoPermiso
              AND RP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND RP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SELECT CAST((1) AS bit) AS [Permitido];
        ELSE
            SELECT CAST((0) AS bit) AS [Permitido];

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los permisos; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_PermisoCheck'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_PaginaAccessCheck]
    @PaginaUrl varchar(500),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        DECLARE @CveRol int;
        DECLARE @PuedeAcceder bit = (0);

        SELECT @CveRol = [CveRol]
        FROM [dbo].[Sis_Usuarios]
        WHERE [CveUsuario] = @CveUsuarioEjecuta
          AND [Activo] = (1)
          AND [Borrado] = (0);

        IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_Modulos] AS M
            INNER JOIN [dbo].[Sis_RolesModulos] AS RM ON RM.[CveModulo] = M.[CveModulo]
            WHERE RM.[CveRol] = @CveRol
              AND RM.[PuedeVer] = (1)
              AND M.[Activo] = (1)
              AND M.[Visible] = (1)
              AND RM.[Borrado] = (0)
              AND M.[Borrado] = (0)
              AND
              (
                  LOWER(REPLACE(M.[Url], '~/', '')) = LOWER(@PaginaUrl)
                  OR LOWER(REPLACE(M.[Url], '/', '')) = LOWER(REPLACE(@PaginaUrl, '/', ''))
              )
        )
            SET @PuedeAcceder = (1);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_Modulos] AS M
            WHERE M.[Activo] = (1)
              AND M.[Borrado] = (0)
              AND LOWER(REPLACE(M.[Url], '~/', '')) = LOWER(@PaginaUrl)
        )
            SET @PuedeAcceder = (0);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_UsuariosPermisos] AS UP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = UP.[CvePermiso]
            WHERE UP.[CveUsuario] = @CveUsuarioEjecuta
              AND P.[CodigoPermiso] = 'System.Access'
              AND UP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND UP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SET @PuedeAcceder = (1);
        ELSE IF EXISTS
        (
            SELECT 1
            FROM [dbo].[Sis_RolesPermisos] AS RP
            INNER JOIN [dbo].[Sis_Permisos] AS P ON P.[CvePermiso] = RP.[CvePermiso]
            WHERE RP.[CveRol] = @CveRol
              AND P.[CodigoPermiso] = 'System.Access'
              AND RP.[Permitido] = (1)
              AND P.[Activo] = (1)
              AND RP.[Borrado] = (0)
              AND P.[Borrado] = (0)
        )
            SET @PuedeAcceder = (1);

        SELECT @PuedeAcceder AS [PuedeAcceder];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al validar el acceso; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_PaginaAccessCheck'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_MenuList]
    @CveRol int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;

    BEGIN TRY
        SELECT DISTINCT
            M.[CveModulo], M.[CveModuloPadre], M.[NombreModulo], M.[Url],
            M.[ClaseCssIcono], M.[ClaseCssEstilo], M.[OrdenVisualizacion], M.[Visible]
        FROM [dbo].[Sis_Modulos] AS M
        INNER JOIN [dbo].[Sis_RolesModulos] AS RM ON RM.[CveModulo] = M.[CveModulo]
        WHERE RM.[CveRol] = @CveRol
          AND RM.[PuedeVer] = (1)
          AND M.[Activo] = (1)
          AND M.[Visible] = (1)
          AND RM.[Borrado] = (0)
          AND M.[Borrado] = (0)
        ORDER BY M.[OrdenVisualizacion], M.[NombreModulo];

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el menú; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @ErrorTecnico nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', COALESCE(ERROR_PROCEDURE(), 'PA_Sis_MenuList'), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @ErrorTecnico);
    END CATCH;
END;
GO


/* ==================== USUARIOS, CLIENTES Y AUTORREGISTRO ==================== */

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/* ==================== COLUMNAS DE VERIFICACIÓN ==================== */

IF COL_LENGTH(N'dbo.Sis_Usuarios', N'CorreoVerificado') IS NULL
    ALTER TABLE [dbo].[Sis_Usuarios]
        ADD [CorreoVerificado] bit NOT NULL
            CONSTRAINT [DF_Sis_Usuarios_CorreoVerificado] DEFAULT ((1)) WITH VALUES;
GO

IF COL_LENGTH(N'dbo.Sis_Usuarios', N'FechaVerificacionCorreo') IS NULL
    ALTER TABLE [dbo].[Sis_Usuarios] ADD [FechaVerificacionCorreo] datetime2(0) NULL;
GO

/* ==================== RELACIÓN USUARIO - CLIENTE ==================== */

IF OBJECT_ID(N'dbo.Sis_UsuariosClientes', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Sis_UsuariosClientes]
    (
        [CveUsuarioCliente] int IDENTITY(1,1) NOT NULL,
        [CveUsuario] int NOT NULL,
        [CveCliente] int NOT NULL,
        [Orden] int NOT NULL,
        [EsPrincipal] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_EsPrincipal] DEFAULT ((0)),
        [Activo] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_Activo] DEFAULT ((1)),
        [FechaCreacion] datetime2(0) NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_FechaCreacion] DEFAULT (SYSDATETIME()),
        [FechaActualizacion] datetime2(0) NULL,
        [CveUsuarioRegistro] int NOT NULL,
        [Borrado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosClientes_Borrado] DEFAULT ((0)),
        [FechaBorrado] datetime2(0) NULL,
        CONSTRAINT [PK_Sis_UsuariosClientes] PRIMARY KEY CLUSTERED ([CveUsuarioCliente]),
        CONSTRAINT [CK_Sis_UsuariosClientes_Orden] CHECK ([Orden] > (0)),
        CONSTRAINT [CK_Sis_UsuariosClientes_Borrado] CHECK
        (([Borrado] = (0) AND [FechaBorrado] IS NULL) OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL)),
        CONSTRAINT [FK_Sis_UsuariosClientes_Usuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]),
        CONSTRAINT [FK_Sis_UsuariosClientes_Cliente] FOREIGN KEY ([CveCliente]) REFERENCES [dbo].[Clientes] ([CveCliente]),
        CONSTRAINT [FK_Sis_UsuariosClientes_UsuarioRegistro] FOREIGN KEY ([CveUsuarioRegistro]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario])
    );

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Sis_UsuariosClientes_Usuario_Cliente]
        ON [dbo].[Sis_UsuariosClientes] ([CveUsuario], [CveCliente])
        WHERE [Borrado] = (0);

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Sis_UsuariosClientes_Usuario_Orden]
        ON [dbo].[Sis_UsuariosClientes] ([CveUsuario], [Orden])
        WHERE [Borrado] = (0) AND [Activo] = (1);
END;
GO

/* ==================== VERIFICACIÓN DE CORREO ==================== */

IF OBJECT_ID(N'dbo.Sis_UsuariosVerificacionesCorreo', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Sis_UsuariosVerificacionesCorreo]
    (
        [CveUsuarioVerificacionCorreo] int IDENTITY(1,1) NOT NULL,
        [CveUsuario] int NOT NULL,
        [CveClienteRegistro] int NULL,
        [HashToken] char(64) COLLATE Modern_Spanish_CS_AI NOT NULL,
        [FechaExpiracion] datetime2(0) NOT NULL,
        [FechaVerificacion] datetime2(0) NULL,
        [Utilizado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_Utilizado] DEFAULT ((0)),
        [FechaCreacion] datetime2(0) NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_FechaCreacion] DEFAULT (SYSDATETIME()),
        [Borrado] bit NOT NULL CONSTRAINT [DF_Sis_UsuariosVerificacionesCorreo_Borrado] DEFAULT ((0)),
        [FechaBorrado] datetime2(0) NULL,
        CONSTRAINT [PK_Sis_UsuariosVerificacionesCorreo] PRIMARY KEY CLUSTERED ([CveUsuarioVerificacionCorreo]),
        CONSTRAINT [UQ_Sis_UsuariosVerificacionesCorreo_HashToken] UNIQUE NONCLUSTERED ([HashToken]),
        CONSTRAINT [CK_Sis_UsuariosVerificacionesCorreo_Borrado] CHECK
        (([Borrado] = (0) AND [FechaBorrado] IS NULL) OR ([Borrado] = (1) AND [FechaBorrado] IS NOT NULL)),
        CONSTRAINT [FK_Sis_UsuariosVerificacionesCorreo_Usuario] FOREIGN KEY ([CveUsuario]) REFERENCES [dbo].[Sis_Usuarios] ([CveUsuario]),
        CONSTRAINT [FK_Sis_UsuariosVerificacionesCorreo_Cliente] FOREIGN KEY ([CveClienteRegistro]) REFERENCES [dbo].[Clientes] ([CveCliente])
    );

    CREATE NONCLUSTERED INDEX [IX_Sis_UsuariosVerificacionesCorreo_Pendiente]
        ON [dbo].[Sis_UsuariosVerificacionesCorreo] ([CveUsuario], [Utilizado], [FechaExpiracion])
        WHERE [Borrado] = (0);
END;
GO

/* ==================== CATÁLOGOS ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RolList]
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SELECT [CveRol], [NombreRol], [Descripcion]
        FROM [dbo].[Sis_Roles]
        WHERE [Activo] = (1) AND [Borrado] = (0)
        ORDER BY [NombreRol];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los roles; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Gral_EstadoList]
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SELECT [CveEstado], [NombreEstado], [ClaveSATEstado]
        FROM [dbo].[Gral_SAT_Estado]
        WHERE [Borrado] = (0)
        ORDER BY [NombreEstado];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los estados; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

/* ==================== AUTORREGISTRO ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RegistroClienteValidate]
    @NombreUsuario varchar(100),
    @CorreoElectronico varchar(255),
    @RFC varchar(15),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SET @NombreUsuario = LTRIM(RTRIM(@NombreUsuario));
        SET @CorreoElectronico = LOWER(LTRIM(RTRIM(@CorreoElectronico)));
        SET @RFC = UPPER(REPLACE(LTRIM(RTRIM(@RFC)), ' ', ''));

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario)
        BEGIN
            SET @ErrorMostrar = 'El nombre de usuario ya se encuentra registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = @CorreoElectronico)
        BEGIN
            SET @ErrorMostrar = 'El correo electrónico ya se encuentra registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        /* Incluye clientes activos, inactivos y borrados para impedir autorreactivación. */
        IF EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE UPPER(REPLACE(LTRIM(RTRIM([RFC])), ' ', '')) = @RFC)
        BEGIN
            SET @ErrorMostrar = 'El RFC ya se encuentra registrado. Contacta a un administrador para solicitar acceso.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al validar el registro; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_RegistroClienteAdd]
    @NombreUsuario varchar(100),
    @HashContrasena varchar(500),
    @CorreoElectronico varchar(255),
    @RFC varchar(15),
    @RazonSocial varchar(200),
    @NombreComercial varchar(200) = NULL,
    @Direccion varchar(90),
    @NoExt varchar(20) = NULL,
    @NoInt varchar(50) = NULL,
    @Colonia varchar(50),
    @Ciudad varchar(50),
    @CveSATEstado int,
    @Pais varchar(50) = 'México',
    @CP varchar(10) = NULL,
    @Telefono varchar(25) = NULL,
    @HashToken char(64),
    @HorasExpiracion int = 24,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        DECLARE @CveRolEmpresa int;
        DECLARE @CveUsuario int;
        DECLARE @CveCliente int;
        DECLARE @NombreEstado varchar(35);

        SET @NombreUsuario = LTRIM(RTRIM(@NombreUsuario));
        SET @CorreoElectronico = LOWER(LTRIM(RTRIM(@CorreoElectronico)));
        SET @RFC = UPPER(REPLACE(LTRIM(RTRIM(@RFC)), ' ', ''));

        SELECT @CveRolEmpresa = [CveRol]
        FROM [dbo].[Sis_Roles]
        WHERE [NombreRol] = 'Empresa' AND [Activo] = (1) AND [Borrado] = (0);

        SELECT @NombreEstado = LEFT([NombreEstado], 35)
        FROM [dbo].[Gral_SAT_Estado]
        WHERE [CveEstado] = @CveSATEstado AND [Borrado] = (0);

        IF @CveRolEmpresa IS NULL OR @NombreEstado IS NULL
        BEGIN
            SET @ErrorMostrar = 'No fue posible determinar el rol Empresa o el estado seleccionado.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario)
           OR EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = @CorreoElectronico)
           OR EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE UPPER(REPLACE(LTRIM(RTRIM([RFC])), ' ', '')) = @RFC)
        BEGIN
            SET @ErrorMostrar = 'Los datos ya fueron registrados por otra solicitud. Actualiza la página e inténtalo nuevamente.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        BEGIN TRANSACTION;

        INSERT INTO [dbo].[Sis_Usuarios]
            ([NombreUsuario], [HashContrasena], [CorreoElectronico], [CveRol], [Activo],
             [DebeCambiarContrasena], [CorreoVerificado])
        VALUES
            (@NombreUsuario, @HashContrasena, @CorreoElectronico, @CveRolEmpresa, (0), (0), (0));

        SET @CveUsuario = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO [dbo].[Clientes]
            ([Nombre], [NombreComercial], [Direccion], [NoExt], [NoInt], [Colonia],
             [Ciudad], [Estado], [CveSATEstado], [Pais], [RFC], [CP], [Telefono],
             [Mail], [Estatus], [Fecha], [CveUsuario], [FechaIng])
        VALUES
            (@RazonSocial, NULLIF(@NombreComercial, ''), @Direccion, NULLIF(@NoExt, ''),
             NULLIF(@NoInt, ''), @Colonia, @Ciudad, @NombreEstado, @CveSATEstado,
             COALESCE(NULLIF(@Pais, ''), 'México'), @RFC, NULLIF(@CP, ''),
             NULLIF(@Telefono, ''), @CorreoElectronico, (0), CONVERT(date, GETDATE()),
             @CveUsuario, GETDATE());

        SET @CveCliente = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO [dbo].[Sis_UsuariosClientes]
            ([CveUsuario], [CveCliente], [Orden], [EsPrincipal], [Activo], [CveUsuarioRegistro])
        VALUES
            (@CveUsuario, @CveCliente, (1), (1), (1), @CveUsuario);

        INSERT INTO [dbo].[Sis_UsuariosVerificacionesCorreo]
            ([CveUsuario], [CveClienteRegistro], [HashToken], [FechaExpiracion])
        VALUES
            (@CveUsuario, @CveCliente, @HashToken, DATEADD(HOUR, @HorasExpiracion, SYSDATETIME()));

        COMMIT TRANSACTION;

        SELECT @CveUsuario AS [CveUsuario], @CveCliente AS [CveCliente];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        SET @ErrorMostrar = 'Ocurrió un error al crear el registro; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_CorreoVerify]
    @HashToken char(64),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        DECLARE @CveVerificacion int;
        DECLARE @CveUsuario int;
        DECLARE @CveCliente int;

        SELECT TOP (1)
            @CveVerificacion = [CveUsuarioVerificacionCorreo],
            @CveUsuario = [CveUsuario],
            @CveCliente = [CveClienteRegistro]
        FROM [dbo].[Sis_UsuariosVerificacionesCorreo]
        WHERE [HashToken] = @HashToken
          AND [Utilizado] = (0)
          AND [Borrado] = (0)
          AND [FechaExpiracion] >= SYSDATETIME();

        IF @CveVerificacion IS NULL
        BEGIN
            SET @ErrorMostrar = 'La liga de verificación no es válida o ya expiró.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        BEGIN TRANSACTION;

        UPDATE [dbo].[Sis_UsuariosVerificacionesCorreo]
        SET [Utilizado] = (1), [FechaVerificacion] = SYSDATETIME()
        WHERE [CveUsuarioVerificacionCorreo] = @CveVerificacion AND [Utilizado] = (0);

        IF @@ROWCOUNT = (0)
        BEGIN
            ROLLBACK TRANSACTION;
            SET @ErrorMostrar = 'La liga de verificación ya fue utilizada.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        UPDATE [dbo].[Sis_Usuarios]
        SET [CorreoVerificado] = (1), [FechaVerificacionCorreo] = SYSDATETIME(),
            [Activo] = (1), [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        /* Activación inicial del cliente creado por este mismo token. */
        UPDATE [dbo].[Clientes]
        SET [Estatus] = (1), [CveUsuarioModifico] = @CveUsuario
        WHERE [CveCliente] = @CveCliente AND [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        COMMIT TRANSACTION;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        SET @ErrorMostrar = 'Ocurrió un error al verificar el correo; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

/* ==================== ADMINISTRACIÓN DE USUARIOS ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioList]
    @Texto varchar(150) = NULL,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios] U
            INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta
              AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'No tienes permiso para consultar usuarios.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        SELECT U.[CveUsuario], U.[NombreUsuario], U.[CorreoElectronico], U.[CveRol],
               R.[NombreRol], U.[Activo], U.[CorreoVerificado], U.[FechaCreacion]
        FROM [dbo].[Sis_Usuarios] U
        INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
        WHERE U.[Borrado] = (0)
          AND (@Texto IS NULL OR U.[NombreUsuario] LIKE '%' + @Texto + '%'
               OR U.[CorreoElectronico] LIKE '%' + @Texto + '%')
        ORDER BY U.[NombreUsuario];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar usuarios; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioGet]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SELECT U.[CveUsuario], U.[NombreUsuario], U.[CorreoElectronico], U.[CveRol],
               R.[NombreRol], U.[Activo], U.[DebeCambiarContrasena], U.[CorreoVerificado]
        FROM [dbo].[Sis_Usuarios] U
        INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
        WHERE U.[CveUsuario] = @CveUsuario AND U.[Borrado] = (0);
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioSave]
    @CveUsuario int = NULL,
    @NombreUsuario varchar(100),
    @CorreoElectronico varchar(255),
    @CveRol int,
    @HashContrasena varchar(500) = NULL,
    @Activo bit,
    @DebeCambiarContrasena bit,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios] U
            INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] = 'SuperUsuario'
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'Sólo un SuperUsuario puede crear o modificar usuarios.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE [NombreUsuario] = @NombreUsuario AND [CveUsuario] <> ISNULL(@CveUsuario, (0)))
           OR EXISTS (SELECT 1 FROM [dbo].[Sis_Usuarios] WHERE LOWER([CorreoElectronico]) = LOWER(@CorreoElectronico) AND [CveUsuario] <> ISNULL(@CveUsuario, (0)))
        BEGIN
            SET @ErrorMostrar = 'El usuario o correo electrónico ya está registrado.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF @CveUsuario IS NULL OR @CveUsuario <= (0)
        BEGIN
            IF NULLIF(@HashContrasena, '') IS NULL
            BEGIN
                SET @ErrorMostrar = 'La contraseña es obligatoria para un usuario nuevo.';
                SET @ErrorTipo = 2;
                RETURN;
            END;

            INSERT INTO [dbo].[Sis_Usuarios]
                ([NombreUsuario], [HashContrasena], [CorreoElectronico], [CveRol], [Activo],
                 [DebeCambiarContrasena], [CorreoVerificado], [FechaVerificacionCorreo])
            VALUES
                (@NombreUsuario, @HashContrasena, @CorreoElectronico, @CveRol, @Activo,
                 @DebeCambiarContrasena, (1), SYSDATETIME());
            SET @CveUsuario = CONVERT(int, SCOPE_IDENTITY());
        END
        ELSE
        BEGIN
            UPDATE [dbo].[Sis_Usuarios]
            SET [NombreUsuario] = @NombreUsuario, [CorreoElectronico] = @CorreoElectronico,
                [CveRol] = @CveRol, [Activo] = @Activo,
                [DebeCambiarContrasena] = @DebeCambiarContrasena,
                [HashContrasena] = COALESCE(NULLIF(@HashContrasena, ''), [HashContrasena]),
                [FechaActualizacion] = GETDATE()
            WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);
        END;

        SELECT @CveUsuario AS [CveUsuario];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al guardar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioDelete]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        IF @CveUsuario = @CveUsuarioEjecuta
        BEGIN
            SET @ErrorMostrar = 'No puedes borrar tu propio usuario.';
            SET @ErrorTipo = 2;
            RETURN;
        END;
        UPDATE [dbo].[Sis_Usuarios]
        SET [Activo] = (0), [Borrado] = (1), [FechaBorrado] = SYSDATETIME(), [FechaActualizacion] = GETDATE()
        WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0)
          AND EXISTS
          (
              SELECT 1 FROM [dbo].[Sis_Usuarios] E
              INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = E.[CveRol]
              WHERE E.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] = 'SuperUsuario'
                AND E.[Activo] = (1) AND E.[Borrado] = (0)
          );
        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'No fue posible borrar el usuario o no tienes autorización.';
            SET @ErrorTipo = 1;
            RETURN;
        END;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al borrar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioClienteList]
    @CveUsuario int,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SELECT UC.[CveUsuarioCliente], UC.[CveUsuario], UC.[CveCliente], C.[Nombre], C.[RFC],
               UC.[Orden], UC.[EsPrincipal], UC.[Activo], C.[Estatus] AS [ClienteActivo]
        FROM [dbo].[Sis_UsuariosClientes] UC
        INNER JOIN [dbo].[Clientes] C ON C.[CveCliente] = UC.[CveCliente]
        WHERE UC.[CveUsuario] = @CveUsuario AND UC.[Borrado] = (0)
        ORDER BY UC.[Orden];
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar los clientes del usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioClienteSave]
    @CveUsuario int,
    @CveCliente int,
    @Orden int,
    @EsPrincipal bit,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        IF NOT EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_Usuarios] U INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
            WHERE U.[CveUsuario] = @CveUsuarioEjecuta AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
              AND U.[Activo] = (1) AND U.[Borrado] = (0)
        )
        BEGIN
            SET @ErrorMostrar = 'No tienes permiso para asignar clientes.';
            SET @ErrorTipo = 1;
            RETURN;
        END;

        IF @Orden <= (0) OR NOT EXISTS (SELECT 1 FROM [dbo].[Clientes] WHERE [CveCliente] = @CveCliente AND [Borrado] = (0))
        BEGIN
            SET @ErrorMostrar = 'El cliente o el orden indicado no es válido.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF EXISTS
        (
            SELECT 1 FROM [dbo].[Sis_UsuariosClientes]
            WHERE [CveUsuario] = @CveUsuario AND [Orden] = @Orden AND [Activo] = (1)
              AND [Borrado] = (0) AND [CveCliente] <> @CveCliente
        )
        BEGIN
            SET @ErrorMostrar = 'Ya existe otro cliente con el mismo orden para este usuario.';
            SET @ErrorTipo = 2;
            RETURN;
        END;

        IF @EsPrincipal = (1)
            UPDATE [dbo].[Sis_UsuariosClientes]
            SET [EsPrincipal] = (0), [FechaActualizacion] = SYSDATETIME()
            WHERE [CveUsuario] = @CveUsuario AND [Borrado] = (0);

        UPDATE [dbo].[Sis_UsuariosClientes]
        SET [Orden] = @Orden, [EsPrincipal] = @EsPrincipal, [Activo] = (1),
            [FechaActualizacion] = SYSDATETIME(), [Borrado] = (0), [FechaBorrado] = NULL
        WHERE [CveUsuario] = @CveUsuario AND [CveCliente] = @CveCliente;

        IF @@ROWCOUNT = (0)
            INSERT INTO [dbo].[Sis_UsuariosClientes]
                ([CveUsuario], [CveCliente], [Orden], [EsPrincipal], [Activo], [CveUsuarioRegistro])
            VALUES
                (@CveUsuario, @CveCliente, @Orden, @EsPrincipal, (1), @CveUsuarioEjecuta);

        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al asignar el cliente; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[PA_Clientes_EstatusUpdate]
    @CveCliente int,
    @Activo bit,
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        UPDATE C
        SET C.[Estatus] = @Activo, C.[CveUsuarioModifico] = @CveUsuarioEjecuta
        FROM [dbo].[Clientes] C
        WHERE C.[CveCliente] = @CveCliente AND C.[Borrado] = (0)
          AND EXISTS
          (
              SELECT 1 FROM [dbo].[Sis_Usuarios] U INNER JOIN [dbo].[Sis_Roles] R ON R.[CveRol] = U.[CveRol]
              WHERE U.[CveUsuario] = @CveUsuarioEjecuta
                AND R.[NombreRol] IN ('SuperUsuario', 'Administrador')
                AND U.[Activo] = (1) AND U.[Borrado] = (0)
          );
        IF @@ROWCOUNT = (0)
        BEGIN
            SET @ErrorMostrar = 'Sólo un Administrador o SuperUsuario puede cambiar el estado del cliente.';
            SET @ErrorTipo = 1;
            RETURN;
        END;
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al cambiar el estado del cliente; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO


/* ==================== AUTENTICACIÓN CON CORREO VERIFICADO ==================== */

CREATE OR ALTER PROCEDURE [dbo].[PA_Sis_UsuarioGetByName]
    @NombreUsuario varchar(100),
    @CveUsuarioEjecuta int,
    @ErrorMostrar varchar(max) OUTPUT,
    @ErrorTipo int OUTPUT,
    @Return bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @ErrorMostrar = NULL, @ErrorTipo = 0, @Return = 0;
    BEGIN TRY
        SELECT TOP (1)
            U.[CveUsuario], U.[NombreUsuario], U.[HashContrasena], U.[CorreoElectronico],
            U.[CveRol], R.[NombreRol], U.[Activo], U.[DebeCambiarContrasena],
            U.[IntentosFallidosInicioSesion], U.[BloqueadoHasta],
            U.[FechaUltimoInicioSesion], U.[FechaUltimaConexion], U.[CorreoVerificado]
        FROM [dbo].[Sis_Usuarios] AS U
        INNER JOIN [dbo].[Sis_Roles] AS R ON R.[CveRol] = U.[CveRol]
        WHERE U.[NombreUsuario] = @NombreUsuario
          AND U.[Borrado] = (0)
          AND R.[Borrado] = (0);
        SET @Return = 1;
    END TRY
    BEGIN CATCH
        SET @ErrorMostrar = 'Ocurrió un error al consultar el usuario; favor de contactar a Sistemas.';
        SET @ErrorTipo = 1;
        SET @Return = 0;
        DECLARE @E nvarchar(2048) = CONCAT('Número: ', ERROR_NUMBER(), '; Procedimiento: ', ERROR_PROCEDURE(), '; Línea: ', ERROR_LINE(), '; Detalle: ', ERROR_MESSAGE());
        RAISERROR(N'%s', 10, 1, @E);
    END CATCH;
END;
GO