KCI-PHP-KANBAN/app/Repositories/CardRepository.php

<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Models\Card;
use Core\Repository;

class CardRepository extends Repository
{
    protected string $table = 'cards';

    public function findById(int $id): ?Card
    {
        $row = $this->database->first('SELECT * FROM cards WHERE id = :id', ['id' => $id]);

        return $row ? Card::fromRow($row) : null;
    }

    /** @return Card[] */
    public function findByBoardId(int $boardId): array
    {
        $rows = $this->database->query(
            'SELECT * FROM cards WHERE board_id = :board_id ORDER BY swim_lane_id ASC, column_id ASC, position ASC',
            ['board_id' => $boardId]
        );

        return array_map(fn(array $r) => Card::fromRow($r), $rows);
    }

    /** @return Card[] */
    public function findByColumnId(int $columnId): array
    {
        $rows = $this->database->query(
            'SELECT * FROM cards WHERE column_id = :column_id ORDER BY swim_lane_id ASC, position ASC',
            ['column_id' => $columnId]
        );

        return array_map(fn(array $r) => Card::fromRow($r), $rows);
    }

    /** @return Card[] */
    public function findBySwimLaneId(int $swimLaneId): array
    {
        $rows = $this->database->query(
            'SELECT * FROM cards WHERE swim_lane_id = :swim_lane_id ORDER BY column_id ASC, position ASC',
            ['swim_lane_id' => $swimLaneId]
        );

        return array_map(fn(array $r) => Card::fromRow($r), $rows);
    }

    public function maxPosition(int $columnId, int $swimLaneId): int
    {
        $row = $this->database->first(
            'SELECT MAX(position) AS max_pos FROM cards WHERE column_id = :col AND swim_lane_id = :lane',
            ['col' => $columnId, 'lane' => $swimLaneId]
        );

        return (int) ($row['max_pos'] ?? -1);
    }

    public function insert(Card $card): Card
    {
        $this->database->execute(
            'INSERT INTO cards
                (board_id, column_id, swim_lane_id, job_number, job_name, customer_name, delivery_date,
                 quantity, notes, full_note, position, cell_entered_at, created_at, created_by, updated_at, updated_by)
             VALUES
                (:board_id, :column_id, :swim_lane_id, :job_number, :job_name, :customer_name, :delivery_date,
                 :quantity, :notes, :full_note, :position, :cell_entered_at, :created_at, :created_by, :updated_at, :updated_by)',
            [
                'board_id'      => $card->boardId,
                'column_id'     => $card->columnId,
                'swim_lane_id'  => $card->swimLaneId,
                'job_number'    => $card->jobNumber,
                'job_name'      => $card->jobName,
                'customer_name' => $card->customerName,
                'delivery_date' => $card->deliveryDate ?: null,
                'quantity'      => $card->quantity !== '' ? $card->quantity : null,
                'notes'         => $card->notes,
                'full_note'     => $card->fullNote,
                'position'      => $card->position,
                'cell_entered_at' => $card->cellEnteredAt,
                'created_at'    => $card->createdAt,
                'created_by'    => $card->createdBy,
                'updated_at'    => $card->updatedAt,
                'updated_by'    => $card->updatedBy,
            ]
        );

        $row = $this->database->first('SELECT last_insert_rowid() AS id');
        $card->id = (int) ($row['id'] ?? 0);

        return $card;
    }

    public function update(Card $card): void
    {
        $this->database->execute(
            'UPDATE cards
             SET job_number = :job_number, job_name = :job_name, customer_name = :customer_name,
                 delivery_date = :delivery_date, quantity = :quantity, notes = :notes, full_note = :full_note,
                 updated_at = :updated_at, updated_by = :updated_by
             WHERE id = :id',
            [
                'job_number'    => $card->jobNumber,
                'job_name'      => $card->jobName,
                'customer_name' => $card->customerName,
                'delivery_date' => $card->deliveryDate ?: null,
                'quantity'      => $card->quantity !== '' ? $card->quantity : null,
                'notes'         => $card->notes,
                'full_note'     => $card->fullNote,
                'updated_at'    => $card->updatedAt,
                'updated_by'    => $card->updatedBy,
                'id'            => $card->id,
            ]
        );
    }

    public function move(int $id, int $columnId, int $swimLaneId, int $position, string $updatedAt, string $updatedBy): void
    {
        $this->database->execute(
            'UPDATE cards SET column_id = :column_id, swim_lane_id = :swim_lane_id, position = :position,
             updated_at = :updated_at, updated_by = :updated_by,
             cell_entered_at = CASE
                 WHEN column_id != :check_column_id OR swim_lane_id != :check_swim_lane_id THEN :cell_entered_at
                 ELSE cell_entered_at
             END
             WHERE id = :id',
            [
                'column_id' => $columnId, 'swim_lane_id' => $swimLaneId, 'position' => $position,
                'updated_at' => $updatedAt, 'updated_by' => $updatedBy,
                'check_column_id' => $columnId, 'check_swim_lane_id' => $swimLaneId,
                'cell_entered_at' => $updatedAt, 'id' => $id,
            ]
        );
    }

    public function updatePosition(int $id, int $position, string $updatedAt, string $updatedBy): void
    {
        $this->database->execute(
            'UPDATE cards SET position = :position, updated_at = :updated_at, updated_by = :updated_by WHERE id = :id',
            ['position' => $position, 'updated_at' => $updatedAt, 'updated_by' => $updatedBy, 'id' => $id]
        );
    }

    public function deleteByBoardId(int $boardId): void
    {
        $this->database->execute('DELETE FROM cards WHERE board_id = :board_id', ['board_id' => $boardId]);
    }

    public function deleteByColumnId(int $columnId): void
    {
        $this->database->execute('DELETE FROM cards WHERE column_id = :column_id', ['column_id' => $columnId]);
    }

    public function deleteBySwimLaneId(int $swimLaneId): void
    {
        $this->database->execute('DELETE FROM cards WHERE swim_lane_id = :swim_lane_id', ['swim_lane_id' => $swimLaneId]);
    }
}