#!/usr/bin/env python3
import os
import glob
import subprocess
import json
import pathlib

repo = pathlib.Path.home() / "netgescon-day0-backup"

# 1. Fetch stabili with codice_fiscale from DB
tinker_cmd = """
use Illuminate\\Support\\Facades\\DB;
$stabili = DB::table("stabili")->whereNotNull("codice_fiscale")->where("codice_fiscale", "!=", "")->get(["id", "codice_stabile", "denominazione", "codice_fiscale"]);
echo json_encode($stabili);
"""

res = subprocess.run(["php", "artisan", "tinker", "--execute=" + tinker_cmd], cwd=repo, capture_output=True, text=True)
out = res.stdout.strip()
json_start = out.find("[")
json_end = out.rfind("]") + 1

if json_start == -1 or json_end == 0:
    print("Errore nel recupero degli stabili dal database.")
    exit(1)

stabili = json.loads(out[json_start:json_end])
print(f"🤖 Trovati {len(stabili)} stabili con Codice Fiscale nel database:\n")
for s in stabili:
    print(f"  • ID {s['id']} [{s['codice_stabile']}] - {s['denominazione']} (CF: {s['codice_fiscale']})")

# 2. Identify candidate directories containing XML/P7M/ZIP invoice files
candidate_dirs = set()

scan_bases = [
    "/mnt/gescon-archives/FattureXML",
    "/mnt/gescon-archives/gescon",
    "/mnt/gescon-archives/Appo",
    "/mnt/gescon-archives/scansioni",
    "/mnt/gescon-archives/Users",
    "/home/michele/netgescon-day0-backup/storage",
    "/home/michele/MIki",
    "/home/michele/netgescon",
]

for base in scan_bases:
    if os.path.exists(base):
        for root, dirs, files in os.walk(base):
            for f in files:
                ext = f.lower()
                if ext.endswith(".xml") or ext.endswith(".p7m") or ext.endswith(".zip"):
                    if any(kw in f.lower() or kw in root.lower() for kw in ["xml", "p7m", "fattur", "cassetto", "ade", "0021", "0013", "0002", "0010", "0016", "0018", "0019", "0023", "0024", "0025", "0026"]):
                        candidate_dirs.add(root)

print(f"\n📂 Trovate {len(candidate_dirs)} directory candidate contenenti file XML/P7M/ZIP di Fatture Elettroniche.")

# 3. Run fe:cassetto-import-local for each Stabile and candidate directory
total_imported = 0

for s in stabili:
    sid = s['id']
    c_code = s['codice_stabile']
    cf = s['codice_fiscale']
    print(f"\n🚀 Sincronizzazione FE per Stabile ID {sid} [{c_code}] - {s['denominazione']} (CF: {cf})...")
    
    for cdir in candidate_dirs:
        cmd = [
            "php", "artisan", "fe:cassetto-import-local",
            str(sid),
            cdir,
            "--dal=2019-01-01",
            "--al=2026-12-31",
            "--no_skip=1"
        ]
        res_imp = subprocess.run(cmd, cwd=repo, capture_output=True, text=True)
        stdout = res_imp.stdout.strip()
        if "imported=" in stdout and "imported=0" not in stdout:
            print(f"   ✔️ {cdir} -> {stdout}")

# 4. Final summary count of imported FE invoices
tinker_summary = """
use Illuminate\\Support\\Facades\\DB;
$summary = DB::table("fatture_elettroniche")
    ->join("stabili", "stabili.id", "=", "fatture_elettroniche.stabile_id")
    ->select("stabili.codice_stabile", "stabili.denominazione", "stabili.codice_fiscale", DB::raw("count(fatture_elettroniche.id) as total_fe"))
    ->groupBy("stabili.id", "stabili.codice_stabile", "stabili.denominazione", "stabili.codice_fiscale")
    ->get();

$total = DB::table("fatture_elettroniche")->count();
echo json_encode(["total_all" => $total, "by_stabile" => $summary]);
"""

res_sum = subprocess.run(["php", "artisan", "tinker", "--execute=" + tinker_summary], cwd=repo, capture_output=True, text=True)
out_sum = res_sum.stdout.strip()
js_start = out_sum.find("{")
js_end = out_sum.rfind("}") + 1
if js_start != -1 and js_end != 0:
    data_sum = json.loads(out_sum[js_start:js_end])
    print(f"\n📊 RIEPILOGO FINALE FATTURE ELETTRONICHE IMPORTATE PER CODICE FISCALE:")
    print(f"   Totale Complessivo FE in Database: {data_sum.get('total_all', 0)}\n")
    for row in data_sum.get("by_stabile", []):
        print(f"   • Stabile [{row['codice_stabile']}] {row['denominazione']} (CF: {row['codice_fiscale']}): {row['total_fe']} FE")