<?php

declare(strict_types=1);

/**
 * 後台「推薦技師」管理：列表查詢輔助（CRUD 與獎勵邏輯沿用 fate_store_masseurs.php）。
 */

require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_store_masseurs.php';

/**
 * @return array{join:string,where:string,params:list<mixed>}
 */
function fate_admin_masseurs_list_query_parts(string $keyword): array
{
    $memberQt = fate_masseur_member_table_qualified();
    $join = 'FROM `fate_store_masseurs` m ';
    $join .= 'LEFT JOIN `spa_store_snapshots` snap ON snap.`spa_store_id` = m.`spa_store_id` ';
    if ($memberQt !== null) {
        $join .= "LEFT JOIN $memberQt mb ON mb.`id` = m.`created_by_member_id` ";
    }
    $where = '';
    $params = [];
    $kw = trim($keyword);
    if ($kw !== '') {
        $where = 'WHERE m.`name` LIKE ? OR snap.`store_name` LIKE ? ';
        $params[] = '%' . $kw . '%';
        $params[] = '%' . $kw . '%';
    }

    return ['join' => $join, 'where' => $where, 'params' => $params];
}

function fate_admin_masseurs_count(PDO $pdo, string $keyword = ''): int
{
    fate_masseur_ensure_tables($pdo);
    $parts = fate_admin_masseurs_list_query_parts($keyword);
    $sql = 'SELECT COUNT(*) ' . $parts['join'] . $parts['where'];

    try {
        $st = $pdo->prepare($sql);
        $st->execute($parts['params']);

        return (int) $st->fetchColumn();
    } catch (Throwable) {
        try {
            return (int) $pdo->query('SELECT COUNT(*) FROM `fate_store_masseurs`')->fetchColumn();
        } catch (Throwable) {
            return 0;
        }
    }
}

/**
 * 技師列表（含店名、建檔者名稱、評論數）。
 *
 * @return list<array<string,mixed>>
 */
function fate_admin_masseurs_list(PDO $pdo, string $keyword = '', int $limit = 100, int $offset = 0): array
{
    fate_masseur_ensure_tables($pdo);
    $limit = max(1, min(500, $limit));
    $offset = max(0, $offset);
    $memberQt = fate_masseur_member_table_qualified();
    $parts = fate_admin_masseurs_list_query_parts($keyword);

    $sql = 'SELECT m.*, snap.`store_name` AS store_name, ';
    $sql .= $memberQt !== null
        ? "COALESCE(NULLIF(TRIM(mb.`display_name`), ''), mb.`username`, CONCAT('會員#', m.`created_by_member_id`)) AS creator_name, "
        : "CONCAT('會員#', m.`created_by_member_id`) AS creator_name, ";
    $sql .= '(SELECT COUNT(*) FROM `fate_masseur_reviews` r WHERE r.`masseur_id` = m.`id`) AS review_count ';
    $sql .= $parts['join'] . $parts['where'];
    $params = $parts['params'];
    $sql .= 'ORDER BY m.`id` DESC LIMIT ' . $limit . ' OFFSET ' . $offset;

    try {
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);

        return is_array($rows) ? $rows : [];
    } catch (Throwable) {
        // 退回不含 JOIN 的簡查
        try {
            $st = $pdo->query(
                'SELECT * FROM `fate_store_masseurs` ORDER BY `id` DESC LIMIT '
                . $limit . ' OFFSET ' . $offset
            );

            return $st !== false ? $st->fetchAll(PDO::FETCH_ASSOC) : [];
        } catch (Throwable) {
            return [];
        }
    }
}

function fate_admin_masseur_reviews_count(PDO $pdo): int
{
    fate_masseur_ensure_tables($pdo);

    try {
        return (int) $pdo->query('SELECT COUNT(*) FROM `fate_masseur_reviews`')->fetchColumn();
    } catch (Throwable) {
        return 0;
    }
}

/**
 * 評論列表分頁資訊。
 *
 * @return array{rows:list<array<string,mixed>>,total:int,page:int,per_page:int,pages:int,from:int,to:int}
 */
function fate_admin_masseur_reviews_page(PDO $pdo, int $page = 1, int $perPage = 20): array
{
    $perPage = max(10, min(100, $perPage));
    $total = fate_admin_masseur_reviews_count($pdo);
    $pages = max(1, (int) ceil($total / $perPage));
    $page = max(1, min($page, $pages));
    $offset = ($page - 1) * $perPage;
    $rows = fate_admin_masseur_reviews_recent($pdo, $perPage, $offset);
    $from = $total > 0 ? $offset + 1 : 0;
    $to = min($offset + count($rows), $total);

    return [
        'rows' => $rows,
        'total' => $total,
        'page' => $page,
        'per_page' => $perPage,
        'pages' => $pages,
        'from' => $from,
        'to' => $to,
    ];
}

/**
 * 技師列表分頁資訊。
 *
 * @return array{rows:list<array<string,mixed>>,total:int,page:int,per_page:int,pages:int,from:int,to:int}
 */
function fate_admin_masseurs_page(PDO $pdo, string $keyword = '', int $page = 1, int $perPage = 25): array
{
    $perPage = max(10, min(100, $perPage));
    $total = fate_admin_masseurs_count($pdo, $keyword);
    $pages = max(1, (int) ceil($total / $perPage));
    $page = max(1, min($page, $pages));
    $offset = ($page - 1) * $perPage;
    $rows = fate_admin_masseurs_list($pdo, $keyword, $perPage, $offset);
    $from = $total > 0 ? $offset + 1 : 0;
    $to = min($offset + count($rows), $total);

    return [
        'rows' => $rows,
        'total' => $total,
        'page' => $page,
        'per_page' => $perPage,
        'pages' => $pages,
        'from' => $from,
        'to' => $to,
    ];
}

/**
 * 最新評論（跨技師），供後台快速審閱。
 *
 * @return list<array<string,mixed>>
 */
function fate_admin_masseur_reviews_recent(PDO $pdo, int $limit = 50, int $offset = 0): array
{
    fate_masseur_ensure_tables($pdo);
    $limit = max(1, min(300, $limit));
    $offset = max(0, $offset);
    $memberQt = fate_masseur_member_table_qualified();

    $sql = 'SELECT r.*, m.`name` AS masseur_name, m.`spa_store_id` AS spa_store_id, ';
    $sql .= $memberQt !== null
        ? "COALESCE(NULLIF(TRIM(mb.`display_name`), ''), mb.`username`, CONCAT('會員#', r.`member_id`)) AS author_name "
        : "CONCAT('會員#', r.`member_id`) AS author_name ";
    $sql .= 'FROM `fate_masseur_reviews` r ';
    $sql .= 'LEFT JOIN `fate_store_masseurs` m ON m.`id` = r.`masseur_id` ';
    if ($memberQt !== null) {
        $sql .= "LEFT JOIN $memberQt mb ON mb.`id` = r.`member_id` ";
    }
    $sql .= 'ORDER BY r.`id` DESC LIMIT ' . $limit . ' OFFSET ' . $offset;

    try {
        $st = $pdo->query($sql);

        return $st !== false ? $st->fetchAll(PDO::FETCH_ASSOC) : [];
    } catch (Throwable) {
        try {
            $st = $pdo->query(
                'SELECT * FROM `fate_masseur_reviews` ORDER BY `id` DESC LIMIT '
                . $limit . ' OFFSET ' . $offset
            );

            return $st !== false ? $st->fetchAll(PDO::FETCH_ASSOC) : [];
        } catch (Throwable) {
            return [];
        }
    }
}

/** 評論預覽（長文折疊用）。 */
function fate_admin_masseur_review_snippet(string $body, int $maxLen = 96): array
{
    $body = trim($body);
    if ($body === '') {
        return ['preview' => '', 'truncated' => false, 'length' => 0];
    }
    $len = mb_strlen($body, 'UTF-8');
    if ($len <= $maxLen) {
        return ['preview' => $body, 'truncated' => false, 'length' => $len];
    }

    return [
        'preview' => mb_substr($body, 0, $maxLen, 'UTF-8') . '…',
        'truncated' => true,
        'length' => $len,
    ];
}
