import { Router } from "express";
import { z } from "zod";
import {
  Booking,
  Favorite,
  Notification,
  Property,
  PropertyUpdate,
  Review,
  City,
  User,
  ExchangeRate,
  ContactMessage,
  WithdrawalRequest,
} from "@/db/models";
import { AppError, asyncHandler } from "@/lib/errors";
import { requireAuth, requireRoles } from "@/middleware/auth";
import { serializeBooking, serializeProperty, toNumber, toPlain } from "@/lib/serialize";
import { getActiveExchangeRate } from "@/services/exchange";
import { notifyUser } from "@/services/notifications";
import { getOwnerAvailablePayoutTnd } from "@/services/withdrawals";
import { sendMail } from "@/services/email";
import { env } from "@/config/env";
import { withFinancialDualWrite, isFinancialDualWriteEnabled } from "@/db/dualWriteFinancial";
import {
  isAuthMysql,
  isBookingsMysql,
  isCitiesMysql,
  isContactMysql,
  isDashboardMysql,
  isFavoritesMysql,
  isNotificationsMysql,
  isPropertiesMysql,
  isReviewsMysql,
  isWithdrawalsMysql,
} from "@/db/activeDatabase";
import { createId } from "@/db/ids";
import { upsertWithdrawalMysql } from "@/db/mysql/financialWrites";
import { listFavoritesMysql } from "@/db/mysql/favorites";
import { listNotificationsMysql, notificationToApi } from "@/db/mysql/notifications";
import {
  findPropertiesByIdsMysql,
  listPropertyUpdatesMysql,
  propertyToApi,
  propertyUpdateToApi,
} from "@/db/mysql/properties";
import {
  listReviewsByPropertyIdsMysql,
  reviewStatsByPropertyIdsMysql,
  reviewToApi,
} from "@/db/mysql/reviews";
import { cityRowToApi, findCitiesByIdsMysql } from "@/db/mysql/cities";
import { findUsersByIdsMysql, countUsersMysql } from "@/db/mysql/users";
import { listContactMysql, contactToApi } from "@/db/mysql/contact";
import { bookingToApi, listBookingsMysql } from "@/db/mysql/bookings";
import { sqlQuery } from "@/db/mysql/pool";
import type { RowDataPacket } from "mysql2/promise";

export const dashboardRouter = Router();

function withdrawalsMysqlPrimary() {
  return isWithdrawalsMysql() && !isFinancialDualWriteEnabled();
}

async function loadPropertiesForIds(ids: string[]) {
  if (isPropertiesMysql()) {
    const rows = await findPropertiesByIdsMysql(ids);
    const cityIds = [...new Set(rows.map((p) => p.cityId))];
    const cities = isCitiesMysql()
      ? await findCitiesByIdsMysql(cityIds)
      : await City.find({ _id: { $in: cityIds } }).lean();
    const cityMap = new Map(
      isCitiesMysql()
        ? (cities as any[]).map((c) => [c.id, cityRowToApi(c)])
        : (cities as any[]).map((c) => [c._id, c]),
    );
    return new Map(
      rows.map((p) => {
        const api = propertyToApi(p);
        return [
          p.id,
          {
            ...api,
            city: cityMap.get(p.cityId),
            images: (api.images || []).slice(0, 1),
          },
        ];
      }),
    );
  }
  const properties = await Property.find({ _id: { $in: ids } }).lean();
  const cityIds = [...new Set(properties.map((p) => p.cityId))];
  const cities = await City.find({ _id: { $in: cityIds } }).lean();
  const cityMap = new Map(cities.map((c) => [c._id, c]));
  return new Map(
    properties.map((p) => [
      p._id,
      {
        ...p,
        city: cityMap.get(p.cityId),
        images: [...(p.images || [])]
          .sort((a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0))
          .slice(0, 1),
      },
    ]),
  );
}

dashboardRouter.get(
  "/customer",
  requireAuth,
  requireRoles("CUSTOMER", "ADMIN"),
  asyncHandler(async (req, res) => {
    const userId = req.user!.id;
    const hybrid = isDashboardMysql();

    const bookings = isBookingsMysql()
      ? (await listBookingsMysql({ customerId: userId, take: 20 })).map(bookingToApi)
      : await Booking.find({ customerId: userId, deletedAt: null })
          .sort({ createdAt: -1 })
          .limit(20)
          .lean();

    let favorites: { id: string; propertyId: string }[] = [];
    let notifications: any[] = [];

    if (hybrid && isFavoritesMysql()) {
      favorites = (await listFavoritesMysql(userId)).slice(0, 12).map((f) => ({
        id: f.id,
        propertyId: f.propertyId,
      }));
    } else {
      favorites = (await Favorite.find({ userId }).limit(12).lean()).map((f) => ({
        id: String(f._id),
        propertyId: f.propertyId,
      }));
    }

    if (hybrid && isNotificationsMysql()) {
      notifications = (await listNotificationsMysql(userId, 20)).notifications.map(
        notificationToApi,
      );
    } else {
      notifications = (
        await Notification.find({ userId }).sort({ createdAt: -1 }).limit(20).lean()
      ).map(toPlain);
    }

    const propertyIds = [
      ...new Set([...bookings.map((b) => b.propertyId), ...favorites.map((f) => f.propertyId)]),
    ];
    const propertyMap = await loadPropertiesForIds(propertyIds);

    let updates: any[] = [];
    if (propertyIds.length) {
      if (hybrid && isPropertiesMysql()) {
        const all = await Promise.all(
          propertyIds.slice(0, 20).map((id) => listPropertyUpdatesMysql(id, 5)),
        );
        updates = all
          .flat()
          .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime())
          .slice(0, 20)
          .map(propertyUpdateToApi);
      } else {
        updates = await PropertyUpdate.find({
          propertyId: { $in: propertyIds },
          deletedAt: null,
        })
          .sort({ createdAt: -1 })
          .limit(20)
          .lean();
      }
    }

    const favPropertyIds = favorites.map((f) => f.propertyId);
    let reviewStatsMap = new Map<string, { count: number; averageRating: number }>();
    if (favPropertyIds.length) {
      if (hybrid && isReviewsMysql()) {
        const stats = await reviewStatsByPropertyIdsMysql(favPropertyIds);
        reviewStatsMap = new Map(stats.map((s) => [s.propertyId, s]));
      } else {
        const reviewStats = await Review.aggregate([
          { $match: { propertyId: { $in: favPropertyIds } } },
          {
            $group: {
              _id: "$propertyId",
              count: { $sum: 1 },
              averageRating: { $avg: "$rating" },
            },
          },
        ]);
        reviewStatsMap = new Map(
          reviewStats.map((r: any) => [
            r._id,
            { count: r.count, averageRating: r.averageRating },
          ]),
        );
      }
    }

    res.json({
      bookings: bookings.map((b) =>
        serializeBooking({ ...b, property: propertyMap.get(b.propertyId) }),
      ),
      favorites: favorites.map((f) => {
        const stats = reviewStatsMap.get(f.propertyId);
        return {
          id: f.id,
          property: {
            ...serializeProperty(propertyMap.get(f.propertyId)),
            _count: { reviews: stats?.count ?? 0 },
            averageRating: stats?.averageRating
              ? Number(Number(stats.averageRating).toFixed(1))
              : 0,
          },
        };
      }),
      notifications,
      updates: updates.map((u) => ({
        ...(hybrid && isPropertiesMysql() ? u : toPlain(u)),
        property: serializeProperty(
          propertyMap.get((u as any).propertyId || (u as any).property_id),
        ),
      })),
      _db: hybrid ? "mysql-hybrid" : "mongodb",
    });
  }),
);

dashboardRouter.get(
  "/owner",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    const ownerId = req.user!.id;
    const hybrid = isDashboardMysql();

    let properties: any[] = [];
    if (hybrid && isPropertiesMysql()) {
      // All statuses for owner dashboard (listPropertiesMysql defaults to public filters).
      const rows = await sqlQuery<RowDataPacket[]>(
        `SELECT id FROM properties WHERE owner_id = ? AND deleted_at IS NULL ORDER BY created_at DESC`,
        [ownerId],
      );
      properties = await findPropertiesByIdsMysql(rows.map((r) => String(r.id)));
      properties = properties.map((p) => propertyToApi(p));
    } else {
      properties = await Property.find({ ownerId, deletedAt: null }).sort({ createdAt: -1 }).lean();
    }

    const bookings = isBookingsMysql()
      ? (await listBookingsMysql({ ownerId, take: 30 })).map(bookingToApi)
      : await Booking.find({ ownerId, deletedAt: null })
          .sort({ createdAt: -1 })
          .limit(30)
          .lean();

    const cityIds = Array.from(new Set<string>(properties.map((p: any) => String(p.cityId))));
    const customerIds = Array.from(
      new Set<string>((bookings as any[]).map((b) => String(b.customerId))),
    );

    let cityMap = new Map<string, any>();
    if (hybrid && isCitiesMysql()) {
      const cities = await findCitiesByIdsMysql(cityIds);
      cityMap = new Map(cities.map((c) => [c.id, cityRowToApi(c)]));
    } else {
      const cities = await City.find({ _id: { $in: cityIds } }).lean();
      cityMap = new Map(cities.map((c) => [c._id, c]));
    }

    let customerMap = new Map<string, any>();
    if (hybrid && isAuthMysql()) {
      const customers = await findUsersByIdsMysql(customerIds);
      customerMap = new Map(
        customers.map((c) => [c.id, { id: c.id, fullName: c.fullName, email: c.email }]),
      );
    } else {
      const customers = await User.find({ _id: { $in: customerIds } })
        .select("fullName email")
        .lean();
      customerMap = new Map(
        customers.map((c) => [c._id, { id: c._id, fullName: c.fullName, email: c.email }]),
      );
    }

    const propertyMap = new Map(properties.map((p) => [String(p.id || p._id), p]));

    const paidBookings = bookings.filter((b) => ["CONFIRMED", "COMPLETED"].includes(b.status));
    const revenueTnd = paidBookings.reduce((s, b) => s + toNumber(b.ownerPayoutTnd), 0);
    const availablePayoutTnd = await getOwnerAvailablePayoutTnd(ownerId);

    let exchangeRateRate = 0;
    try {
      const exchange = await getActiveExchangeRate("TND", "LYD");
      exchangeRateRate = toNumber(exchange.rate);
    } catch {
      exchangeRateRate = 0;
    }

    const now = new Date();
    const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
    const monthEnd = new Date(now.getFullYear(), now.getMonth() + 1, 1);
    const daysInMonth = new Date(now.getFullYear(), now.getMonth() + 1, 0).getDate();

    const propertyPerformance = properties.map((p) => {
      const pid = String(p.id || p._id);
      const monthBookings = bookings.filter(
        (b) =>
          String(b.propertyId) === pid &&
          ["CONFIRMED", "COMPLETED", "WAITING_OWNER"].includes(b.status) &&
          b.checkIn &&
          new Date(b.checkIn) >= monthStart &&
          new Date(b.checkIn) < monthEnd,
      );
      const nightsBooked = monthBookings.reduce((s, b) => s + (Number(b.nights) || 0), 0);
      const occupancyPercent = Math.min(
        100,
        Math.round((nightsBooked / Math.max(daysInMonth, 1)) * 100),
      );
      return {
        propertyId: pid,
        titleAr: p.titleAr,
        titleEn: p.titleEn,
        bookingsThisMonth: monthBookings.length,
        nightsBooked,
        occupancyPercent,
      };
    });

    const propertyIds = properties.map((p) => String(p.id || p._id));

    let updates: any[] = [];
    let reviews: any[] = [];
    if (propertyIds.length) {
      if (hybrid && isPropertiesMysql()) {
        const all = await Promise.all(propertyIds.map((id) => listPropertyUpdatesMysql(id, 10)));
        updates = all
          .flat()
          .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime())
          .slice(0, 30)
          .map(propertyUpdateToApi);
      } else {
        updates = await PropertyUpdate.find({
          propertyId: { $in: propertyIds },
          deletedAt: null,
        })
          .sort({ createdAt: -1 })
          .limit(30)
          .lean();
      }

      if (hybrid && isReviewsMysql()) {
        reviews = (await listReviewsByPropertyIdsMysql(propertyIds))
          .slice(0, 40)
          .map(reviewToApi);
      } else {
        reviews = await Review.find({ propertyId: { $in: propertyIds } })
          .sort({ createdAt: -1 })
          .limit(40)
          .lean();
      }
    }

    const reviewAuthorIds = [
      ...new Set(reviews.map((r) => (r as any).authorId).filter(Boolean)),
    ];
    let reviewAuthorMap = new Map<string, string>();
    if (reviewAuthorIds.length) {
      if (hybrid && isAuthMysql()) {
        const authors = await findUsersByIdsMysql(reviewAuthorIds);
        reviewAuthorMap = new Map(authors.map((a) => [a.id, a.fullName]));
      } else {
        const authors = await User.find({ _id: { $in: reviewAuthorIds } })
          .select("fullName")
          .lean();
        reviewAuthorMap = new Map(authors.map((a) => [a._id, a.fullName]));
      }
    }

    let reviewStatsMap = new Map<string, any>();
    if (propertyIds.length) {
      if (hybrid && isReviewsMysql()) {
        const stats = await reviewStatsByPropertyIdsMysql(propertyIds);
        reviewStatsMap = new Map(stats.map((s) => [s.propertyId, s]));
      } else {
        const reviewStats = await Review.aggregate([
          { $match: { propertyId: { $in: propertyIds } } },
          {
            $group: {
              _id: "$propertyId",
              count: { $sum: 1 },
              averageRating: { $avg: "$rating" },
            },
          },
        ]);
        reviewStatsMap = new Map(reviewStats.map((r: any) => [r._id, r]));
      }
    }

    res.json({
      properties: properties.map((p) => {
        const pid = String(p.id || p._id);
        const stats = reviewStatsMap.get(pid);
        return serializeProperty({
          ...p,
          city: cityMap.get(p.cityId),
          images: [...(p.images || [])]
            .sort((a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0))
            .slice(0, 1),
          averageRating: stats?.averageRating ? Number(Number(stats.averageRating).toFixed(1)) : 0,
          reviewCount: stats?.count ?? 0,
        });
      }),
      bookings: bookings.map((b) =>
        serializeBooking({
          ...b,
          property: propertyMap.get(b.propertyId),
          customer: customerMap.get(b.customerId),
        }),
      ),
      updates: updates.map((u) => ({
        ...(hybrid && isPropertiesMysql() ? u : toPlain(u)),
        property: serializeProperty(propertyMap.get((u as any).propertyId)),
      })),
      reviews: reviews.map((r) => ({
        ...(hybrid && isReviewsMysql() ? r : toPlain(r)),
        authorName: reviewAuthorMap.get((r as any).authorId),
        property: serializeProperty(propertyMap.get((r as any).propertyId)),
      })),
      revenueTnd,
      availablePayoutTnd,
      exchangeRateRate,
      propertyPerformance,
      pendingRequests: bookings.filter((b) => b.status === "WAITING_OWNER").length,
      publishedCount: properties.filter((p) => p.status === "PUBLISHED").length,
      _db: hybrid ? "mysql-hybrid" : "mongodb",
    });
  }),
);

dashboardRouter.post(
  "/owner/withdraw",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    const body = z
      .object({
        amountTnd: z.number().positive(),
        method: z.enum(["BANK_TND", "BANK_LYD"]),
        note: z.string().max(300).optional(),
      })
      .parse(req.body);

    const ownerId = req.user!.id;
    const available = await getOwnerAvailablePayoutTnd(ownerId);
    if (body.amountTnd > available + 0.001) {
      throw new AppError(400, "Amount exceeds available balance");
    }

    let exchangeRateRate = 0;
    try {
      const exchange = await getActiveExchangeRate("TND", "LYD");
      exchangeRateRate = toNumber(exchange.rate);
    } catch {
      exchangeRateRate = 0;
    }
    const amountLyd =
      exchangeRateRate > 0
        ? Math.round(body.amountTnd * exchangeRateRate * 100) / 100
        : Math.round(body.amountTnd * 100) / 100;

    const owner = isAuthMysql()
      ? await findUsersByIdsMysql([ownerId]).then((rows) => rows[0])
      : await User.findById(ownerId).select("fullName email").lean();

    let requestId: string;
    let dbTag: "mysql" | "mongodb";

    if (withdrawalsMysqlPrimary()) {
      requestId = createId();
      const now = new Date();
      await upsertWithdrawalMysql({
        id: requestId,
        ownerId,
        amountTnd: body.amountTnd,
        amountLyd,
        exchangeRateRate,
        method: body.method,
        status: "PENDING",
        note: body.note || null,
        createdAt: now,
        updatedAt: now,
      });
      dbTag = "mysql";
    } else {
      const request = await withFinancialDualWrite({
        site: "withdrawal.create",
        mongoWrite: async () =>
          WithdrawalRequest.create({
            ownerId,
            amountTnd: body.amountTnd,
            amountLyd,
            exchangeRateRate,
            method: body.method,
            status: "PENDING",
            note: body.note || undefined,
          }),
        mysqlWrite: async (doc) => {
          await upsertWithdrawalMysql({
            id: String(doc._id),
            ownerId,
            amountTnd: body.amountTnd,
            amountLyd,
            exchangeRateRate,
            method: body.method,
            status: "PENDING",
            note: body.note || null,
            createdAt: (doc as any).createdAt,
            updatedAt: (doc as any).updatedAt,
          });
        },
        mongoCompensate: async (doc) => {
          await WithdrawalRequest.deleteOne({ _id: doc._id });
        },
      });
      requestId = String((request as any)._id);
      dbTag = "mongodb";
    }

    const admins = isAuthMysql()
      ? (
          await sqlQuery<RowDataPacket[]>(
            `SELECT id, email FROM users
             WHERE deleted_at IS NULL AND role IN ('ADMIN','SUPER_ADMIN','FINANCE_ADMIN','OPERATIONS_ADMIN')`,
          )
        ).map((r) => ({ _id: String(r.id), email: String(r.email) }))
      : await User.find({ role: "ADMIN", deletedAt: null }).select("_id email").lean();

    await Promise.all(
      admins.map((admin) =>
        notifyUser({
          userId: String((admin as any)._id || (admin as any).id),
          titleAr: "طلب سحب أرباح من مضيف",
          titleEn: "Owner payout request",
          messageAr: `طلب سحب ${amountLyd} د.ل (${body.amountTnd} د.ت) من ${(owner as any)?.fullName || "مضيف"}`,
          messageEn: `Payout request ${amountLyd} LYD (${body.amountTnd} TND) from ${(owner as any)?.fullName || "owner"}`,
          link: "/admin/withdrawals",
        }),
      ),
    );

    await notifyUser({
      userId: ownerId,
      titleAr: "تم استلام طلب السحب",
      titleEn: "Payout request received",
      messageAr: `طلبك بسحب ${amountLyd} د.ل قيد المراجعة.`,
      messageEn: `Your request to withdraw ${amountLyd} LYD is under review.`,
      link: "/dashboard/owner#earnings",
    });

    const adminInbox = "admin@safarlibya.com";
    const withdrawUrl = `${env.FRONTEND_URL}/admin/withdrawals`;
    void sendMail({
      to: adminInbox,
      subject: "طلب سحب أرباح جديد",
      text: [
        "طلب سحب أرباح جديد",
        `المضيف: ${(owner as any)?.fullName || ownerId}`,
        `البريد: ${(owner as any)?.email || "—"}`,
        `المبلغ: ${amountLyd} LYD (${body.amountTnd} TND)`,
        `الرابط: ${withdrawUrl}`,
      ].join("\n"),
      html: `<p><strong>طلب سحب أرباح جديد</strong></p>
<p>المضيف: ${(owner as any)?.fullName || ownerId}<br/>البريد: ${(owner as any)?.email || "—"}<br/>
المبلغ: <strong>${amountLyd} LYD</strong> (${body.amountTnd} TND)</p>
<p><a href="${withdrawUrl}">فتح طلبات السحب</a></p>`,
    }).catch(() => undefined);

    res.json({
      ok: true,
      request: {
        id: requestId,
        amountTnd: body.amountTnd,
        amountLyd,
        method: body.method,
        exchangeRateRate,
        status: "PENDING",
      },
      _db: dbTag,
    });
  }),
);

dashboardRouter.get(
  "/admin",
  requireAuth,
  requireRoles("ADMIN"),
  asyncHandler(async (_req, res) => {
    const hybrid = isDashboardMysql();

    let users: number;
    let properties: number;
    if (hybrid && isAuthMysql()) {
      users = await countUsersMysql();
    } else {
      users = await User.countDocuments({ deletedAt: null });
    }
    if (hybrid && isPropertiesMysql()) {
      const rows = await sqlQuery<RowDataPacket[]>(
        `SELECT COUNT(*) AS c FROM properties WHERE deleted_at IS NULL`,
      );
      properties = Number(rows[0]?.c || 0);
    } else {
      properties = await Property.countDocuments({ deletedAt: null });
    }

    const bookings = hybrid && isBookingsMysql()
      ? await (async () => {
          const { countBookingsMysql, listBookingsMysql } = await import("@/db/mysql/bookings");
          return countBookingsMysql();
        })()
      : await Booking.countDocuments({ deletedAt: null });

    let payments: any[] = [];
    if (hybrid && isBookingsMysql()) {
      const { listBookingsMysql, bookingToApi } = await import("@/db/mysql/bookings");
      const paid = await listBookingsMysql({ take: 30, skip: 0 });
      payments = paid
        .filter((b) => b.payment && (b.payment as any).status)
        .slice(0, 10)
        .map((b) => {
          const payment = b.payment as any;
          return { ...payment, id: payment._id || payment.id, amount: toNumber(payment.amount) };
        });
    } else {
      const paidBookings = await Booking.find({ payment: { $exists: true, $ne: null } })
        .sort({ createdAt: -1 })
        .limit(10)
        .lean();
      payments = paidBookings
        .filter((b) => b.payment)
        .map((b) => ({ ...toPlain(b.payment), amount: toNumber(b.payment!.amount) }));
    }

    let exchangeRates: any[] = [];
    if (hybrid) {
      const { sqlQuery } = await import("@/db/mysql/pool");
      const rows = await sqlQuery<RowDataPacket[]>(
        `SELECT * FROM exchange_rates WHERE from_currency='TND' AND to_currency='LYD'
         ORDER BY valid_from DESC LIMIT 5`,
      );
      exchangeRates = rows.map((r) => ({
        id: String(r.id),
        fromCurrency: String(r.from_currency),
        toCurrency: String(r.to_currency),
        rate: toNumber(r.rate),
        validFrom: r.valid_from,
        validTo: r.valid_to,
      }));
    } else {
      exchangeRates = (
        await ExchangeRate.find({ fromCurrency: "TND", toCurrency: "LYD" })
          .sort({ validFrom: -1 })
          .limit(5)
          .lean()
      ).map((r) => ({ ...toPlain(r), rate: toNumber(r.rate) }));
    }

    let contactMessages: any[];
    if (hybrid && isContactMysql()) {
      contactMessages = (await listContactMysql(10)).map(contactToApi);
    } else {
      contactMessages = (await ContactMessage.find().sort({ createdAt: -1 }).limit(10).lean()).map(
        toPlain,
      );
    }

    res.json({
      counts: { users, properties, bookings },
      payments,
      exchangeRates,
      contactMessages,
      _db: hybrid ? "mysql-hybrid" : "mongodb",
    });
  }),
);