Subversion Repositories SmartDukaan

-- ============================================================
-- Solr Update Failures Table
-- Purpose: Track failed Solr updates for visibility and retry
-- Created: 2026-01-14
-- ============================================================

-- Create table for tracking Solr update failures
CREATE TABLE IF NOT EXISTS solr_update_failures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    catalog_id INT NOT NULL COMMENT 'Catalog ID that failed to update in Solr',
    item_id INT COMMENT 'Item ID (optional, for reference)',
    change_type VARCHAR(50) COMMENT 'Type of change: PRICE_UPDATED, STATUS_CHANGED, CREATED, DELETED',
    error_message TEXT COMMENT 'Error message from failed Solr update',
    retry_count INT DEFAULT 0 COMMENT 'Number of retry attempts',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT 'PENDING, RETRYING, FAILED, RESOLVED',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'When the failure occurred',
    last_retry_at DATETIME COMMENT 'Last retry attempt timestamp',

    INDEX idx_status (status),
    INDEX idx_catalog_id (catalog_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks failed Solr update attempts for monitoring and retry';

-- ============================================================
-- Sample Queries for Dashboard
-- ============================================================

-- Get count of pending failures
-- SELECT COUNT(*) as pending_count FROM solr_update_failures WHERE status = 'PENDING';

-- Get all pending failures
-- SELECT * FROM solr_update_failures WHERE status = 'PENDING' ORDER BY created_at ASC;

-- Get failures that exceeded max retries (need manual intervention)
-- SELECT * FROM solr_update_failures WHERE status = 'FAILED' ORDER BY created_at DESC;

-- Clean up old resolved entries (optional maintenance)
-- DELETE FROM solr_update_failures WHERE status = 'RESOLVED' AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);