<?php

namespace Opencart\Extension\Alsendo\Repositories;

require_once(DIR_EXTENSION . 'alsendo/src/DTO/Entity/AlsendoRegionEntity.php');
require_once(DIR_EXTENSION . 'alsendo/src/Models/OrderShipment.php');

use Opencart\Extension\Alsendo\DTO\Entity\AlsendoRegionEntity;
use Opencart\Extension\Alsendo\Models\OrderShipment;

class OrderShipmentRepository
{
    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';
    }

    public function getLatestNotCancelledByOrderId(int $orderId): ?OrderShipment
    {
        $query = $this->db->query("
            SELECT * FROM `" . $this->tableName . "`
            WHERE `order_id` = '" . (int)$orderId . "'
              AND `status` != 'cancelled'
            ORDER BY `created_at` DESC
            LIMIT 1
        ");

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

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

    public function getByExternalId(int $externalId): ?OrderShipment
    {
        $query = $this->db->query("SELECT * FROM `" . $this->tableName . "` WHERE `external_id` = '" . $externalId . "'");

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

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

    public function getAllByOrderId(int $orderId): array
    {
        $query = $this->db->query("
            SELECT * FROM `" . $this->tableName . "`
            WHERE `order_id` = '" . (int)$orderId . "'
            ORDER BY `created_at` DESC
        ");

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

        return $orderShipments;
    }

    public function upsert(OrderShipment $orderShipment): int
    {
        if ($orderShipment->getId()) {
            $this->updateOrderShipment($orderShipment);
            return $orderShipment->getId();
        }

        $sql = "INSERT INTO `" . $this->tableName . "` SET 
            `order_id` = '" . $orderShipment->getOrderId() . "',
            `region_id` = '" . $orderShipment->getRegionId() . "',
            `external_id` = " . ($orderShipment->getExternalId() ? "'" . $orderShipment->getExternalId() . "'" : "NULL") . ",
            `status` = '" . $this->db->escape($orderShipment->getStatus()) . "',
            `data` = " . ($orderShipment->getData() ? "'" . $this->db->escape(json_encode($orderShipment->getData())) . "'" : "NULL") . ",
            `created_at` = '" . $this->db->escape($orderShipment->getCreatedAt()) . "',
            `updated_at` = '" . $this->db->escape($orderShipment->getUpdatedAt()) . "'";

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

    private function updateOrderShipment(OrderShipment $orderShipment): void
    {
        if (!$orderShipment->getId()) {
            return;
        }

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

        $sql = "UPDATE `" . $this->tableName . "` SET 
            `order_id` = '" . (int)$orderShipment->getOrderId() . "',
            `external_id` = " . ($orderShipment->getExternalId() ? "'" . $orderShipment->getExternalId(). "'" : "NULL") . ",
            `status` = '" . $this->db->escape($orderShipment->getStatus()) . "', 
            `data` = " . ($orderShipment->getData() ? "'" . $this->db->escape(json_encode($orderShipment->getData())) . "'" : "NULL") . ",
            `updated_at` = '" . $this->db->escape($orderShipment->getUpdatedAt()) . "' 
            WHERE `id` = '" . (int)$orderShipment->getId() . "'";

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

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

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

    public function getTotalCount(): int
    {
        $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `".$this->tableName."`");

        return (int) $query->row['total'];
    }

    public function findMultipleShipmentsByFilters(array $data): array
    {
        $sql = "SELECT * FROM `".$this->tableName."` aos";

        $sql .= " WHERE 1 = 1";

        if (!empty($data['filter_date_from'])) {
            $sql .= " AND DATE(aos.`created_at`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
        }

        if (!empty($data['filter_date_to'])) {
            $sql .= " AND DATE(aos.`created_at`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
        }

        if (!empty($data['region_id'])) {
            $sql .= " AND `region_id` = '".(int)$data['region_id']."'";
        }

        $sort_data = [
            'aos.id',
            'aos.order_id',
            'aos.created_at',
            'aos.updated_at',
        ];

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            $sql .= " ORDER BY " . $data['sort'];
        } else {
            $sql .= " ORDER BY aos.`id`";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

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

        return $query->rows;
    }

    private function createOrderShipmentFromRow(array $row): OrderShipment
    {
        $data = null;
        if ($row['data']) {
            $decoded = json_decode($row['data'], true);
            $data = json_last_error() === JSON_ERROR_NONE ? $decoded : null;
        }
        
        return new OrderShipment(
            (int)$row['region_id'],
            (int)$row['id'],
            (int)$row['order_id'],
            $data,
            $row['created_at'],
            $row['updated_at'],
            $row['external_id'],
            $row['status']
        );
    }
}
