<?php

namespace App\Services\Core;

use App\Exceptions\DataQualityException;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class ColumnAwareLocationChainValidator
{
    public function assertValid(?int $districtId, ?int $subCountyId, ?int $parishId): void
    {
        if (!$districtId || !$subCountyId || !$parishId) {
            throw new DataQualityException('Please select a valid District, Sub-County and Parish before saving.');
        }

        $this->assertSubCountyBelongsToDistrict($subCountyId, $districtId);
        $this->assertParishBelongsToSubCounty($parishId, $subCountyId);
    }

    protected function assertSubCountyBelongsToDistrict(int $subCountyId, int $districtId): void
    {
        if (!Schema::hasTable('sub_counties')) {
            return;
        }

        $query = DB::table('sub_counties')->where('id', $subCountyId);

        $columns = ['districts_id', 'districtid', 'district_id'];
        $available = array_values(array_filter($columns, function ($column) {
            return Schema::hasColumn('sub_counties', $column);
        }));

        if (empty($available)) {
            return;
        }

        $query->where(function ($q) use ($available, $districtId) {
            foreach ($available as $column) {
                $q->orWhere($column, $districtId);
            }
        });

        if (!$query->exists()) {
            throw new DataQualityException('The selected Sub-County does not belong to the selected District.');
        }
    }

    protected function assertParishBelongsToSubCounty(int $parishId, int $subCountyId): void
    {
        if (!Schema::hasTable('parishes')) {
            return;
        }

        $query = DB::table('parishes')->where('id', $parishId);

        $columns = ['sub_counties_id', 'subcountyid', 'sub_county_id', 'subcounty_id'];
        $available = array_values(array_filter($columns, function ($column) {
            return Schema::hasColumn('parishes', $column);
        }));

        if (empty($available)) {
            return;
        }

        $query->where(function ($q) use ($available, $subCountyId) {
            foreach ($available as $column) {
                $q->orWhere($column, $subCountyId);
            }
        });

        if (!$query->exists()) {
            throw new DataQualityException('The selected Parish does not belong to the selected Sub-County.');
        }
    }
}
