"""Configurable email alerts for business usage-limit and validity events.

This feature owns its own SMTP transport config, fully independent of the
support-ticket email settings. It only borrows the generic ``_smtp_send``
transport helper (a pure sender that takes a config dict).

Two levels of configuration:
  * Global   (settings key ``usage_validity_alert_email``): SMTP transport,
    master enable, global recipient list, and default thresholds
    (usage %, validity warn-days).
  * Per-business (table ``pca_usage_alert_prefs``): optional own enable flag,
    extra recipients (added to global), and threshold overrides. Also stores
    the per-event "already sent" state so each threshold alerts at most once
    per fresh transition.

Events are dynamic:
  * ``usage_<t>``       — transcribed minutes reached t% of the limit (e.g. 90/95/100)
  * ``validity_<d>d``   — d days (or fewer) remain before validity expiry (e.g. 7/1)
  * ``validity_expired``— validity period has passed
"""

from __future__ import annotations

import datetime
import json
import logging
from typing import Any, Dict, List, Optional

from ticket_notification_service import (
    _smtp_send,
    ensure_notification_settings_schema,
)
from usage_allocation_util import evaluate_usage_allocation

logger = logging.getLogger(__name__)

SETTING_KEY = "usage_validity_alert_email"
PASSWORD_MASK = "********"

DEFAULT_USAGE_THRESHOLDS = [90, 95, 100]
DEFAULT_VALIDITY_WARN_DAYS = [7, 1]

DEFAULT_CONFIG: Dict[str, Any] = {
    "enabled": False,
    "recipients": [],
    "smtp_host": "",
    "smtp_port": 587,
    "smtp_user": "",
    "smtp_password": "",
    "smtp_use_tls": True,
    "from_email": "",
    "from_name": "PCA Alerts",
    "reply_to_email": "",
    "usage_thresholds": list(DEFAULT_USAGE_THRESHOLDS),
    "validity_warn_days": list(DEFAULT_VALIDITY_WARN_DAYS),
}


# --------------------------------------------------------------------------- #
# Helpers
# --------------------------------------------------------------------------- #
def _table_exists(cursor, table_name: str) -> bool:
    cursor.execute(
        """
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = %s LIMIT 1
        """,
        (table_name,),
    )
    return cursor.fetchone() is not None


def _clean_int_list(value: Any, *, lo: int, hi: int) -> Optional[List[int]]:
    if value is None:
        return None
    if not isinstance(value, (list, tuple)):
        return None
    out: List[int] = []
    for item in value:
        try:
            n = int(item)
        except (TypeError, ValueError):
            continue
        if lo <= n <= hi and n not in out:
            out.append(n)
    return sorted(out)


def _merge_defaults(raw: Optional[Dict[str, Any]]) -> Dict[str, Any]:
    base = json.loads(json.dumps(DEFAULT_CONFIG))
    if not raw:
        return base
    for key, value in raw.items():
        base[key] = value
    # Normalise threshold lists.
    ut = _clean_int_list(base.get("usage_thresholds"), lo=1, hi=100)
    base["usage_thresholds"] = ut if ut is not None else list(DEFAULT_USAGE_THRESHOLDS)
    vd = _clean_int_list(base.get("validity_warn_days"), lo=0, hi=365)
    base["validity_warn_days"] = vd if vd is not None else list(DEFAULT_VALIDITY_WARN_DAYS)
    return base


def _validate_smtp_host(host: str) -> str:
    host = str(host or "").strip()
    if not host:
        raise ValueError("SMTP host is not configured for usage & validity alerts.")
    if "@" in host:
        raise ValueError(
            "SMTP host must be a server name (e.g. smtp.office365.com or smtp.gmail.com), "
            "not an email address. Put the email in Username / From email instead."
        )
    if " " in host or "/" in host:
        raise ValueError("SMTP host looks invalid. Use a hostname like smtp.office365.com.")
    return host


# --------------------------------------------------------------------------- #
# Global config
# --------------------------------------------------------------------------- #
def get_usage_alert_config(cursor, *, include_secrets: bool = False) -> Dict[str, Any]:
    ensure_notification_settings_schema(cursor)
    stored: Dict[str, Any] = {}
    if _table_exists(cursor, "pcaa_notification_settings"):
        cursor.execute(
            "SELECT config_json FROM pcaa_notification_settings WHERE setting_key = %s LIMIT 1",
            (SETTING_KEY,),
        )
        row = cursor.fetchone()
        if row and row.get("config_json"):
            raw = row["config_json"]
            stored = raw if isinstance(raw, dict) else json.loads(raw)

    config = _merge_defaults(stored)
    if include_secrets:
        return config

    masked = dict(config)
    if masked.get("smtp_password"):
        masked["smtp_password"] = PASSWORD_MASK
        masked["has_smtp_password"] = True
    else:
        masked["has_smtp_password"] = False
    return masked


def save_usage_alert_config(cursor, payload: Dict[str, Any], updated_by: Optional[str] = None) -> Dict[str, Any]:
    ensure_notification_settings_schema(cursor)
    current = get_usage_alert_config(cursor, include_secrets=True)
    config = _merge_defaults(payload or {})
    config["recipients"] = [
        str(r).strip() for r in (config.get("recipients") or []) if str(r or "").strip()
    ]

    password = str(config.get("smtp_password") or "").strip()
    if not password or password == PASSWORD_MASK:
        config["smtp_password"] = current.get("smtp_password") or ""

    host = str(config.get("smtp_host") or "").strip()
    if host:
        config["smtp_host"] = _validate_smtp_host(host)

    cursor.execute(
        """
        INSERT INTO pcaa_notification_settings (setting_key, config_json, updated_by)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE config_json = VALUES(config_json), updated_by = VALUES(updated_by)
        """,
        (SETTING_KEY, json.dumps(config), updated_by),
    )
    return get_usage_alert_config(cursor, include_secrets=False)


def _format_date_human(value) -> str:
    if not value:
        return "N/A"
    if isinstance(value, datetime.datetime):
        d = value.date()
    elif isinstance(value, datetime.date):
        d = value
    else:
        try:
            d = datetime.date.fromisoformat(str(value)[:10])
        except ValueError:
            return str(value)
    return d.strftime("%d %b %Y")


def _build_subscription_reminder_email(name: str, bid: str, usage: Dict[str, Any]):
    validity_months = usage.get("validity_months") or 0
    expiry = usage.get("validity_expiry_date")
    days_remaining = _days_until_expiry(usage)
    used = usage.get("used_minutes") or 0
    limit = usage.get("monthly_minute_limit") or 0
    pct = usage.get("usage_percent")
    pct_display = f"{pct:.2f}%" if pct is not None else "N/A"
    if days_remaining is None:
        days_display = "N/A"
    elif days_remaining < 0:
        days_display = f"Expired {abs(days_remaining)} day(s) ago"
    else:
        days_display = f"{days_remaining} Days"

    subject = f"[PCA TEST] Subscription & Usage Reminder — {name} (BID {bid})"
    body = (
        "Dear Team,\n"
        "This is a friendly reminder that your PCA (Post Call Analytics) subscription is approaching its expiry date.\n\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n"
        "SUBSCRIPTION DETAILS\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n"
        f"Business Name       : {name}\n"
        f"Business ID         : {bid}\n\n"
        f"Subscription Validity : {validity_months} Months\n"
        f"Expiry Date           : {_format_date_human(expiry)}\n"
        f"Days Remaining        : {days_display}\n\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n"
        "CURRENT USAGE\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n"
        f"Current Usage       : {used:,} Minutes\n"
        f"Allocated Limit     : {limit:,} Minutes\n"
        f"Usage Percentage    : {pct_display}\n\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n"
        "ACTION REQUIRED\n"
        "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n"
        "Your subscription is nearing expiration.\n"
        "To ensure uninterrupted access to PCA services, please renew or extend your subscription before the expiry date.\n"
        "If your renewal has already been processed, kindly ignore this email.\n\n"
        "For assistance or renewal support, please contact our support team.\n\n"
        "Thank you for choosing PCA.\n\n"
        "Regards,\n\n"
        "PCA Support Team\n\n"
        "[This is a TEST email sent via the PCA usage & validity alert test path. "
        "No thresholds were marked as already-sent by this test.]"
    )
    return subject, body


def send_test_usage_alert_email(cursor, to_email: str, bid: Optional[str] = None) -> Dict[str, Any]:
    config = get_usage_alert_config(cursor, include_secrets=True)
    if not config.get("enabled"):
        raise ValueError("Usage & validity alert emails are disabled. Enable them first.")
    config["smtp_host"] = _validate_smtp_host(config.get("smtp_host"))

    if bid:
        name = _business_name(cursor, str(bid))
        usage = evaluate_usage_allocation(cursor, str(bid))
        subject, body = _build_subscription_reminder_email(name, str(bid), usage)
    else:
        subject = "PCA Usage & Validity Alerts — test email"
        body = "This is a test email from the PCA usage & validity alert notification system."

    _smtp_send(config, [to_email], subject, body)
    return {"success": True, "sent_to": to_email, "bid": str(bid) if bid else None}


# --------------------------------------------------------------------------- #
# Per-business preferences
# --------------------------------------------------------------------------- #
def ensure_alert_prefs_table(cursor) -> None:
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS pca_usage_alert_prefs (
            bid VARCHAR(64) NOT NULL PRIMARY KEY,
            enabled TINYINT NULL,
            recipients_json JSON NULL,
            usage_thresholds_json JSON NULL,
            validity_warn_days_json JSON NULL,
            sent_state_json JSON NULL,
            updated_by VARCHAR(100) NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
    )


def _load_json_field(row: Dict[str, Any], key: str):
    raw = row.get(key)
    if raw is None:
        return None
    if isinstance(raw, (dict, list)):
        return raw
    try:
        return json.loads(raw)
    except (TypeError, ValueError):
        return None


def get_business_alert_prefs(cursor, bid: str, *, include_state: bool = False) -> Dict[str, Any]:
    ensure_alert_prefs_table(cursor)
    cursor.execute(
        "SELECT * FROM pca_usage_alert_prefs WHERE bid = %s LIMIT 1",
        (str(bid),),
    )
    row = cursor.fetchone() or {}
    enabled = row.get("enabled")
    prefs: Dict[str, Any] = {
        "bid": str(bid),
        "enabled": (None if enabled is None else bool(enabled)),
        "recipients": _load_json_field(row, "recipients_json") or [],
        "usage_thresholds": _clean_int_list(_load_json_field(row, "usage_thresholds_json"), lo=1, hi=100),
        "validity_warn_days": _clean_int_list(_load_json_field(row, "validity_warn_days_json"), lo=0, hi=365),
    }
    if include_state:
        prefs["sent_state"] = _load_json_field(row, "sent_state_json") or {}
    return prefs


def save_business_alert_prefs(cursor, bid: str, payload: Dict[str, Any], updated_by: Optional[str] = None) -> Dict[str, Any]:
    ensure_alert_prefs_table(cursor)
    enabled = payload.get("enabled")
    enabled_val = None if enabled is None else (1 if bool(enabled) else 0)
    recipients = [
        str(r).strip() for r in (payload.get("recipients") or []) if str(r or "").strip()
    ]
    usage_thresholds = _clean_int_list(payload.get("usage_thresholds"), lo=1, hi=100)
    validity_warn_days = _clean_int_list(payload.get("validity_warn_days"), lo=0, hi=365)

    cursor.execute(
        """
        INSERT INTO pca_usage_alert_prefs
            (bid, enabled, recipients_json, usage_thresholds_json, validity_warn_days_json, updated_by)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            enabled = VALUES(enabled),
            recipients_json = VALUES(recipients_json),
            usage_thresholds_json = VALUES(usage_thresholds_json),
            validity_warn_days_json = VALUES(validity_warn_days_json),
            updated_by = VALUES(updated_by)
        """,
        (
            str(bid),
            enabled_val,
            json.dumps(recipients),
            json.dumps(usage_thresholds) if usage_thresholds is not None else None,
            json.dumps(validity_warn_days) if validity_warn_days is not None else None,
            updated_by,
        ),
    )
    return get_business_alert_prefs(cursor, bid)


def _save_sent_state(cursor, bid: str, sent_state: Dict[str, Any]) -> None:
    ensure_alert_prefs_table(cursor)
    cursor.execute(
        """
        INSERT INTO pca_usage_alert_prefs (bid, sent_state_json)
        VALUES (%s, %s)
        ON DUPLICATE KEY UPDATE sent_state_json = VALUES(sent_state_json)
        """,
        (str(bid), json.dumps(sent_state)),
    )


# --------------------------------------------------------------------------- #
# Sending
# --------------------------------------------------------------------------- #
def _business_name(cursor, bid: str) -> str:
    if not _table_exists(cursor, "businesses"):
        return f"Business {bid}"
    cursor.execute("SELECT name FROM businesses WHERE bid = %s LIMIT 1", (str(bid),))
    row = cursor.fetchone() or {}
    return str(row.get("name") or "").strip() or f"Business {bid}"


def _days_until_expiry(usage: Dict[str, Any]) -> Optional[int]:
    expiry = usage.get("validity_expiry_date")
    if not expiry:
        return None
    if isinstance(expiry, datetime.datetime):
        expiry_date = expiry.date()
    elif isinstance(expiry, datetime.date):
        expiry_date = expiry
    else:
        try:
            expiry_date = datetime.date.fromisoformat(str(expiry)[:10])
        except ValueError:
            return None
    return (expiry_date - datetime.date.today()).days


def _events_for(usage: Dict[str, Any], usage_thresholds: List[int], validity_warn_days: List[int]):
    """Yield (event_key, condition, kind, value)."""
    events = []
    up = usage.get("usage_percent")
    if not usage.get("unlimited") and up is not None:
        for t in sorted(set(usage_thresholds)):
            events.append((f"usage_{t}", up >= t, "usage", t))

    if usage.get("validity_expiry_date"):
        expired = bool(usage.get("validity_expired"))
        days = usage.get("validity_days_remaining")
        for d in sorted(set(validity_warn_days)):
            if d <= 0:
                continue
            cond = (not expired) and (days is not None) and (0 <= days <= d)
            events.append((f"validity_{d}d", cond, "validity_warn", d))
        events.append(("validity_expired", expired, "validity_expired", 0))
    return events


def _build_email(event_key: str, kind: str, value: int, name: str, bid: str, usage: Dict[str, Any]):
    used = usage.get("used_minutes")
    limit = usage.get("monthly_minute_limit")
    pct = usage.get("usage_percent")
    expiry = usage.get("validity_expiry_date")
    days = usage.get("validity_days_remaining")
    validity_months = usage.get("validity_months")
    remaining = max((limit or 0) - (used or 0), 0) if not usage.get("unlimited") else "Unlimited"
    usage_text = f"{pct}%" if pct is not None else "N/A (unlimited)"
    expiry_text = str(expiry) if expiry else "Not configured"
    validity_period = (
        f"{validity_months} month(s) from onboarding"
        if validity_months and validity_months > 0
        else "Not configured"
    )
    if not expiry:
        validity_status = "Not configured"
    elif usage.get("validity_expired"):
        validity_status = "Expired"
    elif days is not None:
        validity_status = f"Active ({days} day(s) remaining)"
    else:
        validity_status = "Active"

    account_details = (
        f"Used minutes: {used}\n"
        f"Total allocated minutes: {limit if not usage.get('unlimited') else 'Unlimited'}\n"
        f"Remaining minutes: {remaining}\n"
        f"Usage: {usage_text}\n"
        f"Validity period: {validity_period}\n"
        f"Validity expiry: {expiry_text}\n"
        f"Validity status: {validity_status}"
    )

    if kind == "usage":
        if value >= 100:
            subject = f"[PCA] Usage limit exhausted — {name} (BID {bid})"
            body = (
                f"Business {name} (BID {bid}) has exhausted its allocated minutes.\n\n"
                f"{account_details}\n\n"
                "The pipeline for this business may be auto-paused. "
                "Open PCA Master Panel > Usage to review and top up."
            )
        else:
            subject = f"[PCA] Usage at {value}% — {name} (BID {bid})"
            body = (
                f"Business {name} (BID {bid}) has reached {value}% of its allocated minutes.\n\n"
                f"{account_details}\n\n"
                "Consider topping up before the limit is reached. "
                "Open PCA Master Panel > Usage to review."
            )
        return subject, body

    if kind == "validity_warn":
        subject = f"[PCA] Validity expiring in ~{value} day(s) — {name} (BID {bid})"
        body = (
            f"Business {name} (BID {bid}) will reach its validity expiry soon.\n\n"
            f"{account_details}\n\n"
            "Open PCA Master Panel > Usage to extend validity before it expires."
        )
        return subject, body

    # validity_expired
    subject = f"[PCA] Validity expired — {name} (BID {bid})"
    body = (
        f"Business {name} (BID {bid}) has passed its configured validity period.\n\n"
        f"{account_details}\n\n"
        "The pipeline for this business may be auto-paused. "
        "Open PCA Master Panel > Usage to review and extend validity."
    )
    return subject, body


def maybe_send_usage_validity_alert(cursor, bid: str, usage: Dict[str, Any]) -> None:
    """Send at most one email per threshold per fresh transition into that state.

    Called once per supervisor sweep per bid. Each event's sent-flag is cleared
    when its condition resolves (admin tops up minutes / extends validity) so a
    future re-occurrence alerts again.
    """
    try:
        if not _table_exists(cursor, "pca_business_allocations"):
            return

        global_cfg = get_usage_alert_config(cursor, include_secrets=True)
        if not global_cfg.get("enabled"):
            return
        if not str(global_cfg.get("smtp_host") or "").strip():
            return

        prefs = get_business_alert_prefs(cursor, bid, include_state=True)
        # Per-business opt-out: explicit False disables alerts for this business.
        if prefs.get("enabled") is False:
            return

        recipients = list(
            dict.fromkeys(
                [r for r in (global_cfg.get("recipients") or []) if r]
                + [r for r in (prefs.get("recipients") or []) if r]
            )
        )
        if not recipients:
            return

        usage_thresholds = prefs.get("usage_thresholds") or global_cfg.get("usage_thresholds") or DEFAULT_USAGE_THRESHOLDS
        validity_warn_days = prefs.get("validity_warn_days") or global_cfg.get("validity_warn_days") or DEFAULT_VALIDITY_WARN_DAYS

        usage = dict(usage)
        usage["validity_days_remaining"] = _days_until_expiry(usage)

        events = _events_for(usage, usage_thresholds, validity_warn_days)
        sent_state = dict(prefs.get("sent_state") or {})
        name = _business_name(cursor, bid)
        changed = False

        for key, condition, kind, value in events:
            already = sent_state.get(key)
            if condition and not already:
                try:
                    subject, body = _build_email(key, kind, value, name, bid, usage)
                    _smtp_send(global_cfg, recipients, subject, body)
                    sent_state[key] = datetime.datetime.now().isoformat(timespec="seconds")
                    changed = True
                    logger.info("usage/validity alert sent event=%s bid=%s to=%s", key, bid, recipients)
                except Exception as exc:
                    logger.warning("usage/validity alert failed event=%s bid=%s: %s", key, bid, exc)
            elif not condition and already:
                sent_state[key] = None
                changed = True

        if changed:
            _save_sent_state(cursor, bid, sent_state)
    except Exception as exc:
        logger.warning("maybe_send_usage_validity_alert failed for bid=%s: %s", bid, exc)