Legacy membership migration query

query.sql

SELECT
    clients.id AS client_id,
    clients.name AS client_name,
    DATE(MAX(orders.time_stamp)) AS last_order,
    clients.program AS program_id,
    programs.name AS program_name,
    membership_invoice.id AS last_invoice_id,
    membership_invoice.total AS last_invoice_amount,
    membership_invoice.payment_method AS last_invoice_payment_method,
    membership_invoice.notes AS last_invoice_notes,
    IF(membership_invoice.issued IS NULL, DATE(clients.time_stamp), membership_invoice.issued) AS signup_date,
    IFNULL(user_priority1.id, user_priority2.id) AS 'user_id',
    IFNULL(CONCAT(user_priority1.fname, ' ', user_priority1.lname), CONCAT(user_priority2.fname, ' ', user_priority2.lname)) AS 'contact_name',
    IFNULL(user_priority1.email, user_priority2.email) AS 'email',
    IFNULL(user_priority1.position_name, user_priority2.position_name) AS 'position'
FROM orders
    JOIN clients ON orders.client = clients.id
    LEFT JOIN programs ON clients.program = programs.id
    LEFT JOIN memberships ON clients.id = memberships.client
    LEFT JOIN
        (
            SELECT
                invoices.*,
                group_concat(
                    DISTINCT
                    IF(
                        payments.type = 'credit_memo',
                        'Credit Memo',
                        IF(
                            payment_methods.id IS NOT NULL,
                            CONCAT(payment_methods.brand, ' *', LPAD(payment_methods.last4, 4, '0')),
                            COALESCE(
                                IF(payments.credit_card != '', payments.credit_card, NULL),
                                IF(payments.check_no != '', CONCAT('Check #', payments.check_no), NULL),
                                'Credit Card'
                            )
                        )
                    )
                    SEPARATOR ' + '
                ) AS payment_method
            FROM invoices
            JOIN invoice_items ON invoices.id = invoice_items.invoice
            JOIN payment_application ON payment_application.invoice = invoices.id
            LEFT JOIN payments on payments.id = payment_application.payment
            LEFT JOIN payment_methods ON payment_methods.id = payments.payment_method
            WHERE invoices.total > 0 AND (
                invoice_items.description LIKE ('%corporate%')
                OR invoice_items.description LIKE ('%membership%')
                OR invoice_items.description LIKE ('% annual %')
            )
            GROUP BY invoices.id
            ORDER BY invoices.id DESC
        ) AS membership_invoice ON clients.id = membership_invoice.client
    LEFT JOIN
        (SELECT users.fname, users.lname, users.email, users.id, client_positions.`name` AS position_name, contacts.client
        FROM
            users
            LEFT JOIN contacts ON users.contact = contacts.id
            LEFT JOIN employee_positions ON contacts.id = employee_positions.contact
            LEFT JOIN client_positions ON employee_positions.position = client_positions.id
        WHERE
            users.active = 1
            AND contacts.is_active = 1
            AND contacts.do_not_contact = 0
            AND users.email NOT LIKE ('%@toyoursuccess.com')
            AND client_positions.`name` = 'Primary'
        ORDER BY contacts.id DESC) AS user_priority1 ON clients.id = user_priority1.client
    LEFT JOIN
        (SELECT users.fname, users.lname, users.email, users.id, client_positions.`name` AS position_name, contacts.client
        FROM
            users
            LEFT JOIN contacts ON users.contact = contacts.id
            LEFT JOIN employee_positions ON contacts.id = employee_positions.contact
            LEFT JOIN client_positions ON employee_positions.position = client_positions.id
        WHERE
            users.active = 1
            AND contacts.is_active = 1
            AND contacts.do_not_contact = 0
            AND users.email NOT LIKE ('%@toyoursuccess.com')
            AND client_positions.`name` IN('Owner', 'President', 'CEO', 'VP', 'Vice President', 'GM', 'General Manager', 'COO', 'Operations Manager')
        ORDER BY contacts.id DESC) AS user_priority2 ON clients.id = user_priority2.client
WHERE
    orders.status != 'cancelled'
    AND orders.time_stamp + INTERVAL 1 YEAR >= CURRENT_DATE()
    AND clients.type = 'client'
    AND clients.is_active = 1
    AND clients.do_not_contact = 0
    AND clients.program IN(9, 15, 16)
    AND memberships.id IS NULL
GROUP BY orders.client