CREATE TABLE IF NOT EXISTS `supplier_external_locations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `integration_id` bigint(20) UNSIGNED NOT NULL,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `external_location_id` varchar(255) NOT NULL,
  `external_name` varchar(255) DEFAULT NULL,
  `last_synced_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_external_location_integration_external` (`integration_id`,`external_location_id`),
  UNIQUE KEY `uq_external_location_integration_location` (`integration_id`,`location_id`),
  KEY `idx_external_location_supplier` (`supplier_id`),
  KEY `idx_external_location_location` (`location_id`),
  CONSTRAINT `fk_external_location_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_external_location_integration`
    FOREIGN KEY (`integration_id`) REFERENCES `supplier_integrations` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_external_location_location`
    FOREIGN KEY (`location_id`) REFERENCES `supplier_locations` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `supplier_api_credential_scopes`
    (`credential_id`, `scope`, `created_at`)
SELECT
    `id`,
    'locations:write',
    NOW()
FROM `supplier_api_credentials`
WHERE `status` = 'active';