<?php

namespace App\Services;

use App\Enums\BookingStatus;
use App\Enums\CampaignStatus;
use App\Models\Campaign;
use App\Models\Booking;
use App\Models\Creator;
use App\Models\Video;
use App\Support\IdFormatter;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class DashboardService
{
    protected ?string $orgBin = null;

    /** @var array Bảng màu dùng chung cho chart, tránh tạo mới mỗi lần gọi */
    private const PALETTE = ['#0e6fba', '#1e8e4e', '#7c5cbf', '#c8302e', '#f4a100', '#e63946', '#2ec4b6', '#8a7aa0'];

    protected function setOrgBin(?string $orgHex): void
    {
        $this->orgBin = $orgHex ? IdFormatter::hexToBinary($orgHex) : null;
    }

    public function getAllDashboardData(
        ?string $orgHex,
        ?string $startDate,
        ?string $endDate,
        ?string $month,
        ?string $year,
        string $rangeMode = 'range'
    ): array {
        $this->setOrgBin($orgHex);
        $cacheKey = $this->buildCacheKey($orgHex, $startDate, $endDate, $month, $year, $rangeMode);

        return Cache::remember($cacheKey, 60, function () use ($startDate, $endDate, $month, $year, $rangeMode) {
            $range = $this->parseDateRanges($startDate, $endDate, $month, $year, $rangeMode);
            $prevRange = $range['prev'] ?? null;
            return [
                'campaign_metrics' => $this->getCampaignMetrics(),
                'video_metrics'    => $this->getVideoMetrics(),
                'activity_data'    => $this->getActivityMetrics($range['rs'] ?? null, $range['re'] ?? null),
                'activity_prev'    => $this->getActivityMetrics(
                    $prevRange ? new \DateTime($prevRange['start']) : null,
                    $prevRange ? (new \DateTime($prevRange['end']))->modify('+1 day') : null
                ),
                'status_chart'     => $this->getStatusChartData($range),
                'cost_chart'       => $this->getCostChartData($range),
                'campaign_table'   => $this->getCampaignTableData($range),
            ];
        });
    }

    /**
     * Parse date ranges: current, month, prev.
     * Trích xuất thành phương thức riêng để tái sử dụng, tránh lặp logic parse DateTime ở 4 method.
     */
    private function parseDateRanges(
        ?string $startDate,
        ?string $endDate,
        ?string $month,
        ?string $year,
        string $rangeMode = 'range'
    ): array {
        $rs = $startDate && $rangeMode === 'range' ? new \DateTime($startDate) : null;
        $re = $endDate && $rangeMode === 'range'
            ? (clone new \DateTime($endDate))->modify('+1 day')
            : null;

        $mrStart = ($month && $year) ? new \DateTime("{$year}-{$month}-01") : null;
        $mrEnd   = $mrStart ? (clone $mrStart)->modify('+1 month') : null;

        $prev = null;
        if ($rs && $re) {
            $len = $re->diff($rs)->days + 1;
            $prevEnd = (clone $rs)->modify('-1 day');
            $prevStart = (clone $prevEnd)->modify('-' . ($len - 1) . ' days');
            $prev = [
                'start' => $prevStart->format('Y-m-d'),
                'end'   => $prevEnd->format('Y-m-d'),
            ];
        }

        return compact('rs', 're', 'mrStart', 'mrEnd', 'prev');
    }

    // ──────────────────────────────────────────────
    //  CAMPAIGN METRICS
    // ──────────────────────────────────────────────

    public function getCampaignMetrics(): array
    {
        $campRow = Campaign::query()
            ->selectRaw('COUNT(*) as total_campaigns')
            ->selectRaw('SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as running_campaigns', [CampaignStatus::ACTIVE->value])
            ->when($this->orgBin, fn($q) => $q->where('organization_id', $this->orgBin))
            ->first();

        $bookRow = Booking::query()
            ->selectRaw('COALESCE(SUM(CASE WHEN status = ? THEN 1 ELSE 0 END), 0) as total_kocs', [BookingStatus::COMPLETED->value])
            ->selectRaw('COALESCE(SUM(cast_cost), 0) as total_cast_cost')
            ->selectRaw('COALESCE(SUM(sample_cost), 0) as total_sample_cost')
            ->selectRaw('COALESCE(SUM(shipping_cost), 0) as total_shipping_cost')
            ->when($this->orgBin, fn($q) => $q->where('organization_id', $this->orgBin))
            ->first();

        $totalCast   = (int) ($bookRow->total_cast_cost ?? 0);
        $totalSample = (int) ($bookRow->total_sample_cost ?? 0);
        $totalShip   = (int) ($bookRow->total_shipping_cost ?? 0);

        return [
            'total_campaigns'    => (int) ($campRow->total_campaigns ?? 0),
            'running_campaigns'  => (int) ($campRow->running_campaigns ?? 0),
            'total_kocs'         => (int) ($bookRow->total_kocs ?? 0),
            'total_cast_cost'    => $totalCast,
            'total_sample_cost'  => $totalSample,
            'total_shipping_cost'=> $totalShip,
            'total_all_costs'    => $totalCast + $totalSample + $totalShip,
        ];
    }

    // ──────────────────────────────────────────────
    //  VIDEO METRICS
    // ──────────────────────────────────────────────

    public function getVideoMetrics(): array
    {
        $bookRows = DB::table('bookings')
            ->leftJoin('booking_videos', 'booking_videos.booking_id', '=', 'bookings.id')
            ->selectRaw('
                COUNT(DISTINCT bookings.id) as total_kocs,
                COALESCE(SUM(booking_videos.number_of_videos), 0) as total_videos
            ')
            ->where('bookings.status', BookingStatus::COMPLETED->value)
            ->where('bookings.type', 'video')
            ->where('booking_videos.number_of_videos', '>', 0)
            ->when($this->orgBin, fn($q) => $q->where('bookings.organization_id', $this->orgBin))
            ->first();

        $videoStats = Video::query()
            ->selectRaw('COALESCE(SUM(CASE WHEN status = ? THEN 1 ELSE 0 END), 0) as published', ['published'])
            ->whereHas('booking', function ($q) {
                $q->where('bookings.status', BookingStatus::COMPLETED->value)
                   ->where('bookings.type', 'video')
                   ->when($this->orgBin, fn($qb) => $qb->where('organization_id', $this->orgBin));
            })
            ->first();

        $total_kocs   = (int) ($bookRows->total_kocs ?? 0);
        $total_videos = (int) ($bookRows->total_videos ?? 0);
        $published    = (int) ($videoStats->published ?? 0);
        $pending      = max(0, $total_videos - $published);

        return compact('total_kocs', 'total_videos', 'published', 'pending');
    }

    // ──────────────────────────────────────────────
    //  ACTIVITY METRICS
    // ──────────────────────────────────────────────

    /**
     * Tính activity metrics trong khoảng ngày.
     * contacted/sent/booked gộp 3 query bookings thành 1 với CASE WHEN.
     *
     * @param \DateTime|null $rs Thời điểm bắt đầu (current hoặc prev)
     * @param \DateTime|null $re Thời điểm kết thúc (current hoặc prev, +1 day)
     */
    public function getActivityMetrics(?\DateTime $rs, ?\DateTime $re): array
    {
        $hasDate = $rs && $re;

        $invited = Creator::query()
            ->when($this->orgBin, fn($q) => $q->where('organization_id', $this->orgBin))
            ->when($hasDate, fn($q) => $q->whereBetween('created_at', [$rs, $re]))
            ->count();

        // Gộp contacted/sent/booked từ bookings thành 1 query
        // Khi không có date range, CASE WHEN sẽ không lọc → đếm toàn bộ
        $bookingMetrics = Booking::query()
            ->selectRaw('COUNT(*) as total_all')
            ->selectRaw('COUNT(CASE WHEN ? IS NULL OR (negotiating_at >= ? AND negotiating_at < ?) THEN 1 END) as contacted', [$hasDate ? 1 : null, $rs, $re])
            ->selectRaw('COUNT(CASE WHEN ? IS NULL OR (completed_at >= ? AND completed_at < ?) THEN 1 END) as sent', [$hasDate ? 1 : null, $rs, $re])
            ->selectRaw('COUNT(CASE WHEN ? IS NULL OR (created_at >= ? AND created_at < ?) THEN 1 END) as booked', [$hasDate ? 1 : null, $rs, $re])
            ->when($this->orgBin, fn($q) => $q->where('organization_id', $this->orgBin))
            ->first();

        $videos = DB::table('videos')
            ->join('bookings', 'videos.booking_id', '=', 'bookings.id')
            ->when($this->orgBin, fn($q) => $q->where('bookings.organization_id', $this->orgBin))
            ->when($hasDate, fn($q) => $q->whereBetween('videos.created_at', [$rs, $re]))
            ->count();

        return [
            'invited'   => $invited,
            'contacted' => (int) ($bookingMetrics->contacted ?? 0),
            'sent'      => (int) ($bookingMetrics->sent ?? 0),
            'booked'    => (int) ($bookingMetrics->booked ?? 0),
            'videos'    => $videos,
        ];
    }

    // ──────────────────────────────────────────────
    //  CHART DATA
    // ──────────────────────────────────────────────

    public function getStatusChartData(array $range): array
    {
        $statusMap = [
            'draft'     => ['label' => 'Chuẩn bị', 'color' => '#0e6fba'],
            'active'    => ['label' => 'Đang chạy', 'color' => '#1e8e4e'],
            'ongoing'   => ['label' => 'Đang chạy', 'color' => '#1e8e4e'],
            'completed' => ['label' => 'Hoàn thành', 'color' => '#7c5cbf'],
            'paused'    => ['label' => 'Tạm dừng', 'color' => '#f4a100'],
        ];

        $rangeStart = $range['rs'] ?? null;
        $rangeEnd   = $range['re'] ?? null;
        $mrStart    = $range['mrStart'] ?? null;
        $mrEnd      = $range['mrEnd'] ?? null;
        $hasRange   = $rangeStart && $rangeEnd;
        $hasMonth   = $mrStart && $mrEnd;

        $rows = Campaign::query()
            ->selectRaw('status, COUNT(*) as total')
            ->when($this->orgBin, fn($q) => $q->where('organization_id', $this->orgBin))
            ->when($hasRange, function ($q) use ($rangeStart, $rangeEnd) {
                $q->where('start_date', '<=', $rangeEnd->format('Y-m-d'))
                  ->where(function ($sub) use ($rangeStart) {
                      $sub->whereNull('end_date')
                          ->orWhere('end_date', '>=', $rangeStart->format('Y-m-d'));
                  });
            })
            ->when(!$hasRange && $hasMonth, function ($q) use ($mrStart, $mrEnd) {
                $q->where('start_date', '<=', $mrEnd->format('Y-m-d'))
                  ->where(function ($sub) use ($mrStart) {
                      $sub->whereNull('end_date')
                          ->orWhere('end_date', '>=', $mrStart->format('Y-m-d'));
                  });
            })
            ->groupBy('status')
            ->pluck('total', 'status');

        if ($rows->isEmpty()) {
            return [];
        }

        return $rows->map(function ($count, $status) use ($statusMap) {
            $info = $statusMap[$status] ?? ['label' => $status, 'color' => '#94a3b8'];
            return [
                'label' => $info['label'],
                'value' => (int) $count,
                'color' => $info['color'],
            ];
        })->sortByDesc('value')->values()->toArray();
    }

    public function getCostChartData(array $range): array
    {
        $rs      = $range['rs'] ?? null;
        $re      = $range['re'] ?? null;
        $mrStart = $range['mrStart'] ?? null;
        $mrEnd   = $range['mrEnd'] ?? null;
        $hasRange = $rs && $re;
        $hasMonth = $mrStart && $mrEnd;

        $rows = DB::table('bookings')
            ->selectRaw('
                campaigns.id,
                campaigns.campaign_name,
                COALESCE(SUM(bookings.cast_cost), 0) as cast_cost,
                COALESCE(SUM(bookings.sample_cost), 0) as sample_cost,
                COALESCE(SUM(bookings.shipping_cost), 0) as shipping_cost,
                COALESCE(SUM(bookings.cast_cost + bookings.sample_cost + bookings.shipping_cost), 0) as total_cost
            ')
            ->join('campaigns', 'bookings.campaign_id', '=', 'campaigns.id')
            ->when($this->orgBin, fn($q) => $q->where('bookings.organization_id', $this->orgBin))
            ->when($hasRange, fn($q) => $q->whereBetween('bookings.completed_at', [$rs, $re]))
            ->when(!$hasRange && $hasMonth, fn($q) => $q->whereBetween('bookings.completed_at', [$mrStart, $mrEnd]))
            ->groupBy('campaigns.id', 'campaigns.campaign_name')
            ->having('total_cost', '>', 0)
            ->orderByDesc('total_cost')
            ->limit(50)
            ->get();

        if ($rows->isEmpty()) {
            return [
                'segments'    => [],
                'type_totals' => ['cast' => 0, 'sample' => 0, 'ship' => 0],
                'grand_total' => 0,
                'empty'       => true,
            ];
        }

        $typeTotals = ['cast' => 0, 'sample' => 0, 'ship' => 0];
        $segs = [];

        foreach ($rows as $i => $row) {
            $cast   = (int) $row->cast_cost;
            $sample = (int) $row->sample_cost;
            $ship   = (int) $row->shipping_cost;

            $typeTotals['cast']   += $cast;
            $typeTotals['sample'] += $sample;
            $typeTotals['ship']   += $ship;

            $segs[] = [
                'label' => mb_strlen($row->campaign_name) > 24
                    ? mb_substr($row->campaign_name, 0, 24) . '…'
                    : $row->campaign_name,
                'value' => (int) $row->total_cost,
                'color' => self::PALETTE[$i % count(self::PALETTE)],
                '_costs' => compact('cast', 'sample', 'ship'),
            ];
        }

        $grandTotal = $typeTotals['cast'] + $typeTotals['sample'] + $typeTotals['ship'];

        return [
            'segments'    => $segs,
            'type_totals' => $typeTotals,
            'grand_total' => $grandTotal,
            'empty'       => false,
        ];
    }

    // ──────────────────────────────────────────────
    //  CAMPAIGN TABLE
    // ──────────────────────────────────────────────

    public function getCampaignTableData(array $range): array
    {
        $rs      = $range['rs'] ?? null;
        $re      = $range['re'] ?? null;
        $mrStart = $range['mrStart'] ?? null;
        $mrEnd   = $range['mrEnd'] ?? null;
        $prev    = $range['prev'] ?? null;
        $hasRange = $rs && $re;
        $hasMonth = $mrStart && $mrEnd;
        $hasPrev  = $prev && isset($prev['start'], $prev['end']);

        $ps = $hasPrev ? new \DateTime($prev['start']) : null;
        $pe = $hasPrev ? (new \DateTime($prev['end']))->modify('+1 day') : null;

        $rows = DB::table('bookings')
            ->selectRaw('
                campaigns.id,
                campaigns.campaign_name,
                campaigns.created_at as campaign_created_at,
                COALESCE(SUM(CASE WHEN bookings.status = ? THEN 1 ELSE 0 END), 0) as total_bookings,

                -- Month activity
                COALESCE(SUM(CASE WHEN bookings.created_at >= ? AND bookings.created_at < ? THEN 1 ELSE 0 END), 0) as month_invited,
                COALESCE(SUM(CASE WHEN bookings.negotiating_at >= ? AND bookings.negotiating_at < ? THEN 1 ELSE 0 END), 0) as month_contacted,
                COALESCE(SUM(CASE WHEN bookings.completed_at >= ? AND bookings.completed_at < ? THEN 1 ELSE 0 END), 0) as month_sent,

                -- Current range activity
                COALESCE(SUM(CASE WHEN bookings.created_at >= ? AND bookings.created_at < ? THEN 1 ELSE 0 END), 0) as cur_invited,
                COALESCE(SUM(CASE WHEN bookings.negotiating_at >= ? AND bookings.negotiating_at < ? THEN 1 ELSE 0 END), 0) as cur_contacted,
                COALESCE(SUM(CASE WHEN bookings.completed_at >= ? AND bookings.completed_at < ? THEN 1 ELSE 0 END), 0) as cur_sent,

                -- Prev range activity
                COALESCE(SUM(CASE WHEN bookings.created_at >= ? AND bookings.created_at < ? THEN 1 ELSE 0 END), 0) as prv_invited,
                COALESCE(SUM(CASE WHEN bookings.negotiating_at >= ? AND bookings.negotiating_at < ? THEN 1 ELSE 0 END), 0) as prv_contacted,
                COALESCE(SUM(CASE WHEN bookings.completed_at >= ? AND bookings.completed_at < ? THEN 1 ELSE 0 END), 0) as prv_sent,

                COALESCE((SELECT COUNT(*) FROM videos WHERE videos.booking_id IN (SELECT b2.id FROM bookings b2 WHERE b2.campaign_id = campaigns.id) AND videos.status = ?), 0) as published_videos,
                COALESCE((SELECT SUM(bv.number_of_videos) FROM booking_videos bv INNER JOIN bookings b3 ON b3.id = bv.booking_id WHERE b3.campaign_id = campaigns.id AND b3.status = ?), 0) as expected_videos
            ', [
                BookingStatus::COMPLETED->value,
                $mrStart, $mrEnd, $mrStart, $mrEnd, $mrStart, $mrEnd,
                $rs, $re, $rs, $re, $rs, $re,
                $ps, $pe, $ps, $pe, $ps, $pe,
                'published',
                BookingStatus::COMPLETED->value,
            ])
            ->join('campaigns', 'bookings.campaign_id', '=', 'campaigns.id')
            ->when($this->orgBin, fn($q) => $q->where('bookings.organization_id', $this->orgBin))
            ->groupBy('campaigns.id', 'campaigns.campaign_name', 'campaigns.created_at')
            ->orderByDesc('campaigns.created_at')
            ->get();

        $data = [];
        foreach ($rows as $row) {
            $bookings        = (int) $row->total_bookings;
            $expectedVideos   = (int) ($row->expected_videos ?? 0);
            $publishedVideos = (int) ($row->published_videos ?? 0);
            $binCampId       = $row->id;

            $data[] = [
                'id'               => is_string($binCampId) && strlen($binCampId) === 16
                    ? bin2hex($binCampId) : (string) $binCampId,
                'campaign_name'    => $row->campaign_name ?? '',
                'created_at'       => $row->campaign_created_at
                    ? (is_string($row->campaign_created_at)
                        ? $row->campaign_created_at
                        : $row->campaign_created_at->format('Y-m-d H:i:s'))
                    : '',
                'month'            => [
                    'invited'   => (int) $row->month_invited,
                    'contacted' => (int) $row->month_contacted,
                    'sent'      => (int) $row->month_sent,
                ],
                'current'          => [
                    'invited'   => (int) $row->cur_invited,
                    'contacted' => (int) $row->cur_contacted,
                    'sent'      => (int) $row->cur_sent,
                ],
                'prev'             => [
                    'invited'   => (int) $row->prv_invited,
                    'contacted' => (int) $row->prv_contacted,
                    'sent'      => (int) $row->prv_sent,
                ],
                'bookings'         => $bookings,
                'expected_videos'  => $expectedVideos,
                'published_videos' => $publishedVideos,
                'booking_ratio'    => $bookings > 0 ? round(($expectedVideos / $bookings) * 100, 1) : 0,
                'progress_percent' => $expectedVideos > 0 ? round(($publishedVideos / $expectedVideos) * 100, 1) : 0,
            ];
        }

        return $data;
    }

    // ──────────────────────────────────────────────
    //  HELPERS
    // ──────────────────────────────────────────────

    private function buildCacheKey(
        ?string $orgHex,
        ?string $startDate,
        ?string $endDate,
        ?string $month,
        ?string $year,
        string $rangeMode = 'range'
    ): string {
        return implode(':', [
            'dashboard:v3',
            $orgHex ?? 'all',
            $startDate ?? 'na',
            $endDate ?? 'na',
            $month ?? 'na',
            $year ?? 'na',
            $rangeMode,
        ]);
    }
}