import io
import os
import re
import openpyxl
from app.importer import Record


def _build_export_name(base: str) -> str:
    # ES_2026_04Fic → ES_2026_04_DETTAGLI_Fic
    # Inserisce _DETTAGLI_ dopo il pattern YYYY_MM
    m = re.search(r'(\d{4}_\d{2})(.*)', base)
    if m:
        return base[:m.start(2)] + "_DETTAGLI_" + m.group(2).lstrip("_")
    return base + "_DETTAGLI"


def export_xlsx_sidecar(
    records: list[Record],
    source_file_path: str,
    source_basename: str = "",
    header_row: int = 2,
) -> tuple[bytes, str]:
    """
    Apre il file sorgente e aggiunge colonne sidecar allineate tramite source_row.

    Colonne aggiunte sempre: 1 TV INVERTITA | CODICE
    Solo Germania (header_row=6): + CHANNEL | WEEKDAY | DATE (tutte le righe valorizzate)

    Inversione 1TV: S→N | N→S | C_notte/C_recap/""→""
    """
    wb = openpyxl.load_workbook(source_file_path, data_only=True)
    ws = wb.active

    is_germany = (header_row == 6)

    # Detect Channel column from header row (for Mediametrie in-place overwrite)
    channel_col = None
    for cell in ws[header_row]:
        if str(cell.value or '').strip() == 'Channel':
            channel_col = cell.column
            break

    base_col = ws.max_column
    c_invertita = base_col + 1
    c_codice    = base_col + 2
    c_channel   = base_col + 3
    c_weekday   = base_col + 4
    c_date      = base_col + 5

    ws.cell(row=header_row, column=c_invertita, value="1 TV INVERTITA")
    ws.cell(row=header_row, column=c_codice,    value="CODICE")
    if is_germany:
        ws.cell(row=header_row, column=c_channel, value="Channel")
        ws.cell(row=header_row, column=c_weekday, value="Weekday")
        ws.cell(row=header_row, column=c_date,    value="Date")

    for rec in records:
        if rec.source_row < 3:
            continue
        ptv = rec.prima_tv or ""
        invertita = "N" if ptv == "S" else ("S" if ptv == "N" else "")
        ws.cell(row=rec.source_row, column=c_invertita, value=invertita)
        ws.cell(row=rec.source_row, column=c_codice,    value=rec.cod_oss or None)
        if is_germany:
            ws.cell(row=rec.source_row, column=c_channel, value=rec.channel or None)
            ws.cell(row=rec.source_row, column=c_weekday, value=rec.weekday or None)
            date_str = rec.date.strftime("%d.%m.%Y") if rec.date else None
            ws.cell(row=rec.source_row, column=c_date, value=date_str)
        elif channel_col:
            ws.cell(row=rec.source_row, column=channel_col, value=rec.channel or None)

    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)

    base = os.path.splitext(source_basename or os.path.basename(source_file_path))[0]
    filename = _build_export_name(base) + ".xlsx"

    return buf.read(), filename