CREATE DEFINER=`root`@`localhost` PROCEDURE `customer_deposit`(
    IN `in_acct` INT, 
IN `service_amount` INT, 
IN `userId` VARCHAR(50), 
IN `in_receiptDateSN` VARCHAR(50), 
IN `in_receiptSN` VARCHAR(50), 
IN `in_mode_of_payment` VARCHAR(20), 
IN `in_facId` VARCHAR(50), 
IN `in_description` VARCHAR(1000), 
IN `sourceAcct` VARCHAR(50), 
IN `acc_name` VARCHAR(100), 
IN `acc_type` VARCHAR(20), 
IN `in_date` DATETIME, 
IN `in_address` VARCHAR(100), 
IN `in_phone` VARCHAR(20), 
IN `in_email` VARCHAR(50), 
IN `in_website` VARCHAR(50), 
IN `in_payables_head` VARCHAR(50), 
IN `in_recievables_head` VARCHAR(50), 
IN `in_guarantor_name` VARCHAR(50), 
IN `in_guarantor_address` VARCHAR(100), 
IN `in_guarantor_phone` VARCHAR(20), 
IN `in_bank_name` VARCHAR(50), 
IN `in_pid` VARCHAR(50), 
IN `in_txn_status` VARCHAR(50),
IN `amount_paid` FLOAT(7,4) )NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
	declare client_balance double;
	declare main_balance double;
    #DECLARE remaining_balance double;
	select balance into client_balance from patientfileno where accountNo=in_acct AND facilityId=in_facId;
 
	#SET remaining_balance = service_amount - client_balance;
	set main_balance = client_balance+amount_paid;

	-- # IF customer is a NEW client,
    -- # We create a new account for the customer and deposit the amount
	if client_balance is null and service_amount < 0 then
  
        	insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,service_amount,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(service_amount),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(service_amount),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,
                                  patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(service_amount) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);            
            
            ELSEif client_balance is null then
    
		insert into patientfileno (accountNo,accName,balance,facilityId,status,accountType,contactAddress,contactPhone,contactEmail,contactWebsite,guarantor_name,
               guarantor_address,guarantor_phone)
		values(in_acct,acc_name,service_amount,in_facId,'approved',acc_type,in_address,in_phone,in_email,in_website,in_guarantor_name,
               in_guarantor_address,in_guarantor_phone);

       insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(service_amount),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(service_amount),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_payables_head,0,abs(service_amount) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

	end if;
    
    -- # IF customer has an account, but the deposit amount nullifies his account balance
    -- # i.e. he's paying up his debt
	if  main_balance = 0 then
 
		update patientfileno set balance= (client_balance - service_amount)  where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(service_amount),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);
		 
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(service_amount),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment, enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

	-- # IF customer has an account, and his new account balance after the deposit is a top up
    -- # to his initial account balance
	elseif main_balance  > 0  then
 
		update patientfileno set balance= (client_balance - service_amount)  where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr, reference_no, description, facilityId, createdAt,client_id,txn_status)
		values (in_acct,service_amount,0,in_receiptDateSN, in_description,in_facId, in_date,in_pid,in_txn_status);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(service_amount),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

		-- # IF customer's was owing us some money, then
		if  client_balance < 0 then

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name)
			values (in_description,in_recievables_head,0,abs(client_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);

			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name)
			values (in_description,in_payables_head,0,abs(main_balance) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
            
		-- # ELSE IF customer had some money in his/her account
		elseif client_balance >= 0 then
			insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,
                                      client_acct,patient_id,facilityId,createdAt,bank_name)
			values (in_description,in_payables_head,0,abs(service_amount) ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                    userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
		end if;

	-- # IF the customer was owing some money and the deposit amount is not upto the amount owed
	elseif main_balance  < 0 then
		update patientfileno set balance= (client_balance - service_amount)  where accountNo=in_acct AND facilityId=in_facId;

		insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt,client_id,txn_status)
		values (in_acct,abs(service_amount),0,in_receiptDateSN,in_description,in_facId,in_date,in_pid,in_txn_status);

		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,
                                  modeOfPayment,enteredBy,
                                  client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,sourceAcct,abs(service_amount),0,in_receiptDateSN,in_receiptSN,in_mode_of_payment,
                userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
        
		insert into transactions (description,acct,debit,credit,receiptDateSN,receiptNo,modeOfPayment,
                                  enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name)
		values (in_description,in_recievables_head,0,abs(service_amount) ,in_receiptDateSN,
                in_receiptSN,in_mode_of_payment,userId,in_acct,in_acct,in_facId,in_date,in_bank_name);
        
        
	end if;

    IF amount_paid > 0 then
        update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;
    END IF;
END;;