import * as pg from "pg"

const pool = new pg.Pool({
  database: process.env.GF_DATABASE_NAME,
  user: process.env.GF_DATABASE_USER,
  password: process.env.GF_DATABASE_PASSWORD
})

async function prepare() {
  const client = await pool.connect()
  const schema = process.env.GF_DATABASE_PRIVATE_SCHEMA

  await client.query(`SET search_path TO ${schema}`)
  await client.query(`

    -- DROP TABLE IF EXISTS noblockchain_cryptomaterial;
    CREATE TABLE IF NOT EXISTS noblockchain_cryptomaterial (
        pubkey BYTEA NOT NULL,
        privkey BYTEA NOT null,
        UNIQUE (pubkey)
    );

    DROP TABLE IF EXISTS investor CASCADE;
    CREATE TABLE IF NOT EXISTS investor  (
        rowid SERIAL PRIMARY KEY,
        tuid text not null,
        email text not null,
        password text not null,
        username text not null,
        aux_data json not null,
        UNIQUE (tuid)
    );

    DROP TABLE IF EXISTS portfolio;
    CREATE TABLE IF NOT EXISTS portfolio (
        rowid SERIAL PRIMARY KEY,
        tuid text not null,
        name text not null,
        owner text references investor(tuid) ON DELETE CASCADE,
        aux_data text not null
    );

    DROP TABLE IF EXISTS portfolio_bond;
    CREATE TABLE IF NOT EXISTS portfolio_bond (
        portfolio_tuid text not null,
        bond_tuid text not null,
        amount double precision  not null,
        aux_data jsonb not null default '{}'::jsonb,
        PRIMARY KEY (bond_tuid, portfolio_tuid)
    );
  `)
  console.log(`done`)
  process.exit()
}

prepare()