/*
 * helpers types
 */
create type tier_details as (tier_id int, duration text, name text, price money);
create type card_details as (card_brand text, card_last_four text, card_exp_mo text, card_exp_yr text);

/*
 * helper functions
 */
-- tier_id returns the tier_id for an email_address
create function tier_id(_email_address text) returns int as $$
  select tier_id from users
  where email_address = _email_address;
$$ language sql;

-- tier_info returns the tier_details for an email_address
create function tier_info(_email_address text) returns tier_details as $$
  select tier_id, duration, name, price from tiers
  where tier_id = tier_id(_email_address);
$$ language sql;

-- card_info returns the card_details for an email_address
create function card_info(_email_address text) returns card_details as $$
  select card_brand, card_last_four, card_exp_mo, card_exp_yr from users
  where email_address = _email_address;
$$ language sql;

-- id returns the id for an email_address
create function id(_email_address text) returns uuid as $$
  select id from users
  where email_address = _email_address;
$$ language sql;

-- display_id returns the display id for an email_address
create function display_id(_email_address text) returns text as $$
  select display_id from settings
  where id = id(_email_address);
$$ language sql;

-- email_address_unique returns whether or not an email_address is unique
create function email_address_unique(_email_address text) returns bool as $$
  select exists(
    select from users
    where email_address = _email_address
  );
$$ language sql;

-- dispatch_receipt dispatches a receipt for an email_address
create function dispatch_receipt(_email_address text) returns uuid as $$
declare
  t tier_details := tier_info(_email_address);
  c card_details := card_info(_email_address);
begin
  insert into receipts (id, tier_duration, tier_name, tier_price, card_brand, card_last_four, card_exp_mo, card_exp_yr)
  values (id(_email_address), t.duration, t.name, t.price, c.card_brand, c.card_last_four, c.card_exp_mo, c.card_exp_yr)
  returning receipt_id;
end;
$$ language plpgsql;