ALTER TABLE `sic_usuarios` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
 ADD COLUMN `clienteSecret` VARCHAR(120) AFTER `Password`,
 ADD COLUMN `clientId` VARCHAR(120) AFTER `clienteSecret`,
 ADD PRIMARY KEY (`id`);
ALTER TABLE `sic_usuarios` ADD COLUMN `token` VARCHAR(120) AFTER `clientId`,
 ADD COLUMN `vigenciaToken` DATETIME AFTER `token`;

CREATE TABLE codeaccess (
                                   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                                   `createdAt` DATETIME NOT NULL,
                                   `createdBy` VARCHAR(45) NOT NULL,
                                   `estatus` CHAR(1) NOT NULL,
                                   `code` CHAR(8) NOT NULL,
                                   `tipo` CHAR(1) NOT NULL COMMENT 'M=MOVILE RUTA, S=SISTEMA BACKOFFICE',
                                   `validatedAt` DATETIME,
                                   `updatedAt` DATETIME,
                                   `updatedBy` VARCHAR(45),
                                   `latitud` VARCHAR(45),
                                   `longitud` VARCHAR(45),
                                   `marca` VARCHAR(45),
                                   `modelo` VARCHAR(45),
                                   `versionSo` VARCHAR(45),
                                   `serie` VARCHAR(80),
                                   PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;

CREATE VIEW `ViewRutaTrabajo` AS
SELECT x.NumCliente,c.CveCliente,numInt, c.Nombre,c.Apellidos,c.Calle,c.Numero, c.Colonia, c.CP, c.Coordenadas,longitud,Status,dia,Ruta,Orden,
       (select count(*) from Sic_GuardaParcial where CveCliente=x.NumCliente AND date(fechaAlta)=current_date()) as 'venta'
        FROM Sic_ClienteReparto x,Sic_Cliente c where c.NumCliente =x.NumCliente and status=1
        ORDER BY Orden ASC;



CREATE TABLE  catperfil (
                                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                   `createdAt` datetime NOT NULL,
                                   `createdBy` varchar(45) NOT NULL,
                                   `descripcion` varchar(100) NOT NULL,
                                   `estatus` char(1) NOT NULL,
                                   `updatedAt` datetime DEFAULT NULL,
                                   `updatedBy` varchar(45) DEFAULT NULL,
                                   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE  catmenu(
                                 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                 `descripcion` varchar(60) NOT NULL,
                                 `seccion` char(1) NOT NULL,
                                 `abreviatura` varchar(45) NOT NULL,
                                 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  catmodulo (
                                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                   `descripcion` varchar(45) NOT NULL,
                                   `idCatMenu` int(10) unsigned NOT NULL,
                                   `idPadre` int(10) unsigned DEFAULT NULL,
                                   `link` varchar(360) NOT NULL,
                                   `color` varchar(45) NOT NULL,
                                   `icon` varchar(45) NOT NULL,
                                   PRIMARY KEY (`id`) USING BTREE,
                                   KEY `FK_catmodulo_1` (`idCatMenu`) USING BTREE,
                                   CONSTRAINT `FK_catmodulo_1` FOREIGN KEY (`idCatMenu`) REFERENCES `catmenu` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `catperfilmodulo` (
                                         `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                         `idCatPerfil` int(10) unsigned NOT NULL,
                                         `idCatModulo` int(10) unsigned NOT NULL,
                                         PRIMARY KEY (`id`),
                                         KEY `FK_catperfinmodulo_1` (`idCatPerfil`),
                                         KEY `FK_catperfinmodulo_2` (`idCatModulo`),
                                         CONSTRAINT `FK_catperfinmodulo_1` FOREIGN KEY (`idCatPerfil`) REFERENCES `catperfil` (`id`),
                                         CONSTRAINT `FK_catperfinmodulo_2` FOREIGN KEY (`idCatModulo`) REFERENCES `catmodulo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO catmenu (`descripcion`, `seccion`, `abreviatura`) VALUES ('CONFIGURACIÓN', '6', 'Cong');
INSERT INTO Catmodulo (`descripcion`, `idCatMenu`, `link`, `color`, `icon`) VALUES ('Codigos de Activación', '1', 'config/codeactivate', '-', '-');
INSERT INTO catperfil (`createdAt`, `createdBy`, `descripcion`, `estatus`) VALUES ('2025-10-06', 'drodriguez', 'USERMASTER', 'A');
INSERT INTO `Catperfilmodulo` (`idCatPerfil`, `idCatModulo`) VALUES ('1', '1');
ALTER TABLE `catmenu` ADD COLUMN `icon` VARCHAR(45) NOT NULL AFTER `abreviatura`;
UPDATE `catmenu` SET `icon` = 'settings' WHERE (`id` = '1');


ALTER TABLE `sic_ruta` DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`CveRuta`);

delete  FROM sic_Ruta where cveruta=0;

ALTER TABLE `sic_ruta` MODIFY COLUMN `CveRuta` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `codeaccess` ADD COLUMN `idRuta` INTEGER UNSIGNED AFTER `serie`,
 ADD CONSTRAINT `FK_codeaccess_1` FOREIGN KEY `FK_codeaccess_1` (`idRuta`)
    REFERENCES `sic_ruta` (`CveRuta`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;


CREATE TABLE empresa (
                                `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                                `createdAt` DATETIME NOT NULL,
                                `createdBy` VARCHAR(45) NOT NULL,
                                `razonSocial` VARCHAR(360) NOT NULL,
                                `nombreComercial` VARCHAR(360) NOT NULL,
                                `rfc` VARCHAR(13) NOT NULL,
                                `latitud` VARCHAR(45) NOT NULL,
                                `longitud` VARCHAR(45) NOT NULL,
                                PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;

INSERT INTO `empresa` (`createdAt`, `createdBy`, `razonSocial`, `nombreComercial`, `rfc`, `latitud`, `longitud`) VALUES ('2025-10-01', 'abravo', 'EMBOTELLADORA ALCE', 'VIEBER', 'XAXX010101000', '19.375408842471508', '-99.13945367458379');

ALTER TABLE `codeaccess` ADD COLUMN `latitudValida` VARCHAR(45) AFTER `idRuta`,
 ADD COLUMN `longitudValida` VARCHAR(45) AFTER `latitudValida`;

-- Crea/actualiza la vista con los mismos joins y columnas
CREATE OR REPLACE VIEW `viewClienteDetalle` AS
SELECT
    c.`NumCliente`,
    c.`CveCliente`,
    c.`Nombre`,
    c.`Apellidos`,
    c.`Calle`,
    c.`Numero`,
    col.`DescColinia`        AS `DescColinia`,
    d.`DescDelegacion`       AS `DescDelegacion`,
    c.`CP`,
    c.`Observaciones`,
    c.`Telefono1`,
    c.`Telefono2`,
    c.`Email`,
    -- Concatenación estilo MySQL (antes: NombrePila + ' ' + ApPaterno + ' ' + ApMaterno)
    CONCAT_WS(' ', e.`NombrePila`, e.`ApPaterno`, e.`ApMaterno`) AS `VENDEDOR`,
    c.`Contacto1`,
    c.`Contacto2`,
    c.`Credito`,
    c.`Saldo`,
    c.`Descuento`,
    c.`Aniversario`,
    tc.`DescTipoCliente`,
    sc.`DescStatusCliente`,
    mc.`DescMotivosContratacion`,
    mcan.`DescMotivosCancelacion`,
    r.`DescRuta`,
    c.`Lunes`,
    c.`Martes`,
    c.`Miercoles`,
    c.`Jueves`,
    c.`Viernes`,
    c.`Sabado`,
    c.`Domingo`,
    c.`Hora`,
    c.`PuntajeActual`,
    c.`FechaCancelacion`,
    c.`FechaAlta`,
    c.`Quincena1`,
    c.`Quincena2`,
    df.`RazonSocial`,
    df.`DireccionFiscal`,
    df.`RFC`,
    df.`Telefono`            AS `TelefonoDF`,
    df.`Colonia`             AS `ColoniaDF`,
    c.`Factura`,
    df.`CP`                  AS `Expr1`,
    df.`CURP`,
    c.`Semanal`,
    c.`Festejado`,
    p.`DescPlanta`,
    z.`DescZonas`,
    c.`VtaPer`,
    c.`Visitas`,
    e.`NombrePila`,
    e.`ApPaterno`,
    e.`ApMaterno`,
    c.`ClienteProspecto`,
    c.`Prefactura`,
    c.`HoraIni`,
    c.`HoraFin`,
    c.`NumClienteSami`,
    c.`Coordenadas`,
    c.`longitud`,
    c.`numInt`,
    c.`detalleColonia`,
    c.`Prepago`
FROM `Sic_Cliente`        AS c
         INNER JOIN `Sic_Colonias` AS col  ON c.`Colonia`        = col.`CveColonia`
         INNER JOIN `Sic_Delegacion` AS d  ON col.`NumDelegacion`= d.`CveDelegacion`
         INNER JOIN `Sic_TipoClientes` AS tc ON c.`Tipo`         = tc.`CveTipoCliente`
         INNER JOIN `Sic_StatusCliente` AS sc ON c.`Status`      = sc.`CveStatusCliente`
         INNER JOIN `Sic_MotivosContratacion` AS mc ON c.`MotivoAlta`       = mc.`CveMotivosContratacion`
         INNER JOIN `Sic_MotivosCancelacion`  AS mcan ON c.`MotivoCancelacion` = mcan.`CveMotivosCancelacion`
         INNER JOIN `Sic_Ruta`       AS r   ON c.`RutaActual`    = r.`CveRuta`
         INNER JOIN `Sic_Planta`     AS p   ON c.`Empresa`       = p.`CvePlanta`
         INNER JOIN `Sic_Zonas`      AS z   ON c.`Zona`          = z.`CveZonas`
         INNER JOIN `Sic_DatosFactura` AS df ON c.`NumCliente`   = df.`NumCliente`
         INNER JOIN `Sic_Empleado`   AS e   ON c.`Vendedor`      = e.`idEmpleado`;


DROP VIEW IF EXISTS disoft.Sic_Liquidacion;
DROP VIEW  IF EXISTS disoft.Sic_LIQUIDACION;

ALTER TABLE disoft.sic_liquidaciondistribuidores DROP FOREIGN KEY FK_Sic_LiquidacionDistribuidores_Sic_Liquidacion;
ALTER TABLE disoft.sic_liquidacionproductoservicio DROP FOREIGN KEY FK_LiquidacionProductoServicio_Sic_Liquidacion;
ALTER TABLE disoft.sic_nota DROP FOREIGN KEY FK_Sic_Nota_Sic_Liquidacion;

CREATE TABLE  `Sic_Liquidacion` (
                                             `idLiquidacion` INT NOT NULL,
                                             `idRuta` INT NULL,
                                             `fechaAlta` DATETIME(6) NULL,
                                             `usuario` VARCHAR(80) NULL,
                                             `total` DECIMAL(18,2) NULL,
                                             `titular` INT NULL,
                                             `auxiliar` INT NULL,
                                             `efectivo` DECIMAL(18,2) NULL,
                                             `notas` DECIMAL(18,2) NULL,
                                             `cupon` DECIMAL(18,2) NULL,
                                             `cheque` DECIMAL(18,2) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*ES NECSARIO MIGRAR LOS DATOS DE ESTA TABLA YA QUE NO SE MIGRARON EN EL PROCESO INICIAL*/

-- Si ves una VIEW:
DROP VIEW IF EXISTS disoft.Sic_AutorizacionCredito;
DROP VIEW  IF EXISTS disoft.Sic_AutorizacionCredito;




CREATE TABLE Sic_AutorizacionCredito (
                                         `idAutorizacion` BIGINT NOT NULL,
                                         `fechaAlta` DATETIME(6) NULL,
                                         `idRuta` BIGINT NULL,
                                         `tipo` CHAR(1) NULL,
                                         `idCliente` BIGINT NULL,
                                         `cantidad` INT NULL,
                                         `idProductoServicio` BIGINT NULL,
                                         `usuario` VARCHAR(50) NULL,
                                         `observacion` VARCHAR(250) NULL,
                                         `precioEspecial` DECIMAL(18,2) NULL,
                                         PRIMARY KEY (`idAutorizacion`))
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*ES NECSARIO MIGRAR LOS DATOS DE ESTA TABLA YA QUE NO SE MIGRARON EN EL PROCESO INICIAL Sic_AutorizacionCredito*/

ALTER TABLE `sic_recordatorios` MODIFY COLUMN `NumRecordatorio` BIGINT(20) NOT NULL AUTO_INCREMENT,
    MODIFY COLUMN `FechaAlta` DATETIME NOT NULL,
    MODIFY COLUMN `FechaRecordar` DATETIME NOT NULL,
    MODIFY COLUMN `FechaInicio` DATE DEFAULT NULL,
    MODIFY COLUMN `FechaFin` DATE DEFAULT NULL,
    ADD PRIMARY KEY (`NumRecordatorio`);


ALTER TABLE `sic_claveradio` MODIFY COLUMN `FechaAlta` DATE NOT NULL;
ALTER TABLE `sic_claveradio` ADD COLUMN `ShowMovil` CHAR(1) NOT NULL AFTER `FechaAlta`;
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '1');
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '2');
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '3');
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '4');
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '6');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '5');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '7');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '8');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '9');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '10');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '11');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '12');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '13');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '14');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '16');
UPDATE `sic_claveradio` SET `ShowMovil` = 'S' WHERE (`NumRadio` = '17');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '18');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '20');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '21');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '22');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '23');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '24');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '25');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '26');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '29');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '31');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '32');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '33');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '35');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '37');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '41');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '45');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '52');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '55');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '73');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '75');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '77');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '78');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '79');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '80');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '81');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '82');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '83');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '84');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '85');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '87');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '95');
UPDATE `sic_claveradio` SET `ShowMovil` = 'N' WHERE (`NumRadio` = '98');


ALTER TABLE sic_guardaparcial MODIFY COLUMN `NumCliente` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `NumArticulo` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `NumCantidad` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `NumMensaje` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `FechaAlta` DATETIME DEFAULT NULL,
    MODIFY COLUMN `CveCliente` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `idAutorizacion` INTEGER UNSIGNED DEFAULT NULL;

ALTER TABLE sic_guardaparcial MODIFY COLUMN `NumRuta` INTEGER UNSIGNED DEFAULT NULL,
    ADD COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (`id`);
ALTER TABLE `sic_guardaparcial` MODIFY COLUMN `Precio` DECIMAL(10,2) DEFAULT NULL;

CREATE VIEW `viewrutasegundasvueltas` AS
select NumCliente as 'cveCliente',NumRuta,date(fechaAlta) as 'fechaAlta',
        (select COUNT(*) FROM sic_guardaparcial WHERE NumCliente=g.NumCliente AND NumArticulo>0 and NumCantidad>0 and date(fechaAlta)=date(g.fechaAlta) ) as 'venta',
        (select NumMensaje FROM sic_guardaparcial WHERE NumCliente=g.NumCliente AND NumMensaje<>0 and date(fechaAlta)=date(g.fechaAlta) order by FechaAlta DESC limit 1) as 'clave'
        from sic_guardaparcial g
        group by NumCliente,NumRuta,date(fechaAlta)
        having venta<=0;


ALTER TABLE `sic_guardaparcial` MODIFY COLUMN `NumRuta` INTEGER UNSIGNED NOT NULL,
    MODIFY COLUMN `NumCliente` INTEGER UNSIGNED NOT NULL,
    MODIFY COLUMN `FechaAlta` DATETIME NOT NULL,
    MODIFY COLUMN `CveCliente` INTEGER UNSIGNED NOT NULL,
    MODIFY COLUMN `Hora` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    MODIFY COLUMN `GPS` VARCHAR(1200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
update  Sic_RegSerEspeciales set FechaAlta=STR_TO_DATE(FechaAlta, '%d/%m/%Y %H:%i:%s');
update Sic_RegSerEspeciales set FechaSer=STR_TO_DATE(FechaSer, '%d/%m/%Y');
delete from Sic_RegSerEspeciales where numCliente>=2842422;

ALTER TABLE `sic_regserespeciales` MODIFY COLUMN `NumRegSerEspecial` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `NumCliente` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `FechaSer` DATE DEFAULT NULL,
    MODIFY COLUMN `HorarioSer` TIME DEFAULT NULL,
    MODIFY COLUMN `NumRuta` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `FechaAlta` DATETIME DEFAULT NULL,
    MODIFY COLUMN `NumClienteProspecto` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `Cantidad` INTEGER UNSIGNED DEFAULT NULL,
    MODIFY COLUMN `NumRutaAlterna` INTEGER UNSIGNED DEFAULT NULL;

ALTER TABLE `sic_regserespeciales` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
 ADD PRIMARY KEY (`id`);

ALTER TABLE `sic_regserespeciales` DROP COLUMN `NumRegSerEspecial`;


ALTER TABLE `nm`.`sic_regserespeciales` ADD CONSTRAINT `FK_sic_regserespeciales_1` FOREIGN KEY `FK_sic_regserespeciales_1` (`NumCliente`)
    REFERENCES `sic_cliente` (`CveCliente`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;







