tinyqc/app/Models/Qc/TestCommentsModel.php

<?php
namespace App\Models\Qc;

use App\Models\BaseModel;

class TestCommentsModel extends BaseModel {
    protected $table = 'test_comments';
    protected $primaryKey = 'test_comment_id';
    protected $allowedFields = [
        'test_id',
        'comment_date',
        'comment_text',
        'created_at',
        'updated_at',
        'deleted_at'
    ];
    protected $useTimestamps = true;
    protected $useSoftDeletes = true;

    public function search($keyword = null) {
        if ($keyword) {
            return $this->groupStart()
                ->like('comment_text', $keyword)
                ->groupEnd()
                ->findAll();
        }
        return $this->findAll();
    }

    /**
     * Get comment by test_id and date
     */
    public function getByTestAndDate(int $testId, string $date): ?array {
        return $this->where('test_id', $testId)
            ->where('comment_date', $date)
            ->where('deleted_at', null)
            ->first();
    }

    /**
     * Get all comments for a test
     */
    public function getByTest(int $testId): array {
        return $this->where('test_id', $testId)
            ->where('deleted_at', null)
            ->orderBy('comment_date', 'DESC')
            ->findAll();
    }

    /**
     * Get comments for test(s) within a date range
     * @param int|array $testId Single test ID or array of test IDs
     */
    public function getByTestAndDateRange($testId, string $startDate, string $endDate): array {
        $builder = $this->where('comment_date >=', $startDate)
            ->where('comment_date <=', $endDate)
            ->where('deleted_at', null);
        
        if (is_array($testId)) {
            $builder->whereIn('test_id', $testId);
        } else {
            $builder->where('test_id', $testId);
        }
        
        return $builder->orderBy('comment_date', 'ASC')
            ->findAll();
    }

    /**
     * Get comments by month for a test
     */
    public function getByTestAndMonth(int $testId, string $month): array {
        return $this->where('test_id', $testId)
            ->where('comment_date >=', $month . '-01')
            ->where('comment_date <=', $month . '-31')
            ->where('deleted_at', null)
            ->orderBy('comment_date', 'ASC')
            ->findAll();
    }

    /**
     * Get all comments for multiple tests
     */
    public function getByTestIds(array $testIds): array {
        if (empty($testIds)) {
            return [];
        }
        return $this->whereIn('test_id', $testIds)
            ->where('deleted_at', null)
            ->orderBy('comment_date', 'DESC')
            ->findAll();
    }

    /**
     * Upsert comment for a test + date combination
     */
    public function upsertComment(array $data): int {
        if (!isset($data['test_id']) || !isset($data['comment_date'])) {
            return 0;
        }

        $existing = $this->where('test_id', $data['test_id'])
            ->where('comment_date', $data['comment_date'])
            ->where('deleted_at', null)
            ->first();

        if ($existing) {
            if (empty($data['comment_text'])) {
                // If text is empty, soft delete
                $this->update($existing['test_comment_id'], ['deleted_at' => date('Y-m-d H:i:s')]);
                return $existing['test_comment_id'];
            }
            $this->update($existing['test_comment_id'], $data);
            return $existing['test_comment_id'];
        } else {
            if (empty($data['comment_text'])) {
                return 0; // Don't insert empty comments
            }
            return $this->insert($data, true);
        }
    }

    /**
     * Delete comment by test_id and date
     */
    public function deleteByTestAndDate(int $testId, string $date): bool {
        $existing = $this->where('test_id', $testId)
            ->where('comment_date', $date)
            ->where('deleted_at', null)
            ->first();

        if ($existing) {
            return $this->delete($existing['test_comment_id']);
        }
        return false;
    }
}