#!/usr/bin/env python3
"""
Debug script for V2 extraction pipeline header detection issue.

This script analyzes tables from file 13 to understand why data values
are being mistaken for headers.
"""

import json
import sys
from pathlib import Path
from uuid import uuid4

# Add project root to path
project_root = Path(__file__).parent
sys.path.insert(0, str(project_root))

from src.extraction_v2.excel_to_html_converter import ExcelToHtmlConverter
from src.extraction_v2.html_table_extractor import HtmlTableExtractor
from src.extraction_v2.llm_header_detector import LlmHeaderDetector
from bs4 import BeautifulSoup


def analyze_table_html(table_html: str, table_idx: int, output_dir: Path):
    """Analyze a single table's HTML structure."""
    print(f"\n{'='*70}")
    print(f"ANALYZING TABLE {table_idx}")
    print(f"{'='*70}")

    soup = BeautifulSoup(table_html, 'lxml')
    table_elem = soup.find('table')

    if not table_elem:
        print("ERROR: No <table> element found")
        return

    # Count rows
    all_rows = table_elem.find_all('tr')
    print(f"\nTotal rows in table: {len(all_rows)}")

    # Analyze first 10 rows (what LLM sees)
    first_10_rows = all_rows[:10]
    print(f"\nFirst 10 rows structure:")
    print("-" * 70)

    for row_idx, row in enumerate(first_10_rows):
        cells = row.find_all(['th', 'td'])
        cell_info = []
        for cell in cells:
            tag = cell.name
            text = cell.get_text(strip=True)
            rowspan = cell.get('rowspan', '1')
            colspan = cell.get('colspan', '1')

            # Truncate long text
            display_text = text[:30] + "..." if len(text) > 30 else text

            cell_info.append(f"{tag.upper()}[{display_text}]")
            if rowspan != '1' or colspan != '1':
                cell_info[-1] += f"(r{rowspan}c{colspan})"

        print(f"Row {row_idx}: {' | '.join(cell_info)}")

    # Check if there's a <thead> vs <tbody> distinction
    thead = table_elem.find('thead')
    tbody = table_elem.find('tbody')
    print(f"\n<thead> present: {thead is not None}")
    print(f"<tbody> present: {tbody is not None}")

    # Analyze all <th> tags
    all_th_tags = table_elem.find_all('th')
    print(f"\nTotal <th> tags in table: {len(all_th_tags)}")

    if all_th_tags:
        print("\nAll <th> tag contents:")
        th_by_row = {}
        for th in all_th_tags:
            # Find which row this <th> is in
            parent_tr = th.find_parent('tr')
            row_idx = all_rows.index(parent_tr) if parent_tr in all_rows else -1

            if row_idx not in th_by_row:
                th_by_row[row_idx] = []

            text = th.get_text(strip=True)
            display_text = text[:40] + "..." if len(text) > 40 else text
            th_by_row[row_idx].append(display_text)

        for row_idx, th_texts in sorted(th_by_row.items()):
            print(f"  Row {row_idx}: {th_texts}")

    # Save full HTML to file
    html_file = output_dir / f"table_{table_idx}_full.html"
    with open(html_file, 'w', encoding='utf-8') as f:
        f.write(str(table_elem.prettify()))
    print(f"\n✓ Saved full HTML to: {html_file}")

    # Save first 10 rows HTML (what LLM sees)
    first_10_html = f"<table>{''.join(str(row) for row in first_10_rows)}</table>"
    first_10_file = output_dir / f"table_{table_idx}_first10.html"
    with open(first_10_file, 'w', encoding='utf-8') as f:
        soup_10 = BeautifulSoup(first_10_html, 'lxml')
        f.write(soup_10.prettify())
    print(f"✓ Saved first 10 rows HTML to: {first_10_file}")

    return {
        'total_rows': len(all_rows),
        'has_thead': thead is not None,
        'has_tbody': tbody is not None,
        'th_count': len(all_th_tags),
        'th_by_row': th_by_row
    }


def main():
    """Run the debug analysis."""

    # File 13 - the problematic file
    test_file = Path('/Users/nguyen/Work/DSOL/CustomerDocument/IFф╗ХцзШцЫ╕(уВвуГ│уГПуВЩуГ│уГИуВЩуГл)/уВкуГ╝уВ┐уВЩхИ╢х╛б/1 хЕЙуВвуГ│уГПуВЩуГ│уГИуВЩуГлценхЛЩцФпцП┤уВ╖уВ╣уГЖуГая╝ИхИЖцЮРч│╗я╝Й/1.12 уГХуВбуВдуГлчХкхП╖уБиуГлуГ╝уГИуВ┐уВпуВЩхп╛х┐Ьшби.xls')

    print("="*70)
    print("V2 HEADER DETECTION DEBUG")
    print("="*70)
    print(f"\nTest file: {test_file.name}")
    print(f"Full path: {test_file}")

    if not test_file.exists():
        print(f"\nERROR: File not found!")
        return 1

    # Create output directory
    output_dir = Path('./tmp/debug_v2_headers')
    output_dir.mkdir(parents=True, exist_ok=True)
    print(f"\nOutput directory: {output_dir}")

    print("\n" + "="*70)
    print("STEP 1: Convert Excel to HTML")
    print("="*70)

    converter = ExcelToHtmlConverter()
    html = converter.convert_to_html(str(test_file))

    if not html:
        print("ERROR: Conversion failed!")
        return 1

    print(f"✓ Conversion complete")
    print(f"  HTML size: {len(html):,} characters")

    # Save full HTML
    full_html_file = output_dir / "full_document.html"
    with open(full_html_file, 'w', encoding='utf-8') as f:
        f.write(html)
    print(f"  Saved full HTML to: {full_html_file}")

    print("\n" + "="*70)
    print("STEP 2: Extract Tables")
    print("="*70)

    extractor = HtmlTableExtractor()
    tables = extractor.extract_tables(html)

    print(f"✓ Found {len(tables)} table(s)")

    # Analyze each table
    all_analysis = []
    for idx, table in enumerate(tables, 1):
        analysis = analyze_table_html(table.html, idx, output_dir)
        analysis['table_index'] = idx
        analysis['row_count'] = table.row_count
        analysis['col_count'] = table.col_count
        all_analysis.append(analysis)

    print("\n" + "="*70)
    print("STEP 3: Test LLM Header Detection (First 3 Tables)")
    print("="*70)

    detector = LlmHeaderDetector()

    for idx, table in enumerate(tables[:3], 1):  # Only first 3 tables
        print(f"\n--- Table {idx} ---")
        print(f"Dimensions: {table.row_count} rows x {table.col_count} cols")

        try:
            headers = detector.detect_headers(table.html)

            print(f"✓ Header detection complete")
            print(f"  Header rows: {headers.header_rows}")
            print(f"  Number of columns: {len(headers.headers)}")

            # Show detected headers
            flat_headers = headers.get_flat_headers()
            print(f"\n  Detected headers:")
            for col_idx, header in enumerate(flat_headers):
                print(f"    Column {col_idx}: '{header}'")

            # Save to JSON
            header_file = output_dir / f"table_{idx}_detected_headers.json"
            with open(header_file, 'w', encoding='utf-8') as f:
                json.dump(headers.to_dict(), f, ensure_ascii=False, indent=2)
            print(f"\n  Saved header data to: {header_file}")

        except Exception as e:
            print(f"✗ Header detection failed: {e}")
            import traceback
            traceback.print_exc()

    print("\n" + "="*70)
    print("SUMMARY")
    print("="*70)

    print(f"\nTotal tables: {len(tables)}")
    print(f"\nTable analysis summary:")
    for analysis in all_analysis:
        print(f"\n  Table {analysis['table_index']}:")
        print(f"    Dimensions: {analysis['row_count']} rows x {analysis['col_count']} cols")
        print(f"    Total rows: {analysis['total_rows']}")
        print(f"    <thead> present: {analysis['has_thead']}")
        print(f"    <tbody> present: {analysis['has_tbody']}")
        print(f"    <th> count: {analysis['th_count']}")
        if analysis['th_by_row']:
            print(f"    <th> tags by row:")
            for row_idx, th_texts in sorted(analysis['th_by_row'].items()):
                if row_idx < 10:  # Only show first 10 rows
                    print(f"      Row {row_idx}: {len(th_texts)} <th> tag(s)")

    # Save summary to JSON
    summary_file = output_dir / "analysis_summary.json"
    with open(summary_file, 'w', encoding='utf-8') as f:
        json.dump(all_analysis, f, ensure_ascii=False, indent=2)
    print(f"\n✓ Saved analysis summary to: {summary_file}")

    print("\n" + "="*70)
    print("DEBUG COMPLETE!")
    print("="*70)
    print(f"\nAll output files saved to: {output_dir}")
    print("\nNext steps:")
    print("  1. Review the HTML files to see full table structure")
    print("  2. Check if <th> tags contain data values instead of headers")
    print("  3. Look at detected_headers.json to see what LLM extracted")
    print("  4. Compare first 10 rows HTML vs full table HTML")

    return 0


if __name__ == "__main__":
    sys.exit(main())