-- P5-4877 migrate order delivery address

BEGIN;

    INSERT INTO "order-fields" (name) VALUES ('delivery_address_id');

    -- temporary column with reference to the order
    ALTER TABLE addresses ADD COLUMN order_id INTEGER;

    -- copy delivery address
    INSERT INTO addresses (
        order_id,
        address1,
        address2,
        company_name,
        country_code,
        city,
        zipcode,
        first_name,
        last_name,
        state
    )
    SELECT
        orders.orderid,
        address1.value,
        address2.value,
        company.value,
        COALESCE(delivery_country.value, country.value),
        city.value,
        zipcode.value,
        firstname.value,
        lastname.value,
        state.value
    FROM "order-orders" orders
    LEFT JOIN "order-orders_fields" address1 ON address1.orderid = orders.orderid AND address1.fieldid = 181
    LEFT JOIN "order-orders_fields" address2 ON address2.orderid = orders.orderid AND address2.fieldid = 253
    LEFT JOIN "order-orders_fields" company ON company.orderid = orders.orderid AND company.fieldid = 251
    LEFT JOIN "order-orders_fields" delivery_country ON delivery_country.orderid = orders.orderid
        AND delivery_country.fieldid = 213 AND delivery_country.value != ''
    LEFT JOIN "order-orders_fields" country ON country.orderid = orders.orderid AND country.fieldid = 151
    LEFT JOIN "order-orders_fields" city ON city.orderid = orders.orderid AND city.fieldid = 183
    LEFT JOIN "order-orders_fields" zipcode ON zipcode.orderid = orders.orderid AND zipcode.fieldid = 182
    LEFT JOIN "order-orders_fields" firstname ON firstname.orderid = orders.orderid AND firstname.fieldid = 221
    LEFT JOIN "order-orders_fields" lastname ON lastname.orderid = orders.orderid AND lastname.fieldid = 222
    LEFT JOIN "order-orders_fields" state ON state.orderid = orders.orderid AND state.fieldid = 242;

    -- save delivery address id on the order
    INSERT INTO "order-orders_fields" (orderid, fieldid, value)
        SELECT order_id, 254, id FROM addresses WHERE order_id IS NOT NULL;

    -- drop temp column
    ALTER TABLE addresses DROP COLUMN order_id;

COMMIT;