USE BeezifiIOM;

CREATE TABLE IF NOT EXISTS integrations (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    platform       ENUM('shopify', 'woocommerce') NOT NULL,
    shop_domain    VARCHAR(255) NOT NULL,
    access_token   TEXT NOT NULL,
    api_key        VARCHAR(255) NULL,
    api_secret     TEXT NULL,
    webhook_secret VARCHAR(255) NULL,
    status         ENUM('active', 'inactive', 'error') DEFAULT 'active',
    last_sync_at   TIMESTAMP NULL,
    sync_orders    TINYINT(1) DEFAULT 1,
    sync_inventory TINYINT(1) DEFAULT 1,
    sync_products  TINYINT(1) DEFAULT 0,
    error_message  TEXT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_tenant_platform_domain (tenant_id, platform, shop_domain),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS integration_order_map (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    integration_id    INT UNSIGNED NOT NULL,
    external_order_id VARCHAR(100) NOT NULL,
    iom_order_id      INT UNSIGNED NOT NULL,
    synced_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_external (integration_id, external_order_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE,
    FOREIGN KEY (iom_order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS integration_product_map (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    integration_id      INT UNSIGNED NOT NULL,
    external_product_id VARCHAR(100) NOT NULL,
    external_variant_id VARCHAR(100) NULL,
    iom_product_id      INT UNSIGNED NOT NULL,
    iom_variant_id      INT UNSIGNED NULL,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_external_variant (integration_id, external_variant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS integration_sync_log (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    integration_id INT UNSIGNED NOT NULL,
    event          VARCHAR(100) NOT NULL,
    status         ENUM('success','error','skipped') DEFAULT 'success',
    message        TEXT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    INDEX idx_integration (integration_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;
