#!/usr/bin/env python3
"""
Originals sync with mtime-based incremental detection.
Uses SQL temp-file approach for all server-side DB operations.
Usage: python3 originals_sync.py              # Full sync
       python3 originals_sync.py <offset>      # From offset
       python3 originals_sync.py <offset> <limit>
"""
import os, re, sys, json, subprocess, time

ORIGINALS_DIR = "/Users/chniir0000outlook.com/Library/CloudStorage/OneDrive-个人/originals note"
SSH_TARGET = "hw-backup"
DB = "/root/originals.db"

# ── helpers ────────────────────────────────────────────────

def run_sql_on_server(sql, timeout=60):
    """Write SQL to temp file, scp to server, execute."""
    local_tmp = "/tmp/originals_sql_temp.sql"
    with open(local_tmp, 'w', encoding='utf-8') as f:
        f.write(sql)
    subprocess.run(f"scp {local_tmp} {SSH_TARGET}:/tmp/osql.sql", shell=True, capture_output=True, timeout=15)
    r = subprocess.run(
        f"ssh {SSH_TARGET} \"sqlite3 {DB} < /tmp/osql.sql\"",
        shell=True, capture_output=True, text=True, timeout=timeout
    )
    os.remove(local_tmp)
    subprocess.run(f"ssh {SSH_TARGET} \"rm -f /tmp/osql.sql\"", shell=True, capture_output=True, timeout=10)
    return r

def query_server(sql, timeout=30):
    """Run SELECT query and return stdout (raw text)."""
    local_tmp = "/tmp/originals_query_temp.sql"
    with open(local_tmp, 'w', encoding='utf-8') as f:
        f.write(sql)
    subprocess.run(f"scp {local_tmp} {SSH_TARGET}:/tmp/oq.sql", shell=True, capture_output=True, timeout=15)
    r = subprocess.run(
        f"ssh {SSH_TARGET} \"sqlite3 -separator '|' {DB} < /tmp/oq.sql\"",
        shell=True, capture_output=True, text=True, timeout=timeout
    )
    os.remove(local_tmp)
    subprocess.run(f"ssh {SSH_TARGET} \"rm -f /tmp/oq.sql\"", shell=True, capture_output=True, timeout=10)
    return r

def classify(filename):
    name = filename.replace('.txt', '')
    m7 = re.match(r'^(\d{7})(?:\s+(.*))?$', name)
    if m7: return m7.group(1), 'plant'
    m9 = re.match(r'^(\d{9})(?:\s+(.*))?$', name)
    if m9: return m9.group(1), 'cpi'
    md = re.match(r'^(\d+)-', name)
    if md:
        code = md.group(1)
        return code, 'cpi' if len(code) == 9 else ('plant' if len(code) == 7 else 'pharma')
    return '', 'pharma'

def extract_title(filename, content):
    name = filename.replace('.txt', '')
    
    # Common tab-based extraction for all files with 3+ tab parts
    # 格式: [ID]\t[混排标题]\t[中文项目名称] 或更多字段
    if content:
        first_line = content.split('\n')[0].split('\r')[0]
        parts = first_line.split('\t')
        if len(parts) >= 3:
            p2 = parts[2].split('  ')[0].strip()
            # Truncate at ______ markers to get clean project name
            p2_clean = re.split(r'_{3,}', p2)[0].strip() or p2
            
            # Method 1: Clean project name directly in part[2] (truncated at ______)
            # Company name must appear at start; skip if starts with article openers
            if not any(p2_clean.startswith(w) for w in ['者', '记', '我', '今', '近', '昨', '项', '本', '从', '位', '工', '随', '来']):
                starts_with_company = re.match(r'^[\u4e00-\u9fff]+?(?:有限公司?|有限责任公司|化工)', p2_clean[:25])
                starts_with_company = starts_with_company or re.match(r'^[\u4e00-\u9fff]+?(?:公司|化工)', p2_clean[:20])
                if p2_clean and len(p2_clean) > 10 and ord(p2_clean[0]) > 0x7f \
                   and (p2_clean.endswith('项目') or starts_with_company) \
                   and any(kw in p2_clean for kw in ['项目', '装置', '扩建', '技改', '年产', '工程']):
                    return p2_clean
            
            # Method 2: Continuous text with ______ markers
            if len(p2) > 200 and '______________' in p2:
                between = re.findall(r'_{3,}(.*?)(?:_{3,}|\(推荐\))', p2)
                for b in between:
                    b = b.strip()
                    if b.endswith('项目') and len(b) > 10 \
                       and re.match(r'^[\u4e00-\u9fff]+?(?:有限公司?|有限责任公司|化工)', b[:30]):
                        return b
            
            # Method 3: Extract project description from article body + company name
            if len(p2) > 100:
                chinese_company = parts[1].split('______')[0] if '______' in parts[1] else ''
                proj_desc = re.search(r'(年产\d+[万千][吨瓦].*?(?:工程|项目|装置))', p2[:500])
                if chinese_company and proj_desc:
                    combined = chinese_company + proj_desc.group(1)
                    if len(combined) < 200:
                        return combined
    
    # Fallback for files where content is in part[1] (parts[2:] are empty)
    if content and len(parts) >= 2 and (len(parts) < 3 or not parts[2].strip()):
        p1 = parts[1].strip()
        # Try regex: company name + project description
        proj_match = re.match(r'^([\u4e00-\u9fff]+?(?:有限公司?|有限责任公司).*?(?:项目|工程|装置))', p1[:200])
        if proj_match:
            return proj_match.group(1)
    
    # Fallback for multi-tab files: try part[4] (pure Chinese company name) if part[2] starts with non-Chinese
    if content and len(parts) > 4 and parts[4].strip():
        cn_name = parts[4].strip()
        if cn_name and ord(cn_name[0]) > 0x7f and len(cn_name) < 100:
            # Try to get project description from part[7] (English title) or part[2]
            for src in [parts[7] if len(parts) > 7 else '', parts[2]]:
                desc = re.search(r'(?:GRASSROOT|UNIT ADDITION|PHASE)\s*(.*?)(?:\t|$)', src)
                if desc:
                    eng = desc.group(1).strip().lower()
                    # Remove common English words from title
                    short = cn_name
                    if len(short) > 5:
                        return short
            return cn_name
    
    # ── Line-no-title format: Line0 = bare ID, Line2+ = project name ──
    # Detect: first line is pure digits (no tab), second+ lines contain Chinese text
    if content:
        all_lines = content.strip().split('\n')
        first_line = all_lines[0].strip().split('\r')[0].strip() if all_lines else ''
        if re.match(r'^\d+$', first_line) and len(all_lines) > 2 and '\t' not in first_line:
            skip_prefixes = ('更新时间', '来源', '发布日期', '当前位置', '导出', '收藏', '打印', '跟踪', '纠错', '相关更多', '具体需求', '项目跟踪', '项目详细', '项目主要', '项目信息', '返回项目')
            skip_exact = ('开', '扫', '码', '')
            for i in range(1, min(20, len(all_lines))):
                l = all_lines[i].strip()
                if l in skip_exact:
                    continue
                # Handle "项目名称：" prefix
                if l.startswith('项目名称：'):
                    candidate = l[len('项目名称：'):].strip().rstrip('；;')
                    if candidate and len(candidate) >= 6:
                        return candidate
                    continue
                if any(l.startswith(p) for p in skip_prefixes):
                    continue
                # Pure date/dash patterns
                if re.match(r'^[\d\-\—\s:：年月日时分秒]+$', l):
                    continue
                # Must start with Chinese or symbol (> 0x7f) and be clean project title
                if 8 <= len(l) <= 120 and (ord(l[0]) > 0x7f):
                    starts_with_keyword = re.match(r'(?:有限公司?|有限责任公司?|年产|新建|扩建|技改|建设)', re.sub(r'^[★●◆▸▹▪▫▲▼◇◆▶◀○●◎⚫⬤]+', '', l))
                    has_company_in_first_25 = re.match(r'^.{0,25}(?:有限公司?|有限责任公司?)', l)
                    has_project_start = re.search(r'(?:年产|万吨|万t|新建|扩建|原址|迁建|技术改造)', l)
                    if starts_with_keyword or \
                       (has_company_in_first_25 and re.search(r'(?:项目|工程|装置)', l)) or \
                       (has_project_start and re.search(r'(?:项目|工程)', l)):
                        return l
            # Fallback: return line 2
            if len(all_lines) > 2:
                return all_lines[2].strip()

    # Filename-based extraction
    m9 = re.match(r'^(\d{9})(?:\s+(.*))?$', name)
    if m9 and m9.group(2):
        return m9.group(2).strip()
    m7 = re.match(r'^(\d{7})(?:\s+(.*))?$', name)
    if m7 and m7.group(2):
        return m7.group(2).strip()
    if not re.match(r'^\d+$', name):
        return name.strip()
    
    # Final fallback: strip ID from first line
    if content:
        fl = content.split('\n')[0].strip().split('\r')[0].strip()
        fd = re.sub(r'^\d+\t', '', fl)
        return (fd.strip() if fd and fd != fl else fl[:100].strip()) or name
    return name

# ── main ───────────────────────────────────────────────────

def main():
    offset = int(sys.argv[1]) if len(sys.argv) > 1 else 0
    limit = int(sys.argv[2]) if len(sys.argv) > 2 else 0

    # 1. Ensure tracker table + UNIQUE index on id_code
    run_sql_on_server(
        "CREATE TABLE IF NOT EXISTS sync_tracker "
        "(filename TEXT PRIMARY KEY, mtime REAL, last_synced TEXT);"
    )
    run_sql_on_server(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_tsk_data_id_code "
        "ON tsk_data(id_code) WHERE id_code IS NOT NULL AND id_code != '';"
    )

    # 2. Load tracker
    r = query_server("SELECT filename, mtime FROM sync_tracker;")
    tracker = {}
    for line in r.stdout.strip().split('\n'):
        if '|' in line:
            fn, mt = line.split('|', 1)
            tracker[fn] = float(mt)
    print(f"[INFO] Tracker has {len(tracker)} entries")

    # 3. Scan files (mtime only, no reads)
    files = sorted([f for f in os.listdir(ORIGINALS_DIR)
                    if f.endswith('.txt') and f != '000alerts.txt'])
    if limit > 0:
        files = files[offset:offset+limit]
    elif offset > 0:
        files = files[offset:]

    print(f"[INFO] Scanning {len(files)} files (offset={offset}, limit={'all' if limit==0 else limit})")

    changed = []
    skipped = 0
    for filename in files:
        fp = os.path.join(ORIGINALS_DIR, filename)
        try:
            cmt = os.stat(fp).st_mtime
        except:
            skipped += 1
            continue
        pmt = tracker.get(filename)
        if pmt is not None and abs(cmt - pmt) < 0.01:
            skipped += 1
        else:
            changed.append((filename, cmt))

    print(f"[INFO] mtime: {len(changed)} changed, {skipped} skipped (unchanged)")

    if not changed:
        print("[DONE] No changes since last sync")
        return

    # 4. Process changed files (batch mode)
    imported = 0
    errors = 0
    sbreak = {}
    BATCH_SIZE = 100

    for batch_start in range(0, len(changed), BATCH_SIZE):
        batch = changed[batch_start:batch_start+BATCH_SIZE]
        data_sql_lines = []
        tracker_sql_lines = []

        for filename, cmt in batch:
            fp = os.path.join(ORIGINALS_DIR, filename)
            try:
                # Try UTF-8 first, fall back to GBK/GB18030 (most files are GBK encoded)
                try:
                    with open(fp, 'r', encoding='utf-8') as fh:
                        content = fh.read()
                except UnicodeDecodeError:
                    # Try strict GBK; if that fails (corrupt bytes), use errors='replace'
                    try:
                        with open(fp, 'r', encoding='gbk') as fh:
                            content = fh.read()
                    except UnicodeDecodeError:
                        try:
                            with open(fp, 'r', encoding='gb18030') as fh:
                                content = fh.read()
                        except UnicodeDecodeError:
                            raw = open(fp, 'rb').read()
                            content = raw.decode('gbk', errors='replace')
            except Exception as e:
                print(f"[ERR] Read {filename}: {e}")
                errors += 1
                continue

            id_code, source = classify(filename)
            title = extract_title(filename, content)
            sbreak[source] = sbreak.get(source, 0) + 1

            ic = f"'{id_code}'" if id_code else "NULL"
            tl = title.replace("'", "''")
            ct = content[:50000].replace("'", "''")
            data_sql_lines.append(f"INSERT OR REPLACE INTO tsk_data (source, id_code, title, content) VALUES ('{source}',{ic},'{tl}','{ct}');")

            ts = time.strftime('%Y-%m-%d %H:%M:%S')
            fn_s = filename.replace("'", "''")
            tracker_sql_lines.append(f"INSERT OR REPLACE INTO sync_tracker (filename, mtime, last_synced) VALUES ('{fn_s}', {cmt}, '{ts}');")
            imported += 1

        if data_sql_lines:
            sql = "BEGIN TRANSACTION;\n" + "\n".join(data_sql_lines + tracker_sql_lines) + "\nCOMMIT;"
            run_sql_on_server(sql, timeout=60)

        pct = min(100, (batch_start + len(batch)) * 100 // len(changed))
        print(f"  [{batch_start+len(batch)}/{len(changed)}] +{imported} err{errors} ({pct}%)")

    # Stats
    r1 = query_server("SELECT COUNT(*) FROM tsk_data;")
    r2 = query_server("SELECT source, COUNT(*) FROM tsk_data GROUP BY source ORDER BY source;")

    print(f"\n{'='*50}")
    print(f"[DONE]")
    print(f"  Skipped (mtime unchanged): {skipped}")
    print(f"  Imported (new/changed):    {imported}")
    print(f"  Read errors:               {errors}")
    print(f"  Breakdown: {json.dumps(sbreak, ensure_ascii=False)}")
    print(f"  DB total: {r1.stdout.strip()}")
    for line in r2.stdout.strip().split('\n'):
        if line.strip():
            parts = line.split('|')
            print(f"    {parts[0]}: {parts[1] if len(parts)>1 else ''}")

if __name__ == '__main__':
    main()