-- ============================================================================
-- Migration v5: fix Bengali-text data corruption ("????" in the database)
--
-- ROOT CAUSE: A few columns (e.g. market_items.unit, mess_accounts.district)
-- were added later via plain "ALTER TABLE ... ADD COLUMN" without explicitly
-- setting a character set. On a live database whose tables were originally
-- created before this project always forced utf8mb4, those new columns
-- silently inherited an older, incompatible charset (unable to store Bengali
-- script), so MySQL replaced every Bengali character with a literal "?" at
-- INSERT time — this is real, permanent data loss for any row saved before
-- this fix, not just a display bug.
--
-- THIS SCRIPT converts every table to utf8mb4 so all FUTURE saves are safe.
-- Safe to run more than once. Run in phpMyAdmin > SQL tab.
-- ============================================================================

ALTER TABLE super_admins       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mess_accounts      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE bd_divisions       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE bd_districts       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE bd_thanas          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE otp_codes          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE rooms              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE members            CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE rent_payments      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE utility_bills      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE meal_entries       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE market_records     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE market_items       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE expenses           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE member_settlements CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE owner_accounts     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE owner_payments     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE activity_logs      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE extra_meals        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ad_settings        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ad_campaigns       CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ad_impressions     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ad_clicks          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- NOTE ON ALREADY-CORRUPTED ROWS: this script fixes the table structure so
-- everything saved FROM NOW ON is correct. It cannot recover Bengali text
-- that was already replaced with "?" before this fix — those original bytes
-- are gone. Any market/বাজার entries showing "????" for the unit (কেজি,
-- হালি, etc.) need to be deleted and re-entered after running this script.