<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

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

final class EmployeeRepository
{
    private PDO $pdo;

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

    public function all(): array
    {
        $sql = '
            SELECT
                e.*,

                r.rank_name,

                d.department_name,

                p.position_name,

                s.state_name,

                c.county_name,

                py.payam_name

            FROM employees e

            LEFT JOIN ranks r
                ON r.id = e.rank_id

            LEFT JOIN departments d
                ON d.id = e.department_id

            LEFT JOIN positions p
                ON p.id = e.position_id

            LEFT JOIN states s
                ON s.id = e.state_id

            LEFT JOIN counties c
                ON c.id = e.county_id

            LEFT JOIN payams py
                ON py.id = e.payam_id

            ORDER BY e.id DESC
        ';

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

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

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

    public function findDetailed(
        int $id
    ): ?array {
        $sql = '
            SELECT
                e.*,

                r.rank_name,
                r.rank_code,

                d.department_name,
                d.department_code,

                p.position_name,
                p.position_code,

                s.state_name,

                c.county_name,

                py.payam_name

            FROM employees e

            LEFT JOIN ranks r
                ON r.id = e.rank_id

            LEFT JOIN departments d
                ON d.id = e.department_id

            LEFT JOIN positions p
                ON p.id = e.position_id

            LEFT JOIN states s
                ON s.id = e.state_id

            LEFT JOIN counties c
                ON c.id = e.county_id

            LEFT JOIN payams py
                ON py.id = e.payam_id

            WHERE e.id = ?

            LIMIT 1
        ';

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

        $statement->execute([$id]);

        $employee = $statement->fetch(
            PDO::FETCH_ASSOC
        );

        return $employee ?: null;
    }

    public function findByEmployeeId(
        string $employeeId
    ): ?array {
        return $this->query
            ->table('employees')
            ->where(
                'employee_id',
                $employeeId
            )
            ->first();
    }

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

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

    public function ranks(): array
    {
        return $this->query
            ->table('ranks')
            ->where('status', 1)
            ->orderBy(
                'rank_level',
                'ASC'
            )
            ->get();
    }

    public function departments(): array
    {
        return $this->query
            ->table('departments')
            ->where('status', 1)
            ->orderBy(
                'department_name',
                'ASC'
            )
            ->get();
    }

    public function positions(): array
    {
        return $this->query
            ->table('positions')
            ->where('status', 1)
            ->orderBy(
                'position_name',
                'ASC'
            )
            ->get();
    }

    public function states(): array
    {
        return $this->query
            ->table('states')
            ->where('status', 1)
            ->orderBy(
                'state_name',
                'ASC'
            )
            ->get();
    }

    public function counties(): array
    {
        return $this->query
            ->table('counties')
            ->where('status', 1)
            ->orderBy(
                'county_name',
                'ASC'
            )
            ->get();
    }

    public function payams(): array
    {
        return $this->query
            ->table('payams')
            ->where('status', 1)
            ->orderBy(
                'payam_name',
                'ASC'
            )
            ->get();
    }

    public function lastEmployee(): ?array
    {
        return $this->query
            ->table('employees')
            ->orderBy('id', 'DESC')
            ->first();
    }
}