<?php
/**
 * Heritage Voice — sites.php
 * Handlers cho heritage_sites endpoints
 */

function sites_nearby(): void {
    $lat    = float_param('lat');
    $lng    = float_param('lng');
    $radius = int_param('radius', 2000, 100, MAX_NEARBY_RADIUS_M);

    if ($lat === null || $lng === null) json_error('Thiếu tham số lat và lng');

    $dlat = $radius / 111320;
    $dlng = $radius / (111320 * cos(deg2rad($lat)));

    $stmt = db()->prepare("
        SELECT hs.id, hs.name, hs.slug, hs.type,
               hs.latitude, hs.longitude, hs.geofence_radius_m,
               hs.description_short, hs.cover_image, hs.status, hs.featured,
               p.name_new AS province_name,
               ROUND(6371000 * ACOS(LEAST(1.0,
                   COS(RADIANS(:lat1)) * COS(RADIANS(hs.latitude))
                   * COS(RADIANS(hs.longitude) - RADIANS(:lng1))
                   + SIN(RADIANS(:lat2)) * SIN(RADIANS(hs.latitude))
               ))) AS distance_m
        FROM heritage_sites hs
        LEFT JOIN provinces p ON p.id = hs.province_id
        WHERE hs.status = 'active'
          AND hs.latitude  BETWEEN :lat_min AND :lat_max
          AND hs.longitude BETWEEN :lng_min AND :lng_max
        HAVING distance_m <= :radius
        ORDER BY distance_m ASC
        LIMIT 20
    ");
    $stmt->execute([
        ':lat1' => $lat, ':lng1' => $lng, ':lat2' => $lat,
        ':lat_min' => $lat - $dlat, ':lat_max' => $lat + $dlat,
        ':lng_min' => $lng - $dlng, ':lng_max' => $lng + $dlng,
        ':radius'  => $radius,
    ]);

    $rows = $stmt->fetchAll();
    foreach ($rows as &$r) {
        $r['latitude']          = (float)$r['latitude'];
        $r['longitude']         = (float)$r['longitude'];
        $r['geofence_radius_m'] = (int)$r['geofence_radius_m'];
        $r['distance_m']        = (int)$r['distance_m'];
        $r['featured']          = (bool)$r['featured'];
    }
    json_ok($rows);
}

function sites_list(): void {
    $status   = str_param('status', 'active');
    $province = str_param('province');
    $type     = str_param('type');
    $lang     = str_param('lang', 'vi');
    $page     = int_param('page', 1, 1);
    $limit    = int_param('limit', DEFAULT_PAGE_LIMIT, 1, MAX_PAGE_LIMIT);
    $offset   = ($page - 1) * $limit;
    $allowed_langs = ['vi','en','zh','ja','ko','fr'];
    if (!in_array($lang, $allowed_langs)) $lang = 'vi';

    $where  = [];
    $params = [];
    if ($status !== 'all') { $where[] = 'hs.status = :status'; $params[':status'] = $status; }
    if ($province) { $where[] = 'p.name_new LIKE :province'; $params[':province'] = "%$province%"; }
    if ($type)     { $where[] = 'hs.type = :type';           $params[':type']     = $type; }
    $w = $where ? 'WHERE ' . implode(' AND ', $where) : '';

    $cs = db()->prepare("SELECT COUNT(*) FROM heritage_sites hs LEFT JOIN provinces p ON p.id=hs.province_id $w");
    $cs->execute($params);
    $total = (int)$cs->fetchColumn();

    $params[':lang1'] = $lang;
    $params[':lang2'] = $lang;

    $stmt = db()->prepare("
        SELECT hs.id,
               COALESCE(ct_name.content, hs.name) AS name,
               hs.slug, hs.type,
               hs.latitude, hs.longitude, hs.geofence_radius_m,
               COALESCE(ct_desc.content, hs.description_short) AS description_short,
               hs.description_long,
               hs.cover_image, hs.floor_map_url,
               hs.tracuudi_url, hs.status, hs.featured,
               p.name_new AS province_name,
               (SELECT COUNT(*) FROM audio_points ap WHERE ap.site_id = hs.id AND ap.status = 'active') AS audio_points_count,
               (SELECT COALESCE(SUM(ap2.duration_sec),0) FROM audio_points ap2 WHERE ap2.site_id = hs.id AND ap2.status = 'active') AS duration_total_sec
        FROM heritage_sites hs
        LEFT JOIN provinces p ON p.id = hs.province_id
        LEFT JOIN content_translations ct_name
               ON ct_name.entity_type    = 'heritage_site'
              AND ct_name.entity_id      = hs.id
              AND ct_name.language_code  = :lang1
              AND ct_name.field_name     = 'name'
        LEFT JOIN content_translations ct_desc
               ON ct_desc.entity_type    = 'heritage_site'
              AND ct_desc.entity_id      = hs.id
              AND ct_desc.language_code  = :lang2
              AND ct_desc.field_name     = 'description_short'
        $w
        ORDER BY hs.featured DESC, hs.name ASC
        LIMIT :limit OFFSET :offset
    ");
    foreach ($params as $k => $v) $stmt->bindValue($k, $v);
    $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    $rows = $stmt->fetchAll();
    foreach ($rows as &$r) {
        $r['latitude']            = (float)$r['latitude'];
        $r['longitude']           = (float)$r['longitude'];
        $r['geofence_radius_m']   = (int)$r['geofence_radius_m'];
        $r['featured']            = (bool)$r['featured'];
        $r['audio_points_count']  = (int)$r['audio_points_count'];
        $r['duration_total_sec']  = (int)$r['duration_total_sec'];
    }
    json_ok(['items' => $rows, 'pagination' => paginate($total, $page, $limit)]);
}

function site_detail(string $id): void {
    $stmt = db()->prepare("
        SELECT hs.*, p.name_new AS province_name
        FROM heritage_sites hs
        LEFT JOIN provinces p ON p.id = hs.province_id
        WHERE hs.id = :id AND hs.status = 'active'
    ");
    $stmt->execute([':id' => $id]);
    $row = $stmt->fetch();
    if (!$row) json_error('Không tìm thấy điểm đến', 404);

    $row['latitude']          = (float)$row['latitude'];
    $row['longitude']         = (float)$row['longitude'];
    $row['geofence_radius_m'] = (int)$row['geofence_radius_m'];
    $row['featured']          = (bool)$row['featured'];
    json_ok($row);
}

function site_tours(string $site_id): void {
    $lang   = str_param('lang');
    $where  = 'vt.site_id = :site_id AND vt.status = :status';
    $params = [':site_id' => $site_id, ':status' => 'active'];
    if ($lang) { $where .= ' AND vt.language = :lang'; $params[':lang'] = $lang; }

    $stmt = db()->prepare("
        SELECT vt.id, vt.site_id, vt.title, vt.language, vt.tour_type,
               vt.duration_total_sec, vt.audio_points_count,
               vt.offline_available, vt.version, vt.status,
               hs.name AS site_name
        FROM voice_tours vt
        JOIN heritage_sites hs ON hs.id = vt.site_id
        WHERE $where ORDER BY vt.language, vt.tour_type
    ");
    $stmt->execute($params);
    $rows = $stmt->fetchAll();
    foreach ($rows as &$r) {
        $r['duration_total_sec'] = (int)$r['duration_total_sec'];
        $r['audio_points_count'] = (int)$r['audio_points_count'];
        $r['offline_available']  = (bool)$r['offline_available'];
    }
    json_ok($rows);
}

function site_create(): void {
    required_body(['id','name','type']);
    $b = body();

    // Lookup province_id từ province_name
    $provId = $b['province_id'] ?? null;
    if (!$provId && !empty($b['province_name'])) {
        $ps = db()->prepare("SELECT id FROM provinces WHERE name_new = :n1 OR name_old = :n2 LIMIT 1");
        $ps->execute([':n1' => $b['province_name'], ':n2' => $b['province_name']]);
        $prow = $ps->fetch();
        if ($prow) $provId = $prow['id'];
    }

    $slug = $b['slug'] ?? $b['id'];
    db()->prepare("
        INSERT INTO heritage_sites
        (id,name,slug,type,province_id,address_new,ward,hamlet,latitude,longitude,
         geofence_radius_m,description_short,description_long,
         cover_image,tracuudi_id,tracuudi_url,status,featured)
        VALUES(:id,:name,:slug,:type,:prov,:addr,:ward,:hamlet,:lat,:lng,
               :geo,:ds,:dl,:cover,:tid,:turl,:status,:feat)
    ")->execute([
        ':id'     => $b['id'],          ':name'   => $b['name'],
        ':slug'   => $slug,             ':type'   => $b['type'],
        ':prov'   => $provId,           ':addr'   => $b['address_new']      ?? null,
        ':ward'   => $b['ward']         ?? null,   ':hamlet' => $b['hamlet'] ?? null,
        ':lat'    => $b['latitude']     ?? 0,      ':lng'    => $b['longitude']        ?? 0,
        ':geo'    => $b['geofence_radius_m'] ?? 150,
        ':ds'     => $b['description_short'] ?? null, ':dl' => $b['description_long'] ?? null,
        ':cover'  => $b['cover_image']  ?? null,  ':tid'    => $b['tracuudi_id']       ?? null,
        ':turl'   => $b['tracuudi_url'] ?? null,  ':status' => $b['status']            ?? 'draft',
        ':feat'   => (int)($b['featured'] ?? 0),
    ]);
    json_ok(['id' => $b['id']], 'Tạo điểm đến thành công');
}

function site_update(string $id): void {
    $b      = body();
    $fields = ['name','slug','type','address_new','ward','hamlet','latitude','longitude',
               'geofence_radius_m','description_short','description_long',
               'cover_image','floor_map_url','tracuudi_url','status','featured'];
    $set = []; $params = [':id' => $id];

    // Lookup province_id từ province_name
    if (!empty($b['province_name'])) {
        $ps = db()->prepare("SELECT id FROM provinces WHERE name_new = :n1 OR name_old = :n2 LIMIT 1");
        $ps->execute([':n1' => $b['province_name'], ':n2' => $b['province_name']]);
        $prow = $ps->fetch();
        if ($prow) { $set[] = '`province_id`=:province_id'; $params[':province_id'] = $prow['id']; }
    }
    if (isset($b['province_id'])) { $set[] = '`province_id`=:province_id'; $params[':province_id'] = $b['province_id']; }

    foreach ($fields as $f) {
        if (array_key_exists($f, $b)) { $set[] = "`$f`=:$f"; $params[":$f"] = $b[$f]; }
    }
    if (!$set) json_error('Không có trường nào để cập nhật');
    db()->prepare("UPDATE heritage_sites SET " . implode(',', $set) . " WHERE id=:id")->execute($params);
    json_ok(['id' => $id], 'Cập nhật thành công');
}

function site_delete(string $id): void {
    db()->prepare("UPDATE heritage_sites SET status='inactive' WHERE id=:id")->execute([':id' => $id]);
    json_ok(['id' => $id], 'Đã vô hiệu hóa điểm đến');
}

// ── SITE REVIEWS ─────────────────────────────────────────────
function _ensure_site_reviews_table(): void {
    db()->exec("CREATE TABLE IF NOT EXISTS hv_site_reviews (
        id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        site_id    INT UNSIGNED NOT NULL,
        user_id    INT UNSIGNED NOT NULL,
        rating     TINYINT NOT NULL DEFAULT 5,
        comment    TEXT,
        created_at DATETIME DEFAULT NOW(),
        updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
        INDEX idx_site (site_id),
        INDEX idx_user (user_id),
        UNIQUE KEY uniq_site_user (site_id, user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function site_reviews_list(string $siteId): void {
    _ensure_site_reviews_table();
    $limit  = min((int)($_GET['limit']  ?? 20), 50);
    $offset = max((int)($_GET['offset'] ?? 0),  0);
    // List reviews
    $sid = trim($siteId);
    $stmt = db()->prepare("
        SELECT r.id, r.rating, r.comment, r.created_at,
               u.id AS user_id, u.display_name, u.avatar_url
        FROM hv_site_reviews r
        JOIN users u ON u.id = r.user_id
        WHERE r.site_id = :sid
        ORDER BY r.created_at DESC
        LIMIT :lim OFFSET :off
    ");
    $stmt->bindValue(':sid', $sid, PDO::PARAM_INT);
    $stmt->bindValue(':lim', $limit,  PDO::PARAM_INT);
    $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $reviews = $stmt->fetchAll();
    // Summary
    $sum = db()->prepare("
        SELECT COUNT(*) AS total,
               ROUND(AVG(rating),1) AS avg_rating,
               SUM(rating=5) AS s5, SUM(rating=4) AS s4,
               SUM(rating=3) AS s3, SUM(rating=2) AS s2, SUM(rating=1) AS s1
        FROM hv_site_reviews WHERE site_id=:sid
    ");
    $sum->execute([':sid' => $sid]);
    $s = $sum->fetch();
    json_ok([
        'reviews'    => $reviews,
        'total'      => (int)$s['total'],
        'avg_rating' => $s['avg_rating'] ? (float)$s['avg_rating'] : null,
        'dist'       => [5=>(int)$s['s5'],4=>(int)$s['s4'],3=>(int)$s['s3'],2=>(int)$s['s2'],1=>(int)$s['s1']],
    ]);
}

function site_review_submit(string $siteId): void {
    _ensure_site_reviews_table();
    $u      = require_auth();
    $b      = body();
    $rating = max(1, min(5, (int)($b['rating'] ?? 5)));
    $comment = trim($b['comment'] ?? '');
    // site_id là VARCHAR slug
    $sid = trim($siteId);
    if (!$sid) json_error('ID di tích không hợp lệ', 400);
    $check = db()->prepare("SELECT id FROM heritage_sites WHERE id=:sid LIMIT 1");
    $check->execute([':sid' => $sid]);
    if (!$check->fetch()) json_error('Di tích không tồn tại', 404);
    // Upsert
    db()->prepare("
        INSERT INTO hv_site_reviews (site_id, user_id, rating, comment)
        VALUES (:sid, :uid, :rat, :cmt)
        ON DUPLICATE KEY UPDATE rating=:rat2, comment=:cmt2, updated_at=NOW()
    ")->execute([
        ':sid'  => $sid, ':uid' => $u['id'],
        ':rat'  => $rating, ':cmt' => $comment,
        ':rat2' => $rating, ':cmt2'=> $comment,
    ]);
    json_ok(['message' => 'Đã gửi đánh giá']);
}

function tour_list(): void {
    $status = str_param('status') ?: 'active';
    $site_id = str_param('site_id');
    $lang    = str_param('lang');
    $limit   = int_param('limit', 50, 1, 200);

    $where  = [];
    $params = [];

    if ($status !== 'all') {
        $where[] = 'vt.status = :status';
        $params[':status'] = $status;
    }
    if ($site_id) {
        $where[] = 'vt.site_id = :site_id';
        $params[':site_id'] = $site_id;
    }
    if ($lang) {
        $where[] = 'vt.language = :lang';
        $params[':lang'] = $lang;
    }

    $sql = "SELECT vt.*, hs.name AS site_name
            FROM voice_tours vt
            LEFT JOIN heritage_sites hs ON hs.id = vt.site_id"
         . ($where ? ' WHERE ' . implode(' AND ', $where) : '')
         . " ORDER BY vt.created_at DESC LIMIT $limit";

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll();

    foreach ($rows as &$r) {
        $r['duration_total_sec']  = (int)$r['duration_total_sec'];
        $r['audio_points_count']  = (int)$r['audio_points_count'];
        $r['offline_available']   = (bool)$r['offline_available'];
    }
    json_ok($rows);
}
