<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

use PDO;
use System\Contracts\DatabaseInterface;
use System\Database\QueryBuilder;

final class DynamicFormSubmissionRepository
{
    private PDO $pdo;

    public function __construct(
        private QueryBuilder $query,
        DatabaseInterface $database
    ) {
        $this->pdo =
            $database->getConnection();
    }


    public function create(
        array $data
    ): int {
        $sql = '
            INSERT INTO dynamic_form_submissions
            (
                form_id,
                employee_id,
                submitted_by,
                submission_status,
                submitted_at,
                status,
                created_at,
                updated_at
            )
            VALUES
            (
                :form_id,
                :employee_id,
                :submitted_by,
                :submission_status,
                :submitted_at,
                :status,
                :created_at,
                :updated_at
            )
        ';

        $statement =
            $this->pdo->prepare($sql);

        $statement->execute([
            'form_id' =>
                $data['form_id'],

            'employee_id' =>
                $data['employee_id']
                ?? null,

            'submitted_by' =>
                $data['submitted_by']
                ?? null,

            'submission_status' =>
                $data['submission_status']
                ?? 'submitted',

            'submitted_at' =>
                $data['submitted_at']
                ?? null,

            'status' =>
                $data['status']
                ?? 1,

            'created_at' =>
                $data['created_at'],

            'updated_at' =>
                $data['updated_at']
        ]);

        return (int)
            $this->pdo->lastInsertId();
    }


    public function createValue(
        array $data
    ): bool {
        $sql = '
            INSERT INTO dynamic_form_submission_values
            (
                submission_id,
                field_id,
                value_text,
                file_path,
                file_original_name,
                file_mime_type,
                file_size,
                created_at,
                updated_at
            )
            VALUES
            (
                :submission_id,
                :field_id,
                :value_text,
                :file_path,
                :file_original_name,
                :file_mime_type,
                :file_size,
                :created_at,
                :updated_at
            )
        ';

        $statement =
            $this->pdo->prepare($sql);

        return $statement->execute([
            'submission_id' =>
                $data['submission_id'],

            'field_id' =>
                $data['field_id'],

            'value_text' =>
                $data['value_text']
                ?? null,

            'file_path' =>
                $data['file_path']
                ?? null,

            'file_original_name' =>
                $data['file_original_name']
                ?? null,

            'file_mime_type' =>
                $data['file_mime_type']
                ?? null,

            'file_size' =>
                $data['file_size']
                ?? null,

            'created_at' =>
                $data['created_at'],

            'updated_at' =>
                $data['updated_at']
        ]);
    }


    public function find(
        int $id
    ): ?array {
        return $this->query
            ->table(
                'dynamic_form_submissions'
            )
            ->find($id);
    }


    public function values(
        int $submissionId
    ): array {
        return $this->query
            ->table(
                'dynamic_form_submission_values'
            )
            ->where(
                'submission_id',
                $submissionId
            )
            ->orderBy(
                'id',
                'ASC'
            )
            ->get();
    }


    public function byForm(
        int $formId
    ): array {
        return $this->query
            ->table(
                'dynamic_form_submissions'
            )
            ->where(
                'form_id',
                $formId
            )
            ->where(
                'status',
                1
            )
            ->orderBy(
                'id',
                'DESC'
            )
            ->get();
    }


    public function byEmployee(
        int $employeeId
    ): array {
        return $this->query
            ->table(
                'dynamic_form_submissions'
            )
            ->where(
                'employee_id',
                $employeeId
            )
            ->where(
                'status',
                1
            )
            ->orderBy(
                'id',
                'DESC'
            )
            ->get();
    }


    public function beginTransaction(): void
    {
        if (
            !$this->pdo->inTransaction()
        ) {
            $this->pdo
                ->beginTransaction();
        }
    }


    public function commit(): void
    {
        if (
            $this->pdo->inTransaction()
        ) {
            $this->pdo->commit();
        }
    }


    public function rollBack(): void
    {
        if (
            $this->pdo->inTransaction()
        ) {
            $this->pdo->rollBack();
        }
    }
}