"""Nhập/xuất danh sách thiết bị (CSV/Excel) và xuất báo cáo (HTML/CSV/Excel)."""
from __future__ import annotations

import csv
import html
import ipaddress
from pathlib import Path
from typing import Optional

from drivers.vendors import VENDORS
from .database import Database
from .security import Vault

CSV_COLUMNS = ["name", "ip", "vendor", "username", "password", "secret", "port", "protocol", "site", "tags", "simulated"]


class ImportError_(Exception):
    pass


def validate_host(value: str) -> bool:
    try:
        ipaddress.ip_address(value)
        return True
    except ValueError:
        import re
        return bool(re.fullmatch(r"[A-Za-z0-9]([A-Za-z0-9\-\.]{0,251}[A-Za-z0-9])?", value))


def write_template_csv(path: Path) -> None:
    with open(path, "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.writer(fh)
        w.writerow(CSV_COLUMNS)
        w.writerow(["SW-HN-01", "192.168.1.10", "cisco_ios", "admin", "MatKhau@123", "", "22", "ssh", "HaNoi", "core,tang1", ""])
        w.writerow(["AP-HN-01", "192.168.1.20", "mikrotik_routeros", "admin", "", "", "22", "ssh", "HaNoi", "wifi", ""])
        w.writerow(["GIA-LAP-JUNOS", "10.9.9.1", "juniper_junos", "", "", "", "22", "ssh", "Lab", "gia-lap", "yes"])
        w.writerow(["# vendor hợp lệ:", ", ".join(k for k in VENDORS if k != "mock"), "", "", "", "", "", "", "", "", "cột simulated: yes = thiết bị giả lập"])


def _read_rows(path: Path) -> list[dict]:
    p = Path(path)
    if p.suffix.lower() in (".xlsx", ".xlsm"):
        import openpyxl
        ws = openpyxl.load_workbook(p, read_only=True, data_only=True).active
        rows = list(ws.iter_rows(values_only=True))
        if not rows:
            return []
        head = [str(c or "").strip().lower() for c in rows[0]]
        return [{head[i]: ("" if v is None else str(v)).strip() for i, v in enumerate(r) if i < len(head)} for r in rows[1:]]
    with open(p, newline="", encoding="utf-8-sig") as fh:
        sample = fh.read(2048)
        fh.seek(0)
        delim = ";" if sample.count(";") > sample.count(",") else ","
        return [{(k or "").strip().lower(): (v or "").strip() for k, v in row.items()} for row in csv.DictReader(fh, delimiter=delim)]


def import_devices(path: Path, db: Database, vault: Vault, update_existing: bool = True) -> dict:
    """Nhập thiết bị. Trả về {'added': n, 'updated': n, 'errors': [str]}."""
    stats = {"added": 0, "updated": 0, "errors": []}
    for n, row in enumerate(_read_rows(path), start=2):
        name, ip = row.get("name", ""), row.get("ip", "")
        if not name and not ip or name.startswith("#"):
            continue
        vendor = row.get("vendor", "").lower() or "cisco_ios"
        simulated = row.get("simulated", "").strip().lower() in ("1", "yes", "y", "true", "x", "có", "co")
        if vendor == "mock":
            vendor, simulated = "cisco_ios", True
        if vendor not in VENDORS:
            stats["errors"].append(f"Dòng {n}: hãng '{vendor}' không hỗ trợ")
            continue
        if not ip or not validate_host(ip):
            stats["errors"].append(f"Dòng {n}: IP/hostname '{ip}' không hợp lệ")
            continue
        try:
            port = int(row.get("port") or 22)
        except ValueError:
            stats["errors"].append(f"Dòng {n}: port không hợp lệ")
            continue
        proto = (row.get("protocol") or "ssh").lower()
        if proto not in ("ssh", "telnet"):
            stats["errors"].append(f"Dòng {n}: giao thức phải là ssh hoặc telnet")
            continue
        data = dict(name=name or ip, ip=ip, vendor=vendor, username=row.get("username", ""),
                    port=port, protocol=proto, site=row.get("site", ""), tags=row.get("tags", ""),
                    simulated=int(simulated))
        if row.get("password"):
            data["password_enc"] = vault.encrypt(row["password"])
        if row.get("secret"):
            data["secret_enc"] = vault.encrypt(row["secret"])
        existing = db.find_device_by_ip(ip)
        if existing and update_existing:
            db.update_device(existing["id"], **data)
            stats["updated"] += 1
        elif not existing:
            db.add_device(**data)
            stats["added"] += 1
    return stats


def export_devices(path: Path, db: Database) -> int:
    """Xuất danh sách thiết bị (KHÔNG kèm mật khẩu)."""
    devs = db.list_devices()
    cols = ["name", "ip", "vendor", "username", "port", "protocol", "site", "tags", "model", "os_version", "status", "simulated"]
    p = Path(path)
    if p.suffix.lower() == ".xlsx":
        import openpyxl
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Thiết bị"
        ws.append(cols)
        for d in devs:
            ws.append([d.get(c, "") for c in cols])
        wb.save(p)
    else:
        with open(p, "w", newline="", encoding="utf-8-sig") as fh:
            w = csv.writer(fh)
            w.writerow(cols)
            for d in devs:
                w.writerow([d.get(c, "") for c in cols])
    return len(devs)


def export_job_report(path: Path, job: dict, results: list[dict]) -> None:
    """Xuất báo cáo kết quả job ra HTML / CSV / XLSX tùy đuôi file."""
    p = Path(path)
    cols = ["device_name", "ip", "status", "message", "started", "finished"]
    if p.suffix.lower() == ".csv":
        with open(p, "w", newline="", encoding="utf-8-sig") as fh:
            w = csv.writer(fh)
            w.writerow(cols)
            for r in results:
                w.writerow([r.get(c, "") for c in cols])
    elif p.suffix.lower() == ".xlsx":
        import openpyxl
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Kết quả"
        ws.append(cols)
        for r in results:
            ws.append([r.get(c, "") for c in cols])
        wb.save(p)
    else:
        esc = html.escape
        rows = "".join(
            f"<tr class='{esc(str(r.get('status')))}'><td>{esc(str(r.get('device_name')))}</td><td>{esc(str(r.get('ip')))}</td>"
            f"<td>{esc(str(r.get('status')))}</td><td>{esc(str(r.get('message')))}</td></tr>" for r in results)
        details = "".join(
            f"<h3>{esc(str(r.get('device_name')))}</h3><pre>{esc(str(r.get('commands') or ''))}</pre>"
            + (f"<pre class='diff'>{esc(str(r.get('diff')))}</pre>" if r.get("diff") else "") for r in results)
        p.write_text(f"""<!doctype html><html lang="vi"><meta charset="utf-8"><title>Báo cáo job #{job.get('id')}</title>
<style>body{{font-family:Segoe UI,Arial,sans-serif;margin:24px;color:#1f2933}}table{{border-collapse:collapse;width:100%}}
td,th{{border:1px solid #d0d7de;padding:6px 10px;text-align:left}}th{{background:#f1f5f9}}
tr.success td:nth-child(3){{color:#16a34a;font-weight:600}}tr.failed td:nth-child(3),tr.warning td:nth-child(3){{color:#dc2626;font-weight:600}}
pre{{background:#0f172a;color:#e2e8f0;padding:10px;border-radius:6px;overflow:auto}}</style>
<h1>Báo cáo tác vụ #{job.get('id')}: {esc(str(job.get('label')))}</h1>
<p>Người chạy: <b>{esc(str(job.get('user')))}</b> &middot; Thời gian: {esc(str(job.get('created')))} &middot;
Kết quả: {job.get('ok')} thành công / {job.get('failed')} lỗi trên {job.get('total')} thiết bị</p>
<table><tr><th>Thiết bị</th><th>IP</th><th>Trạng thái</th><th>Ghi chú</th></tr>{rows}</table>
<h2>Chi tiết</h2>{details}</html>""", encoding="utf-8")
