<?php
declare(strict_types=1);

/**
 * app/front.php
 * フロント出力（一覧・詳細）のための取得ユーティリティ
 *
 * 依存：
 * - config.php / helpers.php / db.php
 * - app/fields.php（fields_meta_for_category）
 */

require_once __DIR__ . '/../helpers.php';
require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/fields.php';

/**
 * カテゴリ情報＋フィールドメタを返す
 * @return array{category: array<string,mixed>, meta: array<string,mixed>}|null
 */
function front_category_and_meta(string $categorySlug): ?array {
    $slug = preg_replace('~[^A-Za-z0-9_\-]+~', '', $categorySlug);
    if ($slug === '') return null;

    $pdo = db();
    $st  = $pdo->prepare('SELECT * FROM categories WHERE slug = ? LIMIT 1');
    $st->execute([$slug]);
    $cat = $st->fetch(PDO::FETCH_ASSOC);
    if (!$cat) return null;

    $meta = fields_meta_for_category((int)$cat['id']);
    return ['category' => $cat, 'meta' => $meta];
}

/**
 * 一覧取得（公開=1のみ）
 * GET想定の引数：
 * - q        : タイトル部分一致（100文字上限）
 * - sort     : id|created_at|updated_at（既定 id）
 * - dir      : asc|desc（既定 desc）
 * - page     : 1.. （既定 1）
 * - per      : 20|50|100（既定 20）
 *
 * @return array{
 *   items: array<int, array<string,mixed>>,
 *   pager: array{page:int, per:int, total:int, total_pages:int, sort:string, dir:string}
 * }
 */
function front_list(string $categorySlug, array $query): array {
    $slug = preg_replace('~[^A-Za-z0-9_\-]+~', '', $categorySlug);
    if ($slug === '') {
        return ['items' => [], 'pager' => ['page'=>1,'per'=>20,'total'=>0,'total_pages'=>1,'sort'=>'id','dir'=>'desc']];
    }

    $allowedSort = ['id','created_at','updated_at'];
    $sort = (string)($query['sort'] ?? 'id');
    if (!in_array($sort, $allowedSort, true)) $sort = 'id';

    $dirRaw = strtolower((string)($query['dir'] ?? 'desc'));
    $dirSql = ($dirRaw === 'asc') ? 'ASC' : 'DESC';

    $page = (int)($query['page'] ?? 1);
    if ($page < 1) $page = 1;

    $per = (int)($query['per'] ?? 20);
    if (!in_array($per, [20,50,100], true)) $per = 20;

    $qRaw = (string)($query['q'] ?? '');
    $q    = function_exists('mb_substr') ? mb_substr(trim($qRaw), 0, 100, 'UTF-8') : substr(trim($qRaw), 0, 100);

    $pdo = db();

    // 件数
    $sqlWhere = ' WHERE category = :category AND active = 1';
    $params   = [':category' => $slug];

    if ($q !== '') {
        // タイトルは常に検索対象 + searchable=1 のカスタム項目も検索対象
        $sqlWhere .= ' AND (items.title LIKE :q OR EXISTS (
            SELECT 1
              FROM categories c
              JOIN fields f ON f.category_id = c.id
              JOIN item_values iv
                ON iv.item_id = items.id
               AND iv.field_key = f."key"
             WHERE c.slug = items.category
               AND f.searchable = 1
               AND iv.value LIKE :q
        ))';
        $params[':q'] = '%' . $q . '%';
    }

    // 絞り込み（f[KEY] / f[KEY][]=... を AND 条件、同一KEY内は OR/AND を切替）
    // 対象：fields.type ∈ ("select","radio","checkbox") のみ
    if (isset($query['f']) && is_array($query['f'])) {
        $filterClauses = [];
        $i = 0;
        foreach ($query['f'] as $k => $raw) {
            $key = preg_replace('~[^A-Za-z0-9_\-]+~', '', (string)$k);
            if ($key === '') continue;

            // 値を配列に正規化
            $vals = is_array($raw)
                ? array_values(array_filter(array_map('trim', $raw), 'strlen'))
                : ((trim((string)$raw) !== '') ? [trim((string)$raw)] : []);
            if (!$vals) continue;

            // 切替スイッチ（config.php）：TPCMS_CHECKBOX_JOIN = 'OR' | 'AND'
            $joinMode = (defined('TPCMS_CHECKBOX_JOIN') ? strtoupper((string)TPCMS_CHECKBOX_JOIN) : 'OR');

            if ($joinMode === 'AND' && count($vals) > 1) {
                // —— AND方式（同一フィールド内の全値に一致）：
                //     値ごとに独立した EXISTS を作って AND で連結（checkbox の複数値に対応）
                $ands = [];
                foreach ($vals as $v) {
                    $i++;
                    $pEq   = ':fv_eq_'   . $i; // 完全一致
                    $pLike = ':fv_like_' . $i; // checkbox（CSV）用
                    $pFk   = ':fk_'      . $i; // 同じ key を複数回使うので placeholder も複数用意

                    $ands[] =
                        'EXISTS ( ' .
                        ' SELECT 1 FROM categories c ' .
                        ' JOIN fields f ON f.category_id = c.id ' .
                        ' JOIN item_values iv ON iv.item_id = items.id AND iv.field_key = f."key" ' .
                        ' WHERE c.slug = items.category ' .
                        '   AND f."key" = ' . $pFk . ' ' .
                        '   AND f.type IN ("select","radio","checkbox") ' .
                        '   AND (iv.value = ' . $pEq . ' OR ("," || iv.value || ",") LIKE ' . $pLike . ') ' .
                        ')';
                    $params[$pEq]   = $v;
                    $params[$pLike] = '%,' . $v . ',%';
                    $params[$pFk]   = $key;
                }
                $filterClauses[] = '(' . implode(' AND ', $ands) . ')';

            } else {
                // —— 既定：OR方式（同一フィールド内でどれか一致）
                $inner = [];
                foreach ($vals as $v) {
                    $i++;
                    $pEq   = ':fv_eq_'   . $i; // select/radio の完全一致
                    $pLike = ':fv_like_' . $i; // checkbox（カンマ区切り）用
                    $inner[] = '(iv.value = ' . $pEq . ' OR ("," || iv.value || ",") LIKE ' . $pLike . ')';
                    $params[$pEq]   = $v;
                    $params[$pLike] = '%,' . $v . ',%';
                }

                // カテゴリに属する fields のうち、該当キー＆型が select/radio/checkbox の item_values に一致するものが存在
                $pFk = ':fk_' . $i;
                $filterClauses[] =
                    'EXISTS ( ' .
                    ' SELECT 1 FROM categories c ' .
                    ' JOIN fields f ON f.category_id = c.id ' .
                    ' JOIN item_values iv ON iv.item_id = items.id AND iv.field_key = f."key" ' .
                    ' WHERE c.slug = items.category ' .
                    '   AND f."key" = ' . $pFk . ' ' .
                    '   AND f.type IN ("select","radio","checkbox") ' .
                    '   AND (' . implode(' OR ', $inner) . ')' .
                    ')';
                $params[$pFk] = $key;
            }
        }

        if ($filterClauses) {
            // 複数キーが指定された場合は AND（= すべての条件を満たす）
            $sqlWhere .= ' AND ' . implode(' AND ', $filterClauses);
        }
    }

    $stmtCnt = $pdo->prepare('SELECT COUNT(*) FROM items' . $sqlWhere);
    foreach ($params as $k => $v) { $stmtCnt->bindValue($k, $v, PDO::PARAM_STR); }
    $stmtCnt->execute();
    $total = (int)$stmtCnt->fetchColumn();

    $totalPages = max(1, (int)ceil($total / $per));
    if ($page > $totalPages) $page = $totalPages;
    $offset = ($page - 1) * $per;

    // 本体
    $sql = 'SELECT id, category, title, body, asset_file, active, created_at, updated_at
              FROM items' . $sqlWhere .
           ' ORDER BY ' . $sort . ' ' . $dirSql . ', id DESC' .
           ' LIMIT ' . (int)$per . ' OFFSET ' . (int)$offset;

    $stmt = $pdo->prepare($sql);
    foreach ($params as $k => $v) { $stmt->bindValue($k, $v, PDO::PARAM_STR); }
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

    // item_values を一括取得してマージ
    $ids = array_values(array_filter(array_map(fn($r)=> (int)($r['id'] ?? 0), $rows), fn($v)=> $v>0));
    $ivMap = []; // [item_id => [field_key => value]]
    if ($ids) {
        $ph = implode(',', array_fill(0, count($ids), '?'));
        $stIv = $pdo->prepare('SELECT item_id, field_key, value FROM item_values WHERE item_id IN (' . $ph . ')');
        $stIv->execute($ids);
        foreach ($stIv->fetchAll(PDO::FETCH_ASSOC) ?: [] as $v) {
            $iid = (int)($v['item_id'] ?? 0);
            $fk  = (string)($v['field_key'] ?? '');
            $val = (string)($v['value'] ?? '');
            if ($iid > 0 && $fk !== '') {
                if (!isset($ivMap[$iid])) $ivMap[$iid] = [];
                $ivMap[$iid][$fk] = $val;
            }
        }
    }

    // フロントでそのまま使いやすい形へ整形
    $items = [];
    foreach ($rows as $r) {
        $iid = (int)$r['id'];
        $one = $r;
        $one['values'] = $ivMap[$iid] ?? [];

        // メインアセットURL（items.asset_file）
        $afn = (string)($r['asset_file'] ?? '');
        $one['asset_url'] = ($afn !== '') ? asset_url('/uploads/' . $afn, false) : '';

        $items[] = $one;
    }

    return [
        'items' => $items,
        'pager' => [
            'page'        => $page,
            'per'         => $per,
            'total'       => $total,
            'total_pages' => $totalPages,
            'sort'        => $sort,
            'dir'         => strtolower($dirSql),
        ],
    ];
}

/**
 * 詳細1件（公開=1のみ）
 * @return array<string,mixed>|null
 */
function front_detail(string $categorySlug, int $id): ?array {
    $slug = preg_replace('~[^A-Za-z0-9_\-]+~', '', $categorySlug);
    $id   = max(0, (int)$id);
    if ($slug === '' || $id <= 0) return null;

    $pdo = db();
    $st  = $pdo->prepare('SELECT id, category, title, body, asset_file, active, created_at, updated_at
                            FROM items
                           WHERE id = ? AND category = ? AND active = 1
                           LIMIT 1');
    $st->execute([$id, $slug]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if (!$row) return null;

    // item_values
    $stV = $pdo->prepare('SELECT field_key, value FROM item_values WHERE item_id = ?');
    $stV->execute([$id]);
    $vals = [];
    foreach ($stV->fetchAll(PDO::FETCH_ASSOC) ?: [] as $v) {
        $fk = (string)($v['field_key'] ?? '');
        if ($fk !== '') $vals[$fk] = (string)($v['value'] ?? '');
    }

    $row['values'] = $vals;

    // メインアセットURL（items.asset_file）
    $afn = (string)($row['asset_file'] ?? '');
    $row['asset_url'] = ($afn !== '') ? asset_url('/uploads/' . $afn, false) : '';

    return $row;
}

/**
 * 新着（カテゴリ横断／公開=1から最新n件）
 * @param int $limit 取得件数（既定8, 1〜100に丸め）
 * @return array{items: array<int, array<string,mixed>>}
 */
function front_recents(int $limit = 8): array {
    $limit = max(1, min(100, $limit));

    $pdo = db();

    // 本体（created_at DESC, id DESC）
    $sql = 'SELECT id, category, title, body, asset_file, active, created_at, updated_at
              FROM items
             WHERE active = 1
             ORDER BY created_at DESC, id DESC
             LIMIT ' . (int)$limit;
    $st = $pdo->query($sql);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

    // item_values を一括取得してマージ
    $ids = array_values(array_filter(array_map(fn($r)=> (int)($r['id'] ?? 0), $rows), fn($v)=> $v>0));
    $ivMap = []; // [item_id => [field_key => value]]
    if ($ids) {
        $ph = implode(',', array_fill(0, count($ids), '?'));
        $stIv = $pdo->prepare('SELECT item_id, field_key, value FROM item_values WHERE item_id IN (' . $ph . ')');
        $stIv->execute($ids);
        foreach ($stIv->fetchAll(PDO::FETCH_ASSOC) ?: [] as $v) {
            $iid = (int)($v['item_id'] ?? 0);
            $fk  = (string)($v['field_key'] ?? '');
            $val = (string)($v['value'] ?? '');
            if ($iid > 0 && $fk !== '') {
                if (!isset($ivMap[$iid])) $ivMap[$iid] = [];
                $ivMap[$iid][$fk] = $val;
            }
        }
    }

    // フロントでそのまま使いやすい形へ整形（asset_url を付与）
    $items = [];
    foreach ($rows as $r) {
        $iid = (int)$r['id'];
        $one = $r;
        $one['values'] = $ivMap[$iid] ?? [];

        $afn = (string)($r['asset_file'] ?? '');
        $one['asset_url'] = ($afn !== '') ? asset_url('/uploads/' . $afn, false) : '';

        $items[] = $one;
    }

    return ['items' => $items];
}

/**
 * カテゴリ一覧＋公開件数サマリー
 * @param bool $onlyActive true=公開カテゴリのみ
 * @return array<int, array{slug:string,name:string,count:int}>
 */
function front_categories_summary(bool $onlyActive = true): array {
    $pdo = db();

    $sql = 'SELECT c.slug AS slug, c.name AS name, c.is_active,
                   COUNT(i.id) AS cnt
              FROM categories c
         LEFT JOIN items i
                ON i.category = c.slug AND i.active = 1
             ' . ($onlyActive ? 'WHERE c.is_active = 1' : '') . '
          GROUP BY c.id
          ORDER BY c.created_at DESC, c.id DESC';

    $st = $pdo->query($sql);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

    $out = [];
    foreach ($rows as $r) {
        $slug = (string)($r['slug'] ?? '');
        if ($slug === '') continue;
        $out[] = [
            'slug'  => $slug,
            'name'  => (string)($r['name'] ?? $slug),
            'count' => (int)($r['cnt'] ?? 0),
        ];
    }
    return $out;
}
