<?php

declare(strict_types=1);

namespace FecaMailshots\Repository;

use FecaMailshots\Infrastructure\DatabaseRouter;
use PDO;

final class MailshotRepository
{
    private DatabaseRouter $router;
    private bool $recipientEmailFieldColumnChecked = false;

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

    /** @return list<array<string, mixed>> */
    public function all(): array
    {
        $this->ensureRecipientEmailFieldColumn();
        $sql = 'SELECT id, Purpose, DataSource, CC, BCC, Subject, Message, PDFAttachment, AttachmentNames, PDFFilenameDerivedFrom, ReplyTo, RecipientEmailField FROM mailshots ORDER BY Purpose ASC';
        return $this->router->mailshotsPdo()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find(int $id): ?array
    {
        $this->ensureRecipientEmailFieldColumn();
        $stmt = $this->router->mailshotsPdo()->prepare('SELECT id, Purpose, DataSource, CC, BCC, Subject, Message, PDFAttachment, AttachmentNames, PDFFilenameDerivedFrom, ReplyTo, RecipientEmailField FROM mailshots WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : $row;
    }

    /** @param array<string, mixed> $row */
    public function create(array $row): int
    {
        $this->ensureRecipientEmailFieldColumn();
        $sql = 'INSERT INTO mailshots (Purpose, DataSource, CC, BCC, Subject, Message, PDFAttachment, AttachmentNames, PDFFilenameDerivedFrom, ReplyTo, RecipientEmailField) VALUES (:Purpose, :DataSource, :CC, :BCC, :Subject, :Message, :PDFAttachment, :AttachmentNames, :PDFFilenameDerivedFrom, :ReplyTo, :RecipientEmailField)';
        $stmt = $this->router->mailshotsPdo()->prepare($sql);
        $stmt->execute($this->payload($row));
        return (int) $this->router->mailshotsPdo()->lastInsertId();
    }

    /** @param array<string, mixed> $row */
    public function update(int $id, array $row): void
    {
        $this->ensureRecipientEmailFieldColumn();
        $sql = 'UPDATE mailshots SET Purpose = :Purpose, DataSource = :DataSource, CC = :CC, BCC = :BCC, Subject = :Subject, Message = :Message, PDFAttachment = :PDFAttachment, AttachmentNames = :AttachmentNames, PDFFilenameDerivedFrom = :PDFFilenameDerivedFrom, ReplyTo = :ReplyTo, RecipientEmailField = :RecipientEmailField WHERE id = :id';
        $stmt = $this->router->mailshotsPdo()->prepare($sql);
        $payload = $this->payload($row);
        $payload['id'] = $id;
        $stmt->execute($payload);
    }

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

    public function countByDataSource(string $dataSourceName): int
    {
        $stmt = $this->router->mailshotsPdo()->prepare('SELECT COUNT(*) FROM mailshots WHERE DataSource = :name');
        $stmt->execute(['name' => trim($dataSourceName)]);
        return (int) $stmt->fetchColumn();
    }

    /** @param array<string, mixed> $row @return array<string, mixed> */
    private function payload(array $row): array
    {
        return [
            'Purpose' => $this->textOrNull($row['Purpose'] ?? null),
            'DataSource' => $this->text($row['DataSource'] ?? ''),
            'CC' => $this->textOrNull($row['CC'] ?? null),
            'BCC' => $this->textOrNull($row['BCC'] ?? null),
            'Subject' => $this->text($row['Subject'] ?? ''),
            'Message' => $this->text($row['Message'] ?? ''),
            'PDFAttachment' => $this->textOrNull($row['PDFAttachment'] ?? null),
            'AttachmentNames' => $this->textOrNull($row['AttachmentNames'] ?? null),
            'PDFFilenameDerivedFrom' => $this->textOrNull($row['PDFFilenameDerivedFrom'] ?? null),
            'ReplyTo' => $this->textOrNull($row['ReplyTo'] ?? null),
            'RecipientEmailField' => $this->textOrNull($row['RecipientEmailField'] ?? null),
        ];
    }

    private function ensureRecipientEmailFieldColumn(): void
    {
        if ($this->recipientEmailFieldColumnChecked) {
            return;
        }
        $this->recipientEmailFieldColumnChecked = true;

        $pdo = $this->router->mailshotsPdo();
        $exists = false;
        try {
            $schema = $this->router->mailshotsDbName();
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = :schema AND table_name = 'mailshots' AND column_name = 'RecipientEmailField'");
            $stmt->execute(['schema' => $schema]);
            $exists = (int) $stmt->fetchColumn() > 0;
        } catch (\Throwable $e) {
            $cols = $pdo->query('SHOW COLUMNS FROM mailshots')->fetchAll(PDO::FETCH_ASSOC);
            foreach ($cols as $col) {
                if (strcasecmp((string) ($col['Field'] ?? ''), 'RecipientEmailField') === 0) {
                    $exists = true;
                    break;
                }
            }
        }

        if (!$exists) {
            $pdo->exec('ALTER TABLE mailshots ADD COLUMN RecipientEmailField VARCHAR(255) NULL AFTER ReplyTo');
        }
    }

    private function text($value): string
    {
        return trim((string) $value);
    }

    private function textOrNull($value): ?string
    {
        if ($value === null) {
            return null;
        }
        $v = trim((string) $value);
        return $v === '' ? null : $v;
    }
}
