USE BeezifiIOM;

ALTER TABLE integrations
    MODIFY COLUMN platform ENUM('shopify', 'woocommerce', 'amazon', 'shipstation') NOT NULL,
    MODIFY COLUMN status ENUM('active', 'inactive', 'error', 'paused', 'reauth_required') DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS api_base_url VARCHAR(255) NULL AFTER shop_domain,
    ADD COLUMN IF NOT EXISTS metadata JSON NULL AFTER webhook_secret,
    ADD COLUMN IF NOT EXISTS last_success_at TIMESTAMP NULL AFTER last_sync_at,
    ADD COLUMN IF NOT EXISTS last_failure_at TIMESTAMP NULL AFTER last_success_at,
    ADD COLUMN IF NOT EXISTS paused_until TIMESTAMP NULL AFTER last_failure_at,
    ADD COLUMN IF NOT EXISTS consecutive_failures INT NOT NULL DEFAULT 0 AFTER paused_until,
    ADD COLUMN IF NOT EXISTS failed_count INT NOT NULL DEFAULT 0 AFTER consecutive_failures,
    ADD COLUMN IF NOT EXISTS reauth_required_at TIMESTAMP NULL AFTER failed_count;

CREATE TABLE IF NOT EXISTS integration_sync_jobs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    integration_id  INT UNSIGNED NOT NULL,
    platform        VARCHAR(50) NOT NULL,
    job_type        VARCHAR(100) NOT NULL,
    dedupe_key      VARCHAR(255) NULL,
    trigger_source  VARCHAR(50) NOT NULL DEFAULT 'manual',
    status          ENUM('pending', 'running', 'success', 'error', 'dead_letter') DEFAULT 'pending',
    attempt_count   INT NOT NULL DEFAULT 0,
    max_attempts    INT NOT NULL DEFAULT 3,
    next_attempt_at TIMESTAMP NULL,
    started_at      TIMESTAMP NULL,
    finished_at     TIMESTAMP NULL,
    latency_ms      INT NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    last_error      TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dedupe_key (dedupe_key),
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_integration_job (integration_id, job_type),
    INDEX idx_next_attempt (next_attempt_at),
    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 reconciliation_snapshots (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    integration_id     INT UNSIGNED NULL,
    platform           VARCHAR(50) NULL,
    reconciliation_type ENUM('orders', 'inventory', 'accounting') NOT NULL,
    snapshot_date      DATE NOT NULL,
    external_count     DECIMAL(12,3) NOT NULL DEFAULT 0,
    internal_count     DECIMAL(12,3) NOT NULL DEFAULT 0,
    external_total     DECIMAL(15,2) NOT NULL DEFAULT 0,
    internal_total     DECIMAL(15,2) NOT NULL DEFAULT 0,
    variance_count     DECIMAL(12,3) NOT NULL DEFAULT 0,
    variance_amount    DECIMAL(15,2) NOT NULL DEFAULT 0,
    details            JSON NULL,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_type_date (tenant_id, reconciliation_type, snapshot_date),
    INDEX idx_integration_date (integration_id, snapshot_date),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB;
