#!/usr/bin/env python3
"""
ZC 数据同步：WebDAV 下载 PDF → 解析 → 导入 ZC.db
去重方式：解析后查 project_name 是否已在库里，不再靠文件名 track file
"""

import os, sys, re, json, urllib.parse, base64, urllib.request
import sqlite3
from datetime import datetime

WEBDAV_BASE = "https://dav.jianguoyun.com/dav/Downloads_nuc"
AUTH = "chniir0000@outlook.com:REDACTED_SEE_DOTENV"
PDF_DIR = "/mnt/data/zc_pdfs"
DB = "/root/ZC.db"
LOG = "/var/log/zc_sync.log"

os.makedirs(PDF_DIR, exist_ok=True)


def log(msg):
    ts = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    with open(LOG, 'a') as f:
        f.write(f"[{ts}] {msg}\n")
    print(msg)


def webdav_list():
    url = WEBDAV_BASE + "/"
    req = urllib.request.Request(url, method="PROPFIND", headers={"Depth": "1"})
    creds = base64.b64encode(AUTH.encode()).decode()
    req.add_header("Authorization", f"Basic {creds}")
    try:
        resp = urllib.request.urlopen(req, timeout=30)
        xml_data = resp.read().decode()
    except Exception as e:
        log(f"  WebDAV 列表失败: {e}")
        return []
    pdfs = []
    for m in re.finditer(r'<d:href>([^<]+\.pdf)</d:href>', xml_data):
        href = m.group(1)
        fname = href.rstrip('/').split('/')[-1]
        fname = urllib.parse.unquote(fname)
        pdfs.append(fname)
    return sorted(pdfs)


def webdav_download(filename):
    url = WEBDAV_BASE + "/" + urllib.parse.quote(filename)
    req = urllib.request.Request(url)
    creds = base64.b64encode(AUTH.encode()).decode()
    req.add_header("Authorization", f"Basic {creds}")
    try:
        resp = urllib.request.urlopen(req, timeout=60)
        data = resp.read()
        path = os.path.join(PDF_DIR, filename)
        with open(path, 'wb') as f:
            f.write(data)
        return path
    except Exception as e:
        log(f"  下载失败 {filename}: {e}")
        return None


def parse_pdf(pdf_path):
    import fitz
    doc = fitz.open(pdf_path)
    text_p1 = doc[0].get_text() if doc.page_count > 0 else ''
    text_p2 = doc[1].get_text() if doc.page_count > 1 else ''
    text_contacts = ''
    for pi in range(1, doc.page_count):
        text_contacts += doc[pi].get_text() + '\n'
    doc.close()
    sys.path.insert(0, '/root/gov_crawler')
    import importlib.util
    spec = importlib.util.spec_from_file_location("parse_zc", "/root/gov_crawler/parse_zc.py")
    pz = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(pz)
    p = pz.extract_basic(text_p1)
    p.update(pz.extract_page2(text_p2))
    if not p.get('project_name'):
        fname = os.path.basename(pdf_path)
        p['project_name'] = os.path.splitext(fname)[0]
    contacts = pz.extract_contacts(text_contacts)
    return p, contacts


def sq(s):
    return "'" + str(s).replace("'", "''") + "'"


def check_exists(c, pid, name):
    """查 DB：project_id 或 project_name 是否已存在"""
    if pid:
        r = c.execute("SELECT 1 FROM zc_projects WHERE project_id=?", (pid,)).fetchone()
        if r:
            return True
    if name:
        r = c.execute("SELECT 1 FROM zc_projects WHERE project_name=?", (name,)).fetchone()
        if r:
            return True
    return False


def insert_to_db(p, contacts, filename):
    conn = sqlite3.connect(DB)
    c = conn.cursor()
    pid = p.get('project_id', '')
    name = p.get('project_name', '') or os.path.splitext(filename)[0]
    if check_exists(c, pid, name):
        conn.close()
        return False
    fields = ['project_id', 'project_name', 'version_type', 'publish_date', 'phase',
              'construction_period', 'total_investment', 'project_type', 'owner_nature',
              'industry', 'topic', 'scale', 'quantity_scale', 'industry_level',
              'province', 'city', 'detail_address', 'building_area', 'land_area',
              'floors', 'foreign_investment', 'decoration', 'steel_structure',
              'exterior_wall', 'parking', 'elevator', 'air_conditioning', 'fresh_air',
              'heating', 'prefab', 'passive_house', 'construction_content',
              'equipment_list', 'procurement_equipment', 'process_flow',
              'schedule_overview', 'phase_approval', 'phase_design',
              'phase_procurement', 'phase_construction', 'phase_contractor', 'phase_status',
              'raw_json']
    raw_data = dict(p)
    raw_data['contacts'] = contacts
    p['raw_json'] = json.dumps(raw_data, ensure_ascii=False)
    vals = [sq(p.get(f, '')) for f in fields]
    sql = f"INSERT INTO zc_projects ({', '.join(fields)}) VALUES ({', '.join(vals)})"
    c.execute(sql)
    for ct in contacts:
        c.execute(
            "INSERT INTO zc_contacts (project_id, company, contact_name, department, position, phone, remarks, address, role) VALUES (?,?,?,?,?,?,?,?,?)",
            (pid, ct.get('company',''), ct.get('contact_name',''), ct.get('department',''),
             ct.get('position',''), ct.get('phone',''), ct.get('remarks',''),
             ct.get('address',''), ct.get('role',''))
        )
    conn.commit()
    conn.close()
    return True


def main():
    log("=== 开始 ZC 同步 ===")

    pdfs = webdav_list()
    log(f"WebDAV 共有 {len(pdfs)} 个 PDF")

    new_count = 0
    skip_count = 0
    for fname in pdfs:
        # 已下载则跳过下载，直接解析
        local_path = os.path.join(PDF_DIR, fname)
        if os.path.exists(local_path):
            pdf_path = local_path
        else:
            pdf_path = webdav_download(fname)
            if not pdf_path:
                continue

        try:
            p, contacts = parse_pdf(pdf_path)
            inserted = insert_to_db(p, contacts, fname)
            if inserted:
                new_count += 1
                log(f"  ✅ {fname[:50]} (联系人: {len(contacts)})")
            else:
                skip_count += 1
        except Exception as e:
            log(f"  ✗ {fname[:50]}: {e}")

    count = sqlite3.connect(DB).execute("SELECT COUNT(*) FROM zc_projects").fetchone()[0]
    log(f"新增: {new_count}, 跳过: {skip_count}, ZC.db 总计: {count} 条")
    log("=== 完成 ===")


if __name__ == '__main__':
    main()