Migrate users and addresses from Prestashop

[globule]

Add 2 columns in address table
Create a index table for the users ID (delete it after migration)

###### DOCUMENTATION
##
## The 2 DB must be on the same server
##
## SOURCE ---------------------
## prestashop dbname : prestadb
## table prefix : ps_

## DESTINATION ----------------
## sylius dbname : syliusdb
## table prefix : sls_

## replace all "dbname.prefix..." with your own for both SOURCE and DESTINATION
##
## ( Run in less than 10s on my VM )
##
###### END OF DOCUMENTATION

###### TESTING PART
###
SET FOREIGN_KEY_CHECKS=0;
### Data cleaning from previous tests
### Must be removed for production use
###
### TAKE CARE OF THE IDs IN YOUR TABLES !!!
###
DELETE FROM syliusdb.sls_customer WHERE id >26;
DELETE FROM syliusdb.sls_address WHERE customer_id >26;
DELETE FROM syliusdb.sls_shop_user WHERE customer_id >26;
ALTER TABLE syliusdb.sls_customer auto_increment = 27;
ALTER TABLE syliusdb.sls_address auto_increment = 27;
ALTER TABLE syliusdb.sls_shop_user auto_increment = 27;
ALTER TABLE syliusdb.sls_address ADD COLUMN IF NOT EXISTS street_second VARCHAR(255);
ALTER TABLE syliusdb.sls_address ADD COLUMN IF NOT EXISTS delivery_information VARCHAR(255);
###
SET FOREIGN_KEY_CHECKS=1;
###### END OF TESTING PART

### Temporary index table creation ( no DROP is done at this time in this script )
CREATE TABLE IF NOT EXISTS presta_users (
    id_presta INT ( 11 ) UNSIGNED NOT NULL,
    email VARCHAR ( 255 ) not NULL,
    id_sylius INT ( 11 ) UNSIGNED,
    PRIMARY KEY (`id_presta`),
    UNIQUE INDEX `id` (`id_presta`,`id_sylius`),
    UNIQUE INDEX `presta` (`id_presta`,`email`),
    UNIQUE INDEX `sylius` (`id_sylius`,`email`)
    )
    COLLATE='utf8mb4_general_ci'
    ENGINE=InnoDB
;

### Empty emporary index table (not created if it already exists)
TRUNCATE syliusdb.presta_users;

###### MAIN SCRIPT PART
###
### Temporary index table population from Prestashop customers
INSERT INTO syliusdb.presta_users
SELECT
    prestadb.ps_customer.id_customer,
    prestadb.ps_customer.email,
    NULL
FROM
    prestadb.ps_customer
WHERE
        prestadb.ps_customer.active = 1
  AND prestadb.ps_customer.deleted = 0
  AND prestadb.ps_customer.is_guest = 0
  AND prestadb.ps_customer.email NOT IN ( SELECT email FROM syliusdb.sls_customer )
ORDER BY ps_customer.id_customer
;

### Create Sylius customers from Prestashop customers
	INSERT INTO syliusdb.sls_customer
SELECT DISTINCT
    NULL AS	id,
    1 AS customer_group_id,
    NULL AS default_address_id,
    ps_customer.email,
    ps_customer.email AS email_canonical,
    ps_customer.firstname AS first_name,
    ps_customer.lastname AS last_name,
    ps_customer.birthday,
    CASE ps_customer.id_gender
        WHEN 1 THEN "m"
        WHEN 2 THEN "f"
        ELSE "u"
        END AS id_gender,
    ps_customer.date_add AS created_at,
    ps_customer.date_upd AS updated_at,
    NULL AS phone_number,
    ps_customer.optin AS subscribe_to_newsletter
FROM
    prestadb.ps_customer
        LEFT JOIN prestadb.ps_gender ON prestadb.ps_customer.id_gender = prestadb.ps_gender.id_gender
        LEFT JOIN prestadb.ps_address ON prestadb.ps_customer.id_customer = prestadb.ps_address.id_customer
WHERE
        ps_customer.active = 1
  AND prestadb.ps_customer.deleted = 0
  AND prestadb.ps_customer.is_guest = 0
  AND prestadb.ps_customer.email NOT IN (SELECT email FROM syliusdb.sls_customer)
ORDER BY prestadb.ps_customer.id_customer
;

### Populate temporary index table with Sylius customer ID, based on customer emails
UPDATE syliusdb.presta_users
    INNER JOIN sls_customer ON sls_customer.email = presta_users.email
    AND presta_users.id_sylius IS NULL
    SET presta_users.id_sylius = sls_customer.id;

### Create Sylius adress records from Prestashop adresses via the temp index table to get Sylius Customer ID
	INSERT INTO syliusdb.sls_address
SELECT
    NULL AS id,
    syliusdb.presta_users.id_sylius as customer_id,
    prestadb.ps_address.firstname as first_name,
    prestadb.ps_address.lastname as last_name,
    case prestadb.ps_address.phone_mobile
        when "" then prestadb.ps_address.phone
        ELSE prestadb.ps_address.phone_mobile
        END as phone_number,
    prestadb.ps_address.address1 as street,
    prestadb.ps_address.address2 as street_second,
    prestadb.ps_address.company,
    prestadb.ps_address.city,
    CONCAT("'",prestadb.ps_address.postcode, "'") as postcode,
    prestadb.ps_address.date_add as created_at,
    prestadb.ps_address.date_upd as updated_at,
    prestadb.ps_country.iso_code as country_code,
    NULL AS province_code,
    NULL AS province_name,
    prestadb.ps_address.other as delivery_information
FROM
    prestadb.ps_address
        INNER JOIN
    prestadb.ps_country
    ON
            prestadb.ps_address.id_country = prestadb.ps_country.id_country
        INNER JOIN
    syliusdb.presta_users
    ON
            prestadb.ps_address.id_customer= syliusdb.presta_users.id_presta
WHERE
    syliusdb.presta_users.id_sylius IS NOT NULL;

### Create Sylius shop users with Prestashop customer, via temp index table.
INSERT INTO syliusdb.sls_shop_user
SELECT
    NULL AS id,
    syliusdb.presta_users.id_sylius as customer_id,
    syliusdb.presta_users.email as username,
    syliusdb.presta_users.email as username_canonical,
    1 as enabled,
    prestadb.ps_customer.secure_key as salt,
    NULL AS password,
    NULL AS last_login,
    NULL AS password_reset_token,
    NULL AS password_requested_at,
    NULL AS email_verification_token,
    NULL AS verified_at,
    0 as locked,
    NULL AS expires_at,
    NULL AS crendentials_expire_at,
    'a:1:{i:0;s:9:"ROLE_USER";}' as roles,
    NULL AS email,
    NULL AS email_canonical,
    prestadb.ps_customer.date_add as created_at,
    NOW() as updated_at,
    NULL AS encoder_name
FROM
    syliusdb.presta_users
        INNER JOIN
    prestadb.ps_customer
    ON
            syliusdb.presta_users.id_presta = prestadb.ps_customer.id_customer;

###### MAIN SCRIPT PART END