#!/usr/bin/env python3
import os
import csv
import sys
import hashlib
import subprocess
from pathlib import Path

ODOO_DB   = os.environ.get("ODOO_DB", "ouverture-automatique")
PSQL_CMD  = os.environ.get("PSQL_CMD", "psql")
MYSQL_CMD = os.environ.get("MYSQL_CMD", "mysql")
MAGENTO_DB = os.environ.get("MAGENTO_DB", "ouverture")

MAGENTO_ROOT = os.environ.get("MAGENTO_ROOT", "/var/www/ouverture/html")
MEDIA_ROOT   = os.environ.get("MAGENTO_MEDIA_ROOT", str(Path(MAGENTO_ROOT) / "pub/media/catalog/product"))

OUT_CSV = os.environ.get("OUT_CSV", "image_audit.csv")

def sh(cmd, text=True) -> str:
    p = subprocess.run(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=text)
    if p.returncode != 0:
        raise RuntimeError(f"Command failed: {' '.join(cmd)}\nSTDERR:\n{p.stderr}")
    return p.stdout

def sha1_file(path: Path) -> str:
    h = hashlib.sha1()
    with path.open("rb") as f:
        for chunk in iter(lambda: f.read(1024 * 1024), b""):
            h.update(chunk)
    return h.hexdigest()

def get_odoo_sku_to_checksum() -> dict:
    # Odoo 17: image_1920 est stocké via ir_attachment (type=binary, res_field=image_1920)
    q = r"""
COPY (
  SELECT
    COALESCE(pt.default_code,'') AS sku,
    COALESCE(a.checksum,'')      AS checksum,
    COALESCE(a.file_size,0)      AS bytes
  FROM product_template pt
  LEFT JOIN LATERAL (
    SELECT checksum, file_size
    FROM ir_attachment
    WHERE res_model='product.template'
      AND res_id=pt.id
      AND res_field='image_1920'
      AND type='binary'
    ORDER BY id DESC
    LIMIT 1
  ) a ON TRUE
  WHERE pt.default_code IS NOT NULL AND pt.default_code <> ''
) TO STDOUT WITH (FORMAT csv, HEADER false);
"""
    # si tu lances en root: sudo -u postgres psql ...
    # sinon: il faut un .pgpass / user postgres accessible
    cmd = ["sudo", "-u", "postgres", PSQL_CMD, "-d", ODOO_DB, "-At", "-c", q]
    out = sh(cmd)

    m = {}
    for line in out.splitlines():
        if not line.strip():
            continue
        sku, checksum, b = next(csv.reader([line]))
        m[sku.strip()] = {"checksum": (checksum or "").strip(), "bytes": int(b or 0)}
    return m

def mysql_args_from_env():
    # Option simple: tu exportes MYSQL_ARGS='-uroot -p... -hlocalhost'
    # ou MYSQL_ARGS='--defaults-file=/root/.my.cnf'
    raw = os.environ.get("MYSQL_ARGS", "").strip()
    return raw.split() if raw else []

def get_magento_sku_to_image_path() -> dict:
    # récupère l’attribut "image" (base image) store_id=0
    # (c’est ce que ton webhook utilise via $product->getData('image'))
    q = r"""
SELECT
  cpe.sku,
  COALESCE(v.value,'') AS image
FROM catalog_product_entity cpe
LEFT JOIN eav_attribute ea
  ON ea.attribute_code='image'
LEFT JOIN eav_entity_type eet
  ON eet.entity_type_code='catalog_product'
LEFT JOIN catalog_product_entity_varchar v
  ON v.entity_id=cpe.entity_id
 AND v.attribute_id=ea.attribute_id
 AND v.store_id=0
WHERE ea.entity_type_id=eet.entity_type_id;
"""
    cmd = [MYSQL_CMD] + mysql_args_from_env() + ["-D", MAGENTO_DB, "-N", "-B", "-e", q]
    out = sh(cmd)

    m = {}
    for line in out.splitlines():
        if not line.strip():
            continue
        sku, img = line.split("\t", 1)
        img = (img or "").strip()
        # Magento met parfois 'no_selection'
        if img in ("", "no_selection", None):
            m[sku.strip()] = {"image": "", "abs": "", "sha1": "", "bytes": 0, "status": "NO_IMAGE"}
            continue

        # valeur type: /i/n/infrasense2030.png (souvent avec slash au début)
        rel = img.lstrip("/")
        abs_path = Path(MEDIA_ROOT) / rel
        if not abs_path.exists():
            m[sku.strip()] = {"image": img, "abs": str(abs_path), "sha1": "", "bytes": 0, "status": "FILE_MISSING"}
            continue

        try:
            s = sha1_file(abs_path)
            b = abs_path.stat().st_size
            m[sku.strip()] = {"image": img, "abs": str(abs_path), "sha1": s, "bytes": b, "status": "OK"}
        except Exception as e:
            m[sku.strip()] = {"image": img, "abs": str(abs_path), "sha1": "", "bytes": 0, "status": f"READ_ERR:{e}"}
    return m

def main():
    print("[*] Load Odoo checksums…")
    odoo = get_odoo_sku_to_checksum()
    print(f"    -> {len(odoo)} SKUs Odoo")

    print("[*] Load Magento base image + sha1…")
    mag = get_magento_sku_to_image_path()
    print(f"    -> {len(mag)} SKUs Magento")

    all_skus = sorted(set(odoo.keys()) | set(mag.keys()))

    stats = {
        "OK": 0,
        "MISMATCH": 0,
        "MISSING_IN_ODOO": 0,
        "MISSING_IN_MAGENTO": 0,
        "NO_IMAGE_BOTH": 0,
        "MAGENTO_NO_IMAGE": 0,
        "ODOO_NO_IMAGE": 0,
        "MAGENTO_FILE_MISSING": 0,
    }

    with open(OUT_CSV, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f)
        w.writerow([
            "sku",
            "result",
            "magento_image",
            "magento_abs",
            "magento_sha1",
            "magento_bytes",
            "magento_status",
            "odoo_checksum",
            "odoo_bytes",
        ])

        for sku in all_skus:
            o = odoo.get(sku, {"checksum": "", "bytes": 0})
            m = mag.get(sku, {"image":"", "abs":"", "sha1":"", "bytes":0, "status":"NO_SKU"})

            o_sha1 = (o.get("checksum") or "").strip()
            o_b = int(o.get("bytes") or 0)

            m_sha1 = (m.get("sha1") or "").strip()
            m_b = int(m.get("bytes") or 0)
            m_status = m.get("status") or ""

            # classification
            if (not o_sha1) and (not m_sha1):
                if m_status in ("NO_IMAGE", "NO_SKU"):
                    result = "NO_IMAGE_BOTH"
                    stats["NO_IMAGE_BOTH"] += 1
                elif m_status == "FILE_MISSING":
                    result = "MAGENTO_FILE_MISSING"
                    stats["MAGENTO_FILE_MISSING"] += 1
                else:
                    result = "NO_IMAGE_BOTH"
                    stats["NO_IMAGE_BOTH"] += 1

            elif (not o_sha1) and m_sha1:
                result = "MISSING_IN_ODOO"
                stats["MISSING_IN_ODOO"] += 1

            elif o_sha1 and (not m_sha1):
                # Odoo a une image mais Magento n’en a pas (ou fichier introuvable)
                if m_status == "FILE_MISSING":
                    result = "MAGENTO_FILE_MISSING"
                    stats["MAGENTO_FILE_MISSING"] += 1
                else:
                    result = "MISSING_IN_MAGENTO"
                    stats["MISSING_IN_MAGENTO"] += 1

            else:
                # les deux ont un hash
                if o_sha1 == m_sha1 and (o_b == 0 or m_b == 0 or o_b == m_b):
                    result = "OK"
                    stats["OK"] += 1
                else:
                    result = "MISMATCH"
                    stats["MISMATCH"] += 1

            w.writerow([
                sku,
                result,
                m.get("image",""),
                m.get("abs",""),
                m_sha1,
                m_b,
                m_status,
                o_sha1,
                o_b
            ])

    print("\n=== STATS ===")
    for k, v in stats.items():
        print(f"{k}: {v}")
    print(f"\nCSV: {OUT_CSV}")

if __name__ == "__main__":
    main()