-- Увеличьте параметры для массовой загрузки
SET maintenance_work_mem = '1GB';
SET work_mem = '256MB';
SET max_parallel_workers_per_gather = 4;
SET synchronous_commit = off; -- Важно! Отключите для импорта






-- 1. ИМПОРТ ТИПОВ РЕСУРСОВ
INSERT INTO vit.resourcetype (type) VALUES
    ('Холодная вода'),
    ('Горячая вода'),
    ('Электроэнергия'),
    ('Природный газ')
ON CONFLICT (type) DO NOTHING;

-- 2. ИМПОРТ ДОМОВ 
INSERT INTO vit.house (street, number)
SELECT DISTINCT
    data->>'s' AS street,
    data->>'n' AS number
FROM public.input_data
WHERE data ? 's' AND data ? 'n'
  AND (data->>'s') IS NOT NULL AND (data->>'n') IS NOT NULL
  AND (data->>'s') <> '' AND (data->>'n') <> ''
ON CONFLICT (street, number) DO NOTHING;

-- 3. ИМПОРТ КВАРТИР
INSERT INTO vit.apartments (house_id, number)
SELECT DISTINCT
    h.id AS house_id,
    apt_elem->>'n' AS number
FROM public.input_data idata
JOIN vit.house h 
    ON h.street = (idata.data->>'s') 
    AND h.number = (idata.data->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(idata.data->'a') = 'array' THEN idata.data->'a'
            ELSE '[]'::jsonb
        END AS safe_apartments
) AS apartments_array
CROSS JOIN LATERAL jsonb_array_elements(apartments_array.safe_apartments) AS apt_elem
WHERE idata.data ? 'a'
    AND apt_elem->>'n' IS NOT NULL
    AND apt_elem->>'n' <> ''
ON CONFLICT (house_id, number) DO NOTHING;

-- ОБЩЕДОМОВЫе
INSERT INTO vit.meter (
    house_id, resourse_id, manufacturer, model, serialnumber,
    verificationinterval, installationdate, dismantlingdate,
    isfirsttariffinuse, issecondtariffinuse, isthirdtariffinuse
)
SELECT
    h.id AS house_id,
    CASE (meter_elem->>'res')::TEXT::INT
        WHEN 0 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Холодная вода')
        WHEN 1 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Горячая вода')
        WHEN 2 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Электроэнергия')
        WHEN 3 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Природный газ')
    END AS resourse_id,
    meter_elem->>'man' AS manufacturer,
    meter_elem->>'mod' AS model,
    meter_elem->>'sn' AS serialnumber,
    (meter_elem->>'vi')::INT AS verificationinterval,
    (meter_elem->>'id')::DATE AS installationdate,
    NULLIF(NULLIF(meter_elem->>'dd', ''), 'null')::DATE AS dismantlingdate,
    COALESCE((meter_elem->>'t1')::BOOL, true) AS isfirsttariffinuse,
    COALESCE((meter_elem->>'t2')::BOOL, false) AS issecondtariffinuse,
    COALESCE((meter_elem->>'t3')::BOOL, false) AS isthirdtariffinuse
FROM public.input_data idata
JOIN vit.house h 
    ON h.street = (idata.data->>'s') 
    AND h.number = (idata.data->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(idata.data->'hm') = 'array' THEN idata.data->'hm'
            ELSE '[]'::jsonb
        END AS safe_hm
) AS hm_array
CROSS JOIN LATERAL jsonb_array_elements(hm_array.safe_hm) AS meter_elem
WHERE idata.data ? 'hm'
    AND meter_elem->>'sn' IS NOT NULL
    AND meter_elem->>'sn' <> ''
    AND meter_elem->>'res' IS NOT NULL
    AND (meter_elem->>'vi') IS NOT NULL
    AND (meter_elem->>'id') IS NOT NULL
ON CONFLICT (serialnumber, house_id) WHERE apartments_id IS NULL 
DO NOTHING;



-- Удаляем старые индексы/ограничения
DROP INDEX IF EXISTS vit.uq_meter_house_sn;
DROP INDEX IF EXISTS vit.uq_meter_apartment_sn;

-- Создаем полноценные уникальные ограничения
ALTER TABLE vit.meter 
    DROP CONSTRAINT IF EXISTS uq_meter_house_sn,
    ADD CONSTRAINT uq_meter_house_sn UNIQUE (serialnumber, house_id);

ALTER TABLE vit.meter 
    DROP CONSTRAINT IF EXISTS uq_meter_apartment_sn,
    ADD CONSTRAINT uq_meter_apartment_sn UNIQUE (serialnumber, apartments_id);
SELECT conname, contype 
FROM pg_constraint 
WHERE conrelid = 'vit.meter'::regclass 
AND conname IN ('uq_meter_house_sn', 'uq_meter_apartment_sn');




-- квартирные
INSERT INTO vit.meter (
    apartments_id, house_id, resourse_id, manufacturer, model, serialnumber,
    verificationinterval, installationdate, dismantlingdate,
    isfirsttariffinuse, issecondtariffinuse, isthirdtariffinuse
)
SELECT DISTINCT ON (meter_elem->>'sn', a.id)
    a.id AS apartment_id,
    h.id AS house_id,
    CASE (meter_elem->>'res')::TEXT::INT
        WHEN 0 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Холодная вода')
        WHEN 1 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Горячая вода')
        WHEN 2 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Электроэнергия')
        WHEN 3 THEN (SELECT id FROM vit.resourcetype WHERE type = 'Природный газ')
    END AS resourse_id,
    meter_elem->>'man' AS manufacturer,
    meter_elem->>'mod' AS model,
    meter_elem->>'sn' AS serialnumber,
    (meter_elem->>'vi')::INT AS verificationinterval,
    (meter_elem->>'id')::DATE AS installationdate,
    NULLIF(NULLIF(meter_elem->>'dd', ''), 'null')::DATE AS dismantlingdate,
    COALESCE((meter_elem->>'t1')::BOOL, true) AS isfirsttariffinuse,
    COALESCE((meter_elem->>'t2')::BOOL, false) AS issecondtariffinuse,
    COALESCE((meter_elem->>'t3')::BOOL, false) AS isthirdtariffinuse
FROM public.input_data idata
JOIN vit.house h 
    ON h.street = (idata.data->>'s') 
    AND h.number = (idata.data->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(idata.data->'a') = 'array' THEN idata.data->'a'
            ELSE '[]'::jsonb
        END AS safe_apartments
) AS apartments_array
CROSS JOIN LATERAL jsonb_array_elements(apartments_array.safe_apartments) AS apt_elem
JOIN vit.apartments a 
    ON a.house_id = h.id 
    AND a.number = (apt_elem->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(apt_elem->'m') = 'array' THEN apt_elem->'m'
            ELSE '[]'::jsonb
        END AS safe_meters
) AS meters_array
CROSS JOIN LATERAL jsonb_array_elements(meters_array.safe_meters) AS meter_elem
WHERE apt_elem ? 'm'
    AND meter_elem->>'sn' IS NOT NULL
    AND meter_elem->>'sn' <> ''
    AND NOT EXISTS (  -- Проверка на существование
        SELECT 1 FROM vit.meter m 
        WHERE m.serialnumber = meter_elem->>'sn'
        AND m.apartments_id = a.id
    );


INSERT INTO vit.verification (verifiction_date, validto, meter_id)
WITH verification_data AS (
    SELECT
        (verif_elem->>'d')::DATE AS verifiction_date,
        (verif_elem->>'to')::DATE AS validto,
        m.id AS meter_id
    FROM public.input_data idata
    JOIN vit.house h 
        ON h.street = (idata.data->>'s') 
        AND h.number = (idata.data->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(idata.data->'hm') = 'array' THEN idata.data->'hm'
                ELSE '[]'::jsonb
            END AS safe_hm
    ) AS hm_array
    CROSS JOIN LATERAL jsonb_array_elements(hm_array.safe_hm) AS meter_elem
    JOIN vit.meter m 
        ON m.house_id = h.id 
        AND m.serialnumber = (meter_elem->>'sn')
        AND m.apartments_id IS NULL
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(meter_elem->'vh') = 'array' THEN meter_elem->'vh'
                ELSE '[]'::jsonb
            END AS safe_vh
    ) AS vh_array
    CROSS JOIN LATERAL jsonb_array_elements(vh_array.safe_vh) AS verif_elem
    WHERE meter_elem ? 'vh'
        AND jsonb_typeof(verif_elem) = 'object'
        AND verif_elem ? 'd'
        AND verif_elem ? 'to'
        AND (verif_elem->>'d') IS NOT NULL
        AND (verif_elem->>'to') IS NOT NULL
    
    UNION ALL
    
    -- Квартирные счётчики
    SELECT
        (verif_elem->>'d')::DATE AS verifiction_date,
        (verif_elem->>'to')::DATE AS validto,
        m.id AS meter_id
    FROM public.input_data idata
    JOIN vit.house h 
        ON h.street = (idata.data->>'s') 
        AND h.number = (idata.data->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(idata.data->'a') = 'array' THEN idata.data->'a'
                ELSE '[]'::jsonb
            END AS safe_apartments
    ) AS apartments_array
    CROSS JOIN LATERAL jsonb_array_elements(apartments_array.safe_apartments) AS apt_elem
    JOIN vit.apartments a 
        ON a.house_id = h.id 
        AND a.number = (apt_elem->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(apt_elem->'m') = 'array' THEN apt_elem->'m'
                ELSE '[]'::jsonb
            END AS safe_meters
    ) AS meters_array
    CROSS JOIN LATERAL jsonb_array_elements(meters_array.safe_meters) AS meter_elem
    JOIN vit.meter m 
        ON m.apartments_id = a.id 
        AND m.serialnumber = (meter_elem->>'sn')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(meter_elem->'vh') = 'array' THEN meter_elem->'vh'
                ELSE '[]'::jsonb
            END AS safe_vh
    ) AS vh_array
    CROSS JOIN LATERAL jsonb_array_elements(vh_array.safe_vh) AS verif_elem
    WHERE meter_elem ? 'vh'
        AND jsonb_typeof(verif_elem) = 'object'
        AND verif_elem ? 'd'
        AND verif_elem ? 'to'
        AND (verif_elem->>'d') IS NOT NULL
        AND (verif_elem->>'to') IS NOT NULL
)
SELECT verifiction_date, validto, meter_id
FROM verification_data
ON CONFLICT DO NOTHING;


INSERT INTO vit.meter_read (
    datetimeoffset, 
    firsttariffvalue, 
    secondtariffvalue, 
    thirdtariffvalue, 
    meter_id
)
WITH readings_data AS (
    -- Общедомовые счётчики
    SELECT
        (reading_elem->>'d')::DATE AS datetimeoffset,
        (reading_elem->>'t1')::NUMERIC AS firsttariffvalue,
        NULLIF(reading_elem->>'t2', '')::NUMERIC AS secondtariffvalue,
        NULLIF(reading_elem->>'t3', '')::NUMERIC AS thirdtariffvalue,
        m.id AS meter_id
    FROM public.input_data idata
    JOIN vit.house h 
        ON h.street = (idata.data->>'s') 
        AND h.number = (idata.data->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(idata.data->'hm') = 'array' THEN idata.data->'hm'
                ELSE '[]'::jsonb
            END AS safe_hm
    ) AS hm_array
    CROSS JOIN LATERAL jsonb_array_elements(hm_array.safe_hm) AS meter_elem
    JOIN vit.meter m 
        ON m.house_id = h.id 
        AND m.serialnumber = (meter_elem->>'sn')
        AND m.apartments_id IS NULL
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(meter_elem->'v') = 'array' THEN meter_elem->'v'
                ELSE '[]'::jsonb
            END AS safe_v
    ) AS v_array
    CROSS JOIN LATERAL jsonb_array_elements(v_array.safe_v) AS reading_elem
    WHERE meter_elem ? 'v'
        AND jsonb_typeof(reading_elem) = 'object'
        AND reading_elem ? 'd'
        AND reading_elem ? 't1'
        AND (reading_elem->>'d') IS NOT NULL
    
    UNION ALL
    
    -- Квартирные счётчики
    SELECT
        (reading_elem->>'d')::DATE AS datetimeoffset,
        (reading_elem->>'t1')::NUMERIC AS firsttariffvalue,
        NULLIF(reading_elem->>'t2', '')::NUMERIC AS secondtariffvalue,
        NULLIF(reading_elem->>'t3', '')::NUMERIC AS thirdtariffvalue,
        m.id AS meter_id
    FROM public.input_data idata
    JOIN vit.house h 
        ON h.street = (idata.data->>'s') 
        AND h.number = (idata.data->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(idata.data->'a') = 'array' THEN idata.data->'a'
                ELSE '[]'::jsonb
            END AS safe_apartments
    ) AS apartments_array
    CROSS JOIN LATERAL jsonb_array_elements(apartments_array.safe_apartments) AS apt_elem
    JOIN vit.apartments a 
        ON a.house_id = h.id 
        AND a.number = (apt_elem->>'n')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(apt_elem->'m') = 'array' THEN apt_elem->'m'
                ELSE '[]'::jsonb
            END AS safe_meters
    ) AS meters_array
    CROSS JOIN LATERAL jsonb_array_elements(meters_array.safe_meters) AS meter_elem
    JOIN vit.meter m 
        ON m.apartments_id = a.id 
        AND m.serialnumber = (meter_elem->>'sn')
    CROSS JOIN LATERAL (
        SELECT 
            CASE 
                WHEN jsonb_typeof(meter_elem->'v') = 'array' THEN meter_elem->'v'
                ELSE '[]'::jsonb
            END AS safe_v
    ) AS v_array
    CROSS JOIN LATERAL jsonb_array_elements(v_array.safe_v) AS reading_elem
    WHERE meter_elem ? 'v'
        AND jsonb_typeof(reading_elem) = 'object'
        AND reading_elem ? 'd'
        AND reading_elem ? 't1'
        AND (reading_elem->>'d') IS NOT NULL
)
SELECT 
    datetimeoffset, 
    firsttariffvalue, 
    secondtariffvalue, 
    thirdtariffvalue, 
    meter_id
FROM readings_data
LIMIT 1000000
ON CONFLICT DO NOTHING;


















Select Count(id) from vit.verification

INSERT INTO vit.house (street, number)
SELECT DISTINCT
    data->>'s' AS street,
    data->>'n' AS number
FROM public.input_data
WHERE id <= 100  -- Ограничиваем 10 записями!
  AND data ? 's' AND data ? 'n'
  AND (data->>'s') <> '' AND (data->>'n') <> ''
ON CONFLICT (street, number) DO NOTHING;






































ALTER TABLE vit.verification DISABLE TRIGGER ALL;
DROP INDEX IF EXISTS vit.verification_meter_id_idx;


INSERT INTO vit.meter_read (
    datetimeoffset, 
    firsttariffvalue, 
    secondtariffvalue, 
    thirdtariffvalue, 
    meter_id
)
SELECT
    (reading_elem->>'d')::DATE,
    (reading_elem->>'t1')::NUMERIC,
    NULLIF(reading_elem->>'t2', '')::NUMERIC,
    NULLIF(reading_elem->>'t3', '')::NUMERIC,
    m.id AS meter_id
FROM public.input_data idata
-- Общедомовые счётчики
JOIN vit.house h 
    ON h.street = (idata.data->>'s') 
    AND h.number = (idata.data->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(idata.data->'hm') = 'array' THEN idata.data->'hm'
            ELSE '[]'::jsonb
        END AS safe_hm
) AS hm_array
CROSS JOIN LATERAL jsonb_array_elements(hm_array.safe_hm) AS meter_elem
JOIN vit.meter m 
    ON m.house_id = h.id 
    AND m.serialnumber = (meter_elem->>'sn')
    AND m.apartments_id IS NULL
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(meter_elem->'v') = 'array' THEN meter_elem->'v'
            ELSE '[]'::jsonb
        END AS safe_v
) AS v_array
CROSS JOIN LATERAL jsonb_array_elements(v_array.safe_v) AS reading_elem
WHERE meter_elem ? 'v'
    AND jsonb_typeof(reading_elem) = 'object'
    AND reading_elem ? 'd'
    AND reading_elem ? 't1'
    AND (reading_elem->>'d') IS NOT NULL

UNION ALL

-- Квартирные счётчики
SELECT
    (reading_elem->>'d')::DATE,
    (reading_elem->>'t1')::NUMERIC,
    NULLIF(reading_elem->>'t2', '')::NUMERIC,
    NULLIF(reading_elem->>'t3', '')::NUMERIC,
    m.id AS meter_id
FROM public.input_data idata
JOIN vit.house h 
    ON h.street = (idata.data->>'s') 
    AND h.number = (idata.data->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(idata.data->'a') = 'array' THEN idata.data->'a'
            ELSE '[]'::jsonb
        END AS safe_apartments
) AS apartments_array
CROSS JOIN LATERAL jsonb_array_elements(apartments_array.safe_apartments) AS apt_elem
JOIN vit.apartments a 
    ON a.house_id = h.id 
    AND a.number = (apt_elem->>'n')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(apt_elem->'m') = 'array' THEN apt_elem->'m'
            ELSE '[]'::jsonb
        END AS safe_meters
) AS meters_array
CROSS JOIN LATERAL jsonb_array_elements(meters_array.safe_meters) AS meter_elem
JOIN vit.meter m 
    ON m.apartments_id = a.id 
    AND m.serialnumber = (meter_elem->>'sn')
CROSS JOIN LATERAL (
    SELECT 
        CASE 
            WHEN jsonb_typeof(meter_elem->'v') = 'array' THEN meter_elem->'v'
            ELSE '[]'::jsonb
        END AS safe_v
) AS v_array
CROSS JOIN LATERAL jsonb_array_elements(v_array.safe_v) AS reading_elem
WHERE meter_elem ? 'v'
    AND jsonb_typeof(reading_elem) = 'object'
    AND reading_elem ? 'd'
    AND reading_elem ? 't1'
    AND (reading_elem->>'d') IS NOT NULL
ON CONFLICT DO NOTHING;