<?php

namespace LinkFiliate\App\Services\Importers;

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

class ThirstyAffiliatesImporter implements ImporterInterface
{
    private $wpdb;
    private $stats = [
        'links' => ['imported' => 0, 'updated' => 0, 'skipped' => 0],
        'categories' => ['imported' => 0, 'updated' => 0, 'skipped' => 0],
        'clicks' => ['imported' => 0, 'skipped' => 0],
        'relationships' => ['created' => 0, 'skipped' => 0],
    ];
    private $category_map = []; // Cache for slug -> id mapping
    private $slug_mapping = []; // Cache for old_slug -> new_slug mapping (for create_new strategy)

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
    }

    public function import(array $options): array
    {
        $batch_size = intval($options['limit'] ?? 100);
        $offset = intval($options['offset'] ?? 0);
        $duplicate_handling = $options['duplicateHandling'] ?? 'skip';
        $import_categories = !empty($options['categories']);
        $import_click_stats = !empty($options['click_stats']);

        // IMPORTANT: Import ALL categories FIRST (only in first batch)
        if ($import_categories && $offset === 0) {
            $this->importCategories($duplicate_handling);
        }

        // Get total counts for progress tracking
        $total_links = $this->getTotalLinks();

        // Import links in batch
        $links = $this->fetchLinksBatch($batch_size, $offset);

        foreach ($links as $link) {
            $this->importLink($link, $duplicate_handling, $import_click_stats, $import_categories);
        }

        // Calculate progress
        $next_offset = $offset + $batch_size;
        $progress = min(100, round(($next_offset / max($total_links, 1)) * 100));
        $is_done = $next_offset >= $total_links;

        return [
            'imported' => $this->stats['links']['imported'],
            'updated' => $this->stats['links']['updated'],
            'skipped' => $this->stats['links']['skipped'],
            'total' => $total_links,
            'offset' => $next_offset,
            'progress' => $progress,
            'done' => $is_done,
            'summary' => [
                'links' => [
                    'imported' => $this->stats['links']['imported'],
                    'updated' => $this->stats['links']['updated'],
                    'skipped' => $this->stats['links']['skipped'],
                    'total' => $this->stats['links']['imported'] + $this->stats['links']['updated'],
                ],
                'categories' => $import_categories ? [
                    'imported' => $this->stats['categories']['imported'],
                    'updated' => $this->stats['categories']['updated'],
                    'skipped' => $this->stats['categories']['skipped'],
                    'total' => $this->stats['categories']['imported'] + $this->stats['categories']['updated'],
                ] : 'skipped',
                'clicks' => $import_click_stats ? [
                    'imported' => $this->stats['clicks']['imported'],
                    'skipped' => $this->stats['clicks']['skipped'],
                ] : 'skipped',
                'relationships' => $import_categories ? [
                    'created' => $this->stats['relationships']['created'],
                    'skipped' => $this->stats['relationships']['skipped'],
                ] : 'skipped',
            ],
        ];
    }

    private function getTotalLinks(): int
    {
        return (int) $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(ID) FROM %i WHERE post_type = %s AND post_status = %s",
            $this->wpdb->posts,
            'thirstylink',
            'publish'
        ));
    }

    private function fetchLinksBatch(int $limit, int $offset): array
    {
        return $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT ID, post_title, post_name, post_content 
            FROM %i 
            WHERE post_type = %s AND post_status = %s
            ORDER BY ID ASC
            LIMIT %d OFFSET %d",
            $this->wpdb->posts,
            'thirstylink',
            'publish',
            $limit,
            $offset
        ));
    }

    private function importLink(object $link, string $duplicate_handling, bool $import_clicks, bool $import_categories): void
    {
        $target_url = get_post_meta($link->ID, '_ta_destination_url', true);

        if (empty($target_url)) {
            $this->stats['links']['skipped']++;
            return;
        }

        $slug = sanitize_title($link->post_name);
        $target_links = $this->wpdb->prefix . 'linkfiliate_campaigns';

        // Check for existing link
        $existing = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT id FROM %i WHERE slug = %s",
            $target_links,
            $slug
        ));

        $link_data = $this->prepareLinkData($link, $target_url);
        $target_id = null;

        if ($existing) {
            $target_id = $existing->id;

            switch ($duplicate_handling) {
                case 'overwrite':
                    $result = $this->wpdb->update(
                        $target_links,
                        array_merge($link_data, ['updated_at' => current_time('mysql')]),
                        ['id' => $existing->id]
                    );
                    if ($result !== false) {
                        $this->stats['links']['updated']++;
                    } else {
                        $this->stats['links']['skipped']++;
                    }
                    break;

                case 'create_new':
                    $new_slug = $this->generateUniqueSlug($slug);
                    $link_data['slug'] = $new_slug;
                    $result = $this->wpdb->insert($target_links, $link_data);
                    if ($result) {
                        $this->stats['links']['imported']++;
                        $target_id = $this->wpdb->insert_id;
                    } else {
                        $this->stats['links']['skipped']++;
                    }
                    break;

                default: // skip
                    $this->stats['links']['skipped']++;
                    break;
            }
        } else {
            $result = $this->wpdb->insert($target_links, $link_data);
            if ($result) {
                $this->stats['links']['imported']++;
                $target_id = $this->wpdb->insert_id;
            } else {
                $this->stats['links']['skipped']++;
            }
        }



        // Import category relationships for this link
        if ($target_id && $import_categories) {
            $this->importLinkCategories($link->ID, $target_id);
        }

        // Import clicks for this link
        if ($import_clicks && $target_id) {
            // $this->importClicksForLink($link->ID, $target_id);
            $this->import_stats_with_meta($link->ID, $target_id);
        }
    }

    private function prepareLinkData(object $link, string $target_url): array
    {
        $nofollow       = get_post_meta($link->ID, '_ta_no_follow', true) === 'yes';
        $new_window     = get_post_meta($link->ID, '_ta_new_window', true) === 'yes';
        $pass_query_str = get_post_meta($link->ID, '_ta_pass_query_str', true) === 'yes';

        $redirect       = get_post_meta($link->ID, '_ta_redirect_type', true) ?: '302';

        $settings = wp_json_encode([
            'no_follow'       => (bool) $nofollow,
            'sponsored'       => false,
            'redirect_type'   => $redirect,
            'new_window'      => (bool) $new_window,
            'parameter_forwarding'  => (bool) $pass_query_str,
        ]);

        return [
            'name' => $link->post_title,
            'slug' => sanitize_title($link->post_name),
            'target_url' => $target_url,
            'description' => $link->post_content,
            'settings' => $settings,
            'created_at' => current_time('mysql'),
        ];
    }

    private function generateUniqueSlug(string $base_slug): string
    {
        $target_links = $this->wpdb->prefix . 'linkfiliate_campaigns';
        $counter = 1;
        $new_slug = $base_slug;

        while ($this->wpdb->get_var($this->wpdb->prepare(
            "SELECT id FROM %i WHERE slug = %s",
            $target_links,
            $new_slug
        ))) {
            $new_slug = $base_slug . '-' . $counter;
            $counter++;
        }

        return $new_slug;
    }

    private function importCategories(string $duplicate_handling): void
    {
        $target_cats = $this->wpdb->prefix . 'linkfiliate_categories';

        $ta_categories = get_terms([
            'taxonomy' => 'thirstylink-category',
            'hide_empty' => false,
        ]);

        if (is_wp_error($ta_categories)) {
            return;
        }

        foreach ($ta_categories as $term) {
            $existing_cat = $this->wpdb->get_row($this->wpdb->prepare(
                "SELECT id FROM %i WHERE slug = %s",
                $target_cats,
                $term->slug
            ));

            $category_data = [
                'name' => $term->name,
                'slug' => $term->slug,
                'sort_order' => intval($term->term_order ?? 0),
            ];

            if ($existing_cat) {
                switch ($duplicate_handling) {
                    case 'overwrite':
                        $result = $this->wpdb->update(
                            $target_cats,
                            array_merge($category_data, ['updated_at' => current_time('mysql')]),
                            ['id' => $existing_cat->id]
                        );
                        if ($result !== false) {
                            $this->stats['categories']['updated']++;
                            // Cache the mapping
                            $this->category_map[$term->slug] = $existing_cat->id;
                        } else {
                            $this->stats['categories']['skipped']++;
                        }
                        break;

                    case 'create_new':
                        $new_slug = $this->generateUniqueCategorySlug($term->slug);
                        $category_data['slug'] = $new_slug;
                        $category_data['created_at'] = current_time('mysql');
                        $result = $this->wpdb->insert($target_cats, $category_data);
                        if ($result) {
                            $this->stats['categories']['imported']++;
                            // Cache the mapping with NEW slug
                            $this->slug_mapping[$term->slug] = $new_slug;
                            $this->category_map[$new_slug] = $this->wpdb->insert_id;
                        } else {
                            $this->stats['categories']['skipped']++;
                        }
                        break;

                    default: // skip
                        $this->stats['categories']['skipped']++;
                        // Still cache existing category
                        $this->category_map[$term->slug] = $existing_cat->id;
                        break;
                }
            } else {
                $category_data['created_at'] = current_time('mysql');
                $result = $this->wpdb->insert($target_cats, $category_data);
                if ($result) {
                    $this->stats['categories']['imported']++;
                    // Cache the mapping
                    $this->category_map[$term->slug] = $this->wpdb->insert_id;
                } else {
                    $this->stats['categories']['skipped']++;
                }
            }
        }
    }

    private function generateUniqueCategorySlug(string $base_slug): string
    {
        $target_cats = $this->wpdb->prefix . 'linkfiliate_categories';
        $counter = 1;
        $new_slug = $base_slug;

        while ($this->wpdb->get_var($this->wpdb->prepare(
            "SELECT id FROM %i WHERE slug = %s",
            $target_cats,
            $new_slug
        ))) {
            $new_slug = $base_slug . '-' . $counter;
            $counter++;
        }

        return $new_slug;
    }

    /**
     * Import link-category relationships
     * 
     * @param int $source_link_id ThirstyAffiliates link post ID
     * @param int $target_link_id LinkFilliate campaign ID
     */
    private function importLinkCategories(int $source_link_id, int $target_link_id): void
    {


        // Get ThirstyAffiliates categories for this link
        $terms = wp_get_post_terms($source_link_id, 'thirstylink-category', ['fields' => 'all']);

        if (is_wp_error($terms) || empty($terms)) {
            return;
        }

        $target_link_cats = $this->wpdb->prefix . 'linkfiliate_campaign_categories';

        foreach ($terms as $term) {
            // Get category ID using cached mapping
            $category_id = $this->getCategoryIdBySlug($term->slug);

            if (!$category_id) {
                // Category doesn't exist in LinkFilliate, skip this relationship
                $this->stats['relationships']['skipped']++;
                continue;
            }

            // Check if relationship already exists (prevent duplicates)
            $exists = $this->wpdb->get_var($this->wpdb->prepare(
                "SELECT id FROM %i WHERE campaign_id = %d AND category_id = %d",
                $target_link_cats,
                $target_link_id,
                $category_id
            ));

            if ($exists) {
                // Relationship already exists, skip
                $this->stats['relationships']['skipped']++;
                continue;
            }

            // Insert new relationship
            $result = $this->wpdb->insert($target_link_cats, [
                'campaign_id' => $target_link_id,
                'category_id' => $category_id,
            ]);

            if ($result) {
                $this->stats['relationships']['created']++;
            } else {
                $this->stats['relationships']['skipped']++;
            }
        }
    }

    /**
     * Get category ID by slug with caching
     * Handles slug mapping for "create_new" duplicate strategy
     * 
     * @param string $original_slug Original ThirstyAffiliates category slug
     * @return int|null Category ID or null if not found
     */
    private function getCategoryIdBySlug(string $original_slug): ?int
    {
        // Check if slug was remapped (for create_new strategy)
        $slug = $this->slug_mapping[$original_slug] ?? $original_slug;

        // Return cached value if available
        if (isset($this->category_map[$slug])) {
            return $this->category_map[$slug];
        }

        // Query database and cache result
        $target_cats = $this->wpdb->prefix . 'linkfiliate_categories';
        $category = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT id FROM %i WHERE slug = %s",
            $target_cats,
            $slug
        ));

        $this->category_map[$slug] = $category ? (int)$category->id : null;

        return $this->category_map[$slug];
    }


    private function import_stats_with_meta($ta_link_id, $lc_link_id): int
    {
        $stats_table = $this->wpdb->prefix . 'ta_link_clicks';
        $stats_meta_table = $this->wpdb->prefix . 'ta_link_clicks_meta';

        // Check if stats tables exist
        if ($this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $stats_table)) != $stats_table) {
            return 0;
        }

        // Process in chunks to avoid memory issues
        $batch_size = 100;
        $offset = 0;
        $total_imported = 0;

        while (true) {
            // Get click records
            $clicks = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT id, date_clicked, link_id
                FROM %i
                WHERE link_id = %d
                ORDER BY date_clicked ASC
                LIMIT %d OFFSET %d",
                $stats_table,
                $ta_link_id,
                $batch_size,
                $offset
            ), ARRAY_A);

            if (empty($clicks)) {
                break; // No more stats for this link
            }

            // Get meta data for all clicks in this batch efficiently
            $click_ids = wp_list_pluck($clicks, 'id');
            $meta_data = $this->get_clicks_meta_data($click_ids, $stats_meta_table);

            // Process each click with its meta data
            foreach ($clicks as $click) {
                $click_id = $click['id'];
                $click_meta = $meta_data[$click_id] ?? [];

                // Insert into LinkFilliate clicks table
                $this->wpdb->insert($this->wpdb->prefix . 'linkfiliate_clicks', [
                    'campaign_id' => $lc_link_id,
                    'created_at' => $click['date_clicked'],
                    'referrer' => $click_meta['http_referrer'] ?? '',
                    'user_agent' => $click_meta['browser_device'] ?? '',
                    'ip_address' => $click_meta['user_ip_address'] ?? '',
                ]);

                if ($this->wpdb->insert_id) {
                    $total_imported++;
                }
            }

            $offset += $batch_size;

            // Safety check to prevent infinite loops
            if ($offset > 1000) { // Max clicks per link
                break;
            }
        }

        return $total_imported;
    }

    
    private function get_clicks_meta_data($click_ids, $stats_meta_table): array
    {
        if (empty($click_ids)) {
            return array();
        }

        // Check if meta table exists
        if ($this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $stats_meta_table)) != $stats_meta_table) {
            return array();
        }

        // Create placeholders for IN clause
        $placeholders = array_fill(0, count($click_ids), '%d');
        $placeholders_str = implode(',', $placeholders);

        // Prepare the query with table identifier and click IDs
        $query = $this->wpdb->prepare(
            "SELECT click_id, meta_key, meta_value
            FROM %i
            WHERE click_id IN ($placeholders_str)",
            array_merge([$stats_meta_table], $click_ids)
        );

        $meta_rows = $this->wpdb->get_results($query, ARRAY_A);

        // Organize meta data by click_id
        $meta_data = array();
        foreach ($meta_rows as $row) {
            $click_id = $row['click_id'];
            if (!isset($meta_data[$click_id])) {
                $meta_data[$click_id] = array();
            }
            $meta_data[$click_id][$row['meta_key']] = $row['meta_value'];
        }

        return $meta_data;
    }
}
