namespace :exodus_migration do

	task :active_accounts => :environment do
		start_date = Date.new(2013,1,1)
		end_date = Date.new(2026,7,14)
		i = 0
		CustomerAccount.where(deleted: false, registration_date: start_date..end_date).where.not(sale_type_id: 3).where("project_id IN(1,2)").includes(:stand, :transactions, :suspense_accounts).find_each(batch_size: 500) do |customer_account|
		# CustomerAccount.where(deleted: false, id: 4112).where.not(sale_type_id: 3).includes(:stand, :transactions, :suspense_accounts).find_each(batch_size: 500) do |customer_account|
  			receipt_1 = customer_account.transactions.order(transaction_date: :asc).where(transaction_type_id: 3, posted: false, suspense: true, deleted: false, payment: true).includes(:payment_title)
  			receipt_2 = customer_account.transactions.order(transaction_date: :asc).where(transaction_type_id: 2, posted: false, suspense: false, deleted: false, payment: true).includes(:payment_title)
  			receipts = receipt_1 + receipt_2
  			allocations = customer_account.transactions.order(transaction_date: :asc).where(transaction_type_id: 2, posted: false, deleted: false, payment: false, suspense: true).includes(:payment_title)
  			interests = customer_account.transactions.order(transaction_value_date: :asc).where(transaction_type_id: 2, posted: false, deleted: false).where("interest > 0").includes(:payment_title)
  			suspense_transactions = customer_account.suspense_accounts.order(transaction_date: :asc).where(posted: false).includes(:payment_title)
  			payment_title_ids = [1,2]
  			fees = customer_account.transactions.where("payment_title_id NOT IN (?) AND deleted = ? AND transaction_type_id = ?", payment_title_ids,false,1)
  			total_receipts = receipts.sum{|r| r.amount}
  			# get interests charged from allocations
  			interests.each do |allocation|
  				puts("#{allocation.transaction_date} - #{allocation.interest}")
  				customer_account.customer_account_invoices.create(
					payment_title_id: allocation.payment_title_id,
					transaction_date: allocation.transaction_value_date,
					due_date: allocation.transaction_value_date,
					month: allocation.transaction_value_date&.strftime("%B"),
					year: allocation.transaction_value_date&.strftime("%Y"),
					description: allocation&.payment_title&.name,
					interest: allocation.interest,
					interest_arrears: -(allocation.interest),
					principal: allocation.principal,
					principal_arrears: -(allocation.principal),
					loan_balance: allocation.loan_balance,
					amount_due: allocation.amount,
					debit: true,
					reversal: false,
					deleted: allocation.deleted,
					agent_id: allocation&.customer_account&.agent_id,
					general_plan_id: allocation&.customer_account&.general_plan_id,
					project_id: allocation&.customer_account&.project_id
  				)
  			end
  			puts("***** total interests amount to #{interests.sum{|n| n.interest}}")
  			puts("*** Start of Direct receipts")
  			receipts.each do |transaction|
  				puts("#{transaction.transaction_date},#{transaction.amount}")
  				## fields that matches receipt and suspense account (transaction_date,amount,receipt_number,reference_number,posted)
				begin
				  suspense_account = customer_account.suspense_accounts.find_or_create_by!(
				   reference_number: transaction.transaction_code,
				   posted: true,
				   amount: transaction.amount
				  ) do |s|
				    s.amount = transaction.usd_amount
				    s.amount_deducted = transaction.amount_deducted
				    s.balance = transaction.amount
				    s.month = transaction.transaction_date&.strftime("%B")
				    s.year = transaction.transaction_date&.strftime("%Y")
				    s.payment_title_id = transaction.payment_title_id
				    s.payment_type_id = transaction.payment_type_id
				    s.currency_id = transaction.currency_id
				    s.rate = transaction.rate
				    s.posted = true
				    s.paid = false
				    s.reversed = false
				    s.receipt_number = transaction.reference_number
				    s.reference_number = transaction.transaction_code
				    s.payment_category_id = transaction.payment_category_id
				    @last = SuspenseAccount.last
				    if @last.nil?
				    s.transaction_code = 'S001'
				    else
				    s.transaction_code = @last.transaction_code.next
				    end
				    s.transaction_date = transaction.transaction_date
				    s.user_id = transaction.user_id
				    s.legal = transaction.legal
				    s.provider = transaction.provider
				    s.online_payment = transaction.online_payment
				    s.integrated = transaction.integrated
				    s.journal = transaction.journal 
				    s.journal_reference = transaction.journal_reference
				    s.journal_description = transaction.journal_description
				    s.authorised = true
				    s.status = 'APPROVED'
				  end

				  if suspense_account.previously_new_record?
				    puts "✓ Created Suspense transaction for Account #{customer_account.account_number}, with reference number #{suspense_account.reference_number}"
				  	transaction.update!(transaction_type_id: 3, suspense: true, payment: true)
				  else
				  	suspense_account.update!(receipt_number: transaction.receipt_number, paid: false, status: 'APPROVED', authorised: true)
				  	transaction.update!(transaction_type_id: 3, suspense: true, payment: true)
				    puts "→ Suspense Transaction already exists: ##{suspense_account.id}"
				  end

				rescue ActiveRecord::RecordInvalid => e
				  puts "✗ Failed to create suspense transaction with reference #{transaction.transaction_code}"
				  puts e.record.errors.full_messages.join(", ")
				end  				
  			end
  			puts("Total receipts paid #{total_receipts}")

  			# # get receipts from payments that where directly posted to suspense account with no matching transaction
			# customer_account.suspense_accounts
			#   .where(posted: true)
			#   .where.not(
			#     reference_number: customer_account.transactions
			#       .where(deleted: false, transaction_type_id: 3)
			#       .select(:transaction_code)
			#   ).each do |suspense_receipt|
			#   	begin
			#   		puts("*** #{suspense_receipt.reference_number} - #{suspense_receipt.amount}")
			#   		transaction = customer_account.transactions.find_or_create_by!(
			#   			transaction_code: suspense_receipt.reference_number, 
			#   			amount: suspense_receipt.amount, 
			#   			transaction_date: suspense_receipt.transaction_date, 
			#   			posted: false) do |t|
			#   			t.transaction_date = suspense_receipt.transaction_date
			#   			t.month = suspense_receipt.transaction_date&.strftime("%B")
			#   			t.year = suspense_receipt.transaction_date&.strftime("%Y")
			#   			t.loan_balance = 0
			#   			t.

			#   		end

			#   end

  			# get suspense without matching transactions
			puts "*** Start of Posted to Suspense with unmatching transaction"

			deleted_count = customer_account.suspense_accounts
			  .where(posted: true)
			  .where.not(
			    reference_number: customer_account.transactions
			      .where(deleted: false, transaction_type_id: 3)
			      .select(:transaction_code)
			  )
			  .update_all(deleted: true)

			puts "Updated #{deleted_count} orphaned suspense accounts."
  			
  			puts("**** Start Fees")
			f = 0
			fees.find_each do |fee|
			  puts "#{f += 1},#{fee.transaction_date},#{fee.payment_title&.name},#{fee.fee_due},#{fee.receipt_number}"

			  begin
			    invoice = customer_account.customer_account_invoices.find_or_create_by!(
			      payment_title_id: fee.payment_title_id,
			      transaction_date: fee.transaction_date,
			      amount: fee.fee_due,
			      debit: true
			    ) do |i|
			      i.assign_attributes(
			        due_date: fee.transaction_date,
			        month: fee.transaction_date&.strftime("%B"),
			        year: fee.transaction_date&.strftime("%Y"),
			        description: fee.payment_title&.name,
			        interest: 0,
			        interest_arrears: 0,
			        principal: 0,
			        principal_arrears: 0,
			        loan_balance: 0,
			        amount_due: fee.fee_due,
			        reversal: false,
			        deleted: fee.deleted,
			        agent_id: customer_account.agent_id,
			        general_plan_id: customer_account.general_plan_id,
			        project_id: customer_account.project_id
			      )
			    end

			    if invoice.previously_new_record?
			      puts "✓ Created invoice ##{invoice.id}"
			    else
			      puts "→ Invoice already exists: ##{invoice.id}"
			    end

			  rescue ActiveRecord::RecordInvalid => e
			    puts "✗ Failed for fee ##{fee.id}: #{e.record.errors.full_messages.to_sentence}"
			  end
			end
  			# get suspense transfers
  			puts("*** Delete Suspense Transfers")
  			suspense_transactions.delete_all
  			puts("*** Delete Allocations amount to #{allocations.sum{|a| a.amount}}")
  			allocations.delete_all
  			customer_account.customer_account_invoices.where(debit: false).delete_all
  			puts("#{i+=1},#{customer_account&.currency&.name},#{customer_account.id},#{customer_account.project&.name},#{customer_account.property_number},#{customer_account.account_name},#{customer_account.status}")			
		end
	end

	# check for orphaned suspense transactions
	task :orphaned_suspense_transactions => :environment do
		orphaned_suspense_accounts = SuspenseAccount.where(<<~SQL)
		  NOT EXISTS (
		    SELECT 1
		    FROM transactions
		    WHERE transactions.transaction_code = suspense_accounts.reference_number
		  )
		SQL
		i = 0
		orphaned_suspense_accounts.each do |suspense|
			puts("*** #{i+=1} #{suspense.id} - #{suspense.customer_account.property_number}")
		end
	end


end