60 lines
5.2 KiB
PHP
60 lines
5.2 KiB
PHP
<?php
|
|
|
|
use Illuminate\Foundation\Bootstrap\LoadConfiguration;
|
|
use Illuminate\Foundation\Bootstrap\LoadEnvironmentVariables;
|
|
|
|
// Read-only audit: no Laravel kernel, migrations, seeders or data updates.
|
|
require dirname(__DIR__).'/vendor/autoload.php';
|
|
$app = require dirname(__DIR__).'/bootstrap/app.php';
|
|
(new LoadEnvironmentVariables)->bootstrap($app);
|
|
(new LoadConfiguration)->bootstrap($app);
|
|
$c = $app->make('config')->get('database.connections.'.$app->make('config')->get('database.default'));
|
|
if (($c['driver'] ?? '') !== 'pgsql' || ! in_array($c['url'] ?? null, [null, ''], true)) {
|
|
throw new RuntimeException('Unsupported target');
|
|
}
|
|
$pdo = new PDO('pgsql:host='.$c['host'].';port='.$c['port'].';dbname='.$c['database'], $c['username'], $c['password'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
|
|
$pdo->exec('BEGIN TRANSACTION READ ONLY');
|
|
$proof = ['captured_at' => gmdate('c'), 'identity' => $pdo->query("SELECT current_database() AS database, inet_server_port() AS port, current_setting('transaction_read_only') AS read_only")->fetch(PDO::FETCH_ASSOC), 'records' => []];
|
|
foreach (['opportunities', 'projects', 'economic_clusters', 'investment_zones'] as $table) {
|
|
$rows = $pdo->query('SELECT * FROM '.$table.' WHERE deleted_at IS NULL ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($rows as $row) {
|
|
$relations = [];
|
|
$queries = match ($table) {
|
|
'opportunities' => ['own' => 'SELECT geo_location_id FROM opportunity_locations WHERE opportunity_id=?', 'projects' => 'SELECT project_id FROM opportunity_projects WHERE opportunity_id=?', 'clusters' => 'SELECT cluster_id FROM opportunity_clusters WHERE opportunity_id=?'],
|
|
'projects' => ['own' => 'SELECT * FROM project_locations WHERE project_id=? AND deleted_at IS NULL', 'clusters' => 'SELECT * FROM cluster_projects WHERE project_id=? AND deleted_at IS NULL'],
|
|
'economic_clusters' => ['members' => 'SELECT * FROM cluster_projects WHERE cluster_id=? AND deleted_at IS NULL'],
|
|
'investment_zones' => ['own' => 'SELECT * FROM investment_zone_locations WHERE zone_id=?', 'links' => 'SELECT * FROM investment_zone_links WHERE zone_id=?'],
|
|
};
|
|
foreach ($queries as $kind => $sql) {
|
|
$statement = $pdo->prepare($sql);
|
|
$statement->execute([$row['id']]);
|
|
$relations[$kind] = $statement->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
$issues = [];
|
|
$own = count($relations['own'] ?? []) + (empty($row['primary_territory_id']) ? 0 : 1);
|
|
if ($table === 'opportunities') {
|
|
if (! $own) {
|
|
$issues[] = ($relations['projects'] || $relations['clusters']) ? 'context_only_not_implantation' : 'insufficient_territorial_information';
|
|
}
|
|
if ($own > 1) {
|
|
$issues[] = 'multiple_links_review_distinct_ids';
|
|
}
|
|
$issues[] = 'direct_link_source_and_date_not_stored_in_pivot';
|
|
}
|
|
$proof['records'][] = ['kind' => $table, 'id' => $row['id'], 'record' => $row, 'relations' => $relations, 'findings' => $issues];
|
|
}
|
|
}
|
|
$proof['geography'] = $pdo->query('SELECT g.id,g.name,g.location_type,g.parent_id,g.source_system,g.data_classification,
|
|
p.location_type AS parent_type,p.deleted_at AS parent_deleted_at,
|
|
(SELECT count(*) FROM geo_geometries s WHERE s.geo_location_id=g.id AND s.deleted_at IS NULL) AS geometry_count
|
|
FROM geo_locations g LEFT JOIN geo_locations p ON p.id=g.parent_id WHERE g.deleted_at IS NULL ORDER BY g.id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['geometries'] = $pdo->query('SELECT id,geo_location_id,geometry_type,source_system,properties,data_classification,ST_IsValid(geom) AS valid,ST_IsEmpty(geom) AS empty,ST_SRID(geom) AS srid,ST_AsGeoJSON(geom) AS geometry FROM geo_geometries WHERE deleted_at IS NULL ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['dossiers'] = $pdo->query('SELECT id,project_id,cluster_id,title,source,content,version_no,publication_status FROM investment_dossiers WHERE deleted_at IS NULL ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['legal_sources'] = $pdo->query('SELECT id,title,url,diploma,article,competent_entity,validation_status,validated_at,version_no,valid_from,valid_until FROM investment_legal_sources ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['geo_sources'] = $pdo->query('SELECT id,name,responsible_entity,reference,checksum,source_version,status,approval_reference FROM geo_sources ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['geo_profiles'] = $pdo->query('SELECT geo_location_id,source_id,status,valid_from,valid_to FROM geo_territory_profiles ORDER BY geo_location_id')->fetchAll(PDO::FETCH_ASSOC);
|
|
$proof['traceability_events'] = $pdo->query("SELECT id,action,occurred_at,actor_user_id,after_data FROM audit_events WHERE source_system='territory-traceability-v2' ORDER BY occurred_at,id")->fetchAll(PDO::FETCH_ASSOC);
|
|
$pdo->exec('ROLLBACK');
|
|
file_put_contents(dirname(__DIR__).'/storage/app/private/investmoz-v2/territory-audit.json', json_encode($proof, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR));
|
|
echo json_encode(['identity' => $proof['identity'], 'records' => count($proof['records']), 'territories' => count($proof['geography']), 'geometries' => count($proof['geometries'])], JSON_THROW_ON_ERROR).PHP_EOL;
|