-- Añadir campos que no están en Te-sis de clientes a CLIENTES de Asurnet
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_PersContactoNombre varchar(50) COLLATE Modern_Spanish_CI_AS NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_PersContactoApell1 varchar(50) COLLATE Modern_Spanish_CI_AS NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_PersContactoApell2 varchar(50) COLLATE Modern_Spanish_CI_AS NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_DiaPago smallint NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_DiaPago2 smallint NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_CodigoContable varchar(15) COLLATE Modern_Spanish_CI_AS NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_CadCarnet datetime NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_Estado smallint NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CobID char(5) COLLATE Modern_Spanish_CI_AI NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD EJE_IdTesis char(5) COLLATE Modern_Spanish_CI_AI NULL;
ALTER TABLE asurnet.dbo.CLIENTES ADD CLI_IdTesis int NULL;

-- Crear tabla VALORES en Asurnet
CREATE TABLE asurnet.dbo.VALORES (
	VAL_Id int IDENTITY(1,1) NOT NULL,
	VAL_Descripcion varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CPT_Id int NULL,
	VAL_Orden int NULL,
	CONSTRAINT aaaaaVALORES_PK PRIMARY KEY (VAL_Id)
);

-- Crear índice en la tabla VALORES en Asurnet
 CREATE NONCLUSTERED INDEX IND_VAL_CONCEPTOS ON asurnet.dbo.VALORES (  CPT_Id ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;

-- Añadir clave foránea a la tabla VALORES con la tabla CONCEPTOS
ALTER TABLE asurnet.dbo.VALORES ADD CONSTRAINT FK_VAL_CONCEPTOS FOREIGN KEY (CPT_Id) REFERENCES asurnet.dbo.CONCEPTOS(CPT_Id);

-- Crear tabla clicrit en Asurnet
CREATE TABLE asurnet.dbo.clicrit (
	CLI_Id int NOT NULL,
	CPT_Id int NOT NULL,
	VAL_Id int NOT NULL,
	CONSTRAINT PK_clicrit PRIMARY KEY (CLI_Id,CPT_Id,VAL_Id)
);

-- Crear índices en la tabla clicrit en Asurnet
CREATE NONCLUSTERED INDEX IND_clicrit_CONCEPTOS ON asurnet.dbo.clicrit (  CPT_Id ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ];
CREATE NONCLUSTERED INDEX IND_clicrit_VALORES ON asurnet.dbo.clicrit (  VAL_Id ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ];
CREATE NONCLUSTERED INDEX ind_clicrit_clientes ON asurnet.dbo.clicrit (  CLI_Id ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ];

-- Crear claves foráneas en la tabla clicrit en Asurnet
ALTER TABLE asurnet.dbo.clicrit ADD CONSTRAINT FK_CLICRIT_CONCEPTOS FOREIGN KEY (CPT_Id) REFERENCES asurnet.dbo.CONCEPTOS(CPT_Id);
ALTER TABLE asurnet.dbo.clicrit ADD CONSTRAINT FK_CLICRIT_VALORES FOREIGN KEY (VAL_Id) REFERENCES asurnet.dbo.VALORES(VAL_Id);