use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

function jline($tag,$data){ echo "@@$tag@@".json_encode($data,JSON_UNESCAPED_UNICODE)."\n"; }

// ---------- public curriculum maps (status_id = 3 only) ----------
$pubSkill = DB::table('skills')->where('status_id',3)->pluck('skill','id'); // id=>name
$pubField = DB::table('fields')->where('status_id',3)->pluck('field','id'); // id=>name
$levels   = DB::table('levels')->orderBy('id')->pluck('level','id');        // id=>name

// public tracks that also have a public field
$tracks = DB::table('tracks')->where('status_id',3)
    ->whereIn('field_id', array_keys($pubField->toArray()))
    ->get(['id','level_id','field_id']);
$trackLF = [];
foreach ($tracks as $t) $trackLF[$t->id] = [$t->level_id,$t->field_id];

// skill -> set of (level_id, field_id) via public skill_track
$st = DB::table('skill_track')
    ->whereIn('skill_id', array_keys($pubSkill->toArray()))
    ->whereIn('track_id', array_keys($trackLF))
    ->get(['skill_id','track_id']);
$skillLF = []; // skill_id => [ "lid|fid" => [lid,fid] ]
foreach ($st as $r) {
    [$lid,$fid] = $trackLF[$r->track_id];
    $skillLF[$r->skill_id]["$lid|$fid"] = [$lid,$fid];
}

// ---------- 1. schema ----------
jline('cols', Schema::getColumnListing('questions'));
jline('types', DB::table('types')->orderBy('id')->get(['id','type'])->map(fn($x)=>[$x->id,$x->type]));
jline('statuses', DB::table('statuses')->orderBy('id')->get(['id','status'])->map(fn($x)=>[$x->id,$x->status]));
jline('curriculum', [
    'public_fields'=>count($pubField),'public_tracks'=>count($trackLF),
    'public_skills'=>count($pubSkill),'mapped_skills'=>count($skillLF),
    'levels'=>$levels,
]);

// ---------- iterate questions once ----------
$total=0; $mapped=0; $unmapped=0;
$byLevel=[]; $byField=[]; $byLF=[]; $byType=[];          // counts
$normByField=[]; $normByLevel=[]; $normGlobal=[];        // dup detection
$wordByLevel=[];                                         // [lid=>['word'=>n,'calc'=>n]]

$WORDRE = '/\b(how many|how much|find the|altogether|in total|times as|more than|fewer than|less than|left|remaining|each|share[sd]?|bought|sold|sells?|spent|cost[s]?|pay[s]?|paid|saved?|distance|speed|average|ratio|perimeter|area of|filled|poured|recipe|total of|together)\b/i';

DB::table('questions')->select('id','skill_id','type_id','question')
  ->orderBy('id')->chunk(2000, function($rows) use (
    &$total,&$mapped,&$unmapped,&$byLevel,&$byField,&$byLF,&$byType,
    &$normByField,&$normByLevel,&$normGlobal,&$wordByLevel,$skillLF,$WORDRE){
    foreach ($rows as $q) {
        $total++;
        $byType[$q->type_id] = ($byType[$q->type_id] ?? 0) + 1;
        if (empty($q->skill_id) || !isset($skillLF[$q->skill_id])) { $unmapped++; continue; }
        $mapped++;
        $lfs = $skillLF[$q->skill_id];

        // normalize text
        $s = strip_tags((string)$q->question);
        $s = preg_replace('/\[\?\]|_{2,}/',' ',$s);
        $s = preg_replace('/\\\\[a-zA-Z]+|[\$\\\\(){}\[\]]/',' ',$s);
        $s = strtolower(preg_replace('/[^a-z0-9]+/i',' ', $s));
        $s = trim(preg_replace('/\s+/',' ',$s));
        $words = $s === '' ? 0 : count(explode(' ',$s));
        $isWord = ($words >= 18) || preg_match($WORDRE, $s);

        $seenL=[]; $seenF=[];
        foreach ($lfs as [$lid,$fid]) {
            if(!isset($seenL[$lid])){ $byLevel[$lid]=($byLevel[$lid]??0)+1; $seenL[$lid]=1;
                $wordByLevel[$lid]['word']=$wordByLevel[$lid]['word']??0;
                $wordByLevel[$lid]['calc']=$wordByLevel[$lid]['calc']??0;
                $wordByLevel[$lid][$isWord?'word':'calc']++;
                if($s!=='') $normByLevel[$lid][$s]=($normByLevel[$lid][$s]??0)+1;
            }
            if(!isset($seenF[$fid])){ $byField[$fid]=($byField[$fid]??0)+1; $seenF[$fid]=1; }
            $byLF["$lid|$fid"]=($byLF["$lid|$fid"]??0)+1;
        }
        if($s!==''){
            $normGlobal[$s]=($normGlobal[$s]??0)+1;
            // attribute to first field for per-topic dup
            $ff = array_values($lfs)[0][1];
            $normByField[$ff][$s]=($normByField[$ff][$s]??0)+1;
        }
    }
});

jline('totals',['total'=>$total,'mapped'=>$mapped,'unmapped'=>$unmapped]);
jline('byType',$byType);
jline('byLevel',$byLevel);
jline('byField',$byField);
jline('byLF',$byLF);

// ---------- dup summaries ----------
function dupRate($arr){ // arr: norm=>count
    $tot=array_sum($arr); $dupQ=0; $clusters=0;
    foreach($arr as $c){ if($c>1){ $dupQ+=$c; $clusters++; } }
    return [$tot,$dupQ,$clusters, $tot? round($dupQ*100/$tot,1):0];
}
$gd = dupRate($normGlobal);
jline('dupGlobal',['total'=>$gd[0],'dupQ'=>$gd[1],'clusters'=>$gd[2],'pct'=>$gd[3]]);
$fieldDup=[]; foreach($normByField as $fid=>$a){ $r=dupRate($a); $fieldDup[$fid]=['n'=>$r[0],'dupPct'=>$r[3],'clusters'=>$r[2]]; }
jline('dupByField',$fieldDup);
$levelDup=[]; foreach($normByLevel as $lid=>$a){ $r=dupRate($a); $levelDup[$lid]=['n'=>$r[0],'dupPct'=>$r[3]]; }
jline('dupByLevel',$levelDup);
// top duplicate clusters globally
arsort($normGlobal); $topDup=[]; $i=0;
foreach($normGlobal as $txt=>$c){ if($c<2) break; if($i++>=12) break; $topDup[]=[ $c, mb_substr($txt,0,80) ]; }
jline('topDup',$topDup);

// ---------- word density P3-P6 ----------
jline('wordByLevel',$wordByLevel);
echo "@@DONE@@\n";