CREATE TABLE TESISSEG.dbo.GruposRamosAgora (
	ID INT IDENTITY(1,1) NOT NULL,
	Nombre VARCHAR(50) COLLATE Modern_Spanish_CI_AS NULL,
	CONSTRAINT PK_GruposRamosAgora PRIMARY KEY (ID)
);

INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('AUTOS');
INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('DIVERSOS');
INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('FINANCIERO');
INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('PERSONALES');
INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('TEMPORALES');
INSERT INTO TESISSEG.dbo.GruposRamosAgora (Nombre) VALUES ('VIDA');

ALTER TABLE TESISSEG.dbo.RamosAGORA ADD GRA_Id INT NULL;

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'AUTOS')
WHERE RamAgoID IN (
    'Car', 'CorporateFleets', 'Truck', 'TruckFleets', 'AgriculturalMachinery',
    'Motorbike', 'MotorbikeFleets', 'Vehicle', 'Vehicle Fleets', 'ProfesionalCar'
);

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'DIVERSOS')
WHERE RamAgoID IN (
    'RoadsideAssistance', 'ReplacementVehicle', 'VehicleLegalProtection', 'Excess', 'Hull', 'Boats', 'Transport', 'OtherTransport',
    'FiresAndNaturalEvents', 'Cyber', 'Glasses', 'PropertyRobbery', 'OtherPropertyDamage', 'OtherTechnical',
    'ElectronicEquipment', 'CivilLiabilityNuclearRisks', 'DAndO', 'HuntingAndFishingLiability', 'OtherLiability',
    'CivilLiability', 'ProfessionalCivilLiability', 'RCSecondLayer', 'Credit', 'Guarantee', 'Contingency', 'RentalProtection',
    'Losses', 'LegalDefence', 'FamilyLegalDefence', 'Travel', 'Home', 'Community', 'TradeAndOffice', 'SME', 'Industries',
    'OtherMultiRisk', 'Pets'
);

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'FINANCIERO')
WHERE RamAgoID IN (
	'Financial', 'LifeSaving', 'LifePension', 'PensionPlan', 'Annuity', 'LiveSavingCollective'
);

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'PERSONALES')
WHERE RamAgoID IN (
	'AccidentCollective', 'Accident', 'AccidentLiability', 'AccidentAgreement', 'OldAgeAndDisability', 'IllnessCollective',
	'Illness', 'Dependence', 'Health', 'HealthCollective', 'Attendance', 'Burial'
);

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'TEMPORALES')
WHERE RamAgoID IN (
	'VehicleInsuranceForDays', 'Agricultural', 'TenYearDamage', 'MaterialDamege', 'ConstructionAndAssembly'
);

UPDATE TESISSEG.dbo.RamosAGORA
SET GRA_Id = (SELECT TOP(1) ID FROM TESISSEG.dbo.GruposRamosAgora gra WHERE gra.Nombre = 'VIDA')
WHERE RamAgoID IN (
	'Life', 'LiveCollective'
);
