-- 1. Удаление существующих таблиц в правильном порядке
DROP TABLE IF EXISTS vit.meter_read CASCADE;
DROP TABLE IF EXISTS vit.verification CASCADE;
DROP TABLE IF EXISTS vit.meter CASCADE;
DROP TABLE IF EXISTS vit.apartments CASCADE;
DROP TABLE IF EXISTS vit.house CASCADE;
DROP TABLE IF EXISTS vit.resourcetype CASCADE;

-- 2. Создание схемы (если не существует)
CREATE SCHEMA IF NOT EXISTS vit;

-- 3. Создание таблиц в правильном порядке зависимостей
CREATE TABLE vit.resourcetype (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE vit.house (
    id SERIAL PRIMARY KEY,
    street VARCHAR(255) NOT NULL,
    number VARCHAR(20) NOT NULL,
    CONSTRAINT uq_house_street_number UNIQUE (street, number)
);

CREATE TABLE vit.apartments (
    id SERIAL PRIMARY KEY,
    house_id INTEGER NOT NULL REFERENCES vit.house(id) ON DELETE CASCADE,
    number VARCHAR(20) NOT NULL,
    CONSTRAINT uq_apartment_house_number UNIQUE (house_id, number)
);

CREATE TABLE vit.meter (
    id SERIAL PRIMARY KEY,
    resourse_id INTEGER NOT NULL REFERENCES vit.resourcetype(id),
    apartments_id INTEGER REFERENCES vit.apartments(id) ON DELETE CASCADE,
    house_id INTEGER REFERENCES vit.house(id) ON DELETE CASCADE,
    manufacturer VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    serialnumber VARCHAR(100) NOT NULL,
    verificationinterval INTEGER NOT NULL,
    installationdate DATE NOT NULL,
    dismantlingdate DATE,
    isfirsttariffinuse BOOLEAN NOT NULL DEFAULT true,
    issecondtariffinuse BOOLEAN NOT NULL DEFAULT false,
    isthirdtariffinuse BOOLEAN NOT NULL DEFAULT false,
    CONSTRAINT chk_meter_owner CHECK (
        (apartments_id IS NOT NULL AND house_id IS NULL) OR
        (apartments_id IS NULL AND house_id IS NOT NULL)
    )
);

-- Частичные уникальные индексы для счётчиков
CREATE UNIQUE INDEX uq_meter_house_sn ON vit.meter (serialnumber, house_id) 
WHERE apartments_id IS NULL;

CREATE UNIQUE INDEX uq_meter_apartment_sn ON vit.meter (serialnumber, apartments_id) 
WHERE apartments_id IS NOT NULL;

CREATE TABLE vit.verification (
    id SERIAL PRIMARY KEY,
    verifiction_date DATE NOT NULL,
    validto DATE NOT NULL,
    meter_id INTEGER NOT NULL REFERENCES vit.meter(id) ON DELETE CASCADE
);

CREATE TABLE vit.meter_read (
    id SERIAL PRIMARY KEY,
    verification_id INTEGER REFERENCES vit.verification(id) ON DELETE SET NULL,
    datetimeoffset DATE NOT NULL,
    firsttariffvalue NUMERIC(15,3) NOT NULL,
    secondtariffvalue NUMERIC(15,3),
    thirdtariffvalue NUMERIC(15,3),
    meter_id INTEGER NOT NULL REFERENCES vit.meter(id) ON DELETE CASCADE
);

-- 4. ПОЛНОСТЬЮ ИСПРАВЛЕННАЯ ПРОЦЕДУРА ЗАПОЛНЕНИЯ
CREATE OR REPLACE PROCEDURE vit.fill_data()
LANGUAGE plpgsql
AS $$
DECLARE
    v_cold_water_id INT;
    v_hot_water_id INT;
    v_electricity_id INT;
    v_gas_id INT;
BEGIN
    -- 1. Заполнение типов ресурсов
    INSERT INTO vit.resourcetype (type) VALUES
        ('Холодная вода'),
        ('Горячая вода'),
        ('Электроэнергия'),
        ('Природный газ')
    ON CONFLICT (type) DO NOTHING;

    SELECT id INTO v_cold_water_id FROM vit.resourcetype WHERE type = 'Холодная вода';
    SELECT id INTO v_hot_water_id FROM vit.resourcetype WHERE type = 'Горячая вода';
    SELECT id INTO v_electricity_id FROM vit.resourcetype WHERE type = 'Электроэнергия';
    SELECT id INTO v_gas_id FROM vit.resourcetype WHERE type = 'Природный газ';

    -- 2. Заполнение домов
    INSERT INTO vit.house (street, number)
    SELECT DISTINCT
        data->>'s' AS street,
        data->>'n' AS number
    FROM public.input_data
    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;

    -- 4. Заполнение общедомовых счётчиков
    WITH meter_data AS (
        SELECT 
            h.id AS house_id,
            meter_elem->>'res' AS res_type,
            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
    )
    INSERT INTO vit.meter (
        house_id, resourse_id, manufacturer, model, serialnumber,
        verificationinterval, installationdate, dismantlingdate,
        isfirsttariffinuse, issecondtariffinuse, isthirdtariffinuse
    )
    SELECT
        md.house_id,
        CASE (md.res_type::TEXT)::INT
            WHEN 0 THEN v_cold_water_id
            WHEN 1 THEN v_hot_water_id
            WHEN 2 THEN v_electricity_id
            WHEN 3 THEN v_gas_id
        END,
        md.manufacturer,
        md.model,
        md.serialnumber,
        md.verificationinterval,
        md.installationdate,
        md.dismantlingdate,
        md.isfirsttariffinuse,
        md.issecondtariffinuse,
        md.isthirdtariffinuse
    FROM meter_data md
    ON CONFLICT (serialnumber, house_id) WHERE apartments_id IS NULL 
    DO NOTHING;

    -- 5. Заполнение квартирных счётчиков
    WITH meter_data AS (
        SELECT 
            a.id AS apartment_id,
            h.id AS house_id,
            meter_elem->>'res' AS res_type,
            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 meter_elem->>'res' IS NOT NULL
    )
    INSERT INTO vit.meter (
        apartments_id, house_id, resourse_id, manufacturer, model, serialnumber,
        verificationinterval, installationdate, dismantlingdate,
        isfirsttariffinuse, issecondtariffinuse, isthirdtariffinuse
    )
    SELECT
        md.apartment_id,
        md.house_id,
        CASE (md.res_type::TEXT)::INT
            WHEN 0 THEN v_cold_water_id
            WHEN 1 THEN v_hot_water_id
            WHEN 2 THEN v_electricity_id
            WHEN 3 THEN v_gas_id
        END,
        md.manufacturer,
        md.model,
        md.serialnumber,
        md.verificationinterval,
        md.installationdate,
        md.dismantlingdate,
        md.isfirsttariffinuse,
        md.issecondtariffinuse,
        md.isthirdtariffinuse
    FROM meter_data md
    ON CONFLICT (serialnumber, apartments_id) 
    DO NOTHING;

    -- 6. Заполнение поверок
    WITH meter_verifications AS (
        -- Общедомовые счётчики
        SELECT 
            m.id AS meter_id,
            verif_elem
        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'
        
        UNION ALL
        
        -- Квартирные счётчики
        SELECT 
            m.id AS meter_id,
            verif_elem
        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'
    )
    INSERT INTO vit.verification (verifiction_date, validto, meter_id)
    SELECT
        (verif_elem->>'d')::DATE,
        (verif_elem->>'to')::DATE,
        meter_id
    FROM meter_verifications
    ON CONFLICT DO NOTHING;

    -- 7. Заполнение показаний
    WITH meter_readings AS (
        -- Общедомовые счётчики
        SELECT 
            m.id AS meter_id,
            reading_elem
        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'
        
        UNION ALL
        
        -- Квартирные счётчики
        SELECT 
            m.id AS meter_id,
            reading_elem
        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'
    )
    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,
        meter_id
    FROM meter_readings
    ON CONFLICT DO NOTHING;

END;
$$;