CREATE TABLE `lalouyxb_lalonche`.`sucursals` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` VARCHAR(500) NOT NULL,
  `phone_number` VARCHAR(255) NULL,
  `estado` INT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
);

CREATE TABLE `user_sucursals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_user` bigint unsigned NOT NULL,
  `id_sucursal` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `id_pk_sucursal` FOREIGN KEY (`id_sucursal`) REFERENCES `sucursal` (`id`),
  CONSTRAINT `id_pk_user` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`)
);

ALTER TABLE `lalouyxb_lalonche`.`orders`
ADD COLUMN `id_sucursal` BIGINT UNSIGNED NULL AFTER `client_id`;


/*INSERTA DATOS DE LA SUCURSAL 1*/
INSERT INTO `lalouyxb_lalonche`.`sucursal` (`name`, `address`, `phone_number`) VALUES ('SUCURSAL1', 'DIRECCION1', '50502020');

/*RELACIONAR LOS USUARIOS ACTUALES CON LA SUCURSAL CREADA 1*/
INSERT INTO user_sucursals (`id_user`, `id_sucursal`)
SELECT u.id, '1'
FROM users as u
WHERE u.status = 1;

/*RELACIONAR LAS ORDENES ACTUALES CON LA SUCURSAL CREADA 1*/
UPDATE orders SET `id_sucursal` = 1 WHERE id > 0;

/*CREAR LA LLAVE FORANEA EN ORDENES*/
ALTER TABLE `lalouyxb_lalonche`.`orders`
CHANGE COLUMN `id_sucursal` `id_sucursal` BIGINT UNSIGNED NOT NULL ;

ALTER TABLE `lalouyxb_lalonche`.`orders`
ADD INDEX `id_sursal_idx` (`id_sucursal` ASC) VISIBLE;

ALTER TABLE `lalouyxb_lalonche`.`orders`
ADD CONSTRAINT `id_sursal`
  FOREIGN KEY (`id_sucursal`)
  REFERENCES `lalouyxb_lalonche`.`sucursal` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

