<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

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

final class PositionRepository
{
    private PDO $pdo;

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

    public function all(): array
    {
        $statement = $this->pdo->query(
            'SELECT
                p.*,
                d.department_code,
                d.department_name,
                b.branch_name,
                o.organization_name
             FROM positions p
             INNER JOIN departments d
                ON d.id = p.department_id
             INNER JOIN branches b
                ON b.id = d.branch_id
             INNER JOIN organizations o
                ON o.id = b.organization_id
             ORDER BY p.id DESC'
        );

        return $statement->fetchAll(
            PDO::FETCH_ASSOC
        );
    }

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

    public function departments(): array
    {
        $statement = $this->pdo->query(
            'SELECT
                d.id,
                d.department_code,
                d.department_name,
                b.branch_name,
                o.organization_name
             FROM departments d
             INNER JOIN branches b
                ON b.id = d.branch_id
             INNER JOIN organizations o
                ON o.id = b.organization_id
             WHERE d.status = 1
             ORDER BY
                o.organization_name ASC,
                b.branch_name ASC,
                d.department_name ASC'
        );

        return $statement->fetchAll(
            PDO::FETCH_ASSOC
        );
    }

    public function findByCode(
        string $code
    ): ?array {
        return $this->query
            ->table('positions')
            ->where(
                'position_code',
                $code
            )
            ->first();
    }

    public function findByName(
        int $departmentId,
        string $name
    ): ?array {
        return $this->query
            ->table('positions')
            ->where(
                'department_id',
                $departmentId
            )
            ->where(
                'position_name',
                $name
            )
            ->first();
    }

    public function create(array $data): bool
    {
        return $this->query
            ->table('positions')
            ->insert($data);
    }

    public function update(
        int $id,
        array $data
    ): bool {
        return $this->query
            ->table('positions')
            ->where('id', $id)
            ->update($data);
    }

    public function setStatus(
        int $id,
        int $status
    ): bool {
        return $this->query
            ->table('positions')
            ->where('id', $id)
            ->update([
                'status' => $status,
                'updated_at' => date(
                    'Y-m-d H:i:s'
                )
            ]);
    }
}