Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
92.77%
154 / 166
81.82%
9 / 11
 CRAP
0.00%
0 / 1
EmailContextAssociationService
92.73%
153 / 165
81.82%
9 / 11
 49.92
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 detectAndAssociate
100.00%
31 / 31
100.00%
1 / 1
 5
 unlinkRelation
100.00%
11 / 11
100.00%
1 / 1
 3
 executeUnlink
100.00%
12 / 12
100.00%
1 / 1
 6
 findExistingEmailId
100.00%
16 / 16
100.00%
1 / 1
 4
 loadPersistedAssociations
83.33%
20 / 24
0.00%
0 / 1
 7.23
 persistAssociations
100.00%
47 / 47
100.00%
1 / 1
 14
 formatResult
100.00%
8 / 8
100.00%
1 / 1
 4
 buildEmptyResult
0.00%
0 / 8
0.00%
0 / 1
 2
 cleanEmail
100.00%
1 / 1
100.00%
1 / 1
 1
 cleanEmails
100.00%
6 / 6
100.00%
1 / 1
 3
1<?php
2
3declare(strict_types=1);
4
5/** @license For full copyright and license information, please see the LICENSE.md file. */
6
7namespace App\Modules\Mail\Application\Service;
8
9defined('AMMONLY_APP') || exit('Direct script access is forbidden.');
10
11use App\Modules\Mail\Application\Contract\EmailContextAssociationServiceInterface;
12use App\Modules\Mail\Application\Service\Association\EmailContextCrmResolver;
13use App\Modules\Mail\Domain\Contract\WebmailMessageServiceInterface;
14use App\Modules\Mail\Domain\Model\MailMessageDetailDto;
15use PDO;
16use Throwable;
17
18/**
19 * Enterprise Email Context Association Service.
20 *
21 * Automatically links email messages with CRM entities (Company, Contact, Process, Subprocess),
22 * persists associations in mod_emails_records, and supports interactive unlinking and re-detection.
23 *
24 * @package App\Modules\Mail\Application\Service
25 */
26final readonly class EmailContextAssociationService implements EmailContextAssociationServiceInterface
27{
28    private EmailContextCrmResolver $crmResolver;
29
30    /**
31     * EmailContextAssociationService constructor.
32     *
33     * @param PDO                            $pdo            Database connection.
34     * @param WebmailMessageServiceInterface $messageService Message retrieval service.
35     * @param string                         $tablePrefix    Database table prefix (e.g. 'a_').
36     * @param EmailContextCrmResolver|null   $crmResolver    Optional CRM entity resolver helper.
37     */
38    public function __construct(
39        private PDO $pdo,
40        private WebmailMessageServiceInterface $messageService,
41        private string $tablePrefix = 'a_',
42        ?EmailContextCrmResolver $crmResolver = null
43    ) {
44        $this->crmResolver = $crmResolver ?? new EmailContextCrmResolver($this->pdo, $this->tablePrefix);
45    }
46
47    /**
48     * {@inheritdoc}
49     */
50    public function detectAndAssociate(
51        int $mailboxId,
52        string $folder,
53        string $uid,
54        int $userId,
55        bool $forceRefresh = false
56    ): array {
57        try {
58            $detail = $this->messageService->getMessageDetail($mailboxId, $userId, $folder, $uid, false, false);
59        } catch (Throwable) {
60            return $this->buildEmptyResult();
61        }
62
63        $fromEmail = $this->cleanEmail($detail->summary->fromEmail);
64        $toEmails  = $this->cleanEmails($detail->summary->to);
65        $subject   = $detail->summary->subject;
66        $msgId     = $detail->summary->messageId;
67        $textBody  = $detail->textBody;
68
69        $emailRecordId = $this->findExistingEmailId($mailboxId, $msgId, $fromEmail, $subject);
70
71        if ($emailRecordId !== null && !$forceRefresh) {
72            $existing = $this->loadPersistedAssociations($emailRecordId);
73            if ($existing['has_associations']) {
74                return $existing;
75            }
76        }
77
78        $allAddresses = array_values(array_unique(array_filter([$fromEmail, ...$toEmails])));
79        $contact = $this->crmResolver->resolveContact($allAddresses);
80        $company = $this->crmResolver->resolveCompany($contact['id'] ?? null, $allAddresses);
81        $subprocess = $this->crmResolver->resolveSubprocess($subject, $textBody, $company['id'] ?? null);
82        $process = $this->crmResolver->resolveProcess($subprocess, $company['id'] ?? null);
83
84        $savedId = $this->persistAssociations(
85            $emailRecordId,
86            $mailboxId,
87            $detail,
88            [
89                'company_id' => $company['id'] ?? null,
90                'contact_id' => $contact['id'] ?? null,
91                'subprocess' => $subprocess,
92                'process'    => $process,
93            ],
94            $userId
95        );
96
97        return $this->formatResult($company, $contact, $process, $subprocess, $savedId);
98    }
99
100    /**
101     * {@inheritdoc}
102     */
103    public function unlinkRelation(
104        int $mailboxId,
105        string $folder,
106        string $uid,
107        string $relationType,
108        int $userId
109    ): array {
110        try {
111            $detail = $this->messageService->getMessageDetail($mailboxId, $userId, $folder, $uid, false, false);
112        } catch (Throwable) {
113            return $this->buildEmptyResult();
114        }
115
116        $fromEmail = $this->cleanEmail($detail->summary->fromEmail);
117        $subject   = $detail->summary->subject;
118        $msgId     = $detail->summary->messageId;
119
120        $emailRecordId = $this->findExistingEmailId($mailboxId, $msgId, $fromEmail, $subject);
121        if ($emailRecordId === null) {
122            return $this->buildEmptyResult();
123        }
124
125        $this->executeUnlink($emailRecordId, $relationType);
126
127        return $this->loadPersistedAssociations($emailRecordId);
128    }
129
130    /**
131     * Executes database unlink statement for specified relation type.
132     */
133    private function executeUnlink(int $emailId, string $relationType): void
134    {
135        $table = $this->tablePrefix . 'mod_emails_records';
136        $sql = match ($relationType) {
137            'company'    => "UPDATE `{$table}` SET `company_id` = NULL WHERE `id` = :id",
138            'contact'    => "UPDATE `{$table}` SET `contact_id` = NULL WHERE `id` = :id",
139            'process'    => "UPDATE `{$table}` SET `project_id` = NULL, `contract_id` = NULL WHERE `id` = :id",
140            'subprocess' => "UPDATE `{$table}` SET `ticket_id` = NULL, `task_id` = NULL WHERE `id` = :id",
141            default      => "UPDATE `{$table}` SET `company_id` = NULL, `contact_id` = NULL, "
142                . "`ticket_id` = NULL, `project_id` = NULL, `contract_id` = NULL, `task_id` = NULL "
143                . "WHERE `id` = :id",
144        };
145
146        $stmt = $this->pdo->prepare($sql);
147        $stmt->execute([':id' => $emailId]);
148    }
149
150    /**
151     * Finds primary key of existing record in mod_emails_records.
152     */
153    private function findExistingEmailId(
154        int $mailboxId,
155        ?string $msgId,
156        string $fromEmail,
157        string $subject
158    ): ?int {
159        $table = $this->tablePrefix . 'mod_emails_records';
160
161        if (!empty($msgId)) {
162            $stmt = $this->pdo->prepare(
163                "SELECT `id` FROM `{$table}` WHERE `message_id` = :mid LIMIT 1"
164            );
165            $stmt->execute([':mid' => $msgId]);
166            $id = $stmt->fetchColumn();
167            if ($id !== false) {
168                return (int) $id;
169            }
170        }
171
172        $stmt = $this->pdo->prepare(
173            "SELECT `id` FROM `{$table}` WHERE `mailbox_id` = :mbx AND `from_email` = :frm "
174            . "AND `subject` = :sub LIMIT 1"
175        );
176        $stmt->execute([':mbx' => $mailboxId, ':frm' => $fromEmail, ':sub' => $subject]);
177        $id = $stmt->fetchColumn();
178
179        return $id !== false ? (int) $id : null;
180    }
181
182    /**
183     * Loads persisted associations from mod_emails_records.
184     *
185     * @return array<string, mixed> Formatted associations structure.
186     */
187    private function loadPersistedAssociations(int $emailRecordId): array
188    {
189        $table = $this->tablePrefix . 'mod_emails_records';
190        $stmt = $this->pdo->prepare(
191            "SELECT `company_id`, `contact_id`, `ticket_id`, `project_id`, `contract_id`, `task_id` "
192            . "FROM `{$table}` WHERE `id` = :id LIMIT 1"
193        );
194        $stmt->execute([':id' => $emailRecordId]);
195        $row = $stmt->fetch(PDO::FETCH_ASSOC);
196
197        if ($row === false) {
198            return $this->buildEmptyResult();
199        }
200
201        $company = !empty($row['company_id'])
202            ? $this->crmResolver->fetchCompanyRecord((int) $row['company_id'])
203            : null;
204        $contact = !empty($row['contact_id'])
205            ? $this->crmResolver->fetchContactRecord((int) $row['contact_id'])
206            : null;
207        $subprocess = !empty($row['ticket_id'])
208            ? $this->crmResolver->fetchTicketRecord((int) $row['ticket_id'])
209            : null;
210        $process = null;
211        if (!empty($row['project_id'])) {
212            $process = $this->crmResolver->fetchProjectRecord((int) $row['project_id']);
213        } elseif (!empty($row['contract_id'])) {
214            $process = $this->crmResolver->fetchContractRecord((int) $row['contract_id']);
215        }
216
217        return $this->formatResult($company, $contact, $process, $subprocess, $emailRecordId);
218    }
219
220    /**
221     * Persists or updates associations in mod_emails_records.
222     *
223     * @param int|null $existingId Existing record ID.
224     * @param int $mailboxId Mailbox ID.
225     * @param MailMessageDetailDto $detail Message detail DTO.
226     * @param array<string, mixed> $crmContext CRM relations context map.
227     * @param int $userId Current user ID.
228     * @return int Persisted record ID.
229     */
230    private function persistAssociations(
231        ?int $existingId,
232        int $mailboxId,
233        MailMessageDetailDto $detail,
234        array $crmContext,
235        int $userId
236    ): int {
237        $table = $this->tablePrefix . 'mod_emails_records';
238        $subprocess = is_array($crmContext['subprocess'] ?? null) ? $crmContext['subprocess'] : [];
239        $process = is_array($crmContext['process'] ?? null) ? $crmContext['process'] : [];
240        $companyId = !empty($crmContext['company_id']) ? (int) $crmContext['company_id'] : null;
241        $contactId = !empty($crmContext['contact_id']) ? (int) $crmContext['contact_id'] : null;
242
243        $ticketId = (($subprocess['type'] ?? '') === 'ticket') ? ($subprocess['id'] ?? null) : null;
244        $projectId = (($process['type'] ?? '') === 'project') ? ($process['id'] ?? null) : null;
245        $contractId = (($process['type'] ?? '') === 'contract') ? ($process['id'] ?? null) : null;
246
247        if ($existingId !== null && $existingId > 0) {
248            $stmt = $this->pdo->prepare(
249                "UPDATE `{$table}` SET `company_id` = :cid, `contact_id` = :ctid, `ticket_id` = :tck, "
250                . "`project_id` = :pid, `contract_id` = :crid WHERE `id` = :id"
251            );
252            $stmt->execute([
253                ':cid'  => $companyId,
254                ':ctid' => $contactId,
255                ':tck'  => $ticketId,
256                ':pid'  => $projectId,
257                ':crid' => $contractId,
258                ':id'   => $existingId,
259            ]);
260            return $existingId;
261        }
262
263        $summary = $detail->summary;
264        $to = !empty($summary->to) ? (string) $summary->to[0] : '';
265        $stmt = $this->pdo->prepare(
266            "INSERT INTO `{$table}` (`subject`, `from_email`, `from_name`, `to_email`, `message_id`, "
267            . "`mailbox_id`, `company_id`, `contact_id`, `ticket_id`, `project_id`, `contract_id`, "
268            . "`email_status`, `direction`, `received_at`, `owner`, `created_by`, `special_access`) "
269            . "VALUES (:sub, :frm, :fnm, :to, :mid, :mbx, :cid, :ctid, :tck, :pid, :crid, "
270            . "'received', 'inbound', NOW(), :own, :crb, 0)"
271        );
272        $stmt->execute([
273            ':sub'  => $summary->subject !== '' ? $summary->subject : '(No Subject)',
274            ':frm'  => $this->cleanEmail($summary->fromEmail),
275            ':fnm'  => $summary->fromName,
276            ':to'   => $to,
277            ':mid'  => $summary->messageId,
278            ':mbx'  => $mailboxId,
279            ':cid'  => $companyId,
280            ':ctid' => $contactId,
281            ':tck'  => $ticketId,
282            ':pid'  => $projectId,
283            ':crid' => $contractId,
284            ':own'  => $userId > 0 ? $userId : 1,
285            ':crb'  => $userId > 0 ? $userId : 1,
286        ]);
287
288        return (int) $this->pdo->lastInsertId();
289    }
290
291    /**
292     * Formats normalized output array.
293     *
294     * @return array<string, mixed>
295     */
296    private function formatResult(
297        ?array $company,
298        ?array $contact,
299        ?array $process,
300        ?array $subprocess,
301        ?int $savedId
302    ): array {
303        return [
304            'company'          => $company,
305            'contact'          => $contact,
306            'process'          => $process,
307            'subprocess'       => $subprocess,
308            'email_record_id'  => $savedId,
309            'has_associations' => ($company !== null || $contact !== null || $process !== null || $subprocess !== null),
310        ];
311    }
312
313    /**
314     * Builds default empty associations result.
315     *
316     * @return array<string, mixed>
317     */
318    private function buildEmptyResult(): array
319    {
320        return [
321            'company'          => null,
322            'contact'          => null,
323            'process'          => null,
324            'subprocess'       => null,
325            'email_record_id'  => null,
326            'has_associations' => false,
327        ];
328    }
329
330    private function cleanEmail(string $email): string
331    {
332        return strtolower(trim(trim($email), '<>'));
333    }
334
335    /**
336     * @param array<mixed> $emails
337     * @return array<string>
338     */
339    private function cleanEmails(array $emails): array
340    {
341        $res = [];
342        foreach ($emails as $em) {
343            $c = $this->cleanEmail((string) $em);
344            if ($c !== '') {
345                $res[] = $c;
346            }
347        }
348        return $res;
349    }
350}