-- Copies the data-team dbt output (ded_gid_mapping) into the module-owned mock table
-- energetics.enapo_gid_mapping.
--
-- Source table location differs per environment:
--   - rabin: analytic_dbt.ded_gid_mapping (used below)
--   - golem: tmp.ded_gid_mapping (replace the schema in the FROM clause)
--
-- Notes:
--   - Full refresh of mock rows only: rows are inserted with link_source = 'mock', so this
--     script can be re-run any time and never touches future non-mock (sync task) rows.
--   - The dbt table has no primary key; DISTINCT ON collapses duplicates per (place_id, gid),
--     preferring an active mapping (valid_to IS NULL) and then the newest valid_from.
--   - id_type / commodity are resolved from each other so a row survives when only one of the
--     two columns is filled: an explicit value wins, the other column is the fallback.
--     commodity uses EB codes ('1' -> electricity, '2' -> gas) as well as the DeD names.
--     Because the two are resolved independently, a source row filling both with values that
--     disagree (id_type = 'eic' with commodity = 'electricity') would otherwise be written as an
--     impossible pair. EAN is always electricity and EIC always gas, so such rows are skipped -
--     the same invariant must hold when the dbt sync task takes this logic over.
--   - valid_to = 1900-01-01 is the EB placeholder for "not canceled" and is converted to NULL
--     (dbt should already do this — the NULLIF is a defensive guard).
--   - place_id is capped at 50 chars by the target column; longer values are skipped rather
--     than letting the whole insert fail on a string-truncation error.
--
-- Run the pre-flight query in the comment at the bottom first to see what would be skipped.

BEGIN;

DELETE FROM energetics.enapo_gid_mapping WHERE link_source = 'mock';

WITH src AS (
    SELECT
        trim(s.place_id) AS place_id,
        trim(s.gid) AS gid,
        lower(trim(s.commodity)) AS commodity_raw,
        lower(trim(s.id_type)) AS id_type_raw,
        COALESCE(s.is_first_gid, false) AS is_first_gid,
        s.valid_from,
        NULLIF(s.valid_to, DATE '1900-01-01') AS valid_to
    FROM analytic_dbt.ded_gid_mapping s
    WHERE s.place_id IS NOT NULL AND trim(s.place_id) <> ''
        AND s.gid IS NOT NULL AND trim(s.gid) <> ''
), resolved AS (
    SELECT
        place_id,
        gid,
        is_first_gid,
        valid_from,
        valid_to,
        CASE
            WHEN id_type_raw IN ('ean', 'eic') THEN id_type_raw
            WHEN commodity_raw IN ('1', 'electricity', 'elektrina') THEN 'ean'
            WHEN commodity_raw IN ('2', 'gas', 'plyn') THEN 'eic'
        END AS id_type,
        CASE
            WHEN commodity_raw IN ('1', 'electricity', 'elektrina') THEN 'electricity'
            WHEN commodity_raw IN ('2', 'gas', 'plyn') THEN 'gas'
            WHEN id_type_raw = 'ean' THEN 'electricity'
            WHEN id_type_raw = 'eic' THEN 'gas'
        END AS commodity
    FROM src
    WHERE length(place_id) <= 50
)
INSERT INTO energetics.enapo_gid_mapping
    (place_id, id_type, commodity, gid, is_first_gid, link_source, valid_from, valid_to)
SELECT DISTINCT ON (r.place_id, r.gid)
    r.place_id,
    r.id_type,
    r.commodity,
    r.gid,
    r.is_first_gid,
    'mock',
    r.valid_from,
    r.valid_to
FROM resolved r
WHERE r.id_type IS NOT NULL
    AND r.commodity IS NOT NULL
    -- Drops rows whose source id_type and commodity contradict each other; the two CASE
    -- expressions above resolve independently and cannot catch the disagreement themselves.
    AND (r.id_type, r.commodity) IN (('ean', 'electricity'), ('eic', 'gas'))
ORDER BY
    r.place_id,
    r.gid,
    (r.valid_to IS NULL) DESC,
    r.valid_from DESC NULLS LAST;

COMMIT;

-- Pre-flight / post-check on the source table: which (id_type, commodity) combinations exist,
-- how many rows each holds, whether this script can resolve them and whether the two columns
-- contradict each other (contradicting rows are skipped by the insert above).
--
-- SELECT
--     lower(trim(id_type)) AS id_type_raw,
--     lower(trim(commodity)) AS commodity_raw,
--     count(*) AS rows,
--     count(*) FILTER (WHERE length(trim(place_id)) > 50) AS too_long_place_id,
--     lower(trim(id_type)) IN ('ean', 'eic')
--         OR lower(trim(commodity)) IN ('1', '2', 'electricity', 'elektrina', 'gas', 'plyn') AS resolvable,
--     (lower(trim(id_type)) = 'ean' AND lower(trim(commodity)) IN ('2', 'gas', 'plyn'))
--         OR (lower(trim(id_type)) = 'eic' AND lower(trim(commodity)) IN ('1', 'electricity', 'elektrina'))
--         AS contradicting
-- FROM analytic_dbt.ded_gid_mapping
-- WHERE place_id IS NOT NULL AND trim(place_id) <> ''
--     AND gid IS NOT NULL AND trim(gid) <> ''
-- GROUP BY 1, 2
-- ORDER BY rows DESC;
