const db = require('../db/models');
const Payroll_periodsDBApi = require('../db/api/payroll_periods');
const processFile = require('../middlewares/upload');
const ValidationError = require('./notifications/errors/validation');
const csv = require('csv-parser');
const stream = require('stream');
const { randomUUID } = require('crypto');

const { Op } = db.Sequelize;

const BONUS_TIERS = [
  { minimum: 200000000, rate: 0.05, label: '5% tier (> 200 jt)' },
  { minimum: 150000000, rate: 0.03, label: '3% tier (> 150 jt)' },
  { minimum: 100000000, rate: 0.02, label: '2% tier (> 100 jt)' },
];

function createHttpError(message, code = 400) {
  const error = new Error(message);
  error.code = code;
  return error;
}

function toNumber(value) {
  if (value === null || value === undefined || value === '') {
    return 0;
  }

  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : 0;
}

function roundMoney(value) {
  return Number(toNumber(value).toFixed(2));
}

function addDays(date, days) {
  const result = new Date(date);
  result.setUTCDate(result.getUTCDate() + days);
  return result;
}

function buildPeriodRange(periodMonth) {
  if (!/^\d{4}-\d{2}$/.test(periodMonth || '')) {
    throw createHttpError('Period month must use YYYY-MM format.');
  }

  const [yearText, monthText] = periodMonth.split('-');
  const year = Number(yearText);
  const monthIndex = Number(monthText) - 1;

  if (!Number.isInteger(year) || !Number.isInteger(monthIndex) || monthIndex < 0 || monthIndex > 11) {
    throw createHttpError('Invalid period month.');
  }

  const periodStart = new Date(Date.UTC(year, monthIndex, 1, 0, 0, 0, 0));
  const periodEnd = new Date(Date.UTC(year, monthIndex + 1, 0, 23, 59, 59, 999));

  return { periodStart, periodEnd };
}

function getScopeWhere(currentUser) {
  if (currentUser?.app_role?.globalAccess || !currentUser?.organizationsId) {
    return {};
  }

  return { organizationsId: currentUser.organizationsId };
}

function getDistributionWeight(employee, distributionMethod) {
  if (distributionMethod === 'weighted_by_position') {
    const configuredWeight = toNumber(employee?.job_position?.payroll_weight);
    return configuredWeight > 0 ? configuredWeight : 1;
  }

  return 1;
}

function getRevenueTier(totalRevenue) {
  return BONUS_TIERS.find((tier) => totalRevenue >= tier.minimum) || null;
}

function serializeDate(value) {
  if (!value) {
    return null;
  }

  return new Date(value).toISOString();
}

function normalizeWorkbenchInput(payload = {}) {
  const outletId = payload.outletId;
  const periodMonth = payload.periodMonth;
  const standardWorkdays = Math.max(1, Math.round(toNumber(payload.standardWorkdays || 26)));
  const maxKpiAllowance = Math.max(0, toNumber(payload.maxKpiAllowance || 0));
  const latePenaltyAmount = Math.max(0, toNumber(payload.latePenaltyAmount ?? 1000));
  const distributionMethod =
    payload.distributionMethod === 'equal' ? 'equal' : 'weighted_by_position';

  if (!outletId) {
    throw createHttpError('Outlet is required.');
  }

  if (!periodMonth) {
    throw createHttpError('Period month is required.');
  }

  const { periodStart, periodEnd } = buildPeriodRange(periodMonth);

  return {
    outletId,
    periodMonth,
    periodStart,
    periodEnd,
    standardWorkdays,
    maxKpiAllowance,
    latePenaltyAmount,
    distributionMethod,
  };
}

function groupCounts(items, keySelector, valueSelector = () => 1) {
  const grouped = new Map();

  items.forEach((item) => {
    const key = keySelector(item);
    const currentValue = grouped.get(key) || 0;
    grouped.set(key, currentValue + valueSelector(item));
  });

  return grouped;
}

async function buildPayrollPreview(input, currentUser) {
  const config = normalizeWorkbenchInput(input);
  const scopeWhere = getScopeWhere(currentUser);
  const kpiScoreWindowEnd = addDays(config.periodEnd, 15);
  const kpiScoreWindowStart = addDays(config.periodStart, -7);

  const outlet = await db.outlets.findOne({
    where: {
      id: config.outletId,
      is_active: true,
      ...scopeWhere,
    },
  });

  if (!outlet) {
    throw createHttpError('Outlet not found or unavailable for your workspace.', 404);
  }

  const existingRuns = await db.payroll_periods.findAll({
    where: {
      outletId: config.outletId,
      period_start: config.periodStart,
      period_end: config.periodEnd,
      ...scopeWhere,
    },
    include: [
      {
        model: db.users,
        as: 'generated_by_user',
        required: false,
      },
    ],
    order: [
      ['generated_at', 'DESC'],
      ['createdAt', 'DESC'],
    ],
  });

  const employees = await db.employees.findAll({
    where: {
      outletId: config.outletId,
      is_active: true,
      ...scopeWhere,
      employment_status: {
        [Op.ne]: 'resigned',
      },
      [Op.and]: [
        {
          [Op.or]: [{ join_date: null }, { join_date: { [Op.lte]: config.periodEnd } }],
        },
        {
          [Op.or]: [{ resign_date: null }, { resign_date: { [Op.gte]: config.periodStart } }],
        },
      ],
    },
    include: [
      {
        model: db.job_positions,
        as: 'job_position',
        required: false,
      },
    ],
    order: [['full_name', 'ASC']],
  });

  if (!employees.length) {
    return {
      config: {
        ...config,
        periodStart: serializeDate(config.periodStart),
        periodEnd: serializeDate(config.periodEnd),
      },
      outlet: {
        id: outlet.id,
        name: outlet.name,
        code: outlet.code,
        address: outlet.address,
      },
      readiness: {
        blockers: ['No active employees were found for the selected outlet.'],
        warnings: [],
        employeeCount: 0,
        missingAttendanceCount: 0,
        missingKpiCount: 0,
        existingRunCount: existingRuns.length,
        latestExistingRun: existingRuns[0]
          ? {
              id: existingRuns[0].id,
              name: existingRuns[0].name,
              status: existingRuns[0].status,
              generated_at: serializeDate(existingRuns[0].generated_at),
            }
          : null,
      },
      totals: {
        projectedTakeHomePay: 0,
        averageTakeHomePay: 0,
        totalBaseSalary: 0,
        totalMealTransport: 0,
        totalPositionAllowance: 0,
        totalKpiAllowance: 0,
        totalRevenueBonus: 0,
        totalServiceCharge: 0,
        totalOvertime: 0,
        totalDeductions: 0,
        totalOvertimeHours: 0,
        grossRevenue: 0,
        serviceChargePool: 0,
        bonusPool: 0,
        bonusRate: 0,
        bonusTierLabel: 'No omzet bonus tier reached yet',
      },
      employees: [],
    };
  }

  const employeeIds = employees.map((employee) => employee.id);

  const [attendanceLogs, overtimeLogs, kpiScores, outletRevenues, serviceChargePools, loanAccounts] =
    await Promise.all([
      db.attendance_logs.findAll({
        where: {
          employeeId: {
            [Op.in]: employeeIds,
          },
          outletId: config.outletId,
          work_date: {
            [Op.between]: [config.periodStart, config.periodEnd],
          },
          ...scopeWhere,
        },
      }),
      db.overtime_logs.findAll({
        where: {
          employeeId: {
            [Op.in]: employeeIds,
          },
          outletId: config.outletId,
          work_date: {
            [Op.between]: [config.periodStart, config.periodEnd],
          },
          approval_status: 'approved',
          ...scopeWhere,
        },
      }),
      db.kpi_scores.findAll({
        where: {
          employeeId: {
            [Op.in]: employeeIds,
          },
          outletId: config.outletId,
          ...scopeWhere,
          [Op.or]: [
            {
              scored_at: {
                [Op.between]: [kpiScoreWindowStart, kpiScoreWindowEnd],
              },
            },
            {
              scored_at: null,
            },
          ],
        },
        include: [
          {
            model: db.kpi_periods,
            as: 'kpi_period',
            required: false,
          },
        ],
        order: [
          ['scored_at', 'DESC'],
          ['updatedAt', 'DESC'],
        ],
      }),
      db.outlet_revenues.findAll({
        where: {
          outletId: config.outletId,
          revenue_date: {
            [Op.between]: [config.periodStart, config.periodEnd],
          },
          ...scopeWhere,
        },
      }),
      db.service_charge_pools.findAll({
        where: {
          outletId: config.outletId,
          ...scopeWhere,
          status: {
            [Op.ne]: 'archived',
          },
          period_start: {
            [Op.lte]: config.periodEnd,
          },
          period_end: {
            [Op.gte]: config.periodStart,
          },
        },
      }),
      db.loan_accounts.findAll({
        where: {
          employeeId: {
            [Op.in]: employeeIds,
          },
          outletId: config.outletId,
          ...scopeWhere,
          status: 'active',
          [Op.and]: [
            {
              [Op.or]: [{ start_date: null }, { start_date: { [Op.lte]: config.periodEnd } }],
            },
            {
              [Op.or]: [{ end_date: null }, { end_date: { [Op.gte]: config.periodStart } }],
            },
          ],
        },
      }),
    ]);

  const attendanceByEmployee = new Map();
  attendanceLogs.forEach((log) => {
    const existing = attendanceByEmployee.get(log.employeeId) || [];
    existing.push(log);
    attendanceByEmployee.set(log.employeeId, existing);
  });

  const overtimeHoursByEmployee = groupCounts(
    overtimeLogs,
    (item) => item.employeeId,
    (item) => toNumber(item.hours),
  );

  const loanInstallmentByEmployee = groupCounts(
    loanAccounts,
    (item) => item.employeeId,
    (item) => toNumber(item.installment_amount),
  );

  const kpiScoresByEmployee = new Map();
  kpiScores.forEach((score) => {
    const periodMatches =
      score.kpi_period &&
      score.kpi_period.period_start &&
      score.kpi_period.period_end &&
      new Date(score.kpi_period.period_start) <= config.periodEnd &&
      new Date(score.kpi_period.period_end) >= config.periodStart;

    const scoredAt = score.scored_at ? new Date(score.scored_at) : null;
    const scoredWithinWindow =
      !scoredAt || (scoredAt >= kpiScoreWindowStart && scoredAt <= kpiScoreWindowEnd);

    if ((periodMatches || scoredWithinWindow) && !kpiScoresByEmployee.has(score.employeeId)) {
      kpiScoresByEmployee.set(score.employeeId, score);
    }
  });

  const totalGrossRevenue = roundMoney(
    outletRevenues.reduce((total, revenue) => total + toNumber(revenue.gross_revenue || revenue.net_revenue), 0),
  );
  const totalServiceChargePool = roundMoney(
    serviceChargePools.reduce((total, pool) => total + toNumber(pool.total_amount), 0),
  );
  const revenueTier = getRevenueTier(totalGrossRevenue);
  const totalRevenueBonusPool = roundMoney(totalGrossRevenue * (revenueTier?.rate || 0));

  const totalDistributionWeight = employees.reduce(
    (total, employee) => total + getDistributionWeight(employee, config.distributionMethod),
    0,
  );

  const employeeRows = employees.map((employee) => {
    const employeeAttendance = attendanceByEmployee.get(employee.id) || [];
    const sickDays = employeeAttendance.filter((entry) => entry.status === 'sakit').length;
    const leaveDays = employeeAttendance.filter((entry) => entry.status === 'izin').length;
    const absentDays = employeeAttendance.filter((entry) => entry.status === 'tidak_hadir').length;
    const lateEntries = employeeAttendance.filter((entry) => entry.status === 'telat');
    const lateCount = lateEntries.length;
    const lateMinutesTotal = lateEntries.reduce(
      (total, entry) => total + Math.max(0, Math.round(toNumber(entry.late_minutes))),
      0,
    );

    const workdays = Math.max(0, config.standardWorkdays - sickDays - leaveDays);
    const baseSalaryMonthly = roundMoney(toNumber(employee.base_salary_monthly));
    const dailyRate = config.standardWorkdays > 0 ? roundMoney(baseSalaryMonthly / config.standardWorkdays) : 0;
    const baseSalaryTotal = roundMoney(dailyRate * workdays);

    const mealTransportDaily =
      roundMoney(toNumber(employee.meal_allowance_daily) + toNumber(employee.transport_allowance_daily));
    const mealTransportTotal = roundMoney(mealTransportDaily * workdays);

    const shouldProratePositionAllowance =
      Boolean(employee.prorate_position_allowance_under_20_days) && workdays < 20;
    const positionAllowanceFixed = roundMoney(toNumber(employee.position_allowance_fixed));
    const positionAllowanceTotal = shouldProratePositionAllowance
      ? roundMoney((positionAllowanceFixed * workdays) / config.standardWorkdays)
      : positionAllowanceFixed;

    const kpiScore = roundMoney(toNumber(kpiScoresByEmployee.get(employee.id)?.final_score));
    const kpiAllowanceTotal = roundMoney((kpiScore / 100) * config.maxKpiAllowance);

    const overtimeHoursTotal = roundMoney(overtimeHoursByEmployee.get(employee.id) || 0);
    const overtimeRate = roundMoney(dailyRate / 8);
    const overtimeTotal = roundMoney(overtimeHoursTotal * overtimeRate);

    const employeeWeight = getDistributionWeight(employee, config.distributionMethod);
    const distributionRatio = totalDistributionWeight > 0 ? employeeWeight / totalDistributionWeight : 0;
    const revenueBonusTotal = roundMoney(totalRevenueBonusPool * distributionRatio);
    const serviceChargeTotal = roundMoney(totalServiceChargePool * distributionRatio);

    const absentDeduction = roundMoney(absentDays * dailyRate);
    const lateDeduction = roundMoney(lateMinutesTotal * config.latePenaltyAmount);
    const loanDeduction = roundMoney(loanInstallmentByEmployee.get(employee.id) || 0);
    const deductionTotal = roundMoney(absentDeduction + lateDeduction + loanDeduction);

    const takeHomePay = roundMoney(
      baseSalaryTotal +
        mealTransportTotal +
        positionAllowanceTotal +
        kpiAllowanceTotal +
        revenueBonusTotal +
        serviceChargeTotal +
        overtimeTotal -
        deductionTotal,
    );

    return {
      id: employee.id,
      employee_code: employee.employee_code,
      full_name: employee.full_name,
      employment_status: employee.employment_status,
      bank_name: employee.bank_name,
      bank_account_name: employee.bank_account_name,
      bank_account_number: employee.bank_account_number,
      job_position: employee.job_position
        ? {
            id: employee.job_position.id,
            name: employee.job_position.name,
            payroll_weight: toNumber(employee.job_position.payroll_weight),
          }
        : null,
      workdays,
      sick_days: sickDays,
      leave_days: leaveDays,
      absent_days: absentDays,
      late_count: lateCount,
      late_minutes_total: lateMinutesTotal,
      base_salary_monthly: baseSalaryMonthly,
      daily_rate: dailyRate,
      base_salary_total: baseSalaryTotal,
      meal_transport_total: mealTransportTotal,
      position_allowance_total: positionAllowanceTotal,
      kpi_score: kpiScore,
      kpi_allowance_total: kpiAllowanceTotal,
      overtime_hours_total: overtimeHoursTotal,
      overtime_rate: overtimeRate,
      overtime_total: overtimeTotal,
      revenue_bonus_total: revenueBonusTotal,
      service_charge_total: serviceChargeTotal,
      absent_deduction: absentDeduction,
      late_deduction: lateDeduction,
      loan_deduction: loanDeduction,
      deduction_total: deductionTotal,
      take_home_pay: takeHomePay,
      attendance_record_count: employeeAttendance.length,
      has_kpi_score: kpiScoresByEmployee.has(employee.id),
      has_attendance: employeeAttendance.length > 0,
    };
  });

  const missingAttendanceCount = employeeRows.filter((row) => !row.has_attendance).length;
  const missingKpiCount = employeeRows.filter((row) => !row.has_kpi_score).length;

  const warnings = [];
  if (existingRuns.length) {
    warnings.push(
      `${existingRuns.length} payroll run(s) already exist for this outlet and period. Generating again will create a new revision.`,
    );
  }
  if (missingAttendanceCount) {
    warnings.push(
      `${missingAttendanceCount} employee(s) do not have attendance data for this month. Draft values use default workdays and should be reviewed.`,
    );
  }
  if (missingKpiCount) {
    warnings.push(
      `${missingKpiCount} employee(s) are missing KPI scores. Their KPI allowance is currently projected as Rp 0.`,
    );
  }
  if (!totalGrossRevenue) {
    warnings.push('No outlet revenue was found for this period, so omzet bonus is projected as Rp 0.');
  }
  if (!totalServiceChargePool) {
    warnings.push('No service charge pool was found for this period.');
  }

  const totals = employeeRows.reduce(
    (accumulator, row) => ({
      projectedTakeHomePay: roundMoney(accumulator.projectedTakeHomePay + row.take_home_pay),
      totalBaseSalary: roundMoney(accumulator.totalBaseSalary + row.base_salary_total),
      totalMealTransport: roundMoney(accumulator.totalMealTransport + row.meal_transport_total),
      totalPositionAllowance: roundMoney(
        accumulator.totalPositionAllowance + row.position_allowance_total,
      ),
      totalKpiAllowance: roundMoney(accumulator.totalKpiAllowance + row.kpi_allowance_total),
      totalRevenueBonus: roundMoney(accumulator.totalRevenueBonus + row.revenue_bonus_total),
      totalServiceCharge: roundMoney(accumulator.totalServiceCharge + row.service_charge_total),
      totalOvertime: roundMoney(accumulator.totalOvertime + row.overtime_total),
      totalDeductions: roundMoney(accumulator.totalDeductions + row.deduction_total),
      totalOvertimeHours: roundMoney(accumulator.totalOvertimeHours + row.overtime_hours_total),
    }),
    {
      projectedTakeHomePay: 0,
      totalBaseSalary: 0,
      totalMealTransport: 0,
      totalPositionAllowance: 0,
      totalKpiAllowance: 0,
      totalRevenueBonus: 0,
      totalServiceCharge: 0,
      totalOvertime: 0,
      totalDeductions: 0,
      totalOvertimeHours: 0,
    },
  );

  return {
    config: {
      ...config,
      periodStart: serializeDate(config.periodStart),
      periodEnd: serializeDate(config.periodEnd),
    },
    outlet: {
      id: outlet.id,
      name: outlet.name,
      code: outlet.code,
      address: outlet.address,
      gps_radius_m: outlet.gps_radius_m,
    },
    readiness: {
      blockers: [],
      warnings,
      employeeCount: employeeRows.length,
      missingAttendanceCount,
      missingKpiCount,
      existingRunCount: existingRuns.length,
      latestExistingRun: existingRuns[0]
        ? {
            id: existingRuns[0].id,
            name: existingRuns[0].name,
            status: existingRuns[0].status,
            generated_at: serializeDate(existingRuns[0].generated_at),
            generated_by_user: existingRuns[0].generated_by_user
              ? `${existingRuns[0].generated_by_user.firstName || ''} ${
                  existingRuns[0].generated_by_user.lastName || ''
                }`.trim()
              : null,
          }
        : null,
    },
    totals: {
      ...totals,
      averageTakeHomePay: employeeRows.length
        ? roundMoney(totals.projectedTakeHomePay / employeeRows.length)
        : 0,
      grossRevenue: totalGrossRevenue,
      serviceChargePool: totalServiceChargePool,
      bonusPool: totalRevenueBonusPool,
      bonusRate: revenueTier?.rate || 0,
      bonusTierLabel: revenueTier?.label || 'No omzet bonus tier reached yet',
    },
    employees: employeeRows,
  };
}

module.exports = class Payroll_periodsService {
  static async create(data, currentUser) {
    const transaction = await db.sequelize.transaction();
    try {
      await Payroll_periodsDBApi.create(
        data,
        {
          currentUser,
          transaction,
        },
      );

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async bulkImport(req, res) {
    const transaction = await db.sequelize.transaction();

    try {
      await processFile(req, res);
      const bufferStream = new stream.PassThrough();
      const results = [];

      await bufferStream.end(Buffer.from(req.file.buffer, 'utf-8'));

      await new Promise((resolve, reject) => {
        bufferStream
          .pipe(csv())
          .on('data', (data) => results.push(data))
          .on('end', async () => {
            console.log('CSV results', results);
            resolve();
          })
          .on('error', (error) => reject(error));
      });

      await Payroll_periodsDBApi.bulkImport(results, {
        transaction,
        ignoreDuplicates: true,
        validate: true,
        currentUser: req.currentUser,
      });

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async update(data, id, currentUser) {
    const transaction = await db.sequelize.transaction();
    try {
      const payroll_periods = await Payroll_periodsDBApi.findBy(
        { id },
        { transaction },
      );

      if (!payroll_periods) {
        throw new ValidationError('payroll_periodsNotFound');
      }

      const updatedPayroll_periods = await Payroll_periodsDBApi.update(
        id,
        data,
        {
          currentUser,
          transaction,
        },
      );

      await transaction.commit();
      return updatedPayroll_periods;
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async deleteByIds(ids, currentUser) {
    const transaction = await db.sequelize.transaction();

    try {
      await Payroll_periodsDBApi.deleteByIds(ids, {
        currentUser,
        transaction,
      });

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async remove(id, currentUser) {
    const transaction = await db.sequelize.transaction();

    try {
      await Payroll_periodsDBApi.remove(
        id,
        {
          currentUser,
          transaction,
        },
      );

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }

  static async getWorkbench(currentUser) {
    const scopeWhere = getScopeWhere(currentUser);

    const outlets = await db.outlets.findAll({
      where: {
        is_active: true,
        ...scopeWhere,
      },
      order: [['name', 'ASC']],
    });

    const recentPayrollPeriods = await db.payroll_periods.findAll({
      where: scopeWhere,
      include: [
        {
          model: db.outlets,
          as: 'outlet',
          required: false,
        },
        {
          model: db.users,
          as: 'generated_by_user',
          required: false,
        },
      ],
      order: [
        ['generated_at', 'DESC'],
        ['createdAt', 'DESC'],
      ],
      limit: 6,
    });

    const recentRuns = await Promise.all(
      recentPayrollPeriods.map(async (period) => {
        const [employeeCount, totalTakeHomePay] = await Promise.all([
          db.payroll_items.count({
            where: {
              payroll_periodId: period.id,
              ...scopeWhere,
            },
          }),
          db.payroll_items.sum('take_home_pay', {
            where: {
              payroll_periodId: period.id,
              ...scopeWhere,
            },
          }),
        ]);

        return {
          id: period.id,
          name: period.name,
          status: period.status,
          period_start: serializeDate(period.period_start),
          period_end: serializeDate(period.period_end),
          generated_at: serializeDate(period.generated_at),
          outlet: period.outlet
            ? {
                id: period.outlet.id,
                name: period.outlet.name,
                code: period.outlet.code,
              }
            : null,
          generated_by_user: period.generated_by_user
            ? `${period.generated_by_user.firstName || ''} ${period.generated_by_user.lastName || ''}`.trim()
            : null,
          employeeCount,
          totalTakeHomePay: roundMoney(totalTakeHomePay),
        };
      }),
    );

    return {
      outlets: outlets.map((outlet) => ({
        id: outlet.id,
        code: outlet.code,
        name: outlet.name,
        address: outlet.address,
        gps_radius_m: outlet.gps_radius_m,
      })),
      recentRuns,
    };
  }

  static async previewWorkbench(input, currentUser) {
    return buildPayrollPreview(input, currentUser);
  }

  static async generateWorkbench(input, currentUser) {
    const preview = await buildPayrollPreview(input, currentUser);

    if (preview.readiness.blockers.length) {
      throw createHttpError(preview.readiness.blockers[0]);
    }

    const transaction = await db.sequelize.transaction();

    try {
      const revisionNumber = preview.readiness.existingRunCount + 1;
      const periodId = randomUUID();
      const periodNameBase = `Payroll ${preview.config.periodMonth} • ${preview.outlet.name}`;
      const periodName =
        revisionNumber > 1 ? `${periodNameBase} (Rev ${revisionNumber})` : periodNameBase;
      const generatedAt = new Date();

      await db.payroll_periods.create(
        {
          id: periodId,
          name: periodName,
          period_start: preview.config.periodStart,
          period_end: preview.config.periodEnd,
          standard_workdays: preview.config.standardWorkdays,
          status: 'generated',
          generated_at: generatedAt,
          outletId: preview.outlet.id,
          generated_by_userId: currentUser.id,
          organizationsId: currentUser.organizationsId || null,
          createdById: currentUser.id,
          updatedById: currentUser.id,
        },
        { transaction },
      );

      const payrollItemsPayload = preview.employees.map((employee) => ({
        id: randomUUID(),
        payroll_periodId: periodId,
        employeeId: employee.id,
        outletId: preview.outlet.id,
        organizationsId: currentUser.organizationsId || null,
        workdays: employee.workdays,
        sick_days: employee.sick_days,
        leave_days: employee.leave_days,
        absent_days: employee.absent_days,
        late_count: employee.late_count,
        late_minutes_total: employee.late_minutes_total,
        base_salary_monthly: employee.base_salary_monthly,
        daily_rate: employee.daily_rate,
        base_salary_total: employee.base_salary_total,
        meal_transport_total: employee.meal_transport_total,
        position_allowance_total: employee.position_allowance_total,
        kpi_score: employee.kpi_score,
        kpi_allowance_total: employee.kpi_allowance_total,
        overtime_hours_total: employee.overtime_hours_total,
        overtime_rate: employee.overtime_rate,
        overtime_total: employee.overtime_total,
        revenue_bonus_total: employee.revenue_bonus_total,
        service_charge_total: employee.service_charge_total,
        deduction_total: employee.deduction_total,
        take_home_pay: employee.take_home_pay,
        status: 'draft',
        adjustment_notes: preview.readiness.warnings.join(' | ') || null,
        createdById: currentUser.id,
        updatedById: currentUser.id,
      }));

      await db.payroll_items.bulkCreate(payrollItemsPayload, { transaction });

      const payslipsPayload = payrollItemsPayload.map((item, index) => ({
        id: randomUUID(),
        payroll_itemId: item.id,
        organizationsId: currentUser.organizationsId || null,
        slip_number: `SLIP-${preview.config.periodMonth}-${String(index + 1).padStart(3, '0')}`,
        issued_at: generatedAt,
        delivery_status: 'not_sent',
        delivery_notes: 'Draft payslip created from Payroll Workbench.',
        createdById: currentUser.id,
        updatedById: currentUser.id,
      }));

      await db.payslips.bulkCreate(payslipsPayload, { transaction });

      await transaction.commit();

      return {
        success: true,
        payrollPeriod: {
          id: periodId,
          name: periodName,
          status: 'generated',
          generated_at: serializeDate(generatedAt),
          period_start: preview.config.periodStart,
          period_end: preview.config.periodEnd,
        },
        generatedCount: payrollItemsPayload.length,
        payslipCount: payslipsPayload.length,
        totals: preview.totals,
        warnings: preview.readiness.warnings,
      };
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }
};
