<?php

declare(strict_types=1);

namespace FecaMailshots\Repository;

use FecaMailshots\Infrastructure\DatabaseRouter;
use PDO;

final class MailshotQueryRepository
{
    private DatabaseRouter $router;

    public function __construct(DatabaseRouter $router)
    {
        $this->router = $router;
    }

    /** @return list<array<string, mixed>> */
    public function all(): array
    {
        $sql = 'SELECT ID, name, dsl_text, updated_at FROM mailshot_queries ORDER BY name ASC';
        return $this->router->mailshotsPdo()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find(int $id): ?array
    {
        $stmt = $this->router->mailshotsPdo()->prepare('SELECT ID, name, dsl_text, updated_at FROM mailshot_queries WHERE ID = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : $row;
    }

    public function findByName(string $name): ?array
    {
        $stmt = $this->router->mailshotsPdo()->prepare('SELECT ID, name, dsl_text, updated_at FROM mailshot_queries WHERE name = :name');
        $stmt->execute(['name' => $name]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : $row;
    }

    /** @param array{name:string,dsl_text:string} $data */
    public function create(array $data): int
    {
        $stmt = $this->router->mailshotsPdo()->prepare('INSERT INTO mailshot_queries (name, dsl_text, `sql`) VALUES (:name, :dsl_text, :sql)');
        $stmt->execute([
            'name' => $data['name'],
            'dsl_text' => $data['dsl_text'],
            'sql' => '',
        ]);
        return (int) $this->router->mailshotsPdo()->lastInsertId();
    }

    /** @param array{name:string,dsl_text:string} $data */
    public function update(int $id, array $data): void
    {
        $stmt = $this->router->mailshotsPdo()->prepare('UPDATE mailshot_queries SET name = :name, dsl_text = :dsl_text WHERE ID = :id');
        $stmt->execute([
            'id' => $id,
            'name' => $data['name'],
            'dsl_text' => $data['dsl_text'],
        ]);
    }

    public function delete(int $id): void
    {
        $stmt = $this->router->mailshotsPdo()->prepare('DELETE FROM mailshot_queries WHERE ID = :id');
        $stmt->execute(['id' => $id]);
    }
}
