-- ==========================================================
-- Destination Database Schemas (Server B - cPanel 2 Multi-Database)
-- ==========================================================

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

-- Webhook Inbox Audit Log Table (Multi-Database Support)
DROP TABLE IF EXISTS `webhook_inbox`;
CREATE TABLE `webhook_inbox` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_id` VARCHAR(36) NOT NULL UNIQUE,
  `database_name` VARCHAR(64) NOT NULL DEFAULT 'destination_db',
  `table_name` VARCHAR(64) NOT NULL,
  `action` ENUM('INSERT', 'UPDATE', 'DELETE', 'UPSERT') NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `status` ENUM('success', 'failed', 'skipped') NOT NULL,
  `message` TEXT NULL,
  `received_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_db_event` (`database_name`, `event_id`),
  INDEX `idx_event_status` (`event_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Target Table in `destination_db`
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED 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;

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

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED 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;

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

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED 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;

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