"""
Idempotent Postgres loader — Premium ETL เฟส 2
==============================================
รับข้อมูลที่ normalize แล้วจากเฟส 1 -> ผ่าน quality gate -> UPSERT เข้า Postgres warehouse
UPSERT บน natural key => รัน window เดิมซ้ำกี่ครั้งก็ไม่เบิ้ลแถว (idempotent)

production ใช้ SQLAlchemy + psycopg2 (import แบบ lazy — โมดูลนี้ import ได้ด้วย stdlib)
ดู load_demo.py สำหรับการรันพิสูจน์จริงด้วย sqlite (ตรรกะเดียวกัน)
"""
from __future__ import annotations

import os
import logging

from quality import quality_gate

log = logging.getLogger("loader")

UPSERT_ORDERS = """
INSERT INTO orders (platform, platform_order_id, order_date, total_amount, status, sku, qty, unit_price)
VALUES (:platform, :platform_order_id, :order_date, :total_amount, :status, :sku, :qty, :unit_price)
ON CONFLICT (platform, platform_order_id, sku) DO UPDATE SET
    total_amount = EXCLUDED.total_amount, status = EXCLUDED.status,
    qty = EXCLUDED.qty, unit_price = EXCLUDED.unit_price, updated_at = now()
"""

UPSERT_INVENTORY = """
INSERT INTO inventory (platform, sku, stock, updated_at)
VALUES (:platform, :sku, :stock, :updated_at)
ON CONFLICT (platform, sku) DO UPDATE SET
    stock = EXCLUDED.stock, updated_at = EXCLUDED.updated_at
"""


def _engine():
    from sqlalchemy import create_engine
    url = os.environ.get("DATABASE_URL", "postgresql+psycopg2://user:pass@/db?host=/cloudsql/INSTANCE")
    return create_engine(url, pool_pre_ping=True, future=True)


def load(payload: dict) -> dict:
    """quality gate -> UPSERT orders + inventory. คืนสรุป (โหลดกี่แถว, reject กี่แถว)."""
    from sqlalchemy import text
    gated = quality_gate(payload)
    engine = _engine()
    with engine.begin() as conn:
        if gated["clean_orders"]:
            conn.execute(text(UPSERT_ORDERS), gated["clean_orders"])
        if gated["clean_inventory"]:
            conn.execute(text(UPSERT_INVENTORY), gated["clean_inventory"])
    summary = {
        "orders_loaded": len(gated["clean_orders"]),
        "inventory_loaded": len(gated["clean_inventory"]),
        "rejected": len(gated["rejected"]),
        "quality_ok": gated["ok"],
    }
    log.info("load complete %s", summary)
    return summary