schema.development.kf

database idos;

// EXTENSION INITIALIZATION

use idos as idos;


// TABLES

table users {
    id uuid primary,
    recipient_encryption_public_key text notnull,
    inserter text notnull
}

table wallets {
    id uuid primary,
    user_id uuid notnull,
    address text notnull,
    public_key text,
    wallet_type text notnull,
    message text,
    signature text,
    inserter text,
    #wallets_user_id index(user_id),
    #wallets_evm_scan index(wallet_type, address),
    #wallets_near_scan index(wallet_type, public_key),
    foreign_key (user_id) references users(id) on_delete cascade
}

table credentials {
    id uuid primary,
    user_id uuid notnull,
    verifiable_credential_id text,
    public_notes text notnull,
    content text notnull,
    encryptor_public_key text notnull,
    issuer_auth_public_key text notnull,
    inserter text,
    #credentials_user_id index(user_id),
    #credentials_vc_id index(verifiable_credential_id),
    foreign_key (user_id) references users(id) on_delete cascade
}

table shared_credentials {
    original_id uuid notnull,
    copy_id uuid notnull,
    #primary_key primary(original_id, copy_id),
    #shared_credentials_copy_id index(copy_id),
    foreign_key (original_id) references credentials(id) on_delete cascade,
    foreign_key (copy_id) references credentials(id) on_delete cascade
}

table user_attributes {
    id uuid primary,
    user_id uuid notnull,
    attribute_key text notnull,
    value text notnull,
    inserter text,
    #user_attributes_user_id index(user_id),
    foreign_key (user_id) references users(id) on_delete cascade
}

table shared_user_attributes {
    original_id uuid notnull,
    copy_id uuid notnull,
    #primary_key primary(original_id, copy_id),
    #shared_user_attributes_copy_id index(copy_id),
    foreign_key (original_id) references user_attributes(id) on_delete cascade,
    foreign_key (copy_id) references user_attributes(id) on_delete cascade
}

table inserters {
    id uuid primary,
    name text unique notnull
}

table delegates {
    address text primary,
    inserter_id uuid notnull,
    foreign_key (inserter_id) references inserters(id) on_delete cascade
}

// A user gives a grant to write data (create a credential, share the credential) on his behalf to some issuer
table write_grants {
    id uuid primary,
    wg_owner_wallet_identifier text notnull, // user wallet/pk
    wg_grantee_wallet_identifier text notnull, // issuer wallet/pk
    wg_owner_user_id uuid notnull, // user's id, to be sure we will find the user if the owner's wallet will be deleted
    #write_grants_user_id_grantee unique(wg_owner_user_id, wg_grantee_wallet_identifier),
    foreign_key (wg_owner_user_id) references users(id) on_delete cascade
}

table access_grants {
    id uuid primary,
    ag_owner_user_id uuid notnull,
    ag_grantee_wallet_identifier text notnull,
    data_id uuid notnull,
    locked_until int notnull default(0),
    height int notnull default(1), // TODO: We should remove `defalut` in the next migration
    inserter_type text notnull,
    inserter_id text notnull,
    foreign_key (ag_owner_user_id) references users(id) on_delete cascade,
    #iag_data_id index(data_id),
    #iag_ag_grantee_wallet_identifier index(ag_grantee_wallet_identifier),
    #iag_ag_owner_user_id index(ag_owner_user_id)
}

table configs {
    config_key text primary,
    value text
}


// CONFIG ACTIONS

procedure upsert_config_as_owner($config_key text, $value text) public owner {
    INSERT INTO configs (config_key, value) VALUES ($config_key, $value)
        ON CONFLICT(config_key) DO UPDATE
            SET value = $value;
}

procedure delete_config_as_owner($config_key text) public owner {
    DELETE FROM configs WHERE config_key = $config_key;
}

procedure get_config_as_owner($config_key text) public view owner returns (value text) {
    for $row in SELECT config_key, value FROM configs WHERE config_key = $config_key {
        return $row.value;
    }
}


// INSERTER AND DELEGATE ACTIONS

procedure add_inserter_as_owner($id uuid, $name text) owner public {
    INSERT INTO inserters (id, name) VALUES ($id, $name);
}

procedure delete_inserter_as_owner($id uuid) owner public {
    DELETE FROM inserters WHERE id = $id;
}

procedure add_delegate_as_owner($address text, $inserter_id uuid) owner public {
  INSERT INTO delegates (address, inserter_id) VALUES ($address, $inserter_id);
}

procedure delete_delegate_as_owner($address text) owner public {
  DELETE FROM delegates WHERE address=$address;
}

procedure get_inserter() private view returns (name text) {
    for $row in SELECT inserters.name FROM inserters INNER JOIN delegates ON inserters.id = delegates.inserter_id WHERE delegates.address = @caller {
        return $row.name;
    }
    error('Unauthorized inserter');
}

procedure get_inserter_or_null() private view returns (name text) {
    for $row in SELECT inserters.name FROM inserters INNER JOIN delegates ON inserters.id = delegates.inserter_id WHERE delegates.address = @caller {
        return $row.name;
    }
    return null;
}

// USER ACTIONS

procedure add_user_as_inserter($id uuid, $recipient_encryption_public_key text) public {
    INSERT INTO users (id, recipient_encryption_public_key, inserter) VALUES ($id, $recipient_encryption_public_key, get_inserter());
}

procedure update_user_pub_key_as_inserter($id uuid, $recipient_encryption_public_key text) public {
    get_inserter();
    UPDATE users SET recipient_encryption_public_key=$recipient_encryption_public_key
        WHERE id = $id;
}

// For development, for not to drop a DB if we need to clear it. Should not be in prod envs.
procedure delete_user_as_owner($id uuid) public owner {
    DELETE FROM users WHERE id=$id;
}

// TODO: change to procedure
@kgw(authn='true')
action get_user() public view {
    SELECT id, recipient_encryption_public_key FROM users
    WHERE id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller));
}

@kgw(authn='true')
procedure get_user_as_inserter($id uuid) public view returns (id uuid, recipient_encryption_public_key text, inserter text) {
    get_inserter();
    for $row in SELECT * FROM users WHERE id = $id {
        return $row.id, $row.recipient_encryption_public_key, $row.inserter;
    }
}

procedure get_caller_user_id() private view returns (user_id uuid) {
    for $row in SELECT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller) {
        return $row.user_id;
    }
    error('no user found');
}


// WALLET ACTIONS

// TODO: change to procedure
action upsert_wallet_as_inserter($id, $user_id, $address, $public_key, $wallet_type, $message, $signature) public {
    SELECT CASE WHEN $wallet_type = 'NEAR' AND $public_key::TEXT IS NULL THEN ERROR('NEAR wallets require a public_key to be given.') END;

    $valid_public_key = idos.is_valid_public_key($public_key, $wallet_type);
    SELECT CASE WHEN $wallet_type = 'NEAR' AND $valid_public_key != 1 THEN ERROR('Invalid or unsupported public key.') END;

    INSERT INTO wallets (id, user_id, address, public_key, wallet_type, message, signature, inserter)
    VALUES ($id, $user_id, $address, $public_key, $wallet_type, $message, $signature, get_inserter())
    ON CONFLICT(id) DO UPDATE
    SET user_id=$user_id, address=$address, public_key=$public_key, wallet_type=$wallet_type, message=$message, signature=$signature, inserter=get_inserter();
}

// Temporary, to remove wrong data from initial test period. Owner only.
procedure delete_wallet_as_owner($id uuid) public owner {
    DELETE FROM wallets WHERE id=$id;
}

action add_wallet($id, $address, $public_key, $message, $signature) public {
    $wallet_type = idos.determine_wallet_type($address);

    SELECT CASE WHEN $wallet_type = 'NEAR' AND $public_key::TEXT IS NULL THEN ERROR('NEAR wallets require a public_key to be given.') END;

    $valid_public_key = idos.is_valid_public_key($public_key, $wallet_type);
    SELECT CASE WHEN $wallet_type = 'NEAR' AND $valid_public_key != 1 THEN ERROR('Invalid or unsupported public key.') END;

    INSERT INTO wallets (id, user_id, address, public_key, wallet_type, message, signature)
    VALUES (
        $id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)
        ),
        $address,
        CASE
            WHEN $public_key = '' THEN NULL
            ELSE $public_key
        END,
        $wallet_type,
        $message,
        $signature
    );
}

// TODO: change to procedure
@kgw(authn='true')
action get_wallets() public view {
    SELECT DISTINCT w1.*
    FROM wallets AS w1
    INNER JOIN wallets AS w2 ON w1.user_id = w2.user_id
    WHERE (
        w2.wallet_type = 'EVM' AND w2.address = @caller COLLATE NOCASE
    ) OR (
        w2.wallet_type = 'NEAR' AND w2.public_key = @caller
    );
}

procedure remove_wallet($id uuid) public {
    for $row in SELECT id FROM wallets
        WHERE id = $id
        AND ((wallet_type = 'EVM' AND address=@caller COLLATE NOCASE) OR (wallet_type = 'NEAR' AND public_key = @caller))
        AND EXISTS (
            SELECT count(id) FROM wallets
                WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE) OR (wallet_type = 'NEAR' AND public_key = @caller)
                GROUP BY user_id HAVING count(id) = 1
        ) {
        error('You can not delete a wallet you are connected with. To delete this wallet you have to connect other wallet.');
    }

    DELETE FROM wallets
    WHERE id=$id AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );
}


// CREDENTIAL ACTIONS

action upsert_credential_as_inserter (
  $id
, $user_id
, $issuer_auth_public_key
, $encryptor_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
) public {
    SELECT get_inserter(); // throw an error if not authorized

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN ERROR('assert_credential_signatures: ' || $result) END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $user_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryptor_public_key,
        $issuer_auth_public_key,
        get_inserter()
    )
    ON CONFLICT(id) DO UPDATE
    SET user_id=$user_id
        , verifiable_credential_id=(CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END)
        , public_notes=$public_notes
        , content=$content
        , encryptor_public_key=$encryptor_public_key
        , issuer_auth_public_key=$issuer_auth_public_key
        , inserter=get_inserter()
    ;
}

action add_credential (
  $id
, $issuer_auth_public_key
, $encryptor_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
) public {
    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN ERROR('assert_credential_signatures:' || $result) END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key)
    VALUES (
        $id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)
        ),
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryptor_public_key,
        $issuer_auth_public_key
    );
}

action add_credential_by_write_grant (
  $id
, $user_id
, $issuer_auth_public_key
, $encryptor_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
) public {
    SELECT get_write_grant_id($user_id); // throw an error if no write_grant

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN ERROR('assert_credential_signatures:' || $result) END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $user_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryptor_public_key,
        $issuer_auth_public_key,
        get_write_grant_id($user_id)::text
    );
}

// TODO: change to procedure
@kgw(authn='true')
action get_credentials() public view {
    SELECT DISTINCT c.id, c.user_id, c.public_notes, c.issuer_auth_public_key, c.inserter, sc.original_id AS original_id
    FROM credentials AS c
    LEFT JOIN shared_credentials AS sc ON c.id = sc.copy_id
    INNER JOIN wallets ON c.user_id = wallets.user_id
    WHERE (
        wallets.wallet_type = 'EVM' AND wallets.address = @caller COLLATE NOCASE
    ) OR (
        wallets.wallet_type = 'NEAR' AND wallets.public_key = @caller
    );
}

action edit_credential (
  $id
, $public_notes
, $public_notes_signature
, $broader_signature
, $content
, $encryptor_public_key
, $issuer_auth_public_key
) public {
    SELECT CASE
        WHEN EXISTS (
            SELECT 1 from credentials AS c
                INNER JOIN shared_credentials AS sc on c.id = sc.copy_id
                WHERE c.id = $id
                AND c.user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
                    OR (wallet_type = 'NEAR' AND public_key = @caller))
        ) THEN ERROR('Can not edit shared credential') END;

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN ERROR('assert_credential_signatures:' || $result) END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    UPDATE credentials
    SET public_notes=$public_notes
        , verifiable_credential_id = (CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END)
        , content=$content
        , encryptor_public_key=$encryptor_public_key
        , issuer_auth_public_key=$issuer_auth_public_key
    WHERE id=$id
    AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );
}

// Be aware that @caller here is ed25519 public key, hexcoded.
// All other @caller in the schema are either secp256k1 or nep413
// This action can't be called by kwil-cli (as kwil-cli uses secp256k1 only)
procedure edit_public_notes_as_issuer($public_notes_id text, $public_notes text) public {
    UPDATE credentials SET public_notes = $public_notes
    WHERE issuer_auth_public_key = @caller
        AND verifiable_credential_id = $public_notes_id;
}

procedure remove_credential($id uuid) public {
    if !credential_belongs_to_caller($id) {
        error('the credential does not belong to the caller');
    }

    if has_locked_access_grants($id) {
        error('there are locked access grants for this credential');
    }
    DELETE FROM credentials
    WHERE id=$id
    AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );

    DELETE FROM access_grants WHERE data_id = $id;
}

// Do we need this action? Can share_credential_through_dag cover all cases?
action share_credential (
  $id
, $original_credential_id
, $public_notes
, $public_notes_signature
, $broader_signature
, $content
, $encryptor_public_key
, $issuer_auth_public_key
, $grantee_wallet_identifier
, $locked_until
) public {
    SELECT CASE WHEN NOT credential_belongs_to_caller($original_credential_id) THEN
        error('The original credential does not belong to the caller')
    END;

    SELECT CASE WHEN $public_notes != '' THEN
        error('shared credentials cannot have public_notes, it must be an empty string')
    END;

    add_credential(
        $id
      , $issuer_auth_public_key
      , $encryptor_public_key
      , $content
      , $public_notes
      , $public_notes_signature
      , $broader_signature
    );
    INSERT INTO shared_credentials (original_id, copy_id) VALUES ($original_credential_id, $id);

    // SELECT is just a strange way of calling a procedure from an action
    SELECT create_access_grant(
        $original_credential_id,
        get_caller_user_id(),
        $grantee_wallet_identifier,
        $id,
        $locked_until::int,
        'user',
        @caller
    );
}

// It can be used with EVM-compatible signatures only
action share_credential_through_dag (
  $id
, $user_id
, $issuer_auth_public_key
, $encryptor_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
, $original_credential_id
, $dag_owner_wallet_identifier
, $dag_grantee_wallet_identifier
, $dag_locked_until
, $dag_signature
) public {
    SELECT CASE WHEN NOT EXISTS (
        SELECT 1 from credentials
            WHERE id = $original_credential_id
            AND user_id = $user_id
    ) THEN
        error('The original credential does not belong to the user')
    END;

    // This works for EVM-compatible signatures only
    $owner_verified = idos.verify_owner($dag_owner_wallet_identifier, $dag_grantee_wallet_identifier, $id, $dag_locked_until, $dag_signature);
    SELECT CASE WHEN $owner_verified != 1 THEN
        error('the dag_signature is invalid')
    END;

    SELECT CASE WHEN $public_notes != '' THEN
        error('shared credentials cannot have public_notes, it must be an empty string')
    END;

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN
        error('assert_credential_signatures:' || $result)
    END;

    SELECT CASE WHEN NOT EXISTS (
        SELECT 1 from users
            INNER JOIN wallets ON users.id = wallets.user_id
            WHERE wallet_type = 'EVM'
            AND address=$dag_owner_wallet_identifier COLLATE NOCASE
            AND user_id = $user_id
    ) THEN
        error('the DAG is not signed by the user')
    END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $user_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryptor_public_key,
        $issuer_auth_public_key,
        get_inserter_or_null()
    );

    INSERT INTO shared_credentials (original_id, copy_id) VALUES ($original_credential_id, $id);

    // SELECT is just a strange way of calling a procedure from an action
    SELECT create_access_grant(
        $original_credential_id,
        $user_id,
        $dag_grantee_wallet_identifier,
        $id,
        $dag_locked_until::int,
        'dag_message',
        @caller
    );
}

action share_credential_by_write_grant (
  $id
, $user_id
, $encryptor_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
, $original_credential_id
, $grantee_wallet_identifier
, $locked_until
, $issuer_auth_public_key
) public {
    SELECT CASE WHEN $locked_until < 0 THEN
        error('locked_until must be positive integer timestamp or zero')
    END;

    SELECT get_write_grant_id($user_id); // throw an error if no write_grant

    SELECT CASE WHEN NOT credential_belongs_to_user($original_credential_id, $user_id) THEN
        error('the original credential does not belong to the user')
    END;

    SELECT CASE WHEN get_write_grant_id($user_id)::text != get_credential_inserter($original_credential_id) THEN
        error('you can share only original credentials you created')
    END;

    SELECT CASE WHEN (SELECT count(1) FROM access_grants WHERE id = uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s-%s', $grantee_wallet_identifier::text, $original_credential_id::text, $locked_until::text))) > 0 THEN
        error('a grant with the same grantee, original_credential_id, and locked_until already exists')
    END;

    SELECT CASE WHEN $public_notes != '' THEN
        error('shared credentials cannot have public_notes, it must be an empty string')
    END;

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    SELECT CASE WHEN $result != 'ok' THEN error('assert_credential_signatures:' || $result) END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $user_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryptor_public_key,
        $issuer_auth_public_key,
        get_write_grant_id($user_id)::text
    );

    INSERT INTO shared_credentials (original_id, copy_id) VALUES ($original_credential_id, $id);

    // SELECT is just a strange way of calling a procedure from an action
    SELECT create_access_grant(
        $original_credential_id,
        $user_id,
        $grantee_wallet_identifier,
        $id,
        $locked_until::int,
        'write_grant',
        get_write_grant_id($user_id)::text
    );
}

procedure create_access_grant(
    $original_credential_id uuid,
    $user_id uuid,
    $grantee_wallet_identifier text,
    $data_id uuid,
    $locked_until int,
    $inserter_type text,
    $inserter_id text
) private returns (bool) {
    INSERT INTO access_grants (
        id,
        ag_owner_user_id,
        ag_grantee_wallet_identifier,
        data_id,
        locked_until,
        height,
        inserter_type,
        inserter_id
    ) VALUES (
        uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s-%s', $grantee_wallet_identifier, $original_credential_id::text, $locked_until::text)),
        $user_id,
        $grantee_wallet_identifier,
        $data_id,
        $locked_until,
        @height,
        $inserter_type,
        $inserter_id
    );

    return true; // As this procedure can be called from an action (in SELECT), we have to return a value
}

@kgw(authn='true')
procedure credential_exist_as_inserter($id uuid) public view returns (credential_exist bool) {
    get_inserter();
    return credential_exist($id);
}

// TODO: change to procedure
@kgw(authn='true')
action get_credential_owned ($id) public view {
    SELECT DISTINCT c.id, c.user_id, c.public_notes, c.content, c.encryptor_public_key, c.issuer_auth_public_key, c.inserter
    FROM credentials AS c
    INNER JOIN wallets ON c.user_id = wallets.user_id
    WHERE c.id = $id
    AND (
        (wallets.wallet_type = 'EVM' AND wallets.address = @caller COLLATE NOCASE)
            OR (wallets.wallet_type = 'NEAR' AND wallets.public_key = @caller)
    );
}

// As a credential copy doesn't contain public notes, we return respective original credential public notes
@kgw(authn='true')
procedure get_credential_shared ($id uuid) public view returns table (
  id uuid
, user_id uuid
, public_notes text
, content text
, encryptor_public_key text
, issuer_auth_public_key text
, inserter text
) {
    if !credential_exist($id) {
        error('the credential does not exist');
    }

    $ag_granted bool := false;

    for $int_ag_row in SELECT 1 FROM access_grants WHERE data_id = $id AND ag_grantee_wallet_identifier = @caller COLLATE NOCASE {
        $ag_granted := true;
        break;
    }

    if !$ag_granted {
        error('the credential is not shared with the caller');
    }

    return SELECT c.id
    , c.user_id
    , oc.public_notes
    , c.content
    , c.encryptor_public_key
    , c.issuer_auth_public_key
    , c.inserter
    FROM credentials AS c
    LEFT JOIN shared_credentials ON c.id = shared_credentials.copy_id
    LEFT JOIN credentials as oc ON shared_credentials.original_id = oc.id
    WHERE c.id = $id;
}

procedure credential_belongs_to_user($id uuid, $user_id uuid) private view returns (belongs bool) {
    for $row in SELECT 1 from credentials WHERE id = $id AND user_id = $user_id LIMIT 1 {
        return true;
    }

    return false;
}

procedure credential_belongs_to_caller($id uuid) private view returns (belongs bool) {
    for $row in SELECT 1 from credentials
        WHERE id = $id
        AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)) {
        return true;
    }

    return false;
}

procedure credential_exist($id uuid) private view returns (credential_exist bool) {
    for $row in SELECT 1 FROM credentials WHERE id = $id {
        return true;
    }
    return false;
}

procedure get_credential_inserter($id uuid) private view returns (inserter text) {
    for $row in SELECT inserter FROM credentials WHERE id = $id LIMIT 1 {
        return $row.inserter;
    }

    error('credential not found');
}


// ATTRUBITE ACTIONS

procedure add_attribute_as_inserter($id uuid, $user_id uuid, $attribute_key text, $value text) public {
    INSERT INTO user_attributes (id, user_id, attribute_key, value, inserter)
    VALUES ($id, $user_id, $attribute_key, $value, get_inserter());
}

procedure add_attribute($id uuid, $attribute_key text, $value text) public {
    INSERT INTO user_attributes (id, user_id, attribute_key, value)
    VALUES (
        $id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)
        ),
        $attribute_key,
        $value
    );
}

// TODO: change to procedure
@kgw(authn='true')
action get_attributes() public view {
    SELECT DISTINCT ha.id, ha.user_id, ha.attribute_key, ha.value, sha.original_id AS original_id
    FROM user_attributes AS ha
    LEFT JOIN shared_user_attributes AS sha ON ha.id = sha.copy_id
    INNER JOIN wallets ON ha.user_id = wallets.user_id
    WHERE (
        wallets.wallet_type = 'EVM' AND wallets.address = @caller COLLATE NOCASE
    ) OR (
        wallets.wallet_type = 'NEAR' AND wallets.public_key = @caller
    );
}

// TODO: change to procedure
action edit_attribute($id, $attribute_key, $value) public {
    SELECT CASE
        WHEN EXISTS (
            SELECT 1 from user_attributes AS ha
                INNER JOIN shared_user_attributes AS sha on ha.id = sha.copy_id
                WHERE ha.id = $id
                AND ha.user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
                    OR (wallet_type = 'NEAR' AND public_key = @caller))
        ) THEN ERROR('Can not edit shared attribute') END;

    UPDATE user_attributes
    SET attribute_key=$attribute_key, value=$value
    WHERE id=$id
    AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );
}

procedure remove_attribute($id uuid) public {
    DELETE FROM user_attributes
    WHERE id=$id
    AND user_id=(SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );
}

procedure share_attribute($id uuid, $original_attribute_id uuid, $attribute_key text, $value text) public {
    INSERT INTO user_attributes (id, user_id, attribute_key, value)
    VALUES (
        $id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)
        ),
        $attribute_key,
        $value
    );

    INSERT INTO shared_user_attributes (original_id, copy_id)
    VALUES ($original_attribute_id, $id);
}


// WRITE GRANTS ACTIONS

procedure add_write_grant($wg_grantee_wallet_identifier text) public {
    INSERT INTO write_grants (id, wg_owner_wallet_identifier, wg_grantee_wallet_identifier, wg_owner_user_id) VALUES (
        uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s', @caller, $wg_grantee_wallet_identifier)),
        @caller,
        $wg_grantee_wallet_identifier,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)
        )
    );
}

procedure remove_write_grant($wg_grantee_wallet_identifier text) public {
    DELETE FROM write_grants
        WHERE wg_grantee_wallet_identifier = $wg_grantee_wallet_identifier COLLATE NOCASE
        AND wg_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller));
}

procedure get_write_grant_id($user_id uuid) private view returns (id uuid) {
    for $row in SELECT id FROM write_grants WHERE wg_owner_user_id = $user_id AND wg_grantee_wallet_identifier = @caller COLLATE NOCASE LIMIT 1 {
        return $row.id;
    }

    error('there is no write grant found');
}

@kgw(authn='true')
procedure has_write_grant_given_to($wg_grantee_wallet_identifier text) public view returns (has_given bool) {
    for $row in SELECT 1 FROM write_grants
        WHERE wg_grantee_wallet_identifier = $wg_grantee_wallet_identifier COLLATE NOCASE
        AND wg_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)) {
            return true;
        }
    return false;
}

@kgw(authn='true')
procedure has_write_grant_given_by($user_id uuid) public view returns (has_given bool) {
    for $row in SELECT 1 FROM write_grants
        WHERE wg_owner_user_id = $user_id
        AND wg_grantee_wallet_identifier = @caller COLLATE NOCASE {
            return true;
        }
    return false;
}


// ACCESS GRANTS ACTIONS

procedure revoke_access_grant ($id uuid) public {
    $ag_exist := false;
    for $row in SELECT 1 FROM access_grants WHERE id = $id
        AND ag_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)) {
        $ag_exist := true;
    }

    if !$ag_exist {
        error('the access grant not found');
    }

    for $row2 in SELECT 1 FROM access_grants WHERE id = $id
        AND locked_until >= @block_timestamp
        AND ag_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)) {
        error('the grant is locked');
    }

    DELETE FROM access_grants
    WHERE id = $id
    AND ag_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller));
}

@kgw(authn='true')
procedure get_access_grants_owned () public view returns table (id uuid, ag_owner_user_id uuid,
    ag_grantee_wallet_identifier text, data_id uuid, locked_until int, inserter_type text, inserter_id text) {
    return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, inserter_type, inserter_id FROM access_grants
        WHERE ag_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller));
}

// As arguments can be undefined (user can not send them at all), we have to have default values: page=1, size=20
// Page number starts from 1, as UI usually shows to user in pagination element
// Ordering is consistent because we use height as first odering parameter
@kgw(authn='true')
procedure get_access_grants_granted ($page int, $size int) public view returns table (id uuid, ag_owner_user_id uuid,
    ag_grantee_wallet_identifier text, data_id uuid, locked_until int) {
    $index int := 0;
    if $page < 1 {
        error('page has to be a positive integer');
    } else {
        $index := $page - 1;
    }

    $limit int := 20;
    if $size < 1 {
        error('size has to be a positive integer');
    } else {
        $limit := $size;
    }

    $offset int := $index * $limit;

    return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until FROM access_grants
        WHERE ag_grantee_wallet_identifier =  @caller ORDER BY height ASC, id ASC LIMIT $limit OFFSET $offset;
}

@kgw(authn='true')
procedure get_access_grants_granted_count () public view returns table(count int) {
    return SELECT COUNT(1) as count FROM access_grants
        WHERE ag_grantee_wallet_identifier =  @caller;
}

@kgw(authn='true')
procedure has_locked_access_grants($id uuid) public view returns (has bool) {
    for $ag_row in SELECT 1 FROM access_grants
            WHERE data_id = $id
            AND ag_owner_user_id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
                OR (wallet_type = 'NEAR' AND public_key = @caller))
            AND locked_until >= @block_timestamp LIMIT 1 {
        return true;
    }

    return false;
}


// OTHER ACTIONS

// Should we improve it to work with near wallets too?
// TODO: change to procedure
action has_profile($address) public view {
    SELECT EXISTS (
        SELECT 1 FROM wallets WHERE address=$address COLLATE NOCASE
    ) AS has_profile;
}


// OWNER ACTIONS FOR MANUAL MIGRATIONS

procedure insert_user_as_owner($id uuid, $recipient_encryption_public_key text, $inserter text) owner public {
    INSERT INTO users (id, recipient_encryption_public_key, inserter)
    VALUES ($id, $recipient_encryption_public_key, $inserter);
}

procedure insert_wallet_as_owner($id uuid, $user_id uuid, $address text, $public_key text, $wallet_type text,
    $message text, $signature text, $inserter text) owner public {
    INSERT INTO wallets (id, user_id, address, public_key, wallet_type, message, signature, inserter)
    VALUES ($id, $user_id, $address, $public_key, $wallet_type, $message, $signature, $inserter);
}

procedure insert_credential_as_owner (
  $id uuid
, $user_id uuid
, $verifiable_credential_id text
, $public_notes text
, $content text
, $encryptor_public_key text
, $issuer_auth_public_key text
, $inserter text
) owner public {
    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES ($id, $user_id, $verifiable_credential_id, $public_notes, $content, $encryptor_public_key, $issuer_auth_public_key, $inserter);
}

procedure insert_shared_cred_as_owner($original_id uuid, $copy_id uuid) owner public {
    INSERT INTO shared_credentials (original_id, copy_id)
    VALUES ($original_id, $copy_id);
}

procedure insert_user_attribute_as_owner($id uuid, $user_id uuid, $attribute_key text, $value text, $inserter text) owner public {
    INSERT INTO user_attributes (id, user_id, attribute_key, value, inserter)
    VALUES ($id, $user_id, $attribute_key, $value, $inserter);
}

procedure insert_shared_attr_as_owner($original_id uuid, $copy_id uuid) owner public {
    INSERT INTO shared_user_attributes (original_id, copy_id)
    VALUES ($original_id, $copy_id);
}

// Some entities no need special actions because main actions do the same
// inserters:  add_inserter_as_owner
// delegates: add_delegate_as_owner
// configs: upsert_config_as_owner

procedure insert_access_grants_as_owner($id uuid, $ag_owner_user_id uuid, $ag_grantee_wallet_identifier text, $data_id uuid,
    $locked_until int, $height int, $inserter_type text, $inserter_id text) owner public {
    INSERT INTO access_grants (id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, height, inserter_type, inserter_id)
    VALUES ($id, $ag_owner_user_id, $ag_grantee_wallet_identifier, $data_id, $locked_until, $height, $inserter_type, $inserter_id);
}

procedure insert_write_grants_as_owner($id uuid, $wg_owner_wallet_identifier text, $wg_grantee_wallet_identifier text, $wg_owner_user_id uuid) owner public {
    INSERT INTO write_grants (id, wg_owner_wallet_identifier, wg_grantee_wallet_identifier, wg_owner_user_id)
    VALUES ($id, $wg_owner_wallet_identifier, $wg_grantee_wallet_identifier, $wg_owner_user_id);
}


// ACTIONS FOR IN-SCHEMA DATA MIGRATION (OWNER)

procedure all_users_as_owner() public view owner returns table (id uuid, recipient_encryption_public_key text, inserter text) {
    return SELECT id, recipient_encryption_public_key, inserter FROM users;
}

foreign procedure get_all_users() returns table(id uuid, recipient_encryption_public_key text, inserter text)

procedure migrate_users($dbid text) public owner {
    for $row in SELECT id, recipient_encryption_public_key, inserter FROM get_all_users[$dbid, 'all_users_as_owner']() {
        INSERT INTO users (id, recipient_encryption_public_key, inserter) VALUES ($row.id, $row.recipient_encryption_public_key, $row.inserter);
    }
}

procedure all_wallets_as_owner() public view owner returns table (id uuid, user_id uuid, address text, public_key text,
wallet_type text, message text, signature text, inserter text) {
    return SELECT id, user_id, address, public_key, wallet_type, message, signature, inserter FROM wallets;
}

foreign procedure get_all_wallets() returns table (id uuid, user_id uuid, address text, public_key text,
wallet_type text, message text, signature text, inserter text)

procedure migrate_wallets($dbid text) public owner {
    for $row in SELECT id, user_id, address, public_key, wallet_type, message, signature, inserter FROM get_all_wallets[$dbid, 'all_wallets_as_owner']() {
        INSERT INTO wallets (id, user_id, address, public_key, wallet_type, message, signature, inserter)
            VALUES ($row.id, $row.user_id, $row.address, $row.public_key, $row.wallet_type, $row.message, $row.signature, $row.inserter);
    }
}


procedure all_credentials_as_owner () public view owner returns table (
  id uuid
, user_id uuid
, verifiable_credential_id text
, public_notes text
, content text
, encryptor_public_key text
, issuer_auth_public_key text
, inserter text
) {
    return SELECT id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter FROM credentials;
}

foreign procedure get_all_credentials() returns table (
  id uuid
, user_id uuid
, verifiable_credential_id text
, public_notes text
, content text
, encryptor_public_key text
, issuer_auth_public_key text
, inserter text
)

procedure migrate_credentials($dbid text) public owner {
    for $row in SELECT id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter FROM get_all_credentials[$dbid, 'all_credentials_as_owner']() {
        INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
        VALUES ($row.id, $row.user_id, $row.verifiable_credential_id, $row.public_notes, $row.content, $row.encryptor_public_key, $row.issuer_auth_public_key, $row.inserter);
    }
}


procedure all_shared_credentials_as_owner() public view owner returns table (original_id uuid, copy_id uuid) {
    return SELECT original_id, copy_id FROM shared_credentials;
}

foreign procedure get_all_shared_credentials() returns table (original_id uuid, copy_id uuid)

procedure migrate_shared_credentials($dbid text) public owner {
    for $row in SELECT original_id, copy_id FROM get_all_shared_credentials[$dbid, 'all_shared_credentials_as_owner']() {
        INSERT INTO shared_credentials (original_id, copy_id) VALUES ($row.original_id, $row.copy_id);
    }
}


procedure all_user_attributes_as_owner() public view owner returns table (id uuid, user_id uuid, attribute_key text,
    value text, inserter text) {
    return SELECT id, user_id, attribute_key, value, inserter FROM user_attributes;
}

foreign procedure get_all_user_attributes() returns table (id uuid, user_id uuid, attribute_key text, value text, inserter text)

procedure migrate_user_attributes($dbid text) public owner {
    for $row in SELECT id, user_id, attribute_key, value, inserter FROM get_all_user_attributes[$dbid, 'all_user_attributes_as_owner']() {
        INSERT INTO user_attributes (id, user_id, attribute_key, value, inserter)
        VALUES ($row.id, $row.user_id, $row.attribute_key, $row.value, $row.inserter);
    }
}


procedure all_shared_user_attrs_as_owner() public view owner returns table (original_id uuid, copy_id uuid) {
    return SELECT original_id, copy_id FROM shared_user_attributes;
}

foreign procedure get_all_shared_user_attrs() returns table (original_id uuid, copy_id uuid)

procedure migrate_shared_user_attrs($dbid text) public owner {
    for $row in SELECT original_id, copy_id FROM get_all_shared_user_attrs[$dbid, 'all_shared_user_attrs_as_owner']() {
        INSERT INTO shared_user_attributes (original_id, copy_id) VALUES ($row.original_id, $row.copy_id);
    }
}


procedure all_inserters_as_owner() public view owner returns table (id uuid, name text) {
    return SELECT id, name FROM inserters;
}

foreign procedure get_all_inserters() returns table (id uuid, name text)

procedure migrate_inserters($dbid text) public owner {
    for $row in SELECT id, name FROM get_all_inserters[$dbid, 'all_inserters_as_owner']() {
        INSERT INTO inserters (id, name) VALUES ($row.id, $row.name);
    }
}


procedure all_delegates_as_owner() public view owner returns table (address text, inserter_id uuid) {
    return SELECT address, inserter_id FROM delegates;
}

foreign procedure get_all_delegates() returns table (address text, inserter_id uuid)

procedure migrate_delegates($dbid text) public owner {
    for $row in SELECT address, inserter_id FROM get_all_delegates[$dbid, 'all_delegates_as_owner']() {
        INSERT INTO delegates (address, inserter_id) VALUES ($row.address, $row.inserter_id);
    }
}

procedure all_access_grants_as_owner() public view owner returns table (id uuid, ag_owner_user_id uuid, ag_grantee_wallet_identifier text,
    data_id uuid, locked_until int, height int, inserter_type text, inserter_id text) {
    return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, height, inserter_type, inserter_id FROM access_grants;
}

foreign procedure get_all_access_grants() returns table (id uuid, ag_owner_user_id uuid, ag_grantee_wallet_identifier text,
    data_id uuid, locked_until int, height int, inserter_type text, inserter_id text)

procedure migrate_access_grants($dbid text) public owner {
    for $row in SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, height, inserter_type, inserter_id
        FROM get_all_access_grants[$dbid, 'all_access_grants_as_owner']() {
        INSERT INTO access_grants (id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, height, inserter_type, inserter_id)
            VALUES ($row.id, $row.ag_owner_user_id, $row.ag_grantee_wallet_identifier, $row.data_id, $row.locked_until, $row.height, $row.inserter_type, $row.inserter_id);
    }
}


procedure all_write_grants_as_owner() public view owner returns table (id uuid, wg_owner_wallet_identifier text, wg_grantee_wallet_identifier text,
    wg_owner_user_id uuid) {
    return SELECT id, wg_owner_wallet_identifier, wg_grantee_wallet_identifier, wg_owner_user_id FROM write_grants;
}

foreign procedure get_all_write_grants() returns table (id uuid, wg_owner_wallet_identifier text, wg_grantee_wallet_identifier text, wg_owner_user_id uuid)

procedure migrate_write_grants($dbid text) public owner {
    for $row in SELECT id, wg_owner_wallet_identifier, wg_grantee_wallet_identifier, wg_owner_user_id FROM get_all_write_grants[$dbid, 'all_write_grants_as_owner']() {
        INSERT INTO write_grants (id, wg_owner_wallet_identifier, wg_grantee_wallet_identifier, wg_owner_user_id)
            VALUES ($row.id, $row.wg_owner_wallet_identifier, $row.wg_grantee_wallet_identifier, $row.wg_owner_user_id);
    }
}


procedure all_configs_as_owner() public view owner returns table (config_key text, value text) {
    return SELECT config_key, value FROM configs;
}

foreign procedure get_all_configs() returns table (config_key text, value text)

procedure migrate_configs($dbid text) public owner {
    for $row in SELECT config_key, value FROM get_all_configs[$dbid, 'all_configs_as_owner']() {
        INSERT INTO configs (config_key, value)
            VALUES ($row.config_key, $row.value);
    }
}

// The sequence of inserting of tables is important
procedure migrate_all_data($old_dbid text) public owner {
    migrate_users($old_dbid);
    migrate_wallets($old_dbid);
    migrate_credentials($old_dbid);
    migrate_shared_credentials($old_dbid);
    migrate_user_attributes($old_dbid);
    migrate_shared_user_attrs($old_dbid);
    migrate_inserters($old_dbid);
    migrate_delegates($old_dbid);
    migrate_write_grants($old_dbid); // WGs must be inserted before access grants
    migrate_access_grants($old_dbid);
    migrate_configs($old_dbid);
}

procedure migrate_ext_grants_as_owner(
    $id uuid,
    $ag_owner text,
    $ag_grantee text,
    $data_id uuid,
    $locked_until int,
    $ag_chain text,
    $tx_hash text,
    $block_hash text,
    $height int,
    $status text
) public owner {
    // We don't need to insert non valid AGs
    if $status != 'valid' {
        return;
    }

    // Deleting credentials or even users, don't trigger removal external AGs from DB. So we need to check the user still exists.
    $user_id text := '';
    for $row in SELECT user_id FROM credentials WHERE id = $data_id {
        $user_id := $row.user_id::text;
    }
    if $user_id == '' {
        return;
    }

    create_access_grant(
        $id,
        $user_id::uuid,
        $ag_grantee,
        $data_id,
        $locked_until,
        'external_grant',
        format('%s:%s', $ag_chain, $tx_hash)
    );
}