-- ============================================================
-- AGG-UAE ERP — Migration 52 — Phase M7-p4-h3
--   Collation normalization on tables/columns added by 46/50/51
-- ============================================================
--
-- Symptom: web ERP /erp/modules/manufacturing/auto-schedule/ throws
--   mysqli_sql_exception: Illegal mix of collations
--   (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)
--   for operation '='
--
-- Root cause: migrations 46 (priority_bracket on JOBFORM), 50
-- (JOBFORM_SCHEDULE_PIN + JOBFORM.is_held/held_by columns), and
-- 51 (DAILY_ATTENDANCE + JOBFORM.slack_days) were authored without
-- explicit COLLATE clauses on their CHAR/VARCHAR/ENUM columns and
-- new tables. On installations where the server default collation
-- is utf8mb4_unicode_ci (modern MariaDB/MySQL default), those new
-- columns/tables landed as unicode_ci while the legacy AGG-UAE
-- JOBFORM family (and CRTPARTS, CRTSUPPLIER, etc.) all use
-- utf8mb4_general_ci. Any column-vs-column compare that joins one
-- of those new strings against a legacy string throws the
-- "Illegal mix" error — surfaces as page-wide 500 in the rollup
-- query that COALESCEs priority_bracket against literals.
--
-- Fix: idempotently convert the new tables to utf8mb4_general_ci +
-- modify each individual string column on JOBFORM to match. ALTER
-- is silent-no-op when collation already matches, so re-running is
-- safe. JOBFORM is slim — no innodb_strict_mode dance needed for
-- the per-column modifies.
--
-- Depends on: 46 + 50 + 51 applied.

-- ─────────────────────────────────────────────────────────────
-- (1) New tables from migrations 50 + 51
-- ─────────────────────────────────────────────────────────────

-- JOBFORM_SCHEDULE_PIN (migration 50) — convert all columns +
-- table default to utf8mb4_general_ci. CONVERT TO CHARACTER SET
-- with explicit COLLATE rewrites every textual column in one shot.
ALTER TABLE JOBFORM_SCHEDULE_PIN
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- DAILY_ATTENDANCE (migration 51) — same treatment.
ALTER TABLE DAILY_ATTENDANCE
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- ─────────────────────────────────────────────────────────────
-- (2) New columns on the legacy JOBFORM table (migrations 46/50/51)
-- ─────────────────────────────────────────────────────────────
--
-- These columns can't be fixed with table-wide CONVERT (that would
-- rewrite every legacy varchar/text column too, which is risky on
-- this 80-col table). Modify each new column individually so they
-- match JOBFORM's general_ci collation.

ALTER TABLE JOBFORM
    MODIFY COLUMN priority_bracket
        ENUM('URGENT','HIGH','MEDIUM','LOW','BACKLOG')
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
        NOT NULL DEFAULT 'MEDIUM';

ALTER TABLE JOBFORM
    MODIFY COLUMN held_by
        VARCHAR(100)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
        NULL DEFAULT NULL;

ALTER TABLE JOBFORM
    MODIFY COLUMN last_forecast_check_by
        VARCHAR(100)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
        NULL DEFAULT NULL;

-- slack_days + slack_computed_at + is_held + held_at + last_forecast_check_at
-- are INT/DATETIME — no collation. Skip.