<?php

class PayrollRun extends Model
{
    protected string $table = 'payroll_runs';

    public function listRuns(): array
    {
        return $this->db->query(
            "SELECT pr.*, u.full_name as generated_by_name,
                    (SELECT COUNT(*) FROM payroll_items pi WHERE pi.payroll_run_id = pr.id) as staff_count,
                    (SELECT COALESCE(SUM(net_pay),0) FROM payroll_items pi WHERE pi.payroll_run_id = pr.id) as total_net
             FROM payroll_runs pr LEFT JOIN users u ON u.id = pr.generated_by
             ORDER BY pr.period_year DESC, pr.period_month DESC"
        )->fetchAll();
    }

    public function existsForPeriod(int $month, int $year): bool
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) as cnt FROM payroll_runs WHERE period_month = :m AND period_year = :y");
        $stmt->execute(['m' => $month, 'y' => $year]);
        return (int) $stmt->fetch()['cnt'] > 0;
    }

    public function awaitingPaymentCount(): int
    {
        $stmt = $this->db->query("SELECT COUNT(*) as cnt FROM payroll_runs WHERE status = 'FINALIZED'");
        return (int) $stmt->fetch()['cnt'];
    }

    /**
     * Generate a payroll run for a given month/year using attendance + wage type for all active staff.
     */
    public function generate(int $month, int $year, ?int $userId): int
    {
        $this->db->beginTransaction();
        try {
            $runId = $this->insert([
                'period_month' => $month,
                'period_year'  => $year,
                'status'       => 'DRAFT',
                'generated_by' => $userId,
            ]);

            $attendanceModel = new Attendance();
            $staff = $this->db->query("SELECT * FROM users WHERE is_active = 1")->fetchAll();

            $itemStmt = $this->db->prepare(
                "INSERT INTO payroll_items (payroll_run_id, user_id, days_present, days_absent, basic_amount, additions, deductions, net_pay)
                 VALUES (:run_id, :user_id, :present, :absent, :basic, 0, 0, :net)"
            );

            foreach ($staff as $u) {
                $summary = $attendanceModel->monthSummary((int) $u['id'], $month, $year);
                $daysPresent = $summary['present'] + ($summary['half_day'] * 0.5);

                $basic = match ($u['wage_type']) {
                    'DAILY' => (float) $u['daily_wage'] * $daysPresent,
                    'PIECE_RATE' => 0.0, // set manually per employee — no piece-count tracking yet
                    default => (float) $u['basic_salary'], // MONTHLY
                };

                $itemStmt->execute([
                    'run_id'  => $runId,
                    'user_id' => $u['id'],
                    'present' => $daysPresent,
                    'absent'  => $summary['absent'],
                    'basic'   => $basic,
                    'net'     => $basic,
                ]);
            }

            $this->db->commit();
            return $runId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function findWithItems(int $runId): array|false
    {
        $run = $this->find($runId);
        if (!$run) {
            return false;
        }

        $stmt = $this->db->prepare(
            "SELECT pi.*, u.full_name, u.wage_type, r.role_name
             FROM payroll_items pi
             JOIN users u ON u.id = pi.user_id
             JOIN roles r ON r.id = u.role_id
             WHERE pi.payroll_run_id = :id
             ORDER BY u.full_name ASC"
        );
        $stmt->execute(['id' => $runId]);
        $run['items'] = $stmt->fetchAll();

        return $run;
    }

    public function updateItem(int $itemId, float $additions, float $deductions): void
    {
        $stmt = $this->db->prepare("SELECT basic_amount FROM payroll_items WHERE id = :id");
        $stmt->execute(['id' => $itemId]);
        $basic = (float) ($stmt->fetch()['basic_amount'] ?? 0);
        $net = $basic + $additions - $deductions;

        $this->db->prepare(
            "UPDATE payroll_items SET additions = :add, deductions = :ded, net_pay = :net WHERE id = :id"
        )->execute(['add' => $additions, 'ded' => $deductions, 'net' => $net, 'id' => $itemId]);
    }

    public function finalize(int $runId): void
    {
        $this->update($runId, ['status' => 'FINALIZED']);
    }

    /**
     * Pay one payroll item — debits the chosen finance account and logs a finance transaction.
     */
    public function payItem(int $itemId, int $financeAccountId, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare(
                "SELECT pi.*, u.full_name FROM payroll_items pi JOIN users u ON u.id = pi.user_id WHERE pi.id = :id"
            );
            $stmt->execute(['id' => $itemId]);
            $item = $stmt->fetch();
            if (!$item || $item['is_paid']) {
                $this->db->rollBack();
                return;
            }

            $this->db->prepare("UPDATE payroll_items SET is_paid = 1, paid_at = NOW() WHERE id = :id")
                ->execute(['id' => $itemId]);

            $this->db->prepare("UPDATE finance_accounts SET current_balance = current_balance - :amt WHERE id = :id")
                ->execute(['amt' => $item['net_pay'], 'id' => $financeAccountId]);

            $this->db->prepare(
                "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, reference_id, description, created_by)
                 VALUES (:account_id, 'DEBIT', :amount, 'PAYROLL', 'PAYROLL_ITEM', :item_id, :description, :user_id)"
            )->execute([
                'account_id'  => $financeAccountId,
                'amount'      => $item['net_pay'],
                'item_id'     => $itemId,
                'description' => "Salary payment — {$item['full_name']}",
                'user_id'     => $userId,
            ]);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function payAllUnpaid(int $runId, int $financeAccountId, ?int $userId): void
    {
        $stmt = $this->db->prepare("SELECT id FROM payroll_items WHERE payroll_run_id = :run_id AND is_paid = 0");
        $stmt->execute(['run_id' => $runId]);
        foreach ($stmt->fetchAll() as $row) {
            $this->payItem((int) $row['id'], $financeAccountId, $userId);
        }

        // Mark the run as PAID once every item has been paid
        $check = $this->db->prepare("SELECT COUNT(*) as unpaid FROM payroll_items WHERE payroll_run_id = :id AND is_paid = 0");
        $check->execute(['id' => $runId]);
        if ((int) $check->fetch()['unpaid'] === 0) {
            $this->update($runId, ['status' => 'PAID']);
        }
    }
}
