import pathlib,json,subprocess,hashlib,os,datetime,re
plan=PLAN
expected=EXPECTED
root=pathlib.Path('/var/www/html/sgf.tempo.co.mz')
for f,h in expected.items():
 if hashlib.sha256((root/f).read_bytes()).hexdigest()!=h:raise RuntimeError('Source changed; refuse')
def pg(sql):
 r=subprocess.run(['runuser','-u','postgres','--','psql','-X','-At','-v','ON_ERROR_STOP=1','--dbname=sgf_db'],input=sql,capture_output=True,text=True)
 if r.returncode:raise RuntimeError('Scoped SQL failed: '+r.stderr[-1200:])
 return r.stdout
row=json.loads(pg("BEGIN READ ONLY; SELECT json_build_object('count',(select count(*) from permission_grants where user_id=1 and company_id=1 and tenant_id=1),'definition',pg_get_constraintdef(oid)) FROM pg_constraint WHERE conrelid='permission_grants'::regclass AND conname='permission_grants_scope_check';ROLLBACK;").splitlines()[1])
assert row['count']==6
old=row['definition'];assert old.startswith('CHECK (') and old.endswith(')');expression=old[7:-1] if old.startswith('CHECK ((') else old[7:-1]
# Strip exactly CHECK ( ... ), retaining balanced internal parentheses.
expression=old[len('CHECK ('):-1]
allowed=set(re.findall(r"'([a-z_]+\.[a-z_.]+)'",old));missing=sorted({e['permission'] for e in plan['entries']}-allowed)
expectedMissing=['purchasing_budget.approve','purchasing_budget.consume','purchasing_budget.create','purchasing_budget.record','purchasing_budget.reinforcement.propose','purchasing_budget.reinforcement.review','purchasing_budget.view','supplier_payables.view','workshop_corrections.apply','workshop_corrections.request','workshop_corrections.review','workshop_corrections.view']
assert missing==sorted(expectedMissing),'Unexpected constraint delta'
q=lambda x:"'"+str(x).replace("'","''")+"'"
new_expression='('+expression+') OR (permission IN ('+','.join(q(x) for x in missing)+') AND unit_id IS NOT NULL)'
values=','.join('('+q(e['permission'])+','+('NULL::bigint' if e['unit_id'] is None else '1::bigint')+')' for e in plan['entries'])
wrong=','.join('('+q(e['permission'])+','+('1::bigint' if e['unit_id'] is None else 'NULL::bigint')+')' for e in plan['entries'])
assert pg('SELECT bool_and('+new_expression+') FROM (VALUES '+values+') AS p(permission,unit_id);').strip()=='t'
assert pg('SELECT bool_and(NOT ('+new_expression+')) FROM (VALUES '+wrong+') AS p(permission,unit_id);').strip()=='t'
backup=pathlib.Path('/var/backups/sgf-functional-access-20261030');backup.mkdir(mode=0o700,exist_ok=True);os.chmod(backup,0o700)
archive=backup/'permissions-audit-before-final.dump'
if archive.exists():raise RuntimeError('Final backup already exists; inspect receipt before retry')
r=subprocess.run(['runuser','-u','postgres','--','pg_dump','-Fc','--dbname=sgf_db','-t','public.permission_grants','-t','public.identity_audit'],capture_output=True,check=True)
with os.fdopen(os.open(archive,os.O_CREAT|os.O_EXCL|os.O_WRONLY,0o600),'wb') as f:f.write(r.stdout)
subprocess.run(['pg_restore','--list',str(archive)],capture_output=True,check=True);subprocess.run(['pg_restore','--file=-',str(archive)],capture_output=True,check=True)
oldFile=backup/'constraint-before.sql';oldFile.write_text('ALTER TABLE public.permission_grants ADD CONSTRAINT permission_grants_scope_check '+old+';\n');os.chmod(oldFile,0o600)
manifest=json.dumps([{'permission':e['permission'],'unit_id':e['unit_id']} for e in plan['entries']]);detail=json.dumps({'operator':'Tempo — confirmação expressa de 82 direitos em 30/10/2026','reason':'Pacote aprovado; acrescentar 76 e preservar seis. Adaptação estrita da lista de 12 direitos aprovados ausentes no catálogo; sem tabelas/guardas QA ou operações fiscais/bancárias. Manifesto SHA256 '+MANIFEST_SHA,'grants':json.loads(manifest)})
sql='''BEGIN ISOLATION LEVEL REPEATABLE READ;
SET LOCAL lock_timeout='5s';SET LOCAL statement_timeout='30s';
SELECT pg_advisory_xact_lock(72644101);
LOCK TABLE permission_grants IN ACCESS EXCLUSIVE MODE;
CREATE TEMP TABLE intended_access ON COMMIT DROP AS SELECT * FROM jsonb_to_recordset('''+q(manifest)+'''::jsonb) AS x(permission text,unit_id bigint);
CREATE TEMP TABLE access_before ON COMMIT DROP AS SELECT * FROM permission_grants;
CREATE TEMP TABLE audit_before_count ON COMMIT DROP AS SELECT count(*) AS n FROM identity_audit;
DO $guard$ BEGIN
IF (SELECT count(*) FROM intended_access)<>82 OR (SELECT count(*) FROM permission_grants WHERE user_id=1 AND tenant_id=1 AND company_id=1)<>6 THEN RAISE EXCEPTION 'Changed grant state'; END IF;
IF NOT EXISTS(SELECT 1 FROM users WHERE id=1 AND email='nala@tempo.co.mz' AND name='Osmane Nala') OR NOT EXISTS(SELECT 1 FROM tenants WHERE id=1 AND name='Tempo') OR NOT EXISTS(SELECT 1 FROM companies WHERE id=1 AND tenant_id=1 AND name='Tempo, Lda.') OR NOT EXISTS(SELECT 1 FROM units WHERE id=1 AND tenant_id=1 AND company_id=1 AND name='SEGI') OR NOT EXISTS(SELECT 1 FROM company_memberships WHERE user_id=1 AND tenant_id=1 AND company_id=1) THEN RAISE EXCEPTION 'Identity/scope changed'; END IF;
IF EXISTS(SELECT 1 FROM permission_grants g WHERE g.user_id=1 AND g.tenant_id=1 AND g.company_id=1 AND NOT EXISTS(SELECT 1 FROM intended_access x WHERE x.permission=g.permission AND x.unit_id IS NOT DISTINCT FROM g.unit_id)) THEN RAISE EXCEPTION 'Unapproved existing grant'; END IF;
END $guard$;
ALTER TABLE permission_grants DROP CONSTRAINT permission_grants_scope_check;
ALTER TABLE permission_grants ADD CONSTRAINT permission_grants_scope_check CHECK ('''+new_expression+''');
INSERT INTO permission_grants(user_id,tenant_id,company_id,unit_id,permission,created_at,updated_at)
SELECT 1,1,1,x.unit_id,x.permission,now(),now() FROM intended_access x WHERE NOT EXISTS(SELECT 1 FROM permission_grants g WHERE g.user_id=1 AND g.tenant_id=1 AND g.company_id=1 AND g.permission=x.permission AND g.unit_id IS NOT DISTINCT FROM x.unit_id);
INSERT INTO identity_audit(action,actor_id,tenant_id,company_id,unit_id,subject_id,details,created_at) VALUES ('identity.functional_access_package_granted',1,1,1,1,1,'''+q(detail)+'''::jsonb,now());
DO $verify$ BEGIN
IF (SELECT count(*) FROM permission_grants WHERE user_id=1 AND tenant_id=1 AND company_id=1)<>82 OR (SELECT count(*) FROM permission_grants)<>(SELECT count(*)+76 FROM access_before) OR (SELECT count(*) FROM identity_audit)<>(SELECT n+1 FROM audit_before_count) THEN RAISE EXCEPTION 'Unexpected delta'; END IF;
IF EXISTS(SELECT * FROM access_before EXCEPT SELECT * FROM permission_grants) THEN RAISE EXCEPTION 'Previous grants modified'; END IF;
IF EXISTS(SELECT 1 FROM permission_grants g WHERE g.user_id=1 AND g.tenant_id=1 AND g.company_id=1 AND NOT EXISTS(SELECT 1 FROM intended_access x WHERE x.permission=g.permission AND x.unit_id IS NOT DISTINCT FROM g.unit_id)) THEN RAISE EXCEPTION 'Unapproved resulting grant'; END IF;
END $verify$;
COMMIT;
SELECT json_build_object('applied',true,'final',(select count(*) from permission_grants where user_id=1 and tenant_id=1 and company_id=1),'company',(select count(*) from permission_grants where user_id=1 and tenant_id=1 and company_id=1 and unit_id is null),'unit',(select count(*) from permission_grants where user_id=1 and tenant_id=1 and company_id=1 and unit_id=1),'audit_id',(select max(id) from identity_audit where action='identity.functional_access_package_granted'));
'''
output=pg(sql);receipt=json.loads(output.strip().splitlines()[-1]);receipt.update(existing_preserved=6,added=76,approved_manifest_sha256=MANIFEST_SHA,scope={'tenant_id':1,'company_id':1,'unit_id':1},target='nala@tempo.co.mz',backup=str(archive),backup_catalogue_and_sql_readable=True,constraint_added_unit_names=missing,wrong_scope_candidates_rejected=82,qa_guards_unchanged=True,public_payables_enabled=False,bank_execution=False,real_fiscal_emission=False,checked_at=datetime.datetime.now(datetime.timezone.utc).isoformat(),release=str(root.resolve()),initial_attempt_rolled_back=True,restored_over_live_history=False)
f=backup/'receipt.json';f.write_text(json.dumps(receipt,ensure_ascii=False,indent=2));os.chmod(f,0o600);print(json.dumps(receipt))