property-management-network/app/(dashboard)/reports/page.tsx

import { redirect } from "next/navigation"
import { and, eq, gte } from "drizzle-orm"
import { db } from "@/lib/db"
import { properties as propertiesTable, rent_payments, expenses as expensesTable } from "@/lib/db/schema"
import { getSessionUser } from "@/lib/session"
import { getEffectiveOwnerId } from "@/lib/account"
import { formatCurrency } from "@/lib/utils"
import { TrendingUp, TrendingDown, Building2, DollarSign, Receipt, BarChart3 } from "lucide-react"
import { ReportsClient } from "./reports-client"
import { CsvExportButton } from "@/components/forms/csv-export-button"

export const metadata = { title: "Reports" }

export default async function ReportsPage() {
  const user = await getSessionUser()
  if (!user) redirect("/login")

  const ownerId = await getEffectiveOwnerId(user.id)

  // Last 6 months range
  const sixMonthsAgo = new Date()
  sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 5)
  sixMonthsAgo.setDate(1)
  const rangeStart = sixMonthsAgo.toISOString().slice(0, 10)

  const [properties, payments, expenses] = await Promise.all([
    db
      .select({ id: propertiesTable.id, name: propertiesTable.name })
      .from(propertiesTable)
      .where(eq(propertiesTable.user_id, ownerId)),
    db
      .select({
        amount: rent_payments.amount,
        status: rent_payments.status,
        due_date: rent_payments.due_date,
        property_id: rent_payments.property_id,
      })
      .from(rent_payments)
      .where(and(eq(rent_payments.user_id, ownerId), gte(rent_payments.due_date, rangeStart))),
    db
      .select({
        amount: expensesTable.amount,
        expense_date: expensesTable.expense_date,
        property_id: expensesTable.property_id,
        category: expensesTable.category,
      })
      .from(expensesTable)
      .where(and(eq(expensesTable.user_id, ownerId), gte(expensesTable.expense_date, rangeStart))),
  ])

  // Build monthly buckets for last 6 months
  const months: { key: string; label: string }[] = []
  for (let i = 5; i >= 0; i--) {
    const d = new Date()
    d.setMonth(d.getMonth() - i)
    d.setDate(1)
    const key = d.toISOString().slice(0, 7)
    const label = d.toLocaleDateString("en-US", { month: "short", year: "2-digit" })
    months.push({ key, label })
  }

  // Monthly revenue & expenses
  const monthlyData = months.map(({ key, label }) => {
    const revenue  = (payments ?? []).filter(p => p.status === "paid" && p.due_date?.startsWith(key)).reduce((s, p) => s + Number(p.amount), 0)
    const expense  = (expenses ?? []).filter(e => e.expense_date?.startsWith(key)).reduce((s, e) => s + Number(e.amount), 0)
    return { key, label, revenue, expense, net: revenue - expense }
  })

  // Per-property P&L
  const propertyPnL = (properties ?? []).map((p: any) => {
    const revenue = (payments ?? []).filter(pm => pm.status === "paid" && pm.property_id === p.id).reduce((s, pm) => s + Number(pm.amount), 0)
    const expense = (expenses ?? []).filter(e => e.property_id === p.id).reduce((s, e) => s + Number(e.amount), 0)
    const net = revenue - expense
    const margin = revenue > 0 ? Math.round((net / revenue) * 100) : 0
    return { ...p, revenue, expense, net, margin }
  }).sort((a, b) => b.net - a.net)

  // Summary totals
  const totalRevenue  = (payments ?? []).filter(p => p.status === "paid").reduce((s, p) => s + Number(p.amount), 0)
  const totalExpenses = (expenses ?? []).reduce((s, e) => s + Number(e.amount), 0)
  const totalNet      = totalRevenue - totalExpenses
  const avgMargin     = totalRevenue > 0 ? Math.round((totalNet / totalRevenue) * 100) : 0

  return (
    <div className="space-y-6">
      {/* Header + CSV exports */}
      <div className="flex items-start justify-between gap-4">
        <div>
          <h2 className="text-lg font-semibold text-white">Reports</h2>
          <p className="text-sm text-white/40">Revenue, expenses &amp; profit — last 6 months</p>
        </div>
        <div className="flex flex-wrap items-center justify-end gap-2">
          <CsvExportButton endpoint="/api/export/rent" filename="rent-payments.csv" label="Rent CSV" />
          <CsvExportButton endpoint="/api/export/tenants" filename="tenants.csv" label="Tenants CSV" />
          <CsvExportButton endpoint="/api/expenses/export" filename="expenses.csv" label="Expenses CSV" />
        </div>
      </div>

      {/* Summary KPI cards */}
      <div className="grid grid-cols-2 sm:grid-cols-4 gap-3">
        {[
          { label: "Total Revenue", value: formatCurrency(totalRevenue), icon: DollarSign, color: "text-emerald-400", bg: "bg-emerald-500/10" },
          { label: "Total Expenses", value: formatCurrency(totalExpenses), icon: Receipt, color: "text-rose-400", bg: "bg-rose-500/10" },
          { label: "Net Income", value: formatCurrency(totalNet), icon: totalNet >= 0 ? TrendingUp : TrendingDown, color: totalNet >= 0 ? "text-emerald-400" : "text-red-400", bg: totalNet >= 0 ? "bg-emerald-500/10" : "bg-red-500/10" },
          { label: "Profit Margin", value: `${avgMargin}%`, icon: BarChart3, color: "text-indigo-400", bg: "bg-indigo-500/10" },
        ].map((s) => (
          <div key={s.label} className="rounded-xl border border-white/[0.06] bg-[#16161f] p-4">
            <div className="flex items-center gap-2 mb-2">
              <div className={`flex h-7 w-7 items-center justify-center rounded-lg ${s.bg}`}>
                <s.icon className={`h-3.5 w-3.5 ${s.color}`} />
              </div>
              <p className="text-xs text-white/35">{s.label}</p>
            </div>
            <p className={`text-xl font-bold tabular-nums ${s.color}`}>{s.value}</p>
            <p className="text-[10px] text-white/25 mt-0.5">Last 6 months</p>
          </div>
        ))}
      </div>

      {/* Client component for interactive charts */}
      <ReportsClient monthlyData={monthlyData} propertyPnL={propertyPnL} />
    </div>
  )
}