#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""四川区县预算文件分析 - 干净版
规则：
1. 逐行读取预算表 → 在项目名称列匹配17个关键字 → 旁边金额列就是预算金额
2. 同一区县同一单位类型下，相同关键字合并为一行，金额累加
3. 按市州→单位类型(组织部/党校)→区县 结构输出
4. 每条标注文件中精确位置，文件名可点击跳转
"""
import os, sys, json, re, subprocess, warnings
warnings.filterwarnings('ignore')

# ─── 配置 ───
ROOT = '/data/www/files/scQxYjs'
OUT_JSON = '/data/www/files/scQxYjs/_clean_results.json'
BASE_URL = 'http://192.168.99.133:5678/scQxYjs'

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

# ─── 工具函数 ───
def classify_unit(fname):
    fn = os.path.basename(fname).lower()
    if '党校' in fn or '_dx_' in fn or '_dx.' in fn:
        return '党校'
    if '组织' in fn or '_org_' in fn or '_org.' in fn:
        return '组织部'
    # 模糊判断
    parent = os.path.dirname(fname)
    if '党校' in parent.lower():
        return '党校'
    if '组织' in parent.lower():
        return '组织部'
    return '其他'

def get_city(path):
    parts = path.replace(ROOT, '').strip('/').split('/')
    return parts[0] if parts else '未知'

def get_county(path):
    fn = os.path.basename(path)
    # 从文件名提取区县名
    base = os.path.splitext(fn)[0]
    # 去掉常见后缀
    for suffix in ['2026年部门预算公开','2026年部门预算','2026预算公开','2026预算',
                   '部门预算公开','部门预算','预算公开','预算','公开']:
        base = base.replace(suffix, '')
    # 去掉"市"前缀（如"广元市苍溪县"->"苍溪县"）
    # 提取区县名
    m = re.search(r'([^\d]+?[县区市])', base)
    if m:
        county = m.group(1)
        # 去掉地级市名（如"广元市苍溪县"->"苍溪县"）
        if '市' in county and not county.endswith('市'):
            county = county.split('市')[-1]
        return county
    # 从父目录提取
    parts = path.replace(ROOT, '').strip('/').split('/')
    if len(parts) >= 2:
        d = parts[1]
        m2 = re.search(r'([^\d]+?[县区市])', d)
        if m2:
            c = m2.group(1)
            if '市' in c and not c.endswith('市'):
                c = c.split('市')[-1]
            return c
    return os.path.dirname(path).split('/')[-1] or '未知'

def match_keywords(text):
    """在文本中匹配关键字，返回匹配到的关键字列表"""
    if not text:
        return []
    text_lower = text.lower()
    matched = []
    for kw in KEYWORDS:
        if kw in text or kw.lower() in text_lower:
            matched.append(kw)
    return matched

def extract_amount(row_values, name_col_idx, total_cols=None):
    """从行数据中提取金额，优先取项目名称列旁边的金额列"""
    if total_cols is None:
        total_cols = []
    # 优先从已识别的金额列提取
    for col_idx in total_cols:
        if col_idx < len(row_values):
            v = row_values[col_idx]
            if v and isinstance(v, (int, float)) and v > 0:
                return round(float(v), 2)
            if v and isinstance(v, str):
                m = re.search(r'([\d.]+)\s*万?元?', v.strip())
                if m:
                    try:
                        val = float(m.group(1))
                        if 0 < val < 100000:
                            return round(val, 2)
                    except:
                        pass
    # 回退：扫描整行找金额数字
    for i, v in enumerate(row_values):
        if i == name_col_idx or not v:
            continue
        if isinstance(v, (int, float)) and v > 0:
            return round(float(v), 2)
        if isinstance(v, str):
            m = re.search(r'^([\d.]+)\s*万?元?$', v.strip())
            if m:
                try:
                    val = float(m.group(1))
                    if 0 < val < 100000:
                        return round(val, 2)
                except:
                    pass
    return 0.0

def find_amount_col(ws):
    """识别金额列：找表头中含'预算数'/'总计'/'合计'/'金额'的列"""
    total_cols = []
    for row in ws.iter_rows(min_row=1, max_row=min(10, ws.max_row), values_only=False):
        for cell in row:
            if cell.value and isinstance(cell.value, str):
                cv = cell.value.strip()
                if any(k in cv for k in ['预算数','预算','总计','合计','金额','小计','总预算']):
                    # 这一列就是金额列，记录列索引
                    total_cols.append(cell.column - 1)  # 0-indexed
    return list(set(total_cols))

def find_name_col(ws):
    """识别项目名称列：找表头中含'项目'/'科目'/'经济分类'/'支出'的列"""
    for row in ws.iter_rows(min_row=1, max_row=min(10, ws.max_row), values_only=False):
        for cell in row:
            if cell.value and isinstance(cell.value, str):
                cv = cell.value.strip()
                if any(k in cv for k in ['项目名称','项目','科目','经济分类','经济科目','支出功能分类','功能分类','支出','明细']):
                    return cell.column - 1  # 0-indexed
    # 回退：找最宽的文本列
    for row in ws.iter_rows(min_row=2, max_row=min(15, ws.max_row), values_only=True):
        max_len = 0
        max_idx = -1
        for i, v in enumerate(row):
            if v and isinstance(v, str) and len(v) > max_len:
                max_len = len(v)
                max_idx = i
        if max_idx >= 0:
            return max_idx
    return 1  # 默认B列(0-indexed=1)

# ─── Excel解析 ───
def analyze_excel(filepath):
    results = []
    try:
        import openpyxl
        wb = openpyxl.load_workbook(filepath, data_only=True, read_only=True)
        for ws in wb.worksheets:
            sheet_name = ws.title
            # 跳过绩效表
            if any(k in sheet_name for k in ['绩效','目标','指标']):
                continue
            # 识别列
            total_cols = find_amount_col(ws)
            name_col = find_name_col(ws)
            # 逐行扫描
            for row_idx, row in enumerate(ws.iter_rows(min_row=1, values_only=True), 1):
                if not row:
                    continue
                # 获取项目名称
                if name_col < len(row):
                    name_val = row[name_col]
                else:
                    continue
                if not name_val or not isinstance(name_val, str):
                    continue
                name_val = name_val.strip()
                if len(name_val) < 2:
                    continue
                # 匹配关键字
                matched = match_keywords(name_val)
                if not matched:
                    continue
                # 提取金额
                amount = extract_amount(row, name_col, total_cols)
                for kw in matched:
                    results.append({
                        'keyword': kw,
                        'content': name_val,
                        'location': f'Sheet[{sheet_name}] R{row_idx}',
                        'amount': amount
                    })
        wb.close()
    except Exception as e:
        # 尝试xlrd
        try:
            import xlrd
            wb = xlrd.open_workbook(filepath)
            for sheet in wb.sheets():
                sheet_name = sheet.name
                if any(k in sheet_name for k in ['绩效','目标','指标']):
                    continue
                for row_idx in range(sheet.nrows):
                    row = sheet.row_values(row_idx)
                    if not row:
                        continue
                    # 找名称列和金额列
                    name_val = None
                    name_col = 1
                    for i, v in enumerate(row):
                        if v and isinstance(v, str) and len(str(v).strip()) > 3:
                            name_val = str(v).strip()
                            name_col = i
                            break
                    if not name_val:
                        continue
                    matched = match_keywords(name_val)
                    if not matched:
                        continue
                    # 找金额
                    amount = 0.0
                    for i, v in enumerate(row):
                        if i == name_col:
                            continue
                        try:
                            val = float(v)
                            if 0 < val < 100000:
                                amount = round(val, 2)
                                break
                        except:
                            pass
                    for kw in matched:
                        results.append({
                            'keyword': kw,
                            'content': name_val,
                            'location': f'Sheet[{sheet_name}] R{row_idx+1}',
                            'amount': amount
                        })
        except Exception as e2:
            pass
    return results

# ─── PDF解析（pdftotext快速提取）───
def analyze_pdf(filepath):
    results = []
    try:
        # 用pdftotext快速提取文本（保留布局）
        proc = subprocess.run(['pdftotext', '-layout', filepath, '-'],
                            capture_output=True, text=True, timeout=30)
        text = proc.stdout
        if not text.strip():
            # 尝试不带layout
            proc = subprocess.run(['pdftotext', filepath, '-'],
                                capture_output=True, text=True, timeout=30)
            text = proc.stdout
        if not text.strip():
            return results  # 扫描版PDF，跳过
        
        lines = text.split('\n')
        for line_idx, line in enumerate(lines, 1):
            line = line.strip()
            if len(line) < 2:
                continue
            matched = match_keywords(line)
            if not matched:
                continue
            # 从行中提取金额
            amount = 0.0
            m = re.search(r'([\d.]+)\s*万?元', line)
            if m:
                try:
                    val = float(m.group(1))
                    if 0 < val < 100000:
                        amount = round(val, 2)
                except:
                    pass
            for kw in matched:
                results.append({
                    'keyword': kw,
                    'content': line[:100],  # 截断长行
                    'location': f'PDF P{line_idx}',
                    'amount': amount
                })
    except Exception as e:
        pass
    return results

# ─── Word解析 ───
def analyze_word(filepath):
    results = []
    try:
        # 先尝试docx
        from docx import Document
        doc = Document(filepath)
        for para_idx, para in enumerate(doc.paragraphs, 1):
            text = para.text.strip()
            if len(text) < 2:
                continue
            matched = match_keywords(text)
            if not matched:
                continue
            amount = 0.0
            m = re.search(r'([\d.]+)\s*万?元', text)
            if m:
                try:
                    val = float(m.group(1))
                    if 0 < val < 100000:
                        amount = round(val, 2)
                except:
                    pass
            for kw in matched:
                results.append({
                    'keyword': kw,
                    'content': text[:100],
                    'location': f'Para{para_idx}',
                    'amount': amount
                })
        # 表格
        for tbl_idx, tbl in enumerate(doc.tables, 1):
            for row_idx, row in enumerate(tbl.rows, 1):
                for cell_idx, cell in enumerate(row.cells, 1):
                    text = cell.text.strip()
                    if len(text) < 2:
                        continue
                    matched = match_keywords(text)
                    if not matched:
                        continue
                    amount = 0.0
                    # 找同行其他单元格的金额
                    for other_cell in row.cells:
                        m = re.search(r'^([\d.]+)\s*万?元?$', other_cell.text.strip())
                        if m:
                            try:
                                val = float(m.group(1))
                                if 0 < val < 100000:
                                    amount = round(val, 2)
                                    break
                            except:
                                pass
                    for kw in matched:
                        results.append({
                            'keyword': kw,
                            'content': text[:100],
                            'location': f'Table{tbl_idx} R{row_idx} C{cell_idx}',
                            'amount': amount
                        })
    except Exception as e:
        # .doc格式，用LibreOffice转换
        try:
            import tempfile
            tmpdir = tempfile.mkdtemp()
            subprocess.run(['libreoffice', '--headless', '--convert-to', 'txt',
                          '--outdir', tmpdir, filepath],
                         capture_output=True, timeout=30)
            txt_file = os.path.join(tmpdir, os.path.splitext(os.path.basename(filepath))[0] + '.txt')
            if os.path.exists(txt_file):
                with open(txt_file, 'r', encoding='utf-8', errors='ignore') as f:
                    lines = f.readlines()
                for line_idx, line in enumerate(lines, 1):
                    line = line.strip()
                    if len(line) < 2:
                        continue
                    matched = match_keywords(line)
                    if not matched:
                        continue
                    amount = 0.0
                    m = re.search(r'([\d.]+)\s*万?元', line)
                    if m:
                        try:
                            val = float(m.group(1))
                            if 0 < val < 100000:
                                amount = round(val, 2)
                        except:
                            pass
                    for kw in matched:
                        results.append({
                            'keyword': kw,
                            'content': line[:100],
                            'location': f'Line{line_idx}',
                            'amount': amount
                        })
        except:
            pass
    return results

# ─── ET格式（当xlsx处理）───
def analyze_et(filepath):
    return analyze_excel(filepath)

# ─── 主函数 ───
def main():
    # 收集所有文件
    all_files = []
    for root, dirs, files in os.walk(ROOT):
        if '/_' in root:
            continue
        for f in files:
            if f.startswith('_'):
                continue
            ext = os.path.splitext(f)[1].lower()
            if ext in ['.xlsx', '.xls', '.et', '.doc', '.docx', '.pdf']:
                fp = os.path.join(root, f)
                all_files.append(fp)
    
    print(f"共{len(all_files)}个文件")
    
    # 按格式统计
    from collections import Counter
    ext_counts = Counter(os.path.splitext(f)[1].lower() for f in all_files)
    print(f"格式分布: {dict(ext_counts)}")
    
    # 分析每个文件
    all_results = {}  # city -> unit_type -> county -> [matches]
    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()
        if ext in ['.xlsx', '.xls']:
            matches = analyze_excel(filepath)
        elif ext == '.pdf':
            matches = analyze_pdf(filepath)
        elif ext in ['.doc', '.docx']:
            matches = analyze_word(filepath)
        elif ext == '.et':
            matches = analyze_et(filepath)
        else:
            matches = []
        
        if not matches:
            continue
        
        # 分类
        city = get_city(filepath)
        unit_type = classify_unit(filepath)
        county = get_county(filepath)
        rel_path = filepath.replace(ROOT + '/', '')
        file_url = f"{BASE_URL}/{rel_path}"
        
        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] = []
        
        for m in matches:
            m['file_path'] = rel_path
            m['file_url'] = file_url
            all_results[city][unit_type][county].append(m)
    
    print(f"\n分析完成!")
    
    # 关键字合并：同一区县同一单位类型下，相同关键字合并
    for city in all_results:
        for ut in all_results[city]:
            for county in all_results[city][ut]:
                matches = all_results[city][ut][county]
                merged = {}
                for m in matches:
                    kw = m['keyword']
                    if kw not in merged:
                        merged[kw] = {
                            'keyword': kw,
                            'contents': [],
                            'locations': [],
                            'amount': 0.0,
                            'file_path': m['file_path'],
                            'file_url': m['file_url']
                        }
                    merged[kw]['contents'].append(m['content'])
                    merged[kw]['locations'].append(m['location'])
                    merged[kw]['amount'] += m['amount']
                    # 如果金额为0但有值，保留
                    if m['amount'] > 0 and merged[kw]['amount'] == 0:
                        merged[kw]['amount'] = m['amount']
                # 转换为列表
                merged_list = []
                for kw in merged:
                    item = merged[kw]
                    item['content_str'] = ' + '.join(list(dict.fromkeys(item['contents'])))[:200]
                    item['location_str'] = ', '.join(item['locations'][:5])
                    if len(item['locations']) > 5:
                        item['location_str'] += f' ...共{len(item["locations"])}处'
                    item['count'] = len(item['contents'])
                    del item['contents']
                    del item['locations']
                    merged_list.append(item)
                # 按金额降序
                merged_list.sort(key=lambda x: x['amount'], reverse=True)
                all_results[city][ut][county] = merged_list
    
    # 统计
    total_matches = 0
    total_amount = 0.0
    city_count = 0
    for city in all_results:
        city_count += 1
        for ut in all_results[city]:
            for county in all_results[city][ut]:
                for m in all_results[city][ut][county]:
                    total_matches += 1
                    total_amount += m['amount']
    
    print(f"市州数: {city_count}")
    print(f"总匹配项: {total_matches}")
    print(f"总金额: {total_amount:.2f}万元")
    
    # 写入JSON
    with open(OUT_JSON, 'w', encoding='utf-8') as f:
        json.dump(all_results, f, ensure_ascii=False, indent=2)
    print(f"结果已写入: {OUT_JSON}")

if __name__ == '__main__':
    main()