#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
完整分析脚本：递归遍历所有子文件夹，分析483个预算文件
- 17个关键字匹配
- 提取预算金额
- 按市州→区县→组织部/党校分类
- 相同关键字合并
"""

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

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

BASE = "/data/www/files/scQxYjs"

# ===== 文件分类 =====
def classify_unit(filename):
    """判断是组织部还是党校"""
    fn = filename.lower()
    if '党校' in filename or 'dx_' in fn or '_dx' in fn:
        return '党校'
    if '组织部' in filename or 'org_' in fn or '_org' in fn:
        return '组织部'
    return '其他'

def extract_county(city, subdir, filename):
    """从子文件夹名或文件名中提取区县名"""
    # 优先从子文件夹名提取
    if subdir:
        county = subdir
        # 去掉市州前缀
        for prefix in ['成都市','广元市','遂宁市','绵阳市','宜宾市','眉山市']:
            county = county.replace(prefix, '')
        county = county.replace('组织部、党校','').replace('组织部、党校/','').strip()
        return county
    
    # 从文件名提取
    name = filename
    # 去掉扩展名
    for ext in ['.xlsx','.xls','.docx','.doc','.pdf','.et']:
        name = name.replace(ext, '')
    
    # 常见区县名
    counties = [
        '龙泉驿','金堂','新津','彭州','新都','武侯','温江','青羊','青白江','郫都',
        '双流','锦江','金牛','成华','简阳','都江堰','邛崃','大邑','蒲江','崇州',
        '峨边','峨眉山','沐川','夹江','井研','犍为','金口河','沙湾',
        '高县','长宁','兴文','叙州','屏山','南溪','江安','翠屏',
        '平昌','通江','恩阳','南江',
        '宝兴','芦山','天全','石棉','汉源','名山','荥经','雨城',
        '顺庆','高坪','嘉陵','营山','蓬安','仪陇','西充','阆中',
        '理县','小金','金川','壤塘','马尔康','松潘','九寨沟','黑水','阿坝','若尔盖','红原',
        '理塘','巴塘','乡城','得荣','稻城','泸定','丹巴','九龙','雅江','道孚','炉霍','甘孜','新龙','德格','白玉','石渠','色达',
        '雷波','美姑','甘洛','越西','冕宁','喜德','昭觉','金阳','布拖','德昌','会东','会理','宁南','盐源','木里','西昌',
        '东区','米易','仁和','西区','盐边',
        '丹棱','青神','洪雅','仁寿','东坡','彭山',
        '乐至','雁江','安岳',
        '梓潼','北川','三台','平武','游仙','涪城','安州','江油',
        '朝天','剑阁','苍溪','青川','旺苍','利州','昭化',
        '前锋','华蓥','邻水','武胜','岳池',
        '纳溪','合江','叙永','古蔺','龙马潭','泸县',
        '万源','渠县','开江','宣汉','达川','通川',
        '船山','蓬溪','射洪','大英','安居',
        '隆昌','东兴','资中',
        '富顺','荣县','沿滩','大安','贡井','自流井',
        '绵竹','什邡','广汉','中江','罗江',
    ]
    for c in counties:
        if c in name:
            # 补全区县名
            if not name.endswith('区') and not name.endswith('县') and not name.endswith('市'):
                return c
            return c
    
    return name

def collect_all_files():
    """递归收集所有文件，返回 [(city, county, unit_type, filepath, filename), ...]"""
    results = []
    for city in sorted(os.listdir(BASE)):
        city_path = os.path.join(BASE, city)
        if not os.path.isdir(city_path):
            continue
        for item in sorted(os.listdir(city_path)):
            item_path = os.path.join(city_path, item)
            if os.path.isdir(item_path):
                # 子文件夹（区县）
                county = extract_county(city, item, "")
                # 递归遍历子文件夹
                for root, dirs, files in os.walk(item_path):
                    for f in files:
                        fp = os.path.join(root, f)
                        unit = classify_unit(f)
                        # 如果文件名中有更具体的区县名，用它
                        file_county = extract_county(city, "", f)
                        # 优先用文件名中的区县名，否则用子文件夹名
                        final_county = file_county if file_county else county
                        results.append((city, final_county, unit, fp, f))
            else:
                # 直接文件
                unit = classify_unit(item)
                county = extract_county(city, "", item)
                results.append((city, county, unit, item_path, item))
    return results

# ===== Excel解析 =====
def analyze_excel(filepath):
    results = []
    try:
        from openpyxl import load_workbook
        wb = load_workbook(filepath, data_only=True, read_only=True)
        for ws in wb.worksheets:
            sheet_name = ws.title
            rows = list(ws.iter_rows(values_only=False))
            if not rows:
                continue
            # 找表头行，确定项目名称列和金额列
            header_row_idx = None
            name_col = None
            amount_col = None
            for i, row in enumerate(rows[:10]):
                vals = [str(c.value) if c.value else '' for c in row]
                for j, v in enumerate(vals):
                    v_clean = v.replace('\xa0','').strip()
                    if v_clean in ['项目名称','项目','支出项目','经济科目','科目名称','预算科目']:
                        name_col = j
                        header_row_idx = i
                    if v_clean in ['总计','预算数','预算金额','金额','合计','预算数（万元）','本年预算','预算安排']:
                        amount_col = j
                    # 也匹配"XX预算"
                    if '预算' in v_clean and amount_col is None and '科目' not in v_clean:
                        amount_col = j
            
            # 如果没找到表头，默认B列=项目名，F列=金额
            if name_col is None:
                name_col = 1  # B列
            if amount_col is None:
                # 尝试找数字最多的列
                for j in range(2, min(15, len(rows[0]) if rows else 0)):
                    num_count = 0
                    for row in rows[5:30]:
                        if j < len(row):
                            v = row[j].value
                            if isinstance(v, (int, float)) and v > 0:
                                num_count += 1
                    if num_count > 3:
                        amount_col = j
                        break
            if amount_col is None:
                amount_col = 5  # F列
            
            # 遍历数据行
            for i, row in enumerate(rows):
                if i <= (header_row_idx or 0):
                    continue
                if name_col >= len(row):
                    continue
                cell_val = row[name_col].value
                if not cell_val:
                    continue
                cell_str = str(cell_val).replace('\xa0','').strip()
                if not cell_str or len(cell_str) < 2:
                    continue
                
                # 匹配关键字
                for kw in KEYWORDS:
                    if kw in cell_str:
                        # 提取金额
                        amount = 0.0
                        if amount_col < len(row):
                            av = row[amount_col].value
                            if isinstance(av, (int, float)):
                                amount = float(av)
                            elif isinstance(av, str):
                                nums = re.findall(r'[\d.]+', av.replace(',',''))
                                if nums:
                                    try:
                                        amount = float(nums[0])
                                    except:
                                        pass
                        # 排除明显异常值（>100万或<0）
                        if amount > 10000:
                            amount = 0.0
                        if amount < 0:
                            amount = 0.0
                        
                        results.append({
                            'keyword': kw,
                            'content': cell_str[:100],
                            'amount': round(amount, 2),
                            'location': f'Sheet[{sheet_name}] R{i+1}'
                        })
                        break  # 一个单元格只匹配一个关键字
        wb.close()
    except Exception as e:
        # 尝试xlrd
        try:
            import xlrd
            wb = xlrd.open_workbook(filepath)
            for ws in wb.sheets():
                sheet_name = ws.name
                for i in range(ws.nrows):
                    row = ws.row_values(i)
                    name_col = 1
                    amount_col = 5
                    if name_col < len(row):
                        cell_str = str(row[name_col]).strip()
                        if not cell_str or len(cell_str) < 2:
                            continue
                        for kw in KEYWORDS:
                            if kw in cell_str:
                                amount = 0.0
                                if amount_col < len(row):
                                    try:
                                        amount = float(row[amount_col])
                                    except:
                                        pass
                                if amount > 10000:
                                    amount = 0.0
                                results.append({
                                    'keyword': kw,
                                    'content': cell_str[:100],
                                    'amount': round(amount, 2),
                                    'location': f'Sheet[{sheet_name}] R{i+1}'
                                })
                                break
        except Exception as e2:
            print(f"  ⚠ Excel解析失败: {os.path.basename(filepath)} - {e2}", file=sys.stderr)
    return results

# ===== Word解析 =====
def analyze_word(filepath):
    results = []
    tmp_file = None
    try:
        ext = os.path.splitext(filepath)[1].lower()
        if ext == '.doc':
            # 转换doc为docx
            tmp_dir = '/tmp/doc_convert'
            os.makedirs(tmp_dir, exist_ok=True)
            tmp_file = os.path.join(tmp_dir, os.path.basename(filepath) + 'x')
            try:
                subprocess.run(['libreoffice', '--headless', '--convert-to', 'docx',
                              '--outdir', tmp_dir, filepath],
                             capture_output=True, timeout=30)
                if os.path.exists(tmp_file):
                    filepath = tmp_file
            except:
                pass
        
        from docx import Document
        doc = Document(filepath)
        
        # 解析表格
        for ti, table in enumerate(doc.tables):
            for ri, row in enumerate(table.rows):
                cells = [cell.text.strip() for cell in row.cells]
                for kw in KEYWORDS:
                    for ci, cell_text in enumerate(cells):
                        if kw in cell_text and len(cell_text) > 1:
                            # 在同行找金额
                            amount = 0.0
                            for ci2, ct in enumerate(cells):
                                if ci2 == ci:
                                    continue
                                nums = re.findall(r'[\d,.]+', ct)
                                for n in nums:
                                    try:
                                        v = float(n.replace(',',''))
                                        if 0 < v < 10000:
                                            amount = v
                                            break
                                    except:
                                        pass
                                if amount > 0:
                                    break
                            
                            results.append({
                                'keyword': kw,
                                'content': cell_text[:100],
                                'amount': round(amount, 2),
                                'location': f'Table{ti+1} R{ri+1}'
                            })
                            break
        
        # 解析段落
        for pi, para in enumerate(doc.paragraphs):
            text = para.text.strip()
            if not text or len(text) < 2:
                continue
            for kw in KEYWORDS:
                if kw in text:
                    # 找金额
                    amount = 0.0
                    nums = re.findall(r'([\d.]+)\s*万?元', text)
                    if nums:
                        try:
                            amount = float(nums[0])
                        except:
                            pass
                    if amount > 10000:
                        amount = 0.0
                    results.append({
                        'keyword': kw,
                        'content': text[:100],
                        'amount': round(amount, 2),
                        'location': f'Para R{pi+1}'
                    })
                    break
    except Exception as e:
        print(f"  ⚠ Word解析失败: {os.path.basename(filepath)} - {e}", file=sys.stderr)
    finally:
        if tmp_file and os.path.exists(tmp_file):
            try:
                os.remove(tmp_file)
            except:
                pass
    return results

# ===== PDF解析 =====
def analyze_pdf(filepath):
    results = []
    try:
        # 先用pdftotext快速提取
        result = subprocess.run(['pdftotext', '-layout', filepath, '-'],
                              capture_output=True, timeout=30, text=True)
        text = result.stdout
        if not text or len(text) < 50:
            # 尝试pdfplumber
            try:
                import pdfplumber
                with pdfplumber.open(filepath) as pdf:
                    for pi, page in enumerate(pdf.pages[:50]):
                        page_text = page.extract_text() or ''
                        text += f'\n--- Page {pi+1} ---\n' + page_text
            except:
                pass
        
        if not text:
            return results
        
        lines = text.split('\n')
        for li, line in enumerate(lines):
            line = line.strip()
            if not line or len(line) < 2:
                continue
            for kw in KEYWORDS:
                if kw in line:
                    # 提取金额：找行末尾的数字（万元）
                    amount = 0.0
                    # 优先找 X.XX万元 或 X.XX万
                    m = re.findall(r'([\d.]+)\s*万', line)
                    if m:
                        try:
                            amount = float(m[-1])
                        except:
                            pass
                    if amount == 0:
                        # 找行末尾最后一个数字
                        nums = re.findall(r'([\d,.]+)', line)
                        for n in reversed(nums):
                            try:
                                v = float(n.replace(',',''))
                                if 0 < v < 10000:
                                    amount = v
                                    break
                            except:
                                pass
                    
                    if amount > 10000:
                        amount = 0.0
                    
                    # 计算页码
                    page_num = 1
                    for li2 in range(li, -1, -1):
                        if '--- Page' in lines[li2]:
                            m2 = re.search(r'Page (\d+)', lines[li2])
                            if m2:
                                page_num = int(m2.group(1))
                            break
                    
                    results.append({
                        'keyword': kw,
                        'content': line[:120],
                        'amount': round(amount, 2),
                        'location': f'PDF p.{page_num} L{li+1}'
                    })
                    break
    except Exception as e:
        print(f"  ⚠ PDF解析失败: {os.path.basename(filepath)} - {e}", file=sys.stderr)
    return results

# ===== 主分析逻辑 =====
def main():
    print("=== 开始完整分析 ===", flush=True)
    
    all_files = collect_all_files()
    print(f"总文件数: {len(all_files)}", flush=True)
    
    # 统计格式分布
    ext_counts = {}
    for _, _, _, _, fn in all_files:
        ext = os.path.splitext(fn)[1].lower()
        ext_counts[ext] = ext_counts.get(ext, 0) + 1
    print(f"格式分布: {ext_counts}", flush=True)
    
    # 分析每个文件
    all_results = {}  # {市州: {单位类型: {区县: [匹配项]}}}
    processed = 0
    errors = 0
    
    for city, county, unit_type, filepath, filename in all_files:
        processed += 1
        if processed % 20 == 0:
            print(f"进度: {processed}/{len(all_files)}", flush=True)
        
        try:
            ext = os.path.splitext(filepath)[1].lower()
            if ext in ['.xlsx', '.xls', '.et']:
                items = analyze_excel(filepath)
            elif ext in ['.doc', '.docx']:
                items = analyze_word(filepath)
            elif ext == '.pdf':
                items = analyze_pdf(filepath)
            else:
                continue
            
            if not items:
                continue
            
            # 添加文件链接
            rel_path = os.path.relpath(filepath, BASE)
            file_url = f"/scQxYjs/{rel_path}"
            
            for item in items:
                item['file'] = filename
                item['file_url'] = file_url
            
            # 写入结果
            if city not in all_results:
                all_results[city] = {}
            if unit_type not in all_results[city]:
                all_results[city][unit_type] = {}
            if county not in all_results[city][unit_type]:
                all_results[city][unit_type][county] = []
            
            all_results[city][unit_type][county].extend(items)
            
        except Exception as e:
            errors += 1
            print(f"  ⚠ 错误: {filename} - {e}", flush=True)
    
    print(f"\n=== 分析完成 ===", flush=True)
    print(f"已处理: {processed}, 错误: {errors}", flush=True)
    
    # 合并相同关键字
    total_items = 0
    total_amount = 0.0
    for city in all_results:
        for ut in all_results[city]:
            for county in all_results[city][ut]:
                items = all_results[city][ut][county]
                # 按关键字分组
                merged = {}
                for item in items:
                    kw = item['keyword']
                    if kw not in merged:
                        merged[kw] = {
                            'keyword': kw,
                            'contents': [],
                            'amount': 0,
                            'locations': [],
                            'files': [],
                            'file_urls': []
                        }
                    merged[kw]['contents'].append(item['content'])
                    merged[kw]['amount'] += item['amount']
                    merged[kw]['locations'].append(item['location'])
                    if item['file'] not in merged[kw]['files']:
                        merged[kw]['files'].append(item['file'])
                        merged[kw]['file_urls'].append(item['file_url'])
                
                merged_list = list(merged.values())
                for m in merged_list:
                    m['amount'] = round(m['amount'], 2)
                    m['count'] = len(m['contents'])
                    m['contents_str'] = '；'.join(m['contents'][:5])
                    m['locations_str'] = '，'.join(m['locations'][:8])
                    m['files_str'] = '；'.join(m['files'])
                    m['file_urls_str'] = '；'.join(m['file_urls'])
                    total_items += 1
                    total_amount += m['amount']
                
                all_results[city][ut][county] = merged_list
    
    # 统计
    city_count = len(all_results)
    county_count = sum(len(ut_data.get('组织部',{})) + len(ut_data.get('党校',{})) 
                      for ut_data in all_results.values())
    
    print(f"市州数: {city_count}", flush=True)
    print(f"区县-单位组合数: {county_count}", flush=True)
    print(f"合并后匹配项: {total_items}", flush=True)
    print(f"总金额: {total_amount:.2f}万元", flush=True)
    
    # 保存结果
    output_path = os.path.join(BASE, "_complete_results.json")
    with open(output_path, 'w', encoding='utf-8') as f:
        json.dump(all_results, f, ensure_ascii=False, indent=2)
    print(f"\n结果已保存: {output_path}", flush=True)

if __name__ == '__main__':
    main()