cmc-sales/scripts/run_corruption_fix.sh

#!/bin/bash
# ============================================================================
# Safe Corruption Fix Execution Script
# ============================================================================
# This script:
# 1. Creates a backup
# 2. Shows current corruption status
# 3. Prompts for confirmation
# 4. Applies fixes
# 5. Shows before/after comparison
# ============================================================================

set -e  # Exit on error

DB_USER="root"
DB_PASS="secureRootPassword"
DB_NAME="cmc"
CONTAINER="cmc-db"
BACKUP_DIR="./backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

echo -e "${BLUE}============================================================${NC}"
echo -e "${BLUE}CMC Database Corruption Fix Script${NC}"
echo -e "${BLUE}============================================================${NC}"
echo ""

# Create backups directory
mkdir -p "$BACKUP_DIR"

# Step 1: Create backup
echo -e "${YELLOW}Step 1: Creating database backup...${NC}"
docker exec $CONTAINER mariadb-dump \
    -u $DB_USER \
    -p$DB_PASS \
    --default-character-set=utf8mb4 \
    --single-transaction \
    $DB_NAME | gzip > "$BACKUP_DIR/backup_before_corruption_fix_$TIMESTAMP.sql.gz"

BACKUP_SIZE=$(du -h "$BACKUP_DIR/backup_before_corruption_fix_$TIMESTAMP.sql.gz" | cut -f1)
echo -e "${GREEN}✓ Backup created: backup_before_corruption_fix_$TIMESTAMP.sql.gz (${BACKUP_SIZE})${NC}"
echo ""

# Step 2: Show current corruption status
echo -e "${YELLOW}Step 2: Current corruption status${NC}"
echo "-----------------------------------"
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "
SELECT
    'principles' as table_name,
    COUNT(*) as corrupted_records
FROM principles
WHERE name REGEXP '[^ -~]'
   OR address REGEXP '[^ -~]'
   OR city REGEXP '[^ -~]'
UNION ALL
SELECT 'customers', COUNT(*)
FROM customers
WHERE name REGEXP '[^ -~]'
UNION ALL
SELECT 'addresses', COUNT(*)
FROM addresses
WHERE address REGEXP '[^ -~]'
   OR city REGEXP '[^ -~]'
UNION ALL
SELECT 'products', COUNT(*)
FROM products
WHERE title REGEXP '[^ -~]'
   OR description REGEXP '[^ -~]'
   OR item_description REGEXP '[^ -~]';
"
echo ""

# Step 3: Show sample corrupted data
echo -e "${YELLOW}Step 3: Sample corrupted data (BEFORE fix)${NC}"
echo "-------------------------------------------"
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "
SELECT 'CUSTOMERS' as table_name, id, name FROM customers WHERE id IN (253, 1006, 1387, 1608)
UNION ALL
SELECT 'PRINCIPLES', id, name FROM principles WHERE id IN (2, 9, 13, 14)
UNION ALL
SELECT 'PRODUCTS', id, title FROM products WHERE id IN (30, 38, 67);
"
echo ""

# Step 4: Prompt for confirmation
echo -e "${RED}IMPORTANT:${NC}"
echo "- Backup created at: $BACKUP_DIR/backup_before_corruption_fix_$TIMESTAMP.sql.gz"
echo "- This will modify data in tables: principles, customers, addresses, products"
echo "- Changes fix mojibake like: ’ → ', é → é, etc."
echo ""
read -p "Do you want to proceed with the fix? (yes/no): " CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo -e "${RED}Fix cancelled by user${NC}"
    exit 0
fi

# Step 5: Apply fixes
echo ""
echo -e "${YELLOW}Step 5: Applying corruption fixes...${NC}"
docker exec -i $CONTAINER mariadb \
    -u $DB_USER \
    -p$DB_PASS \
    --default-character-set=utf8mb4 \
    $DB_NAME < scripts/fix_corrupted_data.sql

echo -e "${GREEN}✓ Fix script executed${NC}"
echo ""

# Step 6: Show results
echo -e "${YELLOW}Step 6: Results (AFTER fix)${NC}"
echo "----------------------------"
echo ""
echo "Sample fixed data:"
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "
SELECT 'CUSTOMERS' as table_name, id, name FROM customers WHERE id IN (253, 1006, 1387, 1608)
UNION ALL
SELECT 'PRINCIPLES', id, name FROM principles WHERE id IN (2, 9, 13, 14)
UNION ALL
SELECT 'PRODUCTS', id, title FROM products WHERE id IN (30, 38, 67);
"
echo ""

echo "Remaining corruption counts:"
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "
SELECT
    'principles' as table_name,
    COUNT(*) as corrupted_records
FROM principles
WHERE name REGEXP '[^ -~]'
   OR address REGEXP '[^ -~]'
   OR city REGEXP '[^ -~]'
UNION ALL
SELECT 'customers', COUNT(*)
FROM customers
WHERE name REGEXP '[^ -~]'
UNION ALL
SELECT 'addresses', COUNT(*)
FROM addresses
WHERE address REGEXP '[^ -~]'
   OR city REGEXP '[^ -~]'
UNION ALL
SELECT 'products', COUNT(*)
FROM products
WHERE title REGEXP '[^ -~]'
   OR description REGEXP '[^ -~]'
   OR item_description REGEXP '[^ -~]';
"
echo ""

# Step 7: Test new insert
echo -e "${YELLOW}Step 7: Testing new inserts with special characters...${NC}"
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "
START TRANSACTION;
INSERT INTO principles (name, code, address, city, state, postcode, country_id, currency_id, short_name, url)
VALUES ('Test Müller & Söhne GmbH', 999999, 'Straße 123', 'München', 'Bayern', '80331', 1, 1, 'TEST', 'http://test.com');
SELECT name, address, city FROM principles WHERE code = 999999;
ROLLBACK;
"
echo -e "${GREEN}✓ New inserts working correctly${NC}"
echo ""

# Final summary
echo -e "${BLUE}============================================================${NC}"
echo -e "${GREEN}FIX COMPLETED SUCCESSFULLY!${NC}"
echo -e "${BLUE}============================================================${NC}"
echo ""
echo "Summary:"
echo "- Backup location: $BACKUP_DIR/backup_before_corruption_fix_$TIMESTAMP.sql.gz"
echo "- Fixed common patterns: smart quotes, accented characters, dashes"
echo "- Fixed specific records: ~30+ known corrupted records"
echo ""
echo "Next steps:"
echo "1. Review the fixed data in the application"
echo "2. Run: bash scripts/report_remaining_corruption.sh to see what's left"
echo "3. Manually fix any remaining corrupted records"
echo ""
echo "To rollback if needed:"
echo "  gunzip < $BACKUP_DIR/backup_before_corruption_fix_$TIMESTAMP.sql.gz | \\"
echo "  docker exec -i $CONTAINER mariadb -u $DB_USER -p$DB_PASS $DB_NAME"
echo ""