import { and, eq, gte, lte } from "drizzle-orm"
import { db } from "@/lib/db"
import {
  follow_up_rules,
  follow_up_log,
  rent_payments,
  maintenance_requests,
  leases,
  units,
} from "@/lib/db/schema"
import { logActivity } from "@/lib/activity"
import { sendEmail, followUpHtml } from "@/lib/email/send"

/**
 * Process all active follow-up rules for a single owner account and send the
 * resulting emails. Everything is scoped by `userId` (the effective owner id).
 * Returns the number of follow-up actions triggered.
 */
export async function runFollowUpsForUser(userId: string): Promise<{ sent: number }> {
  const ownerId = userId

  const rules = await db
    .select()
    .from(follow_up_rules)
    .where(and(eq(follow_up_rules.user_id, ownerId), eq(follow_up_rules.is_active, true)))

  if (!rules.length) return { sent: 0 }

  const now = new Date()
  const followUpsToLog: (typeof follow_up_log.$inferInsert)[] = []

  for (const rule of rules) {
    const cutoff = new Date(now)
    cutoff.setDate(cutoff.getDate() - rule.trigger_days)

    if (rule.type === "overdue_rent") {
      const overdue = await db.query.rent_payments.findMany({
        where: and(
          eq(rent_payments.user_id, ownerId),
          eq(rent_payments.status, "overdue"),
          lte(rent_payments.due_date, cutoff.toISOString().slice(0, 10))
        ),
        columns: { id: true, amount: true, due_date: true },
        with: {
          tenant: { columns: { first_name: true, last_name: true, email: true } },
        },
      })

      for (const payment of overdue) {
        const tenant = payment.tenant
        if (!tenant?.email) continue
        const daysOverdue = Math.ceil((now.getTime() - new Date(payment.due_date).getTime()) / (1000 * 60 * 60 * 24))
        followUpsToLog.push({
          user_id: ownerId,
          rule_id: rule.id,
          type: "overdue_rent",
          recipient_name: `${tenant.first_name} ${tenant.last_name}`,
          recipient_email: tenant.email,
          subject: `Rent Payment Reminder — ${daysOverdue} Days Overdue`,
          message: rule.message_template
            ?? `Dear ${tenant.first_name}, your rent payment of $${Number(payment.amount).toLocaleString()} was due on ${payment.due_date} and is now ${daysOverdue} days overdue. Please make your payment as soon as possible to avoid further action.`,
          status: "sent",
        })
      }
    }

    if (rule.type === "maintenance_stale") {
      const stale = await db.query.maintenance_requests.findMany({
        where: and(
          eq(maintenance_requests.user_id, ownerId),
          eq(maintenance_requests.status, "open"),
          lte(maintenance_requests.created_at, cutoff.toISOString())
        ),
        columns: { id: true, title: true, priority: true, created_at: true },
        with: {
          tenant: { columns: { first_name: true, last_name: true, email: true } },
        },
      })

      for (const req of stale) {
        const tenant = req.tenant
        const daysOpen = Math.ceil((now.getTime() - new Date(req.created_at).getTime()) / (1000 * 60 * 60 * 24))
        followUpsToLog.push({
          user_id: ownerId,
          rule_id: rule.id,
          type: "maintenance_stale",
          recipient_name: tenant ? `${tenant.first_name} ${tenant.last_name}` : "N/A",
          recipient_email: tenant?.email ?? null,
          subject: `Maintenance Update: ${req.title}`,
          message: rule.message_template
            ?? `Your maintenance request "${req.title}" has been open for ${daysOpen} days. We are working on resolving this as soon as possible and will update you shortly.`,
          status: "sent",
        })
      }
    }

    if (rule.type === "lease_renewal") {
      const renewalDate = new Date(now)
      renewalDate.setDate(renewalDate.getDate() + rule.trigger_days)

      const expiring = await db.query.leases.findMany({
        where: and(
          eq(leases.user_id, ownerId),
          eq(leases.status, "active"),
          lte(leases.lease_end, renewalDate.toISOString().slice(0, 10)),
          gte(leases.lease_end, now.toISOString().slice(0, 10))
        ),
        columns: { id: true, lease_end: true, rent_amount: true },
        with: {
          tenant: { columns: { first_name: true, last_name: true, email: true } },
        },
      })

      for (const lease of expiring) {
        const tenant = lease.tenant
        if (!tenant?.email) continue
        const daysLeft = Math.ceil((new Date(lease.lease_end).getTime() - now.getTime()) / (1000 * 60 * 60 * 24))
        followUpsToLog.push({
          user_id: ownerId,
          rule_id: rule.id,
          type: "lease_renewal",
          recipient_name: `${tenant.first_name} ${tenant.last_name}`,
          recipient_email: tenant.email,
          subject: `Lease Renewal Notice — Expires in ${daysLeft} Days`,
          message: rule.message_template
            ?? `Dear ${tenant.first_name}, your lease expires on ${lease.lease_end} (${daysLeft} days from now). Please contact us to discuss renewal options and ensure continuity of your tenancy.`,
          status: "sent",
        })
      }
    }

    if (rule.type === "vacant_unit") {
      const vacant = await db.query.units.findMany({
        where: and(eq(units.user_id, ownerId), eq(units.status, "vacant")),
        columns: { id: true, unit_number: true, rent_amount: true },
        with: {
          property: { columns: { name: true } },
        },
      })

      for (const unit of vacant) {
        const property = unit.property
        followUpsToLog.push({
          user_id: ownerId,
          rule_id: rule.id,
          type: "vacant_unit",
          recipient_name: "You",
          recipient_email: null,
          subject: `Vacant Unit Alert: ${property?.name ?? ""} — Unit ${unit.unit_number}`,
          message: rule.message_template
            ?? `Unit ${unit.unit_number} at ${property?.name ?? "your property"} has been vacant. Consider reviewing your listing or adjusting the rent of $${Number(unit.rent_amount).toLocaleString()}/month to attract tenants faster.`,
          status: "sent",
        })
      }
    }

    // Update last_run_at
    await db
      .update(follow_up_rules)
      .set({ last_run_at: now.toISOString() })
      .where(and(eq(follow_up_rules.id, rule.id), eq(follow_up_rules.user_id, ownerId)))
  }

  // Send actual emails for all follow-ups that have a recipient
  for (const log of followUpsToLog) {
    if (log.recipient_email) {
      try {
        await sendEmail({
          to: log.recipient_email,
          subject: log.subject,
          html: followUpHtml(log.message),
        })
      } catch {
        log.status = "failed"
      }
    }
  }

  if (followUpsToLog.length > 0) {
    await db.insert(follow_up_log).values(followUpsToLog)
  }

  await logActivity({
    userId: ownerId,
    type: "ai_action",
    title: `Follow-ups processed: ${followUpsToLog.length} action${followUpsToLog.length !== 1 ? "s" : ""} triggered`,
  })

  return { sent: followUpsToLog.length }
}
