<?php
/**
 * CreditNotes — Slice 8Z-y
 *
 * When a customer disputes/returns/gets over-charged on an already-ISSUED invoice, we cannot
 * silently reverse it — VAT + EFD + audit trail all require a formal Credit Note document
 * that references the original invoice.
 *
 * Flow: DRAFT (Accountant creates against issued sale) → ISSUED (own EFD if fiscalised) →
 *       optionally CANCELLED.
 *
 * On ISSUE:
 *   - sales.outstanding_amount is reduced by the credit total (Sales::recomputeOutstanding handles)
 *   - if the CN clears the balance, sales.invoice_workflow_status becomes CREDITED
 *   - best-effort journal reversal: Dr Sales 4110 / Cr Trade Receivables 1121 (mirror of original)
 */
class CreditNotes extends Model
{
    public const REASONS = [
        'RETURN'       => 'Goods returned',
        'DISPUTE'      => 'Quality dispute',
        'OVERCHARGE'   => 'Over-charge / pricing error',
        'CANCELLATION' => 'Order cancelled',
        'ADJUSTMENT'   => 'Adjustment / rebate',
        'OTHER'        => 'Other',
    ];

    public function nextCnNo(): string
    {
        $db  = new DB();
        $yr  = date('Y');
        $r   = $db->mySelect("SELECT cn_no FROM credit_notes WHERE cn_no LIKE :p ORDER BY c0d DESC LIMIT 1", [':p' => 'CN-' . $yr . '-%']);
        $next = 1;
        if (!empty($r) && preg_match('/CN-\d{4}-(\d+)/', (string)$r[0]['cn_no'], $m)) $next = ((int)$m[1]) + 1;
        return sprintf('CN-%s-%04d', $yr, $next);
    }

    public function find(int $id): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT cn.*, s.sale_no, s.total AS sale_total, s.customer_name AS sale_customer_name,
                    s.invoice_workflow_status AS sale_status, s.outstanding_amount AS sale_outstanding,
                    p.project_no, p.project_name
               FROM credit_notes cn
               JOIN sales s      ON s.c0d = cn.sale_id
          LEFT JOIN c_projects p ON p.c0d = cn.project_id
              WHERE cn.c0d = :id AND cn.deleted = 0",
            [':id' => $id]
        );
        return $r[0] ?? [];
    }

    public function linesFor(int $cnId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM credit_note_lines WHERE credit_note_id = :c ORDER BY line_no, c0d",
            [':c' => $cnId]
        ) ?: [];
    }

    public function list(string $status = ''): array
    {
        $db = new DB();
        $where = "WHERE cn.deleted = 0";
        $params = [];
        if ($status !== '') { $where .= " AND cn.status = :s"; $params[':s'] = $status; }
        return $db->mySelect(
            "SELECT cn.c0d, cn.cn_no, cn.sale_id, cn.currency, cn.total, cn.status, cn.reason,
                    cn.drafted_by_name, cn.drafted_on, cn.issued_at,
                    s.sale_no, s.customer_name,
                    p.project_no
               FROM credit_notes cn
               JOIN sales s      ON s.c0d = cn.sale_id
          LEFT JOIN c_projects p ON p.c0d = cn.project_id
               $where
              ORDER BY cn.c0d DESC",
            $params
        ) ?: [];
    }

    public function forSale(int $saleId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, cn_no, total, status, reason, issued_at, drafted_by_name, drafted_on
               FROM credit_notes WHERE sale_id = :s AND deleted = 0 ORDER BY c0d DESC",
            [':s' => $saleId]
        ) ?: [];
    }

    /**
     * Create a DRAFT credit note against an issued sale.
     * $d fields: sale_id, reason, narrative, lines: [ ['description','qty','unit','unit_price','sales_item_id'] ]
     */
    public function draft(array $d, int $uid, string $uname): int
    {
        $saleId = (int)($d['sale_id'] ?? 0);
        if ($saleId <= 0) throw new InvalidArgumentException('sale_id required.');
        $db = new DB();
        $sale = $db->mySelect("SELECT c0d, project_id, customer_id, customer_name, currency, invoice_workflow_status, total, outstanding_amount FROM sales WHERE c0d = :id AND deleted = 0", [':id' => $saleId]);
        if (empty($sale)) throw new RuntimeException('Sale not found.');
        $sale = $sale[0];
        if (!in_array($sale['invoice_workflow_status'], ['ISSUED','PART_PAID','OVERDUE','PAID_LATE','PAID'], true)) {
            throw new RuntimeException('Can only credit-note an issued invoice (current: ' . $sale['invoice_workflow_status'] . ').');
        }
        $reason = strtoupper((string)($d['reason'] ?? 'RETURN'));
        if (!array_key_exists($reason, self::REASONS)) $reason = 'RETURN';

        $lines = $d['lines'] ?? [];
        if (!is_array($lines) || empty($lines)) throw new InvalidArgumentException('At least one line is required.');
        $subtotal = 0.0;
        foreach ($lines as $ln) {
            $qty = (float)($ln['qty'] ?? 0);
            $up  = (float)($ln['unit_price'] ?? 0);
            $subtotal += round($qty * $up, 2);
        }
        $subtotal = round($subtotal, 2);
        if ($subtotal <= 0) throw new InvalidArgumentException('Credit total must be > 0.');
        if ($subtotal > (float)$sale['total'] + 0.005) throw new InvalidArgumentException('Credit total cannot exceed original invoice.');

        // For now VAT proportional to original sale — Accountant can adjust before issuing.
        $vatRatio = (float)$sale['total'] > 0 ? ((float)$sale['total'] - $subtotal) / (float)$sale['total'] : 0.0;
        $vat = 0.0; // will be inferred at issue time if sale had VAT
        $total = round($subtotal, 2);

        $no = $this->nextCnNo();
        $fb = $db->insertUpdate(
            "INSERT INTO credit_notes
                (cn_no, sale_id, project_id, customer_id, customer_name, currency, reason,
                 subtotal, vat_amount, total, status, narrative,
                 drafted_by, drafted_by_name, created_by)
             VALUES
                (:no, :s, :p, :cust, :cn, :c, :r, :sub, :vat, :tot, 'DRAFT', :nt, :u, :un, :u)",
            [':no'=>$no, ':s'=>$saleId, ':p'=>(int)$sale['project_id'], ':cust'=>(int)$sale['customer_id'],
             ':cn'=>$sale['customer_name'], ':c'=>$sale['currency'], ':r'=>$reason,
             ':sub'=>$subtotal, ':vat'=>$vat, ':tot'=>$total, ':nt'=>trim((string)($d['narrative'] ?? '')) ?: null,
             ':u'=>$uid, ':un'=>$uname]
        );
        $cnId = (int)($fb['last_id'] ?? 0);
        $i = 1;
        foreach ($lines as $ln) {
            $qty = (float)($ln['qty'] ?? 0);
            $up  = (float)($ln['unit_price'] ?? 0);
            $db->insertUpdate(
                "INSERT INTO credit_note_lines (credit_note_id, line_no, sales_item_id, description, qty, unit, unit_price, line_total)
                 VALUES (:cn, :ln, :si, :d, :q, :un, :up, :lt)",
                [':cn'=>$cnId, ':ln'=>$i++, ':si'=>!empty($ln['sales_item_id']) ? (int)$ln['sales_item_id'] : null,
                 ':d'=>trim((string)$ln['description']), ':q'=>$qty, ':un'=>(string)($ln['unit'] ?? ''),
                 ':up'=>$up, ':lt'=>round($qty * $up, 2)]
            );
        }
        return $cnId;
    }

    /**
     * Issue a DRAFT credit note: flip to ISSUED, apply to sales.outstanding_amount, best-effort journal reversal.
     */
    public function issue(int $cnId, array $efd, int $uid, string $uname): array
    {
        $cn = $this->find($cnId);
        if (empty($cn)) throw new RuntimeException('Credit note not found.');
        if ($cn['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT credit notes can be issued (current: ' . $cn['status'] . ').');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE credit_notes SET
                status = 'ISSUED',
                efd_receipt_no = :efd,
                efd_date = :ed,
                tra_verification_code = :tra,
                original_efd_receipt_no = :orig,
                issued_by = :u, issued_by_name = :un, issued_at = NOW()
              WHERE c0d = :id",
            [':efd'=>trim((string)($efd['efd_receipt_no'] ?? '')) ?: null,
             ':ed'=>trim((string)($efd['efd_date'] ?? '')) ?: null,
             ':tra'=>trim((string)($efd['tra_verification_code'] ?? '')) ?: null,
             ':orig'=>trim((string)($efd['original_efd_receipt_no'] ?? '')) ?: null,
             ':u'=>$uid, ':un'=>$uname, ':id'=>$cnId]
        );

        // Reduce sale.outstanding_amount indirectly by increasing amount_paid — the CN is treated as a settlement.
        // Actually cleaner: add to wht_settled_amount would be wrong. Use a bespoke column? For MVP, add to amount_paid.
        $db->insertUpdate(
            "UPDATE sales SET amount_paid = amount_paid + :t, edited_on = NOW() WHERE c0d = :id",
            [':t'=>(float)$cn['total'], ':id'=>(int)$cn['sale_id']]
        );
        if (!class_exists('Sales')) require_once ROOT . '/app/models/Sales.php';
        (new Sales())->recomputeOutstanding((int)$cn['sale_id']);
        // If outstanding now = 0 due to this credit, flip status to CREDITED (over PAID) if entire balance was credited.
        $after = $db->mySelect("SELECT total, amount_paid, outstanding_amount FROM sales WHERE c0d = :id", [':id' => (int)$cn['sale_id']]);
        if (!empty($after) && (float)$after[0]['outstanding_amount'] < 0.005) {
            $db->insertUpdate("UPDATE sales SET invoice_workflow_status = 'CREDITED' WHERE c0d = :id", [':id' => (int)$cn['sale_id']]);
        }

        // Best-effort journal reversal: Dr Sales 4110 / Cr Trade Receivables 1121
        $jeId = $this->postReversalJournal($cnId, $uid, $uname);
        if ($jeId > 0) {
            $db->insertUpdate("UPDATE credit_notes SET journal_entry_id = :j WHERE c0d = :id", [':j'=>$jeId, ':id'=>$cnId]);
        }
        return ['ok'=>true, 'journal_entry_id'=>$jeId];
    }

    public function cancel(int $cnId, int $uid): void
    {
        $cn = $this->find($cnId);
        if (empty($cn)) throw new RuntimeException('Credit note not found.');
        if ($cn['status'] === 'ISSUED') throw new RuntimeException('Cannot cancel an ISSUED credit note — issue a reversing CN instead.');
        $db = new DB();
        $db->insertUpdate("UPDATE credit_notes SET status = 'CANCELLED', edited_on = NOW() WHERE c0d = :id", [':id' => $cnId]);
    }

    private function postReversalJournal(int $cnId, int $uid, string $uname): int
    {
        try {
            if (!class_exists('JournalPosting')) require_once ROOT . '/app/models/JournalPosting.php';
            $cn = $this->find($cnId);
            if (empty($cn)) return 0;
            $sales = $this->resolveAccountCode('4110');   // Sales / Revenue
            $ar    = $this->resolveAccountCode('1121');   // Trade Receivables
            if (!$sales || !$ar) return 0;
            $amt = (float)$cn['total'];
            $post = (new JournalPosting())->post([
                'entry_date'  => date('Y-m-d'),
                'source_type' => 'CREDIT_NOTE',
                'source_id'   => $cnId,
                'source_ref'  => (string)$cn['cn_no'],
                'narrative'   => 'Credit note reversal · ' . $cn['cn_no'] . ' against ' . (string)$cn['sale_no'] . ' · ' . self::REASONS[$cn['reason']],
                'currency'    => (string)$cn['currency'],
                'posted_by'   => $uid,
                'posted_by_name' => $uname,
                'lines' => [
                    ['account_id' => $sales, 'debit' => $amt, 'credit' => 0, 'narrative' => 'Sales reversal · ' . $cn['cn_no']],
                    ['account_id' => $ar,    'debit' => 0,    'credit' => $amt, 'narrative' => 'AR reduced · ' . $cn['cn_no']],
                ],
            ]);
            return !empty($post['ok']) ? (int)($post['id'] ?? 0) : 0;
        } catch (\Throwable $e) { return 0; }
    }

    private function resolveAccountCode(string $code): int
    {
        $db = new DB();
        $r = $db->mySelect("SELECT c0d FROM chart_of_accounts WHERE account_code = :c AND is_active = 1 LIMIT 1", [':c' => $code]);
        return (int)($r[0]['c0d'] ?? 0);
    }
}
