"""Async SMTP email sender using aiosmtplib."""

from __future__ import annotations

import logging
from datetime import datetime, timezone
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import aiosmtplib

from database.connection_manager import ConnectionManager
from notifications.email_templates import (
    render_change_notification,
    render_chain_status,
    render_stage_transition,
    render_test_email,
)

logger = logging.getLogger(__name__)


class EmailSender:
    """Send notification emails via SMTP."""

    def __init__(self, cm: ConnectionManager) -> None:
        self._cm = cm

    async def _get_smtp_config(self) -> dict:
        row = await self._cm.fetch_one(
            "SELECT value FROM v3q_config.notification_settings WHERE key = 'smtp'"
        )
        if not row:
            return {}
        import json
        val = row["value"]
        return json.loads(val) if isinstance(val, str) else val

    async def _get_global_config(self) -> dict:
        row = await self._cm.fetch_one(
            "SELECT value FROM v3q_config.notification_settings WHERE key = 'global'"
        )
        if not row:
            return {"enabled": False, "max_retries": 3}
        import json
        val = row["value"]
        return json.loads(val) if isinstance(val, str) else val

    async def send_pending_notifications(self) -> int:
        """Send all pending notifications from notification_log."""
        global_cfg = await self._get_global_config()
        if not global_cfg.get("enabled", False):
            return 0

        smtp_cfg = await self._get_smtp_config()
        if not smtp_cfg.get("host"):
            logger.warning("SMTP not configured, skipping notification send")
            return 0

        max_retries = global_cfg.get("max_retries", 3)
        batch_size = global_cfg.get("batch_size", 50)

        pending = await self._cm.execute(
            f"""SELECT * FROM v3q_config.notification_log
                WHERE status = 'pending' AND retry_count < :max_retries
                ORDER BY created_at
                LIMIT :batch_size""",
            {"max_retries": max_retries, "batch_size": batch_size},
        )

        if not pending:
            return 0

        sent_count = 0
        for notif in pending:
            notif = dict(notif)
            try:
                # Load associated events for the notification
                changes = await self._load_changes_for_notification(notif)

                html_body = render_change_notification(
                    doc_number=notif["doc_number"],
                    changes=changes,
                )

                await self._send_email(
                    smtp_cfg=smtp_cfg,
                    to_email=notif["email"],
                    subject=notif["subject"],
                    html_body=html_body,
                )

                await self._cm.execute_write(
                    """UPDATE v3q_config.notification_log
                       SET status = 'sent', sent_at = :sent_at
                       WHERE id = :id""",
                    {"id": str(notif["id"]), "sent_at": datetime.now(timezone.utc).isoformat()},
                )
                sent_count += 1

            except Exception as e:
                logger.error(f"Failed to send notification {notif['id']}: {e}")
                await self._cm.execute_write(
                    """UPDATE v3q_config.notification_log
                       SET status = CASE WHEN retry_count + 1 >= :max_retries THEN 'failed' ELSE 'pending' END,
                           retry_count = retry_count + 1,
                           error_message = :error
                       WHERE id = :id""",
                    {
                        "id": str(notif["id"]),
                        "error": str(e),
                        "max_retries": max_retries,
                    },
                )

        logger.info(f"Email sender: {sent_count}/{len(pending)} notifications sent")
        return sent_count

    async def send_test_email(self, to_email: str) -> dict:
        """Send a test email to verify SMTP configuration."""
        smtp_cfg = await self._get_smtp_config()
        if not smtp_cfg.get("host"):
            return {"success": False, "error": "SMTP host not configured"}

        try:
            timestamp = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")
            html_body = render_test_email(timestamp)

            await self._send_email(
                smtp_cfg=smtp_cfg,
                to_email=to_email,
                subject="[V3Q.AI] SMTP Test Email",
                html_body=html_body,
            )
            return {"success": True, "message": f"Test email sent to {to_email}"}
        except Exception as e:
            return {"success": False, "error": str(e)}

    async def _send_email(
        self, smtp_cfg: dict, to_email: str, subject: str, html_body: str
    ) -> None:
        msg = MIMEMultipart("alternative")
        msg["From"] = f"{smtp_cfg.get('from_name', 'V3Q.AI')} <{smtp_cfg['from_email']}>"
        msg["To"] = to_email
        msg["Subject"] = subject
        msg.attach(MIMEText(html_body, "html"))

        await aiosmtplib.send(
            msg,
            hostname=smtp_cfg["host"],
            port=smtp_cfg.get("port", 587),
            username=smtp_cfg.get("username") or None,
            password=smtp_cfg.get("password") or None,
            start_tls=smtp_cfg.get("use_tls", True),
        )

    async def send_subscription_confirmation(
        self, email: str, doc_number: str, source_system: str | None = None
    ) -> dict:
        """Send initial subscription email with current chain status."""
        smtp_cfg = await self._get_smtp_config()
        if not smtp_cfg.get("host"):
            return {"success": False, "error": "SMTP host not configured"}

        global_cfg = await self._get_global_config()
        if not global_cfg.get("enabled", False):
            return {"success": False, "error": "Notifications disabled"}

        try:
            # Load stage definitions for this document
            stages = await self._cm.execute(
                """SELECT * FROM v3q_config.status_stage_definitions
                   ORDER BY doc_type, source_system, stage_order""",
            )
            stage_list = []
            for s in stages:
                stage_list.append({
                    "stage_label": s["stage_label"],
                    "stage_key": s["stage_key"],
                    "source_system": s.get("source_system", ""),
                    "status": "upcoming",  # default; will be updated below
                })

            # Load linked chain documents if available
            chain_docs = []
            try:
                chain_rows = await self._cm.execute(
                    """SELECT dl.* FROM v3q_config.document_links dl
                       JOIN v3q_config.document_chains dc ON dl.chain_id = dc.id
                       WHERE dc.chain_reference LIKE :ref
                       ORDER BY dl.created_at""",
                    {"ref": f"%{doc_number}%"},
                )
                chain_docs = [dict(r) for r in chain_rows]
            except Exception:
                pass  # Document tracking tables may not exist yet

            html_body = render_chain_status(
                doc_number=doc_number,
                stages=stage_list,
                chain_documents=chain_docs,
            )

            await self._send_email(
                smtp_cfg=smtp_cfg,
                to_email=email,
                subject=f"[V3Q.AI] Subscription confirmed: tracking {doc_number}",
                html_body=html_body,
            )

            # Log the confirmation email
            await self._cm.execute_write(
                """INSERT INTO v3q_config.notification_log
                   (email, doc_number, subject, status, sent_at)
                   VALUES (:email, :doc_number, :subject, 'sent', now())""",
                {
                    "email": email,
                    "doc_number": doc_number,
                    "subject": f"[V3Q.AI] Subscription confirmed: tracking {doc_number}",
                },
            )

            return {"success": True, "message": f"Confirmation email sent to {email}"}
        except Exception as e:
            logger.error(f"Failed to send subscription confirmation: {e}")
            return {"success": False, "error": str(e)}

    async def _load_changes_for_notification(self, notif: dict) -> list[dict]:
        """Load sync_events associated with a notification."""
        event_id = notif.get("event_id")
        doc_number = notif.get("doc_number")

        if event_id:
            # Load this event and related events for the same doc_number
            rows = await self._cm.execute(
                """SELECT column_name, old_value, new_value, change_type
                   FROM v3q_data.sync_events
                   WHERE doc_number = :doc_number AND processed = true
                   ORDER BY detected_at DESC LIMIT 10""",
                {"doc_number": doc_number},
            )
        else:
            rows = []

        if rows:
            return [dict(r) for r in rows]
        # Fallback: create a generic change entry from the notification subject
        return [{"column_name": "status", "old_value": None, "new_value": "updated", "change_type": "changed"}]
