This file contains snippets of code that you can execute in a postgres client in order to carry out certain user stories at the database-level
Running the below commands assumes that you have taken the 2 steps (in order):
- seed the database
- run latest database migrations (ie. the ones provided by the db--payment_model_changes branch of the Legible Monorepo)
-- user signals intent to buy
begin;
delete from app_public.payment_intents where id = '9161408e-3afb-633e-50cd-f1b20de6f466';
delete from app_public.acquired_books where id = 'ab61408e-3afb-633e-50cd-f1b20de6f466';
insert into app_public.payment_intents(id, status, amount, stripe_pi_code)
values ('9161408e-3afb-633e-50cd-f1b20de6f466', 'PENDING', 200, 'pi_NOTREAL');
-- make sure to replace book_id, purchaser_id and recipient_id with actual values in your database
insert into app_public.acquired_books(id, status, book_id, purchaser_id, recipient_id, payment_intent_id)
values ('ab61408e-3afb-633e-50cd-f1b20de6f466', 'ACQUISITION_PENDING', '093f65e0-80a2-95f8-076b-1c5722a46aa2', '98f13708-2101-94c4-7568-7be6106a3b84', '98f13708-2101-94c4-7568-7be6106a3b84', '9161408e-3afb-633e-50cd-f1b20de6f466');
commit;-- user finalizes the purchase
begin;
delete from app_public.invoices where id = '140e8fab-6dd8-4f1b-b98c-67d101d6f873';
delete from app_public.invoice_items where id = '987665ab-6dd8-4f1b-b98c-67d101d6f873';
insert into app_public.invoices(id, currency_code, billing_address)
values ('140e8fab-6dd8-4f1b-b98c-67d101d6f873', 'CAD', '{}');
insert into app_public.invoice_items(id, invoice_id, payment_intent_id, amount)
values ('987665ab-6dd8-4f1b-b98c-67d101d6f873', '140e8fab-6dd8-4f1b-b98c-67d101d6f873', '9161408e-3afb-633e-50cd-f1b20de6f466', '{"listPrice":223,"paidPrice":200}');
update app_public.payment_intents
set status = 'SUCCEEDED'
where id = '9161408e-3afb-633e-50cd-f1b20de6f466';
update app_public.acquired_books
set status = 'ACQUIRED'
where id = 'ab61408e-3afb-633e-50cd-f1b20de6f466';
commit;-- Initiate a refund
begin;
insert into app_public.refunds(id, status, amount, refund_requested_reason, payment_intent_id)
values ('6eefd08e-3afb-633e-50cd-f1b20de6f466', 'REQUESTED', 200, 'CONTENT_MISMATCH', '9161408e-3afb-633e-50cd-f1b20de6f466')
commit;-- Approve a refund (e.g. from Retool, or automatically)
begin;
update app_public.refunds
set status = 'LEGIBLE_APPROVED'
where id = '6eefd08e-3afb-633e-50cd-f1b20de6f466';
commit;-- Stripe confirms the refund has been given (webhook)
begin;
insert into app_public.invoices(id, currency_code, billing_address)
values ('146e8fab-6dd8-4f1b-b98c-67d101d6f873', 'CAD', '{}');
insert into app_public.invoice_items(invoice_id, payment_intent_id, amount)
values ('146e8fab-6dd8-4f1b-b98c-67d101d6f873', '6p11408e-3afb-633e-50cd-f1b20de6f466', '{"return_price":200}');
-- update status columns
update app_public.refunds
set status = 'SUCCEEDED'
where id = '6eefd08e-3afb-633e-50cd-f1b20de6f466';
update app_public.acquired_books
set status = 'REFUNDED'
where id = 'ab61408e-3afb-633e-50cd-f1b20de6f466';
commit;make sure to reset your database; we are going to be using the same IDs
to setup, let's first make sure we have a promo bookshelf with some books in it here you'll need to figure out the entity.id of the publisher (probably openroad_media).
You'll have to find it with
select from app_public.entities where slug = 'openroadmedia';
begin;
insert into app_public.bookshelf_groups(id, name)
values ('b591408e-3afb-633e-50cd-f1b20de6f466', 'Promos');
-- NOTE: remember to replace the owner_id here with that entity_id you just found for openroad_media
insert into app_public.bookshelves(id, name, owner_id, bookshelf_group_id)
values ('b005408e-3afb-633e-50cd-f1b20de6f466', 'openroad_early_bird_referral_promo', '2be18626-2609-412a-bc7d-d2d49ddf8587', 'b591408e-3afb-633e-50cd-f1b20de6f466');
insert into app_public.promos(id, bookshelf_id)
values ('9405408e-3afb-633e-50cd-f1b20de6f466', 'b005408e-3afb-633e-50cd-f1b20de6f466');
-- depending on the books you've imported into your dev database, you might have to change the bookIds here:
insert into app_public.bookshelf_books(bookshelf_id, book_id)
values ('b005408e-3afb-633e-50cd-f1b20de6f466', '6a258ee4-e6f1-ea67-4fb4-74f8c6bc29e0');
insert into app_public.bookshelf_books(bookshelf_id, book_id)
values ('b005408e-3afb-633e-50cd-f1b20de6f466', '158fb212-cd69-8369-bf5a-a46cd9d2752f');
insert into app_public.bookshelf_books(bookshelf_id, book_id)
values ('b005408e-3afb-633e-50cd-f1b20de6f466', 'd1d222a1-7ba0-3f77-7eee-59f7d565e38b');
insert into app_public.bookshelf_books(bookshelf_id, book_id)
values ('b005408e-3afb-633e-50cd-f1b20de6f466', '5c910798-e241-6a09-9014-ea49327a002e');
commit;
-- note: at this point you can navigate to `/bookshelf/promo`, and you should see 4 books on the bookshelf
-- next, let's insert some coupons we can test with
begin;
insert into app_public.payment_methods(id)
values ('9305408e-3afb-633e-50cd-f1b20de6f466');
insert into app_public.payment_methods(id)
values ('8305408e-3afb-633e-50cd-f1b20de6f466');
insert into app_public.payment_methods(id)
values ('7305408e-3afb-633e-50cd-f1b20de6f466');
-- a 100%-off coupon with unlimited use
insert into app_public.coupons(code, percent_discount, promo_id, payment_method_id)
values('ABCDEFG', 1.00, '9405408e-3afb-633e-50cd-f1b20de6f466', '9305408e-3afb-633e-50cd-f1b20de6f466');
-- a double use coupon— Check to make sure that it can't be used 3 times.
insert into app_public.coupons(code, total_allowable_use, percent_discount, promo_id, payment_method_id)
values('EFGHIJK', 2, 1.00, '9405408e-3afb-633e-50cd-f1b20de6f466', '8305408e-3afb-633e-50cd-f1b20de6f466');
-- a 75%-off coupon— check to make sure that we can't use this on the promo bookshelf.
insert into app_public.coupons(code, percent_discount, promo_id, payment_method_id)
values('IJKLMNOP', 0.75, '9405408e-3afb-633e-50cd-f1b20de6f466', '7305408e-3afb-633e-50cd-f1b20de6f466');
commit;
-- finally, let's acquire the book for the user. This is broken out here, but in the actual code, these inserts are encapsulated in the `really_acquire_book` function
begin;
insert into app_public.payment_intents(id, status, amount, stripe_pi_code)
values ('9161408e-3afb-633e-50cd-f1b20de6f466', 'PENDING', 200, 'pi_NOTREAL');
insert into app_public.acquired_books(id, status, book_id, purchaser_id, recipient_id, payment_intent_id)
values ('ab61408e-3afb-633e-50cd-f1b20de6f466', 'ACQUISITION_PENDING', '093f65e0-80a2-95f8-076b-1c5722a46aa2', '98f13708-2101-94c4-7568-7be6106a3b84', '98f13708-2101-94c4-7568-7be6106a3b84', '9161408e-3afb-633e-50cd-f1b20de6f466');
commit;
-- insert a payment_source that is associated with the coupon's payment_method_id
insert into app_public.payment_sources(amount_contribution, payment_intent_id, payment_method_id)
values (200, '9161408e-3afb-633e-50cd-f1b20de6f466', 'e69f341b-b74f-4052-a592-08b4e89a250f');
-- user finalizes the purchase
begin;
insert into app_public.invoices(id, currency_code, billing_address)
values ('140e8fab-6dd8-4f1b-b98c-67d101d6f873', 'CAD', '{}');
insert into app_public.invoice_items(invoice_id, payment_intent_id, amount)
values ('140e8fab-6dd8-4f1b-b98c-67d101d6f873', '9161408e-3afb-633e-50cd-f1b20de6f466', '{"list_price":223,"paid_price":200}');
update app_public.payment_intents
set status = 'SUCCEEDED'
where id = '9161408e-3afb-633e-50cd-f1b20de6f466';
update app_public.acquired_books
set status = 'ACQUIRED'
where id = 'ab61408e-3afb-633e-50cd-f1b20de6f466';
commit;todo: (using new create_method_of_payment function)
select * from app_public.payment_sources as ps
inner join app_public.payment_intents as pi
on pi.id = ps.payment_intent_id
where pi.id = '9161408e-3afb-633e-50cd-f1b20de6f466';