<?php
namespace App\Services;

use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Settings;

/**
 * Laporan Kualitas Data.
 *
 * Menjalankan pemeriksaan berkelanjutan atas seluruh masalah yang ditemukan pada
 * workbook Excel asal (DQ-01 s/d DQ-33) sehingga masalah serupa tidak terulang
 * dan invoice yang bermasalah dapat ditindaklanjuti.
 */
class DataQualityService
{
    /** Label ramah untuk tanda kualitas data yang tersimpan di invoices.dq_flags. */
    public const FLAG_LABELS = [
        'TGL_KIRIM_KOSONG'          => 'Tanggal kirim invoice kosong',
        'KIRIM_SEBELUM_INVOICE'     => 'Tanggal kirim lebih awal dari tanggal invoice',
        'KIRIM_TERLALU_LAMA'        => 'Selisih tanggal kirim > 90 hari dari tanggal invoice',
        'FP_KOSONG'                 => 'Nomor faktur pajak kosong',
        'FP_FORMAT'                 => 'Format nomor faktur pajak tidak standar',
        'FP_DUPLIKAT_FILE'          => 'Nomor faktur pajak duplikat di berkas import',
        'FP_DUPLIKAT_DB'            => 'Nomor faktur pajak duplikat di database',
        'PPH23_TARIF_NONSTANDAR'    => 'Tarif PPh 23 berbeda dari tarif standar',
        'PPH23_TARIF_TINGGI'        => 'Tarif PPh 23 melebihi batas kewajaran',
        'KELEBIHAN_BAYAR'           => 'Pembayaran melebihi nilai tagihan',
        'BAYAR_SEBELUM_INVOICE'     => 'Tanggal bayar lebih awal dari tanggal invoice',
        'BAYAR_TANPA_TANGGAL'       => 'Pembayaran tanpa tanggal bayar',
        'TGL_BAYAR_TANPA_NOMINAL'   => 'Tanggal bayar terisi tanpa nominal pembayaran',
        'NOMINAL_TEKS'              => 'Nominal invoice berformat teks',
        'TGL_INVOICE_MASA_DEPAN'    => 'Tanggal invoice lebih dari 1 tahun ke depan',
        'TGL_INVOICE_DARI_CADANGAN' => 'Tanggal invoice diambil dari tanggal cadangan',
        'EXCEL_ERROR_GROSS_AMOUNT'  => 'Sel nominal berisi nilai error Excel',
        'EXCEL_ERROR_INVOICE_DATE'  => 'Sel tanggal invoice berisi nilai error Excel',
    ];

    /** Daftar pemeriksaan aktif. */
    public static function checks(): array
    {
        $tol = Settings::float('ar.toleransi_lunas', 1);
        $std = Settings::float('pajak.tarif_pph23', 2);

        return [
            'saldo_negatif' => [
                'label' => 'Saldo piutang negatif (kelebihan bayar)',
                'tingkat' => 'tinggi',
                'saran' => 'Terbitkan nota kredit atau proses refund atas kelebihan bayar.',
                'sql' => "i.outstanding_amount < -$tol",
            ],
            'status_tidak_konsisten' => [
                'label' => 'Status pembayaran tidak sesuai saldo',
                'tingkat' => 'kritis',
                'saran' => 'Jalankan Hitung Ulang Piutang pada menu Pengaturan.',
                'sql' => "((i.payment_status_code = 'LUNAS' AND i.outstanding_amount > $tol)
                        OR (i.payment_status_code <> 'LUNAS' AND i.doc_status = 'active' AND i.outstanding_amount <= $tol))",
            ],
            'bayar_lebih_tagihan' => [
                'label' => 'Pembayaran melebihi nilai tagihan',
                'tingkat' => 'tinggi',
                'saran' => 'Periksa alokasi pembayaran; kemungkinan pembayaran salah invoice.',
                'sql' => 'i.paid_amount > i.net_amount + 0.5',
            ],
            'tanpa_tanggal_kirim' => [
                'label' => 'Tanggal kirim invoice kosong (jatuh tempo memakai tanggal cadangan)',
                'tingkat' => 'sedang',
                'saran' => 'Lengkapi tanggal kirim agar jatuh tempo akurat.',
                'sql' => 'i.sent_date IS NULL',
            ],
            'kirim_sebelum_invoice' => [
                'label' => 'Tanggal kirim lebih awal dari tanggal invoice',
                'tingkat' => 'sedang',
                'saran' => 'Perbaiki salah satu tanggal.',
                'sql' => 'i.sent_date IS NOT NULL AND i.sent_date < i.invoice_date',
            ],
            'kirim_terlalu_lama' => [
                'label' => 'Selisih tanggal kirim dan tanggal invoice lebih dari 90 hari',
                'tingkat' => 'sedang',
                'saran' => 'Periksa keterlambatan proses penerbitan/pengiriman invoice.',
                'sql' => 'i.sent_date IS NOT NULL AND DATEDIFF(i.sent_date, i.invoice_date) > 90',
            ],
            'tanpa_faktur_pajak' => [
                'label' => 'Nomor faktur pajak kosong',
                'tingkat' => 'sedang',
                'saran' => 'Lengkapi nomor faktur pajak untuk kepatuhan perpajakan.',
                'sql' => "(i.tax_invoice_no IS NULL OR i.tax_invoice_no = '')",
            ],
            'faktur_pajak_invalid' => [
                'label' => 'Format nomor faktur pajak tidak standar (bukan 16–17 digit)',
                'tingkat' => 'sedang',
                'saran' => 'Perbaiki penulisan nomor faktur pajak.',
                'sql' => "i.tax_invoice_no IS NOT NULL AND i.tax_invoice_no <> '' AND i.tax_invoice_no NOT REGEXP '^[0-9]{16,17}$'",
            ],
            'pph_nonstandar' => [
                'label' => 'Tarif efektif PPh 23 berbeda dari tarif standar (' . Num::fmt($std, 2) . '%)',
                'tingkat' => 'sedang',
                'saran' => 'Verifikasi bukti potong PPh 23 dari customer.',
                'sql' => "i.pph23_amount > 0 AND ABS(i.pph23_rate - $std) > 0.05",
            ],
            'pph_terlalu_tinggi' => [
                'label' => 'Tarif efektif PPh 23 melebihi batas kewajaran',
                'tingkat' => 'tinggi',
                'saran' => 'Periksa kemungkinan salah input nominal potongan.',
                'sql' => 'i.pph23_amount > 0 AND i.pph23_rate > ' . Settings::float('pajak.batas_wajar_pph23', 20),
            ],
            'potongan_melebihi' => [
                'label' => 'Total potongan melebihi nominal invoice',
                'tingkat' => 'kritis',
                'saran' => 'Koreksi nilai potongan pada invoice.',
                'sql' => 'i.total_deduction > i.gross_amount',
            ],
            'dibatalkan_masih_ar' => [
                'label' => 'Invoice dibatalkan/diganti tetapi masih memiliki saldo piutang',
                'tingkat' => 'kritis',
                'saran' => 'Jalankan Hitung Ulang Piutang; saldo seharusnya nol.',
                'sql' => "i.doc_status <> 'active' AND i.outstanding_amount > $tol",
            ],
            'revenue_dobel' => [
                'label' => 'Invoice lama sudah diganti tetapi masih dihitung sebagai revenue',
                'tingkat' => 'kritis',
                'saran' => 'Terapkan ulang revisi RF terkait.',
                'sql' => "i.doc_status IN ('replaced','revised','cancelled','written_off') AND i.is_revenue_counted = 1",
            ],
            'jatuh_tempo_sebelum_invoice' => [
                'label' => 'Jatuh tempo lebih awal dari tanggal invoice',
                'tingkat' => 'tinggi',
                'saran' => 'Periksa tanggal dasar dan termin pembayaran.',
                'sql' => 'i.due_date < i.invoice_date',
            ],
            'nominal_nol' => [
                'label' => 'Nominal invoice nol atau negatif',
                'tingkat' => 'kritis',
                'saran' => 'Lengkapi nilai invoice atau batalkan invoice.',
                'sql' => 'i.gross_amount <= 0',
            ],
            'tanpa_customer' => [
                'label' => 'Invoice tanpa customer',
                'tingkat' => 'kritis',
                'saran' => 'Tetapkan customer pada invoice.',
                'sql' => 'i.customer_id IS NULL',
            ],
            'tanpa_area' => [
                'label' => 'Invoice tanpa area',
                'tingkat' => 'rendah',
                'saran' => 'Lengkapi area agar laporan per wilayah akurat.',
                'sql' => 'i.area_id IS NULL',
            ],
            'tanpa_kategori_pendapatan' => [
                'label' => 'Invoice tanpa kategori PMSC / By Case',
                'tingkat' => 'rendah',
                'saran' => 'Lengkapi kategori pendapatan.',
                'sql' => "i.revenue_category_id IS NULL OR i.revenue_category_id = (SELECT id FROM revenue_categories WHERE code='TIDAK_DIKATEGORIKAN' LIMIT 1)",
            ],
            'bayar_sebelum_invoice' => [
                'label' => 'Tanggal pembayaran lebih awal dari tanggal invoice',
                'tingkat' => 'sedang',
                'saran' => 'Periksa apakah pembayaran adalah uang muka atau salah tanggal.',
                'sql' => 'i.first_payment_date IS NOT NULL AND i.first_payment_date < i.invoice_date',
            ],
            'terlambat_lama' => [
                'label' => 'Piutang terlambat lebih dari 120 hari',
                'tingkat' => 'tinggi',
                'saran' => 'Pertimbangkan eskalasi penagihan atau penyisihan piutang.',
                'sql' => "i.outstanding_amount > $tol AND DATEDIFF(CURDATE(), i.due_date) > 120",
            ],
        ];
    }

    /** Ringkasan semua pemeriksaan. */
    public static function summary(array $filters = []): array
    {
        $db = Database::i();
        [$w, $p] = ArCalculator::buildFilterSql($filters, 'i');
        $out = [];
        foreach (self::checks() as $key => $c) {
            $r = $db->one(
                "SELECT COUNT(*) AS jumlah, COALESCE(SUM(i.gross_amount),0) AS nilai_bruto,
                        COALESCE(SUM(i.outstanding_amount),0) AS nilai_saldo
                   FROM invoices i WHERE i.deleted_at IS NULL AND $w AND ({$c['sql']})",
                $p
            );
            $out[$key] = array_merge($c, [
                'kode' => $key,
                'jumlah' => (int)$r['jumlah'],
                'nilai_bruto' => (float)$r['nilai_bruto'],
                'nilai_saldo' => (float)$r['nilai_saldo'],
            ]);
        }
        uasort($out, function ($a, $b) {
            $rank = ['kritis' => 0, 'tinggi' => 1, 'sedang' => 2, 'rendah' => 3];
            $c = ($rank[$a['tingkat']] ?? 9) <=> ($rank[$b['tingkat']] ?? 9);
            return $c !== 0 ? $c : ($b['jumlah'] <=> $a['jumlah']);
        });
        return $out;
    }

    /** Daftar invoice yang gagal satu pemeriksaan tertentu. */
    public static function detail(string $checkKey, array $filters = [], int $page = 1, int $perPage = 50): array
    {
        $checks = self::checks();
        if (!isset($checks[$checkKey])) return ['data' => [], 'total' => 0, 'halaman' => 1, 'total_halaman' => 1, 'per_halaman' => $perPage, 'dari' => 0, 'sampai' => 0];
        [$w, $p] = ArCalculator::buildFilterSql($filters, 'i');
        $c = $checks[$checkKey];
        $sql = 'SELECT ' . \App\Models\Invoice::SELECT_COLS . \App\Models\Invoice::SELECT_JOIN
             . " WHERE i.deleted_at IS NULL AND $w AND ({$c['sql']}) ORDER BY i.outstanding_amount DESC, i.invoice_date DESC";
        $countSql = 'SELECT COUNT(*)' . \App\Models\Invoice::SELECT_JOIN . " WHERE i.deleted_at IS NULL AND $w AND ({$c['sql']})";
        $m = new \App\Models\Invoice();
        $res = $m->paginate($sql, $p, $page, $perPage, $countSql);
        $res['pemeriksaan'] = $c;
        return $res;
    }

    /** Rekapitulasi tanda kualitas data dari proses import. */
    public static function importFlags(): array
    {
        $rows = Database::i()->column("SELECT dq_flags FROM invoices WHERE deleted_at IS NULL AND dq_flags IS NOT NULL AND dq_flags <> '[]'");
        $count = [];
        foreach ($rows as $j) {
            foreach (arr($j) as $f) $count[$f] = ($count[$f] ?? 0) + 1;
        }
        arsort($count);
        $out = [];
        foreach ($count as $k => $v) {
            $out[] = ['kode' => $k, 'label' => self::FLAG_LABELS[$k] ?? str_replace('_', ' ', $k), 'jumlah' => $v];
        }
        return $out;
    }

    /** Rekonsiliasi nilai sistem vs nilai rumus Excel yang tersimpan di import_rows. */
    public static function excelReconciliation(int $limit = 100): array
    {
        $db = Database::i();
        if (!$db->tableExists('import_rows')) return [];
        $rows = $db->all(
            "SELECT ir.row_number, ir.raw_json, ib.sheet_name, i.invoice_no, i.net_amount, i.outstanding_amount,
                    i.gross_amount, i.total_deduction, i.paid_amount
               FROM import_rows ir
               JOIN import_batches ib ON ib.id = ir.import_batch_id
               JOIN invoices i ON i.id = ir.invoice_id
              WHERE ir.invoice_id IS NOT NULL AND ib.import_type = 'invoice'
                AND ib.status <> 'rolled_back' AND i.deleted_at IS NULL
              ORDER BY ir.id"
        );
        $diff = [];
        foreach ($rows as $r) {
            $raw = arr($r['raw_json']);
            $excelTotal = isset($raw['W']) ? Num::parse((string)$raw['W']) : null;
            $excelSaldo = isset($raw['Y']) ? Num::parse((string)$raw['Y']) : null;
            $dTotal = $excelTotal === null ? null : round((float)$r['net_amount'] - $excelTotal, 2);
            $dSaldo = $excelSaldo === null ? null : round((float)$r['outstanding_amount'] - $excelSaldo, 2);
            if (($dTotal !== null && abs($dTotal) > 1) || ($dSaldo !== null && abs($dSaldo) > 1)) {
                $diff[] = [
                    'sheet' => $r['sheet_name'], 'baris' => (int)$r['row_number'], 'invoice_no' => $r['invoice_no'],
                    'net_sistem' => (float)$r['net_amount'], 'net_excel' => $excelTotal, 'selisih_net' => $dTotal,
                    'saldo_sistem' => (float)$r['outstanding_amount'], 'saldo_excel' => $excelSaldo, 'selisih_saldo' => $dSaldo,
                ];
            }
            if (count($diff) >= $limit) break;
        }
        return $diff;
    }

    /** Deteksi nomor invoice/faktur pajak duplikat yang tersisa di sistem. */
    public static function duplicates(): array
    {
        $db = Database::i();
        return [
            'faktur_pajak' => $db->all(
                "SELECT tax_invoice_no, COUNT(*) AS jumlah,
                        GROUP_CONCAT(invoice_no ORDER BY invoice_no SEPARATOR ', ') AS daftar_invoice
                   FROM invoices
                  WHERE deleted_at IS NULL AND tax_invoice_no IS NOT NULL AND tax_invoice_no <> ''
                  GROUP BY tax_invoice_no HAVING COUNT(*) > 1 ORDER BY jumlah DESC LIMIT 100"
            ),
            'so' => $db->all(
                "SELECT so_no, COUNT(*) AS jumlah,
                        GROUP_CONCAT(invoice_no ORDER BY invoice_no SEPARATOR ', ') AS daftar_invoice
                   FROM invoices
                  WHERE deleted_at IS NULL AND so_no IS NOT NULL AND so_no <> ''
                  GROUP BY so_no HAVING COUNT(*) > 2 ORDER BY jumlah DESC LIMIT 100"
            ),
        ];
    }

    /** Menjalankan perbaikan otomatis yang aman. */
    public static function autoFix(): array
    {
        $db = Database::i();
        $out = [];
        // 1. Invoice non-aktif yang masih punya saldo -> hitung ulang
        $ids = array_map('intval', $db->column(
            "SELECT id FROM invoices WHERE deleted_at IS NULL AND doc_status <> 'active' AND outstanding_amount > 1"
        ));
        foreach ($ids as $id) ArCalculator::recalculate($id);
        $out['saldo_invoice_nonaktif_dinolkan'] = count($ids);

        // 2. Invoice diganti tetapi masih dihitung revenue
        $n = $db->exec("UPDATE invoices SET is_revenue_counted = 0, revenue_amount = 0
                         WHERE deleted_at IS NULL AND doc_status IN ('replaced','revised','cancelled','written_off')
                           AND is_revenue_counted = 1");
        $out['revenue_dobel_diperbaiki'] = $n;

        // 3. Status pembayaran tidak konsisten
        $n2 = ArCalculator::refreshAgingOnly();
        $out['status_disegarkan'] = (int)$db->value('SELECT COUNT(*) FROM invoices WHERE deleted_at IS NULL');

        // 4. Mencocokkan ulang revisi yang tertunda
        $out['revisi_dicocokkan_ulang'] = RevisionService::rematchPending();

        // 5. Mengevaluasi janji bayar yang lewat
        $out['janji_bayar_dievaluasi'] = array_sum(CollectionService::evaluatePromises());

        \App\Core\Audit::log('kualitas_data', 'recalculate', null, null, 'Perbaikan otomatis kualitas data dijalankan', null, $out);
        return $out;
    }
}
