<?php

if (!defined('ABSPATH')) { exit; }

class AIEO_TRS_Rules {

    private static function table() {
        global $wpdb;
        return $wpdb->prefix . 'aieo_dmm_shipping_rates';
    }

    
    private static function column_specs() {
        return array(
            'shipping_method_id'        => 'int',
            'legacy_rate_id'            => 'int_or_null',
            'rate_class'                => 'text',
            'rate_condition'            => 'text',
            'rate_min'                  => 'decimal_or_null',
            'rate_max'                  => 'decimal_or_null',
            'rate_cost'                 => 'decimal',
            'rate_cost_per_item'        => 'decimal',
            'rate_cost_per_weight_unit' => 'decimal',
            'rate_cost_percent'         => 'decimal',
            'rate_label'                => 'text',
            'rate_priority'             => 'int',
            'rate_order'                => 'int',
            'rate_abort'                => 'int',
            'rate_abort_reason'         => 'text',
            'enabled'                   => 'int',
        );
    }

    public static function create(array $data) {
        global $wpdb;
        $row = self::sanitize($data);
        if (empty($row['shipping_method_id'])) {
            return array('ok' => false, 'error' => 'shipping_method_id required');
        }
        $row['created_at'] = current_time('mysql', true);
        $row['updated_at'] = current_time('mysql', true);
        $r = $wpdb->insert(self::table(), $row);
        if ($r === false) {
            return array('ok' => false, 'error' => 'INSERT failed: ' . $wpdb->last_error);
        }
        $id = (int) $wpdb->insert_id;
        self::bust_shipping_cache();
        return array('ok' => true, 'id' => $id);
    }

    public static function update($id, array $data) {
        global $wpdb;
        $id = (int) $id;
        if ($id <= 0) { return array('ok' => false, 'error' => 'invalid id'); }
        $row = self::sanitize($data);
        unset($row['shipping_method_id'], $row['legacy_rate_id'], $row['created_at']);
        $row['updated_at'] = current_time('mysql', true);
        if (empty($row)) { return array('ok' => true, 'noop' => true); }
        $r = $wpdb->update(self::table(), $row, array('id' => $id));
        if ($r === false) { return array('ok' => false, 'error' => 'UPDATE failed: ' . $wpdb->last_error); }
        self::bust_shipping_cache();
        return array('ok' => true, 'rows' => (int) $r);
    }

    public static function delete($id) {
        global $wpdb;
        $rows = $wpdb->delete(self::table(), array('id' => (int) $id), array('%d'));
        if ($rows !== false && $rows > 0) { self::bust_shipping_cache(); }
        return $rows !== false && $rows > 0;
    }

    public static function delete_for_instance($shipping_method_id) {
        global $wpdb;
        $rows = $wpdb->delete(self::table(), array('shipping_method_id' => (int) $shipping_method_id), array('%d'));
        if ($rows !== false && $rows > 0) { self::bust_shipping_cache(); }
        return is_int($rows) ? $rows : 0;
    }

    public static function get($id) {
        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE id = %d', (int) $id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function get_for_instance($shipping_method_id) {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE shipping_method_id = %d ORDER BY rate_order ASC, id ASC',
                (int) $shipping_method_id
            ),
            ARRAY_A
        );
        return is_array($rows) ? $rows : array();
    }

    public static function find_by_legacy($legacy_rate_id) {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE legacy_rate_id = %d LIMIT 1', (int) $legacy_rate_id),
            ARRAY_A
        );
        return is_array($row) ? $row : null;
    }

    public static function reorder(array $id_order) {
        global $wpdb;
        $i = 0;
        foreach ($id_order as $id) {
            $i++;
            $wpdb->update(self::table(), array('rate_order' => $i, 'updated_at' => current_time('mysql', true)), array('id' => (int) $id));
        }
        self::bust_shipping_cache();
        return $i;
    }

    public static function count_for_instance($shipping_method_id) {
        global $wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare('SELECT COUNT(*) FROM ' . self::table() . ' WHERE shipping_method_id = %d', (int) $shipping_method_id)
        );
    }

    
    public static function count_all() {
        global $wpdb;
        return (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . self::table());
    }

    private static function sanitize(array $data) {
        $specs = self::column_specs();
        $out = array();
        foreach ($specs as $col => $kind) {
            if (!array_key_exists($col, $data)) { continue; }
            $val = $data[$col];
            switch ($kind) {
                case 'int':
                    $out[$col] = (int) $val;
                    break;
                case 'int_or_null':
                    $out[$col] = ($val === null || $val === '') ? null : (int) $val;
                    break;
                case 'decimal':
                    $out[$col] = is_numeric($val) ? (string) (float) $val : '0';
                    break;
                case 'decimal_or_null':
                    $out[$col] = ($val === null || $val === '' || !is_numeric($val)) ? null : (string) (float) $val;
                    break;
                case 'text':
                    $out[$col] = sanitize_text_field((string) $val);
                    break;
            }
        }
        return $out;
    }

    
    public static function bust_shipping_cache() {
        if (class_exists('WC_Cache_Helper') && method_exists('WC_Cache_Helper', 'get_transient_version')) {
            WC_Cache_Helper::get_transient_version('shipping', true);
        }
    }
}
