MoonMiners/sql/import-config.sql

create or replace function import_config()
returns void as $$
begin
  create temporary table if not exists storeItems(
    id integer primary key,
    name text not null,
    description text not null,
    image text not null,
    claimAmount float8 not null,
    completionTimeInMins integer not null,
    basePrice float8 not null,
    tier_1_price float8 not null,
    tier_1_claimboost float8 not null,
    tier_2_price float8 not null,
    tier_2_claimboost float8 not null,
    tier_3_price float8 not null,
    tier_3_claimboost float8 not null,
    tier_4_price float8 not null,
    tier_4_claimboost float8 not null,
    tier_5_price float8 not null,
    tier_5_claimboost float8 not null
  ) on commit drop;
  create temporary table if not exists stakingSources(
    id integer primary key,
    name text not null,
    description text not null,
    image text not null
  ) on commit drop;
  create temporary table if not exists gameConstants(
    key text primary key,
    value float4 not null
  ) on commit drop;

  perform download_sheets();

  --- For some reason if you don't explicitly set it, it just copies it in a weird order
  copy storeItems(
    id, name, description, image, claimAmount, completionTimeInMins, basePrice,
    tier_1_price, tier_1_claimboost, tier_2_price, tier_2_claimboost,
    tier_3_price, tier_3_claimboost, tier_4_price, tier_4_claimboost,
    tier_5_price, tier_5_claimboost
  ) from '/tmp/storeItems.csv' with (delimiter ',',  format csv, header true);
  copy stakingSources from '/tmp/stakingSources.csv' (delimiter ',',  format csv, header true);
  copy gameConstants from '/tmp/gameConstants.csv' with (delimiter ',',  format csv, header true);

  perform copy_store_items();

  perform copy_upgrade_items(1);
  perform copy_upgrade_items(2);
  perform copy_upgrade_items(3);
  perform copy_upgrade_items(4);
  perform copy_upgrade_items(5);

  perform copy_staking_source_items();
  perform copy_game_constants();

end;
$$ language plpgsql;

create or replace function copy_store_items()
returns void as $$
begin

  insert into store_item(
    id,
    name,
    description,
    price,
    image_name,
    claim_amount,
    completion_time_in_mins
  )
  select
    id,
    name,
    description,
    basePrice,
    image,
    claimAmount,
    completionTimeInMins
  from storeItems
  on conflict(id)
  do update set
    name = excluded.name,
    description = excluded.description,
    price = excluded.price,
    image_name = excluded.image_name,
    claim_amount = excluded.claim_amount,
    completion_time_in_mins = excluded.completion_time_in_mins;

end;
$$ language plpgsql;

create or replace function copy_upgrade_items(p_tier integer)
returns void as $$
begin
  execute format('
    insert into upgrade_item(tier, store_item_id, price, claim_boost)
    select %1$s, id, tier_%1$s_price, tier_%1$s_claimboost
    from storeItems
    on conflict(store_item_id, tier) do update set
        price = excluded.price,
        claim_boost = excluded.claim_boost;
  ', p_tier);

end;
$$ language plpgsql;

create or replace function copy_staking_source_items()
returns void as $$
begin
  insert into staking_source_item(id, name, description, image_name)
  select id, name, description, image
  from stakingSources
  on conflict(id)
  do update set
    name = excluded.name,
    description = excluded.description,
    image_name = excluded.image_name;
end;
$$ language plpgsql;

create or replace function copy_game_constants()
returns void as $$
begin
  insert into game_constants(key, value)
  select key, value from gameConstants
  on conflict(key) do update set
    value = excluded.value;
end;
$$ language plpgsql;

-- Needed to load the python3 extension
create or replace trusted language plpython3u;
-- If you don't grant this permission then devs will not be able to update this function
-- It's dangerous though, I guess
update pg_language set lanpltrusted = true where lanname = 'plpython3u';

create or replace function download_sheets()
returns void as $$
import requests
import tempfile
import os

# Download CSV file
sheet_names = [("storeItems", "0"),("stakingSources","1898988196"), ("gameConstants","583419882") ]

spread_sheet_id = "1TitHE6rHFgmaRlBBLtMLarfNtp1UMzvRur4ZJ-PKhr0"
temp_files = []
for (sheet_name,sheet_id) in sheet_names:
    url = ("https://docs.google.com/spreadsheets/d/" +
           spread_sheet_id +
           "/export?format=csv&gid=" +
           sheet_id)

    response = requests.get(url)
    response.raise_for_status()

    # Save CSV to tmp directory file
    with open(f'/tmp/{sheet_name}.csv', 'w') as f:
        f.write(response.content.decode('utf-8'))

$$ language plpython3u;