from datetime import datetime
from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Font
from sqlalchemy import func, select
from sqlalchemy.dialects.mysql import insert as mysql_insert
from sqlalchemy.orm import Session

from app.anviz_client import (
    BACKUP_LABELS,
    TYPE_LABELS,
    AnvizDevice,
    AnvizError,
    AnvizRecord,
)
from app.config import settings
from app.devices import RelojConfig, get_reloj, load_relojes
from app.models import Marcacion


def _anviz_client(cfg: RelojConfig) -> AnvizDevice:
    return AnvizDevice(
        device_id=cfg.device_id,
        host=cfg.host,
        port=cfg.port,
        timeout=settings.ANVIZ_TIMEOUT,
        comm_password=cfg.comm_password,
    )


def list_relojes_config() -> list[dict]:
    return [
        {
            "id": r.id,
            "name": r.name,
            "host": r.host,
            "port": r.port,
            "device_id": r.device_id,
            "enabled": r.enabled,
        }
        for r in load_relojes()
    ]


def sync_reloj(db: Session, cfg: RelojConfig) -> dict:
    device = _anviz_client(cfg)
    try:
        with device:
            device.ping()  # autentica (Comm.PW) y detecta codificación de password
            records = device.download_all_records()
    except (AnvizError, OSError, TimeoutError) as exc:
        raise AnvizError(f"{cfg.name} ({cfg.host}): {exc}") from exc

    inserted = 0
    skipped = 0
    for rec in records:
        if _upsert_record(db, cfg, rec):
            inserted += 1
        else:
            skipped += 1

    return {
        "reloj_id": cfg.id,
        "reloj_name": cfg.name,
        "downloaded": len(records),
        "inserted": inserted,
        "skipped_duplicates": skipped,
    }


def sync_from_device(db: Session, reloj_id: str | None = None) -> dict:
    skipped: list[str] = []
    if reloj_id:
        cfg = get_reloj(reloj_id)
        if not cfg.enabled:
            raise AnvizError(f"{cfg.name} está deshabilitado en relojes.json (enabled: false)")
        results = [sync_reloj(db, cfg)]
    else:
        results = []
        errors: list[str] = []
        for cfg in load_relojes():
            if not cfg.enabled:
                skipped.append(f"{cfg.name} (deshabilitado)")
                continue
            try:
                results.append(sync_reloj(db, cfg))
            except AnvizError as exc:
                errors.append(str(exc))

        if errors and not results:
            raise AnvizError("; ".join(errors))
        if errors:
            db.commit()
            out = {
                "results": results,
                "errors": errors,
                "downloaded": sum(r["downloaded"] for r in results),
                "inserted": sum(r["inserted"] for r in results),
                "skipped_duplicates": sum(r["skipped_duplicates"] for r in results),
            }
            if skipped:
                out["skipped_disabled"] = skipped
            return out

    db.commit()
    if len(results) == 1 and not skipped:
        return results[0]
    out = {
        "results": results,
        "downloaded": sum(r["downloaded"] for r in results),
        "inserted": sum(r["inserted"] for r in results),
        "skipped_duplicates": sum(r["skipped_duplicates"] for r in results),
    }
    if skipped:
        out["skipped_disabled"] = skipped
    return out


def _upsert_record(db: Session, cfg: RelojConfig, rec: AnvizRecord) -> bool:
    stmt = mysql_insert(Marcacion).values(
        reloj_id=cfg.id,
        reloj_name=cfg.name,
        user_code=rec.user_code,
        marcacion_at=rec.timestamp,
        backup_type=rec.backup_type,
        record_type=rec.record_type,
        work_type=rec.work_type,
        backup_label=BACKUP_LABELS.get(rec.backup_type, str(rec.backup_type)),
        type_label=TYPE_LABELS.get(rec.record_type, str(rec.record_type)),
        synced_at=datetime.utcnow(),
    )
    stmt = stmt.on_duplicate_key_update(id=Marcacion.id)
    result = db.execute(stmt)
    return result.rowcount == 1


def list_marcaciones(
    db: Session,
    desde: datetime | None,
    hasta: datetime | None,
    user_code: int | None,
    reloj_id: str | None,
    limit: int = 500,
    offset: int = 0,
) -> tuple[list[Marcacion], int]:
    q = select(Marcacion)
    count_q = select(func.count()).select_from(Marcacion)

    if desde:
        q = q.where(Marcacion.marcacion_at >= desde)
        count_q = count_q.where(Marcacion.marcacion_at >= desde)
    if hasta:
        q = q.where(Marcacion.marcacion_at <= hasta)
        count_q = count_q.where(Marcacion.marcacion_at <= hasta)
    if user_code is not None:
        q = q.where(Marcacion.user_code == user_code)
        count_q = count_q.where(Marcacion.user_code == user_code)
    if reloj_id:
        q = q.where(Marcacion.reloj_id == reloj_id)
        count_q = count_q.where(Marcacion.reloj_id == reloj_id)

    total = db.scalar(count_q) or 0
    rows = (
        db.scalars(
            q.order_by(Marcacion.marcacion_at.desc()).limit(limit).offset(offset)
        ).all()
    )
    return list(rows), total


def export_excel(
    db: Session,
    desde: datetime,
    hasta: datetime,
    reloj_id: str | None = None,
) -> BytesIO:
    rows, _ = list_marcaciones(
        db, desde, hasta, None, reloj_id, limit=100_000, offset=0
    )
    rows.sort(key=lambda r: r.marcacion_at)

    wb = Workbook()
    ws = wb.active
    ws.title = "Marcaciones"

    headers = [
        "ID",
        "Reloj",
        "Código empleado",
        "Fecha y hora",
        "Tipo",
        "Método",
        "Código trabajo",
        "Sincronizado",
    ]
    ws.append(headers)
    for cell in ws[1]:
        cell.font = Font(bold=True)

    for r in rows:
        ws.append(
            [
                r.id,
                r.reloj_name,
                r.user_code,
                r.marcacion_at.strftime("%Y-%m-%d %H:%M:%S"),
                r.type_label,
                r.backup_label,
                r.work_type,
                r.synced_at.strftime("%Y-%m-%d %H:%M:%S"),
            ]
        )

    for col in ws.columns:
        max_len = max(len(str(c.value or "")) for c in col)
        ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 40)

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


def device_status(reloj_id: str | None = None) -> dict:
    configs = [get_reloj(reloj_id)] if reloj_id else load_relojes()
    relojes: list[dict] = []

    for cfg in configs:
        entry = {
            "id": cfg.id,
            "name": cfg.name,
            "host": cfg.host,
            "port": cfg.port,
            "device_id": cfg.device_id,
            "enabled": cfg.enabled,
        }
        if not cfg.enabled:
            entry["connected"] = False
            entry["error"] = "Deshabilitado en relojes.json (reloj apagado o fuera de servicio)"
            relojes.append(entry)
            continue

        device = _anviz_client(cfg)
        try:
            with device:
                ping = device.ping()
            entry.update(ping)
        except (AnvizError, OSError, TimeoutError) as exc:
            entry["connected"] = False
            entry["error"] = str(exc)
        relojes.append(entry)

    if reloj_id:
        return relojes[0]
    return {"relojes": relojes}