-- QUERY START:
create table tig_socks5_users (
	-- unique uid for fast lookup
	uid BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
	-- jid of user
	user_id varchar(2049) NOT NULL,

	-- domain part of jid of user
	"domain" varchar(2049) NOT NULL,

	-- limit of file size 
	filesize_limit bigint default 0,
	-- limit of transfer per user (0 - get default, -1 - deny any transfer)
	transfer_limit_per_user bigint default 0,
	-- limit of transfer per domain (0 - get default, -1 - deny any transfer)
	transfer_limit_per_domain bigint default 0
);
-- QUERY END:

-- QUERY START:
create unique index tig_socks5_users_user_id on tig_socks5_users ( user_id );
-- QUERY END:

-- QUERY START:
create unique index tig_socks5_users_domain on tig_socks5_users ( "domain" );
-- QUERY END:

-- QUERY START:
create table tig_socks5_connections (

	-- id of connection for fast lookup for update
	conn_id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,

	-- uid of user (uid of jid)
	uid bigint NOT NULL references tig_socks5_users(uid),

    -- server instance used as proxy
    instance varchar(128) NOT NULL,

	-- direction of transfer, 0-in, 1-out
	direction int NOT NULL,

	-- count of bytes transferred thru connections
	transferred_bytes bigint default 0,

	-- timestamp of last part transfer
	transfer_timestamp timestamp DEFAULT CURRENT_TIMESTAMP
);
-- QUERY END:

-- QUERY START:
create index tig_socks5_connections_uid on tig_socks5_connections ( uid );
-- QUERY END:

-- QUERY START:
create index tig_socks5_connections_uid_transfer_timestamp on tig_socks5_connections ( uid, transfer_timestamp );
-- QUERY END:

-- QUERY START:
create index tig_socks5_connections_instance_transfer_timestamp on tig_socks5_connections ( instance, transfer_timestamp );
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5CreateUid(userId varchar(2049), "domain" varchar(255)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5CreateUid';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5GetUid(userId varchar(2049)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5GetUid';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5GetTransferLimits(userId varchar(2049)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5GetTransferLimits';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5TransferUsedGeneral() 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5TransferUsedGeneral';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5TransferUsedInstance(instance varchar(128)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5TransferUsedInstance';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5TransferUsedDomain("domain" varchar(2049)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5TransferUsedDomain';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5TransferUsedUser(uid bigint) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5TransferUsedUser';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5CreateTransferUsed(uid bigint, direction int, instance varchar(128)) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5CreateTransferUsed';
-- QUERY END:

-- QUERY START:
CREATE procedure TigSocks5UpdateTransferUsed(cid bigint, transferredBytes bigint) 
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.socks5.repository.derby.StoredProcedures.tigSocks5UpdateTransferUsed';
-- QUERY END:
