# SQL SCHEMA

--
-- BIP44 format of BIP32 spec
--   m/392'/44'/72'/0'
--
-- But Ed25519 does not support non-hardened child keys.
--

--
--
-- ss58 :: public-key -> curve -> network -> SS58
-- eth :: secp256k1 public-key -> ETH address
-- doge :: ? -> 
--
--
-- let eth_public_key
-- let cennz_public_key
-- let login_public_key 


CREATE TABLE user (
  id uuid default uuid_generate_v4() not null -- the unique challenge id
    constraint user primary key,
  
  first_name varchar(100),
  last_name varchar(100)
  
  -- linked accounts
  email_address varchar(255) NULL, -- verified email address
  ethereum_address varchar NULL,
  cennznet_address varchar NULL,
  
  -- meta
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  modified_at TIMESTAMP NOT NULL DEFAULT NOW(),
  
  PRIMARY_KEY(id)
);

CREATE UNIQUE INDEX lower_email_idx ON "user" ((lower("email_address")));

CREATE UNIQUE INDEX lower_cennznetAddress_idx ON "user" ((lower("ethereum_address")));

CREATE UNIQUE INDEX eth_public_key ON "user" ((lower("cennznet_address")));

-- routines:
-- * clean up: detect unactivated accounts (all challenges have expired)
--
-- other considerations:
-- * change email
-- * resend (regenerate?) email challenges?


async getCennzAddress(uid: string): string


let eth_public_key = SELECT eth_public_key from user where id = uid;
select cennz_address from user_pub_key where public_key = eth_public_key

-- gets all pub keys for user;
SELECT k.* from user_pub_key as k
  join user on user_id = id
  where user_id = ? and verified_at != NULL;

-- https://github.com/dn3010/glorious-server/blob/7a994d414f1c543bf54824781db933108637058a/src/db/Database.ts#L11
-- add (not implemented) functions to Database class and use in server
  
-- Goal: make sure nothing is missed from login flow
-- database controller: interface?
--

CREATE TABLE user_public_key (
    user_id VARCHAR NOT NULL,
    public_key VARCHAR NOT NULL,
    public_key_curve VARCHAR NOT NULL, -- 'secp256k1', 'ed25519'
    
    -- challenge
    challenge_id VARCHAR NOT NULL, -- nonce and idx for server access
    challenge VARCHAR NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    verified_at TIMESTAMP NULL,

    -- pre-computed addresses
    cennz_address VARCHAR NULL, -- SS58(public_key, public_key_curve, network=42) -> cennz_address
    eth_address VARCHAR NULL, -- if (public_key_curve == 'secp25k1') ETHAddress(pubic_key)
    
    PRIMARY_KEY (user_id, public_key, public_key_curve),
    FOREIGN KEY (user_id) REFERENCES user.id,
);

CREATE TABLE auth_pk_challenge {
    id uuid default uuid_generate_v4() not null -- the unique challenge id
              constraint auth_pk_challenge_pk
                  primary key,                 
--           message          VARCHAR,            -- the message to sign
--           public_key       VARCHAR   NOT NULL,
--           public_key_curve VARCHAR   NOT NULL, -- 'secp256k1', 'ed25519'
    created_at       TIMESTAMP NOT NULL DEFAULT NOW(),
    expire_at        TIMESTAMP NOT NULL,
    validated_at     TIMESTAMP NULL
}

CREATE TABLE user_email (
    user_id varchar NOT NULL,
    email varchar NOT NULL,
    created_at timestamp,
    
    -- challenge
    challenge VARCHAR NULL,
    verified_at TIMESTAMP NULL,
    
    PRIMARY_KEY (user_id, email),
    FOREIGN KEY (user_id) REFERENCES user.id,
);

-- 
SELECT * FROM user;
SELECT * FROM user_email_challenge c INNER
  JOIN USER u ON c.user = u.user_id
  WHERE u.email_adress != c.email;

-- Flow of data
--
-- Client                                                             Server
-- =========================================================================
--
-- Sign Up:
--
--    ---------- challenge request (cennznet address) ------------------->
--          POST /auth/sign-up/cennznet                    * derive public key from <cennznet address> (SS58 decode)
--                                                         * generate challenge for <public key>
--                                                         * store challenge (with TTL, subject to clean up)
--                                                         * send challenge to peer
--    challenge <---------- challenge -----------------------------------
--    
--    * sign(prompt="Glorious Authentication",
--           walletId=<wallet id>,
--           account=<wallet's primary cennz account>,
--           data=<challenge>)
--
--    ---------- challenge response -------------------------------------->
--          POST /auth/sign-up/cennznet/confirm            * verify submitted challenge
--                                                           * load challenge and check expiry
--                                                           * check signature
--                                                         * create user row in database
--
-- Update Details: 
--  
--    ---------- update details request --------------------------------->
--          POST /user/edit                                * if email is set:
--                                                            * generate challenge
--                                                            * store challenge in "user_email_challenge" table (with TTL?)
--                                                            * send verification email
--
-- Verify Email:
--    ---------- verify email (user follows link) ----------------------->
--          GET /auth/email/confirm?link={...}             * resolve user based on <link> via "user_email_challenge" table
--                                                         * remove row from "user_email_challenge" table
--                                                         * upate "user" table