<?php

namespace PloverCRM\Common;

/**
 * Stores structured operational activity events for feature-level log views.
 *
 * Raw file logs remain available for deep diagnostics, while this manager keeps
 * a lightweight queryable stream that the app can load lazily in context.
 */
class ActivityEventManager
{
    public const TABLE_NAME = 'plover_crm_activity_events';
    public const SCHEMA_VERSION_OPTION = 'plover_crm_activity_events_schema_version';
    public const SCHEMA_VERSION = '1';

    private static $tableReady = false;

    public static function init(): void
    {
        self::maybeCreateTable();
    }

    public static function getTableName(): string
    {
        global $wpdb;

        return $wpdb->prefix . self::TABLE_NAME;
    }

    public static function maybeCreateTable(): void
    {
        global $wpdb;

        $table = self::getTableName();
        $tableExists = self::tableExists($table);

        if (
            $tableExists &&
            get_option(self::SCHEMA_VERSION_OPTION) === self::SCHEMA_VERSION
        ) {
            self::$tableReady = true;
            return;
        }

        $charsetCollate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            module_name varchar(64) NOT NULL,
            feature_name varchar(64) NOT NULL,
            channel_name varchar(120) NOT NULL,
            log_level varchar(20) NOT NULL,
            status varchar(30) DEFAULT NULL,
            summary text NOT NULL,
            details_json longtext DEFAULT NULL,
            task_id varchar(120) DEFAULT NULL,
            actor_wp_user_id bigint(20) unsigned DEFAULT NULL,
            actor_crm_user_id bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY (id),
            KEY module_feature (module_name, feature_name, id),
            KEY channel_name (channel_name),
            KEY log_level (log_level),
            KEY task_id (task_id),
            KEY actor_wp_user_id (actor_wp_user_id),
            KEY created_at (created_at)
        ) $charsetCollate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);

        self::$tableReady = self::tableExists($table);

        if (self::$tableReady) {
            update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
        }
    }

    public static function record(array $event): void
    {
        self::maybeCreateTable();

        if (!self::$tableReady) {
            return;
        }

        global $wpdb;

        $actorWpUserId = isset($event['actor_wp_user_id']) ? (int) $event['actor_wp_user_id'] : get_current_user_id();
        $actorCrmUserId = isset($event['actor_crm_user_id']) ? (int) $event['actor_crm_user_id'] : null;

        if ($actorCrmUserId === null && $actorWpUserId > 0) {
            $crmUser = CrmUserManager::getByWordPressUserId($actorWpUserId);
            $actorCrmUserId = $crmUser ? (int) ($crmUser['id'] ?? 0) : 0;
        }

        $details = self::normalizeValue($event['details'] ?? null);
        $detailsJson = null;

        if ($details !== null && $details !== []) {
            $encoded = wp_json_encode($details, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
            $detailsJson = $encoded === false ? null : $encoded;
        }

        $wpdb->insert(
            self::getTableName(),
            [
                'module_name' => sanitize_key((string) ($event['module'] ?? 'system')),
                'feature_name' => sanitize_key((string) ($event['feature'] ?? 'general')),
                'channel_name' => sanitize_text_field((string) ($event['channel'] ?? 'system')),
                'log_level' => self::normalizeLevel((string) ($event['level'] ?? 'info')),
                'status' => self::normalizeStatus($event['status'] ?? null),
                'summary' => self::normalizeSummary((string) ($event['summary'] ?? '')),
                'details_json' => $detailsJson,
                'task_id' => self::normalizeOptionalText($event['task_id'] ?? null, 120),
                'actor_wp_user_id' => $actorWpUserId > 0 ? $actorWpUserId : null,
                'actor_crm_user_id' => $actorCrmUserId > 0 ? $actorCrmUserId : null,
                'created_at' => current_time('mysql', true),
            ],
            ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s']
        );
    }

    public static function query(array $filters = []): array
    {
        self::maybeCreateTable();

        if (!self::$tableReady) {
            return [
                'items' => [],
                'meta' => [
                    'limit' => 0,
                    'filters' => self::normalizeFilters($filters),
                ],
            ];
        }

        global $wpdb;

        $normalizedFilters = self::normalizeFilters($filters);
        $where = ['1=1'];
        $params = [];

        if (!empty($normalizedFilters['modules'])) {
            $modulePlaceholders = implode(', ', array_fill(0, count($normalizedFilters['modules']), '%s'));
            $where[] = "module_name IN ($modulePlaceholders)";
            $params = array_merge($params, $normalizedFilters['modules']);
        }

        if (!empty($normalizedFilters['features'])) {
            $featurePlaceholders = implode(', ', array_fill(0, count($normalizedFilters['features']), '%s'));
            $where[] = "feature_name IN ($featurePlaceholders)";
            $params = array_merge($params, $normalizedFilters['features']);
        }

        if (!empty($normalizedFilters['levels'])) {
            $levelPlaceholders = implode(', ', array_fill(0, count($normalizedFilters['levels']), '%s'));
            $where[] = "log_level IN ($levelPlaceholders)";
            $params = array_merge($params, $normalizedFilters['levels']);
        }

        if (!empty($normalizedFilters['task_id'])) {
            $where[] = 'task_id = %s';
            $params[] = $normalizedFilters['task_id'];
        }

        if (!empty($normalizedFilters['before_id'])) {
            $where[] = 'id < %d';
            $params[] = (int) $normalizedFilters['before_id'];
        }

        $sql = 'SELECT * FROM ' . self::getTableName() .
            ' WHERE ' . implode(' AND ', $where) .
            ' ORDER BY id DESC LIMIT %d';

        $params[] = $normalizedFilters['limit'];

        $rows = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);

        return [
            'items' => array_map([self::class, 'formatRow'], is_array($rows) ? $rows : []),
            'meta' => [
                'limit' => $normalizedFilters['limit'],
                'filters' => $normalizedFilters,
            ],
        ];
    }

    private static function formatRow(array $row): array
    {
        $actor = null;
        $crmUserId = !empty($row['actor_crm_user_id']) ? (int) $row['actor_crm_user_id'] : null;
        $wpUserId = !empty($row['actor_wp_user_id']) ? (int) $row['actor_wp_user_id'] : null;

        if ($crmUserId) {
            $crmUser = CrmUserManager::getByPublicId($crmUserId);

            if ($crmUser) {
                $actor = [
                    'crm_user_id' => (int) $crmUser['id'],
                    'wp_user_id' => (int) ($crmUser['wp_user_id'] ?? 0),
                    'display_name' => $crmUser['display_name'] ?? '',
                    'username' => $crmUser['username'] ?? '',
                ];
            }
        }

        if ($actor === null && $wpUserId) {
            $wpUser = get_userdata($wpUserId);

            if ($wpUser) {
                $actor = [
                    'crm_user_id' => null,
                    'wp_user_id' => $wpUserId,
                    'display_name' => $wpUser->display_name ?: $wpUser->user_login,
                    'username' => $wpUser->user_login,
                ];
            }
        }

        $details = null;
        if (!empty($row['details_json'])) {
            $decoded = json_decode((string) $row['details_json'], true);
            if (is_array($decoded)) {
                $details = $decoded;
            }
        }

        return [
            'id' => (int) ($row['id'] ?? 0),
            'module' => (string) ($row['module_name'] ?? ''),
            'feature' => (string) ($row['feature_name'] ?? ''),
            'channel' => (string) ($row['channel_name'] ?? ''),
            'level' => self::normalizeLevel((string) ($row['log_level'] ?? 'info')),
            'status' => $row['status'] !== null && $row['status'] !== '' ? (string) $row['status'] : null,
            'summary' => (string) ($row['summary'] ?? ''),
            'details' => $details,
            'task_id' => $row['task_id'] !== null && $row['task_id'] !== '' ? (string) $row['task_id'] : null,
            'actor' => $actor,
            'created_at' => (string) ($row['created_at'] ?? ''),
        ];
    }

    private static function normalizeFilters(array $filters): array
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 50;
        $limit = max(1, min($limit, 200));

        return [
            'modules' => self::normalizeFilterList($filters['modules'] ?? $filters['module'] ?? []),
            'features' => self::normalizeFilterList($filters['features'] ?? $filters['feature'] ?? []),
            'levels' => array_values(array_filter(
                array_map([self::class, 'normalizeLevel'], self::normalizeFilterList($filters['levels'] ?? $filters['level'] ?? []))
            )),
            'task_id' => self::normalizeOptionalText($filters['task_id'] ?? null, 120),
            'before_id' => !empty($filters['before_id']) ? (int) $filters['before_id'] : null,
            'limit' => $limit,
        ];
    }

    private static function normalizeFilterList($value): array
    {
        if (is_string($value)) {
            $value = array_filter(array_map('trim', explode(',', $value)));
        }

        if (!is_array($value)) {
            return [];
        }

        return array_values(array_filter(array_map(static function ($item) {
            return sanitize_key((string) $item);
        }, $value)));
    }

    private static function normalizeValue($value)
    {
        if ($value === null) {
            return null;
        }

        if (is_scalar($value)) {
            return $value;
        }

        if (is_object($value)) {
            if ($value instanceof \JsonSerializable) {
                return self::normalizeValue($value->jsonSerialize());
            }

            if ($value instanceof \WP_Error) {
                return [
                    'code' => $value->get_error_code(),
                    'message' => $value->get_error_message(),
                    'data' => $value->get_error_data(),
                ];
            }

            return self::normalizeValue(get_object_vars($value));
        }

        if (is_array($value)) {
            $normalized = [];

            foreach ($value as $key => $item) {
                $normalizedKey = is_int($key) ? $key : sanitize_key((string) $key);
                $normalized[$normalizedKey] = self::normalizeValue($item);
            }

            return $normalized;
        }

        return (string) $value;
    }

    private static function normalizeLevel(string $level): string
    {
        $normalized = strtolower(trim($level));

        return in_array($normalized, ['debug', 'info', 'warning', 'error'], true)
            ? $normalized
            : 'info';
    }

    private static function normalizeStatus($status): ?string
    {
        if (!is_scalar($status) || $status === '') {
            return null;
        }

        return sanitize_key((string) $status);
    }

    private static function normalizeSummary(string $summary): string
    {
        $summary = trim($summary);

        return $summary !== '' ? $summary : 'Activity recorded';
    }

    private static function normalizeOptionalText($value, int $maxLength): ?string
    {
        if (!is_scalar($value)) {
            return null;
        }

        $sanitized = sanitize_text_field((string) $value);

        if ($sanitized === '') {
            return null;
        }

        return substr($sanitized, 0, $maxLength);
    }

    private static function tableExists(string $table): bool
    {
        global $wpdb;

        return $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) === $table;
    }
}
