#!/usr/bin/env python3
"""Check what text is actually in the Excel sheets using openpyxl."""

import sys
import subprocess
import tempfile
from pathlib import Path
from openpyxl import load_workbook


def convert_xls_to_xlsx(xls_path: str) -> str:
    """Convert .xls to .xlsx using LibreOffice on macOS."""
    temp_dir = tempfile.mkdtemp()

    # Use LibreOffice on macOS
    soffice_path = '/Applications/LibreOffice.app/Contents/MacOS/soffice'

    result = subprocess.run([
        soffice_path,
        '--headless',
        '--convert-to', 'xlsx',
        '--outdir', temp_dir,
        xls_path
    ], capture_output=True, text=True, timeout=60)

    if result.returncode != 0:
        print(f"Conversion failed: {result.stderr}")
        raise Exception("Conversion failed")

    # Find converted file
    xlsx_files = list(Path(temp_dir).glob('*.xlsx'))
    if not xlsx_files:
        raise Exception("No .xlsx file found after conversion")

    return str(xlsx_files[0])


def check_sheet_content(file_path: str):
    """Check actual content in Excel sheets."""

    # Convert if needed
    if file_path.endswith('.xls'):
        print(f"Converting {file_path} to .xlsx...\n")
        xlsx_path = convert_xls_to_xlsx(file_path)
        print(f"Converted to: {xlsx_path}\n")
    else:
        xlsx_path = file_path

    # Load workbook
    wb = load_workbook(xlsx_path, read_only=True, data_only=True)

    print(f"Sheet names: {wb.sheetnames}\n")

    # Check target sheets
    target_sheets = ['ステータス戻し実施可否判定', 'チェック対象判定']

    for sheet_name in target_sheets:
        print(f"\n{'='*80}")
        print(f"Sheet: {sheet_name}")
        print(f"{'='*80}\n")

        if sheet_name not in wb.sheetnames:
            print(f"  ✗ Sheet not found!")
            continue

        sheet = wb[sheet_name]

        # Count non-empty cells
        non_empty_cells = []
        for row_idx, row in enumerate(sheet.iter_rows(values_only=True), 1):
            for col_idx, cell_value in enumerate(row, 1):
                if cell_value is not None and str(cell_value).strip():
                    non_empty_cells.append((row_idx, col_idx, str(cell_value).strip()))

        print(f"  Total non-empty cells: {len(non_empty_cells)}")

        if non_empty_cells:
            print(f"\n  First 20 cells with content:")
            for row, col, value in non_empty_cells[:20]:
                col_letter = chr(64 + col) if col <= 26 else f"Col{col}"
                value_preview = value[:100] if len(value) > 100 else value
                print(f"    {col_letter}{row}: {value_preview}")
        else:
            print(f"  ✗ Sheet is EMPTY - no text content found!")

    wb.close()


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python check_sheet_content.py <path_to_excel_file>")
        sys.exit(1)

    check_sheet_content(sys.argv[1])