<?php

namespace Core\App\Models;

use PDO;

class ShippingPriceModel
{
    protected PDO $db;

    public function __construct()
    {
        $this->db = \db();
    }

    /**
     * =========================================
     * Get price by provider / wilaya / type
     * =========================================
     */
    public function getPrice(
        string $provider,
        string $wilayaCode,
        string $deliveryType
    ): ?array {
        $stmt = $this->db->prepare(
            "SELECT *
             FROM shipping_prices
             WHERE provider = :provider
               AND wilaya_code = :wilaya
               AND delivery_type = :type
               AND is_active = 1
             LIMIT 1"
        );

        $stmt->execute([
            ':provider' => $provider,
            ':wilaya'   => $wilayaCode,
            ':type'     => $deliveryType,
        ]);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }

    /**
     * =========================================
     * Get all prices for provider
     * =========================================
     */
    public function getAllByProvider(string $provider): array
    {
        $stmt = $this->db->prepare(
            "SELECT *
             FROM shipping_prices
             WHERE provider = :provider
             ORDER BY wilaya_name ASC"
        );

        $stmt->execute([
            ':provider' => $provider
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }


    public function deletePrice(string $provider, string $wilayaCode, string $deliveryType): void
    {
        $stmt = $this->db->prepare(
            "DELETE FROM shipping_prices
             WHERE provider = :provider
               AND wilaya_code = :wilaya
               AND delivery_type = :type"
        );

        $stmt->execute([
            ':provider' => $provider,
            ':wilaya'   => $wilayaCode,
            ':type'     => $deliveryType,
        ]);
    }

    /**
     * =========================================
     * Save or Update price
     * =========================================
     */
    public function save(array $data): void
    {
        $stmt = $this->db->prepare(
            "INSERT INTO shipping_prices
                (provider, country_code, wilaya_code, wilaya_name,
                 delivery_type, price, return_price, is_active)
             VALUES
                (:provider, :country, :code, :name,
                 :type, :price, :return_price, :active)
             ON DUPLICATE KEY UPDATE
                price = VALUES(price),
                return_price = VALUES(return_price),
                is_active = VALUES(is_active),
                updated_at = CURRENT_TIMESTAMP"
        );

        $stmt->execute([
            ':provider'      => $data['provider'],
            ':country'       => $data['country_code'] ?? 'DZ',
            ':code'          => $data['wilaya_code'],
            ':name'          => $data['wilaya_name'],
            ':type'          => $data['delivery_type'], // home | desk
            ':price'         => $data['price'],
            ':return_price'  => $data['return_price'] ?? 0,
            ':active'        => $data['is_active'] ?? 1,
        ]);
    }
}
