--
-- Tigase AuditLog Component - Implementation of Audit Log pattern for Tigase XMPP Server
-- Copyright (C) 2017 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

-- QUERY START:
create table if not exists tig_auditlog_entries (
    id binary(16) not null,
	session_id binary(16) not null,
	client_ip varchar(64) not null,
	cluster_node varchar(512) not null,
	domain varchar(1024),
	user_jid varchar(2049) character set utf8mb4 collate utf8mb4_bin,
	res varchar(1024) character set utf8mb4 collate utf8mb4_bin,
	ts timestamp(6) not null,


    `type` varchar(64),
    `subtype` varchar(64),

	`stanza_id` varchar(128),
	`conversation_id` varchar(128),

    errorCondition varchar(64),
    errorMessage mediumtext character set utf8mb4 collate utf8mb4_bin,

    primary key (id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
drop procedure if exists TigExecuteIfNot;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure TigExecuteIfNot(cond int, query text)
begin
set @s = (select if (
        cond <= 0,
        query,
        'select 1'
    ));
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
end //
-- QUERY END:

delimiter ;

-- QUERY START:
call TigExecuteIfNot(
    (
        SELECT count(1)
        FROM information_schema.statistics s1
        WHERE
            s1.table_schema = database()
            AND s1.table_name = 'tig_auditlog_entries'
            AND s1.index_name = 'tig_auditlog_entries_user_jid_type_subtype_ts'
    ),
    "create index tig_auditlog_entries_user_jid_type_subtype_ts on tig_auditlog_entries (user_jid(255), `type`, `subtype`, ts);"
);
-- QUERY END:

-- QUERY START:
call TigExecuteIfNot(
    (
        SELECT count(1)
        FROM information_schema.statistics s1
        WHERE
            s1.table_schema = database()
            AND s1.table_name = 'tig_auditlog_entries'
            AND s1.index_name = 'tig_auditlog_entries_session_id'
    ),
    "create index tig_auditlog_entries_session_id on tig_auditlog_entries (session_id, `type`, `subtype`);"
);
-- QUERY END:

-- QUERY START:
call TigExecuteIfNot(
    (
        SELECT count(1)
        FROM information_schema.statistics s1
        WHERE
            s1.table_schema = database()
            AND s1.table_name = 'tig_auditlog_entries'
            AND s1.index_name = 'tig_auditlog_entries_domain_type_subtype'
    ),
    "create index tig_auditlog_entries_domain_type_subtype on tig_auditlog_entries (domain(255), `type`, `subtype`, ts);"
);
-- QUERY END:

-- QUERY START:
call TigExecuteIfNot(
    (
        SELECT count(1)
        FROM information_schema.statistics s1
        WHERE
            s1.table_schema = database()
            AND s1.table_name = 'tig_auditlog_entries'
            AND s1.index_name = 'tig_auditlog_entries_type_subtype_cluster_node'
    ),
    "create index tig_auditlog_entries_type_subtype_cluster_node on tig_auditlog_entries (`type`, `subtype`, cluster_node);"
);
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_Append;
-- QUERY END:

-- QUERY START:
drop function  if exists Tig_AuditLog_BinaryToUUID;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetActiveConnections;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetLastConnections;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetConnectionsStatistics;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetConnectionsStates;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetConnectedUsers;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_GetDisconnectedUsers;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_AuditLog_ClusterNodeShutdown;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure Tig_AuditLog_Append(
    _id varchar(36),
    _session_id varchar(36),
    _client_ip varchar(64),
    _cluster_node varchar(512),
    _domain varchar(1024) charset utf8mb4 collate utf8mb4_bin,

    _user_jid varchar(2049),
    _res varchar(1024),
    _ts timestamp(6),
    _type varchar(64),
    _subtype varchar(64),

    _stanza_id varchar(128),
    _conversation_id varchar(128),
    _errorCondition varchar(64),
    _errorMessage mediumtext charset utf8mb4 collate utf8mb4_bin)
begin
    insert into tig_auditlog_entries (
        id, session_id, client_ip, cluster_node, domain, user_jid, res, ts, `type`, `subtype`, stanza_id, conversation_id, errorCondition, errorMessage
    ) values (
        UNHEX(REPLACE(_id,'-','')), UNHEX(REPLACE(_session_id,'-','')), _client_ip, _cluster_node, _domain,
        _user_jid, _res, _ts, _type, _subtype, _stanza_id, _conversation_id, _errorCondition, _errorMessage
    );
end //
-- QUERY END:

-- QUERY START:
create function Tig_AuditLog_BinaryToUUID (
    uuid binary(16))
returns varchar(36) deterministic
return LOWER(CONCAT(
    SUBSTR(HEX(uuid), 1, 8), '-',
    SUBSTR(HEX(uuid), 9, 4), '-',
    SUBSTR(HEX(uuid), 13, 4), '-',
    SUBSTR(HEX(uuid), 17, 4), '-',
    SUBSTR(HEX(uuid), 21)
  )) //
-- QUERY END:


-- QUERY START:
create procedure Tig_AuditLog_GetActiveConnections (
    _user_jid varchar(2049),
    _domain varchar(1024))
begin
    select
        Tig_AuditLog_BinaryToUUID(e1.session_id) as session_id, e1.ts, e1.client_ip, e1.cluster_node, TIMESTAMPDIFF(MICROSECOND, e1.ts, now(6)) / 1000000
    from tig_auditlog_entries e1
    where
        (_user_jid is null or e1.user_jid = _user_jid)
        and (_domain is null or e1.domain = _domain)
        and e1.`type` = 'auth' and e1.`subtype` = 'success'
        and not exists (
            select 1
            from tig_auditlog_entries e2
            where
                e2.session_id = e1.session_id
                and e2.`type` = 'auth'
                and e2.`subtype` = 'disconnected'
        );
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_GetLastConnections (
    _user_jid varchar(2049),
    _domain varchar(1024))
begin
    select
        Tig_AuditLog_BinaryToUUID(e1.session_id) as session_id, e1.ts, e1.client_ip, e1.cluster_node, TIMESTAMPDIFF(MICROSECOND, e3.ts, e1.ts) / 1000000
    from tig_auditlog_entries e1
    inner join tig_auditlog_entries e3 on e1.session_id = e3.session_id and e3.`type` = 'auth' and e3.`subtype` = 'success'
    where
        (_user_jid is null or e1.user_jid = _user_jid)
        and (_domain is null or e1.domain = _domain)
        and e1.`type` = 'auth' and e1.`subtype` = 'disconnected'
        and not exists (
            select 1
            from tig_auditlog_entries e2
            where
                e2.user_jid = e1.user_jid
                and e2.`type` = 'auth' and e2.`subtype` = 'success'
                and e2.ts > e1.ts
        );
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_GetConnectionsStatistics (
    _user_jid varchar(2049),
    _from timestamp(6),
    _to timestamp(6))
begin
    select
        (
            select count(1)
            from tig_auditlog_entries ec
            where
                ec.user_jid = _user_jid
                and ec.ts > _from and ec.ts < _to
                and ec.`type` = 'auth'
                and ec.`subtype` = 'success'
        ) as connections,
        (
            select count(1)
            from tig_auditlog_entries ed
            where
                ed.user_jid = _user_jid
                and ed.ts > _from and ed.ts < _to
                and ed.`type` = 'auth'
                and ed.`subtype` = 'disconnected'
        ) as disconnections,
        (
            select count(1)
            from tig_auditlog_entries ed
            where
                ed.user_jid = _user_jid
                and ed.ts > _from and ed.ts < _to
                and ed.`type` = 'auth'
                and ed.`subtype` = 'failure'
        ) as failures,
        (
            select avg(TIMESTAMPDIFF(MICROSECOND, ec.ts, ed.ts) / 1000000)
            from tig_auditlog_entries ec
            inner join tig_auditlog_entries ed on ec.session_id = ed.session_id and ed.`type` = 'auth' and ed.`subtype` = 'disconnected'
            where
                ec.user_jid = _user_jid
                and ec.ts > _from and ec.ts < _to
                and ec.`type` = 'auth'
                and ec.`subtype` = 'success'
            group by ec.user_jid
        ) as avg_duration;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_GetConnectionsStates (
    _user_jid varchar(2049),
    _domain varchar(1024),
    _from timestamp(6),
    _to timestamp(6))
begin
    select
        Tig_AuditLog_BinaryToUUID(ea.session_id) as session_id, ea.ts, ea.user_jid, ea.`type`, ea.`subtype`, ea.client_ip, ea.cluster_node,
         case ea.`subtype`
            when 'disconnected' then TIMESTAMPDIFF(MICROSECOND, ed.ts, ea.ts) / 1000000
            else 0.0
         end as "duration",
         ea.errorCondition, ea.errorMessage
    from tig_auditlog_entries ea
    left join tig_auditlog_entries ed on ed.session_id = ea.session_id and ed.`type` = 'auth' and ed.id <> ea.id and ed.`subtype` = 'success'
    where
        (_user_jid is null or ea.user_jid = _user_jid)
        and (_domain is null or ea.domain = _domain)
        and ea.`type` = 'auth'
        and ea.ts > _from and ea.ts < _to
    order by ea.ts desc;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_GetConnectedUsers (
    _domain varchar(1024),
    _jid_like varchar(2049))
begin
    select *
    from (
        select e1.user_jid, max(e1.ts) as ts, TIMESTAMPDIFF(MICROSECOND, max(e1.ts), now(6)) / 1000000 as duration
        from tig_auditlog_entries e1
        where
            (_domain is null or e1.domain = _domain)
            and e1.`type` = 'auth' and e1.`subtype` = 'success'
            and (_jid_like is null or e1.user_jid like concat('%', _jid_like, '%'))
        group by e1.user_jid
    ) x
    where
        not exists (
            select 1 from  tig_auditlog_entries e2
            where e2.user_jid = x.user_jid
            and e2.`type` = 'auth'
            and e2.`subtype` = 'disconnected'
            and e2.ts > x.ts)
    order by x.user_jid asc;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_GetDisconnectedUsers (
    _domain varchar(1024),
    _jid_like varchar(2049))
begin
    select *
    from (
        select e1.user_jid, max(e1.ts) as ts, TIMESTAMPDIFF(MICROSECOND, max(e1.ts), now(6)) / 1000000 as duration
        from tig_auditlog_entries e1
        where
            (_domain is null or e1.domain = _domain)
            and e1.`type` = 'auth'
            and e1.`subtype` = 'disconnected'
            and (_jid_like is null or e1.user_jid like concat('%', _jid_like, '%'))
        group by e1.user_jid
    ) x
    where
        not exists (
            select 1 from  tig_auditlog_entries e2
            where e2.user_jid = x.user_jid
            and e2.`type` = 'auth'
            and e2.ts > x.ts)
   order by x.user_jid asc;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_AuditLog_ClusterNodeShutdown (
    _cluster_node varchar(512),
    _ts timestamp(6))
begin
    insert into tig_auditlog_entries
        (id, session_id, client_ip, cluster_node, domain, user_jid, res, ts, `type`, `subtype`, stanza_id, conversation_id, errorCondition, errorMessage)
    select
    UNHEX(REPLACE(UUID(),'-','')), e1.session_id, e1.client_ip, e1.cluster_node, e1.domain,
        e1.user_jid, e1.res, _ts, 'auth', 'disconnected', null, null, null, 'Cluster node shutdown'
    from tig_auditlog_entries e1
    left join tig_auditlog_entries e2
        on e2.session_id = e1.session_id
        and e2.`type` = 'auth'
        and e2.`subtype` = 'disconnected'
        and e2.ts > e1.ts
    where
        e1.`type` = 'auth' and e1.`subtype` = 'success'
        and e1.cluster_node = _cluster_node
        and e2.session_id is null;
end //
-- QUERY END:

-- QUERY START:
call TigSetComponentVersion('auditlog', '2.0.0');
-- QUERY END:

delimiter ;