<?php
namespace App\Services;

use App\Repositories\OperationRepository;
use App\Repositories\OperationRuleRepository;
use App\Utils\Logger;
use PDO;
use Throwable;

class OperationManagerService
{
    private OperationRepository $operations;
    private OperationRuleRepository $rules;

    public function __construct(OperationRepository $operations, OperationRuleRepository $rules)
    {
        $this->operations = $operations;
        $this->rules = $rules;
    }

    public function preview(array $filters, int $page = 1, int $perPage = 50): array
    {
        $page = max(1, $page);
        $perPage = max(10, min(100, $perPage));
        $params = [];
        $where = $this->buildWhere($filters, $params);
        $pdo = $this->operations->getPdo();

        $countStmt = $pdo->prepare("SELECT COUNT(*) FROM persons p {$this->joins()} WHERE {$where}");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $offset = ($page - 1) * $perPage;
        $sql = "SELECT
                p.id AS person_id,
                p.didar_contact_id,
                p.first_name,
                p.last_name,
                p.mobile_phone,
                p.customer_code,
                p.owner_didar_id,
                owner.display_name AS owner_name,
                p.previous_owner_id,
                p.previous_owner_history,
                p.sale_status,
                p.register_time,
                p.register_time_jalali,
                p.lead_pool_id,
                lp.pool_key,
                lp.title AS pool_title,
                latest_deal.id AS last_deal_id,
                latest_deal.didar_deal_id AS last_deal_didar_id,
                latest_deal.status AS last_deal_status,
                latest_deal.pipeline_stage AS last_deal_pipeline_stage,
                latest_deal.register_time AS last_deal_at,
                latest_deal.failure_reason_code AS last_deal_failure_reason_code,
                latest_activity.register_date AS last_activity_at,
                latest_activity.activity_type_title AS last_activity_type,
                latest_payment.payment_date AS last_payment_at,
                latest_payment.amount AS last_payment_amount,
                latest_payment.status AS last_payment_status,
                CASE WHEN latest_payment.id IS NULL THEN 0 ELSE 1 END AS has_any_payment
            FROM persons p
            {$this->joins()}
            WHERE {$where}
            ORDER BY COALESCE(latest_deal.register_time, p.register_time, p.created_at) DESC
            LIMIT {$perPage} OFFSET {$offset}";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return [
            'rows' => $rows,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total_count' => $total,
                'total_pages' => (int)ceil($total / $perPage),
            ],
            'filters' => $filters,
        ];
    }

    public function execute(array $filters, array $operation, array $selectedIds, bool $selectAll, ?int $userId, string $source = 'manual', ?int $ruleId = null): array
    {
        $preview = $this->preview($filters, 1, 100);
        $allRows = $this->fetchAllRows($filters, $selectedIds, $selectAll);
        $runId = $this->operations->createRun($ruleId, $source, $userId, $filters, $operation);
        $affected = 0;
        $failed = 0;
        $errors = [];

        foreach ($allRows as $row) {
            try {
                $before = $row;
                $after = $this->applyOperation($row, $operation, $runId, $ruleId, $userId, $source);
                $affected++;
                $this->operations->insertRunItem($runId, [
                    'entity_type' => 'lead',
                    'entity_id' => (string)$row['person_id'],
                    'contact_didar_id' => (string)$row['didar_contact_id'],
                    'deal_didar_id' => (string)($row['last_deal_didar_id'] ?? ''),
                    'before' => $before,
                    'after' => $after,
                    'status' => 'success',
                ]);
            } catch (Throwable $e) {
                $failed++;
                $errors[] = $e->getMessage();
                $this->operations->insertRunItem($runId, [
                    'entity_type' => 'lead',
                    'entity_id' => (string)($row['person_id'] ?? ''),
                    'contact_didar_id' => (string)($row['didar_contact_id'] ?? ''),
                    'deal_didar_id' => (string)($row['last_deal_didar_id'] ?? ''),
                    'before' => $row,
                    'status' => 'failed',
                    'error_message' => $e->getMessage(),
                ]);
            }
        }

        $status = $failed > 0 ? ($affected > 0 ? 'partial_failed' : 'failed') : 'success';
        $this->operations->finishRun($runId, $status, (int)$preview['pagination']['total_count'], count($allRows), $affected, $failed, implode("\n", array_slice($errors, 0, 10)));
        if ($ruleId) {
            $this->rules->markRun($ruleId);
        }

        return [
            'run_id' => $runId,
            'matched_count' => (int)$preview['pagination']['total_count'],
            'selected_count' => count($allRows),
            'affected_count' => $affected,
            'failed_count' => $failed,
            'status' => $status,
            'errors' => array_slice($errors, 0, 10),
        ];
    }

    public function runRule(array $rule, ?int $userId = null, string $source = 'automation'): array
    {
        $filters = $this->decode($rule['filter_json'] ?? '{}');
        $condition = $this->decode($rule['condition_json'] ?? '{}');
        $operation = $this->decode($rule['operation_json'] ?? '{}');
        $filters = array_merge($filters, $this->conditionToFilters($condition));
        return $this->execute($filters, $operation, [], true, $userId, $source, (int)$rule['id']);
    }

    public function runDueRules(): array
    {
        $results = [];
        foreach ($this->rules->dueRules() as $rule) {
            $results[] = [
                'rule_id' => (int)$rule['id'],
                'name' => $rule['name'],
                'result' => $this->runRule($rule, null, 'automation'),
            ];
        }
        return $results;
    }

    public function exportAll(array $filters): array
    {
        $params = [];
        $where = $this->buildWhere($filters, $params);
        $pdo = $this->operations->getPdo();
        $sql = "SELECT
                p.first_name,
                p.last_name,
                p.mobile_phone,
                p.customer_code,
                p.sale_status,
                p.register_time,
                p.register_time_jalali,
                owner.display_name AS owner_name,
                prev_owner.display_name AS previous_owner_name,
                lp.pool_key,
                lp.title AS pool_title,
                latest_deal.status AS last_deal_status,
                latest_deal.register_time AS last_deal_at,
                latest_activity.register_date AS last_activity_at,
                latest_payment.payment_date AS last_payment_at,
                latest_payment.amount AS last_payment_amount
            FROM persons p
            {$this->joins()}
            LEFT JOIN users prev_owner ON prev_owner.didar_user_id = p.previous_owner_id
            WHERE {$where}
            ORDER BY COALESCE(latest_deal.register_time, p.register_time, p.created_at) DESC
            LIMIT 10000";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function ownerHistory(string $contactId): array
    {
        $stmt = $this->operations->getPdo()->prepare("SELECT * FROM lead_owner_history WHERE contact_didar_id = ? ORDER BY created_at DESC, id DESC");
        $stmt->execute([$contactId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function fetchAllRows(array $filters, array $selectedIds, bool $selectAll): array
    {
        if (!$selectAll && empty($selectedIds)) {
            return [];
        }
        $filters['_selected_ids'] = $selectAll ? [] : $selectedIds;
        $params = [];
        $where = $this->buildWhere($filters, $params);
        $sql = "SELECT p.id AS person_id, p.didar_contact_id, p.owner_didar_id, owner.display_name AS owner_name,
                       latest_deal.id AS last_deal_id, latest_deal.didar_deal_id AS last_deal_didar_id,
                       latest_deal.status AS last_deal_status, latest_deal.register_time AS last_deal_at,
                       latest_payment.id AS last_payment_id
                FROM persons p {$this->joins()} WHERE {$where} LIMIT 5000";
        $stmt = $this->operations->getPdo()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function applyOperation(array $row, array $operation, int $runId, ?int $ruleId, ?int $userId, string $source): array
    {
        $pdo = $this->operations->getPdo();
        $actions = $operation['actions'] ?? [];
        if (empty($actions) && !empty($operation['type'])) {
            $actions = [$operation];
        }
        $pdo->beginTransaction();
        try {
            foreach ($actions as $action) {
                $type = (string)($action['type'] ?? '');
                if ($type === 'change_owner') {
                    $this->changeOwner($row, trim((string)($action['owner_didar_id'] ?? '')), $source, $runId, $ruleId, $userId, (string)($action['reason'] ?? 'Operation manager owner change'));
                } elseif ($type === 'move_lead_to_pool') {
                    $this->moveToPool($row, (string)($action['pool_key'] ?? 'crm_leads'), $source, $runId, $ruleId, $userId, (string)($action['reason'] ?? 'Moved to CRM lead pool'));
                } elseif ($type === 'mark_last_deal_lost') {
                    $this->markLastDealLost($row, (string)($action['failure_reason'] ?? 'sales_agent_underperformance'));
                }
            }
            $pdo->commit();
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
        return ['applied_actions' => $actions];
    }

    private function changeOwner(array $row, string $newOwnerId, string $source, int $runId, ?int $ruleId, ?int $userId, string $reason): void
    {
        if ($newOwnerId === '') {
            throw new \InvalidArgumentException('owner_didar_id is required');
        }
        $this->setLeadOwner($row, $newOwnerId, null, null, $source, $runId, $ruleId, $userId, $reason);
    }

    private function moveToPool(array $row, string $poolKey, string $source, int $runId, ?int $ruleId, ?int $userId, string $reason): void
    {
        $pool = $this->operations->getLeadPoolByKey($poolKey);
        if (!$pool) {
            throw new \RuntimeException('Lead pool not found: ' . $poolKey);
        }
        $this->setLeadOwner($row, null, (int)$pool['id'], $reason, $source, $runId, $ruleId, $userId, $reason);
    }

    private function markLastDealLost(array $row, string $failureReason): void
    {
        if (empty($row['last_deal_id'])) {
            return;
        }
        $stmt = $this->operations->getPdo()->prepare("UPDATE deals
            SET status = 'Lost', failure_reason_code = ?, failure_reason_text = 'عدم توانمندی کارشناس فروش',
                failure_reason_description = COALESCE(NULLIF(failure_reason_description, ''), 'Operation manager automatic loss rule'),
                lost_time = COALESCE(lost_time, NOW()), last_update_time = NOW()
            WHERE id = ? AND status <> 'Won'");
        $stmt->execute([$failureReason, (int)$row['last_deal_id']]);
    }

    private function setLeadOwner(array $row, ?string $newOwnerId, ?int $poolId, ?string $poolReason, string $source, int $runId, ?int $ruleId, ?int $userId, string $reason): void
    {
        $pdo = $this->operations->getPdo();
        $oldOwner = (string)($row['owner_didar_id'] ?? '');
        $newOwnerName = $newOwnerId ? $this->ownerName($newOwnerId) : null;
        $oldOwnerName = $oldOwner !== '' ? $this->ownerName($oldOwner) : null;

        $stmt = $pdo->prepare("UPDATE persons
            SET owner_didar_id = ?, previous_owner_id = CASE WHEN ? IS NULL THEN previous_owner_id ELSE ? END,
                previous_owner_history = CASE
                    WHEN ? IS NULL OR ? = '' THEN previous_owner_history
                    WHEN previous_owner_history IS NULL OR previous_owner_history = '' THEN ?
                    WHEN FIND_IN_SET(?, previous_owner_history) > 0 THEN previous_owner_history
                    ELSE CONCAT(previous_owner_history, ',', ?)
                END,
                lead_pool_id = ?, pool_entered_at = CASE WHEN ? IS NULL THEN NULL ELSE NOW() END, pool_reason = ?
            WHERE id = ?");
        $stmt->execute([
            $newOwnerId,
            $oldOwner !== '' ? $oldOwner : null,
            $oldOwner !== '' ? $oldOwner : null,
            $oldOwner,
            $oldOwner,
            $oldOwner,
            $oldOwner,
            $oldOwner,
            $poolId,
            $poolId,
            $poolReason,
            (int)$row['person_id'],
        ]);

        $hist = $pdo->prepare("INSERT INTO lead_owner_history
            (person_id, contact_didar_id, from_owner_didar_id, from_owner_name, to_owner_didar_id, to_owner_name,
             changed_by_user_id, changed_by_didar_id, changed_by_name, change_source, operation_run_id, rule_id, reason, metadata_json, created_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())");
        $hist->execute([
            (int)$row['person_id'],
            (string)$row['didar_contact_id'],
            $oldOwner ?: null,
            $oldOwnerName,
            $newOwnerId,
            $newOwnerName,
            $userId,
            $_SESSION['didar_id'] ?? null,
            $_SESSION['display_name'] ?? $_SESSION['username'] ?? null,
            $source,
            $runId,
            $ruleId,
            $reason,
            json_encode(['pool_id' => $poolId], JSON_UNESCAPED_UNICODE),
        ]);
    }

    private function ownerName(string $didarId): ?string
    {
        $stmt = $this->operations->getPdo()->prepare("SELECT COALESCE(NULLIF(display_name, ''), CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,'')), email) FROM users WHERE didar_user_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        $name = trim((string)$stmt->fetchColumn());
        return $name !== '' ? $name : null;
    }

    private function joins(): string
    {
        return "LEFT JOIN users owner ON owner.didar_user_id = p.owner_didar_id
            LEFT JOIN lead_pools lp ON lp.id = p.lead_pool_id
            LEFT JOIN deals latest_deal ON latest_deal.id = (
                SELECT d2.id FROM deals d2 WHERE d2.contact_didar_id = p.didar_contact_id ORDER BY COALESCE(d2.register_time, d2.created_at) DESC, d2.id DESC LIMIT 1
            )
            LEFT JOIN activities latest_activity ON latest_activity.id = (
                SELECT a2.id FROM activities a2 WHERE a2.contact_didar_id = p.didar_contact_id ORDER BY COALESCE(a2.register_date, a2.created_at) DESC, a2.id DESC LIMIT 1
            )
            LEFT JOIN transactions latest_payment ON latest_payment.id = (
                SELECT t2.id FROM transactions t2
                INNER JOIN deals dpay ON dpay.id = t2.deal_id
                WHERE dpay.contact_didar_id = p.didar_contact_id AND COALESCE(t2.status, 'confirmed') <> 'cancelled'
                ORDER BY t2.payment_date DESC, t2.id DESC LIMIT 1
            )";
    }

    private function buildWhere(array $filters, array &$params): string
    {
        $where = ["p.didar_contact_id IS NOT NULL"];
        $map = [
            'owner_didar_id' => 'p.owner_didar_id = ?',
            'previous_owner_id' => '(p.previous_owner_id = ? OR FIND_IN_SET(?, p.previous_owner_history) > 0 OR EXISTS (SELECT 1 FROM lead_owner_history h WHERE h.contact_didar_id = p.didar_contact_id AND (h.from_owner_didar_id = ? OR h.to_owner_didar_id = ?)))',
            'sale_status' => 'p.sale_status = ?',
            'deal_status' => 'latest_deal.status = ?',
            'pool_key' => 'lp.pool_key = ?',
        ];
        foreach ($map as $key => $clause) {
            if (!isset($filters[$key]) || $filters[$key] === '') {
                continue;
            }
            $where[] = $clause;
            $repeat = substr_count($clause, '?');
            for ($i = 0; $i < $repeat; $i++) {
                $params[] = $filters[$key];
            }
        }
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ids = array_values(array_filter(array_map('strval', $filters['owner_didar_ids'])));
            if (!empty($ids)) {
                $placeholders = implode(',', array_fill(0, count($ids), '?'));
                $where[] = "p.owner_didar_id IN ($placeholders)";
                $params = array_merge($params, $ids);
            }
        }
        if (!empty($filters['no_owner'])) {
            $where[] = "(p.owner_didar_id IS NULL OR p.owner_didar_id = '')";
        }
        if (!empty($filters['search'])) {
            $where[] = "(p.customer_code LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ? OR p.mobile_phone LIKE ?)";
            $s = '%' . $filters['search'] . '%';
            array_push($params, $s, $s, $s, $s);
        }
        foreach ([
            'lead_date_from' => ['COALESCE(p.register_time, p.created_at) >= ?', ' 00:00:00'],
            'lead_date_to' => ['COALESCE(p.register_time, p.created_at) <= ?', ' 23:59:59'],
            'last_deal_from' => ['latest_deal.register_time >= ?', ' 00:00:00'],
            'last_deal_to' => ['latest_deal.register_time <= ?', ' 23:59:59'],
            'last_activity_from' => ['latest_activity.register_date >= ?', ' 00:00:00'],
            'last_activity_to' => ['latest_activity.register_date <= ?', ' 23:59:59'],
            'last_payment_from' => ['latest_payment.payment_date >= ?', ''],
            'last_payment_to' => ['latest_payment.payment_date <= ?', ''],
        ] as $key => $def) {
            if (!empty($filters[$key])) {
                $where[] = $def[0];
                $params[] = str_replace('/', '-', (string)$filters[$key]) . $def[1];
            }
        }
        if (!empty($filters['deal_older_than_days'])) {
            $where[] = "latest_deal.register_time <= DATE_SUB(NOW(), INTERVAL ? DAY)";
            $params[] = (int)$filters['deal_older_than_days'];
        }
        if (array_key_exists('has_payment', $filters) && $filters['has_payment'] !== '') {
            $where[] = !empty($filters['has_payment']) ? "latest_payment.id IS NOT NULL" : "latest_payment.id IS NULL";
        }
        if (!empty($filters['_selected_ids']) && is_array($filters['_selected_ids'])) {
            $ids = array_values(array_filter(array_map('intval', $filters['_selected_ids'])));
            if (!empty($ids)) {
                $where[] = "p.id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";
                $params = array_merge($params, $ids);
            }
        }
        return implode(' AND ', $where);
    }

    private function conditionToFilters(array $condition): array
    {
        $filters = [];
        foreach (($condition['all'] ?? []) as $rule) {
            $field = $rule['field'] ?? '';
            if ($field === 'last_deal_at' && ($rule['operator'] ?? '') === 'older_than') {
                $filters['deal_older_than_days'] = (int)($rule['value'] ?? 30);
            } elseif ($field === 'lead_registered_at' && ($rule['operator'] ?? '') === 'older_than') {
                $filters['lead_date_to'] = date('Y-m-d', strtotime('-' . max(1, (int)($rule['value'] ?? 30)) . ' days'));
            } elseif ($field === 'last_activity_at' && ($rule['operator'] ?? '') === 'older_than') {
                $filters['last_activity_to'] = date('Y-m-d', strtotime('-' . max(1, (int)($rule['value'] ?? 30)) . ' days'));
            } elseif ($field === 'last_payment_at' && ($rule['operator'] ?? '') === 'older_than') {
                $filters['last_payment_to'] = date('Y-m-d', strtotime('-' . max(1, (int)($rule['value'] ?? 30)) . ' days'));
            } elseif ($field === 'has_any_payment' && ($rule['operator'] ?? '') === 'equals') {
                $filters['has_payment'] = !empty($rule['value']) ? 1 : 0;
            } elseif ($field === 'last_deal_status') {
                $operator = (string)($rule['operator'] ?? '');
                $value = (string)($rule['value'] ?? '');
                if ($operator === 'equals' && in_array($value, ['Pending', 'Won', 'Lost'], true)) {
                    $filters['deal_status'] = $value;
                } elseif ($operator === 'not_equals' && $value === 'Won') {
                    $filters['deal_status'] = 'Pending';
                }
            } elseif ($field === 'sale_status' && ($rule['operator'] ?? '') === 'equals') {
                $filters['sale_status'] = !empty($rule['value']) ? 1 : 0;
            } elseif ($field === 'owner_empty' && ($rule['operator'] ?? '') === 'equals' && !empty($rule['value'])) {
                $filters['no_owner'] = 1;
            } elseif ($field === 'pool_key' && ($rule['operator'] ?? '') === 'equals') {
                $filters['pool_key'] = (string)($rule['value'] ?? '');
            }
        }
        return $filters;
    }

    private function decode(string $json): array
    {
        $data = json_decode($json ?: '{}', true);
        return is_array($data) ? $data : [];
    }
}
