"""
變更偵測模組

比對新舊資料，偵測會員異動
"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import json
from datetime import datetime
from pathlib import Path

from database import (
    get_connection, get_latest_snapshot, save_snapshot,
    find_appraiser_by_name, insert_appraiser, insert_career_history,
    update_appraiser_last_seen, set_appraiser_inactive,
    end_career_history, log_membership_event, log_change, calculate_checksum
)

def normalize_office_name(name):
    """標準化事務所名稱"""
    if not name:
        return ''
    name = ' '.join(str(name).split())
    return name

def normalize_name(name):
    """
    標準化姓名

    處理：
    - 移除前後空白
    - 移除中間多餘空白（例如「張 三」→「張三」）
    - 移除全形空白
    """
    if not name:
        return ''
    # 移除所有空白（包括全形空白）
    name = ''.join(str(name).split())
    return name


def normalize_record(record):
    """標準化記錄欄位名稱"""
    # 統一欄位名稱（使用加強版姓名正規化）
    name = normalize_name(
        record.get('姓名') or
        ''
    )

    # 會員編號（用於組合鍵）
    # 注意：不使用「序號」，因為那只是流水號，會因其他會員退出而變動
    member_id = (
        record.get('會員編號') or
        record.get('會號') or
        ''
    ).strip()

    office = normalize_office_name(
        record.get('事務所') or
        record.get('事務所名稱') or
        ''
    )

    address = (
        record.get('地址') or
        record.get('開業執照地址') or
        record.get('事務所地址') or
        ''
    ).strip()

    phone = (
        record.get('聯絡電話') or
        record.get('電話') or
        ''
    ).strip()

    return {
        'name': name,
        'member_id': member_id,
        'office': office,
        'address': address,
        'phone': phone,
        'raw': record,
    }


def make_compare_key(normalized):
    """
    產生比對用的 key

    策略：優先用「會員編號 + 姓名」，若無會員編號則用「姓名」
    這樣可以處理同公會內同名同姓的情況
    """
    if normalized['member_id']:
        return f"{normalized['member_id']}_{normalized['name']}"
    return normalized['name']

def detect_changes(old_data, new_data, association):
    """
    比對兩次抓取的差異

    Args:
        old_data: 上次抓取的資料（list of dict）
        new_data: 本次抓取的資料（list of dict）
        association: 公會名稱

    Returns:
        dict: 變更摘要
    """
    changes = {
        'new': [],           # 新加入
        'left': [],          # 退出
        'office_changed': [], # 換事務所
        'info_updated': [],   # 其他資訊更新
        'suspected_data_fix': [],  # 疑似資料修正（同名同時出現在新加入和退出）
    }

    # 標準化並建立索引（使用組合鍵：會員編號 + 姓名）
    old_by_key = {}
    for r in old_data:
        normalized = normalize_record(r)
        if normalized['name']:
            key = make_compare_key(normalized)
            old_by_key[key] = normalized

    new_by_key = {}
    for r in new_data:
        normalized = normalize_record(r)
        if normalized['name']:
            key = make_compare_key(normalized)
            new_by_key[key] = normalized

    # 新加入：本次有，上次沒有
    for key, record in new_by_key.items():
        if key not in old_by_key:
            changes['new'].append({
                'name': record['name'],
                'association': association,
                'office': record['office'],
                'address': record['address'],
                'phone': record['phone'],
            })

    # 退出：上次有，本次沒有
    for key, record in old_by_key.items():
        if key not in new_by_key:
            changes['left'].append({
                'name': record['name'],
                'association': association,
                'office': record['office'],
            })

    # 換事務所 / 其他更新
    for key in new_by_key:
        if key in old_by_key:
            old = old_by_key[key]
            new = new_by_key[key]

            # 比較事務所
            if old['office'] and new['office'] and old['office'] != new['office']:
                changes['office_changed'].append({
                    'name': new['name'],
                    'association': association,
                    'old_office': old['office'],
                    'new_office': new['office'],
                })
            # 比較其他欄位
            elif (old['address'] != new['address'] or old['phone'] != new['phone']):
                changes['info_updated'].append({
                    'name': new['name'],
                    'association': association,
                    'changes': {
                        'address': {'old': old['address'], 'new': new['address']} if old['address'] != new['address'] else None,
                        'phone': {'old': old['phone'], 'new': new['phone']} if old['phone'] != new['phone'] else None,
                    }
                })

    # ===== 智慧比對：偵測疑似資料修正 =====
    # 如果同名的人同時出現在「新加入」和「退出」，可能是公會修正重複資料
    new_names = {item['name'] for item in changes['new']}
    left_names = {item['name'] for item in changes['left']}
    suspected_names = new_names & left_names  # 交集

    if suspected_names:
        # 將疑似資料修正的項目從 new/left 移到 suspected_data_fix
        for name in suspected_names:
            new_item = next((item for item in changes['new'] if item['name'] == name), None)
            left_item = next((item for item in changes['left'] if item['name'] == name), None)

            if new_item and left_item:
                changes['suspected_data_fix'].append({
                    'name': name,
                    'association': association,
                    'old_record': left_item,
                    'new_record': new_item,
                    'reason': '同名同時出現在新加入與退出，可能是公會修正重複資料或會員編號變更',
                })

        # 從原列表移除
        changes['new'] = [item for item in changes['new'] if item['name'] not in suspected_names]
        changes['left'] = [item for item in changes['left'] if item['name'] not in suspected_names]

    return changes

def apply_changes(changes, association):
    """
    將變更套用到資料庫

    Args:
        changes: detect_changes() 的回傳值
        association: 公會名稱

    Returns:
        dict: 套用結果摘要
    """
    today = datetime.now().strftime('%Y-%m-%d')
    results = {
        'new_added': 0,
        'left_marked': 0,
        'office_updated': 0,
        'info_updated': 0,
    }

    # 處理新加入
    for item in changes['new']:
        name = item['name']

        # 檢查是否是舊會員回歸（之前在別的公會或狀態為 inactive）
        existing = find_appraiser_by_name(name)

        if existing:
            # 更新狀態
            update_appraiser_last_seen(existing['id'])

            # 新增事務所記錄
            insert_career_history({
                'appraiser_id': existing['id'],
                'source': 'association',
                'association': association,
                'office_name': item.get('office'),
                'office_address': item.get('address'),
                'phone': item.get('phone'),
            })

            # 記錄會籍
            log_membership_event(existing['id'], association, 'rejoined')
        else:
            # 全新會員
            appraiser_id = insert_appraiser({
                'name': name,
                'identity_confidence': 'medium',
            })

            insert_career_history({
                'appraiser_id': appraiser_id,
                'source': 'association',
                'association': association,
                'office_name': item.get('office'),
                'office_address': item.get('address'),
                'phone': item.get('phone'),
            })

            log_membership_event(appraiser_id, association, 'joined')

        # 記錄變更
        log_change(association, 'new', name, None, item.get('office'), item)
        results['new_added'] += 1

    # 處理退出
    for item in changes['left']:
        name = item['name']
        existing = find_appraiser_by_name(name, association)

        if existing:
            # 結束事務所記錄
            end_career_history(existing['id'], association)

            # 記錄退出
            log_membership_event(existing['id'], association, 'left')

            # 檢查是否還在其他公會
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute('''
                SELECT COUNT(*) as cnt FROM career_history
                WHERE appraiser_id = ? AND is_current = 1
            ''', (existing['id'],))
            active_count = cursor.fetchone()['cnt']
            conn.close()

            # 如果不在任何公會，設為非活躍
            if active_count == 0:
                set_appraiser_inactive(existing['id'])

        # 記錄變更
        log_change(association, 'left', name, item.get('office'), None, item)
        results['left_marked'] += 1

    # 處理換事務所
    for item in changes['office_changed']:
        name = item['name']
        existing = find_appraiser_by_name(name, association)

        if existing:
            # 結束舊的事務所記錄
            end_career_history(existing['id'], association)

            # 新增新的事務所記錄
            insert_career_history({
                'appraiser_id': existing['id'],
                'source': 'association',
                'association': association,
                'office_name': item['new_office'],
            })

            update_appraiser_last_seen(existing['id'])

        # 記錄變更
        log_change(association, 'office_changed', name,
                  item['old_office'], item['new_office'], item)
        results['office_updated'] += 1

    # 處理其他更新
    for item in changes['info_updated']:
        name = item['name']
        existing = find_appraiser_by_name(name, association)

        if existing:
            update_appraiser_last_seen(existing['id'])

            # 更新事務所資訊
            conn = get_connection()
            cursor = conn.cursor()

            updates = []
            values = []

            if item['changes'].get('address'):
                updates.append('office_address = ?')
                values.append(item['changes']['address']['new'])

            if item['changes'].get('phone'):
                updates.append('phone = ?')
                values.append(item['changes']['phone']['new'])

            if updates:
                values.extend([existing['id'], association])
                cursor.execute(f'''
                    UPDATE career_history
                    SET {', '.join(updates)}
                    WHERE appraiser_id = ? AND association = ? AND is_current = 1
                ''', values)
                conn.commit()

            conn.close()

        # 記錄變更
        log_change(association, 'info_updated', name, None, None, item)
        results['info_updated'] += 1

    return results

def process_scrape_result(association_code, new_data, association_name):
    """
    處理抓取結果：比對變更並更新資料庫

    Args:
        association_code: 公會代碼（如 'taipei'）
        new_data: 新抓取的資料
        association_name: 公會名稱（如 '台北市'）

    Returns:
        dict: 處理結果
    """
    # 取得上次快照
    last_snapshot = get_latest_snapshot(association_code, 'appraisers')

    # 計算新資料的 checksum
    new_checksum = calculate_checksum(new_data)

    result = {
        'association': association_name,
        'record_count': len(new_data),
        'has_changes': False,
        'changes': None,
        'apply_results': None,
        'warning': None,  # 新增：警告訊息
    }

    # ===== 人數驟降警報 =====
    # 如果新資料比上次少超過 50%，可能是抓取失敗
    if last_snapshot:
        old_count = last_snapshot['record_count']
        new_count = len(new_data)

        if old_count > 0 and new_count < old_count * 0.5:
            warning_msg = (
                f"⚠️ {association_name} 人數異常下降！"
                f"上次 {old_count} 人 → 本次 {new_count} 人（減少 {old_count - new_count} 人）"
                f"可能是網站故障或抓取失敗，建議人工確認。"
            )
            result['warning'] = warning_msg
            print(f"  {warning_msg}")

    if last_snapshot:
        # 比較 checksum
        if last_snapshot['checksum'] == new_checksum:
            # 無變化，不儲存快照、不更新資料庫
            result['has_changes'] = False
            return result

        # 有變化，進行詳細比對
        old_data = last_snapshot['data']
        changes = detect_changes(old_data, new_data, association_name)

        # 檢查是否有實質變更
        total_changes = (
            len(changes['new']) +
            len(changes['left']) +
            len(changes['office_changed']) +
            len(changes['info_updated'])
        )

        if total_changes > 0:
            result['has_changes'] = True
            result['changes'] = changes

            # 套用變更到資料庫
            apply_results = apply_changes(changes, association_name)
            result['apply_results'] = apply_results

            # 只在有變更時儲存新快照
            save_snapshot('association', association_code, 'appraisers', new_data)
        else:
            # checksum 不同但無實質變更（可能是格式差異）
            result['has_changes'] = False
    else:
        # 第一次抓取，儲存快照但不視為「變更」
        save_snapshot('association', association_code, 'appraisers', new_data)
        result['has_changes'] = False
        result['is_first_scrape'] = True

    return result

def get_changes_summary(changes):
    """產生變更摘要"""
    if not changes:
        return '無變更'

    parts = []
    if changes['new']:
        parts.append(f"新加入 {len(changes['new'])} 位")
    if changes['left']:
        parts.append(f"退出 {len(changes['left'])} 位")
    if changes['office_changed']:
        parts.append(f"換事務所 {len(changes['office_changed'])} 位")
    if changes['info_updated']:
        parts.append(f"資訊更新 {len(changes['info_updated'])} 位")
    if changes.get('suspected_data_fix'):
        parts.append(f"疑似資料修正 {len(changes['suspected_data_fix'])} 位")

    return '、'.join(parts) if parts else '無變更'

if __name__ == '__main__':
    # 測試用
    print('變更偵測模組')

    # 模擬測試
    old_data = [
        {'姓名': '張三', '事務所': 'A事務所', '電話': '02-1234'},
        {'姓名': '李四', '事務所': 'B事務所', '電話': '02-5678'},
        {'姓名': '王五', '事務所': 'C事務所', '電話': '02-9999'},
    ]

    new_data = [
        {'姓名': '張三', '事務所': 'A事務所', '電話': '02-1234'},  # 不變
        {'姓名': '李四', '事務所': 'D事務所', '電話': '02-5678'},  # 換事務所
        {'姓名': '趙六', '事務所': 'E事務所', '電話': '02-0000'},  # 新加入
        # 王五 退出
    ]

    changes = detect_changes(old_data, new_data, '測試公會')

    print('\n偵測結果:')
    print(f"新加入: {[c['name'] for c in changes['new']]}")
    print(f"退出: {[c['name'] for c in changes['left']]}")
    print(f"換事務所: {[c['name'] for c in changes['office_changed']]}")
    print(f"摘要: {get_changes_summary(changes)}")