"""
build_veeam_report_data.py
Turns the per-BDR CSVs from Get-VeeamBackupReportData.ps1 into the four datasets
used by the client Veeam backup report.

Usage:
    python build_veeam_report_data.py <csv_dir> <out_dir> [--end YYYY-MM-DD] [--nights 21] [--all-machines]

Inputs (one pair per BDR): <Site>_<Server>_schedule.csv and <Site>_<Server>_history.csv
Outputs: backup_frequency.csv, nightly_status.csv, machine_summary.csv
(findings.csv is written by a person or an AI step after reviewing these.)
"""
import argparse
import glob
import os
import re

import pandas as pd

# A backup "night" runs 8 AM to 8 AM, so a 9 PM job and its post-midnight retries count together.
NIGHT_SHIFT = pd.Timedelta(hours=8)
# Veeam 13 agent check-ins show up as sessions of a few seconds; ignore them for run counts.
CHECKIN_SECONDS = 60
SERVER_NAME_HINTS = re.compile(r"(server|srv|dc\b|-dc|data|remote|rds|-ts\b|softdent|fileserver|hvhost)", re.I)
RANK = {"Success": 3, "Warning": 2, "Failed": 1}
NAME = {3: "Success", 2: "Warning", 1: "Failed"}


def load(csv_dir):
    hist = pd.concat([pd.read_csv(f) for f in glob.glob(os.path.join(csv_dir, "*_history.csv"))], ignore_index=True)
    sched = pd.concat([pd.read_csv(f) for f in glob.glob(os.path.join(csv_dir, "*_schedule.csv"))], ignore_index=True)
    return hist, sched


def prepare(hist, prefix_to_strip=None):
    h = hist.copy()
    h["Start"] = pd.to_datetime(h.Start, format="mixed")
    h["End"] = pd.to_datetime(h.End, format="mixed", errors="coerce")
    if prefix_to_strip:
        h["Site"] = h.Site.str.replace(prefix_to_strip, "", regex=False)
    h["Job"] = h.Job.fillna("")
    h["Tier"] = h.Job.str.startswith("S3 Copy").map({True: "Offsite", False: "Local"})
    h["IsAgent"] = h.Job.eq("")  # agent sessions come back with a blank job name on VBR 13
    h["Seconds"] = (h.End - h.Start).dt.total_seconds()
    h["M"] = h.Machine.astype(str).str.strip().str.lower()
    # Job-level copy rows: take the machine from the copy job name ("... - <machine>")
    jl = h.M.eq("(job-level)")
    h.loc[jl, "M"] = h.loc[jl, "Job"].str.extract(r" - ([^\\]+)$")[0].str.lower()
    # Copy jobs sometimes use a short alias (MR-PANO vs SLMR-PANO): map to the local name at the same site
    for site, g in h.groupby("Site"):
        local = set(g[g.Tier == "Local"].M)
        for m in set(g[g.Tier == "Offsite"].M) - local:
            match = [x for x in local if x.endswith(m.split("-", 1)[-1]) and x != m]
            if len(match) == 1:
                h.loc[(h.Site == site) & (h.M == m), "M"] = match[0]
    h["Machine"] = h.M.str.upper()
    h["Night"] = (h.Start - NIGHT_SHIFT).dt.normalize()
    h["r"] = h.Result.map(RANK)
    return h


def role_for(site, machine, h, sched):
    g = h[(h.Site == site) & (h.Machine == machine) & (h.Tier == "Local")]
    if (~g.IsAgent).any():
        return "Server"  # Hyper-V host-level job: everything in it is a VM server
    return "Server" if SERVER_NAME_HINTS.search(machine) else "Workstation"


def schedule_for(site, machine, role, is_vm, sched):
    s = sched[(sched.Site == site) & sched.ScheduleKind.notna() & (sched.JobType != "Agent")]
    if is_vm:
        s = s[s.JobType == "Backup"]
    elif role == "Server":
        s2 = s[s.JobType.eq("EpAgentBackup") | (s.JobType.eq("EpAgentPolicy") & s.Machine.str.lower().ne("workstations"))]
        s = s2 if len(s2) else s[s.JobType == "EpAgentPolicy"]
    else:
        s = s[s.JobType.eq("EpAgentPolicy") & s.Machine.str.lower().eq("workstations")]
    if not len(s):
        return "", "", ""
    r = s.iloc[0]
    d = str(r.ScheduleDetail)
    d = (d.replace("Monday, Tuesday, Wednesday, Thursday, Friday, Saturday", "Mon–Sat")
          .replace("Monday, Tuesday, Wednesday, Thursday, Friday", "Mon–Fri").replace("Everyday", "Every day"))
    d = re.sub(r"at (\d\d):(\d\d)", lambda m: "at " + pd.Timestamp(f"2026-01-01 {m.group(1)}:{m.group(2)}").strftime("%-I:%M %p"), d)
    if not r.ScheduleEnabled:
        conf = "Disabled (no schedule)"
    elif r.ScheduleKind == "Periodic":
        conf = "Hourly" if "3600" in d or "60 Minutes" in d else d
    else:
        conf = f"Daily, {d}"
    return r.Job, conf, r.RetainRestorePoints


def expected_days(conf):
    if "Hourly" in conf or "Every day" in conf:
        return set(range(7))
    if "Mon–Sat" in conf:
        return {0, 1, 2, 3, 4, 5}
    return {0, 1, 2, 3, 4}


def build(csv_dir, out_dir, end=None, n_nights=21, servers_only=True, prefix="Smile Loft "):
    hist, sched = load(csv_dir)
    sched = sched.copy()
    sched["Site"] = sched.Site.str.replace(prefix, "", regex=False)
    h = prepare(hist, prefix)
    end = pd.Timestamp(end) if end else h.Night.max() - pd.Timedelta(days=0)
    nights = pd.date_range(end - pd.Timedelta(days=n_nights - 1), end)
    h = h[h.Night.isin(nights)]
    bdr = h.groupby("Site").VBRServer.first()

    # Frequency per machine (local backups)
    freq = []
    for (site, machine), g in h[h.Tier == "Local"].groupby(["Site", "Machine"]):
        role = role_for(site, machine, h, sched)
        is_vm = (~g.IsAgent).any()
        job, conf, ret = schedule_for(site, machine, role, is_vm, sched)
        real = g[g.Result.isin(["Success", "Warning"]) & (~g.IsAgent | (g.Seconds >= CHECKIN_SECONDS))]
        days = set(real.Start.dt.dayofweek)
        per_day = len(real) / n_nights
        if not len(real):
            obs = "No successful runs"
        elif per_day >= 6:
            hrs = real.Start.dt.hour
            obs = f"Hourly, {pd.Timestamp(f'2026-01-01 {hrs.min()}:00'):%-I %p}–{pd.Timestamp(f'2026-01-01 {hrs.max()}:00'):%-I %p}"
        elif real.Night.nunique() >= n_nights - 2:
            obs = "Nightly, every day"
        elif days == {0, 1, 2, 3, 4, 5}:
            obs = "Nightly, Mon–Sat"
        elif days <= {0, 1, 2, 3, 4}:
            obs = "Nightly, Mon–Fri"
        else:
            obs = "Nightly, irregular"
        typical = real.Start.dt.hour.mode()
        freq.append(dict(
            Site=site, BDR=bdr[site], Machine=machine, Role=role,
            Method="Hyper-V host backup (VM)" if is_vm else "Veeam Agent",
            Job=g.Job[g.Job != ""].iloc[0] if is_vm else job,
            ConfiguredSchedule=conf, ObservedPattern=obs, CompletedRuns=len(real),
            TypicalStart=f"{pd.Timestamp(f'2026-01-01 {int(typical.iloc[0])}:00'):%-I %p}" if len(typical) else "",
            RestorePoints=ret,
            OffsiteCopy="Yes" if ((h.Site == site) & (h.Machine == machine) & (h.Tier == "Offsite")).any() else "No",
        ))
    freq = pd.DataFrame(freq)
    # Sites whose workstation policy is disabled get an explicit row
    for site in sched.Site.unique():
        ws = sched[(sched.Site == site) & (sched.JobType == "EpAgentPolicy") & sched.Machine.str.lower().eq("workstations")]
        if len(ws) and not ws.ScheduleEnabled.iloc[0] and not ((freq.Site == site) & (freq.Role == "Workstation")).any():
            freq.loc[len(freq)] = dict(Site=site, BDR=bdr.get(site, ""), Machine="All workstations", Role="Workstation",
                                       Method="Veeam Agent", Job=ws.Job.iloc[0], ConfiguredSchedule="Disabled (no schedule)",
                                       ObservedPattern="Not backed up", CompletedRuns=0, TypicalStart="",
                                       RestorePoints=ws.RetainRestorePoints.iloc[0], OffsiteCopy="No")
    if servers_only:
        freq = freq[freq.Role == "Server"]
    keep = set(zip(freq.Site, freq.Machine))

    # Nightly grid: best result per machine, tier and night
    rows = []
    for (site, machine, tier), g in h.groupby(["Site", "Machine", "Tier"]):
        if (site, machine) not in keep:
            continue
        by = g.groupby("Night")
        for n in nights:
            if n in by.groups:
                x = by.get_group(n)
                rows.append(dict(Site=site, Machine=machine, Tier=tier, Night=f"{n:%Y-%m-%d}", Status=NAME[x.r.max()], Attempts=len(x)))
            else:
                rows.append(dict(Site=site, Machine=machine, Tier=tier, Night=f"{n:%Y-%m-%d}", Status="No run", Attempts=0))
    nightly = pd.DataFrame(rows)

    # Summary and health
    fq = freq.set_index(["Site", "Machine"])
    srows = []
    for (site, machine, tier), g in nightly.groupby(["Site", "Machine", "Tier"]):
        hh = h[(h.Site == site) & (h.Machine == machine) & (h.Tier == tier)]
        c = g.Status.value_counts()
        ex = expected_days(fq.ConfiguredSchedule.get((site, machine), ""))
        missed = int(((g.Status == "No run") & pd.to_datetime(g.Night).dt.dayofweek.isin(ex)).sum())
        last = hh[hh.Result.isin(["Success", "Warning"])].Start.max()
        srows.append(dict(Site=site, Machine=machine, Role=fq.Role.get((site, machine), ""), Tier=tier,
                          NightsSuccess=int(c.get("Success", 0)), NightsWarning=int(c.get("Warning", 0)),
                          NightsFailed=int(c.get("Failed", 0)), MissedScheduledNights=missed,
                          FailedAttempts=int((hh.Result == "Failed").sum()),
                          LastCompleted=f"{last:%Y-%m-%d %H:%M}" if pd.notna(last) else ""))
    summ = pd.DataFrame(srows)
    cutoff = end + pd.Timedelta(days=1) - pd.Timedelta(days=2)

    def health(r):
        if r.NightsSuccess + r.NightsWarning == 0 or (r.LastCompleted and pd.Timestamp(r.LastCompleted) < cutoff):
            return "Critical"
        if r.NightsFailed + r.MissedScheduledNights >= 5:
            return "Critical"
        if r.NightsFailed or r.NightsWarning or r.MissedScheduledNights:
            return "Attention"
        return "Healthy"

    summ["Health"] = summ.apply(health, axis=1)
    summ["Key"] = summ.Site + " | " + summ.Machine + " | " + summ.Tier

    os.makedirs(out_dir, exist_ok=True)
    freq.sort_values(["Site", "Role", "Machine"]).to_csv(os.path.join(out_dir, "backup_frequency.csv"), index=False)
    nightly.to_csv(os.path.join(out_dir, "nightly_status.csv"), index=False)
    summ.to_csv(os.path.join(out_dir, "machine_summary.csv"), index=False)
    return freq, nightly, summ


if __name__ == "__main__":
    ap = argparse.ArgumentParser()
    ap.add_argument("csv_dir")
    ap.add_argument("out_dir")
    ap.add_argument("--end", help="Last backup night to include (YYYY-MM-DD); default is the latest night in the data")
    ap.add_argument("--nights", type=int, default=21)
    ap.add_argument("--all-machines", action="store_true", help="Include workstations")
    ap.add_argument("--site-prefix", default="", help="Text to strip from site names, for example 'Smile Loft '")
    a = ap.parse_args()
    f, n, s = build(a.csv_dir, a.out_dir, a.end, a.nights, not a.all_machines, a.site_prefix)
    print(f"{len(f)} machines, {len(s)} streams:", s.Health.value_counts().to_dict())