-- Normalize local branch numbers per school.
-- users.branch_id remains the global branches.id foreign key.

SET @old_fk=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;

DROP PROCEDURE IF EXISTS normalize_local_branch_ids;
DELIMITER $$

CREATE PROCEDURE normalize_local_branch_ids()
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE v_school BIGINT;
 DECLARE v_branch BIGINT;
 DECLARE v_local INT;
 DECLARE current_school BIGINT DEFAULT -1;

 DECLARE cur CURSOR FOR
  SELECT id,school_id
  FROM branches
  WHERE school_id<>0
  ORDER BY school_id,is_head_office DESC,id;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

 OPEN cur;
 read_loop: LOOP
  FETCH cur INTO v_branch,v_school;
  IF done=1 THEN LEAVE read_loop; END IF;

  IF current_school<>v_school THEN
   SET current_school=v_school;
   SET v_local=0;
  END IF;

  UPDATE branches
  SET branch_id=v_local,
      is_head_office=IF(v_local=0,1,0)
  WHERE id=v_branch;

  SET v_local=v_local+1;
 END LOOP;
 CLOSE cur;
END$$

DELIMITER ;

CALL normalize_local_branch_ids();
DROP PROCEDURE normalize_local_branch_ids;

SET @has_index=(
 SELECT COUNT(*) FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA=DATABASE()
 AND TABLE_NAME='branches'
 AND INDEX_NAME='uk_school_local_branch'
);

SET @sql=IF(@has_index=0,
 'ALTER TABLE branches ADD UNIQUE KEY uk_school_local_branch (school_id,branch_id)',
 'SELECT 1');
PREPARE s FROM @sql;EXECUTE s;DEALLOCATE PREPARE s;

SET FOREIGN_KEY_CHECKS=@old_fk;

SELECT id,school_id,branch_id,branch_name,is_head_office
FROM branches
ORDER BY school_id,branch_id;