<?php

declare(strict_types=1);

/**
 * FATE 瀏覽紀錄：每次請求寫入一筆（時間、IP、page_key、選填會員），所有統計由此聚合。
 */

function fate_visit_log_table_ddl(): string
{
    return <<<'SQL'
CREATE TABLE IF NOT EXISTS fate_visit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    viewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_ip VARCHAR(45) NOT NULL DEFAULT '',
    page_key VARCHAR(128) NOT NULL,
    page_title VARCHAR(255) NULL DEFAULT NULL,
    member_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (id),
    KEY idx_page_key (page_key),
    KEY idx_viewed_at (viewed_at),
    KEY idx_page_viewed (page_key, viewed_at),
    KEY idx_ip_viewed (client_ip, viewed_at)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
SQL;
}

function fate_visit_log_table_exists(PDO $pdo): bool
{
    try {
        $st = $pdo->query("SHOW TABLES LIKE 'fate_visit_log'");

        return $st !== false && $st->rowCount() > 0;
    } catch (Throwable) {
        return false;
    }
}

/**
 * 確保 fate_visit_log 存在（表已存在時不強制 CREATE，避免無建表權限時永遠失敗）。
 */
function fate_visit_log_ensure_table(PDO $pdo): bool
{
    static $state = 'todo';
    if ($state === 'ok') {
        return true;
    }
    if ($state === 'fail') {
        if (fate_visit_log_table_exists($pdo)) {
            $state = 'ok';
            fate_visit_log_ensure_page_title_column($pdo);

            return true;
        }

        return false;
    }

    try {
        if (!fate_visit_log_table_exists($pdo)) {
            $pdo->exec(fate_visit_log_table_ddl());
        }
        fate_visit_log_ensure_page_title_column($pdo);
        $state = 'ok';

        return true;
    } catch (Throwable) {
        if (fate_visit_log_table_exists($pdo)) {
            $state = 'ok';
            fate_visit_log_ensure_page_title_column($pdo);

            return true;
        }
        $state = 'fail';

        return false;
    }
}

function fate_visit_log_ensure_page_title_column(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }
    try {
        $st = $pdo->query("SHOW COLUMNS FROM fate_visit_log LIKE 'page_title'");
        if ($st && $st->rowCount() === 0) {
            $pdo->exec(
                'ALTER TABLE fate_visit_log ADD COLUMN page_title VARCHAR(255) NULL DEFAULT NULL AFTER page_key'
            );
        }
        $done = true;
    } catch (Throwable) {
        /* 略過 */
    }
}

function fate_visit_log_sanitize_page_title(?string $raw): ?string
{
    if ($raw === null) {
        return null;
    }
    $s = trim($raw);
    if ($s === '') {
        return null;
    }
    if (function_exists('mb_substr')) {
        return mb_substr($s, 0, 255, 'UTF-8');
    }

    return strlen($s) > 255 ? substr($s, 0, 255) : $s;
}

function fate_visit_log_client_ip(): string
{
    if (defined('FATE_BEHIND_PROXY') && FATE_BEHIND_PROXY) {
        $xff = $_SERVER['HTTP_X_FORWARDED_FOR'] ?? '';
        if (is_string($xff) && $xff !== '') {
            $parts = array_map('trim', explode(',', $xff));
            if (isset($parts[0]) && $parts[0] !== '') {
                return substr($parts[0], 0, 45);
            }
        }
    }

    return substr(trim((string) ($_SERVER['REMOTE_ADDR'] ?? '')), 0, 45);
}

/** 與舊 page_key 規則一致（英數與 ._:-）；無效或過長回傳 null */
function fate_visit_log_normalize_page_key(string $raw): ?string
{
    $s = strtolower(trim($raw));
    $s = preg_replace('/[^a-z0-9._:-]+/', '-', $s);
    $s = trim((string) $s, '-_.');
    if ($s === '' || strlen($s) > 128) {
        return null;
    }

    return $s;
}

/**
 * 寫入一筆瀏覽紀錄（失敗時靜默略過，不阻擋頁面）。
 */
function fate_visit_log_append(?PDO $pdo, string $pageKeyRaw, ?int $memberId = null, ?string $pageTitle = null): void
{
    if (!$pdo instanceof PDO || PHP_SAPI === 'cli') {
        return;
    }
    $key = fate_visit_log_normalize_page_key($pageKeyRaw);
    if ($key === null) {
        return;
    }
    $ip = fate_visit_log_client_ip();
    $mid = ($memberId !== null && $memberId > 0) ? $memberId : null;
    $title = fate_visit_log_sanitize_page_title($pageTitle);

    try {
        if (!fate_visit_log_ensure_table($pdo)) {
            return;
        }
        $st = $pdo->prepare(
            'INSERT INTO fate_visit_log (client_ip, page_key, page_title, member_id) VALUES (?, ?, ?, ?)'
        );
        $st->execute([$ip, $key, $title, $mid]);
    } catch (Throwable) {
        try {
            $st = $pdo->prepare(
                'INSERT INTO fate_visit_log (client_ip, page_key, member_id) VALUES (?, ?, ?)'
            );
            $st->execute([$ip, $key, $mid]);
        } catch (Throwable) {
            /* 略過 */
        }
    }
}

function fate_visit_log_count_by_page(?PDO $pdo, string $pageKeyRaw): ?int
{
    if (!$pdo instanceof PDO) {
        return null;
    }
    $key = fate_visit_log_normalize_page_key($pageKeyRaw);
    if ($key === null) {
        return null;
    }

    try {
        if (!fate_visit_log_ensure_table($pdo)) {
            return null;
        }
        $st = $pdo->prepare('SELECT COUNT(*) FROM fate_visit_log WHERE page_key = ?');
        $st->execute([$key]);

        return (int) $st->fetchColumn();
    } catch (Throwable) {
        return null;
    }
}

/** 建立或確認瀏覽紀錄表（供安裝腳本／後台使用）。 */
function fate_visit_log_install(PDO $pdo): array
{
    try {
        if (!fate_visit_log_table_exists($pdo)) {
            $pdo->exec(fate_visit_log_table_ddl());
        }
        fate_visit_log_ensure_page_title_column($pdo);

        return ['ok' => true, 'message' => 'fate_visit_log 已建立或已存在。'];
    } catch (Throwable $e) {
        return ['ok' => false, 'message' => $e->getMessage()];
    }
}

/**
 * 本週熱門店家（依該週詳情頁 `store.{id}` 瀏覽筆數；定點／非定點各自獨立排行）。
 *
 * @return list<array<string,mixed>>
 */
function fate_visit_log_weekly_top_stores(
    PDO $pdo,
    string $weekStartYmd,
    int $limit = 10,
    string $listingKind = 'fixed'
): array {
    require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_store_listing.php';

    $limit = max(1, min(50, $limit));
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $weekStartYmd)) {
        return [];
    }

    try {
        fate_visit_log_ensure_table($pdo);
        $tz = new DateTimeZone(date_default_timezone_get());
        $start = new DateTimeImmutable($weekStartYmd . ' 00:00:00', $tz);
        $endEx = $start->modify('+7 days');
        $startStr = $start->format('Y-m-d H:i:s');
        $endStr = $endEx->format('Y-m-d H:i:s');
        $listingCond = fate_store_listing_sql_condition_aliased(
            fate_store_listing_normalize_kind($listingKind),
            'snap'
        );

        $sql = 'SELECT t.spa_store_id, t.week_hits,
                snap.store_name, snap.city_name, snap.district_name, snap.display_address,
                snap.cover_photo_path, snap.photos_json, snap.place_rating, snap.place_ratings_total,
                snap.payload_json
                FROM (
                    SELECT CAST(SUBSTRING(v.page_key FROM 7) AS UNSIGNED) AS spa_store_id,
                           COUNT(*) AS week_hits
                    FROM fate_visit_log v
                    WHERE v.page_key LIKE \'store.%\'
                      AND v.viewed_at >= ?
                      AND v.viewed_at < ?
                    GROUP BY spa_store_id
                    HAVING spa_store_id > 0
                ) t
                INNER JOIN spa_store_snapshots snap ON snap.spa_store_id = t.spa_store_id
                    AND snap.is_approved = 1
                    AND (' . $listingCond . ')
                ORDER BY t.week_hits DESC, t.spa_store_id ASC
                LIMIT ' . $limit;
        $st = $pdo->prepare($sql);
        $st->execute([$startStr, $endStr]);
        $out = [];
        while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
            if (is_array($row)) {
                $out[] = $row;
            }
        }

        return $out;
    } catch (Throwable) {
        return [];
    }
}

function fate_visit_log_distinct_ip_total(PDO $pdo): int
{
    fate_visit_log_ensure_table($pdo);
    $n = $pdo->query(
        'SELECT COUNT(DISTINCT client_ip) FROM fate_visit_log WHERE client_ip <> \'\''
    )->fetchColumn();

    return (int) $n;
}

function fate_visit_log_distinct_ip_since_datetime(PDO $pdo, string $startDatetime): int
{
    fate_visit_log_ensure_table($pdo);
    $st = $pdo->prepare(
        'SELECT COUNT(DISTINCT client_ip) FROM fate_visit_log WHERE client_ip <> \'\' AND viewed_at >= ?'
    );
    $st->execute([$startDatetime]);

    return (int) $st->fetchColumn();
}

function fate_visit_log_distinct_ip_in_window(PDO $pdo, int $windowMinutes): int
{
    $windowMinutes = max(1, min(24 * 60, $windowMinutes));
    fate_visit_log_ensure_table($pdo);
    $st = $pdo->prepare(
        'SELECT COUNT(DISTINCT client_ip) FROM fate_visit_log
         WHERE client_ip <> \'\' AND viewed_at >= DATE_SUB(NOW(), INTERVAL ? MINUTE)'
    );
    $st->bindValue(1, $windowMinutes, PDO::PARAM_INT);
    $st->execute();

    return (int) $st->fetchColumn();
}
