-- strategy TABLE_PER_CLASS, Hibernate 7.4.13.Final

== persist 3 payments and 1 order: 4 INSERT statements
insert into tp_card_payment (amount,currency,card_last4,network,id) values (?,?,?,?,?);
insert into tp_bank_transfer_payment (amount,currency,bank_code,iban,id) values (?,?,?,?,?);
insert into tp_wallet_payment (amount,currency,provider,id) values (?,?,?,?);
insert into tp_order (payment_id,reference,id) values (?,?,?);

== select p from TpPayment p   (polymorphic, returns 3 rows)
select p1_0.id,p1_0.clazz_,p1_0.amount,p1_0.currency,p1_0.bank_code,p1_0.iban,p1_0.card_last4,p1_0.network,p1_0.provider
from (select amount, card_last4, currency, id, network, null as bank_code, null as iban, null as provider, 1 as clazz_
from tp_card_payment
union all
select amount, null as card_last4, currency, id, null as network, bank_code, iban, null as provider, 2 as clazz_
from tp_bank_transfer_payment
union all
select amount, null as card_last4, currency, id, null as network, null as bank_code, null as iban, provider, 3 as clazz_
from tp_wallet_payment) p1_0
  order by p1_0.id;

== from TpCardPayment   (one subclass, returns 1 row)
select cp1_0.id,cp1_0.amount,cp1_0.currency,cp1_0.card_last4,cp1_0.network
from tp_card_payment cp1_0;

== select o from TpOrder o join fetch o.payment   (association to the root)
select po1_0.id,po1_0.payment_id,p1_0.id,p1_0.clazz_,p1_0.amount,p1_0.currency,p1_0.bank_code,p1_0.iban,p1_0.card_last4,p1_0.network,p1_0.provider,po1_0.reference
from tp_order po1_0
  join (select amount, card_last4, currency, id, network, null as bank_code, null as iban, null as provider, 1 as clazz_
from tp_card_payment
union all
select amount, null as card_last4, currency, id, null as network, bank_code, iban, null as provider, 2 as clazz_
from tp_bank_transfer_payment
union all
select amount, null as card_last4, currency, id, null as network, null as bank_code, null as iban, provider, 3 as clazz_
from tp_wallet_payment) p1_0 on p1_0.id=po1_0.payment_id;
