--liquibase formatted sql
--changeset lewis.stone:adding-is-all-in-qb-field
ALTER TABLE `invoices`
ADD COLUMN `is_all_in_quickbooks` BIT(1) NULL DEFAULT b'1' AFTER `dateTimeCreated`;

UPDATE invoices as i INNER JOIN accounts as a on i.id = a.invoice_id
SET i.is_all_in_quickbooks = 0
WHERE a.is_in_quickbooks = 0;

ALTER TABLE `invoices`
CHANGE COLUMN `is_all_in_quickbooks` `is_all_in_quickbooks` BIT(1) NOT NULL DEFAULT b'0';

--changeset lewis.stone:removing-root-definer-non-invoiced-report-view --runOnChange:true
DROP VIEW IF EXISTS not_in_qb_invoice_reports_view;
CREATE
    ALGORITHM = UNDEFINED
    SQL SECURITY DEFINER
VIEW `not_in_qb_invoice_reports_view` AS
    SELECT
        `o`.`id` AS `pw_number`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.quote.carrier_name')) AS `carrier_name`,
        `o`.`organization_id` AS `organization_id`,
        `o`.`dealer_number` AS `dealer_number`,
        `o`.`return_number` AS `return_number`,
        `org`.`external_id` AS `external_id`,
        `org`.`name` AS `org_name`,
        `o`.`pro_number` AS `pro_number`,
        `o`.`status` AS `status`,
        CAST(`i`.`is_all_in_quickbooks` AS UNSIGNED) AS `is_all_in_quickbooks`,
        `i`.`id` AS `invoice_id`,
        `i`.`arActual` AS `arActual`,
        `i`.`actualAP` AS `actualAP`,
        `o`.`chksum` AS `expectedAP`,
        `o`.`total` AS `expectedAR`,
        CAST(`i`.`deferred` AS UNSIGNED) AS `deferred`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.quote.carrier_code')) AS `carrier_code`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.origin.name')) AS `origin_name`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.origin.address_line_1')) AS `origin_address_line_1`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.origin.city')) AS `origin_city`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.origin.state_code')) AS `origin_state_code`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.origin.postal_code')) AS `origin_zip`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.destination.name')) AS `destination_name`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.destination.address_line_1')) AS `destination_address_line_1`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.destination.city')) AS `destination_city`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.destination.state_code')) AS `destination_state_code`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`,
                        '$.request.shipment.destination.postal_code')) AS `destination_zip`,
        JSON_UNQUOTE(JSON_EXTRACT(`orr`.`blob`, '$.request.quote.mode')) AS `service_type`,
        DATE_FORMAT(`o`.`order_date`, '%Y-%m-%d') AS `order_date`,
        DATE_FORMAT(`o`.`pickup_date`, '%Y-%m-%d') AS `pickup_date`,
        DATE_FORMAT(`o`.`estimated_delivery_date`,
                '%Y-%m-%d') AS `estimated_delivery_date`,
        DATE_FORMAT(`o`.`delivery_date`,
                '%Y-%m-%d') AS `delivery_date`
    FROM
        (((`orders` `o`
        LEFT JOIN `invoices` `i` ON ((`i`.`swNumber` = `o`.`id`)))
        JOIN `ordersrr` `orr` ON (((`o`.`id` = `orr`.`order_id`)
            AND (`orr`.`type` = 2))))
        JOIN `organization` `org` ON ((`o`.`organization_id` = `org`.`id`)))
