select CLIENTS_HMI.CLIENT_ID as CLIENT_ID,
    CLIENTS_HMI.FIRSTNAME as FIRSTNAME,
    CLIENTS_HMI.LASTNAME as LASTNAME,
    CLIENTS_HMI.DATE_OF_BIRTH as DATE_OF_BIRTH,
    CLIENTS_HMI.CONTACT as CONTACT,
    CLIENTS_HMI.TYPE_OF_ID as TYPE_OF_ID,
    CLIENTS_HMI.ID_NUMBER as ID_NUMBER,
    CLIENTS_HMI.CREATED_BY as CREATED_BY,
    CLIENTS_HMI.CREATED_DATE as CREATED_DATE 
 from CLIENTS_HMI CLIENTS_HMI where CLIENT_ID =  :client_id
declare

client_seq NUMBER;
client_check number;
account_check number;



d_firstname CLIENTS_HMI.firstname%TYPE;
d_contact CLIENTS_HMI.contact%TYPE;
dep_amount varchar2(20);
api sms_api.sms_api%TYPE;
dep_msg varchar2(1000);
l_clob 	CLOB;
message varchar2(1000);


l_real_deposit number ;
front_load number;
d_interest HMI_INTERESTS.interest%TYPE;

BEGIN

SELECT HMI_CLIENT_USSD_SEQ.NEXTVAL INTO client_seq FROM DUAL;


INSERT INTO CLIENTS_HMI 
(
    CLIENT_ID,
    FIRSTNAME,
    LASTNAME,
    CONTACT,
    DATE_OF_BIRTH,
    TYPE_OF_ID,
    ID_NUMBER,
    PLACE_OF_STAY,
    CREATED_BY,
    CREATED_DATE,
    EPC_BALANCE,
    AIC_BALANCE
)
VALUES
(
     client_seq,
    :firstname,
    :lastname,
    :contact,
    to_date(:date_of_birth, 'MM/DD/YYYY'),
    :type_of_id,
    :id_number,
    :place_of_stay,
    'E-TRANZACT',
    sysdate,
     0.00,
     0.00

);

dep_amount := TO_CHAR(:initial_investment_amount, '99,999,999.99');

SELECT SMS_API INTO api FROM SMS_API WHERE COMPANY = 'HMI';

message:= 'Dear '||:firstname||', AIC A/C NO '||concat('1030000',client_seq)||' is successfully opened on '||sysdate||'. For any enquiries, kindly whatsapp us on https://wa.me/233245124120. Thank you!';
      

dep_msg:= 'Dear '||:firstname||', AIC Investment of GHS '||TRIM(dep_amount)||' made for AC ***'||client_seq||' completed successfully on '||sysdate||' via eTranzact-('||:receipt_number||'). Thank you';


SELECT interest INTO d_interest FROM HMI_INTERESTS WHERE product = 'AIC-FL'; 

INSERT INTO ACCOUNTS_HMI 
(
    ACCOUNT_NUMBER,
    CLIENT_ID,
    ACCOUNT_TYPE,
    INITIAL_INVESTMENT_AMOUNT,
    MODE_OF_PAYMENT,
    APPROVAL_STATUS,
    CREATED_DATE,
    CREATED_BY
)
VALUES
(
    concat('1030000',client_seq),
    client_seq,
    'A.I.C',
    :initial_investment_amount,
    :mode_of_payment,
    'APPROVED',
     sysdate,
    'E-TRANZACT'
);

front_load := :initial_investment_amount * d_interest;
l_real_deposit:= :initial_investment_amount - front_load;

l_clob := apex_web_service.make_rest_request(
p_url => 'https://sms.arkesel.com/sms/api?action=send-sms&api_key='||api||'&to='||:contact||'&from=USSDsms='||message||'',
p_http_method => 'GET');


INSERT INTO AIC_TRANSACTIONS_HMI (CLIENT_ID,ACCOUNT_NO,DATE_OF_TRANSACTION,RECEIPT_NUMBER,TRANSACTION_TYPE,DEPOSIT_AMOUNT,FRONT_LOAD_CHARGE,REAL_DEPOSIT,TRANSACTION_STATUS,OLD_BALANCE,NEW_BALANCE,DATE_CREATED,CREATED_BY,MODE_OF_PAYMENT)
 VALUES (client_seq, concat('1030000',client_seq), sysdate, :receipt_number, 'DEPOSIT', :initial_investment_amount, front_load, l_real_deposit, 'CONFIRMED', 0.00, l_real_deposit, sysdate, 'E-TRANZACT', :mode_of_payment );

update clients_hmi set AIC_balance = l_real_deposit where client_id = client_seq;

l_clob := apex_web_service.make_rest_request(
p_url => 'https://sms.arkesel.com/sms/api?action=send-sms&api_key='||api||'&to='||:contact||'&from=USSDsms='||DEP_msg||'',
p_http_method => 'GET');

:status := 201;
IF :status = 201 then
commit;
end if;

exception when others then
    :status := 404;
    :errmsg := sqlerrm;

END;