🗄️ Tabla custom + ObjectModel — ejemplo real ecom_statsbots

<?php

public function install()
{
    return parent::install()
        && $this->registerHook('displayHeader')
        && $this->registerHook('displayBackOfficeHeader')
        && $this->installDb()
        && $this->installTab();
}

public function uninstall()
{
    return $this->uninstallTab()
        && $this->uninstallDb()
        && parent::uninstall();
}

public function installDb()
{
    $sql = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'ecom_statsbots` (
        `id_ecom_statsbots` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_agent` TEXT NOT NULL,
        `bot_name` VARCHAR(64) DEFAULT NULL,
        `ua_hash` VARCHAR(32) NOT NULL,
        `bg_type` VARCHAR(20) NOT NULL DEFAULT "unknown",
        `hits` INT(11) UNSIGNED NOT NULL DEFAULT 1,
        `date_add` DATETIME NOT NULL,
        `date_upd` DATETIME NOT NULL,
        PRIMARY KEY (`id_ecom_statsbots`),
        UNIQUE KEY `idx_ua_hash` (`ua_hash`)
    ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=utf8mb4;';

    return Db::getInstance()->execute($sql);
}

public function uninstallDb()
{
    return Db::getInstance()->execute(
        'DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'ecom_statsbots`'
    );
}

// CLAVE: Usar _DB_PREFIX_ y _MYSQL_ENGINE_ siempre
// CLAVE: UNIQUE KEY en ua_hash para INSERT ON DUPLICATE KEY UPDATE

<?php
// Archivo: modules/ecom_statsbots/classes/StatsBot.php

class StatsBot extends ObjectModel
{
    public $id_ecom_statsbots;
    public $user_agent;
    public $bot_name;
    public $ua_hash;
    public $bg_type;
    public $hits;
    public $date_add;
    public $date_upd;

    public static $definition = [
        'table'   => 'ecom_statsbots',
        'primary' => 'id_ecom_statsbots',
        'fields'  => [
            'user_agent' => [
                'type' => self::TYPE_HTML,   // TEXT, admite HTML
                'validate' => 'isCleanHtml',
                'required' => true,
            ],
            'bot_name' => [
                'type' => self::TYPE_STRING,
                'validate' => 'isGenericName',
                'size' => 64,
            ],
            'ua_hash' => [
                'type' => self::TYPE_STRING,
                'validate' => 'isMd5',
                'required' => true,
                'size' => 32,
            ],
            'bg_type' => [
                'type' => self::TYPE_STRING,
                'validate' => 'isGenericName',
                'size' => 20,
            ],
            'hits' => [
                'type' => self::TYPE_INT,
                'validate' => 'isUnsignedInt',
            ],
            'date_add' => [
                'type' => self::TYPE_DATE,
                'validate' => 'isDate',
            ],
            'date_upd' => [
                'type' => self::TYPE_DATE,
                'validate' => 'isDate',
            ],
        ],
    ];
}

// CLAVE: El archivo se incluye con require_once en el modulo principal
// require_once __DIR__ . '/classes/StatsBot.php';

<?php

public function installTab()
{
    $tab = new Tab();
    $tab->active = true;
    $tab->class_name = 'AdminStatsBots';
    $tab->name = [];

    // Nombre multiidioma obligatorio
    foreach (Language::getLanguages(true) as $lang) {
        $tab->name[$lang['id_lang']] = 'Stats Bots & AI';
    }

    // Buscar el tab padre por SQL directa (mas robusto)
    $id_parent = (int) Db::getInstance()->getValue(
        'SELECT id_tab FROM `' . _DB_PREFIX_ . 'tab` '
        . 'WHERE class_name = "AdminStats"'
    );
    $tab->id_parent = $id_parent;
    $tab->module = $this->name;

    return $tab->add();
}

public function uninstallTab()
{
    $id_tab = (int) Db::getInstance()->getValue(
        'SELECT id_tab FROM `' . _DB_PREFIX_ . 'tab` '
        . 'WHERE class_name = "AdminStatsBots"'
    );

    if ($id_tab) {
        $tab = new Tab($id_tab);
        return $tab->delete();
    }
    return true;
}

// ALTERNATIVA: Usar Tab::getIdFromClassName('AdminStats')
// Pero SQL directa es mas fiable si el tab padre no existe

<?php

public function hookDisplayHeader()
{
    // Ignorar requests de assets estaticos
    if (isset($_SERVER['REQUEST_URI']) &&
        preg_match('/\.(jpg|jpeg|png|gif|css|js|ico|woff|woff2|ttf|svg)$/i',
                   $_SERVER['REQUEST_URI'])) {
        return;
    }

    $userAgent = isset($_SERVER['HTTP_USER_AGENT'])
        ? trim($_SERVER['HTTP_USER_AGENT']) : '';

    if (empty($userAgent)) {
        return;
    }

    // Deteccion ligera por keywords en user-agent
    $uaLower = strtolower($userAgent);
    $type = 'User';

    if (strpos($uaLower, 'bot') !== false ||
        strpos($uaLower, 'crawl') !== false ||
        strpos($uaLower, 'spider') !== false) {
        $type = 'Bot';
    }

    // IA override (mas especifico que bot generico)
    if (strpos($uaLower, 'gpt') !== false ||
        strpos($uaLower, 'claude') !== false ||
        strpos($uaLower, 'gemini') !== false) {
        $type = 'AI';
    }

    $uaHash = md5($userAgent);

    // UPSERT: INSERT si es nuevo, UPDATE hits si ya existe
    Db::getInstance()->execute(
        'INSERT INTO `' . _DB_PREFIX_ . 'ecom_statsbots`
         (`user_agent`, `ua_hash`, `bg_type`, `hits`, `date_add`, `date_upd`)
         VALUES (
            \'' . pSQL($userAgent) . '\',
            \'' . pSQL($uaHash) . '\',
            \'' . pSQL($type) . '\',
            1,
            NOW(),
            NOW()
         )
         ON DUPLICATE KEY UPDATE
            `hits` = `hits` + 1,
            `date_upd` = NOW()'
    );
}

// CLAVE: ON DUPLICATE KEY UPDATE evita SELECT + INSERT/UPDATE
// CLAVE: pSQL() en TODOS los valores string
// CLAVE: Filtrar assets estaticos para no inflar la tabla