"""
台灣不動產估價師 CRM 資料庫模組

根據 SPEC.md v0.3 設計
"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
import json
import hashlib
from datetime import datetime
from pathlib import Path

# 資料庫路徑
DB_PATH = Path(__file__).parent / 'data' / 'appraisers.db'

def get_connection():
    """取得資料庫連線"""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_database():
    """初始化資料庫 schema"""
    conn = get_connection()
    cursor = conn.cursor()

    # ===== 估價師主檔 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS appraisers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            gender TEXT,
            license_number TEXT,
            license_expiry DATE,
            first_seen_date DATE,
            last_seen_date DATE,
            status TEXT DEFAULT 'active',  -- active / inactive / unknown

            -- 識別欄位
            identity_confidence TEXT DEFAULT 'medium',  -- high / medium / low / manual / inferred
            needs_review INTEGER DEFAULT 0,

            -- CRM 欄位
            relationship TEXT,  -- 同業 / 競爭者 / 合作夥伴 / 客戶
            specialty TEXT,
            notes TEXT,
            tags TEXT,  -- JSON array

            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # ===== 事務所異動歷史 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS career_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            appraiser_id INTEGER NOT NULL,
            source TEXT,  -- association / gov_data
            association TEXT,  -- 所屬公會
            office_name TEXT,
            office_address TEXT,
            phone TEXT,
            fax TEXT,
            email TEXT,
            website TEXT,
            start_date DATE,
            end_date DATE,
            is_current INTEGER DEFAULT 1,

            FOREIGN KEY (appraiser_id) REFERENCES appraisers(id)
        )
    ''')

    # ===== 會籍異動記錄 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS membership_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            appraiser_id INTEGER NOT NULL,
            association TEXT NOT NULL,
            event_type TEXT NOT NULL,  -- joined / left / rejoined
            event_date DATE,
            detected_date DATE,
            notes TEXT,

            FOREIGN KEY (appraiser_id) REFERENCES appraisers(id)
        )
    ''')

    # ===== 估價助理員 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS assistants (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            association TEXT,
            office_name TEXT,
            first_seen_date DATE,
            last_seen_date DATE,
            status TEXT DEFAULT 'active'
        )
    ''')

    # ===== 理監事 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS supervisors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            appraiser_id INTEGER,
            name TEXT NOT NULL,
            english_name TEXT,
            association TEXT,
            position TEXT,  -- 理事長 / 理事 / 監事 等
            office_name TEXT,  -- 事務所名稱
            education TEXT,  -- 學歷
            licenses TEXT,  -- 證照（逗號分隔）
            term TEXT,  -- 屆別
            start_date DATE,
            end_date DATE,
            is_current INTEGER DEFAULT 1,

            FOREIGN KEY (appraiser_id) REFERENCES appraisers(id)
        )
    ''')

    # 新增欄位（如果舊表沒有）
    try:
        cursor.execute('ALTER TABLE supervisors ADD COLUMN office_name TEXT')
    except:
        pass
    try:
        cursor.execute('ALTER TABLE supervisors ADD COLUMN education TEXT')
    except:
        pass
    try:
        cursor.execute('ALTER TABLE supervisors ADD COLUMN licenses TEXT')
    except:
        pass

    # ===== 抓取快照 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS scrape_snapshots (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            scrape_date DATE NOT NULL,
            source TEXT NOT NULL,  -- association / gov_data
            source_name TEXT NOT NULL,  -- taipei / taichung / ...
            data_type TEXT NOT NULL,  -- appraisers / assistants / supervisors
            record_count INTEGER,
            checksum TEXT,  -- 內容 hash
            raw_json TEXT,  -- 完整原始資料

            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # ===== 變更記錄 =====
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS change_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            detected_date DATE NOT NULL,
            association TEXT,
            change_type TEXT NOT NULL,  -- new / left / office_changed / info_updated
            appraiser_name TEXT,
            old_value TEXT,
            new_value TEXT,
            details TEXT,  -- JSON

            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # ===== 建立索引 =====
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_appraisers_name ON appraisers(name)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_appraisers_license ON appraisers(license_number)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_career_appraiser ON career_history(appraiser_id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_career_current ON career_history(is_current)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_membership_appraiser ON membership_log(appraiser_id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_snapshots_date ON scrape_snapshots(scrape_date)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_change_date ON change_log(detected_date)')

    conn.commit()
    conn.close()
    print(f'資料庫已初始化: {DB_PATH}')

def calculate_checksum(data):
    """計算資料的 checksum"""
    json_str = json.dumps(data, sort_keys=True, ensure_ascii=False)
    return hashlib.md5(json_str.encode('utf-8')).hexdigest()

# ===== 估價師操作 =====

def find_appraiser_by_name(name, association=None):
    """根據姓名查找估價師"""
    conn = get_connection()
    cursor = conn.cursor()

    if association:
        cursor.execute('''
            SELECT a.* FROM appraisers a
            JOIN career_history ch ON a.id = ch.appraiser_id
            WHERE a.name = ? AND ch.association = ? AND ch.is_current = 1
        ''', (name, association))
    else:
        cursor.execute('SELECT * FROM appraisers WHERE name = ?', (name,))

    result = cursor.fetchone()
    conn.close()
    return dict(result) if result else None

def find_appraiser_by_license(license_number):
    """根據證書字號查找估價師"""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM appraisers WHERE license_number = ?', (license_number,))
    result = cursor.fetchone()
    conn.close()
    return dict(result) if result else None

def insert_appraiser(data):
    """新增估價師"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')

    cursor.execute('''
        INSERT INTO appraisers (
            name, gender, license_number, license_expiry,
            first_seen_date, last_seen_date, status,
            identity_confidence, needs_review
        ) VALUES (?, ?, ?, ?, ?, ?, 'active', ?, ?)
    ''', (
        data.get('name'),
        data.get('gender'),
        data.get('license_number'),
        data.get('license_expiry'),
        today,
        today,
        data.get('identity_confidence', 'medium'),
        data.get('needs_review', 0)
    ))

    appraiser_id = cursor.lastrowid
    conn.commit()
    conn.close()
    return appraiser_id

def update_appraiser_last_seen(appraiser_id, date=None):
    """更新最後出現日期"""
    conn = get_connection()
    cursor = conn.cursor()

    if date is None:
        date = datetime.now().strftime('%Y-%m-%d')

    cursor.execute('''
        UPDATE appraisers
        SET last_seen_date = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    ''', (date, appraiser_id))

    conn.commit()
    conn.close()

def set_appraiser_inactive(appraiser_id):
    """設定估價師為非活躍狀態"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        UPDATE appraisers
        SET status = 'inactive', updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    ''', (appraiser_id,))

    conn.commit()
    conn.close()

# ===== 事務所歷史操作 =====

def insert_career_history(data):
    """新增事務所歷史記錄"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')

    cursor.execute('''
        INSERT INTO career_history (
            appraiser_id, source, association, office_name, office_address,
            phone, fax, email, website, start_date, is_current
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
    ''', (
        data['appraiser_id'],
        data.get('source', 'association'),
        data.get('association'),
        data.get('office_name'),
        data.get('office_address'),
        data.get('phone'),
        data.get('fax'),
        data.get('email'),
        data.get('website'),
        today
    ))

    conn.commit()
    conn.close()

def end_career_history(appraiser_id, association):
    """結束目前的事務所記錄"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')

    cursor.execute('''
        UPDATE career_history
        SET end_date = ?, is_current = 0
        WHERE appraiser_id = ? AND association = ? AND is_current = 1
    ''', (today, appraiser_id, association))

    conn.commit()
    conn.close()

def get_current_career(appraiser_id):
    """取得估價師目前的事務所"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT * FROM career_history
        WHERE appraiser_id = ? AND is_current = 1
    ''', (appraiser_id,))

    results = cursor.fetchall()
    conn.close()
    return [dict(r) for r in results]

# ===== 會籍記錄操作 =====

def log_membership_event(appraiser_id, association, event_type, notes=None):
    """記錄會籍異動"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')

    cursor.execute('''
        INSERT INTO membership_log (
            appraiser_id, association, event_type, event_date, detected_date, notes
        ) VALUES (?, ?, ?, ?, ?, ?)
    ''', (appraiser_id, association, event_type, today, today, notes))

    conn.commit()
    conn.close()

# ===== 快照操作 =====

def save_snapshot(source, source_name, data_type, data):
    """儲存抓取快照"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')
    checksum = calculate_checksum(data)
    raw_json = json.dumps(data, ensure_ascii=False)

    cursor.execute('''
        INSERT INTO scrape_snapshots (
            scrape_date, source, source_name, data_type, record_count, checksum, raw_json
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', (today, source, source_name, data_type, len(data), checksum, raw_json))

    conn.commit()
    conn.close()

def get_latest_snapshot(source_name, data_type):
    """取得最新的快照"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT * FROM scrape_snapshots
        WHERE source_name = ? AND data_type = ?
        ORDER BY scrape_date DESC, id DESC
        LIMIT 1
    ''', (source_name, data_type))

    result = cursor.fetchone()
    conn.close()

    if result:
        snapshot = dict(result)
        snapshot['data'] = json.loads(snapshot['raw_json'])
        return snapshot
    return None

# ===== 變更記錄操作 =====

def log_change(association, change_type, appraiser_name, old_value=None, new_value=None, details=None):
    """記錄變更"""
    conn = get_connection()
    cursor = conn.cursor()

    today = datetime.now().strftime('%Y-%m-%d')
    details_json = json.dumps(details, ensure_ascii=False) if details else None

    cursor.execute('''
        INSERT INTO change_log (
            detected_date, association, change_type, appraiser_name,
            old_value, new_value, details
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', (today, association, change_type, appraiser_name, old_value, new_value, details_json))

    conn.commit()
    conn.close()

def get_changes_by_date(date):
    """取得指定日期的變更"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT * FROM change_log WHERE detected_date = ?
        ORDER BY association, change_type
    ''', (date,))

    results = cursor.fetchall()
    conn.close()
    return [dict(r) for r in results]

def cleanup_old_snapshots(days=30):
    """清理超過指定天數的舊快照"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        DELETE FROM scrape_snapshots
        WHERE scrape_date < date('now', ? || ' days')
    ''', (f'-{days}',))

    deleted = cursor.rowcount
    conn.commit()
    conn.close()

    if deleted > 0:
        print(f'已清理 {deleted} 筆超過 {days} 天的舊快照')

    return deleted

def get_snapshot_stats():
    """取得快照統計"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT
            COUNT(*) as total,
            MIN(scrape_date) as oldest,
            MAX(scrape_date) as newest,
            SUM(LENGTH(raw_json)) as total_size
        FROM scrape_snapshots
    ''')

    result = cursor.fetchone()
    conn.close()

    return {
        'total': result['total'],
        'oldest': result['oldest'],
        'newest': result['newest'],
        'total_size_kb': round(result['total_size'] / 1024, 1) if result['total_size'] else 0,
    }

# ===== 統計查詢 =====

def get_appraiser_count_by_association():
    """取得各公會估價師人數"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT ch.association, COUNT(DISTINCT ch.appraiser_id) as count
        FROM career_history ch
        JOIN appraisers a ON ch.appraiser_id = a.id
        WHERE ch.is_current = 1 AND a.status = 'active'
        GROUP BY ch.association
        ORDER BY count DESC
    ''')

    results = cursor.fetchall()
    conn.close()
    return {r['association']: r['count'] for r in results}

def get_total_appraisers():
    """取得估價師總數"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('SELECT COUNT(*) as count FROM appraisers WHERE status = "active"')
    result = cursor.fetchone()
    conn.close()
    return result['count']

def get_all_appraisers():
    """取得所有估價師（含事務所資訊）"""
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT a.*, ch.association, ch.office_name, ch.office_address, ch.phone
        FROM appraisers a
        LEFT JOIN career_history ch ON a.id = ch.appraiser_id AND ch.is_current = 1
        WHERE a.status = 'active'
        ORDER BY ch.association, a.name
    ''')

    results = cursor.fetchall()
    conn.close()
    return [dict(r) for r in results]

# ===== CLI 介面 =====

if __name__ == '__main__':
    import argparse

    parser = argparse.ArgumentParser(description='估價師資料庫管理')
    parser.add_argument('command', choices=['init', 'stats', 'list'], help='指令')

    args = parser.parse_args()

    if args.command == 'init':
        init_database()
    elif args.command == 'stats':
        init_database()  # 確保資料庫存在
        total = get_total_appraisers()
        by_assoc = get_appraiser_count_by_association()
        print(f'\n估價師總數: {total}')
        print('\n各公會人數:')
        for assoc, count in by_assoc.items():
            print(f'  {assoc}: {count}')
    elif args.command == 'list':
        init_database()
        appraisers = get_all_appraisers()
        for a in appraisers[:10]:  # 只顯示前 10 筆
            print(f"{a['name']} - {a.get('association', '?')} - {a.get('office_name', '?')}")
        if len(appraisers) > 10:
            print(f'... 共 {len(appraisers)} 筆')