<?php
class GoodsCustomerDelivery extends Model
{
    public function nextDnNo(): string
    {
        $db   = new DB();
        $year = (int) date('Y');
        $rs = $db->mySelect(
            "SELECT dn_no FROM goods_customer_deliveries WHERE dn_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'DN-' . $year . '-%']
        );
        $seq = (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['dn_no'], $m)) ? (int)$m[1] + 1 : 1;
        return sprintf('DN-%d-%04d', $year, $seq);
    }

    /**
     * Create a customer DN against a Client PO.
     * $d = [ 'client_po_id', 'delivery_date', 'delivered_to', 'delivered_to_phone',
     *        'delivered_to_signature', 'condition_ok', 'notes',
     *        'items' => [ ['client_po_item_id', 'qty_delivered', 'condition_notes'] ] ]
     *
     * Cascade:
     *   dn_items.qty_delivered → goods_client_po_items.qty_delivered (cumulative)
     *   When Σqty_delivered ≥ Σqty on the PO → goods_client_pos.status = 'CLOSED'
     * Guard: qty_delivered ≤ (qty_received − already_delivered)
     */
    public function create(array $d, int $uid, string $uname): array
    {
        $cpoId = (int)($d['client_po_id'] ?? 0);
        $items = $d['items'] ?? [];
        if ($cpoId <= 0)   return ['ok' => false, 'msg' => 'client_po_id required.', 'id' => 0];
        if (empty($items)) return ['ok' => false, 'msg' => 'Add at least one line delivery.', 'id' => 0];

        $db = new DB();
        // Load Client PO for context
        $crs = $db->mySelect(
            "SELECT c0d, client_po_no, project_id, customer_id, customer_name, currency, status
               FROM goods_client_pos WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $cpoId]
        );
        $cpo = $crs[0] ?? null;
        if (!$cpo)                                      return ['ok' => false, 'msg' => 'Client PO not found.', 'id' => 0];
        if (in_array($cpo['status'], ['CANCELLED','CLOSED'], true)) {
            return ['ok' => false, 'msg' => 'Client PO is ' . $cpo['status'] . ' — cannot record delivery.', 'id' => 0];
        }

        // Load PO items for validation
        $poItems = $db->mySelect(
            "SELECT c0d, description, qty, qty_received, qty_delivered
               FROM goods_client_po_items WHERE client_po_id = :id ORDER BY item_no",
            [':id' => $cpoId]
        ) ?: [];
        $byId = [];
        foreach ($poItems as $pi) $byId[(int)$pi['c0d']] = $pi;

        // Validate lines
        $cleanItems = [];
        $totalDelivered = 0.0;
        foreach ($items as $it) {
            $poItemId = (int)($it['client_po_item_id'] ?? 0);
            $qd       = round((float)($it['qty_delivered'] ?? 0), 4);
            if ($poItemId <= 0 || !isset($byId[$poItemId])) continue;
            if ($qd <= 0) continue;
            $pi = $byId[$poItemId];
            $inStock     = round((float)$pi['qty_received'] - (float)$pi['qty_delivered'], 4);
            $outstanding = round((float)$pi['qty']          - (float)$pi['qty_delivered'], 4);
            if ($qd > $inStock + 0.0001) {
                return ['ok' => false, 'msg' => 'Line "' . $pi['description'] . '": delivering ' . $qd . ' exceeds warehouse stock ' . $inStock . ' (received but not yet delivered).', 'id' => 0];
            }
            if ($qd > $outstanding + 0.0001) {
                return ['ok' => false, 'msg' => 'Line "' . $pi['description'] . '": delivering ' . $qd . ' exceeds PO outstanding ' . $outstanding . '.', 'id' => 0];
            }
            $totalDelivered += $qd;
            $cleanItems[] = [
                'client_po_item_id' => $poItemId,
                'qty_delivered'     => $qd,
                'condition_notes'   => (string)($it['condition_notes'] ?? ''),
            ];
        }
        if (empty($cleanItems) || $totalDelivered <= 0) {
            return ['ok' => false, 'msg' => 'No positive-qty deliveries.', 'id' => 0];
        }

        // Insert DN header ----------------------------------------------
        $dnNo = $this->nextDnNo();
        $r = $db->insertUpdate(
            "INSERT INTO goods_customer_deliveries
                (dn_no, client_po_id, project_id, customer_id, customer_name,
                 delivery_date, delivered_to, delivered_to_phone, delivered_to_signature,
                 delivered_by, delivered_by_name, condition_ok, notes,
                 created_by, edited_by)
             VALUES
                (:dn, :cid, :pid, :cust, :cnm,
                 :dd, :dt, :dtp, :dts,
                 :uid, :unm, :ck, :nt,
                 :uid, :uid)",
            [
                ':dn'   => $dnNo,
                ':cid'  => $cpoId,
                ':pid'  => (int)$cpo['project_id'],
                ':cust' => (int)$cpo['customer_id'],
                ':cnm'  => (string)$cpo['customer_name'],
                ':dd'   => (string)($d['delivery_date'] ?? date('Y-m-d')),
                ':dt'   => (string)($d['delivered_to'] ?? ''),
                ':dtp'  => (string)($d['delivered_to_phone'] ?? ''),
                ':dts'  => (string)($d['delivered_to_signature'] ?? ''),
                ':uid'  => $uid,
                ':unm'  => $uname,
                ':ck'   => isset($d['condition_ok']) ? (int)!empty($d['condition_ok']) : 1,
                ':nt'   => (string)($d['notes'] ?? ''),
            ]
        );
        $dnId = (int)$r['last_id'];
        if ($dnId <= 0) return ['ok' => false, 'msg' => 'Save failed on DN header.', 'id' => 0];

        // Insert lines + roll up on client PO items
        foreach ($cleanItems as $ci) {
            $db->insertUpdate(
                "INSERT INTO goods_customer_delivery_items (dn_id, client_po_item_id, qty_delivered, condition_notes)
                 VALUES (:did, :cid, :qd, :cn)",
                [':did' => $dnId, ':cid' => $ci['client_po_item_id'], ':qd' => $ci['qty_delivered'], ':cn' => $ci['condition_notes']]
            );
            $db->insertUpdate(
                "UPDATE goods_client_po_items SET qty_delivered = qty_delivered + :qd WHERE c0d = :id",
                [':qd' => $ci['qty_delivered'], ':id' => $ci['client_po_item_id']]
            );
        }

        // Refresh Client PO status: if fully delivered → CLOSED
        $agg = $db->mySelect(
            "SELECT SUM(qty) AS ordered, SUM(qty_delivered) AS delivered
               FROM goods_client_po_items WHERE client_po_id = :id",
            [':id' => $cpoId]
        );
        $ordered   = (float)($agg[0]['ordered']   ?? 0);
        $delivered = (float)($agg[0]['delivered'] ?? 0);
        $newStatus = $cpo['status'];
        $msg = 'DN posted (' . $dnNo . ').';
        if ($delivered >= $ordered - 0.0001 && $ordered > 0) {
            $newStatus = 'CLOSED';
            $msg .= ' Client PO fully delivered — status → CLOSED.';
            $db->insertUpdate(
                "UPDATE goods_client_pos SET status = :s, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
                [':s' => $newStatus, ':uid' => $uid, ':id' => $cpoId]
            );
        } elseif ($cpo['status'] === 'DRAFT' || $cpo['status'] === 'CONFIRMED' || $cpo['status'] === 'SOURCING') {
            // First partial delivery → move to INVOICING (customer expects invoicing to begin)
            $newStatus = 'INVOICING';
            $db->insertUpdate(
                "UPDATE goods_client_pos SET status = 'INVOICING', edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
                [':uid' => $uid, ':id' => $cpoId]
            );
        }

        return ['ok' => true, 'msg' => $msg, 'id' => $dnId, 'dn_no' => $dnNo, 'cpo_status' => $newStatus];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT d.*, cpo.client_po_no, cpo.customer_po_ref, cpo.currency, p.project_no
               FROM goods_customer_deliveries d
          LEFT JOIN goods_client_pos cpo ON cpo.c0d = d.client_po_id
          LEFT JOIN c_projects p         ON p.c0d = d.project_id
              WHERE d.c0d = :id AND d.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function itemsFor(int $dn_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT di.*, cpi.description, cpi.qty AS ordered_qty, cpi.unit, cpi.unit_price_sell, cpi.item_no
               FROM goods_customer_delivery_items di
          LEFT JOIN goods_client_po_items cpi ON cpi.c0d = di.client_po_item_id
              WHERE di.dn_id = :id
              ORDER BY cpi.item_no ASC",
            [':id' => $dn_id]
        ) ?: [];
    }

    public function forClientPo(int $cpo_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, dn_no, delivery_date, delivered_to, delivered_by_name, condition_ok, status
               FROM goods_customer_deliveries WHERE client_po_id = :id AND deleted = 0 ORDER BY c0d DESC",
            [':id' => $cpo_id]
        ) ?: [];
    }

    public function list(int $projectId = 0, int $assignedProcUser = 0): array
    {
        $db = new DB();
        $where  = "WHERE d.deleted = 0";
        $params = [];
        if ($projectId > 0)        { $where .= " AND d.project_id = :p";      $params[':p']   = $projectId; }
        if ($assignedProcUser > 0) { $where .= " AND p.procurement_id = :apu"; $params[':apu'] = $assignedProcUser; }
        return $db->mySelect(
            "SELECT d.c0d, d.dn_no, d.delivery_date, d.delivered_to, d.customer_name, d.condition_ok, d.status,
                    cpo.client_po_no, cpo.customer_po_ref, cpo.currency,
                    p.project_no, p.project_name,
                    (SELECT COUNT(*) FROM goods_customer_delivery_items di WHERE di.dn_id = d.c0d) AS line_count,
                    (SELECT SUM(qty_delivered) FROM goods_customer_delivery_items di WHERE di.dn_id = d.c0d) AS qty_delivered_total
               FROM goods_customer_deliveries d
          LEFT JOIN goods_client_pos cpo ON cpo.c0d = d.client_po_id
          LEFT JOIN c_projects p         ON p.c0d = d.project_id
               $where
              ORDER BY d.c0d DESC",
            $params
        ) ?: [];
    }

    /* ── Slice 8Z-x · PoD photo evidence ─────────────────────────────── */

    private const MAX_PHOTO_BYTES = 10 * 1024 * 1024;

    public function photosFor(int $dnId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM goods_customer_delivery_photos WHERE dn_id = :d ORDER BY c0d",
            [':d' => $dnId]
        ) ?: [];
    }

    public function findPhoto(int $id): array
    {
        $db = new DB();
        $r  = $db->mySelect("SELECT * FROM goods_customer_delivery_photos WHERE c0d = :id", [':id' => $id]);
        return $r[0] ?? [];
    }

    public function addPhoto(int $dnId, array $file, string $kind, string $caption, int $uid, string $uname): int
    {
        $err = (int)($file['error'] ?? 0);
        if ($err !== UPLOAD_ERR_OK)                    throw new RuntimeException('Upload error ' . $err);
        $size = (int)($file['size'] ?? 0);
        if ($size <= 0 || $size > self::MAX_PHOTO_BYTES) throw new RuntimeException('Photo size invalid.');
        $mime = @mime_content_type($file['tmp_name']) ?: (string)($file['type'] ?? '');
        $ok   = ['image/jpeg','image/png','image/webp','image/gif','application/pdf'];
        if (!in_array($mime, $ok, true))               throw new RuntimeException('Unsupported photo type: ' . $mime);
        $kindU = strtoupper($kind);
        if (!in_array($kindU, ['DELIVERY','SIGNATURE','OTHER'], true)) $kindU = 'DELIVERY';

        $orig = (string)($file['name'] ?? 'photo');
        $safe = preg_replace('/[^A-Za-z0-9._\-]+/', '_', $orig);
        $ext  = pathinfo($safe, PATHINFO_EXTENSION);
        $base = pathinfo($safe, PATHINFO_FILENAME) . '_' . substr(bin2hex(random_bytes(4)), 0, 8) . ($ext ? '.' . $ext : '');
        $dir  = ROOT . '/attachments/delivery-photos/' . $dnId;
        if (!is_dir($dir) && !@mkdir($dir, 0777, true) && !is_dir($dir)) throw new RuntimeException('Could not create attachment folder.');
        $abs = $dir . '/' . $base;
        if (!@move_uploaded_file($file['tmp_name'], $abs))              throw new RuntimeException('Failed to save photo.');

        $db  = new DB();
        $fb  = $db->insertUpdate(
            "INSERT INTO goods_customer_delivery_photos
                (dn_id, file_path, original_name, mime, size_bytes, kind, caption, uploaded_by, uploaded_by_name)
             VALUES (:d, :fp, :on, :mm, :sz, :k, :cap, :u, :un)",
            [':d'=>$dnId, ':fp'=>'attachments/delivery-photos/' . $dnId . '/' . $base,
             ':on'=>$orig, ':mm'=>$mime, ':sz'=>$size, ':k'=>$kindU,
             ':cap'=>trim($caption) ?: null, ':u'=>$uid, ':un'=>$uname]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function removePhoto(int $photoId): void
    {
        $p = $this->findPhoto($photoId);
        if (empty($p)) return;
        $abs = ROOT . '/' . ltrim((string)$p['file_path'], '/');
        if (is_file($abs)) @unlink($abs);
        $db = new DB();
        $db->insertUpdate("DELETE FROM goods_customer_delivery_photos WHERE c0d = :id", [':id' => $photoId]);
    }
}
