<?php

namespace FleekDash\Models;

defined('ABSPATH') || exit;

class SiteGrowthAchievement extends BaseModel
{
    protected $table = 'fleekdash_site_growth_achievements';
    protected $primaryKey = 'id';

    protected $fillable = [
        'achievement_key',
        'achievement_name',
        'description',
        'icon',
        'color',
        'requirement_type',
        'requirement_value',
        'reward_points',
        'achieved_at',
        'is_active'
    ];

    protected $casts = [
        'is_active' => 'boolean',
        'requirement_value' => 'integer',
        'reward_points' => 'integer',
    ];

    /**
     * Get all unlocked achievements
     */
    public static function getUnlocked()
    {
        $instance = new static();

        $results = $instance->wpdb->get_results(
            "SELECT * FROM {$instance->table} WHERE achieved_at IS NOT NULL ORDER BY achieved_at DESC",
            ARRAY_A
        );

        return array_map([$instance, 'castAttributes'], $results);
    }

    /**
     * Get all active achievements
     */
    public static function getActive()
    {
        $instance = new static();

        $results = $instance->wpdb->get_results(
            "SELECT * FROM {$instance->table} WHERE is_active = 1 ORDER BY achievement_name ASC",
            ARRAY_A
        );

        return array_map([$instance, 'castAttributes'], $results);
    }

    /**
     * Unlock achievement
     */
    public static function unlock($achievement_key)
    {
        $instance = new static();

        return $instance->wpdb->update(
            $instance->table,
            ['achieved_at' => \current_time('mysql')],
            ['achievement_key' => $achievement_key]
        );
    }

    /**
     * Get all categories
     */
    public static function getCategories()
    {
        return [
            'record_breaker' => [
                'name' => 'Record Breaker',
                'description' => 'General site-wide records',
                'icon' => 'trophy',
                'color' => '#FFD700',
                'order' => 1
            ],
            'content_master' => [
                'name' => 'Content Master',
                'description' => 'Content creation achievements',
                'icon' => 'edit',
                'color' => '#4CAF50',
                'order' => 2
            ],
            'engagement_guru' => [
                'name' => 'Engagement Guru',
                'description' => 'User engagement achievements',
                'icon' => 'users',
                'color' => '#2196F3',
                'order' => 3
            ],
            'seo_expert' => [
                'name' => 'SEO Expert',
                'description' => 'SEO optimization achievements',
                'icon' => 'search',
                'color' => '#FF9800',
                'order' => 4
            ]
        ];
    }

    /**
     * Get achievements by category
     */
    public static function getByCategory($category, $limit = null)
    {
        $instance = new static();

        $sql = "SELECT * FROM {$instance->table}
                WHERE is_active = 1 AND category = %s
                ORDER BY display_order ASC, requirement_value DESC";

        if ($limit) {
            $sql .= " LIMIT %d";
            $results = $instance->wpdb->get_results(
                $instance->wpdb->prepare($sql, $category, $limit),
                ARRAY_A
            );
        } else {
            $results = $instance->wpdb->get_results(
                $instance->wpdb->prepare($sql, $category),
                ARRAY_A
            );
        }

        return array_map([$instance, 'castAttributes'], $results);
    }

    /**
     * Get achievements close to completion
     */
    public static function getCloseToCompletion($limit = 5)
    {
        $instance = new static();

        $sql = "SELECT * FROM {$instance->table}
                WHERE is_active = 1
                AND achieved_at IS NULL
                AND target_value > 0
                ORDER BY (current_record / target_value) DESC
                LIMIT %d";

        $results = $instance->wpdb->get_results(
            $instance->wpdb->prepare($sql, $limit),
            ARRAY_A
        );

        return array_map([$instance, 'castAttributes'], $results);
    }

    /**
     * Get dashboard summary statistics
     */
    public static function getDashboardSummary()
    {
        $instance = new static();

        $total = $instance->wpdb->get_var(
            "SELECT COUNT(*) FROM {$instance->table} WHERE is_active = 1"
        );

        $unlocked = $instance->wpdb->get_var(
            "SELECT COUNT(*) FROM {$instance->table} WHERE is_active = 1 AND achieved_at IS NOT NULL"
        );

        $in_progress = $instance->wpdb->get_var(
            "SELECT COUNT(*) FROM {$instance->table}
             WHERE is_active = 1 AND achieved_at IS NULL AND current_record > 0"
        );

        return [
            'total' => (int)$total,
            'unlocked' => (int)$unlocked,
            'in_progress' => (int)$in_progress,
            'locked' => (int)($total - $unlocked)
        ];
    }

    /**
     * Get next display order for a category
     */
    public static function getNextDisplayOrder($category)
    {
        $instance = new static();

        $max_order = $instance->wpdb->get_var($instance->wpdb->prepare(
            "SELECT MAX(display_order) FROM {$instance->table} WHERE category = %s",
            $category
        ));

        return $max_order ? (int)$max_order + 1 : 1;
    }
}
