# -*- coding: utf-8 -*-
"""
事務所地址批次地理編碼

使用 TGOS API 將事務所地址轉換為座標
"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
import json
import time
import requests
from pathlib import Path

# TGOS API 設定
TGOS_API_URL = "https://api.tgos.tw/TGOSGeocoder/Geocode"
TGOS_API_KEY = "your_api_key_here"  # 需要申請

# 使用免費的 Nominatim API 作為替代（OpenStreetMap）
NOMINATIM_URL = "https://nominatim.openstreetmap.org/search"

# 快取檔案
CACHE_FILE = Path(__file__).parent.parent.parent / "geocoding_cache.json"


def load_cache():
    """載入快取"""
    if CACHE_FILE.exists():
        with open(CACHE_FILE, 'r', encoding='utf-8') as f:
            return json.load(f)
    return {}


def save_cache(cache):
    """儲存快取"""
    with open(CACHE_FILE, 'w', encoding='utf-8') as f:
        json.dump(cache, f, ensure_ascii=False, indent=2)


def geocode_nominatim(address):
    """使用 Nominatim (OpenStreetMap) 進行地理編碼"""
    try:
        # 標準化地址（將「臺」轉為「台」）
        normalized = address.replace('臺', '台')

        response = requests.get(NOMINATIM_URL, params={
            'q': normalized,
            'format': 'json',
            'countrycodes': 'tw',
            'limit': 1,
        }, headers={
            'User-Agent': 'TaiwanAppraiserRegistry/1.0'
        }, timeout=10)

        if response.status_code == 200:
            results = response.json()
            if results:
                return {
                    'lat': float(results[0]['lat']),
                    'lng': float(results[0]['lon']),
                    'status': 'ok',
                    'source': 'nominatim'
                }
        return {'status': 'not_found', 'source': 'nominatim'}
    except Exception as e:
        return {'status': 'error', 'error': str(e), 'source': 'nominatim'}


def geocode_addresses():
    """批次地理編碼事務所地址"""
    # 連接資料庫
    db_path = Path(__file__).parent / 'data' / 'appraisers.db'
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    # 取得尚未編碼的地址
    cursor.execute('''
        SELECT DISTINCT office_address
        FROM career_history
        WHERE office_address IS NOT NULL
          AND office_address != ''
          AND (lat IS NULL OR geocode_status IS NULL)
    ''')
    addresses = [row['office_address'] for row in cursor.fetchall()]

    print(f'待處理地址: {len(addresses)} 筆')

    # 載入快取
    cache = load_cache()

    # 統計
    stats = {'cached': 0, 'success': 0, 'not_found': 0, 'error': 0}

    for i, address in enumerate(addresses):
        # 檢查快取
        if address in cache and cache[address].get('status') == 'ok':
            result = cache[address]
            stats['cached'] += 1
        else:
            # 呼叫 API
            print(f'[{i+1}/{len(addresses)}] 編碼: {address[:40]}...')
            result = geocode_nominatim(address)

            if result['status'] == 'ok':
                stats['success'] += 1
                cache[address] = result
            elif result['status'] == 'not_found':
                stats['not_found'] += 1
            else:
                stats['error'] += 1

            # 避免請求過快（Nominatim 要求 1 req/sec）
            time.sleep(1.1)

        # 更新資料庫
        if result.get('status') == 'ok':
            cursor.execute('''
                UPDATE career_history
                SET lat = ?, lng = ?, geocode_status = 'ok'
                WHERE office_address = ?
            ''', (result['lat'], result['lng'], address))
        else:
            cursor.execute('''
                UPDATE career_history
                SET geocode_status = ?
                WHERE office_address = ?
            ''', (result.get('status', 'error'), address))

        # 定期提交
        if (i + 1) % 10 == 0:
            conn.commit()
            save_cache(cache)
            print(f'  已處理 {i+1} 筆，快取已儲存')

    # 最終提交
    conn.commit()
    save_cache(cache)
    conn.close()

    # 顯示統計
    print('\n' + '=' * 50)
    print('地理編碼完成')
    print('=' * 50)
    print(f'從快取取得: {stats["cached"]} 筆')
    print(f'成功編碼: {stats["success"]} 筆')
    print(f'找不到: {stats["not_found"]} 筆')
    print(f'錯誤: {stats["error"]} 筆')


if __name__ == '__main__':
    geocode_addresses()