# noinspection SpellCheckingInspectionForFile



DELETE FROM storage WHERE legacyId IS NOT NULL;

SET @location_prefixes = 'SZTE Y|SZTE X|SZTE|SZOD|POMAZ X|POMAZ 2|POMAZ|GYOR|DEB|BTKT|BP VIH|BP VAR|BP UJL|BP TIS|BP PET|BP KLV|BP DOH';

INSERT INTO storage (
    legacyId,
    legacySlug,
    legacyPicture,
    createdAt,
    updatedAt,
    locationId,
    storageTypeId,
    name,
    price1M,
    price3M,
    price6M,
    price12M
)
SELECT
    n.nid AS legacyId,
    SUBSTRING_INDEX( ua.alias, '/', -1 ) AS legacySlug,
    IF( COUNT( fm.fid ) = 0, NULL, JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', fm.fid,
            'name', fm.filename,
            'uri', fm.uri,
            'mime', fm.filemime,
            'size', fm.filesize,
            'timestamp', fm.timestamp,
            'alt', NULL,
            'width', NULL,
            'height', NULL
        )
    ) ) AS legacyPicture,
    FROM_UNIXTIME( n.created ) AS createdAt,
    FROM_UNIXTIME( n.changed ) AS updatedAt,
    CASE
        WHEN n.title LIKE 'SZTE Y%' THEN 7
        WHEN n.title LIKE 'SZTE X%' THEN 2
        WHEN n.title LIKE 'SZTE%' THEN 1
        WHEN n.title LIKE 'SZOD%' THEN 11
        WHEN n.title LIKE 'POMAZ X%' THEN 6
        WHEN n.title LIKE 'POMAZ 2%' THEN 12
        WHEN n.title LIKE 'POMAZ%' THEN 3
        WHEN n.title LIKE 'GYOR%' THEN 13
        WHEN n.title LIKE 'DEB%' THEN 9
        WHEN n.title LIKE 'BTKT%' THEN 18
        WHEN n.title LIKE 'BP VIH%' THEN 5
        WHEN n.title LIKE 'BP VAR%' THEN 17
        WHEN n.title LIKE 'BP UJL%' THEN 4
        WHEN n.title LIKE 'BP TIS%' THEN 14
        WHEN n.title LIKE 'BP PET%' THEN 16
        WHEN n.title LIKE 'BP KLV%' THEN 8
        WHEN n.title LIKE 'BP DOH%' THEN 10
        ELSE 0
    END AS locationId,
    CASE ft.field_tipus_tid
        WHEN 6 THEN 1
        WHEN 7 THEN 2
        ELSE 0
    END AS storageTypeId,
    TRIM( REGEXP_REPLACE(
        TRIM( SUBSTRING_INDEX( n.title, '-', 1 ) ),
        CONCAT( '^', @location_prefixes, '[ ]*' ),
        ''
     ) ) AS name,
    IfNull( p1.field_1_honapra_amount, 0 ) AS price1M,
    IfNull( p3.field_3_honapra_amount, 0 ) AS price3M,
    IfNull( p6.field_6_honapra_amount, 0 ) AS price6M,
    IfNull( p12.field_12_honapra_amount, 0 ) AS price12M
FROM raktar24_old.node AS n
LEFT JOIN raktar24_old.url_alias AS ua ON ua.source = CONCAT( 'node/', n.nid )
LEFT JOIN raktar24_old.field_data_field_tipus AS ft ON n.nid = ft.entity_id AND ft.bundle = 'tarolo' AND ft.entity_type = 'node'
LEFT JOIN raktar24_old.field_data_field_tarolo AS fp ON n.nid = fp.entity_id AND fp.bundle = 'tarolo' AND fp.entity_type = 'node'
LEFT JOIN raktar24_old.field_data_field_1_honapra AS p1 ON fp.field_tarolo_product_id = p1.entity_id AND p1.bundle = 'product' AND p1.entity_type = 'commerce_product'
LEFT JOIN raktar24_old.field_data_field_3_honapra AS p3 ON fp.field_tarolo_product_id = p3.entity_id AND p3.bundle = 'product' AND p3.entity_type = 'commerce_product'
LEFT JOIN raktar24_old.field_data_field_6_honapra AS p6 ON fp.field_tarolo_product_id = p6.entity_id AND p6.bundle = 'product' AND p6.entity_type = 'commerce_product'
LEFT JOIN raktar24_old.field_data_field_12_honapra AS p12 ON fp.field_tarolo_product_id = p12.entity_id AND p12.bundle = 'product' AND p12.entity_type = 'commerce_product'
LEFT JOIN raktar24_old.file_usage AS fu ON n.nid = fu.id AND fu.type = 'node' AND fu.module = 'file'
LEFT JOIN raktar24_old.file_managed AS fm ON fu.fid = fm.fid
WHERE n.type = 'tarolo'
GROUP BY
    n.nid,
    n.title,
    ft.field_tipus_tid,
    p1.field_1_honapra_amount,
    p3.field_3_honapra_amount,
    p6.field_6_honapra_amount,
    p12.field_12_honapra_amount,
    n.created,
    n.changed;

-- Description

UPDATE raktar24.storage AS s
    JOIN raktar24_old.field_data_body AS fdb ON s.legacyId = fdb.entity_id
SET s.description = REGEXP_REPLACE( REGEXP_REPLACE( fdb.body_value, '\\s*style\\s*=\\s*["\'][^"\']*["\']', '' ), '<style.*?>.*?</style>', '' )
WHERE fdb.entity_type = 'node' AND fdb.bundle = 'tarolo';