"""
app/jobs/magento_deletion_detector.py

After each Plytix feed ingest, detect SKUs that are in magento_sync_state
(have been pushed to Magento) but are absent from the current feed snapshot.
Flags them in magento_deletion_review and sends a notification email.

Called from main.py after successful ingest.
"""
from __future__ import annotations

import csv
import io
import logging
from datetime import datetime, timezone
from typing import List, Optional

logger = logging.getLogger(__name__)


def run_detection(connection_id: int, ingest_run_id: int) -> int:
    """
    Detect missing SKUs for one connection after an ingest run.
    Returns number of new rows flagged.

    Uses:
    - product_snapshot (valid_to IS NULL) for the current Plytix feed SKUs
    - magento_sync_state.last_pushed_at IS NOT NULL for SKUs previously pushed
    - product_snapshot.product_type and variant_of for type info (configurable/simple/parent)
    """
    from db.session import get_session
    from db.models import (
        MagentoSyncState,
        MagentoDeletionReview,
        ProductSnapshot,
        RawFeedRow,
    )
    from sqlalchemy import select

    with get_session() as session:
        # Current Plytix feed SKUs — all SKUs in the ingest file for this run.
        # IMPORTANT: Use RawFeedRow, NOT ProductSnapshot. Ingest only creates
        # ProductSnapshot rows for CHANGED/NEW SKUs; unchanged SKUs get no new
        # row. Using ProductSnapshot would treat unchanged SKUs as "missing"
        # and wrongly flag them for deletion.
        current_stmt = (
            select(RawFeedRow.sku)
            .where(
                RawFeedRow.ingest_run_id == ingest_run_id,
                RawFeedRow.sku.isnot(None),
            )
            .distinct()
        )
        current_skus: set[str] = {row for (row,) in session.execute(current_stmt).all() if row}

        # SKUs previously pushed to this Magento connection
        pushed_stmt = (
            select(MagentoSyncState.sku)
            .where(
                MagentoSyncState.connection_id == connection_id,
                MagentoSyncState.last_pushed_at.isnot(None),
            )
        )
        pushed_skus: set[str] = {row for (row,) in session.execute(pushed_stmt).all()}
        logger.info(
            "[deletion_detector] connection=%d ingest_run_id=%d current=%d pushed=%d sample_current=%s sample_pushed=%s",
            connection_id,
            ingest_run_id,
            len(current_skus),
            len(pushed_skus),
            sorted(list(current_skus))[:20],
            sorted(list(pushed_skus))[:20],
        )
        missing_skus = pushed_skus - current_skus
        #Now we need to pull SKUs from magento_deletion_review with approved status and we will remove them from missing_skus
        approved_stmt = (
            select(MagentoDeletionReview.sku)
            .where(
                MagentoDeletionReview.connection_id == connection_id,
                MagentoDeletionReview.status == "approved",
            )
        )
        approved_skus: set[str] = {row for (row,) in session.execute(approved_stmt).all()}
        missing_skus = missing_skus - approved_skus
        if not missing_skus:
            logger.info(
                "[deletion_detector] connection=%d no missing SKUs (pushed=%d current=%d)",
                connection_id, len(pushed_skus), len(current_skus),
            )
            return 0

        # Fetch product type info for the missing SKUs from the most recent snapshot
        # (valid_to IS NULL = still the active snapshot, or the last closed one)
        snapshot_stmt = (
            select(
                ProductSnapshot.sku,
                ProductSnapshot.product_type,
                ProductSnapshot.variant_of,
            )
            .where(ProductSnapshot.sku.in_(list(missing_skus)))
            .order_by(ProductSnapshot.sku, ProductSnapshot.valid_from.desc())
        )
        snapshot_info: dict[str, tuple[Optional[str], Optional[str]]] = {}
        for row in session.execute(snapshot_stmt).all():
            if row.sku not in snapshot_info:
                snapshot_info[row.sku] = (row.product_type, row.variant_of)

        # Skip SKUs already pending to avoid duplicate flags
        existing_stmt = (
            select(MagentoDeletionReview.sku)
            .where(
                MagentoDeletionReview.connection_id == connection_id,
                MagentoDeletionReview.status == "pending",
                MagentoDeletionReview.sku.in_(list(missing_skus)),
            )
        )
        already_pending: set[str] = {row for (row,) in session.execute(existing_stmt).all()}

        new_skus = missing_skus - already_pending
        if not new_skus:
            logger.info(
                "[deletion_detector] connection=%d all %d missing SKUs already pending",
                connection_id, len(missing_skus),
            )
            return 0

        rows_to_insert: List[MagentoDeletionReview] = []
        for sku in sorted(new_skus):
            pt, variant_of = snapshot_info.get(sku, (None, None))
            # Normalize product_type to "simple" or "configurable" only
            if pt and "configurable" in pt.lower():
                product_type: Optional[str] = "configurable"
            elif pt and "simple" in pt.lower():
                product_type = "simple"
            else:
                product_type = pt  # keep as-is if unknown

            rows_to_insert.append(
                MagentoDeletionReview(
                    connection_id=connection_id,
                    sku=sku,
                    product_type=product_type,
                    parent_sku=variant_of or None,
                    ingest_run_id=ingest_run_id,
                    flagged_at=datetime.now(timezone.utc),
                    status="pending",
                    created_at=datetime.now(timezone.utc),
                )
            )

        session.add_all(rows_to_insert)
        session.commit()

        logger.info(
            "[deletion_detector] connection=%d flagged %d SKUs for deletion review",
            connection_id, len(rows_to_insert),
        )

        _send_notification_email(connection_id, rows_to_insert)
        return len(rows_to_insert)


def _send_notification_email(connection_id: int, flagged_rows: list) -> None:
    """Send an HTML email with embedded CSV listing the flagged SKUs."""
    from settings import load_email_config
    from mailer.providers import get_email_sender

    cfg = load_email_config()
    recipient = cfg.to_default
    if not recipient:
        logger.info("[deletion_detector] EMAIL_TO_DEFAULT not set, skipping notification email")
        return

    sender = get_email_sender()

    # Build CSV string
    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(["sku", "product_type", "parent_sku", "flagged_at"])
    for row in flagged_rows:
        writer.writerow([
            row.sku,
            row.product_type or "",
            row.parent_sku or "",
            row.flagged_at.isoformat() if row.flagged_at else "",
        ])
    csv_content = buf.getvalue()

    count = len(flagged_rows)
    subject = f"[PlytixMage] {count} SKU(s) flagged for deletion review (connection {connection_id})"

    html = f"""
<h2>SKU Deletion Review Required</h2>
<p><strong>{count} SKU(s)</strong> were present in previous Plytix feeds but are
<strong>missing from the latest feed</strong> for Magento connection
<strong>{connection_id}</strong>.</p>
<p>These SKUs are currently <strong>held</strong> — no Magento updates will be pushed
for them until a decision is made.</p>
<p>To approve or reject deletions, call:</p>
<pre>POST /api/magento/deletion-review/decide
{{
  "connection_id": {connection_id},
  "approve": ["SKU1", "SKU2"],
  "reject": ["SKU3"]
}}</pre>
<p>To list all pending reviews:</p>
<pre>GET /api/magento/deletion-review?connection_id={connection_id}</pre>
<h3>Flagged SKUs (CSV)</h3>
<pre>{csv_content}</pre>
"""

    ok = sender.send(to=recipient, subject=subject, html=html)
    if ok:
        logger.info("[deletion_detector] notification email sent to %s", recipient)
    else:
        logger.error("[deletion_detector] failed to send notification email to %s", recipient)