CREATE TABLE TESISSEG.dbo.GRUPOS_CORREDURIAS (
	Id int IDENTITY(0,1) NOT NULL,
	Nombre varchar(100) NOT NULL,
	CONSTRAINT GRUPOS_CORREDURIAS_PK PRIMARY KEY (Id)
);

CREATE TABLE TESISSEG.dbo.GRUPOS_CORREDURIAS_CORREDURIAS (
	GC_Id int NOT NULL,
	EmpID smallint NOT NULL,
	CONSTRAINT PK_GRUPOS_CORREDURIAS_CORREDURIAS PRIMARY KEY (GC_Id,EmpID),
	CONSTRAINT FK_GRUPOS_CORREDURIAS_GRUPOS_CORREDURIAS_CORREDURIAS FOREIGN KEY (GC_Id) REFERENCES TESISSEG.dbo.GRUPOS_CORREDURIAS(Id),
    CONSTRAINT FK_CORREDURIAS_GRUPOS_CORREDURIAS_CORREDURIAS FOREIGN KEY (EmpID) REFERENCES TESISSEG.dbo.Corredurias(EmpID)
);

CREATE TABLE CONS_COMPANIAS (
    CIA_Id int IDENTITY(0,1) NOT NULL,
    CIA_Codigo varchar(50) NOT NULL,
    CIA_Nombre varchar(255) NOT NULL,
    CIA_Cif varchar(20) NOT NULL,
    CIA_Tel varchar(20) NOT NULL,
    CIA_Logo varchar(255) NOT NULL,
    GC_Id int NOT NULL,
    CONSTRAINT PK_CONS_COMPANIAS PRIMARY KEY (CIA_Id),
    CONSTRAINT FK_GRUPOS_CORREDURIAS_CONS_COMPANIAS FOREIGN KEY (GC_Id) REFERENCES TESISSEG.dbo.GRUPOS_CORREDURIAS(Id),
);
CREATE  UNIQUE NONCLUSTERED INDEX COMPANIAS_CIA_Codigo_uindex ON TESISSEG.dbo.CONS_COMPANIAS (  CIA_Codigo ASC, GC_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 TABLE CONS_COMPANIAS_PREFERENTES (
    CIAP_Id int IDENTITY(0,1) NOT NULL,
    CIA_Id int NOT NULL,
    ciap_aNIO int NOT NULL,
    GC_Id int NOT NULL,
    CONSTRAINT PK_CONS_COMPANIAS_PREFERENTES PRIMARY KEY (CIAP_Id),
    CONSTRAINT FK_CONS_COMPANIAS_CONS_COMPANIAS_PREFERENTES FOREIGN KEY (CIA_Id) REFERENCES TESISSEG.dbo.CONS_COMPANIAS(CIA_Id),
    CONSTRAINT FK_GRUPOS_CORREDURIAS_CONS_COMPANIAS_PREFERENTES FOREIGN KEY (GC_Id) REFERENCES TESISSEG.dbo.GRUPOS_CORREDURIAS(Id)
);


CREATE TABLE CONS_GRUPOS_RAMOS (
    GRP_Id int IDENTITY(0,1) NOT NULL,
    GRP_Nombre varchar(255) NOT NULL,
    CONSTRAINT PK_CONS_GRUPOS_RAMOS PRIMARY KEY (GRP_Id)
);

CREATE TABLE CONS_RAMOS (
    RAM_Id int IDENTITY(0,1) NOT NULL,
    RAM_Eiac varchar(100) NOT NULL,
    RAM_Nombre varchar(255) NOT NULL,
    RAM_Vida tinyint NOT NULL DEFAULT '0',
    GRP_Id int NOT NULL,
    GC_Id int NOT NULL,
    CONSTRAINT PK_CONS_RAMOS PRIMARY KEY (RAM_Id),
    CONSTRAINT FK_CONS_GRUPOS_RAMOS_CONS_RAMOS FOREIGN KEY (GRP_Id) REFERENCES TESISSEG.dbo.CONS_GRUPOS_RAMOS(GRP_Id),
    CONSTRAINT FK_GRUPOS_CORREDURIAS_CONS_RAMOS FOREIGN KEY (GC_Id) REFERENCES TESISSEG.dbo.GRUPOS_CORREDURIAS(Id)
);
CREATE  UNIQUE NONCLUSTERED INDEX CONS_RAMOS_CIA_RAM_Id_RAM_Vida_uindex ON TESISSEG.dbo.CONS_RAMOS (  RAM_Id ASC, RAM_Vida 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 TABLE CONS_CM_OBJETIVOS (
    ID int IDENTITY(0,1) NOT NULL,
    CMO_Nombre varchar(255) NOT NULL,
    CIA_Id int,
    RAM_Id int,
    CMO_TipoPoliza int NOT NULL,
    CMO_TipoMedicion int NOT NULL,
    CMO_Objetivo int NOT NULL,
    CMO_Year int NOT NULL,
    EmpID smallint,
    CMO_VisibleHome int NOT NULL,
    CMO_ComputaVida tinyint NOT NULL DEFAULT 0,
    CMO_Compromiso tinyint NOT NULL DEFAULT 0,
    GC_Id int NOT NULL,
    CONSTRAINT CONS_CM_OBJETIVOS_PK PRIMARY KEY (ID),
    CONSTRAINT FK_GRUPOS_CORREDURIAS_CONS_CM_OBJETIVOS FOREIGN KEY (GC_Id) REFERENCES TESISSEG.dbo.GRUPOS_CORREDURIAS(Id),
    CONSTRAINT FK_CONS_RAMOS_CONS_CM_OBJETIVOS FOREIGN KEY (RAM_Id) REFERENCES TESISSEG.dbo.CONS_RAMOS(RAM_Id),
    CONSTRAINT FK_CONS_COMPANIAS_CONS_CM_OBJETIVOS FOREIGN KEY (CIA_Id) REFERENCES TESISSEG.dbo.CONS_COMPANIAS(CIA_Id),
    CONSTRAINT FK_CORREDURIAS_CONS_CM_OBJETIVOS FOREIGN KEY (EmpID) REFERENCES TESISSEG.dbo.Corredurias(EmpID)
);

CREATE TABLE CONS_RAMOS_CORREDURIAS (
    RAMCORR_Id int IDENTITY(0,1) NOT NULL,
    RAM_Id int NOT NULL,
    EmpId smallint NOT NULL,
    CORR_RamId char(5) NOT NULL,
    CONSTRAINT CONS_RAMOS_CORREDURIAS_PK PRIMARY KEY (RAMCORR_Id),
    CONSTRAINT FK_CONS_RAMOS_CONS_RAMOS_CORREDURIAS FOREIGN KEY (RAM_Id) REFERENCES TESISSEG.dbo.CONS_RAMOS(RAM_Id),
    CONSTRAINT FK_CORREDURIAS_CONS_RAMOS_CORREDURIAS FOREIGN KEY (EmpID) REFERENCES TESISSEG.dbo.Corredurias(EmpID),
    CONSTRAINT FK_RAMOS_CONS_RAMOS_CORREDURIAS FOREIGN KEY (EmpId, CORR_RamId) REFERENCES TESISSEG.dbo.Ramos(EmpId, RamID)
);
CREATE  UNIQUE NONCLUSTERED INDEX CONS_RAMOS_CORREDURIAS_CORR_RamId_EmpId_uindex ON TESISSEG.dbo.CONS_RAMOS_CORREDURIAS (  CORR_RamId ASC, EmpId 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 VIEW dbo.CONS_POLIZAS AS
SELECT A.EmpID AS POL_Correduria, G.ComClaUDG AS POL_CiaDGS, A.PolNumID AS POL_Id, LTRIM(RTRIM(A.PolNum)) AS POL_NumPoliza,
CASE WHEN A.PolSAge = 'VIGOR' THEN 2 WHEN A.PolSAge = 'BAJA' THEN 3 ELSE 1 END AS POL_Estado,
A.PolFecIni AS POL_PrimerEfecto, A.PolFecUltEfe AS POL_Efecto, A.PolFPrxRen AS POL_FechaVencimiento, A.PolPriNet AS POL_PNActual,
CASE WHEN B.RecSer IS NULL THEN 0 ELSE B.RecComBru END AS POL_URComCorreduria, ISNULL(E.RAM_Id,999999) AS RAM_Id,
CASE WHEN A.PolFecBaja = '1753-01-01 00:00:00' THEN NULL ELSE A.PolFecBaja END AS POL_FechaAnulacion, A.PolTipPro AS POL_Tipo,
stuff(LTRIM(RTRIM(A.MotAnuID)), 1, patindex('%[^' + 'P' + ']%', LTRIM(RTRIM(A.MotAnuID))) - 1, null) AS POL_MotivoAnulacion,
F.GC_Id
FROM TESISSEG.dbo.Polizas A
LEFT JOIN TESISSEG.dbo.Recibos B ON A.PolNumID = B.PolNumID AND A.EmpID = B.EmpID
LEFT JOIN TESISSEG.dbo.TipSeguros C ON A.TipSegID = C.TipSegID AND C.EmpID = A.EmpID
LEFT JOIN TESISSEG.dbo.Ramos D ON C.RamId = D.RamId AND C.EmpID = D.EmpID
LEFT JOIN TESISSEG.dbo.CONS_RAMOS_CORREDURIAS E ON LTRIM(RTRIM(D.RamID)) = E.CORR_RamId AND E.EmpId = D.EmpID
LEFT JOIN TESISSEG.dbo.GRUPOS_CORREDURIAS_CORREDURIAS F ON F.EmpID = A.EmpID
LEFT JOIN TESISSEG.dbo.Companias G ON G.ComID = A.ComID AND G.EmpID = A.EmpID
LEFT JOIN (
	SELECT EmpID, PolNumID, MAX(RecFecEfe) AS MaxRecFecEfe, MAX(RecPriTot) AS MaxRecPriTot
	FROM TESISSEG.dbo.Recibos
	WHERE RecTip IN ('C', 'P')
	GROUP BY EmpID, PolNumID
) H ON A.PolNumID = H.PolNumID AND A.EmpID = H.EmpID
WHERE ((B.RecFecEfe = H.MaxRecFecEfe) OR B.RecSer IS NULL)
AND A.PolPro NOT IN ('R','S')
AND ((B.RecPriTot = H.MaxRecPriTot) OR B.RecSer IS NULL)
;

CREATE VIEW dbo.CONS_RECIBOS AS
SELECT A.EmpID AS REC_Correduria, H.ComClaUDG AS REC_CiaDGS, CONCAT(A.RecSer, '/', A.RecNumID) AS REC_Id, B.PolNumID AS POL_Id,
CONCAT(A.RecSer, '-', A.RecNumID) AS REC_NumRecibo, A.RecPriNet AS REC_PrimaNeta, A.RecComNet AS REC_ComPrimaNeta,
A.RecComBru AS REC_ComCorreduria, CASE WHEN A.RecFecBaja = '1753-01-01 00:00:00' THEN 1 ELSE 2 END AS REC_Estado,
A.RecSAge AS REC_Situacion, A.RecFecEfe AS REC_Efecto, MONTH(A.RecFecEfe) AS REC_EfectoMes, YEAR(A.RecFecEfe) AS REC_EfectoAnio,
ISNULL(F.RAM_Id,999999) AS RAM_Id, G.GC_Id
FROM TESISSEG.dbo.Recibos A
LEFT JOIN TESISSEG.dbo.Polizas B ON A.PolNumID = B.PolNumID AND A.EmpID = B.EmpID
LEFT JOIN TESISSEG.dbo.TipSeguros D ON B.TipSegID = D.TipSegID AND B.EmpID = D.EmpID
LEFT JOIN TESISSEG.dbo.Ramos E ON D.RamId = E.RamId AND D.EmpID = E.EmpID
LEFT JOIN TESISSEG.dbo.CONS_RAMOS_CORREDURIAS F ON LTRIM(RTRIM(E.RamID)) = F.CORR_RamId AND E.EmpId = F.EmpID
LEFT JOIN TESISSEG.dbo.GRUPOS_CORREDURIAS_CORREDURIAS G ON G.EmpID = A.EmpID
LEFT JOIN TESISSEG.dbo.Companias H ON H.ComID = B.ComID AND H.EmpID = B.EmpID
;



