"""Post-migration for v18.0.2.4.0.

Backfill the new V9-parity fields on existing ``job.partner.commission``
rows. They were created before the schema added ``state`` /
``transaction_type`` / service snapshots; legacy rows had no
pending/canceled state so every one is effectively final.

Don't use ``COALESCE`` on ``state`` — Odoo backfills the new column
with the field default ``'pending'`` on ``ALTER TABLE``, so ``state``
is never NULL by the time this migration runs. We instead flip rows
that have no ``completed_date`` (a marker reserved for the new code
path) to ``completed``.
"""

import logging

_logger = logging.getLogger(__name__)


def migrate(cr, version):
    cr.execute(
        """
        UPDATE job_partner_commission jpc
           SET transaction_type = 'commission',
               state            = 'completed',
               completed_date   = jpc.create_date,
               service_price    = COALESCE(jpc.service_price, j.base_amount),
               service_title    = COALESCE(
                   jpc.service_title,
                   j.name->>'en_US',
                   j.name->>'de_DE',
                   (SELECT value FROM jsonb_each_text(j.name) LIMIT 1)
               ),
               referrer_source  = COALESCE(jpc.referrer_source, 'kj')
          FROM hr_job j
         WHERE jpc.job_id = j.id
           AND jpc.completed_date IS NULL
        """
    )
    _logger.info(
        "Backfilled %d existing job.partner.commission rows to "
        "state='completed' with service snapshots",
        cr.rowcount,
    )