<?php

declare(strict_types=1);

namespace ABS\Modules\Reports\Repositories;

use ABS\Core\Database;
use PDO;

final class SupplierStatisticsRepository
{
    public function suppliers(): array
    {
        return Database::query(
            "SELECT *
             FROM report_suppliers
             WHERE is_active=1
             ORDER BY sort_order, name"
        )->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function supplier(int $id): ?array
    {
        $statement = Database::prepare(
            "SELECT *
             FROM report_suppliers
             WHERE id=:id
             LIMIT 1"
        );
        $statement->execute([':id' => $id]);

        return $statement->fetch(PDO::FETCH_ASSOC)
            ?: null;
    }

    public function updateLogo(
        int $supplierId,
        string $logoPath
    ): void {
        $statement = Database::prepare(
            "UPDATE report_suppliers
             SET logo_path=:logo_path,
                 updated_at=NOW()
             WHERE id=:id"
        );
        $statement->execute([
            ':logo_path' => $logoPath,
            ':id' => $supplierId,
        ]);
    }

    public function summary(int $year): array
    {
        $statement = Database::prepare(
            "SELECT
                COUNT(DISTINCT supplier_id)
                    AS supplier_count,
                COALESCE(SUM(
                    CASE
                        WHEN YEAR(invoice_date)=:year
                        THEN invoice_value
                        ELSE 0
                    END
                ),0) AS purchases,
                COALESCE(SUM(
                    CASE
                        WHEN outstanding_value>0
                        THEN outstanding_value
                        ELSE 0
                    END
                ),0) AS outstanding
             FROM report_supplier_documents"
        );
        $statement->execute([':year' => $year]);
        $row =
            $statement->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'supplier_count' =>
                (int) ($row['supplier_count'] ?? 0),
            'purchases' =>
                (float) ($row['purchases'] ?? 0),
            'outstanding' =>
                (float) ($row['outstanding'] ?? 0),
        ];
    }

    public function supplierStatisticsFiltered(
        int $supplierId,
        int $year,
        int $month,
        string $asOfDate
    ): array {
        $dateFrom = sprintf(
            '%04d-%02d-01',
            $year,
            $month > 0 ? $month : 1
        );

        $dateTo = $month > 0
            ? min(
                $asOfDate,
                date('Y-m-t', strtotime($dateFrom))
            )
            : min($asOfDate, $year . '-12-31');

        if ($month === 0) {
            $dateFrom = $year . '-01-01';
        }

        $statement = Database::prepare(
            "SELECT
                COALESCE(SUM(
                    CASE
                        WHEN invoice_date>=:gross_date_from
                         AND invoice_date<=:gross_date_to
                        THEN invoice_value
                        ELSE 0
                    END
                ),0) AS purchases_gross,
                COALESCE(SUM(
                    CASE
                        WHEN invoice_date>=:net_date_from
                         AND invoice_date<=:net_date_to
                        THEN COALESCE(
                            net_value,
                            ROUND(invoice_value / 1.21, 2)
                        )
                        ELSE 0
                    END
                ),0) AS purchases_net,
                COALESCE(SUM(
                    CASE
                        WHEN invoice_date>=:tax_date_from
                         AND invoice_date<=:tax_date_to
                        THEN COALESCE(
                            tax_value,
                            invoice_value
                            - ROUND(invoice_value / 1.21, 2)
                        )
                        ELSE 0
                    END
                ),0) AS purchases_tax,
                COALESCE(SUM(
                    CASE
                        WHEN outstanding_value>0
                         AND invoice_date<=:outstanding_date
                        THEN outstanding_value
                        ELSE 0
                    END
                ),0) AS outstanding,
                COALESCE(SUM(
                    CASE
                        WHEN due_date<:overdue_date
                         AND outstanding_value>0
                        THEN outstanding_value
                        ELSE 0
                    END
                ),0) AS overdue,
                COUNT(
                    CASE
                        WHEN invoice_date>=:count_date_from
                         AND invoice_date<=:count_date_to
                        THEN 1
                    END
                ) AS document_count
             FROM report_supplier_documents
             WHERE supplier_id=:supplier_id"
        );

        $statement->execute([
            ':gross_date_from' => $dateFrom,
            ':gross_date_to' => $dateTo,
            ':net_date_from' => $dateFrom,
            ':net_date_to' => $dateTo,
            ':tax_date_from' => $dateFrom,
            ':tax_date_to' => $dateTo,
            ':outstanding_date' => $asOfDate,
            ':overdue_date' => $asOfDate,
            ':count_date_from' => $dateFrom,
            ':count_date_to' => $dateTo,
            ':supplier_id' => $supplierId,
        ]);

        $statistics =
            $statement->fetch(PDO::FETCH_ASSOC) ?: [];

        $target = $month > 0
            ? $this->monthlyTarget(
                $supplierId,
                $year,
                $month
            )
            : $this->annualTarget(
                $supplierId,
                $year
            );

        $netPurchases =
            (float) ($statistics['purchases_net'] ?? 0);
        $purchaseTarget =
            (float) ($target['purchase_target'] ?? 0);

        /*
         * Targetul de achiziții este comparat exclusiv
         * cu achizițiile fără TVA.
         */
        $statistics['purchases'] =
            (float) ($statistics['purchases_gross'] ?? 0);
        $statistics['purchase_target'] = $purchaseTarget;
        $statistics['target_progress'] =
            $purchaseTarget > 0
                ? ($netPurchases / $purchaseTarget) * 100
                : 0;
        $statistics['date_from'] = $dateFrom;
        $statistics['date_to'] = $dateTo;

        return $statistics;
    }

    public function supplierStatistics(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT
                COALESCE(SUM(
                    CASE
                        WHEN YEAR(invoice_date)=:year
                        THEN invoice_value
                        ELSE 0
                    END
                ),0) AS purchases,
                COALESCE(SUM(
                    CASE
                        WHEN outstanding_value>0
                        THEN outstanding_value
                        ELSE 0
                    END
                ),0) AS outstanding,
                COALESCE(SUM(
                    CASE
                        WHEN due_date<CURDATE()
                         AND outstanding_value>0
                        THEN outstanding_value
                        ELSE 0
                    END
                ),0) AS overdue,
                COUNT(*) AS document_count
             FROM report_supplier_documents
             WHERE supplier_id=:supplier_id"
        );
        $statement->execute([
            ':year' => $year,
            ':supplier_id' => $supplierId,
        ]);
        $statistics =
            $statement->fetch(PDO::FETCH_ASSOC) ?: [];

        $target =
            $this->annualTarget($supplierId, $year);

        $purchases =
            (float) ($statistics['purchases'] ?? 0);
        $purchaseTarget =
            (float) ($target['purchase_target'] ?? 0);

        $statistics['purchase_target'] =
            $purchaseTarget;
        $statistics['target_progress'] =
            $purchaseTarget > 0
                ? ($purchases / $purchaseTarget) * 100
                : 0;

        return $statistics;
    }

    public function monthlyStatistics(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT
                MONTH(invoice_date) AS month_number,
                COALESCE(SUM(invoice_value),0)
                    AS purchases_gross,
                COALESCE(SUM(
                    COALESCE(
                        net_value,
                        ROUND(invoice_value / 1.21, 2)
                    )
                ),0) AS purchases_net,
                COALESCE(SUM(
                    COALESCE(
                        tax_value,
                        invoice_value
                        - ROUND(invoice_value / 1.21, 2)
                    )
                ),0) AS purchases_tax
             FROM report_supplier_documents
             WHERE supplier_id=:supplier_id
               AND YEAR(invoice_date)=:year
             GROUP BY MONTH(invoice_date)
             ORDER BY month_number"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':year' => $year,
        ]);

        $indexed = [];

        foreach (
            $statement->fetchAll(PDO::FETCH_ASSOC) ?: []
            as $row
        ) {
            $indexed[(int) $row['month_number']] =
                $row;
        }

        $result = [];

        for ($month = 1; $month <= 12; $month++) {
            $target = $this->monthlyTarget(
                $supplierId,
                $year,
                $month
            );

            $result[] = [
                'month_number' => $month,
                'purchases' => (float) (
                    $indexed[$month]['purchases_gross'] ?? 0
                ),
                'purchases_gross' => (float) (
                    $indexed[$month]['purchases_gross'] ?? 0
                ),
                'purchases_net' => (float) (
                    $indexed[$month]['purchases_net'] ?? 0
                ),
                'purchases_tax' => (float) (
                    $indexed[$month]['purchases_tax'] ?? 0
                ),
                'target' => (float) (
                    $target['purchase_target'] ?? 0
                ),
            ];
        }

        return $result;
    }

    public function weeklyPaymentSchedule(
        int $supplierId,
        string $startDate,
        int $weeks
    ): array {
        $start = (
            new \DateTimeImmutable($startDate)
        )->modify('monday this week');

        $result = [];

        for ($index = 0; $index < $weeks; $index++) {
            $from =
                $start->modify('+' . $index . ' weeks');
            $to = $from->modify('+6 days');

            $statement = Database::prepare(
                "SELECT
                    COALESCE(SUM(
                        CASE
                            WHEN outstanding_value>0
                            THEN outstanding_value
                            ELSE 0
                        END
                    ),0) AS amount,
                    COUNT(*) AS documents
                 FROM report_supplier_documents
                 WHERE supplier_id=:supplier_id
                   AND due_date>=:date_from
                   AND due_date<=:date_to"
            );
            $statement->execute([
                ':supplier_id' => $supplierId,
                ':date_from' =>
                    $from->format('Y-m-d'),
                ':date_to' => $to->format('Y-m-d'),
            ]);
            $row =
                $statement->fetch(PDO::FETCH_ASSOC) ?: [];

            $result[] = [
                'week_number' => $index + 1,
                'date_from' =>
                    $from->format('Y-m-d'),
                'date_to' =>
                    $to->format('Y-m-d'),
                'amount' =>
                    (float) ($row['amount'] ?? 0),
                'documents' =>
                    (int) ($row['documents'] ?? 0),
            ];
        }

        return $result;
    }

    public function overdueAmount(
        int $supplierId,
        string $beforeDate
    ): float {
        $statement = Database::prepare(
            "SELECT COALESCE(SUM(
                CASE
                    WHEN outstanding_value>0
                    THEN outstanding_value
                    ELSE 0
                END
             ),0)
             FROM report_supplier_documents
             WHERE supplier_id=:supplier_id
               AND due_date<:before_date"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':before_date' => $beforeDate,
        ]);

        return (float) $statement->fetchColumn();
    }

    public function createImport(array $data): int
    {
        $statement = Database::prepare(
            "INSERT INTO report_supplier_imports
                (
                    supplier_id,
                    original_filename,
                    stored_file_path,
                    parser_code,
                    imported_rows,
                    skipped_rows,
                    period_year,
                    period_month,
                    coverage_type,
                    period_from,
                    period_to,
                    notes,
                    created_by,
                    created_at
                )
             VALUES
                (
                    :supplier_id,
                    :original_filename,
                    :stored_file_path,
                    :parser_code,
                    :imported_rows,
                    :skipped_rows,
                    :period_year,
                    :period_month,
                    :coverage_type,
                    :period_from,
                    :period_to,
                    :notes,
                    :created_by,
                    NOW()
                )"
        );
        $statement->execute([
            ':supplier_id' => $data['supplier_id'],
            ':original_filename' =>
                $data['original_filename'],
            ':stored_file_path' =>
                $data['stored_file_path'],
            ':parser_code' => $data['parser_code'],
            ':imported_rows' =>
                $data['imported_rows'],
            ':skipped_rows' =>
                $data['skipped_rows'],
            ':period_year' => $data['period_year'],
            ':period_month' => $data['period_month'],
            ':coverage_type' => $data['coverage_type'],
            ':period_from' => $data['period_from'],
            ':period_to' => $data['period_to'],
            ':notes' => $data['notes'],
            ':created_by' => $data['created_by'],
        ]);

        return (int) Database::lastInsertId();
    }

    public function resetOutstandingForSupplier(
        int $supplierId
    ): void {
        $statement = Database::prepare(
            "UPDATE report_supplier_documents
             SET outstanding_value=0,
                 updated_at=NOW()
             WHERE supplier_id=:supplier_id"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
        ]);
    }

    public function upsertDocuments(
        int $supplierId,
        int $importId,
        array $rows
    ): int {
        $statement = Database::prepare(
            "INSERT INTO report_supplier_documents
                (
                    supplier_id,
                    import_id,
                    invoice_number,
                    order_number,
                    invoice_date,
                    due_date,
                    invoice_value,
                    net_value,
                    tax_value,
                    outstanding_value,
                    penalties,
                    source_key,
                    raw_json,
                    created_at,
                    updated_at
                )
             VALUES
                (
                    :supplier_id,
                    :import_id,
                    :invoice_number,
                    :order_number,
                    :invoice_date,
                    :due_date,
                    :invoice_value,
                    :net_value,
                    :tax_value,
                    :outstanding_value,
                    :penalties,
                    :source_key,
                    :raw_json,
                    NOW(),
                    NOW()
                )
             ON DUPLICATE KEY UPDATE
                import_id=VALUES(import_id),
                order_number=VALUES(order_number),
                invoice_date=VALUES(invoice_date),
                due_date=VALUES(due_date),
                invoice_value=VALUES(invoice_value),
                net_value=VALUES(net_value),
                tax_value=VALUES(tax_value),
                outstanding_value=
                    VALUES(outstanding_value),
                penalties=VALUES(penalties),
                source_key=VALUES(source_key),
                raw_json=VALUES(raw_json),
                updated_at=NOW()"
        );

        $saved = 0;

        foreach ($rows as $row) {
            $statement->execute([
                ':supplier_id' => $supplierId,
                ':import_id' => $importId,
                ':invoice_number' =>
                    $row['invoice_number'],
                ':order_number' =>
                    $row['order_number'],
                ':invoice_date' =>
                    $row['invoice_date'],
                ':due_date' =>
                    $row['due_date'],
                ':invoice_value' =>
                    $row['invoice_value'],
                ':net_value' => $row['net_value']
                    ?? round(
                        (float) $row['invoice_value'] / 1.21,
                        2
                    ),
                ':tax_value' => $row['tax_value']
                    ?? round(
                        (float) $row['invoice_value']
                        - (
                            (float) $row['invoice_value'] / 1.21
                        ),
                        2
                    ),
                ':outstanding_value' =>
                    $row['outstanding_value'],
                ':penalties' =>
                    $row['penalties'],
                ':source_key' =>
                    $row['source_key'],
                ':raw_json' => json_encode(
                    $row['raw'],
                    JSON_UNESCAPED_UNICODE
                    | JSON_UNESCAPED_SLASHES
                ),
            ]);
            $saved++;
        }

        return $saved;
    }

    public function imports(int $supplierId): array
    {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_imports
             WHERE supplier_id=:supplier_id
             ORDER BY id DESC
             LIMIT 50"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
        ]);

        return $statement->fetchAll(PDO::FETCH_ASSOC)
            ?: [];
    }

    public function importsForYear(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_imports
             WHERE supplier_id=:supplier_id
               AND period_year=:period_year
             ORDER BY period_month, created_at DESC"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':period_year' => $year,
        ]);

        return $statement->fetchAll(PDO::FETCH_ASSOC)
            ?: [];
    }

    public function import(
        int $importId,
        int $supplierId
    ): ?array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_imports
             WHERE id=:id
               AND supplier_id=:supplier_id
             LIMIT 1"
        );
        $statement->execute([
            ':id' => $importId,
            ':supplier_id' => $supplierId,
        ]);

        return $statement->fetch(PDO::FETCH_ASSOC)
            ?: null;
    }

    public function importsByIds(
        int $supplierId,
        array $ids
    ): array {
        if ($ids === []) {
            return [];
        }

        $placeholders = implode(
            ',',
            array_fill(0, count($ids), '?')
        );

        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_imports
             WHERE supplier_id=?
               AND id IN ({$placeholders})
             ORDER BY period_year, period_month, created_at"
        );

        $statement->execute(
            array_merge([$supplierId], $ids)
        );

        return $statement->fetchAll(PDO::FETCH_ASSOC)
            ?: [];
    }

    public function monthlyChecklist(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_monthly_checklist
             WHERE supplier_id=:supplier_id
               AND checklist_year=:checklist_year"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':checklist_year' => $year,
        ]);

        $rows = [];

        foreach (
            $statement->fetchAll(PDO::FETCH_ASSOC) ?: []
            as $row
        ) {
            $rows[(int) $row['checklist_month']] = $row;
        }

        $imports = $this->importsForYear(
            $supplierId,
            $year
        );

        $importsByMonth = [];

        foreach ($imports as $import) {
            $month = (int) $import['period_month'];
            $importsByMonth[$month][] = $import;
        }

        $totalsStatement = Database::prepare(
            "SELECT
                MONTH(invoice_date) AS month_number,
                COALESCE(SUM(invoice_value),0)
                    AS total_gross,
                COALESCE(SUM(
                    COALESCE(
                        net_value,
                        ROUND(invoice_value / 1.21, 2)
                    )
                ),0) AS total_net
             FROM report_supplier_documents
             WHERE supplier_id=:supplier_id
               AND YEAR(invoice_date)=:invoice_year
             GROUP BY MONTH(invoice_date)"
        );
        $totalsStatement->execute([
            ':supplier_id' => $supplierId,
            ':invoice_year' => $year,
        ]);

        $totalsByMonth = [];

        foreach (
            $totalsStatement->fetchAll(PDO::FETCH_ASSOC) ?: []
            as $totalRow
        ) {
            $totalsByMonth[
                (int) $totalRow['month_number']
            ] = $totalRow;
        }

        $result = [];

        for ($month = 1; $month <= 12; $month++) {
            $row = $rows[$month] ?? [];
            $monthImports = $importsByMonth[$month] ?? [];
            $hasFull = false;
            $hasPartial = false;

            foreach ($monthImports as $import) {
                if (
                    ($import['coverage_type'] ?? 'full')
                    === 'partial'
                ) {
                    $hasPartial = true;
                } else {
                    $hasFull = true;
                }
            }

            $result[] = [
                'month' => $month,
                'imports' => $monthImports,
                'has_full' => $hasFull,
                'has_partial' => $hasPartial,
                'is_uploaded' =>
                    !empty($row['is_uploaded'])
                    || $monthImports !== [],
                'is_checked' =>
                    !empty($row['is_checked']),
                'is_sent_accounting' =>
                    !empty($row['is_sent_accounting']),
                'is_reconciled' =>
                    !empty($row['is_reconciled']),
                'total_net' => (float) (
                    $totalsByMonth[$month]['total_net']
                    ?? 0
                ),
                'total_gross' => (float) (
                    $totalsByMonth[$month]['total_gross']
                    ?? 0
                ),
                'notes' => (string) (
                    $row['notes'] ?? ''
                ),
            ];
        }

        return $result;
    }

    public function markChecklistUploaded(
        int $supplierId,
        int $year,
        int $month,
        int $importId
    ): void {
        $statement = Database::prepare(
            "INSERT INTO report_supplier_monthly_checklist
                (
                    supplier_id,
                    checklist_year,
                    checklist_month,
                    is_uploaded,
                    last_import_id,
                    created_at,
                    updated_at
                )
             VALUES
                (
                    :supplier_id,
                    :checklist_year,
                    :checklist_month,
                    1,
                    :last_import_id,
                    NOW(),
                    NOW()
                )
             ON DUPLICATE KEY UPDATE
                is_uploaded=1,
                last_import_id=VALUES(last_import_id),
                updated_at=NOW()"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':checklist_year' => $year,
            ':checklist_month' => $month,
            ':last_import_id' => $importId,
        ]);
    }

    public function saveChecklist(array $data): void
    {
        $statement = Database::prepare(
            "INSERT INTO report_supplier_monthly_checklist
                (
                    supplier_id,
                    checklist_year,
                    checklist_month,
                    is_checked,
                    is_sent_accounting,
                    is_reconciled,
                    notes,
                    updated_by,
                    created_at,
                    updated_at
                )
             VALUES
                (
                    :supplier_id,
                    :checklist_year,
                    :checklist_month,
                    :is_checked,
                    :is_sent_accounting,
                    :is_reconciled,
                    :notes,
                    :updated_by,
                    NOW(),
                    NOW()
                )
             ON DUPLICATE KEY UPDATE
                is_checked=VALUES(is_checked),
                is_sent_accounting=
                    VALUES(is_sent_accounting),
                is_reconciled=VALUES(is_reconciled),
                notes=VALUES(notes),
                updated_by=VALUES(updated_by),
                updated_at=NOW()"
        );
        $statement->execute([
            ':supplier_id' => $data['supplier_id'],
            ':checklist_year' => $data['checklist_year'],
            ':checklist_month' => $data['checklist_month'],
            ':is_checked' => $data['is_checked'],
            ':is_sent_accounting' =>
                $data['is_sent_accounting'],
            ':is_reconciled' => $data['is_reconciled'],
            ':notes' => $data['notes'],
            ':updated_by' => $data['updated_by'],
        ]);
    }

    public function targets(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_targets
             WHERE supplier_id=:supplier_id
               AND target_year=:year
             ORDER BY
                target_month IS NULL DESC,
                target_month"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':year' => $year,
        ]);

        return $statement->fetchAll(PDO::FETCH_ASSOC)
            ?: [];
    }

    public function saveTarget(array $data): void
    {
        if ($data['target_month'] === null) {
            $delete = Database::prepare(
                "DELETE FROM report_supplier_targets
                 WHERE supplier_id=:supplier_id
                   AND target_year=:year
                   AND target_month IS NULL"
            );
            $delete->execute([
                ':supplier_id' => $data['supplier_id'],
                ':year' => $data['target_year'],
            ]);
        }

        $statement = Database::prepare(
            "INSERT INTO report_supplier_targets
                (
                    supplier_id,
                    target_year,
                    target_month,
                    purchase_target,
                    payment_target,
                    notes,
                    created_at,
                    updated_at
                )
             VALUES
                (
                    :supplier_id,
                    :target_year,
                    :target_month,
                    :purchase_target,
                    :payment_target,
                    :notes,
                    NOW(),
                    NOW()
                )
             ON DUPLICATE KEY UPDATE
                purchase_target=
                    VALUES(purchase_target),
                payment_target=
                    VALUES(payment_target),
                notes=VALUES(notes),
                updated_at=NOW()"
        );
        $statement->execute([
            ':supplier_id' => $data['supplier_id'],
            ':target_year' => $data['target_year'],
            ':target_month' => $data['target_month'],
            ':purchase_target' =>
                $data['purchase_target'],
            ':payment_target' =>
                $data['payment_target'],
            ':notes' => $data['notes'],
        ]);
    }

    public function latestCreditSnapshot(
        int $supplierId,
        string $asOfDate
    ): ?array {
        $statement = Database::prepare(
            "SELECT
                *,
                GREATEST(
                    credit_limit_total - available_credit,
                    0
                ) AS used_credit
             FROM report_supplier_credit_snapshots
             WHERE supplier_id=:supplier_id
               AND snapshot_date<=:snapshot_date
             ORDER BY snapshot_date DESC, id DESC
             LIMIT 1"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':snapshot_date' => $asOfDate,
        ]);

        return $statement->fetch(PDO::FETCH_ASSOC)
            ?: null;
    }

    public function saveCreditSnapshot(array $data): void
    {
        $statement = Database::prepare(
            "INSERT INTO report_supplier_credit_snapshots
                (
                    supplier_id,
                    snapshot_date,
                    credit_limit_total,
                    available_credit,
                    notes,
                    created_by,
                    created_at,
                    updated_at
                )
             VALUES
                (
                    :supplier_id,
                    :snapshot_date,
                    :credit_limit_total,
                    :available_credit,
                    :notes,
                    :created_by,
                    NOW(),
                    NOW()
                )
             ON DUPLICATE KEY UPDATE
                credit_limit_total=VALUES(credit_limit_total),
                available_credit=VALUES(available_credit),
                notes=VALUES(notes),
                created_by=VALUES(created_by),
                updated_at=NOW()"
        );
        $statement->execute([
            ':supplier_id' => $data['supplier_id'],
            ':snapshot_date' => $data['snapshot_date'],
            ':credit_limit_total' =>
                $data['credit_limit_total'],
            ':available_credit' =>
                $data['available_credit'],
            ':notes' => $data['notes'],
            ':created_by' => $data['created_by'],
        ]);
    }

    private function annualTarget(
        int $supplierId,
        int $year
    ): array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_targets
             WHERE supplier_id=:supplier_id
               AND target_year=:year
               AND target_month IS NULL
             LIMIT 1"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':year' => $year,
        ]);

        return $statement->fetch(PDO::FETCH_ASSOC)
            ?: [];
    }

    private function monthlyTarget(
        int $supplierId,
        int $year,
        int $month
    ): array {
        $statement = Database::prepare(
            "SELECT *
             FROM report_supplier_targets
             WHERE supplier_id=:supplier_id
               AND target_year=:year
               AND target_month=:month
             LIMIT 1"
        );
        $statement->execute([
            ':supplier_id' => $supplierId,
            ':year' => $year,
            ':month' => $month,
        ]);

        return $statement->fetch(PDO::FETCH_ASSOC)
            ?: [];
    }
}