ALTER TABLE `client` 
DROP FOREIGN KEY `fk_client_user1`;
ALTER TABLE `client` 
DROP COLUMN `user_iduser`,
DROP INDEX `fk_client_user1_idx` ;



ALTER TABLE `user` 
ADD COLUMN `client_id` INT NULL AFTER `token`,
ADD INDEX `FK_client_user_idx` (`client_id` ASC) VISIBLE;
;
ALTER TABLE `user` 
ADD CONSTRAINT `FK_client_user`
  FOREIGN KEY (`client_id`)
  REFERENCES `client` (`idclient`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


CREATE TABLE `typemoney` (
  `idtypeMoney` INT NOT NULL AUTO_INCREMENT,
  `typeMoneyDesc` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idtypeMoney`));
  
  
INSERT INTO `typemoney` (`typeMoneyDesc`) VALUES ('DOLAR');
INSERT INTO `typemoney` (`typeMoneyDesc`) VALUES ('QUETZALES');

ALTER TABLE `policy` 
ADD COLUMN `typeMoney` INT NOT NULL DEFAULT 1 AFTER `policyDisablepolicy`,
ADD INDEX `FK_typeMoney_idx` (`typeMoney` ASC) VISIBLE;
;
ALTER TABLE `policy` 
ADD CONSTRAINT `FK_typeMoney`
  FOREIGN KEY (`typeMoney`)
  REFERENCES `typemoney` (`idtypeMoney`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;



ALTER TABLE `transportcertificate` 
DROP COLUMN `amountPosessionTransport`;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `idtransportCertificate` `idtransportCertificate` INT NOT NULL ,
CHANGE COLUMN `noRef` `noRef` INT NOT NULL AUTO_INCREMENT ,
ADD UNIQUE INDEX `noRef_UNIQUE` (`noRef` ASC) VISIBLE;
;


INSERT INTO `statuscertificate` (`statusCertificateDesc`) VALUES ('EN REVISIÓN');
INSERT INTO `statuscertificate` (`statusCertificateDesc`) VALUES ('ACEPTADO');
INSERT INTO `statuscertificate` (`statusCertificateDesc`) VALUES ('RECHAZADO');


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `cover` `cover` VARCHAR(1000) NULL DEFAULT NULL ;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `idtransportCertificate` `idtransportCertificate` INT NOT NULL AUTO_INCREMENT ,
CHANGE COLUMN `noRef` `noRef` INT NOT NULL ;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `idtransportCertificate` `idtransportCertificate` INT NOT NULL AUTO_INCREMENT ,
CHANGE COLUMN `noRef` `noRef` INT NULL ,
DROP INDEX `noRef_UNIQUE` ;
;


ALTER TABLE `transportcertificate` 
ADD COLUMN `notes` TEXT(5000) NOT NULL AFTER `certificateDisable`;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `dateRequest` `dateRequest` DATE NOT NULL ;



ALTER TABLE `policy` 
CHANGE COLUMN `dateCreate` `dateCreate` DATE NOT NULL ,
CHANGE COLUMN `dateCurrency` `dateCurrency` DATE NOT NULL ;


ALTER TABLE `policy` 
ADD COLUMN `dateCurrencyEnd` DATE NOT NULL AFTER `dateCurrencyStart`,
CHANGE COLUMN `internalTransportation` `internalTransportation` VARCHAR(50) NULL DEFAULT NULL AFTER `authWays`,
CHANGE COLUMN `dateCurrency` `dateCurrencyStart` DATE NOT NULL ;
ALTER TABLE `policy` 
ADD COLUMN `possesionExcluded` VARCHAR(2500) NULL AFTER `posessionInsured`;


ALTER TABLE `policy` 
ADD COLUMN `deductibleDamageMinimum` DECIMAL(10,2) NOT NULL AFTER `deductibleTheft`,
ADD COLUMN `deductibleTheftMinimum` DECIMAL(10,2) NOT NULL AFTER `deductibleDamageMinimum`,
CHANGE COLUMN `deductibleDamage` `deductibleDamage` DECIMAL(10,2) NOT NULL ,
CHANGE COLUMN `deductibleTheft` `deductibleTheft` DECIMAL(10,2) NOT NULL ;

ALTER TABLE `policy` 
DROP COLUMN `propertyType`;


ALTER TABLE `policy` 
DROP COLUMN `movementType`;



ALTER TABLE `policy` 
ADD COLUMN `importExportRate` DECIMAL(10,2) NOT NULL AFTER `typeMoney`,
ADD COLUMN `foodFrozenRate` DECIMAL(10,2) NOT NULL AFTER `importExportRate`,
ADD COLUMN `internalTransport` DECIMAL(10,2) NOT NULL AFTER `foodFrozenRate`,
ADD COLUMN `deductibleFranquise` DECIMAL(10,2) NOT NULL AFTER `internalTransport`;


ALTER TABLE `policy` 
DROP COLUMN `rates`;


ALTER TABLE `policy` 
ADD COLUMN `observations` VARCHAR(2500) NOT NULL AFTER `deductibleFranquise`;

ALTER TABLE `transportcertificate` 
ADD COLUMN `typeMovement` VARCHAR(500) NULL AFTER `notes`;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `notes` `notes` TEXT NULL ;

ALTER TABLE `policy` 
DROP COLUMN `applicableSecurityMeasures`;

ALTER TABLE `transportcertificate` 
ADD COLUMN `applicableSecurityMeasures` TEXT NULL AFTER `typeMovement`;

#CAMBIOS HOY
UPDATE `statuscertificate` SET `statusCertificateDesc` = 'ACTIVO' WHERE (`idstatusCertificate` = '2');
UPDATE `statuscertificate` SET `statusCertificateDesc` = 'ANULADO' WHERE (`idstatusCertificate` = '3');
INSERT INTO `statuscertificate` (`idstatusCertificate`, `statusCertificateDesc`) VALUES ('4', 'CERRADO');



ALTER TABLE `transportcertificate` 
ADD COLUMN `dateVigencyStart` DATE NULL AFTER `applicableSecurityMeasures`,
ADD COLUMN `dateVigencyEnd` DATE NULL AFTER `dateVigencyStart`;


ALTER TABLE `policy` 
CHANGE COLUMN `deductibleDamage` `deductibleDamage` INT NOT NULL ,
CHANGE COLUMN `deductibleTheft` `deductibleTheft` INT NOT NULL ,
CHANGE COLUMN `deductibleDamageMinimum` `deductibleDamageMinimum` INT NOT NULL ,
CHANGE COLUMN `deductibleTheftMinimum` `deductibleTheftMinimum` INT NOT NULL ;


ALTER TABLE `transportcertificate` 
CHANGE COLUMN `normalDeductible` `normalDeductible` DECIMAL(10,0) NULL ,
CHANGE COLUMN `franchiseDeductible` `franchiseDeductible` DECIMAL(10,0) NULL ,
CHANGE COLUMN `internalNormalDeductible` `internalNormalDeductible` DECIMAL(10,0) NULL ;


ALTER TABLE `transportcertificate` 
ADD COLUMN `noReference` VARCHAR(100) NULL AFTER `dateVigencyEnd`;



ALTER TABLE `policy` 
CHANGE COLUMN `deductibleDamageMinimum` `deductibleDamageMinimum` DECIMAL(10,2) NOT NULL ,
CHANGE COLUMN `deductibleTheftMinimum` `deductibleTheftMinimum` DECIMAL(10,2) NOT NULL ;


#cambios 1 de noviembre
ALTER TABLE `aseguradoradb`.`transportcertificate` 
ADD COLUMN `destinationObservation10` TEXT NULL AFTER `noReference`;

#cambios 11 de noviembre
ALTER TABLE `aseguradoradb`.`policy` 
ADD COLUMN `internalNormalDeductible` DECIMAL(10,2) NULL AFTER `observations`;

# cambios 24 de noviembre
ALTER TABLE `policy` 
ADD COLUMN `deductibleTheftNoMeasures` INT NULL AFTER `internalNormalDeductible`,
ADD COLUMN `deductibleTheftNoMeasuresMinimum` DECIMAL(10,2) NULL AFTER `deductibleTheftNoMeasures`;


# cambios 28 de diciembre
ALTER TABLE `client` 
ADD COLUMN `clientLogo` VARCHAR(100) NULL AFTER `clientDisable`;
ALTER TABLE `policy` 
ADD COLUMN `internalNormalDeductibleMinimum` DECIMAL(10,2) NULL AFTER `deductibleTheftNoMeasuresMinimum`;


ALTER TABLE `policy` 
ADD COLUMN `deductibleDamageTransport` INT NULL AFTER `internalNormalDeductibleMinimum`,
ADD COLUMN `deductibleDamageTransportMinimum` DECIMAL(10,2) NULL AFTER `deductibleDamageTransport`,
ADD COLUMN `deductibleTheftTransport` INT NULL AFTER `deductibleDamageTransportMinimum`,
ADD COLUMN `deductibleTheftTransportMinimum` VARCHAR(45) NULL AFTER `deductibleTheftTransport`;

ALTER TABLE  `policy` 
ADD COLUMN `deductibleTheftTransportNoMeasure` INT NULL AFTER `deductibleTheftTransportMinimum`,
ADD COLUMN `deductibleTheftTransportNoMeasureMinimum` DECIMAL(10,2) NULL AFTER `deductibleTheftTransportNoMeasure`,
CHANGE COLUMN `deductibleTheftTransportMinimum` `deductibleTheftTransportMinimum` DECIMAL(10,2) NULL DEFAULT NULL ;


#nuevos cambios
ALTER TABLE `policy` 
ADD COLUMN `deductibleDamageFranquise` INT NULL AFTER `deductibleTheftTransportNoMeasureMinimum`,
ADD COLUMN `deductibleDamageFranquiseMinimum` DECIMAL(10,2) NULL AFTER `deductibleDamageFranquise`;


INSERT INTO `typeactivities` (`typeActivitiesDesc`) VALUES ('CERTÍFICADO');
INSERT INTO `typeactivities` (`typeActivitiesDesc`) VALUES ('POLIZAS');
INSERT INTO `typeactivities` (`typeActivitiesDesc`) VALUES ('USUARIOS');
INSERT INTO `typeactivities` (`typeActivitiesDesc`) VALUES ('CLIENTES');
INSERT INTO `typeactivities` (`typeActivitiesDesc`) VALUES ('ASEGURADORA');


#29/11/2022

ALTER TABLE `policy` 
CHANGE COLUMN `valuationBase` `valuationBase` INT NULL DEFAULT NULL ;


#cambios 2
ALTER TABLE `policy` 
CHANGE COLUMN `observations` `observations` VARCHAR(2500) NULL ;



#cambios 1/12/2022
ALTER TABLE `managerassignment` 
ADD COLUMN `managerassignmentId` INT NOT NULL AUTO_INCREMENT AFTER `client_idclient`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`managerassignmentId`);
;

ALTER TABLE `policy` 
CHANGE COLUMN `deductibleDamage` `deductibleDamage` DECIMAL(10,2) NOT NULL ,
CHANGE COLUMN `deductibleTheft` `deductibleTheft` DECIMAL(10,2) NOT NULL ,
CHANGE COLUMN `deductibleTheftNoMeasures` `deductibleTheftNoMeasures` DECIMAL(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `deductibleDamageTransport` `deductibleDamageTransport` DECIMAL(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `deductibleTheftTransport` `deductibleTheftTransport` DECIMAL(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `deductibleTheftTransportNoMeasure` `deductibleTheftTransportNoMeasure` DECIMAL(10,2) NULL DEFAULT NULL ,
CHANGE COLUMN `deductibleDamageFranquise` `deductibleDamageFranquise` DECIMAL(10,2) NULL DEFAULT NULL ;


ALTER TABLE `policy` 
ADD COLUMN `internalTransportRateMinor` DECIMAL(10,2) NULL AFTER `deductibleDamageFranquiseMinimum`;

#cambios 12/6
ALTER TABLE `transportcertificate` 
CHANGE COLUMN `observationsCertificate` `observationsCertificate` TEXT NULL DEFAULT NULL ,
CHANGE COLUMN `merchandiseDescription` `merchandiseDescription` TEXT NULL DEFAULT NULL ;

ALTER TABLE `policy` 
ADD COLUMN `statusPolicy` VARCHAR(45) NULL AFTER `internalTransportRateMinor`;

ALTER TABLE `policy` 
CHANGE COLUMN `statusPolicy` `statusPolicy` VARCHAR(45) NULL DEFAULT 'ACTIVO' ;


#cambios 12/13
ALTER TABLE `policy` 
CHANGE COLUMN `deductibleDamage` `deductibleDamage` INT NOT NULL ,
CHANGE COLUMN `deductibleTheft` `deductibleTheft` INT NOT NULL ;

#cambios 14/12
ALTER TABLE `policy` 
CHANGE COLUMN `valuationBase` `valuationBasePolicy` INT NULL DEFAULT NULL ;


#CAMBIOS
ALTER TABLE `transportcertificate` 
ADD COLUMN `iduser` INT NULL AFTER `destinationObservation`,
ADD INDEX `FK_TRANSPORTUSERID_idx` (`iduser` ASC) VISIBLE;
;
ALTER TABLE `transportcertificate` 
ADD CONSTRAINT `FK_TRANSPORTUSERID`
  FOREIGN KEY (`iduser`)
  REFERENCES `user` (`iduser`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  
  
#cambios 21
ALTER TABLE `managerassignment` 
ADD COLUMN `updated_at` TIMESTAMP NULL AFTER `managerassignmentId`,
ADD COLUMN `created_at` TIMESTAMP NULL AFTER `updated_at`;

#cambios 29

ALTER TABLE `transportcertificate` 
ADD COLUMN `correlativeNumber` INT NULL AFTER `iduser`;


#cambios 26 de enero
ALTER TABLE `policy` 
ADD COLUMN `transportInternalmayor` DECIMAL(10,2) NULL AFTER `statusPolicy`,
ADD COLUMN `transportInternalMinor` DECIMAL(10,2) NULL AFTER `transportInternalmayor`;


#CAMBIOS 27
ALTER TABLE `policy` 
ADD COLUMN `depositPreimium` DECIMAL(10,2) NULL AFTER `transportInternalMinor`;


ALTER TABLE `transportcertificate` 
ADD COLUMN `depositPremiumBefore` DECIMAL(10,2) NULL AFTER `correlativeNumber`,
ADD COLUMN `depositPreimiumAfter` DECIMAL(10,2) NULL AFTER `depositPremiumBefore`;


ALTER TABLE `aseguradoradb`.`policy` 
CHANGE COLUMN `deductibleFranquise` `deductibleFranquise` DECIMAL(10,2) NOT NULL ;

# cotizaciones

CREATE TABLE `quotecertificate` (
  `idtransportCertificate` int NOT NULL AUTO_INCREMENT,
  `dateRequest` date NOT NULL,
  `merchandiseType` varchar(500) NOT NULL,
  `noRef` int DEFAULT NULL,
  `invoiceNo` varchar(100) DEFAULT NULL,
  `blHwbCp` varchar(500) NOT NULL,
  `via` varchar(150) NOT NULL,
  `shipping` decimal(10,0) NOT NULL,
  `verifiableExpenses` decimal(10,0) NOT NULL,
  `total` decimal(10,0) NOT NULL,
  `normalDeductible` decimal(10,0) DEFAULT NULL,
  `franchiseDeductible` decimal(10,0) DEFAULT NULL,
  `internalNormalDeductible` decimal(10,0) DEFAULT NULL,
  `internalFranchiseDeductible` decimal(10,0) DEFAULT NULL,
  `cover` varchar(1000) DEFAULT NULL,
  `deductibleSelected` varchar(5000) DEFAULT NULL,
  `observationsCertificate` text,
  `policy_idpolicy` int NOT NULL,
  `client_idclient` int NOT NULL,
  `statusCertificate_idstatusCertificate` int NOT NULL,
  `originCountry` varchar(100) DEFAULT NULL,
  `originAddress` varchar(500) DEFAULT NULL,
  `destinationCountry` varchar(100) DEFAULT NULL,
  `destinationAddress` varchar(500) DEFAULT NULL,
  `beneficiaryName` varchar(100) DEFAULT NULL,
  `shippingNumber` varchar(50) DEFAULT NULL,
  `merchandiseDescription` text,
  `insuredOrigin` varchar(10) DEFAULT NULL,
  `propertyUnderrLiability` varchar(10) DEFAULT NULL,
  `valuationBase` decimal(10,2) DEFAULT NULL,
  `invoiceValue` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `certificateDisable` tinyint DEFAULT '1',
  `notes` text,
  `typeMovement` varchar(500) DEFAULT NULL,
  `applicableSecurityMeasures` text,
  `dateVigencyStart` date DEFAULT NULL,
  `dateVigencyEnd` date DEFAULT NULL,
  `noReference` varchar(100) DEFAULT NULL,
  `destinationObservation` text,
  `iduser` int DEFAULT NULL,
  `correlativeNumber` int DEFAULT NULL,
  `bamCost` decimal(10,2) default null,
  `clientCost` decimal(10,2) default null,
  PRIMARY KEY (`idtransportCertificate`),
  KEY `fk_transportCertificate_policy1_idx` (`policy_idpolicy`),
  KEY `fk_transportCertificate_client1_idx` (`client_idclient`),
  KEY `fk_transportCertificate_statusCertificate1_idx` (`statusCertificate_idstatusCertificate`),
  KEY `FK_TRANSPORTUSERID_idx` (`iduser`),
  CONSTRAINT `fk_transportCertificate_client2` FOREIGN KEY (`client_idclient`) REFERENCES `client` (`idclient`),
  CONSTRAINT `fk_transportCertificate_policy2` FOREIGN KEY (`policy_idpolicy`) REFERENCES `policy` (`idpolicy`),
  CONSTRAINT `fk_transportCertificate_statusCertificate2` FOREIGN KEY (`statusCertificate_idstatusCertificate`) REFERENCES `statuscertificate` (`idstatusCertificate`),
  CONSTRAINT `FK_TRANSPORTUSERID1` FOREIGN KEY (`iduser`) REFERENCES `user` (`iduser`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



#cambios 20/02/2023
ALTER TABLE `quotecertificate` 
ADD COLUMN `safetyMeasure` VARCHAR(900) NULL AFTER `clientCost`;


#cambios 16/03

ALTER TABLE `quotecertificate` 
ADD COLUMN `idCertificate` VARCHAR(900) NULL AFTER `safetyMeasure`;

#cambios 15/01/2024
ALTER TABLE `aseguradoradb`.`transportcertificate` 
ADD COLUMN `frozenFood` VARCHAR(45) NULL AFTER `depositPreimiumAfter`;


ALTER TABLE `aseguradoradb`.`quotecertificate` 
ADD COLUMN `frozenFood` VARCHAR(45) NULL AFTER `idCertificate`;