CREATE TABLE orders_products_losses (
    orders_products_id INT NOT NULL PRIMARY KEY,
    orders_products_losses_status INT NOT NULL,
    orders_products_losses_date_added TIMESTAMP NOT NULL,
    orders_products_losses_date_changed TIMESTAMP NULL,
    orders_products_losses_comment text NULL
) COMMENT='Потери товара на складе' ENGINE=InnoDB;

CREATE TABLE orders_products_losses_acts (
    orders_products_losses_acts_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    orders_products_losses_acts_date TIMESTAMP NOT NULL,
    orders_products_losses_acts_type INTEGER NOT NULL,
    customers_id INT NOT NULL,
    acl_locations_id INT NOT NULL
) COMMENT='Акты списания потерь' ENGINE=InnoDB;

CREATE TABLE orders_products_losses_to_acts (
    orders_products_losses_to_acts_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    orders_products_id INT NOT NULL,
    orders_products_losses_acts_id INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO
    export_data_1c_component(export_data_1c_component_name, table_name, key_field, comment)
VALUES
    ('orders_products_losses_acts', 'orders_products_losses_acts', 'orders_products_losses_acts_id', 'Акты списания потерь на склады');

# acl
DELETE FROM acl_settings WHERE acl_rules_id IN (
    SELECT acl_rules_id FROM acl_rules WHERE action = 'show-products-found'
);
DELETE FROM acl_rules WHERE controller = 'stock' AND action = 'show-products-found';

UPDATE acl_rules SET action = 'losses' WHERE controller = 'stock' AND action = 'show-lost-products';

INSERT INTO acl_rules(module, controller, action, acl_rules_fake) VALUES('call', 'stock', 'upload-photos-for-losses', 'N');
INSERT INTO acl_rules(module, controller, action, acl_rules_fake) VALUES('call', 'stock', 'add-product-to-losses', 'N');
INSERT INTO acl_rules(module, controller, action, acl_rules_fake) VALUES('call', 'stock', 'delete-product-from-losses', 'N');
INSERT INTO acl_rules(module, controller, action, acl_rules_fake) VALUES('call', 'stock', 'get-order-product', 'N');

INSERT INTO acl_settings(acl_roles_id, acl_rules_id, access_type)
SELECT
    acl_roles_id,
    (SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'upload-photos-for-losses'),
    access_type
FROM
    acl_settings
WHERE
    acl_rules_id IN (
        SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'losses'
    )
;

INSERT INTO acl_settings(acl_roles_id, acl_rules_id, access_type)
SELECT
    acl_roles_id,
    (SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'add-product-to-losses'),
    access_type
FROM
    acl_settings
WHERE
    acl_rules_id IN (
        SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'losses'
    )
;

INSERT INTO acl_settings(acl_roles_id, acl_rules_id, access_type)
SELECT
    acl_roles_id,
    (SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'delete-product-from-losses'),
    access_type
FROM
    acl_settings
WHERE
    acl_rules_id IN (
        SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'losses'
    )
;

INSERT INTO acl_settings(acl_roles_id, acl_rules_id, access_type)
SELECT
    acl_roles_id,
    (SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'get-order-product'),
    access_type
FROM
    acl_settings
WHERE
    acl_rules_id IN (
        SELECT acl_rules_id FROM acl_rules WHERE module = 'call' AND controller = 'stock' AND action = 'losses'
    )
;

UPDATE acl_rules SET action = 'set-product-lost-and-found' WHERE module = 'call' AND controller = 'order' AND action = 'set-product-lost-and-find';

# menu
DELETE FROM admin_menu WHERE url = '/call/stock/show-products-found';
UPDATE admin_menu SET url = '/call/stock/losses' WHERE url = '/call/stock/show-lost-products';


# move all
INSERT INTO orders_products_losses
    (orders_products_id, orders_products_losses_status, orders_products_losses_date_added)
SELECT
    op.id, op.is_lost, st.date_added
FROM
    orders_products_new op
LEFT JOIN (
    SELECT
        op.id orders_products_id,
        och.date_added,
        IF(LOCATE('Комментарий: ', och.comment) > 0, REGEXP_REPLACE(och.comment, '(.*Комментарий: ")(.+)"', '\\2'), '') comment
    FROM
        orders_products_new op
    JOIN
        products p ON op.products_id = p.products_id
    JOIN
        products_description pd ON op.products_id = pd.products_id
    LEFT JOIN
        products_attributes ps ON op.products_attributes_id = ps.products_attributes_id
    LEFT JOIN
        orders_comment_history och ON op.orders_id = och.object_id
    WHERE
        op.is_lost = 1
        AND och.comment LIKE CONCAT('Товар (', products_model, ') "', ps.products_name, '%отмечен как <span class="label label-warning">Потерянный</span>%')
) st ON st.orders_products_id = op.id
WHERE
    op.is_lost = 1;


INSERT INTO orders_products_losses
    (orders_products_id, orders_products_losses_status, orders_products_losses_date_added, orders_products_losses_date_changed, orders_products_losses_comment)
SELECT
    op.id, op.is_lost, IFNULL(st.date_added, op.op_date_added), IFNULL(st.date_added, op.op_date_added), st.comment
FROM
    orders_products_new op
LEFT JOIN (
    SELECT
        op.id orders_products_id,
        och.date_added,
        IF(LOCATE('Комментарий: ', och.comment) > 0, REGEXP_REPLACE(och.comment, '(.*Комментарий: ")(.+)"', '\\2'), '') comment
    FROM
        orders_products_new op
    JOIN
        products p ON op.products_id = p.products_id
    JOIN
        products_description pd ON op.products_id = pd.products_id
    LEFT JOIN
        products_attributes ps ON op.products_attributes_id = ps.products_attributes_id
    LEFT JOIN
        orders_comment_history och ON op.orders_id = och.object_id
    WHERE
        op.is_lost = 2
        AND och.comment LIKE CONCAT('Товар (', products_model, ') "', ps.products_name, '%снята отметка <span class="label label-warning">Потерянный</span>%')
) st ON st.orders_products_id = op.id
WHERE
    op.is_lost = 2;

ALTER TABLE orders_products_losses_acts ADD COLUMN orders_products_losses_acts_comment TEXT NOT NULL AFTER orders_products_losses_acts_type;

CREATE INDEX orders_products_id ON orders_products_losses_to_acts(orders_products_id);
CREATE INDEX orders_products_losses_acts_id ON orders_products_losses_to_acts(orders_products_losses_acts_id);