"""
CA Automator — deadline sweep and email notifications.

Two things happen here: sweep_deadlines() finds pending deadlines that have
crossed one of their configured reminder points (or gone overdue) and haven't
already been notified today, raises an in-app notification for the assigned
staff member, and — if SMTP is configured — emails them too. Nothing here is
required for the app to run; if SMTP isn't configured, email is silently
skipped and the in-app notification still gets raised, so "Alerts" in the
header always works even on a laptop with no mail server anywhere near it.

SMTP configuration can come from either of two places, checked in this
order:

1. **The `smtp_config` table** — set from Admin > Settings > Email. This is
   deliberately its own table, never the `setting` table: `setting` is
   broadcast wholesale to every logged-in browser via GET /api/bootstrap,
   which is exactly the wrong place for a mailbox password (the same
   reasoning that keeps the credential-vault encryption key out of it too).
   The password is stored via db.encrypt_secret() — the same at-rest scheme
   client_credential uses — and a super admin is the only one who can read
   or write the row (and even then, never the password itself back out).
2. **Environment variables** — the original deployment path, still fully
   supported for a firm that would rather configure SMTP at the process
   level than through the UI:

    CA_SMTP_HOST      required to enable email at all
    CA_SMTP_PORT      default 587
    CA_SMTP_USER      SMTP auth username (optional — some relays don't need it)
    CA_SMTP_PASS      SMTP auth password
    CA_SMTP_FROM      default: CA_SMTP_USER, else 'no-reply@localhost'
    CA_SMTP_USE_TLS   default '1' — STARTTLS; set '0' for an already-encrypted
                      connection or a local relay that doesn't support it

A DB row, if present and has a host, wins over the environment variables
entirely (not merged field-by-field) so a partially-filled admin form can't
silently combine with leftover env vars into something nobody configured.

Both run.py's night worker and the "Sweep now" API endpoint call
sweep_deadlines() — one implementation, two ways to trigger it, so a firm
without cron access can still raise today's reminders by clicking a button.
"""
import os
import smtplib
import ssl
from datetime import datetime, timedelta, timezone
from email.message import EmailMessage

import db


def _smtp_settings():
    """DB config wins if present and has a host; otherwise environment
    variables; otherwise None (email disabled)."""
    row = db.query("SELECT * FROM smtp_config ORDER BY firm_id LIMIT 1", one=True)
    if row and row["host"]:
        return {
            "host": row["host"], "port": row["port"] or 587,
            "user": row["username"] or None,
            "pwd": db.decrypt_secret(row["password_enc"]) if row["password_enc"] else None,
            "from_addr": row["from_email"] or row["username"] or "no-reply@localhost",
            "use_tls": bool(row["use_tls"]),
        }
    if os.environ.get("CA_SMTP_HOST"):
        return {
            "host": os.environ["CA_SMTP_HOST"], "port": int(os.environ.get("CA_SMTP_PORT", "587")),
            "user": os.environ.get("CA_SMTP_USER"), "pwd": os.environ.get("CA_SMTP_PASS"),
            "from_addr": os.environ.get("CA_SMTP_FROM") or os.environ.get("CA_SMTP_USER") or "no-reply@localhost",
            "use_tls": os.environ.get("CA_SMTP_USE_TLS", "1") != "0",
        }
    return None


def smtp_configured():
    return _smtp_settings() is not None


def send_email(to_addr, subject, body, related_type=None, related_id=None):
    """Best-effort — returns True/False, never raises. A mail-server hiccup
    must never break the sweep or take down the in-app notification, which
    has already landed by the time this is called. Every attempt is logged
    to email_log (sent/failed/skipped) so Admin > Email can show what went
    out and what didn't, regardless of outcome."""
    cfg = _smtp_settings()
    if not cfg or not to_addr:
        db.execute(
            "INSERT INTO email_log (to_addr, subject, status, error, related_type, related_id) VALUES (?,?,?,?,?,?)",
            (to_addr or "", subject, "skipped", "SMTP not configured" if not cfg else "No recipient address",
             related_type, related_id))
        return False
    msg = EmailMessage()
    msg["Subject"] = subject
    msg["From"] = cfg["from_addr"]
    msg["To"] = to_addr
    msg.set_content(body)
    try:
        with smtplib.SMTP(cfg["host"], cfg["port"], timeout=15) as s:
            if cfg["use_tls"]:
                s.starttls(context=ssl.create_default_context())
            if cfg["user"] and cfg["pwd"]:
                s.login(cfg["user"], cfg["pwd"])
            s.send_message(msg)
        db.execute(
            "INSERT INTO email_log (to_addr, subject, status, related_type, related_id) VALUES (?,?,?,?,?)",
            (to_addr, subject, "sent", related_type, related_id))
        return True
    except Exception as e:
        print("notify: email send failed:", e)
        db.execute(
            "INSERT INTO email_log (to_addr, subject, status, error, related_type, related_id) VALUES (?,?,?,?,?,?)",
            (to_addr, subject, "failed", str(e), related_type, related_id))
        return False


def render_template(key, ctx):
    """Looks up an admin-editable email_template row by key and substitutes
    {{placeholder}} tokens from ctx (plain string replacement — no code
    execution, no arbitrary templating engine, on purpose). Returns
    (subject, body) or None if the key doesn't exist (a template can be
    deleted; callers should treat that as "no email configured for this
    event" rather than raising)."""
    row = db.query("SELECT subject, body FROM email_template WHERE `key` = ?", (key,), one=True)
    if not row:
        return None
    subject, body = row["subject"], row["body"]
    for k, v in ctx.items():
        token = "{{" + k + "}}"
        subject = subject.replace(token, str(v))
        body = body.replace(token, str(v))
    return subject, body


def sweep_deadlines():
    """Mark overdue items and raise notifications (in-app, plus email if
    configured) at each deadline's configured reminder points. Returns the
    count of notifications raised this run."""
    today = datetime.now(timezone.utc).date()
    raised = 0
    db.execute("UPDATE deadline SET status = 'late' WHERE status = 'pending' AND due_date_ad < ?",
               (today.isoformat(),))
    for d in db.query("SELECT * FROM deadline WHERE status IN ('pending','late')"):
        try:
            due = datetime.strptime(d["due_date_ad"], "%Y-%m-%d").date()
        except Exception:
            continue
        days_left = (due - today).days
        points = [int(x) for x in (d["reminder_days"] or "7,3,1").split(",") if x.strip().isdigit()]
        if days_left not in points and not (days_left < 0 and days_left % 7 == 0):
            continue
        last = d["last_notified_at"] or ""
        if last[:10] == today.isoformat():
            continue

        title = f"{d['title']} — {'overdue' if days_left < 0 else f'due in {days_left} days'}"
        body = f"{d.get('period_label') or ''} due {d['due_date_ad']}".strip()
        db.execute(
            "INSERT INTO notification (user_id, kind, title, body, link) VALUES (?,?,?,?,?)",
            (d["assigned_to"], "deadline", title, body, "/#/clients"))
        db.execute("UPDATE deadline SET last_notified_at = ? WHERE id = ?", (db.now_iso(), d["id"]))
        raised += 1

        if d["assigned_to"]:
            user = db.query("SELECT email, full_name FROM app_user WHERE id = ?", (d["assigned_to"],), one=True)
            if user and user["email"]:
                client = db.query("SELECT name FROM client WHERE id = ?", (d["client_id"],), one=True)
                client_name = client["name"] if client else ""
                send_email(user["email"], title,
                          f"{client_name}\n{title}\n{body}\n\n— CA Automator")
    return raised


def sweep_overdue_tasks():
    """Same "notified once per day" shape as sweep_deadlines(), but for the
    task table (which has its own due_date, independent of the deadline
    table) — fires workflow_engine's task.overdue event once per task per
    day it stays overdue, so a firm-configured workflow_rule (e.g. "notify
    the reviewer") actually has something calling it. Doesn't touch
    task.status — a task doesn't have a "late" status the way deadline does,
    "overdue" here is purely due_date < today AND status != 'done'."""
    import workflow_engine  # local import: notify.py must not depend on app.py's route layer, and workflow_engine has no app.py dependency of its own — this just avoids a needless import at module load for callers (e.g. tests) that never sweep tasks
    today = datetime.now(timezone.utc).date().isoformat()
    raised = 0
    overdue = db.query(
        "SELECT t.*, COALESCE(c.firm_id, c2.firm_id) firm_id FROM task t"
        " LEFT JOIN client c ON c.id = t.client_id"
        " LEFT JOIN engagement e ON e.id = t.engagement_id"
        " LEFT JOIN client c2 ON c2.id = e.client_id"
        " WHERE t.status != 'done' AND t.due_date IS NOT NULL AND t.due_date < ?"
        " AND (t.overdue_notified_at IS NULL OR t.overdue_notified_at < ?)",
        (today, today))
    for t in overdue:
        firm_id = t["firm_id"]
        if firm_id is None:
            continue  # an engagement-only task whose client lookup missed — nothing to scope the rule to
        if t["assignee_id"]:
            db.execute(
                "INSERT INTO notification (user_id, kind, title, body, link) VALUES (?,?,?,?,?)",
                (t["assignee_id"], "task_overdue", f"Overdue: {t['title']}",
                 f"Was due {t['due_date']}", "/#/tasks"))
        db.execute("UPDATE task SET overdue_notified_at = ? WHERE id = ?", (db.now_iso(), t["id"]))
        workflow_engine.fire_event(firm_id, "task.overdue", {
            "task_id": t["id"], "title": t["title"], "assignee_id": t["assignee_id"],
            "client_id": t["client_id"], "engagement_id": t["engagement_id"],
        })
        raised += 1
    return raised


def sweep_subscriptions():
    """Grace-period and suspension notices for firms on a term subscription
    (firm.subscription_expires_at set — a perpetual/grandfathered firm has
    it NULL and never enters this sweep at all). Each transition notifies
    every one of that firm's active super admins exactly once, tracked via
    firm.subscription_grace_notified_at / subscription_suspended_notified_at
    (both cleared whenever the expiry date is changed — see
    app.py's platform_update_firm — so a renewed firm gets fresh notices the
    next time it actually lapses). Nothing here ever touches client or
    workfile data — this only raises notices; app.py's _user() is what
    actually blocks a suspended firm's staff from using the app."""
    today = datetime.now(timezone.utc).date()
    raised = 0
    for f in db.query("SELECT * FROM firm WHERE subscription_expires_at IS NOT NULL AND is_active = 1"):
        try:
            expires = datetime.strptime(f["subscription_expires_at"], "%Y-%m-%d").date()
        except (TypeError, ValueError):
            continue
        grace_ends = expires + timedelta(days=db.SUBSCRIPTION_GRACE_DAYS)
        if today <= expires:
            continue

        if today <= grace_ends:
            already, column, kind = f["subscription_grace_notified_at"], "subscription_grace_notified_at", "subscription_grace"
            title = "Your CA-Automator subscription has expired"
            body = (f"It expired on {f['subscription_expires_at']}. Renew by {grace_ends.isoformat()} to avoid "
                    "service interruption — after that, staff won't be able to sign in until it's renewed. "
                    "Your client data is safe and will not be touched either way.")
        else:
            already, column, kind = f["subscription_suspended_notified_at"], "subscription_suspended_notified_at", "subscription_suspended"
            title = "CA-Automator access paused — renew to restore service"
            body = (f"Your subscription expired on {f['subscription_expires_at']} and the "
                    f"{db.SUBSCRIPTION_GRACE_DAYS}-day grace period has now ended, so staff can no longer sign in "
                    "to use the app. Your client data remains safely stored and untouched — access resumes "
                    "immediately once the subscription is renewed.")
        if already:
            continue

        admins = db.query("SELECT id, email FROM app_user WHERE firm_id = ? AND is_super_admin = 1 AND is_active = 1",
                          (f["id"],))
        for a in admins:
            db.execute("INSERT INTO notification (user_id, kind, title, body, link) VALUES (?,?,?,?,?)",
                      (a["id"], kind, title, body, "/#/admin"))
            if a["email"]:
                send_email(a["email"], title, body)
        db.execute(f"UPDATE firm SET {column} = ? WHERE id = ?", (db.now_iso(), f["id"]))
        raised += len(admins)
    return raised
