DROP FUNCTION IF EXISTS sp_schetzapidentification;
DROP PROCEDURE IF EXISTS sp_schetzapidentification;

CREATE PROCEDURE  public.sp_schetzapidentification(
	IN "SchetID" uuid
	)
LANGUAGE 'plpgsql'
AS $BODY$
	
declare type_ integer;
begin
	SELECT t1.type_ INTO type_
    FROM ies.T_SCHET t1
    WHERE t1.SchetID = "SchetID"
    LIMIT 1;

	-- Обновление mindate
    UPDATE ies.T_SCHET_ZAP t1
    SET mindate = (
        SELECT MIN(t2.DATE_2)::date
        FROM ies.T_SCHET_SLUCH t2
        WHERE t2.SchetZap = t1.SchetZapID
    )
    WHERE t1.Schet = "SchetID";

    -- Обновление iip
	-- step 1
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
        WHERE LOWER(t5.LastName) = LOWER(t1.FAM)
          AND LOWER(t5.FirstName) = LOWER(t1.IM)
          AND LOWER(COALESCE(t5.Patronymic, '')) = LOWER(COALESCE(t1.OT, ''))
          AND t5.Sex = t1.W
          AND t5.Birthday = t1.DR
          AND t5.SNILS = t1.SNILS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC, t3.OmsType DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID" AND t1.iip IS NULL;

	-- step 2
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
          AND LOWER(t2.FirstName) = LOWER(t1.IM)
          AND LOWER(COALESCE(t2.Patronymic, '')) = LOWER(COALESCE(t1.OT, ''))
          AND t2.Sex = t1.W
          AND t2.Birhday = t1.DR
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC, t3.OmsType DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID" AND t1.iip IS NULL;

	-- step 3
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM_P)
          AND LOWER(t2.FirstName) = LOWER(t1.IM_P)
          AND LOWER(COALESCE(t2.Patronymic, '')) = LOWER(COALESCE(t1.OT_P, ''))
          AND t2.Sex = t1.W_P
          AND t2.Birhday = t1.DR_P
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC, t3.OmsType DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID" AND COALESCE(t1.NOVOR, '0') <> '0' AND t1.iip IS NULL;

	-- step 4
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
        WHERE LOWER(t5.LastName) = LOWER(t1.FAM_P)
          AND LOWER(t5.FirstName) = LOWER(t1.IM_P)
          AND LOWER(COALESCE(t5.Patronymic, '')) = LOWER(COALESCE(t1.OT_P, ''))
          AND t5.Sex = t1.W_P
          AND t5.Birthday = t1.DR_P
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC, t3.OmsType DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID" AND t1.iip IS NULL;

	-- step 5
    -- СНИЛС - ФАМИЛИЯ - ИМЯ - ОТЧЕСТВО ДР для местных
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
        WHERE LOWER(t5.LastName) = LOWER(t1.FAM)
          AND LOWER(t5.FirstName) = LOWER(t1.IM)
          AND LOWER(COALESCE(t5.Patronymic, '')) = LOWER(COALESCE(t1.OT, ''))
          AND t5.SNILS = t1.SNILS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC, t3.OmsType DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID" 
		--AND type_ = 693 
		AND t1.iip IS NULL;

	-- step 6
    UPDATE ies.T_SCHET_ZAP t1
      SET iip = (
          SELECT t2.InsuredPolicyID
          FROM ies.T_INSURED_POLICY t2
          JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
          JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
          WHERE LOWER(t5.LastName) = LOWER(t1.FAM)
            AND LOWER(COALESCE(t5.Patronymic, '')) = LOWER(COALESCE(t1.OT, ''))
            AND t2.Birhday = t1.DR  
       		AND t5.SNILS = t1.SNILS 
            AND t2.DateBegin <= t1.mindate
            AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
          ORDER BY t2.DateBegin DESC, t3.OmsType DESC
          LIMIT 1
      )
      WHERE t1.Schet = "SchetID" 
      --AND type_ = 693 
      AND t1.iip IS NULL;

    -- step 7
    UPDATE ies.T_SCHET_ZAP t1
      SET iip = (
          SELECT t2.InsuredPolicyID
          FROM ies.T_INSURED_POLICY t2
          JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
          JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
          WHERE LOWER(t5.FirstName) = LOWER(t1.IM)
            AND LOWER(COALESCE(t5.Patronymic, '')) = LOWER(COALESCE(t1.OT, ''))
            AND t5.Birthday = t1.DR  
        	AND t5.SNILS = t1.SNILS 
            AND t2.DateBegin <= t1.mindate
            AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
          ORDER BY t2.DateBegin DESC, t3.OmsType DESC
          LIMIT 1
      )
      WHERE t1.Schet = "SchetID" 
      --AND type_ = 693 
      AND t1.iip IS NULL;

    -- step 8
    UPDATE ies.T_SCHET_ZAP t1
      SET iip = (
          SELECT t2.InsuredPolicyID
          FROM ies.T_INSURED_POLICY t2
          JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
          JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
          WHERE LOWER(t5.LastName) = LOWER(t1.FAM)
        	AND LOWER(t5.FirstName) = LOWER(t1.IM)
            AND t5.Birthday = t1.DR  
        	AND t5.SNILS = t1.SNILS 
            AND t2.DateBegin <= t1.mindate
            AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
          ORDER BY t2.DateBegin DESC, t3.OmsType DESC
          LIMIT 1
      )
      WHERE t1.Schet = "SchetID" 
      --AND type_ = 693 
      AND t1.iip IS NULL;

    -- step 9
    UPDATE ies.T_SCHET_ZAP t1
      SET iip = (
          SELECT t2.InsuredPolicyID
          FROM ies.T_INSURED_POLICY t2
          JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
          JOIN ies.T_INSURED t5 ON t2.Insured = t5.InsuredID
          WHERE LOWER(t5.LastName) = LOWER(t1.FAM)
        	AND LOWER(t5.FirstName) = LOWER(t1.IM)
            AND t5.Birthday = t1.DR  
        	AND t5.SNILS = t1.SNILS 
            AND t2.DateBegin <= t1.mindate
            AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
          ORDER BY t2.DateBegin DESC, t3.OmsType DESC
          LIMIT 1
      )
      WHERE t1.Schet = "SchetID" 
      --AND type_ = 693 
      AND t1.iip IS NULL;

    
    -- СНИЛС - ФАМИЛИЯ - ИМЯ - ОТЧЕСТВО ДР--
    -- УДЛ - ФИО - ДР для местных - 
    
	  -- step 10
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t31 ON t31.PolicyID = t2.Policy
        JOIN ies.T_DOCUMENT t3 ON t3.DocumentID = t2.Document
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
          AND LOWER(t2.FirstName) = LOWER(t1.IM)
          AND LOWER(t2.Patronymic) = LOWER(t1.OT)
          AND t3.DocNumber = t1.DOCNUM
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL
      AND LENGTH(t1.DOCNUM) > 0;
      --AND type_ = 693;

    -- step 11
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t31 ON t31.PolicyID = t2.Policy
        JOIN ies.T_DOCUMENT t3 ON t3.DocumentID = t2.Document
        WHERE LOWER(t2.FirstName) = LOWER(t1.IM)
          AND LOWER(t2.Patronymic) = LOWER(t1.OT)
          AND t2.Birhday = t1.DR
          AND t3.DocNumber = t1.DOCNUM
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL
      AND LENGTH(t1.DOCNUM) > 0;
      --AND type_ = 693;

      -- step 12
      UPDATE ies.T_SCHET_ZAP t1
      SET iip = (
          SELECT t2.InsuredPolicyID
          FROM ies.T_INSURED_POLICY t2
          JOIN ies.T_POLICY t31 ON t31.PolicyID = t2.Policy
          JOIN ies.T_DOCUMENT t3 ON t3.DocumentID = t2.Document
          WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
            AND LOWER(t2.Patronymic) = LOWER(t1.OT)
            AND t2.Birhday = t1.DR
            AND t3.DocNumber = t1.DOCNUM
            AND t2.DateBegin <= t1.mindate
            AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
          ORDER BY t2.DateBegin DESC
          LIMIT 1
      )
      WHERE t1.Schet = "SchetID"
        AND t1.iip IS NULL
        AND LENGTH(t1.DOCNUM) > 0;
        --AND type_ = 693;

    -- step 13
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t31 ON t31.PolicyID = t2.Policy
        JOIN ies.T_DOCUMENT t3 ON t3.DocumentID = t2.Document
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
          AND LOWER(t2.FirstName) = LOWER(t1.IM)
          AND t2.Birhday = t1.DR
          AND t3.DocNumber = t1.DOCNUM
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL
      AND LENGTH(t1.DOCNUM) > 0;
      --AND type_ = 693;

	  -- УДЛ - ФИО - ДР для местных - 
	  -- NPOLIS ФИО ДР--
		
    -- step 14
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
          AND LOWER(t2.FirstName) = LOWER(t1.IM)
          AND LOWER(t2.Patronymic) = LOWER(t1.OT)
          AND COALESCE(t3.Series, '') || t3.Number = COALESCE(t1.SPOLIS, '') || t1.NPOLIS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL;
      --AND type_ = 693;

    -- step 15
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.FirstName) = LOWER(t1.IM)
          AND LOWER(t2.Patronymic) = LOWER(t1.OT)
          AND t2.Birhday = t1.DR
          AND COALESCE(t3.Series, '') || t3.Number = COALESCE(t1.SPOLIS, '') || t1.NPOLIS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL;
      --AND type_ = 693;

    -- step 16
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.LastName) = LOWER(t1.FAM)
          AND LOWER(t2.Patronymic) = LOWER(t1.OT)
          AND t2.Birhday = t1.DR
          AND COALESCE(t3.Series, '') || t3.Number = COALESCE(t1.SPOLIS, '') || t1.NPOLIS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL;
      --AND type_ = 693;

    -- step 17
    UPDATE ies.T_SCHET_ZAP t1
    SET iip = (
        SELECT t2.InsuredPolicyID
        FROM ies.T_INSURED_POLICY t2
        JOIN ies.T_POLICY t3 ON t3.PolicyID = t2.Policy
        WHERE LOWER(t2.FirstName) = LOWER(t1.IM)
          AND t2.Birhday = t1.DR
          AND COALESCE(t3.Series, '') || t3.Number = COALESCE(t1.SPOLIS, '') || t1.NPOLIS
          AND t2.DateBegin <= t1.mindate
          AND (t2.DateStop >= t1.mindate OR t2.DateStop IS NULL)
        ORDER BY t2.DateBegin DESC
        LIMIT 1
    )
    WHERE t1.Schet = "SchetID"
      AND t1.iip IS NULL;
      --AND type_ = 693;

	
    return;
end
$BODY$;