<?php
namespace App\Services;

use App\Core\Database;
use PDO;
use Throwable;

final class TierService
{
    public static function tablesReady(?PDO $pdo = null): bool
    {
        $pdo = $pdo ?: Database::connection();
        try {
            $pdo->query('SELECT id FROM loyalty_tiers LIMIT 1');
            $pdo->query('SELECT id FROM member_tier_assignments LIMIT 1');
            return true;
        } catch (Throwable) {
            return false;
        }
    }

    public static function expireOldTiers(?PDO $pdo = null): int
    {
        $pdo = $pdo ?: Database::connection();
        if (!self::tablesReady($pdo)) return 0;
        $stmt = $pdo->prepare('UPDATE member_tier_assignments SET status = "expired", updated_at = NOW() WHERE status = "active" AND expires_at IS NOT NULL AND expires_at < NOW()');
        $stmt->execute();
        return $stmt->rowCount();
    }

    public static function activeTiers(?PDO $pdo = null): array
    {
        $pdo = $pdo ?: Database::connection();
        if (!self::tablesReady($pdo)) return [];
        return $pdo->query('SELECT * FROM loyalty_tiers WHERE status = "active" ORDER BY sort_order DESC, min_points DESC, required_badge_count DESC, id DESC')->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function memberMetrics(PDO $pdo, int $userId): array
    {
        $stmt = $pdo->prepare('SELECT COALESCE(mp.loyalty_points,0) AS points, COALESCE(mp.total_walks,0) AS total_walks, COALESCE(mp.total_kilometers,0) AS total_kilometers,
            (SELECT COUNT(*) FROM user_badges ub WHERE ub.user_id = ?) AS badge_count
            FROM users u
            LEFT JOIN member_profiles mp ON mp.user_id = u.id
            WHERE u.id = ? LIMIT 1');
        $stmt->execute([$userId, $userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        return [
            'points' => (int)($row['points'] ?? 0),
            'badge_count' => (int)($row['badge_count'] ?? 0),
            'total_walks' => (int)($row['total_walks'] ?? 0),
            'total_kilometers' => (float)($row['total_kilometers'] ?? 0),
        ];
    }

    private static function requiredBadgeIds(array $tier): array
    {
        $raw = trim((string)($tier['required_badges_json'] ?? ''));
        if ($raw === '') return [];
        $decoded = json_decode($raw, true);
        if (!is_array($decoded)) return [];
        return array_values(array_unique(array_filter(array_map('intval', $decoded))));
    }

    private static function memberHasRequiredBadges(PDO $pdo, int $userId, array $badgeIds): bool
    {
        if (!$badgeIds) return true;
        $placeholders = implode(',', array_fill(0, count($badgeIds), '?'));
        $params = array_merge([$userId], $badgeIds);
        $stmt = $pdo->prepare("SELECT COUNT(DISTINCT badge_id) FROM user_badges WHERE user_id = ? AND badge_id IN ($placeholders)");
        $stmt->execute($params);
        return (int)$stmt->fetchColumn() >= count($badgeIds);
    }

    public static function qualifiedTier(PDO $pdo, int $userId): ?array
    {
        if (!self::tablesReady($pdo)) return null;
        $metrics = self::memberMetrics($pdo, $userId);
        foreach (self::activeTiers($pdo) as $tier) {
            if ($metrics['points'] < (int)$tier['min_points']) continue;
            if ($metrics['badge_count'] < (int)$tier['required_badge_count']) continue;
            if (!self::memberHasRequiredBadges($pdo, $userId, self::requiredBadgeIds($tier))) continue;
            $tier['_metrics'] = $metrics;
            return $tier;
        }
        return null;
    }

    public static function currentTier(PDO $pdo, int $userId): ?array
    {
        if (!self::tablesReady($pdo)) return null;
        self::expireOldTiers($pdo);
        $stmt = $pdo->prepare('SELECT mta.*, lt.tier_name, lt.tier_code, lt.icon, lt.color, lt.description, lt.min_points, lt.required_badge_count, lt.sort_order
            FROM member_tier_assignments mta
            JOIN loyalty_tiers lt ON lt.id = mta.tier_id
            WHERE mta.user_id = ? AND mta.status = "active" AND (mta.expires_at IS NULL OR mta.expires_at >= NOW())
            ORDER BY lt.sort_order DESC, lt.min_points DESC, mta.assigned_at DESC
            LIMIT 1');
        $stmt->execute([$userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public static function recalculateMember(PDO $pdo, int $userId, int $adminId = 0): ?array
    {
        if (!self::tablesReady($pdo)) return null;
        self::expireOldTiers($pdo);
        $tier = self::qualifiedTier($pdo, $userId);
        if (!$tier) {
            $pdo->prepare('UPDATE member_tier_assignments SET status = "replaced", updated_at = NOW() WHERE user_id = ? AND status = "active"')->execute([$userId]);
            return null;
        }

        $current = self::currentTier($pdo, $userId);
        if ($current && (int)$current['tier_id'] === (int)$tier['id']) {
            return $current;
        }

        $pdo->prepare('UPDATE member_tier_assignments SET status = "replaced", updated_at = NOW() WHERE user_id = ? AND status = "active"')->execute([$userId]);
        $days = max(0, (int)($tier['level_valid_days'] ?? 365));
        $expiresAt = $days > 0 ? date('Y-m-d H:i:s', strtotime('+' . $days . ' days')) : null;
        $stmt = $pdo->prepare('INSERT INTO member_tier_assignments (user_id, tier_id, source, assigned_by, assigned_at, expires_at, status, notes, created_at, updated_at) VALUES (?, ?, "auto", ?, NOW(), ?, "active", ?, NOW(), NOW())');
        $stmt->execute([$userId, (int)$tier['id'], $adminId ?: null, $expiresAt, 'Auto-assigned by points/badges rule']);
        return self::currentTier($pdo, $userId);
    }

    public static function recalculateAll(PDO $pdo, int $adminId = 0): array
    {
        if (!self::tablesReady($pdo)) return ['updated' => 0, 'members' => []];
        self::expireOldTiers($pdo);
        $stmt = $pdo->query('SELECT u.id FROM users u JOIN roles r ON r.id = u.role_id WHERE r.name = "member" AND u.status = "active" ORDER BY u.id ASC');
        $updated = 0;
        $members = [];
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $id) {
            $before = self::currentTier($pdo, (int)$id);
            $after = self::recalculateMember($pdo, (int)$id, $adminId);
            if ((int)($before['tier_id'] ?? 0) !== (int)($after['tier_id'] ?? 0)) $updated++;
            $members[] = ['user_id' => (int)$id, 'tier' => $after['tier_name'] ?? null];
        }
        return ['updated' => $updated, 'members' => $members];
    }
}
