import random
import re
import string
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.worksheet.worksheet import Worksheet

from product_staging.logic.utils.spreadsheet.openpyxl import maximum_row

ID_COLUMNS = ["UPC", "ISRC"]
PROJECT_CODE_COLUMN = "Project Code*"
PRODUCT_CODE_COLUMN = "Product Code*"
PATTERN = re.compile(r"^[A-Z]{3}\d{6}$")


def get_column_index(*, worksheet: Worksheet, column: str) -> int | None:
    """Get the index of the Project Code column in a worksheet."""
    header_row = [cell.value for cell in worksheet[2]]
    return next(
        (i for i, v in enumerate(header_row) if isinstance(v, str) and v == column),
        None,
    )


def random_code(prefix: str | None = None) -> str:
    prefix = prefix or "".join(random.choices(string.ascii_uppercase, k=3))
    digits = "".join(random.choices(string.digits, k=6))
    code = prefix + digits

    # Optional regex validation (will always match)
    assert PATTERN.match(code), f"Generated code failed regex: {code}"

    return code


def randomize_codes(path: Path) -> None:
    """
    Randomize project and product codes for an XLSX spreadsheet located at <path>.
    """
    workbook = load_workbook(path)
    project_prefix = "".join(random.choices(string.ascii_uppercase, k=3))
    product_prefix = "".join(random.choices(string.ascii_uppercase, k=3))
    projects = workbook["Projects"]
    products = workbook["Products"]

    project_code_index = get_column_index(
        worksheet=projects, column=PROJECT_CODE_COLUMN
    )
    assert project_code_index

    product_code_index = get_column_index(
        worksheet=products, column=PRODUCT_CODE_COLUMN
    )
    assert product_code_index

    project_codes = set()
    product_codes = set()

    for row_index in range(3, maximum_row(projects) + 1):
        cell = projects.cell(row=row_index, column=project_code_index + 1)
        if not isinstance(cell, MergedCell):
            project_codes.add(cell.value)

    for row_index in range(3, maximum_row(products) + 1):
        cell = products.cell(row=row_index, column=product_code_index + 1)
        if not isinstance(cell, MergedCell):
            product_codes.add(cell.value)

    project_code_map = {}
    product_code_map = {}
    used_project_codes = set()
    used_product_codes = set()

    for old_code in project_codes:
        new_code = random_code(project_prefix)
        while new_code in used_project_codes:
            new_code = random_code(project_prefix)
        project_code_map[old_code] = new_code
        used_project_codes.add(new_code)

    for old_code in product_codes:
        new_code = random_code(product_prefix)
        while new_code in used_product_codes:
            new_code = random_code(product_prefix)
        product_code_map[old_code] = new_code
        used_product_codes.add(new_code)

    for worksheet in workbook.worksheets:
        project_code_index = get_column_index(
            worksheet=worksheet, column=PROJECT_CODE_COLUMN
        )
        # If there is a project code column, use randomized code
        if project_code_index is not None:
            max_row: int = maximum_row(worksheet)
            for row_index in range(3, max_row + 1):
                cell = worksheet.cell(row=row_index, column=project_code_index + 1)
                if not isinstance(cell, MergedCell):
                    # Replace the value with the new randomized code
                    cell.value = project_code_map.get(cell.value, None)

        # If there is a product code column, use randomized code
        product_code_index = get_column_index(
            worksheet=worksheet, column=PRODUCT_CODE_COLUMN
        )
        if product_code_index is not None:
            max_row: int = maximum_row(worksheet)
            for row_index in range(3, max_row + 1):
                cell = worksheet.cell(row=row_index, column=product_code_index + 1)
                if not isinstance(cell, MergedCell):
                    # Replace the value with the new randomized code
                    cell.value = product_code_map.get(cell.value, None)

    workbook.save(path)