CREATE TABLE [Solicitudes] ([SolicitudNro] decimal( 10) NOT NULL , [ServicioCodigo] smallint NOT NULL , [SolicitudFecha] datetime NOT NULL , [SolicitudHora] datetime NOT NULL , [ClienteNit] decimal( 15) NOT NULL , [ServicioCantidad] smallint NOT NULL , [SolicitudFechaInicio] datetime NOT NULL , [SolicitudFechaFin] datetime NOT NULL , PRIMARY KEY([SolicitudNro], [ServicioCodigo]))
CREATE NONCLUSTERED INDEX [ISOLICITUDES1] ON [Solicitudes] ([ClienteNit] )
CREATE NONCLUSTERED INDEX [ISOLICITUDES2] ON [Solicitudes] ([ServicioCodigo] )

CREATE TABLE [Servicios] ([ServicioCodigo] smallint NOT NULL , [ServicioNombre] nchar(50) NOT NULL , PRIMARY KEY([ServicioCodigo]))

CREATE TABLE [PQRS] ([PqrsFecha] datetime NOT NULL , [ClienteNit] decimal( 15) NOT NULL , [PqrsTipo] nchar(20) NOT NULL , [PqrsQuien] nchar(20) NOT NULL , [PqrsDescripcion] nvarchar(500) NOT NULL , PRIMARY KEY([PqrsFecha], [ClienteNit]))
CREATE NONCLUSTERED INDEX [IPQRS1] ON [PQRS] ([ClienteNit] )

CREATE TABLE [ItemDotacion] ([ItemDotacionNro] smallint NOT NULL , [ItemDotacionNombre] nchar(50) NOT NULL , PRIMARY KEY([ItemDotacionNro]))

CREATE TABLE [Empresa] ([EmpresaNit] decimal( 15) NOT NULL , [EmpresaRazonSocial] nchar(50) NOT NULL , [EmpresaDireccion] nvarchar(1024) NOT NULL , [EmpresaTelefono] nchar(20) NOT NULL , [EmpresaTerminosCondiciones] NVARCHAR(MAX) NOT NULL , [EmpresaLogo] VARBINARY(MAX) NOT NULL , [EmpresaLogo_GXI] varchar(2048) NULL , PRIMARY KEY([EmpresaNit]))

CREATE TABLE [Empleados] ([EmpleadoId] decimal( 15) NOT NULL , [EmpleadoNombre] nchar(50) NOT NULL , [EmpleadoDireccion] nvarchar(1024) NOT NULL , [EmpleadoTelefono] nchar(20) NOT NULL , [EmpleadoEmail] nvarchar(100) NOT NULL , [EmpleadoPerfil] nchar(15) NOT NULL , [EmpleadoUsuario] nchar(20) NOT NULL , [EmpleadoClave] nchar(20) NOT NULL , [EmpleadoImage] VARBINARY(MAX) NOT NULL , [EmpleadoImage_GXI] varchar(2048) NULL , PRIMARY KEY([EmpleadoId]))
CREATE NONCLUSTERED INDEX [UEMPLEADOS] ON [Empleados] ([EmpleadoUsuario] ,[EmpleadoClave] )

CREATE TABLE [DotacionLevel1] ([DotacionFecha] datetime NOT NULL , [EmpleadoId] decimal( 15) NOT NULL , [ItemDotacionNro] smallint NOT NULL , [ItemDotacionCantidad] smallint NOT NULL , PRIMARY KEY([DotacionFecha], [EmpleadoId], [ItemDotacionNro]))
CREATE NONCLUSTERED INDEX [IDOTACIONLEVEL2] ON [DotacionLevel1] ([ItemDotacionNro] )

CREATE TABLE [Dotacion] ([DotacionFecha] datetime NOT NULL , [EmpleadoId] decimal( 15) NOT NULL , PRIMARY KEY([DotacionFecha], [EmpleadoId]))
CREATE NONCLUSTERED INDEX [IDOTACION1] ON [Dotacion] ([EmpleadoId] )

CREATE TABLE [AsignacionServiciosLevel1] ([SolicitudNro] decimal( 10) NOT NULL , [ServicioCodigo] smallint NOT NULL , [EmpleadoId] decimal( 15) NOT NULL , [AsignaFechaIni] datetime NOT NULL , [AsignaFechaFin] datetime NOT NULL , [AsignaComentario] nvarchar(500) NOT NULL , [AsignaComentarioEmpleada] nvarchar(500) NOT NULL , PRIMARY KEY([SolicitudNro], [ServicioCodigo], [EmpleadoId]))
CREATE NONCLUSTERED INDEX [IASIGNACIONSERVICIOSLEVEL2] ON [AsignacionServiciosLevel1] ([EmpleadoId] )

CREATE TABLE [Clientes] ([ClienteNit] decimal( 15) NOT NULL , [ClienteNombre] nchar(50) NOT NULL , [ClienteFoto] VARBINARY(MAX) NOT NULL , [ClienteFoto_GXI] varchar(2048) NULL , [ClienteEmail] nvarchar(100) NOT NULL , [ClienteTelefono] nchar(20) NOT NULL , [ClienteWeb] nvarchar(1000) NOT NULL , [ClienteCelular] nchar(20) NOT NULL , [ClienteUbicacion] nchar(50) NOT NULL , [ClienteDispositivo] nchar(128) NOT NULL , PRIMARY KEY([ClienteNit]))
CREATE UNIQUE NONCLUSTERED INDEX [UCLIENTES] ON [Clientes] ([ClienteEmail] )

ALTER TABLE [AsignacionServiciosLevel1] ADD CONSTRAINT [IASIGNACIONSERVICIOSLEVEL3] FOREIGN KEY ([SolicitudNro], [ServicioCodigo]) REFERENCES [Solicitudes] ([SolicitudNro], [ServicioCodigo])
ALTER TABLE [AsignacionServiciosLevel1] ADD CONSTRAINT [IASIGNACIONSERVICIOSLEVEL2] FOREIGN KEY ([EmpleadoId]) REFERENCES [Empleados] ([EmpleadoId])

ALTER TABLE [Dotacion] ADD CONSTRAINT [IDOTACION1] FOREIGN KEY ([EmpleadoId]) REFERENCES [Empleados] ([EmpleadoId])

ALTER TABLE [DotacionLevel1] ADD CONSTRAINT [IDOTACIONLEVEL3] FOREIGN KEY ([DotacionFecha], [EmpleadoId]) REFERENCES [Dotacion] ([DotacionFecha], [EmpleadoId])
ALTER TABLE [DotacionLevel1] ADD CONSTRAINT [IDOTACIONLEVEL2] FOREIGN KEY ([ItemDotacionNro]) REFERENCES [ItemDotacion] ([ItemDotacionNro])

ALTER TABLE [PQRS] ADD CONSTRAINT [IPQRS1] FOREIGN KEY ([ClienteNit]) REFERENCES [Clientes] ([ClienteNit])

ALTER TABLE [Solicitudes] ADD CONSTRAINT [ISOLICITUDES2] FOREIGN KEY ([ServicioCodigo]) REFERENCES [Servicios] ([ServicioCodigo])
ALTER TABLE [Solicitudes] ADD CONSTRAINT [ISOLICITUDES1] FOREIGN KEY ([ClienteNit]) REFERENCES [Clientes] ([ClienteNit])