ChatHistory.lua

--[[
    ChatHistory - FFXI Windower 4 Addon
    Records incoming tells, party chat, and linkshell messages to a per-character
    SQLite database, and provides a flexible search interface via addon commands.

    Installation:
      Copy the ChatHistory/ folder to <Windower>/addons/ChatHistory/
      Then in-game: //lua load ChatHistory

    Commands (alias //ch works too):
      //chathistory search [options]
      //chathistory stats
      //chathistory purge [--days N]
      //chathistory vacuum
      //chathistory help

    License: BSD 3-Clause
--]]

_addon.name     = 'ChatHistory'
_addon.author   = 'Garyfromwork'
_addon.version  = '1.0.0'
_addon.command  = 'chathistory'
_addon.commands = {'ch'}

local sqlite3 = require('lsqlite3')

-- ============================================================
-- Configuration
-- Edit these values directly, or they can be changed in-game
-- by editing the source and reloading.
-- ============================================================

local CONFIG = {
    -- Default number of results returned by //ch search
    default_limit    = 20,

    -- Auto-prune messages older than this many days when a character logs in.
    -- FFXI chat volume is low enough that 90 days across 3 chat types
    -- typically produces ~10,000–50,000 rows — well within SQLite's comfort zone.
    -- Set to 0 to disable auto-pruning on login.
    prune_days       = 90,

    -- Print every unrecognised incoming text mode to chat.
    -- Enable this temporarily if messages are not being captured,
    -- then look for the mode number of the missing chat type and
    -- update CHAT_MODES below.
    debug_modes      = false,
}

-- ============================================================
-- Incoming chat mode constants
--
-- These are the display-layer mode values passed by Windower's
-- 'incoming text' event. They come from the ChatPorter addon
-- reference and community documentation.
--
-- If a chat type is not being recorded, set CONFIG.debug_modes = true,
-- send a test message, and note the printed mode number, then update here.
-- ============================================================

local CHAT_MODES = {
    TELL       = 12,   -- Someone sends you a tell
    PARTY      = 13,   -- Party chat
    LINKSHELL1 = 14,   -- Linkshell 1
    LINKSHELL2 = 214,  -- Linkshell 2
}

-- Map mode → DB type string
local MODE_TO_TYPE = {
    [CHAT_MODES.TELL]       = 'tell',
    [CHAT_MODES.PARTY]      = 'party',
    [CHAT_MODES.LINKSHELL1] = 'ls1',
    [CHAT_MODES.LINKSHELL2] = 'ls2',
}

-- ============================================================
-- Output color codes for windower.add_to_chat()
-- ============================================================

local COLOR = {
    HEADER = 207,   -- Light blue  (used for addon status lines)
    INFO   = 1,     -- White
    TELL   = 204,   -- Pink/magenta
    PARTY  = 14,    -- Cyan
    LS     = 72,    -- Green
    ERROR  = 167,   -- Yellow-orange
    DIM    = 8,     -- Dark gray
}

local TYPE_COLOR = {
    tell  = COLOR.TELL,
    party = COLOR.PARTY,
    ls1   = COLOR.LS,
    ls2   = COLOR.LS,
}

local TYPE_LABEL = {
    tell  = 'Tell ',
    party = 'Party',
    ls1   = 'LS1  ',
    ls2   = 'LS2  ',
}

-- ============================================================
-- State
-- ============================================================

local db      = nil   -- lsqlite3 database handle
local db_path = nil   -- absolute path to the open .db file

-- ============================================================
-- Utility helpers
-- ============================================================

local function trim(s)
    return s:match('^%s*(.-)%s*$')
end

-- Strip Windower/FFXI color and escape codes from a raw chat string.
-- Windower embeds color using \30 (0x1E) followed by a single color byte.
-- Standard ANSI CSI sequences (\27[...m) may also appear.
local function strip_codes(text)
    if not text then return '' end
    text = text:gsub('\30.', '')                      -- Windower \1Exx codes
    text = text:gsub('\31.', '')                      -- Windower \1Fxx codes
    text = text:gsub('\27%[[^a-zA-Z]*[a-zA-Z]', '')  -- ANSI CSI sequences
    text = text:gsub('[%z\1-\9\11-\29]', '')          -- Other control chars
    return trim(text)
end

-- Parse sender and body from a formatted FFXI tell string.
-- Incoming tells look like: "Sender >> message text"
local function parse_tell(text)
    return text:match('^(.-)%s*>>%s*(.+)$')
end

-- Party chat looks like: "(Sender) message text"
local function parse_party(text)
    return text:match('^%((.-)%)%s*(.+)$')
end

-- Linkshell chat looks like: "<Sender> message text"
local function parse_linkshell(text)
    return text:match('^<(.-)>%s*(.+)$')
end

-- ============================================================
-- Database initialisation
-- ============================================================

local function setup_tables()
    -- WAL mode gives much better write performance for an append-heavy workload
    -- without sacrificing read safety.
    local sql = [[
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous  = NORMAL;

        CREATE TABLE IF NOT EXISTS messages (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            type      TEXT    NOT NULL,
            sender    TEXT    NOT NULL,
            message   TEXT    NOT NULL,
            timestamp INTEGER NOT NULL
        );

        -- Index for type-only queries (//ch search --type tell)
        CREATE INDEX IF NOT EXISTS idx_type
            ON messages(type);

        -- Index for player-only queries (//ch search --player Bob)
        CREATE INDEX IF NOT EXISTS idx_sender
            ON messages(sender COLLATE NOCASE);

        -- Index for date-range queries (//ch search --from ... --to ...)
        CREATE INDEX IF NOT EXISTS idx_timestamp
            ON messages(timestamp);

        -- Composite index covers the most common combined query:
        -- type + date range (//ch search --type tell --from ...)
        CREATE INDEX IF NOT EXISTS idx_type_timestamp
            ON messages(type, timestamp);
    ]]

    local rc = db:exec(sql)
    if rc ~= sqlite3.OK then
        windower.add_to_chat(COLOR.ERROR,
            '[ChatHistory] Table setup error (code ' .. rc .. '): ' .. db:errmsg())
    end
end

-- Open (or re-open) the database for a given character name.
-- Each character gets their own .db file so histories don't mix.
local function open_db(char_name)
    local data_dir = windower.addon_path .. 'data'
    -- FFXI only runs on Windows; the 2>nul suppresses the "already exists" error
    os.execute('mkdir "' .. data_dir .. '" 2>nul')

    if db then
        db:close()
        db = nil
    end

    db_path = data_dir .. '\\' .. char_name .. '.db'
    db = sqlite3.open(db_path)

    if not db then
        windower.add_to_chat(COLOR.ERROR,
            '[ChatHistory] Could not open database: ' .. db_path)
        return false
    end

    setup_tables()
    return true
end

-- ============================================================
-- Record insertion
-- ============================================================

local function record_message(msg_type, sender, message)
    if not db then return end

    local ok, err = pcall(function()
        local stmt = db:prepare(
            'INSERT INTO messages (type, sender, message, timestamp) VALUES (?, ?, ?, ?)'
        )
        stmt:bind(1, msg_type)
        stmt:bind(2, sender)
        stmt:bind(3, message)
        stmt:bind(4, os.time())
        stmt:step()
        stmt:finalize()
    end)

    if not ok then
        windower.add_to_chat(COLOR.ERROR,
            '[ChatHistory] Insert failed: ' .. tostring(err))
    end
end

-- ============================================================
-- Pruning
--
-- Table size analysis:
--   A moderately active FFXI player might see ~100-400 chat messages per
--   hour across all channels. At 4 hours/day with 90-day retention that
--   is roughly 14,400–57,600 rows — comfortably small for SQLite.
--   Even at 1M rows, indexed queries typically complete in < 50 ms.
--   The prune_days setting is the main lever; VACUUM reclaims disk space.
-- ============================================================

local function prune(days)
    if not db then return 0 end
    days = days or CONFIG.prune_days
    if days <= 0 then return 0 end

    local cutoff = os.time() - (days * 86400)
    db:exec('DELETE FROM messages WHERE timestamp < ' .. cutoff)
    return db:changes()
end

-- ============================================================
-- Search
-- ============================================================

-- User-facing type aliases → one or more DB type strings
local TYPE_ALIASES = {
    tell       = {'tell'},
    tells      = {'tell'},
    t          = {'tell'},
    party      = {'party'},
    p          = {'party'},
    pt         = {'party'},
    ls         = {'ls1', 'ls2'},
    linkshell  = {'ls1', 'ls2'},
    ls1        = {'ls1'},
    linkshell1 = {'ls1'},
    ls2        = {'ls2'},
    linkshell2 = {'ls2'},
    all        = {'tell', 'party', 'ls1', 'ls2'},
}

-- Parse a date string to a Unix timestamp.
-- Accepts: YYYY-MM-DD  or  YYYY-MM-DD HH:MM:SS
local function parse_date(str)
    if not str then return nil end
    local y, mo, d, h, mi, s =
        str:match('^(%d%d%d%d)-(%d%d)-(%d%d)%s*(%d*):?(%d*):?(%d*)$')
    if not y then return nil end
    return os.time({
        year  = tonumber(y),
        month = tonumber(mo),
        day   = tonumber(d),
        hour  = tonumber(h)  or 0,
        min   = tonumber(mi) or 0,
        sec   = tonumber(s)  or 0,
    })
end

-- Parse //ch search argument list into a params table.
-- Returns (params, nil) on success, (nil, error_string) on failure.
local function parse_search_args(args)
    local params = {
        types   = nil,
        player  = nil,
        from_ts = nil,
        to_ts   = nil,
        limit   = CONFIG.default_limit,
        order   = 'ASC',
    }

    local i = 1
    while i <= #args do
        local flag = args[i]:lower()

        if (flag == '--type' or flag == '-t') then
            i = i + 1
            if not args[i] then
                return nil, '--type requires a value (tell, party, ls, ls1, ls2, all)'
            end
            local alias = args[i]:lower()
            params.types = TYPE_ALIASES[alias]
            if not params.types then
                return nil, 'Unknown type "' .. args[i] ..
                    '". Valid: tell, party, ls, ls1, ls2, all'
            end

        elseif (flag == '--player' or flag == '-p') then
            i = i + 1
            if not args[i] then
                return nil, '--player requires a name'
            end
            params.player = args[i]

        elseif (flag == '--from' or flag == '-f') then
            i = i + 1
            if not args[i] then
                return nil, '--from requires a date (YYYY-MM-DD)'
            end
            params.from_ts = parse_date(args[i])
            if not params.from_ts then
                return nil, 'Invalid --from date "' .. args[i] ..
                    '". Use YYYY-MM-DD or YYYY-MM-DD HH:MM:SS'
            end

        elseif flag == '--to' then
            i = i + 1
            if not args[i] then
                return nil, '--to requires a date (YYYY-MM-DD)'
            end
            params.to_ts = parse_date(args[i])
            if not params.to_ts then
                return nil, 'Invalid --to date "' .. args[i] ..
                    '". Use YYYY-MM-DD or YYYY-MM-DD HH:MM:SS'
            end

        elseif (flag == '--limit' or flag == '-l') then
            i = i + 1
            if not args[i] then
                return nil, '--limit requires a number'
            end
            local n = tonumber(args[i])
            if not n or n < 1 then
                return nil, '--limit must be a positive integer'
            end
            params.limit = n

        elseif (flag == '--order' or flag == '-o') then
            i = i + 1
            if not args[i] then
                return nil, '--order requires asc or desc'
            end
            local ord = args[i]:lower()
            if ord == 'desc' or ord == 'd' or ord == 'descending' then
                params.order = 'DESC'
            elseif ord == 'asc' or ord == 'a' or ord == 'ascending' then
                params.order = 'ASC'
            else
                return nil, 'Unknown --order value "' .. args[i] ..
                    '". Use asc or desc'
            end
        end

        i = i + 1
    end

    return params, nil
end

-- Execute the search and return a table of result rows.
local function db_search(params)
    if not db then return {} end

    local wheres = {}
    local binds  = {}

    if params.types and #params.types > 0 then
        local placeholders = {}
        for _, t in ipairs(params.types) do
            placeholders[#placeholders + 1] = '?'
            binds[#binds + 1] = t
        end
        wheres[#wheres + 1] = 'type IN (' .. table.concat(placeholders, ',') .. ')'
    end

    if params.player then
        wheres[#wheres + 1] = 'sender LIKE ? COLLATE NOCASE'
        binds[#binds + 1] = '%' .. params.player .. '%'
    end

    if params.from_ts then
        wheres[#wheres + 1] = 'timestamp >= ?'
        binds[#binds + 1] = params.from_ts
    end

    if params.to_ts then
        wheres[#wheres + 1] = 'timestamp <= ?'
        binds[#binds + 1] = params.to_ts
    end

    local sql = 'SELECT type, sender, message, timestamp FROM messages'
    if #wheres > 0 then
        sql = sql .. ' WHERE ' .. table.concat(wheres, ' AND ')
    end
    sql = sql .. ' ORDER BY timestamp ' .. params.order .. ' LIMIT ?'
    binds[#binds + 1] = params.limit

    local results = {}
    local ok, err = pcall(function()
        local stmt = db:prepare(sql)
        for idx, v in ipairs(binds) do
            stmt:bind(idx, v)
        end
        for row in stmt:nrows() do
            results[#results + 1] = {
                type      = row.type,
                sender    = row.sender,
                message   = row.message,
                timestamp = row.timestamp,
            }
        end
        stmt:finalize()
    end)

    if not ok then
        windower.add_to_chat(COLOR.ERROR,
            '[ChatHistory] Search error: ' .. tostring(err))
        return {}
    end

    return results
end

-- ============================================================
-- Display helpers
-- ============================================================

local function print_results(results)
    if #results == 0 then
        windower.add_to_chat(COLOR.HEADER, '[ChatHistory] No messages found.')
        return
    end

    windower.add_to_chat(COLOR.HEADER,
        '[ChatHistory] ' .. #results .. ' result(s):')
    windower.add_to_chat(COLOR.DIM, string.rep('-', 60))

    for _, row in ipairs(results) do
        local label = TYPE_LABEL[row.type] or row.type
        local color = TYPE_COLOR[row.type] or COLOR.INFO
        local ts    = os.date('%m/%d %H:%M', row.timestamp)

        windower.add_to_chat(color,
            string.format('[%s][%s] %s: %s', ts, label, row.sender, row.message))
    end
end

-- ============================================================
-- Command implementations
-- ============================================================

local HELP = {
    '[ChatHistory] v' .. _addon.version .. ' — Commands:',
    '',
    '  //ch search [options]',
    '    --type  <tell|party|ls|ls1|ls2|all>   filter by chat type',
    '    --player <name>                        sender name (partial, case-insensitive)',
    '    --from  <YYYY-MM-DD>                   on or after this date',
    '    --to    <YYYY-MM-DD>                   on or before this date',
    '    --limit <n>                            max rows returned (default: ' .. CONFIG.default_limit .. ')',
    '    --order <asc|desc>                     sort direction  (default: asc)',
    '',
    '  //ch stats           Row counts and database path',
    '  //ch purge [--days N]  Delete messages older than N days (default: ' .. CONFIG.prune_days .. ')',
    '  //ch vacuum          Rebuild database file to reclaim disk space',
    '  //ch help            Show this help',
    '',
    '  Examples:',
    '    //ch search --type tell --player Shantotto',
    '    //ch search --type party --from 2024-06-01 --limit 50 --order desc',
    '    //ch search --player Maat --from 2024-01-01 --to 2024-03-31',
    '    //ch search --type ls --order desc --limit 5',
}

local function cmd_help()
    for _, line in ipairs(HELP) do
        windower.add_to_chat(COLOR.INFO, line)
    end
end

local function cmd_search(args)
    local params, err = parse_search_args(args)
    if err then
        windower.add_to_chat(COLOR.ERROR, '[ChatHistory] ' .. err)
        windower.add_to_chat(COLOR.INFO,  '  Type //ch help for usage.')
        return
    end
    print_results(db_search(params))
end

local function cmd_stats()
    if not db then
        windower.add_to_chat(COLOR.ERROR, '[ChatHistory] No database open.')
        return
    end

    -- Per-type counts
    local counts = {}
    for row in db:nrows(
        'SELECT type, COUNT(*) AS cnt FROM messages GROUP BY type ORDER BY type'
    ) do
        counts[row.type] = row.cnt
    end

    -- Total
    local total = 0
    for row in db:nrows('SELECT COUNT(*) AS cnt FROM messages') do
        total = row.cnt
    end

    -- Oldest message
    local oldest_ts = nil
    for row in db:nrows('SELECT MIN(timestamp) AS ts FROM messages') do
        oldest_ts = row.ts
    end

    windower.add_to_chat(COLOR.HEADER, '[ChatHistory] Statistics:')
    windower.add_to_chat(COLOR.TELL,
        '  Tells:     ' .. (counts['tell']  or 0))
    windower.add_to_chat(COLOR.PARTY,
        '  Party:     ' .. (counts['party'] or 0))
    windower.add_to_chat(COLOR.LS,
        '  LS1:       ' .. (counts['ls1']   or 0))
    windower.add_to_chat(COLOR.LS,
        '  LS2:       ' .. (counts['ls2']   or 0))
    windower.add_to_chat(COLOR.INFO,
        '  Total:     ' .. total)
    if oldest_ts and oldest_ts > 0 then
        windower.add_to_chat(COLOR.INFO,
            '  Oldest:    ' .. os.date('%Y-%m-%d', oldest_ts))
    end
    windower.add_to_chat(COLOR.DIM,
        '  Path:      ' .. (db_path or 'N/A'))
end

local function cmd_purge(args)
    local days = CONFIG.prune_days

    local i = 1
    while i <= #args do
        if args[i]:lower() == '--days' then
            i = i + 1
            local n = tonumber(args[i])
            if not n or n < 1 then
                windower.add_to_chat(COLOR.ERROR,
                    '[ChatHistory] --days must be a positive integer.')
                return
            end
            days = n
        end
        i = i + 1
    end

    local deleted = prune(days)
    windower.add_to_chat(COLOR.HEADER,
        '[ChatHistory] Purged ' .. deleted ..
        ' message(s) older than ' .. days .. ' day(s).')
end

local function cmd_vacuum()
    if not db then
        windower.add_to_chat(COLOR.ERROR, '[ChatHistory] No database open.')
        return
    end
    db:exec('VACUUM')
    windower.add_to_chat(COLOR.HEADER, '[ChatHistory] VACUUM complete.')
end

-- ============================================================
-- Addon command router
-- ============================================================

windower.register_event('addon command', function(...)
    local args = {...}
    local cmd  = (args[1] or 'help'):lower()
    local rest = {}
    for i = 2, #args do rest[#rest + 1] = args[i] end

    if     cmd == 'search' or cmd == 's'              then cmd_search(rest)
    elseif cmd == 'stats'                             then cmd_stats()
    elseif cmd == 'purge'                             then cmd_purge(rest)
    elseif cmd == 'vacuum'                            then cmd_vacuum()
    elseif cmd == 'help' or cmd == 'h' or cmd == '?' then cmd_help()
    else
        windower.add_to_chat(COLOR.ERROR,
            '[ChatHistory] Unknown command "' .. cmd ..
            '". Type //ch help for usage.')
    end
end)

-- ============================================================
-- Incoming chat handler
-- ============================================================

windower.register_event('incoming text',
    function(original, modified, original_mode, modified_mode, blocked)
        if blocked or not db then return end

        local msg_type = MODE_TO_TYPE[original_mode]

        if not msg_type then
            if CONFIG.debug_modes then
                windower.add_to_chat(COLOR.DIM,
                    '[ChatHistory DEBUG] mode=' .. tostring(original_mode) ..
                    ' text=' .. (original or ''))
            end
            return
        end

        local text   = strip_codes(original)
        local sender, message

        if original_mode == CHAT_MODES.TELL then
            sender, message = parse_tell(text)
        elseif original_mode == CHAT_MODES.PARTY then
            sender, message = parse_party(text)
        else
            -- Both LINKSHELL1 and LINKSHELL2 use angle-bracket format
            sender, message = parse_linkshell(text)
        end

        if sender and message then
            record_message(msg_type, sender, message)
        elseif CONFIG.debug_modes then
            windower.add_to_chat(COLOR.DIM,
                '[ChatHistory DEBUG] Parse failed for mode=' ..
                original_mode .. ' text="' .. text .. '"')
        end
    end
)

-- ============================================================
-- Login / logout lifecycle
-- ============================================================

local function on_login(char_name)
    if not open_db(char_name) then return end

    if CONFIG.prune_days > 0 then
        local deleted = prune(CONFIG.prune_days)
        if deleted > 0 then
            windower.add_to_chat(COLOR.DIM,
                '[ChatHistory] Auto-pruned ' .. deleted ..
                ' message(s) older than ' .. CONFIG.prune_days .. ' days.')
        end
    end

    windower.add_to_chat(COLOR.HEADER,
        '[ChatHistory] v' .. _addon.version ..
        ' ready (' .. char_name .. '). Type //ch help for usage.')
end

windower.register_event('login', function(name)
    on_login(name)
end)

windower.register_event('logout', function()
    if db then
        db:close()
        db = nil
    end
end)

windower.register_event('unload', function()
    if db then
        db:close()
        db = nil
    end
end)

-- Support loading the addon while already logged in
local player = windower.ffxi.get_player()
if player and player.name then
    on_login(player.name)
else
    windower.add_to_chat(COLOR.HEADER,
        '[ChatHistory] v' .. _addon.version ..
        ' loaded. Waiting for character login...')
end