Arnaud
5633969d98
Carte Leaflet (annuaire Éducation nationale) + API PHP/MySQL : fiche établissement (effectifs, IPS, population INSEE, DPE ADEME), GED documents/photos, commentaires par groupe, gestion utilisateurs et politique de mots de passe. Imports opendata (effectifs, IPS, population, DPE, IRIS). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
321 lines
14 KiB
PHP
321 lines
14 KiB
PHP
<?php
|
||
/**
|
||
* Initialisation de la base « ecoles » :
|
||
* - crée la base et les tables (etablissements, users, user_scopes, user_groups,
|
||
* group_members, group_scopes, comments, folders, items) ;
|
||
* - importe les établissements depuis data/etablissements.json ;
|
||
* - crée un super-admin par défaut et une arborescence d'exemple ;
|
||
* - crée les dossiers d'upload.
|
||
*
|
||
* Usage :
|
||
* php setup.php (ligne de commande)
|
||
* php setup.php --reset (réimporte les établissements)
|
||
* http://.../setup.php (navigateur ; ?reset=1 pour réimporter)
|
||
*
|
||
* Idempotent : relançable sans risque.
|
||
*/
|
||
declare(strict_types=1);
|
||
require __DIR__ . '/api/db.php';
|
||
|
||
$cli = PHP_SAPI === 'cli';
|
||
$reset = isset($_GET['reset']) || in_array('--reset', $argv ?? [], true);
|
||
|
||
if (!$cli) {
|
||
header('Content-Type: text/plain; charset=utf-8');
|
||
}
|
||
function say(string $m): void
|
||
{
|
||
echo $m . "\n";
|
||
flush();
|
||
}
|
||
|
||
/** Ajoute une colonne si elle n'existe pas déjà (migration idempotente). */
|
||
function add_col_if_missing(PDO $pdo, string $table, string $col, string $ddl): void
|
||
{
|
||
$st = $pdo->prepare(
|
||
'SELECT 1 FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?'
|
||
);
|
||
$st->execute([$table, $col]);
|
||
if (!$st->fetchColumn()) {
|
||
$pdo->exec("ALTER TABLE `$table` ADD COLUMN $ddl");
|
||
say(" + colonne $table.$col ajoutée.");
|
||
}
|
||
}
|
||
|
||
try {
|
||
// 1. Création de la base (connexion sans schéma).
|
||
$root = new PDO(
|
||
'mysql:host=' . DB_HOST . ';port=' . DB_PORT . ';charset=utf8mb4',
|
||
DB_USER,
|
||
DB_PASS,
|
||
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
|
||
);
|
||
$root->exec('CREATE DATABASE IF NOT EXISTS `' . DB_NAME . '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
|
||
say('✔ Base « ' . DB_NAME . ' » prête.');
|
||
|
||
$pdo = db();
|
||
|
||
// 2. Établissements.
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS etablissements (
|
||
id VARCHAR(12) NOT NULL PRIMARY KEY,
|
||
nom VARCHAR(255),
|
||
type VARCHAR(80),
|
||
stat VARCHAR(40),
|
||
reg VARCHAR(120),
|
||
aca VARCHAR(120),
|
||
dep VARCHAR(120),
|
||
depc VARCHAR(10),
|
||
com VARCHAR(160),
|
||
cp VARCHAR(10),
|
||
adr VARCHAR(255),
|
||
tel VARCHAR(40),
|
||
mail VARCHAR(160),
|
||
web VARCHAR(255),
|
||
etat VARCHAR(20),
|
||
circ VARCHAR(255),
|
||
lat DECIMAL(9,5),
|
||
lon DECIMAL(9,5),
|
||
rest TINYINT NOT NULL DEFAULT 0,
|
||
heb TINYINT NOT NULL DEFAULT 0,
|
||
ulis TINYINT NOT NULL DEFAULT 0,
|
||
segp TINYINT NOT NULL DEFAULT 0,
|
||
appr TINYINT NOT NULL DEFAULT 0,
|
||
INDEX idx_type (type),
|
||
INDEX idx_reg (reg),
|
||
INDEX idx_aca (aca),
|
||
INDEX idx_dep (dep),
|
||
INDEX idx_comdep (com, depc),
|
||
INDEX idx_etat (etat)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
|
||
// Anciennes tables de la v1 (remplacées par « items »).
|
||
$pdo->exec('DROP TABLE IF EXISTS documents');
|
||
$pdo->exec('DROP TABLE IF EXISTS photos');
|
||
|
||
// 3. Utilisateurs (admins) et périmètres.
|
||
$pdo->exec(
|
||
"CREATE TABLE IF NOT EXISTS users (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(80) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
role ENUM('super','perimeter','member') NOT NULL DEFAULT 'perimeter',
|
||
email VARCHAR(160) NULL,
|
||
must_change TINYINT NOT NULL DEFAULT 0,
|
||
failed_attempts INT NOT NULL DEFAULT 0,
|
||
locked_until DATETIME NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
|
||
);
|
||
// Migration des bases existantes : rôle « member » + colonnes de politique de mot de passe.
|
||
$pdo->exec("ALTER TABLE users MODIFY role ENUM('super','perimeter','member') NOT NULL DEFAULT 'perimeter'");
|
||
add_col_if_missing($pdo, 'users', 'email', 'email VARCHAR(160) NULL');
|
||
add_col_if_missing($pdo, 'users', 'must_change', 'must_change TINYINT NOT NULL DEFAULT 0');
|
||
add_col_if_missing($pdo, 'users', 'failed_attempts', 'failed_attempts INT NOT NULL DEFAULT 0');
|
||
add_col_if_missing($pdo, 'users', 'locked_until', 'locked_until DATETIME NULL');
|
||
|
||
// Jetons de réinitialisation de mot de passe (lien à usage unique).
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS password_resets (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
token_hash CHAR(64) NOT NULL,
|
||
expires_at DATETIME NOT NULL,
|
||
used TINYINT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_token (token_hash),
|
||
CONSTRAINT fk_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS user_scopes (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
com VARCHAR(160) NOT NULL,
|
||
depc VARCHAR(10) NOT NULL,
|
||
UNIQUE KEY uniq_scope (user_id, com, depc),
|
||
INDEX idx_comdep (com, depc),
|
||
CONSTRAINT fk_scope_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
|
||
// 3 bis. Groupes d'utilisateurs (équipes). can_validate = le groupe peut valider
|
||
// les documents ; group_scopes porte alors son périmètre de communes.
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS user_groups (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(120) NOT NULL UNIQUE,
|
||
can_validate TINYINT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS group_members (
|
||
group_id INT NOT NULL,
|
||
user_id INT NOT NULL,
|
||
PRIMARY KEY (group_id, user_id),
|
||
INDEX idx_user (user_id),
|
||
CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_gm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS group_scopes (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
group_id INT NOT NULL,
|
||
com VARCHAR(160) NOT NULL,
|
||
depc VARCHAR(10) NOT NULL,
|
||
UNIQUE KEY uniq_gscope (group_id, com, depc),
|
||
INDEX idx_comdep (com, depc),
|
||
CONSTRAINT fk_gs_group FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
|
||
// 3 ter. Commentaires de fiche : horodatés, visibles entre membres d'un même groupe.
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS comments (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
uai VARCHAR(12) NOT NULL,
|
||
item_id INT NULL, -- commentaire sur un document précis (sinon NULL)
|
||
user_id INT NOT NULL,
|
||
body TEXT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_uai (uai),
|
||
INDEX idx_item (item_id),
|
||
CONSTRAINT fk_com_uai FOREIGN KEY (uai) REFERENCES etablissements(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_com_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
|
||
// 4. Arborescence globale de dossiers.
|
||
$pdo->exec(
|
||
'CREATE TABLE IF NOT EXISTS folders (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
parent_id INT NULL,
|
||
name VARCHAR(160) NOT NULL,
|
||
position INT NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_parent (parent_id),
|
||
CONSTRAINT fk_folder_parent FOREIGN KEY (parent_id) REFERENCES folders(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
|
||
);
|
||
|
||
// 5. Éléments : fichiers, photos, liens — soumis puis validés.
|
||
$pdo->exec(
|
||
"CREATE TABLE IF NOT EXISTS items (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
uai VARCHAR(12) NOT NULL,
|
||
folder_id INT NULL,
|
||
kind ENUM('file','photo','link') NOT NULL,
|
||
title VARCHAR(255) NOT NULL,
|
||
description TEXT NULL,
|
||
filename VARCHAR(255) NULL,
|
||
url VARCHAR(2048) NULL,
|
||
size BIGINT NULL,
|
||
mime VARCHAR(160) NULL,
|
||
status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
|
||
submitter_name VARCHAR(160) NULL,
|
||
submitter_email VARCHAR(160) NULL,
|
||
reject_reason VARCHAR(500) NULL,
|
||
submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
validated_by INT NULL,
|
||
validated_at DATETIME NULL,
|
||
INDEX idx_uai (uai),
|
||
INDEX idx_status (status),
|
||
INDEX idx_folder (folder_id),
|
||
CONSTRAINT fk_item_uai FOREIGN KEY (uai) REFERENCES etablissements(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_item_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_item_validator FOREIGN KEY (validated_by) REFERENCES users(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
|
||
);
|
||
|
||
// Migrations des bases existantes : description des éléments + portée document des commentaires.
|
||
add_col_if_missing($pdo, 'items', 'description', 'description TEXT NULL AFTER title');
|
||
add_col_if_missing($pdo, 'comments', 'item_id', 'item_id INT NULL AFTER uai');
|
||
// Index sur comments.item_id si absent.
|
||
$hasIdx = $pdo->query("SHOW INDEX FROM comments WHERE Key_name = 'idx_item'")->fetchColumn();
|
||
if (!$hasIdx) {
|
||
$pdo->exec('ALTER TABLE comments ADD INDEX idx_item (item_id)');
|
||
}
|
||
say('✔ Tables prêtes.');
|
||
|
||
// 6. Dossiers d'upload.
|
||
foreach (['files', 'photos'] as $d) {
|
||
$p = __DIR__ . '/uploads/' . $d;
|
||
if (!is_dir($p)) {
|
||
mkdir($p, 0777, true);
|
||
}
|
||
}
|
||
say('✔ Dossiers uploads/ prêts.');
|
||
|
||
// 7. Super-admin par défaut (changement de mot de passe forcé à la 1re connexion).
|
||
if ((int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn() === 0) {
|
||
$pdo->prepare('INSERT INTO users (username, password_hash, role, must_change) VALUES (?, ?, ?, 1)')
|
||
->execute(['admin', password_hash('admin', PASSWORD_DEFAULT), 'super']);
|
||
say('✔ Super-admin créé : « admin » / « admin » → changement de mot de passe demandé à la 1re connexion.');
|
||
}
|
||
|
||
// 7 bis. Dossier privé (boîte d'envoi du mailer en mode « log »), non servi par Apache.
|
||
$priv = __DIR__ . '/private';
|
||
if (!is_dir($priv)) {
|
||
mkdir($priv, 0777, true);
|
||
}
|
||
if (!is_file($priv . '/.htaccess')) {
|
||
file_put_contents($priv . '/.htaccess', "Require all denied\n");
|
||
}
|
||
say('✔ Dossier private/ prêt.');
|
||
|
||
// 8. Arborescence d'exemple (modifiable ensuite dans l'admin).
|
||
if ((int) $pdo->query('SELECT COUNT(*) FROM folders')->fetchColumn() === 0) {
|
||
$tree = [
|
||
'Administratif' => ['Budgets', 'Comptes-rendus de conseil'],
|
||
'Pédagogie' => ['Projet d’établissement', 'Règlement intérieur'],
|
||
'Travaux & bâtiments' => [],
|
||
'Photos' => ['Façade', 'Intérieur'],
|
||
'Liens utiles' => [],
|
||
];
|
||
$insTop = $pdo->prepare('INSERT INTO folders (parent_id, name, position) VALUES (NULL, ?, ?)');
|
||
$insSub = $pdo->prepare('INSERT INTO folders (parent_id, name, position) VALUES (?, ?, ?)');
|
||
$pos = 0;
|
||
foreach ($tree as $top => $subs) {
|
||
$insTop->execute([$top, $pos++]);
|
||
$pid = (int) $pdo->lastInsertId();
|
||
$sp = 0;
|
||
foreach ($subs as $sub) {
|
||
$insSub->execute([$pid, $sub, $sp++]);
|
||
}
|
||
}
|
||
say('✔ Arborescence d’exemple créée.');
|
||
}
|
||
|
||
// 9. Import des établissements.
|
||
$count = (int) $pdo->query('SELECT COUNT(*) FROM etablissements')->fetchColumn();
|
||
if ($count > 0 && !$reset) {
|
||
say("ℹ $count établissements déjà en base — import ignoré (utilisez --reset / ?reset=1 pour réimporter).");
|
||
} else {
|
||
$jsonPath = __DIR__ . '/data/etablissements.json';
|
||
if (!is_file($jsonPath)) {
|
||
throw new RuntimeException("Fichier introuvable : $jsonPath");
|
||
}
|
||
say('… Lecture de ' . basename($jsonPath) . ' …');
|
||
$rows = json_decode(file_get_contents($jsonPath), true);
|
||
if (!is_array($rows)) {
|
||
throw new RuntimeException('JSON invalide.');
|
||
}
|
||
$n = import_etablissements($pdo, $rows);
|
||
say("✔ Import terminé : $n établissements.");
|
||
}
|
||
|
||
say('');
|
||
say('🎉 Initialisation terminée.');
|
||
} catch (Throwable $e) {
|
||
if (isset($pdo) && $pdo->inTransaction()) {
|
||
$pdo->rollBack();
|
||
}
|
||
http_response_code(500);
|
||
say('!! ERREUR : ' . $e->getMessage());
|
||
exit(1);
|
||
}
|