<?php

declare(strict_types=1);

final class TaskChecklistService
{
    public static function ensureTable(PDO $pdo): void
    {
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS task_checklists (
                task_id INT PRIMARY KEY,
                fumigation_completed TINYINT(1) NOT NULL DEFAULT 0,
                bait_box_checked TINYINT(1) NOT NULL DEFAULT 0,
                glue_box_checked TINYINT(1) NOT NULL DEFAULT 0,
                efk_checked TINYINT(1) NOT NULL DEFAULT 0,
                ilt_checked TINYINT(1) NOT NULL DEFAULT 0,
                others_checked TINYINT(1) NOT NULL DEFAULT 0,
                others_text VARCHAR(255) NULL,
                updated_by INT NULL,
                updated_at DATETIME NOT NULL,
                CONSTRAINT fk_task_checklists_task_id FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );

        // Backward-compatible migration: if table existed before others_text was added.
        $stmt = $pdo->prepare(
            "SELECT COUNT(*)
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = 'task_checklists'
               AND column_name = 'others_text'"
        );
        $stmt->execute();
        if ((int)$stmt->fetchColumn() === 0) {
            $pdo->exec("ALTER TABLE task_checklists ADD COLUMN others_text VARCHAR(255) NULL AFTER others_checked");
        }
    }

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

        $stmt = $pdo->prepare(
            'SELECT task_id, fumigation_completed, bait_box_checked, glue_box_checked, efk_checked, ilt_checked, others_checked, others_text
             FROM task_checklists
             WHERE task_id = :tid
             LIMIT 1'
        );
        $stmt->execute(['tid' => $taskId]);
        $row = $stmt->fetch();

        return $row ? (array)$row : [
            'task_id' => $taskId,
            'fumigation_completed' => 0,
            'bait_box_checked' => 0,
            'glue_box_checked' => 0,
            'efk_checked' => 0,
            'ilt_checked' => 0,
            'others_checked' => 0,
            'others_text' => null,
        ];
    }

    public static function save(PDO $pdo, int $taskId, array $flags, ?int $updatedBy): void
    {
        self::ensureTable($pdo);

        $othersChecked = !empty($flags['others_checked']);
        $othersText = $othersChecked ? trim((string)($flags['others_text'] ?? '')) : '';
        if ($othersText === '') {
            $othersText = null;
        }

        $data = [
            'task_id' => $taskId,
            'fumigation_completed' => !empty($flags['fumigation_completed']) ? 1 : 0,
            'bait_box_checked' => !empty($flags['bait_box_checked']) ? 1 : 0,
            'glue_box_checked' => !empty($flags['glue_box_checked']) ? 1 : 0,
            'efk_checked' => !empty($flags['efk_checked']) ? 1 : 0,
            'ilt_checked' => !empty($flags['ilt_checked']) ? 1 : 0,
            'others_checked' => $othersChecked ? 1 : 0,
            'others_text' => $othersText,
            'updated_by' => $updatedBy,
            'updated_at' => date('Y-m-d H:i:s'),
        ];

        $stmt = $pdo->prepare(
            'INSERT INTO task_checklists (
                task_id, fumigation_completed, bait_box_checked, glue_box_checked, efk_checked, ilt_checked, others_checked, others_text, updated_by, updated_at
             ) VALUES (
                :task_id, :fumigation_completed, :bait_box_checked, :glue_box_checked, :efk_checked, :ilt_checked, :others_checked, :others_text, :updated_by, :updated_at
             )
             ON DUPLICATE KEY UPDATE
                fumigation_completed = VALUES(fumigation_completed),
                bait_box_checked = VALUES(bait_box_checked),
                glue_box_checked = VALUES(glue_box_checked),
                efk_checked = VALUES(efk_checked),
                ilt_checked = VALUES(ilt_checked),
                others_checked = VALUES(others_checked),
                others_text = VALUES(others_text),
                updated_by = VALUES(updated_by),
                updated_at = VALUES(updated_at)'
        );
        $stmt->execute($data);
    }
}
