#!/usr/bin/env python3
"""
Re-import IC13 TXT files with content comparison:
Only append if content after normalization actually differs.
"""
import os, sys, sqlite3, datetime, re

DB = '/root/originals.db'
IC13_DIR = '/tmp/ic13_copy_new/'
TODAY = datetime.date.today().strftime('%Y-%m-%d')

TRAILING_CLEAN = re.compile(r'[，、。；：]+$')

def normalize(text):
    if not text:
        return ''
    t = text.strip()
    t = re.sub(r'\s+', ' ', t)
    return t

def read_txt(filepath):
    for enc in ('gbk', 'gb18030', 'utf-8'):
        try:
            with open(filepath, 'r', encoding=enc) as f:
                return f.read()
        except (UnicodeDecodeError, UnicodeError):
            continue
    with open(filepath, 'r', encoding='latin-1') as f:
        return f.read()

def clean_title(title):
    title = title.strip()
    title = TRAILING_CLEAN.sub('', title)
    return title.strip()

def truncate_at_project(title):
    """当标题过长（正文混入），在最后一个项目处截断"""
    if len(title) <= 200:
        return title
    idx = title.rfind('项目')
    if idx > 0:
        return title[:idx + 2]  # +2 for 项目
    return title

def extract_title(text, fallback_id):
    if not text:
        return fallback_id
    lines = text.split('\n')
    for line in lines:
        line = line.strip()
        if not line or line == '目录':
            continue
        if '项目' in line:
            m = re.match(r'^项目名称[：:]\s*(.+)$', line)
            if m:
                return clean_title(truncate_at_project(m.group(1)))
            return clean_title(truncate_at_project(line))
    for line in lines:
        line = line.strip()
        if line and line != '目录':
            return clean_title(truncate_at_project(line))
    return fallback_id

def main():
    if not os.path.isdir(IC13_DIR):
        print(f'Directory {IC13_DIR} not found. Nothing to do.')
        return
    
    conn = sqlite3.connect(DB)
    conn.execute('PRAGMA journal_mode=OFF')
    conn.execute('PRAGMA synchronous=OFF')
    c = conn.cursor()
    
    existing = {}
    for row in c.execute(
        "SELECT id_code, content FROM tsk_data WHERE source='pharma' AND id_code IS NOT NULL AND id_code != ''"
    ):
        existing[row[0].strip()] = row[1] if row[1] else ''
    
    max_id = c.execute("SELECT MAX(id) FROM tsk_data").fetchone()[0] or 0
    
    files = sorted([f for f in os.listdir(IC13_DIR) if f.endswith('.txt')])
    print(f'Existing pharma id_codes: {len(existing)}')
    print(f'IC13 files: {len(files)}')
    
    new_count = update_count = skip_count = error_count = 0
    updates = []
    inserts = []
    
    for fname in files:
        id_code = fname.replace('.txt', '')
        filepath = os.path.join(IC13_DIR, fname)
        
        try:
            text = read_txt(filepath)
        except Exception as e:
            print(f'  ERROR reading {fname}: {e}')
            error_count += 1
            continue
        
        # Split: first line is title candidate, rest is content
        parts = text.split('\n', 1)
        content_new = parts[1].strip() if len(parts) > 1 else ''
        title = extract_title(text, id_code)
        
        if id_code in existing:
            old_content = existing[id_code]
            old_norm = normalize(old_content)
            new_norm = normalize(content_new)
            
            if old_norm == new_norm:
                skip_count += 1
                continue
            
            SEPARATOR = f'\n\n---\n更新日期: {TODAY}\n---\n\n'
            if old_content:
                content_new = old_content + SEPARATOR + content_new
            updates.append((title, content_new, id_code))
            update_count += 1
        else:
            max_id += 1
            inserts.append((max_id, id_code, title, content_new))
            new_count += 1
    
    print(f'\nProcessing...')
    if updates:
        c.executemany(
            "UPDATE tsk_data SET title=?, content=? WHERE id_code=? AND source='pharma'",
            updates
        )
        print(f'  Updates: {update_count}')
    if inserts:
        c.executemany(
            "INSERT INTO tsk_data (id, source, id_code, title, content) VALUES (?, 'pharma', ?, ?, ?)",
            inserts
        )
        print(f'  New inserts: {new_count}')
    
    conn.commit()
    total = c.execute("SELECT COUNT(*) FROM tsk_data WHERE source='pharma'").fetchone()[0]
    conn.close()
    
    print(f'Skipped (no change): {skip_count}')
    print(f'  Errors: {error_count}')
    print(f'  Total pharma records: {total}')

if __name__ == '__main__':
    main()