<?php

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

class AIEO_Fulfillment_Documents {

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

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

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

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

        
        $existing = self::find_by_number($type, $number);
        if ($existing) {
            return array('ok' => true, 'id' => (int) $existing['id'], 'duplicate' => true);
        }

        $row = self::sanitize_row($data);
        $row['order_id']   = $order_id;
        $row['type']       = $type;
        $row['number']     = $number;
        $row['created_at'] = current_time('mysql', true);
        $row['updated_at'] = current_time('mysql', true);
        if (empty($row['issued_at'])) { $row['issued_at'] = current_time('mysql', true); }
        if (empty($row['language']))  { $row['language']  = (string) AIEO_Fulfillment_Settings::get('general.language', 'el'); }

        $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['type'], $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);
    }

    public static function delete($id) {
        global $wpdb;
        $id  = (int) $id;
        if ($id <= 0) { return false; }
        $row = $wpdb->get_row($wpdb->prepare('SELECT pdf_path FROM ' . self::table() . ' WHERE id = %d', $id), ARRAY_A);
        if (!empty($row['pdf_path'])) {
            $abs = WP_CONTENT_DIR . '/uploads/' . ltrim((string) $row['pdf_path'], '/');
            if (file_exists($abs)) { @wp_delete_file($abs); }
        }
        $r = $wpdb->delete(self::table(), array('id' => $id), array('%d'));
        return $r !== false && $r > 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_order($order_id) {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE order_id = %d ORDER BY type ASC, id ASC', (int) $order_id),
            ARRAY_A
        );
        return is_array($rows) ? $rows : array();
    }

    public static function get_for_order_by_type($order_id, $type) {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE order_id = %d AND type = %s ORDER BY id DESC LIMIT 1',
                (int) $order_id, sanitize_key((string) $type)
            ),
            ARRAY_A
        );
        return !empty($rows) ? $rows[0] : null;
    }

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

    
    public static function next_sequence_value($type, $year = null, $start = 1) {
        global $wpdb;
        $type  = sanitize_key((string) $type);
        $year  = $year !== null ? (int) $year : (int) gmdate('Y');
        $start = max(1, (int) $start);
        if ($type === '') { return 0; }

        $tbl = self::seq_table();
        
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$tbl} (`type`, `year`, `next_value`) VALUES (%s, %d, %d)
             ON DUPLICATE KEY UPDATE `next_value` = `next_value` + 1",
            $type, $year, $start + 1
        ));
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT next_value FROM {$tbl} WHERE `type` = %s AND `year` = %d",
            $type, $year
        ), ARRAY_A);
        if (!$row) { return $start; }
        
        
        return max(1, ((int) $row['next_value']) - 1);
    }

    private static function sanitize_row(array $data) {
        $allowed = array(
            'order_id'           => 'int',
            'type'               => 'key',
            'number'             => 'text',
            'number_seq'         => 'int',
            'sequence_year'      => 'int',
            'issued_at'          => 'datetime',
            'due_at'             => 'datetime',
            'language'           => 'text',
            'template_id'        => 'text',
            'pdf_path'           => 'text',
            'pdf_hash'           => 'text',
            'attached_to_emails' => 'text',
            'created_at'         => 'datetime',
            'updated_at'         => 'datetime',
        );
        $out = array();
        foreach ($allowed as $col => $kind) {
            if (!array_key_exists($col, $data)) { continue; }
            $val = $data[$col];
            if ($val === null) { $out[$col] = null; continue; }
            switch ($kind) {
                case 'int':      $out[$col] = (int) $val; 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;
    }

    
    public static function cache_pdf($document_id, $type, $number, $pdf_bytes) {
        $upload = wp_upload_dir(null, false);
        $year   = gmdate('Y');
        $month  = gmdate('m');
        $base   = $upload['basedir'] . '/aieo-dmm/fulfillment/' . sanitize_file_name($type) . '/' . $year . '/' . $month;
        if (!wp_mkdir_p($base)) { return ''; }

        $name = sanitize_file_name($type . '-' . $number . '.pdf');
        $abs  = $base . '/' . $name;
        
        if (file_put_contents($abs, $pdf_bytes) === false) { return ''; }

        $rel  = 'aieo-dmm/fulfillment/' . sanitize_file_name($type) . '/' . $year . '/' . $month . '/' . $name;
        $hash = hash('sha256', $pdf_bytes);
        self::update($document_id, array('pdf_path' => $rel, 'pdf_hash' => $hash));
        return $rel;
    }

    
    public static function mark_printed($document_id) {
        global $wpdb;
        $id = (int) $document_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 has_printed_for_order($order_id, $type) {
        global $wpdb;
        $order_id = (int) $order_id;
        $type     = sanitize_key((string) $type);
        if ($order_id <= 0 || $type === '') { return false; }
        $count = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM " . self::table() . "
             WHERE order_id = %d AND type = %s
               AND printed_at IS NOT NULL",
            $order_id, $type
        ));
        return $count > 0;
    }
}
