do
$$
declare
	cur_head refcursor;
	cur_det refcursor;
	preg record;
	pitem record;
	errmsg text;
	item_id int;
	item_cat_id int;
	status_id int;
	br_id int;
	br_status int;
	br_det_count int;
	br_det_id int = :br_det_id;
	src_id int = 1; --Billing Register
	bill_type_id int = 1; --Vat Bill
begin

	open cur_head for
		select prd."BillDate",
			prd."InvoiceNo", prd."SellerName", prd."SellerPAN",
			br."EntryBy", br."BranchId", prd."PPNO",
			br."FiscalYearId", br."MonthId",
			br."BusinessPanNo",
			fy."FiscalYearEng"
				|| br."BusinessPanNo"
				|| 'BBBR'
				|| lpad(nextval('"BillingRegister".seq_sales_tran')::varchar(10), 10, '0')
				as "TranNo",
			0 as "TotalAmount",
			0 as "TotalTaxableAmount",
			0 as "TotalTaxAmount",
			0 as "TotalTaxableImportAmount",
			0 as "TotalTaxExemptAmount"
			-- sum(coalesce(replace(prd."TotalAmount", ',','')::numeric, 0)) as "TotalAmount",
			-- sum(coalesce(replace(prd."TaxableAmount", ',', '')::numeric, 0)) as "TotalTaxableAmount",
			-- sum(coalesce(NULLIF(regexp_replace(prd."TaxAmount", 'D','','g'), '')::numeric, 0)) as "TotalTaxAmount",
			-- sum(coalesce(NULLIF(regexp_replace(prd."TaxableImportAmount", 'D','','g'), '')::numeric, 0)) as "TotalTaxableImportAmount",
			-- sum(coalesce(NULLIF(regexp_replace(prd."TaxExemptAmount", 'D','','g'), '')::numeric, 0)) as "TotalTaxExemptAmount"
		from "BillingRegister"."PurchaseRegisterDumps" prd
		join "BillingRegister"."BillingRegisterDetails" brd
			on brd."Id" = prd."BillingRegisterDetailId"
		join "BillingRegister"."BillingRegisters" br
			on brd."BillingRegisterId" = br."Id"
		join "BillingRegister"."FiscalYears" fy
			on fy."Id" = br."FiscalYearId"
		where prd."BillingRegisterDetailId" = :br_det_id
		group by prd."BillDate", prd."InvoiceNo", prd."SellerName",
			prd."SellerPAN", br."EntryBy", br."BranchId", prd."PPNO",
			br."FiscalYearId", br."MonthId", br."BusinessPanNo", fy."FiscalYearEng";
	loop
		fetch cur_head into preg;
		exit when not found;
		begin
			insert into "BillingRegister"."PurchaseRegisters"
			(
				"BillDate",
				"BillDateNep",
				"InvoiceNo",
				"SellerName",
				"SellerPAN",
				"EntryBy",
				"BranchId",
				"PPNo",
				"FiscalYearId",
				"MonthId",
				"BillingDataSourceId",
				"ImportedData",
				"TranNo",
				"TotalAmount",
				"TotalPayableAmount",
				"TotalTaxAmount",
				"TotalTaxExemptAmount",
				"TotalTaxableAmount",
				"BRDetId",
				"TotalTaxableImportAmount",
				"UUID",
				"EntryDate"
			) values (
				get_eng_date(preg."BillDate"),
				preg."BillDate",
				preg."InvoiceNo",
				preg."SellerName",
				preg."SellerPAN",
				preg."EntryBy",
				preg."BranchId",
				preg."PPNO",
				preg."FiscalYearId",
				preg."MonthId",
				src_id,
				true,
				preg."TranNo",
				coalesce(replace(preg."TotalAmount", ',', '')::numeric, 0),
				coalesce(replace(preg."TotalAmount", ',', '')::numeric, 0)
					+ coalesce(replace(preg."TotalTaxAmount", ',', '')::numeric, 0),
				coalesce(replace(preg."TotalTaxAmount", ',', '')::numeric, 0),
				coalesce(replace(preg."TotalTaxExemptAmount", ',', '')::numeric, 0),
				coalesce(replace(preg."TotalTaxableAmount", ',', '')::numeric, 0),
				br_det_id,
				coalesce(replace(preg."TotalTaxableImportAmount", ',', '')::numeric, 0),
				uuid_generate_v4(),
				now()
			);
			exception when others then
				update "BillingRegister"."PurchaseRegisterDumps"
				set "StatusId" = 2,
				"Issues" = case
						when "Issues" is null
						then sqlerrm || ' (' || sqlstate || ')'
						else "Issues" || ', ' || sqlerrm || ' (' || sqlstate || ')'
					end
				where "BillingRegisterDetailId" = br_det_id
				and "InvoiceNo" = preg."InvoiceNo";
		end;
	end loop;
	close cur_head;

	/*** TODO: insert into items here instead of below ***/

	open cur_det for
		select
			prd."ItemName", coalesce(prd."Quantity", ',', '') as "Quantity", prd."TotalAmount",
			prd."TaxExemptAmount", prd."TaxableAmount",
			prd."TaxAmount", prd."TaxableImportAmount",
			prd."TaxableImportTax", prd."TaxableCapitalGoodsAmount",
			prd."TaxableCapitalGoodsTax", sr."Id", i."Id" as item_id,
			b."PAN", prd."Id" as prd_id
		from "BillingRegister"."PurchaseRegisterDumps" prd
		join "BillingRegister"."BillingRegisterDetails" brd
			on brd."Id" = prd."BillingRegisterDetailId"
		join "BillingRegister"."BillingRegisters" br
			on brd."BillingRegisterId" = br."Id"
		join "BillingRegister"."PurchaseRegisters" sr
			on sr."BranchId" = br."BranchId"
			and sr."InvoiceNo" = prd."InvoiceNo"
		join "BillingRegister"."Branches" b
			on b."Id" = br."BranchId"
		left outer join "BillingRegister"."Items" i
			on i."PAN" = b."PAN"
			and i."NameEng" = prd."ItemName"
		where prd."BillingRegisterDetailId" = :br_det_id
		and prd."StatusId" is null;
	loop
		fetch cur_det into pitem;
		exit when not found;
		begin
			errmsg := null;
			insert into public.test (entrydate, msg) values (now(), 'test');
			if coalesce(replace(pitem."TaxAmount", ',', ''), '') ~ '^[0-9\.]*$' then
				if coalesce(replace(pitem."TaxAmount", ',', '')::numeric, 0) = 0 then
					item_cat_id := 1;
				else
					item_cat_id := 2;
				end if;
			else
				errmsg := coalesce(errmsg, '') || ', TaxAmount should be number';
			end if;
			if item_cat_id = 2 and
				round(coalesce(NULLIF(regexp_replace(pitem."TaxableAmount", 'D','','g'), '')::numeric, 0) * 0.13, 2)
				<> round(coalesce(NULLIF(regexp_replace(pitem."TaxAmount", 'D','','g'), '')::numeric, 0), 2) then
					errmsg := coalesce(errmsg, '') || ', Incorrect Tax Amount';
			else
				item_id := (select "Id" from "BillingRegister"."Items" where "PAN" = pitem."PAN" and "NameEng" = pitem."ItemName");
				if pitem.item_id is null and item_id is null then
					insert into "BillingRegister"."Items"
						("NameEng", "NameNep", "ItemSubCategoryId", "PAN")
					values
						(pitem."ItemName", pitem."ItemName", item_cat_id, pitem."PAN")
					returning "Id" into item_id;
				end if;
				begin
					insert into "BillingRegister"."PurchaseItems"
					(
						 "ItemId",
						 "Quantity",
						 "TotalAmount",
						 "TaxExemptAmount",
						 "TaxableAmount",
						 "TaxAmount",
						 "TaxableImportAmount",
						 "TaxableImportTax",
						 "TaxableCapitalGoodsAmount",
						 "TaxableCapitalGoodsTax",
						 "PurchaseRegisterId",
						 "TotalPayableAmount",
						 "Rate",
						 "PurchaseTypeId"
					)
					values
					(
						coalesce(pitem.item_id, item_id),
						coalesce(NULLIF(regexp_replace(pitem."Quantity", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TotalAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxExemptAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxableAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxableImportAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxableImportTax", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxableCapitalGoodsAmount", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TaxableCapitalGoodsTax", 'D','','g'), '')::numeric, 0),
						pitem."Id",
						coalesce(NULLIF(regexp_replace(pitem."TotalAmount", 'D','','g'), '')::numeric, 0) / coalesce(NULLIF(regexp_replace(pitem."Quantity", 'D','','g'), '')::numeric, 0),
						coalesce(NULLIF(regexp_replace(pitem."TotalAmount", 'D','','g'), '')::numeric, 0) + coalesce(NULLIF(regexp_replace(pitem."TaxAmount", 'D','','g'), '')::numeric, 0),
						case coalesce(NULLIF(regexp_replace(pitem."TaxableCapitalGoodsAmount", 'D','','g'), '')::numeric, 0)
							when 0 then 1
							else 2
						end
					);
				/*
					exception when others then
						errmsg := sqlerrm || ' (' || sqlstate || ')';
						*/
				end;
			end if;
			if errmsg is not null then
				update "BillingRegister"."PurchaseRegisterDumps"
				set "StatusId" = 2,
				"Issues" = case
						when "Issues" is null
						then errmsg
						else "Issues" || ', ' || errmsg
					end
				where "Id" = pitem."prd_id";
			else
				update "BillingRegister"."PurchaseRegisterDumps"
				set "StatusId" = 1
				where "Id" = pitem."prd_id"
				and "StatusId" is null;
			end if;
		end;
	end loop;
	close cur_det;

	if (
		select count("Id")
		from "BillingRegister"."PurchaseRegisterDumps"
		where "BillingRegisterDetailId" = br_det_id
		and "StatusId" = 2
	) > 0 then
		status_id := 5;
		-- TODO: later do validation and then insert instead of insert and validate

		delete from "BillingRegister"."PurchaseItems" si
		where "Id" in (
			select ssi."Id"
			from "BillingRegister"."PurchaseItems" ssi
			join "BillingRegister"."PurchaseRegisters" sr
				on sr."Id" = ssi."PurchaseRegisterId"
			where sr."BRDetId" = br_det_id
		);

		delete from "BillingRegister"."PurchaseRegisters" sr
		where "BRDetId" = br_det_id;

	else
		status_id := 4;
		delete from "BillingRegister"."PurchaseRegisterDumps"
		where "BillingRegisterDetailId" = br_det_id;
	end if;

	update "BillingRegister"."BillingRegisterDetails"
	set "StatusId" = status_id
	where "Id" = br_det_id;
	select "BillingRegisterId" into br_id
	from "BillingRegister"."BillingRegisterDetails"
	where "Id" = br_det_id;

	select count("Id") into br_det_count
	from "BillingRegister"."BillingRegisterDetails"
	where "BillingRegisterId" = br_id;

	if (
		select count("Id")
		from "BillingRegister"."BillingRegisterDetails"
		where "BillingRegisterId" = br_id
		and "StatusId" = 1
	) > 0 then
		br_status := 1;
	elsif (
		select count("Id")
		from "BillingRegister"."BillingRegisterDetails"
		where "BillingRegisterId" = br_id
		and "StatusId" = 2
	) > 0 then
		br_status := 2;
	elsif (
		select count("Id")
		from "BillingRegister"."BillingRegisterDetails"
		where "BillingRegisterId" = br_id
		and "StatusId" = 3
	) > 0 then
		br_status := 3;
	elsif (
		select count("Id")
		from "BillingRegister"."BillingRegisterDetails"
		where "BillingRegisterId" = br_id
		and ("StatusId" = 4 or "StatusId" = 7)
	) = br_det_count then
		br_status := 4;
	else
		br_status := 5;
	end if;

	update "BillingRegister"."BillingRegisters"
	set "BRStatusId" = br_status
	where "Id" = br_id;
end;
$$;