<?php
/**
 * @intranet-file common/visitas_tracking.php
 * @intranet-file-version 2026.03.13.1
 * @intranet-file-date 2026-03-13
 * @intranet-file-owner CSIF Intranet
 */
declare(strict_types=1);

/**
 * Tracking de clics (Apps / Links / Novedades / NNTT)
 *
 * Tablas objetivo: stats_*
 * Compatibilidad: si existen visitas_* intenta renombrarlas a stats_*.
 */

function visitas_client_ip(): string
{
    $ip = '';

    $xff = $_SERVER['HTTP_X_FORWARDED_FOR'] ?? '';
    if (is_string($xff) && $xff !== '') {
        $parts = array_map('trim', explode(',', $xff));
        foreach ($parts as $p) {
            if (filter_var($p, FILTER_VALIDATE_IP)) {
                $ip = $p;
                break;
            }
        }
    }

    if ($ip === '') {
        $ra = $_SERVER['REMOTE_ADDR'] ?? '';
        if (is_string($ra) && filter_var($ra, FILTER_VALIDATE_IP)) {
            $ip = $ra;
        }
    }

    return $ip;
}

function stats_table_exists(PDO $pdo, string $table): bool
{
    $st = $pdo->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t");
    $st->execute(['t' => $table]);
    return ((int)$st->fetchColumn() > 0);
}

function stats_try_rename_legacy(PDO $pdo): void
{
    $map = [
        'visitas_year' => 'stats_year',
        'visitas_user_year' => 'stats_user_year',
        'visitas_apps_year' => 'stats_apps_year',
        'visitas_apps_user_year' => 'stats_apps_user_year',
        'visitas_links_year' => 'stats_links_year',
        'visitas_links_user_year' => 'stats_links_user_year',
        'visitas_clicks' => 'stats_clicks',
    ];

    foreach ($map as $old => $new) {
        try {
            if (stats_table_exists($pdo, $old) && !stats_table_exists($pdo, $new)) {
                $pdo->exec("RENAME TABLE `$old` TO `$new`");
            }
        } catch (Throwable $e) {
            // sin permisos, etc.
        }
    }
}

/**
 * Crea el esquema stats_* si no existe.
 */
function visitas_ensure_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) return;

    // Si existe el esquema nuevo, listo
    try {
        $pdo->query("SELECT 1 FROM stats_clicks LIMIT 1");
        $done = true;
        return;
    } catch (Throwable $e) {
        // continuar
    }

    // Compat: si existen tablas legacy, intentar renombrarlas
    try {
        if (stats_table_exists($pdo, 'visitas_clicks') && !stats_table_exists($pdo, 'stats_clicks')) {
            stats_try_rename_legacy($pdo);
        }
    } catch (Throwable $e) {}

    // Si tras el rename siguen sin existir, crear
    $stmts = [
        "CREATE TABLE IF NOT EXISTS stats_year (
          year INT NOT NULL PRIMARY KEY,
          total INT NOT NULL DEFAULT 0,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_user_year (
          year INT NOT NULL,
          username VARCHAR(190) NOT NULL,
          total INT NOT NULL DEFAULT 0,
          last_visit_at TIMESTAMP NULL,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (year, username),
          INDEX idx_year_total (year, total)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_apps_year (
          year INT NOT NULL,
          app_id VARCHAR(100) NOT NULL,
          total INT NOT NULL DEFAULT 0,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (year, app_id),
          INDEX idx_year_total (year, total)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_apps_user_year (
          year INT NOT NULL,
          app_id VARCHAR(100) NOT NULL,
          username VARCHAR(190) NOT NULL,
          total INT NOT NULL DEFAULT 0,
          last_click_at TIMESTAMP NULL,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (year, app_id, username),
          INDEX idx_year_user (year, username),
          INDEX idx_year_app_total (year, app_id, total)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_links_year (
          year INT NOT NULL,
          link_type ENUM('links','nntt','novedades') NOT NULL,
          link_id INT NOT NULL,
          total INT NOT NULL DEFAULT 0,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (year, link_type, link_id),
          INDEX idx_year_total (year, total),
          INDEX idx_year_type_total (year, link_type, total)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_links_user_year (
          year INT NOT NULL,
          link_type ENUM('links','nntt','novedades') NOT NULL,
          link_id INT NOT NULL,
          username VARCHAR(190) NOT NULL,
          total INT NOT NULL DEFAULT 0,
          last_click_at TIMESTAMP NULL,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (year, link_type, link_id, username),
          INDEX idx_year_user (year, username),
          INDEX idx_year_link_total (year, link_id, total)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        "CREATE TABLE IF NOT EXISTS stats_clicks (
          id BIGINT AUTO_INCREMENT PRIMARY KEY,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          username VARCHAR(190) NOT NULL,
          item_kind ENUM('app','link') NOT NULL,
          item_key VARCHAR(100) NOT NULL,
          link_type ENUM('links','nntt','novedades') NULL,
          label VARCHAR(255) NULL,
          destino VARCHAR(800) NULL,
          ip VARCHAR(45) NULL,
          user_agent VARCHAR(255) NULL,
          referer VARCHAR(500) NULL,
          INDEX idx_created_at (created_at),
          INDEX idx_user_created (username, created_at),
          INDEX idx_kind_key_created (item_kind, item_key, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];

    foreach ($stmts as $sql) {
        $pdo->exec($sql);
    }

    $done = true;
}

function visitas_increment_overall(PDO $pdo, int $year, string $username): void
{
    $st1 = $pdo->prepare(
        "INSERT INTO stats_year (year, total) VALUES (:y, 1)
         ON DUPLICATE KEY UPDATE total = total + 1, updated_at = NOW()"
    );
    $st1->execute(['y' => $year]);

    $st2 = $pdo->prepare(
        "INSERT INTO stats_user_year (year, username, total, last_visit_at)
         VALUES (:y, :u, 1, NOW())
         ON DUPLICATE KEY UPDATE total = total + 1, last_visit_at = NOW()"
    );
    $st2->execute(['y' => $year, 'u' => $username]);
}

function visitas_log_raw_click(PDO $pdo, array $row): void
{
    $st = $pdo->prepare(
        "INSERT INTO stats_clicks (created_at, username, item_kind, item_key, link_type, label, destino, ip, user_agent, referer)
         VALUES (NOW(), :username, :item_kind, :item_key, :link_type, :label, :destino, :ip, :user_agent, :referer)"
    );

    $st->execute([
        'username'   => (string)($row['username'] ?? ''),
        'item_kind'  => (string)($row['item_kind'] ?? ''),
        'item_key'   => (string)($row['item_key'] ?? ''),
        'link_type'  => $row['link_type'] ?? null,
        'label'      => $row['label'] ?? null,
        'destino'    => $row['destino'] ?? null,
        'ip'         => $row['ip'] ?? null,
        'user_agent' => $row['user_agent'] ?? null,
        'referer'    => $row['referer'] ?? null,
    ]);
}

/**
 * Vista de página (dashboard): cuenta a nivel intranet por usuario/año.
 */
function visitas_record_page_view(PDO $pdo, string $username): void
{
    visitas_ensure_schema($pdo);
    $year = (int)date('Y');

    $pdo->beginTransaction();
    visitas_increment_overall($pdo, $year, $username);
    $pdo->commit();
}

function visitas_record_app_click(PDO $pdo, string $username, string $appId, ?string $label, ?string $destino): void
{
    visitas_ensure_schema($pdo);
    $year = (int)date('Y');
    $ip = visitas_client_ip();
    $ua = (string)($_SERVER['HTTP_USER_AGENT'] ?? '');
    $ref = (string)($_SERVER['HTTP_REFERER'] ?? '');

    $pdo->beginTransaction();

    visitas_increment_overall($pdo, $year, $username);

    visitas_log_raw_click($pdo, [
        'username' => $username,
        'item_kind' => 'app',
        'item_key' => $appId,
        'link_type' => null,
        'label' => $label,
        'destino' => $destino,
        'ip' => $ip,
        'user_agent' => $ua,
        'referer' => $ref,
    ]);

    $st1 = $pdo->prepare(
        "INSERT INTO stats_apps_year (year, app_id, total)
         VALUES (:year, :app_id, 1)
         ON DUPLICATE KEY UPDATE total = total + 1, updated_at = NOW()"
    );
    $st1->execute(['year' => $year, 'app_id' => $appId]);

    $st2 = $pdo->prepare(
        "INSERT INTO stats_apps_user_year (year, app_id, username, total, last_click_at)
         VALUES (:year, :app_id, :username, 1, NOW())
         ON DUPLICATE KEY UPDATE total = total + 1, last_click_at = NOW()"
    );
    $st2->execute(['year' => $year, 'app_id' => $appId, 'username' => $username]);

    $pdo->commit();
}

function visitas_record_link_click(PDO $pdo, string $username, int $linkId, string $linkType, ?string $label, ?string $destino): void
{
    visitas_ensure_schema($pdo);
    $year = (int)date('Y');
    $ip = visitas_client_ip();
    $ua = (string)($_SERVER['HTTP_USER_AGENT'] ?? '');
    $ref = (string)($_SERVER['HTTP_REFERER'] ?? '');

    $pdo->beginTransaction();

    visitas_increment_overall($pdo, $year, $username);

    visitas_log_raw_click($pdo, [
        'username' => $username,
        'item_kind' => 'link',
        'item_key' => (string)$linkId,
        'link_type' => $linkType,
        'label' => $label,
        'destino' => $destino,
        'ip' => $ip,
        'user_agent' => $ua,
        'referer' => $ref,
    ]);

    $st1 = $pdo->prepare(
        "INSERT INTO stats_links_year (year, link_type, link_id, total)
         VALUES (:year, :t, :id, 1)
         ON DUPLICATE KEY UPDATE total = total + 1, updated_at = NOW()"
    );
    $st1->execute(['year' => $year, 't' => $linkType, 'id' => $linkId]);

    $st2 = $pdo->prepare(
        "INSERT INTO stats_links_user_year (year, link_type, link_id, username, total, last_click_at)
         VALUES (:year, :t, :id, :u, 1, NOW())
         ON DUPLICATE KEY UPDATE total = total + 1, last_click_at = NOW()"
    );
    $st2->execute(['year' => $year, 't' => $linkType, 'id' => $linkId, 'u' => $username]);

    $pdo->commit();
}
