-- Copyright (C) 2026 XlSolucionesTI  <xlsolucionesti@hotmail.com>
-- Upgrade script for SesioneX module
-- Run this script if you get "Unknown column" errors

-- Add missing columns to sesionex_log table
ALTER TABLE llx_sesionex_log 
    ADD COLUMN latitude double(10,6) DEFAULT NULL AFTER city,
    ADD COLUMN longitude double(10,6) DEFAULT NULL AFTER latitude;

-- If tables don't exist, create them
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;

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;