exports.up = (pgm) => {
  pgm.sql(`
    -- Emails greet customers, and a single 'name' column only allows the formal
    -- whole name: "Hi Thom Lamb," rather than "Hi Thom,". Splitting it is what
    -- makes an informal greeting possible.
    --
    -- Both columns are nullable even though registration now requires them. The
    -- requirement is enforced in the route, where a missing field can produce a
    -- 400 naming it. Marking these NOT NULL would mean backfilling legacy rows
    -- with empty strings, which asserts that every customer has a name — and
    -- that is not true of anyone who registered while the field was optional.
    -- The table should record what is actually the case.
    ALTER TABLE customers ADD COLUMN IF NOT EXISTS first_name TEXT;
    ALTER TABLE customers ADD COLUMN IF NOT EXISTS last_name TEXT;

    -- The lossy part, and there is no version of this that is not.
    --
    -- Splitting on the first space is right for "Thom Lamb" and wrong for
    -- "Mary Jane Smith", who ends up with a last name of "Jane Smith". Names do
    -- not reliably divide into two parts at all. This was chosen over leaving
    -- the columns empty because there is currently no way for a customer to
    -- correct their own name — PUT /api/customers/me exists but nothing calls
    -- it — so empty would mean permanently unpersonalised for everyone who
    -- registered before this.
    --
    -- Treat backfilled values as a best guess rather than as data the customer
    -- gave you in this shape.
    UPDATE customers
    SET first_name = CASE
          WHEN position(' ' in btrim(name)) > 0 THEN split_part(btrim(name), ' ', 1)
          ELSE btrim(name)
        END,
        last_name = CASE
          WHEN position(' ' in btrim(name)) > 0
            THEN btrim(substring(btrim(name) from position(' ' in btrim(name)) + 1))
          ELSE NULL
        END
    WHERE name IS NOT NULL AND btrim(name) <> '';

    -- Dropped rather than kept alongside. Two columns describing the same fact
    -- drift, and the new pair is now the only place a name lives.
    ALTER TABLE customers DROP COLUMN IF EXISTS name;
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    ALTER TABLE customers ADD COLUMN IF NOT EXISTS name TEXT;

    -- Rejoins the parts. Not a perfect inverse of the split above — a name that
    -- was mangled on the way in stays mangled on the way out — but it restores
    -- a usable whole name rather than leaving the column empty.
    UPDATE customers
    SET name = btrim(concat_ws(' ', first_name, last_name))
    WHERE first_name IS NOT NULL OR last_name IS NOT NULL;

    UPDATE customers SET name = NULL WHERE name = '';

    ALTER TABLE customers DROP COLUMN IF EXISTS first_name;
    ALTER TABLE customers DROP COLUMN IF EXISTS last_name;
  `);
};