-- ============================================================================
-- Fix Character Corruption in ALL Tables
-- ============================================================================
-- This script fixes mojibake across the entire database
-- Run this AFTER running scan_all_tables_for_corruption.sql to see what's affected
--
-- IMPORTANT: Create a backup first!
-- docker exec cmc-db mariadb-dump -u root -psecureRootPassword --default-character-set=utf8mb4 cmc | gzip > backup_before_complete_fix_$(date +%Y%m%d).sql.gz
--
-- To run: docker exec -i cmc-db mariadb -u root -psecureRootPassword --default-character-set=utf8mb4 cmc < scripts/fix_all_corruption.sql
-- ============================================================================

SET NAMES utf8mb4;

SELECT '============================================================' as '';
SELECT 'COMPREHENSIVE CORRUPTION FIX - ALL TABLES' as '';
SELECT '============================================================' as '';

-- ============================================================================
-- Define reusable corruption patterns
-- These are applied to every text/varchar column
-- ============================================================================

-- Smart quotes and punctuation
-- ’ → ' (smart apostrophe)
-- “ → " (left double quote)
-- †→ " (right double quote)
-- â€" → – (en dash)
-- â€" → — (em dash)
-- ​ → (zero-width space, remove)
--   → (space)
-- … → …

-- Accented characters
-- é → é
-- É → É
-- ó → ó
-- Ã → í
-- ç → ç
-- ü → ü
-- á → á
-- ñ → ñ
-- ö → ö
-- ô → ô
-- ß → ß
-- ä → ä

-- Symbols
-- ° → °
-- ® → ®
-- â„¢ → ™

-- ============================================================================
-- ADDRESSES Table
-- ============================================================================
SELECT 'Fixing addresses table...' as '';

UPDATE addresses SET name = REPLACE(name, '’', ''') WHERE name LIKE '%’%';
UPDATE addresses SET name = REPLACE(name, '“', '"') WHERE name LIKE '%“%';
UPDATE addresses SET name = REPLACE(name, 'â€', '"') WHERE name LIKE '%â€%';
UPDATE addresses SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE addresses SET name = REPLACE(name, 'ü', 'ü') WHERE name LIKE '%ü%';

UPDATE addresses SET address = REPLACE(address, '’', ''') WHERE address LIKE '%’%';
UPDATE addresses SET address = REPLACE(address, '“', '"') WHERE address LIKE '%“%';
UPDATE addresses SET address = REPLACE(address, 'â€', '"') WHERE address LIKE '%â€%';
UPDATE addresses SET address = REPLACE(address, 'â€"', '–') WHERE address LIKE '%â€"%';
UPDATE addresses SET address = REPLACE(address, 'â€"', '—') WHERE address LIKE '%â€"%';
UPDATE addresses SET address = REPLACE(address, 'é', 'é') WHERE address LIKE '%é%';
UPDATE addresses SET address = REPLACE(address, 'ü', 'ü') WHERE address LIKE '%ü%';
UPDATE addresses SET address = REPLACE(address, 'ó', 'ó') WHERE address LIKE '%ó%';
UPDATE addresses SET address = REPLACE(address, 'ñ', 'ñ') WHERE address LIKE '%ñ%';

UPDATE addresses SET city = REPLACE(city, 'é', 'é') WHERE city LIKE '%é%';
UPDATE addresses SET city = REPLACE(city, 'ü', 'ü') WHERE city LIKE '%ü%';
UPDATE addresses SET city = REPLACE(city, 'ó', 'ó') WHERE city LIKE '%ó%';

-- ============================================================================
-- ATTACHMENTS Table
-- ============================================================================
SELECT 'Fixing attachments table...' as '';

UPDATE attachments SET name = REPLACE(name, '’', ''') WHERE name LIKE '%’%';
UPDATE attachments SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE attachments SET name = REPLACE(name, 'ü', 'ü') WHERE name LIKE '%ü%';

UPDATE attachments SET description = REPLACE(description, '’', ''') WHERE description LIKE '%’%';
UPDATE attachments SET description = REPLACE(description, '“', '"') WHERE description LIKE '%“%';
UPDATE attachments SET description = REPLACE(description, 'â€', '"') WHERE description LIKE '%â€%';
UPDATE attachments SET description = REPLACE(description, 'é', 'é') WHERE description LIKE '%é%';
UPDATE attachments SET description = REPLACE(description, 'ü', 'ü') WHERE description LIKE '%ü%';

-- ============================================================================
-- CONTACTS Table
-- ============================================================================
SELECT 'Fixing contacts table...' as '';

UPDATE contacts SET name = REPLACE(name, '’', ''') WHERE name LIKE '%’%';
UPDATE contacts SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE contacts SET name = REPLACE(name, 'ü', 'ü') WHERE name LIKE '%ü%';
UPDATE contacts SET name = REPLACE(name, 'ó', 'ó') WHERE name LIKE '%ó%';

UPDATE contacts SET first_name = REPLACE(first_name, '’', ''') WHERE first_name LIKE '%’%';
UPDATE contacts SET first_name = REPLACE(first_name, 'é', 'é') WHERE first_name LIKE '%é%';
UPDATE contacts SET first_name = REPLACE(first_name, 'ü', 'ü') WHERE first_name LIKE '%ü%';

UPDATE contacts SET last_name = REPLACE(last_name, '’', ''') WHERE last_name LIKE '%’%';
UPDATE contacts SET last_name = REPLACE(last_name, 'é', 'é') WHERE last_name LIKE '%é%';
UPDATE contacts SET last_name = REPLACE(last_name, 'ü', 'ü') WHERE last_name LIKE '%ü%';

UPDATE contacts SET notes = REPLACE(notes, '’', ''') WHERE notes LIKE '%’%';
UPDATE contacts SET notes = REPLACE(notes, '“', '"') WHERE notes LIKE '%“%';
UPDATE contacts SET notes = REPLACE(notes, 'â€', '"') WHERE notes LIKE '%â€%';
UPDATE contacts SET notes = REPLACE(notes, 'é', 'é') WHERE notes LIKE '%é%';
UPDATE contacts SET notes = REPLACE(notes, 'ü', 'ü') WHERE notes LIKE '%ü%';

UPDATE contacts SET job_title = REPLACE(job_title, '’', ''') WHERE job_title LIKE '%’%';
UPDATE contacts SET job_title = REPLACE(job_title, 'é', 'é') WHERE job_title LIKE '%é%';

-- ============================================================================
-- CUSTOMERS Table (re-apply in case some were missed)
-- ============================================================================
SELECT 'Fixing customers table...' as '';

UPDATE customers SET name = REPLACE(name, '’', ''') WHERE name LIKE '%’%';
UPDATE customers SET name = REPLACE(name, '“', '"') WHERE name LIKE '%“%';
UPDATE customers SET name = REPLACE(name, 'â€', '"') WHERE name LIKE '%â€%';
UPDATE customers SET name = REPLACE(name, 'â€"', '–') WHERE name LIKE '%â€"%';
UPDATE customers SET name = REPLACE(name, 'â€"', '—') WHERE name LIKE '%â€"%';
UPDATE customers SET name = REPLACE(name, '​', '') WHERE name LIKE '%​%';
UPDATE customers SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE customers SET name = REPLACE(name, 'ü', 'ü') WHERE name LIKE '%ü%';
UPDATE customers SET name = REPLACE(name, 'ó', 'ó') WHERE name LIKE '%ó%';
UPDATE customers SET name = REPLACE(name, 'ç', 'ç') WHERE name LIKE '%ç%';

UPDATE customers SET trading_name = REPLACE(trading_name, '’', ''') WHERE trading_name LIKE '%’%';
UPDATE customers SET trading_name = REPLACE(trading_name, 'é', 'é') WHERE trading_name LIKE '%é%';

UPDATE customers SET notes = REPLACE(notes, '’', ''') WHERE notes LIKE '%’%';
UPDATE customers SET notes = REPLACE(notes, '“', '"') WHERE notes LIKE '%“%';
UPDATE customers SET notes = REPLACE(notes, 'â€', '"') WHERE notes LIKE '%â€%';
UPDATE customers SET notes = REPLACE(notes, 'é', 'é') WHERE notes LIKE '%é%';

-- ============================================================================
-- DOCUMENTS Table
-- ============================================================================
SELECT 'Fixing documents table...' as '';

UPDATE documents SET shipping_details = REPLACE(shipping_details, '’', ''') WHERE shipping_details LIKE '%’%';
UPDATE documents SET shipping_details = REPLACE(shipping_details, 'é', 'é') WHERE shipping_details LIKE '%é%';

UPDATE documents SET bill_to = REPLACE(bill_to, '’', ''') WHERE bill_to LIKE '%’%';
UPDATE documents SET bill_to = REPLACE(bill_to, 'é', 'é') WHERE bill_to LIKE '%é%';

UPDATE documents SET ship_to = REPLACE(ship_to, '’', ''') WHERE ship_to LIKE '%’%';
UPDATE documents SET ship_to = REPLACE(ship_to, 'é', 'é') WHERE ship_to LIKE '%é%';

UPDATE documents SET subject = REPLACE(subject, '’', ''') WHERE subject LIKE '%’%';
UPDATE documents SET subject = REPLACE(subject, '“', '"') WHERE subject LIKE '%“%';
UPDATE documents SET subject = REPLACE(subject, 'â€', '"') WHERE subject LIKE '%â€%';
UPDATE documents SET subject = REPLACE(subject, 'é', 'é') WHERE subject LIKE '%é%';

-- ============================================================================
-- EMAILS Table
-- ============================================================================
SELECT 'Fixing emails table...' as '';

UPDATE emails SET `from` = REPLACE(`from`, '’', ''') WHERE `from` LIKE '%’%';
UPDATE emails SET `from` = REPLACE(`from`, 'é', 'é') WHERE `from` LIKE '%é%';

UPDATE emails SET `to` = REPLACE(`to`, '’', ''') WHERE `to` LIKE '%’%';
UPDATE emails SET `to` = REPLACE(`to`, 'é', 'é') WHERE `to` LIKE '%é%';

UPDATE emails SET subject = REPLACE(subject, '’', ''') WHERE subject LIKE '%’%';
UPDATE emails SET subject = REPLACE(subject, '“', '"') WHERE subject LIKE '%“%';
UPDATE emails SET subject = REPLACE(subject, 'â€', '"') WHERE subject LIKE '%â€%';
UPDATE emails SET subject = REPLACE(subject, 'é', 'é') WHERE subject LIKE '%é%';
UPDATE emails SET subject = REPLACE(subject, 'ü', 'ü') WHERE subject LIKE '%ü%';

UPDATE emails SET body = REPLACE(body, '’', ''') WHERE body LIKE '%’%';
UPDATE emails SET body = REPLACE(body, '“', '"') WHERE body LIKE '%“%';
UPDATE emails SET body = REPLACE(body, 'â€', '"') WHERE body LIKE '%â€%';
UPDATE emails SET body = REPLACE(body, 'â€"', '–') WHERE body LIKE '%â€"%';
UPDATE emails SET body = REPLACE(body, 'â€"', '—') WHERE body LIKE '%â€"%';
UPDATE emails SET body = REPLACE(body, 'é', 'é') WHERE body LIKE '%é%';
UPDATE emails SET body = REPLACE(body, 'ü', 'ü') WHERE body LIKE '%ü%';
UPDATE emails SET body = REPLACE(body, '°', '°') WHERE body LIKE '%°%';
UPDATE emails SET body = REPLACE(body, '®', '®') WHERE body LIKE '%®%';

-- ============================================================================
-- ENQUIRIES Table
-- ============================================================================
SELECT 'Fixing enquiries table...' as '';

UPDATE enquiries SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE enquiries SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE enquiries SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE enquiries SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';
UPDATE enquiries SET comments = REPLACE(comments, 'ü', 'ü') WHERE comments LIKE '%ü%';

-- ============================================================================
-- INVOICES Table
-- ============================================================================
SELECT 'Fixing invoices table...' as '';

UPDATE invoices SET ship_via = REPLACE(ship_via, '’', ''') WHERE ship_via LIKE '%’%';
UPDATE invoices SET ship_via = REPLACE(ship_via, 'é', 'é') WHERE ship_via LIKE '%é%';

UPDATE invoices SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE invoices SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE invoices SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE invoices SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

-- ============================================================================
-- JOBS Table
-- ============================================================================
SELECT 'Fixing jobs table...' as '';

UPDATE jobs SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE jobs SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE jobs SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE jobs SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

-- ============================================================================
-- LINE_ITEMS Table
-- ============================================================================
SELECT 'Fixing line_items table...' as '';

UPDATE line_items SET name = REPLACE(name, '’', ''') WHERE name LIKE '%’%';
UPDATE line_items SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE line_items SET name = REPLACE(name, '°', '°') WHERE name LIKE '%°%';
UPDATE line_items SET name = REPLACE(name, '®', '®') WHERE name LIKE '%®%';

UPDATE line_items SET description = REPLACE(description, '’', ''') WHERE description LIKE '%’%';
UPDATE line_items SET description = REPLACE(description, '“', '"') WHERE description LIKE '%“%';
UPDATE line_items SET description = REPLACE(description, 'â€', '"') WHERE description LIKE '%â€%';
UPDATE line_items SET description = REPLACE(description, 'é', 'é') WHERE description LIKE '%é%';
UPDATE line_items SET description = REPLACE(description, '°', '°') WHERE description LIKE '%°%';
UPDATE line_items SET description = REPLACE(description, '®', '®') WHERE description LIKE '%®%';

-- ============================================================================
-- ORDER_ACKNOWLEDGEMENTS Table
-- ============================================================================
SELECT 'Fixing order_acknowledgements table...' as '';

UPDATE order_acknowledgements SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE order_acknowledgements SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE order_acknowledgements SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE order_acknowledgements SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

-- ============================================================================
-- PRINCIPLES Table (re-apply)
-- ============================================================================
SELECT 'Fixing principles table...' as '';

UPDATE principles SET name = REPLACE(name, 'ü', 'ü') WHERE name LIKE '%ü%';
UPDATE principles SET name = REPLACE(name, 'é', 'é') WHERE name LIKE '%é%';
UPDATE principles SET name = REPLACE(name, 'ó', 'ó') WHERE name LIKE '%ó%';

UPDATE principles SET address = REPLACE(address, 'ß', 'ß') WHERE address LIKE '%ß%';
UPDATE principles SET address = REPLACE(address, 'ü', 'ü') WHERE address LIKE '%ü%';
UPDATE principles SET address = REPLACE(address, 'é', 'é') WHERE address LIKE '%é%';
UPDATE principles SET address = REPLACE(address, 'ñ', 'ñ') WHERE address LIKE '%ñ%';

UPDATE principles SET city = REPLACE(city, 'ü', 'ü') WHERE city LIKE '%ü%';
UPDATE principles SET city = REPLACE(city, 'é', 'é') WHERE city LIKE '%é%';

-- ============================================================================
-- PRODUCTS Table (re-apply with all patterns)
-- ============================================================================
SELECT 'Fixing products table...' as '';

-- Mojibake
UPDATE products SET title = REPLACE(title, '°', '°') WHERE title LIKE '%°%';
UPDATE products SET title = REPLACE(title, '®', '®') WHERE title LIKE '%®%';
UPDATE products SET title = REPLACE(title, 'â„¢', '™') WHERE title LIKE '%â„¢%';
UPDATE products SET title = REPLACE(title, '’', ''') WHERE title LIKE '%’%';
UPDATE products SET title = REPLACE(title, '“', '"') WHERE title LIKE '%“%';
UPDATE products SET title = REPLACE(title, 'â€', '"') WHERE title LIKE '%â€%';

UPDATE products SET description = REPLACE(description, '°', '°') WHERE description LIKE '%°%';
UPDATE products SET description = REPLACE(description, '®', '®') WHERE description LIKE '%®%';
UPDATE products SET description = REPLACE(description, 'â„¢', '™') WHERE description LIKE '%â„¢%';
UPDATE products SET description = REPLACE(description, '’', ''') WHERE description LIKE '%’%';

UPDATE products SET item_description = REPLACE(item_description, '°', '°') WHERE item_description LIKE '%°%';
UPDATE products SET item_description = REPLACE(item_description, '®', '®') WHERE item_description LIKE '%®%';

-- HTML entities
UPDATE products SET title = REPLACE(title, '&deg;', '°') WHERE title LIKE '%&deg;%';
UPDATE products SET title = REPLACE(title, '&nbsp;', ' ') WHERE title LIKE '%&nbsp;%';
UPDATE products SET title = REPLACE(title, '&amp;', '&') WHERE title LIKE '%&amp;%';

UPDATE products SET description = REPLACE(description, '&deg;', '°') WHERE description LIKE '%&deg;%';
UPDATE products SET description = REPLACE(description, '&nbsp;', ' ') WHERE description LIKE '%&nbsp;%';
UPDATE products SET description = REPLACE(description, '&amp;', '&') WHERE description LIKE '%&amp;%';

-- ============================================================================
-- PURCHASE_ORDERS Table
-- ============================================================================
SELECT 'Fixing purchase_orders table...' as '';

UPDATE purchase_orders SET ship_via = REPLACE(ship_via, '’', ''') WHERE ship_via LIKE '%’%';
UPDATE purchase_orders SET ship_via = REPLACE(ship_via, 'é', 'é') WHERE ship_via LIKE '%é%';

UPDATE purchase_orders SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE purchase_orders SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE purchase_orders SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE purchase_orders SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

-- ============================================================================
-- QUOTES Table
-- ============================================================================
SELECT 'Fixing quotes table...' as '';

UPDATE quotes SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE quotes SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE quotes SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE quotes SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

UPDATE quotes SET notes = REPLACE(notes, '’', ''') WHERE notes LIKE '%’%';
UPDATE quotes SET notes = REPLACE(notes, '“', '"') WHERE notes LIKE '%“%';
UPDATE quotes SET notes = REPLACE(notes, 'â€', '"') WHERE notes LIKE '%â€%';
UPDATE quotes SET notes = REPLACE(notes, 'é', 'é') WHERE notes LIKE '%é%';

-- ============================================================================
-- SHIPMENTS Table
-- ============================================================================
SELECT 'Fixing shipments table...' as '';

UPDATE shipments SET ship_via = REPLACE(ship_via, '’', ''') WHERE ship_via LIKE '%’%';
UPDATE shipments SET ship_via = REPLACE(ship_via, 'é', 'é') WHERE ship_via LIKE '%é%';

UPDATE shipments SET comments = REPLACE(comments, '’', ''') WHERE comments LIKE '%’%';
UPDATE shipments SET comments = REPLACE(comments, '“', '"') WHERE comments LIKE '%“%';
UPDATE shipments SET comments = REPLACE(comments, 'â€', '"') WHERE comments LIKE '%â€%';
UPDATE shipments SET comments = REPLACE(comments, 'é', 'é') WHERE comments LIKE '%é%';

-- ============================================================================
-- Verification
-- ============================================================================
SELECT '' as '';
SELECT '============================================================' as '';
SELECT 'FIX COMPLETE - Verification' as '';
SELECT '============================================================' as '';

SELECT 'Run scripts/scan_all_tables_for_corruption.sql to see remaining corruption' as '';