-- noinspection SqlNoDataSourceInspectionForFile
START TRANSACTION;

DELETE FROM wbsv2_from_napi.SAS_ADDRESS;
DELETE FROM wbsv2_from_napi.SAS_SDA;
DELETE FROM wbsv2_from_napi.SAS_SITE;
DELETE FROM wbsv2_from_napi.SAS_CUSTOMER;
DELETE FROM wbsv2_from_napi.SAS_PARTNER;
INSERT INTO wbsv2_from_napi.SAS_PARTNER(`id`, `name`, `key_selfcare`, `key_wbs`)  SELECT `id`, `name`, `key_selfcare`, `key_wbs` FROM FROM_NAPI_SAS_UNION.ise_partner WHERE 1;

INSERT INTO wbsv2_from_napi.SAS_CUSTOMER(`id`, `name`,`id_partner`, `key_wbs`, `key_selfcare`, `version_selfcare`)  SELECT `id`, `name`,`id_partner`, `key_wbs`, `key_selfcare` , `version_selfcare` FROM FROM_NAPI_SAS_UNION.ise_customer WHERE 1;


INSERT INTO wbsv2_from_napi.SAS_SITE(`id`, `name`, `id_customer`, `key_wbs`, `key_selfcare`, `version_selfcare`)
SELECT `id`, `name`, `id_customer` ,`key_wbs`, `key_selfcare`, version_selfcare FROM FROM_NAPI_SAS_UNION.ise_site WHERE id IN(SELECT s.id FROM FROM_NAPI_SAS_UNION.ise_site s INNER JOIN FROM_NAPI_SAS_UNION.ise_customer c ON s.id_customer = c.id);


INSERT INTO wbsv2_from_napi.SAS_SDA(`id`, `id_site`, `long_number`, `short_number`,  `key_selfcare`,`key_wbs`, `version_selfcare`)
SELECT
    `id`, `id_site`, `long_number`, `short_number`,  `key_selfcare`,`key_wbs`, `version_selfcare`
FROM
    FROM_NAPI_SAS_UNION.ise_sda
where version_selfcare ='V3';


INSERT INTO wbsv2_from_napi.SAS_SDA(`id`, `id_site`, `long_number`, `short_number`,  `key_selfcare`,`key_wbs`, `version_selfcare`)
SELECT
    `id`, `id_site`, `long_number`, `short_number`,  `key_selfcare`,`key_wbs`, `version_selfcare`
FROM
    FROM_NAPI_SAS_UNION.ise_sda
where version_selfcare ='V2'
and id not in (select id from wbsv2_from_napi.SAS_SDA);



INSERT INTO wbsv2_from_napi.SAS_ADDRESS(`id`, `id_partner`, `id_customer`, `id_site`, `siret`, `postal_code`, `city`, `country`,`street`, `key_selfcare`,`key_wbs`)
SELECT `id`, `partner_id`, `customer_id`, `site_id`, `siret`, `postal_code`, `city`, `country`,`street`, `key_selfcare`, `key_wbs` FROM FROM_NAPI_SAS_UNION.ise_adress WHERE 1;



INSERT INTO wbsv2_backend.`billing_information`(`site_id`,postal_code,siret,city ,country,street)
SELECT site.id,address.postal_code, address.siret, address.city, address.country, address.street
FROM
    wbsv2_backend.v_NAPI_site site
        LEFT JOIN FROM_NAPI_SAS_UNION.ise_adress address on site.id = address.site_id
    LEFT JOIN  wbsv2_backend.billing_information bi
    ON site.id=  bi.site_id
WHERE bi.site_id IS NULL;



    INSERT INTO wbsv2_backend.`billing_information`(`customer_id`,postal_code,siret,city ,country,street)
SELECT customer.id ,address.postal_code, address.siret, address.city, address.country, address.street
FROM
    wbsv2_backend.v_NAPI_customer customer
        LEFT JOIN FROM_NAPI_SAS_UNION.ise_adress address on customer.id = address.customer_id
        LEFT JOIN
    wbsv2_backend.billing_information bi
    ON customer.id=  bi.customer_id
WHERE bi.customer_id IS NULL;


UPDATE
    wbsv2_backend.billing_information bi,
    wbsv2_backend.v_NAPI_customer customer
set bi.social_name = customer.name
WHERE customer.id=  bi.customer_id and bi.social_name IS NULL;

UPDATE
    wbsv2_backend.billing_information bi,
    wbsv2_backend.v_NAPI_site site
set bi.social_name = site.name
WHERE site.id=  bi.site_id and bi.social_name IS NULL;

COMMIT;
