schema.sql

-- PRIVILEGE SETTINGS

REVOKE IF GRANTED SELECT ON main FROM default;


-- EXTENSION INITIALIZATION

USE IF NOT EXISTS idos AS idos;


-- TABLES

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    recipient_encryption_public_key TEXT NOT NULL,
    encryption_password_store TEXT NOT NULL CHECK (encryption_password_store IN ('user', 'mpc')),
    inserter TEXT NOT NULL,
    gas_allowance NUMERIC(78,0) NOT NULL DEFAULT '100000000000000000000'::NUMERIC(78,0) -- 100 * 10^18
);

-- for EVM type, address is EVM case insensitive 20 bytes address like 0x5ccbe82FEDE13aecdA449eCA4D4dE05E45861684, and public key can be any or null
-- for NEAR type, address is like alex.testnet, and public key is base58 in format ed25519:6YMr5ggaCe9AtiQNeh2spn8iff72QVHyaXvu4aKsyWuB
-- for XRPL type, address is case sensitive like rHb9CJAWyB4rj91VRWn96Dk6kG4b4dtyTh, and public key is 32-byte Ed25519 public key in hex format, prefixed by `ED`
-- for Stellar type, address is case sensitive like GCFXQ3PM5Y6V4KXQ5Y4L, and public key is 32 byte Ed25519 public key in StrKey format, starting with `G`
-- for FaceSign type, address and public key are the same, it's the ed25519 public key in hex format

CREATE TABLE IF NOT EXISTS wallets (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    address TEXT NOT NULL,
    public_key TEXT,
    wallet_type TEXT NOT NULL CHECK (wallet_type IN ('EVM', 'NEAR', 'XRPL', 'Stellar', 'FaceSign')),
    message TEXT,
    signature TEXT,
    inserter TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS wallets_user_id ON wallets(user_id);
CREATE INDEX IF NOT EXISTS wallets_address_scan ON wallets(address, wallet_type);
CREATE INDEX IF NOT EXISTS wallets_public_key_scan ON wallets(public_key, wallet_type);

CREATE TABLE IF NOT EXISTS credentials (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    verifiable_credential_id TEXT,
    public_notes TEXT NOT NULL,
    content TEXT NOT NULL,
    encryptor_public_key TEXT NOT NULL,
    issuer_auth_public_key TEXT NOT NULL,
    inserter TEXT,
    issuer_fee NUMERIC(78,0) NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS credentials_user_id ON credentials(user_id);
CREATE INDEX IF NOT EXISTS credentials_vc_id ON credentials(verifiable_credential_id);

CREATE TABLE IF NOT EXISTS shared_credentials (
    original_id UUID NOT NULL,
    copy_id UUID NOT NULL,
    PRIMARY KEY (original_id, copy_id),
    FOREIGN KEY (original_id) REFERENCES credentials(id) ON DELETE CASCADE,
    FOREIGN KEY (copy_id) REFERENCES credentials(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS shared_credentials_copy_id ON shared_credentials(copy_id);

CREATE TABLE IF NOT EXISTS user_attributes (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    attribute_key TEXT NOT NULL,
    value TEXT NOT NULL,
    inserter TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS user_attributes_user_id ON user_attributes(user_id);

CREATE TABLE IF NOT EXISTS shared_user_attributes (
    original_id UUID NOT NULL,
    copy_id UUID NOT NULL,
    PRIMARY KEY (original_id, copy_id),
    FOREIGN KEY (original_id) REFERENCES user_attributes(id) ON DELETE CASCADE,
    FOREIGN KEY (copy_id) REFERENCES user_attributes(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS shared_user_attributes_copy_id ON shared_user_attributes(copy_id);

CREATE TABLE IF NOT EXISTS inserters (
    id UUID PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS delegates (
    address TEXT PRIMARY KEY, -- identifier of a caller, for ed25519 it is a hex encoded public key
    inserter_id UUID NOT NULL,
    FOREIGN KEY (inserter_id) REFERENCES inserters(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS consumed_write_grants (
    id UUID PRIMARY KEY,
    owner_wallet_identifier TEXT NOT NULL, -- user wallet/pk
    grantee_wallet_identifier TEXT NOT NULL, -- grantee wallet/pk
    issuer_public_key TEXT NOT NULL,
    original_credential_id UUID,
    copy_credential_id UUID,
    access_grant_timelock TEXT,
    not_usable_before TEXT,
    not_usable_after TEXT,
    FOREIGN KEY (original_credential_id) REFERENCES credentials(id) ON DELETE SET NULL,
    FOREIGN KEY (copy_credential_id) REFERENCES credentials(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS access_grants (
    id UUID PRIMARY KEY,
    ag_owner_user_id UUID NOT NULL,
    ag_grantee_wallet_identifier TEXT NOT NULL,
    data_id UUID NOT NULL,
    locked_until INT8 NOT NULL DEFAULT 0,
    content_hash TEXT,
    height int NOT NULL,
    inserter_type TEXT NOT NULL,
    inserter_id TEXT NOT NULL,
    FOREIGN KEY (ag_owner_user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS ag_data_id ON access_grants(data_id);
CREATE INDEX IF NOT EXISTS ag_grantee_content_hash ON access_grants(ag_grantee_wallet_identifier, content_hash);
CREATE INDEX IF NOT EXISTS ag_owner_user_id ON access_grants(ag_owner_user_id);

-- INSERTER AND DELEGATE ACTIONS

-- @generator.description "Add inserter as owner"
CREATE OR REPLACE ACTION add_inserter_as_owner($id UUID, $name TEXT) OWNER PUBLIC {
    INSERT INTO inserters (id, name) VALUES ($id, $name);
};

-- @generator.description "Delete inserter as owner"
CREATE OR REPLACE ACTION delete_inserter_as_owner($id UUID) OWNER PUBLIC {
    DELETE FROM inserters WHERE id = $id;
};

-- @generator.description "Add a delegate as owner"
CREATE OR REPLACE ACTION add_delegate_as_owner($address TEXT, $inserter_id UUID) OWNER PUBLIC {
  INSERT INTO delegates (address, inserter_id) VALUES (lower($address), $inserter_id);
};

-- @generator.description "Delete a delegate from idOS"
CREATE OR REPLACE ACTION delete_delegate_as_owner($address TEXT) OWNER PUBLIC {
  DELETE FROM delegates WHERE address=$address;
};

CREATE OR REPLACE ACTION 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');
};

CREATE OR REPLACE ACTION 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

-- @generator.description "Add a user to idOS"
CREATE OR REPLACE ACTION add_user_as_inserter($id UUID, $recipient_encryption_public_key TEXT, $encryption_password_store TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $inserter := get_inserter();
    INSERT INTO users (id, recipient_encryption_public_key, encryption_password_store, inserter)
        VALUES ($id, $recipient_encryption_public_key, $encryption_password_store, $inserter);
};

-- @generator.description "Update user's encryption key and password store in idOS as inserter (profile creator)"
CREATE OR REPLACE ACTION update_user_pub_key_as_inserter($id UUID, $recipient_encryption_public_key TEXT, $encryption_password_store TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    get_inserter();
    UPDATE users SET recipient_encryption_public_key=$recipient_encryption_public_key, encryption_password_store=$encryption_password_store
        WHERE id = $id;
};

CREATE OR REPLACE ACTION get_user() PUBLIC VIEW RETURNS (id UUID, recipient_encryption_public_key TEXT, encryption_password_store TEXT) {
    for $row in SELECT id, recipient_encryption_public_key, encryption_password_store FROM users
        WHERE id = (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address = @caller COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)) {
        return $row.id, $row.recipient_encryption_public_key, $row.encryption_password_store;
    }
};

CREATE OR REPLACE ACTION get_user_as_inserter($id UUID) PUBLIC VIEW RETURNS (
    id UUID,
    recipient_encryption_public_key TEXT,
    encryption_password_store TEXT,
    inserter TEXT
) {
    get_inserter();
    for $row in SELECT * FROM users WHERE id = $id {
        return $row.id, $row.recipient_encryption_public_key, $row.encryption_password_store, $row.inserter;
    }
};


-- WALLET ACTIONS

-- @generator.paramOptional "public_key"
-- @generator.description "Add a wallet to idOS by inserter (profile creator)"
CREATE OR REPLACE ACTION upsert_wallet_as_inserter(
    $id UUID,
    $user_id UUID,
    $address TEXT,
    $public_key TEXT,
    $wallet_type TEXT,
    $message TEXT,
    $signature TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    if $wallet_type != 'EVM' AND $wallet_type != 'NEAR' AND $wallet_type != 'XRPL' AND $wallet_type != 'Stellar' AND $wallet_type != 'FaceSign' {
        error('unsupported wallet type');
    }

    if !idos.is_wallet_valid($address, $public_key, $wallet_type, $message, $signature) {
        error('wallet is invalid');
    }

    if $wallet_type = 'NEAR' OR $wallet_type = 'XRPL' OR $wallet_type = 'Stellar' OR $wallet_type = 'FaceSign' {
        if $public_key is null {
            error('wallet require a public_key to be given');
        }

        for $row_public_key in SELECT 1 FROM wallets WHERE id != $id AND wallet_type IN ('NEAR', 'Stellar', 'XRPL', 'FaceSign') AND public_key = $public_key {
            error('wallet public key already exists in idos');
        }
    }

    if $wallet_type = 'EVM' OR $wallet_type = 'XRPL' OR $wallet_type = 'Stellar' {
        for $row_address in SELECT 1 FROM wallets WHERE id != $id AND
            ((wallet_type = 'EVM' AND address = $address COLLATE NOCASE) OR (wallet_type IN ('XRPL', 'Stellar') AND address = $address)) {
                error('wallet address already exists in idos');
        }
    }

    $inserter := get_inserter();
    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)
    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=$inserter;
};

-- @generator.paramOptional "public_key"
-- @generator.description "Add a wallet to idOS"
CREATE OR REPLACE ACTION add_wallet(
    $id UUID,
    $address TEXT,
    $public_key TEXT,
    $wallet_type TEXT,
    $message TEXT,
    $signature TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    if $wallet_type != 'EVM' AND $wallet_type != 'NEAR' AND $wallet_type != 'XRPL' AND $wallet_type != 'Stellar' AND $wallet_type != 'FaceSign' {
        error('unsupported wallet type');
    }

    if !idos.is_wallet_valid($address, $public_key, $wallet_type, $message, $signature) {
        error('wallet is invalid');
    }

    if $wallet_type = 'NEAR' OR $wallet_type = 'XRPL' OR $wallet_type = 'Stellar' OR $wallet_type = 'FaceSign' {
        if $public_key is null {
            error('wallet require a public_key to be given');
        }

        for $row_public_key in SELECT 1 FROM wallets WHERE id != $id AND wallet_type IN ('NEAR', 'Stellar', 'XRPL', 'FaceSign') AND public_key = $public_key {
            error('wallet public key already exists in idos');
        }
    }

    if $wallet_type = 'EVM' OR $wallet_type = 'XRPL' OR $wallet_type = 'Stellar' {
        for $row_address in SELECT 1 FROM wallets WHERE id != $id AND
            ((wallet_type = 'EVM' AND address = $address COLLATE NOCASE) OR (wallet_type IN ('XRPL', 'Stellar') AND address = $address)) {
                error('wallet address already exists in idos');
        }
    }

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
        ),
        $address,
        CASE
            WHEN $public_key = '' THEN NULL
            ELSE $public_key
        END,
        $wallet_type,
        $message,
        $signature
    );
};

-- @generator.returnOptional "public_key", "inserter"
CREATE OR REPLACE ACTION get_wallets() PUBLIC VIEW RETURNS table (
    id UUID,
    user_id UUID,
    address TEXT,
    public_key TEXT,
    wallet_type TEXT,
    message TEXT,
    signature TEXT,
    inserter TEXT
) {
    return SELECT DISTINCT w1.id, w1.user_id, w1.address, w1.public_key, w1.wallet_type, w1.message, w1.signature, w1.inserter
        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 IN ('XRPL', 'Stellar') AND w2.address = @caller
        ) OR (
            w2.wallet_type IN ('NEAR', 'FaceSign') AND w2.public_key = @caller
        );
};

-- @generator.description "Remove a wallet from idOS"
CREATE OR REPLACE ACTION remove_wallet($id UUID) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    for $row in SELECT id FROM wallets
        WHERE id = $id
        AND ((wallet_type = 'EVM' AND address = @caller COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = @caller)
            OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller))
        AND EXISTS (
            SELECT count(id) FROM wallets
                WHERE (wallet_type = 'EVM' AND address = @caller COLLATE NOCASE)
                    OR (wallet_type IN ('XRPL', 'Stellar') AND address = @caller)
                    OR (wallet_type IN ('NEAR', 'FaceSign') 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
    );
};


-- CREDENTIAL ACTIONS

-- @generator.description "Add or update a credential in idOS on behalf of a user by permissioned profile creator (inserter) "
CREATE OR REPLACE ACTION upsert_credential_as_inserter (
    $id UUID,
    $user_id UUID,
    $issuer_auth_public_key TEXT,
    $encryptor_public_key TEXT,
    $content TEXT,
    $public_notes TEXT,
    $public_notes_signature TEXT,
    $broader_signature TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $inserter := 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);
    if !$result {
        error('signature is wrong');
    }

    $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,
        $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=$inserter;
};

-- @generator.description "Add a new credential"
CREATE OR REPLACE ACTION add_credential (
    $id UUID,
    $issuer_auth_public_key TEXT,
    $encryptor_public_key TEXT,
    $content TEXT,
    $public_notes TEXT,
    $public_notes_signature TEXT,
    $broader_signature TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    if !$result {
        error('signature is wrong');
    }

    $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 IN ('XRPL', 'Stellar') AND address = @caller)  OR (wallet_type IN ('NEAR', 'FaceSign') 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
    );
};

-- @generator.returnOptional "original_id", "inserter"
CREATE OR REPLACE ACTION get_credentials() PUBLIC VIEW RETURNS table (
    id UUID,
    user_id UUID,
    public_notes TEXT,
    issuer_auth_public_key TEXT,
    inserter TEXT,
    original_id UUID
) {
    return SELECT DISTINCT c.id, c.user_id, c.public_notes, c.issuer_auth_public_key, c.inserter, sc.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 IN ('XRPL', 'Stellar') AND wallets.address = @caller
        ) OR (
            wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = @caller
        );
};

-- @generator.paramOptional "original_issuer_auth_public_key"
-- @generator.returnOptional "original_id", "inserter"
CREATE OR REPLACE ACTION get_credentials_shared_by_user($user_id UUID, $original_issuer_auth_public_key TEXT) PUBLIC VIEW RETURNS table (
    id UUID,
    user_id UUID,
    public_notes TEXT,
    encryptor_public_key TEXT,
    issuer_auth_public_key TEXT,
    inserter TEXT,
    original_id UUID) {
    if $original_issuer_auth_public_key is null {
      return SELECT DISTINCT c.id, c.user_id, oc.public_notes, c.encryptor_public_key, c.issuer_auth_public_key, c.inserter, sc.original_id AS original_id
          FROM credentials AS c
          INNER JOIN access_grants as ag ON c.id = ag.data_id
          INNER JOIN shared_credentials AS sc ON c.id = sc.copy_id
          INNER JOIN credentials as oc ON oc.id = sc.original_id
          WHERE c.user_id = $user_id
              AND ag.ag_grantee_wallet_identifier = @caller COLLATE NOCASE;
    } else {
        return SELECT DISTINCT c.id, c.user_id, oc.public_notes, c.encryptor_public_key, c.issuer_auth_public_key, c.inserter, sc.original_id AS original_id
          FROM credentials AS c
          INNER JOIN access_grants as ag ON c.id = ag.data_id
          INNER JOIN shared_credentials AS sc ON c.id = sc.copy_id
          INNER JOIN credentials as oc ON oc.id = sc.original_id
          WHERE c.user_id = $user_id
            AND oc.issuer_auth_public_key = $original_issuer_auth_public_key
            AND ag.ag_grantee_wallet_identifier = @caller COLLATE NOCASE;
    }
};

-- @generator.description "Edit a credential"
CREATE OR REPLACE ACTION edit_credential (
    $id UUID,
    $public_notes TEXT,
    $public_notes_signature TEXT,
    $broader_signature TEXT,
    $content TEXT,
    $encryptor_public_key TEXT,
    $issuer_auth_public_key TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    -- we forbid to edit a copy
    -- only copies can have AGs, so data_id in AGs is id of a copy
    for $row1 in SELECT 1 from access_grants WHERE data_id = $id {
        error('can not edit shared credential');
    }
    -- if $id is shared_credentials.copy_id - it is a copy
    for $row2 in 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)) {
        error('can not edit shared credential');
    }

    $result = idos.assert_credential_signatures($issuer_auth_public_key, $public_notes, $public_notes_signature, $content, $broader_signature);
    if !$result {
        error('signature is wrong');
    }

    $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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
    );
};

-- Be aware that @caller here is ed25519 public key, hex encoded.
-- 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)
-- @generator.description "Edit public notes in a credential as issuer"
CREATE OR REPLACE ACTION edit_public_notes_as_issuer($public_notes_id TEXT, $public_notes TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    UPDATE credentials SET public_notes = $public_notes
    WHERE issuer_auth_public_key = @caller
        AND verifiable_credential_id = $public_notes_id;
};

-- @generator.description "Remove a credential from your idOS profile"
CREATE OR REPLACE ACTION remove_credential($id UUID) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
    );

    DELETE FROM access_grants WHERE data_id = $id;
};

-- @generator.description "Rescind a shared credential as a grantee"
CREATE OR REPLACE ACTION rescind_shared_credential($credential_id UUID) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $credential_found := false;
    for $row in SELECT 1 FROM credentials AS c
        INNER JOIN access_grants AS ag ON c.id = ag.data_id
        INNER JOIN shared_credentials AS sc ON c.id = sc.copy_id
        INNER JOIN credentials AS oc ON oc.id = sc.original_id
            WHERE c.id = $credential_id AND ag_grantee_wallet_identifier = @caller COLLATE NOCASE LIMIT 1 {
        $credential_found := true;
        break;
    }
    if !$credential_found {
        error('can not find the credential shared to you');
    }

    DELETE FROM credentials WHERE id = $credential_id;
    DELETE FROM access_grants WHERE data_id = $credential_id;
};

-- @generator.description "Share a credential with creating AG"
CREATE OR REPLACE ACTION share_credential (
    $id UUID,
    $original_credential_id UUID,
    $public_notes TEXT,
    $public_notes_signature TEXT,
    $broader_signature TEXT,
    $content TEXT,
    $content_hash TEXT,
    $encryptor_public_key TEXT,
    $issuer_auth_public_key TEXT,
    $grantee_wallet_identifier TEXT,
    $locked_until INT8
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    if !credential_belongs_to_caller($original_credential_id) {
        error('original credential does not belong to the caller');
    }

    if $public_notes != '' {
        error('shared credentials cannot have public_notes, it must be an empty string');
    }

    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);

    create_access_grant(
        $grantee_wallet_identifier,
        $id,
        $locked_until::int,
        $content_hash,
        'user',
        @caller
    );
};

-- Delegated write credential actions

-- @generator.description "Add original credential and copy credential with AG on behalf of a user (using delegated write grant given by the user)"
CREATE OR REPLACE ACTION create_credentials_by_dwg(
    $issuer_auth_public_key TEXT,
    $original_encryptor_public_key TEXT,
    $original_credential_id UUID,
    $original_content TEXT,
    $original_public_notes TEXT,
    $original_public_notes_signature TEXT,
    $original_broader_signature TEXT,
    $copy_encryptor_public_key TEXT,
    $copy_credential_id UUID,
    $copy_content TEXT,
    $copy_public_notes_signature TEXT,
    $copy_broader_signature TEXT,
    $content_hash TEXT, -- For access grant
    $dwg_owner TEXT,
    $dwg_grantee TEXT,
    $dwg_issuer_public_key TEXT,
    $dwg_id UUID,
    $dwg_access_grant_timelock TEXT,
    $dwg_not_before TEXT,
    $dwg_not_after TEXT,
    $dwg_signature TEXT) PUBLIC {

    capture_gas(0::NUMERIC(6,2));

    -- Check the content creator (encryptor) of credentials is the issuer that user delegated to issue the credentials
    $the_same_issuer := false;
    for $row in SELECT 1 FROM delegates d1 INNER JOIN delegates d2 ON d1.inserter_id = d2.inserter_id
        WHERE d1.address = lower($issuer_auth_public_key) AND d2.address = lower($dwg_issuer_public_key) LIMIT 1 {
        $the_same_issuer := true;
        break;
    }
    if !$the_same_issuer {
        error('credentials issuer must be an issuer of delegated write grant');
    }

    -- Get the wallet type and public key for XRPL/NEAR wallets from database
    $dwg_owner_found bool := false;
    $dwg_owner_wallet_type string := '';
    $dwg_owner_public_key string := '';
    for $wallet in SELECT wallet_type, public_key FROM wallets WHERE (wallet_type = 'EVM' AND address = $dwg_owner COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = $dwg_owner)  OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = $dwg_owner) {
        $dwg_owner_found := true;
        $dwg_owner_wallet_type := $wallet.wallet_type;
        $dwg_owner_public_key := $wallet.public_key;
        break;
    }

    if !$dwg_owner_found {
        error('dwg_owner not found');
    }

    -- Will fail if not in the RFC3339 format
    $ag_timelock = idos.parse_date($dwg_access_grant_timelock);

    -- Check the format and precedence
    if !idos.validate_not_usable_times($dwg_not_before, $dwg_not_after) {
        error('dwg_not_before must be before dwg_not_after');
    }

    -- Check if current block timestamp in time range allowed by write grant.
    -- @block_timestamp is a timestamp of previous block, which is can be a few seconds earlier
    -- (max is 6 seconds in current network consensus settings) then a time on a requester's machine.
    -- Also, if requester's machine has wrong time, it can be an issue.
    if parse_unix_timestamp($dwg_not_before, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::int > (@block_timestamp + 65)
            OR @block_timestamp > parse_unix_timestamp($dwg_not_after, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::int {

        error('this write grant can only be used after dwg_not_before and before dwg_not_after');
    }

    $dwg_result = idos.dwg_verify_owner(
        $dwg_owner,
        $dwg_owner_wallet_type,
        $dwg_owner_public_key,
        $dwg_grantee,
        $dwg_issuer_public_key,
        $dwg_id::TEXT,
        $dwg_access_grant_timelock,
        $dwg_not_before,
        $dwg_not_after,
        $dwg_signature
    );
    if !$dwg_result {
        error('verify owner failed');
    }

    $original_result = idos.assert_credential_signatures(
        $issuer_auth_public_key,
        $original_public_notes,
        $original_public_notes_signature,
        $original_content,
        $original_broader_signature
    );
    if !$original_result {
        error('an original credential signature is wrong');
    }


    $copy_result = idos.assert_credential_signatures(
        $issuer_auth_public_key,
        '',
        $copy_public_notes_signature,
        $copy_content,
        $copy_broader_signature
    );
    if !$copy_result {
        error('a copy credential signature is wrong');
    }


    -- Insert original credential
    $verifiable_credential_id = idos.get_verifiable_credential_id($original_public_notes);

    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $original_credential_id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address = $dwg_owner COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = $dwg_owner) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = $dwg_owner)),
        CASE WHEN $verifiable_credential_id = '' THEN NULL ELSE $verifiable_credential_id END,
        $original_public_notes,
        $original_content,
        $original_encryptor_public_key,
        $issuer_auth_public_key,
        $dwg_id::TEXT
    );

    -- Insert copy credential
    INSERT INTO credentials (id, user_id, verifiable_credential_id, public_notes, content, encryptor_public_key, issuer_auth_public_key, inserter)
    VALUES (
        $copy_credential_id,
        (SELECT DISTINCT user_id FROM wallets WHERE (wallet_type = 'EVM' AND address = $dwg_owner COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = $dwg_owner) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = $dwg_owner)),
        NULL,
        '',
        $copy_content,
        $copy_encryptor_public_key,
        $issuer_auth_public_key,
        $dwg_id::TEXT
    );

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

    create_access_grant(
        $dwg_grantee,
        $copy_credential_id,
        $ag_timelock,
        $content_hash,
        'delegated_write_grant',
        $dwg_id::TEXT
    );

    INSERT INTO consumed_write_grants (
        id,
        owner_wallet_identifier,
        grantee_wallet_identifier,
        issuer_public_key,
        original_credential_id,
        copy_credential_id,
        access_grant_timelock,
        not_usable_before,
        not_usable_after
    ) VALUES (
        $dwg_id,
        $dwg_owner,
        $dwg_grantee,
        $dwg_issuer_public_key,
        $original_credential_id,
        $copy_credential_id,
        $dwg_access_grant_timelock,
        $dwg_not_before,
        $dwg_not_after
    );
};

CREATE OR REPLACE ACTION credential_exist_as_inserter($id UUID) PUBLIC VIEW RETURNS (credential_exist BOOL) {
    get_inserter();
    return credential_exist($id);
};

-- @generator.returnOptional "inserter"
CREATE OR REPLACE ACTION get_credential_owned ($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
) {
    return 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 IN ('XRPL', 'Stellar') AND wallets.address = @caller) OR (wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = @caller)
        );
};

-- As a credential copy doesn't contain PUBLIC notes, we return respective original credential PUBLIC notes
-- @generator.returnOptional "inserter"
CREATE OR REPLACE ACTION 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;
    };

CREATE OR REPLACE ACTION get_sibling_credential_id ($content_hash TEXT) PUBLIC VIEW RETURNS (id UUID) {
    for $row in SELECT c.id FROM credentials as c INNER JOIN access_grants as ag ON c.id = ag.data_id
        WHERE ag.content_hash = $content_hash AND ag.ag_grantee_wallet_identifier = @caller COLLATE NOCASE {
            return $row.id;
        }
};

CREATE OR REPLACE ACTION 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)) {
        return true;
    }

    return false;
};

CREATE OR REPLACE ACTION credential_exist($id UUID) PRIVATE VIEW RETURNS (credential_exist BOOL) {
    for $row in SELECT 1 FROM credentials WHERE id = $id {
        return true;
    }
    return false;
};


-- ATTRIBUTE ACTIONS

-- @generator.description "Add a new attribute as inserter"
CREATE OR REPLACE ACTION add_attribute_as_inserter($id UUID, $user_id UUID, $attribute_key TEXT, $value TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $inserter := get_inserter();
    INSERT INTO user_attributes (id, user_id, attribute_key, value, inserter)
    VALUES ($id, $user_id, $attribute_key, $value, $inserter);
};

-- @generator.description  "Create a new attribute in your idOS profile"
CREATE OR REPLACE ACTION add_attribute($id UUID, $attribute_key TEXT, $value TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
        ),
        $attribute_key,
        $value
    );
};

CREATE OR REPLACE ACTION get_attributes() PUBLIC VIEW returns table (
    id UUID,
    user_id UUID,
    attribute_key TEXT,
    value TEXT,
    original_id UUID
) {
    return 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 IN ('XRPL', 'Stellar') AND wallets.address = @caller
        ) OR (
            wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = @caller
        );
};

-- @generator.description "Edit an existing attribute"
CREATE OR REPLACE ACTION edit_attribute($id UUID, $attribute_key TEXT, $value TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    for $row in 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)) {
        error('Can not edit shared attribute');
    }

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
    );
};

-- @generator.description "Remove an existing attribute"
CREATE OR REPLACE ACTION remove_attribute($id UUID) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
    );
};

-- @generator.description "Share an attribute"
CREATE OR REPLACE ACTION share_attribute($id UUID, $original_attribute_id UUID, $attribute_key TEXT, $value TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller)
        ),
        $attribute_key,
        $value
    );

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


-- WRITE GRANTS ACTIONS

CREATE OR REPLACE ACTION dwg_message(
    $owner_wallet_identifier TEXT,
    $grantee_wallet_identifier TEXT,
    $issuer_public_key TEXT,
    $id UUID,
    $access_grant_timelock TEXT, -- Must be in yyyy-mm-ddThh:mm:ssZ format
    $not_usable_before TEXT, -- Must be in yyyy-mm-ddThh:mm:ssZ format
    $not_usable_after TEXT -- Must be in yyyy-mm-ddThh:mm:ssZ format
) PUBLIC VIEW returns (message TEXT) {
    -- Will fail if not in the yyyy-mm-ddThh:mm:ssZ format, and not comply to RFC3339
    idos.parse_date($access_grant_timelock);

    -- Check the format and precedence
    if !idos.validate_not_usable_times($not_usable_before, $not_usable_after) {
        error('not_usable_before must be before not_usable_after');
    }

    return idos.dwg_message(
        $owner_wallet_identifier,
        $grantee_wallet_identifier,
        $issuer_public_key,
        $id::TEXT,
        $access_grant_timelock,
        $not_usable_before,
        $not_usable_after
    );
};


-- ACCESS GRANTS ACTIONS

-- @generator.description "Revoke an Access Grant from idOS"
CREATE OR REPLACE ACTION revoke_access_grant ($id UUID) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller));
};

-- @generator.returnOptional "content_hash"
CREATE OR REPLACE ACTION 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,
    content_hash TEXT,
    inserter_type TEXT,
    inserter_id TEXT
) {
    return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, content_hash, 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') 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 ordering parameter
-- @generator.paramOptional "user_id"
-- @generator.returnOptional "content_hash"
CREATE OR REPLACE ACTION get_access_grants_granted ($user_id UUID, $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,
    content_hash TEXT,
    inserter_type TEXT,
    inserter_id TEXT
) {
    $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;

    if $user_id is null {
      return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, content_hash, inserter_type, inserter_id
        FROM access_grants
        WHERE ag_grantee_wallet_identifier = @caller COLLATE NOCASE
        ORDER BY height ASC, id ASC LIMIT $limit OFFSET $offset;
    } else {
      return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, content_hash, inserter_type, inserter_id
        FROM access_grants
        WHERE ag_grantee_wallet_identifier = @caller COLLATE NOCASE
            AND ag_owner_user_id = $user_id
        ORDER BY height ASC, id ASC LIMIT $limit OFFSET $offset;
    }
};

-- @generator.paramOptional "user_id"
CREATE OR REPLACE ACTION get_access_grants_granted_count ($user_id UUID) PUBLIC VIEW RETURNS (count INT) {
    if $user_id is null {
      for $row in SELECT COUNT(1) as count FROM access_grants WHERE ag_grantee_wallet_identifier =  @caller COLLATE NOCASE {
        return $row.count;
      }
    } else {
      for $row in SELECT COUNT(1) as count FROM access_grants
        WHERE ag_grantee_wallet_identifier =  @caller COLLATE NOCASE
            AND ag_owner_user_id = $user_id {
        return $row.count;
      }
    }
};

CREATE OR REPLACE ACTION 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 IN ('XRPL', 'Stellar') AND address = @caller) OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = @caller))
            AND locked_until >= @block_timestamp LIMIT 1 {
        return true;
    }

    return false;
};

CREATE OR REPLACE ACTION dag_message(
    $dag_owner_wallet_identifier TEXT,
    $dag_grantee_wallet_identifier TEXT,
    $dag_data_id UUID,
    $dag_locked_until INT,
    $dag_content_hash TEXT
) PUBLIC VIEW returns (message TEXT) {
    return idos.dag_message(
        $dag_owner_wallet_identifier,
        $dag_grantee_wallet_identifier,
        $dag_data_id::TEXT,
        $dag_locked_until,
        $dag_content_hash
    );
};

-- @generator.description "Create an Access Grant in idOS"
CREATE OR REPLACE ACTION create_ag_by_dag_for_copy(
    $dag_owner_wallet_identifier TEXT,
    $dag_grantee_wallet_identifier TEXT,
    $dag_data_id UUID,
    $dag_locked_until INT,
    $dag_content_hash TEXT,
    $dag_signature TEXT
) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    -- Get the wallet type and public key for XRPL/NEAR wallets from database
    $dag_owner_found bool := false;
    $dag_owner_wallet_type string := '';
    $dag_owner_public_key string := '';
    for $wallet in SELECT wallet_type, public_key FROM wallets WHERE (wallet_type = 'EVM' AND address = $dag_owner_wallet_identifier COLLATE NOCASE)
            OR (wallet_type IN ('XRPL', 'Stellar') AND address = $dag_owner_wallet_identifier)
            OR (wallet_type IN ('NEAR', 'FaceSign') AND public_key = $dag_owner_wallet_identifier) {
        $dag_owner_found := true;
        $dag_owner_wallet_type := $wallet.wallet_type;
        $dag_owner_public_key := $wallet.public_key;
        break;
    }
    if !$dag_owner_found {
        error('dag_owner not found');
    }

    -- This works for EVM-compatible signatures only
    $owner_verified = idos.verify_owner(
        $dag_owner_wallet_identifier,
        $dag_owner_wallet_type,
        $dag_owner_public_key,
        $dag_grantee_wallet_identifier,
        $dag_data_id::TEXT,
        $dag_locked_until,
        $dag_content_hash,
        $dag_signature
    );
    if !$owner_verified {
        error('the dag_signature is invalid');
    }

    $data_id_belongs_to_owner bool := false;
    for $row in SELECT 1 from credentials
            INNER JOIN wallets ON credentials.user_id = wallets.user_id
            WHERE credentials.id = $dag_data_id
            AND ((wallets.wallet_type = 'EVM' AND wallets.address = $dag_owner_wallet_identifier COLLATE NOCASE)
                OR (wallets.wallet_type IN ('XRPL', 'Stellar') AND wallets.address = $dag_owner_wallet_identifier)
                OR (wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = $dag_owner_wallet_identifier)) {
        $data_id_belongs_to_owner := true;
        break;
    }
    if !$data_id_belongs_to_owner {
        error('the data_id does not belong to the owner');
    }

    create_access_grant(
        $dag_grantee_wallet_identifier,
        $dag_data_id,
        $dag_locked_until::int,
        $dag_content_hash,
        'dag_message',
        @caller
    );
};

-- @generator.description "Create a new access grant"
CREATE OR REPLACE ACTION create_access_grant(
    $grantee_wallet_identifier TEXT,
    $data_id UUID,
    $locked_until INT,
    $content_hash TEXT,
    $inserter_type TEXT,
    $inserter_id TEXT
) PRIVATE {
    $user_id TEXT := '';
    for $row in SELECT user_id from credentials WHERE id = $data_id {
        $user_id := $row.user_id::TEXT;
        break;
    }
    -- data_id is an id of a copy. It always has a user. So if no user found then there is no credential found.
    if $user_id == '' {
        error('the credential, that the AG is for, does not exist');
    }

    for $row3 in SELECT 1 FROM access_grants WHERE id = uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::UUID, format('%s-%s-%s', $grantee_wallet_identifier, $data_id, $locked_until::TEXT)) {
        error('a grant with the same grantee, copy_credential_id, and locked_until already exists');
    }


    INSERT INTO access_grants (
        id,
        ag_owner_user_id,
        ag_grantee_wallet_identifier,
        data_id,
        locked_until,
        content_hash,
        height,
        inserter_type,
        inserter_id
    ) VALUES (
        uuid_generate_v5('31276fd4-105f-4ff7-9f64-644942c14b79'::UUID, format('%s-%s-%s', $grantee_wallet_identifier, $data_id, $locked_until::TEXT)),
        $user_id::UUID,
        $grantee_wallet_identifier,
        $data_id,
        $locked_until,
        $content_hash,
        @height,
        $inserter_type,
        $inserter_id
    );
};

-- @generator.returnOptional "content_hash"
CREATE OR REPLACE ACTION get_access_grants_for_credential($credential_id UUID) PUBLIC VIEW RETURNS table (
    id UUID,
    ag_owner_user_id UUID,
    ag_grantee_wallet_identifier TEXT,
    data_id UUID,
    locked_until INT,
    content_hash TEXT,
    inserter_type TEXT,
    inserter_id TEXT
) {
    return SELECT id, ag_owner_user_id, ag_grantee_wallet_identifier, data_id, locked_until, content_hash, inserter_type, inserter_id
        FROM access_grants WHERE data_id = $credential_id AND ag_grantee_wallet_identifier = @caller COLLATE NOCASE;
};

-- OTHER ACTIONS

-- Should we improve it to work with near wallets too?
-- @generator.notAuthorized
CREATE OR REPLACE ACTION has_profile($address TEXT) PUBLIC VIEW returns (has_profile BOOL) {
    for $row in SELECT 1 FROM wallets WHERE address=$address COLLATE NOCASE {
        return true;
    }

    return false;
};
-- GAS AND FEES

CREATE OR REPLACE ACTION check_balance($address TEXT, $token TEXT) PUBLIC VIEW RETURNS (balance NUMERIC(78,0)) {
    $balance NUMERIC(78,0);

    if $token == 'IDOS' {
        $balance = idos_token_bridge.balance($address);
    } else if $token == 'USDC' {
        $balance = usdc_token_bridge.balance($address);
    } else {
        ERROR('invalid token');
    }

    RETURN $balance;
};

CREATE OR REPLACE ACTION get_wallet_with_balance($token TEXT) PUBLIC VIEW RETURNS (wallet_address TEXT) {
    $evm_addresses TEXT[];
    IF !has_profile(@caller) {
        -- even if the @caller is not EVM address, there is no harm to try to get the balance, it will return nothing
        -- because bridge.balance can only have records with EVM addresses (it is filled from EVM-compatible contract events)
        $evm_addresses = array_append($evm_addresses, @caller);
    } ELSE {
        FOR $row IN get_wallets() {
            IF $row.wallet_type == 'EVM' {
                $evm_addresses = array_append($evm_addresses, $row.address);
            }
        }
    }

    $balance NUMERIC(78,0);
    FOR $address IN ARRAY $evm_addresses {
        IF $token == 'IDOS' {
            $balance = idos_token_bridge.balance($address);
        } ELSE IF $token == 'USDC' {
            $balance = usdc_token_bridge.balance($address);
        } ELSE {
            ERROR('invalid token');
        }

        IF $balance > 0::NUMERIC(78,0) {
            RETURN $address;
        }
    }

    return null;
};

-- @generator.description "Request a withdrawal of all tokens from idOS to user's EVM wallet"
CREATE OR REPLACE ACTION request_withdrawal($token TEXT) PUBLIC {
    capture_gas(0::NUMERIC(6,2));

    $evm_address := get_wallet_with_balance($token);
    if $evm_address is null {
        ERROR('no wallet with balance found');
    }

    $balance := check_balance($evm_address, $token);

    -- we use lock_admin()+issue() because bridge() is tied to @caller
    IF $token == 'IDOS' {
        idos_token_bridge.lock_admin($evm_address, $balance);
        idos_token_bridge.issue($evm_address, $balance);
    } ELSE IF $token == 'USDC' {
        usdc_token_bridge.lock_admin($evm_address, $balance);
        usdc_token_bridge.issue($evm_address, $balance);
    } ELSE {
        ERROR('invalid token');
    }
};

CREATE OR REPLACE ACTION from_human_units($amount NUMERIC(6,2)) PRIVATE VIEW RETURNS(new_amount NUMERIC(78,0)) {
    $new_amount := ($amount * 100::NUMERIC(6,2))::NUMERIC(78,0);

    RETURN $new_amount * 10000000000000000::NUMERIC(78,0);
};

-- FaceSign should not be able to call this
CREATE OR REPLACE ACTION get_allowance() PUBLIC VIEW RETURNS (gas_allowance NUMERIC(78,0)) {
    RETURN SELECT users.gas_allowance FROM users
        INNER JOIN wallets ON users.id = wallets.user_id
        WHERE (wallets.wallet_type = 'EVM' AND wallets.address = @caller COLLATE NOCASE)
            OR (wallets.wallet_type IN ('XRPL', 'Stellar') AND wallets.address = @caller)
            OR (wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = @caller);
};

-- FaceSign should not be able to call this
-- @generator.description "Update gas allowance for the caller"
CREATE OR REPLACE ACTION update_allowance($amount NUMERIC(78,0)) PRIVATE {
    UPDATE users SET gas_allowance=$amount FROM wallets
        WHERE users.id = wallets.user_id AND
            ((wallets.wallet_type = 'EVM' AND wallets.address = @caller COLLATE NOCASE)
            OR (wallets.wallet_type IN ('XRPL', 'Stellar') AND wallets.address = @caller)
            OR (wallets.wallet_type IN ('NEAR', 'FaceSign') AND wallets.public_key = @caller));
};

-- @generator.description "Capture gas cost from the caller"
CREATE OR REPLACE ACTION capture_gas($amount_human NUMERIC(6,2)) PRIVATE {
    $amount := from_human_units($amount_human);

    IF has_profile(@caller) {
        $allowance NUMERIC(78,0) := get_allowance();

        update_allowance(greatest($allowance - $amount, 0::NUMERIC(78, 0)));

        $amount = greatest($amount - $allowance, 0::NUMERIC(78,0));
    }

    IF $amount > 0::NUMERIC(78,0) {
        $evm_address := get_wallet_with_balance('IDOS');
        if $evm_address is null {
            ERROR('no wallet with balance found');
        }
        idos_token_bridge.lock_admin($evm_address, $amount);
    }
};

-- @generator.description "Get cost of action for 1.2 gas"
CREATE OR REPLACE ACTION action_costing_gas() PUBLIC {
    capture_gas(1.2::NUMERIC(6,2));
};

-- @generator.description "Action that captures IDOS tokens from the caller"
CREATE OR REPLACE ACTION action_costing_idos_token($amount NUMERIC(78,0)) PUBLIC {
    $evm_address := get_wallet_with_balance('IDOS');
    if $evm_address is null {
        ERROR('no wallet with balance found');
    }

    IF $amount > 0::NUMERIC(78,0) {
        idos_token_bridge.lock_admin($evm_address, $amount);
    }
};

CREATE OR REPLACE ACTION get_issuer_fee($credential_id UUID) PUBLIC VIEW RETURNS (issuer_fee NUMERIC(78,0)) {
    FOR $row IN SELECT issuer_fee FROM credentials WHERE id = $credential_id {
        RETURN $row.issuer_fee;
    }

    error('credential not found');
};

-- @generator.description "Capture fee from the credentials"
CREATE OR REPLACE ACTION capture_fee($credential_id UUID) PRIVATE {
    $evm_address := get_wallet_with_balance('USDC');
    if $evm_address is null {
        ERROR('no wallet with balance found');
    }

    $fee := get_issuer_fee($credential_id);
    $amount := ($fee * 125)::NUMERIC(78,0) / 100::NUMERIC(78,0);

    usdc_token_bridge.lock_admin($evm_address, $amount);
};

-- @generator.description "Capture fee from credentials"
CREATE OR REPLACE ACTION action_costing_fee($credential_id UUID) PUBLIC {
    capture_fee($credential_id);
};

-- @generator.description "Request a withdrawal of all tokens from idOS to specified EVM address"
CREATE OR REPLACE ACTION request_balance_withdrawal($token TEXT, $evm_address_to TEXT) OWNER PUBLIC {
    IF $token == 'IDOS' {
        _, _, _, _, _, $balance, _, _, _ := idos_token_bridge.info();
        idos_token_bridge.issue($evm_address_to, $balance);
    } ELSE IF $token == 'USDC' {
        _, _, _, _, _, $balance, _, _, _ := usdc_token_bridge.info();
        usdc_token_bridge.issue($evm_address_to, $balance);
    } ELSE {
        ERROR('invalid token');
    }
}