schema.production.kf

database idos;

// EXTENSION INITIALIZATION

use idos as idos;


// TABLES

table humans {
    id uuid primary,
    current_public_key text notnull,
    inserter text notnull
}

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

table credentials {
    id uuid primary,
    human_id uuid notnull,
    verifiable_credential_id text,
    public_notes text notnull,
    content text notnull,
    encryption_public_key text notnull, // public key of encryptor
    issuer_auth_public_key text notnull,
    inserter text,
    #credentials_human_id index(human_id),
    #credentials_vc_id index(verifiable_credential_id),
    foreign_key (human_id) references humans(id) on_delete cascade
}

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

table human_attributes {
    id uuid primary,
    human_id uuid notnull,
    attribute_key text notnull,
    value text notnull,
    inserter text,
    #human_attributes_human_id index(human_id),
    foreign_key (human_id) references humans(id) on_delete cascade
}

table shared_human_attributes {
    original_id uuid notnull,
    duplicate_id uuid notnull,
    #primary_key primary(original_id, duplicate_id),
    foreign_key (original_id) references human_attributes(id) on_delete cascade,
    foreign_key (duplicate_id) references human_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
}

table external_access_grants {
    id uuid primary,
    ag_owner text notnull,
    ag_grantee text notnull,
    data_id text notnull,
    locked_until int notnull,
    ag_chain text notnull,
    tx_hash text,
    block_hash text,
    height int,
    // data_id in contract can be any data, but idOS expects uuid of a credential, so we need a status field to mark validity of the access grant
    status text, // valid, invalid_by_reason, ...
    #external_ag_data_id_grantee index(data_id, ag_grantee),
    #external_ag_data_id_owner index(data_id, ag_owner)
}

// A human/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 text notnull, // human wallet/pk
    wg_grantee text notnull, // issuer wallet/pk
    wg_owner_human_id uuid notnull, // human's id, to be sure we will find the human if the owner's wallet will be deleted
    #write_grants_human_id_grantee unique(wg_owner_human_id, wg_grantee),
    foreign_key (wg_owner_human_id) references humans(id) on_delete cascade
}

// internal access grants can be granted if such permission is in write_grants
table internal_access_grants {
    id uuid primary,
    ag_owner_human_id uuid notnull,
    ag_grantee text notnull,
    data_id uuid notnull,
    locked_until int notnull default(0),
    write_grant_id uuid, // This should be notnull, but we do set it to null in the FK.
    foreign_key (write_grant_id) references write_grants(id) on_delete set null,
    foreign_key (ag_owner_human_id) references humans(id) on_delete cascade,
    #iag_data_id index(data_id),
    #iag_ag_grantee index(ag_grantee),
    #iag_ag_owner_human_id index(ag_owner_human_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;
}

// HUMAN ACTIONS

procedure add_human_as_inserter($id uuid, $current_public_key text) public {
    INSERT INTO humans (id, current_public_key, inserter) VALUES ($id, $current_public_key, get_inserter());
}

procedure update_human_pub_key_as_inserter($id uuid, $current_public_key text) public {
    get_inserter();
    UPDATE humans SET current_public_key=$current_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_human_as_owner($id uuid) public owner {
    DELETE FROM humans WHERE id=$id;
}

// TODO: change to procedure
@kgw(authn='true')
action get_human() public view {
    SELECT id, current_public_key FROM humans
    WHERE id = (SELECT DISTINCT human_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_human_as_inserter($id uuid) public view returns (id uuid, current_public_key text, inserter text) {
    get_inserter();
    for $row in SELECT * FROM humans WHERE id = $id {
        return $row.id, $row.current_public_key, $row.inserter;
    }
}


// WALLET ACTIONS

// TODO: change to procedure
action upsert_wallet_as_inserter($id, $human_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, human_id, address, public_key, wallet_type, message, signature, inserter)
    VALUES ($id, $human_id, $address, $public_key, $wallet_type, $message, $signature, get_inserter())
    ON CONFLICT(id) DO UPDATE
    SET human_id=$human_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;
}

// Do we need to ask user/sdk to provide id? It is possible now to generate the `id` in the procedure in consensus way
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, human_id, address, public_key, wallet_type, message, signature)
    VALUES (
        $id,
        (SELECT DISTINCT human_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.human_id = w2.human_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 human_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 human_id=(SELECT DISTINCT human_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
, $human_id
, $issuer_auth_public_key
, $encryption_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, human_id, verifiable_credential_id, public_notes, content, encryption_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $human_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryption_public_key,
        $issuer_auth_public_key,
        get_inserter()
    )
    ON CONFLICT(id) DO UPDATE
    SET human_id=$human_id
        , verifiable_credential_id=(CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END)
        , public_notes=$public_notes
        , content=$content
        , encryption_public_key=$encryption_public_key
        , issuer_auth_public_key=$issuer_auth_public_key
        , inserter=get_inserter()
    ;
}

action add_credential (
  $id
, $issuer_auth_public_key
, $encryption_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, human_id, verifiable_credential_id, public_notes, content, encryption_public_key, issuer_auth_public_key)
    VALUES (
        $id,
        (SELECT DISTINCT human_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,
        $encryption_public_key,
        $issuer_auth_public_key
    );
}

action add_credential_by_write_grant (
  $id
, $human_id
, $issuer_auth_public_key
, $encryption_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
) public {
    SELECT get_write_grant_id($human_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, human_id, verifiable_credential_id, public_notes, content, encryption_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $human_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryption_public_key,
        $issuer_auth_public_key,
        get_write_grant_id($human_id)::text
    );
}

// TODO: change to procedure
@kgw(authn='true')
action get_credentials() public view {
    SELECT DISTINCT c.id, c.human_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.duplicate_id
    INNER JOIN wallets ON c.human_id = wallets.human_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
, $encryption_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.duplicate_id
                WHERE c.id = $id
                AND c.human_id=(SELECT DISTINCT human_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
        , encryption_public_key=$encryption_public_key
        , issuer_auth_public_key=$issuer_auth_public_key
    WHERE id=$id
    AND human_id=(SELECT DISTINCT human_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_grants($id) {
        error('there are locked grants for this credential');
    }
    DELETE FROM credentials
    WHERE id=$id
    AND human_id=(SELECT DISTINCT human_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
        OR (wallet_type = 'NEAR' AND public_key = @caller)
    );

    DELETE FROM internal_access_grants WHERE data_id = $id;
}

// Do we need this procedure? Can share_credential_through_dag cover all cases?
action share_credential (
  $id
, $original_credential_id
, $public_notes
, $public_notes_signature
, $broader_signature
, $content
, $encryption_public_key
, $issuer_auth_public_key
) 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
      , $encryption_public_key
      , $content
      , $public_notes
      , $public_notes_signature
      , $broader_signature
    );
    INSERT INTO shared_credentials (original_id, duplicate_id) VALUES ($original_credential_id, $id);
}

// Who is using this? Could very well be nobody.
action share_credential_through_dag (
  $id
, $human_id
, $issuer_auth_public_key
, $encryption_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
, $original_credential_id
, $dag_owner
, $dag_grantee
, $dag_locked_until
, $dag_signature
) public {
    SELECT CASE WHEN NOT EXISTS (
        SELECT 1 from credentials
            WHERE id = $original_credential_id
            AND human_id = $human_id
    ) THEN
        error('The original credential does not belong to the human')
    END;

    $owner_verified = idos.verify_owner($dag_owner, $dag_grantee, $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 humans
            INNER JOIN wallets ON humans.id = wallets.human_id
            WHERE wallet_type = 'EVM'
            AND address=$dag_owner COLLATE NOCASE
            AND human_id = $human_id
    ) THEN
        error('the DAG is not signed by the human')
    END;

    $verifiable_credential_id = idos.get_verifiable_credential_id($public_notes);

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

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

action share_credential_by_write_grant (
  $id
, $human_id
, $encryption_public_key
, $content
, $public_notes
, $public_notes_signature
, $broader_signature
, $original_credential_id
, $grantee
, $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($human_id); // throw an error if no write_grant

    SELECT CASE WHEN NOT credential_belongs_to_human($original_credential_id, $human_id) THEN
        error('the original credential does not belong to the human')
    END;

    SELECT CASE WHEN get_write_grant_id($human_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 internal_access_grants WHERE id = uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s-%s', $grantee::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, human_id, verifiable_credential_id, public_notes, content, encryption_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $id,
        $human_id,
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $public_notes,
        $content,
        $encryption_public_key,
        $issuer_auth_public_key,
        get_write_grant_id($human_id)::text
    );

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

    INSERT INTO internal_access_grants (
        id,
        ag_owner_human_id,
        ag_grantee,
        data_id,
        locked_until,
        write_grant_id
    ) VALUES (
        uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s-%s', $grantee::text, $original_credential_id::text, $locked_until::text)),
        $human_id,
        $grantee,
        $id,
        $locked_until::int,
        get_write_grant_id($human_id)
    );
}

@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.human_id, c.public_notes, c.content, c.encryption_public_key, c.issuer_auth_public_key, c.inserter
    FROM credentials AS c
    INNER JOIN wallets ON c.human_id = wallets.human_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
, human_id uuid
, public_notes text
, content text
, encryption_public_key text
, issuer_auth_public_key text
, inserter text
) {
    if !credential_exist($id) {
        error('the credential does not exist');
    }

    $ext_ag_granted bool := false;
    $ext_ag_grant_valid bool := false;
    $int_ag_granted bool := false;

    for $ext_ag_row in SELECT status FROM external_access_grants WHERE data_id = $id::TEXT AND ag_grantee = @caller COLLATE NOCASE {
        $ext_ag_granted := true;
        if $ext_ag_row.status == 'valid' {
            $ext_ag_grant_valid := true;
            break;
        }
    }

    for $int_ag_row in SELECT 1 FROM internal_access_grants WHERE data_id = $id AND ag_grantee = @caller COLLATE NOCASE {
        $int_ag_granted := true;
    }

    // We only keep valid internal AGs. So, we only need to double-check the validity for external AGs.
    if !$int_ag_granted {
        if !$ext_ag_granted {
            error('the credential is not shared with the caller');
        }

        if !$ext_ag_grant_valid {
            error('the credential has no valid access grant; for more details, call list_external_ag_statuses');
        }
    }

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

procedure credential_belongs_to_human($id uuid, $human_id uuid) private view returns (belongs bool) {
    for $row in SELECT 1 from credentials WHERE id = $id AND human_id = $human_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 human_id=(SELECT DISTINCT human_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, $human_id uuid, $attribute_key text, $value text) public {
    INSERT INTO human_attributes (id, human_id, attribute_key, value, inserter)
    VALUES ($id, $human_id, $attribute_key, $value, get_inserter());
}

procedure add_attribute($id uuid, $attribute_key text, $value text) public {
    INSERT INTO human_attributes (id, human_id, attribute_key, value)
    VALUES (
        $id,
        (SELECT DISTINCT human_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.human_id, ha.attribute_key, ha.value, sha.original_id AS original_id
    FROM human_attributes AS ha
    LEFT JOIN shared_human_attributes AS sha ON ha.id = sha.duplicate_id
    INNER JOIN wallets ON ha.human_id = wallets.human_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 human_attributes AS ha
                INNER JOIN shared_human_attributes AS sha on ha.id = sha.duplicate_id
                WHERE ha.id = $id
                AND ha.human_id=(SELECT DISTINCT human_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 human_attributes
    SET attribute_key=$attribute_key, value=$value
    WHERE id=$id
    AND human_id=(SELECT DISTINCT human_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 human_attributes
    WHERE id=$id
    AND human_id=(SELECT DISTINCT human_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 human_attributes (id, human_id, attribute_key, value)
    VALUES (
        $id,
        (SELECT DISTINCT human_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_human_attributes (original_id, duplicate_id)
    VALUES ($original_attribute_id, $id);
}


// EXTERNAL ACCESS GRANTS ACTIONS

@kgw(authn='true')
procedure list_external_ag_statuses($data_id text) public view returns table (ag_owner text, ag_grantee text, locked_until int, status text) {
    return
        SELECT ag_owner, ag_grantee, locked_until, status
        FROM external_access_grants
        WHERE data_id = $data_id
          AND ag_grantee = @caller;
}

// This procedure is used in an oracle resolution to determine the status of a AG at the moment of adding it into idOS
// It needs to be done at this moment because of later defining data can change (original credential deleted, owner wallet or profile removed etc.)
procedure define_ag_status_as_owner($ag_owner text, $ag_grantee text, $data_id uuid) public view owner returns (status text) {
    if !credential_exist($data_id) {
        return 'invalid_data_id_not_found';
    }

    $data_id_is_duplicate := false;
    for $row in SELECT 1 FROM shared_credentials WHERE duplicate_id = $data_id LIMIT 1 {
        $data_id_is_duplicate := true;
    }
    if !$data_id_is_duplicate {
        return 'invalid_data_id_is_not_a_duplicate';
    }

    for $row2 in SELECT 1 FROM wallets
        INNER JOIN credentials ON credentials.human_id = wallets.human_id
        WHERE credentials.id = $data_id
        AND ((wallets.address = $ag_owner COLLATE NOCASE AND wallets.wallet_type = 'EVM')
            OR (wallets.public_key = $ag_owner COLLATE NOCASE AND wallets.wallet_type = 'NEAR'))
        LIMIT 1 {
            return 'valid';
        }

    return 'invalid_ag_owner_does_not_match_original_credential_owner';
}


// WRITE GRANTS ACTIONS

procedure add_write_grant($wg_grantee text) public {
    INSERT INTO write_grants (id, wg_owner, wg_grantee, wg_owner_human_id) VALUES (
        uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::uuid, format('%s-%s', @caller, $wg_grantee)),
        @caller,
        $wg_grantee,
        (SELECT DISTINCT human_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 text) public {
    DELETE FROM write_grants
        WHERE wg_grantee = $wg_grantee COLLATE NOCASE
        AND wg_owner_human_id = (SELECT DISTINCT human_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($human_id uuid) private view returns (id uuid) {
    for $row in SELECT id FROM write_grants WHERE wg_owner_human_id = $human_id AND wg_grantee = @caller COLLATE NOCASE LIMIT 1 {
        return $row.id;
    }

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

@kgw(authn='true')
procedure has_write_grant_given_to($grantee text) public view returns (has_given bool) {
    for $row in SELECT 1 FROM write_grants
        WHERE wg_grantee = $grantee COLLATE NOCASE
        AND wg_owner_human_id = (SELECT DISTINCT human_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($human_id uuid) public view returns (has_given bool) {
    for $row in SELECT 1 FROM write_grants
        WHERE wg_owner_human_id = $human_id
        AND wg_grantee = @caller COLLATE NOCASE {
            return true;
        }
    return false;
}


// INTERNAL ACCESS GRANTS ACTIONS

procedure revoke_internal_access_grant ($id uuid) public {
    $iag_exist := false;
    for $row in SELECT 1 FROM internal_access_grants WHERE id = $id
        AND ag_owner_human_id = (SELECT DISTINCT human_id FROM wallets WHERE (wallet_type = 'EVM' AND address=@caller COLLATE NOCASE)
            OR (wallet_type = 'NEAR' AND public_key = @caller)) {
        $iag_exist := true;
    }

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

    for $row2 in SELECT 1 FROM internal_access_grants WHERE id = $id
        AND locked_until >= @block_timestamp
        AND ag_owner_human_id = (SELECT DISTINCT human_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 internal_access_grants
    WHERE id = $id
    AND ag_owner_human_id = (SELECT DISTINCT human_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_internal_ag_owned () public view returns table (id uuid, ag_owner_human_id uuid,
    ag_grantee text, data_id uuid, locked_until int, write_grant_id uuid) {
    return SELECT id, ag_owner_human_id, ag_grantee, data_id, locked_until, write_grant_id FROM internal_access_grants
        WHERE ag_owner_human_id = (SELECT DISTINCT human_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_internal_ag_granted () public view returns table (id uuid, ag_owner_human_id uuid,
    ag_grantee text, data_id uuid, locked_until int) {
    return SELECT id, ag_owner_human_id, ag_grantee, data_id, locked_until FROM internal_access_grants
        WHERE ag_grantee =  @caller;
}


// BOTH GRANTS ACTIONS

@kgw(authn='true')
procedure has_locked_grants($id uuid) public view returns (has bool) {
    for $ext_row in SELECT 1 FROM external_access_grants
            WHERE data_id = $id::TEXT
            AND ag_owner = @caller COLLATE NOCASE
            AND status = 'valid'
            AND locked_until >= @block_timestamp LIMIT 1 {
        return true;
    }

    for $int_row in SELECT 1 FROM internal_access_grants
            WHERE data_id = $id
            AND ag_owner_human_id = (SELECT DISTINCT human_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_human_as_owner($id uuid, $current_public_key text, $inserter text) owner public {
    INSERT INTO humans (id, current_public_key, inserter)
    VALUES ($id, $current_public_key, $inserter);
}

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

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

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

procedure insert_human_attribute_as_owner($id uuid, $human_id uuid, $attribute_key text, $value text, $inserter text) owner public {
    INSERT INTO human_attributes (id, human_id, attribute_key, value, inserter)
    VALUES ($id, $human_id, $attribute_key, $value, $inserter);
}

procedure insert_shared_attr_as_owner($original_id uuid, $duplicate_id uuid) owner public {
    INSERT INTO shared_human_attributes (original_id, duplicate_id)
    VALUES ($original_id, $duplicate_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_external_ags_as_owner($id uuid, $ag_owner text, $ag_grantee text, $data_id text,
    $locked_until int, $ag_chain text, $tx_hash text, $block_hash text, $height int, $status text
    ) owner public {
    INSERT INTO external_access_grants (id, ag_owner, ag_grantee, data_id, locked_until, ag_chain,
        tx_hash, block_hash, height, status)
    VALUES ($id, $ag_owner, $ag_grantee, $data_id, $locked_until, $ag_chain,
        $tx_hash, $block_hash, $height, $status);
}

procedure insert_internal_ags_as_owner($id uuid, $ag_owner_human_id uuid, $ag_grantee text, $data_id uuid,
    $locked_until int, $write_grant_id uuid) owner public {
    INSERT INTO internal_access_grants (id, ag_owner_human_id, ag_grantee, data_id, locked_until, write_grant_id)
    VALUES ($id, $ag_owner_human_id, $ag_grantee, $data_id, $locked_until, $write_grant_id);
}

procedure insert_write_grants_as_owner($id uuid, $wg_owner text, $wg_grantee text, $wg_owner_human_id uuid) owner public {
    INSERT INTO write_grants (id, wg_owner, wg_grantee, wg_owner_human_id)
    VALUES ($id, $wg_owner, $wg_grantee, $wg_owner_human_id);
}


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

procedure all_humans_as_owner() public view owner returns table (id uuid, current_public_key text, inserter text) {
    return SELECT id, current_public_key, inserter FROM humans;
}

foreign procedure get_all_humans() returns table(id uuid, current_public_key text, inserter text)

procedure migrate_humans($dbid text) public owner {
    for $row in SELECT id, current_public_key, inserter FROM get_all_humans[$dbid, 'all_humans_as_owner']() {
        INSERT INTO humans (id, current_public_key, inserter) VALUES ($row.id, $row.current_public_key, $row.inserter);
    }
}

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

foreign procedure get_all_wallets() returns table (id uuid, human_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, human_id, address, public_key, wallet_type, message, signature, inserter FROM get_all_wallets[$dbid, 'all_wallets_as_owner']() {
        INSERT INTO wallets (id, human_id, address, public_key, wallet_type, message, signature, inserter)
            VALUES ($row.id, $row.human_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
, human_id uuid
, verifiable_credential_id text
, public_notes text
, content text
, encryption_public_key text
, issuer_auth_public_key text
, inserter text
) {
    return SELECT id, human_id, verifiable_credential_id, public_notes, content, encryption_public_key, issuer_auth_public_key, inserter FROM credentials;
}

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

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


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

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

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


procedure all_human_attributes_as_owner() public view owner returns table (id uuid, human_id uuid, attribute_key text,
    value text, inserter text) {
    return SELECT id, human_id, attribute_key, value, inserter FROM human_attributes;
}

foreign procedure get_all_human_attributes() returns table (id uuid, human_id uuid, attribute_key text, value text, inserter text)

procedure migrate_human_attributes($dbid text) public owner {
    for $row in SELECT id, human_id, attribute_key, value, inserter FROM get_all_human_attributes[$dbid, 'all_human_attributes_as_owner']() {
        INSERT INTO human_attributes (id, human_id, attribute_key, value, inserter)
        VALUES ($row.id, $row.human_id, $row.attribute_key, $row.value, $row.inserter);
    }
}


procedure all_shared_human_attrs_as_owner() public view owner returns table (original_id uuid, duplicate_id uuid) {
    return SELECT original_id, duplicate_id FROM shared_human_attributes;
}

foreign procedure get_all_shared_human_attrs() returns table (original_id uuid, duplicate_id uuid)

procedure migrate_shared_human_attrs($dbid text) public owner {
    for $row in SELECT original_id, duplicate_id FROM get_all_shared_human_attrs[$dbid, 'all_shared_human_attrs_as_owner']() {
        INSERT INTO shared_human_attributes (original_id, duplicate_id) VALUES ($row.original_id, $row.duplicate_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_external_grants_as_owner() public view owner returns table (id uuid, ag_owner text, ag_grantee text,
    data_id text, locked_until int, ag_chain text, tx_hash text, height int, block_hash text) {
    return SELECT id, ag_owner, ag_grantee, data_id, locked_until, ag_chain, tx_hash, height, block_hash FROM external_access_grants;
}

foreign procedure get_all_external_grants() returns table (id uuid, ag_owner text, ag_grantee text,
    data_id text, locked_until int, ag_chain text, tx_hash text, height int, block_hash text)

procedure migrate_external_grants($dbid text) public owner {
    for $row in SELECT id, ag_owner, ag_grantee, data_id, locked_until, ag_chain, tx_hash, height, block_hash
        FROM get_all_external_grants[$dbid, 'all_external_grants_as_owner']() {
        INSERT INTO external_access_grants (id, ag_owner, ag_grantee, data_id, locked_until, ag_chain, tx_hash, height, block_hash)
            VALUES ($row.id, $row.ag_owner, $row.ag_grantee, $row.data_id, $row.locked_until, $row.ag_chain,
            $row.tx_hash, $row.height, $row.block_hash);
    }
}


procedure all_internal_grants_as_owner() public view owner returns table (id uuid, ag_owner_human_id uuid, ag_grantee text,
    data_id uuid, locked_until int, write_grant_id uuid) {
    return SELECT id, ag_owner_human_id, ag_grantee, data_id, locked_until, write_grant_id FROM internal_access_grants;
}

foreign procedure get_all_internal_grants() returns table (id uuid, ag_owner_human_id uuid, ag_grantee text,
    data_id uuid, locked_until int, write_grant_id uuid)

procedure migrate_internal_grants($dbid text) public owner {
    for $row in SELECT id, ag_owner_human_id, ag_grantee, data_id, locked_until, write_grant_id
        FROM get_all_internal_grants[$dbid, 'all_internal_grants_as_owner']() {
        INSERT INTO internal_access_grants (id, ag_owner_human_id, ag_grantee, data_id, locked_until, write_grant_id)
            VALUES ($row.id, $row.ag_owner_human_id, $row.ag_grantee, $row.data_id, $row.locked_until, $row.write_grant_id);
    }
}


procedure all_write_grants_as_owner() public view owner returns table (id uuid, wg_owner text, wg_grantee text,
    wg_owner_human_id uuid) {
    return SELECT id, wg_owner, wg_grantee, wg_owner_human_id FROM write_grants;
}

foreign procedure get_all_write_grants() returns table (id uuid, wg_owner text, wg_grantee text, wg_owner_human_id uuid)

procedure migrate_write_grants($dbid text) public owner {
    for $row in SELECT id, wg_owner, wg_grantee, wg_owner_human_id FROM get_all_write_grants[$dbid, 'all_write_grants_as_owner']() {
        INSERT INTO write_grants (id, wg_owner, wg_grantee, wg_owner_human_id)
            VALUES ($row.id, $row.wg_owner, $row.wg_grantee, $row.wg_owner_human_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_humans($old_dbid);
    migrate_wallets($old_dbid);
    migrate_credentials($old_dbid);
    migrate_shared_credentials($old_dbid);
    migrate_human_attributes($old_dbid);
    migrate_shared_human_attrs($old_dbid);
    migrate_inserters($old_dbid);
    migrate_delegates($old_dbid);
    migrate_external_grants($old_dbid);
    migrate_write_grants($old_dbid); // WGs must be inserted before internal AGs
    migrate_internal_grants($old_dbid);
    migrate_configs($old_dbid);
}