CREATE TABLE IF NOT EXISTS integration_connection_secret_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    connection_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    secret_enc TEXT NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_integration_secret_version (tenant_id, connection_id, version_number),
    INDEX idx_integration_secret_versions_lookup (tenant_id, connection_id, created_at),
    CONSTRAINT fk_integration_secret_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_secret_versions_connection FOREIGN KEY (connection_id) REFERENCES integration_connections(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_secret_versions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_connection_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    connection_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(60) NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    before_json JSON NULL,
    after_json JSON NULL,
    metadata_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_integration_audit_lookup (tenant_id, connection_id, created_at),
    CONSTRAINT fk_integration_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_audit_connection FOREIGN KEY (connection_id) REFERENCES integration_connections(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_audit_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
