#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
预算文件关键字分析 - 符合budget-keyword-analysis skill规则
1. 17个关键字全匹配
2. 精确位置标注(Sheet名+行号+列名 / PDF页码+行号)
3. 按市州→区县→组织部/党校分层
4. 同区县同单位同关键字合并，金额累加，位置全列出
5. 每个文件独立可点击链接
"""

import os
import re
import json
import subprocess
import sys
from pathlib import Path

# 17个关键字
KEYWORDS = [
    "网络培训", "培训", "能力提升", "视频拍摄", "党员教育片",
    "课程制作", "直播", "视频", "课件", "系统",
    "信息化", "网络", "数字", "设备", "平台",
    "维护", "软件"
]

BASE_DIR = "/data/www/files/2026-yjs-lzy"
OUTPUT_JSON = "/data/www/files/2026-yjs-lzy/parsed_full.json"
LOG_FILE = "/data/www/files/2026-yjs-lzy/parse_full_log.txt"

# 市州/区县/单位类型识别
CITY_DISTRICT_MAP = {
    "成都": ["双流", "大邑", "崇州", "都江堰", "金堂", "龙泉驿", "青白江", "邛崃", "郫都", "浦江", "浦江"],
    "巴中": ["恩阳", "南江", "平昌", "通江"],
    "攀枝花": ["东区", "仁和", "西区", "盐边", "米易"],
    "绵阳": ["安州", "平武", "三台", "涪城", "游仙", "梓潼", "北川", "江油"],
    "乐山": ["井研", "峨边", "沙湾", "沐川", "市中区", "夹江", "犍为", "金口河", "峨眉山"],
    "雅安": ["天全", "宝兴", "汉源", "石棉", "芦山", "荥经", "名山", "雨城"],
}

def detect_city(relative_path, filename):
    """从路径和文件名识别市州"""
    # 先从路径判断
    for city in CITY_DISTRICT_MAP:
        if city in relative_path or city in filename:
            return city
    # 从区县关键词推断
    for city, districts in CITY_DISTRICT_MAP.items():
        for d in districts:
            if d in filename or d in relative_path:
                return city
    # 特殊处理
    if "成都市" in filename or "郫都" in filename or "金堂" in filename or "龙泉驿" in filename or "青白江" in filename:
        return "成都"
    if "攀枝花" in filename:
        return "攀枝花"
    if "绵阳" in filename:
        return "绵阳"
    if "乐山" in filename:
        return "乐山"
    if "雅安" in filename:
        return "雅安"
    if "巴中" in filename:
        return "巴中"
    return "其他"

def detect_district(relative_path, filename):
    """识别区县"""
    all_districts = []
    for city, districts in CITY_DISTRICT_MAP.items():
        all_districts.extend(districts)
    
    for d in all_districts:
        if d in filename or d in relative_path:
            return d
    
    # 特殊处理
    special = {
        "双流": "双流", "大邑": "大邑", "崇州": "崇州", "都江堰": "都江堰",
        "邛崃": "邛崃", "浦江": "浦江", "郫都": "郫都",
    }
    for key, val in special.items():
        if key in filename:
            return val
    
    return "市级"

def detect_unit_type(filename):
    """识别单位类型：组织部 or 党校"""
    fname = filename.lower()
    # 明确标识
    if "党校" in filename or "dx_" in fname or "_dx" in fname:
        return "党校"
    if "组织部" in filename or "org_" in fname or "_org" in fname:
        return "组织部"
    # 从文件名推断
    if "党校" in filename:
        return "党校"
    if "组织部" in filename:
        return "组织部"
    # 经济科技和信息化 - 归为其他
    if "经济科技" in filename or "信息化局" in filename:
        return "其他"
    return "未分类"

def detect_unit_type_v2(relative_path, filename):
    """识别单位类型：组织部 or 党校 (改进版)"""
    text = relative_path + "/" + filename
    
    # 明确标识
    if "党校" in text or "行政学校" in text:
        return "党校"
    if "组织部" in text:
        return "组织部"
    # 从文件名中的标记
    if "_dx_" in filename.lower() or "_dx." in filename.lower():
        return "党校"
    if "_org_" in filename.lower() or "_org." in filename.lower():
        return "组织部"
    # 经济科技和信息化 - 归为其他
    if "经济科技" in filename:
        return "其他"
    return "未分类"

def extract_amount_from_cell(value):
    """从单元格值提取金额 - 排除异常值"""
    if value is None:
        return 0.0
    if isinstance(value, (int, float)):
        v = float(value)
        # 排除异常值：负数、超大值(>10000万=1亿)、零
        if v <= 0 or v > 10000:
            return 0.0
        return v
    if isinstance(value, str):
        # 排除包含字母的编码（如51111122T000004930678）
        if re.search(r'[a-zA-Z]', value):
            return 0.0
        # 提取数字
        nums = re.findall(r'[\d,]+\.?\d*', value.replace('，', ''))
        if nums:
            try:
                v = float(nums[0].replace(',', ''))
                if v <= 0 or v > 10000:  # 排除异常值
                    return 0.0
                return v
            except:
                return 0.0
    return 0.0

def find_amount_column(ws, row_idx, max_col=30):
    """在Excel行中找到预算金额列"""
    amount_headers = ['预算数', '预算金额', '金额', '总计', '合计', '本年预算', '预算', '支出预算', '一般公共预算支出']
    
    for col_idx in range(1, max_col + 1):
        try:
            cell_val = ws.cell(row=row_idx, column=col_idx).value
            if cell_val and isinstance(cell_val, str):
                for header in amount_headers:
                    if header in str(cell_val):
                        return col_idx
        except:
            pass
    return None

def parse_excel(filepath, relative_path, filename):
    """解析Excel文件 - 精确到Sheet名+行号+列名"""
    results = []
    try:
        from openpyxl import load_workbook
        wb = load_workbook(filepath, data_only=True)
        
        for ws_name in wb.sheetnames:
            ws = wb[ws_name]
            rows_processed = 0
            
            for row in ws.iter_rows(min_row=1, max_col=30):
                row_idx = row[0].row if row[0].row else rows_processed + 1
                rows_processed += 1
                
                # 检查每个单元格是否匹配关键字
                for cell in row:
                    if cell.value is None:
                        continue
                    cell_str = str(cell.value).strip()
                    if not cell_str or len(cell_str) < 2:
                        continue
                    
                    for kw in KEYWORDS:
                        if kw in cell_str:
                            # 提取金额：先找同行金额列
                            amount = 0.0
                            # 尝试从同行找金额列
                            for other_cell in row:
                                if other_cell.column != cell.column and other_cell.value is not None:
                                    val = extract_amount_from_cell(other_cell.value)
                                    if val > 0:
                                        amount = val
                                        break
                            
                            # 获取列名
                            col_letter = cell.column_letter if hasattr(cell, 'column_letter') else f"Col{cell.column}"
                            
                            results.append({
                                "keyword": kw,
                                "content": cell_str[:200],
                                "location": f"{ws_name}:R{row_idx}:{col_letter}",
                                "sheet": ws_name,
                                "row": row_idx,
                                "col": col_letter,
                                "amount": round(amount, 2),
                                "file": filename,
                                "relative_path": relative_path,
                            })
            
            
        wb.close()
    except Exception as e:
        # 尝试用xlrd处理.xls
        try:
            import xlrd
            wb = xlrd.open_workbook(filepath)
            for ws_name in wb.sheet_names():
                ws = wb.sheet_by_name(ws_name)
                for row_idx in range(ws.nrows):
                    row = ws.row_values(row_idx)
                    for col_idx, cell_val in enumerate(row):
                        cell_str = str(cell_val).strip()
                        if not cell_str or len(cell_str) < 2:
                            continue
                        for kw in KEYWORDS:
                            if kw in cell_str:
                                amount = 0.0
                                for other_val in row:
                                    val = extract_amount_from_cell(other_val)
                                    if val > 0:
                                        amount = val
                                        break
                                results.append({
                                    "keyword": kw,
                                    "content": cell_str[:200],
                                    "location": f"{ws_name}:R{row_idx+1}:Col{col_idx+1}",
                                    "sheet": ws_name,
                                    "row": row_idx + 1,
                                    "col": f"Col{col_idx+1}",
                                    "amount": round(amount, 2),
                                    "file": filename,
                                    "relative_path": relative_path,
                                })
        except Exception as e2:
            print(f"[ERROR] Excel parse failed {filepath}: {e} / {e2}", file=sys.stderr)
    
    return results

def parse_pdf(filepath, relative_path, filename):
    """解析PDF文件 - 精确到页码+行号"""
    results = []
    try:
        # 先用pdftotext提取
        result = subprocess.run(
            ['pdftotext', '-layout', filepath, '-'],
            capture_output=True, text=True, timeout=60
        )
        if result.returncode != 0 or not result.stdout.strip():
            # 尝试OCR
            return parse_pdf_ocr(filepath, relative_path, filename)
        
        text = result.stdout
        pages = text.split('\f')
        
        for page_idx, page_text in enumerate(pages, 1):
            lines = page_text.split('\n')
            for line_idx, line in enumerate(lines, 1):
                line = line.strip()
                if not line or len(line) < 2:
                    continue
                
                for kw in KEYWORDS:
                    if kw in line:
                        # 提取金额
                        amount = 0.0
                        # 方法1：同行找 XX万元
                        amt_match = re.search(r'(\d+\.?\d*)\s*万?元?', line)
                        if amt_match:
                            try:
                                val = float(amt_match.group(1))
                                if val > 0 and val < 10000:  # 排除异常值
                                    amount = val
                            except:
                                pass
                        
                        # 方法2：同行找纯数字（可能是表格中的金额列）
                        if amount == 0:
                            nums = re.findall(r'[\d,]+\.?\d*', line)
                            for num_str in nums:
                                try:
                                    val = float(num_str.replace(',', ''))
                                    if val > 0 and val < 10000 and '.' in num_str:
                                        amount = val
                                        break
                                except:
                                    pass
                        
                        results.append({
                            "keyword": kw,
                            "content": line[:200],
                            "location": f"P{page_idx}:L{line_idx}",
                            "sheet": f"Page{page_idx}",
                            "row": line_idx,
                            "col": "",
                            "amount": round(amount, 2),
                            "file": filename,
                            "relative_path": relative_path,
                        })
    except Exception as e:
        print(f"[ERROR] PDF parse failed {filepath}: {e}", file=sys.stderr)
        # 尝试OCR
        return parse_pdf_ocr(filepath, relative_path, filename)
    
    return results

def parse_pdf_ocr(filepath, relative_path, filename):
    """OCR解析PDF"""
    results = []
    try:
        # pdf2image
        from pdf2image import convert_from_path
        images = convert_from_path(filepath, dpi=200)
        
        for page_idx, img in enumerate(images, 1):
            # tesseract OCR
            import pytesseract
            text = pytesseract.image_to_string(img, lang='chi_sim')
            lines = text.split('\n')
            
            for line_idx, line in enumerate(lines, 1):
                line = line.strip()
                if not line or len(line) < 2:
                    continue
                for kw in KEYWORDS:
                    if kw in line:
                        amount = 0.0
                        amt_match = re.search(r'(\d+\.?\d*)\s*万?元?', line)
                        if amt_match:
                            try:
                                val = float(amt_match.group(1))
                                if val > 0 and val < 10000:
                                    amount = val
                            except:
                                pass
                        results.append({
                            "keyword": kw,
                            "content": line[:200],
                            "location": f"P{page_idx}:L{line_idx}(OCR)",
                            "sheet": f"Page{page_idx}",
                            "row": line_idx,
                            "col": "",
                            "amount": round(amount, 2),
                            "file": filename,
                            "relative_path": relative_path,
                        })
    except Exception as e:
        print(f"[ERROR] PDF OCR failed {filepath}: {e}", file=sys.stderr)
    
    return results

def parse_word(filepath, relative_path, filename):
    """解析Word文件"""
    results = []
    try:
        from docx import Document
        doc = Document(filepath)
        
        # 提取段落
        for para_idx, para in enumerate(doc.paragraphs, 1):
            text = para.text.strip()
            if not text or len(text) < 2:
                continue
            for kw in KEYWORDS:
                if kw in text:
                    amount = 0.0
                    amt_match = re.search(r'(\d+\.?\d*)\s*万?元?', text)
                    if amt_match:
                        try:
                            val = float(amt_match.group(1))
                            if val > 0 and val < 10000:
                                amount = val
                        except:
                            pass
                    results.append({
                        "keyword": kw,
                        "content": text[:200],
                        "location": f"Para{para_idx}",
                        "sheet": "Document",
                        "row": para_idx,
                        "col": "",
                        "amount": round(amount, 2),
                        "file": filename,
                        "relative_path": relative_path,
                    })
        
        # 提取表格
        for tbl_idx, table in enumerate(doc.tables, 1):
            for row_idx, row in enumerate(table.rows, 1):
                for cell_idx, cell in enumerate(row.cells, 1):
                    cell_text = cell.text.strip()
                    if not cell_text or len(cell_text) < 2:
                        continue
                    for kw in KEYWORDS:
                        if kw in cell_text:
                            amount = 0.0
                            # 从同行其他单元格找金额
                            for other_cell in row.cells:
                                val = extract_amount_from_cell(other_cell.text)
                                if val > 0:
                                    amount = val
                                    break
                            results.append({
                                "keyword": kw,
                                "content": cell_text[:200],
                                "location": f"Table{tbl_idx}:R{row_idx}:C{cell_idx}",
                                "sheet": f"Table{tbl_idx}",
                                "row": row_idx,
                                "col": f"C{cell_idx}",
                                "amount": round(amount, 2),
                                "file": filename,
                                "relative_path": relative_path,
                            })
    except Exception as e:
        print(f"[ERROR] Word parse failed {filepath}: {e}", file=sys.stderr)
        # 尝试LibreOffice转文本
        try:
            result = subprocess.run(
                ['libreoffice', '--headless', '--convert-to', 'txt', filepath, '--outdir', '/tmp'],
                capture_output=True, text=True, timeout=60
            )
            txt_file = '/tmp/' + os.path.splitext(os.path.basename(filepath))[0] + '.txt'
            if os.path.exists(txt_file):
                with open(txt_file, 'r') as f:
                    text = f.read()
                lines = text.split('\n')
                for line_idx, line in enumerate(lines, 1):
                    line = line.strip()
                    if not line or len(line) < 2:
                        continue
                    for kw in KEYWORDS:
                        if kw in line:
                            amount = 0.0
                            amt_match = re.search(r'(\d+\.?\d*)\s*万?元?', line)
                            if amt_match:
                                try:
                                    val = float(amt_match.group(1))
                                    if val > 0 and val < 10000:
                                        amount = val
                                except:
                                    pass
                            results.append({
                                "keyword": kw,
                                "content": line[:200],
                                "location": f"L{line_idx}",
                                "sheet": "Document",
                                "row": line_idx,
                                "col": "",
                                "amount": round(amount, 2),
                                "file": filename,
                                "relative_path": relative_path,
                            })
                os.remove(txt_file)
        except Exception as e2:
            print(f"[ERROR] Word LO fallback failed {filepath}: {e2}", file=sys.stderr)
    
    return results

def parse_doc(filepath, relative_path, filename):
    """解析.doc文件 - 用LibreOffice转文本"""
    results = []
    try:
        result = subprocess.run(
            ['libreoffice', '--headless', '--convert-to', 'txt', filepath, '--outdir', '/tmp'],
            capture_output=True, text=True, timeout=60
        )
        txt_file = '/tmp/' + os.path.splitext(os.path.basename(filepath))[0] + '.txt'
        if os.path.exists(txt_file):
            with open(txt_file, 'r', errors='ignore') as f:
                text = f.read()
            lines = text.split('\n')
            for line_idx, line in enumerate(lines, 1):
                line = line.strip()
                if not line or len(line) < 2:
                    continue
                for kw in KEYWORDS:
                    if kw in line:
                        amount = 0.0
                        amt_match = re.search(r'(\d+\.?\d*)\s*万?元?', line)
                        if amt_match:
                            try:
                                val = float(amt_match.group(1))
                                if val > 0 and val < 10000:
                                    amount = val
                            except:
                                pass
                        results.append({
                            "keyword": kw,
                            "content": line[:200],
                            "location": f"L{line_idx}",
                            "sheet": "Document",
                            "row": line_idx,
                            "col": "",
                            "amount": round(amount, 2),
                            "file": filename,
                            "relative_path": relative_path,
                        })
            os.remove(txt_file)
    except Exception as e:
        print(f"[ERROR] DOC parse failed {filepath}: {e}", file=sys.stderr)
    
    return results

def main():
    log_lines = []
    
    # 收集所有文件
    all_files = []
    for root, dirs, files in os.walk(BASE_DIR):
        for f in files:
            ext = os.path.splitext(f)[1].lower()
            if ext in ['.xlsx', '.xls', '.et', '.pdf', '.doc', '.docx']:
                filepath = os.path.join(root, f)
                relative_path = os.path.relpath(filepath, BASE_DIR)
                all_files.append((filepath, relative_path, f))
    
    log_lines.append(f"找到 {len(all_files)} 个预算文件")
    print(f"找到 {len(all_files)} 个预算文件")
    
    all_matches = []
    file_info = {}  # filepath -> {city, district, unit_type}
    
    for i, (filepath, relative_path, filename) in enumerate(all_files, 1):
        ext = os.path.splitext(filename)[1].lower()
        city = detect_city(relative_path, filename)
        district = detect_district(relative_path, filename)
        unit_type = detect_unit_type_v2(relative_path, filename)
        
        file_info[relative_path] = {
            "city": city,
            "district": district,
            "unit_type": unit_type,
            "filename": filename,
        }
        
        matches = []
        if ext in ['.xlsx', '.et']:
            matches = parse_excel(filepath, relative_path, filename)
        elif ext == '.xls':
            matches = parse_excel(filepath, relative_path, filename)
        elif ext == '.pdf':
            matches = parse_pdf(filepath, relative_path, filename)
        elif ext == '.docx':
            matches = parse_word(filepath, relative_path, filename)
        elif ext == '.doc':
            matches = parse_doc(filepath, relative_path, filename)
        
        # 为每条匹配添加市州/区县/单位类型
        for m in matches:
            m["city"] = city
            m["district"] = district
            m["unit_type"] = unit_type
        
        all_matches.extend(matches)
        
        log_line = f"[{i}/{len(all_files)}] {city}/{district}/{unit_type} - {filename} -> {len(matches)} matches"
        log_lines.append(log_line)
        print(log_line)
    
    # 按规则9合并：同区县+同单位类型+同关键字 → 合并，金额累加，位置全列出
    merged = {}  # key: (city, district, unit_type, keyword) -> {content_list, locations, total_amount, files}
    
    for m in all_matches:
        key = (m["city"], m["district"], m["unit_type"], m["keyword"])
        if key not in merged:
            merged[key] = {
                "city": m["city"],
                "district": m["district"],
                "unit_type": m["unit_type"],
                "keyword": m["keyword"],
                "contents": [],
                "locations": [],
                "amounts": [],
                "files": set(),
                "match_count": 0,
            }
        entry = merged[key]
        entry["contents"].append(m["content"])
        entry["locations"].append(m["location"])
        entry["amounts"].append(m["amount"])
        entry["files"].add(m["relative_path"])
        entry["match_count"] += 1
    
    # 转换为列表，set转list用于JSON
    merged_list = []
    for entry in merged.values():
        entry["total_amount"] = round(sum(entry["amounts"]), 2)
        entry["files"] = sorted(list(entry["files"]))
        # 合并位置为字符串
        entry["locations_str"] = ", ".join(entry["locations"][:20])  # 最多显示20个位置
        if len(entry["locations"]) > 20:
            entry["locations_str"] += f" ...等{len(entry['locations'])}处"
        # 合并内容（去重）
        unique_contents = list(dict.fromkeys(entry["contents"]))
        entry["content_display"] = " | ".join(unique_contents[:3])
        if len(unique_contents) > 3:
            entry["content_display"] += f" ...等{len(unique_contents)}条"
        merged_list.append(entry)
    
    # 统计
    cities = set()
    districts = set()
    unit_types = set()
    keywords_found = set()
    total_amount = 0
    
    for entry in merged_list:
        cities.add(entry["city"])
        districts.add(f"{entry['city']}/{entry['district']}")
        unit_types.add(entry["unit_type"])
        keywords_found.add(entry["keyword"])
        total_amount += entry["total_amount"]
    
    stats = {
        "total_files": len(all_files),
        "total_matches": len(all_matches),
        "total_merged": len(merged_list),
        "total_amount": round(total_amount, 2),
        "cities_count": len(cities),
        "districts_count": len(districts),
        "keywords_count": len(keywords_found),
        "keywords_found": sorted(list(keywords_found)),
        "cities": sorted(list(cities)),
    }
    
    output = {
        "stats": stats,
        "merged_data": merged_list,
        "file_info": file_info,
    }
    
    with open(OUTPUT_JSON, 'w', encoding='utf-8') as f:
        json.dump(output, f, ensure_ascii=False, indent=2)
    
    with open(LOG_FILE, 'w', encoding='utf-8') as f:
        f.write('\n'.join(log_lines))
    
    print(f"\n=== 解析完成 ===")
    print(f"总文件数: {stats['total_files']}")
    print(f"总匹配项: {stats['total_matches']}")
    print(f"合并后条目: {stats['total_merged']}")
    print(f"总金额: {stats['total_amount']}万元")
    print(f"市州数: {stats['cities_count']}")
    print(f"区县数: {stats['districts_count']}")
    print(f"关键字数: {stats['keywords_count']}")
    print(f"输出: {OUTPUT_JSON}")

if __name__ == '__main__':
    main()