<?php
namespace App\Models;

use App\Core\Acl;
use App\Core\Database;
use App\Core\Dates;
use App\Core\Model;
use App\Core\Settings;
use App\Services\ArCalculator;

/** Model tabel `invoices` beserta query daftar & agregat. */
class Invoice extends Model
{
    protected string $table = 'invoices';

    /** SELECT dengan seluruh join deskriptif. */
    public const SELECT_JOIN = "
        FROM invoices i
        LEFT JOIN customers c   ON c.id = i.customer_id
        LEFT JOIN outlets   o   ON o.id = i.outlet_id
        LEFT JOIN areas     a   ON a.id = i.area_id
        LEFT JOIN business_units bu ON bu.id = i.business_unit_id
        LEFT JOIN salespersons s ON s.id = i.salesperson_id
        LEFT JOIN collection_officers co ON co.id = i.collection_officer_id
        LEFT JOIN job_categories jc ON jc.id = i.job_category_id
        LEFT JOIN job_types jt  ON jt.id = i.job_type_id
        LEFT JOIN revenue_categories rc ON rc.id = i.revenue_category_id
        LEFT JOIN payment_statuses ps ON ps.code = i.payment_status_code";

    public const SELECT_COLS = "i.*,
        c.name AS customer_name, c.code AS customer_code, c.npwp AS customer_npwp,
        o.name AS outlet_name, a.name AS area_name, bu.name AS business_unit_name,
        s.name AS salesperson_name, co.name AS collection_officer_name,
        jc.name AS job_category_name, jt.name AS job_type_name, rc.name AS revenue_category_name,
        ps.name AS payment_status_name, ps.color AS payment_status_color";

    public function findFull(int $id): ?array
    {
        return $this->db()->one(
            'SELECT ' . self::SELECT_COLS . self::SELECT_JOIN . ' WHERE i.id = :id LIMIT 1',
            ['id' => $id]
        );
    }

    public function findByNo(string $invoiceNo): ?array
    {
        return $this->db()->one(
            'SELECT * FROM invoices WHERE invoice_no = :n AND deleted_at IS NULL LIMIT 1',
            ['n' => $invoiceNo]
        );
    }

    /** Kolom yang boleh dipakai untuk pengurutan (whitelist). */
    private const SORTABLE = [
        'invoice_no' => 'i.invoice_no', 'tax_invoice_no' => 'i.tax_invoice_no', 'so_no' => 'i.so_no',
        'invoice_date' => 'i.invoice_date', 'sent_date' => 'i.sent_date', 'due_date' => 'i.due_date',
        'customer' => 'c.name', 'outlet' => 'o.name', 'area' => 'a.name', 'salesperson' => 's.name',
        'gross_amount' => 'i.gross_amount', 'net_amount' => 'i.net_amount',
        'paid_amount' => 'i.paid_amount', 'outstanding_amount' => 'i.outstanding_amount',
        'aging_days' => 'i.aging_days', 'collection_days' => 'i.collection_days',
        'payment_status' => 'i.payment_status_code', 'last_payment_date' => 'i.last_payment_date',
        'job_category' => 'jc.name', 'created_at' => 'i.created_at',
    ];

    /**
     * Daftar invoice dengan filter, pengurutan, dan paginasi.
     * Klausa pembatas cakupan data (role) diterapkan otomatis.
     */
    public function listing(array $filters, string $sort = 'invoice_date', string $dir = 'desc', int $page = 1, int $perPage = 50, bool $applyScope = true): array
    {
        if ($applyScope) {
            [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
            $filters['_scope_sql'] = $scopeSql;
            $filters['_scope_params'] = $scopeParams;
        }
        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');

        // Filter tambahan yang butuh join
        $extra = '';
        if (!empty($filters['cari_teks'])) {
            $extra .= " AND (i.invoice_no LIKE :q OR i.tax_invoice_no LIKE :q OR i.so_no LIKE :q
                             OR c.name LIKE :q OR o.name LIKE :q OR i.notes LIKE :q)";
            $params['q'] = '%' . $filters['cari_teks'] . '%';
        }
        // Catatan: filter bucket aging (bucket_from/bucket_to) diterjemahkan oleh caller
        // menjadi aging_min/aging_max supaya ekspresi hari mengikuti konfigurasi aging aktif.
        if (isset($filters['aging_min']) && $filters['aging_min'] !== '') {
            $extra .= ' AND i.aging_days >= :agmin'; $params['agmin'] = (int)$filters['aging_min'];
        }
        if (isset($filters['aging_max']) && $filters['aging_max'] !== '') {
            $extra .= ' AND i.aging_days <= :agmax'; $params['agmax'] = (int)$filters['aging_max'];
        }
        if (!empty($filters['tanpa_faktur_pajak'])) $extra .= " AND (i.tax_invoice_no IS NULL OR i.tax_invoice_no = '')";
        if (!empty($filters['ada_dq'])) $extra .= " AND i.dq_flags IS NOT NULL AND i.dq_flags <> '[]'";

        $orderCol = self::SORTABLE[$sort] ?? 'i.invoice_date';
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';

        $sql = 'SELECT ' . self::SELECT_COLS . self::SELECT_JOIN
             . " WHERE i.deleted_at IS NULL AND $where $extra ORDER BY $orderCol $dir, i.id DESC";
        $countSql = 'SELECT COUNT(*)' . self::SELECT_JOIN . " WHERE i.deleted_at IS NULL AND $where $extra";

        return $this->paginate($sql, $params, $page, $perPage, $countSql);
    }

    /** Agregat untuk kartu ringkasan di atas tabel. */
    public function listingTotals(array $filters, bool $applyScope = true): array
    {
        if ($applyScope) {
            [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
            $filters['_scope_sql'] = $scopeSql;
            $filters['_scope_params'] = $scopeParams;
        }
        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');
        $extra = '';
        if (!empty($filters['cari_teks'])) {
            $extra .= " AND (i.invoice_no LIKE :q OR i.tax_invoice_no LIKE :q OR i.so_no LIKE :q
                             OR c.name LIKE :q OR o.name LIKE :q OR i.notes LIKE :q)";
            $params['q'] = '%' . $filters['cari_teks'] . '%';
        }
        if (!empty($filters['tanpa_faktur_pajak'])) $extra .= " AND (i.tax_invoice_no IS NULL OR i.tax_invoice_no = '')";
        if (!empty($filters['ada_dq'])) $extra .= " AND i.dq_flags IS NOT NULL AND i.dq_flags <> '[]'";

        return (array)$this->db()->one(
            'SELECT COUNT(*) AS jumlah,
                    COALESCE(SUM(i.gross_amount),0) AS bruto,
                    COALESCE(SUM(i.total_deduction),0) AS potongan,
                    COALESCE(SUM(i.net_amount),0) AS bersih,
                    COALESCE(SUM(i.paid_amount),0) AS dibayar,
                    COALESCE(SUM(i.outstanding_amount),0) AS saldo,
                    COALESCE(SUM(i.pph23_amount),0) AS pph23,
                    COUNT(DISTINCT i.customer_id) AS jumlah_customer'
            . self::SELECT_JOIN . " WHERE i.deleted_at IS NULL AND $where $extra",
            $params
        );
    }

    public function deductions(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT d.*, t.code, t.name, t.calc_type, t.target_column
               FROM invoice_deductions d
               JOIN deduction_types t ON t.id = d.deduction_type_id
              WHERE d.invoice_id = :id AND d.deleted_at IS NULL
              ORDER BY t.sort_order, d.id',
            ['id' => $invoiceId]
        );
    }

    public function payments(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT pa.id AS allocation_id, pa.amount AS allocated_amount, pa.notes AS allocation_notes,
                    p.*, b.name AS bank_name, u.name AS created_by_name, ua.name AS approved_by_name
               FROM payment_allocations pa
               JOIN payments p ON p.id = pa.payment_id
               LEFT JOIN banks b ON b.id = p.bank_id
               LEFT JOIN users u ON u.id = p.created_by
               LEFT JOIN users ua ON ua.id = p.approved_by
              WHERE pa.invoice_id = :id AND pa.deleted_at IS NULL AND p.deleted_at IS NULL
              ORDER BY p.payment_date, p.id',
            ['id' => $invoiceId]
        );
    }

    public function revisions(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT r.*, rt.name AS revision_type_name,
                    oi.invoice_no AS old_no, ni.invoice_no AS new_no
               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
              WHERE (r.old_invoice_id = :id OR r.new_invoice_id = :id2) AND r.deleted_at IS NULL
              ORDER BY r.rf_date DESC, r.id DESC',
            ['id' => $invoiceId, 'id2' => $invoiceId]
        );
    }

    public function creditNotes(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT cn.*, u.name AS created_by_name, ua.name AS approved_by_name
               FROM credit_notes cn
               LEFT JOIN users u ON u.id = cn.created_by
               LEFT JOIN users ua ON ua.id = cn.approved_by
              WHERE cn.invoice_id = :id AND cn.deleted_at IS NULL ORDER BY cn.cn_date, cn.id',
            ['id' => $invoiceId]
        );
    }

    public function followups(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT f.*, cs.name AS status_name, cs.color AS status_color,
                    co.name AS officer_name, u.name AS created_by_name
               FROM collection_followups f
               LEFT JOIN collection_statuses cs ON cs.id = f.collection_status_id
               LEFT JOIN collection_officers co ON co.id = f.collection_officer_id
               LEFT JOIN users u ON u.id = f.created_by
              WHERE f.invoice_id = :id AND f.deleted_at IS NULL
              ORDER BY f.followup_date DESC, f.id DESC',
            ['id' => $invoiceId]
        );
    }

    public function promises(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT * FROM promise_to_pay WHERE invoice_id = :id AND deleted_at IS NULL ORDER BY promise_date DESC',
            ['id' => $invoiceId]
        );
    }

    public function attachments(int $invoiceId): array
    {
        return $this->db()->all(
            'SELECT a.*, t.name AS type_name, u.name AS uploaded_by_name
               FROM attachments a
               LEFT JOIN attachment_types t ON t.id = a.attachment_type_id
               LEFT JOIN users u ON u.id = a.uploaded_by
              WHERE a.entity_type = "invoice" AND a.entity_id = :id AND a.deleted_at IS NULL
              ORDER BY a.created_at DESC',
            ['id' => $invoiceId]
        );
    }

    public function auditTrail(int $invoiceId, int $limit = 100): array
    {
        return $this->db()->all(
            'SELECT * FROM audit_logs WHERE entity_type = "invoice" AND entity_id = :id
              ORDER BY created_at DESC LIMIT ' . (int)$limit,
            ['id' => $invoiceId]
        );
    }

    /** Nomor invoice berikutnya untuk prefix tertentu. */
    public function nextInvoiceNo(string $prefix = 'FA'): string
    {
        $yy = date('y');
        $pat = "$prefix/$yy/%";
        $last = $this->db()->value(
            'SELECT invoice_no FROM invoices WHERE invoice_no LIKE :p ORDER BY invoice_no DESC LIMIT 1',
            ['p' => $pat]
        );
        $n = 1;
        if ($last && preg_match('#/(\d+)$#', (string)$last, $m)) $n = (int)$m[1] + 1;
        return sprintf('%s/%s/%04d', $prefix, $yy, $n);
    }

    public static function sortableFields(): array { return array_keys(self::SORTABLE); }
}
