import os
import sys
import json
from pathlib import Path
import openpyxl

base_dir = Path(r"C:\antigra-DS\CLAW_LAM_VIEC\ERP\CAUHOIKHAOSATMS\Tra Loi")
output_dir = Path(r"C:\antigra-DS\CLAW_LAM_VIEC\ERP\CAUHOIKHAOSATMS\_extracted")
output_dir.mkdir(exist_ok=True)

def extract_excel(filepath):
    """Extract all sheets from an Excel file into a structured dict."""
    try:
        wb = openpyxl.load_workbook(filepath, data_only=True)
        sheets = {}
        for sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            rows = []
            for row in ws.iter_rows(min_row=1, values_only=True):
                # Filter out completely empty rows
                if any(cell is not None for cell in row):
                    rows.append([str(cell) if cell is not None else "" for cell in row])
            sheets[sheet_name] = rows
        wb.close()
        return sheets
    except Exception as e:
        return {"__error__": str(e)}

all_data = {}

for dept_dir in sorted(base_dir.iterdir()):
    if dept_dir.is_dir():
        dept_name = dept_dir.name
        all_data[dept_name] = {}
        print(f"Processing: {dept_name}")
        for f in sorted(dept_dir.iterdir()):
            if f.suffix.lower() in ['.xlsx', '.xlsm'] and not f.name.startswith('~$'):
                print(f"  - {f.name}")
                sheets = extract_excel(f)
                all_data[dept_name][f.name] = sheets

# Save as JSON for easy reading
output_file = output_dir / "all_extracted.json"
with open(output_file, 'w', encoding='utf-8') as f:
    json.dump(all_data, f, ensure_ascii=False, indent=2)

print(f"\nDone! Output saved to: {output_file}")

# Also save per-department text summaries
for dept_name, files in all_data.items():
    dept_out = output_dir / f"{dept_name}.txt"
    with open(dept_out, 'w', encoding='utf-8') as f:
        f.write(f"=== PHÒNG BAN: {dept_name} ===\n\n")
        for filename, sheets in files.items():
            f.write(f"\n--- FILE: {filename} ---\n")
            for sheet_name, rows in sheets.items():
                f.write(f"\n  [{sheet_name}] ({len(rows)} dòng)\n")
                for i, row in enumerate(rows):
                    f.write(f"    R{i+1}: {' | '.join(row)}\n")
    print(f"Written: {dept_out}")

print("\nAll done!")