-- Copyright (C) 2026 XlSolucionesTI  <xlsolucionesti@hotmail.com>
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program. If not, see <https://www.gnu.org/licenses/>.

-- Session log table
CREATE TABLE IF NOT EXISTS llx_sesionex_log (
    rowid int(11) NOT NULL AUTO_INCREMENT,
    fk_user int(11) NOT NULL,
    date_login datetime NOT NULL,
    date_logout datetime DEFAULT NULL,
    ip varchar(250) DEFAULT NULL,
    user_agent varchar(500) DEFAULT NULL,
    browser varchar(100) DEFAULT NULL,
    country varchar(100) DEFAULT NULL,
    city varchar(100) DEFAULT NULL,
    latitude double(10,6) DEFAULT NULL,
    longitude double(10,6) DEFAULT NULL,
    module_accessed varchar(100) DEFAULT NULL,
    session_duration int(11) DEFAULT NULL,
    last_activity datetime DEFAULT NULL,
    is_active tinyint(1) DEFAULT 1,
    entity int(11) DEFAULT 1,
    tms timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rowid),
    KEY idx_fk_user (fk_user),
    KEY idx_date_login (date_login),
    KEY idx_entity (entity),
    KEY idx_is_active (is_active)
) ENGINE=innodb DEFAULT CHARSET=utf8;

-- Page tracking table
CREATE TABLE IF NOT EXISTS llx_sesionex_page_log (
    rowid int(11) NOT NULL AUTO_INCREMENT,
    fk_session int(11) NOT NULL,
    fk_user int(11) NOT NULL,
    page_url varchar(500) NOT NULL,
    module_name varchar(100) DEFAULT NULL,
    page_title varchar(255) DEFAULT NULL,
    date_visit datetime NOT NULL,
    time_spent int(11) DEFAULT NULL,
    entity int(11) DEFAULT 1,
    PRIMARY KEY (rowid),
    KEY idx_fk_session (fk_session),
    KEY idx_fk_user (fk_user),
    KEY idx_date_visit (date_visit),
    KEY idx_entity (entity)
) ENGINE=innodb DEFAULT CHARSET=utf8;

-- Geolocation cache table
CREATE TABLE IF NOT EXISTS llx_sesionex_geo_cache (
    rowid int(11) NOT NULL AUTO_INCREMENT,
    ip varchar(250) NOT NULL,
    country varchar(100) DEFAULT NULL,
    city varchar(100) DEFAULT NULL,
    region varchar(100) DEFAULT NULL,
    latitude double(10,6) DEFAULT NULL,
    longitude double(10,6) DEFAULT NULL,
    date_cached datetime NOT NULL,
    PRIMARY KEY (rowid),
    UNIQUE KEY idx_ip (ip)
) ENGINE=innodb DEFAULT CHARSET=utf8;