"""
CSV 資料匯入模組

將現有的 CSV 檔案匯入到 SQLite 資料庫
"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import csv
import json
from pathlib import Path
from datetime import datetime

from database import (
    init_database, get_connection,
    find_appraiser_by_name, insert_appraiser, insert_career_history,
    log_membership_event, save_snapshot
)

# 專案目錄
PROJECT_DIR = Path(__file__).parent

# 公會名稱對照
ASSOCIATION_MAP = {
    'taipei': '台北市',
    'newtaipei': '新北市',
    'taoyuan': '桃園市',
    'taichung': '台中市',
    'ctreaa': '中台灣',
    'tainan': '台南市',
    'kaohsiung': '高雄市',
}

def read_csv(filename):
    """讀取 CSV 檔案"""
    filepath = PROJECT_DIR / filename
    if not filepath.exists():
        print(f'檔案不存在: {filepath}')
        return []

    # 嘗試不同編碼
    for encoding in ['utf-8-sig', 'utf-8', 'cp950']:
        try:
            with open(filepath, 'r', encoding=encoding) as f:
                reader = csv.DictReader(f)
                return list(reader)
        except UnicodeDecodeError:
            continue

    print(f'無法讀取檔案: {filepath}')
    return []

def normalize_office_name(name):
    """標準化事務所名稱"""
    if not name:
        return ''
    # 移除多餘空白
    name = ' '.join(name.split())
    # 統一「不動產估價師事務所」
    name = name.replace('不動產估價師聯合事務所', '聯合事務所')
    return name

def import_appraisers(association_code, filename):
    """匯入估價師名單"""
    data = read_csv(filename)
    if not data:
        return 0

    association = ASSOCIATION_MAP.get(association_code, association_code)
    today = datetime.now().strftime('%Y-%m-%d')
    imported = 0
    skipped = 0

    print(f'\n匯入 {association} 估價師...')

    for row in data:
        # 取得姓名（不同公會欄位名稱不同）
        name = row.get('姓名', '').strip()
        if not name:
            continue

        # 檢查是否已存在
        existing = find_appraiser_by_name(name, association)
        if existing:
            skipped += 1
            continue

        # 取得事務所資訊（欄位名稱因公會而異）
        office_name = (
            row.get('事務所') or
            row.get('事務所名稱') or
            ''
        ).strip()

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

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

        fax = row.get('傳真', '').strip()
        email = row.get('Email', '').strip()
        website = row.get('網址', '').strip()

        # 證書字號（部分公會有）
        license_number = row.get('證書字號', '').strip()

        # 新增估價師
        appraiser_id = insert_appraiser({
            'name': name,
            'license_number': license_number if license_number else None,
            'identity_confidence': 'high' if license_number else 'medium',
        })

        # 新增事務所記錄
        insert_career_history({
            'appraiser_id': appraiser_id,
            'source': 'association',
            'association': association,
            'office_name': normalize_office_name(office_name),
            'office_address': office_address,
            'phone': phone,
            'fax': fax,
            'email': email,
            'website': website,
        })

        # 記錄入會
        log_membership_event(appraiser_id, association, 'joined', '初始匯入')

        imported += 1

    # 儲存快照
    save_snapshot('association', association_code, 'appraisers', data)

    print(f'  新增: {imported}, 略過: {skipped}')
    return imported

def import_assistants(association_code, filename):
    """匯入助理員名單"""
    data = read_csv(filename)
    if not data:
        return 0

    association = ASSOCIATION_MAP.get(association_code, association_code)
    today = datetime.now().strftime('%Y-%m-%d')
    imported = 0

    print(f'\n匯入 {association} 助理員...')

    conn = get_connection()
    cursor = conn.cursor()

    for row in data:
        name = row.get('姓名', '').strip()
        if not name:
            continue

        office_name = row.get('事務所', '').strip()

        cursor.execute('''
            INSERT INTO assistants (name, association, office_name, first_seen_date, last_seen_date)
            VALUES (?, ?, ?, ?, ?)
        ''', (name, association, office_name, today, today))

        imported += 1

    conn.commit()
    conn.close()

    # 儲存快照
    save_snapshot('association', association_code, 'assistants', data)

    print(f'  新增: {imported}')
    return imported

def import_supervisors(association_code, filename):
    """匯入理監事名單"""
    data = read_csv(filename)
    if not data:
        return 0

    association = ASSOCIATION_MAP.get(association_code, association_code)
    imported = 0

    print(f'\n匯入 {association} 理監事...')

    conn = get_connection()
    cursor = conn.cursor()

    for row in data:
        name = row.get('姓名', '').strip()
        if not name:
            continue

        position = row.get('職稱', '').strip()
        term = row.get('屆別', '').strip()
        english_name = row.get('英文名', '').strip()

        # 嘗試關聯到估價師
        appraiser = find_appraiser_by_name(name, association)
        appraiser_id = appraiser['id'] if appraiser else None

        cursor.execute('''
            INSERT INTO supervisors (
                appraiser_id, name, english_name, association, position, term, is_current
            ) VALUES (?, ?, ?, ?, ?, ?, 1)
        ''', (appraiser_id, name, english_name, association, position, term))

        imported += 1

    conn.commit()
    conn.close()

    # 儲存快照
    save_snapshot('association', association_code, 'supervisors', data)

    print(f'  新增: {imported}')
    return imported

def import_all():
    """匯入所有資料"""
    init_database()

    total_appraisers = 0
    total_assistants = 0
    total_supervisors = 0

    # 匯入各公會估價師
    appraiser_files = [
        ('taipei', 'taipei_appraisers.csv'),
        ('newtaipei', 'newtaipei_appraisers.csv'),
        ('taoyuan', 'taoyuan_appraisers.csv'),
        ('taichung', 'taichung_appraisers.csv'),
        ('tainan', 'tainan_appraisers.csv'),
        ('kaohsiung', 'kaohsiung_appraisers.csv'),
    ]

    for code, filename in appraiser_files:
        count = import_appraisers(code, filename)
        total_appraisers += count

    # 匯入助理員（目前只有台北市）
    assistant_files = [
        ('taipei', 'taipei_assistants.csv'),
    ]

    for code, filename in assistant_files:
        count = import_assistants(code, filename)
        total_assistants += count

    # 匯入理監事
    supervisor_files = [
        ('taipei', 'taipei_supervisors.csv'),
        ('taichung', 'taichung_supervisors.csv'),
        ('ctreaa', 'ctreaa_supervisors.csv'),
    ]

    for code, filename in supervisor_files:
        count = import_supervisors(code, filename)
        total_supervisors += count

    print('\n' + '=' * 50)
    print('匯入完成!')
    print(f'  估價師: {total_appraisers}')
    print(f'  助理員: {total_assistants}')
    print(f'  理監事: {total_supervisors}')
    print('=' * 50)

    return {
        'appraisers': total_appraisers,
        'assistants': total_assistants,
        'supervisors': total_supervisors,
    }

if __name__ == '__main__':
    import argparse

    parser = argparse.ArgumentParser(description='匯入 CSV 資料')
    parser.add_argument('--all', action='store_true', help='匯入所有資料')
    parser.add_argument('--appraisers', metavar='CODE', help='匯入指定公會估價師')
    parser.add_argument('--assistants', metavar='CODE', help='匯入指定公會助理員')
    parser.add_argument('--supervisors', metavar='CODE', help='匯入指定公會理監事')

    args = parser.parse_args()

    if args.all:
        import_all()
    elif args.appraisers:
        init_database()
        import_appraisers(args.appraisers, f'{args.appraisers}_appraisers.csv')
    elif args.assistants:
        init_database()
        import_assistants(args.assistants, f'{args.assistants}_assistants.csv')
    elif args.supervisors:
        init_database()
        import_supervisors(args.supervisors, f'{args.supervisors}_supervisors.csv')
    else:
        parser.print_help()