<?php
declare(strict_types=1);
namespace PpPhp\Core\Projects;
use PpPhp\Core\DatabaseConnection;use PpPhp\Core\Database\Dialect\DatabaseDialectRegistry;use PpPhp\Core\Database\Schema\TableDesignerService;

final class ProjectSchemaDriftService
{
    /** Types whose length/precision is genuinely user-chosen and worth comparing.
     *  For every other type (integers, TEXT/BLOB families, dates, JSON…) the "size"
     *  is either cosmetic (INT(11)) or implicit in the subtype (MEDIUMTEXT), and the
     *  designer (0) and INFORMATION_SCHEMA (precision / max length) report it
     *  differently — so comparing it only manufactures false drift. */
    private const SIZED_TYPES = ['VARCHAR','CHAR','VARBINARY','BINARY','DECIMAL','NUMERIC','DEC','FIXED'];
    private array $config;private TableDesignerService $designer;
    public function __construct(private string $projectDir,array $connection){$this->config=DatabaseConnection::normalize($connection);$pdo=DatabaseConnection::connect($this->config,$projectDir);$this->designer=new TableDesignerService($pdo,DatabaseDialectRegistry::forConfig($this->config),$this->config);}
    public function compare():array
    {
        $state=$this->state();$selected=array_values(array_unique(array_map('strval',(array)($state['selectedTables']??[]))));$schemas=(array)($state['tableSchemas']??[]);$live=[];$system=[];
        foreach($this->designer->listObjects() as $object){$name=(string)$object['name'];$row=['name'=>$name,'type'=>strtoupper((string)($object['type']??'TABLE'))];if($this->isSystem($name)){$system[]=$row;continue;}$live[$name]=$row;}
        $databaseOnly=[];$projectOnly=[];$changed=[];$unchanged=[];$needsRefresh=[];
        foreach($live as $name=>$object){if(!in_array($name,$selected,true)){$databaseOnly[]=$object;continue;}if(!isset($schemas[$name])||!is_array($schemas[$name])){$needsRefresh[]=$object;continue;}$details=$this->designer->describe($name);$liveFields=$this->fields((array)($details['columns']??[]));$savedFields=$this->fields((array)($schemas[$name]['fields']??[]));$diff=$this->fieldDiff($savedFields,$liveFields);if($diff['clean'])$unchanged[]=$object;else $changed[]=$object+['fields'=>$diff];}
        foreach($selected as $name)if(!isset($live[$name]))$projectOnly[]=['name'=>$name,'type'=>strtoupper((string)($schemas[$name]['type']??'TABLE')),'blueprint_available'=>$this->blueprintHas($name)];
        return ['dialect'=>$this->designer->dialectConfig(),'database_only'=>array_values($databaseOnly),'project_only'=>$projectOnly,'changed'=>$changed,'needs_refresh'=>$needsRefresh,'unchanged'=>$unchanged,'system_count'=>count($system),'counts'=>['database_only'=>count($databaseOnly),'project_only'=>count($projectOnly),'changed'=>count($changed),'needs_refresh'=>count($needsRefresh),'unchanged'=>count($unchanged)],'clean'=>!$databaseOnly&&!$projectOnly&&!$changed&&!$needsRefresh];
    }
    public function import(array $names):array
    {
        $state=$this->state();$objects=[];foreach($this->designer->listObjects() as $o)$objects[(string)$o['name']]=strtoupper((string)($o['type']??'TABLE'));$imported=[];$skipped=[];
        foreach($this->names($names) as $name){if(!isset($objects[$name])||$this->isSystem($name)){$skipped[]=$name;continue;}$details=$this->designer->describe($name);$state['selectedTables']=$this->append((array)($state['selectedTables']??[]),$name);$state['tableSchemas'][$name]=['name'=>$name,'type'=>$objects[$name],'fields'=>$this->fields((array)($details['columns']??[])),'updated_at'=>date(DATE_ATOM)];if($objects[$name]==='VIEW')$state['views'][$name]=($state['views'][$name]??[])+['name'=>$name,'type'=>'VIEW','source'=>'database'];if(!$this->hasPosition((array)($state['positions']??[]),$name))$state['positions'][]=['table'=>$name,'left'=>40+(count($imported)%4)*260,'top'=>40+intdiv(count($imported),4)*170,'x'=>40+(count($imported)%4)*260,'y'=>40+intdiv(count($imported),4)*170];$imported[]=$name;}
        $this->save($state);$this->refreshBlueprint();return ['imported'=>$imported,'skipped'=>$skipped];
    }
    public function rebuild(bool $removeMissing=false):array
    {
        $state=$this->state();$objects=[];foreach($this->designer->listObjects() as $o)$objects[(string)$o['name']]=strtoupper((string)($o['type']??'TABLE'));$refreshed=[];$missing=[];
        foreach(array_values(array_map('strval',(array)($state['selectedTables']??[]))) as $name){if(!isset($objects[$name])){$missing[]=$name;continue;}$details=$this->designer->describe($name);$state['tableSchemas'][$name]=['name'=>$name,'type'=>$objects[$name],'fields'=>$this->fields((array)($details['columns']??[])),'updated_at'=>date(DATE_ATOM)];$refreshed[]=$name;}
        if($removeMissing&&$missing){$state['selectedTables']=array_values(array_diff((array)$state['selectedTables'],$missing));foreach($missing as $name){unset($state['tableSchemas'][$name],$state['views'][$name]);}$state['positions']=array_values(array_filter((array)$state['positions'],fn(array $p):bool=>!in_array((string)($p['table']??''),$missing,true)));$state['relations']=array_values(array_filter((array)$state['relations'],fn(array $r):bool=>!in_array((string)($r['master_table']??''),$missing,true)&&!in_array((string)($r['detail_table']??''),$missing,true)));}
        $this->save($state);$this->refreshBlueprint();return ['refreshed'=>$refreshed,'missing'=>$missing,'removed'=>$removeMissing?$missing:[]];
    }
    public function createMissing(array $names):array{$wanted=$this->names($names);$blueprint=$this->blueprint();$blueprint['tables']=array_values(array_filter((array)($blueprint['tables']??[]),fn(array $t):bool=>in_array((string)($t['name']??''),$wanted,true)));if(!$blueprint['tables'])throw new \InvalidArgumentException('None of the selected tables has a portable schema blueprint.');$result=(new ProjectSchemaBlueprintService())->apply($this->projectDir,$this->config,$blueprint);return $result;}
    private function state():array{$path=$this->projectDir.'/config/tables.json';$state=is_file($path)?json_decode((string)file_get_contents($path),true):[];return is_array($state)?$state:[];}
    private function save(array $state):void{$state['_state_version']=max(2,(int)($state['_state_version']??0));$state['_last_updated']=time();$path=$this->projectDir.'/config/tables.json';$tmp=tempnam(dirname($path),'.schema-sync-');if($tmp===false||file_put_contents($tmp,json_encode($state,JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE)."\n",LOCK_EX)===false||!rename($tmp,$path)){if(is_string($tmp)&&is_file($tmp))@unlink($tmp);throw new \RuntimeException('Could not atomically update the project table structures.');}}
    private function fields(array $columns):array{return array_values(array_filter(array_map(static function(array $c):array{return ['name'=>(string)($c['name']??$c['COLUMN_NAME']??''),'type'=>strtoupper((string)($c['type']??$c['DATA_TYPE']??'VARCHAR')),'size'=>(int)($c['size']??$c['CHARACTER_MAXIMUM_LENGTH']??$c['NUMERIC_PRECISION']??0),'scale'=>(int)($c['scale']??$c['NUMERIC_SCALE']??0),'nullable'=>array_key_exists('nullable',$c)?(bool)$c['nullable']:strtoupper((string)($c['IS_NULLABLE']??'YES'))==='YES','default_mode'=>strtoupper((string)($c['default_mode']??'NONE')),'default'=>$c['default']??$c['COLUMN_DEFAULT']??null,'default_expression'=>$c['default_expression']??null,'primary_key'=>!empty($c['primary_key'])||($c['COLUMN_KEY']??'')==='PRI','auto_increment'=>!empty($c['auto_increment'])||($c['EXTRA']??'')==='auto_increment','enum_values'=>(string)($c['enum_values']??'')];},$columns),fn(array $f):bool=>$f['name']!==''));}
    private function fieldDiff(array $saved,array $live):array{$a=[];$b=[];foreach($saved as $f)$a[strtolower($f['name'])]=$f;foreach($live as $f)$b[strtolower($f['name'])]=$f;$added=[];$removed=[];$modified=[];foreach($b as $k=>$f){if(!isset($a[$k]))$added[]=$f['name'];elseif($this->signature($a[$k])!==$this->signature($f))$modified[]=['name'=>$f['name'],'project'=>$a[$k],'database'=>$f];}foreach($a as $k=>$f)if(!isset($b[$k]))$removed[]=$f['name'];return ['added'=>$added,'removed'=>$removed,'modified'=>$modified,'clean'=>!$added&&!$removed&&!$modified];}
    private function signature(array $f):string{
        $type=strtoupper((string)($f['type']??''));
        // Only compare a size for types where the length/precision is user-chosen;
        // otherwise it is cosmetic or implicit in the type and just manufactures drift.
        $size=in_array($type,self::SIZED_TYPES,true)?(int)($f['size']??0):0;
        // "No default": the designer records an empty string, the database reports
        // NULL — collapse both so an unchanged column is not flagged as drifted.
        $default=$f['default']??null;if($default==='')$default=null;
        return json_encode([
            'type'=>$type,'size'=>$size,'scale'=>(int)($f['scale']??0),'nullable'=>(bool)($f['nullable']??false),
            'default_mode'=>strtoupper((string)($f['default_mode']??'NONE')),'default'=>$default,
            'primary_key'=>(bool)($f['primary_key']??false),'auto_increment'=>(bool)($f['auto_increment']??false),
            'enum_values'=>(string)($f['enum_values']??''),
        ],JSON_UNESCAPED_SLASHES);
    }
    private function blueprint():array{$p=$this->projectDir.'/config/schema-blueprint.json';$v=is_file($p)?json_decode((string)file_get_contents($p),true):[];return is_array($v)?$v:[];}private function blueprintHas(string $n):bool{foreach((array)($this->blueprint()['tables']??[])as $t)if((string)($t['name']??'')===$n)return true;return false;}private function refreshBlueprint():void{$old=$this->blueprint();$fresh=(new ProjectSchemaBlueprintService())->capture($this->projectDir,$this->config);$names=array_column((array)($fresh['tables']??[]),'name');$selected=array_map('strval',(array)($this->state()['selectedTables']??[]));foreach((array)($old['tables']??[])as $table){$name=(string)($table['name']??'');if($name!==''&&in_array($name,$selected,true)&&!in_array($name,$names,true)){$fresh['tables'][]=$table;$names[]=$name;}}file_put_contents($this->projectDir.'/config/schema-blueprint.json',json_encode($fresh,JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES)."\n",LOCK_EX);}
    private function isSystem(string $n):bool{return \PpPhp\Core\SystemTables::isSystem($n);}private function names(array $v):array{return array_values(array_unique(array_filter(array_map('strval',$v),fn(string $n):bool=>preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/',$n)===1)));}private function append(array $v,string $n):array{$v=array_values(array_map('strval',$v));if(!in_array($n,$v,true))$v[]=$n;return $v;}private function hasPosition(array $p,string $n):bool{foreach($p as $x)if((string)($x['table']??'')===$n)return true;return false;}
}
