<?php

namespace App\Services;

use App\Services\Sharing\ShareDefaults;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

/** Read actual orders so historical and not-yet-settled play does not depend on commission synchronization. */
class PlayedGameService
{
    public static function forMembers(array $memberIds, ?string $start = null, ?string $end = null): array
    {
        if ($memberIds === []) return [];

        $played = [];
        $schema = DB::connection()->getSchemaBuilder();
        // Game modules are optional; installations may not have every order table.
        if ($schema->hasTable('third_game_orders')) {
            $third = DB::table('third_game_orders as o')->join('users as u', function ($join) {
                $join->on('o.user_id', '=', 'u.id')->orWhere(function ($join) {
                    $join->whereNull('o.user_id')->on('o.member_id', '=', 'u.member_id');
                });
            })->whereIn('u.member_id', $memberIds)
                ->where(function ($query) {
                    $query->whereNull('o.member_id')->orWhere('o.member_id', '')->orWhereColumn('o.member_id', 'u.member_id');
                })
                ->whereIn('o.game_type', range(1, 7))->whereIn('o.status', [0, 1])->where('o.bet_amount', '>', 0);
            if ($start && $end) $third->whereBetween('o.bet_time', [$start, $end]);
            foreach ($third->distinct()->get(['u.member_id', 'o.game_type']) as $row) {
                $played[$row->member_id]['third:' . $row->game_type] = true;
            }
        }

        // sport_order.user_id stores the member ID, unlike third_game_orders.user_id (users.id).
        $sources = [
            ['sport_order', 'user_id', 'create_time', 'self:sport', 'status', [1], true],
            ['sport_game_order', 'member_id', 'created_at', 'self:sport', 'status', [0, 1, 2], false],
            ['lhc_order', 'member_id', 'created_at', 'self:lottery', 'lottery_status', [0, 1, 2], true],
            ['bets', 'member_id', 'created_at', 'self:lottery', 'status', [1, 2], false],
            ['jisu_game_order', 'member_id', 'created_at', 'self:lottery', 'status', [0, 1, 2], false],
        ];
        $startTimestamp = $start ? Carbon::parse($start, ShareDefaults::TIMEZONE)->timestamp : null;
        $endTimestamp = $end ? Carbon::parse($end, ShareDefaults::TIMEZONE)->timestamp : null;
        foreach ($sources as [$table, $memberColumn, $timeColumn, $category, $statusColumn, $statuses, $timestamp]) {
            if (!$schema->hasTable($table)) continue;
            $query = DB::table($table)->whereIn($memberColumn, $memberIds)
                ->whereIn($statusColumn, $statuses)->where('amount', '>', 0);
            // Historical lottery/sports tables may not contain this optional per-order flag.
            if (in_array($table, ['sport_order', 'lhc_order'], true) && $schema->hasColumn($table, 'is_faker')) {
                $query->where('is_faker', 0);
            }
            if ($table === 'sport_order') $query->where('pay_status', 1)->where('return_status', '<>', 2);
            if ($start && $end) {
                $query->whereBetween($timeColumn, $timestamp
                    ? [$startTimestamp, $endTimestamp]
                    : [$start, $end]);
            }
            foreach ($query->distinct()->pluck($memberColumn) as $memberId) $played[$memberId][$category] = true;
        }

        $result = [];
        foreach ($played as $memberId => $categories) {
            // Keep the same names and stable ordering as commission categories.
            foreach (ShareDefaults::categories() as $category => $label) {
                if (isset($categories[$category])) $result[$memberId][] = ['category' => $category, 'label' => $label];
            }
        }

        return $result;
    }
}