<?php

namespace FleekDash\Controllers\Schema\FieldDetectors;

if (!\function_exists('fleekdash_log_debug')) {
    require_once FLEEKDASH_PLUGIN_DIR . 'includes/Core/debug-helpers.php';
}

/**
 * Database Field Detector
 *
 * Fallback detector that scans the database for existing meta fields.
 * Lowest priority detector used when other sources don't detect fields.
 *
 * @since 2.0.0 Extracted from monolithic SchemaController
 */
class DatabaseFieldDetector
{
    private const DEFAULT_STRING_DESCRIPTOR = [
        'type' => 'text',
        'component' => 'input',
        'detected_from' => 'string',
    ];

    private $priority = 1;

    /**
     * Check if database scanning is available
     *
     * @return bool
     */
    public function isAvailable()
    {
        global $wpdb;
        return !empty($wpdb);
    }

    /**
     * Get detector priority
     *
     * @return int
     */
    public function getPriority()
    {
        return $this->priority;
    }

    /**
     * Get existing meta fields from database for a post type
     *
     * @param string $post_type
     * @return array
     */
    public function getFields($post_type)
    {
        $fields = [];

        if (!$this->isAvailable()) {
            fleekdash_log_debug("FleekDash [DatabaseFieldDetector]: Database not available");
            return $fields;
        }

        try {
            fleekdash_log_debug("FleekDash [DatabaseFieldDetector]: Scanning database for meta fields of post type: {$post_type}");

            global $wpdb;

            // Query to get unique meta keys for this post type
            $query = $wpdb->prepare("
                SELECT DISTINCT pm.meta_key,
                       COUNT(*) as usage_count,
                       pm.meta_value as sample_value
                FROM {$wpdb->postmeta} pm
                JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                WHERE p.post_type = %s
                  AND pm.meta_key NOT LIKE '\_%'
                  AND pm.meta_key != ''
                GROUP BY pm.meta_key
                HAVING COUNT(*) >= 1
                ORDER BY usage_count DESC, pm.meta_key ASC
                LIMIT 50
            ", $post_type);

            $results = $wpdb->get_results($query);

            if (empty($results)) {
                fleekdash_log_debug("FleekDash [DatabaseFieldDetector]: No meta fields found in database for {$post_type}");
                return $fields;
            }

            foreach ($results as $row) {
                $meta_key = $row->meta_key;
                $sample_value = $row->sample_value;
                $usage_count = $row->usage_count;

                // Try to determine field type from sample value
                $detected_type = $this->detectTypeFromValue($sample_value);

                $field_config = [
                    'name' => $meta_key,
                    'label' => $this->formatLabel($meta_key),
                    'type' => $detected_type['type'],
                    'component' => $detected_type['component'],
                    'required' => false,
                    'validation' => [],
                    'description' => "Database field (used {$usage_count} times)",
                    'default' => null,
                    'meta_key' => $meta_key,
                    'source' => 'Database Scan',
                    'source_priority' => $this->priority,
                    'database_info' => [
                        'usage_count' => $usage_count,
                        'sample_value' => $sample_value,
                        'detected_type' => $detected_type['detected_from']
                    ]
                ];

                $fields[$meta_key] = $field_config;

                fleekdash_log_debug("FleekDash [DatabaseFieldDetector]: Added database field '{$meta_key}' " .
                    "(type: {$detected_type['type']}, usage: {$usage_count})");
            }

            fleekdash_log_debug("FleekDash [DatabaseFieldDetector]: Total database fields processed: " . count($fields));

        } catch (\Exception $e) {
            fleekdash_log_error("FleekDash [DatabaseFieldDetector]: Error scanning database: " . $e->getMessage());
        }

        return $fields;
    }

    /**
     * Detect field type from a sample value
     *
     * @param mixed $value
     * @return array{type: string, component: string, detected_from: string}
     */
    private function detectTypeFromValue(mixed $value): array
    {
        $steps = [
            'descriptorForEmptyOrNull',
            'descriptorForSerialized',
            'descriptorForJson',
            'descriptorForNumeric',
            'descriptorForBooleanLike',
            'descriptorForUrl',
            'descriptorForEmail',
            'descriptorForDate',
            'descriptorForLongText',
        ];

        $result = null;
        foreach ($steps as $method) {
            $candidate = $this->{$method}($value);
            if ($candidate !== null) {
                $result = $candidate;
                break;
            }
        }

        if ($result === null) {
            $result = self::DEFAULT_STRING_DESCRIPTOR;
        }

        return $result;
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForEmptyOrNull(mixed $value): ?array
    {
        if (!(empty($value) || $value === null)) {
            return null;
        }

        return [
            'type' => 'text',
            'component' => 'input',
            'detected_from' => 'empty_value',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForSerialized(mixed $value): ?array
    {
        if (!is_serialized($value)) {
            return null;
        }

        $unserialized = fleekdash_safe_unserialize($value);

        if (is_array($unserialized)) {
            return [
                'type' => 'multiselect',
                'component' => 'multiselect',
                'detected_from' => 'serialized_array',
            ];
        }

        return [
            'type' => 'textarea',
            'component' => 'textarea',
            'detected_from' => 'serialized_data',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForJson(mixed $value): ?array
    {
        if (!$this->isJson($value)) {
            return null;
        }

        $decoded = json_decode($value, true);

        if (is_array($decoded)) {
            return [
                'type' => 'repeater',
                'component' => 'repeater',
                'detected_from' => 'json_array',
            ];
        }

        return [
            'type' => 'textarea',
            'component' => 'textarea',
            'detected_from' => 'json_object',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForNumeric($value): ?array
    {
        if (!is_numeric($value)) {
            return null;
        }

        $as_string = (string) $value;
        $detected_from = strpos($as_string, '.') !== false ? 'decimal_number' : 'integer';

        return [
            'type' => 'number',
            'component' => 'input',
            'detected_from' => $detected_from,
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForBooleanLike(mixed $value): ?array
    {
        $normalized = strtolower((string) $value);
        $boolean_like = ['true', 'false', '1', '0', 'yes', 'no'];
        if (!in_array($normalized, $boolean_like, true)) {
            return null;
        }

        return [
            'type' => 'boolean',
            'component' => 'checkbox',
            'detected_from' => 'boolean_string',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForUrl($value): ?array
    {
        if (!is_string($value) || !filter_var($value, FILTER_VALIDATE_URL)) {
            return null;
        }

        return [
            'type' => 'url',
            'component' => 'input',
            'detected_from' => 'url',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForEmail(mixed $value): ?array
    {
        if (!is_string($value) || !filter_var($value, FILTER_VALIDATE_EMAIL)) {
            return null;
        }

        return [
            'type' => 'email',
            'component' => 'input',
            'detected_from' => 'email',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForDate($value): ?array
    {
        if (!$this->isDate($value)) {
            return null;
        }

        return [
            'type' => 'date',
            'component' => 'input',
            'detected_from' => 'date_string',
        ];
    }

    /**
     * @return array{type: string, component: string, detected_from: string}|null
     */
    private function descriptorForLongText(mixed $value): ?array
    {
        if (!is_string($value)) {
            return null;
        }

        $is_long = strlen($value) > 100 || strpos($value, "\n") !== false;
        if (!$is_long) {
            return null;
        }

        return [
            'type' => 'textarea',
            'component' => 'textarea',
            'detected_from' => 'long_text',
        ];
    }

    /**
     * Check if string is valid JSON
     *
     * @param string $string
     * @return bool
     */
    private function isJson($string)
    {
        if (!is_string($string)) {
            return false;
        }

        json_decode($string);

        return json_last_error() === JSON_ERROR_NONE;
    }

    /**
     * Check if string is a valid date
     *
     * @param string $string
     * @return bool
     */
    private function isDate($string)
    {
        if (!is_string($string)) {
            return false;
        }

        // Try to parse as date
        $timestamp = strtotime($string);

        // Check if it's a valid timestamp and not too far in the past/future
        if ($timestamp === false) {
            return false;
        }

        $year = \date('Y', $timestamp);

        return $year >= 1900 && $year <= 2100;
    }

    /**
     * Format field label from meta key
     *
     * @param string $key
     * @return string
     */
    private function formatLabel(string $key): string
    {
        return \ucwords(\str_replace(['_', '-'], ' ', $key));
    }
}
