#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""四川区县预算文件分析 - 完整版
提取17个关键字匹配项+预算金额+精确位置
按市州→区县→单位类型(组织部/党校)分类
同关键字合并，金额累加
"""

import os
import sys
import json
import re
import traceback
from collections import defaultdict

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

# 市州列表
CITIES = ['成都','自贡','攀枝花','泸州','德阳','绵阳','广元','遂宁','内江',
          '乐山','南充','眉山','宜宾','广安','达州','巴中','雅安','资阳',
          '阿坝','甘孜','凉山']

DATA_DIR = '/data/www/files/scQxYjs'
OUTPUT_JSON = '/data/www/files/scQxYjs/_final_results.json'

# ============ 文件分类 ============
def classify_file(filepath):
    """根据文件路径判断：市州、区县、单位类型(组织部/党校)"""
    rel = os.path.relpath(filepath, DATA_DIR)
    parts = rel.replace('\\','/').split('/')
    filename = os.path.basename(filepath)
    basename = os.path.splitext(filename)[0]
    
    city = ''
    county = ''
    unit_type = ''
    
    # 判断市州
    for c in CITIES:
        if c in rel:
            city = c
            break
    
    # 判断单位类型
    if '组织部' in filename or '组织部' in rel:
        unit_type = '组织部'
    elif '党校' in filename or '党校' in rel:
        unit_type = '党校'
    
    # 判断区县：从路径中提取
    # 路径格式：市州/区县/文件 或 市州/区县组织部党校/文件
    if len(parts) >= 2:
        # 检查是否有子目录按县分类
        if parts[0] in CITIES or any(parts[0].startswith(c) for c in CITIES):
            if len(parts) >= 3:
                # parts[1] 可能是区县名或"区县组织部、党校"
                dir_name = parts[1] if len(parts) > 2 else ''
                county = extract_county(dir_name, parts, city)
    
    # 从文件名提取区县
    if not county:
        county = extract_county_from_filename(filename, city)
    
    return city, county, unit_type

def extract_county(dir_name, parts, city):
    """从目录名提取区县名"""
    # 去掉"组织部、党校"等后缀
    name = dir_name.replace('组织部','').replace('党校','').replace('、','').strip()
    # 去掉市州前缀
    if city and name.startswith(city):
        name = name[len(city):]
    return name

def extract_county_from_filename(filename, city):
    """从文件名提取区县名"""
    basename = os.path.splitext(filename)[0]
    # 去掉市州前缀
    name = basename
    if city and name.startswith(city):
        name = name[len(city):]
    # 去掉单位类型
    name = name.replace('组织部','').replace('党校','').replace('、','').strip()
    # 去掉年份和"预算"等
    name = re.sub(r'20\d{2}', '', name)
    name = re.sub(r'预算.*$', '', name)
    name = re.sub(r'部门.*$', '', name)
    name = re.sub(r'决算.*$', '', name)
    name = name.strip('_- ')
    return name

# ============ Excel解析 ============
def analyze_excel(filepath):
    """分析Excel文件，返回匹配项列表"""
    matches = []
    try:
        import openpyxl
        wb = openpyxl.load_workbook(filepath, data_only=True)
    except:
        try:
            import xlrd
            wb = None  # xlrd需要不同处理
        except:
            return matches
    
    if wb is None:
        return analyze_xls(filepath)
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        # 先找到金额列（表头行）
        amount_col = None
        name_col = None
        header_row = None
        
        # 扫描前20行找表头
        for row_idx in range(1, min(21, ws.max_row+1)):
            row_vals = [str(ws.cell(row=row_idx, column=c).value or '').strip() for c in range(1, min(ws.max_column+1, 30))]
            for col_idx, val in enumerate(row_vals):
                val_lower = val.lower()
                if not amount_col and ('预算' in val or '总计' in val or '金额' in val or '合计' in val or '小计' in val):
                    amount_col = col_idx + 1
                    header_row = row_idx
                if not name_col and ('项目' in val or '科目' in val or '名称' in val or '支出' in val or '经济' in val):
                    name_col = col_idx + 1
                    header_row = row_idx
        
        # 如果没找到表头，默认B列是名称，F列是金额
        if not name_col:
            name_col = 2
        if not amount_col:
            amount_col = 6
        
        # 扫描数据行
        for row_idx in range(header_row or 1, ws.max_row+1):
            name_val = ws.cell(row=row_idx, column=name_col).value
            if not name_val:
                continue
            name_str = str(name_val).strip()
            if not name_str or name_str in ['None','nan','']:
                continue
            
            # 匹配关键字
            for kw in KEYWORDS:
                if kw in name_str:
                    # 提取金额
                    amount = 0
                    amt_val = ws.cell(row=row_idx, column=amount_col).value
                    if amt_val:
                        try:
                            amount = float(amt_val)
                        except:
                            amount = 0
                    
                    # 如果金额为0，扫描整行找数字
                    if amount == 0:
                        for c in range(1, min(ws.max_column+1, 30)):
                            v = ws.cell(row=row_idx, column=c).value
                            if v:
                                try:
                                    fv = float(v)
                                    if fv > 0 and fv < 100000:  # 合理金额范围
                                        amount = fv
                                        break
                                except:
                                    pass
                    
                    col_letter = openpyxl.utils.get_column_letter(amount_col)
                    matches.append({
                        'keyword': kw,
                        'content': name_str,
                        'amount': round(amount, 2),
                        'location': f'{sheet_name}:R{row_idx}:{col_letter}列',
                        'file': os.path.basename(filepath)
                    })
                    break  # 一个项目只匹配第一个关键字
    
    return matches

def analyze_xls(filepath):
    """分析.xls文件"""
    matches = []
    try:
        import xlrd
        wb = xlrd.open_workbook(filepath)
    except:
        return matches
    
    for sheet in wb.sheets():
        # 找金额列
        amount_col = -1
        name_col = -1
        header_row = 0
        
        for row_idx in range(min(20, sheet.nrows)):
            for col_idx in range(min(sheet.ncols, 30)):
                val = str(sheet.cell_value(row_idx, col_idx)).strip()
                if amount_col < 0 and ('预算' in val or '总计' in val or '金额' in val or '合计' in val):
                    amount_col = col_idx
                    header_row = row_idx
                if name_col < 0 and ('项目' in val or '科目' in val or '名称' in val or '支出' in val):
                    name_col = col_idx
                    header_row = row_idx
        
        if name_col < 0:
            name_col = 1
        if amount_col < 0:
            amount_col = 5
        
        for row_idx in range(header_row, sheet.nrows):
            name_val = sheet.cell_value(row_idx, name_col)
            name_str = str(name_val).strip()
            if not name_str or name_str == '':
                continue
            
            for kw in KEYWORDS:
                if kw in name_str:
                    amount = 0
                    if amount_col < sheet.ncols:
                        amt = sheet.cell_value(row_idx, amount_col)
                        try:
                            amount = float(amt)
                        except:
                            pass
                    
                    if amount == 0:
                        for c in range(min(sheet.ncols, 30)):
                            v = sheet.cell_value(row_idx, c)
                            try:
                                fv = float(v)
                                if 0 < fv < 100000:
                                    amount = fv
                                    break
                            except:
                                pass
                    
                    matches.append({
                        'keyword': kw,
                        'content': name_str,
                        'amount': round(amount, 2),
                        'location': f'{sheet.name}:R{row_idx+1}:C{amount_col+1}',
                        'file': os.path.basename(filepath)
                    })
                    break
    
    return matches

# ============ Word解析 ============
def analyze_word(filepath):
    """分析.doc/.docx文件"""
    matches = []
    try:
        from docx import Document
        doc = Document(filepath)
    except:
        # .doc格式，用antiword或tesseract
        return analyze_doc_old(filepath)
    
    # 提取表格
    for table_idx, table in enumerate(doc.tables):
        # 找金额列
        amount_col = -1
        name_col = -1
        if len(table.rows) > 0:
            header = table.rows[0]
            for cell_idx, cell in enumerate(header.cells):
                val = cell.text.strip()
                if amount_col < 0 and ('预算' in val or '总计' in val or '金额' in val or '合计' in val):
                    amount_col = cell_idx
                if name_col < 0 and ('项目' in val or '科目' in val or '名称' in val or '支出' in val):
                    name_col = cell_idx
        
        if name_col < 0:
            name_col = 1
        if amount_col < 0:
            amount_col = 5
        
        for row_idx, row in enumerate(table.rows):
            cells = row.cells
            if name_col < len(cells):
                name_str = cells[name_col].text.strip()
                if not name_str:
                    continue
                
                for kw in KEYWORDS:
                    if kw in name_str:
                        amount = 0
                        if amount_col < len(cells):
                            amt_str = cells[amount_col].text.strip()
                            try:
                                amount = float(amt_str)
                            except:
                                # 尝试提取数字
                                nums = re.findall(r'[\d.]+', amt_str)
                                if nums:
                                    try:
                                        amount = float(nums[0])
                                    except:
                                        pass
                        
                        matches.append({
                            'keyword': kw,
                            'content': name_str,
                            'amount': round(amount, 2),
                            'location': f'表格{table_idx+1}:行{row_idx+1}',
                            'file': os.path.basename(filepath)
                        })
                        break
    
    # 提取段落中的关键字
    for para_idx, para in enumerate(doc.paragraphs):
        text = para.text.strip()
        if not text:
            continue
        for kw in KEYWORDS:
            if kw in text:
                # 提取金额
                amount = 0
                m = re.search(r'([\d.]+)\s*万?元', text)
                if m:
                    try:
                        amount = float(m.group(1))
                    except:
                        pass
                matches.append({
                    'keyword': kw,
                    'content': text[:100],
                    'amount': round(amount, 2),
                    'location': f'段落{para_idx+1}',
                    'file': os.path.basename(filepath)
                })
                break
    
    return matches

def analyze_doc_old(filepath):
    """分析旧版.doc文件"""
    matches = []
    # 用antiword提取文本
    try:
        import subprocess
        result = subprocess.run(['antiword', filepath], capture_output=True, text=True, timeout=30)
        text = result.stdout
    except:
        # 用tesseract OCR
        text = ''
        try:
            import subprocess
            # 先转图片
            subprocess.run(['libreoffice', '--headless', '--convert-to', 'pdf', '--outdir', '/tmp', filepath], timeout=30)
            pdf_path = '/tmp/' + os.path.splitext(os.path.basename(filepath))[0] + '.pdf'
            if os.path.exists(pdf_path):
                text = extract_pdf_text(pdf_path)
        except:
            return matches
    
    if not text:
        return matches
    
    lines = text.split('\n')
    for line_idx, line in enumerate(lines):
        line = line.strip()
        if not line:
            continue
        for kw in KEYWORDS:
            if kw in line:
                amount = 0
                m = re.search(r'([\d.]+)\s*万?元', line)
                if m:
                    try:
                        amount = float(m.group(1))
                    except:
                        pass
                matches.append({
                    'keyword': kw,
                    'content': line[:100],
                    'amount': round(amount, 2),
                    'location': f'行{line_idx+1}',
                    'file': os.path.basename(filepath)
                })
                break
    
    return matches

# ============ PDF解析 ============
def analyze_pdf(filepath):
    """分析PDF文件（含OCR）"""
    matches = []
    
    # 先尝试用pdfplumber提取文本
    text_pages = []
    try:
        import pdfplumber
        with pdfplumber.open(filepath) as pdf:
            for page_idx, page in enumerate(pdf.pages):
                # 尝试提取表格
                tables = page.extract_tables()
                if tables:
                    for table_idx, table in enumerate(tables):
                        # 找金额列
                        amount_col = -1
                        name_col = -1
                        if len(table) > 0:
                            for cell_idx, cell in enumerate(table[0]):
                                val = str(cell or '').strip()
                                if amount_col < 0 and ('预算' in val or '总计' in val or '金额' in val or '合计' in val):
                                    amount_col = cell_idx
                                if name_col < 0 and ('项目' in val or '科目' in val or '名称' in val or '支出' in val):
                                    name_col = cell_idx
                        if name_col < 0:
                            name_col = 1
                        if amount_col < 0:
                            amount_col = 5
                        
                        for row_idx, row in enumerate(table):
                            if name_col < len(row):
                                name_str = str(row[name_col] or '').strip()
                                if not name_str:
                                    continue
                                for kw in KEYWORDS:
                                    if kw in name_str:
                                        amount = 0
                                        if amount_col < len(row):
                                            amt_str = str(row[amount_col] or '').strip()
                                            try:
                                                amount = float(amt_str)
                                            except:
                                                nums = re.findall(r'[\d.]+', amt_str)
                                                if nums:
                                                    try:
                                                        amount = float(nums[0])
                                                    except:
                                                        pass
                                        matches.append({
                                            'keyword': kw,
                                            'content': name_str[:100],
                                            'amount': round(amount, 2),
                                            'location': f'第{page_idx+1}页:表格{table_idx+1}:行{row_idx+1}',
                                            'file': os.path.basename(filepath)
                                        })
                                        break
                
                # 提取文本行
                page_text = page.extract_text()
                if page_text:
                    text_pages.append((page_idx+1, page_text))
    except:
        pass
    
    # 如果pdfplumber提取不到文本，用OCR
    if not text_pages and not matches:
        text_pages = ocr_pdf(filepath)
    
    # 从文本行中匹配关键字
    for page_num, page_text in text_pages:
        lines = page_text.split('\n')
        for line_idx, line in enumerate(lines):
            line = line.strip()
            if not line:
                continue
            for kw in KEYWORDS:
                if kw in line:
                    # 排除已经从表格匹配的
                    amount = 0
                    m = re.search(r'([\d.]+)\s*万?元', line)
                    if m:
                        try:
                            amount = float(m.group(1))
                        except:
                            pass
                    matches.append({
                        'keyword': kw,
                        'content': line[:100],
                        'amount': round(amount, 2),
                        'location': f'第{page_num}页:行{line_idx+1}',
                        'file': os.path.basename(filepath)
                    })
                    break
    
    # 去重：相同关键字+内容只保留一条
    seen = set()
    deduped = []
    for m in matches:
        key = (m['keyword'], m['content'])
        if key not in seen:
            seen.add(key)
            deduped.append(m)
    
    return deduped

def ocr_pdf(filepath):
    """OCR扫描版PDF"""
    pages = []
    try:
        from pdf2image import convert_from_path
        import pytesseract
        from PIL import Image
        
        images = convert_from_path(filepath, dpi=200)
        for page_idx, img in enumerate(images):
            text = pytesseract.image_to_string(img, lang='chi_sim')
            pages.append((page_idx+1, text))
    except Exception as e:
        print(f'OCR失败: {filepath}: {e}')
    
    return pages

# ============ ET格式解析 ============
def analyze_et(filepath):
    """分析.wps .et格式"""
    # 先尝试openpyxl
    try:
        return analyze_excel(filepath)
    except:
        pass
    # 再尝试xlrd
    try:
        return analyze_xls(filepath)
    except:
        pass
    return []

# ============ 主函数 ============
def main():
    # 遍历所有文件
    all_files = []
    for root, dirs, files in os.walk(DATA_DIR):
        for f in files:
            if f.startswith('.') or f.startswith('_') or f.endswith('.json') or f.endswith('.html') or f.endswith('.py'):
                continue
            filepath = os.path.join(root, f)
            ext = os.path.splitext(f)[1].lower()
            if ext in ['.xlsx','.xls','.doc','.docx','.pdf','.et','.wps']:
                all_files.append(filepath)
    
    print(f'共{len(all_files)}个文件待分析')
    
    # 分析每个文件
    results = defaultdict(lambda: defaultdict(lambda: defaultdict(lambda: {'组织部': [], '党校': [], '其他': []})))
    # results[市州][区县][单位类型] = [匹配项列表]
    
    processed = 0
    for filepath in all_files:
        processed += 1
        if processed % 50 == 0:
            print(f'进度: {processed}/{len(all_files)}')
        
        ext = os.path.splitext(filepath)[1].lower()
        city, county, unit_type = classify_file(filepath)
        
        if not city:
            continue
        
        if not unit_type:
            # 从文件名再判断
            fname = os.path.basename(filepath)
            if '组织部' in fname:
                unit_type = '组织部'
            elif '党校' in fname:
                unit_type = '党校'
            else:
                unit_type = '其他'
        
        if not county:
            county = '未分类'
        
        # 按格式分析
        matches = []
        try:
            if ext == '.xlsx':
                matches = analyze_excel(filepath)
            elif ext == '.xls':
                matches = analyze_xls(filepath)
            elif ext == '.et' or ext == '.wps':
                matches = analyze_et(filepath)
            elif ext == '.docx':
                matches = analyze_word(filepath)
            elif ext == '.doc':
                matches = analyze_doc_old(filepath)
            elif ext == '.pdf':
                matches = analyze_pdf(filepath)
        except Exception as e:
            print(f'分析失败: {filepath}: {e}')
            continue
        
        if matches:
            rel_path = os.path.relpath(filepath, DATA_DIR)
            for m in matches:
                m['filepath'] = rel_path
            
            if city not in results:
                results[city] = {}
            if county not in results[city]:
                results[city][county] = {'组织部': [], '党校': [], '其他': []}
            if unit_type not in results[city][county]:
                unit_type = '其他'
            results[city][county][unit_type].extend(matches)
    
    print(f'分析完成，开始合并关键字...')
    
    # 合并相同关键字
    # results[市州][区县][单位类型] → 按关键字分组
    final = {}
    total_matches = 0
    total_amount = 0
    
    for city, counties in results.items():
        final[city] = {}
        for county, units in counties.items():
            final[city][county] = {}
            for unit_type, items in units.items():
                if not items:
                    continue
                # 按关键字合并
                merged = defaultdict(lambda: {'contents': [], 'amounts': [], 'locations': [], 'files': set()})
                for m in items:
                    kw = m['keyword']
                    merged[kw]['contents'].append(m['content'])
                    merged[kw]['amounts'].append(m['amount'])
                    merged[kw]['locations'].append(m['location'])
                    merged[kw]['files'].add(m.get('filepath', m.get('file','')))
                
                merged_list = []
                unit_total = 0
                for kw, data in merged.items():
                    kw_amount = sum(data['amounts'])
                    unit_total += kw_amount
                    # 合并内容（去重）
                    unique_contents = list(dict.fromkeys(data['contents']))
                    merged_list.append({
                        'keyword': kw,
                        'contents': unique_contents,
                        'amount': round(kw_amount, 2),
                        'locations': data['locations'],
                        'files': list(data['files'])
                    })
                    total_matches += 1
                    total_amount += kw_amount
                
                # 按金额降序排列
                merged_list.sort(key=lambda x: x['amount'], reverse=True)
                final[city][county][unit_type] = {
                    'items': merged_list,
                    'total_amount': round(unit_total, 2),
                    'file_count': len(set().union(*[d['files'] for d in merged.values()] if merged else [set()]))
                }
    
    # 保存结果
    # set转list用于JSON序列化
    def serialize(obj):
        if isinstance(obj, set):
            return list(obj)
        if isinstance(obj, dict):
            return {k: serialize(v) for k, v in obj.items()}
        if isinstance(obj, list):
            return [serialize(v) for v in obj]
        return obj
    
    with open(OUTPUT_JSON, 'w', encoding='utf-8') as f:
        json.dump(serialize(final), f, ensure_ascii=False, indent=2)
    
    print(f'\n=== 分析完成 ===')
    print(f'总匹配项（合并后）: {total_matches}')
    print(f'总金额: {total_amount:.2f}万元')
    print(f'结果保存到: {OUTPUT_JSON}')
    
    # 打印各市州统计
    for city in CITIES:
        if city in final:
            city_amount = 0
            city_items = 0
            for county, units in final[city].items():
                for unit_type, data in units.items():
                    if isinstance(data, dict) and 'items' in data:
                        city_amount += data.get('total_amount', 0)
                        city_items += len(data['items'])
            print(f'  {city}: {city_items}项, {city_amount:.2f}万元')

if __name__ == '__main__':
    main()