#!/usr/bin/env python3
"""
Test to reproduce the actual bug: tables with gaps in bordered cells.

ROOT CAUSE HYPOTHESIS:
If a table has a row in the middle with NO borders (or only 1 border per cell),
that row won't be detected as "bordered". This creates a GAP in the bordered cell set.

The flood fill algorithm cannot bridge this gap, so it splits the table into
MULTIPLE regions. Then separators get inserted between these regions - INSIDE the table!

This test creates exactly this scenario.
"""

import os
import sys
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment

# Add src to path
sys.path.insert(0, str(Path(__file__).parent.parent.parent))

from src.extraction_v2.detect_border import BorderTableDetector


def create_table_with_gap(output_path: str):
    """
    Create a table that has a row WITHOUT sufficient borders in the middle.

    Structure:
    Row 1: Title (with borders)
    Row 2: Headers (with borders)
    Row 3: Data (with borders)
    Row 4: Data (NO BORDERS OR ONLY 1 BORDER) ← This creates a GAP!
    Row 5: Data (with borders)
    Row 6: Data (with borders)

    Expected: This should be detected as ONE table
    Bug: It will be detected as TWO tables (rows 1-3 and rows 5-6)
    Result: A separator will be inserted after row 3, INSIDE the table!
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "TableWithGap"

    full_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # Only top border (not enough to be considered "bordered")
    top_only = Border(top=Side(style='thin'))

    # No border
    no_border = Border()

    # Row 1: Title with borders
    ws['B2'] = "表 ４.２.１.４.４.６－１０－１　「所内SO工事結果」自動更新条件"
    ws.merge_cells('B2:E2')
    ws['B2'].font = Font(bold=True)
    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}2'].border = full_border

    # Row 2: Headers with borders
    headers = ['項目', '条件', '備考', '状態']
    for idx, header in enumerate(headers, start=2):
        cell = ws.cell(row=3, column=idx)
        cell.value = header
        cell.border = full_border
        cell.font = Font(bold=True)

    # Row 3: Data WITH borders
    data_row_1 = ['工事番号', 'NOT NULL', '必須項目', 'OK']
    for idx, value in enumerate(data_row_1, start=2):
        cell = ws.cell(row=4, column=idx)
        cell.value = value
        cell.border = full_border

    # Row 4: Data WITHOUT sufficient borders (THE GAP!)
    # This row has content but NO borders - it won't be detected as "bordered"
    data_row_2 = ['17', 'None', "'－'", '']  # This is the problematic row!
    for idx, value in enumerate(data_row_2, start=2):
        cell = ws.cell(row=5, column=idx)
        cell.value = value
        cell.border = no_border  # NO BORDER - creates gap!

    # Row 5: Data WITH borders
    data_row_3 = ['工事名', 'LENGTH > 0', '必須項目', 'OK']
    for idx, value in enumerate(data_row_3, start=2):
        cell = ws.cell(row=6, column=idx)
        cell.value = value
        cell.border = full_border

    # Row 6: Data WITH borders
    data_row_4 = ['完了日', 'DATE VALID', '日付形式', 'OK']
    for idx, value in enumerate(data_row_4, start=2):
        cell = ws.cell(row=7, column=idx)
        cell.value = value
        cell.border = full_border

    ws['B1'] = "Content above table"
    ws['B8'] = "Content below table"

    wb.save(output_path)
    print(f"✓ Created table with gap: {output_path}")
    print(f"  Row 5 has NO borders - this should create a gap!")


def test_gap_detection(file_path: str):
    """Test if the gap causes table splitting."""
    print("\n" + "=" * 80)
    print("TEST: Detecting Table with Border Gap")
    print("=" * 80)

    detector = BorderTableDetector()
    tables = detector.detect_tables_by_border(file_path)

    print(f"\nDetected {len(tables)} table(s):")
    for idx, table in enumerate(tables, start=1):
        print(f"  Table {idx}: {table['range']} (rows {table['start_row']}-{table['end_row']})")

    print("\n" + "-" * 80)
    print("ANALYSIS:")

    if len(tables) == 1:
        print("✓ PASS: Detected as 1 table (gap did not cause split)")
        return True, tables
    elif len(tables) == 2:
        print(f"✗ FAIL: Detected as 2 tables - GAP at row 5 caused table split!")
        print(f"  Table 1: rows {tables[0]['start_row']}-{tables[0]['end_row']}")
        print(f"  Table 2: rows {tables[1]['start_row']}-{tables[1]['end_row']}")
        print(f"  Gap row: {tables[0]['end_row'] + 1} (row 5 with no borders)")
        print("\n  This is the BUG! The detector cannot bridge the gap.")
        return False, tables
    else:
        print(f"✗ FAIL: Detected as {len(tables)} tables - unexpected behavior")
        return False, tables


def test_separator_insertion_with_gap(file_path: str, tables):
    """Test separator insertion when table has a gap."""
    print("\n" + "=" * 80)
    print("TEST: Separator Insertion with Gap")
    print("=" * 80)

    detector = BorderTableDetector()
    output_path = file_path.replace('.xlsx', '_separated.xlsx')

    stats = detector.insert_separator_rows(file_path, output_path)

    print(f"\nInsertion Statistics:")
    print(f"  Tables processed:      {stats['tables_processed']}")
    print(f"  Rows inserted above:   {stats['rows_inserted_above']}")
    print(f"  Rows inserted below:   {stats['rows_inserted_below']}")

    print("\n" + "-" * 80)
    print("VERIFICATION:")

    from openpyxl import load_workbook
    wb = load_workbook(output_path)
    ws = wb.active

    # If table was split, check if separator was inserted at the gap
    if len(tables) >= 2:
        # The gap is between table 1 end and table 2 start
        gap_row_original = tables[0]['end_row'] + 1

        # After preprocessing, check if there's content at row 5 (the gap row)
        print(f"\nChecking gap row {gap_row_original} in original file...")
        print(f"Expected: This row has data ['17', 'None', \"'-'\", '']")

        # In preprocessed file, check if separator was inserted
        # If separator inserted after table 1, the gap row shifts down

        # Check original row 5 (our gap row) - it should have data
        orig_wb = load_workbook(file_path)
        orig_ws = orig_wb.active

        gap_has_data = any(
            orig_ws.cell(row=5, column=col).value
            for col in range(2, 6)
        )

        if gap_has_data:
            print(f"✓ Gap row (row 5) has data in original file")
        else:
            print(f"✗ Gap row (row 5) is empty in original file (unexpected)")

        # Now check if separator was inserted after first table
        # This would be AFTER row tables[0]['end_row']
        # In the separated file, check row tables[0]['end_row'] + 1

        # Look for empty rows in the separated file
        print(f"\nScanning separated file for empty rows...")
        for row_num in range(2, 10):
            row_empty = not any(
                ws.cell(row=row_num, column=col).value
                for col in range(2, 6)
            )

            row_values = [ws.cell(row=row_num, column=col).value for col in range(2, 6)]

            if row_empty:
                print(f"  Row {row_num}: [EMPTY] ← Separator")
            else:
                print(f"  Row {row_num}: {row_values[:2]}...")

        print("\n✗ FAIL: Separator was inserted INSIDE the table due to gap!")
        print("  This is the bug! Row 5 should be part of the table, not a gap.")
        return False

    else:
        print("✓ PASS: Table was detected as single unit, no mid-table separators")
        return True


def main():
    """Run the gap test."""
    print("=" * 80)
    print("BORDER GAP BUG TEST")
    print("=" * 80)
    print("\nThis test creates a table with a row that has NO borders.")
    print("This row creates a 'gap' in the bordered cells, causing the")
    print("flood fill algorithm to split the table into multiple regions.")
    print("Result: Separators get inserted INSIDE the table!")
    print("=" * 80)

    tmp_dir = Path(__file__).parent.parent.parent / "tmp"
    tmp_dir.mkdir(exist_ok=True)

    test_file = tmp_dir / "test_border_gap.xlsx"

    # Create test file
    print("\nCreating test file with border gap...")
    create_table_with_gap(str(test_file))

    # Test detection
    detection_passed, tables = test_gap_detection(str(test_file))

    # Test separator insertion
    insertion_passed = test_separator_insertion_with_gap(str(test_file), tables)

    # Summary
    print("\n" + "=" * 80)
    print("TEST SUMMARY")
    print("=" * 80)

    if detection_passed and insertion_passed:
        print("✓ ALL TESTS PASSED - Bug is fixed or doesn't apply to this scenario")
        return 0
    else:
        print("✗ BUG REPRODUCED!")
        print("\nROOT CAUSE:")
        print("  - Row 5 has NO borders, so it's not in the 'bordered_cells' set")
        print("  - This creates a gap that the flood fill cannot bridge")
        print("  - The table is split into 2 regions: rows 2-4 and rows 6-7")
        print("  - A separator is inserted after row 4, INSIDE the table!")
        print("\nSOLUTION:")
        print("  - Need to improve table detection to bridge gaps")
        print("  - Consider rows with content but no borders as part of table")
        print("  - Or use a different grouping strategy that considers content")
        return 1


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