-- ==========================================================
-- Source Database Schemas (Server A - cPanel 1 Multi-Database)
-- ==========================================================

-- Primary Management Database
CREATE DATABASE IF NOT EXISTS `source_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `source_db`;

-- Webhook Outbox Queue Table (Multi-Destination & Multi-Database Support)
DROP TABLE IF EXISTS `webhook_outbox`;
CREATE TABLE `webhook_outbox` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_id` VARCHAR(36) NOT NULL,
  `destination_id` VARCHAR(64) NOT NULL DEFAULT 'server_b',
  `database_name` VARCHAR(64) NOT NULL DEFAULT 'source_db',
  `table_name` VARCHAR(64) NOT NULL,
  `action` ENUM('INSERT', 'UPDATE', 'DELETE', 'UPSERT') NOT NULL,
  `primary_key_name` VARCHAR(64) DEFAULT 'id',
  `payload` LONGTEXT NOT NULL,
  `status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
  `retry_count` INT UNSIGNED DEFAULT 0,
  `response_code` INT UNSIGNED NULL,
  `last_error` TEXT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `processed_at` DATETIME NULL,
  UNIQUE KEY `idx_event_destination` (`event_id`, `destination_id`),
  INDEX `idx_db_status` (`database_name`, `status`),
  INDEX `idx_status_retries` (`status`, `retry_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample Table in `source_db`
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `stock_quantity` INT NOT NULL DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` (`id`, `sku`, `name`, `price`, `stock_quantity`, `status`) VALUES
(1, 'PROD-1001', 'Wireless Gaming Mouse', 49.99, 120, 'active'),
(2, 'PROD-1002', 'Mechanical RGB Keyboard', 89.50, 45, 'active')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Additional Database 2: `store_usa`
CREATE DATABASE IF NOT EXISTS `store_usa` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `store_usa`;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `stock_quantity` INT NOT NULL DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` (`id`, `sku`, `name`, `price`, `stock_quantity`, `status`) VALUES
(10, 'USA-9001', 'US Store Laptop Charger', 29.99, 100, 'active')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Additional Database 3: `store_eu`
CREATE DATABASE IF NOT EXISTS `store_eu` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `store_eu`;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `stock_quantity` INT NOT NULL DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` (`id`, `sku`, `name`, `price`, `stock_quantity`, `status`) VALUES
(20, 'EU-8001', 'EU USB-C Power Adapter', 35.00, 80, 'active')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Switch back to main database
USE `source_db`;