-- File: database/imports/010_import_master_catalog_from_json.sql
-- Compatible import script avoiding ON DUPLICATE KEY UPDATE on INSERT ... SELECT
-- which can break depending on the MySQL/MariaDB version and parser.
-- MySQL 8.0+ recommended because of JSON_TABLE.
--
-- Usage:
-- 1) Copy product_catalog.json into a path readable by MySQL.
-- 2) Update @catalog_json_path below.
-- 3) Execute this script.

SET collation_connection = 'utf8mb4_unicode_ci';

SET @catalog_json_path = 'C:/wamp64/tmp/product_catalog.json';
SET @catalog_json = LOAD_FILE(@catalog_json_path);

SELECT
    @catalog_json_path AS json_path,
    JSON_VALID(@catalog_json) AS is_valid_json,
    JSON_LENGTH(@catalog_json) AS product_count;

DROP TEMPORARY TABLE IF EXISTS tmp_master_catalog_raw;
CREATE TEMPORARY TABLE tmp_master_catalog_raw (
    row_num INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    category_name VARCHAR(120) NULL,
    subcategory_name VARCHAR(120) NULL,
    description_text TEXT NULL,
    notes_text TEXT NULL,
    source_text TEXT NULL,
    formats_text TEXT NULL,
    materials_text TEXT NULL,
    grammages_text TEXT NULL,
    printing_text TEXT NULL,
    finishes_text TEXT NULL,
    options_text TEXT NULL,
    quantities_text TEXT NULL,
    attributes_json JSON NULL,
    PRIMARY KEY (row_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO tmp_master_catalog_raw (
    row_num,
    name,
    category_name,
    subcategory_name,
    description_text,
    notes_text,
    source_text,
    formats_text,
    materials_text,
    grammages_text,
    printing_text,
    finishes_text,
    options_text,
    quantities_text,
    attributes_json
)
SELECT
    jt.row_num,
    jt.name,
    jt.category_name,
    jt.subcategory_name,
    jt.description_text,
    jt.notes_text,
    jt.source_text,
    jt.formats_text,
    jt.materials_text,
    jt.grammages_text,
    jt.printing_text,
    jt.finishes_text,
    jt.options_text,
    jt.quantities_text,
    jt.attributes_json
FROM JSON_TABLE(
    @catalog_json,
    '$[*]' COLUMNS (
        row_num FOR ORDINALITY,
        name VARCHAR(190) PATH '$.name',
        category_name VARCHAR(120) PATH '$.category',
        subcategory_name VARCHAR(120) PATH '$.subcategory',
        description_text TEXT PATH '$.description',
        notes_text TEXT PATH '$.notes',
        source_text TEXT PATH '$.source',
        formats_text TEXT PATH '$.formats',
        materials_text TEXT PATH '$.materials',
        grammages_text TEXT PATH '$.grammages',
        printing_text TEXT PATH '$.printing',
        finishes_text TEXT PATH '$.finishes',
        options_text TEXT PATH '$.options',
        quantities_text TEXT PATH '$.quantities',
        attributes_json JSON PATH '$.attributes_json'
    )
) AS jt;

INSERT IGNORE INTO products (
    sku,
    default_name,
    default_description,
    product_type,
    category_name,
    subcategory_name,
    base_spec_json,
    is_active
)
SELECT
    CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
        COALESCE(r.category_name, ''),
        '|',
        COALESCE(r.subcategory_name, ''),
        '|',
        COALESCE(r.name, '')
    )), 1, 12))) AS sku,
    r.name,
    COALESCE(NULLIF(r.description_text, ''), CONCAT(r.name, ' — ', COALESCE(r.category_name, ''), ' / ', COALESCE(r.subcategory_name, ''))),
    CASE
        WHEN r.category_name = 'Papeterie' THEN 'print'
        WHEN r.category_name = 'Signalétique' THEN 'signage'
        WHEN r.category_name = 'Packaging' THEN 'packaging'
        WHEN r.category_name = 'Événementiel' THEN 'event'
        WHEN r.category_name = 'Goodies' THEN 'goodies'
        ELSE 'catalog'
    END AS product_type,
    r.category_name,
    r.subcategory_name,
    JSON_OBJECT(
        'notes', r.notes_text,
        'source', r.source_text,
        'formats_text', r.formats_text,
        'materials_text', r.materials_text,
        'grammages_text', r.grammages_text,
        'printing_text', r.printing_text,
        'finishes_text', r.finishes_text,
        'options_text', r.options_text,
        'quantities_text', r.quantities_text
    ) AS base_spec_json,
    1
FROM tmp_master_catalog_raw r;

UPDATE products p
INNER JOIN (
    SELECT
        CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
            COALESCE(r.category_name, ''),
            '|',
            COALESCE(r.subcategory_name, ''),
            '|',
            COALESCE(r.name, '')
        )), 1, 12))) AS sku,
        r.name AS default_name,
        COALESCE(NULLIF(r.description_text, ''), CONCAT(r.name, ' — ', COALESCE(r.category_name, ''), ' / ', COALESCE(r.subcategory_name, ''))) AS default_description,
        CASE
            WHEN r.category_name = 'Papeterie' THEN 'print'
            WHEN r.category_name = 'Signalétique' THEN 'signage'
            WHEN r.category_name = 'Packaging' THEN 'packaging'
            WHEN r.category_name = 'Événementiel' THEN 'event'
            WHEN r.category_name = 'Goodies' THEN 'goodies'
            ELSE 'catalog'
        END AS product_type,
        r.category_name,
        r.subcategory_name,
        JSON_OBJECT(
            'notes', r.notes_text,
            'source', r.source_text,
            'formats_text', r.formats_text,
            'materials_text', r.materials_text,
            'grammages_text', r.grammages_text,
            'printing_text', r.printing_text,
            'finishes_text', r.finishes_text,
            'options_text', r.options_text,
            'quantities_text', r.quantities_text
        ) AS base_spec_json
    FROM tmp_master_catalog_raw r
) src ON src.sku COLLATE utf8mb4_unicode_ci = p.sku COLLATE utf8mb4_unicode_ci
SET
    p.default_name = src.default_name,
    p.default_description = src.default_description,
    p.product_type = src.product_type,
    p.category_name = src.category_name,
    p.subcategory_name = src.subcategory_name,
    p.base_spec_json = src.base_spec_json,
    p.is_active = 1;

INSERT IGNORE INTO product_attributes (
    product_id,
    code,
    name,
    input_type,
    is_required,
    is_pricing_quantity,
    position
)
SELECT
    p.id,
    CASE
        WHEN LOWER(a.attribute_name) LIKE 'format%' THEN 'format'
        WHEN LOWER(a.attribute_name) LIKE 'support%' OR LOWER(a.attribute_name) LIKE '%mati%' THEN 'material'
        WHEN LOWER(a.attribute_name) LIKE 'grammage%' OR LOWER(a.attribute_name) LIKE '%epaisseur%' OR LOWER(a.attribute_name) LIKE '%épaisseur%' THEN 'grammage'
        WHEN LOWER(a.attribute_name) LIKE 'impression%' THEN 'printing'
        WHEN LOWER(a.attribute_name) LIKE 'finit%' THEN 'finish'
        WHEN LOWER(a.attribute_name) LIKE '%option%' OR LOWER(a.attribute_name) LIKE '%faconnage%' OR LOWER(a.attribute_name) LIKE '%façonnage%' THEN 'option'
        WHEN LOWER(a.attribute_name) LIKE '%quantit%' THEN 'lot_quantity'
        ELSE CONCAT('attr_', LPAD(a.position, 2, '0'))
    END AS code,
    a.attribute_name,
    CASE
        WHEN a.input_type IN ('select', 'multiselect', 'text', 'number', 'boolean') THEN a.input_type
        ELSE 'select'
    END AS input_type,
    COALESCE(a.is_required, 0) AS is_required,
    CASE
        WHEN LOWER(a.attribute_name) LIKE '%quantit%' THEN 1
        ELSE 0
    END AS is_pricing_quantity,
    a.position
FROM tmp_master_catalog_raw r
INNER JOIN products p
    ON p.sku COLLATE utf8mb4_unicode_ci = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
        COALESCE(r.category_name, ''),
        '|',
        COALESCE(r.subcategory_name, ''),
        '|',
        COALESCE(r.name, '')
    )), 1, 12)))
INNER JOIN JSON_TABLE(
    r.attributes_json,
    '$[*]' COLUMNS (
        position FOR ORDINALITY,
        attribute_name VARCHAR(190) PATH '$.name',
        input_type VARCHAR(30) PATH '$.type',
        is_required TINYINT PATH '$.required'
    )
) AS a;

UPDATE product_attributes pa
INNER JOIN (
    SELECT
        p.id AS product_id,
        CASE
            WHEN LOWER(a.attribute_name) LIKE 'format%' THEN 'format'
            WHEN LOWER(a.attribute_name) LIKE 'support%' OR LOWER(a.attribute_name) LIKE '%mati%' THEN 'material'
            WHEN LOWER(a.attribute_name) LIKE 'grammage%' OR LOWER(a.attribute_name) LIKE '%epaisseur%' OR LOWER(a.attribute_name) LIKE '%épaisseur%' THEN 'grammage'
            WHEN LOWER(a.attribute_name) LIKE 'impression%' THEN 'printing'
            WHEN LOWER(a.attribute_name) LIKE 'finit%' THEN 'finish'
            WHEN LOWER(a.attribute_name) LIKE '%option%' OR LOWER(a.attribute_name) LIKE '%faconnage%' OR LOWER(a.attribute_name) LIKE '%façonnage%' THEN 'option'
            WHEN LOWER(a.attribute_name) LIKE '%quantit%' THEN 'lot_quantity'
            ELSE CONCAT('attr_', LPAD(a.position, 2, '0'))
        END AS code,
        a.attribute_name AS name,
        CASE
            WHEN a.input_type IN ('select', 'multiselect', 'text', 'number', 'boolean') THEN a.input_type
            ELSE 'select'
        END AS input_type,
        COALESCE(a.is_required, 0) AS is_required,
        CASE
            WHEN LOWER(a.attribute_name) LIKE '%quantit%' THEN 1
            ELSE 0
        END AS is_pricing_quantity,
        a.position
    FROM tmp_master_catalog_raw r
    INNER JOIN products p
        ON p.sku = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
            COALESCE(r.category_name, ''),
            '|',
            COALESCE(r.subcategory_name, ''),
            '|',
            COALESCE(r.name, '')
        )), 1, 12)))
    INNER JOIN JSON_TABLE(
        r.attributes_json,
        '$[*]' COLUMNS (
            position FOR ORDINALITY,
            attribute_name VARCHAR(190) PATH '$.name',
            input_type VARCHAR(30) PATH '$.type',
            is_required TINYINT PATH '$.required'
        )
    ) AS a
) src
    ON src.product_id = pa.product_id
   AND src.code COLLATE utf8mb4_unicode_ci = pa.code COLLATE utf8mb4_unicode_ci
SET
    pa.name = src.name,
    pa.input_type = src.input_type,
    pa.is_required = src.is_required,
    pa.is_pricing_quantity = src.is_pricing_quantity,
    pa.position = src.position;

INSERT IGNORE INTO product_attribute_options (
    product_attribute_id,
    value,
    label,
    position
)
SELECT
    pa.id AS product_attribute_id,
    attribute_options.option_value AS value,
    attribute_options.option_value AS label,
    attribute_options.option_position - 1 AS position
FROM tmp_master_catalog_raw r
INNER JOIN products p
    ON p.sku COLLATE utf8mb4_unicode_ci = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
        COALESCE(r.category_name, ''),
        '|',
        COALESCE(r.subcategory_name, ''),
        '|',
        COALESCE(r.name, '')
    )), 1, 12)))
INNER JOIN JSON_TABLE(
    r.attributes_json,
    '$[*]' COLUMNS (
        attribute_name VARCHAR(190) PATH '$.name',
        NESTED PATH '$.options[*]' COLUMNS (
            option_position FOR ORDINALITY,
            option_value VARCHAR(190) PATH '$'
        )
    )
) AS attribute_options
INNER JOIN product_attributes pa
    ON pa.product_id = p.id
   AND pa.name COLLATE utf8mb4_unicode_ci = attribute_options.attribute_name COLLATE utf8mb4_unicode_ci;

UPDATE product_attribute_options pao
INNER JOIN (
    SELECT
        pa.id AS product_attribute_id,
        attribute_options.option_value AS value,
        attribute_options.option_value AS label,
        attribute_options.option_position - 1 AS position
    FROM tmp_master_catalog_raw r
    INNER JOIN products p
        ON p.sku = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
            COALESCE(r.category_name, ''),
            '|',
            COALESCE(r.subcategory_name, ''),
            '|',
            COALESCE(r.name, '')
        )), 1, 12)))
    INNER JOIN JSON_TABLE(
        r.attributes_json,
        '$[*]' COLUMNS (
            attribute_name VARCHAR(190) PATH '$.name',
            NESTED PATH '$.options[*]' COLUMNS (
                option_position FOR ORDINALITY,
                option_value VARCHAR(190) PATH '$'
            )
        )
    ) AS attribute_options
    INNER JOIN product_attributes pa
        ON pa.product_id = p.id
       AND pa.name COLLATE utf8mb4_unicode_ci = attribute_options.attribute_name COLLATE utf8mb4_unicode_ci
) src
    ON src.product_attribute_id = pao.product_attribute_id
   AND src.value COLLATE utf8mb4_unicode_ci = pao.value COLLATE utf8mb4_unicode_ci
SET
    pao.label = src.label,
    pao.position = src.position;

INSERT IGNORE INTO price_grids (
    product_id,
    code,
    name,
    currency,
    is_active
)
SELECT
    p.id,
    'master-default',
    'Tarif maître',
    'EUR',
    1
FROM tmp_master_catalog_raw r
INNER JOIN products p
    ON p.sku COLLATE utf8mb4_unicode_ci = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
        COALESCE(r.category_name, ''),
        '|',
        COALESCE(r.subcategory_name, ''),
        '|',
        COALESCE(r.name, '')
    )), 1, 12)));

UPDATE price_grids pg
INNER JOIN (
    SELECT
        p.id AS product_id,
        'master-default' AS code,
        'Tarif maître' AS name,
        'EUR' AS currency,
        1 AS is_active
    FROM tmp_master_catalog_raw r
    INNER JOIN products p
        ON p.sku = CONCAT('CAT-', UPPER(SUBSTRING(MD5(CONCAT(
            COALESCE(r.category_name, ''),
            '|',
            COALESCE(r.subcategory_name, ''),
            '|',
            COALESCE(r.name, '')
        )), 1, 12)))
) src
    ON src.product_id = pg.product_id
   AND src.code COLLATE utf8mb4_unicode_ci = pg.code COLLATE utf8mb4_unicode_ci
SET
    pg.name = src.name,
    pg.currency = src.currency,
    pg.is_active = src.is_active;

SELECT
    COUNT(*) AS imported_products
FROM products
WHERE sku LIKE 'CAT-%';
