const express = require('express');
const { Op } = require('sequelize');

const db = require('../db/models');
const wrapAsync = require('../helpers').wrapAsync;

const router = express.Router();

const bookingPermissions = ['READ_BOOKING_REQUESTS'];
const approvalPermissions = ['READ_APPROVAL_STEPS'];
const reservationPermissions = ['READ_RESERVATIONS'];
const servicePermissions = ['READ_SERVICE_REQUESTS'];
const financePermissions = ['READ_INVOICES'];
const inventoryPermissions = ['READ_PROPERTIES', 'READ_UNITS'];
const accountPermissions = ['READ_ORGANIZATIONS'];

function getPermissionSet(currentUser) {
  return new Set([
    ...((currentUser?.custom_permissions || []).map((permission) => permission.name)),
    ...((currentUser?.app_role_permissions || []).map((permission) => permission.name)),
  ]);
}

function hasAnyPermission(currentUser, permissions) {
  if (currentUser?.app_role?.globalAccess) {
    return true;
  }

  const permissionSet = getPermissionSet(currentUser);
  return permissions.some((permission) => permissionSet.has(permission));
}

function scopedWhere(currentUser, key, extraWhere = {}) {
  if (currentUser?.app_role?.globalAccess || !currentUser?.organizationId) {
    return extraWhere;
  }

  return {
    ...extraWhere,
    [key]: currentUser.organizationId,
  };
}

function groupRowsToCounts(rows, field) {
  return rows.reduce((accumulator, row) => {
    accumulator[row[field]] = Number(row.count || 0);
    return accumulator;
  }, {});
}

function formatUserDisplay(user) {
  if (!user) {
    return null;
  }

  const fullName = [user.firstName, user.lastName].filter(Boolean).join(' ').trim();
  return fullName || user.email || 'Assigned staff';
}

async function getGroupedCounts(model, statusField, where) {
  const rows = await model.findAll({
    attributes: [
      statusField,
      [db.sequelize.fn('COUNT', db.sequelize.col('id')), 'count'],
    ],
    where,
    group: [statusField],
    raw: true,
  });

  return groupRowsToCounts(rows, statusField);
}

router.get(
  '/overview',
  wrapAsync(async (req, res) => {
    const { currentUser } = req;
    const now = new Date();
    const nextSevenDays = new Date(now);
    nextSevenDays.setDate(nextSevenDays.getDate() + 7);

    const access = {
      accounts: hasAnyPermission(currentUser, accountPermissions),
      bookings: hasAnyPermission(currentUser, bookingPermissions),
      approvals: hasAnyPermission(currentUser, approvalPermissions),
      reservations: hasAnyPermission(currentUser, reservationPermissions),
      serviceRequests: hasAnyPermission(currentUser, servicePermissions),
      invoices: hasAnyPermission(currentUser, financePermissions),
      inventory: hasAnyPermission(currentUser, inventoryPermissions),
    };

    const response = {
      generatedAt: now.toISOString(),
      access,
      organizations: {
        total: 0,
      },
      bookingRequests: {
        statusCounts: {},
        pendingReview: 0,
        approvedReady: 0,
        recent: [],
      },
      approvals: {
        pending: 0,
      },
      reservations: {
        statusCounts: {},
        upcomingArrivals: 0,
        upcomingDepartures: 0,
        inHouse: 0,
        recent: [],
      },
      serviceRequests: {
        statusCounts: {},
        open: 0,
        urgent: 0,
        recent: [],
      },
      invoices: {
        statusCounts: {},
        openBalance: 0,
        recent: [],
      },
      inventory: {
        activeProperties: 0,
        unitStatusCounts: {},
      },
    };

    if (access.accounts) {
      response.organizations.total = await db.organizations.count({
        where: currentUser?.app_role?.globalAccess
          ? {}
          : { id: currentUser.organizationId },
      });
    }

    if (access.bookings) {
      const bookingWhere = scopedWhere(currentUser, 'organizationId');
      response.bookingRequests.statusCounts = await getGroupedCounts(
        db.booking_requests,
        'status',
        bookingWhere,
      );
      response.bookingRequests.pendingReview = [
        'submitted',
        'in_review',
        'changes_requested',
      ].reduce(
        (sum, status) => sum + (response.bookingRequests.statusCounts[status] || 0),
        0,
      );
      response.bookingRequests.approvedReady = (
        response.bookingRequests.statusCounts.approved || 0
      );

      const recentBookingRequests = await db.booking_requests.findAll({
        where: bookingWhere,
        attributes: [
          'id',
          'request_code',
          'status',
          'check_in_at',
          'check_out_at',
          'guest_count',
          'preferred_bedrooms',
          'updatedAt',
        ],
        include: [
          {
            model: db.organizations,
            as: 'organization',
            attributes: ['id', 'name'],
            required: false,
          },
          {
            model: db.properties,
            as: 'preferred_property',
            attributes: ['id', 'name', 'city'],
            required: false,
          },
          {
            model: db.unit_types,
            as: 'preferred_unit_type',
            attributes: ['id', 'name'],
            required: false,
          },
          {
            model: db.users,
            as: 'requested_by',
            attributes: ['id', 'firstName', 'lastName', 'email'],
            required: false,
          },
        ],
        order: [['updatedAt', 'DESC']],
        limit: 5,
      });

      response.bookingRequests.recent = recentBookingRequests.map((item) => ({
        id: item.id,
        request_code: item.request_code,
        status: item.status,
        check_in_at: item.check_in_at,
        check_out_at: item.check_out_at,
        guest_count: item.guest_count,
        preferred_bedrooms: item.preferred_bedrooms,
        updatedAt: item.updatedAt,
        organizationName: item.organization?.name || 'Unassigned account',
        requestedBy: formatUserDisplay(item.requested_by),
        propertyName: item.preferred_property?.name || 'Open inventory',
        unitTypeName: item.preferred_unit_type?.name || 'Any unit type',
      }));
    }

    if (access.approvals) {
      response.approvals.pending = await db.approval_steps.count({
        where: scopedWhere(currentUser, 'organizationsId', { decision: 'pending' }),
      });
    }

    if (access.reservations) {
      const reservationWhere = scopedWhere(currentUser, 'organizationId');
      response.reservations.statusCounts = await getGroupedCounts(
        db.reservations,
        'status',
        reservationWhere,
      );
      response.reservations.upcomingArrivals = await db.reservations.count({
        where: scopedWhere(currentUser, 'organizationId', {
          status: { [Op.in]: ['confirmed', 'checked_in'] },
          check_in_at: { [Op.between]: [now, nextSevenDays] },
        }),
      });
      response.reservations.upcomingDepartures = await db.reservations.count({
        where: scopedWhere(currentUser, 'organizationId', {
          status: { [Op.in]: ['confirmed', 'checked_in'] },
          check_out_at: { [Op.between]: [now, nextSevenDays] },
        }),
      });
      response.reservations.inHouse = await db.reservations.count({
        where: scopedWhere(currentUser, 'organizationId', {
          status: 'checked_in',
        }),
      });

      const recentReservations = await db.reservations.findAll({
        where: reservationWhere,
        attributes: [
          'id',
          'reservation_code',
          'status',
          'check_in_at',
          'check_out_at',
          'guest_count',
          'currency',
          'nightly_rate',
          'updatedAt',
        ],
        include: [
          {
            model: db.organizations,
            as: 'organization',
            attributes: ['id', 'name'],
            required: false,
          },
          {
            model: db.properties,
            as: 'property',
            attributes: ['id', 'name', 'city'],
            required: false,
          },
          {
            model: db.units,
            as: 'unit',
            attributes: ['id', 'unit_number', 'status'],
            required: false,
          },
          {
            model: db.booking_requests,
            as: 'booking_request',
            attributes: ['id', 'request_code'],
            required: false,
          },
        ],
        order: [['check_in_at', 'ASC']],
        limit: 5,
      });

      response.reservations.recent = recentReservations.map((item) => ({
        id: item.id,
        reservation_code: item.reservation_code,
        status: item.status,
        check_in_at: item.check_in_at,
        check_out_at: item.check_out_at,
        guest_count: item.guest_count,
        updatedAt: item.updatedAt,
        organizationName: item.organization?.name || 'Unassigned account',
        propertyName: item.property?.name || 'Property pending',
        unitNumber: item.unit?.unit_number || 'TBD',
        sourceRequestCode: item.booking_request?.request_code || null,
      }));
    }

    if (access.serviceRequests) {
      const serviceWhere = scopedWhere(currentUser, 'organizationsId');
      response.serviceRequests.statusCounts = await getGroupedCounts(
        db.service_requests,
        'status',
        serviceWhere,
      );
      response.serviceRequests.open = Object.entries(response.serviceRequests.statusCounts)
        .filter(([status]) => !['completed', 'canceled'].includes(status))
        .reduce((sum, [, count]) => sum + Number(count || 0), 0);
      response.serviceRequests.urgent = await db.service_requests.count({
        where: scopedWhere(currentUser, 'organizationsId', {
          priority: 'urgent',
          status: { [Op.notIn]: ['completed', 'canceled'] },
        }),
      });

      const recentServiceRequests = await db.service_requests.findAll({
        where: serviceWhere,
        attributes: [
          'id',
          'request_type',
          'status',
          'priority',
          'summary',
          'due_at',
          'updatedAt',
        ],
        include: [
          {
            model: db.reservations,
            as: 'reservation',
            attributes: ['id', 'reservation_code'],
            required: false,
          },
          {
            model: db.users,
            as: 'assigned_to',
            attributes: ['id', 'firstName', 'lastName', 'email'],
            required: false,
          },
        ],
        order: [['updatedAt', 'DESC']],
        limit: 5,
      });

      response.serviceRequests.recent = recentServiceRequests.map((item) => ({
        id: item.id,
        request_type: item.request_type,
        status: item.status,
        priority: item.priority,
        summary: item.summary,
        due_at: item.due_at,
        updatedAt: item.updatedAt,
        reservationCode: item.reservation?.reservation_code || null,
        assignedTo: formatUserDisplay(item.assigned_to),
      }));
    }

    if (access.invoices) {
      const invoiceWhere = scopedWhere(currentUser, 'organizationId');
      response.invoices.statusCounts = await getGroupedCounts(
        db.invoices,
        'status',
        invoiceWhere,
      );
      const openBalance = await db.invoices.sum('balance_due', {
        where: scopedWhere(currentUser, 'organizationId', {
          status: { [Op.in]: ['issued', 'overdue', 'partially_paid'] },
        }),
      });
      response.invoices.openBalance = Number(openBalance || 0);

      const recentInvoices = await db.invoices.findAll({
        where: invoiceWhere,
        attributes: [
          'id',
          'invoice_number',
          'status',
          'total_amount',
          'balance_due',
          'currency',
          'due_at',
          'updatedAt',
        ],
        include: [
          {
            model: db.organizations,
            as: 'organization',
            attributes: ['id', 'name'],
            required: false,
          },
          {
            model: db.reservations,
            as: 'reservation',
            attributes: ['id', 'reservation_code'],
            required: false,
          },
        ],
        order: [['updatedAt', 'DESC']],
        limit: 5,
      });

      response.invoices.recent = recentInvoices.map((item) => ({
        id: item.id,
        invoice_number: item.invoice_number,
        status: item.status,
        total_amount: Number(item.total_amount || 0),
        balance_due: Number(item.balance_due || 0),
        currency: item.currency || 'USD',
        due_at: item.due_at,
        updatedAt: item.updatedAt,
        organizationName: item.organization?.name || 'Unassigned account',
        reservationCode: item.reservation?.reservation_code || null,
      }));
    }

    if (access.inventory) {
      response.inventory.activeProperties = await db.properties.count({
        where: scopedWhere(currentUser, 'organizationsId', { is_active: true }),
      });
      response.inventory.unitStatusCounts = await getGroupedCounts(
        db.units,
        'status',
        scopedWhere(currentUser, 'organizationsId'),
      );
    }

    res.status(200).send(response);
  }),
);

module.exports = router;
