<?php

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

class AIEO_Fulfillment_Vouchers {

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

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

    
    public static function create(array $data) {
        global $wpdb;

        $order_id   = isset($data['order_id'])   ? (int)    $data['order_id']   : 0;
        $carrier    = isset($data['carrier'])    ? sanitize_key((string) $data['carrier']) : '';
        $voucher_no = isset($data['voucher_no']) ? sanitize_text_field((string) $data['voucher_no']) : '';
        if ($order_id <= 0 || $carrier === '' || $voucher_no === '') {
            return array('ok' => false, 'error' => 'order_id, carrier, voucher_no required');
        }

        
        $existing = self::find_by_voucher_no($carrier, $voucher_no);
        if ($existing) {
            return array('ok' => true, 'id' => (int) $existing['id'], 'duplicate' => true);
        }

        $row = self::sanitize_row(array_merge(array(
            'pieces'       => 1,
            'status'       => 'created',
            'services_csv' => '',
        ), $data));
        $row['order_id']   = $order_id;
        $row['carrier']    = $carrier;
        $row['voucher_no'] = $voucher_no;
        $row['created_at'] = current_time('mysql', true);
        $row['updated_at'] = current_time('mysql', true);

        
        
        
        
        
        
        if (!isset($row['postal_code']) || $row['postal_code'] === '') {
            $pc = self::resolve_order_shipping_postcode($order_id);
            if ($pc !== '') { $row['postal_code'] = $pc; }
        }

        $r = $wpdb->insert(self::table(), $row);
        if ($r === false) {
            return array('ok' => false, 'error' => 'INSERT failed: ' . $wpdb->last_error);
        }
        
        
        $new_id = (int) $wpdb->insert_id;

        
        if (function_exists('wc_get_order')) {
            $order = wc_get_order($order_id);
            if ($order) {
                $order->update_meta_data('_aieo_fulfillment_linked', 1);
                $order->save_meta_data();
            }
        }

        return array('ok' => true, 'id' => $new_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_row($data);
        unset($row['order_id'], $row['carrier'], $row['voucher_no'], $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); }
        return array('ok' => true, 'rows' => (int) $r);
    }

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

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

    
    public static function mark_printed($voucher_id) {
        global $wpdb;
        $id = (int) $voucher_id;
        if ($id <= 0) { return false; }
        $now = current_time('mysql', true);
        $ok  = $wpdb->query($wpdb->prepare(
            'UPDATE ' . self::table() . '
             SET printed_at = %s,
                 print_count = COALESCE(print_count, 0) + 1
             WHERE id = %d',
            $now, $id
        ));
        return (bool) $ok;
    }

    
    public static function mark_printed_for_order($order_id) {
        global $wpdb;
        $order_id = (int) $order_id;
        if ($order_id <= 0) { return 0; }
        $now = current_time('mysql', true);
        return (int) $wpdb->query($wpdb->prepare(
            'UPDATE ' . self::table() . '
             SET printed_at = %s,
                 print_count = COALESCE(print_count, 0) + 1
             WHERE order_id = %d',
            $now, $order_id
        ));
    }

    
    public static function has_printed_for_order($order_id) {
        global $wpdb;
        $order_id = (int) $order_id;
        if ($order_id <= 0) { return false; }
        $count = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(*) FROM ' . self::table() . '
             WHERE order_id = %d AND printed_at IS NOT NULL',
            $order_id
        ));
        return $count > 0;
    }

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

    
    public static function find_by_voucher_no($carrier, $voucher_no) {
        global $wpdb;
        $carrier    = sanitize_key((string) $carrier);
        $voucher_no = (string) $voucher_no;
        if ($carrier === '' || $voucher_no === '') { return null; }
        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE carrier = %s AND voucher_no = %s LIMIT 1',
                $carrier, $voucher_no
            ),
            ARRAY_A
        );
        return is_array($row) ? $row : null;
    }

    
    public static function update_status($id, $status, $status_code = '', $delivered_at = null, $consignee = null, $arrived_at = null, $arrived_status = null) {
        $patch = array(
            'status'         => sanitize_key((string) $status),
            'status_code'    => sanitize_text_field((string) $status_code),
            'status_at'      => current_time('mysql', true),
            'last_polled_at' => current_time('mysql', true),
        );
        if ($delivered_at !== null) {
            $patch['delivered_at'] = gmdate('Y-m-d H:i:s', (int) $delivered_at);
        }
        if ($consignee !== null) {
            $patch['consignee'] = sanitize_text_field((string) $consignee);
        }
        if ($arrived_at !== null) {
            $patch['arrived_at'] = gmdate('Y-m-d H:i:s', (int) $arrived_at);
        }
        if ($arrived_status !== null) {
            $patch['arrived_status'] = sanitize_key((string) $arrived_status);
        }
        return self::update($id, $patch);
    }

    
    public static function record_checkpoints($voucher_id, array $checkpoints) {
        global $wpdb;
        $voucher_id = (int) $voucher_id;
        if ($voucher_id <= 0 || empty($checkpoints)) { return 0; }

        $inserted = 0;
        $table = self::checkpoints_table();
        foreach ($checkpoints as $cp) {
            $code  = sanitize_text_field((string) ($cp['code'] ?? ''));
            $desc  = (string) ($cp['description'] ?? '');
            $loc   = sanitize_text_field((string) ($cp['location'] ?? ''));
            $when  = isset($cp['checkpoint_at']) && is_int($cp['checkpoint_at'])
                ? gmdate('Y-m-d H:i:s', (int) $cp['checkpoint_at'])
                : null;
            
            $sql = $wpdb->prepare(
                "INSERT IGNORE INTO {$table} (voucher_id, code, description, location, checkpoint_at) VALUES (%d, %s, %s, %s, %s)",
                $voucher_id, $code, $desc, $loc, $when
            );
            $r = $wpdb->query($sql);
            if ($r) { $inserted++; }
        }
        return $inserted;
    }

    
    private static function sanitize_row(array $data) {
        $allowed = array(
            'order_id'             => 'int',
            'carrier'              => 'key',
            'carrier_account'      => 'text',
            'voucher_no'           => 'text',
            'parent_voucher_no'    => 'text',
            'job_id'               => 'int',
            'pieces'               => 'int',
            'weight'               => 'decimal',
            'cod_amount'           => 'decimal',
            'ins_amount'           => 'decimal',
            'carrier_cost'         => 'decimal',
            'carrier_vat'          => 'decimal',
            'cost_source'          => 'key',
            'customer_shipping_paid' => 'decimal',
            'services_csv'         => 'text',
            'content_type_id'      => 'int',
            'content_description'  => 'text',
            'reference_1'          => 'text',
            'reference_2'          => 'text',
            'status'               => 'key',
            'status_code'          => 'text',
            'status_at'            => 'datetime',
            'consignee'            => 'text',
            'postal_code'          => 'text',
            'arrived_at'           => 'datetime',
            'arrived_status'       => 'key',
            'delivered_at'         => 'datetime',
            'label_pdf_path'       => 'text',
            'last_polled_at'       => 'datetime',
            'closed_at'            => 'datetime',
            'canceled_at'          => 'datetime',
            'printed_at'           => 'datetime',
            'print_count'          => 'int',
            'created_at'           => 'datetime',
            'updated_at'           => 'datetime',
        );
        $out = array();
        foreach ($allowed as $col => $type) {
            if (!array_key_exists($col, $data)) { continue; }
            $val = $data[$col];
            if ($val === null) { $out[$col] = null; continue; }
            switch ($type) {
                case 'int':      $out[$col] = (int) $val; break;
                case 'decimal':  $out[$col] = is_numeric($val) ? (string) (float) $val : null; break;
                case 'key':      $out[$col] = sanitize_key((string) $val); break;
                case 'text':     $out[$col] = sanitize_text_field((string) $val); break;
                case 'datetime': $out[$col] = (string) $val; break;
            }
        }
        return $out;
    }

    
    private static function resolve_order_shipping_postcode($order_id) {
        $order_id = (int) $order_id;
        if ($order_id <= 0 || !function_exists('wc_get_order')) { return ''; }
        $order = wc_get_order($order_id);
        if (!$order || !is_object($order)) { return ''; }
        $pc = '';
        if (method_exists($order, 'get_shipping_postcode')) {
            $pc = trim((string) $order->get_shipping_postcode());
        }
        if ($pc === '' && method_exists($order, 'get_billing_postcode')) {
            $pc = trim((string) $order->get_billing_postcode());
        }
        return $pc;
    }
}
