DROP FUNCTION IF EXISTS public.sp_schetoutidrecalc;
DROP PROCEDURE IF EXISTS public.sp_schetoutidrecalc;

CREATE PROCEDURE public.sp_schetoutidrecalc("SchetID" UUID)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
	schet_kind INT = (SELECT schetkind FROM ies.t_schet WHERE schetid = "SchetID");
BEGIN
	DELETE FROM ies.t_schet_sluch AS ss
	USING 
		ies.t_schet_zap AS sz
	INNER JOIN ies.t_schet_sluch_accomplished AS ssa 
		ON ssa.schetzap = sz.schetzapid
	WHERE 
		sz.schet = "SchetID"
	  	AND ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		AND ss.isdelete = 1
	;
	   
	DELETE FROM ies.t_schet_sluch_accomplished AS ssa
	USING ies.t_schet_zap AS sz
	WHERE
		sz.schet = "SchetID"
		AND ssa.schetzap = sz.schetzapid
		AND NOT EXISTS (SELECT * FROM ies.t_schet_sluch AS ss WHERE ss.schetzap = sz.schetzapid AND ss.isdelete = 0)
	;
	   
	DELETE FROM ies.t_schet_zap AS sz
	WHERE 
		sz.schet = "SchetID"
	  	AND NOT EXISTS (SELECT * FROM ies.t_schet_sluch AS ss WHERE ss.schetzap = sz.schetzapid AND ss.isdelete = 0)
	;

	IF schet_kind = 2 THEN
		UPDATE ies.t_schet_sluch AS ss
		SET 
			sl_id = ssmain.sl_id,
			idcase = ssmain.idcase,
			schetsluchmain = ssmain.schetsluchid
		FROM
			ies.t_schet_zap AS sz
		INNER JOIN
			ies.t_schet AS s
			ON sz.schet = s.schetid
		INNER JOIN
			ies.t_schet AS smain
			ON s.schetmain = smain.schetid
			AND smain.schetkind = 0
			AND smain.isdelete = 0
		INNER JOIN
			ies.t_schet_zap AS szmain
			ON smain.schetid = szmain.schet
		INNER JOIN
			ies.t_schet_sluch AS ssmain
			ON ssmain.schetzap = szmain.schetzapid
			AND ssmain.omsschetsluch = ss.omsschetsluch
			AND ssmain.isdelete = 0
		WHERE
			ss.schetzap = sz.schetzapid
			AND s.schetid = "SchetID"
			AND ss.isdelete = 0
		;

		UPDATE ies.schet_zap AS sz
		SET
			n_zap = szmain.n_zap
		FROM
			ies.t_schet_sluch AS ss
		INNER JOIN
			ies.t_schet AS s
			ON sz.schet = s.schetid
		INNER JOIN
			ies.t_schet_sluch AS ssmain
			ON ssmain.schetsluchid = ss.schetsluchmain
		INNER JOIN
			ies.t_schet_zap AS szmain
			ON ssmain.schetzap = szmain.schetzapid
		WHERE
			sz.schetzapid = ss.schetzap
			AND s.schetid = "SchetID"
			AND ss.isdelete = 0
		;

		UPDATE ies.schet_sluch_accomplished AS ssa
		SET
			idcase = ssamain.idcase
		FROM
			ies.schet_zap AS sz
		INNER JOIN
			ies.schet_sluch AS ss
			ON sz.schetzapid = ss.schetzap
		INNER JOIN
			ies.t_schet AS s
			ON s.schetid = sz.schet
		INNER JOIN
			ies.t_schet_sluch as ssmain
			ON ssmain.schetsluchid = ss.schetsluchmain
		INNER JOIN
			ies.t_schet_sluch_accomplished AS ssamain
			ON ssamain.schetsluchaccomplishedid = ssmain.schetsluchaccomplished
		WHERE
			ssa.schetsluchaccomplishedid = ss.schetsluchaccomplished
			AND s.schetid = "SchetID"
			AND ss.isdelete = 0
		;
		
	ELSEIF schet_kind = 0 THEN
		WITH new_n_zap AS (
			SELECT
				ROW_NUMBER() OVER(
					ORDER BY
						fam,
						im,
						ot,
						dr,
						schetzapid
				) AS id_,
				schetzapid
			FROM ies.t_schet_zap
			WHERE
				schet = "SchetID"
				AND isdelete = 0
		)
		UPDATE ies.t_schet_zap AS sz
		SET n_zap = new_n_zap.id_
		FROM new_n_zap
		WHERE
			sz.schetzapid = new_n_zap.schetzapid
			AND sz.isdelete = 0
		;

		WITH new_idcase AS (
			SELECT
				ROW_NUMBER() OVER(
					ORDER BY 
						sz.n_zap,
						ssa.date_z_1,
						ssa.schetsluchaccomplishedid
				) AS id_,
				ssa.schetsluchaccomplishedid
			FROM
				ies.t_schet_sluch_accomplished AS ssa
			INNER JOIN
				ies.t_schet_zap AS sz
				ON sz.schetzapid = ssa.schetzap
			WHERE
				sz.schet = "SchetID"
				AND ssa.isdelete = 0
		)
		UPDATE ies.t_schet_sluch_accomplished AS ssa
		SET
			idcase = new_idcase.id_
		FROM
			new_idcase
		WHERE
			ssa.schetsluchaccomplishedid = new_idcase.schetsluchaccomplishedid
			AND ssa.isdelete = 0
		;

		WITH new_id AS (
			SELECT
				ROW_NUMBER() OVER (
					ORDER BY
						sz.n_zap,
						ss.date_1,
						ss.schetsluchid
				) AS id_,
				ss.schetsluchid
			FROM
				ies.t_schet_sluch AS ss
			INNER JOIN
				ies.t_schet_zap AS sz
				ON sz.schetzapid = ss.schetzap
			WHERE
				sz.schet = "SchetID"
				AND ss.isdelete = 0
		)
		UPDATE ies.t_schet_sluch AS ss
		SET
			sl_id = new_id.id_,
			idcase = new_id.id_
		FROM
			new_id
		WHERE
			ss.schetsluchid = new_id.schetsluchid
			AND ss.isdelete = 0
		;

		DROP TABLE IF EXISTS temp_sanks;

		
		CREATE TEMP TABLE temp_sanks AS 
		WITH sanks AS (
			SELECT
				COALESCE(sss.schetsluchsankid, gen_random_uuid()) AS schetsluchsankid,
				ss.schetsluchaccomplished AS mtrschetsluchaccomplished,
				ss.schetsluchid AS mtrschetsluchid,
				mr.n_expert,
				oms.schetsluchid,
				f006.idvid,
				er.medicalerrorid,
				er.denyreason,
				ROW_NUMBER() OVER (PARTITION BY f006.idvid, ss.schetsluchaccomplished ORDER BY mr.n_expert DESC) AS id_
			FROM
				ies.t_schet AS s
			INNER JOIN ies.t_schet_zap AS sz ON sz.schet = s.schetid AND sz.isdelete = 0
			INNER JOIN ies.t_schet_sluch AS ss ON ss.schetzap = sz.schetzapid AND ss.isdelete = 0
			INNER JOIN ies.t_schet_sluch AS oms ON oms.schetsluchid = ss.omsschetsluch AND oms.isdelete = 0
			INNER JOIN ies.t_medical_expertise_error AS mee ON mee.schetsluch = oms.schetsluchid AND mee.istaking = 1
			INNER JOIN ies.t_medical_expertise AS me ON me.medicalexpertiseid = mee.medicalexpertise
			INNER JOIN ies.t_f006_control_type AS f006 ON f006.f006controltypeid = me.f006controltype
			LEFT JOIN LATERAL (SELECT sss1.schetsluchsankid
								FROM ies.t_schet_sluch_sank AS sss1
								WHERE sss1.schetsluch = ss.schetsluchid
									AND sss1.s_tip = f006.idvid
								LIMIT 1
							) AS sss ON True 
			LEFT JOIN LATERAL (SELECT 	mri1.*
								FROM ies.t_medical_request_item AS mri1
								WHERE mri1.schetsluchaccomplished = oms.schetsluchaccomplished
								LIMIT 1
							) AS mri ON True
			LEFT JOIN LATERAL (SELECT mr1.*, f042.n_expert
									FROM ies.t_medical_request AS mr1
									left join ies.t_f042_trekmp f042 on f042.f042trekmpid = mr1.f042Expert
									WHERE mr1.medicalrequestid = mri.medicalrequest
										AND mr1.medicalexpertise = me.medicalexpertiseid
									LIMIT 1
								) AS mr ON True 
			LEFT JOIN LATERAL (SELECT ma1.*
								FROM ies.t_medical_act AS ma1
								WHERE ma1.medicalrequest = mr.medicalrequestid
								LIMIT 1
							) AS ma ON True
			LEFT JOIN LATERAL (SELECT er1.*
								FROM ies.t_medical_error AS er1
								WHERE er1.medicalrequest = mr.medicalrequestid
									AND er1.ismainerror = 1
								LIMIT 1
							) AS er ON True 	
			WHERE
				sz.schet = "SchetID"
				AND ma.medicalactid IS NULL
				AND s.isdelete = 0
				AND s.schetkind = 0
				AND ((s.year = 2022 AND s.month >= 11) OR s.year > 2022)
		)
		SELECT sanks.*
		FROM sanks
		WHERE sanks.id_ = 1
		;

		WITH deny_reasons AS (SELECT f014denyreasonid, osn
								FROM ies.t_f014_deny_reason	
							),
			idvid_osn_map AS (SELECT *
							FROM (VALUES
							('73', '35.3'),
					        ('84', '37.6'),
					        ('78', '35.8'),
					        ('58', '22.7'),
					        ('83', '37.5'),
					        ('76', '35.6'),
					        ('72', '35.2'),
					        ('81', '37.2'),
					        ('77', '35.7'),
					        ('71', '35.1'),
					        ('80', '37.2'),
					        ('53', '22.2'),
					        ('82', '37.4'),
					        ('75', '35.5'),
					        ('56', '22.5'),
					        ('79', '37.1'),
					        ('74', '35.4'),
					        ('92', '22.4'),
					        ('90', '22.1'),
					        ('89', '37.7'),
					        ('93', '22.6'),
					        ('88', '22.8'),
					        ('91', '22.3')
						) AS t(idvid, osn)
					),
		control_types AS (SELECT  idvid, f006controltypeid
							FROM ies.t_f006_control_type
							WHERE migratedentityname IS NULL
						),
		prepared_data AS (SELECT sanks.schetsluchsankid,
								ss.schetsluchid,
								sanks.schetsluchsankid AS s_code,
								0 AS s_sum,
								sanks.idvid,
								5 AS s_ist,
								sanks.medicalerrorid,
								COALESCE(dr.f014denyreasonid, sanks.denyreason) AS s_osn,
								ss.schetsluchaccomplished,
								ct.f006controltypeid AS s_tip_f006
						FROM temp_sanks AS sanks
						INNER JOIN ies.t_schet_sluch AS ss ON ss.schetsluchid = sanks.mtrschetsluchid AND ss.isdelete = 0
						LEFT JOIN  idvid_osn_map AS iom ON iom.idvid = sanks.idvid
						LEFT JOIN deny_reasons AS dr ON dr.osn = iom.osn
						LEFT JOIN control_types AS ct ON ct.idvid = sanks.idvid
						WHERE NOT EXISTS(SELECT *
										FROM ies.t_schet_sluch_sank AS sss
										WHERE sss.schetsluchaccomplished = ss.schetsluchaccomplished
											AND sss.s_ist = 5
											AND sss.s_sum = 0
											AND sss.isdelete = 0
									)
					)
		INSERT INTO ies.t_schet_sluch_sank (
			schetsluchsankid,
			schetsluch,
			s_code,
			s_sum,
			s_tip,
			s_ist,
			medicalerror,
			s_osn,
			schetsluchaccomplished,
			s_tip_f006
		)
		SELECT
			schetsluchsankid,
			schetsluchid,
			s_code,
			s_sum,
			idvid,
			s_ist,
			medicalerrorid,
			s_osn,
			schetsluchaccomplished,
			s_tip_f006
		FROM prepared_data
		;

		INSERT INTO ies.t_schet_sluch_sank_exp(
			schetsluchsankexpid,
			schetsluchsank,
			f004expert
		)
		SELECT
			sanks.schetsluchsankid,
			sss.schetsluchsankid,
			sanks.n_expert
		FROM
			temp_sanks AS sanks
		INNER JOIN
			ies.t_schet_sluch_sank AS sss
			ON sanks.mtrschetsluchid = sss.schetsluch
			AND sanks.schetsluchsankid = sss.schetsluchsankid
			AND sss.isdelete = 0
		WHERE
			sanks.n_expert IS NOT NULL
			AND NOT EXISTS (
				SELECT *
				FROM ies.t_schet_sluch_sank_exp AS se
				WHERE se.schetsluchsank = sss.schetsluchsankid
			)
		;
	END IF;
END;
$BODY$;

