#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""重新分析所有预算文件，提取关键字匹配项+预算金额"""

import json, os, glob, re, sys, traceback
import openpyxl

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

BASE_DIR = "/data/www/files/scQxYjs"
OUTPUT_FILE = os.path.join(BASE_DIR, "_final_results.json")

def classify_unit(filepath):
    """根据文件路径判断单位类型：组织部/党校"""
    if "组织" in filepath:
        return "组织部"
    elif "党校" in filepath:
        return "党校"
    return "其他"

def get_city_from_path(filepath):
    """从路径提取市州"""
    rel = os.path.relpath(filepath, BASE_DIR)
    parts = rel.split("/")
    if not parts:
        return "未知"
    city = parts[0]
    # 标准化城市名
    city_map = {
        "凉山": "凉山彝族自治州",
        "阿坝": "阿坝藏族羌族自治州",
        "甘孜": "甘孜藏族自治州",
    }
    for k, v in city_map.items():
        if k in city:
            return v
    return city

def get_district_from_path(filepath):
    """从路径提取区县"""
    rel = os.path.relpath(filepath, BASE_DIR)
    parts = rel.split("/")
    if len(parts) < 2:
        return "未知"
    
    # 尝试从路径中提取区县
    for part in parts[1:]:
        # 常见区县后缀
        for suffix in ["区", "县", "市"]:
            if suffix in part and len(part) < 20:
                # 提取区县名
                for kw in ["组织部", "党校", "预算", "公开", "2026", "xls", "pdf", "doc"]:
                    part = part.replace(kw, "")
                part = part.strip("_- ")
                if part:
                    return part
    return parts[1] if len(parts) > 1 else "未知"

def extract_amount(value):
    """提取金额，返回float"""
    if value is None:
        return 0
    if isinstance(value, (int, float)):
        return float(value)
    s = str(value).strip()
    if not s:
        return 0
    # 去掉非数字字符
    s = re.sub(r'[^\d.]', '', s)
    try:
        return float(s) if s else 0
    except:
        return 0

def find_amount_column(ws, header_row=1, max_check=5):
    """找到'预算数'/'合计'/'总计'列的索引"""
    amount_cols = []
    name_cols = []
    
    for r in range(1, min(max_check + 1, ws.max_row + 1)):
        for c in range(1, ws.max_column + 1):
            v = ws.cell(r, c).value
            if v is None:
                continue
            s = str(v).strip()
            # 金额列标志
            if any(kw in s for kw in ["预算数", "合计", "总计", "金额", "小计", "预算金额"]):
                if c not in amount_cols:
                    amount_cols.append(c)
            # 项目名称列标志
            if any(kw in s for kw in ["项目", "科目", "经济分类", "支出类型", "项目名称"]):
                if c not in name_cols:
                    name_cols.append(c)
    
    return name_cols, amount_cols

def analyze_excel(filepath):
    """分析Excel文件，提取关键字匹配项+金额"""
    results = []
    try:
        wb = openpyxl.load_workbook(filepath, data_only=True)
    except Exception as e:
        return [{"关键字": "文件错误", "内容": str(e)[:50], "位置": "-", "金额": 0, "文件": filepath}]
    
    for sn in wb.sheetnames:
        ws = wb[sn]
        if ws.max_row < 2:
            continue
        
        # 找项目名列和金额列
        name_cols, amount_cols = find_amount_column(ws)
        
        # 如果没找到明确的列，用启发式：扫描所有列
        if not name_cols:
            # 默认用第2或第3列作为项目名
            name_cols = [2, 3]
        if not amount_cols:
            # 找最后一个有数字的列
            for c in range(ws.max_column, 0, -1):
                for r in range(1, min(ws.max_row + 1, 20)):
                    v = ws.cell(r, c).value
                    if isinstance(v, (int, float)) and v > 0:
                        amount_cols = [c]
                        break
                if amount_cols:
                    break
        
        # 扫描每一行
        for r in range(1, ws.max_row + 1):
            row_text = ""
            cell_values = {}
            for c in range(1, min(ws.max_column + 1, 20)):
                v = ws.cell(r, c).value
                if v is not None:
                    s = str(v).strip()
                    row_text += s + " "
                    cell_values[c] = s
            
            if not row_text.strip():
                continue
            
            # 匹配17个关键字
            for kw in KEYWORDS:
                if kw in row_text:
                    # 提取内容（项目名列的值）
                    content = ""
                    for nc in name_cols:
                        if nc in cell_values:
                            content = cell_values[nc]
                            break
                    if not content:
                        # 用整行文本
                        content = row_text.strip()[:80]
                    
                    # 提取金额
                    amount = 0
                    for ac in amount_cols:
                        if ac in cell_values:
                            amount = extract_amount(cell_values[ac])
                            if amount > 0:
                                break
                    # 如果金额列为空，扫描整行找数字
                    if amount == 0:
                        for c, v in cell_values.items():
                            amt = extract_amount(v)
                            if amt > 0:
                                amount = amt
                                break
                    
                    results.append({
                        "关键字": kw,
                        "内容": content[:80],
                        "位置": f"Sheet:{sn},R{r}",
                        "金额": amount,
                        "文件": filepath
                    })
    
    if not results:
        results.append({"关键字": "无匹配", "内容": "文件中未找到关键字", "位置": "-", "金额": 0, "文件": filepath})
    
    return results

def analyze_text_file(filepath, text):
    """分析从PDF/Word提取的文本，提取关键字匹配项"""
    results = []
    lines = text.split('\n')
    
    for i, line in enumerate(lines, 1):
        line = line.strip()
        if not line or len(line) < 3:
            continue
        
        for kw in KEYWORDS:
            if kw in line:
                # 尝试从行中提取金额
                amount = 0
                # 查找数字（可能带小数点）
                numbers = re.findall(r'[\d,]+\.?\d*', line)
                for num_str in numbers:
                    num_str = num_str.replace(',', '')
                    try:
                        n = float(num_str)
                        if n > 0 and n < 100000:  # 合理的预算金额范围
                            amount = n
                            break
                    except:
                        pass
                
                results.append({
                    "关键字": kw,
                    "内容": line[:80],
                    "位置": f"L{i}",
                    "金额": amount,
                    "文件": filepath
                })
    
    if not results:
        results.append({"关键字": "无匹配", "内容": "文件中未找到关键字", "位置": "-", "金额": 0, "文件": filepath})
    
    return results

def extract_pdf_text(filepath):
    """从PDF提取文本"""
    # 先尝试pdftotext（快）
    import subprocess
    try:
        r = subprocess.run(["pdftotext", filepath, "-"], capture_output=True, text=True, timeout=30)
        if r.stdout and len(r.stdout.strip()) > 50:
            return r.stdout
    except:
        pass
    
    # 尝试pdf2image + OCR
    try:
        from pdf2image import convert_from_path
        import pytesseract
        images = convert_from_path(filepath, dpi=200, first_page=1, last_page=20)
        text = ""
        for img in images:
            text += pytesseract.image_to_string(img, lang='chi_sim') + "\n"
        return text
    except:
        return ""

def extract_doc_text(filepath):
    """从Word doc/docx提取文本"""
    import subprocess
    # docx用python-docx
    if filepath.endswith('.docx'):
        try:
            from docx import Document
            doc = Document(filepath)
            return '\n'.join([p.text for p in doc.paragraphs])
        except:
            pass
    # doc/xls用libreoffice转换
    try:
        r = subprocess.run(["libreoffice", "--headless", "--convert-to", "txt", filepath, "--outdir", "/tmp/"],
                          capture_output=True, text=True, timeout=30)
        basename = os.path.splitext(os.path.basename(filepath))[0]
        txt_path = f"/tmp/{basename}.txt"
        if os.path.exists(txt_path):
            with open(txt_path) as f:
                return f.read()
    except:
        pass
    return ""

def extract_xls_text(filepath):
    """从xls提取文本"""
    import subprocess
    try:
        r = subprocess.run(["libreoffice", "--headless", "--convert-to", "txt", filepath, "--outdir", "/tmp/"],
                          capture_output=True, text=True, timeout=30)
        basename = os.path.splitext(os.path.basename(filepath))[0]
        txt_path = f"/tmp/{basename}.txt"
        if os.path.exists(txt_path):
            with open(txt_path) as f:
                return f.read()
    except:
        pass
    return ""

def analyze_file(filepath):
    """分析单个文件"""
    ext = os.path.splitext(filepath)[1].lower()
    
    if ext in ['.xlsx', '.xls']:
        if ext == '.xlsx':
            return analyze_excel(filepath)
        else:
            # xls用文本提取
            text = extract_xls_text(filepath)
            if text:
                return analyze_text_file(filepath, text)
            else:
                return [{"关键字": "文件错误", "内容": "无法读取xls", "位置": "-", "金额": 0, "文件": filepath}]
    elif ext == '.pdf':
        text = extract_pdf_text(filepath)
        if text:
            return analyze_text_file(filepath, text)
        else:
            return [{"关键字": "文件错误", "内容": "无法读取PDF", "位置": "-", "金额": 0, "文件": filepath}]
    elif ext in ['.doc', '.docx']:
        text = extract_doc_text(filepath)
        if text:
            return analyze_text_file(filepath, text)
        else:
            return [{"关键字": "文件错误", "内容": "无法读取Word", "位置": "-", "金额": 0, "文件": filepath}]
    elif ext == '.et':
        # WPS表格，用libreoffice转换
        text = extract_xls_text(filepath)
        if text:
            return analyze_text_file(filepath, text)
        else:
            return [{"关键字": "文件错误", "内容": "无法读取ET", "位置": "-", "金额": 0, "文件": filepath}]
    else:
        return [{"关键字": "文件错误", "内容": f"不支持的格式: {ext}", "位置": "-", "金额": 0, "文件": filepath}]

def main():
    # 遍历所有文件
    all_files = []
    for ext in ['*.xlsx', '*.xls', '*.pdf', '*.doc', '*.docx', '*.et']:
        all_files.extend(glob.glob(os.path.join(BASE_DIR, "**", ext), recursive=True))
    
    print(f"共找到 {len(all_files)} 个文件")
    
    # 分析每个文件
    results = {}  # 市州 → 单位类型 → 区县 → [匹配项]
    
    for i, filepath in enumerate(all_files):
        if i % 50 == 0:
            print(f"进度: {i}/{len(all_files)}", flush=True)
            # 保存中间结果
            if results:
                with open(OUTPUT_FILE, 'w') as f:
                    json.dump(results, f, ensure_ascii=False)
        
        try:
            city = get_city_from_path(filepath)
            unit = classify_unit(filepath)
            district = get_district_from_path(filepath)
            
            if city not in results:
                results[city] = {}
            if unit not in results[city]:
                results[city][unit] = {}
            if district not in results[city][unit]:
                results[city][unit][district] = []
            
            matches = analyze_file(filepath)
            
            # 只保留有实际匹配的（排除无匹配和文件错误）
            real_matches = [m for m in matches if m["关键字"] not in ["无匹配", "文件错误"]]
            if real_matches:
                results[city][unit][district].extend(real_matches)
            
        except Exception as e:
            print(f"  错误 {filepath}: {e}", flush=True)
    
    # 保存最终结果
    with open(OUTPUT_FILE, 'w') as f:
        json.dump(results, f, ensure_ascii=False)
    
    # 统计
    total_matches = 0
    total_amount = 0
    for city, units in results.items():
        for unit, districts in units.items():
            for dist, items in districts.items():
                total_matches += len(items)
                for item in items:
                    total_amount += item.get("金额", 0)
    
    print(f"\n完成！总匹配: {total_matches}, 总金额: {total_amount:.2f}万元")
    print(f"结果保存到: {OUTPUT_FILE}")

if __name__ == "__main__":
    main()