<?php
if (!defined('ABSPATH')) exit;

/**
 * MetaBox custom table fields linked to post types.
 */
class AISB_MetaBox_Fields {

    public static function is_active() {
        return function_exists('rwmb_meta') || class_exists('\MetaBox\CustomTable\API');
    }

    /** @return array[] keyed by post_type */
    public static function discover_tables() {
        $boxes = self::get_meta_boxes();
        $out = [];
        foreach ($boxes as $box) {
            if (!is_array($box)) {
                continue;
            }
            $storage = $box['storage_type'] ?? '';
            $table = $box['table'] ?? '';
            if ($storage !== 'custom_table' || !$table) {
                continue;
            }
            $post_types = (array) ($box['post_types'] ?? []);
            $field_ids = [];
            foreach ((array) ($box['fields'] ?? []) as $field) {
                $id = $field['id'] ?? $field['name'] ?? '';
                if ($id) {
                    $field_ids[] = sanitize_key($id);
                }
            }
            foreach ($post_types as $pt) {
                $pt = sanitize_key($pt);
                if (!$pt) {
                    continue;
                }
                if (!isset($out[$pt])) {
                    $out[$pt] = [
                        'post_type' => $pt,
                        'tables'    => [],
                        'fields'    => [],
                    ];
                }
                $out[$pt]['tables'][] = sanitize_key($table);
                $out[$pt]['fields'] = array_values(array_unique(array_merge($out[$pt]['fields'], $field_ids)));
            }
        }
        return apply_filters('aisb_metabox_tables_discover', $out);
    }

    public static function get_row_for_post($post_id) {
        $post_id = (int) $post_id;
        if (!$post_id) {
            return [];
        }
        $post_type = get_post_type($post_id);
        $discovered = self::discover_tables();
        if (empty($discovered[$post_type]['tables'])) {
            return self::get_rwmb_meta_fallback($post_id);
        }
        $merged = [];
        foreach (array_unique($discovered[$post_type]['tables']) as $table) {
            $row = self::get_table_row($post_id, $table);
            if ($row) {
                $merged = array_merge($merged, $row);
            }
        }
        if (empty($merged)) {
            $merged = self::get_rwmb_meta_fallback($post_id);
        }
        return self::normalize_row($merged);
    }

    private static function get_table_row($post_id, $table) {
        if (class_exists('\MetaBox\CustomTable\API') && method_exists('\MetaBox\CustomTable\API', 'get')) {
            $row = \MetaBox\CustomTable\API::get($post_id, $table);
            return is_array($row) ? $row : [];
        }
        global $wpdb;
        $table = preg_replace('/[^a-z0-9_]/i', '', $table);
        if (!$table) {
            return [];
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- MetaBox table name sanitized to [a-z0-9_]; post ID is parameterized.
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$table}` WHERE ID = %d LIMIT 1", $post_id), ARRAY_A);
        return is_array($row) ? $row : [];
    }

    private static function get_rwmb_meta_fallback($post_id) {
        if (!function_exists('rwmb_get_object_fields')) {
            return [];
        }
        $post_type = get_post_type($post_id);
        $fields = rwmb_get_object_fields($post_type, 'post');
        if (!is_array($fields)) {
            return [];
        }
        $out = [];
        foreach ($fields as $id => $field) {
            if (!function_exists('rwmb_get_value')) {
                break;
            }
            $val = rwmb_get_value($id, [], $post_id);
            if ($val !== '' && $val !== null && $val !== []) {
                $out[$id] = is_scalar($val) ? $val : wp_json_encode($val);
            }
        }
        return $out;
    }

    private static function get_meta_boxes() {
        $boxes = apply_filters('aisb_metabox_meta_boxes', get_option('rwmb_meta_boxes', []));
        if (is_array($boxes) && !empty($boxes)) {
            return $boxes;
        }
        if (function_exists('rwmb_get_registry')) {
            $registry = rwmb_get_registry('meta_box');
            if (is_object($registry) && method_exists($registry, 'get_by')) {
                $all = [];
                foreach (get_post_types([], 'names') as $pt) {
                    $found = $registry->get_by(['object_type' => 'post', 'post_types' => $pt]);
                    if (is_array($found)) {
                        $all = array_merge($all, $found);
                    }
                }
                return $all;
            }
        }
        return [];
    }

    private static function normalize_row($row) {
        $out = [];
        foreach ((array) $row as $k => $v) {
            if ($k === 'ID') {
                continue;
            }
            if (is_scalar($v) && $v !== '') {
                $out[sanitize_key($k)] = $v;
            }
        }
        return $out;
    }
}
