<?php

declare(strict_types=1);

final class TaskBoxService
{
    public static function ensureTable(PDO $pdo): void
    {
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS task_boxes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                task_id INT NOT NULL,
                box_no VARCHAR(50) NOT NULL,
                created_by INT NULL,
                created_at DATETIME NOT NULL,
                UNIQUE KEY uq_task_boxes (task_id, box_no),
                INDEX idx_task_boxes_task (task_id),
                CONSTRAINT fk_task_boxes_task_id FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );
    }

    public static function normalizeBoxNo(string $boxNo): string
    {
        $boxNo = trim($boxNo);
        $boxNo = preg_replace('/\s+/', ' ', $boxNo) ?? $boxNo;
        return $boxNo;
    }

    public static function parseBoxNos(string $input): array
    {
        $input = trim($input);
        if ($input === '') {
            return [];
        }

        $input = str_replace(["\r\n", "\r"], "\n", $input);
        $raw = preg_split('/[\n,]+/', $input) ?: [];

        $out = [];
        foreach ($raw as $v) {
            $v = self::normalizeBoxNo((string)$v);
            if ($v === '') {
                continue;
            }
            $out[$v] = true;
        }

        return array_keys($out);
    }

    public static function getBoxNos(PDO $pdo, int $taskId): array
    {
        self::ensureTable($pdo);

        $stmt = $pdo->prepare('SELECT box_no FROM task_boxes WHERE task_id = :tid ORDER BY box_no ASC');
        $stmt->execute(['tid' => $taskId]);
        $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);

        $out = [];
        foreach ($rows as $r) {
            $v = self::normalizeBoxNo((string)$r);
            if ($v !== '') {
                $out[] = $v;
            }
        }

        return $out;
    }

    public static function setBoxNos(PDO $pdo, int $taskId, array $boxNos, ?int $byUserId = null): void
    {
        self::ensureTable($pdo);

        $normalized = [];
        foreach ($boxNos as $b) {
            $b = self::normalizeBoxNo((string)$b);
            if ($b !== '') {
                $normalized[$b] = true;
            }
        }
        $normalized = array_keys($normalized);

        $pdo->prepare('DELETE FROM task_boxes WHERE task_id = :tid')->execute(['tid' => $taskId]);

        if (!$normalized) {
            return;
        }

        $stmt = $pdo->prepare(
            'INSERT INTO task_boxes (task_id, box_no, created_by, created_at)
             VALUES (:tid, :box, :by, :at)'
        );

        $now = date('Y-m-d H:i:s');
        foreach ($normalized as $boxNo) {
            $stmt->execute([
                'tid' => $taskId,
                'box' => $boxNo,
                'by' => $byUserId,
                'at' => $now,
            ]);
        }
    }

    public static function isAllowed(PDO $pdo, int $taskId, string $boxNo): bool
    {
        $boxNo = self::normalizeBoxNo($boxNo);
        if ($taskId <= 0 || $boxNo === '') {
            return false;
        }

        self::ensureTable($pdo);

        $stmt = $pdo->prepare('SELECT 1 FROM task_boxes WHERE task_id = :tid AND box_no = :box LIMIT 1');
        $stmt->execute(['tid' => $taskId, 'box' => $boxNo]);
        return (bool)$stmt->fetchColumn();
    }
}
