o
    ]iB                  
   @   s@  d Z ddlZejjdd ddlZddlZddlZddlmZ ddlm	Z	 e	e
jd d Zd	d
 Zdd Zdd ZdJddZdd Zdd ZdJddZdd Zdd Zdd Zdd ZdJdd Zd!d" Zd#d$ ZdKd%d&Zd'd( ZdLd*d+Zd,d- Zd.d/ Zd0d1 Z d2d3 Z!e"d4krddl#Z#e#j$d5d6Z%e%j&d7g d8d9d: e%' Z(e(j)d;kre  dS e(j)d<kre  e  Z*e Z+e,d=e*  e,d> e+- D ]\Z.Z/e,d?e. d@e/  qdS e(j)dAkre  e! Z0e0ddB D ]Z1e,e1dC  dDe12dEdF dDe12dGdF  qe3e0dBkre,dHe3e0 dI dS dS dS dS )MuJ   
台灣不動產估價師 CRM 資料庫模組

根據 SPEC.md v0.3 設計
    Nutf-8)encoding)datetime)Pathdatazappraisers.dbc                  C   s&   t jjddd tt } tj| _| S )u   取得資料庫連線T)parentsexist_ok)DB_PATHparentmkdirsqlite3ZconnectZRowZrow_factory)conn r   ZC:\Users\User\Documents\GitHub\Research_zoo\projects\taiwan-appraiser-registry\database.pyget_connection   s   
r   c                  C   s  t  } |  }|d |d |d |d |d z|d W n   Y z|d W n   Y z|d W n   Y |d	 |d
 |d |d |d |d |d |d |d |   |   tdt  dS )u   初始化資料庫 schemau^  
        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
        )
    uF  
        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)
        )
    a  
        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)
        )
    a9  
        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'
        )
    u  
        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)
        )
    z3ALTER TABLE supervisors ADD COLUMN office_name TEXTz1ALTER TABLE supervisors ADD COLUMN education TEXTz0ALTER TABLE supervisors ADD COLUMN licenses TEXTu'  
        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
        )
    a  
        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
        )
    zBCREATE INDEX IF NOT EXISTS idx_appraisers_name ON appraisers(name)zOCREATE INDEX IF NOT EXISTS idx_appraisers_license ON appraisers(license_number)zOCREATE INDEX IF NOT EXISTS idx_career_appraiser ON career_history(appraiser_id)zKCREATE INDEX IF NOT EXISTS idx_career_current ON career_history(is_current)zSCREATE INDEX IF NOT EXISTS idx_membership_appraiser ON membership_log(appraiser_id)zNCREATE INDEX IF NOT EXISTS idx_snapshots_date ON scrape_snapshots(scrape_date)zGCREATE INDEX IF NOT EXISTS idx_change_date ON change_log(detected_date)u   資料庫已初始化: N)r   cursorexecutecommitcloseprintr	   )r   r   r   r   r   init_database   s>   













r   c                 C   s$   t j| ddd}t|d S )u   計算資料的 checksumTF)	sort_keysensure_asciir   )jsondumpshashlibZmd5encodeZ	hexdigest)r   Zjson_strr   r   r   calculate_checksum   s   r   c                 C   sR   t  }| }|r|d| |f n|d| f | }|  |r't|S dS )u   根據姓名查找估價師z
            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
        z'SELECT * FROM appraisers WHERE name = ?Nr   r   r   fetchoner   dict)nameassociationr   r   resultr   r   r   find_appraiser_by_name   s   r$   c                 C   s<   t  }| }|d| f | }|  |rt|S dS )u!   根據證書字號查找估價師z1SELECT * FROM appraisers WHERE license_number = ?Nr   )license_numberr   r   r#   r   r   r   find_appraiser_by_license   s   r&   c                 C   sz   t  }| }t d}|d| d| d| d| d||| dd| d	d
f |j}|  |	  |S )u   新增估價師%Y-%m-%dz
        INSERT INTO appraisers (
            name, gender, license_number, license_expiry,
            first_seen_date, last_seen_date, status,
            identity_confidence, needs_review
        ) VALUES (?, ?, ?, ?, ?, ?, 'active', ?, ?)
    r!   Zgenderr%   Zlicense_expiryZidentity_confidenceZmediumZneeds_reviewr   )
r   r   r   nowstrftimer   getZ	lastrowidr   r   )r   r   r   todayappraiser_idr   r   r   insert_appraiser   s$   

r-   c                 C   sH   t  }| }|du rt d}|d|| f |  |  dS )u   更新最後出現日期Nr'   zs
        UPDATE appraisers
        SET last_seen_date = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    r   r   r   r(   r)   r   r   r   )r,   dater   r   r   r   r   update_appraiser_last_seen   s   r0   c                 C   s0   t  }| }|d| f |  |  dS )u!   設定估價師為非活躍狀態zt
        UPDATE appraisers
        SET status = 'inactive', updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    N)r   r   r   r   r   )r,   r   r   r   r   r   set_appraiser_inactive
  s   r1   c                 C   s   t  }| }t d}|d| d | dd| d| d| d| d| d	| d
| d|f
 |  |  dS )u   新增事務所歷史記錄r'   z
        INSERT INTO career_history (
            appraiser_id, source, association, office_name, office_address,
            phone, fax, email, website, start_date, is_current
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
    r,   sourcer"   office_nameoffice_addressphoneZfaxZemailZwebsiteN)	r   r   r   r(   r)   r   r*   r   r   )r   r   r   r+   r   r   r   insert_career_history  s$   
r6   c                 C   sB   t  }| }t d}|d|| |f |  |  dS )u   結束目前的事務所記錄r'   z
        UPDATE career_history
        SET end_date = ?, is_current = 0
        WHERE appraiser_id = ? AND association = ? AND is_current = 1
    Nr.   )r,   r"   r   r   r+   r   r   r   end_career_history6  s   r7   c                 C   :   t  }| }|d| f | }|  dd |D S )u!   取得估價師目前的事務所z\
        SELECT * FROM career_history
        WHERE appraiser_id = ? AND is_current = 1
    c                 S      g | ]}t |qS r   r    .0rr   r   r   
<listcomp>R      z&get_current_career.<locals>.<listcomp>r   r   r   fetchallr   )r,   r   r   resultsr   r   r   get_current_careerF     rC   c              	   C   sH   t  }| }t d}|d| |||||f |  |  dS )u   記錄會籍異動r'   z
        INSERT INTO membership_log (
            appraiser_id, association, event_type, event_date, detected_date, notes
        ) VALUES (?, ?, ?, ?, ?, ?)
    Nr.   )r,   r"   Z
event_typeZnotesr   r   r+   r   r   r   log_membership_eventV  s   rE   c           	   
   C   sd   t  }| }t d}t|}tj|dd}|d|| ||t	|||f |
  |  dS )u   儲存抓取快照r'   Fr   z
        INSERT INTO scrape_snapshots (
            scrape_date, source, source_name, data_type, record_count, checksum, raw_json
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    N)r   r   r   r(   r)   r   r   r   r   lenr   r   )	r2   source_name	data_typer   r   r   r+   Zchecksumraw_jsonr   r   r   save_snapshoth  s   rK   c                 C   sT   t  }| }|d| |f | }|  |r(t|}t|d |d< |S dS )u   取得最新的快照z
        SELECT * FROM scrape_snapshots
        WHERE source_name = ? AND data_type = ?
        ORDER BY scrape_date DESC, id DESC
        LIMIT 1
    rJ   r   N)r   r   r   r   r   r    r   loads)rH   rI   r   r   r#   Zsnapshotr   r   r   get_latest_snapshotz  s   rM   c           
   
   C   s`   t  }| }t d}|rtj|ddnd}	|d|| |||||	f |  |	  dS )u   記錄變更r'   FrF   Nz
        INSERT INTO change_log (
            detected_date, association, change_type, appraiser_name,
            old_value, new_value, details
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
    )
r   r   r   r(   r)   r   r   r   r   r   )
r"   change_typeappraiser_name	old_value	new_valueZdetailsr   r   r+   Zdetails_jsonr   r   r   
log_change  s   rR   c                 C   r8   )u   取得指定日期的變更zh
        SELECT * FROM change_log WHERE detected_date = ?
        ORDER BY association, change_type
    c                 S   r9   r   r:   r;   r   r   r   r>     r?   z'get_changes_by_date.<locals>.<listcomp>r@   )r/   r   r   rB   r   r   r   get_changes_by_date  rD   rS      c                 C   sZ   t  }| }|dd|  f |j}|  |  |dkr+td| d|  d |S )u$   清理超過指定天數的舊快照z`
        DELETE FROM scrape_snapshots
        WHERE scrape_date < date('now', ? || ' days')
    -r   u
   已清理 u    筆超過 u    天的舊快照)r   r   r   Zrowcountr   r   r   )daysr   r   Zdeletedr   r   r   cleanup_old_snapshots  s   
rW   c                  C   s`   t  } |  }|d | }|   |d |d |d |d r,t|d d dd	S dd	S )
u   取得快照統計z
        SELECT
            COUNT(*) as total,
            MIN(scrape_date) as oldest,
            MAX(scrape_date) as newest,
            SUM(LENGTH(raw_json)) as total_size
        FROM scrape_snapshots
    totaloldestnewestZ
total_sizei      r   )rX   rY   rZ   Ztotal_size_kb)r   r   r   r   r   roundr   r   r#   r   r   r   get_snapshot_stats  s   
	r^   c                  C   6   t  } |  }|d | }|   dd |D S )u   取得各公會估價師人數a  
        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
    c                 S   s   i | ]	}|d  |d qS )r"   countr   r;   r   r   r   
<dictcomp>  s    z6get_appraiser_count_by_association.<locals>.<dictcomp>r@   r   r   rB   r   r   r   "get_appraiser_count_by_association  s   
	rc   c                  C   s0   t  } |  }|d | }|   |d S )u   取得估價師總數z@SELECT COUNT(*) as count FROM appraisers WHERE status = "active"r`   )r   r   r   r   r   r]   r   r   r   get_total_appraisers  s   
rd   c                  C   r_   )u-   取得所有估價師（含事務所資訊）a  
        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
    c                 S   r9   r   r:   r;   r   r   r   r>   	  r?   z&get_all_appraisers.<locals>.<listcomp>r@   rb   r   r   r   get_all_appraisers  s   
re   __main__u   估價師資料庫管理)descriptioncommand)initstatslistu   指令)choiceshelpri   rj   u   
估價師總數: u   
各公會人數:z  z: rk   
   r!   z - r"   ?r3   u   ... 共 u    筆)N)NNN)rT   )4__doc__sysstdoutreconfigurer   r   r   r   pathlibr   __file__r
   r	   r   r   r   r$   r&   r-   r0   r1   r6   r7   rC   rE   rK   rM   rR   rS   rW   r^   rc   rd   re   __name__argparseArgumentParserparseradd_argument
parse_argsargsrh   rX   by_assocr   itemsassocr`   
appraisersar*   rG   r   r   r   r   <module>   sr     "
	








.