---- Add Foreign keys and indexes ----
--------------------------------------
ALTER TABLE tran_master ADD CONSTRAINT tran_master_sender_mst_FK FOREIGN KEY (sender_auto_id) REFERENCES sender_mst(sender_auto_id);

ALTER TABLE sender_mst ADD CONSTRAINT UK_sender_mst_sender_cd UNIQUE (sender_cd);

alter table tran_master alter column receiver_cd int;

alter table tran_master add constraint tran_master_receiver_mst_fk foreign key (receiver_cd) references receiver_mst(receiver_cd);

create index ix_tran_master_sender_cd on tran_master(sender_cd);

create index ix_tran_master_receiver_cd on tran_master(receiver_cd);

create index ix_tran_master_tran_date on tran_master(tran_date);

create index ix_tran_master_remit_type on tran_master(remit_type);

alter table tran_master add constraint uk_tran_master_remit_no unique (remit_number);

ALTER TABLE tran_clr ADD CONSTRAINT tran_clr_FK FOREIGN KEY (parent_agent_cd,sub_agent_cd) REFERENCES sub_agent(parent_agent_cd,sub_agent_cd);

create index ix_tran_clr_clr_date on tran_clr(clr_date);

-- alter table tran_master add constraint fk_tran_master_created_by foreign key (created_by) references remit_user (remit_user_cd);

alter table tran_master add constraint fk_tran_master_coll_sub_agent foreign key (parent_agent_cd, parent_coll_sub_agent_cd) references sub_agent(parent_agent_cd, sub_agent_cd);

alter table tran_master add constraint fk_tran_master_pay_sub_agent foreign key (parent_pay_agent_cd, parent_pay_sub_agent_cd) references sub_agent(parent_agent_cd, sub_agent_cd);

alter table tran_master add constraint fk_tran_master_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

truncate table bank_branch;

truncate table agent_bank_map;

select * from tran_clr;

alter table agent_bank_map drop constraint agent_bank_map_fk2;

alter table bank_branch drop constraint bank_branch_fk1;

update tran_master set receiver_bank_cd = null, receiver_bank_branch_cd = null, receiver_bank_ac_no = null;

update receiver_mst  set bank_cd  = null, bank_branch_cd = null, bank_ac_no = null;

truncate table bank;

alter table tran_master add constraint fk_tran_master_bank_ac_no foreign key (receiver_bank_cd, receiver_bank_branch_cd) references bank_branch (bank_cd, bank_branch_cd);

alter table agent_bank_map add constraint fk_agent_bank_map_bank foreign key (bank_cd) references bank (bank_cd);

alter table bank_branch add constraint fk_bank_branch_bank foreign key (bank_cd) references bank (bank_cd);

alter table receiver_mst  add constraint fk_receiver_mst_bank_branch foreign key (bank_cd, bank_branch_cd) references bank_branch (bank_cd, bank_branch_cd);

alter table tran_master add constraint fk_tran_master_bank_branch foreign key (receiver_bank_cd, receiver_bank_branch_cd) references bank_branch (bank_cd, bank_branch_cd);

alter table tran_master add constraint fk_tran_master_action_cd foreign key (action_cd) references remit_action (remit_action_cd);

alter table tran_master add constraint fk_tran_master_pay_crncy_cd foreign key (pay_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_pay_intermediate_crncy_cd foreign key (pay_intermediate_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_settle_pay_share_crncy_cd foreign key (settle_pay_share_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_sender_salary_crncy_cd foreign key (sender_salary_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_coll_crncy_cd foreign key (coll_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_coll_agent_comm_crncy_cd foreign key (coll_agent_comm_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_net_owner_comm_crncy_cd foreign key (net_owner_comm_crncy_cd) references currency (crncy_cd);

alter table tran_master add constraint fk_tran_master_coll_intermediate_crncy_cd  foreign key (coll_intermediate_crncy_cd ) references currency (crncy_cd);


--- sender

ALTER TABLE phpSenderBeneDb ADD CONSTRAINT fk_phpSenderBeneDb_sender_cd FOREIGN KEY (sender_cd) REFERENCES sender_mst(sender_cd);

alter table phpSenderBeneDb alter column receiver_cd int;

ALTER TABLE phpSenderBeneDb ADD CONSTRAINT fk_phpSenderBeneDb_receiver_cd FOREIGN KEY (receiver_cd) REFERENCES receiver_mst(receiver_cd);

alter table sender_block_history add constraint fk_sender_block_history_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_block_history add constraint fk_sender_block_history_sub_agent_cd foreign key (cre_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_id_category add constraint fk_sender_id_category_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_id_category add constraint fk_sender_id_category_id_card_type foreign key (id_card_type_cd) references id_card_type (id_card_type_cd);

alter table sender_id_category alter column created_parent_agent_cd varchar(25);

-- update sender_id_category set created_parent_agent_cd = 'MY0001' where created_parent_agent_cd = ''; -- Updating Wrong data

alter table sender_id_category add constraint fk_sender_id_category_created_parent_agent foreign key (created_parent_agent_cd) references parent_agent (parent_agent_cd);

delete
from sender_id_pending where sender_auto_id not in (select sender_auto_id from sender_mst);

alter table sender_id_pending add constraint fk_sender_id_pending_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_id_pending alter column created_parent_agent_cd varchar(25);

alter table sender_id_pending alter column sub_agent_cd varchar(25);

-- update sender_id_pending set created_parent_agent_cd = 'MY0001' where created_parent_agent_cd = ''; -- Updating Wrong data

delete
from sender_id_pending
where sub_agent_cd not in (select sub_agent_cd from sub_agent sa)

alter table sender_id_pending add constraint fk_sender_id_pending_sub_agent foreign key (created_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_id_pending add constraint fk_sender_id_pending_tran_id foreign key (tran_id) references tran_master (tran_id);


alter table future_remit_no drop constraint FK_future_remit_no_future_remit_no;

drop table sw_code;
drop table tbl_test;

alter table sender_mst add constraint fk_sender_master_id_card_type foreign key (id_card_type_cd) references id_card_type (id_card_type_cd);

alter table sender_mst add mod_sub_agent_cd varchar(25);

update
sender_mst
set sub_agent_cd = '10641002'
where sub_agent_cd not in (select sub_agent_cd from sub_agent sa)

update sender_mst
set cre_parent_agent_cd = 'MY0001'
where sub_agent_cd = '10641002'


with tbl as (
    select sender_auto_id, cre_parent_agent_cd, sub_agent_cd
    from sender_mst
    where cre_parent_agent_cd + ':' + sub_agent_cd not in (select parent_agent_cd + ':' + sub_agent_cd from sub_agent sa)
)
update tbl set cre_parent_agent_cd = 'MY0001'


alter table sender_mst add constraint fk_sender_master_cre_sub_agent foreign key (cre_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_mst add constraint fk_sender_master_mod_sub_agent foreign key (mod_parent_agent_cd, mod_sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_mst add constraint fk_sender_master_salary_crncy foreign key (salary_crncy_cd) references currency (crncy_cd);

with tbl as (
    select cre_parent_agent_cd, created_by
    from sender_mst sm
    where sm.cre_parent_agent_cd + ':' + sm.created_by not in (
        select parent_agent_cd + ':' + remit_user_cd
        from remit_user
    )
)
update tbl set created_by = 'PCP.ARAU';

alter table sender_mst add constraint fk_sender_master_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table sender_mst_str add constraint fk_sender_mst_str_tran_id foreign key (tran_id) references tran_master (tran_id);

alter table sender_mst_str add constraint fk_sender_mst_str_sub_agent foreign key (cre_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_mst_str add constraint fk_sender_mst_str_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table sender_mst_str add constraint fk_sender_mst_str_coll_parent_agent_cd foreign key (coll_parent_agent_cd) references parent_agent (parent_agent_cd);






alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_id_card_type foreign key (id_card_type_cd) references id_card_type (id_card_type_cd);

alter table sender_mst_temp_edit add mod_sub_agent_cd varchar(25);

with tbl as (
    select smte.cre_parent_agent_cd as pa, smte.sub_agent_cd
    from sender_mst_temp_edit smte
    where cre_parent_agent_cd + ':' + sub_agent_cd not in (
        select parent_agent_cd + ':' + sub_agent_cd
        from sub_agent sa
    )
)
update tbl
set pa = 'MY0001';

alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_cre_sub_agent foreign key (cre_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_mod_sub_agent foreign key (mod_parent_agent_cd, mod_sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_salary_crncy foreign key (salary_crncy_cd) references currency (crncy_cd);

alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table sender_mst_temp_edit add constraint fk_sender_master_tmp_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

ALTER TABLE sender_mst_temp_edit ADD CONSTRAINT fk_sender_mst_temp_edit_city FOREIGN KEY (cntry_cd,rsp_cd,district_cd,city_cd) REFERENCES city(cntry_cd,rsp_cd,district_cd,city_cd);

alter table sender_on_behalf add constraint fk_sender_on_behalf_sender_mst foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_on_behalf_history add constraint fk_sender_on_behalf_history_sender_mst foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_on_behalf_history drop constraint fk_sender_on_behalf_history_sender_mst;

alter table sender_on_behalf_history add constraint fk_sender_on_behalf_history_sender_on_behalf foreign key (sender_auto_id, sender_on_behalf_id) references sender_on_behalf (sender_auto_id, sender_on_behalf_id);

alter table sender_on_behalf_history drop constraint fk_sender_on_behalf_history_sender_on_behalf;

alter table sender_update_history add constraint fk_sender_update_history_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_update_history drop constraint fk_sender_update_history_sender_auto_id;

alter table sender_update_history add constraint fk_sender_update_history_modified_by foreign key (mod_parent_agent_cd, modified_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table sender_update_history drop constraint fk_sender_update_history_modified_by;

alter table sender_update_histroy add constraint fk_sender_update_histroy_sender_auto_id foreign key (sender_auto_id) references sender_mst (sender_auto_id);

alter table sender_update_histroy drop constraint fk_sender_update_histroy_sender_auto_id;

alter table sender_update_histroy add constraint fk_sender_update_histroy_modified_by foreign key (mod_parent_agent_cd, modified_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table sender_update_histroy drop constraint fk_sender_update_histroy_modified_by;

alter table special_sender alter column created_by varchar(30);

alter table special_sender add constraint fk_special_sender_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table special_sender alter column modified_by varchar(30);

alter table special_sender add constraint fk_special_sender_modified_by foreign key (modified_parent_agent_cd, modified_by) references remit_user (parent_agent_cd, remit_user_cd);


alter table sw_charge_details_beared_by_sender add constraint fk_sw_charge_details_beared_by_sender_coll_parent_agent_cd foreign key (coll_parent_agent_cd) references parent_agent (parent_agent_cd);

alter table sw_charge_details_beared_by_sender add constraint fk_sw_charge_details_beared_by_sender_pay_parent_agent_cd foreign key (pay_parent_agent_cd) references parent_agent (parent_agent_cd);

alter table sw_charge_details_beared_by_sender add constraint fk_sw_charge_details_beared_by_sender_coll_crncy_cd foreign key (coll_crncy_cd) references currency (crncy_cd);

alter table sw_charge_details_beared_by_sender add constraint fk_sw_charge_details_beared_by_sender_net_own_charge_crncy foreign key (net_own_charge_crncy) references currency (crncy_cd);

alter table sw_charge_details_beared_by_sender add constraint fk_sw_charge_details_beared_by_sender_final_coll_charge_crncy_cd foreign key (final_coll_charge_crncy_cd) references currency (crncy_cd);

alter table tran_sender_on_behalf add constraint fk_tran_sender_on_behalf_sender_on_behalf_id foreign key (sender_auto_id, sender_on_behalf_id) references sender_on_behalf (sender_auto_id, sender_on_behalf_id);

alter table receiver_mst_str add constraint fk_receiver_mst_str_sub_agent_cd foreign key (cre_parent_agent_cd, sub_agent_cd) references sub_agent (parent_agent_cd, sub_agent_cd);

alter table receiver_mst_str add constraint fk_receiver_mst_str_created_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table receiver_mst_str add constraint fk_receiver_mst_str_tran_id foreign key (tran_id) references tran_master (tran_id);

alter table receiver_mst_str add constraint fk_receiver_mst_str_coll_parent_agent_cd foreign key (coll_parent_agent_cd) references parent_agent (parent_agent_cd);

ALTER TABLE RoleUser ADD CONSTRAINT fk_RoleUser_role FOREIGN KEY (roleId) REFERENCES [Role](Id);

alter table RoleUser add parent_agent_cd varchar(25);


with tbl as (
    select ru.parent_agent_cd as pp, u.parent_agent_cd as p
    from remit_user u
    join RoleUser ru
        on ru.remit_user_cd = u.remit_user_cd
)
update tbl
set pp = p;

alter table RoleUser alter column parent_agent_cd varchar(25) not null;

alter table RoleUser alter column remit_user_cd varchar(30);

alter table RoleUser add constraint fk_role_user_user foreign key (parent_agent_cd, remit_user_cd) references remit_user (parent_agent_cd, remit_user_cd);

ALTER TABLE RoleUser ADD CONSTRAINT RoleUser_UN UNIQUE (parent_agent_cd,remit_user_cd,roleId);

alter table remit_user add constraint fk_remit_user_remit_role foreign key (remit_role_cd) references remit_role (remit_role_cd);

alter table loginsession_history add constraint pk_loginsession_history primary key (id);

delete
from loginsession_history
where parent_agent_cd + ':' + remit_user_cd not in (select parent_agent_cd + ':' + remit_user_cd from remit_user);

alter table loginsession_history add constraint fk_loginsession_history foreign key (parent_agent_cd, remit_user_cd) references remit_user (parent_agent_cd, remit_user_cd);

alter table receiver_update_history add constraint fk_receiver_update_history_receiver_mst foreign key (receiver_cd) references receiver_mst (receiver_cd);

alter table receiver_update_history add constraint fk_receiver_update_history_modified_by foreign key (mod_parent_agent_cd, modified_by) references remit_user (parent_agent_cd, remit_user_cd);

alter table advance_remit_no alter column parent_agent_cd varchar(25);

alter table advance_remit_no add constraint fk_advance_remit_no_parent_agent_cd foreign key (parent_agent_cd) references parent_agent (parent_agent_cd);

alter table advance_remit_no alter column cre_parent_agent_cd varchar(25);

alter table advance_remit_no alter column created_by varchar(30);

alter table advance_remit_no add constraint fk_advance_remit_no_create_by foreign key (cre_parent_agent_cd, created_by) references remit_user (parent_agent_cd, remit_user_cd);

ALTER TABLE advance_remit_no DROP CONSTRAINT FK_advance_remit_no_advance_remit_no;

alter table tran_clr add constraint uk_tran_clr unique (tran_id, clrno);