<?php

namespace FleekDash\Models;

defined('ABSPATH') || exit;

/**
 * Growth Achievement Model
 *
 * @deprecated 2.2.0 This model uses table 'fleekdash_growth_achievements' which doesn't exist.
 * Use SiteGrowthAchievement model instead which uses 'fleekdash_site_growth_achievements' table.
 * This model is kept for backward compatibility but may not work correctly.
 */
class GrowthAchievement extends BaseModel
{
    protected $table = 'fleekdash_growth_achievements';
    protected $primaryKey = 'id';

    // Achievement types
    const TYPE_DAILY_RECORD = 'daily_record';
    const TYPE_WEEKLY_RECORD = 'weekly_record';
    const TYPE_MONTHLY_RECORD = 'monthly_record';
    const TYPE_ALL_TIME_RECORD = 'all_time_record';
    const TYPE_POINTS_MILESTONE = 'points_milestone';
    const TYPE_STREAK_MILESTONE = 'streak_milestone';

    // Period types
    const PERIOD_DAILY = 'daily';
    const PERIOD_WEEKLY = 'weekly';
    const PERIOD_MONTHLY = 'monthly';
    const PERIOD_ALL_TIME = 'all_time';

    /** WordPress {@see wpdb::prepare()} fragment for dynamic numeric LIMIT (leading space). */
    private const SQL_LIMIT_FRAGMENT = ' LIMIT %d';

    protected $fillable = [
        'achievement_key',
        'achievement_name',
        'description',
        'category',
        'requirement_type',
        'requirement_value',
        'icon',
        'color',
        'reward_points',
        'is_active',
        'sort_order',
        'achievement_type',
        'period_type',
        'record_value',
        'previous_record',
        'points_breakdown',
        'is_milestone',
        'milestone_level',
        'achieved_at'
    ];

    protected $casts = [
        'is_active' => 'boolean',
        'requirement_value' => 'integer',
        'reward_points' => 'integer',
        'sort_order' => 'integer',
        'record_value' => 'integer',
        'previous_record' => 'integer',
        'is_milestone' => 'boolean',
        'milestone_level' => 'integer',
    ];

    /**
     * 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 sort_order ASC",
            ARRAY_A
        );

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

    /**
     * Get achievements by category
     *
     * @return list<array<string, mixed>>
     */
    public static function getByCategory(string $category): array
    {
        $instance = new static();

        $results = $instance->wpdb->get_results($instance->wpdb->prepare(
            "SELECT * FROM {$instance->table} WHERE category = %s AND is_active = 1 ORDER BY sort_order ASC",
            $category
        ), ARRAY_A);

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

    /**
     * Get latest record value for a period
     *
     * @param string $period_type Period type (daily, weekly, monthly, all_time)
     * @return int Latest record value
     */
    public static function getLatestRecord($period_type)
    {
        $instance = new static();

        $result = $instance->wpdb->get_var($instance->wpdb->prepare(
            "SELECT record_value FROM {$instance->table}
             WHERE period_type = %s
             ORDER BY achieved_at DESC, record_value DESC
             LIMIT 1",
            $period_type
        ));

        return $result ? intval($result) : 0;
    }

    /**
     * Create a new achievement
     *
     * @param array $data Achievement data
     * @return int|false Achievement ID or false on failure
     */
    public static function createAchievement($data)
    {
        $instance = new static();

        // Add timestamp
        if (!isset($data['achieved_at'])) {
            $data['achieved_at'] = \current_time('mysql');
        }

        // Insert achievement
        $result = $instance->wpdb->insert(
            $instance->table,
            $data,
            array_fill(0, count($data), '%s')
        );

        return $result ? $instance->wpdb->insert_id : false;
    }

    /**
     * Check if achievement already exists
     *
     * @param string $achievement_type Achievement type
     * @param string $period_type Period type
     * @param int $record_value Record value
     * @return bool True if exists
     */
    public static function achievementExists($achievement_type, $period_type, $record_value)
    {
        $instance = new static();

        $count = $instance->wpdb->get_var($instance->wpdb->prepare(
            "SELECT COUNT(*) FROM {$instance->table}
             WHERE achievement_type = %s
             AND period_type = %s
             AND record_value = %d",
            $achievement_type,
            $period_type,
            $record_value
        ));

        return intval($count) > 0;
    }

    /**
     * Get achievements by type
     *
     * @param string $achievement_type Achievement type
     * @param int|null $limit Limit number of results
     * @return array Achievements
     */
    public static function getByType($achievement_type, $limit = null)
    {
        $instance = new static();

        $sql = $instance->wpdb->prepare(
            "SELECT * FROM {$instance->table}
             WHERE achievement_type = %s
             ORDER BY achieved_at DESC",
            $achievement_type
        );

        if ($limit) {
            $sql = self::appendPreparedLimitClause($instance->wpdb, $sql, (int) $limit);
        }

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

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

    /**
     * Get all achievements
     *
     * @param int|null $limit Limit number of results
     * @return array Achievements
     */
    public static function getAllAchievements($limit = null)
    {
        $instance = new static();

        $sql = "SELECT * FROM {$instance->table} ORDER BY achieved_at DESC";

        if ($limit) {
            $sql = self::appendPreparedLimitClause($instance->wpdb, $sql, (int) $limit);
        }

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

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

    /**
     * Get achievements for a period (matches {@see self::PERIOD_DAILY} … {@see self::PERIOD_ALL_TIME}).
     *
     * @return list<array<string, mixed>>
     */
    public static function getByPeriod(string $period, ?int $limit = null): array
    {
        $instance = new static();
        $allowed = [
            self::PERIOD_DAILY,
            self::PERIOD_WEEKLY,
            self::PERIOD_MONTHLY,
            self::PERIOD_ALL_TIME,
        ];
        if (!\in_array($period, $allowed, true)) {
            return [];
        }

        $sql = $instance->wpdb->prepare(
            "SELECT * FROM {$instance->table}
             WHERE period_type = %s
             ORDER BY achieved_at DESC",
            $period
        );

        if ($limit !== null && $limit > 0) {
            $sql = self::appendPreparedLimitClause($instance->wpdb, $sql, $limit);
        }

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

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

    /**
     * Milestone rows (is_milestone = true), newest first.
     *
     * @return list<array<string, mixed>>
     */
    public static function getMilestones(?int $limit = null): array
    {
        $instance = new static();
        $sql = "SELECT * FROM {$instance->table} WHERE is_milestone = 1 ORDER BY achieved_at DESC";

        if ($limit !== null && $limit > 0) {
            $sql = self::appendPreparedLimitClause($instance->wpdb, $sql, $limit);
        }

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

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

    /**
     * Append a prepared LIMIT clause so the SQL limit literal lives only in {@see self::SQL_LIMIT_FRAGMENT}.
     */
    private static function appendPreparedLimitClause(\wpdb $wpdb, string $sql, int $limit): string
    {
        return $sql . $wpdb->prepare(self::SQL_LIMIT_FRAGMENT, $limit);
    }
}
