<?php

declare(strict_types=1);

namespace App\Modules\Administration\Repositories;

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

final class PayamRepository
{
    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.*,
                c.county_code,
                c.county_name,
                s.id AS state_id,
                s.state_code,
                s.state_name,
                s.administrative_type
            FROM payams p
            INNER JOIN counties c
                ON c.id = p.county_id
            INNER JOIN states s
                ON s.id = c.state_id
            ORDER BY
                s.state_name ASC,
                c.county_name ASC,
                p.payam_name ASC'
        );

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

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

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

    public function counties(): array
    {
        $statement = $this->pdo->query(
            'SELECT
                c.*,
                s.state_name
            FROM counties c
            INNER JOIN states s
                ON s.id = c.state_id
            WHERE c.status = 1
            AND s.status = 1
            ORDER BY
                s.state_name ASC,
                c.county_name ASC'
        );

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

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

    public function findByName(
        int $countyId,
        string $name
    ): ?array {
        return $this->query
            ->table('payams')
            ->where('county_id', $countyId)
            ->where('payam_name', $name)
            ->first();
    }

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

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

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