<?php

namespace Opencart\Extension\Alsendo\Repositories;

require_once(DIR_EXTENSION . 'alsendo/src/Models/OrderShipmentPackage.php');

use Opencart\Extension\Alsendo\Models\OrderShipmentPackage;

class OrderShipmentPackageRepository
{
    private object $db;
    private string $tablePrefix;
    private string $tableName;

    public function __construct(object $db, string $tablePrefix = '')
    {
        $this->db = $db;
        $this->tablePrefix = $tablePrefix ?: (defined('\DB_PREFIX') ? \DB_PREFIX : '');
        $this->tableName = $this->tablePrefix . 'alsendo_order_shipment_package';
    }

    public function getByOrderShipmentId(int $orderShipmentId): ?OrderShipmentPackage
    {
        $query = $this->db->query("
            SELECT p.*, t.name AS template_name
            FROM `{$this->tableName}` p
            LEFT JOIN `{$this->tablePrefix}alsendo_shipping_settings_templates` t 
                ON p.shipping_settings_template_id = t.id
            WHERE p.order_shipment_id = '" . (int)$orderShipmentId . "'
        ");

        if (!$query->num_rows) {
            return null;
        }

        return $this->createOrderShipmentPackageFromRow($query->row);
    }

    /**
     * @return OrderShipmentPackage[]
     */
    public function getMultipleByOrderShipmentId(int $orderShipmentId): array {
        $query = $this->db->query("
            SELECT p.*, t.name AS template_name
            FROM `{$this->tableName}` p
            LEFT JOIN `{$this->tablePrefix}alsendo_shipping_settings_templates` t 
                ON p.shipping_settings_template_id = t.id
            WHERE p.order_shipment_id = '" . (int)$orderShipmentId . "'
        ");

        if (!$query->num_rows) {
            return [];
        }

        $shipments = [];
        foreach ($query->rows as $row) {
            $shipments[] = $this->createOrderShipmentPackageFromRow($row);
        }

        return $shipments;
    }

    public function getById(int $id): ?OrderShipmentPackage
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `id` = '" . $id . "'");
        
        if (!$query->num_rows) {
            return null;
        }
        
        return $this->createOrderShipmentPackageFromRow($query->row);
    }

    public function upsert(OrderShipmentPackage $orderShipmentPackage): int
    {
        if ($orderShipmentPackage->getId()) {
            $this->updateOrderShipmentPackage($orderShipmentPackage);
            return $orderShipmentPackage->getId();
        }

        $sql = "INSERT INTO `" . $this->tableName . "` SET 
            `order_shipment_id` = '" . $orderShipmentPackage->getOrderShipmentId() . "',
            `shipping_settings_template_id` = '" . $orderShipmentPackage->getShippingSettingsTemplateId() . "',
            `package_type` = '" . $this->db->escape($orderShipmentPackage->getPackageType()) . "',
            `pickup_type` = " . ($orderShipmentPackage->getPickupType() ? "'" . $this->db->escape($orderShipmentPackage->getPickupType()) . "'" : "NULL") . ",
            `length` = '" . $orderShipmentPackage->getLength() . "',
            `width` = '" . $orderShipmentPackage->getWidth() . "',
            `height` = '" . $orderShipmentPackage->getHeight() . "',
            `weight` = '" . $orderShipmentPackage->getWeight() . "',
            `cash_on_delivery` = " . ($orderShipmentPackage->getCashOnDelivery() ? "'" . $orderShipmentPackage->getCashOnDelivery() . "'" : "NULL") . ",
            `declared_value` = " . ($orderShipmentPackage->getDeclaredValue() ? "'" . $orderShipmentPackage->getDeclaredValue() . "'" : "NULL") . ",
            `content_text` = " . ($orderShipmentPackage->getContentText() ? "'" . $this->db->escape($orderShipmentPackage->getContentText()) . "'" : "NULL") . ",
            `created_at` = '" . $this->db->escape($orderShipmentPackage->getCreatedAt()) . "',
            `updated_at` = '" . $this->db->escape($orderShipmentPackage->getUpdatedAt()) . "'";

        $this->db->query($sql);
        return $this->db->getLastId();
    }

    public function updateOrderShipmentPackage(OrderShipmentPackage $orderShipmentPackage): void
    {
        if (!$orderShipmentPackage->getId()) {
            return;
        }

        $existingQuery = $this->db->query("SELECT `id` FROM `" . $this->tableName . "` WHERE `id` = '" . (int)$orderShipmentPackage->getId() . "'");
        
        if (!$existingQuery->num_rows) {
            throw new \Exception('OrderShipmentPackage with ID ' . $orderShipmentPackage->getId() . ' not found');
        }

        $sql = "UPDATE `" . $this->tableName . "` SET 
            `order_shipment_id` = '" . $orderShipmentPackage->getOrderShipmentId() . "',
            `shipping_settings_template_id` = '" . $orderShipmentPackage->getShippingSettingsTemplateId() . "',
            `package_type` = '" . $this->db->escape($orderShipmentPackage->getPackageType()) . "',
            `pickup_type` = " . ($orderShipmentPackage->getPickupType() ? "'" . $this->db->escape($orderShipmentPackage->getPickupType()) . "'" : "NULL") . ",
            `length` = '" . $orderShipmentPackage->getLength() . "',
            `width` = '" . $orderShipmentPackage->getWidth() . "',
            `height` = '" . $orderShipmentPackage->getHeight() . "',
            `weight` = '" . $orderShipmentPackage->getWeight() . "',
            `cash_on_delivery` = " . ($orderShipmentPackage->getCashOnDelivery() ? "'" . $orderShipmentPackage->getCashOnDelivery() . "'" : "NULL") . ",
            `declared_value` = " . ($orderShipmentPackage->getDeclaredValue() ? "'" . $orderShipmentPackage->getDeclaredValue() . "'" : "NULL") . ",
            `content_text` = " . ($orderShipmentPackage->getContentText() ? "'" . $this->db->escape($orderShipmentPackage->getContentText()) . "'" : "NULL") . ",
            `updated_at` = '" . $this->db->escape($orderShipmentPackage->getUpdatedAt()) . "' 
            WHERE `id` = '" . (int)$orderShipmentPackage->getId() . "'";

        $this->db->query($sql);
    }

    public function deleteById(int $id): void
    {
        $this->db->query("DELETE FROM `" . $this->tableName . "` WHERE `id` = '" . $id . "'");
    }

    public function deleteByOrderShipmentId(int $orderShipmentId): void
    {
        $this->db->query("DELETE FROM `" . $this->tableName . "` WHERE `order_shipment_id` = '" . $orderShipmentId . "'");
    }

    private function createOrderShipmentPackageFromRow(array $row): OrderShipmentPackage
    {
        return new OrderShipmentPackage(
            (int)$row['id'],
            (int)$row['order_shipment_id'],
            $row['shipping_settings_template_id'],
            $row['template_name'],
            $row['package_type'],
            $row['pickup_type'],
            (int)$row['length'],
            (int)$row['width'],
            (int)$row['height'],
            (int)$row['weight'],
            $row['cash_on_delivery'] ? (int)$row['cash_on_delivery'] : null,
            $row['declared_value'] ? (int)$row['declared_value'] : null,
            $row['content_text'],
            $row['created_at'],
            $row['updated_at']
        );
    }
}
