-- create extension
create extension wrappers;

-- create foreign data wrapper and enable 'FirebaseFdw'
create foreign data wrapper firebase_wrapper
  handler firebase_fdw_handler
  validator firebase_fdw_validator;

-- Below we're using the service account key stored in Vault, if you don't want
-- to use Vault, you can directly specify the service account key in `sa_key`
-- option but it is less secure. For example,
--
-- create server my_firebase_server
--   foreign data wrapper firebase_wrapper
--   options (
--     sa_key '
--     {
--        "type": "service_account",
--        "project_id": "your_gcp_project_id",
--        ...
--     }
--    ',
--     project_id 'firebase_project_id',
--   );

-- save Firebase service account json in Vault and get its key id
select pgsodium.create_key(name := 'firebase');
insert into vault.secrets (secret, key_id) values ('
{
  "type": "service_account",
  "project_id": "your_gcp_project_id",
  ...
}
',
(select id from pgsodium.valid_key where name = 'firebase')
) returning key_id;

-- create server and specify custom options
do $$
declare
  key_id text;
begin
  select id into key_id from pgsodium.valid_key where name = 'firebase' limit 1;

  execute format(
    E'create server my_firebase_server \n'
    '   foreign data wrapper firebase_wrapper \n'
    '   options ( \n'
    '     sa_key_id ''%s'', \n'
    '     project_id ''firebase_project_id'' \n'
    ' );',
    key_id
  );
end $$;

-- create an example foreign table
create foreign table firebase_users (
  uid text,
  email text,
  created_at timestamp,
  attrs jsonb
)
  server my_firebase_server
  options (
    object 'auth/users'
  );

create foreign table firebase_docs (
  name text,
  created_at timestamp,
  updated_at timestamp,
  attrs jsonb
)
  server my_firebase_server
  options (
    object 'firestore/user-profiles'  -- format: 'firestore/[collection_id]'
  );

select * from firebase_users;
select * from firebase_docs;