<?php
namespace App\Services;

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

final class InactivityRulesService
{
    private PDO $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: Database::connection();
    }

    public function tableReady(): bool
    {
        try {
            $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = "loyalty_inactivity_rules"');
            $stmt->execute();
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }

    public function rules(): array
    {
        if (!$this->tableReady()) return [];

        $sql = 'SELECT r.*,
                   u.full_name AS created_by_name,
                   (SELECT COUNT(*) FROM loyalty_inactivity_rule_applications a WHERE a.rule_id = r.id) AS total_applications,
                   (SELECT COALESCE(SUM(a.points_deducted),0) FROM loyalty_inactivity_rule_applications a WHERE a.rule_id = r.id) AS total_points_deducted
                FROM loyalty_inactivity_rules r
                LEFT JOIN users u ON u.id = r.created_by
                ORDER BY r.status = "active" DESC, r.updated_at DESC, r.id DESC';
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function recentRuns(int $limit = 10): array
    {
        if (!$this->tableReady()) return [];

        $limit = max(1, min(50, $limit));
        $stmt = $this->pdo->prepare('SELECT rr.*, r.rule_name, u.full_name AS run_by_name
            FROM loyalty_inactivity_rule_runs rr
            LEFT JOIN loyalty_inactivity_rules r ON r.id = rr.rule_id
            LEFT JOIN users u ON u.id = rr.run_by
            ORDER BY rr.started_at DESC, rr.id DESC
            LIMIT ' . $limit);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function stats(): array
    {
        if (!$this->tableReady()) {
            return [
                'active_rules' => 0,
                'rules' => 0,
                'members_penalized_30d' => 0,
                'points_deducted_30d' => 0,
                'last_run_at' => null,
            ];
        }

        $row = $this->pdo->query('SELECT
            COUNT(*) AS rules,
            SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) AS active_rules
            FROM loyalty_inactivity_rules')->fetch(PDO::FETCH_ASSOC) ?: [];

        $apps = $this->pdo->query('SELECT
            COUNT(DISTINCT user_id) AS members_penalized_30d,
            COALESCE(SUM(points_deducted),0) AS points_deducted_30d
            FROM loyalty_inactivity_rule_applications
            WHERE applied_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)')->fetch(PDO::FETCH_ASSOC) ?: [];

        $last = $this->pdo->query('SELECT MAX(started_at) FROM loyalty_inactivity_rule_runs')->fetchColumn();

        return [
            'rules' => (int)($row['rules'] ?? 0),
            'active_rules' => (int)($row['active_rules'] ?? 0),
            'members_penalized_30d' => (int)($apps['members_penalized_30d'] ?? 0),
            'points_deducted_30d' => (int)($apps['points_deducted_30d'] ?? 0),
            'last_run_at' => $last ?: null,
        ];
    }

    public function findRule(int $id): ?array
    {
        if ($id <= 0 || !$this->tableReady()) return null;
        $stmt = $this->pdo->prepare('SELECT * FROM loyalty_inactivity_rules WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        $rule = $stmt->fetch(PDO::FETCH_ASSOC);
        return $rule ?: null;
    }

    public function saveRule(array $input, int $adminId): int
    {
        if (!$this->tableReady()) {
            throw new \RuntimeException('Inactivity rules table is not installed. Run database/migrations/035_loyalty_inactivity_rules.sql');
        }

        $id = (int)($input['id'] ?? 0);
        $name = trim((string)($input['rule_name'] ?? ''));
        if ($name === '') $name = 'Inactive member point deduction';

        $description = trim((string)($input['description'] ?? ''));
        $days = max(1, (int)($input['inactivity_days'] ?? 30));
        $points = max(1, (int)($input['penalty_points'] ?? 1));
        $frequency = max(1, (int)($input['frequency_days'] ?? $days));
        $minPoints = max(0, (int)($input['min_points_before_penalty'] ?? 1));
        $maxMembers = max(0, (int)($input['max_members_per_run'] ?? 0));
        $allowNegative = !empty($input['allow_negative']) ? 1 : 0;
        $status = in_array((string)($input['status'] ?? 'active'), ['active', 'inactive'], true) ? (string)$input['status'] : 'active';
        $basis = in_array((string)($input['activity_basis'] ?? 'any_activity'), ['any_activity', 'completed_walk'], true) ? (string)$input['activity_basis'] : 'any_activity';
        $userStatus = in_array((string)($input['target_user_status'] ?? 'active'), ['active', 'inactive', 'all'], true) ? (string)$input['target_user_status'] : 'active';

        if ($id > 0) {
            $stmt = $this->pdo->prepare('UPDATE loyalty_inactivity_rules
                SET rule_name = ?, description = ?, inactivity_days = ?, penalty_points = ?, frequency_days = ?,
                    min_points_before_penalty = ?, max_members_per_run = ?, allow_negative = ?, activity_basis = ?,
                    target_user_status = ?, status = ?, updated_at = NOW()
                WHERE id = ?');
            $stmt->execute([$name, $description, $days, $points, $frequency, $minPoints, $maxMembers, $allowNegative, $basis, $userStatus, $status, $id]);
            return $id;
        }

        $stmt = $this->pdo->prepare('INSERT INTO loyalty_inactivity_rules
            (rule_name, description, inactivity_days, penalty_points, frequency_days, min_points_before_penalty,
             max_members_per_run, allow_negative, activity_basis, target_user_status, status, created_by, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())');
        $stmt->execute([$name, $description, $days, $points, $frequency, $minPoints, $maxMembers, $allowNegative, $basis, $userStatus, $status, $adminId]);
        return (int)$this->pdo->lastInsertId();
    }

    public function deleteRule(int $id): void
    {
        if ($id <= 0 || !$this->tableReady()) return;
        $stmt = $this->pdo->prepare('UPDATE loyalty_inactivity_rules SET status = "inactive", updated_at = NOW() WHERE id = ?');
        $stmt->execute([$id]);
    }

    public function eligibleMembers(array $rule, int $limit = 0): array
    {
        $ruleId = (int)$rule['id'];
        $days = max(1, (int)$rule['inactivity_days']);
        $frequency = max(1, (int)$rule['frequency_days']);
        $minPoints = max(0, (int)$rule['min_points_before_penalty']);
        $allowNegative = (int)($rule['allow_negative'] ?? 0) === 1;
        $basis = (string)($rule['activity_basis'] ?? 'any_activity');
        $targetStatus = (string)($rule['target_user_status'] ?? 'active');

        if ($limit <= 0) {
            $limit = (int)($rule['max_members_per_run'] ?? 0);
        }
        $limitSql = $limit > 0 ? ' LIMIT ' . min(10000, max(1, $limit)) : '';

        $activitySql = $basis === 'completed_walk'
            ? 'SELECT a.user_id, MAX(COALESCE(a.finish_time, a.updated_at, a.created_at)) AS last_activity_at
                 FROM attendance a
                 WHERE a.status IN ("finished", "manual_verified", "partially_completed") OR a.finish_time IS NOT NULL
                 GROUP BY a.user_id'
            : 'SELECT user_id, MAX(activity_at) AS last_activity_at
                 FROM (
                   SELECT a.user_id, COALESCE(a.finish_time, a.check_in_time, a.updated_at, a.created_at) AS activity_at
                   FROM attendance a
                   WHERE COALESCE(a.finish_time, a.check_in_time, a.updated_at, a.created_at) IS NOT NULL
                   UNION ALL
                   SELECT wr.user_id, COALESCE(wr.registered_at, wr.updated_at, wr.created_at) AS activity_at
                   FROM walk_registrations wr
                   WHERE wr.registration_status IN ("registered", "approved")
                 ) activity_union
                 GROUP BY user_id';

        $params = [];
        $statusSql = '';
        if ($targetStatus !== 'all') {
            $statusSql = ' AND u.status = ?';
            $params[] = $targetStatus;
        }

        $pointsSql = $allowNegative ? '1=1' : 'mp.loyalty_points > 0';
        $deductExpr = $allowNegative
            ? 'LEAST(?, ?)'
            : 'LEAST(?, GREATEST(0, mp.loyalty_points))';

        $params[] = $ruleId;
        $params[] = $frequency;
        $params[] = $days;
        $params[] = $minPoints;
        $params[] = $rule['penalty_points'];
        $params[] = (int)$rule['penalty_points'];

        $sql = "SELECT
                    u.id AS user_id,
                    u.full_name,
                    u.email,
                    u.mobile,
                    u.status AS user_status,
                    mp.loyalty_points,
                    COALESCE(act.last_activity_at, u.created_at) AS last_activity_at,
                    TIMESTAMPDIFF(DAY, COALESCE(act.last_activity_at, u.created_at), NOW()) AS inactive_days,
                    {$deductExpr} AS points_to_deduct
                FROM users u
                JOIN roles r ON r.id = u.role_id
                JOIN member_profiles mp ON mp.user_id = u.id
                LEFT JOIN ({$activitySql}) act ON act.user_id = u.id
                WHERE r.name = 'member'
                  {$statusSql}
                  AND {$pointsSql}
                  AND NOT EXISTS (
                    SELECT 1 FROM loyalty_inactivity_rule_applications app
                    WHERE app.rule_id = ?
                      AND app.user_id = u.id
                      AND app.applied_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  )
                  AND COALESCE(act.last_activity_at, u.created_at, '1970-01-01') <= DATE_SUB(NOW(), INTERVAL ? DAY)
                  AND mp.loyalty_points >= ?
                HAVING points_to_deduct > 0
                ORDER BY inactive_days DESC, mp.loyalty_points DESC, u.full_name ASC{$limitSql}";

        // The deduct expression placeholders must come before rule/frequency/days placeholders in SELECT.
        // Rebuild parameter order based on the SQL order: deductExpr first, then status, then rule checks.
        $ordered = [(int)$rule['penalty_points']];
        if (!$allowNegative) {
            // second placeholder is not used in non-negative expression because the second arg is loyalty_points.
        } else {
            $ordered[] = (int)$rule['penalty_points'];
        }

        if ($targetStatus !== 'all') {
            $ordered[] = $targetStatus;
        }
        $ordered[] = $ruleId;
        $ordered[] = $frequency;
        $ordered[] = $days;
        $ordered[] = $minPoints;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($ordered);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function preview(int $ruleId, int $limit = 50): array
    {
        $rule = $this->findRule($ruleId);
        if (!$rule) {
            throw new \RuntimeException('Rule not found.');
        }

        $members = $this->eligibleMembers($rule, $limit);
        $points = 0;
        foreach ($members as $m) {
            $points += (int)$m['points_to_deduct'];
        }

        return [
            'rule' => $rule,
            'members' => $members,
            'member_count' => count($members),
            'points_to_deduct' => $points,
        ];
    }

    public function runRule(int $ruleId, int $adminId = 0, bool $dryRun = false): array
    {
        $rule = $this->findRule($ruleId);
        if (!$rule) {
            throw new \RuntimeException('Rule not found.');
        }

        if ((string)$rule['status'] !== 'active' && !$dryRun) {
            throw new \RuntimeException('Only active rules can be applied.');
        }

        $members = $this->eligibleMembers($rule);
        $summary = [
            'eligible_count' => count($members),
            'applied_count' => 0,
            'skipped_count' => 0,
            'points_deducted' => 0,
            'dry_run' => $dryRun ? 1 : 0,
            'members' => $members,
        ];

        if ($dryRun) {
            foreach ($members as $m) $summary['points_deducted'] += (int)$m['points_to_deduct'];
            return $summary;
        }

        $this->pdo->beginTransaction();
        $runId = 0;

        try {
            $runStmt = $this->pdo->prepare('INSERT INTO loyalty_inactivity_rule_runs
                (rule_id, run_by, dry_run, eligible_count, applied_count, skipped_count, points_deducted, started_at, created_at)
                VALUES (?, ?, 0, ?, 0, 0, 0, NOW(), NOW())');
            $runStmt->execute([(int)$rule['id'], $adminId ?: null, count($members)]);
            $runId = (int)$this->pdo->lastInsertId();

            $updateStmt = $this->pdo->prepare('UPDATE member_profiles
                SET loyalty_points = CASE
                    WHEN ? = 1 THEN loyalty_points - ?
                    ELSE GREATEST(0, loyalty_points - ?)
                END,
                updated_at = NOW()
                WHERE user_id = ?');

            $appStmt = $this->pdo->prepare('INSERT INTO loyalty_inactivity_rule_applications
                (rule_id, run_id, user_id, points_before, points_deducted, points_after, last_activity_at, inactive_days, applied_at, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())');

            $txStmt = $this->pdo->prepare('INSERT INTO loyalty_transactions
                (user_id, walk_id, points, transaction_type, description, created_at)
                VALUES (?, NULL, ?, "adjustment", ?, NOW())');

            foreach ($members as $member) {
                $userId = (int)$member['user_id'];
                $before = (int)$member['loyalty_points'];
                $deduct = (int)$member['points_to_deduct'];
                if ($deduct <= 0) {
                    $summary['skipped_count']++;
                    continue;
                }

                $after = ((int)$rule['allow_negative'] === 1) ? ($before - $deduct) : max(0, $before - $deduct);

                $updateStmt->execute([(int)$rule['allow_negative'], $deduct, $deduct, $userId]);
                $appStmt->execute([
                    (int)$rule['id'],
                    $runId,
                    $userId,
                    $before,
                    $deduct,
                    $after,
                    $member['last_activity_at'] ?? null,
                    (int)($member['inactive_days'] ?? 0),
                ]);
                $txStmt->execute([
                    $userId,
                    -1 * $deduct,
                    'Inactive for ' . (int)$rule['inactivity_days'] . ' days: ' . (string)$rule['rule_name'],
                ]);

                $summary['applied_count']++;
                $summary['points_deducted'] += $deduct;
            }

            $finishStmt = $this->pdo->prepare('UPDATE loyalty_inactivity_rule_runs
                SET applied_count = ?, skipped_count = ?, points_deducted = ?, finished_at = NOW()
                WHERE id = ?');
            $finishStmt->execute([$summary['applied_count'], $summary['skipped_count'], $summary['points_deducted'], $runId]);

            $this->pdo->prepare('UPDATE loyalty_inactivity_rules SET last_run_at = NOW(), updated_at = NOW() WHERE id = ?')
                ->execute([(int)$rule['id']]);

            $this->pdo->commit();

            $summary['run_id'] = $runId;
            return $summary;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    public function runAllActive(int $adminId = 0): array
    {
        $rules = array_filter($this->rules(), static fn($r) => (string)($r['status'] ?? '') === 'active');
        $results = [];

        foreach ($rules as $rule) {
            $results[] = [
                'rule_id' => (int)$rule['id'],
                'rule_name' => $rule['rule_name'],
                'result' => $this->runRule((int)$rule['id'], $adminId, false),
            ];
        }

        return $results;
    }
}
