<?php

namespace App\Repositories\Admin;

use App\Models\AdminUser;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

final class AdminUserRepository
{
    /** @var array<string, array<int, string>> */
    private array $colsCache = [];

    /** @var array<string, string|null> */
    private array $colTypeCache = [];

    // =========================
    // Basic Find
    // =========================
    public function findByEmail(string $email): ?AdminUser
    {
        return AdminUser::query()
            ->where('email', $email)
            ->first();
    }

    public function findActiveById(int $id): ?AdminUser
    {
        return AdminUser::query()
            ->whereKey($id)
            ->where('status', 'active')
            ->first();
    }

    public function find(int $id): ?object
    {
        return DB::table('admin_users')->where('id', $id)->first();
    }

    // =========================
    // Login Touch
    // =========================
    public function touchLogin(AdminUser $admin, string $ip, ?string $ua = null): void
    {
        $data = [
            'last_login_at' => now(),
        ];

        // last_login_ip 타입이 BINARY일 수도, VARCHAR일 수도 있어서 타입별 처리
        if ($this->hasCol('admin_users', 'last_login_ip')) {
            $type = $this->getColumnType('admin_users', 'last_login_ip'); // e.g. binary, varbinary, varchar...
            if ($type && (str_contains($type, 'binary') || str_contains($type, 'blob'))) {
                $data['last_login_ip'] = inet_pton($ip) ?: null;
            } else {
                $data['last_login_ip'] = $ip ?: null;
            }
        }

        if ($ua !== null && $this->hasCol('admin_users', 'last_login_ua')) {
            $data['last_login_ua'] = $ua;
        }

        $this->save($admin, $data);
    }

    // =========================
    // Password
    // =========================
    public function setPassword(AdminUser $admin, string $plainPassword): void
    {
        //  AdminUser 모델에 password cast(hashed)가 있으면 plain을 넣어도 해싱됨
        $data = [
            'password' => $plainPassword,
            'must_reset_password' => 0,
            'password_changed_at' => now(),
        ];

        $this->save($admin, $data);
    }

    public function setTemporaryPassword(AdminUser $admin, string $plainPassword, int $actorId = 0): void
    {
        $data = [
            'password' => $plainPassword,          // 모델 cast가 해싱
            'must_reset_password' => 1,
            'password_changed_at' => now(),
            'updated_by' => $actorId ?: null,
        ];

        $this->save($admin, $data);
    }

    // =========================
    // Update (DB safe)
    // =========================
    public function updateById(int $id, array $data): bool
    {
        $data['updated_at'] = $data['updated_at'] ?? now();

        $data = $this->filterToExistingColumns('admin_users', $data);

        return DB::table('admin_users')
                ->where('id', $id)
                ->update($data) > 0;
    }

    /** 기존 코드 호환용 */
    public function update(int $id, array $data): bool
    {
        return $this->updateById($id, $data);
    }

    public function save(AdminUser $admin, array $data): void
    {
        $data = $this->filterToExistingColumns('admin_users', $data);

        foreach ($data as $k => $v) {
            $admin->{$k} = $v;
        }

        $admin->save();
    }

    public function existsPhoneHash(string $hash, int $ignoreId): bool
    {
        return DB::table('admin_users')
            ->whereNull('deleted_at')
            ->where('phone_hash', $hash)
            ->where('id', '!=', $ignoreId)
            ->exists();
    }

    // =========================
    // List / Search
    // =========================
    public function paginateUsers(array $filters, int $perPage = 20): LengthAwarePaginator
    {
        $q = AdminUser::query();

        $keyword = trim((string)($filters['q'] ?? ''));
        if ($keyword !== '') {
            $q->where(function ($w) use ($keyword) {
                if ($this->hasCol('admin_users', 'email')) $w->orWhere('email', 'like', "%{$keyword}%");
                if ($this->hasCol('admin_users', 'name')) $w->orWhere('name', 'like', "%{$keyword}%");
                if ($this->hasCol('admin_users', 'nickname')) $w->orWhere('nickname', 'like', "%{$keyword}%");
            });
        }

        $status = (string)($filters['status'] ?? '');
        if ($status !== '' && $this->hasCol('admin_users', 'status')) {
            $q->where('status', $status);
        }

        $role = (string)($filters['role'] ?? '');
        if ($role !== '' && Schema::hasTable('admin_role_user') && Schema::hasTable('admin_roles')) {
            $roleNameCol = $this->firstExistingColumn('admin_roles', ['name','code','slug']);
            if ($roleNameCol) {
                $q->whereIn('id', function ($sub) use ($role, $roleNameCol) {
                    $sub->select('aru.admin_user_id')
                        ->from('admin_role_user as aru')
                        ->join('admin_roles as r', 'r.id', '=', 'aru.admin_role_id')
                        ->where("r.{$roleNameCol}", $role);
                });
            }
        }

        if ($this->hasCol('admin_users', 'last_login_at')) {
            $q->orderByDesc('last_login_at');
        } else {
            $q->orderByDesc('id');
        }

        return $q->paginate($perPage)->withQueryString();
    }

    // =========================
    // Roles / Permissions
    // (현재 DB: roles.name/label, perms.name/label 기반도 지원)
    // =========================
    public function getAllRoles(): array
    {
        if (!Schema::hasTable('admin_roles')) return [];

        $codeCol = $this->firstExistingColumn('admin_roles', ['code','name','slug']);
        $nameCol = $this->firstExistingColumn('admin_roles', ['label','title','name']);

        return DB::table('admin_roles')
            ->orderBy('id')
            ->get([
                'id',
                DB::raw(($codeCol ? "`{$codeCol}`" : "CAST(id AS CHAR)") . " as code"),
                DB::raw(($nameCol ? "`{$nameCol}`" : "CAST(id AS CHAR)") . " as name"),
            ])
            ->map(fn($r)=>(array)$r)
            ->all();
    }

    public function findRoleIdByCode(string $code): ?int
    {
        if (!Schema::hasTable('admin_roles')) return null;

        $codeCol = $this->firstExistingColumn('admin_roles', ['code','name','slug']);
        if (!$codeCol) return null;

        $id = DB::table('admin_roles')->where($codeCol, $code)->value('id');
        return $id ? (int)$id : null;
    }

    public function getRoleIdsForUser(int $adminUserId): array
    {
        if (!Schema::hasTable('admin_role_user')) return [];
        return DB::table('admin_role_user')
            ->where('admin_user_id', $adminUserId)
            ->pluck('admin_role_id')
            ->map(fn($v)=>(int)$v)
            ->all();
    }

    /**  (중복 선언 금지) 상세/내정보/관리페이지 공용 */
    public function getRolesForUser(int $adminUserId): array
    {
        if (!Schema::hasTable('admin_roles') || !Schema::hasTable('admin_role_user')) return [];

        $codeCol = $this->firstExistingColumn('admin_roles', ['code','name','slug']);
        $nameCol = $this->firstExistingColumn('admin_roles', ['label','title','name']);

        return DB::table('admin_role_user as aru')
            ->join('admin_roles as r', 'r.id', '=', 'aru.admin_role_id')
            ->where('aru.admin_user_id', $adminUserId)
            ->orderBy('r.id')
            ->get([
                'r.id',
                DB::raw(($codeCol ? "r.`{$codeCol}`" : "CAST(r.id AS CHAR)") . " as code"),
                DB::raw(($nameCol ? "r.`{$nameCol}`" : "CAST(r.id AS CHAR)") . " as name"),
            ])
            ->map(fn($row)=>(array)$row)
            ->all();
    }

    public function getRoleMapForUsers(array $userIds): array
    {
        if (empty($userIds)) return [];
        if (!Schema::hasTable('admin_role_user') || !Schema::hasTable('admin_roles')) return [];

        $codeCol = $this->firstExistingColumn('admin_roles', ['code','name','slug']);
        $nameCol = $this->firstExistingColumn('admin_roles', ['label','title','name']);

        $rows = DB::table('admin_role_user as aru')
            ->join('admin_roles as r', 'r.id', '=', 'aru.admin_role_id')
            ->whereIn('aru.admin_user_id', $userIds)
            ->get([
                'aru.admin_user_id as uid',
                'r.id as rid',
                DB::raw(($codeCol ? "r.`{$codeCol}`" : "CAST(r.id AS CHAR)") . " as code"),
                DB::raw(($nameCol ? "r.`{$nameCol}`" : "CAST(r.id AS CHAR)") . " as name"),
            ]);

        $map = [];
        foreach ($rows as $row) {
            $uid = (int)$row->uid;
            $map[$uid] ??= [];
            $map[$uid][] = ['id'=>(int)$row->rid,'code'=>(string)$row->code,'name'=>(string)$row->name];
        }
        return $map;
    }

    public function getPermissionsForUser(int $adminUserId): array
    {
        if (!Schema::hasTable('admin_role_user') || !Schema::hasTable('admin_permission_role') || !Schema::hasTable('admin_permissions')) {
            return [];
        }

        $codeCol = $this->firstExistingColumn('admin_permissions', ['code','name','slug']);
        $nameCol = $this->firstExistingColumn('admin_permissions', ['label','title','name']);

        return DB::table('admin_role_user as aru')
            ->join('admin_permission_role as apr', 'apr.admin_role_id', '=', 'aru.admin_role_id')
            ->join('admin_permissions as p', 'p.id', '=', 'apr.admin_permission_id')
            ->where('aru.admin_user_id', $adminUserId)
            ->distinct()
            ->orderBy('p.id')
            ->get([
                'p.id',
                DB::raw(($codeCol ? "p.`{$codeCol}`" : "CAST(p.id AS CHAR)") . " as code"),
                DB::raw(($nameCol ? "p.`{$nameCol}`" : "CAST(p.id AS CHAR)") . " as name"),
            ])
            ->map(fn($row)=>(array)$row)
            ->all();
    }

    public function getPermissionCountMapForUsers(array $userIds): array
    {
        if (empty($userIds)) return [];
        if (!Schema::hasTable('admin_role_user') || !Schema::hasTable('admin_permission_role')) return [];

        $rows = DB::table('admin_role_user as aru')
            ->join('admin_permission_role as apr', 'apr.admin_role_id', '=', 'aru.admin_role_id')
            ->whereIn('aru.admin_user_id', $userIds)
            ->select('aru.admin_user_id as uid', DB::raw('COUNT(DISTINCT apr.admin_permission_id) as cnt'))
            ->groupBy('aru.admin_user_id')
            ->get();

        $map = [];
        foreach ($rows as $r) $map[(int)$r->uid] = (int)$r->cnt;
        return $map;
    }

    public function syncRoles(int $adminUserId, array $roleIds, int $actorId): void
    {
        if (!Schema::hasTable('admin_role_user')) return;

        $roleIds = array_values(array_unique(array_map('intval', $roleIds)));

        DB::table('admin_role_user')
            ->where('admin_user_id', $adminUserId)
            ->whereNotIn('admin_role_id', $roleIds)
            ->delete();

        $existing = DB::table('admin_role_user')
            ->where('admin_user_id', $adminUserId)
            ->pluck('admin_role_id')
            ->map(fn($v)=>(int)$v)->all();

        $need = array_values(array_diff($roleIds, $existing));

        foreach ($need as $rid) {
            $row = [
                'admin_user_id' => $adminUserId,
                'admin_role_id' => (int)$rid,
            ];
            if ($this->hasCol('admin_role_user', 'assigned_by')) $row['assigned_by'] = $actorId;
            if ($this->hasCol('admin_role_user', 'assigned_at')) $row['assigned_at'] = now();

            DB::table('admin_role_user')->insert($row);
        }
    }

    // =========================
    // Helpers
    // =========================
    private function filterToExistingColumns(string $table, array $data): array
    {
        $cols = $this->cols($table);
        $out = [];
        foreach ($data as $k => $v) {
            if (in_array($k, $cols, true)) {
                // null 허용 컬럼만 null 넣고 싶으면 여기서 정책 추가 가능
                $out[$k] = $v;
            }
        }
        return $out;
    }

    private function cols(string $table): array
    {
        if (!isset($this->colsCache[$table])) {
            $this->colsCache[$table] = Schema::hasTable($table) ? Schema::getColumnListing($table) : [];
        }
        return $this->colsCache[$table];
    }

    private function hasCol(string $table, string $col): bool
    {
        return in_array($col, $this->cols($table), true);
    }

    private function firstExistingColumn(string $table, array $candidates): ?string
    {
        $cols = $this->cols($table);
        foreach ($candidates as $c) {
            if (in_array($c, $cols, true)) return $c;
        }
        return null;
    }

    private function getColumnType(string $table, string $col): ?string
    {
        $key = "{$table}.{$col}";
        if (array_key_exists($key, $this->colTypeCache)) {
            return $this->colTypeCache[$key];
        }

        try {
            $row = DB::selectOne("SHOW COLUMNS FROM `{$table}` LIKE ?", [$col]);
            $type = $row?->Type ?? null; // e.g. varbinary(16), varchar(45)
            $this->colTypeCache[$key] = $type ? strtolower((string)$type) : null;
            return $this->colTypeCache[$key];
        } catch (\Throwable $e) {
            $this->colTypeCache[$key] = null;
            return null;
        }
    }

    public function bumpLoginFail(int $id, int $limit = 3): array
    {
        return DB::transaction(function () use ($id, $limit) {
            $row = DB::table('admin_users')
                ->select('failed_login_count', 'locked_until')
                ->where('id', $id)
                ->lockForUpdate()
                ->first();

            $cur = (int)($row->failed_login_count ?? 0);
            $next = $cur + 1;

            $locked = ($row->locked_until ?? null) !== null;

            $update = [
                'failed_login_count' => $next,
                'updated_at' => now(),
            ];

            //  3회 이상이면 "영구잠금"
            if (!$locked && $next >= $limit) {
                $update['locked_until'] = now();
                $locked = true;
            }

            DB::table('admin_users')->where('id', $id)->update($update);

            return ['count' => $next, 'locked' => $locked];
        });
    }

    /**
     * 로그인 성공 시 실패 카운터/잠금 초기화
     */
    public function clearLoginFailAndUnlock(int $id): void
    {
        DB::table('admin_users')
            ->where('id', $id)
            ->update([
                'failed_login_count' => 0,
                'locked_until' => null,
                'updated_at' => now(),
            ]);
    }

    public function existsByEmail(string $email): bool
    {
        return DB::table('admin_users')->where('email', $email)->exists();
    }

    public function existsByPhoneHash(string $phoneHash): bool
    {
        return DB::table('admin_users')->where('phone_hash', $phoneHash)->exists();
    }

    public function insertAdminUser(array $data): int
    {
        // admin_users 컬럼 그대로 사용 (DB 구조 변경 X)
        $id = DB::table('admin_users')->insertGetId($data);
        return (int)$id;
    }

    public function findRoleByCode(string $code): ?array
    {
        $row = DB::table('admin_roles')->where('name', $code)->first();
        if (!$row) return null;
        return (array)$row;
    }

    public function deleteRoleMappingsExcept(int $adminId, int $roleId): void
    {
        DB::table('admin_role_user')
            ->where('admin_user_id', $adminId)
            ->where('admin_role_id', '!=', $roleId)
            ->delete();
    }

    public function upsertRoleMapping(int $adminId, int $roleId): void
    {
        $table = 'admin_role_user';

        $vals = [];
        $now = now();

        // 컬럼 있으면 채움
        if (Schema::hasColumn($table, 'assigned_at')) $vals['assigned_at'] = $now;
        if (Schema::hasColumn($table, 'created_at'))  $vals['created_at']  = $now;
        if (Schema::hasColumn($table, 'updated_at'))  $vals['updated_at']  = $now;

        DB::table($table)->updateOrInsert(
            ['admin_user_id' => $adminId, 'admin_role_id' => $roleId],
            $vals
        );
    }

    public function updateTotpStart(int $id, string $secretEnc): bool
    {
        return DB::table('admin_users')->where('id', $id)->update([
                'totp_secret_enc'   => $secretEnc,
                'totp_verified_at'  => null,
                'totp_enabled'      => 0,     // 등록 완료 전에는 SMS로 유지
                'updated_at'        => now(),
            ]) > 0;
    }

    public function confirmTotp(int $id): bool
    {
        return DB::table('admin_users')->where('id', $id)->update([
                'totp_verified_at' => now(),
                'totp_enabled'     => 1,      // 등록 완료되면 OTP 사용으로 전환(원하면 0 유지로 바꿔도 됨)
                'updated_at'       => now(),
            ]) > 0;
    }

    public function disableTotp(int $id): bool
    {
        return DB::table('admin_users')->where('id', $id)->update([
                'totp_secret_enc'  => null,
                'totp_verified_at' => null,
                'totp_enabled'     => 0,
                'updated_at'       => now(),
            ]) > 0;
    }

    public function updateTotpMode(int $id, int $enabled): bool
    {
        return DB::table('admin_users')->where('id', $id)->update([
                'totp_enabled' => $enabled ? 1 : 0,
                'updated_at'   => now(),
            ]) > 0;
    }

    public function getEmailMapByIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), fn($v)=>$v>0)));
        if (empty($ids)) return [];

        // id => email
        return DB::table('admin_users')
            ->whereIn('id', $ids)
            ->pluck('email', 'id')
            ->map(fn($v)=>(string)$v)
            ->all();
    }

    public function getMetaMapByIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), fn($v)=>$v>0)));
        if (empty($ids)) return [];

        $rows = DB::table('admin_users')
            ->whereIn('id', $ids)
            ->get(['id','email','name','nickname']);

        $map = [];
        foreach ($rows as $r) {
            $id = (int)$r->id;
            $map[$id] = [
                'id' => (int)$r->id,
                'email' => (string)($r->email ?? ''),
                'name'  => (string)($r->name ?? ''),
                'nick'  => (string)($r->nickname ?? ''),
            ];
        }
        return $map;
    }

}