<?php
/**
 * ShippingRate — Rate data access and cost calculation.
 *
 * Manages tc_shipping_rates + tc_shipping_rate_conditions.
 *
 * A "rate" ties a shipping method to a zone and declares how the cost is
 * calculated.  One or more "condition" rows define the actual cost table.
 *
 * Rate types and condition semantics:
 *   flat       — Single condition row; price field is the fixed cost.
 *   free       — Always £0 / $0.  Conditions are ignored.
 *   by_weight  — Conditions are weight bands: from_value <= weight <= to_value → price.
 *   by_price   — Conditions are subtotal bands: from_value <= subtotal <= to_value → price.
 *   by_items   — Conditions are item-count bands: from_value <= items <= to_value → price.
 *
 * For range-based types, to_value = NULL means "no upper limit" (open-ended band).
 * Bands are evaluated in ascending from_value order; first match wins.
 *
 * @package TitanCart\Modules\Shipping
 */

namespace TitanCart\Modules\Shipping;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

class ShippingRate {

    private Database $db;

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

    // ══════════════════════════════════════════════════════════════════════════
    // RATE CRUD
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Return all rates for a method, with conditions hydrated.
     *
     * @param  int $method_id
     * @return array[]
     */
    public function get_for_method( int $method_id ): array {
        $rates = $this->db->getArray(
            "SELECT * FROM {$this->db->table('shipping_rates')}
             WHERE method_id = ?i
             ORDER BY position ASC, id ASC",
            $method_id
        );

        foreach ( $rates as &$rate ) {
            $rate['conditions'] = $this->get_conditions( (int) $rate['id'] );
        }

        return $rates;
    }

    /**
     * Return a single rate row (with conditions), or null.
     *
     * @param  int $id
     * @return array|null
     */
    public function get_by_id( int $id ): ?array {
        $rate = $this->db->getRow(
            "SELECT * FROM {$this->db->table('shipping_rates')} WHERE id = ?i LIMIT 1",
            $id
        );

        if ( ! $rate ) {
            return null;
        }

        $rate['conditions'] = $this->get_conditions( $id );
        return $rate;
    }

    /**
     * Create a rate row along with its conditions.
     *
     * @param  array   $data        {method_id, zone_id, rate_type, position?}
     * @param  array[] $conditions  Each: {from_value, to_value?, price}
     * @return int  New rate ID.
     */
    public function create( array $data, array $conditions = [] ): int {
        $rate_type = in_array(
            $data['rate_type'] ?? 'flat',
            [ 'flat', 'by_weight', 'by_price', 'by_items', 'free' ],
            true
        ) ? $data['rate_type'] : 'flat';

        $this->db->query(
            "INSERT INTO {$this->db->table('shipping_rates')}
             (method_id, zone_id, rate_type, position)
             VALUES (?i, ?i, ?s, ?i)",
            (int) $data['method_id'],
            (int) $data['zone_id'],
            $rate_type,
            (int) ( $data['position'] ?? 0 )
        );

        $rate_id = $this->db->lastInsertId();

        foreach ( $conditions as $condition ) {
            $this->add_condition( $rate_id, $condition );
        }

        return $rate_id;
    }

    /**
     * Update rate fields (does not touch conditions).
     *
     * @param  int   $id
     * @param  array $data  Subset of {method_id, zone_id, rate_type, position}
     * @return bool
     */
    public function update( int $id, array $data ): bool {
        $allowed = [ 'method_id', 'zone_id', 'rate_type', 'position' ];
        $fields  = array_intersect_key( $data, array_flip( $allowed ) );

        if ( empty( $fields ) ) {
            return false;
        }

        $this->db->query(
            "UPDATE {$this->db->table('shipping_rates')} SET ?u WHERE id = ?i",
            $fields,
            $id
        );

        return true;
    }

    /**
     * Delete a rate and all its condition rows.
     *
     * @param  int $id
     * @return bool
     */
    public function delete( int $id ): bool {
        $this->db->query(
            "DELETE FROM {$this->db->table('shipping_rate_conditions')} WHERE rate_id = ?i",
            $id
        );
        $this->db->query(
            "DELETE FROM {$this->db->table('shipping_rates')} WHERE id = ?i",
            $id
        );

        return true;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // CONDITION CRUD
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Return all condition rows for a rate, sorted by from_value ascending.
     *
     * @param  int $rate_id
     * @return array[]
     */
    public function get_conditions( int $rate_id ): array {
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('shipping_rate_conditions')}
             WHERE rate_id = ?i
             ORDER BY from_value ASC",
            $rate_id
        );
    }

    /**
     * Add a single condition row to an existing rate.
     *
     * @param  int   $rate_id
     * @param  array $data    {from_value, to_value?, price}
     * @return int   New condition ID.
     */
    public function add_condition( int $rate_id, array $data ): int {
        // to_value is nullable — NULL means open-ended upper bound ("and up").
        $to_value = isset( $data['to_value'] ) && $data['to_value'] !== '' && $data['to_value'] !== null
            ? (string) $data['to_value']
            : null;

        $price_type = isset( $data['price_type'] ) && $data['price_type'] === 'percent'
            ? 'percent'
            : 'fixed';

        $this->db->query(
            "INSERT INTO {$this->db->table('shipping_rate_conditions')}
             (rate_id, from_value, to_value, price, price_type)
             VALUES (?i, ?s, ?s, ?s, ?s)",
            $rate_id,
            (string) ( $data['from_value'] ?? '0.0000' ),
            $to_value,
            (string) ( $data['price'] ?? '0.0000' ),
            $price_type
        );

        return $this->db->lastInsertId();
    }

    /**
     * Replace all conditions for a rate (delete existing, insert new set).
     *
     * @param  int     $rate_id
     * @param  array[] $conditions
     * @return void
     */
    public function replace_conditions( int $rate_id, array $conditions ): void {
        $this->db->query(
            "DELETE FROM {$this->db->table('shipping_rate_conditions')} WHERE rate_id = ?i",
            $rate_id
        );

        foreach ( $conditions as $condition ) {
            $this->add_condition( $rate_id, $condition );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // COST CALCULATION
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Calculate the shipping cost for a rate row given the cart totals.
     *
     * @param  array $rate   Rate row including 'rate_type' and 'conditions' (hydrated).
     * @param  array $totals {subtotal, item_count, total_weight}
     * @return float|null    Calculated cost, or null if the rate doesn't apply
     *                       (e.g. no matching weight band).
     */
    public function calculate( array $rate, array $totals ): ?float {
        $type       = $rate['rate_type'] ?? 'flat';
        $conditions = $rate['conditions'] ?? [];

        switch ( $type ) {

            case 'free':
                return 0.0;

            case 'flat':
                // The single condition row holds the flat cost in its price field.
                if ( ! empty( $conditions ) ) {
                    return (float) $conditions[0]['price'];
                }
                // Flat rate with no condition rows configured — treat as free.
                return 0.0;

            case 'by_weight':
                return $this->range_lookup( $conditions, (float) ( $totals['total_weight'] ?? 0 ) );

            case 'by_price':
                return $this->range_lookup( $conditions, (float) ( $totals['subtotal'] ?? 0 ) );

            case 'by_items':
                return $this->range_lookup( $conditions, (float) ( $totals['item_count'] ?? 0 ) );

            default:
                return null;
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // INTERNAL HELPERS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Find the cost for $value in a range-condition table.
     *
     * Each condition has from_value, to_value (NULL = ∞), price.
     * Conditions are pre-sorted ascending by from_value.
     * Returns the price from the first band where from_value ≤ $value ≤ to_value.
     * Returns null if no band matches (method unavailable for this cart).
     *
     * @param  array[] $conditions
     * @param  float   $value
     * @return float|null
     */
    private function range_lookup( array $conditions, float $value ): ?float {
        foreach ( $conditions as $c ) {
            $from = (float) $c['from_value'];
            $to   = $c['to_value'] !== null ? (float) $c['to_value'] : PHP_FLOAT_MAX;

            if ( $value >= $from && $value <= $to ) {
                return (float) $c['price'];
            }
        }

        return null;
    }
}
