#!/usr/bin/env python3
"""
ZC项目PDF解析器（NUC文件夹专用）
适用：~/Nutstore Files/Downloads_nuc/ 目录
全部可选文字，无需OCR

用法：
  测试单个：python3 parse_zc.py "山东星必达....pdf"
  批量SQL： python3 parse_zc.py --batch > insert.sql
"""

import fitz, re, os, sys

PDF_DIR = os.path.expanduser("~/Nutstore Files/Downloads_nuc")


# ═══════════════════════════════════════════════════
#  Page 1 字段提取（标签: 值 格式）
# ═══════════════════════════════════════════════════

def extract_basic(text):
    """提取 page 1 基础字段"""
    p = {}
    lines = text.split('\n')

    field_map = {
        '项目编号': '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',
    }

    for i, l in enumerate(lines):
        for label, key in field_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
                # 所属行业可能跨多行（如"其他仓储物流/石化/煤化工/基础\n化工"）
                if key == 'industry':
                    vals = []
                    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
                    if vals:
                        val = ''.join(vals)
                p[key] = val
                break

    # 省/市（特殊格式：四川成都市 → 四川/成都市）
    for i, l in enumerate(lines):
        if l.startswith('省/市') or l.startswith('省／市'):
            val = l.split('：', 1)[-1].strip() if '：' in l else l.split(':', 1)[-1].strip()
            # 尝试找省份和城市：如"山东菏泽市"、"内蒙古包头市"
            m = re.match(r'^(黑龙江|内蒙古|广西|西藏|新疆|宁夏|青海|甘肃|四川|贵州|云南|陕西|山西|河北|山东|河南|湖北|湖南|江苏|浙江|安徽|江西|福建|广东|海南|辽宁|吉林|上海|北京|天津|重庆)(.*)$', val)
            if m:
                p['province'] = m.group(1).strip()
                p['city'] = m.group(2).strip()
            elif '/' in val:
                parts = val.split('/', 1)
                p['province'] = parts[0].strip()
                p['city'] = parts[1].strip()
            else:
                p['province'] = val
                p['city'] = ''

    # 详细地址
    for l in lines:
        if l.startswith('详细地址') and ('：' in l or ':' in l):
            p['detail_address'] = l.split('：', 1)[-1].strip() 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].strip() 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:
        p['construction_content'] = ''.join(desc_lines)

    # 设备（温馨提示之前的行）
    for l in lines:
        if l.startswith('该项目可能') or l.startswith('主要设备'):
            val = l.split('：', 1)[-1].strip() if '：' in l else l.split(':', 1)[-1].strip()
            # 去掉尾部温馨提示
            val = re.sub(r'温馨提示.*', '', val).strip()
            p['equipment_list'] = val
            break

    # 项目名称：从文件路径取或从页眉取
    # page header 通常是第一行
    header = lines[0].strip() if lines else ''
    if header and len(header) > 10 and '项目' not in header:
        p['project_name'] = header
    elif len(lines) > 1:
        # 有时标题在第二行
        header2 = lines[1].strip()
        if header2 and len(header2) > 10:
            p['project_name'] = header2

    return p


def extract_page2(text):
    """提取 page 2 字段"""
    p = {}
    lines = text.split('\n')

    for i, l in enumerate(lines):
        # 精准采购设备
        m = re.search(r'精准采购设备[：:]?\s*(.*)', l)
        if m:
            p['procurement_equipment'] = m.group(1).strip()
            continue

        # 工艺流程
        m = re.search(r'工艺流程[：:]?\s*(.*)', l)
        if m:
            p['process_flow'] = m.group(1).strip()
            continue

        # 工期概述
        m = re.search(r'工期概述[：：]?\s*(.*)', l)
        if m:
            p['schedule_overview'] = m.group(1).strip()
            continue

        # 立项审批/项目设计等阶段状态
        for phase_label, key in [
            ('立项审批', 'phase_approval'),
            ('项目设计', 'phase_design'),
            ('主设备材料采购', 'phase_procurement'),
            ('主体施工', 'phase_construction'),
            ('工程分包', 'phase_contractor'),
            ('暂存/取消/已完工', 'phase_status'),
        ]:
            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 = lines[i + 1].strip() if i + 1 < len(lines) else ''
                else:
                    val = l.split('：', 1)[-1].strip() if '：' in l else l.split(':', 1)[-1].strip()
                p[key] = val
                break

    return p


# ═══════════════════════════════════════════════════
#  联系人提取（可选文字，无需OCR）
# ═══════════════════════════════════════════════════

def extract_contacts(text):
    """从 '业主方' / '设计院' 等多个区域提取联系人"""
    contacts = []
    
    CONTACT_SECTIONS = ["业主方", "设计院", "施工单位", "施工方", "业主单位", "设计单位", "承包商", "承包方"]
    SECTION_ORDER = ["业主方", "设计院", "施工单位", "施工方", "业主单位", "设计单位", "承包商", "承包方"]
    
    def parse_one_section(blk, default_role=""):
        """解析一个联系人区域块"""
        lines = blk.split('\n')
        # 首行为区域名
        section_title = lines[0].strip() if lines else ""
        # 角色映射
        role_map = {"业主方": "业主", "设计院": "设计院", "施工单位": "施工单位", "施工方": "施工单位", "业主单位": "业主", "设计单位": "设计院", "承包商": "施工单位", "承包方": "施工单位"}
        role = role_map.get(section_title, default_role)
        
        # 提取单位名称
        company = ''
        for line in lines:
            if '单位名称' in line and ('：' in line or ':' in line):
                # 格式: 单位名称：[主体承建商]XXX公司(私营)
                val = line.split('：', 1)[-1].strip() if '：' in line else line.split(':', 1)[-1].strip()
                # 去掉尾部 (私营) (外资) (国有/集体所有) 等
                val = re.sub(r'\s*[（(].*?[）)]$', '', val).strip()
                # 去掉 [业主] 前缀（role已标记），保留 [施工图设计] [主体承建商] 等
                val = re.sub(r'^\[业主\]', '', val).strip()
                company = val
                break
        
        # 每个联系人以 '姓名：' 开头
        person_blocks = re.split(r'\n(?=姓名[：:])', blk)
        
        results = []
        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):
                    name = l.split('：', 1)[-1].strip()
                    c['contact_name'] = name
                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']:
                results.append(c)
        return results
    
    # 找所有区域位置
    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):
        # 边界：从 sec_pos 到 next section 或 "Powered by TCPDF"
        next_pos = len(text)
        if i + 1 < len(sections):
            next_pos = sections[i + 1][1]
        else:
            # 最后一个区域，到 Powered by 或文本结尾
            pbr = text.find('Powered by TCPDF', sec_pos)
            if pbr >= 0:
                next_pos = pbr
        
        blk = text[sec_pos:next_pos].strip()
        if blk:
            contacts.extend(parse_one_section(blk))
    
    return contacts


# ═══════════════════════════════════════════════════
#  SQL 生成
# ═══════════════════════════════════════════════════

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


def gen_sql(p, contacts, filename):
    pid = p.get('project_id', '')
    name = p.get('project_name', '') or os.path.splitext(filename)[0]

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

    vals = [sq(p.get(f, '')) for f in fields]
    sql = f"INSERT INTO zc_projects ({', '.join(fields)}) VALUES ({', '.join(vals)});\n"

    for c in contacts:
        sql += (
            f"INSERT INTO zc_contacts "
            f"(project_id, company, contact_name, department, position, phone, remarks, address, role) "
            f"VALUES ({sq(pid)}, {sq(c['company'])}, {sq(c['contact_name'])}, "
            f"{sq(c['department'])}, {sq(c['position'])}, {sq(c['phone'])}, "
            f"{sq(c['remarks'])}, {sq(c['address'])}, {sq(c.get('role', ''))});\n"
        )

    return sql


# ═══════════════════════════════════════════════════
#  解析单个 PDF
# ═══════════════════════════════════════════════════

def parse_one(pdf_path):
    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 ''
    # 联系人可能跨多页（第2页到最后一页）
    text_contacts = ''
    for pi in range(1, doc.page_count):
        text_contacts += doc[pi].get_text() + '\n'
    doc.close()

    p = extract_basic(text_p1)
    p.update(extract_page2(text_p2))

    # 标题优先从文件路径取
    if not p.get('project_name'):
        fname = os.path.basename(pdf_path)
        name = os.path.splitext(fname)[0]
        p['project_name'] = name

    contacts = extract_contacts(text_contacts)

    return p, contacts


# ═══════════════════════════════════════════════════
#  主入口
# ═══════════════════════════════════════════════════

def main():
    if len(sys.argv) < 2 or sys.argv[1] in ('-h', '--help'):
        print(__doc__)
        return

    if sys.argv[1] == '--batch':
        pdfs = sorted([f for f in os.listdir(PDF_DIR) if f.endswith('.pdf')])
        total = len(pdfs)
        for i, fname in enumerate(pdfs, 1):
            path = os.path.join(PDF_DIR, fname)
            p, contacts = parse_one(path)
            sql = gen_sql(p, contacts, fname)
            print(f"-- [{i}/{total}] {fname}")
            print(sql)
        print(f"-- Total: {total} PDFs")
        return

    # 单文件
    pdf_name = sys.argv[1]
    pdf_path = os.path.join(PDF_DIR, pdf_name) if not pdf_name.startswith('/') else pdf_name

    print(f"📄 {os.path.basename(pdf_path)}")
    p, contacts = parse_one(pdf_path)

    print("\n── 项目信息 ──")
    for k, v in p.items():
        if v:
            output = str(v)[:100] + '...' if len(str(v)) > 100 else str(v)
            print(f"  {k}: {output}")

    print(f"\n── 联系人 ──")
    if contacts:
        for i, c in enumerate(contacts, 1):
            print(f"  [{i}]")
            for k, v in c.items():
                if v:
                    print(f"    {k}: {v}")
            print()
    else:
        print("  (无)")

    print(f"\n── SQL ──")
    print(gen_sql(p, contacts, pdf_name))


if __name__ == '__main__':
    main()