-- Create users_accessible_branches many-to-many table
USE acbptxvs_branch_system;

CREATE TABLE IF NOT EXISTS `users_accessible_branches` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customuser_id` char(32) NOT NULL,
  `branch_id` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_accessible_branches_customuser_id_branch_id` (`customuser_id`, `branch_id`),
  KEY `users_accessible_branches_customuser_id` (`customuser_id`),
  KEY `users_accessible_branches_branch_id` (`branch_id`),
  CONSTRAINT `users_accessible_branches_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`),
  CONSTRAINT `users_accessible_branches_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SELECT 'users_accessible_branches table created!' as status;