DROP FUNCTION IF EXISTS akuz.update_calcaddress CASCADE;

CREATE OR REPLACE FUNCTION akuz.update_calcaddress()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
DECLARE
    C  AKUZ.T_CITY%ROWTYPE;
    ST AKUZ.T_STREET%ROWTYPE;
    R  AKUZ.T_RAJON%ROWTYPE;
    RE AKUZ.T_REGION%ROWTYPE;
BEGIN

    SELECT * INTO C FROM AKUZ.T_CITY WHERE CityID = NEW.City;
    SELECT * INTO ST FROM AKUZ.T_STREET WHERE StreetID = NEW.Street;
    SELECT * INTO R FROM AKUZ.T_RAJON WHERE RajonID = C.Rajon;
    SELECT * INTO RE FROM AKUZ.T_REGION WHERE RegionID = R.Region;

    NEW.CalcAddress :=
    CASE
        WHEN NEW.City IS NULL AND NEW.Street IS NULL
            THEN NEW.FreeAddressString
        ELSE
            COALESCE(C.ZipCode || ' ', ''::text)
            || CASE WHEN RE.RegionType IS NOT NULL AND RE.RegionType <> '' THEN RE.RegionType ELSE ''::text END
            || CASE WHEN RE.Name IS NOT NULL AND RE.Name <> '' THEN '.'::text || RE.Name ELSE ''::text END
            || CASE WHEN R.Name IS NOT NULL AND R.Name <> '' THEN ', район '::text || R.Name || ', '::text ELSE ''::text END
            || CASE WHEN C.CityType IS NOT NULL AND C.CityType <> '' THEN C.CityType ELSE ''::text END
            || CASE WHEN C.Name IS NOT NULL AND C.Name <> '' THEN '.'::text || C.Name ELSE ''::text END
            || CASE WHEN ST.Name IS NOT NULL AND ST.Name <> '' THEN ', '::text || ST.Name ELSE ''::text END
            || CASE WHEN NEW.House IS NOT NULL AND NEW.House <> '' AND NEW.House <> '0' THEN ', '::text || NEW.House ELSE ''::text END
            || CASE WHEN NEW.Block IS NOT NULL AND NEW.Block <> '' AND NEW.Block <> '0' THEN ', '::text || NEW.Block ELSE ''::text END
            || CASE WHEN NEW.Flat IS NOT NULL AND NEW.Flat <> '' AND NEW.Flat <> '0' THEN ', '::text || NEW.Flat ELSE ''::text END
    END;

    RETURN NEW;
END;
$$;



CREATE TRIGGER updateafterinsert_calcaddress 
after INSERT ON AKUZ.T_PATIENT_ADDRESS
FOR EACH ROW
EXECUTE FUNCTION akuz.update_calcaddress();