import openpyxl
from datetime import datetime, time, timedelta
from dataclasses import dataclass, field
from typing import Optional


@dataclass
class Record:
    channel: str = ""
    weekday: str = ""
    date: Optional[datetime] = None
    start_time: Optional[time] = None
    title: str = ""
    description: str = ""
    duration: str = ""
    share_4plus: Optional[float] = None
    rtg_4plus: Optional[float] = None
    share_comm: Optional[float] = None
    rtg_comm: Optional[float] = None
    share_men16plus: Optional[float] = None
    rtg_men16plus: Optional[float] = None
    share_women16plus: Optional[float] = None
    rtg_women16plus: Optional[float] = None
    share_men1659: Optional[float] = None
    rtg_men1659: Optional[float] = None
    share_women1659: Optional[float] = None
    rtg_women1659: Optional[float] = None
    cod_oss: str = ""
    agganciato: bool = False
    prima_tv: str = ""  # "S", "N", "C_notte", "C_recap", or ""
    tooltip: str = ""   # testo aggancio: TITOLO | TIPOLOGIA | RETE | PAESE | ANNO | CodOss
    raw_data: list = field(default_factory=list)
    source_row: int = 0  # riga xlsx originale (1-based), per allineamento export sidecar


def _parse_duration(val) -> str:
    if val is None:
        return ""
    if isinstance(val, timedelta):
        total_seconds = int(val.total_seconds())
        hours = total_seconds // 3600
        minutes = (total_seconds % 3600) // 60
        seconds = total_seconds % 60
        return f"{hours:02d}{minutes:02d}:{seconds:02d}"
    return str(val)


def _parse_float(val) -> Optional[float]:
    if val is None:
        return None
    try:
        return float(val)
    except (ValueError, TypeError):
        return None


def _parse_float_eu(val) -> Optional[float]:
    if val is None:
        return None
    try:
        return float(str(val).replace(',', '.'))
    except (ValueError, TypeError):
        return None


def _parse_date_str(val) -> Optional[datetime]:
    if val is None:
        return None
    if isinstance(val, datetime):
        return val
    s = str(val).strip()
    for fmt in ("%d/%m/%Y", "%d.%m.%Y"):
        try:
            return datetime.strptime(s, fmt)
        except ValueError:
            pass
    return None


def _parse_time_str(val) -> Optional[time]:
    if val is None:
        return None
    if isinstance(val, time):
        return val
    if isinstance(val, datetime):
        return val.time()
    if isinstance(val, timedelta):
        total = int(val.total_seconds())
        return time(total // 3600 % 24, (total % 3600) // 60, total % 60)
    s = str(val).strip()
    for fmt in ("%H:%M:%S", "%H:%M"):
        try:
            t = datetime.strptime(s, fmt)
            return t.time()
        except ValueError:
            pass
    # Broadcast convention: ore >= 24 (es. "24:25:39" = 00:25 del giorno dopo)
    parts = s.split(":")
    if len(parts) >= 2:
        try:
            h, m = int(parts[0]), int(parts[1])
            sc = int(parts[2]) if len(parts) > 2 else 0
            return time(h % 24, m, sc)
        except (ValueError, TypeError):
            pass
    return None


def _raw_cell(v) -> str:
    if v is None:
        return ""
    if isinstance(v, timedelta):
        return _parse_duration(v)
    if isinstance(v, datetime):
        return v.strftime("%d/%m/%Y %H:%M:%S")
    return str(v)


_INDIVIDUALS_GROUPS = ("Individuals 4+", "Individuals 2+ (Combined)",
                       "Individuals 3+", "Individuals 2+", "Individuals 5+")


def _load_xlsx_mediametrie(ws) -> tuple[list[Record], list[str]]:
    all_rows = list(ws.iter_rows(min_row=1, max_row=2, values_only=True))
    row1 = all_rows[0]  # group headers
    row2 = all_rows[1]  # field sub-headers

    # Combined headers for diagnostica (row1 / row2)
    headers = []
    for i in range(max(len(row1), len(row2))):
        h1 = _raw_cell(row1[i]) if i < len(row1) else ""
        h2 = _raw_cell(row2[i]) if i < len(row2) else ""
        headers.append(f"{h1} / {h2}" if h1 and h2 else h1 or h2 or f"Col{i+1}")

    # Detect basic field columns from row2 (like VBA sub_RECUPERA_COLONNE)
    col_channel = col_weekday = col_date = col_start_time = None
    col_program = col_duration = None
    col_share = None  # Shr% of main Individuals group

    for i, v in enumerate(row1):
        if v in _INDIVIDUALS_GROUPS and col_share is None:
            col_share = i + 1  # Shr% is one column to the right (VBA: Cells(2, i+1))

    for i, v in enumerate(row2):
        if v == "Channel":              col_channel = i
        elif v in ("Program / Secondary", "Program"): col_program = i
        elif v == "Date":               col_date = i
        elif v == "Start Time":         col_start_time = i
        elif v == "Weekday":            col_weekday = i
        elif v == "Duration":           col_duration = i

    records = []
    for row_num, row in enumerate(ws.iter_rows(min_row=3, values_only=True), start=3):
        if not any(row):
            continue
        channel = str(row[col_channel] or "").strip() if col_channel is not None else ""
        if not channel:
            continue

        date_val = _parse_date_str(row[col_date]) if col_date is not None else None
        time_val = _parse_time_str(row[col_start_time]) if col_start_time is not None else None
        duration = _parse_duration(row[col_duration]) if col_duration is not None else ""
        weekday = str(row[col_weekday] or "").strip() if col_weekday is not None else ""

        program_field = str(row[col_program] or "").strip() if col_program is not None else ""
        if " / " in program_field:
            title, description = program_field.split(" / ", 1)
        elif program_field.endswith(" /"):
            title, description = program_field[:-2].strip(), ""
        else:
            title, description = program_field, ""

        def fc(i):
            return _parse_float_eu(row[i]) if i is not None and len(row) > i else None

        share_idx = col_share
        rec = Record(
            channel=channel,
            weekday=weekday,
            date=date_val,
            start_time=time_val,
            title=title.strip(),
            description=description.strip(),
            duration=duration,
            share_4plus=fc(share_idx),
            rtg_4plus=fc(share_idx + 2) if share_idx else None,
            share_comm=fc(43),
            rtg_comm=fc(45),
            share_men16plus=fc(73),
            rtg_men16plus=fc(75),
            share_women16plus=fc(93),
            rtg_women16plus=fc(95),
            share_men1659=fc(68),
            rtg_men1659=fc(70),
            share_women1659=fc(88),
            rtg_women1659=fc(90),
            raw_data=[_raw_cell(v) for v in row],
            source_row=row_num,
        )
        records.append(rec)
    return records, headers


def _load_xlsx_germany(ws) -> tuple[list[Record], list[str]]:
    """Format: 4 metadata rows + 1 blank, then 2 header rows (6-7), data from row 8.
    Channel/Weekday/Date are sparse (only on first row of each group) — fill-down applied."""
    row6 = [ws.cell(6, c).value for c in range(1, ws.max_column + 1)]
    row7 = [ws.cell(7, c).value for c in range(1, ws.max_column + 1)]

    headers = []
    for i in range(max(len(row6), len(row7))):
        h1 = _raw_cell(row6[i]) if i < len(row6) else ""
        h2 = _raw_cell(row7[i]) if i < len(row7) else ""
        headers.append(f"{h1} / {h2}" if h1 and h2 else h1 or h2 or f"Col{i+1}")

    # Column indices (0-based) from row 6/7 structure:
    # 0:Channel  1:Weekday  2:Date  3:Start Time  4:Program/Secondary  5:Duration
    # 6:Ind3+ Shr%  7:Ind3+ Rat#
    # 8:Women14+ Shr%  9:Women14+ Rat#
    # 10:Men14+ Shr%  11:Men14+ Rat#
    # 12:Ind14-49 Shr%  13:Ind14-49 Rat#
    # 14:Women14-49 Shr%  15:Women14-49 Rat#
    # 20:Men14-49 Shr%  21:Men14-49 Rat#

    last_channel = ""
    last_weekday = ""
    last_date = None

    records = []
    for row_num, row in enumerate(ws.iter_rows(min_row=8, values_only=True), start=8):
        if not any(row):
            continue

        channel = str(row[0] or "").strip() or last_channel
        weekday = str(row[1] or "").strip() or last_weekday
        raw_date = row[2] if row[2] is not None else last_date

        if channel:
            last_channel = channel
        if weekday:
            last_weekday = weekday
        if raw_date is not None:
            last_date = raw_date

        start_time = row[3] if isinstance(row[3], time) else _parse_time_str(row[3])
        date_val = _parse_date_str(raw_date) if not isinstance(raw_date, datetime) else raw_date

        program_field = str(row[4] or "").strip()
        if " / " in program_field:
            title, description = program_field.split(" / ", 1)
        elif program_field.endswith(" /"):
            title, description = program_field[:-2].strip(), ""
        else:
            title, description = program_field, ""

        if row[3] is None:  # no Start Time → riga metadata (es. "Quelle:")
            continue
        if not channel and not title:
            continue

        def fc(i, row=row):
            return _parse_float(row[i]) if i < len(row) else None

        rec = Record(
            channel=channel,
            weekday=weekday,
            date=date_val,
            start_time=start_time,
            title=title.strip(),
            description=description.strip(),
            duration=_parse_duration(row[5]),
            share_4plus=fc(6),      # Individuals 3+
            rtg_4plus=fc(7),
            share_comm=fc(12),      # Individuals 14-49
            rtg_comm=fc(13),
            share_women16plus=fc(8),    # Women 14+
            rtg_women16plus=fc(9),
            share_men16plus=fc(10),     # Men 14+
            rtg_men16plus=fc(11),
            share_women1659=fc(14),     # Women 14-49
            rtg_women1659=fc(15),
            share_men1659=fc(20),       # Men 14-49
            rtg_men1659=fc(21),
            raw_data=[_raw_cell(v) for v in row],
            source_row=row_num,
        )
        records.append(rec)

    return records, headers


_RECAP_SUFFIXES = (
    ":PRESENTACION", ":PRESENTACION)", ":EN EL PROXIMO CAPITULO",
    ":EN CAPITULOS ANTERIORES", ":EN EL CAPITULO ANTERIOR",
    ":MARTES", ":MARTES)", ":DOMINGO", ":DOMINGO)", ":MAÑANA)", ":MAÑANA",
    ":ESTA NOCHE", ":ESTA NOCHE)", ":MIERCOLES", ":MIERCOLES)",
    ":ULTIMO CAPITULO", ":ULTIMOS CAPITULOS)", ":ULTIMOS CAPITULOS",
    ":NUEVA TEMPORADA", ":LUNES)", ":PREVIO", ":CONTINUACION",
    ":PREPARATE PARA LA DESPEDIDA",
)


def _auto_prima_tv(records: list[Record]) -> None:
    for rec in records:
        if rec.prima_tv:
            continue
        t = rec.start_time
        if t is not None:
            h, m = t.hour, t.minute
            if (h >= 3 and h <= 6) or (h == 2 and m >= 30):
                rec.prima_tv = "C_notte"
                continue
        title_upper = rec.title.upper()
        if any(title_upper.endswith(s) for s in _RECAP_SUFFIXES):
            rec.prima_tv = "C_recap"


def load_xlsx(path: str) -> tuple[list[Record], list[str], int]:
    """Returns (records, headers, export_header_row)."""
    wb = openpyxl.load_workbook(path, data_only=True)
    ws = wb.active

    if ws.max_column > 50:
        records, headers = _load_xlsx_mediametrie(ws)
        _auto_prima_tv(records)
        return records, headers, 2

    first_cell = str(ws.cell(1, 1).value or "")
    if first_cell.startswith("Auswertungsmappe"):
        records, headers = _load_xlsx_germany(ws)
        _auto_prima_tv(records)
        return records, headers, 6

    # Marketing format Spagna: 2 header rows, data from row 3
    mkt_rows = list(ws.iter_rows(min_row=1, max_row=2, values_only=True))
    r1, r2 = mkt_rows[0], mkt_rows[1]
    headers = []
    for i in range(max(len(r1), len(r2))):
        h1 = _raw_cell(r1[i]) if i < len(r1) else ""
        h2 = _raw_cell(r2[i]) if i < len(r2) else ""
        headers.append(f"{h1} / {h2}" if h1 and h2 else h1 or h2 or f"Col{i+1}")

    records = []
    for row_num, row in enumerate(ws.iter_rows(min_row=3, values_only=True), start=3):
        if not any(row):
            continue
        (channel, weekday, date, start_time, title, description, duration,
         share_4plus, rtg_4plus, share_comm, rtg_comm,
         share_men16plus, rtg_men16plus, share_women16plus, rtg_women16plus,
         share_men1659, rtg_men1659, share_women1659, rtg_women1659) = row[:19]

        if not channel and not title:
            continue

        rec = Record(
            channel=str(channel or "").strip(),
            weekday=str(weekday or "").strip(),
            date=_parse_date_str(date),
            start_time=_parse_time_str(start_time),
            title=str(title or "").strip(),
            description=str(description or "").strip(),
            duration=_parse_duration(duration),
            share_4plus=_parse_float(share_4plus),
            rtg_4plus=_parse_float(rtg_4plus),
            share_comm=_parse_float(share_comm),
            rtg_comm=_parse_float(rtg_comm),
            share_men16plus=_parse_float(share_men16plus),
            rtg_men16plus=_parse_float(rtg_men16plus),
            share_women16plus=_parse_float(share_women16plus),
            rtg_women16plus=_parse_float(rtg_women16plus),
            share_men1659=_parse_float(share_men1659),
            rtg_men1659=_parse_float(rtg_men1659),
            share_women1659=_parse_float(share_women1659),
            rtg_women1659=_parse_float(rtg_women1659),
            raw_data=[_raw_cell(v) for v in row],
            source_row=row_num,
        )
        records.append(rec)

    _auto_prima_tv(records)
    return records, headers, 2