<?php

namespace PloverCRM\Core\CRM\Providers;

use PloverCRM\Common\CrmUserManager;
use PloverCRM\Core\CRM\CRMProviderInterface;
use PloverCRM\Modules\FluentCRM\Services\Logger;
use PloverCRM\Core\Helpers\DateTimeHelper;

/**
 * FluentCRM Provider Implementation
 * 
 * Implements CRM operations for FluentCRM
 */
class FluentCRMProvider implements CRMProviderInterface
{
    /**
     * @var Logger
     */
    private $logger;

    /**
     * Get logger instance
     */
    private function getLogger(): Logger
    {
        if (!$this->logger) {
            $this->logger = new Logger();
        }
        return $this->logger;
    }

    /**
     * {@inheritdoc}
     */
    public function isAvailable(): bool
    {
        // Check if FluentCRM plugin is installed and active
        if (!class_exists('FluentCrm\App\Models\Subscriber') || !function_exists('FluentCrmApi')) {
            return false;
        }

        // Check if FluentCRM module is enabled
        $disabledModules = get_option('plover_crm_disabled_modules', []);
        return !in_array('fluent_crm', $disabledModules);
    }

    /**
     * {@inheritdoc}
     */
    public function getName(): string
    {
        return 'FluentCRM';
    }

    /**
     * {@inheritdoc}
     */
    public function getContact(int $id): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $subscriber = \FluentCrmApi('contacts')->getContact($id);

            if (!$subscriber) {
                return null;
            }

            // Eager load tags and lists
            $subscriber->load('tags', 'lists');

            return $this->normalizeContact($subscriber);
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get contact', [
                'contact_id' => $id,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getContactByEmail(string $email): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $subscriber = \FluentCrmApi('contacts')->getContact($email);

            if (!$subscriber) {
                return null;
            }

            // Eager load tags and lists
            $subscriber->load('tags', 'lists');

            return $this->normalizeContact($subscriber);
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get contact by email', [
                'email' => $email,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function createOrUpdateContact(array $data): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $is_update = isset($data['id']) && !empty($data['id']);
            $existing = null;

            // If updating, get existing contact
            if ($is_update) {
                $existing = \FluentCrm\App\Models\Subscriber::find($data['id']);
                if (!$existing) {
                    $this->getLogger()->logError('Contact not found for update', [
                        'contact_id' => $data['id']
                    ]);
                    return null;
                }
            }

            // Handle full_name splitting if provided
            if (!empty($data['full_name']) && is_string($data['full_name'])) {
                $full_name = trim($data['full_name']);
                $name_parts = explode(' ', $full_name, 2);

                $data['first_name'] = $name_parts[0];
                $data['last_name'] = isset($name_parts[1]) ? $name_parts[1] : '';

                // Update names directly if updating
                if ($existing) {
                    $existing->update([
                        'first_name' => $data['first_name'],
                        'last_name' => $data['last_name']
                    ]);
                }

                unset($data['full_name']);
            }

            // Handle email update validation
            if ($is_update && !empty($data['email']) && is_email($data['email']) && $data['email'] !== $existing->email) {
                $email_exists = \FluentCrmApi('contacts')->getContact($data['email']);
                if ($email_exists && $email_exists->id != $data['id']) {
                    $this->getLogger()->logError('Email already in use by another contact', [
                        'email' => $data['email'],
                        'existing_contact_id' => $email_exists->id,
                        'attempted_contact_id' => $data['id']
                    ]);
                    return null;
                }
                // Update email directly
                $existing->update(['email' => $data['email']]);
            } elseif ($is_update) {
                // Use existing email if not changing
                $data['email'] = $existing->email;
            }

            // Convert custom_fields to custom_values for FluentCRM
            if (isset($data['custom_fields']) && is_array($data['custom_fields'])) {
                // Get custom field definitions to know field types
                $customFieldDefs = fluentcrm_get_option('contact_custom_fields', []);
                $fieldTypes = [];
                foreach ($customFieldDefs as $field) {
                    $fieldTypes[$field['slug']] = $field['type'] ?? 'text';
                }

                // Format date/datetime values to FluentCRM expected format
                foreach ($data['custom_fields'] as $slug => $value) {
                    if (empty($value)) {
                        continue;
                    }

                    $fieldType = $fieldTypes[$slug] ?? 'text';

                    // Convert date fields to Y-m-d format
                    if ($fieldType === 'date') {
                        $data['custom_fields'][$slug] = DateTimeHelper::normalizeDate($value, 'Y-m-d');
                    }
                    // Convert datetime fields to Y-m-d H:i:s format
                    elseif ($fieldType === 'date_time') {
                        $data['custom_fields'][$slug] = DateTimeHelper::normalizeDateTime($value, 'Y-m-d H:i:s');
                    }
                }

                // Format custom field values (handles array types like checkbox, multi-select)
                $customFieldModel = new \FluentCrm\App\Models\CustomContactField();
                $data['custom_values'] = $customFieldModel->formatCustomFieldValues($data['custom_fields']);
                unset($data['custom_fields']);
            }

            // Validate/Set status
            $valid_statuses = fluentcrm_subscriber_statuses();
            if (!empty($data['status'])) {
                if (!in_array($data['status'], $valid_statuses)) {
                    $data['status'] = 'subscribed';
                }
            } elseif (!$is_update) {
                // Default to subscribed for new contacts
                $data['status'] = 'subscribed';
            }

            $sanitized = $this->sanitizeContactData($data);
            $subscriber = \FluentCrmApi('contacts')->createOrUpdate($sanitized, true, false);

            if (!$subscriber) {
                return null;
            }

            // Deduplicate custom field meta rows to work around FluentCRM's
            // syncCustomFieldValues bug: it uses meta()->where('key')->first() to find
            // existing rows (returns lowest ID), but custom_fields() reads all rows and
            // the last one in the loop wins. If duplicates exist, the update goes to the
            // first row but the read returns the second (stale) row.
            if ($is_update && !empty($sanitized['custom_values'])) {
                $this->deduplicateCustomFieldMeta($subscriber->id, array_keys($sanitized['custom_values']));
            }

            // Handle double opt-in for new subscribers with pending status
            if (!$is_update && $subscriber->status === 'pending') {
                $subscriber->sendDoubleOptinEmail();
            }

            // Handle lists replacement (sync) if provided
            if (isset($data['lists']) && is_array($data['lists'])) {
                $list_ids = array_map('intval', $data['lists']);
                $subscriber->lists()->sync($list_ids);
            }

            // Handle tags replacement (sync) if provided
            if (isset($data['tags']) && is_array($data['tags'])) {
                $tag_ids = array_map('intval', $data['tags']);
                $subscriber->tags()->sync($tag_ids);
            }

            // Reload relationships
            $subscriber->load('tags', 'lists');

            $result = $this->normalizeContact($subscriber);
            // wasRecentlyCreated is set by FluentCRM when a new contact is created
            $result['is_new'] = $subscriber->wasRecentlyCreated ?? false;

            if (
                !$result['is_new'] &&
                !\PloverCRM\Common\Utils::isBulkImporting() &&
                in_array($data['source'] ?? '', ['Form', 'Webhook'], true)
            ) {
                do_action('plover_crm/contact_touched', $subscriber->id, $result, $data);
            }

            return $result;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to create/update contact', [
                'email' => $data['email'] ?? 'unknown',
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getContactCustomFields(array $contact): array
    {
        // Custom fields are already included in the normalized contact data
        return $contact['custom_fields'] ?? [];
    }

    /**
     * {@inheritdoc}
     */
    public function getAllCustomFields(): array
    {
        if (!$this->isAvailable()) {
            return [];
        }

        try {
            return fluentcrm_get_option('contact_custom_fields', []);
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get all custom fields', [
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function createOrUpdateCustomFields(array $fields): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $existingFields = fluentcrm_get_option('contact_custom_fields', []);

            foreach ($fields as $newField) {
                // Normalize field for FluentCRM
                $normalizedField = $this->normalizeCustomField($newField);

                $exists = false;

                foreach ($existingFields as &$existingField) {
                    if ($existingField['slug'] === $normalizedField['slug']) {
                        $existingField = array_merge($existingField, $normalizedField);
                        $exists = true;
                        break;
                    }
                }

                if (!$exists) {
                    $existingFields[] = $normalizedField;
                }
            }

            fluentcrm_update_option('contact_custom_fields', $existingFields);
            return true;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to create/update custom fields', [
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * Normalize custom field structure for FluentCRM
     * 
     * @param array $field Common field structure
     * @return array FluentCRM-specific field structure
     */
    private function normalizeCustomField(array $field): array
    {
        // Map common types to FluentCRM types
        $typeMap = [
            'datetime' => 'date_time',
            // Add more mappings as needed
        ];

        $type = $field['type'] ?? 'text';
        $mappedType = $typeMap[$type] ?? $type;

        // FluentCRM requires field_key (same as type)
        $normalized = [
            'field_key' => $mappedType,
            'type' => $mappedType,
            'label' => $field['label'] ?? '',
            'slug' => $field['slug'] ?? '',
        ];

        // Add optional fields if present
        if (isset($field['group'])) {
            $normalized['group'] = $field['group'];
        }

        if (isset($field['options'])) {
            $normalized['options'] = $field['options'];
        }

        if (array_key_exists('readonly', $field)) {
            $normalized['readonly'] = (bool) $field['readonly'];
        }

        if (array_key_exists('system', $field)) {
            $normalized['system'] = (bool) $field['system'];
        }

        if (isset($field['owner_capability'])) {
            $normalized['owner_capability'] = (string) $field['owner_capability'];
        }

        if (isset($field['visibility'])) {
            $normalized['visibility'] = (string) $field['visibility'];
        }

        return $normalized;
    }

    /**
     * {@inheritdoc}
     */
    public function getCustomFieldOptions(string $slug): array
    {
        if (!$this->isAvailable()) {
            return [];
        }

        try {
            $allFields = $this->getAllCustomFields();

            foreach ($allFields as $field) {
                if (isset($field['slug']) && $field['slug'] === $slug) {
                    return $field['options'] ?? [];
                }
            }

            return [];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get field options', [
                'field_slug' => $field_slug,
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getAllLists(): array
    {
        if (!$this->isAvailable()) {
            return [];
        }

        try {
            $lists = \FluentCrmApi('lists')->all();
            return $lists ? $lists->toArray() : [];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get all lists', [
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function createList(array $data): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $listData = [
                'title' => sanitize_text_field($data['title']),
                'slug' => !empty($data['slug']) ? sanitize_title($data['slug']) : sanitize_title($data['title']),
                'description' => sanitize_textarea_field($data['description'] ?? ''),
            ];

            $list = \FluentCrm\App\Models\Lists::create($listData);

            return $list ? $list->toArray() : null;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to create list', [
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function deleteList(int $id): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $list = \FluentCrm\App\Models\Lists::find($id);
            if ($list) {
                $list->delete();
                return true;
            }
            return false;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to delete list', [
                'list_id' => $id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function updateList(int $id, array $data): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $list = \FluentCrm\App\Models\Lists::find($id);
            if (!$list) {
                $this->getLogger()->logError('List not found for update', ['list_id' => $id]);
                return null;
            }

            // Validate required fields
            if (isset($data['title']) && empty($data['title'])) {
                return null;
            }

            $updateData = [];

            if (isset($data['title'])) {
                $updateData['title'] = sanitize_text_field($data['title']);
            }

            if (isset($data['slug'])) {
                $updateData['slug'] = sanitize_title($data['slug']);
            } elseif (isset($data['title'])) {
                // Generate slug from title if not provided
                $updateData['slug'] = \FluentCrm\App\Services\Helper::slugify($data['title']);
            }

            if (isset($data['description'])) {
                $updateData['description'] = sanitize_textarea_field($data['description']);
            }

            if (empty($updateData)) {
                return $list->toArray();
            }

            // Check for duplicate slug
            if (isset($updateData['slug'])) {
                $existing = \FluentCrm\App\Models\Lists::where('slug', $updateData['slug'])
                    ->where('id', '!=', $id)
                    ->first();

                if ($existing) {
                    $this->getLogger()->logError('Duplicate slug for list', [
                        'list_id' => $id,
                        'slug' => $updateData['slug']
                    ]);
                    return null;
                }
            }

            \FluentCrm\App\Models\Lists::where('id', $id)->update($updateData);

            // Trigger FluentCRM hooks
            do_action('fluentcrm_list_updated', $id);
            do_action('fluent_crm/list_updated', $id);

            return \FluentCrm\App\Models\Lists::find($id)->toArray();
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to update list', [
                'list_id' => $id,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getContactLists(array $contact): array
    {
        // Lists are already included in the normalized contact data
        return $contact['lists'] ?? [];
    }

    /**
     * {@inheritdoc}
     */
    public function getContactTags(array $contact): array
    {
        // Tags are already included in the normalized contact data
        return $contact['tags'] ?? [];
    }

    /**
     * {@inheritdoc}
     */
    public function getOption(string $key, $default = null)
    {
        if (!$this->isAvailable()) {
            return $default;
        }

        try {
            return fluentcrm_get_option($key, $default);
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get option', [
                'option_key' => $key,
                'error' => $e->getMessage()
            ]);
            return $default;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function updateOption(string $key, $value): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            fluentcrm_update_option($key, $value);
            return true;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to update option', [
                'option_key' => $key,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getStandardFields(): array
    {
        if (!$this->isAvailable()) {
            return [];
        }

        try {
            return \FluentCrm\App\Models\Subscriber::mappables();
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get standard fields', [
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function sanitizeContactData(array $data): array
    {
        if (!$this->isAvailable()) {
            return $data;
        }

        try {
            return \FluentCrm\App\Services\Sanitize::contact($data);
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to sanitize contact data', [
                'error' => $e->getMessage()
            ]);
            return $data;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function registerHooks(): void
    {
        if (!$this->isAvailable()) {
            return;
        }

        // Map FluentCRM hooks to generic CRM hooks
        add_action('fluent_crm/contact_created', function ($subscriber) {
            do_action('plover_crm/contact_created', $subscriber->id, $this->normalizeContact($subscriber));
        }, 10, 1);

        add_action('fluent_crm/contact_custom_data_updated', function ($newValues, $subscriber, $updateValues) {
            do_action('plover_crm/contact_updated', $subscriber->id, $this->normalizeContact($subscriber), $newValues, $updateValues);
        }, 10, 3);
    }

    /**
     * {@inheritdoc}
     */
    public function getAllTags(): array
    {
        if (!$this->isAvailable()) {
            return [];
        }

        try {
            $tags = \FluentCrmApi('tags')->all();
            return $tags ? $tags->toArray() : [];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get all tags', [
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function createTag(array $data): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $tagData = [
                'title' => sanitize_text_field($data['title']),
                'slug' => !empty($data['slug']) ? sanitize_title($data['slug']) : sanitize_title($data['title']),
                'description' => sanitize_textarea_field($data['description'] ?? ''),
            ];

            $tag = \FluentCrm\App\Models\Tag::create($tagData);

            return $tag ? $tag->toArray() : null;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to create tag', [
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function deleteTag(int $id): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $tag = \FluentCrm\App\Models\Tag::find($id);
            if ($tag) {
                $tag->delete();
                return true;
            }
            return false;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to delete tag', [
                'tag_id' => $id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function updateTag(int $id, array $data): ?array
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $tag = \FluentCrm\App\Models\Tag::find($id);
            if (!$tag) {
                $this->getLogger()->logError('Tag not found for update', ['tag_id' => $id]);
                return null;
            }

            // Validate required fields
            if (isset($data['title']) && empty($data['title'])) {
                return null;
            }

            $updateData = [];

            if (isset($data['title'])) {
                $updateData['title'] = sanitize_text_field($data['title']);
            }

            if (isset($data['slug'])) {
                $updateData['slug'] = sanitize_title($data['slug']);
            } elseif (isset($data['title'])) {
                // Generate slug from title if not provided
                $updateData['slug'] = \FluentCrm\App\Services\Helper::slugify($data['title']);
            }

            if (isset($data['description'])) {
                $updateData['description'] = sanitize_textarea_field($data['description']);
            }

            if (empty($updateData)) {
                return $tag->toArray();
            }

            // Check for duplicate slug
            if (isset($updateData['slug'])) {
                $existing = \FluentCrm\App\Models\Tag::where('slug', $updateData['slug'])
                    ->where('id', '!=', $id)
                    ->first();

                if ($existing) {
                    $this->getLogger()->logError('Duplicate slug for tag', [
                        'tag_id' => $id,
                        'slug' => $updateData['slug']
                    ]);
                    return null;
                }
            }

            \FluentCrm\App\Models\Tag::where('id', $id)->update($updateData);

            // Trigger FluentCRM hooks
            do_action('fluentcrm_tag_updated', $id);
            do_action('fluent_crm/tag_updated', $id);

            return \FluentCrm\App\Models\Tag::find($id)->toArray();
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to update tag', [
                'tag_id' => $id,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getAllContacts(array $args = []): array
    {
        if (!$this->isAvailable()) {
            return ['contacts' => [], 'total' => 0];
        }

        try {
            $page = $args['page'] ?? 1;
            $per_page = $args['per_page'] ?? 20;
            $search = $args['search'] ?? '';

            $query = \FluentCrm\App\Models\Subscriber::query();

            if (!empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('email', 'LIKE', "%{$search}%")
                        ->orWhere('first_name', 'LIKE', "%{$search}%")
                        ->orWhere('last_name', 'LIKE', "%{$search}%");
                });
            }

            $total = $query->count();
            $subscribers = $query->orderBy('id', 'DESC')
                ->skip(($page - 1) * $per_page)
                ->take($per_page)
                ->get();

            $contacts = [];
            foreach ($subscribers as $subscriber) {
                $contacts[] = $this->normalizeContact($subscriber);
            }

            return [
                'contacts' => $contacts,
                'total' => $total,
            ];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get all contacts', [
                'page' => $page,
                'per_page' => $per_page,
                'error' => $e->getMessage()
            ]);
            return ['contacts' => [], 'total' => 0];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function searchContacts(array $args = []): array
    {
        if (!$this->isAvailable()) {
            return ['contacts' => [], 'total' => 0, 'total_pages' => 0];
        }

        try {
            $query = $args['query'] ?? '';
            $page = $args['page'] ?? 1;
            $per_page = $args['per_page'] ?? 20;
            $list_ids = $args['list_ids'] ?? [];
            $tag_ids = $args['tag_ids'] ?? [];
            $fields = $args['fields'] ?? [];
            $custom_fields = $args['custom_fields'] ?? [];
            $search_mode = $args['search_mode'] ?? 'or';
            $statuses = $args['statuses'] ?? [];
            $stages = $args['stages'] ?? [];

            global $wpdb;
            $table_name = $wpdb->prefix . 'fc_subscribers';
            $validColumns = $wpdb->get_col("SHOW COLUMNS FROM {$table_name}");

            $queryBuilder = \FluentCrm\App\Models\Subscriber::with(['tags', 'lists']);

            if (class_exists('\FluentCrm\App\Services\Helper') && \FluentCrm\App\Services\Helper::isCompanyEnabled()) {
                $queryBuilder->with(['companies']);
            }

            // Apply status filter
            if (!empty($statuses)) {
                $queryBuilder->whereIn('status', $statuses);
            }

            // Apply list filter
            if (!empty($list_ids)) {
                $queryBuilder->filterByLists($list_ids);
            }

            // Apply tag filter
            if (!empty($tag_ids)) {
                $queryBuilder->filterByTags($tag_ids);
            }

            // Apply stage filter (custom field exact match)
            // Note: contacts without any stage value are treated as being in the first/default stage
            if (!empty($stages)) {
                // Get the first (default) stage name from custom field options
                $stageFieldDef = null;
                $allCustomFields = $this->getAllCustomFields();
                foreach ($allCustomFields as $cf) {
                    if (isset($cf['slug']) && $cf['slug'] === 'stage-plover-crm') {
                        $stageFieldDef = $cf;
                        break;
                    }
                }
                $defaultStage = '';
                if ($stageFieldDef && !empty($stageFieldDef['options']) && is_array($stageFieldDef['options'])) {
                    $defaultStage = $stageFieldDef['options'][0] ?? '';
                }

                $includesDefault = $defaultStage && in_array($defaultStage, $stages);

                if ($includesDefault) {
                    // Include contacts with matching stage value OR contacts with no stage assigned
                    $queryBuilder->where(function ($q) use ($stages) {
                        $q->whereHas('custom_field_meta', function ($metaq) use ($stages) {
                            $metaq->where('key', 'stage-plover-crm')
                                ->whereIn('value', $stages);
                        });
                        $q->doesntHave('custom_field_meta', 'or', function ($metaq) {
                            $metaq->where('key', 'stage-plover-crm');
                        });
                    });
                } else {
                    $queryBuilder->whereHas('custom_field_meta', function ($metaq) use ($stages) {
                        $metaq->where('key', 'stage-plover-crm')
                            ->whereIn('value', $stages);
                    });
                }
            }

            // Apply text search if query provided
            if (!empty($query)) {
                $like = "%{$query}%";

                if (empty($fields) && empty($custom_fields)) {
                    // Default broad search: all standard + all custom (OR mode via searchBy)
                    $queryBuilder->searchBy($query, true);
                } else {
                    // Targeted multi-field/custom search
                    $is_or_mode = ($search_mode === 'or');
                    $connector = $is_or_mode ? 'orWhere' : 'where';

                    $queryBuilder->where(function ($q) use ($fields, $custom_fields, $query, $like, $validColumns, $connector, $is_or_mode) {
                        $first_condition = true;

                        // Standard fields
                        if (!empty($fields)) {
                            foreach ($fields as $f) {
                                $f = trim($f);
                                if ($f === 'full_name') {
                                    // Special handling: OR between first_name and last_name
                                    $group_connector = $first_condition ? 'where' : $connector;
                                    $q->{$group_connector}(function ($subq) use ($like) {
                                        $subq->where('first_name', 'LIKE', $like)->orWhere('last_name', 'LIKE', $like);
                                    });
                                } elseif (in_array($f, $validColumns)) {
                                    // Direct field
                                    $field_connector = $first_condition ? 'where' : $connector;
                                    $q->{$field_connector}($f, 'LIKE', $like);
                                }
                                $first_condition = false;
                            }
                        }

                        // Custom fields
                        if (!empty($custom_fields)) {
                            $group_connector = $first_condition ? 'where' : $connector;
                            $q->{$group_connector}(function ($subq) use ($custom_fields, $like) {
                                $subq->whereHas('custom_field_meta', function ($metaq) use ($custom_fields, $like) {
                                    $metaq->whereIn('key', $custom_fields)->where('value', 'LIKE', $like);
                                });
                            });
                            $first_condition = false;
                        }
                    });
                }
            }

            $total = $queryBuilder->count();
            $total_pages = ceil($total / $per_page);
            $offset = ($page - 1) * $per_page;

            $subscribers = $queryBuilder->orderBy('id', 'DESC')->skip($offset)->take($per_page)->get();

            $contacts = [];
            foreach ($subscribers as $subscriber) {
                $contacts[] = $this->normalizeContact($subscriber);
            }

            return [
                'contacts' => $contacts,
                'total' => $total,
                'total_pages' => $total_pages,
            ];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to search contacts', [
                'query' => $args['query'] ?? '',
                'error' => $e->getMessage()
            ]);
            return ['contacts' => [], 'total' => 0, 'total_pages' => 0];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function deleteContact(int $id): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $subscriber = \FluentCrmApi('contacts')->getContact($id);
            if ($subscriber) {
                $subscriber->delete();
                return true;
            }
            return false;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to delete contact', [
                'contact_id' => $id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getContactNotes(int $contact_id, array $args = []): array
    {
        if (!$this->isAvailable()) {
            return ['notes' => [], 'total' => 0];
        }

        try {
            global $wpdb;

            $page = $args['page'] ?? 1;
            $per_page = $args['per_page'] ?? 20;
            $offset = ($page - 1) * $per_page;

            // Count total records for pagination
            $total_query = $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}fc_subscriber_notes WHERE subscriber_id = %d",
                $contact_id
            );
            $total = $wpdb->get_var($total_query);

            // Get notes with pagination
            $crmUsersTable = CrmUserManager::getTableName();
            $query = $wpdb->prepare(
                "SELECT 
                    n.id,
                    n.subscriber_id,
                    n.created_by,
                    n.type,
                    n.title,
                    n.description,
                    n.created_at,
                    n.updated_at,
                    crm.id as created_by_crm_user_id,
                    crm.username as created_by_username,
                    crm.display_name as created_by_name
                FROM {$wpdb->prefix}fc_subscriber_notes n
                LEFT JOIN {$crmUsersTable} crm ON n.created_by = crm.wp_user_id
                WHERE n.subscriber_id = %d
                ORDER BY n.id DESC
                LIMIT %d OFFSET %d",
                $contact_id,
                $per_page,
                $offset
            );

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

            // Format the response
            $formatted = array_map(function ($note) {
                return [
                    'id' => intval($note->id),
                    'subscriber_id' => $note->subscriber_id,
                    'created_by' => $note->created_by_name ?: 'Unknown',
                    'created_by_user' => !empty($note->created_by_crm_user_id) ? [
                        'id' => (int) $note->created_by_crm_user_id,
                        'username' => (string) $note->created_by_username,
                        'display_name' => (string) $note->created_by_name,
                    ] : null,
                    'type' => $note->type,
                    'title' => $note->title,
                    'description' => $note->description,
                    'created_at' => $note->created_at,
                    'updated_at' => $note->updated_at
                ];
            }, $notes);

            return [
                'notes' => $formatted,
                'total' => intval($total),
            ];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get contact notes', [
                'contact_id' => $contact_id,
                'page' => $page,
                'per_page' => $per_page,
                'error' => $e->getMessage()
            ]);
            return ['notes' => [], 'total' => 0];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function addContactNote(int $contact_id, array $data)
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            // Get valid activity types from FluentCRM
            $valid_types = array_column(fluentcrm_activity_types(), 'id');

            // Validate type, default to 'note' if invalid or not provided
            $type = 'note';
            if (!empty($data['type']) && in_array($data['type'], $valid_types)) {
                $type = $data['type'];
            }

            $note_data = [
                'subscriber_id' => $contact_id,
                'title' => $data['title'] ?? '',
                'description' => $data['description'] ?? '',
                'type' => $type,
                'created_by' => get_current_user_id(),
            ];

            // Allow custom created_at timestamp
            if (!empty($data['created_at'])) {
                $note_data['created_at'] = $data['created_at'];
            }

            $note = \FluentCrm\App\Models\SubscriberNote::create($note_data);

            return $note->id;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to add contact note', [
                'contact_id' => $contact_id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function updateNote(int $note_id, array $data): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $note = \FluentCrm\App\Models\SubscriberNote::find($note_id);
            if (!$note) {
                return false;
            }

            // Verify note belongs to contact if contact_id is provided
            if (isset($data['contact_id']) && $note->subscriber_id != $data['contact_id']) {
                $this->getLogger()->logError('Note does not belong to specified contact', [
                    'note_id' => $note_id,
                    'expected_contact_id' => $data['contact_id'],
                    'actual_contact_id' => $note->subscriber_id
                ]);
                return false;
            }

            // Update title if provided
            if (isset($data['title'])) {
                $note->title = sanitize_text_field($data['title']);
            }

            // Update description if provided
            if (isset($data['description'])) {
                $note->description = sanitize_textarea_field($data['description']);
            }

            // Update type if provided and valid
            if (isset($data['type'])) {
                $valid_types = array_column(fluentcrm_activity_types(), 'id');
                if (in_array($data['type'], $valid_types)) {
                    $note->type = sanitize_text_field($data['type']);
                }
            }

            // Update created_at if provided
            if (isset($data['created_at'])) {
                $note->created_at = $data['created_at'];
            }

            $note->save();
            return true;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to update note', [
                'note_id' => $note_id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function deleteNote(int $note_id, array $data = []): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            $note = \FluentCrm\App\Models\SubscriberNote::find($note_id);
            if (!$note) {
                return false;
            }

            // Verify note belongs to contact if contact_id is provided
            if (isset($data['contact_id']) && $note->subscriber_id != $data['contact_id']) {
                $this->getLogger()->logError('Note does not belong to specified contact', [
                    'note_id' => $note_id,
                    'expected_contact_id' => $data['contact_id'],
                    'actual_contact_id' => $note->subscriber_id
                ]);
                return false;
            }

            $note->delete();
            return true;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to delete note', [
                'note_id' => $note_id,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * Normalize FluentCRM subscriber object to array
     * 
     * @param object $subscriber FluentCRM subscriber object
     * @return array Normalized contact data
     */
    private function normalizeContact($subscriber): array
    {
        if (!$subscriber) {
            return [];
        }

        // Convert to array
        $data = $subscriber->toArray();

        // Add avatar
        $data['plover_crm_avatar'] = $this->generateAvatar($subscriber);

        // Add formatted custom fields
        $data['custom_fields'] = $this->formatCustomFields($subscriber);

        // Add last note
        $data['last_note'] = $this->getFormattedLastNote($subscriber);

        // Convert country code to country name
        if (!empty($data['country']) && strlen($data['country']) === 2) {
            $data['country'] = $this->getCountryName($data['country']);
        }

        return $data;
    }

    /**
     * Generate avatar URL
     */
    private function generateAvatar($subscriber): string
    {
        $first = $subscriber->first_name ?? '';
        $last = $subscriber->last_name ?? '';

        $initials = ($first || $last)
            ? strtoupper(mb_substr($first, 0, 1) . mb_substr($last, 0, 1))
            : 'NA';

        return sprintf(
            'https://www.gravatar.com/avatar/%s?d=%s',
            $subscriber->hash,
            urlencode("https://ui-avatars.com/api/{$initials}/128/random/fff")
        );
    }

    /**
     * Format custom fields
     */
    private function formatCustomFields($subscriber): array
    {
        $custom_fields = $subscriber->custom_fields();
        $definitions = fluentcrm_get_option('contact_custom_fields', []);
        $defs_by_slug = array_column($definitions, null, 'slug');

        $formatted_fields = [];
        foreach ($custom_fields as $slug => $value) {
            // Skip if no definition found
            if (!isset($defs_by_slug[$slug])) {
                continue;
            }

            // Skip if label is missing
            if (!isset($defs_by_slug[$slug]['label']) || empty($defs_by_slug[$slug]['label'])) {
                continue;
            }

            // Only include if value is not empty
            if ($value === null || $value === '') {
                continue;
            }

            $formatted_fields[] = [
                'label' => $defs_by_slug[$slug]['label'],
                'key' => $slug,
                'value' => $value,
            ];
        }

        return $formatted_fields;
    }

    /**
     * Get formatted last note
     */
    private function getFormattedLastNote($subscriber)
    {
        $last_note = $subscriber->notes()
            ->orderBy('id', 'DESC')
            ->select(['title', 'description', 'created_at', 'created_by'])
            ->first();

        if ($last_note && $last_note->created_by) {
            $crmUser = CrmUserManager::getPublicProfileByWordPressUserId((int) $last_note->created_by);
            $last_note->created_by = [
                'id' => (int) ($crmUser['id'] ?? 0),
                'username' => $crmUser['username'] ?? 'Unknown'
            ];
        }

        return $last_note;
    }

    /**
     * Get country name from code
     */
    private function getCountryName(string $countryCode): string
    {
        static $countries = null;

        $countryCode = strtoupper(trim($countryCode));

        if (strlen($countryCode) !== 2) {
            return $countryCode;
        }

        // Load country list once
        if ($countries === null) {
            $countryNames = new \FluentCrm\App\Hooks\Handlers\CountryNames();
            $allCountries = $countryNames->get();

            $countries = [];
            foreach ($allCountries as $country) {
                if (isset($country['code']) && isset($country['title'])) {
                    $countries[strtoupper($country['code'])] = $country['title'];
                }
            }
        }

        return $countries[$countryCode] ?? $countryCode;
    }

    /**
     * {@inheritdoc}
     */
    public function getActivityTypes(): array
    {
        $types = [];

        if (function_exists('fluentcrm_activity_types')) {
            $fluentTypes = fluentcrm_activity_types();

            foreach ($fluentTypes as $type) {
                $types[] = \PloverCRM\Core\CRM\Schemas\ActivityTypeSchema::normalize([
                    'id' => $type['id'] ?? '',
                    'label' => $type['label'] ?? '',
                ]);
            }
        }

        return $types;
    }

    /**
     * {@inheritdoc}
     */
    public function getRequiredUniqueField(): array
    {
        // FluentCRM always requires email as the unique identifier
        return [
            'field' => 'email',
            'label' => 'Email',
            'is_custom_field' => false,
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function validateContactData(array $data): array
    {
        // FluentCRM requires email
        if (empty($data['email'])) {
            return [
                'valid' => false,
                'error_code' => 'missing_email',
                'error_message' => 'Email address is required',
            ];
        }

        if (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
            return [
                'valid' => false,
                'error_code' => 'invalid_email',
                'error_message' => 'Please provide a valid email address',
            ];
        }

        return ['valid' => true, 'error_code' => null, 'error_message' => null];
    }

    /**
     * {@inheritdoc}
     */
    public function getContactOwner(int $contactId): ?int
    {
        if (!$this->isAvailable()) {
            return null;
        }

        try {
            $subscriber = \FluentCrm\App\Models\Subscriber::find($contactId);
            if (!$subscriber) {
                return null;
            }

            // FluentCRM stores contact_owner as user_id
            $ownerId = $subscriber->contact_owner;
            return $ownerId ? intval($ownerId) : null;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get contact owner', [
                'contact_id' => $contactId,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function setContactOwner(int $contactId, ?int $userId): bool
    {
        if (!$this->isAvailable()) {
            return false;
        }

        try {
            // Use direct update since contact_owner may not be in model's $fillable
            $updated = \FluentCrm\App\Models\Subscriber::where('id', $contactId)
                ->update(['contact_owner' => $userId]);

            return $updated > 0;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to set contact owner', [
                'contact_id' => $contactId,
                'user_id' => $userId,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getContactsByOwner(array $args = []): array
    {
        if (!$this->isAvailable()) {
            return ['contacts' => [], 'total' => 0];
        }

        try {
            $page = $args['page'] ?? 1;
            $per_page = $args['per_page'] ?? 20;
            $search = $args['search'] ?? '';
            $owner_id = $args['owner_id'] ?? null;
            $include_unassigned = $args['include_unassigned'] ?? false;
            $only_unassigned = $args['only_unassigned'] ?? false;

            $query = \FluentCrm\App\Models\Subscriber::query();

            // Apply ownership filter
            if ($only_unassigned) {
                $query->whereNull('contact_owner');
            } elseif ($owner_id !== null) {
                if ($include_unassigned) {
                    $query->where(function ($q) use ($owner_id) {
                        $q->where('contact_owner', $owner_id)
                            ->orWhereNull('contact_owner');
                    });
                } else {
                    $query->where('contact_owner', $owner_id);
                }
            }

            // Apply search filter
            if (!empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('email', 'LIKE', "%{$search}%")
                        ->orWhere('first_name', 'LIKE', "%{$search}%")
                        ->orWhere('last_name', 'LIKE', "%{$search}%");
                });
            }

            $total = $query->count();
            $subscribers = $query->orderBy('id', 'DESC')
                ->skip(($page - 1) * $per_page)
                ->take($per_page)
                ->get();

            $contacts = [];
            foreach ($subscribers as $subscriber) {
                $subscriber->load('tags', 'lists');
                $contacts[] = $this->normalizeContact($subscriber);
            }

            return [
                'contacts' => $contacts,
                'total' => $total,
            ];
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to get contacts by owner', [
                'args' => $args,
                'error' => $e->getMessage()
            ]);
            return ['contacts' => [], 'total' => 0];
        }
    }

    /**
     * {@inheritdoc}
     */
    public function updateCustomFieldValue(string $fieldSlug, string $oldValue, string $newValue): int
    {
        if (!$this->isAvailable()) {
            return 0;
        }

        try {
            global $wpdb;
            $table = $wpdb->prefix . 'fc_subscriber_meta';

            $updated = $wpdb->update(
                $table,
                ['value' => $newValue],
                [
                    'key' => $fieldSlug,
                    'value' => $oldValue,
                    'object_type' => 'custom_field'
                ],
                ['%s'],
                ['%s', '%s', '%s']
            );

            return $updated !== false ? $updated : 0;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to update custom field value', [
                'field_slug' => $fieldSlug,
                'old_value' => $oldValue,
                'new_value' => $newValue,
                'error' => $e->getMessage()
            ]);
            return 0;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function countContactsWithCustomFieldValue(string $fieldSlug, string $value): int
    {
        if (!$this->isAvailable()) {
            return 0;
        }

        try {
            global $wpdb;
            $subscribers_table = $wpdb->prefix . 'fc_subscribers';
            $meta_table = $wpdb->prefix . 'fc_subscriber_meta';

            $count = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT m.subscriber_id) FROM {$meta_table} m
                 INNER JOIN {$subscribers_table} s ON m.subscriber_id = s.id
                 WHERE m.`key` = %s AND m.`value` = %s AND m.`object_type` = 'custom_field'",
                $fieldSlug,
                $value
            ));

            return (int) $count;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to count contacts with custom field value', [
                'field_slug' => $fieldSlug,
                'value' => $value,
                'error' => $e->getMessage()
            ]);
            return 0;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function countContactsWithoutCustomField(string $fieldSlug): int
    {
        if (!$this->isAvailable()) {
            return 0;
        }

        try {
            global $wpdb;
            $subscribers_table = $wpdb->prefix . 'fc_subscribers';
            $meta_table = $wpdb->prefix . 'fc_subscriber_meta';

            $count = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$subscribers_table} s
                 WHERE NOT EXISTS (
                     SELECT 1 FROM {$meta_table} m
                     WHERE m.subscriber_id = s.id
                     AND m.`key` = %s
                     AND m.`object_type` = 'custom_field'
                 )",
                $fieldSlug
            ));

            return (int) $count;
        } catch (\Exception $e) {
            $this->getLogger()->logError('Failed to count contacts without custom field', [
                'field_slug' => $fieldSlug,
                'error' => $e->getMessage()
            ]);
            return 0;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function getAdminMenuParent(): string
    {
        return 'fluentcrm-admin';
    }

    /**
     * Remove duplicate custom field meta rows for a subscriber.
     *
     * FluentCRM's syncCustomFieldValues uses meta()->where('key')->first() which
     * returns the lowest-ID row and updates it. But custom_fields() iterates all
     * rows and the last one wins. If duplicates exist, the stale row gets returned.
     *
     * This keeps the row with the most recent updated_at (lowest ID as tiebreaker,
     * since syncCustomFieldValues updates the lowest-ID row).
     *
     * @param int $subscriberId
     * @param array $fieldKeys Custom field slugs to deduplicate
     */
    private function deduplicateCustomFieldMeta(int $subscriberId, array $fieldKeys): void
    {
        if (empty($fieldKeys)) {
            return;
        }

        global $wpdb;
        $meta_table = $wpdb->prefix . 'fc_subscriber_meta';

        foreach ($fieldKeys as $key) {
            // Count rows for this subscriber/key
            $count = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $meta_table
                 WHERE subscriber_id = %d AND `key` = %s AND object_type = 'custom_field'",
                $subscriberId,
                $key
            ));

            if ($count <= 1) {
                continue;
            }

            // Find the best row to keep: most recent updated_at, lowest ID as tiebreaker
            $keep_id = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM $meta_table
                 WHERE subscriber_id = %d AND `key` = %s AND object_type = 'custom_field'
                 ORDER BY updated_at DESC, id ASC
                 LIMIT 1",
                $subscriberId,
                $key
            ));

            if ($keep_id) {
                $wpdb->query($wpdb->prepare(
                    "DELETE FROM $meta_table
                     WHERE subscriber_id = %d AND `key` = %s AND object_type = 'custom_field' AND id != %d",
                    $subscriberId,
                    $key,
                    $keep_id
                ));
            }
        }
    }
}
