<?php
namespace App\Services;

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

/**
 * Modul Revisi Invoice (RF).
 *
 * Menegakkan aturan R-12: mencegah pendapatan & piutang dihitung dua kali antara
 * invoice lama dan invoice pengganti. Riwayat invoice lama tidak pernah dihapus.
 */
class RevisionService
{
    public const ACTIONS = [
        'replaced'    => 'Diganti Invoice Baru',
        'cancelled'   => 'Dibatalkan',
        'revised'     => 'Direvisi',
        'written_off' => 'Dihapuskan (Bad Debt)',
    ];

    /**
     * Menerapkan sebuah revisi yang sudah disetujui.
     * Seluruh perubahan dijalankan dalam satu transaksi database.
     */
    public static function apply(int $revisionId, bool $force = false): array
    {
        $db = Database::i();
        return $db->transaction(function () use ($db, $revisionId, $force) {
            $rev = $db->one('SELECT * FROM invoice_revisions WHERE id = :id AND deleted_at IS NULL', ['id' => $revisionId]);
            if (!$rev) throw new \RuntimeException('Data revisi tidak ditemukan.');
            if (!empty($rev['applied_at']) && !$force) {
                return ['sukses' => false, 'pesan' => 'Revisi ini sudah diterapkan sebelumnya.'];
            }

            $oldId = $rev['old_invoice_id'] ? (int)$rev['old_invoice_id'] : null;
            $newId = $rev['new_invoice_id'] ? (int)$rev['new_invoice_id'] : null;

            // Pencocokan ulang bila belum ter-resolve
            if (!$oldId && $rev['old_invoice_no']) {
                $r = $db->one('SELECT id FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $rev['old_invoice_no']]);
                $oldId = $r ? (int)$r['id'] : null;
            }
            if (!$newId && $rev['new_invoice_no']) {
                $r = $db->one('SELECT id FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $rev['new_invoice_no']]);
                $newId = $r ? (int)$r['id'] : null;
            }
            if (!$oldId) {
                $db->update('invoice_revisions', [
                    'status' => 'menunggu_pencocokan',
                    'match_notes' => 'Invoice lama ' . $rev['old_invoice_no'] . ' belum ada di database.',
                ], 'id = :pk', ['pk' => $revisionId]);
                return ['sukses' => false, 'pesan' => 'Invoice lama belum ditemukan di database. Revisi ditandai menunggu pencocokan.'];
            }

            $old = $db->one('SELECT * FROM invoices WHERE id = :id', ['id' => $oldId]);
            $action = (string)$rev['old_invoice_action'];
            $revenueBefore = (float)$old['revenue_amount'];
            $outstandingBefore = (float)$old['outstanding_amount'];

            // --- Invoice lama keluar dari AR aktif & dari pengakuan pendapatan
            $db->update('invoices', [
                'doc_status' => $action,
                'is_revenue_counted' => 0,
                'replaced_by_invoice_id' => $newId,
                'revision_id' => $revisionId,
                'updated_by' => Auth::check() ? Auth::id() : null,
            ], 'id = :pk', ['pk' => $oldId]);

            // --- Invoice pengganti menunjuk balik ke invoice lama
            if ($newId) {
                $db->update('invoices', [
                    'replaces_invoice_id' => $oldId,
                    'revision_id' => $revisionId,
                    'is_revenue_counted' => 1,
                    'updated_by' => Auth::check() ? Auth::id() : null,
                ], 'id = :pk', ['pk' => $newId]);
            }

            $db->update('invoice_revisions', [
                'old_invoice_id' => $oldId,
                'new_invoice_id' => $newId,
                'status' => 'approved',
                'applied_at' => Dates::now(),
                'approved_by' => $rev['approved_by'] ?: (Auth::check() ? Auth::id() : null),
                'approved_at' => $rev['approved_at'] ?: Dates::now(),
                'revised_amount' => $newId ? (float)$db->value('SELECT gross_amount FROM invoices WHERE id = ?', [$newId]) : 0,
                'match_notes' => null,
            ], 'id = :pk', ['pk' => $revisionId]);

            // Hitung ulang kedua invoice
            ArCalculator::recalculate($oldId);
            if ($newId) ArCalculator::recalculate($newId);

            Audit::log('revisi', 'approve', 'invoice_revision', $revisionId,
                'Revisi ' . $rev['rf_no'] . ' diterapkan: invoice ' . $rev['old_invoice_no'] . ' -> ' . self::ACTIONS[$action],
                ['doc_status' => $old['doc_status'], 'is_revenue_counted' => $old['is_revenue_counted'], 'outstanding_amount' => $outstandingBefore],
                ['doc_status' => $action, 'is_revenue_counted' => 0, 'replaced_by_invoice_id' => $newId]);

            return [
                'sukses' => true,
                'pesan' => 'Revisi berhasil diterapkan.',
                'old_invoice_id' => $oldId,
                'new_invoice_id' => $newId,
                'revenue_dicegah' => $revenueBefore,
                'ar_dikeluarkan' => $outstandingBefore,
            ];
        });
    }

    /** Membatalkan penerapan revisi (mengembalikan invoice lama menjadi aktif). */
    public static function unapply(int $revisionId, string $reason = ''): array
    {
        $db = Database::i();
        return $db->transaction(function () use ($db, $revisionId, $reason) {
            $rev = $db->one('SELECT * FROM invoice_revisions WHERE id = :id', ['id' => $revisionId]);
            if (!$rev) throw new \RuntimeException('Data revisi tidak ditemukan.');
            $oldId = (int)$rev['old_invoice_id'];
            $newId = $rev['new_invoice_id'] ? (int)$rev['new_invoice_id'] : null;
            if ($oldId) {
                $db->update('invoices', [
                    'doc_status' => 'active', 'is_revenue_counted' => 1,
                    'replaced_by_invoice_id' => null, 'revision_id' => null,
                ], 'id = :pk', ['pk' => $oldId]);
                ArCalculator::recalculate($oldId);
            }
            if ($newId) {
                $db->update('invoices', ['replaces_invoice_id' => null, 'revision_id' => null], 'id = :pk', ['pk' => $newId]);
                ArCalculator::recalculate($newId);
            }
            $db->update('invoice_revisions', ['status' => 'rejected', 'applied_at' => null,
                'match_notes' => mb_substr('Penerapan dibatalkan: ' . $reason, 0, 400)], 'id = :pk', ['pk' => $revisionId]);
            Audit::log('revisi', 'reject', 'invoice_revision', $revisionId, 'Penerapan revisi dibatalkan. ' . $reason);
            return ['sukses' => true, 'pesan' => 'Penerapan revisi dibatalkan.'];
        });
    }

    /** Menerapkan otomatis seluruh revisi yang invoice-nya sudah dapat dicocokkan. */
    public static function autoApplyMatched(): array
    {
        $db = Database::i();
        $rows = $db->all(
            "SELECT * FROM invoice_revisions
              WHERE deleted_at IS NULL AND applied_at IS NULL
                AND status IN ('pending','menunggu_pencocokan','approved')
              ORDER BY rf_date, id"
        );
        $ok = 0; $pending = 0; $prevented = 0.0;
        foreach ($rows as $r) {
            try {
                $res = self::apply((int)$r['id']);
                if (!empty($res['sukses'])) { $ok++; $prevented += (float)($res['revenue_dicegah'] ?? 0); }
                else $pending++;
            } catch (\Throwable $e) {
                $pending++;
                \App\Core\Logger::warning('Gagal menerapkan revisi #' . $r['id'] . ': ' . $e->getMessage());
            }
        }
        return ['diterapkan' => $ok, 'tertunda' => $pending, 'revenue_dicegah' => $prevented];
    }

    /** Mencoba mencocokkan ulang seluruh revisi yang tertunda. */
    public static function rematchPending(): int
    {
        $db = Database::i();
        $rows = $db->all("SELECT * FROM invoice_revisions WHERE deleted_at IS NULL AND status = 'menunggu_pencocokan'");
        $n = 0;
        foreach ($rows as $r) {
            $old = $r['old_invoice_no'] ? $db->one('SELECT id FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $r['old_invoice_no']]) : null;
            $new = $r['new_invoice_no'] ? $db->one('SELECT id FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL', ['n' => $r['new_invoice_no']]) : null;
            if ($old) {
                $db->update('invoice_revisions', [
                    'old_invoice_id' => (int)$old['id'],
                    'new_invoice_id' => $new ? (int)$new['id'] : null,
                    'status' => 'pending',
                    'match_notes' => null,
                ], 'id = :pk', ['pk' => (int)$r['id']]);
                $n++;
            }
        }
        return $n;
    }

    /** Daftar revisi dengan filter. */
    public static function listing(array $f = [], string $sort = 'rf_date', string $dir = 'desc', int $page = 1, int $perPage = 50): array
    {
        $db = Database::i();
        $w = ['r.deleted_at IS NULL'];
        $p = [];
        if (!empty($f['cari'])) {
            $w[] = '(r.rf_no LIKE :q OR r.old_invoice_no LIKE :q OR r.new_invoice_no LIKE :q OR r.reason LIKE :q)';
            $p['q'] = '%' . $f['cari'] . '%';
        }
        if (!empty($f['status']))            { $w[] = 'r.status = :st'; $p['st'] = $f['status']; }
        if (!empty($f['revision_type_id']))  { $w[] = 'r.revision_type_id = :rt'; $p['rt'] = (int)$f['revision_type_id']; }
        if (!empty($f['action']))            { $w[] = 'r.old_invoice_action = :ac'; $p['ac'] = $f['action']; }
        if (!empty($f['dari']))              { $w[] = 'r.rf_date >= :d1'; $p['d1'] = $f['dari']; }
        if (!empty($f['sampai']))            { $w[] = 'r.rf_date <= :d2'; $p['d2'] = $f['sampai']; }

        $sortable = ['rf_no' => 'r.rf_no', 'rf_date' => 'r.rf_date', 'old_invoice_no' => 'r.old_invoice_no',
                     'amount' => 'r.original_amount', 'status' => 'r.status'];
        $col = $sortable[$sort] ?? 'r.rf_date';
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';
        $where = implode(' AND ', $w);

        $sql = 'SELECT r.*, rt.name AS jenis_revisi,
                       oi.customer_id AS old_customer_id, oc.name AS customer_name,
                       oi.gross_amount AS old_gross, ni.gross_amount AS new_gross,
                       u.name AS requested_by_name, ua.name AS approved_by_name
                  FROM invoice_revisions r
                  LEFT JOIN revision_types rt ON rt.id = r.revision_type_id
                  LEFT JOIN invoices oi ON oi.id = r.old_invoice_id
                  LEFT JOIN invoices ni ON ni.id = r.new_invoice_id
                  LEFT JOIN customers oc ON oc.id = oi.customer_id
                  LEFT JOIN users u  ON u.id = r.requested_by
                  LEFT JOIN users ua ON ua.id = r.approved_by
                 WHERE ' . $where . " ORDER BY $col $dir, r.id DESC";
        $countSql = 'SELECT COUNT(*) FROM invoice_revisions r WHERE ' . $where;

        $m = new \App\Models\InvoiceRevision();
        return $m->paginate($sql, $p, $page, $perPage, $countSql);
    }

    public static function statistics(): array
    {
        $db = Database::i();
        $s = (array)$db->one(
            "SELECT COUNT(*) AS total,
                    SUM(status='approved') AS disetujui,
                    SUM(status='pending') AS menunggu,
                    SUM(status='menunggu_pencocokan') AS belum_cocok,
                    SUM(status='rejected') AS ditolak,
                    SUM(old_invoice_action='replaced') AS diganti,
                    SUM(old_invoice_action='cancelled') AS dibatalkan,
                    SUM(old_invoice_action='written_off') AS dihapuskan,
                    COALESCE(SUM(original_amount),0) AS nilai_lama,
                    COALESCE(SUM(revised_amount),0) AS nilai_baru
               FROM invoice_revisions WHERE deleted_at IS NULL"
        );
        $s['revenue_dicegah'] = (float)$db->value(
            "SELECT COALESCE(SUM(i.dpp_amount),0) FROM invoices i
              WHERE i.deleted_at IS NULL AND i.is_revenue_counted = 0
                AND i.doc_status IN ('replaced','cancelled','revised','written_off')"
        );
        return $s;
    }

    /** Nomor RF berikutnya. */
    public static function nextRfNo(): string
    {
        $prefix = Settings::str('ar.prefix_rf', 'RF');
        $yy = date('y');
        $last = Database::i()->value(
            'SELECT rf_no FROM invoice_revisions WHERE rf_no LIKE :p ORDER BY rf_no DESC LIMIT 1',
            ['p' => "$prefix/$yy/%"]
        );
        $n = 1;
        if ($last && preg_match('#/(\d+)$#', (string)$last, $m)) $n = (int)$m[1] + 1;
        return sprintf('%s/%s/%04d', $prefix, $yy, $n);
    }

    /** Deteksi celah penomoran RF (DQ: RF/25/0025 hilang). */
    public static function numberingGaps(): array
    {
        $rows = Database::i()->column('SELECT rf_no FROM invoice_revisions WHERE deleted_at IS NULL ORDER BY rf_no');
        $bySeries = [];
        foreach ($rows as $no) {
            if (preg_match('#^([A-Z]+)/(\d{2})/(\d+)$#', (string)$no, $m)) {
                $bySeries[$m[1] . '/' . $m[2]][] = (int)$m[3];
            }
        }
        $gaps = [];
        foreach ($bySeries as $series => $nums) {
            sort($nums);
            for ($i = min($nums); $i <= max($nums); $i++) {
                if (!in_array($i, $nums, true)) $gaps[] = sprintf('%s/%04d', $series, $i);
            }
        }
        return $gaps;
    }
}
