<?php
declare(strict_types=1);

/**
 * 將 store_public API（omit_google_reviews）之 meta + store 對應為 fate.spa_store_snapshots 欄位。
 *
 * @param array<string,mixed> $meta
 * @param array<string,mixed> $store
 * @return array<string,mixed> PDO 綁定用（鍵為欄位名，不含 spa_store_id 時由呼叫端補）
 */
function fate_spa_normalize_store_row(array $meta, array $store, string $syncedAt, ?string $payloadJson): array
{
    $str = static function ($v, int $max = 65535): ?string {
        if ($v === null) {
            return null;
        }
        $s = trim((string) $v);
        if ($s === '') {
            return null;
        }
        if ($max > 0 && strlen($s) > $max) {
            return substr($s, 0, $max);
        }
        return $s;
    };

    $tc = isset($meta['google_reviews_table_cursor']) && is_array($meta['google_reviews_table_cursor'])
        ? $meta['google_reviews_table_cursor']
        : [];
    $tblRows = isset($tc['row_count']) ? (int) $tc['row_count'] : null;
    $tblMaxId = isset($tc['max_id']) && $tc['max_id'] !== null ? (int) $tc['max_id'] : null;

    $photos = $store['photos'] ?? [];
    $photosJson = null;
    if (is_array($photos) && $photos !== []) {
        try {
            $photosJson = json_encode(array_values($photos), JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR);
        } catch (Throwable $e) {
            $photosJson = null;
        }
    }

    $bh = $store['business_hours'] ?? [];
    $bhJson = null;
    if (is_array($bh) && $bh !== []) {
        try {
            $bhJson = json_encode($bh, JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR);
        } catch (Throwable $e) {
            $bhJson = null;
        }
    }

    $lat = isset($store['latitude']) && $store['latitude'] !== '' && $store['latitude'] !== null
        ? round((float) $store['latitude'], 7)
        : null;
    $lng = isset($store['longitude']) && $store['longitude'] !== '' && $store['longitude'] !== null
        ? round((float) $store['longitude'], 7)
        : null;

    $basic = isset($store['basic_charge']) && $store['basic_charge'] !== '' && $store['basic_charge'] !== null && is_numeric($store['basic_charge'])
        ? round((float) $store['basic_charge'], 2)
        : null;

    return [
        'synced_at' => $syncedAt,
        'source_data_as_of_unix' => (int) ($meta['data_as_of_unix'] ?? 0),
        'source_data_as_of_iso' => $str($meta['data_as_of_iso'] ?? null, 40),
        'meta_store_updated_at' => $str($meta['store_updated_at'] ?? null, 64),
        'meta_place_last_updated' => $str($meta['place_last_updated'] ?? null, 64),
        'google_reviews_source' => $str($meta['google_reviews_source'] ?? null, 32),
        'google_reviews_count' => isset($meta['google_reviews_count']) ? (int) $meta['google_reviews_count'] : 0,
        'google_reviews_table_rows' => $tblRows !== null && $tblRows >= 0 ? $tblRows : null,
        'google_reviews_table_max_id' => $tblMaxId !== null && $tblMaxId > 0 ? $tblMaxId : null,
        'store_name' => $str($store['store_name'] ?? '', 255) ?? '',
        'display_address' => $str($store['display_address'] ?? null, 0),
        'city_name' => $str($store['city_name'] ?? null, 120),
        'district_name' => $str($store['district_name'] ?? null, 120),
        'street_address' => $str($store['street_address'] ?? null, 512),
        'place_formatted_address' => $str($store['place_formatted_address'] ?? null, 0),
        'google_place_id' => $str($store['google_place_id'] ?? null, 255),
        'google_map_location' => $str($store['google_map_location'] ?? null, 255),
        'latitude' => $lat,
        'longitude' => $lng,
        'place_rating' => isset($store['place_rating']) && $store['place_rating'] !== '' && $store['place_rating'] !== null
            ? round((float) $store['place_rating'], 2)
            : null,
        'place_ratings_total' => isset($store['place_ratings_total']) && $store['place_ratings_total'] !== '' && $store['place_ratings_total'] !== null
            ? (int) $store['place_ratings_total']
            : null,
        'phone' => $str($store['phone'] ?? null, 80),
        'mobile' => $str($store['mobile'] ?? null, 80),
        'line' => $str($store['line'] ?? null, 255),
        'line_id' => $str($store['line_id'] ?? null, 255),
        'wechat' => $str($store['wechat'] ?? null, 255),
        'wechat_id' => $str($store['wechat_id'] ?? null, 255),
        'telegram_contact' => $str($store['telegram_contact'] ?? null, 255),
        'tiktok_contact' => $str($store['tiktok_contact'] ?? null, 255),
        'email' => $str($store['email'] ?? null, 255),
        'website' => $str($store['website'] ?? null, 512),
        'facebook' => $str($store['facebook'] ?? null, 512),
        'cover_photo_path' => $str($store['cover_photo_path'] ?? null, 512),
        'photos_json' => $photosJson,
        'place_opening_hours_raw' => $str($store['place_opening_hours_raw'] ?? null, 0),
        'business_hours_json' => $bhJson,
        'business_hours_start' => $str($store['business_hours_start'] ?? null, 64),
        'business_hours_end' => $str($store['business_hours_end'] ?? null, 64),
        'business_type' => $str($store['business_type'] ?? null, 128),
        'appointment_required' => $str($store['appointment_required'] ?? null, 64),
        'parking' => $str($store['parking'] ?? null, 255),
        'payment_method' => $str($store['payment_method'] ?? null, 255),
        'spa_type' => $str($store['spa_type'] ?? null, 128),
        'basic_charge' => $basic,
        'prev_store_id' => isset($store['prev_store_id']) && $store['prev_store_id'] !== '' && $store['prev_store_id'] !== null
            ? (int) $store['prev_store_id']
            : null,
        'next_store_id' => isset($store['next_store_id']) && $store['next_store_id'] !== '' && $store['next_store_id'] !== null
            ? (int) $store['next_store_id']
            : null,
        'public_detail_url_path' => $str($store['public_detail_url_path'] ?? null, 255),
        'payload_json' => $payloadJson,
        'source_type' => 'api',
        'is_approved' => 1,
        'created_member_id' => null,
    ];
}

/**
 * @return list<string>
 */
function fate_spa_normalized_snapshot_columns(): array
{
    return [
        'spa_store_id',
        'synced_at',
        'source_data_as_of_unix',
        'source_data_as_of_iso',
        'meta_store_updated_at',
        'meta_place_last_updated',
        'google_reviews_source',
        'google_reviews_count',
        'google_reviews_table_rows',
        'google_reviews_table_max_id',
        'store_name',
        'display_address',
        'city_name',
        'district_name',
        'street_address',
        'place_formatted_address',
        'google_place_id',
        'google_map_location',
        'latitude',
        'longitude',
        'place_rating',
        'place_ratings_total',
        'phone',
        'mobile',
        'line',
        'line_id',
        'wechat',
        'wechat_id',
        'telegram_contact',
        'tiktok_contact',
        'email',
        'website',
        'facebook',
        'cover_photo_path',
        'photos_json',
        'place_opening_hours_raw',
        'business_hours_json',
        'business_hours_start',
        'business_hours_end',
        'business_type',
        'appointment_required',
        'parking',
        'payment_method',
        'spa_type',
        'basic_charge',
        'prev_store_id',
        'next_store_id',
        'public_detail_url_path',
        'payload_json',
        'source_type',
        'is_approved',
        'created_member_id',
    ];
}

/**
 * INSERT … ON DUPLICATE KEY UPDATE 之 SQL 片段（spa_store_snapshots 正規化後）。
 */
function fate_spa_normalized_upsert_sql(): string
{
    $cols = fate_spa_normalized_snapshot_columns();
    $quoted = array_map(static fn (string $c): string => '`' . str_replace('`', '``', $c) . '`', $cols);
    $insertList = implode(', ', $quoted);
    $ph = implode(', ', array_map(static fn (string $c): string => ':' . $c, $cols));

    $updates = [];
    foreach ($cols as $c) {
        if ($c === 'spa_store_id') {
            continue;
        }
        $updates[] = '`' . str_replace('`', '``', $c) . '`=VALUES(`' . str_replace('`', '``', $c) . '`)';
    }

    return 'INSERT INTO spa_store_snapshots (' . $insertList . ')
            VALUES (' . $ph . ')
            ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
}
