"""
Template Engine for Fastener Certificate Generation
=====================================================
Fills Excel templates according to saved template configs.

Usage:
    from template_engine import fill_template
    result = fill_template(template_path, config, cert_data, output_path)
"""

import json
import os
import re
import random
from copy import copy
from datetime import datetime
from pathlib import Path

BASE_DIR = Path(__file__).parent


def copy_row_style(ws, source_row, target_row):
    """Copy cell styles from source_row to target_row."""
    for col in range(1, ws.max_column + 1):
        src = ws.cell(row=source_row, column=col)
        tgt = ws.cell(row=target_row, column=col)
        if src.has_style:
            tgt.font = copy(src.font)
            tgt.border = copy(src.border)
            tgt.fill = copy(src.fill)
            tgt.number_format = src.number_format
            tgt.alignment = copy(src.alignment)


def _get_nested_value(data, key_path):
    """Get a value from nested dict using dot notation, e.g. 'product.size'."""
    parts = key_path.split('.')
    current = data
    for part in parts:
        if isinstance(current, dict):
            current = current.get(part, '')
        else:
            return ''
    return current if current is not None else ''


def _generate_random_in_range(spec_min, spec_max, decimals=2):
    """Generate a random value between spec_min and spec_max."""
    try:
        lo = float(spec_min)
        hi = float(spec_max)
        if lo > hi:
            lo, hi = hi, lo
        # Generate within 10-90% of range to look realistic
        margin = (hi - lo) * 0.1
        val = random.uniform(lo + margin, hi - margin)
        return round(val, decimals)
    except (ValueError, TypeError):
        return ''


def _generate_actual_value(spec_str, decimals=2):
    """Generate a realistic actual value from a specification string like '13.00 / 14.38' or '>= 600'."""
    if not spec_str:
        return ''
    spec_str = str(spec_str).strip()

    # Range: "13.00 / 14.38" or "13.00 - 14.38" or "13.00 <---> 14.38"
    m = re.match(r'([\d.]+)\s*[/\-<>\s]+\s*([\d.]+)', spec_str)
    if m:
        return _generate_random_in_range(m.group(1), m.group(2), decimals)

    # Min only: ">= 600" or "600 min"
    m = re.match(r'[>=≥]+\s*([\d.]+)', spec_str)
    if m:
        base = float(m.group(1))
        return round(base * random.uniform(1.02, 1.15), decimals)
    m = re.match(r'([\d.]+)\s*min', spec_str)
    if m:
        base = float(m.group(1))
        return round(base * random.uniform(1.02, 1.15), decimals)

    # Max only: "<= 0.045" or "0.045 max"
    m = re.match(r'[<=≤]+\s*([\d.]+)', spec_str)
    if m:
        base = float(m.group(1))
        return round(base * random.uniform(0.50, 0.90), decimals)
    m = re.match(r'([\d.]+)\s*max', spec_str)
    if m:
        base = float(m.group(1))
        return round(base * random.uniform(0.50, 0.90), decimals)

    return ''


def _resolve_field_value(field_key, cert_data, overrides=None):
    """Resolve a field key to its actual value from cert_data or overrides."""
    overrides = overrides or {}

    # Check overrides first
    if field_key in overrides:
        return overrides[field_key]

    # Direct header fields (user-input fields)
    header_fields = {
        'cert_no': 'header.cert_no',
        'po_no': 'header.po_no',
        'quantity': 'header.quantity',
        'heat_no': 'header.heat_no',
        'lot_no': 'header.lot_no',
        'mfr_date': 'header.mfr_date',
        'marking': 'header.marking',
        'item_no': 'header.item_no',
    }
    if field_key in header_fields:
        return _get_nested_value(cert_data, header_fields[field_key])

    # Product info fields (auto)
    if field_key.startswith('product.') or field_key.startswith('classification.') or field_key.startswith('header.'):
        return _get_nested_value(cert_data, field_key)

    # Product name special handling
    if field_key == 'product_name':
        return _get_nested_value(cert_data, 'classification.group_name_en')

    return ''


def _fill_table_section(ws, table_config, items, start_row, row_offset_delta=0):
    """
    Fill a table section (dimensional, mechanical, or chemical).

    Args:
        ws: worksheet
        table_config: dict with col_characteristic, col_specification, col_actual, col_determinant, template_rows
        items: list of data items to fill
        start_row: starting row in the worksheet
        row_offset_delta: cumulative row offset from previous table adjustments

    Returns:
        row_delta: how many rows were added (positive) or removed (negative)
    """
    from openpyxl.utils import column_index_from_string

    actual_start = start_row + row_offset_delta
    template_rows_count = table_config.get('template_rows', 5)
    data_rows_count = len(items)

    col_char = column_index_from_string(table_config.get('col_characteristic', 'A'))
    col_spec = column_index_from_string(table_config.get('col_specification', 'C'))
    col_actual = column_index_from_string(table_config.get('col_actual', 'E'))
    col_det = column_index_from_string(table_config.get('col_determinant', 'G'))

    row_delta = 0

    # Insert or delete rows as needed
    if data_rows_count > template_rows_count:
        # Need to insert rows
        extra = data_rows_count - template_rows_count
        insert_at = actual_start + template_rows_count
        ws.insert_rows(insert_at, extra)
        # Copy style from last template row
        for i in range(extra):
            copy_row_style(ws, actual_start, insert_at + i)
        row_delta = extra
    elif data_rows_count < template_rows_count:
        # Need to delete extra rows
        extra = template_rows_count - data_rows_count
        delete_at = actual_start + data_rows_count
        ws.delete_rows(delete_at, extra)
        row_delta = -extra

    # Fill data
    for i, item in enumerate(items):
        row = actual_start + i

        # Characteristic name
        char_name = item.get('name', item.get('element', ''))
        ws.cell(row=row, column=col_char, value=char_name)

        # Specification
        spec = item.get('specification', item.get('spec_str', ''))
        if not spec and item.get('spec_min') and item.get('spec_max'):
            spec = f"{item['spec_min']} / {item['spec_max']}"
        elif not spec and item.get('spec_min'):
            spec = f"{item['spec_min']} min"
        elif not spec and item.get('spec_max'):
            spec = f"{item['spec_max']} max"
        ws.cell(row=row, column=col_spec, value=spec)

        # Actual result
        actual = item.get('actual_result', item.get('actual_value', ''))
        if not actual and spec:
            actual = _generate_actual_value(spec)
        ws.cell(row=row, column=col_actual, value=actual)

        # Determinant
        det = item.get('determinant', 'Qualified')
        if not det:
            det = 'Qualified'
        ws.cell(row=row, column=col_det, value=det)

    return row_delta


def fill_template(template_path, config, cert_data, output_path):
    """
    Opens Excel template, fills data according to config, saves output.

    Args:
        template_path: path to source Excel template
        config: template configuration dict with cell mappings and table configs
        cert_data: certificate data from cert_manager.build_certificate_data()
        output_path: path to save the filled Excel file

    Returns:
        dict with success status and output path
    """
    from openpyxl import load_workbook

    try:
        wb = load_workbook(template_path)
        ws = wb.active

        overrides = cert_data.get('_overrides', {})

        # Step 1: Fill header cells (simple cell -> value mapping)
        cell_mappings = config.get('cell_mappings', {})
        for address, field_key in cell_mappings.items():
            if field_key == 'skip' or not field_key:
                continue
            if field_key in ('dim_table_start', 'mech_table_start', 'chem_table_start'):
                continue  # These are table markers, handled separately

            value = _resolve_field_value(field_key, cert_data, overrides)
            if value:
                ws[address] = value

        # Step 2: Fill dimensional table
        row_offset = 0
        dim_config = config.get('dimensional_table', {})
        if dim_config.get('start_row'):
            characteristics = _get_nested_value(cert_data, 'dimensional.characteristics')
            if isinstance(characteristics, list):
                # Build items with spec strings
                items = []
                for char in characteristics:
                    spec_str = ''
                    if char.get('spec_min') and char.get('spec_max'):
                        spec_str = f"{char['spec_min']} / {char['spec_max']}"
                    elif char.get('spec_min'):
                        spec_str = f"{char['spec_min']} min"
                    elif char.get('spec_max'):
                        spec_str = f"{char['spec_max']} max"
                    items.append({
                        'name': char.get('name', ''),
                        'spec_str': spec_str,
                        'actual_result': '',
                        'determinant': 'Qualified',
                    })
                row_offset += _fill_table_section(
                    ws, dim_config, items, dim_config['start_row'], 0
                )

        # Step 3: Fill mechanical table
        mech_config = config.get('mechanical_table', {})
        if mech_config.get('start_row'):
            mech_props = _get_nested_value(cert_data, 'mechanical.properties')
            if isinstance(mech_props, list):
                row_offset += _fill_table_section(
                    ws, mech_config, mech_props, mech_config['start_row'], row_offset
                )

        # Step 4: Fill chemical table
        chem_config = config.get('chemical_table', {})
        if chem_config.get('start_row'):
            elements = _get_nested_value(cert_data, 'chemical.elements')
            if isinstance(elements, list):
                items = []
                for el in elements:
                    spec = el.get('specification', '')
                    actual = el.get('actual_value', '')
                    if not actual and spec:
                        actual = _generate_actual_value(spec, decimals=3)
                    items.append({
                        'name': el.get('element', ''),
                        'specification': spec,
                        'actual_result': actual,
                        'determinant': 'Qualified' if spec else '',
                    })
                row_offset += _fill_table_section(
                    ws, chem_config, items, chem_config['start_row'], row_offset
                )

        # Save output
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        wb.save(output_path)

        return {
            'success': True,
            'path': output_path,
            'message': f'Certificate generated: {os.path.basename(output_path)}'
        }

    except Exception as e:
        import traceback
        traceback.print_exc()
        return {
            'success': False,
            'error': str(e)
        }


def parse_template_cells(filepath):
    """
    Parse an Excel file and return all cells with values.
    Used for the template configuration UI.
    """
    from openpyxl import load_workbook

    wb = load_workbook(filepath)
    ws = wb.active
    cells = []
    for row in ws.iter_rows():
        for cell in row:
            if cell.value is not None and str(cell.value).strip():
                cells.append({
                    'address': cell.coordinate,
                    'value': str(cell.value)[:100],
                    'row': cell.row,
                    'col': cell.column,
                    'font': cell.font.name if cell.font else '',
                    'size': cell.font.size if cell.font else '',
                    'bold': cell.font.bold if cell.font else False,
                    'merged': False
                })

    # Check merged cells
    for merged_range in ws.merged_cells.ranges:
        for c in cells:
            if c['address'] in merged_range:
                c['merged'] = True

    return cells