#!/bin/bash
# ============================================================================
# Fix Corrupted Character Data - Safe Version
# ============================================================================
# This script fixes mojibake by generating SQL dynamically to avoid encoding issues
# ============================================================================

DB_USER="root"
DB_PASS="secureRootPassword"
DB_NAME="cmc"
CONTAINER="cmc-db"

# Function to run SQL
run_sql() {
    docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "$1"
}

echo "============================================================"
echo "Fixing Character Corruption - Safe Version"
echo "============================================================"
echo ""

# Set charset
run_sql "SET NAMES utf8mb4;"

echo "Fixing CUSTOMERS table..."
# Fix customers - specific known records
run_sql "UPDATE customers SET name = 'SOLUZÉ CIVIL ENGINEERS Trading Under SOLUZE PTY LTD' WHERE id = 253;"
run_sql "UPDATE customers SET name = 'Dr. Prem''s Molecules Private Limited (DPMolecules)' WHERE id = 1006;"
run_sql "UPDATE customers SET name = 'DEE ENTERPRISES (QLD) PTY LTD trading as \"MEKLEK\"' WHERE id = 1387;"
run_sql "UPDATE customers SET name = 'Guidera O''Connor Pty Ltd' WHERE id = 1608;"
run_sql "UPDATE customers SET name = 'Ingredion ANZ Pty Ltd' WHERE id = 2174;"
run_sql "UPDATE customers SET name = 'Vale Nouvelle-Calédonie S.A.S' WHERE id = 2215;"
run_sql "UPDATE customers SET name = 'Evaluación y Control Ambiental S.A.S.' WHERE id = 2375;"
run_sql "UPDATE customers SET name = 'Zontahevy Comércio e Serviços Offshore Ltda' WHERE id = 3143;"
run_sql "UPDATE customers SET name = 'Société des Mines de Syama' WHERE id = 3529;"
run_sql "UPDATE customers SET name = 'P.J. Berriman & Co PTY LTD' WHERE id = 3633;"
run_sql "UPDATE customers SET name = 'Rambøll Danmark' WHERE id = 4325;"
run_sql "UPDATE customers SET name = 'SONNIVA ENERGY MAKİNA İNŞAAT İTH. İHR. LTD. ŞTİ.' WHERE id = 4350;"
run_sql "UPDATE customers SET name = 'F.C.C. Fluides Conseils Calédonie SARL' WHERE id = 4669;"
run_sql "UPDATE customers SET name = 'DGPack Prostějov' WHERE id = 4743;"
run_sql "UPDATE customers SET name = 'Mccready Welding Pty Ltd Trading under the entity Mccready''s Welding Services' WHERE id = 4764;"
run_sql "UPDATE customers SET name = 'Oxiquímica, S.A.P.I. de C.V.' WHERE id = 4893;"

echo "Fixing PRINCIPLES table..."
run_sql "UPDATE principles SET address = 'Heinz-Fangman-Straße 18' WHERE id = 2;"
run_sql "UPDATE principles SET address = 'Bezručova 2901\n756 61 Rožnov pod Radhoštěm', city = 'Rožnov pod Radhoštěm' WHERE id = 9;"
run_sql "UPDATE principles SET name = 'IEP Technologies GmbH - BRILEX Gesellschaft für Explosionsschutz mbH' WHERE id = 13;"
run_sql "UPDATE principles SET address = 'Liebigstraße 2' WHERE id = 14;"
run_sql "UPDATE principles SET name = 'Nosia S.r.l.- Señalización Industrial' WHERE id = 58;"
run_sql "UPDATE principles SET address = 'Alte Emser Straße 32' WHERE id = 65;"

echo "Fixing ADDRESSES table..."
run_sql "UPDATE addresses SET address = 'Lvl 3, Building B, 7–11 Talavera Road' WHERE id = 19;"

echo "Fixing PRODUCTS table (specific records)..."
run_sql "UPDATE products SET title = 'C95SN189C DSTGL40/C-Digital temperature probe for P8xx1 Web Sensor. Range -30 to +80°C. With CINCH connector, 1m Cable' WHERE id = 30;"
run_sql "UPDATE products SET title = 'Mid-West Instrument Model 240 – SC – 02 – O(TT)' WHERE id = 38;"
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® II FIBC Static Earthing System, GRP Enclosure with 5m 2 Core Spiral Cable and FIBC Clamp' WHERE id = 67;"
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR Tester - ER2/CRT' WHERE id = 76;"
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® Installers Kit A for IIB areas only (Metal Enclosures Only)' WHERE id = 77;"
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR™ Tri-Mode Static Grounding System, Metal Enclosure, X90-IP Heavy Duty Clamp with 10m 2 Core Spiral Cable' WHERE id = 85;"
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR System Spares - X90-IP Heavy Duty 2 Core Clamp with 10m 2 Core Spiral Cable and Male Quick Connect' WHERE id = 86;"
run_sql "UPDATE products SET title = 'Newson Gale Bond-Rite® EZ, VESX90-IP Heavy Duty Clamp & 3m 2 Core Spiral Cable' WHERE id = 149;"

echo ""
echo "Fixing HTML entities in products..."
# These are safe ASCII characters, so no encoding issues
run_sql "UPDATE products SET title = REPLACE(title, '–', '–') WHERE title LIKE '%–%';"
run_sql "UPDATE products SET description = REPLACE(description, '–', '–') WHERE description LIKE '%–%';"
run_sql "UPDATE products SET title = REPLACE(title, '&', '&') WHERE title LIKE '%&%';"
run_sql "UPDATE products SET description = REPLACE(description, '&', '&') WHERE description LIKE '%&%';"
run_sql "UPDATE products SET title = REPLACE(title, ' ', ' ') WHERE title LIKE '% %';"
run_sql "UPDATE products SET description = REPLACE(description, ' ', ' ') WHERE description LIKE '% %';"
run_sql "UPDATE products SET title = REPLACE(title, '°', '°') WHERE title LIKE '%°%';"
run_sql "UPDATE products SET description = REPLACE(description, '°', '°') WHERE description LIKE '%°%';"
run_sql "UPDATE products SET title = REPLACE(title, '½', '½') WHERE title LIKE '%½%';"
run_sql "UPDATE products SET description = REPLACE(description, '½', '½') WHERE description LIKE '%½%';"
run_sql "UPDATE products SET title = REPLACE(title, '”', '\"') WHERE title LIKE '%”%';"
run_sql "UPDATE products SET description = REPLACE(description, '”', '\"') WHERE description LIKE '%”%';"
run_sql "UPDATE products SET title = REPLACE(title, '“', '\"') WHERE title LIKE '%“%';"
run_sql "UPDATE products SET description = REPLACE(description, '“', '\"') WHERE description LIKE '%“%';"

echo ""
echo "============================================================"
echo "Fix complete! Verification:"
echo "============================================================"

run_sql "SELECT 'Remaining corrupted customers:' as status, COUNT(*) as count FROM customers WHERE name REGEXP '[^ -~]';"
run_sql "SELECT 'Remaining corrupted principles:' as status, COUNT(*) as count FROM principles WHERE name REGEXP '[^ -~]' OR address REGEXP '[^ -~]';"
run_sql "SELECT 'Remaining corrupted products:' as status, COUNT(*) as count FROM products WHERE title REGEXP '[^ -~]' OR description REGEXP '[^ -~]';"

echo ""
echo "Done! Check specific records:"
echo "SELECT id, name FROM customers WHERE id IN (253, 1006, 1387);"
echo "SELECT id, name, city FROM principles WHERE id IN (2, 9, 13);"
echo "SELECT id, title FROM products WHERE id IN (30, 38, 67, 85);"