"""
Template Engine - Xử lý 3 loại file: Excel, PDF, Word.

Mỗi loại file có cách xử lý riêng:
- Excel (.xlsx): đọc cell address (A1, B2...), mapping chính xác, xuất Excel
- PDF (.pdf): extract text blocks + vị trí, thay text, xuất PDF
- Word (.docx): đọc paragraphs + tables, thay text, xuất Word

Luồng:
1. Upload file → detect loại → parse cấu trúc
2. Trả về danh sách "editable items" để frontend hiển thị
3. User chọn item → gán field key
4. Khi tạo cert: đọc file gốc → thay giá trị → xuất file cùng định dạng
"""

import os
import re
import json
import shutil
from datetime import datetime
from copy import deepcopy

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
TEMPLATE_DIR = os.path.join(BASE_DIR, 'visual_templates')
TEMPLATE_FILES_DIR = os.path.join(TEMPLATE_DIR, 'files')  # Lưu file gốc
TEMPLATE_IMAGES_DIR = os.path.join(TEMPLATE_DIR, 'images')  # Lưu ảnh preview
CONFIGS_FILE = os.path.join(BASE_DIR, 'visual_template_configs.json')


def _load_configs():
    from db import load_json_file
    data = load_json_file('visual_template_configs.json')
    return data if isinstance(data, list) else []


def _save_configs(configs):
    from db import save_json_file
    save_json_file('visual_template_configs.json', configs)


def detect_file_type(filepath):
    """Detect file type from extension. Returns 'excel', 'pdf', 'word' or raises error."""
    ext = os.path.splitext(filepath)[1].lower()
    if ext in ('.xlsx', '.xls'):
        return 'excel'
    elif ext == '.pdf':
        return 'pdf'
    elif ext in ('.docx', '.doc'):
        return 'word'
    else:
        raise ValueError(f'Không hỗ trợ định dạng {ext}. Chỉ chấp nhận Excel (.xlsx), PDF (.pdf), Word (.docx)')


# ============================================================
# PHẦN 1: PARSE - Đọc cấu trúc file, trả về editable items
# ============================================================

def parse_file(filepath):
    """Parse file và trả về cấu trúc để frontend hiển thị.

    Returns: {
        'file_type': 'excel' | 'pdf' | 'word',
        'preview_images': ['/visual_templates/images/xxx.png', ...],
        'sheets': [  # Excel only
            {'name': 'Sheet1', 'cells': [
                {'address': 'A1', 'row': 1, 'col': 1, 'value': 'Customer:', 'is_label': True},
                {'address': 'B1', 'row': 1, 'col': 2, 'value': 'MECSU JOINT STOCK', 'is_label': False},
            ]}
        ],
        'text_blocks': [  # PDF only
            {'id': 0, 'text': 'CERTIFICATE OF INSPECTION', 'x': 200, 'y': 100, 'page': 0, 'is_label': True},
            {'id': 1, 'text': '16X60', 'x': 300, 'y': 250, 'page': 0, 'is_label': False},
        ],
        'elements': [  # Word only
            {'id': 0, 'type': 'paragraph', 'text': 'Certificate No: 202406191355', 'para_idx': 0},
            {'id': 1, 'type': 'table_cell', 'text': 'MECSU', 'table_idx': 0, 'row': 0, 'col': 1},
        ]
    }
    """
    file_type = detect_file_type(filepath)

    os.makedirs(TEMPLATE_FILES_DIR, exist_ok=True)
    os.makedirs(TEMPLATE_IMAGES_DIR, exist_ok=True)

    basename = os.path.splitext(os.path.basename(filepath))[0]
    basename = re.sub(r'[^\w\-.]', '_', basename)

    if file_type == 'excel':
        return _parse_excel(filepath, basename)
    elif file_type == 'pdf':
        return _parse_pdf(filepath, basename)
    elif file_type == 'word':
        return _parse_word(filepath, basename)


def _parse_excel(filepath, basename):
    """Parse Excel: đọc tất cả cells với address, value, formatting."""
    from openpyxl import load_workbook
    from openpyxl.utils import get_column_letter

    wb = load_workbook(filepath, data_only=True)
    sheets = []
    preview_images = []

    for sheet_idx, ws in enumerate(wb.worksheets):
        max_col = min(ws.max_column or 1, 26)
        max_row = min(ws.max_row or 1, 100)

        cells = []
        for row in range(1, max_row + 1):
            for col in range(1, max_col + 1):
                cell = ws.cell(row=row, column=col)
                if cell.value is not None and str(cell.value).strip():
                    value = str(cell.value).strip()
                    address = f"{get_column_letter(col)}{row}"

                    # Detect if this cell is a label (typically left column, bold, or ends with :)
                    is_label = False
                    if value.endswith(':') or value.endswith('：'):
                        is_label = True
                    elif cell.font and cell.font.bold:
                        is_label = True

                    # Get merged cell info
                    merged = None
                    for merge_range in ws.merged_cells.ranges:
                        if cell.coordinate in merge_range:
                            merged = str(merge_range)
                            break

                    cells.append({
                        'address': address,
                        'row': row,
                        'col': col,
                        'col_letter': get_column_letter(col),
                        'value': value,
                        'is_label': is_label,
                        'merged': merged,
                    })

        # Collect merged cell ranges with details
        merged_ranges = []
        for merge_range in ws.merged_cells.ranges:
            min_col, min_row, max_col_m, max_row_m = merge_range.min_col, merge_range.min_row, merge_range.max_col, merge_range.max_row
            merged_ranges.append({
                'range': str(merge_range),
                'start_address': f"{get_column_letter(min_col)}{min_row}",
                'min_row': min_row,
                'min_col': min_col,
                'max_row': max_row_m,
                'max_col': max_col_m,
                'colspan': max_col_m - min_col + 1,
                'rowspan': max_row_m - min_row + 1,
            })

        sheets.append({
            'name': ws.title,
            'index': sheet_idx,
            'cells': cells,
            'max_row': max_row,
            'max_col': max_col,
            'merged_ranges': merged_ranges,
        })

        # Generate preview image
        img_path = _render_excel_preview(ws, basename, sheet_idx, max_row, max_col)
        preview_images.append(f'/visual_templates/images/{os.path.basename(img_path)}')

    wb.close()

    return {
        'file_type': 'excel',
        'preview_images': preview_images,
        'sheets': sheets,
    }


def _render_excel_preview(ws, basename, sheet_idx, max_row, max_col):
    """Render 1 sheet Excel thành ảnh preview."""
    from PIL import Image, ImageDraw, ImageFont

    # Calculate column widths
    col_widths = []
    for col in range(1, max_col + 1):
        max_len = 6
        for row in range(1, max_row + 1):
            cell = ws.cell(row=row, column=col)
            if cell.value is not None:
                max_len = max(max_len, min(len(str(cell.value)), 35))
        col_widths.append(min(max_len * 8 + 16, 280))

    row_height = 26
    header_h = 24  # Row/col header height
    padding = 10
    img_width = sum(col_widths) + padding * 2 + 40  # +40 for row numbers
    img_height = max_row * row_height + padding * 2 + header_h

    img = Image.new('RGB', (img_width, img_height), '#FFFFFF')
    draw = ImageDraw.Draw(img)

    try:
        font = ImageFont.truetype("C:/Windows/Fonts/arial.ttf", 11)
        font_bold = ImageFont.truetype("C:/Windows/Fonts/arialbd.ttf", 11)
        font_small = ImageFont.truetype("C:/Windows/Fonts/arial.ttf", 9)
    except Exception:
        font = ImageFont.load_default()
        font_bold = font
        font_small = font

    from openpyxl.utils import get_column_letter

    # Draw column headers (A, B, C...)
    x = padding + 40
    for col in range(1, max_col + 1):
        w = col_widths[col - 1]
        draw.rectangle([x, padding, x + w, padding + header_h], fill='#E8EAF6', outline='#B0BEC5')
        letter = get_column_letter(col)
        draw.text((x + w // 2 - 4, padding + 4), letter, fill='#5C6BC0', font=font_small)
        x += w

    # Draw rows
    y = padding + header_h
    for row in range(1, max_row + 1):
        # Row number
        draw.rectangle([padding, y, padding + 40, y + row_height], fill='#E8EAF6', outline='#B0BEC5')
        draw.text((padding + 4, y + 6), str(row), fill='#5C6BC0', font=font_small)

        x = padding + 40
        for col in range(1, max_col + 1):
            cell = ws.cell(row=row, column=col)
            w = col_widths[col - 1]

            # Cell border
            draw.rectangle([x, y, x + w, y + row_height], outline='#E0E0E0')

            # Cell background
            try:
                if cell.fill and cell.fill.fgColor and cell.fill.fgColor.rgb:
                    rgb_str = str(cell.fill.fgColor.rgb)
                    if len(rgb_str) >= 6 and rgb_str not in ('00000000', '0', '00'):
                        hex6 = rgb_str[-6:]
                        if all(c in '0123456789ABCDEFabcdef' for c in hex6) and hex6 != '000000':
                            draw.rectangle([x + 1, y + 1, x + w - 1, y + row_height - 1], fill='#' + hex6)
            except Exception:
                pass

            # Cell text
            if cell.value is not None:
                text = str(cell.value)[:35]
                use_font = font_bold if (cell.font and cell.font.bold) else font
                draw.text((x + 3, y + 6), text, fill='#212121', font=use_font)

            x += w
        y += row_height

    out = os.path.join(TEMPLATE_IMAGES_DIR, f'{basename}_sheet{sheet_idx + 1}.png')
    img.save(out, 'PNG')
    return out


def _parse_pdf(filepath, basename):
    """Parse PDF: extract text blocks với vị trí chính xác."""
    import fitz

    doc = fitz.open(filepath)
    text_blocks = []
    preview_images = []
    block_id = 0

    for page_idx, page in enumerate(doc):
        # Generate preview image
        mat = fitz.Matrix(2, 2)
        pix = page.get_pixmap(matrix=mat)
        img_path = os.path.join(TEMPLATE_IMAGES_DIR, f'{basename}_page{page_idx + 1}.png')
        pix.save(img_path)
        preview_images.append(f'/visual_templates/images/{os.path.basename(img_path)}')

        # Extract text blocks with positions
        blocks = page.get_text("dict")["blocks"]
        for block in blocks:
            if block.get("type") != 0:  # Skip image blocks
                continue
            for line in block.get("lines", []):
                for span in line.get("spans", []):
                    text = span.get("text", "").strip()
                    if not text:
                        continue

                    bbox = span.get("bbox", [0, 0, 0, 0])
                    font_name = span.get("font", "")
                    font_size = round(span.get("size", 10), 1)

                    # Detect labels: typically end with : or are bold/uppercase headers
                    is_label = False
                    if text.endswith(':') or text.endswith('：'):
                        is_label = True
                    elif text.isupper() and len(text) > 3:
                        is_label = True
                    elif 'Bold' in font_name or 'bold' in font_name:
                        is_label = True

                    text_blocks.append({
                        'id': block_id,
                        'text': text,
                        'page': page_idx,
                        'x0': round(bbox[0], 2),
                        'y0': round(bbox[1], 2),
                        'x1': round(bbox[2], 2),
                        'y1': round(bbox[3], 2),
                        'font': font_name,
                        'font_size': font_size,
                        'is_label': is_label,
                    })
                    block_id += 1

    doc.close()

    return {
        'file_type': 'pdf',
        'preview_images': preview_images,
        'text_blocks': text_blocks,
    }


def _parse_word(filepath, basename):
    """Parse Word: đọc paragraphs và table cells."""
    from docx import Document

    doc = Document(filepath)
    elements = []
    elem_id = 0

    # Parse paragraphs
    for para_idx, para in enumerate(doc.paragraphs):
        text = para.text.strip()
        if not text:
            continue

        is_label = False
        if text.endswith(':') or text.endswith('：'):
            is_label = True
        elif para.style and 'Heading' in (para.style.name or ''):
            is_label = True
        elif para.runs and para.runs[0].bold:
            is_label = True

        elements.append({
            'id': elem_id,
            'type': 'paragraph',
            'text': text,
            'para_idx': para_idx,
            'is_label': is_label,
            'style': para.style.name if para.style else '',
        })
        elem_id += 1

    # Parse tables - with structure info
    tables_info = []
    for table_idx, table in enumerate(doc.tables):
        max_row = len(table.rows)
        max_col = max((len(row.cells) for row in table.rows), default=0)
        tables_info.append({
            'table_idx': table_idx,
            'max_row': max_row,
            'max_col': max_col,
        })

        # Track merged cells (docx cells can span)
        seen_cells = set()
        for row_idx, row in enumerate(table.rows):
            for col_idx, cell in enumerate(row.cells):
                # Skip duplicate cells (merged cells return same object)
                cell_id = id(cell)
                if cell_id in seen_cells:
                    continue
                seen_cells.add(cell_id)

                text = cell.text.strip()

                is_label = (col_idx == 0)
                if text.endswith(':') or text.endswith('：'):
                    is_label = True

                elements.append({
                    'id': elem_id,
                    'type': 'table_cell',
                    'text': text,
                    'table_idx': table_idx,
                    'row': row_idx,
                    'col': col_idx,
                    'is_label': is_label,
                })
                elem_id += 1

    # Generate preview image using PyMuPDF if possible, else Pillow fallback
    preview_images = []
    try:
        import fitz
        pdf_doc = fitz.open(filepath)
        for i, page in enumerate(pdf_doc):
            mat = fitz.Matrix(2, 2)
            pix = page.get_pixmap(matrix=mat)
            img_path = os.path.join(TEMPLATE_IMAGES_DIR, f'{basename}_page{i + 1}.png')
            pix.save(img_path)
            preview_images.append(f'/visual_templates/images/{os.path.basename(img_path)}')
        pdf_doc.close()
    except Exception:
        # Fallback: render text as simple image
        img_path = _render_word_preview(elements, basename)
        preview_images.append(f'/visual_templates/images/{os.path.basename(img_path)}')

    return {
        'file_type': 'word',
        'preview_images': preview_images,
        'elements': elements,
        'tables_info': tables_info,
    }


def _render_word_preview(elements, basename):
    """Fallback: render Word elements as image."""
    from PIL import Image, ImageDraw, ImageFont

    try:
        font = ImageFont.truetype("C:/Windows/Fonts/arial.ttf", 12)
    except Exception:
        font = ImageFont.load_default()

    lines = []
    for el in elements:
        if el['type'] == 'paragraph':
            lines.append(el['text'])
        else:
            lines.append(f"  [{el['row']},{el['col']}] {el['text']}")

    line_height = 22
    padding = 30
    img_w = 900
    img_h = max(len(lines) * line_height + padding * 2, 200)

    img = Image.new('RGB', (img_w, img_h), '#FFFFFF')
    draw = ImageDraw.Draw(img)

    y = padding
    for line in lines:
        draw.text((padding, y), line[:100], fill='#212121', font=font)
        y += line_height

    out = os.path.join(TEMPLATE_IMAGES_DIR, f'{basename}_page1.png')
    img.save(out, 'PNG')
    return out


# ============================================================
# PHẦN 2: GENERATE - Tạo file mới từ template + field values
# ============================================================

def generate_from_template(config_id, field_values):
    """Tạo file mới từ template config + field values.

    Đọc file gốc, thay thế giá trị tại các vị trí đã mapping,
    xuất file CÙNG ĐỊNH DẠNG với file gốc.

    Args:
        config_id: ID của template config
        field_values: dict {field_key: new_value}

    Returns: output file path
    """
    configs = _load_configs()
    config = None
    for c in configs:
        if c.get('id') == config_id:
            config = c
            break
    if not config:
        raise ValueError(f'Template config {config_id} not found')

    file_type = config.get('file_type', '')
    source_file = config.get('source_file', '')

    if not os.path.isabs(source_file):
        source_file = os.path.join(BASE_DIR, source_file)

    if not os.path.exists(source_file):
        raise FileNotFoundError(f'Source file not found: {source_file}')

    output_dir = os.path.join(BASE_DIR, 'output')
    os.makedirs(output_dir, exist_ok=True)
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')

    if file_type == 'excel':
        return _generate_excel(source_file, config, field_values, output_dir, timestamp)
    elif file_type == 'pdf':
        return _generate_pdf(source_file, config, field_values, output_dir, timestamp)
    elif file_type == 'word':
        return _generate_word(source_file, config, field_values, output_dir, timestamp)
    else:
        raise ValueError(f'Unknown file type: {file_type}')


def _generate_excel(source_file, config, field_values, output_dir, timestamp):
    """Excel: copy file gốc, thay giá trị tại cell address, giữ nguyên format."""
    from openpyxl import load_workbook

    # Copy file gốc
    name = config.get('name', 'cert')
    name_safe = re.sub(r'[^\w\-.]', '_', name)
    ext = os.path.splitext(source_file)[1]
    output_path = os.path.join(output_dir, f'{name_safe}_{timestamp}{ext}')
    shutil.copy2(source_file, output_path)

    # Mở file copy và thay giá trị
    wb = load_workbook(output_path)

    mappings = config.get('mappings', [])
    print(f"  [VT-EXCEL] Generating with {len(mappings)} mappings")
    print(f"  [VT-EXCEL] Field values keys: {list(field_values.keys())}")
    for mapping in mappings:
        field_key = mapping.get('field_key', '')
        value = field_values.get(field_key)
        print(f"  [VT-EXCEL] {mapping.get('address')} -> key='{field_key}' -> value={repr(value)}")
        if value is None:
            continue

        sheet_idx = mapping.get('sheet_index', 0)
        address = mapping.get('address', '')  # e.g. "B3"

        if sheet_idx < len(wb.worksheets) and address:
            ws = wb.worksheets[sheet_idx]
            ws[address] = value

    # Process direct edits (cells edited by user without mapping)
    # Format 1: _direct_edits dict (legacy)
    # Format 2: _addr_K8 = "value" (flat keys)
    ws = wb.worksheets[0]
    direct_count = 0

    # Format 1: dict
    direct_edits = field_values.get('_direct_edits', {})
    if isinstance(direct_edits, dict):
        for address, value in direct_edits.items():
            if value:
                try:
                    ws[address] = value
                    direct_count += 1
                    print(f"  [VT-EXCEL] Direct(dict): {address} -> {repr(value)}")
                except Exception as e:
                    print(f"  [VT-EXCEL] Direct edit error at {address}: {e}")

    # Format 2: flat keys _addr_XX
    for key, value in field_values.items():
        if key.startswith('_addr_') and value:
            address = key[6:]  # Remove '_addr_' prefix
            try:
                ws[address] = value
                direct_count += 1
                print(f"  [VT-EXCEL] Direct(flat): {address} -> {repr(value)}")
            except Exception as e:
                print(f"  [VT-EXCEL] Direct edit error at {address}: {e}")

    if direct_count:
        print(f"  [VT-EXCEL] Total direct edits: {direct_count}")

    wb.save(output_path)
    wb.close()

    return output_path


def _generate_pdf(source_file, config, field_values, output_dir, timestamp):
    """PDF: mở file gốc, xóa text cũ + chèn text mới tại cùng vị trí."""
    import fitz

    name = config.get('name', 'cert')
    name_safe = re.sub(r'[^\w\-.]', '_', name)
    output_path = os.path.join(output_dir, f'{name_safe}_{timestamp}.pdf')

    doc = fitz.open(source_file)

    mappings = config.get('mappings', [])
    for mapping in mappings:
        field_key = mapping.get('field_key', '')
        value = field_values.get(field_key)
        if value is None:
            continue

        page_idx = mapping.get('page', 0)
        if page_idx >= len(doc):
            continue

        page = doc[page_idx]

        # Vị trí text gốc (từ lúc parse)
        x0 = mapping.get('x0', 0)
        y0 = mapping.get('y0', 0)
        x1 = mapping.get('x1', 0)
        y1 = mapping.get('y1', 0)
        font_size = mapping.get('font_size', 10)
        original_text = mapping.get('original_text', '')

        # Bước 1: Tìm và xóa text gốc bằng redaction
        if original_text:
            # Tìm text areas chứa text gốc
            text_instances = page.search_for(original_text)
            for inst in text_instances:
                # Chỉ xóa instance gần vị trí đã lưu
                if abs(inst.y0 - y0) < 5 and abs(inst.x0 - x0) < 20:
                    # Tạo redaction annotation (white box xóa text cũ)
                    page.add_redact_annot(inst, fill=(1, 1, 1))  # White fill
                    break
            page.apply_redactions()

        # Bước 2: Chèn text mới tại vị trí cũ
        text_point = fitz.Point(x0, y1)  # y1 = baseline
        try:
            page.insert_text(text_point, str(value), fontsize=font_size, fontname="helv")
        except Exception:
            page.insert_text(text_point, str(value), fontsize=font_size)

    doc.save(output_path)
    doc.close()

    return output_path


def _generate_word(source_file, config, field_values, output_dir, timestamp):
    """Word: copy file gốc, tìm và thay text trong paragraphs + tables."""
    from docx import Document

    name = config.get('name', 'cert')
    name_safe = re.sub(r'[^\w\-.]', '_', name)
    ext = os.path.splitext(source_file)[1]
    output_path = os.path.join(output_dir, f'{name_safe}_{timestamp}{ext}')

    # Copy file gốc rồi mở
    shutil.copy2(source_file, output_path)
    doc = Document(output_path)

    mappings = config.get('mappings', [])
    for mapping in mappings:
        field_key = mapping.get('field_key', '')
        value = field_values.get(field_key)
        if value is None:
            continue

        elem_type = mapping.get('type', '')
        original_text = mapping.get('original_text', '')

        if elem_type == 'paragraph':
            para_idx = mapping.get('para_idx', -1)
            if 0 <= para_idx < len(doc.paragraphs):
                para = doc.paragraphs[para_idx]
                # Thay text trong paragraph, giữ formatting
                _replace_text_in_paragraph(para, original_text, str(value))

        elif elem_type == 'table_cell':
            table_idx = mapping.get('table_idx', -1)
            row_idx = mapping.get('row', -1)
            col_idx = mapping.get('col', -1)

            if (0 <= table_idx < len(doc.tables) and
                0 <= row_idx < len(doc.tables[table_idx].rows)):
                table = doc.tables[table_idx]
                row = table.rows[row_idx]
                if 0 <= col_idx < len(row.cells):
                    cell = row.cells[col_idx]
                    # Thay text trong cell, giữ formatting
                    for para in cell.paragraphs:
                        if original_text in para.text:
                            _replace_text_in_paragraph(para, original_text, str(value))
                            break
                    else:
                        # Nếu không tìm thấy exact match, thay toàn bộ cell
                        if cell.paragraphs:
                            _replace_text_in_paragraph(cell.paragraphs[0], cell.paragraphs[0].text, str(value))

    doc.save(output_path)
    return output_path


def _replace_text_in_paragraph(paragraph, old_text, new_text):
    """Thay text trong Word paragraph, giữ nguyên formatting của run đầu tiên."""
    if not paragraph.runs:
        return

    full_text = paragraph.text
    if old_text and old_text in full_text:
        # Tìm run chứa text cần thay
        # Word chia text thành nhiều runs, nên cần ghép lại
        new_full = full_text.replace(old_text, new_text)

        # Giữ formatting của run đầu tiên
        if paragraph.runs:
            first_run = paragraph.runs[0]
            # Clear tất cả runs
            for run in paragraph.runs:
                run.text = ''
            # Set text mới vào run đầu tiên
            first_run.text = new_full
    else:
        # Không tìm thấy exact match → thay toàn bộ
        if paragraph.runs:
            for run in paragraph.runs:
                run.text = ''
            paragraph.runs[0].text = new_text


# ============================================================
# PHẦN 3: LEGACY - Giữ lại hàm cũ cho backward compatibility
# ============================================================

def file_to_images(filepath):
    """Legacy: convert file to preview images. Dùng parse_file internally."""
    result = parse_file(filepath)

    # Convert URL paths back to filesystem paths
    images = []
    for img_url in result.get('preview_images', []):
        img_path = os.path.join(BASE_DIR, img_url.lstrip('/'))
        if os.path.exists(img_path):
            images.append(img_path)

    return images


def generate_filled_pdf(template_id, field_values):
    """Legacy: redirect to new generate function."""
    return generate_from_template(template_id, field_values)


def _get_dim_characteristics(product_group):
    """Get dimensional characteristics list from cert_config for a product group."""
    try:
        from db import load_json_file
        config = load_json_file('cert_config.json') or {}
        group_data = config.get('product_groups', {}).get(product_group, {})
        return group_data.get('dimensional_characteristics', [])
    except Exception:
        return []


def _get_chemical_specs(product_group, standard=''):
    """Get chemical composition specs from cert_config.
    Returns list of {element, spec} based on material type."""
    try:
        from db import load_json_file
        config = load_json_file('cert_config.json') or {}
        # Determine material type - default to Carbon Steel for grade 8.8/10.9/12.9
        chem_data = config.get('chemical_specs', {})
        # Try Carbon Steel first (most common for fasteners)
        material = chem_data.get('Carbon Steel', chem_data.get('Alloy Steel', {}))
        if not material:
            # Fallback: first available
            for k, v in chem_data.items():
                material = v
                break
        if not material:
            return []

        result = []
        for elem_key in ['C', 'Si', 'Mn', 'P', 'S', 'Ni', 'Cr', 'Mo', 'Cu', 'N', 'V', 'Ti', 'Al', 'Fe', 'B']:
            elem_data = material.get(elem_key, {})
            if not elem_data:
                continue
            # Format spec
            spec_min = elem_data.get('min')
            spec_max = elem_data.get('max')
            if spec_min is not None and spec_max is not None:
                spec_str = f"{spec_min} - {spec_max}"
            elif spec_max is not None:
                spec_str = f"≤ {spec_max}"
            elif spec_min is not None:
                spec_str = f"≥ {spec_min}"
            else:
                continue
            result.append({'element': elem_key, 'spec': spec_str})
        return result
    except:
        return []


def _map_dim_specs(dim_chars, spec_data, product_group):
    """Map dimensional characteristics to actual spec values from globalfastener data.

    dim_chars: from cert_config [{key, name, name_vi}, ...]
    spec_data: from globalfastener {size: M8, s max=nominal size: 13, k Nominal Size: 5.3, ...}

    Returns: [{name, spec}, ...] in order
    """
    # Mapping from cert_config key to globalfastener data keys
    KEY_MAP = {
        # Bolt
        'width_across_flats': ['s max=nominal size', 's min', 's max'],
        'across_corner': ['e min', 'e Grade A min', 'e min Grade A'],
        'head_height': ['k Nominal Size', 'k Grade A min', 'k max'],
        'nominal_length': [],  # Length is from part number, not spec table
        'major_diameter': ['ds max=nominal size', 'ds min Grade A'],
        'pitch_diameter': [],
        'washer_face_thickness': ['c min', 'c max'],
        'thread_pitch': ['P Pitch', 'P Thread Pitch', 'P Pitch Coarse thread'],
        # Nut
        'thickness': ['m max=nominal size', 'm min'],
        'wrenching_height': ['mw min'],
        'flange_diameter': [],
        # Washer
        'inner_diameter': ['d1 Nominal Size', 'd1 min', 'Inner Dia min'],
        'outer_diameter': ['d2 Nominal Size', 'd2 max', 'Outer Dia max'],
    }

    result = []
    for char in dim_chars:
        key = char.get('key', '')
        name = char.get('name', key)
        spec_keys = KEY_MAP.get(key, [])

        # Try to find min/max from spec_data
        spec_str = ''
        if spec_keys:
            vals = []
            for sk in spec_keys:
                v = spec_data.get(sk)
                if v is not None and v != '' and v != '-' and v != '/':
                    try:
                        vals.append(float(v))
                    except (ValueError, TypeError):
                        vals.append(v)
            if len(vals) >= 2 and all(isinstance(v, (int, float)) for v in vals):
                spec_str = f"{min(vals)} - {max(vals)}"
            elif len(vals) == 1:
                spec_str = str(vals[0])

        # Fallback: search by name fragments in spec_data keys
        if not spec_str:
            name_lower = name.lower()
            for data_key, data_val in spec_data.items():
                dk = data_key.lower()
                if data_val and data_val != '-' and data_val != '/':
                    if name_lower.split()[0] in dk or key.replace('_', ' ') in dk:
                        try:
                            spec_str = str(float(data_val))
                        except:
                            pass
                        break

        result.append({'name': name, 'spec': spec_str})

    return result


def generate_batch_from_template(config_id, sizes):
    """Sinh mẫu hàng loạt cho nhiều size từ 1 template gốc.

    Mỗi size: copy file gốc → thay các ô auto bằng spec của size đó
              → xóa trắng các ô manual → lưu file mới.

    Args:
        config_id: ID template config
        sizes: list of {size: "M8", data: {key: val}} from globalfastener

    Returns: list of {size, filename, path, success, error}
    """
    configs = _load_configs()
    config = None
    for c in configs:
        if c.get('id') == config_id or str(c.get('id')) == str(config_id):
            config = c
            break
    if not config:
        raise ValueError(f'Template config {config_id} not found')

    file_type = config.get('file_type', '')
    if file_type != 'excel':
        raise ValueError(f'Batch generate only supports Excel, got: {file_type}')

    source_file = config.get('source_file', '')
    if not os.path.isabs(source_file):
        source_file = os.path.join(BASE_DIR, source_file)
    if not os.path.exists(source_file):
        raise FileNotFoundError(f'Source file not found: {source_file}')

    # Output directory
    gen_dir = os.path.join(TEMPLATE_DIR, 'generated')
    os.makedirs(gen_dir, exist_ok=True)

    mappings = config.get('mappings', [])
    standard = config.get('standard', 'STD')
    product_group = config.get('product_group', 'ITEM')
    supplier = config.get('supplier', 'NCC')

    # Build mapping lookup: which fields are auto vs manual
    auto_fields = {}  # field_key -> {address, sheet_index}
    manual_fields = []  # addresses to clear
    for m in mappings:
        mode = m.get('mode', 'auto')
        addr = m.get('address', '')
        fk = m.get('field_key', '')
        if mode == 'auto' and fk and addr:
            auto_fields[fk] = {'address': addr, 'sheet_index': m.get('sheet_index', 0)}
        elif mode in ('manual', 'manual_edit') and addr:
            manual_fields.append({'address': addr, 'sheet_index': m.get('sheet_index', 0)})

    # Build dimensional characteristics list from cert_config
    dim_chars = _get_dim_characteristics(product_group)
    # Build chemical specs from cert_config
    chem_specs = _get_chemical_specs(product_group, standard)

    results = []
    for size_info in sizes:
        size = size_info.get('size', 'unknown')
        spec_data = size_info.get('data', {})  # globalfastener data for this size

        try:
            from openpyxl import load_workbook

            # Create output filename
            safe_std = re.sub(r'[^\w]', '', standard)
            safe_grp = re.sub(r'[^\w]', '', product_group)
            safe_size = re.sub(r'[^\w.]', '', size)
            filename = f"cfg{config_id}_{safe_std}_{safe_grp}_{safe_size}.xlsx"
            output_path = os.path.join(gen_dir, filename)

            # Copy source
            shutil.copy2(source_file, output_path)

            # Open and modify
            wb = load_workbook(output_path)

            # Build dim specs for this size from globalfastener data
            dim_values = _map_dim_specs(dim_chars, spec_data, product_group)

            # Fill auto fields
            for field_key, info in auto_fields.items():
                value = None
                addr = info['address']
                si = info['sheet_index']

                # Map field_key to actual value
                if field_key == 'part_number':
                    value = ''  # No specific part number in batch mode
                elif field_key == 'size':
                    value = size
                elif field_key == 'standard':
                    value = standard
                elif field_key == 'grade':
                    value = None  # Keep original
                elif field_key == 'product_name':
                    value = f"{standard} {product_group} {size}"
                # Dimensional fields: dim_N_name, dim_N_spec
                elif field_key.startswith('dim_') and '_name' in field_key:
                    idx = int(re.search(r'dim_(\d+)_', field_key).group(1)) - 1
                    if idx < len(dim_values):
                        value = dim_values[idx]['name']
                elif field_key.startswith('dim_') and '_spec' in field_key:
                    idx = int(re.search(r'dim_(\d+)_', field_key).group(1)) - 1
                    if idx < len(dim_values):
                        value = dim_values[idx]['spec']
                # Chemical fields: chem_N_elem, chem_N_spec
                elif field_key.startswith('chem_') and '_elem' in field_key:
                    idx = int(re.search(r'chem_(\d+)_', field_key).group(1)) - 1
                    if idx < len(chem_specs):
                        value = chem_specs[idx]['element']
                elif field_key.startswith('chem_') and '_spec' in field_key:
                    idx = int(re.search(r'chem_(\d+)_', field_key).group(1)) - 1
                    if idx < len(chem_specs):
                        value = chem_specs[idx]['spec']
                else:
                    # Try direct match in spec_data
                    value = spec_data.get(field_key)

                if value is not None and si < len(wb.worksheets):
                    wb.worksheets[si][addr] = value

            # Clear manual fields (leave empty for user to fill)
            for info in manual_fields:
                addr = info['address']
                si = info['sheet_index']
                if si < len(wb.worksheets):
                    wb.worksheets[si][addr] = ''

            wb.save(output_path)
            wb.close()

            results.append({
                'size': size,
                'filename': filename,
                'path': f"visual_templates/generated/{filename}",
                'success': True,
            })
            print(f"  [BATCH] Generated: {filename}")

        except Exception as e:
            import traceback
            traceback.print_exc()
            results.append({
                'size': size,
                'filename': '',
                'path': '',
                'success': False,
                'error': str(e),
            })

    return results


def _fill_dim_zone(wb, zone_mapping, spec_data, config):
    """Fill dimensional inspection zone with spec data.
    (placeholder for future use - currently not needed since user fills manually)
    """
    pass