import { HttpStatus, Injectable } from '@nestjs/common';
import { DatabaseService, logger } from '../../config';
import {
  MemberStatusEnum,
  OffboardingCaseStatusEnum,
  OffboardingReasonEnum,
  SettlementStatusEnum,
  TaskOwnerTypeEnum,
  TaskStatusEnum,
} from '../../config/database/entities';
import { createGeneralError } from '../../utils';
import { NotificationsService } from '../notifications/notifications.service';
import {
  addEquipmentReturnDto,
  calculateFinalSettlementDto,
  createExitInterviewTemplateDto,
  createOffboardingCaseDto,
  createOffboardingTaskDto,
  createOffboardingTemplateDto,
  filterOffboardingCaseDto,
  submitExitInterviewDto,
  updateEquipmentReturnDto,
  updateOffboardingCaseDto,
  updateOffboardingTaskDto,
} from './offboarding.dto';

@Injectable()
export class OffboardingService {
  constructor(
    private readonly db: DatabaseService,
    private readonly notificationsService: NotificationsService,
  ) {}

  // -------------------------------------------------------------
  // TEMPLATES
  // -------------------------------------------------------------

  async createTemplate(data: createOffboardingTemplateDto, ownerId: string) {
    try {
      const template = await this.db.offboarding_templates.create({
        data: {
          title: data.title,
          description: data.description,
          is_active: data.is_active ?? true,
          owner_id: ownerId,
        },
      });

      if (data.tasks && data.tasks.length > 0) {
        for (let i = 0; i < data.tasks.length; i++) {
          const t = data.tasks[i];
          await this.db.offboarding_template_tasks.create({
            data: {
              template_id: template.id,
              title: t.title,
              description: t.description,
              owner_type: t.owner_type,
              priority: t.priority,
              relative_due_days: t.relative_due_days,
              order_index: t.order_index ?? i,
            },
          });
        }
      }

      return this.getTemplateById(template.id, ownerId);
    } catch (error) {
      logger.error('Failed to create offboarding template', error);
      throw error;
    }
  }

  async seedDefaultTemplates(ownerId: string) {
    try {
      if (!ownerId) return;
      const existingCount = await this.db.offboarding_templates.count({
        where: { owner_id: ownerId },
      });
      if (existingCount > 0) return;

      const templatesToSeed = [
        {
          title: 'Standard Offboarding',
          description: 'Standard company offboarding checklist for departing employees.',
          is_active: true,
          tasks: [
            {
              title: 'Revoke IT Systems & Email Credentials Access',
              description: 'Deactivate SSO, Google Workspace, Slack, and internal tools',
              owner_type: TaskOwnerTypeEnum.it,
              priority: 'critical',
              relative_due_days: 0,
            },
            {
              title: 'Collect Laptop, Monitor & Company Property',
              description: 'Return hardware equipment and security badges',
              owner_type: TaskOwnerTypeEnum.it,
              priority: 'high',
              relative_due_days: 1,
            },
            {
              title: 'Complete Exit Interview Survey & Feedback Session',
              description: 'Schedule exit interview and record feedback',
              owner_type: TaskOwnerTypeEnum.hr,
              priority: 'medium',
              relative_due_days: 3,
            },
            {
              title: 'Knowledge Transfer & Handover Documentation',
              description: 'Handover ongoing projects and document key operational processes',
              owner_type: TaskOwnerTypeEnum.employee,
              priority: 'high',
              relative_due_days: 5,
            },
            {
              title: 'Final Settlement & Payroll Calculation',
              description: 'Calculate pending salary, leave encashment, and release final pay',
              owner_type: TaskOwnerTypeEnum.finance,
              priority: 'high',
              relative_due_days: 7,
            },
          ],
        },
        {
          title: 'Executive Offboarding',
          description: 'Offboarding workflow for Senior Executives, Directors, and Department Heads.',
          is_active: true,
          tasks: [
            {
              title: 'Board & Leadership Team Notification',
              description: 'Notify executive team and organize transition plan',
              owner_type: TaskOwnerTypeEnum.hr,
              priority: 'high',
              relative_due_days: 1,
            },
            {
              title: 'Legal NDA & Non-Compete Agreement Review',
              description: 'Review confidentiality obligations and post-employment covenants',
              owner_type: TaskOwnerTypeEnum.hr,
              priority: 'high',
              relative_due_days: 2,
            },
            {
              title: 'Executive Corporate Card & Banking Access Revocation',
              description: 'Cancel corporate credit cards and revoke banking signatory rights',
              owner_type: TaskOwnerTypeEnum.finance,
              priority: 'critical',
              relative_due_days: 1,
            },
            {
              title: 'Strategic Handover Briefing to Successor',
              description: 'Conduct strategic handoff sessions and transfer key stakeholder relationships',
              owner_type: TaskOwnerTypeEnum.manager,
              priority: 'high',
              relative_due_days: 5,
            },
          ],
        },
      ];

      for (const tplData of templatesToSeed) {
        const { tasks, ...templateFields } = tplData;
        const template = await this.db.offboarding_templates.create({
          data: {
            ...templateFields,
            owner_id: ownerId,
          },
        });

        if (tasks && tasks.length > 0) {
          for (let i = 0; i < tasks.length; i++) {
            const t = tasks[i];
            await this.db.offboarding_template_tasks.create({
              data: {
                template_id: template.id,
                title: t.title,
                description: t.description,
                owner_type: t.owner_type,
                priority: t.priority as any,
                relative_due_days: t.relative_due_days,
                order_index: i,
              },
            });
          }
        }
      }
    } catch (error) {
      logger.error('Failed to seed default offboarding templates', error);
    }
  }

  async getTemplates(ownerId: string) {
    await this.seedDefaultTemplates(ownerId);
    return await this.db.offboarding_templates.findMany({
      where: { owner_id: ownerId },
      include: { tasks: true },
      orderBy: { created_at: 'desc' },
    });
  }

  async getTemplateById(id: string, ownerId: string) {
    const template = await this.db.offboarding_templates.findFirst({
      where: { id, owner_id: ownerId },
      include: { tasks: true },
    });

    if (!template) {
      throw createGeneralError('Offboarding template not found', 'general', HttpStatus.NOT_FOUND);
    }
    return template;
  }

  // -------------------------------------------------------------
  // OFFBOARDING CASES
  // -------------------------------------------------------------

  async createCase(data: createOffboardingCaseDto, ownerId: string, initiatedById: string) {
    try {
      const member = await this.db.members.findFirst({
        where: { id: data.member_id, owner_id: ownerId },
      });
      if (!member) {
        throw createGeneralError('Member not found', 'general', HttpStatus.NOT_FOUND);
      }

      const existingCase = await this.db.offboarding_cases.findFirst({
        where: {
          member_id: data.member_id,
          status: { not: OffboardingCaseStatusEnum.cancelled },
        },
      });
      if (existingCase && existingCase.status !== OffboardingCaseStatusEnum.completed) {
        throw createGeneralError(
          'Active offboarding case already exists for this member',
          'general', HttpStatus.CONFLICT,
        );
      }

      const lastWorkingDate = data.last_working_date
        ? new Date(data.last_working_date)
        : new Date(Date.now() + 30 * 24 * 60 * 60 * 1000);

      const offboardingCase = await this.db.runInTransaction(async (manager) => {
        const caseRepo = manager.getRepository('offboarding_cases');
        const taskRepo = manager.getRepository('offboarding_tasks');
        const eqRepo = manager.getRepository('equipment_returns');
        const exitRepo = manager.getRepository('exit_interviews');
        const settlementRepo = manager.getRepository('final_settlements');

        const newCase = caseRepo.create({
          member_id: data.member_id,
          template_id: data.template_id || null,
          initiated_by_id: initiatedById,
          reason: data.reason || OffboardingReasonEnum.resignation,
          last_working_date: lastWorkingDate,
          notice_period_days: data.notice_period_days ?? 30,
          status: OffboardingCaseStatusEnum.initiated,
          notes: data.notes || null,
          owner_id: ownerId,
        });

        const savedCase = await caseRepo.save(newCase);
        const caseId = (savedCase as any).id;

        // Populate tasks from template or defaults
        let tasksToInsert: any[] = [];
        if (data.template_id) {
          const tpl = await this.db.offboarding_templates.findFirst({
            where: { id: data.template_id, owner_id: ownerId },
            include: { tasks: true },
          });
          if (tpl && tpl.tasks && tpl.tasks.length > 0) {
            tasksToInsert = tpl.tasks.map((t: any, idx: number) => ({
              offboarding_case_id: caseId,
              title: t.title,
              description: t.description,
              owner_type: t.owner_type,
              due_date: new Date(lastWorkingDate.getTime() - (t.relative_due_days || 3) * 24 * 60 * 60 * 1000),
              priority: t.priority,
              status: TaskStatusEnum.pending,
              order_index: t.order_index ?? idx,
            }));
          }
        }

        if (tasksToInsert.length === 0) {
          tasksToInsert = [
            {
              offboarding_case_id: caseId,
              title: 'Knowledge Transfer & Handover Documentation',
              description: 'Handover active projects and responsibilities to designated team member',
              owner_type: TaskOwnerTypeEnum.employee,
              due_date: new Date(lastWorkingDate.getTime() - 7 * 24 * 60 * 60 * 1000),
              priority: 'high',
              status: TaskStatusEnum.pending,
              order_index: 0,
            },
            {
              offboarding_case_id: caseId,
              title: 'Equipment & Asset Return',
              description: 'Return company laptop, monitor, peripherals, and building access card',
              owner_type: TaskOwnerTypeEnum.it,
              due_date: lastWorkingDate,
              priority: 'high',
              status: TaskStatusEnum.pending,
              order_index: 1,
            },
            {
              offboarding_case_id: caseId,
              title: 'Exit Interview Completion',
              description: 'Complete confidential exit feedback questionnaire',
              owner_type: TaskOwnerTypeEnum.hr,
              due_date: new Date(lastWorkingDate.getTime() - 3 * 24 * 60 * 60 * 1000),
              priority: 'medium',
              status: TaskStatusEnum.pending,
              order_index: 2,
            },
            {
              offboarding_case_id: caseId,
              title: 'IT & User Access Deactivation',
              description: 'Revoke company email, VPN, SSO, and internal portal authorization',
              owner_type: TaskOwnerTypeEnum.it,
              due_date: lastWorkingDate,
              priority: 'critical',
              status: TaskStatusEnum.pending,
              order_index: 3,
            },
            {
              offboarding_case_id: caseId,
              title: 'Final Financial Settlement Approval',
              description: 'Calculate and approve final salary, leave encashment, and severance',
              owner_type: TaskOwnerTypeEnum.finance,
              due_date: new Date(lastWorkingDate.getTime() + 3 * 24 * 60 * 60 * 1000),
              priority: 'high',
              status: TaskStatusEnum.pending,
              order_index: 4,
            },
          ];
        }

        await taskRepo.save(taskRepo.create(tasksToInsert));

        // Default Equipment Returns
        const eqToInsert = [
          {
            offboarding_case_id: caseId,
            item_name: 'Company Laptop / Workstation',
            item_type: 'hardware',
            condition: 'good',
          },
          {
            offboarding_case_id: caseId,
            item_name: 'Building ID Badge & Keycard',
            item_type: 'access',
            condition: 'good',
          },
        ];
        await eqRepo.save(eqRepo.create(eqToInsert));

        // Default Exit Interview record
        await exitRepo.save(
          exitRepo.create({
            offboarding_case_id: caseId,
            member_id: data.member_id,
            status: 'pending',
          }),
        );

        // Default Final Settlement record (draft)
        await settlementRepo.save(
          settlementRepo.create({
            offboarding_case_id: caseId,
            member_id: data.member_id,
            status: SettlementStatusEnum.draft,
            owner_id: ownerId,
          }),
        );

        return savedCase;
      });

      // Send notification
      try {
        await this.notificationsService.sendNotification({
          owner_id: ownerId,
          title: 'Offboarding Initiated',
          message: `Offboarding case initiated for ${member.first_name} ${member.last_name}. Last working day: ${lastWorkingDate.toLocaleDateString()}`,
          type: 'RECRUITMENT' as any,
          include_admins: true,
          reference_id: (offboardingCase as any).id,
        });
      } catch (err) {
        logger.warn('Failed to send offboarding notification', err);
      }

      return this.getCaseById((offboardingCase as any).id, ownerId);
    } catch (error) {
      logger.error('Failed to create offboarding case', error);
      throw error;
    }
  }

  async getCasesByFilter(query: filterOffboardingCaseDto, ownerId: string) {
    try {
      const page = query.page || 1;
      const limit = query.limit || 10;
      const skip = (page - 1) * limit;

      const where: any = { owner_id: ownerId };
      if (query.status) where.status = query.status;
      if (query.member_id) where.member_id = query.member_id;

      const total = await this.db.offboarding_cases.count({ where });
      const items = await this.db.offboarding_cases.findMany({
        where,
        include: {
          member: { include: { department: true, designation: true } },
          tasks: true,
          equipment_returns: true,
          exit_interview: true,
          final_settlement: true,
        },
        orderBy: { created_at: 'desc' },
        skip,
        take: limit,
      });

      return {
        data: items,
        pagination: {
          total,
          page,
          limit,
          totalPages: Math.ceil(total / limit),
        },
      };
    } catch (error) {
      logger.error('Failed to fetch offboarding cases', error);
      throw error;
    }
  }

  async getCaseById(id: string, ownerId: string) {
    const offboardingCase = await this.db.offboarding_cases.findFirst({
      where: { id, owner_id: ownerId },
      include: {
        member: { include: { department: true, designation: true, reporting_manager: true } },
        tasks: { include: { assigned_member: true } },
        equipment_returns: true,
        exit_interview: true,
        final_settlement: { include: { items: true } },
      },
    });

    if (!offboardingCase) {
      throw createGeneralError('Offboarding case not found', 'general', HttpStatus.NOT_FOUND);
    }
    return offboardingCase;
  }

  async updateCase(id: string, data: updateOffboardingCaseDto, ownerId: string) {
    const currentCase = await this.getCaseById(id, ownerId);

    const updatePayload: any = { ...data };

    // Business validation on closure
    if (data.status === OffboardingCaseStatusEnum.completed && currentCase.status !== OffboardingCaseStatusEnum.completed) {
      const pendingTasks = (currentCase.tasks || []).filter(
        (t: any) => t.status === TaskStatusEnum.pending || t.status === TaskStatusEnum.in_progress,
      );
      if (pendingTasks.length > 0) {
        throw createGeneralError(
          `Cannot close offboarding case because ${pendingTasks.length} clearance task(s) remain incomplete`,
          'general', HttpStatus.BAD_REQUEST,
        );
      }

      updatePayload.completed_at = new Date();

      // Deactivate member account access if not already deactivated
      if (!currentCase.access_deactivated_at) {
        updatePayload.access_deactivated_at = new Date();
        await this.db.members.update({
          where: { id: currentCase.member_id },
          data: { status: MemberStatusEnum.deactivated },
        });
      }
    }

    await this.db.offboarding_cases.update({
      where: { id },
      data: updatePayload,
    });

    return this.getCaseById(id, ownerId);
  }

  // -------------------------------------------------------------
  // TASKS & ACCESS DEACTIVATION WORKFLOW
  // -------------------------------------------------------------

  async addTask(caseId: string, data: createOffboardingTaskDto, ownerId: string) {
    await this.getCaseById(caseId, ownerId);

    return await this.db.offboarding_tasks.create({
      data: {
        offboarding_case_id: caseId,
        title: data.title,
        description: data.description,
        owner_type: data.owner_type,
        assigned_member_id: data.assigned_member_id || null,
        due_date: data.due_date ? new Date(data.due_date) : null,
        priority: data.priority,
        status: TaskStatusEnum.pending,
        order_index: data.order_index ?? 0,
        notes: data.notes || null,
      },
    });
  }

  async updateTask(taskId: string, data: updateOffboardingTaskDto, ownerId: string, currentMemberId?: string) {
    const task = await this.db.offboarding_tasks.findFirst({
      where: { id: taskId },
      include: { offboarding_case: true },
    });

    if (!task || task.offboarding_case.owner_id !== ownerId) {
      throw createGeneralError('Offboarding task not found', 'general', HttpStatus.NOT_FOUND);
    }

    const updatePayload: any = { ...data };
    if (data.status === TaskStatusEnum.completed && task.status !== TaskStatusEnum.completed) {
      updatePayload.completed_at = new Date();
      updatePayload.completed_by_id = currentMemberId || null;

      // Special check: If this is an IT Access Revocation task, execute user account deactivation
      if (task.title.toLowerCase().includes('access') || task.title.toLowerCase().includes('deactivation')) {
        await this.db.members.update({
          where: { id: task.offboarding_case.member_id },
          data: { status: MemberStatusEnum.deactivated },
        });
        await this.db.offboarding_cases.update({
          where: { id: task.offboarding_case_id },
          data: { access_deactivated_at: new Date() },
        });
      }
    }

    await this.db.offboarding_tasks.update({
      where: { id: taskId },
      data: updatePayload,
    });

    return this.db.offboarding_tasks.findFirst({ where: { id: taskId } });
  }

  // -------------------------------------------------------------
  // EQUIPMENT RETURN
  // -------------------------------------------------------------

  async addEquipmentReturn(caseId: string, data: addEquipmentReturnDto, ownerId: string) {
    await this.getCaseById(caseId, ownerId);

    return await this.db.equipment_returns.create({
      data: {
        offboarding_case_id: caseId,
        item_name: data.item_name,
        item_type: data.item_type,
        serial_number: data.serial_number || null,
        condition: data.condition || 'good',
        notes: data.notes || null,
      },
    });
  }

  async updateEquipmentReturn(returnId: string, data: updateEquipmentReturnDto, ownerId: string, receiverId?: string) {
    const item = await this.db.equipment_returns.findFirst({
      where: { id: returnId },
      include: { offboarding_case: true },
    });

    if (!item || item.offboarding_case.owner_id !== ownerId) {
      throw createGeneralError('Equipment return item not found', 'general', HttpStatus.NOT_FOUND);
    }

    const updatePayload: any = {
      condition: data.condition || item.condition,
      returned_at: data.returned_at ? new Date(data.returned_at) : new Date(),
      received_by_id: receiverId || null,
      notes: data.notes || item.notes,
    };

    await this.db.equipment_returns.update({
      where: { id: returnId },
      data: updatePayload,
    });

    return this.db.equipment_returns.findFirst({ where: { id: returnId } });
  }

  // -------------------------------------------------------------
  // EXIT INTERVIEW ENGINE & REPORTING
  // -------------------------------------------------------------

  async createExitTemplate(data: createExitInterviewTemplateDto, ownerId: string) {
    return await this.db.exit_interview_templates.create({
      data: {
        title: data.title,
        description: data.description,
        is_active: data.is_active ?? true,
        questions: data.questions,
        owner_id: ownerId,
      },
    });
  }

  async getExitTemplates(ownerId: string) {
    return await this.db.exit_interview_templates.findMany({
      where: { owner_id: ownerId },
      orderBy: { created_at: 'desc' },
    });
  }

  async submitExitInterview(caseId: string, data: submitExitInterviewDto, ownerId: string) {
    const offboardingCase = await this.getCaseById(caseId, ownerId);

    let interview = await this.db.exit_interviews.findFirst({
      where: { offboarding_case_id: caseId },
    });

    if (!interview) {
      interview = await this.db.exit_interviews.create({
        data: {
          offboarding_case_id: caseId,
          member_id: offboardingCase.member_id,
          status: 'completed',
          submitted_at: new Date(),
          responses: data.responses,
          notes: data.notes || null,
        },
      });
    } else {
      await this.db.exit_interviews.update({
        where: { id: interview.id },
        data: {
          status: 'completed',
          submitted_at: new Date(),
          responses: data.responses,
          notes: data.notes || interview.notes,
        },
      });
    }

    return this.db.exit_interviews.findFirst({ where: { offboarding_case_id: caseId } });
  }

  // Anonymized Turnover Insights Aggregation API
  async getTurnoverAnalytics(ownerId: string) {
    const cases = await this.db.offboarding_cases.findMany({
      where: { owner_id: ownerId },
      include: { member: { include: { department: true } } },
    });

    const totalOffboarded = cases.length;
    const reasonBreakdown: Record<string, number> = {};
    const departmentBreakdown: Record<string, number> = {};

    for (const c of cases) {
      const reason = c.reason || 'other';
      reasonBreakdown[reason] = (reasonBreakdown[reason] || 0) + 1;

      const deptName = c.member?.department?.title || 'Unassigned';
      departmentBreakdown[deptName] = (departmentBreakdown[deptName] || 0) + 1;
    }

    return {
      total_offboarded: totalOffboarded,
      reasons_breakdown: reasonBreakdown,
      department_breakdown: departmentBreakdown,
    };
  }

  // -------------------------------------------------------------
  // FINAL SETTLEMENT & PAYROLL INTEGRATION
  // -------------------------------------------------------------

  async calculateFinalSettlement(caseId: string, data: calculateFinalSettlementDto, ownerId: string) {
    const offboardingCase = await this.getCaseById(caseId, ownerId);

    const grossSalary = data.gross_salary ?? 50000;
    const unusedLeaveDays = data.unused_leave_days ?? 5;
    const dailyRate = data.daily_leave_rate ?? (grossSalary / 30);
    const leaveEncashment = unusedLeaveDays * dailyRate;
    const severance = data.severance_amount ?? 0;
    const noticePay = data.notice_pay_adjustment ?? 0;
    const deductions = data.deductions_amount ?? 0;

    const grossSettlement = grossSalary + leaveEncashment + severance + noticePay;
    const netSettlement = Math.max(0, grossSettlement - deductions);

    const calcDate = new Date();

    let settlement = await this.db.final_settlements.findFirst({
      where: { offboarding_case_id: caseId },
    });

    if (settlement) {
      // Clear old items and update
      await this.db.final_settlement_items.delete({ where: { settlement_id: settlement.id } });

      await this.db.final_settlements.update({
        where: { id: settlement.id },
        data: {
          status: SettlementStatusEnum.calculated,
          calculation_date: calcDate,
          gross_settlement: grossSettlement,
          total_deductions: deductions,
          net_settlement: netSettlement,
          notes: data.notes || settlement.notes,
        },
      });
    } else {
      settlement = await this.db.final_settlements.create({
        data: {
          offboarding_case_id: caseId,
          member_id: offboardingCase.member_id,
          status: SettlementStatusEnum.calculated,
          calculation_date: calcDate,
          gross_settlement: grossSettlement,
          total_deductions: deductions,
          net_settlement: netSettlement,
          notes: data.notes || null,
          owner_id: ownerId,
        },
      });
    }

    const settlementId = settlement.id;

    // Create Line Items
    const items = [
      { settlement_id: settlementId, item_name: 'Base Salary (Final Month)', item_type: 'earning', amount: grossSalary },
      { settlement_id: settlementId, item_name: `Unused Leave Encashment (${unusedLeaveDays} days)`, item_type: 'earning', amount: leaveEncashment },
      ...(severance > 0 ? [{ settlement_id: settlementId, item_name: 'Gratuity / Severance Pay', item_type: 'earning', amount: severance }] : []),
      ...(noticePay > 0 ? [{ settlement_id: settlementId, item_name: 'Notice Period Adjustment', item_type: 'earning', amount: noticePay }] : []),
      ...(deductions > 0 ? [{ settlement_id: settlementId, item_name: 'Company Deductions / Loan Clearance', item_type: 'deduction', amount: deductions }] : []),
    ];

    for (const item of items) {
      await this.db.final_settlement_items.create({ data: item });
    }

    return this.db.final_settlements.findFirst({
      where: { id: settlementId },
      include: { items: true },
    });
  }

  async approveFinalSettlement(settlementId: string, ownerId: string, approverId: string) {
    const settlement = await this.db.final_settlements.findFirst({
      where: { id: settlementId, owner_id: ownerId },
    });

    if (!settlement) {
      throw createGeneralError('Final settlement record not found', 'general', HttpStatus.NOT_FOUND);
    }

    if (settlement.status === SettlementStatusEnum.approved || settlement.status === SettlementStatusEnum.included_in_payroll) {
      throw createGeneralError('Settlement is already approved', 'general', HttpStatus.BAD_REQUEST);
    }

    await this.db.final_settlements.update({
      where: { id: settlementId },
      data: {
        status: SettlementStatusEnum.approved,
        approved_at: new Date(),
        approved_by_id: approverId,
      },
    });

    return this.db.final_settlements.findFirst({
      where: { id: settlementId },
      include: { items: true },
    });
  }

  // -------------------------------------------------------------
  // DASHBOARD
  // -------------------------------------------------------------

  async getDashboardStats(ownerId: string) {
    const activeCases = await this.db.offboarding_cases.count({
      where: { owner_id: ownerId, status: OffboardingCaseStatusEnum.in_progress },
    });

    const initiatedCases = await this.db.offboarding_cases.count({
      where: { owner_id: ownerId, status: OffboardingCaseStatusEnum.initiated },
    });

    const pendingSettlements = await this.db.final_settlements.count({
      where: { owner_id: ownerId, status: SettlementStatusEnum.calculated },
    });

    const completedCases = await this.db.offboarding_cases.count({
      where: { owner_id: ownerId, status: OffboardingCaseStatusEnum.completed },
    });

    return {
      active_cases: activeCases,
      initiated_cases: initiatedCases,
      pending_settlements: pendingSettlements,
      completed_cases: completedCases,
    };
  }
}
