DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `service_transaction`(IN `in_description` VARCHAR(1000), 
    IN `in_acct` INT, IN `service_amount` INT, IN `in_receiptDateSN` VARCHAR(50), IN `in_receiptSN` VARCHAR(50), 
    IN `in_mode_of_payment` VARCHAR(20), IN `patientId` VARCHAR(50), IN `in_facId` VARCHAR(50), 
    IN `sourceAcct` VARCHAR(50), IN `userId` VARCHAR(50), IN `in_service_head` VARCHAR(50), 
    IN `txnType` VARCHAR(10), IN `in_date` DATETIME, IN `in_payables_head` VARCHAR(50),
    IN `in_recievables_head` VARCHAR(50), IN `in_bank_name` VARCHAR(50), IN `in_txn_date` DATE, 
    IN `in_discount` INT, IN `in_discount_head` VARCHAR(50), IN `txn_status` VARCHAR(30), 
    IN `in_payables_head_name` VARCHAR(150), IN `in_recievables_head_name` VARCHAR(150), 
    IN `in_discount_head_name` VARCHAR(150), IN `sourceAcct_name` VARCHAR(150), 
    IN `in_service_head_name` VARCHAR(150), IN `in_patient_name` VARCHAR(150))

BEGIN
	declare client_balance int;
	declare main_balance int;
    declare receivable int;
    DECLARE instant_balance int;
    DECLARE cash_amount int;
   
--    declare service_description varchar(200);
	select balance into client_balance  from patientfileno where accountNo=in_acct AND facilityId=in_facId;
	
    set cash_amount = service_amount - in_discount;
    set main_balance = client_balance-cash_amount;
    set receivable  = cash_amount-client_balance;
    set instant_balance = client_balance + cash_amount;
   
    
    IF txnType = 'insta' THEN
    
    	update patientfileno set balance= instant_balance where accountNo=in_acct AND facilityId=in_facId;
        
        insert into account_entries (acct,dr,cr,reference_no,description,facilityId,createdAt)
            values (in_acct,0,service_amount,in_receiptDateSN,in_description,in_facId,in_date);
    
    	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_service_head,in_service_head_name,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);

            insert into transactions3 (description,acct,acc_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,sourceAcct,sourceAcct_name,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head, in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;
    
    ELSE

        -- IF the service amount is equal to the customer account balance (initial deposit amount)
        -- IF after the service amount is deducted, the client is still a debtor,

        if main_balance  > 0  then

            update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

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

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_service_head,in_service_head_name,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_payables_head, in_payables_head_name,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,accName,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head, in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;

        elseif main_balance < 0 then
            update patientfileno set balance= main_balance where accountNo=in_acct AND facilityId=in_facId;

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

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_service_head,in_service_head_name,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);


            if client_balance = 0 then
            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_recievables_head,in_recievables_head_name,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head,in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;

            -- IF client had some amount in their deposit
        elseif client_balance > 0  then

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_payables_head,in_payables_head_name,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_recievables_head,in_recievables_head_name,abs(receivable),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head,in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;
            
        elseif client_balance < 0  then

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_recievables_head,in_recievables_head_name,abs(cash_amount),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head,in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;

            end if;
        elseif  main_balance = 0 then

            update patientfileno set balance= main_balance  where accountNo=in_acct AND facilityId=in_facId;

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

            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_service_head,in_service_head_name,0,service_amount,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);


            insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_payables_head,in_payables_head_name,abs(client_balance),0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            
            IF in_discount > 0 THEN
            	insert into transactions3 (description,acct,acct_name,debit,credit,receiptDateSN,receiptNo,modeOfPayment,enteredBy,client_acct,patient_id,facilityId,createdAt,bank_name,transaction_date)
            values (in_description,in_discount_head,in_discount_head_name,in_discount,0 ,in_receiptDateSN,in_receiptSN,in_mode_of_payment,userId,in_acct,patientId,in_facId,in_date,in_bank_name,in_txn_date);
            END IF;

        -- IF the service amount is less the customer account balance
        end if;
    END IF;
END$$
DELIMITER ;