#!/usr/bin/env python3
"""
parse_zc_v2.py — ZC 项目 PDF 解析（Downloads_nuc 目录）
====================================================

数据来源：坚果云 WebDAV /dav/Downloads_nuc/（PDF 文件）
缓存目录：/mnt/data/zc_pdfs/
目标数据库：/root/ZC.db (zc_projects + zc_contacts)

用法：
  python3 parse_zc_v2.py             # 增量（按 project_name 去重，已有则 UPDATE）
  python3 parse_zc_v2.py force       # 全量重跑
  python3 parse_zc_v2.py test        # 测试 3 个文件
"""

import sys, json, os, re, sqlite3, urllib.parse
from datetime import datetime
from collections import OrderedDict

import requests
from requests.auth import HTTPBasicAuth
from xml.etree import ElementTree as ET
import fitz  # PyMuPDF

# ════════════════════════════════════════════════════════════
#  CONFIG
# ════════════════════════════════════════════════════════════

WEBDAV_BASE   = 'https://dav.jianguoyun.com'
WEBDAV_DIR    = '/dav/Downloads_nuc/'
WEBDAV_USER   = 'chniir0000@outlook.com'
WEBDAV_PASS   = 'REDACTED_SEE_DOTENV'
AUTH          = HTTPBasicAuth(WEBDAV_USER, WEBDAV_PASS)

LOCAL_CACHE  = '/mnt/data/zc_pdfs'
DB_PATH      = '/root/ZC.db'

os.makedirs(LOCAL_CACHE, exist_ok=True)

# ════════════════════════════════════════════════════════════
#  ZC 数据库字段列表（不含 raw_json — 最后单独处理）
# ════════════════════════════════════════════════════════════

ZC_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',
]

# ════════════════════════════════════════════════════════════
#  Page 1 字段映射（中文标签 → 英文字段）
# ════════════════════════════════════════════════════════════

PAGE1_MAP = OrderedDict([
    ('项目编号',     'project_id'),
    ('版本类型',     'version_type'),
    ('发布时间',     'publish_date'),
    ('项目阶段',     'phase'),
    ('建设周期',     'construction_period'),
    ('总投资额',     'total_investment'),
    ('工程类型',     'project_type'),
    ('甲方性质',     'owner_nature'),
    ('所属行业',     'industry'),
    ('所属专题',     'topic'),
    ('项目规模',     'scale'),
    ('数量规模',     'quantity_scale'),
    ('行业级别',     'industry_level'),
    ('建筑面积',     'building_area'),
    ('占地面积',     'land_area'),
    ('建筑层数',     'floors'),
    ('外资参与',     'foreign_investment'),
    ('装修',        'decoration'),
    ('钢结构',       'steel_structure'),
    ('外墙材料',     'exterior_wall'),
    ('车库停车位',    'parking'),
    ('电梯',        'elevator'),
    ('空调',        'air_conditioning'),
    ('新风系统',     'fresh_air'),
    ('供暖方式',     'heating'),
    ('装配式建筑',    'prefab'),
    ('被动房',       'passive_house'),
])

# ════════════════════════════════════════════════════════════
#  Page 2 阶段标签 → 字段
# ════════════════════════════════════════════════════════════

PHASE_LABELS = [
    ('立项审批',        'phase_approval'),
    ('项目设计',        'phase_design'),
    ('主设备材料采购',   'phase_procurement'),
    ('主体施工',        'phase_construction'),
    ('工程分包',        'phase_contractor'),
    ('暂存/取消/已完工', 'phase_status'),
]

# 联系人区域 → 角色
ROLE_MAP = {
    '业主方': '业主', '设计院': '设计院', '施工单位': '施工单位',
    '施工方': '施工单位', '业主单位': '业主', '设计单位': '设计院',
    '承包商': '施工单位', '承包方': '施工单位',
}

CONTACT_SECTIONS = list(ROLE_MAP.keys())


# ════════════════════════════════════════════════════════════
#  工具
# ════════════════════════════════════════════════════════════

def log(msg):
    print(f'[{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}] {msg}')


# ════════════════════════════════════════════════════════════
#  WebDAV
# ════════════════════════════════════════════════════════════

def webdav_list():
    url = WEBDAV_BASE + WEBDAV_DIR
    r = requests.request('PROPFIND', url, auth=AUTH, headers={'Depth': '1'}, timeout=30)
    if r.status_code != 207:
        log(f'  WEBDAV PROPFIND failed: {r.status_code}')
        return []
    root = ET.fromstring(r.content)
    ns = {'d': 'DAV:'}
    pdfs = []
    for resp in root.findall('.//d:response', ns):
        href = resp.find('d:href', ns)
        fn = (href.text or '').rstrip('/').rsplit('/', 1)[-1]
        fn = urllib.parse.unquote(fn)
        if fn.endswith('.pdf'):
            pdfs.append(fn)
    return sorted(pdfs)


def webdav_download(filename, local_path):
    url = WEBDAV_BASE + WEBDAV_DIR + urllib.parse.quote(filename)
    r = requests.get(url, auth=AUTH, timeout=60)
    if r.status_code == 200:
        with open(local_path, 'wb') as f:
            f.write(r.content)
        return True
    log(f'  DOWNLOAD FAILED {filename}: HTTP {r.status_code}')
    return False


# ════════════════════════════════════════════════════════════
#  联系人解析（ZC 格式）
# ════════════════════════════════════════════════════════════

def extract_contacts(text):
    """从 PDF 正文（第2页起）提取联系人列表。
    格式：
      业主方
        单位名称：[主体承建商]XXX公司(私营)
        姓名：XXX
        部门：XXX
        职务：XXX
        手机：1XXXXXXXXXX
        ...
      设计院
        ...
    """
    contacts = []

    # 定位各区域在文本中的位置
    positions = {}
    for sec in CONTACT_SECTIONS:
        idx = text.find('\n' + sec + '\n')
        if idx < 0:
            idx = text.find(sec)
        if idx >= 0:
            positions[sec] = idx

    if not positions:
        return contacts

    sections = sorted(positions.items(), key=lambda x: x[1])

    for i, (sec_name, sec_pos) in enumerate(sections):
        # 区域结束 = 下一区域 or "Powered by TCPDF"
        next_pos = len(text)
        if i + 1 < len(sections):
            next_pos = sections[i + 1][1]
        else:
            pbr = text.find('Powered by TCPDF', sec_pos)
            if pbr >= 0:
                next_pos = pbr

        blk = text[sec_pos:next_pos].strip()
        if not blk:
            continue

        lines = blk.split('\n')
        role = ROLE_MAP.get(sec_name, '')

        # 提取公司名
        company = ''
        for line in lines:
            if '单位名称' in line and ('：' in line or ':' in line):
                val = line.split('：', 1)[-1].strip() if '：' in line else line.split(':', 1)[-1].strip()
                # 去掉尾部 (私营) (外资) 等
                val = re.sub(r'\s*[（(].*?[）)]$', '', val).strip()
                val = re.sub(r'^\[业主\]', '', val).strip()
                company = val
                break

        # 按 "姓名：" 分割每个人
        person_blocks = re.split(r'\n(?=姓名[：:])', blk)

        for pblk in person_blocks:
            plines = pblk.split('\n')
            c = {'company': company, 'contact_name': '', 'department': '',
                 'position': '', 'phone': '', 'remarks': '', 'address': '', 'role': role}
            for l in plines:
                l = l.strip()
                if not l:
                    continue
                if l.startswith('姓名') and ('：' in l):
                    c['contact_name'] = l.split('：', 1)[-1].strip()
                elif l.startswith('部门') and ('：' in l):
                    c['department'] = l.split('：', 1)[-1].strip()
                elif l.startswith('职务') and ('：' in l):
                    c['position'] = l.split('：', 1)[-1].strip()
                elif l.startswith('手机') and ('：' in l):
                    c['phone'] = l.split('：', 1)[-1].strip()
                elif l.startswith('备注') and ('：' in l):
                    c['remarks'] = l.split('：', 1)[-1].strip()
                elif l.startswith('单位注册地址') and ('：' in l):
                    c['address'] = l.split('：', 1)[-1].strip()
            if c['contact_name'] or c['phone']:
                contacts.append(c)

    return contacts


# ════════════════════════════════════════════════════════════
#  PDF 解析
# ════════════════════════════════════════════════════════════

def parse_pdf(filepath):
    """解析单个 PDF → (record dict, contacts list)"""
    doc = fitz.open(filepath)
    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()

    record = {}

    # ─── Page 1: 基础字段 ───
    lines = text_p1.split('\n')
    for i, l in enumerate(lines):
        for label, key in PAGE1_MAP.items():
            if l.startswith(label + '：') or l.startswith(label + ':'):
                val = l[len(label)+1:].strip()
                # 空值取下一行
                if not val and key != 'industry' and i + 1 < len(lines):
                    next_l = lines[i+1].strip()
                    if next_l and '：' not in next_l and ':' not in next_l:
                        val = next_l
                # 行业可能跨多行
                if key == 'industry':
                    vals = [val] if val else []
                    n = 1
                    while i + n < len(lines):
                        next_l = lines[i + n].strip()
                        if '：' in next_l or ':' in next_l or len(next_l) < 2 or next_l.startswith('项目'):
                            break
                        vals.append(next_l)
                        n += 1
                    val = ''.join(vals)
                record[key] = val
                break

    # 省/市
    for l in lines:
        if l.startswith('省/市') or l.startswith('省／市'):
            val = (l.split('：', 1)[-1] if '：' in l else l.split(':', 1)[-1]).strip()
            m = re.match(
                r'^(黑龙江|内蒙古|广西|西藏|新疆|宁夏|青海|甘肃|四川|贵州|云南|陕西|山西|'
                r'河北|山东|河南|湖北|湖南|江苏|浙江|安徽|江西|福建|广东|海南|'
                r'辽宁|吉林|上海|北京|天津|重庆)(.*)$', val)
            if m:
                record['province'] = m.group(1).strip()
                record['city'] = m.group(2).strip()
            elif '/' in val:
                parts = val.split('/', 1)
                record['province'] = parts[0].strip()
                record['city'] = parts[1].strip()
            else:
                record['province'] = val

    # 详细地址
    for l in lines:
        if l.startswith('详细地址') and ('：' in l or ':' in l):
            record['detail_address'] = (l.split('：', 1)[-1] if '：' in l else l.split(':', 1)[-1]).strip()

    # 建设内容描述
    desc_lines = []
    in_desc = False
    for l in lines:
        if l.startswith('建设内容描述') and ('：' in l or ':' in l):
            in_desc = True
            rest = (l.split('：', 1)[-1] if '：' in l else l.split(':', 1)[-1]).strip()
            if rest:
                desc_lines.append(rest)
            continue
        if in_desc:
            if l.startswith('该项目可能') or l.startswith('温馨提示'):
                break
            if l.strip():
                desc_lines.append(l.strip())
    if desc_lines:
        record['construction_content'] = ''.join(desc_lines)

    # 设备
    for l in lines:
        if l.startswith('该项目可能') or l.startswith('主要设备'):
            val = (l.split('：', 1)[-1] if '：' in l else l.split(':', 1)[-1]).strip()
            val = re.sub(r'温馨提示.*', '', val).strip()
            record['equipment_list'] = val
            break

    # 项目名称：从页眉取（跳过含字段标签的行）
    label_prefixes = tuple(k for k in PAGE1_MAP.keys()) + ('省/市', '省／市', '详细地址',
        '建设内容描述', '该项目可能', '主要设备', '温馨提示')
    for l in lines:
        l = l.strip()
        if l and len(l) > 5 and not l.startswith(label_prefixes) and '：' not in l and ':' not in l:
            record['project_name'] = l
            break
    if not record.get('project_name'):
        for l in lines:
            l = l.strip()
            if l and len(l) > 5 and not l.startswith(label_prefixes):
                record['project_name'] = l.split('：', 1)[-1].strip()
                break

    # ─── Page 2: 阶段状态 ───
    p2_lines = text_p2.split('\n')
    for i, l in enumerate(p2_lines):
        # 精准采购设备
        m = re.search(r'精准采购设备[：:]?\s*(.*)', l)
        if m:
            record['procurement_equipment'] = m.group(1).strip()
        m = re.search(r'工艺流程[：:]?\s*(.*)', l)
        if m:
            record['process_flow'] = m.group(1).strip()
        m = re.search(r'工期概述[：：]?\s*(.*)', l)
        if m:
            record['schedule_overview'] = m.group(1).strip()
        for phase_label, key in PHASE_LABELS:
            if l.startswith(phase_label) and ('：' in l or ':' in l or len(l.strip()) == len(phase_label)):
                if len(l.strip()) == len(phase_label):
                    val = p2_lines[i + 1].strip() if i + 1 < len(p2_lines) else ''
                else:
                    val = (l.split('：', 1)[-1] if '：' in l else l.split(':', 1)[-1]).strip()
                record[key] = val
                break

    # 标题回退：文件名
    if not record.get('project_name'):
        fname = os.path.basename(filepath)
        record['project_name'] = os.path.splitext(fname)[0]

    # 联系人
    contacts = extract_contacts(text_contacts)

    # raw_json = 全部原始数据快照
    raw = dict(record)
    raw['contacts'] = contacts
    record['raw_json'] = json.dumps(raw, ensure_ascii=False)

    return record, contacts


# ════════════════════════════════════════════════════════════
#  DB
# ════════════════════════════════════════════════════════════

def upsert_project(record):
    db = sqlite3.connect(DB_PATH)
    pid = record.get('project_id', '')
    pname = record.get('project_name', '')

    existing = None
    if pid:
        existing = db.execute('SELECT rowid FROM zc_projects WHERE project_id = ?', (pid,)).fetchone()
    if existing is None and pname:
        # 使用 rowid（SQLite 隐式主键）定位
        existing = db.execute('SELECT rowid FROM zc_projects WHERE project_name = ?', (pname,)).fetchone()

    # 准备值（按 ZC_FIELDS 顺序）
    vals = [record.get(f, '') for f in ZC_FIELDS]
    vals.append(record.get('raw_json', ''))  # raw_json 列

    all_cols = ZC_FIELDS + ['raw_json']
    ph = ', '.join(['?'] * len(all_cols))

    if existing:
        set_c = ', '.join(f'{f}=?' for f in all_cols)
        sql = f'UPDATE zc_projects SET {set_c} WHERE rowid=?'
        db.execute(sql, vals + [existing[0]])
        rowid = existing[0]
        action = 'UPDATE'
    else:
        sql = f'INSERT INTO zc_projects ({",".join(all_cols)}) VALUES ({ph})'
        db.execute(sql, vals)
        rowid = db.execute('SELECT last_insert_rowid()').fetchone()[0]
        action = 'INSERT'

    db.commit()
    db.close()
    return action, pid or pname


def save_contacts(project_pid, contacts):
    if not contacts:
        return 0
    db = sqlite3.connect(DB_PATH)
    # 删除旧联系人
    db.execute('DELETE FROM zc_contacts WHERE project_id = ?', (project_pid,))
    saved = 0
    for c in contacts:
        db.execute(
            'INSERT INTO zc_contacts (project_id, company, contact_name, department, position, phone, remarks, address, role) VALUES (?,?,?,?,?,?,?,?,?)',
            (project_pid, c.get('company', ''), c.get('contact_name', ''),
             c.get('department', ''), c.get('position', ''), c.get('phone', ''),
             c.get('remarks', ''), c.get('address', ''), c.get('role', ''))
        )
        saved += 1
    db.commit()
    db.close()
    return saved


# ════════════════════════════════════════════════════════════
#  MAIN
# ════════════════════════════════════════════════════════════

def main():
    mode = sys.argv[1] if len(sys.argv) > 1 else 'daily'
    log(f'=== ZC v2 parse [mode={mode}] ===')

    # 1) 列出 WebDAV 所有 PDF
    pdfs = webdav_list()
    log(f'WebDAV {WEBDAV_DIR}: {len(pdfs)} PDFs')

    if mode == 'test':
        to_process = pdfs[:3]
    else:
        to_process = pdfs

    if not to_process:
        db = sqlite3.connect(DB_PATH)
        n = db.execute('SELECT COUNT(*) FROM zc_projects').fetchone()[0]
        nc = db.execute('SELECT COUNT(*) FROM zc_contacts').fetchone()[0]
        db.close()
        log(f'Nothing to process. Projects: {n}, Contacts: {nc}')
        return

    ins = upd = err = 0
    total = len(to_process)

    for idx, fname in enumerate(to_process, 1):
        lp = os.path.join(LOCAL_CACHE, fname)
        if not os.path.exists(lp) and not webdav_download(fname, lp):
            err += 1
            continue

        try:
            rec, contacts = parse_pdf(lp)
            if rec and rec.get('project_name'):
                act, pid = upsert_project(rec)
                c_saved = save_contacts(pid, contacts)
                if act == 'INSERT':
                    ins += 1
                else:
                    upd += 1
                if idx % 20 == 0:
                    log(f'  [{idx}/{total}] +{ins} U{upd} err{err}')
            else:
                err += 1
                log(f'  SKIP {fname[:40]}: no project_name')
        except Exception as e:
            err += 1
            log(f'  ERROR {fname[:40]}: {e}')

    db = sqlite3.connect(DB_PATH)
    n = db.execute('SELECT COUNT(*) FROM zc_projects').fetchone()[0]
    nc = db.execute('SELECT COUNT(*) FROM zc_contacts').fetchone()[0]
    db.close()
    log(f'Done: +{ins} U{upd} err{err}')
    log(f'ZC.db: {n} projects, {nc} contacts')


if __name__ == '__main__':
    main()