florinel-chis · August 8, 2025 17:09
diff --git a/sku-update-generator.html b/sku-update-generator.html
 <!DOCTYPE html>
 <html lang="en">
 <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Magento 2 SKU Update SQL Generator</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 20px;
        }
        
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: rgba(255, 255, 255, 0.95);
            border-radius: 20px;
            padding: 30px;
            box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);
        }
        
        h1 {
            color: #333;
            margin-bottom: 10px;
            font-size: 2.5em;
            background: linear-gradient(135deg, #667eea, #764ba2);
            -webkit-background-clip: text;
            -webkit-text-fill-color: transparent;
            background-clip: text;
        }
        
        .subtitle {
            color: #666;
            margin-bottom: 30px;
            font-size: 1.1em;
        }
        
        .input-section {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
        }
        
        .file-input-wrapper {
            position: relative;
            display: inline-block;
            cursor: pointer;
            width: 100%;
        }
        
        .file-input-wrapper input[type=file] {
            position: absolute;
            left: -9999px;
        }
        
        .file-input-label {
            display: block;
            padding: 15px;
            background: linear-gradient(135deg, #667eea, #764ba2);
            color: white;
            border-radius: 10px;
            text-align: center;
            font-weight: 600;
            transition: transform 0.2s, box-shadow 0.2s;
            cursor: pointer;
        }
        
        .file-input-label:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(102, 126, 234, 0.4);
        }
        
        .file-info {
            margin-top: 15px;
            padding: 10px;
            background: white;
            border-radius: 5px;
            display: none;
        }
        
        .file-info.active {
            display: block;
        }
        
        .preview-section {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            display: none;
        }
        
        .preview-section.active {
            display: block;
        }
        
        .preview-table {
            width: 100%;
            background: white;
            border-radius: 5px;
            overflow: hidden;
            margin-top: 10px;
        }
        
        .preview-table table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .preview-table th {
            background: #6c757d;
            color: white;
            padding: 10px;
            text-align: left;
            font-weight: 600;
        }
        
        .preview-table td {
            padding: 8px 10px;
            border-bottom: 1px solid #dee2e6;
        }
        
        .preview-table tr:hover {
            background: #f8f9fa;
        }
        
        .options-section {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 20px;
            margin-bottom: 20px;
            display: none;
        }
        
        .options-section.active {
            display: block;
        }
        
        .option-group {
            margin-bottom: 15px;
        }
        
        .option-group label {
            display: flex;
            align-items: center;
            cursor: pointer;
            padding: 5px;
            border-radius: 5px;
            transition: background 0.2s;
        }
        
        .option-group label:hover {
            background: white;
        }
        
        .option-group input[type="checkbox"] {
            margin-right: 10px;
            width: 18px;
            height: 18px;
            cursor: pointer;
        }
        
        .option-text {
            flex: 1;
        }
        
        .option-description {
            font-size: 0.9em;
            color: #6c757d;
            margin-left: 28px;
            margin-top: 3px;
        }
        
        .generate-button {
            width: 100%;
            padding: 15px;
            background: linear-gradient(135deg, #28a745, #20c997);
            color: white;
            border: none;
            border-radius: 10px;
            font-size: 1.1em;
            font-weight: 600;
            cursor: pointer;
            transition: transform 0.2s, box-shadow 0.2s;
            display: none;
        }
        
        .generate-button.active {
            display: block;
        }
        
        .generate-button:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(40, 167, 69, 0.4);
        }
        
        .generate-button:active {
            transform: translateY(0);
        }
        
        .output-section {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 20px;
            display: none;
        }
        
        .output-section.active {
            display: block;
        }
        
        .sql-output {
            background: #263238;
            color: #aed581;
            padding: 20px;
            border-radius: 10px;
            font-family: 'Courier New', monospace;
            font-size: 14px;
            line-height: 1.6;
            overflow-x: auto;
            max-height: 600px;
            overflow-y: auto;
            margin: 15px 0;
        }
        
        .sql-comment {
            color: #78909c;
        }
        
        .sql-keyword {
            color: #82b1ff;
            font-weight: bold;
        }
        
        .sql-string {
            color: #ffcc80;
        }
        
        .copy-button, .download-button {
            padding: 10px 20px;
            margin-right: 10px;
            background: #007bff;
            color: white;
            border: none;
            border-radius: 5px;
            cursor: pointer;
            font-weight: 600;
            transition: background 0.2s;
        }
        
        .copy-button:hover {
            background: #0056b3;
        }
        
        .download-button {
            background: #6c757d;
        }
        
        .download-button:hover {
            background: #545b62;
        }
        
        .stats {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin-top: 20px;
        }
        
        .stat-card {
            background: white;
            padding: 15px;
            border-radius: 10px;
            text-align: center;
            border-left: 4px solid #667eea;
        }
        
        .stat-value {
            font-size: 2em;
            font-weight: bold;
            color: #667eea;
        }
        
        .stat-label {
            color: #6c757d;
            margin-top: 5px;
        }
        
        .warning {
            background: #fff3cd;
            border-left: 4px solid #ffc107;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        
        .warning-title {
            font-weight: bold;
            color: #856404;
            margin-bottom: 5px;
        }
        
        .error {
            background: #f8d7da;
            border-left: 4px solid #dc3545;
            padding: 10px;
            border-radius: 5px;
            margin-top: 10px;
            display: none;
        }
        
        .error.active {
            display: block;
        }
    </style>
 </head>
 <body>
    <div class="container">
        <h1>Magento 2 SKU Update SQL Generator</h1>
        <p class="subtitle">Generate SQL queries to safely update product SKUs in your Magento 2 database</p>
        
        <div class="warning">
            <div class="warning-title">⚠️ Important Notes:</div>
            <ul style="margin-left: 20px; margin-top: 10px;">
                <li>Always backup your database before running these queries</li>
                <li>Run on a staging environment first</li>
                <li>Clear all caches and reindex after execution</li>
                <li>This tool assumes products are new/unused (no orders, quotes, or wishlists)</li>
            </ul>
        </div>
        
        <div class="input-section">
            <h2 style="margin-bottom: 15px;">Step 1: Upload SKU Mapping File</h2>
            <p style="color: #6c757d; margin-bottom: 15px;">File format: Tab-delimited text file with two columns (old_sku → new_sku)</p>
            
            <div class="file-input-wrapper">
                <input type="file" id="fileInput" accept=".txt,.tsv,.tab">
                <label for="fileInput" class="file-input-label">
                    📁 Choose File (Tab-Delimited .txt)
                </label>
            </div>
            
            <div class="file-info" id="fileInfo"></div>
            <div class="error" id="fileError"></div>
        </div>
        
        <div class="preview-section" id="previewSection">
            <h2 style="margin-bottom: 10px;">Step 2: Review SKU Mappings</h2>
            <div class="preview-table">
                <table id="previewTable">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>Old SKU</th>
                            <th>New SKU</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody></tbody>
                </table>
            </div>
            <div class="stats" id="stats"></div>
        </div>
        
        <div class="options-section" id="optionsSection">
            <h2 style="margin-bottom: 15px;">Step 3: Configure Options</h2>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeBackup" checked>
                    <div class="option-text">Include Backup Tables</div>
                </label>
                <div class="option-description">Creates backup of affected tables before updates</div>
            </div>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeValidation" checked>
                    <div class="option-text">Include Validation Queries</div>
                </label>
                <div class="option-description">Adds pre-flight checks for SKU existence and duplicates</div>
            </div>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeTransaction" checked>
                    <div class="option-text">Wrap in Transaction</div>
                </label>
                <div class="option-description">Allows rollback if any query fails</div>
            </div>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeMSI" checked>
                    <div class="option-text">Update MSI Tables</div>
                </label>
                <div class="option-description">Updates Multi-Source Inventory tables</div>
            </div>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeUrlRewrite" checked>
                    <div class="option-text">Clean URL Rewrites</div>
                </label>
                <div class="option-description">Removes old URL rewrites for updated products</div>
            </div>
            
            <div class="option-group">
                <label>
                    <input type="checkbox" id="includeCommands">
                    <div class="option-text">Include CLI Commands</div>
                </label>
                <div class="option-description">Adds Magento cache clear and reindex commands</div>
            </div>
        </div>
        
        <button class="generate-button" id="generateButton">🚀 Generate SQL Queries</button>
        
        <div class="output-section" id="outputSection">
            <h2 style="margin-bottom: 10px;">Generated SQL Queries</h2>
            <div>
                <button class="copy-button" onclick="copySQL()">📋 Copy to Clipboard</button>
                <button class="download-button" onclick="downloadSQL()">💾 Download SQL File</button>
            </div>
            <div class="sql-output" id="sqlOutput"></div>
        </div>
    </div>
    
    <script>
        let skuMappings = [];
        let generatedSQL = '';
        
        document.getElementById('fileInput').addEventListener('change', handleFileSelect);
        document.getElementById('generateButton').addEventListener('click', generateSQL);
        
        function handleFileSelect(event) {
            const file = event.target.files[0];
            if (!file) return;
            
            const fileInfo = document.getElementById('fileInfo');
            const fileError = document.getElementById('fileError');
            const previewSection = document.getElementById('previewSection');
            const optionsSection = document.getElementById('optionsSection');
            const generateButton = document.getElementById('generateButton');
            const outputSection = document.getElementById('outputSection');
            
            // Reset sections
            fileError.classList.remove('active');
            outputSection.classList.remove('active');
            skuMappings = [];
            
            // Show file info
            fileInfo.innerHTML = `
                <strong>File:</strong> ${file.name}<br>
                <strong>Size:</strong> ${(file.size / 1024).toFixed(2)} KB<br>
                <strong>Type:</strong> ${file.type || 'text/plain'}
            `;
            fileInfo.classList.add('active');
            
            // Read file
            const reader = new FileReader();
            reader.onload = function(e) {
                try {
                    const content = e.target.result;
                    const lines = content.trim().split('\n');
                    
                    skuMappings = [];
                    const duplicateChecks = new Set();
                    const errors = [];
                    
                    lines.forEach((line, index) => {
                        if (line.trim()) {
                            const parts = line.split('\t');
                            if (parts.length !== 2) {
                                errors.push(`Line ${index + 1}: Expected 2 columns, found ${parts.length}`);
                                return;
                            }
                            
                            const oldSku = parts[0].trim();
                            const newSku = parts[1].trim();
                            
                            if (!oldSku || !newSku) {
                                errors.push(`Line ${index + 1}: Empty SKU detected`);
                                return;
                            }
                            
                            if (duplicateChecks.has(oldSku)) {
                                errors.push(`Line ${index + 1}: Duplicate old SKU: ${oldSku}`);
                            }
                            if (duplicateChecks.has(newSku)) {
                                errors.push(`Line ${index + 1}: New SKU conflicts with existing: ${newSku}`);
                            }
                            
                            duplicateChecks.add(oldSku);
                            duplicateChecks.add(newSku);
                            
                            skuMappings.push({
                                oldSku: oldSku,
                                newSku: newSku,
                                line: index + 1
                            });
                        }
                    });
                    
                    if (errors.length > 0) {
                        fileError.innerHTML = '<strong>Errors found:</strong><br>' + errors.join('<br>');
                        fileError.classList.add('active');
                    }
                    
                    if (skuMappings.length > 0) {
                        displayPreview();
                        previewSection.classList.add('active');
                        optionsSection.classList.add('active');
                        generateButton.classList.add('active');
                    } else {
                        throw new Error('No valid SKU mappings found in file');
                    }
                    
                } catch (error) {
                    fileError.innerHTML = '<strong>Error parsing file:</strong> ' + error.message;
                    fileError.classList.add('active');
                }
            };
            
            reader.onerror = function() {
                fileError.innerHTML = '<strong>Error reading file</strong>';
                fileError.classList.add('active');
            };
            
            reader.readAsText(file);
        }
        
        function displayPreview() {
            const tbody = document.querySelector('#previewTable tbody');
            tbody.innerHTML = '';
            
            // Show first 10 mappings
            const previewCount = Math.min(10, skuMappings.length);
            for (let i = 0; i < previewCount; i++) {
                const mapping = skuMappings[i];
                const row = tbody.insertRow();
                row.innerHTML = `
                    <td>${mapping.line}</td>
                    <td><code>${escapeHtml(mapping.oldSku)}</code></td>
                    <td><code>${escapeHtml(mapping.newSku)}</code></td>
                    <td><span style="color: green;">✓ Valid</span></td>
                `;
            }
            
            if (skuMappings.length > 10) {
                const row = tbody.insertRow();
                row.innerHTML = `
                    <td colspan="4" style="text-align: center; font-style: italic;">
                        ... and ${skuMappings.length - 10} more mappings
                    </td>
                `;
            }
            
            // Display stats
            const stats = document.getElementById('stats');
            stats.innerHTML = `
                <div class="stat-card">
                    <div class="stat-value">${skuMappings.length}</div>
                    <div class="stat-label">Total SKUs</div>
                </div>
                <div class="stat-card">
                    <div class="stat-value">${new Set(skuMappings.map(m => m.oldSku)).size}</div>
                    <div class="stat-label">Unique Old SKUs</div>
                </div>
                <div class="stat-card">
                    <div class="stat-value">${new Set(skuMappings.map(m => m.newSku)).size}</div>
                    <div class="stat-label">Unique New SKUs</div>
                </div>
            `;
        }
        
        function generateSQL() {
            const includeBackup = document.getElementById('includeBackup').checked;
            const includeValidation = document.getElementById('includeValidation').checked;
            const includeTransaction = document.getElementById('includeTransaction').checked;
            const includeMSI = document.getElementById('includeMSI').checked;
            const includeUrlRewrite = document.getElementById('includeUrlRewrite').checked;
            const includeCommands = document.getElementById('includeCommands').checked;
            
            let sql = [];
            
            // Header
            sql.push('-- Magento 2 SKU Update Script');
            sql.push(`-- Generated: ${new Date().toISOString()}`);
            sql.push(`-- Total SKU Updates: ${skuMappings.length}`);
            sql.push('-- ================================================');
            sql.push('');
            
            // Validation queries
            if (includeValidation) {
                sql.push('-- ================================================');
                sql.push('-- VALIDATION QUERIES (Run these first!)');
                sql.push('-- ================================================');
                sql.push('');
                sql.push('-- Check if all old SKUs exist:');
                sql.push('SELECT sku FROM catalog_product_entity WHERE sku IN (');
                skuMappings.forEach((mapping, index) => {
                    const comma = index < skuMappings.length - 1 ? ',' : '';
                    sql.push(`    '${escapeSql(mapping.oldSku)}'${comma}`);
                });
                sql.push(');');
                sql.push('');
                
                sql.push('-- Check if any new SKUs already exist (should return 0 rows):');
                sql.push('SELECT sku FROM catalog_product_entity WHERE sku IN (');
                skuMappings.forEach((mapping, index) => {
                    const comma = index < skuMappings.length - 1 ? ',' : '';
                    sql.push(`    '${escapeSql(mapping.newSku)}'${comma}`);
                });
                sql.push(');');
                sql.push('');
            }
            
            // Backup tables
            if (includeBackup) {
                sql.push('-- ================================================');
                sql.push('-- CREATE BACKUP TABLES');
                sql.push('-- ================================================');
                sql.push('');
                const timestamp = new Date().toISOString().replace(/[:\-T]/g, '').substring(0, 14);
                sql.push(`CREATE TABLE catalog_product_entity_backup_${timestamp} AS SELECT * FROM catalog_product_entity;`);
                if (includeMSI) {
                    sql.push(`CREATE TABLE inventory_source_item_backup_${timestamp} AS SELECT * FROM inventory_source_item;`);
                }
                sql.push('');
            }
            
            // Transaction start
            if (includeTransaction) {
                sql.push('-- ================================================');
                sql.push('-- BEGIN TRANSACTION');
                sql.push('-- ================================================');
                sql.push('START TRANSACTION;');
                sql.push('');
            }
            
            // Main updates
            sql.push('-- ================================================');
            sql.push('-- UPDATE CATALOG_PRODUCT_ENTITY');
            sql.push('-- ================================================');
            sql.push('');
            
            skuMappings.forEach((mapping, index) => {
                sql.push(`-- Update ${index + 1}/${skuMappings.length}: ${mapping.oldSku} -> ${mapping.newSku}`);
                sql.push(`UPDATE catalog_product_entity SET sku = '${escapeSql(mapping.newSku)}' WHERE sku = '${escapeSql(mapping.oldSku)}';`);
                sql.push('');
            });
            
            // MSI updates
            if (includeMSI) {
                sql.push('-- ================================================');
                sql.push('-- UPDATE INVENTORY_SOURCE_ITEM (MSI)');
                sql.push('-- ================================================');
                sql.push('');
                
                skuMappings.forEach((mapping, index) => {
                    sql.push(`UPDATE inventory_source_item SET sku = '${escapeSql(mapping.newSku)}' WHERE sku = '${escapeSql(mapping.oldSku)}';`);
                });
                sql.push('');
            }
            
            // URL Rewrite cleanup
            if (includeUrlRewrite) {
                sql.push('-- ================================================');
                sql.push('-- CLEAN URL REWRITES');
                sql.push('-- ================================================');
                sql.push('');
                sql.push('-- Delete URL rewrites for updated products');
                sql.push('DELETE FROM url_rewrite WHERE entity_type = \'product\' AND entity_id IN (');
                sql.push('    SELECT entity_id FROM catalog_product_entity WHERE sku IN (');
                skuMappings.forEach((mapping, index) => {
                    const comma = index < skuMappings.length - 1 ? ',' : '';
                    sql.push(`        '${escapeSql(mapping.newSku)}'${comma}`);
                });
                sql.push('    )');
                sql.push(');');
                sql.push('');
            }
            
            // Transaction commit
            if (includeTransaction) {
                sql.push('-- ================================================');
                sql.push('-- COMMIT TRANSACTION');
                sql.push('-- ================================================');
                sql.push('COMMIT;');
                sql.push('');
                sql.push('-- If any errors occurred, run: ROLLBACK;');
                sql.push('');
            }
            
            // Post-update verification
            sql.push('-- ================================================');
            sql.push('-- POST-UPDATE VERIFICATION');
            sql.push('-- ================================================');
            sql.push('');
            sql.push('-- Verify all SKUs were updated (should match total count):');
            sql.push('SELECT COUNT(*) as updated_count FROM catalog_product_entity WHERE sku IN (');
            skuMappings.forEach((mapping, index) => {
                const comma = index < skuMappings.length - 1 ? ',' : '';
                sql.push(`    '${escapeSql(mapping.newSku)}'${comma}`);
            });
            sql.push(');');
            sql.push('');
            
            // CLI commands
            if (includeCommands) {
                sql.push('-- ================================================');
                sql.push('-- MAGENTO CLI COMMANDS (Run after SQL)');
                sql.push('-- ================================================');
                sql.push('/*');
                sql.push('Run these commands from Magento root directory:');
                sql.push('');
                sql.push('# Clear all caches');
                sql.push('bin/magento cache:clean');
                sql.push('bin/magento cache:flush');
                sql.push('');
                sql.push('# Reindex all indexes');
                sql.push('bin/magento indexer:reindex');
                sql.push('');
                sql.push('# Or reindex specific indexes:');
                sql.push('bin/magento indexer:reindex catalog_product_attribute');
                sql.push('bin/magento indexer:reindex catalog_product_price');
                sql.push('bin/magento indexer:reindex cataloginventory_stock');
                sql.push('bin/magento indexer:reindex catalogsearch_fulltext');
                sql.push('*/');
            }
            
            generatedSQL = sql.join('\n');
            displaySQL();
            
            document.getElementById('outputSection').classList.add('active');
            document.getElementById('outputSection').scrollIntoView({ behavior: 'smooth' });
        }
        
        function displaySQL() {
            const output = document.getElementById('sqlOutput');
            let formatted = generatedSQL;
            
            // Syntax highlighting
            formatted = formatted.replace(/^--.*$/gm, '<span class="sql-comment">$&</span>');
            formatted = formatted.replace(/\b(SELECT|FROM|WHERE|UPDATE|SET|IN|AS|CREATE|TABLE|DELETE|INSERT|INTO|VALUES|START|TRANSACTION|COMMIT|ROLLBACK|COUNT)\b/gi, 
                '<span class="sql-keyword">$&</span>');
            formatted = formatted.replace(/'[^']*'/g, '<span class="sql-string">$&</span>');
            
            output.innerHTML = formatted;
        }
        
        function escapeSql(str) {
            return str.replace(/'/g, "''");
        }
        
        function escapeHtml(str) {
            const div = document.createElement('div');
            div.textContent = str;
            return div.innerHTML;
        }
        
        function copySQL() {
            navigator.clipboard.writeText(generatedSQL).then(() => {
                const button = event.target;
                const originalText = button.textContent;
                button.textContent = '✓ Copied!';
                button.style.background = '#28a745';
                setTimeout(() => {
                    button.textContent = originalText;
                    button.style.background = '#007bff';
                }, 2000);
            });
        }
        
        function downloadSQL() {
            const timestamp = new Date().toISOString().replace(/[:\-T]/g, '').substring(0, 14);
            const filename = `magento_sku_update_${timestamp}.sql`;
            
            const blob = new Blob([generatedSQL], { type: 'text/plain' });
            const url = window.URL.createObjectURL(blob);
            const a = document.createElement('a');
            a.href = url;
            a.download = filename;
            document.body.appendChild(a);
            a.click();
            document.body.removeChild(a);
            window.URL.revokeObjectURL(url);
        }
    </script>
 </body>
 </html>
	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Magento 2 SKU Update SQL Generator</title>
	<style>
	* {
	margin: 0;
	padding: 0;
	box-sizing: border-box;
	}

	body {
	font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
	background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
	min-height: 100vh;
	padding: 20px;
	}

	.container {
	max-width: 1200px;
	margin: 0 auto;
	background: rgba(255, 255, 255, 0.95);
	border-radius: 20px;
	padding: 30px;
	box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);
	}

	h1 {
	color: #333;
	margin-bottom: 10px;
	font-size: 2.5em;
	background: linear-gradient(135deg, #667eea, #764ba2);
	-webkit-background-clip: text;
	-webkit-text-fill-color: transparent;
	background-clip: text;
	}

	.subtitle {
	color: #666;
	margin-bottom: 30px;
	font-size: 1.1em;
	}

	.input-section {
	background: #f8f9fa;
	border-radius: 10px;
	padding: 20px;
	margin-bottom: 20px;
	}

	.file-input-wrapper {
	position: relative;
	display: inline-block;
	cursor: pointer;
	width: 100%;
	}

	.file-input-wrapper input[type=file] {
	position: absolute;
	left: -9999px;
	}

	.file-input-label {
	display: block;
	padding: 15px;
	background: linear-gradient(135deg, #667eea, #764ba2);
	color: white;
	border-radius: 10px;
	text-align: center;
	font-weight: 600;
	transition: transform 0.2s, box-shadow 0.2s;
	cursor: pointer;
	}

	.file-input-label:hover {
	transform: translateY(-2px);
	box-shadow: 0 5px 15px rgba(102, 126, 234, 0.4);
	}

	.file-info {
	margin-top: 15px;
	padding: 10px;
	background: white;
	border-radius: 5px;
	display: none;
	}

	.file-info.active {
	display: block;
	}

	.preview-section {
	background: #f8f9fa;
	border-radius: 10px;
	padding: 20px;
	margin-bottom: 20px;
	display: none;
	}

	.preview-section.active {
	display: block;
	}

	.preview-table {
	width: 100%;
	background: white;
	border-radius: 5px;
	overflow: hidden;
	margin-top: 10px;
	}

	.preview-table table {
	width: 100%;
	border-collapse: collapse;
	}

	.preview-table th {
	background: #6c757d;
	color: white;
	padding: 10px;
	text-align: left;
	font-weight: 600;
	}

	.preview-table td {
	padding: 8px 10px;
	border-bottom: 1px solid #dee2e6;
	}

	.preview-table tr:hover {
	background: #f8f9fa;
	}

	.options-section {
	background: #f8f9fa;
	border-radius: 10px;
	padding: 20px;
	margin-bottom: 20px;
	display: none;
	}

	.options-section.active {
	display: block;
	}

	.option-group {
	margin-bottom: 15px;
	}

	.option-group label {
	display: flex;
	align-items: center;
	cursor: pointer;
	padding: 5px;
	border-radius: 5px;
	transition: background 0.2s;
	}

	.option-group label:hover {
	background: white;
	}

	.option-group input[type="checkbox"] {
	margin-right: 10px;
	width: 18px;
	height: 18px;
	cursor: pointer;
	}

	.option-text {
	flex: 1;
	}

	.option-description {
	font-size: 0.9em;
	color: #6c757d;
	margin-left: 28px;
	margin-top: 3px;
	}

	.generate-button {
	width: 100%;
	padding: 15px;
	background: linear-gradient(135deg, #28a745, #20c997);
	color: white;
	border: none;
	border-radius: 10px;
	font-size: 1.1em;
	font-weight: 600;
	cursor: pointer;
	transition: transform 0.2s, box-shadow 0.2s;
	display: none;
	}

	.generate-button.active {
	display: block;
	}

	.generate-button:hover {
	transform: translateY(-2px);
	box-shadow: 0 5px 15px rgba(40, 167, 69, 0.4);
	}

	.generate-button:active {
	transform: translateY(0);
	}

	.output-section {
	background: #f8f9fa;
	border-radius: 10px;
	padding: 20px;
	display: none;
	}

	.output-section.active {
	display: block;
	}

	.sql-output {
	background: #263238;
	color: #aed581;
	padding: 20px;
	border-radius: 10px;
	font-family: 'Courier New', monospace;
	font-size: 14px;
	line-height: 1.6;
	overflow-x: auto;
	max-height: 600px;
	overflow-y: auto;
	margin: 15px 0;
	}

	.sql-comment {
	color: #78909c;
	}

	.sql-keyword {
	color: #82b1ff;
	font-weight: bold;
	}

	.sql-string {
	color: #ffcc80;
	}

	.copy-button, .download-button {
	padding: 10px 20px;
	margin-right: 10px;
	background: #007bff;
	color: white;
	border: none;
	border-radius: 5px;
	cursor: pointer;
	font-weight: 600;
	transition: background 0.2s;
	}

	.copy-button:hover {
	background: #0056b3;
	}

	.download-button {
	background: #6c757d;
	}

	.download-button:hover {
	background: #545b62;
	}

	.stats {
	display: grid;
	grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
	gap: 15px;
	margin-top: 20px;
	}

	.stat-card {
	background: white;
	padding: 15px;
	border-radius: 10px;
	text-align: center;
	border-left: 4px solid #667eea;
	}

	.stat-value {
	font-size: 2em;
	font-weight: bold;
	color: #667eea;
	}

	.stat-label {
	color: #6c757d;
	margin-top: 5px;
	}

	.warning {
	background: #fff3cd;
	border-left: 4px solid #ffc107;
	padding: 15px;
	border-radius: 5px;
	margin-bottom: 20px;
	}

	.warning-title {
	font-weight: bold;
	color: #856404;
	margin-bottom: 5px;
	}

	.error {
	background: #f8d7da;
	border-left: 4px solid #dc3545;
	padding: 10px;
	border-radius: 5px;
	margin-top: 10px;
	display: none;
	}

	.error.active {
	display: block;
	}
	</style>
	</head>
	<body>
	<div class="container">
	<h1>Magento 2 SKU Update SQL Generator</h1>
	<p class="subtitle">Generate SQL queries to safely update product SKUs in your Magento 2 database</p>

	<div class="warning">
	<div class="warning-title">⚠️ Important Notes:</div>
	<ul style="margin-left: 20px; margin-top: 10px;">
	<li>Always backup your database before running these queries</li>
	<li>Run on a staging environment first</li>
	<li>Clear all caches and reindex after execution</li>
	<li>This tool assumes products are new/unused (no orders, quotes, or wishlists)</li>
	</ul>
	</div>

	<div class="input-section">
	<h2 style="margin-bottom: 15px;">Step 1: Upload SKU Mapping File</h2>
	<p style="color: #6c757d; margin-bottom: 15px;">File format: Tab-delimited text file with two columns (old_sku → new_sku)</p>

	<div class="file-input-wrapper">
	<input type="file" id="fileInput" accept=".txt,.tsv,.tab">
	<label for="fileInput" class="file-input-label">
	📁 Choose File (Tab-Delimited .txt)
	</label>
	</div>

	<div class="file-info" id="fileInfo"></div>
	<div class="error" id="fileError"></div>
	</div>

	<div class="preview-section" id="previewSection">
	<h2 style="margin-bottom: 10px;">Step 2: Review SKU Mappings</h2>
	<div class="preview-table">
	<table id="previewTable">
	<thead>
	<tr>
	<th>#</th>
	<th>Old SKU</th>
	<th>New SKU</th>
	<th>Status</th>
	</tr>
	</thead>
	<tbody></tbody>
	</table>
	</div>
	<div class="stats" id="stats"></div>
	</div>

	<div class="options-section" id="optionsSection">
	<h2 style="margin-bottom: 15px;">Step 3: Configure Options</h2>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeBackup" checked>
	<div class="option-text">Include Backup Tables</div>
	</label>
	<div class="option-description">Creates backup of affected tables before updates</div>
	</div>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeValidation" checked>
	<div class="option-text">Include Validation Queries</div>
	</label>
	<div class="option-description">Adds pre-flight checks for SKU existence and duplicates</div>
	</div>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeTransaction" checked>
	<div class="option-text">Wrap in Transaction</div>
	</label>
	<div class="option-description">Allows rollback if any query fails</div>
	</div>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeMSI" checked>
	<div class="option-text">Update MSI Tables</div>
	</label>
	<div class="option-description">Updates Multi-Source Inventory tables</div>
	</div>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeUrlRewrite" checked>
	<div class="option-text">Clean URL Rewrites</div>
	</label>
	<div class="option-description">Removes old URL rewrites for updated products</div>
	</div>

	<div class="option-group">
	<label>
	<input type="checkbox" id="includeCommands">
	<div class="option-text">Include CLI Commands</div>
	</label>
	<div class="option-description">Adds Magento cache clear and reindex commands</div>
	</div>
	</div>

	<button class="generate-button" id="generateButton">🚀 Generate SQL Queries</button>

	<div class="output-section" id="outputSection">
	<h2 style="margin-bottom: 10px;">Generated SQL Queries</h2>
	<div>
	<button class="copy-button" onclick="copySQL()">📋 Copy to Clipboard</button>
	<button class="download-button" onclick="downloadSQL()">💾 Download SQL File</button>
	</div>
	<div class="sql-output" id="sqlOutput"></div>
	</div>
	</div>

	<script>
	let skuMappings = [];
	let generatedSQL = '';

	document.getElementById('fileInput').addEventListener('change', handleFileSelect);
	document.getElementById('generateButton').addEventListener('click', generateSQL);

	function handleFileSelect(event) {
	const file = event.target.files[0];
	if (!file) return;

	const fileInfo = document.getElementById('fileInfo');
	const fileError = document.getElementById('fileError');
	const previewSection = document.getElementById('previewSection');
	const optionsSection = document.getElementById('optionsSection');
	const generateButton = document.getElementById('generateButton');
	const outputSection = document.getElementById('outputSection');

	// Reset sections
	fileError.classList.remove('active');
	outputSection.classList.remove('active');
	skuMappings = [];

	// Show file info
	fileInfo.innerHTML = `
	<strong>File:</strong> ${file.name}<br>
	<strong>Size:</strong> ${(file.size / 1024).toFixed(2)} KB<br>
	<strong>Type:</strong> ${file.type \|\| 'text/plain'}
	`;
	fileInfo.classList.add('active');

	// Read file
	const reader = new FileReader();
	reader.onload = function(e) {
	try {
	const content = e.target.result;
	const lines = content.trim().split('\n');

	skuMappings = [];
	const duplicateChecks = new Set();
	const errors = [];

	lines.forEach((line, index) => {
	if (line.trim()) {
	const parts = line.split('\t');
	if (parts.length !== 2) {
	errors.push(`Line ${index + 1}: Expected 2 columns, found ${parts.length}`);
	return;
	}

	const oldSku = parts[0].trim();
	const newSku = parts[1].trim();

	if (!oldSku \|\| !newSku) {
	errors.push(`Line ${index + 1}: Empty SKU detected`);
	return;
	}

	if (duplicateChecks.has(oldSku)) {
	errors.push(`Line ${index + 1}: Duplicate old SKU: ${oldSku}`);
	}
	if (duplicateChecks.has(newSku)) {
	errors.push(`Line ${index + 1}: New SKU conflicts with existing: ${newSku}`);
	}

	duplicateChecks.add(oldSku);
	duplicateChecks.add(newSku);

	skuMappings.push({
	oldSku: oldSku,
	newSku: newSku,
	line: index + 1
	});
	}
	});

	if (errors.length > 0) {
	fileError.innerHTML = '<strong>Errors found:</strong><br>' + errors.join('<br>');
	fileError.classList.add('active');
	}

	if (skuMappings.length > 0) {
	displayPreview();
	previewSection.classList.add('active');
	optionsSection.classList.add('active');
	generateButton.classList.add('active');
	} else {
	throw new Error('No valid SKU mappings found in file');
	}

	} catch (error) {
	fileError.innerHTML = '<strong>Error parsing file:</strong> ' + error.message;
	fileError.classList.add('active');
	}
	};

	reader.onerror = function() {
	fileError.innerHTML = '<strong>Error reading file</strong>';
	fileError.classList.add('active');
	};

	reader.readAsText(file);
	}

	function displayPreview() {
	const tbody = document.querySelector('#previewTable tbody');
	tbody.innerHTML = '';

	// Show first 10 mappings
	const previewCount = Math.min(10, skuMappings.length);
	for (let i = 0; i < previewCount; i++) {
	const mapping = skuMappings[i];
	const row = tbody.insertRow();
	row.innerHTML = `
	<td>${mapping.line}</td>
	<td><code>${escapeHtml(mapping.oldSku)}</code></td>
	<td><code>${escapeHtml(mapping.newSku)}</code></td>
	<td><span style="color: green;">✓ Valid</span></td>
	`;
	}

	if (skuMappings.length > 10) {
	const row = tbody.insertRow();
	row.innerHTML = `
	<td colspan="4" style="text-align: center; font-style: italic;">
	... and ${skuMappings.length - 10} more mappings
	</td>
	`;
	}

	// Display stats
	const stats = document.getElementById('stats');
	stats.innerHTML = `
	<div class="stat-card">
	<div class="stat-value">${skuMappings.length}</div>
	<div class="stat-label">Total SKUs</div>
	</div>
	<div class="stat-card">
	<div class="stat-value">${new Set(skuMappings.map(m => m.oldSku)).size}</div>
	<div class="stat-label">Unique Old SKUs</div>
	</div>
	<div class="stat-card">
	<div class="stat-value">${new Set(skuMappings.map(m => m.newSku)).size}</div>
	<div class="stat-label">Unique New SKUs</div>
	</div>
	`;
	}

	function generateSQL() {
	const includeBackup = document.getElementById('includeBackup').checked;
	const includeValidation = document.getElementById('includeValidation').checked;
	const includeTransaction = document.getElementById('includeTransaction').checked;
	const includeMSI = document.getElementById('includeMSI').checked;
	const includeUrlRewrite = document.getElementById('includeUrlRewrite').checked;
	const includeCommands = document.getElementById('includeCommands').checked;

	let sql = [];

	// Header
	sql.push('-- Magento 2 SKU Update Script');
	sql.push(`-- Generated: ${new Date().toISOString()}`);
	sql.push(`-- Total SKU Updates: ${skuMappings.length}`);
	sql.push('-- ================================================');
	sql.push('');

	// Validation queries
	if (includeValidation) {
	sql.push('-- ================================================');
	sql.push('-- VALIDATION QUERIES (Run these first!)');
	sql.push('-- ================================================');
	sql.push('');
	sql.push('-- Check if all old SKUs exist:');
	sql.push('SELECT sku FROM catalog_product_entity WHERE sku IN (');
	skuMappings.forEach((mapping, index) => {
	const comma = index < skuMappings.length - 1 ? ',' : '';
	sql.push(` '${escapeSql(mapping.oldSku)}'${comma}`);
	});
	sql.push(');');
	sql.push('');

	sql.push('-- Check if any new SKUs already exist (should return 0 rows):');
	sql.push('SELECT sku FROM catalog_product_entity WHERE sku IN (');
	skuMappings.forEach((mapping, index) => {
	const comma = index < skuMappings.length - 1 ? ',' : '';
	sql.push(` '${escapeSql(mapping.newSku)}'${comma}`);
	});
	sql.push(');');
	sql.push('');
	}

	// Backup tables
	if (includeBackup) {
	sql.push('-- ================================================');
	sql.push('-- CREATE BACKUP TABLES');
	sql.push('-- ================================================');
	sql.push('');
	const timestamp = new Date().toISOString().replace(/[:\-T]/g, '').substring(0, 14);
	sql.push(`CREATE TABLE catalog_product_entity_backup_${timestamp} AS SELECT * FROM catalog_product_entity;`);
	if (includeMSI) {
	sql.push(`CREATE TABLE inventory_source_item_backup_${timestamp} AS SELECT * FROM inventory_source_item;`);
	}
	sql.push('');
	}

	// Transaction start
	if (includeTransaction) {
	sql.push('-- ================================================');
	sql.push('-- BEGIN TRANSACTION');
	sql.push('-- ================================================');
	sql.push('START TRANSACTION;');
	sql.push('');
	}

	// Main updates
	sql.push('-- ================================================');
	sql.push('-- UPDATE CATALOG_PRODUCT_ENTITY');
	sql.push('-- ================================================');
	sql.push('');

	skuMappings.forEach((mapping, index) => {
	sql.push(`-- Update ${index + 1}/${skuMappings.length}: ${mapping.oldSku} -> ${mapping.newSku}`);
	sql.push(`UPDATE catalog_product_entity SET sku = '${escapeSql(mapping.newSku)}' WHERE sku = '${escapeSql(mapping.oldSku)}';`);
	sql.push('');
	});

	// MSI updates
	if (includeMSI) {
	sql.push('-- ================================================');
	sql.push('-- UPDATE INVENTORY_SOURCE_ITEM (MSI)');
	sql.push('-- ================================================');
	sql.push('');

	skuMappings.forEach((mapping, index) => {
	sql.push(`UPDATE inventory_source_item SET sku = '${escapeSql(mapping.newSku)}' WHERE sku = '${escapeSql(mapping.oldSku)}';`);
	});
	sql.push('');
	}

	// URL Rewrite cleanup
	if (includeUrlRewrite) {
	sql.push('-- ================================================');
	sql.push('-- CLEAN URL REWRITES');
	sql.push('-- ================================================');
	sql.push('');
	sql.push('-- Delete URL rewrites for updated products');
	sql.push('DELETE FROM url_rewrite WHERE entity_type = \'product\' AND entity_id IN (');
	sql.push(' SELECT entity_id FROM catalog_product_entity WHERE sku IN (');
	skuMappings.forEach((mapping, index) => {
	const comma = index < skuMappings.length - 1 ? ',' : '';
	sql.push(` '${escapeSql(mapping.newSku)}'${comma}`);
	});
	sql.push(' )');
	sql.push(');');
	sql.push('');
	}

	// Transaction commit
	if (includeTransaction) {
	sql.push('-- ================================================');
	sql.push('-- COMMIT TRANSACTION');
	sql.push('-- ================================================');
	sql.push('COMMIT;');
	sql.push('');
	sql.push('-- If any errors occurred, run: ROLLBACK;');
	sql.push('');
	}

	// Post-update verification
	sql.push('-- ================================================');
	sql.push('-- POST-UPDATE VERIFICATION');
	sql.push('-- ================================================');
	sql.push('');
	sql.push('-- Verify all SKUs were updated (should match total count):');
	sql.push('SELECT COUNT(*) as updated_count FROM catalog_product_entity WHERE sku IN (');
	skuMappings.forEach((mapping, index) => {
	const comma = index < skuMappings.length - 1 ? ',' : '';
	sql.push(` '${escapeSql(mapping.newSku)}'${comma}`);
	});
	sql.push(');');
	sql.push('');

	// CLI commands
	if (includeCommands) {
	sql.push('-- ================================================');
	sql.push('-- MAGENTO CLI COMMANDS (Run after SQL)');
	sql.push('-- ================================================');
	sql.push('/*');
	sql.push('Run these commands from Magento root directory:');
	sql.push('');
	sql.push('# Clear all caches');
	sql.push('bin/magento cache:clean');
	sql.push('bin/magento cache:flush');
	sql.push('');
	sql.push('# Reindex all indexes');
	sql.push('bin/magento indexer:reindex');
	sql.push('');
	sql.push('# Or reindex specific indexes:');
	sql.push('bin/magento indexer:reindex catalog_product_attribute');
	sql.push('bin/magento indexer:reindex catalog_product_price');
	sql.push('bin/magento indexer:reindex cataloginventory_stock');
	sql.push('bin/magento indexer:reindex catalogsearch_fulltext');
	sql.push('*/');
	}

	generatedSQL = sql.join('\n');
	displaySQL();

	document.getElementById('outputSection').classList.add('active');
	document.getElementById('outputSection').scrollIntoView({ behavior: 'smooth' });
	}

	function displaySQL() {
	const output = document.getElementById('sqlOutput');
	let formatted = generatedSQL;

	// Syntax highlighting
	formatted = formatted.replace(/^--.*$/gm, '<span class="sql-comment">$&</span>');
	formatted = formatted.replace(/\b(SELECT\|FROM\|WHERE\|UPDATE\|SET\|IN\|AS\|CREATE\|TABLE\|DELETE\|INSERT\|INTO\|VALUES\|START\|TRANSACTION\|COMMIT\|ROLLBACK\|COUNT)\b/gi,
	'<span class="sql-keyword">$&</span>');
	formatted = formatted.replace(/'[^']*'/g, '<span class="sql-string">$&</span>');

	output.innerHTML = formatted;
	}

	function escapeSql(str) {
	return str.replace(/'/g, "''");
	}

	function escapeHtml(str) {
	const div = document.createElement('div');
	div.textContent = str;
	return div.innerHTML;
	}

	function copySQL() {
	navigator.clipboard.writeText(generatedSQL).then(() => {
	const button = event.target;
	const originalText = button.textContent;
	button.textContent = '✓ Copied!';
	button.style.background = '#28a745';
	setTimeout(() => {
	button.textContent = originalText;
	button.style.background = '#007bff';
	}, 2000);
	});
	}

	function downloadSQL() {
	const timestamp = new Date().toISOString().replace(/[:\-T]/g, '').substring(0, 14);
	const filename = `magento_sku_update_${timestamp}.sql`;

	const blob = new Blob([generatedSQL], { type: 'text/plain' });
	const url = window.URL.createObjectURL(blob);
	const a = document.createElement('a');
	a.href = url;
	a.download = filename;
	document.body.appendChild(a);
	a.click();
	document.body.removeChild(a);
	window.URL.revokeObjectURL(url);
	}
	</script>
	</body>
	</html>