#!/usr/bin/env python3
"""
Test script to reproduce and verify the border detection bug.

Problem: BorderTableDetector inserts separator rows INSIDE tables,
not just before/after tables.

This test creates a controlled Excel file and verifies separator placement.
"""

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_test_excel_with_table(output_path: str):
    """
    Create a test Excel file with a table that has consistent borders.

    The table structure:
    Row 1: Title (merged, with borders)
    Row 2: Headers (with borders)
    Row 3-5: Data rows (with borders)

    Expected behavior: Separators should ONLY be at top and bottom of table,
    NOT between rows 2-3 or 3-4 or 4-5.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "TestSheet"

    # Define border style - all sides
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

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

    # Apply borders to title
    for col in ['B', 'C', 'D', 'E']:
        ws[f'{col}2'].border = thin_border

    # Row 2: Headers
    headers = ['項目', '条件', '備考', '状態']
    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)
        cell.alignment = Alignment(horizontal='center')

    # Rows 3-5: Data rows
    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
            cell.alignment = Alignment(horizontal='left')

    # Add some content above and below table to test separator insertion
    ws['B1'] = "Content above table"
    ws['B7'] = "Content below table"

    wb.save(output_path)
    print(f"✓ Created test Excel file: {output_path}")
    print(f"  Table range: B2:E6 (title + headers + 3 data rows)")
    print(f"  Content above: B1")
    print(f"  Content below: B7")


def test_border_detection(excel_path: str):
    """Test border detection on the created file."""
    print("\n" + "=" * 80)
    print("TEST 1: Detect Tables")
    print("=" * 80)

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

    print(f"\nDetected {len(tables)} table(s)")

    for idx, table in enumerate(tables, start=1):
        print(f"\nTable {idx}:")
        print(f"  Sheet:      {table['sheet']}")
        print(f"  Range:      {table['range']}")
        print(f"  Rows:       {table['start_row']} → {table['end_row']}")
        print(f"  Columns:    {table['start_col']} → {table['end_col']}")
        print(f"  Cell count: {table['cell_count']}")

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

    if len(tables) == 1:
        print("✓ PASS: Detected exactly 1 table (expected)")
        table = tables[0]

        # The table should span rows 2-6 (title row 2 + header row 3 + data rows 4-6)
        expected_start = 2
        expected_end = 6

        if table['start_row'] == expected_start and table['end_row'] == expected_end:
            print(f"✓ PASS: Table boundaries correct (rows {expected_start}-{expected_end})")
        else:
            print(f"✗ FAIL: Table boundaries wrong")
            print(f"  Expected: rows {expected_start}-{expected_end}")
            print(f"  Got:      rows {table['start_row']}-{table['end_row']}")
            return False
    else:
        print(f"✗ FAIL: Detected {len(tables)} tables instead of 1")
        print("  This indicates the table was split incorrectly!")
        return False

    return True


def test_separator_insertion(excel_path: str, output_path: str):
    """Test separator row insertion."""
    print("\n" + "=" * 80)
    print("TEST 2: Insert Separator Rows")
    print("=" * 80)

    detector = BorderTableDetector()
    stats = detector.insert_separator_rows(excel_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(f"  Total rows inserted:   {stats['rows_inserted_above'] + stats['rows_inserted_below']}")

    print(f"\nDetails:")
    for detail in stats['details']:
        print(f"  {detail['sheet']}: {detail['range']}")
        if detail['inserted_above']:
            print(f"    - Separator inserted ABOVE table")
        if detail['inserted_below']:
            print(f"    - Separator inserted BELOW table")

    # Verify the output file
    print("\n" + "-" * 80)
    print("VERIFICATION:")

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

    # Check if rows were inserted incorrectly in the middle
    print("\nScanning for empty rows within table range...")

    # After insertion, table should be:
    # Row 1: Content above
    # Row 2: [SEPARATOR] - inserted before table
    # Row 3: Title
    # Row 4: Headers
    # Row 5: Data
    # Row 6: Data
    # Row 7: Data
    # Row 8: [SEPARATOR] - inserted after table
    # Row 9: Content below

    empty_rows_in_table = []

    # Check rows 3-7 (title through last data row)
    for row_num in range(3, 8):
        row_empty = True
        for col in range(2, 6):  # Columns B-E
            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_in_table.append(row_num)
            print(f"  ✗ Found empty row at {row_num} - SEPARATOR INSERTED INSIDE TABLE!")

    if empty_rows_in_table:
        print(f"\n✗ FAIL: Found {len(empty_rows_in_table)} separator(s) INSIDE the table")
        print(f"  Empty rows: {empty_rows_in_table}")
        return False
    else:
        print("✓ PASS: No separators found inside table")

    # Verify separator placement
    row_2_empty = not any(
        ws.cell(row=2, column=col).value for col in range(2, 6)
    )
    row_8_empty = not any(
        ws.cell(row=8, column=col).value for col in range(2, 6)
    )

    if row_2_empty:
        print("✓ PASS: Separator correctly placed BEFORE table (row 2)")
    else:
        print("✗ FAIL: No separator before table")
        return False

    if row_8_empty:
        print("✓ PASS: Separator correctly placed AFTER table (row 8)")
    else:
        print("✗ FAIL: No separator after table")
        return False

    return True


def main():
    """Run all tests."""
    print("=" * 80)
    print("BORDER DETECTION BUG TEST SUITE")
    print("=" * 80)

    # Create test files in tmp directory
    tmp_dir = Path(__file__).parent.parent.parent / "tmp"
    tmp_dir.mkdir(exist_ok=True)

    test_file = tmp_dir / "test_border_detection.xlsx"
    output_file = tmp_dir / "test_border_detection_separated.xlsx"

    # Clean up old files
    for f in [test_file, output_file]:
        if f.exists():
            f.unlink()

    # Step 1: Create test file
    print("\nStep 1: Creating test Excel file...")
    create_test_excel_with_table(str(test_file))

    # Step 2: Test border detection
    print("\nStep 2: Testing border detection...")
    detection_passed = test_border_detection(str(test_file))

    # Step 3: Test separator insertion
    print("\nStep 3: Testing separator insertion...")
    insertion_passed = test_separator_insertion(str(test_file), str(output_file))

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

    tests_passed = 0
    tests_total = 2

    if detection_passed:
        print("✓ Test 1: Border Detection - PASSED")
        tests_passed += 1
    else:
        print("✗ Test 1: Border Detection - FAILED")

    if insertion_passed:
        print("✓ Test 2: Separator Insertion - PASSED")
        tests_passed += 1
    else:
        print("✗ Test 2: Separator Insertion - FAILED")

    print(f"\nResult: {tests_passed}/{tests_total} tests passed")

    if tests_passed == tests_total:
        print("\n✓ ALL TESTS PASSED - Bug is fixed!")
        return 0
    else:
        print(f"\n✗ {tests_total - tests_passed} TEST(S) FAILED - Bug still exists")
        print(f"\nTest files saved to:")
        print(f"  Input:  {test_file}")
        print(f"  Output: {output_file}")
        print("\nOpen these files in Excel to inspect the issue visually.")
        return 1


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