<?php

namespace App\Http\Controllers;

use App\Models\District;
use App\Models\Parish;
use App\Models\Partner;
use App\Models\SubCounty;
use App\Models\Village;
use App\Models\Location;
use App\Models\Disability;
use App\Models\Status;
use App\Models\Satisfaction;
use App\Models\User;
use App\Models\NationalStatusCategory;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\Schema;

class DropdownController extends Controller
{
    public function findSubcounty(Request $request)
    {
        $districtId = $this->validatedId($request, ['id', 'district_id', 'districtid']);

        if (empty($districtId)) {
            return Response::json([]);
        }

        $foreignKey = $this->firstExistingColumn('sub_counties', [
            'districts_id',
            'district_id',
            'districtid',
            'district',
        ]);

        if (!$foreignKey) {
            return Response::json([]);
        }

        $subcounties = DB::table('sub_counties')
            ->select('id', 'name')
            ->where($foreignKey, $districtId)
            ->orderBy('name')
            ->get();

        return Response::json($subcounties);
    }

    public function findParish(Request $request)
    {
        $subCountyId = $this->validatedId($request, ['id', 'subcounty_id', 'subcountyid']);

        if (empty($subCountyId)) {
            return Response::json([]);
        }

        $foreignKey = $this->firstExistingColumn('parishes', [
            'sub_counties_id',
            'sub_county_id',
            'subcounty_id',
            'subcountyid',
        ]);

        if (!$foreignKey) {
            return Response::json([]);
        }

        $parishes = DB::table('parishes')
            ->select('id', 'name')
            ->where($foreignKey, $subCountyId)
            ->orderBy('name')
            ->get();

        return Response::json($parishes);
    }

    public function findVillage(Request $request)
    {
        $parishId = $this->validatedId($request, ['id', 'parish_id', 'parishid']);

        if (empty($parishId) || !Schema::hasTable('villages')) {
            return Response::json([]);
        }

        $foreignKey = $this->firstExistingColumn('villages', [
            'parishes_id',
            'parish_id',
            'parishid',
        ]);

        if (!$foreignKey) {
            return Response::json([]);
        }

        $villages = DB::table('villages')
            ->select('id', 'name')
            ->where($foreignKey, $parishId)
            ->orderBy('name')
            ->get();

        return Response::json($villages);
    }

    public function findDistrict(Request $request)
    {
        return Response::json(District::select('id', 'name')->orderBy('name')->get());
    }

    public function findPartner(Request $request)
    {
        return Response::json(Partner::select('id', 'name')->orderBy('name')->get());
    }

    public function findDisability(Request $request)
    {
        return Response::json(Disability::select('id', 'name')->where('status', '=', '1')->orderBy('name')->get());
    }

    public function findStatuses(Request $request)
    {
        return Response::json(Status::select('id', 'name')->orderBy('name')->get());
    }

    public function findSatisfaction(Request $request)
    {
        return Response::json(Satisfaction::select('id', 'name')->orderBy('name')->get());
    }

    public function usercredentials(Request $request)
    {
        $request->validate(['id' => ['required', 'string', 'max:255']]);
        $userid = base64_decode($request->id, true);
        abort_unless($userid && ctype_digit((string) $userid), 422, 'Invalid user reference.');
        return User::select('email')->where('id', (int) $userid)->get();
    }

    public function findNSCategory(Request $request)
    {
        $nationalStatusId = $this->validatedId($request, ['id', 'national_status_id', 'national_status', 'nstatus']);

        if (empty($nationalStatusId)) {
            return Response::json([]);
        }

        $table = 'national_status_categories';
        if (!Schema::hasTable($table)) {
            return Response::json([]);
        }

        $foreignKey = $this->firstExistingColumn($table, [
            'national_statuses_id',
            'national_status_id',
            'nationalstatus_id',
            'status_id',
        ]);

        if (!$foreignKey) {
            return Response::json([]);
        }

        $query = DB::table($table)->select('id', 'name')->where($foreignKey, $nationalStatusId);

        if (Schema::hasColumn($table, 'status')) {
            $query->where(function ($q) {
                $q->where('status', '1')->orWhere('status', 1)->orWhereNull('status');
            });
        }

        return Response::json($query->orderBy('name')->get());
    }

    private function validatedId(Request $request, array $keys): ?int
    {
        foreach ($keys as $key) {
            $value = $request->input($key);
            if ($value !== null && $value !== '') {
                if (!ctype_digit((string) $value)) {
                    abort(422, 'Invalid reference selected.');
                }
                return (int) $value;
            }
        }

        return null;
    }

    private function firstExistingColumn(string $table, array $columns): ?string
    {
        if (!Schema::hasTable($table)) {
            return null;
        }

        foreach ($columns as $column) {
            if (Schema::hasColumn($table, $column)) {
                return $column;
            }
        }

        return null;
    }
}
