# noinspection SpellCheckingInspectionForFile

DELETE FROM location WHERE legacyId IS NOT NULL;

INSERT INTO location (
    legacyId,
    legacySlug,
    legacyPicture,
    createdAt,
    updatedAt,
    name,
    shortName,
    addressId
)
SELECT
    n.nid AS legacyId,
    SUBSTRING_INDEX( ua.alias, '/', -1 ) AS legacySlug,
    IF( COUNT( fm.fid ) = 0, NULL, JSON_ARRAYAGG(
        JSON_OBJECT(
            'fid', fm.fid,
            'name', fm.filename,
            'uri', fm.uri,
            'mime', fm.filemime,
            'size', fm.filesize,
            'timestamp', fm.timestamp,
            'alt', fi.field_image_alt,
            'width', fi.field_image_width,
            'height', fi.field_image_height
        )
    ) ) AS legacyPicture,
    FROM_UNIXTIME( n.created ) AS createdAt,
    FROM_UNIXTIME( n.changed ) AS updatedAt,
    IF( n.title REGEXP 'Budapest|Győr|Debrecen|Pomáz|Szentendre', TRIM( SUBSTRING_INDEX( n.title, '-', -1 ) ), n.title ) AS name,
    CASE n.nid
        WHEN 177 THEN 'SZTE'
        WHEN 178 THEN 'SZTE X'
        WHEN 179 THEN 'POMAZ'
        WHEN 180 THEN 'BP UJL'
        WHEN 283 THEN 'BP VIH'
        WHEN 284 THEN 'POMAZ X'
        WHEN 285 THEN 'SZTE Y'
        WHEN 288 THEN 'BP KLV'
        WHEN 289 THEN 'DEB'
        WHEN 323 THEN 'BP DOH'
        WHEN 364 THEN 'SZOD'
        WHEN 389 THEN 'POMAZ 2'
        WHEN 456 THEN 'GYOR'
        WHEN 474 THEN 'BP TIS'
        WHEN 540 THEN 'BP KLV'
        WHEN 544 THEN 'BP PET'
        WHEN 592 THEN 'BP VAR'
        WHEN 623 THEN 'BTKT'
        ELSE ''
    END AS shortName,
    CASE n.nid
        WHEN 177 THEN 27
        WHEN 178 THEN 29
        WHEN 179 THEN 26
        WHEN 180 THEN 20
        WHEN 283 THEN 22
        WHEN 284 THEN 26
        WHEN 285 THEN 28
        WHEN 288 THEN 17
        WHEN 289 THEN 23
        WHEN 323 THEN 16
        WHEN 364 THEN 30
        WHEN 389 THEN 25
        WHEN 456 THEN 24
        WHEN 474 THEN 19
        WHEN 540 THEN 17
        WHEN 544 THEN 18
        WHEN 592 THEN 21
        WHEN 623 THEN 0
        ELSE 0
    END AS addressId
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.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
LEFT JOIN raktar24_old.field_data_field_image AS fi ON n.nid = fi.entity_id AND fu.fid = fi.field_image_fid AND fi.bundle = 'helyszinalaprajz'
WHERE n.type = 'helyszinalaprajz'
GROUP BY n.nid;