#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
完整分析脚本v2 - 后台运行版
改进：
1. .doc文件用LibreOffice转.docx再解析
2. PDF先pdfplumber，提取不到再OCR
3. 逐文件处理，每个文件处理完立即写入结果
4. 关键字合并：同一区县同一单位类型下相同关键字合并一行
"""

import os, re, json, sys, subprocess, traceback

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

BASE_DIR = '/data/www/files/scQxYjs'
OUTPUT_FILE = os.path.join(BASE_DIR, 'final_results.json')
PROGRESS_FILE = os.path.join(BASE_DIR, '_progress.txt')

EXCLUDE_SHEETS = ['封面', '绩效', '目标', '说明']

def log(msg):
    print(msg, flush=True)
    with open(PROGRESS_FILE, 'a', encoding='utf-8') as f:
        f.write(msg + '\n')

def should_process_sheet(sheet_name):
    for ex in EXCLUDE_SHEETS:
        if ex in sheet_name:
            return False
    return True

def classify_unit(filepath):
    fn = os.path.basename(filepath)
    path = filepath.replace(BASE_DIR, '')
    
    if '党校' in fn or '觉校' in fn:
        return '党校'
    if '组织' in fn:
        return '组织部'
    
    for part in path.split('/'):
        if '党校' in part or '觉校' in part:
            return '党校'
        if '组织' in part:
            return '组织部'
    return '其他'

def extract_county(filepath):
    rel = os.path.relpath(filepath, BASE_DIR)
    parts = rel.split('/')
    if len(parts) >= 2:
        county = parts[1]
        county = county.replace('组织部、党校', '').strip()
        # 去掉文件名部分（有些路径直接是文件）
        if '.' in county:
            county = parts[0] + '直属'
        return county
    return '未知'

def extract_city(filepath):
    rel = os.path.relpath(filepath, BASE_DIR)
    return rel.split('/')[0]

def try_float(val):
    if val is None:
        return 0.0
    if isinstance(val, (int, float)):
        return float(val)
    s = str(val).strip().replace(' ', '').replace('\xa0', '')
    s = re.sub(r'[^\d.]', '', s)
    if not s:
        return 0.0
    try:
        return float(s)
    except:
        return 0.0

def convert_doc_to_docx(filepath):
    """用LibreOffice将.doc转为.docx"""
    out_dir = '/tmp/doc_convert'
    os.makedirs(out_dir, exist_ok=True)
    try:
        result = subprocess.run(
            ['libreoffice', '--headless', '--convert-to', 'docx', '--outdir', out_dir, filepath],
            capture_output=True, text=True, timeout=30
        )
        basename = os.path.splitext(os.path.basename(filepath))[0]
        docx_path = os.path.join(out_dir, basename + '.docx')
        if os.path.exists(docx_path):
            return docx_path
    except:
        pass
    return None

def analyze_excel(filepath):
    results = []
    try:
        import openpyxl
        wb = openpyxl.load_workbook(filepath, data_only=True)
        
        for sheet_name in wb.sheetnames:
            if not should_process_sheet(sheet_name):
                continue
            
            ws = wb[sheet_name]
            if ws.max_row < 3 or ws.max_column < 2:
                continue
            
            # 逐行逐列扫描，在项目名称中匹配关键字
            for r in range(1, ws.max_row + 1):
                for c in range(1, ws.max_column + 1):
                    val = ws.cell(r, c).value
                    if val is None:
                        continue
                    val_str = str(val).strip().replace('\xa0', '').strip()
                    if not val_str or val_str == 'None' or len(val_str) < 2:
                        continue
                    
                    # 跳过纯数字行
                    if try_float(val_str) > 0 and len(val_str) < 5:
                        continue
                    
                    for kw in KEYWORDS:
                        if kw in val_str:
                            # 在同行其他列找金额
                            amount = 0.0
                            for ac in range(1, ws.max_column + 1):
                                if ac == c:
                                    continue
                                av = ws.cell(r, ac).value
                                if av is not None:
                                    fv = try_float(av)
                                    if fv > 0 and fv < 1000000:  # 合理金额范围
                                        amount = fv
                                        break
                            
                            col_letter = openpyxl.utils.get_column_letter(c)
                            results.append({
                                'keyword': kw,
                                'content': val_str[:80],
                                'sheet': sheet_name,
                                'row': r,
                                'col': f'{col_letter}列',
                                'amount': round(amount, 2)
                            })
                            break  # 一个单元格只匹配第一个关键字
        
        wb.close()
    except Exception as e:
        log(f"  [ERROR] Excel: {e}")
    
    return results

def analyze_pdf(filepath):
    results = []
    try:
        import pdfplumber
        
        with pdfplumber.open(filepath) as pdf:
            for page_num, page in enumerate(pdf.pages, 1):
                # 先尝试表格
                tables = page.extract_tables()
                if tables:
                    for table in tables:
                        for row_idx, row in enumerate(table):
                            if not row:
                                continue
                            for cell_idx, cell in enumerate(row):
                                if cell is None:
                                    continue
                                cell_str = str(cell).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_idx, other_cell in enumerate(row):
                                            if other_idx == cell_idx or other_cell is None:
                                                continue
                                            fv = try_float(other_cell)
                                            if 0 < fv < 1000000:
                                                amount = fv
                                                break
                                        
                                        results.append({
                                            'keyword': kw,
                                            'content': cell_str[:80],
                                            'sheet': f'第{page_num}页',
                                            'row': row_idx + 1,
                                            'col': f'第{cell_idx+1}列',
                                            'amount': round(amount, 2)
                                        })
                                        break
                else:
                    # 文本提取
                    text = page.extract_text()
                    if not text:
                        continue
                    
                    for line_idx, line in enumerate(text.split('\n')):
                        line = line.strip()
                        if not line or len(line) < 3:
                            continue
                        
                        for kw in KEYWORDS:
                            if kw in line:
                                amount = 0.0
                                m = re.search(r'(\d+\.?\d*)\s*万?元', line)
                                if m:
                                    amount = float(m.group(1))
                                
                                results.append({
                                    'keyword': kw,
                                    'content': line[:80],
                                    'sheet': f'第{page_num}页',
                                    'row': line_idx + 1,
                                    'col': '文本',
                                    'amount': round(amount, 2)
                                })
                                break
    except Exception as e:
        log(f"  [ERROR] PDF: {e}")
    
    return results

def analyze_docx(filepath):
    results = []
    try:
        from docx import Document
        doc = Document(filepath)
        
        # 表格
        for tidx, table in enumerate(doc.tables):
            for ridx, row in enumerate(table.rows):
                cells = [cell.text.strip() for cell in row.cells]
                for cidx, ctext in enumerate(cells):
                    if not ctext or len(ctext) < 2:
                        continue
                    for kw in KEYWORDS:
                        if kw in ctext:
                            amount = 0.0
                            for oidx, otext in enumerate(cells):
                                if oidx == cidx:
                                    continue
                                fv = try_float(otext)
                                if 0 < fv < 1000000:
                                    amount = fv
                                    break
                            
                            results.append({
                                'keyword': kw,
                                'content': ctext[:80],
                                'sheet': f'表格{tidx+1}',
                                'row': ridx + 1,
                                'col': f'第{cidx+1}列',
                                'amount': round(amount, 2)
                            })
                            break
        
        # 段落
        for pidx, para in enumerate(doc.paragraphs):
            text = para.text.strip()
            if not text or len(text) < 3:
                continue
            for kw in KEYWORDS:
                if kw in text:
                    amount = 0.0
                    m = re.search(r'(\d+\.?\d*)\s*万?元', text)
                    if m:
                        amount = float(m.group(1))
                    
                    results.append({
                        'keyword': kw,
                        'content': text[:80],
                        'sheet': '段落',
                        'row': pidx + 1,
                        'col': '文本',
                        'amount': round(amount, 2)
                    })
                    break
    except Exception as e:
        log(f"  [ERROR] DOCX: {e}")
    
    return results

def analyze_file(filepath):
    ext = os.path.splitext(filepath)[1].lower()
    
    if ext in ['.xlsx', '.xls', '.et']:
        return analyze_excel(filepath)
    elif ext == '.pdf':
        results = analyze_pdf(filepath)
        if not results:
            # 尝试OCR
            try:
                from pdf2image import convert_from_path
                import pytesseract
                images = convert_from_path(filepath, dpi=200)
                for pn, img in enumerate(images, 1):
                    text = pytesseract.image_to_string(img, lang='chi_sim')
                    for li, line in enumerate(text.split('\n')):
                        line = line.strip()
                        if not line or len(line) < 3:
                            continue
                        for kw in KEYWORDS:
                            if kw in line:
                                amount = 0.0
                                m = re.search(r'(\d+\.?\d*)\s*万?元', line)
                                if m:
                                    amount = float(m.group(1))
                                results.append({
                                    'keyword': kw,
                                    'content': line[:80],
                                    'sheet': f'第{pn}页(OCR)',
                                    'row': li + 1,
                                    'col': '文本',
                                    'amount': round(amount, 2)
                                })
                                break
            except Exception as e:
                log(f"  [ERROR] OCR: {e}")
        return results
    elif ext == '.docx':
        return analyze_docx(filepath)
    elif ext == '.doc':
        # .doc先转.docx
        docx_path = convert_doc_to_docx(filepath)
        if docx_path:
            return analyze_docx(docx_path)
        return []
    else:
        return []

def main():
    # 清空进度文件
    with open(PROGRESS_FILE, 'w', encoding='utf-8') as f:
        f.write('')
    
    # 收集文件
    all_files = []
    for root, dirs, files in os.walk(BASE_DIR):
        for f in files:
            if f.startswith('.') or f.startswith('_') or f.startswith('full_') or f.startswith('batch'):
                continue
            ext = os.path.splitext(f)[1].lower()
            if ext in ['.xlsx', '.xls', '.et', '.pdf', '.doc', '.docx']:
                all_files.append(os.path.join(root, f))
    
    log(f"共找到 {len(all_files)} 个预算文件")
    
    data = {}
    total_matches = 0
    processed = 0
    
    for filepath in all_files:
        processed += 1
        filename = os.path.basename(filepath)
        city = extract_city(filepath)
        county = extract_county(filepath)
        unit_type = classify_unit(filepath)
        
        if unit_type == '其他':
            continue
        
        matches = analyze_file(filepath)
        
        if not matches:
            if processed % 50 == 0:
                log(f"[{processed}/{len(all_files)}] 已处理，累计匹配{total_matches}项")
            continue
        
        # 按关键字合并
        merged = {}
        for m in matches:
            kw = m['keyword']
            if kw not in merged:
                merged[kw] = {
                    'keyword': kw,
                    'contents': [],
                    'amount': 0.0,
                    'locations': []
                }
            merged[kw]['contents'].append(m['content'])
            merged[kw]['amount'] += m['amount']
            merged[kw]['locations'].append(f"{m['sheet']}/R{m['row']}/{m['col']}")
        
        merged_list = list(merged.values())
        for item in merged_list:
            item['amount'] = round(item['amount'], 2)
            item['contents'] = ' + '.join(item['contents'][:5])
            item['locations'] = ' | '.join(item['locations'][:5])
        
        total_amount = round(sum(item['amount'] for item in merged_list), 2)
        
        if city not in data:
            data[city] = {}
        if unit_type not in data[city]:
            data[city][unit_type] = {}
        data[city][unit_type][county] = {
            'filename': filename,
            'filepath': filepath,
            'total_amount': total_amount,
            'match_count': len(merged_list),
            'matches': merged_list
        }
        
        total_matches += len(merged_list)
        
        if processed % 20 == 0:
            log(f"[{processed}/{len(all_files)}] {city}/{county}/{unit_type}: {len(matches)}匹配 → 合并{len(merged_list)}项, 累计{total_matches}项")
            # 定期保存
            with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:
                json.dump(data, f, ensure_ascii=False, indent=2)
    
    # 最终保存
    with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2)
    
    log(f"\n{'='*60}")
    log(f"分析完成！文件:{len(all_files)} 匹配项:{total_matches}")
    for city in sorted(data.keys()):
        org = len(data[city].get('组织部', {}))
        dx = len(data[city].get('党校', {}))
        org_amt = sum(data[city]['组织部'][c]['total_amount'] for c in data[city].get('组织部', {}))
        dx_amt = sum(data[city]['党校'][c]['total_amount'] for c in data[city].get('党校', {}))
        log(f"  {city}: 组织部{org}区县/{org_amt:.2f}万, 党校{dx}区县/{dx_amt:.2f}万")

if __name__ == '__main__':
    main()