<?php

namespace App\Repositories;

use App\Models\Creator;
use Illuminate\Support\Facades\DB;

class CreatorRepository extends BaseRepository
{
    protected $model;

    private const SELECT_COLUMNS = [
        'id', 'organization_id', 'created_by_id', 'creator_name',
        'tiktok_link', 'tiktok_cid', 'instagram_link', 'facebook_link',
        'contact_email', 'contact_phone', 'contact_zalo',
        'followers', 'gmv', 'items', 'average_views', 'audience', 'advantages',
        'address', 'notes', 'content',
    ];

    public function __construct()
    {
        $this->model = new Creator();
    }

    public function getByIdInOrganization(string $id, ?string $orgHex)
    {
        $query = $this->model->select(self::SELECT_COLUMNS)
            ->where('id', $this->normalizeId($id));
        return $this->applyOrganizationScope($query, $orgHex)->first();
    }

    public function searchByName(string $name, ?string $orgHex)
    {
        $query = $this->model->select([
            'id', 'creator_name', 'contact_phone', 'contact_email', 'contact_zalo'
        ])->where('creator_name', 'LIKE', "%{$name}%");
        return $this->applyOrganizationScope($query, $orgHex)->limit(100)->get();
    }

    public function create(array $data)
    {
        return $this->model->create($data);
    }

    public function batchInsert(array $records): int
    {
        if (empty($records)) return 0;

        $inserted = Creator::insert($records);
        return $inserted ? count($records) : 0;
    }

    public function updateInOrganization(string $id, array $data, ?string $orgHex)
    {
        $query = $this->model->where('id', $this->normalizeId($id));
        $creator = $this->applyOrganizationScope($query, $orgHex)->first();

        if ($creator) {
            $creator->update($data);
        }

        return $creator;
    }

    public function deleteInOrganization(string $id, ?string $orgHex)
    {
        $query = $this->model->where('id', $this->normalizeId($id));
        return $this->applyOrganizationScope($query, $orgHex)->delete();
    }

    public function deleteMultipleInOrganization(array $ids, ?string $orgHex): int
    {
        $binIds = array_map([$this, 'normalizeId'], $ids);
        $query = $this->model->whereIn('id', $binIds);
        return $this->applyOrganizationScope($query, $orgHex)->delete();
    }

    public function paginateByOrganization(?string $orgHex, int $perPage = 15)
    {
        $query = $this->model->select(self::SELECT_COLUMNS);
        $query = $this->applyOrganizationScope($query, $orgHex);
        return $query->orderBy('created_at', 'desc')->paginate($perPage);
    }

    public function paginateFilteredByOrganization(?string $orgHex, array $filters, int $perPage = 10)
    {
        $safePerPage = max(1, min(100, $perPage));

        $query = $this->model->select(self::SELECT_COLUMNS)
            ->withCount('bookings');
        $query = $this->applyOrganizationScope($query, $orgHex);

        $keyword = trim((string)($filters['keyword'] ?? ''));
        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($keyword, $like) {
                $q->where('creator_name', 'LIKE', $like)
                    ->orWhere('audience', 'LIKE', $like)
                    ->orWhere('advantages', 'LIKE', $like)
                    ->orWhere('contact_phone', 'LIKE', $like)
                    ->orWhere('contact_email', 'LIKE', $like)
                    ->orWhere('contact_zalo', 'LIKE', $like);
            });
        }

        $contactColumns = [
            'phone' => 'contact_phone',
            'email' => 'contact_email',
            'zalo' => 'contact_zalo',
        ];
        $contacts = is_array($filters['contacts'] ?? null) ? $filters['contacts'] : [];
        $contacts = array_values(array_filter($contacts, fn ($v) => isset($contactColumns[$v])));
        if (!empty($contacts)) {
            $query->where(function ($q) use ($contacts, $contactColumns) {
                foreach ($contacts as $index => $contactKey) {
                    $column = $contactColumns[$contactKey];
                    $method = $index === 0 ? 'where' : 'orWhere';
                    $q->{$method}(function ($sq) use ($column) {
                        $sq->whereNotNull($column)->where($column, '<>', '');
                    });
                }
            });
        }

        $content = trim((string)($filters['content'] ?? ''));
        if ($content !== '') {
            $query->whereRaw('LOWER(content) LIKE ?', ['%' . mb_strtolower($content) . '%']);
        }

        $advantages = is_array($filters['advantages'] ?? null) ? $filters['advantages'] : [];
        $advantages = array_values(array_filter($advantages, fn ($v) => is_string($v) && trim($v) !== ''));
        if (!empty($advantages)) {
            $query->where(function ($q) use ($advantages) {
                foreach ($advantages as $index => $value) {
                    $value = trim($value);
                    $method = $index === 0 ? 'where' : 'orWhere';
                    $q->{$method}(function ($sq) use ($value) {
                        $sq->whereRaw('LOWER(advantages) LIKE ?', ['%' . mb_strtolower($value) . '%']);
                        $short = preg_replace('/^(live)\s*stream$/i', 'live', $value);
                        if ($short !== $value) {
                            $sq->orWhereRaw('LOWER(advantages) LIKE ?', ['%' . mb_strtolower($short) . '%']);
                        }
                    });
                }
            });
        }

        $ranges = [
            'followers' => ['min' => 'followers_min', 'max' => 'followers_max'],
            'gmv' => ['min' => 'gmv_min', 'max' => 'gmv_max'],
            'items' => ['min' => 'items_min', 'max' => 'items_max'],
        ];
        foreach ($ranges as $column => $keys) {
            $min = $filters[$keys['min']] ?? null;
            $max = $filters[$keys['max']] ?? null;
            if ($min !== null && $min !== '') {
                $query->where($column, '>=', (int)$min);
            }
            if ($max !== null && $max !== '') {
                $query->where($column, '<=', (int)$max);
            }
        }

        $sortMap = [
            'creator_name' => 'creator_name',
            'followers' => 'followers',
            'gmv' => 'gmv',
            'items' => 'items',
            'average_views' => 'average_views',

            'campaigns_count' => 'bookings_count',
            'bookings_count' => 'bookings_count',
            'created_at' => 'created_at',
        ];
        $sortField = $filters['sort_field'] ?? null;
        $sortDirection = strtolower((string)($filters['sort_direction'] ?? 'asc')) === 'desc' ? 'desc' : 'asc';

        if (is_string($sortField) && isset($sortMap[$sortField])) {
            $query->orderBy($sortMap[$sortField], $sortDirection);
        } else {
            $query->orderBy('created_at', 'desc');
        }

        return $query->paginate($safePerPage);
    }

    public function getAllNamesByOrganization(?string $orgHex): array
    {
        $query = $this->model->select('creator_name');
        $query = $this->applyOrganizationScope($query, $orgHex);
        return $query->pluck('creator_name')
            ->map(fn ($name) => mb_strtolower(trim($name), 'UTF-8'))
            ->toArray();
    }

    public function getMetricsByOrganization(?string $orgHex): array
    {
        $orgBin = $orgHex ? \App\Support\IdFormatter::hexToBinary($orgHex) : null;

        $metrics = DB::table('creators')
            ->selectRaw('
                COUNT(*) as total,
                SUM(CASE WHEN contact_phone IS NOT NULL AND contact_phone <> \'\' THEN 1 ELSE 0 END) as with_phone,
                SUM(CASE WHEN facebook_link IS NOT NULL AND facebook_link <> \'\' THEN 1 ELSE 0 END) as with_facebook,
                SUM(CASE WHEN instagram_link IS NOT NULL AND instagram_link <> \'\' THEN 1 ELSE 0 END) as with_instagram,
                SUM(CASE WHEN contact_email IS NOT NULL AND contact_email <> \'\' THEN 1 ELSE 0 END) as with_email,
                SUM(
                    CASE WHEN 
                        (contact_phone IS NOT NULL AND contact_phone <> \'\') OR
                        (contact_email IS NOT NULL AND contact_email <> \'\') OR
                        (contact_zalo IS NOT NULL AND contact_zalo <> \'\')
                    THEN 1 ELSE 0 END
                ) as reachable
            ')
            ->when($orgBin, function ($q) use ($orgBin) {
                return $q->where('organization_id', $orgBin);
            })
            ->first();

        $total = (int)($metrics->total ?? 0);

        $inCampaign = DB::table('creators')
            ->whereExists(function ($q) {
                $q->select(DB::raw(1))
                    ->from('bookings')
                    ->whereRaw('bookings.creator_id = creators.id');
            })
            ->when($orgBin, function ($q) use ($orgBin) {
                return $q->where('organization_id', $orgBin);
            })
            ->count();

        $notInCampaign = max(0, $total - $inCampaign);

        return [
            'Tổng KOC' => $total,
            'KOC có SDT' => (int)($metrics->with_phone ?? 0),
            'KOC có Facebook' => (int)($metrics->with_facebook ?? 0),
            'KOC có Instagram' => (int)($metrics->with_instagram ?? 0),
            'KOC có Gmail' => (int)($metrics->with_email ?? 0),
            'KOC có thể liên hệ ngay' => (int)($metrics->reachable ?? 0),
            'KOC đã vào chiến dịch' => $inCampaign,
            'KOC chưa vào chiến dịch' => $notInCampaign,
        ];
    }
}
