CREATE TABLE IF NOT EXISTS `{{prefix}}inhouse_service_staff` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inhouse_service_staff` (`branch_id`,`service_id`,`user_id`),
  KEY `idx_inhouse_staff_lookup` (`branch_id`,`service_id`,`is_active`),
  KEY `idx_inhouse_staff_user` (`user_id`,`is_active`),
  CONSTRAINT `{{prefix}}fk_inhouse_staff_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
