-- 1. metter_object_temp tabula
INSERT INTO metter_object_temp (old_name)
SELECT name
FROM (
    SELECT name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS rn
    FROM skaititaji
) AS derived
WHERE rn = 1;


-- 2. metter_data tabula
WITH parsed_data AS (
    SELECT
        REGEXP_REPLACE(number, '[A-Z]', '') AS numeric_part,
        obis,
        date_from AS meter_date,
        value_from AS meter_value
    FROM skaititaji

    UNION ALL

    SELECT
        REGEXP_REPLACE(number, '[A-Z]', ''),
        obis,
        date_to,
        value_to
    FROM skaititaji
)
INSERT INTO metter_data (NUMBER, obis, DATE, VALUE)
SELECT
    CAST(numeric_part AS DECIMAL(12,0)),
    obis,
    meter_date,
    meter_value
FROM parsed_data;


-- 3. metter_meter tabula
INSERT INTO metter_meter (ch, number, len, `length`)
SELECT
    REGEXP_REPLACE(number, '[0-9]', '') AS chars_only,
    CAST(REGEXP_REPLACE(number, '[A-Z]', '') AS DECIMAL(12,0)) AS numeric_only,
    CHAR_LENGTH(number) AS full_length,
    GREATEST(CHAR_LENGTH(value_from), CHAR_LENGTH(value_to)) AS max_val_length
FROM skaititaji;