"""18.0.2.2.0 — landing_page coercion + dead-column drop.

1. landing_page legacy values
   The field is a Selection of /, /job-offers, /company-profiles, but
   the column is `character varying` at the SQL level, so historic
   inserts (raw SQL, data files, manual edits) can stash arbitrary
   strings — e.g. ``/jobs`` was valid in an earlier module version.
   Such rows render fine at runtime but **fail to save** in the
   backend form (ORM Selection validation rejects them). Map known
   aliases; normalise anything unmapped to the field default.

2. Dead column drop
   theme_custom_title / theme_no_results_text were stored but never
   read by any endpoint. The fields are removed from the model in
   this version; drop the orphaned columns so schema matches model.
"""


_LEGACY_MAP = {
    "/jobs": "/job-offers",
    "/job_offers": "/job-offers",
    "/company": "/company-profiles",
    "/companies": "/company-profiles",
    "/home": "/",
}


def migrate(cr, version):
    # --- landing_page: coerce legacy aliases to valid Selection values ---
    cr.execute(
        """
        SELECT id, landing_page
        FROM affiliate_widget_config
        WHERE landing_page IS NOT NULL
          AND landing_page NOT IN ('/', '/job-offers', '/company-profiles')
        """,
    )
    rows = cr.fetchall()
    for row_id, current in rows:
        new = _LEGACY_MAP.get(current, "/job-offers")
        cr.execute(
            "UPDATE affiliate_widget_config SET landing_page = %s WHERE id = %s",
            (new, row_id),
        )

    # --- Drop two stored-but-dead theme columns ---
    # theme_custom_title / theme_no_results_text were never read by any
    # endpoint. Removing the fields from the model leaves the columns
    # orphaned; drop them so the schema matches the model.
    for col in ("theme_custom_title", "theme_no_results_text"):
        cr.execute(
            "ALTER TABLE affiliate_widget_config DROP COLUMN IF EXISTS %s"
            % col,
        )