<?php
namespace Tempo\Purchasing\SgfSourcing;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Schema\Blueprint;
use Tempo\Purchasing\Delivery\Installation;

final class Schema
{
    public const RIGHTS=['purchasing_sourcing.prepare','purchasing_sourcing.evaluate','purchasing_sourcing.propose','purchasing_sourcing.confirm'];
    public const TABLES=['ss_suppliers','ss_processes','ss_events','ss_commands'];
    public static function hash():string{return hash_file('sha256',__FILE__);}
    public static function signature(ConnectionInterface $db):string{return Installation::signature($db,[...Installation::tables(),...self::TABLES]);}
    public static function compatible(ConnectionInterface $db):bool{
        $row=$db->table('sgc_installations')->where('module','sourcing')->first();
        return $row&&$row->revision===1&&hash_equals($row->migration_sha256,self::hash())&&hash_equals($row->schema_signature,self::signature($db));
    }
    public static function migrate(ConnectionInterface $db,string $backupHash,string $operator):array{
        if($db->getDriverName()!=='pgsql'||!preg_match('/^[a-f0-9]{64}$/D',$backupHash)||trim($operator)==='')throw new \RuntimeException('Backup e operador obrigatórios.');
        return $db->transaction(function()use($db,$backupHash,$operator){
            $db->select("select pg_advisory_xact_lock(hashtext('tempo.sourcing.install.v1'))");
            if($db->table('sgc_installations')->where('module','sourcing')->exists()){
                if(!self::compatible($db))throw new \RuntimeException('Schema de sourcing divergente.');
                return ['prepared'=>true,'replayed'=>true];
            }
            $old=$db->table('sgc_installations')->where('module','internal-requisitions')->first();
            if(!$old||$old->revision!==2||!hash_equals($old->schema_signature,Installation::signature($db,Installation::tables())))throw new \RuntimeException('Conferir schema anterior antes de ampliar catálogo.');
            $schema=$db->getSchemaBuilder();
            foreach(self::TABLES as $table){
                if($schema->hasTable($table))throw new \RuntimeException('Schema parcial: não sobrescrever.');
                $schema->create($table,function(Blueprint $t)use($table){
                    $t->id();foreach(['tenant_id','company_id','unit_id'] as $key)$t->unsignedBigInteger($key);
                    if($table==='ss_suppliers'){$t->string('reference');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','reference']);}
                    elseif($table==='ss_processes'){$t->unsignedBigInteger('request_id');$t->unsignedInteger('version');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','request_id']);}
                    elseif($table==='ss_events'){$t->unsignedBigInteger('request_id');$t->unsignedInteger('version');$t->unsignedBigInteger('actor_id');$t->string('action');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','request_id','version']);}
                    else{$t->unsignedBigInteger('actor_id');$t->uuid('operation_key');$t->string('request_hash',64);$t->jsonb('result');$t->unique(['tenant_id','company_id','unit_id','actor_id','operation_key']);}
                    $t->timestampTz('created_at');
                });
                $db->statement("ALTER TABLE $table ADD FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)");
                $db->statement("ALTER TABLE $table ADD UNIQUE(tenant_id,company_id,unit_id,id)");
            }
            foreach(['ss_processes','ss_events'] as $table)$db->statement("ALTER TABLE $table ADD FOREIGN KEY(tenant_id,company_id,unit_id,request_id) REFERENCES ir_requests(tenant_id,company_id,unit_id,id)");
            foreach(['ss_events','ss_commands'] as $table){
                $db->statement("ALTER TABLE $table ADD FOREIGN KEY(actor_id) REFERENCES users(id)");
                $db->unprepared("CREATE FUNCTION {$table}_immutable() RETURNS trigger LANGUAGE plpgsql AS \$\$ BEGIN RAISE EXCEPTION 'Preserve sourcing history'; END; \$\$; CREATE TRIGGER {$table}_immutable BEFORE UPDATE OR DELETE ON $table FOR EACH ROW EXECUTE FUNCTION {$table}_immutable();");
            }
            $db->statement("ALTER TABLE ss_processes ADD CHECK(payload->'confirmed' IS NULL OR payload->'confirmed'='null'::jsonb OR (payload->'confirmed'->>'confirmed_by')::bigint<>(payload->'confirmed'->'proposal'->>'proposed_by')::bigint)");
            $db->unprepared(<<<'SQL'
CREATE FUNCTION ss_process_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE requester bigint;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Preserve sourcing origin'; END IF;
 IF (NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.request_id) IS DISTINCT FROM (OLD.tenant_id,OLD.company_id,OLD.unit_id,OLD.request_id) OR NEW.payload->'origin' IS DISTINCT FROM OLD.payload->'origin' THEN RAISE EXCEPTION 'Immutable sourcing origin'; END IF;
 IF NEW.version<>OLD.version+1 THEN RAISE EXCEPTION 'Sourcing version must advance once'; END IF;
 IF OLD.payload->'confirmed' IS NOT NULL AND OLD.payload->'confirmed'<>'null'::jsonb THEN RAISE EXCEPTION 'Confirmed award immutable'; END IF;
 SELECT requester_id INTO requester FROM ir_requests WHERE id=NEW.request_id;
 IF NEW.payload->'confirmed' IS NOT NULL AND NEW.payload->'confirmed'<>'null'::jsonb AND (requester=(NEW.payload->'confirmed'->>'confirmed_by')::bigint OR requester=(NEW.payload->'confirmed'->'proposal'->>'proposed_by')::bigint) THEN RAISE EXCEPTION 'Requester cannot award'; END IF;
 RETURN NEW;
END; $$;
CREATE TRIGGER ss_process_guard BEFORE UPDATE OR DELETE ON ss_processes FOR EACH ROW EXECUTE FUNCTION ss_process_guard();
SQL);
            $c=$db->selectOne("select pg_get_constraintdef(oid) as definition from pg_constraint where conrelid='permission_grants'::regclass and conname='permission_grants_scope_check'");
            if(!preg_match('/^CHECK\s*\((.*)\)$/s',$c->definition,$match))throw new \RuntimeException('Catálogo desconhecido.');
            $rights=implode(',',array_map(fn($r)=>$db->getPdo()->quote($r).'::text',self::RIGHTS));
            $db->statement('ALTER TABLE permission_grants DROP CONSTRAINT permission_grants_scope_check');
            $db->statement("ALTER TABLE permission_grants ADD CONSTRAINT permission_grants_scope_check CHECK (({$match[1]}) OR (unit_id IS NOT NULL AND permission::text IN ($rights)))");
            $db->table('sgc_installations')->insert(['module'=>'sourcing','revision'=>1,'schema_signature'=>self::signature($db),'migration_sha256'=>self::hash(),'backup_sha256'=>$backupHash,'operator'=>$operator,'database_operator'=>$db->selectOne('select current_user as role')->role,'prepared_at'=>now()]);
            return ['prepared'=>true,'replayed'=>false,'tables'=>self::TABLES,'access_granted'=>false];
        });
    }
}