#!/usr/bin/env python3
"""
Advanced test to reproduce border detection bug with complex tables.

This test creates scenarios where:
1. Tables have varying border styles (thick header, thin data)
2. Some rows might have missing borders on certain sides
3. Multiple tables close together

These scenarios can cause tables to be detected as multiple separate regions,
leading to separator insertion INSIDE tables.
"""

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_complex_test_1(output_path: str):
    """
    Test Case 1: Table with varying border thickness.

    Real-world tables often have:
    - Thick borders for title/header rows
    - Thin borders for data rows

    This can cause the detector to see them as separate regions.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "VaryingBorders"

    # Thick border for title/header
    thick_border = Border(
        left=Side(style='medium'),
        right=Side(style='medium'),
        top=Side(style='medium'),
        bottom=Side(style='medium')
    )

    # Thin border for data
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # Row 1: Title with thick borders
    ws['B2'] = "表 ４.２.１.４.４.６－１０－１　「所内SO工事結果」自動更新条件"
    ws.merge_cells('B2:E2')
    ws['B2'].font = Font(bold=True, size=12)
    ws['B2'].alignment = Alignment(horizontal='center')

    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}2'].border = thick_border

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

    # Rows 3-5: Data with thin borders
    data_rows = [
        ['工事番号', 'NOT NULL', '必須項目', 'OK'],
        ['工事名', 'LENGTH > 0', '必須項目', 'OK'],
        ['完了日', 'DATE VALID', '日付形式', 'OK'],
    ]

    for row_idx, row_data in enumerate(data_rows, start=4):
        for col_idx, value in enumerate(row_data, start=2):
            cell = ws.cell(row=row_idx, column=col_idx)
            cell.value = value
            cell.border = thin_border  # Thin border for data

    ws['B1'] = "Content above"
    ws['B7'] = "Content below"

    wb.save(output_path)
    print(f"✓ Test Case 1: Varying border thickness - {output_path}")


def create_complex_test_2(output_path: str):
    """
    Test Case 2: Table with merged cells causing inconsistent borders.

    Merged cells might have borders only on outer edges, not between cells.
    This can break the contiguity detection.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "MergedCells"

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

    # Row 1: Title (merged)
    ws['B2'] = "表タイトル"
    ws.merge_cells('B2:E2')
    ws['B2'].font = Font(bold=True)
    ws['B2'].alignment = Alignment(horizontal='center')

    # Apply border only to outer edges of merged cell
    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}2'].border = thin_border

    # Row 2: Headers
    headers = ['No.', 'Name', 'Value', 'Status']
    for idx, header in enumerate(headers, start=2):
        cell = ws.cell(row=3, column=idx)
        cell.value = header
        cell.border = thin_border
        cell.font = Font(bold=True)

    # Row 3-4: Data with some merged cells
    ws['B4'] = '1'
    ws['C4'] = 'Item A'
    ws.merge_cells('D4:E4')  # Merge value and status
    ws['D4'].value = 'OK - 正常'

    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}4'].border = thin_border

    ws['B5'] = '2'
    ws['C5'] = 'Item B'
    ws['D5'] = '100'
    ws['E5'] = 'OK'

    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}5'].border = thin_border

    ws['B1'] = "Content above"
    ws['B6'] = "Content below"

    wb.save(output_path)
    print(f"✓ Test Case 2: Merged cells - {output_path}")


def create_complex_test_3(output_path: str):
    """
    Test Case 3: Table with partial borders.

    Some Excel tables only have borders on certain edges:
    - Only top/bottom (horizontal lines between rows)
    - Only left/right on outer edges

    This is common in minimalist table designs.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "PartialBorders"

    # Only top and bottom borders
    horizontal_only = Border(
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # Full borders for outer edges
    full_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # Row 1: Title with full border
    ws['B2'] = "表タイトル"
    ws.merge_cells('B2:E2')
    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}2'].border = full_border

    # Row 2: Headers - only horizontal borders, left/right on edges
    headers = ['Col1', 'Col2', 'Col3', 'Col4']
    for idx, header in enumerate(headers, start=2):
        cell = ws.cell(row=3, column=idx)
        cell.value = header
        cell.font = Font(bold=True)

        # Left edge gets left border
        if idx == 2:
            cell.border = Border(
                left=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
        # Right edge gets right border
        elif idx == 5:
            cell.border = Border(
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
        # Middle cells only get top/bottom
        else:
            cell.border = horizontal_only

    # Data rows - same pattern
    for row in range(4, 7):
        for col in range(2, 6):
            cell = ws.cell(row=row, column=col)
            cell.value = f"Data{row-3},{col-1}"

            if col == 2:
                cell.border = Border(
                    left=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
            elif col == 5:
                cell.border = Border(
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
            else:
                cell.border = horizontal_only

    ws['B1'] = "Content above"
    ws['B7'] = "Content below"

    wb.save(output_path)
    print(f"✓ Test Case 3: Partial borders - {output_path}")


def test_scenario(test_name: str, file_path: str, expected_tables: int = 1):
    """Test a specific scenario."""
    print(f"\n{'=' * 80}")
    print(f"TEST: {test_name}")
    print(f"{'=' * 80}")

    detector = BorderTableDetector()

    # Test detection
    print("\n1. Detecting tables...")
    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']} ({table['cell_count']} cells)")

    # Test separator insertion
    output_path = file_path.replace('.xlsx', '_separated.xlsx')
    print(f"\n2. Inserting separators...")
    stats = detector.insert_separator_rows(file_path, output_path)

    print(f"  Inserted {stats['rows_inserted_above'] + stats['rows_inserted_below']} separator rows")

    # Verify
    print(f"\n3. Verifying...")

    passed = True

    if len(tables) != expected_tables:
        print(f"  ✗ FAIL: Expected {expected_tables} table(s), detected {len(tables)}")
        print(f"       This means the table was incorrectly split into multiple regions!")
        passed = False
    else:
        print(f"  ✓ PASS: Detected expected number of tables ({expected_tables})")

    # Check for separators inside table range
    from openpyxl import load_workbook
    wb = load_workbook(output_path)
    ws = wb.active

    if len(tables) > 0:
        # For simplicity, check the first table
        table = tables[0]
        start_row = table['start_row']
        end_row = table['end_row']

        # After insertion, separators should be BEFORE start_row and AFTER end_row
        # NOT between start_row and end_row

        # We need to adjust for inserted rows
        # If separator inserted above, table shifts down by 1
        # Check original table range + 1 for content

        empty_rows = []
        for row_num in range(start_row + 1, end_row + 2):  # Accounting for shift
            row_empty = True
            for col in range(1, ws.max_column + 1):
                cell = ws.cell(row=row_num, column=col)
                if cell.value is not None and str(cell.value).strip():
                    row_empty = False
                    break
            if row_empty:
                empty_rows.append(row_num)

        if empty_rows:
            print(f"  ✗ FAIL: Found {len(empty_rows)} empty row(s) inside table range: {empty_rows}")
            print(f"       These are separators incorrectly inserted INSIDE the table!")
            passed = False
        else:
            print(f"  ✓ PASS: No separators found inside table")

    return passed


def main():
    """Run all complex tests."""
    print("=" * 80)
    print("COMPLEX BORDER DETECTION TESTS")
    print("=" * 80)

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

    print("\nCreating test files...")

    test1_file = tmp_dir / "test_varying_borders.xlsx"
    test2_file = tmp_dir / "test_merged_cells.xlsx"
    test3_file = tmp_dir / "test_partial_borders.xlsx"

    create_complex_test_1(str(test1_file))
    create_complex_test_2(str(test2_file))
    create_complex_test_3(str(test3_file))

    print("\nRunning tests...")

    results = []
    results.append(("Varying Border Thickness", test_scenario(
        "Varying Border Thickness", str(test1_file), expected_tables=1
    )))

    results.append(("Merged Cells", test_scenario(
        "Merged Cells", str(test2_file), expected_tables=1
    )))

    results.append(("Partial Borders", test_scenario(
        "Partial Borders", str(test3_file), expected_tables=1
    )))

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

    passed_count = sum(1 for _, passed in results if passed)
    total_count = len(results)

    for name, passed in results:
        status = "✓ PASSED" if passed else "✗ FAILED"
        print(f"{status}: {name}")

    print(f"\nResult: {passed_count}/{total_count} tests passed")

    if passed_count == total_count:
        print("\n✓ ALL TESTS PASSED")
        return 0
    else:
        print(f"\n✗ {total_count - passed_count} TEST(S) FAILED")
        print("\nCheck output files in tmp/ directory for visual inspection.")
        return 1


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