<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Models\CardMovement;
use Core\Repository;

class CardMovementRepository extends Repository
{
    protected string $table = 'card_movements';

    public function insert(CardMovement $movement): CardMovement
    {
        $this->database->execute(
            'INSERT INTO card_movements
                (card_id, from_column_id, from_column_name, to_column_id, to_column_name,
                 from_swim_lane_id, from_swim_lane_name, to_swim_lane_id, to_swim_lane_name,
                 moved_at, moved_by)
             VALUES
                (:card_id, :from_column_id, :from_column_name, :to_column_id, :to_column_name,
                 :from_swim_lane_id, :from_swim_lane_name, :to_swim_lane_id, :to_swim_lane_name,
                 :moved_at, :moved_by)',
            [
                'card_id'             => $movement->cardId,
                'from_column_id'      => $movement->fromColumnId,
                'from_column_name'    => $movement->fromColumnName,
                'to_column_id'        => $movement->toColumnId,
                'to_column_name'      => $movement->toColumnName,
                'from_swim_lane_id'   => $movement->fromSwimLaneId,
                'from_swim_lane_name' => $movement->fromSwimLaneName,
                'to_swim_lane_id'     => $movement->toSwimLaneId,
                'to_swim_lane_name'   => $movement->toSwimLaneName,
                'moved_at'            => $movement->movedAt,
                'moved_by'            => $movement->movedBy,
            ]
        );

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

        return $movement;
    }

    /** @return CardMovement[] */
    public function findByCardId(int $cardId): array
    {
        $rows = $this->database->query(
            'SELECT * FROM card_movements WHERE card_id = :card_id ORDER BY moved_at DESC, id DESC',
            ['card_id' => $cardId]
        );

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

    public function deleteByCardId(int $cardId): void
    {
        $this->database->execute('DELETE FROM card_movements WHERE card_id = :card_id', ['card_id' => $cardId]);
    }

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

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

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