Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 329
0.00%
0 / 14
 CRAP
0.00%
0 / 1
FundsRepository
0.00%
0 / 329
0.00%
0 / 14
 6320
0.00%
0 / 1
 __construct
0.00%
0 / 1
0.00%
0 / 1
 2
 findFunds
0.00%
0 / 48
0.00%
0 / 1
 380
 createFund
0.00%
0 / 38
0.00%
0 / 1
 20
 findPublishedFunds
0.00%
0 / 40
0.00%
0 / 1
 90
 findPublishedFundById
0.00%
0 / 10
0.00%
0 / 1
 12
 findFundById
0.00%
0 / 10
0.00%
0 / 1
 12
 findUserFundAllocations
0.00%
0 / 28
0.00%
0 / 1
 90
 findFundPerformance
0.00%
0 / 8
0.00%
0 / 1
 6
 findUserDistributions
0.00%
0 / 12
0.00%
0 / 1
 20
 updateFund
0.00%
0 / 39
0.00%
0 / 1
 20
 archiveFund
0.00%
0 / 10
0.00%
0 / 1
 12
 findAllUserAllocations
0.00%
0 / 19
0.00%
0 / 1
 12
 createOrUpdateAllocation
0.00%
0 / 33
0.00%
0 / 1
 30
 decreaseAllocation
0.00%
0 / 33
0.00%
0 / 1
 110
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Funds\Repository;
6
7use App\Domain\Funds\Data\FundData;
8use PDO;
9use PDOException;
10use DomainException;
11use RuntimeException;
12
13final class FundsRepository
14{
15    public function __construct(
16        private readonly PDO $pdo,
17    ) {}
18
19    /**
20     * @return array{data: list<FundData>, total: int}
21     */
22    public function findFunds(
23        int $page,
24        int $limit,
25        ?string $search = null,
26        ?string $type = null,
27        ?string $status = null,
28        ?bool $published = null,
29    ): array {
30        $offset = ($page - 1) * $limit;
31
32        $conditions = [];
33        $params = [];
34
35        if ($search !== null && $search !== '') {
36            $conditions[] = '(f.name ILIKE :search OR f.manager_name ILIKE :search OR f.ir_email ILIKE :search)';
37            $params['search'] = '%' . $search . '%';
38        }
39
40        if ($type !== null && $type !== '') {
41            $conditions[] = 'f.type = :type';
42            $params['type'] = $type;
43        }
44
45        if ($status !== null && $status !== '') {
46            $conditions[] = 'f.status = :status';
47            $params['status'] = $status;
48        }
49
50        if ($published !== null) {
51            $conditions[] = 'f.published = :published';
52            $params['published'] = $published ? 'true' : 'false';
53        }
54
55        $whereClause = count($conditions) > 0 ? 'WHERE ' . implode(' AND ', $conditions) : '';
56
57        $countSql = "SELECT COUNT(*) FROM funds f {$whereClause}";
58        $countStmt = $this->pdo->prepare($countSql);
59
60        if ($countStmt === false) {
61            throw new RuntimeException('Failed to prepare funds count statement');
62        }
63
64        if (!empty($params)) {
65            foreach ($params as $key => $value) {
66                if ($key === 'published') {
67                    $countStmt->bindValue($key, $value === 'true' ? true : false, PDO::PARAM_BOOL);
68                } else {
69                    $countStmt->bindValue($key, $value);
70                }
71            }
72        }
73
74        $countStmt->execute();
75        $total = (int)$countStmt->fetchColumn();
76
77        $sql = "
78            SELECT
79                f.id AS \"id\",
80                f.name AS \"name\",
81                f.type AS \"type\",
82                f.status AS \"status\",
83                f.description_short AS \"descriptionShort\",
84                f.description_full AS \"descriptionFull\",
85                f.hero_image_url AS \"heroImageUrl\",
86                f.aum::TEXT AS \"aum\",
87                f.target_return AS \"targetReturn\",
88                f.min_investment::TEXT AS \"minInvestment\",
89                f.manager_name AS \"managerName\",
90                f.ir_email AS \"irEmail\",
91                f.published AS \"published\",
92                f.vintage_year AS \"vintageYear\",
93                f.fund_life AS \"fundLife\",
94                f.geographic_focus AS \"geographicFocus\",
95                f.target_irr AS \"targetIrr\",
96                f.management_fee_pct AS \"managementFeePct\",
97                f.carried_interest_pct AS \"carriedInterestPct\",
98                f.investment_highlights AS \"investmentHighlights\",
99                f.risk_factors AS \"riskFactors\",
100                f.manager_bio AS \"managerBio\",
101                f.manager_photo_url AS \"managerPhotoUrl\",
102                f.ir_contact_name AS \"irContactName\",
103                f.ir_contact_phone AS \"irContactPhone\",
104                f.next_distribution_date AS \"nextDistributionDate\",
105                f.liquidity AS \"liquidity\",
106                f.hero_image_alt AS \"heroImageAlt\",
107                f.investor_count AS \"investorCount\",
108                to_char(f.last_updated, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS \"lastUpdated\"
109            FROM view_admin_fund_list f
110            {$whereClause}
111            ORDER BY f.published DESC, f.status ASC, f.name ASC
112            LIMIT :limit
113            OFFSET :offset
114        ";
115
116        $stmt = $this->pdo->prepare($sql);
117
118        if ($stmt === false) {
119            throw new RuntimeException('Failed to prepare funds list statement');
120        }
121
122        foreach ($params as $key => $value) {
123            if ($key === 'published') {
124                $stmt->bindValue($key, $value === 'true' ? true : false, PDO::PARAM_BOOL);
125            } else {
126                $stmt->bindValue($key, $value);
127            }
128        }
129
130        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
131        $stmt->bindValue('offset', $offset, PDO::PARAM_INT);
132        $stmt->execute();
133
134        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
135
136        return [
137            'data' => array_map(
138                static fn(array $row): FundData => FundData::fromRow($row),
139                $rows,
140            ),
141            'total' => $total,
142        ];
143    }
144
145    public function createFund(
146        string $name,
147        string $type,
148        string $status,
149        ?string $descriptionShort,
150        ?string $descriptionFull,
151        ?string $heroImageUrl,
152        ?string $aum,
153        ?string $targetReturn,
154        ?string $minInvestment,
155        ?string $managerName,
156        ?string $irEmail,
157        bool $published,
158        ?int $vintageYear,
159        ?string $fundLife,
160        ?string $geographicFocus,
161        ?string $targetIrr,
162        ?string $managementFeePct,
163        ?string $carriedInterestPct,
164        ?string $investmentHighlightsJson,
165        ?string $riskFactors,
166        ?string $managerBio,
167        ?string $managerPhotoUrl,
168        ?string $irContactName,
169        ?string $irContactPhone,
170        ?string $nextDistributionDate,
171        ?string $liquidity,
172        ?string $heroImageAlt,
173    ): FundData {
174        $sql = "
175            INSERT INTO funds (
176                id,
177                name,
178                type,
179                status,
180                description_short,
181                description_full,
182                hero_image_url,
183                aum,
184                target_return,
185                min_investment,
186                manager_name,
187                ir_email,
188                published,
189                vintage_year,
190                fund_life,
191                geographic_focus,
192                target_irr,
193                management_fee_pct,
194                carried_interest_pct,
195                investment_highlights,
196                risk_factors,
197                manager_bio,
198                manager_photo_url,
199                ir_contact_name,
200                ir_contact_phone,
201                next_distribution_date,
202                liquidity,
203                hero_image_alt
204            ) VALUES (
205                gen_random_uuid()::uuid,
206                :name,
207                :type,
208                :status,
209                :description_short,
210                :description_full,
211                :hero_image_url,
212                :aum,
213                :target_return,
214                :min_investment,
215                :manager_name,
216                :ir_email,
217                :published,
218                :vintage_year,
219                :fund_life,
220                :geographic_focus,
221                :target_irr,
222                :management_fee_pct,
223                :carried_interest_pct,
224                :investment_highlights,
225                :risk_factors,
226                :manager_bio,
227                :manager_photo_url,
228                :ir_contact_name,
229                :ir_contact_phone,
230                :next_distribution_date,
231                :liquidity,
232                :hero_image_alt
233            )
234            RETURNING
235                id::TEXT AS \"id\",
236                name AS \"name\",
237                type AS \"type\",
238                status AS \"status\",
239                description_short AS \"descriptionShort\",
240                description_full AS \"descriptionFull\",
241                hero_image_url AS \"heroImageUrl\",
242                aum::TEXT AS \"aum\",
243                target_return AS \"targetReturn\",
244                min_investment::TEXT AS \"minInvestment\",
245                manager_name AS \"managerName\",
246                ir_email AS \"irEmail\",
247                published AS \"published\",
248                vintage_year AS \"vintageYear\",
249                fund_life AS \"fundLife\",
250                geographic_focus AS \"geographicFocus\",
251                target_irr AS \"targetIrr\",
252                management_fee_pct::TEXT AS \"managementFeePct\",
253                carried_interest_pct::TEXT AS \"carriedInterestPct\",
254                investment_highlights::TEXT AS \"investmentHighlights\",
255                risk_factors AS \"riskFactors\",
256                manager_bio AS \"managerBio\",
257                manager_photo_url AS \"managerPhotoUrl\",
258                ir_contact_name AS \"irContactName\",
259                ir_contact_phone AS \"irContactPhone\",
260                next_distribution_date::TEXT AS \"nextDistributionDate\",
261                liquidity AS \"liquidity\",
262                hero_image_alt AS \"heroImageAlt\"
263        ";
264
265        $stmt = $this->pdo->prepare($sql);
266
267        if ($stmt === false) {
268            throw new RuntimeException('Failed to prepare create fund statement');
269        }
270
271        $stmt->execute([
272            'name' => $name,
273            'type' => $type,
274            'status' => $status,
275            'description_short' => $descriptionShort,
276            'description_full' => $descriptionFull,
277            'hero_image_url' => $heroImageUrl,
278            'aum' => $aum,
279            'target_return' => $targetReturn,
280            'min_investment' => $minInvestment,
281            'manager_name' => $managerName,
282            'ir_email' => $irEmail,
283            'published' => $published ? 'true' : 'false',
284            'vintage_year' => $vintageYear,
285            'fund_life' => $fundLife,
286            'geographic_focus' => $geographicFocus,
287            'target_irr' => $targetIrr,
288            'management_fee_pct' => $managementFeePct,
289            'carried_interest_pct' => $carriedInterestPct,
290            'investment_highlights' => $investmentHighlightsJson,
291            'risk_factors' => $riskFactors,
292            'manager_bio' => $managerBio,
293            'manager_photo_url' => $managerPhotoUrl,
294            'ir_contact_name' => $irContactName,
295            'ir_contact_phone' => $irContactPhone,
296            'next_distribution_date' => $nextDistributionDate,
297            'liquidity' => $liquidity,
298            'hero_image_alt' => $heroImageAlt,
299        ]);
300
301        $row = $stmt->fetch(PDO::FETCH_ASSOC);
302        if ($row === false) {
303            throw new RuntimeException('Failed to retrieve created fund');
304        }
305
306        return FundData::fromRow($row);
307    }
308
309    /**
310     * @return array{data: list<FundData>, total: int}
311     */
312    public function findPublishedFunds(
313        int $page,
314        int $limit,
315        ?string $search = null,
316        ?string $type = null,
317    ): array {
318        $offset = ($page - 1) * $limit;
319
320        $conditions = [
321            'f.published = true',
322            "f.status <> 'Closed'",
323        ];
324        $params = [];
325
326        if ($search !== null && $search !== '') {
327            $conditions[] = '(f.name ILIKE :search OR f.manager_name ILIKE :search)';
328            $params['search'] = '%' . $search . '%';
329        }
330
331        if ($type !== null && $type !== '') {
332            $conditions[] = 'f.type = :type';
333            $params['type'] = $type;
334        }
335
336        $whereClause = 'WHERE ' . implode(' AND ', $conditions);
337
338        $countSql = "SELECT COUNT(*) FROM funds f {$whereClause}";
339        $countStmt = $this->pdo->prepare($countSql);
340        if ($countStmt === false) {
341            throw new RuntimeException('Failed to prepare investor funds count statement');
342        }
343
344        foreach ($params as $key => $value) {
345            $countStmt->bindValue($key, $value);
346        }
347        $countStmt->execute();
348        $total = (int)$countStmt->fetchColumn();
349
350        $sql = "
351            SELECT
352                f.id::TEXT AS \"id\",
353                f.name AS \"name\",
354                f.type AS \"type\",
355                f.status AS \"status\",
356                f.description_short AS \"descriptionShort\",
357                f.description_full AS \"descriptionFull\",
358                f.hero_image_url AS \"heroImageUrl\",
359                f.aum::TEXT AS \"aum\",
360                f.target_return AS \"targetReturn\",
361                f.min_investment::TEXT AS \"minInvestment\",
362                f.manager_name AS \"managerName\",
363                f.ir_email AS \"irEmail\",
364                f.published AS \"published\",
365                f.vintage_year AS \"vintageYear\",
366                f.fund_life AS \"fundLife\",
367                f.geographic_focus AS \"geographicFocus\",
368                f.target_irr AS \"targetIrr\",
369                f.management_fee_pct::TEXT AS \"managementFeePct\",
370                f.carried_interest_pct::TEXT AS \"carriedInterestPct\",
371                f.investment_highlights::TEXT AS \"investmentHighlights\",
372                f.risk_factors AS \"riskFactors\",
373                f.manager_bio AS \"managerBio\",
374                f.manager_photo_url AS \"managerPhotoUrl\",
375                f.ir_contact_name AS \"irContactName\",
376                f.ir_contact_phone AS \"irContactPhone\",
377                f.next_distribution_date::TEXT AS \"nextDistributionDate\",
378                f.liquidity AS \"liquidity\",
379                f.hero_image_alt AS \"heroImageAlt\",
380                COALESCE(ai.investor_count, 0) AS \"investorCount\",
381                TO_CHAR(f.updated_at, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS \"lastUpdated\",
382                COALESCE(perf.performance, '[]') AS \"performance\"
383            FROM funds f
384            LEFT JOIN (
385                SELECT fund_id, COUNT(DISTINCT user_id) AS investor_count
386                FROM allocations
387                GROUP BY fund_id
388            ) ai ON ai.fund_id = f.id
389            LEFT JOIN (
390                SELECT
391                    fund_id,
392                    json_agg(json_build_object(
393                        'date', TO_CHAR(MAKE_DATE(year, month, 1), 'YYYY-MM-DD'),
394                        'returnPct', return_pct::float8,
395                        'benchmarkPct', benchmark_pct::float8,
396                        'commentary', commentary
397                    ) ORDER BY year ASC, month ASC) AS performance
398                FROM (
399                    SELECT
400                        fund_id,
401                        year,
402                        month,
403                        return_pct,
404                        benchmark_pct,
405                        commentary,
406                        ROW_NUMBER() OVER (PARTITION BY fund_id ORDER BY year DESC, month DESC) AS rn
407                    FROM fund_performance
408                ) fp
409                WHERE fp.rn <= 12
410                GROUP BY fund_id
411            ) perf ON perf.fund_id = f.id
412            {$whereClause}
413            ORDER BY f.name ASC
414            LIMIT :limit
415            OFFSET :offset
416        ";
417
418        $stmt = $this->pdo->prepare($sql);
419        if ($stmt === false) {
420            throw new RuntimeException('Failed to prepare investor funds list statement');
421        }
422
423        foreach ($params as $key => $value) {
424            $stmt->bindValue($key, $value);
425        }
426        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
427        $stmt->bindValue('offset', $offset, PDO::PARAM_INT);
428        $stmt->execute();
429
430        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
431
432        return [
433            'data' => array_map(
434                static fn(array $row): FundData => FundData::fromRow($row),
435                $rows,
436            ),
437            'total' => $total,
438        ];
439    }
440
441    public function findPublishedFundById(string $id): ?FundData
442    {
443        $sql = "
444            SELECT
445                f.id::TEXT AS \"id\",
446                f.name AS \"name\",
447                f.type AS \"type\",
448                f.status AS \"status\",
449                f.description_short AS \"descriptionShort\",
450                f.description_full AS \"descriptionFull\",
451                f.hero_image_url AS \"heroImageUrl\",
452                f.aum::TEXT AS \"aum\",
453                f.target_return AS \"targetReturn\",
454                f.min_investment::TEXT AS \"minInvestment\",
455                f.manager_name AS \"managerName\",
456                f.ir_email AS \"irEmail\",
457                f.published AS \"published\",
458                f.vintage_year AS \"vintageYear\",
459                f.fund_life AS \"fundLife\",
460                f.geographic_focus AS \"geographicFocus\",
461                f.target_irr AS \"targetIrr\",
462                f.management_fee_pct::TEXT AS \"managementFeePct\",
463                f.carried_interest_pct::TEXT AS \"carriedInterestPct\",
464                f.investment_highlights::TEXT AS \"investmentHighlights\",
465                f.risk_factors AS \"riskFactors\",
466                f.manager_bio AS \"managerBio\",
467                f.manager_photo_url AS \"managerPhotoUrl\",
468                f.ir_contact_name AS \"irContactName\",
469                f.ir_contact_phone AS \"irContactPhone\",
470                f.next_distribution_date::TEXT AS \"nextDistributionDate\",
471                f.liquidity AS \"liquidity\",
472                f.hero_image_alt AS \"heroImageAlt\",
473                COALESCE(ai.investor_count, 0) AS \"investorCount\",
474                TO_CHAR(f.updated_at, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS \"lastUpdated\"
475            FROM funds f
476            LEFT JOIN (
477                SELECT fund_id, COUNT(DISTINCT user_id) AS investor_count
478                FROM allocations
479                GROUP BY fund_id
480            ) ai ON ai.fund_id = f.id
481            WHERE f.id = :id
482              AND f.published = true
483              AND f.status <> 'Closed'
484            LIMIT 1
485        ";
486
487        $stmt = $this->pdo->prepare($sql);
488        if ($stmt === false) {
489            throw new RuntimeException('Failed to prepare investor fund detail statement');
490        }
491        $stmt->execute(['id' => $id]);
492
493        $row = $stmt->fetch(PDO::FETCH_ASSOC);
494        if ($row === false) {
495            return null;
496        }
497
498        return FundData::fromRow($row);
499    }
500
501    public function findFundById(string $id): ?FundData
502    {
503        $sql = "
504            SELECT
505                f.id::TEXT AS \"id\",
506                f.name AS \"name\",
507                f.type AS \"type\",
508                f.status AS \"status\",
509                f.description_short AS \"descriptionShort\",
510                f.description_full AS \"descriptionFull\",
511                f.hero_image_url AS \"heroImageUrl\",
512                f.aum::TEXT AS \"aum\",
513                f.target_return AS \"targetReturn\",
514                f.min_investment::TEXT AS \"minInvestment\",
515                f.manager_name AS \"managerName\",
516                f.ir_email AS \"irEmail\",
517                f.published AS \"published\",
518                f.vintage_year AS \"vintageYear\",
519                f.fund_life AS \"fundLife\",
520                f.geographic_focus AS \"geographicFocus\",
521                f.target_irr AS \"targetIrr\",
522                f.management_fee_pct::TEXT AS \"managementFeePct\",
523                f.carried_interest_pct::TEXT AS \"carriedInterestPct\",
524                f.investment_highlights::TEXT AS \"investmentHighlights\",
525                f.risk_factors AS \"riskFactors\",
526                f.manager_bio AS \"managerBio\",
527                f.manager_photo_url AS \"managerPhotoUrl\",
528                f.ir_contact_name AS \"irContactName\",
529                f.ir_contact_phone AS \"irContactPhone\",
530                f.next_distribution_date::TEXT AS \"nextDistributionDate\",
531                f.liquidity AS \"liquidity\",
532                f.hero_image_alt AS \"heroImageAlt\",
533                COALESCE(ai.investor_count, 0) AS \"investorCount\",
534                TO_CHAR(f.updated_at, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS \"lastUpdated\"
535            FROM funds f
536            LEFT JOIN (
537                SELECT fund_id, COUNT(DISTINCT user_id) AS investor_count
538                FROM allocations
539                GROUP BY fund_id
540            ) ai ON ai.fund_id = f.id
541            WHERE f.id = :id
542            LIMIT 1
543        ";
544
545        $stmt = $this->pdo->prepare($sql);
546        if ($stmt === false) {
547            throw new RuntimeException('Failed to prepare fund lookup statement');
548        }
549
550        $stmt->execute(['id' => $id]);
551        $row = $stmt->fetch(PDO::FETCH_ASSOC);
552
553        if ($row === false) {
554            return null;
555        }
556
557        return FundData::fromRow($row);
558    }
559
560    /**
561     * @param list<string> $fundIds
562     * @return array<string, array{investedAmount: string|null, currentValue: string|null}>
563     */
564    public function findUserFundAllocations(array $fundIds, string $userIdText): array
565    {
566        if ($fundIds === []) {
567            return [];
568        }
569
570        $placeholders = [];
571        $params = ['userIdText' => $userIdText];
572        foreach ($fundIds as $index => $fundId) {
573            $key = 'fundId' . $index;
574            $placeholders[] = ':' . $key;
575            $params[$key] = $fundId;
576        }
577
578        $sql = "
579            SELECT
580                a.fund_id::TEXT AS \"fundId\",
581                a.invested_amount::TEXT AS \"investedAmount\",
582                a.current_value::TEXT AS \"currentValue\"
583            FROM allocations a
584            WHERE a.user_id::TEXT = :userIdText
585              AND a.fund_id IN (" . implode(', ', $placeholders) . ")
586        ";
587
588        $stmt = $this->pdo->prepare($sql);
589        if ($stmt === false) {
590            throw new RuntimeException('Failed to prepare user fund allocations statement');
591        }
592
593        foreach ($params as $key => $value) {
594            $stmt->bindValue($key, $value);
595        }
596        $stmt->execute();
597
598        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
599        $result = [];
600        foreach ($rows as $row) {
601            $fundId = (string)($row['fundId'] ?? '');
602            if ($fundId === '') {
603                continue;
604            }
605
606            $result[$fundId] = [
607                'investedAmount' => isset($row['investedAmount']) ? (string)$row['investedAmount'] : null,
608                'currentValue' => isset($row['currentValue']) ? (string)$row['currentValue'] : null,
609            ];
610        }
611
612        return $result;
613    }
614
615    /**
616     * @return list<array{date: string, returnPct: float|null, benchmarkPct: float|null, commentary: ?string}>
617     */
618    public function findFundPerformance(string $fundId): array
619    {
620        $sql = "
621            SELECT
622                TO_CHAR(MAKE_DATE(year, month, 1), 'YYYY-MM-DD') AS \"date\",
623                return_pct::float8 AS \"returnPct\",
624                benchmark_pct::float8 AS \"benchmarkPct\",
625                commentary AS \"commentary\"
626            FROM fund_performance
627            WHERE fund_id = :fundId
628            ORDER BY year ASC, month ASC
629        ";
630
631        $stmt = $this->pdo->prepare($sql);
632        if ($stmt === false) {
633            throw new RuntimeException('Failed to prepare fund performance statement');
634        }
635        $stmt->execute(['fundId' => $fundId]);
636
637        /** @var list<array{date: string, returnPct: float|null, benchmarkPct: float|null, commentary: ?string}> $rows */
638        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
639
640        return $rows;
641    }
642
643    /**
644     * @return list<array{paymentDate: string, amount: string, type: string, description: ?string}>
645     */
646    public function findUserDistributions(string $fundId, string $userId): array
647    {
648        $sql = "
649            SELECT
650                TO_CHAR(payment_date, 'YYYY-MM-DD') AS \"paymentDate\",
651                amount::text AS \"amount\",
652                type,
653                description
654            FROM distributions
655            WHERE fund_id = :fundId AND user_id = :userId
656            ORDER BY payment_date DESC
657        ";
658
659        try {
660            $stmt = $this->pdo->prepare($sql);
661            if ($stmt === false) {
662                throw new RuntimeException('Failed to prepare user distributions statement');
663            }
664            $stmt->execute(['fundId' => $fundId, 'userId' => $userId]);
665        } catch (PDOException $e) {
666            if ($e->getCode() === '42P01') {
667                return [];
668            }
669            throw $e;
670        }
671
672        /** @var list<array{paymentDate: string, amount: string, type: string, description: ?string}> $rows */
673        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
674
675        return $rows;
676    }
677
678    public function updateFund(
679        string $id,
680        string $name,
681        string $type,
682        string $status,
683        ?string $descriptionShort,
684        ?string $descriptionFull,
685        ?string $heroImageUrl,
686        ?string $aum,
687        ?string $targetReturn,
688        ?string $minInvestment,
689        ?string $managerName,
690        ?string $irEmail,
691        bool $published,
692        ?int $vintageYear,
693        ?string $fundLife,
694        ?string $geographicFocus,
695        ?string $targetIrr,
696        ?string $managementFeePct,
697        ?string $carriedInterestPct,
698        ?string $investmentHighlightsJson,
699        ?string $riskFactors,
700        ?string $managerBio,
701        ?string $managerPhotoUrl,
702        ?string $irContactName,
703        ?string $irContactPhone,
704        ?string $nextDistributionDate,
705        ?string $liquidity,
706        ?string $heroImageAlt,
707    ): FundData {
708        $sql = "
709            UPDATE funds SET
710                name = :name,
711                type = :type,
712                status = :status,
713                description_short = :description_short,
714                description_full = :description_full,
715                hero_image_url = :hero_image_url,
716                aum = :aum,
717                target_return = :target_return,
718                min_investment = :min_investment,
719                manager_name = :manager_name,
720                ir_email = :ir_email,
721                published = :published,
722                vintage_year = :vintage_year,
723                fund_life = :fund_life,
724                geographic_focus = :geographic_focus,
725                target_irr = :target_irr,
726                management_fee_pct = :management_fee_pct,
727                carried_interest_pct = :carried_interest_pct,
728                investment_highlights = :investment_highlights,
729                risk_factors = :risk_factors,
730                manager_bio = :manager_bio,
731                manager_photo_url = :manager_photo_url,
732                ir_contact_name = :ir_contact_name,
733                ir_contact_phone = :ir_contact_phone,
734                next_distribution_date = :next_distribution_date,
735                liquidity = :liquidity,
736                hero_image_alt = :hero_image_alt,
737                updated_at = CURRENT_TIMESTAMP
738            WHERE id = :id
739            RETURNING
740                id::TEXT AS \"id\",
741                name AS \"name\",
742                type AS \"type\",
743                status AS \"status\",
744                description_short AS \"descriptionShort\",
745                description_full AS \"descriptionFull\",
746                hero_image_url AS \"heroImageUrl\",
747                aum::TEXT AS \"aum\",
748                target_return AS \"targetReturn\",
749                min_investment::TEXT AS \"minInvestment\",
750                manager_name AS \"managerName\",
751                ir_email AS \"irEmail\",
752                published AS \"published\",
753                vintage_year AS \"vintageYear\",
754                fund_life AS \"fundLife\",
755                geographic_focus AS \"geographicFocus\",
756                target_irr AS \"targetIrr\",
757                management_fee_pct AS \"managementFeePct\",
758                carried_interest_pct AS \"carriedInterestPct\",
759                investment_highlights AS \"investmentHighlights\",
760                risk_factors AS \"riskFactors\",
761                manager_bio AS \"managerBio\",
762                manager_photo_url AS \"managerPhotoUrl\",
763                ir_contact_name AS \"irContactName\",
764                ir_contact_phone AS \"irContactPhone\",
765                next_distribution_date AS \"nextDistributionDate\",
766                liquidity AS \"liquidity\",
767                hero_image_alt AS \"heroImageAlt\",
768                updated_at AS \"updatedAt\"
769        ";
770
771        $stmt = $this->pdo->prepare($sql);
772        if ($stmt === false) {
773            throw new RuntimeException('Failed to prepare update fund statement');
774        }
775
776        $stmt->execute([
777            'id' => $id,
778            'name' => $name,
779            'type' => $type,
780            'status' => $status,
781            'description_short' => $descriptionShort,
782            'description_full' => $descriptionFull,
783            'hero_image_url' => $heroImageUrl,
784            'aum' => $aum,
785            'target_return' => $targetReturn,
786            'min_investment' => $minInvestment,
787            'manager_name' => $managerName,
788            'ir_email' => $irEmail,
789            'published' => $published ? 'true' : 'false',
790            'vintage_year' => $vintageYear,
791            'fund_life' => $fundLife,
792            'geographic_focus' => $geographicFocus,
793            'target_irr' => $targetIrr,
794            'management_fee_pct' => $managementFeePct,
795            'carried_interest_pct' => $carriedInterestPct,
796            'investment_highlights' => $investmentHighlightsJson,
797            'risk_factors' => $riskFactors,
798            'manager_bio' => $managerBio,
799            'manager_photo_url' => $managerPhotoUrl,
800            'ir_contact_name' => $irContactName,
801            'ir_contact_phone' => $irContactPhone,
802            'next_distribution_date' => $nextDistributionDate,
803            'liquidity' => $liquidity,
804            'hero_image_alt' => $heroImageAlt,
805        ]);
806
807        $row = $stmt->fetch(PDO::FETCH_ASSOC);
808        if ($row === false) {
809            throw new RuntimeException('Failed to retrieve updated fund');
810        }
811
812        return FundData::fromRow($row);
813    }
814
815    public function archiveFund(string $id): FundData
816    {
817        $sql = "
818            UPDATE funds SET
819                status = 'Closed',
820                published = false,
821                updated_at = CURRENT_TIMESTAMP
822            WHERE id = :id
823            RETURNING
824                id::TEXT AS \"id\",
825                name AS \"name\",
826                type AS \"type\",
827                status AS \"status\",
828                description_short AS \"descriptionShort\",
829                description_full AS \"descriptionFull\",
830                hero_image_url AS \"heroImageUrl\",
831                aum::TEXT AS \"aum\",
832                target_return AS \"targetReturn\",
833                min_investment::TEXT AS \"minInvestment\",
834                manager_name AS \"managerName\",
835                ir_email AS \"irEmail\",
836                published AS \"published\",
837                updated_at AS \"updatedAt\"
838        ";
839
840        $stmt = $this->pdo->prepare($sql);
841        if ($stmt === false) {
842            throw new RuntimeException('Failed to prepare archive fund statement');
843        }
844
845        $stmt->execute(['id' => $id]);
846        $row = $stmt->fetch(PDO::FETCH_ASSOC);
847        if ($row === false) {
848            throw new RuntimeException('Failed to archive fund');
849        }
850
851        return FundData::fromRow($row);
852    }
853
854    /**
855     * @return list<array{fundId: string, investedAmount: string, currentValue: string, units: string|null, createdAt: string}>
856     */
857    public function findAllUserAllocations(int $userId): array
858    {
859        $sql = "
860            SELECT
861                a.fund_id::TEXT AS \"fundId\",
862                a.invested_amount::TEXT AS \"investedAmount\",
863                a.current_value::TEXT AS \"currentValue\",
864                a.units::TEXT AS \"units\",
865                a.created_at::TEXT AS \"createdAt\",
866                f.name AS \"fundName\"
867            FROM allocations a
868            JOIN funds f ON f.id::TEXT = a.fund_id::TEXT
869            WHERE a.user_id = :userId
870            ORDER BY a.created_at DESC
871        ";
872
873        $stmt = $this->pdo->prepare($sql);
874        if ($stmt === false) {
875            throw new RuntimeException('Failed to prepare user allocations statement');
876        }
877
878        $stmt->execute(['userId' => $userId]);
879        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
880
881        return array_map(
882            static fn(array $row): array => [
883                'id' => $row['fundId'] . '-' . $userId, // Generate composite ID
884                'fundId' => (string)($row['fundId'] ?? ''),
885                'fundName' => (string)($row['fundName'] ?? ''),
886                'investedAmount' => (string)($row['investedAmount'] ?? ''),
887                'currentValue' => (string)($row['currentValue'] ?? ''),
888                'units' => isset($row['units']) ? (string)$row['units'] : null,
889                'createdAt' => (string)($row['createdAt'] ?? ''),
890            ],
891            $rows,
892        );
893    }
894
895    /**
896     * Create or update an allocation for a user and fund
897     */
898    public function createOrUpdateAllocation(int $userId, string $fundId, string $amount): void
899    {
900        // First check if allocation exists
901        $sql = "
902            SELECT
903                a.invested_amount::TEXT AS \"investedAmount\",
904                a.current_value::TEXT AS \"currentValue\"
905            FROM allocations a
906            WHERE a.user_id = :userId
907              AND a.fund_id::TEXT = :fundId
908        ";
909
910        $stmt = $this->pdo->prepare($sql);
911        if ($stmt === false) {
912            throw new RuntimeException('Failed to prepare find allocation statement');
913        }
914
915        $stmt->execute(['userId' => $userId, 'fundId' => $fundId]);
916        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
917
918        if ($existing !== false) {
919            // Update existing allocation
920            $currentInvested = (float)($existing['investedAmount'] ?? '0');
921            $currentValue = (float)($existing['currentValue'] ?? '0');
922            $newInvested = $currentInvested + (float)$amount;
923            $newValue = $currentValue + (float)$amount;
924
925            $updateSql = "
926                UPDATE allocations
927                SET invested_amount = :investedAmount,
928                    current_value = :currentValue,
929                    updated_at = NOW()
930                WHERE user_id = :userId
931                  AND fund_id::TEXT = :fundId
932            ";
933
934            $updateStmt = $this->pdo->prepare($updateSql);
935            if ($updateStmt === false) {
936                throw new RuntimeException('Failed to prepare update allocation statement');
937            }
938
939            $updateStmt->execute([
940                'investedAmount' => $newInvested,
941                'currentValue' => $newValue,
942                'userId' => $userId,
943                'fundId' => $fundId,
944            ]);
945        } else {
946            // Create new allocation
947            $insertSql = "
948                INSERT INTO allocations (user_id, fund_id, invested_amount, current_value, units, created_at, updated_at)
949                VALUES (
950                    :userId,
951                    :fundId::UUID,
952                    :amount,
953                    :amount,
954                    NULL,
955                    NOW(),
956                    NOW()
957                )
958            ";
959
960            $insertStmt = $this->pdo->prepare($insertSql);
961            if ($insertStmt === false) {
962                throw new RuntimeException('Failed to prepare insert allocation statement');
963            }
964
965            $insertStmt->execute([
966                'userId' => $userId,
967                'fundId' => $fundId,
968                'amount' => $amount,
969            ]);
970        }
971    }
972
973    /**
974     * Decrease an existing allocation for a withdrawal approval.
975     */
976    public function decreaseAllocation(int $userId, string $fundId, string $amount): void
977    {
978        $sql = "
979            SELECT
980                a.invested_amount::TEXT AS \"investedAmount\",
981                a.current_value::TEXT AS \"currentValue\",
982                a.units::TEXT AS \"units\"
983            FROM allocations a
984            WHERE a.user_id = :userId
985              AND a.fund_id::TEXT = :fundId
986        ";
987
988        $stmt = $this->pdo->prepare($sql);
989        if ($stmt === false) {
990            throw new RuntimeException('Failed to prepare find allocation statement');
991        }
992
993        $stmt->execute(['userId' => $userId, 'fundId' => $fundId]);
994        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
995
996        if ($existing === false) {
997            throw new RuntimeException('Allocation not found for withdrawal approval');
998        }
999
1000        $currentInvested = (float)($existing['investedAmount'] ?? '0');
1001        $currentValue = (float)($existing['currentValue'] ?? '0');
1002        $currentUnits = isset($existing['units']) && $existing['units'] !== ''
1003            ? (float)$existing['units']
1004            : null;
1005        $amountFloat = (float)$amount;
1006
1007        if ($amountFloat > $currentValue) {
1008            throw new DomainException('Insufficient allocation balance for withdrawal');
1009        }
1010
1011        $newValue = $currentValue - $amountFloat;
1012        $ratio = $currentValue > 0.0 ? ($newValue / $currentValue) : 0.0;
1013        $newInvested = $currentInvested * $ratio;
1014        $newUnits = $currentUnits !== null ? $currentUnits * $ratio : null;
1015
1016        $updateSql = "
1017            UPDATE allocations
1018            SET invested_amount = :investedAmount,
1019                current_value = :currentValue,
1020                units = :units,
1021                updated_at = NOW()
1022            WHERE user_id = :userId
1023              AND fund_id::TEXT = :fundId
1024        ";
1025
1026        $updateStmt = $this->pdo->prepare($updateSql);
1027        if ($updateStmt === false) {
1028            throw new RuntimeException('Failed to prepare update allocation statement');
1029        }
1030
1031        $updateStmt->execute([
1032            'investedAmount' => number_format($newInvested, 2, '.', ''),
1033            'currentValue' => number_format($newValue, 2, '.', ''),
1034            'units' => $newUnits !== null ? number_format($newUnits, 10, '.', '') : null,
1035            'userId' => $userId,
1036            'fundId' => $fundId,
1037        ]);
1038    }
1039}
1040