"""Script to create sample Excel files for merged cell resolution testing."""

from openpyxl import Workbook
from pathlib import Path

# Get fixtures directory
fixtures_dir = Path(__file__).parent


def create_merged_data_sample():
    """Create test_merged_data.xlsx with merged cells in data rows."""
    wb = Workbook()
    sheet = wb.active
    sheet.title = "Sheet1"

    # Header row
    sheet["A1"] = "Category"
    sheet["B1"] = "Product"
    sheet["C1"] = "Q1"
    sheet["D1"] = "Q2"
    sheet["E1"] = "Q3"

    # Data rows with merged cells in Category column
    # Group A spans rows 2-4
    sheet["A2"] = "Group A"
    sheet.merge_cells("A2:A4")
    sheet["B2"] = "Widget"
    sheet["C2"] = "100"
    sheet["D2"] = "150"
    sheet["E2"] = "200"

    sheet["B3"] = "Gadget"
    sheet["C3"] = "80"
    sheet["D3"] = "120"
    sheet["E3"] = "160"

    sheet["B4"] = "Tool"
    sheet["C4"] = "50"
    sheet["D4"] = "75"
    sheet["E4"] = "100"

    # Group B spans rows 5-6
    sheet["A5"] = "Group B"
    sheet.merge_cells("A5:A6")
    sheet["B5"] = "Component"
    sheet["C5"] = "200"
    sheet["D5"] = "250"
    sheet["E5"] = "300"

    sheet["B6"] = "Part"
    sheet["C6"] = "150"
    sheet["D6"] = "180"
    sheet["E6"] = "220"

    wb.save(fixtures_dir / "test_merged_data.xlsx")
    print("Created test_merged_data.xlsx")


def create_complex_merges_sample():
    """Create test_complex_merges.xlsx with complex merged regions."""
    wb = Workbook()
    sheet = wb.active
    sheet.title = "Sheet1"

    # Complex layout with multiple merge types

    # Title row (merged across all columns)
    sheet["A1"] = "Sales Report 2024"
    sheet.merge_cells("A1:E1")

    # Header rows with merged cells
    sheet["A2"] = "Region"
    sheet.merge_cells("A2:A3")  # Vertical merge in headers

    sheet["B2"] = "Products"
    sheet.merge_cells("B2:C2")  # Horizontal merge in headers
    sheet["B3"] = "Type A"
    sheet["C3"] = "Type B"

    sheet["D2"] = "Summary"
    sheet.merge_cells("D2:E2")
    sheet["D3"] = "Total"
    sheet["E3"] = "Average"

    # Data rows with merged regions
    sheet["A4"] = "North"
    sheet.merge_cells("A4:A5")
    sheet["B4"] = "100"
    sheet["C4"] = "150"
    sheet["D4"] = "250"
    sheet["E4"] = "125"

    sheet["B5"] = "120"
    sheet["C5"] = "180"
    sheet["D5"] = "300"
    sheet["E5"] = "150"

    sheet["A6"] = "South"
    sheet.merge_cells("A6:A7")
    sheet["B6"] = "90"
    sheet["C6"] = "130"
    sheet["D6"] = "220"
    sheet["E6"] = "110"

    sheet["B7"] = "110"
    sheet["C7"] = "160"
    sheet["D7"] = "270"
    sheet["E7"] = "135"

    # Block merge in data (simulating a note or special cell)
    sheet["B8"] = "Special Note Area"
    sheet.merge_cells("B8:C9")
    sheet["A8"] = "West"
    sheet["D8"] = "200"
    sheet["E8"] = "100"
    sheet["A9"] = "East"
    sheet["D9"] = "180"
    sheet["E9"] = "90"

    wb.save(fixtures_dir / "test_complex_merges.xlsx")
    print("Created test_complex_merges.xlsx")


if __name__ == "__main__":
    create_merged_data_sample()
    create_complex_merges_sample()
    print("All sample Excel files created successfully!")