huntingclubroster/classes/MaintenanceService.php

<?php

/** Transactional rules shared by the administrative controllers. */
class MaintenanceService extends Base {

    public static function id(mixed $value): int {
        $id = filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
        if ($id === false)
            throw new AppException('A valid record ID is required.');
        return $id;
    }

    public static function boolean(mixed $value): bool {
        if (!is_scalar($value))
            throw new AppException('A true or false value is required.');
        $result = filter_var($value, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
        if (!is_scalar($value) || $result === null || $value === '')
            throw new AppException('A true or false value is required.');
        return $result;
    }

    public static function usageRule(string $table, string $column): void {
        // No current roster field uses dropdown IDs. Add explicit code-owned references
        // here when such a feature is implemented; database values are never SQL/callbacks.
        if ($table !== '*none' || $column !== '') {
            throw new AppException('Only "No usage reference" is supported. Custom table names and callbacks are disabled.');
        }
    }

    public function dropdownUsed(int $id, ?PDO $db = null): bool {
        $stmt = ($db ?? $this->connect())->prepare('SELECT d.dropdown_default,t.dropdowntype_table, t.dropdowntype_column FROM dropdowns d JOIN dropdowntypes t ON t.dropdowntype_serial=d.dropdown_dropdowntype WHERE d.dropdown_serial=?');
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        if (!$row)
            throw new AppException('Dropdown not found.', 404);
        self::usageRule((string) $row['dropdowntype_table'], (string) $row['dropdowntype_column']);
        return (bool) $row['dropdown_default'];
    }

    public function deleteDropdown(int $id): void {
        $db = $this->connect();
        $db->beginTransaction();
        try {
            $stmt = $db->prepare('SELECT dropdown_dropdowntype FROM dropdowns WHERE dropdown_serial=?');
            $stmt->execute([$id]);
            $type = $stmt->fetchColumn();
            if (!$type)
                throw new AppException('Dropdown not found.', 404);
            $stmt = $db->prepare('SELECT dropdowntype_serial FROM dropdowntypes WHERE dropdowntype_serial=? FOR UPDATE');
            $stmt->execute([$type]);
            $stmt->fetch();
            $stmt = $db->prepare('SELECT dropdown_default FROM dropdowns WHERE dropdown_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            if (!$row)
                throw new AppException('Dropdown not found.', 404);
            if ($row['dropdown_default'] || $this->dropdownUsed($id, $db))
                throw new AppException('Choose another default before deleting this dropdown.', 409);
            $stmt = $db->prepare('DELETE FROM dropdowns WHERE dropdown_serial=?');
            $stmt->execute([$id]);
            $db->commit();
        } catch (Throwable $e) {
            if ($db->inTransaction())
                $db->rollBack();throw $e;
        }
    }

    public function deleteType(int $id): void {
        $db = $this->connect();
        $db->beginTransaction();
        try {
            $stmt = $db->prepare('SELECT dropdowntype_serial FROM dropdowntypes WHERE dropdowntype_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            if (!$stmt->fetch())
                throw new AppException('Dropdown type not found.', 404);
            $stmt = $db->prepare('SELECT dropdown_serial FROM dropdowns WHERE dropdown_dropdowntype=? LIMIT 1');
            $stmt->execute([$id]);
            if ($stmt->fetch())
                throw new AppException('Remove the dropdown values before deleting their type.', 409);
            $stmt = $db->prepare('DELETE FROM dropdowntypes WHERE dropdowntype_serial=?');
            $stmt->execute([$id]);
            $db->commit();
        } catch (Throwable $e) {
            if ($db->inTransaction())
                $db->rollBack();throw $e;
        }
    }

    public function setDropdownDefault(int $id, int $type, bool $checked): void {
        $db = $this->connect();
        $db->beginTransaction();
        try {
            $stmt = $db->prepare('SELECT dropdowntype_serial FROM dropdowntypes WHERE dropdowntype_serial=? FOR UPDATE');
            $stmt->execute([$type]);
            if (!$stmt->fetch())
                throw new AppException('Dropdown type not found.', 404);
            $stmt = $db->prepare('SELECT dropdown_dropdowntype,dropdown_active FROM dropdowns WHERE dropdown_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            if (!$row || (int) $row['dropdown_dropdowntype'] !== $type)
                throw new AppException('Dropdown does not belong to this type.', 409);
            if ($checked && !$row['dropdown_active'])
                throw new AppException('Activate the dropdown before selecting it as the default.', 409);
            if ($checked) {
                $stmt = $db->prepare('UPDATE dropdowns SET dropdown_default=0 WHERE dropdown_dropdowntype=?');
                $stmt->execute([$type]);
            }
            $stmt = $db->prepare('UPDATE dropdowns SET dropdown_default=?, dropdown_changer=?,dropdown_changed=? WHERE dropdown_serial=?');
            $stmt->execute([(int) $checked, $_SESSION[self::USER_NAME] ?? '', $this->getTimestamp(), $id]);
            $db->commit();
        } catch (Throwable $e) {
            if ($db->inTransaction())
                $db->rollBack();throw $e;
        }
    }

    public function setDropdownActive(int $id, bool $checked): void {
        $stmt = $this->connect()->prepare('UPDATE dropdowns SET dropdown_active=?,dropdown_default=IF(?=0,0,dropdown_default),dropdown_changer=?,dropdown_changed=? WHERE dropdown_serial=?');
        $stmt->execute([(int) $checked, (int) $checked, $_SESSION[self::USER_NAME] ?? '', $this->getTimestamp(), $id]);
    }

    private function lockAdministrators(PDO $db): array {
        return $db->query("SELECT user_serial FROM users WHERE user_status='active' AND user_role='Super System Administrator' ORDER BY user_serial FOR UPDATE")->fetchAll(PDO::FETCH_COLUMN);
    }

    private function guardAdministrator(array $admins, int $id, string $role, string $status, bool $deleting = false): void {
        if ($deleting && $id === (int) ($_SESSION[self::USER_SERIAL] ?? 0))
            throw new AppException('You cannot delete your own account.', 409);
        if (in_array($id, array_map('intval', $admins), true) && count($admins) <= 1 && ($deleting || $status !== 'active' || $role!==TenantAccess::SUPER)) {
            throw new AppException('The last active Super System Administrator cannot be removed, disabled or demoted.', 409);
        }
    }

    public function userUsed(int $id, ?PDO $db = null): bool {
        $db ??= $this->connect();
        (new TenantAccess())->requireSuper($db);
        $stmt = $db->prepare("SELECT COUNT(*) FROM notes WHERE note_type='User' AND note_reference=?");
        $stmt->execute([$id]);
        if ((int) $stmt->fetchColumn() > 0)
            return true;
        $stmt = $db->prepare('SELECT COUNT(*) FROM journal WHERE journal_origin=?');
        $stmt->execute([(string) $id]);
        if ((int) $stmt->fetchColumn() > 0)
            return true;
        // Roster tables are installed by the separate non-destructive roster migration.
        $exists = $db->query("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='club_memberships'")->fetchColumn();
        if ($exists) {
            $stmt = $db->prepare('SELECT COUNT(*) FROM club_memberships WHERE user_serial=?');
            $stmt->execute([$id]);
            if ((int) $stmt->fetchColumn() > 0)
                return true;
        }
        foreach(['club_invitations'=>'invited_by','club_tickets'=>'user_serial','club_ticket_messages'=>'author_serial'] as $table=>$column){
            $stmt=$db->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");$stmt->execute([$table]);
            if($stmt->fetchColumn()){$stmt=$db->prepare('SELECT 1 FROM '.$table.' WHERE '.$column.'=? LIMIT 1');$stmt->execute([$id]);if($stmt->fetchColumn())return true;}
        }
        return false;
    }

    public function deleteUser(int $id): void {
        (new TenantAccess())->requireSuper();
        $db = $this->connect();
        DatabaseTransaction::run($db,function(PDO $db)use($id){
            $admins = $this->lockAdministrators($db);
            (new TenantAccess())->requireSuper($db);
            $stmt = $db->prepare('SELECT user_role,user_status FROM users WHERE user_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            $user = $stmt->fetch();
            if (!$user)
                throw new AppException('User not found.', 404);
            if ($user['user_role'] === TenantAccess::SUPER && !(new TenantAccess())->isSuper($db))
                throw new AppException('Only a system administrator can modify that account.', 403);
            $this->guardAdministrator($admins, $id, $user['user_role'], $user['user_status'], true);
            if ($this->userUsed($id, $db))
                throw new AppException('This user has recorded history. Disable the account to preserve its history.', 409);
            $stmt = $db->prepare('DELETE FROM password_resets WHERE user_serial=?');
            $stmt->execute([$id]);
            $stmt = $db->prepare('DELETE FROM users WHERE user_serial=?');
            $stmt->execute([$id]);
            Audit::required($db, (int) ($_SESSION[self::USER_SERIAL] ?? 0), 'deleted unused user ' . $id);
        });
    }

    public function updateUser(int $id, array $input): void {
        (new TenantAccess())->requireSuper();
        $name = trim($input['user_name'] ?? '');
        $email = trim($input['user_email'] ?? '');
        $first = trim($input['user_first_name'] ?? '');
        $last = trim($input['user_last_name'] ?? '');
        $role = $input['user_role'] ?? '';
        $status = self::boolean($input['user_active'] ?? false) ? 'active' : 'inactive';
        if (!preg_match('/\A[A-Za-z0-9_.-]{3,64}\z/', $name) || !filter_var($email, FILTER_VALIDATE_EMAIL) || strlen($email) > 100 || !$first || !$last || strlen($first) > 128 || strlen($last) > 128 || !in_array($role, ['User', 'Guest', 'Administrator', TenantAccess::SUPER], true))
            throw new AppException('Enter a valid username, first/last name, email and role.');
        // Only an existing system administrator may assign that role.
        if ($role === TenantAccess::SUPER && !(new TenantAccess())->isSuper())
            throw new AppException('Only a system administrator can assign that role.', 403);
        $db = $this->connect();
        DatabaseTransaction::run($db,function(PDO $db)use($id,$name,$email,$first,$last,$role,$status){
            $admins = $this->lockAdministrators($db);
            (new TenantAccess())->requireSuper($db);
            $stmt = $db->prepare('SELECT * FROM users WHERE user_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            $old = $stmt->fetch();
            if (!$old)
                throw new AppException('User not found.', 404);
            if ($old['user_role'] === TenantAccess::SUPER && !(new TenantAccess())->isSuper($db))
                throw new AppException('Only a system administrator can modify that account.', 403);
            $this->guardAdministrator($admins, $id, $role, $status);
            $revoke = $old['user_role'] !== $role || $old['user_status'] !== $status || $old['user_email'] !== $email || $old['user_name'] !== $name;
            $stmt = $db->prepare('UPDATE users SET user_name=?,user_email=?,user_first_name=?,user_last_name=?,user_role=?,user_status=?,user_auth_version=user_auth_version+?,user_changer=?,user_changed=? WHERE user_serial=?');
            $stmt->execute([$name, $email, $first, $last, $role, $status, (int) $revoke, $_SESSION[self::USER_NAME] ?? '', $this->getTimestamp(), $id]);
            if ($old['user_email'] !== $email) {
                $stmt = $db->prepare('UPDATE users SET user_email_verified_at=NULL WHERE user_serial=?');
                $stmt->execute([$id]);
            }
            if ($revoke) {
                $stmt = $db->prepare('DELETE FROM password_resets WHERE user_serial=?');
                $stmt->execute([$id]);
                $stmt = $db->prepare('DELETE FROM email_verifications WHERE user_serial=?');
                $stmt->execute([$id]);
            }
            $fields = ['user_name', 'user_email', 'user_first_name', 'user_last_name', 'user_role', 'user_status'];
            Audit::required($db, (int) ($_SESSION[self::USER_SERIAL] ?? 0), 'updated user ' . json_encode(['user' => $id, 'before' => array_intersect_key($old, array_flip($fields)), 'after' => ['user_name' => $name, 'user_email' => $email, 'user_first_name' => $first, 'user_last_name' => $last, 'user_role' => $role, 'user_status' => $status]]));
        });
    }

    public function setUserActive(int $id, bool $active): void {
        (new TenantAccess())->requireSuper();
        $db = $this->connect();
        DatabaseTransaction::run($db,function(PDO $db)use($id,$active){
            $admins = $this->lockAdministrators($db);
            (new TenantAccess())->requireSuper($db);
            $stmt = $db->prepare('SELECT user_role FROM users WHERE user_serial=? FOR UPDATE');
            $stmt->execute([$id]);
            $role = $stmt->fetchColumn();
            if (!$role)
                throw new AppException('User not found.', 404);
            if ($role === TenantAccess::SUPER && !(new TenantAccess())->isSuper($db))
                throw new AppException('Only a system administrator can modify that account.', 403);
            $this->guardAdministrator($admins, $id, $role, $active ? 'active' : 'inactive');
            $stmt = $db->prepare('UPDATE users SET user_status=?,user_auth_version=user_auth_version+1 WHERE user_serial=?');
            $stmt->execute([$active ? 'active' : 'inactive', $id]);
            $stmt = $db->prepare('DELETE FROM password_resets WHERE user_serial=?');
            $stmt->execute([$id]);
            $stmt = $db->prepare('DELETE FROM email_verifications WHERE user_serial=?');
            $stmt->execute([$id]);
            Audit::required($db, (int) ($_SESSION[self::USER_SERIAL] ?? 0), 'changed user status ' . json_encode(['user' => $id, 'active' => $active]));
        });
    }

    public function deleteStatus(int $id): void {
        $db = $this->connect();
        $db->beginTransaction();
        try {
            $rows = $db->query('SELECT status_serial,status_default FROM statuses ORDER BY status_serial FOR UPDATE')->fetchAll(PDO::FETCH_KEY_PAIR);
            if (!array_key_exists($id, $rows))
                throw new AppException('Status not found.', 404);
            if ($rows[$id])
                throw new AppException('Choose another default before deleting this status.', 409);
            // Current club membership statuses are explicit enums, not references to this list.
            $stmt = $db->prepare('DELETE FROM statuses WHERE status_serial=?');
            $stmt->execute([$id]);
            $db->commit();
        } catch (Throwable $e) {
            if ($db->inTransaction())
                $db->rollBack();throw $e;
        }
    }

    public function saveStatus(int $id, array $input): void {
        $name = trim($input['status_name'] ?? '');
        $classes = trim($input['status_classes'] ?? '');
        $active = self::boolean($input['status_active'] ?? false);
        $default = self::boolean($input['status_default'] ?? false);
        if (!$name || strlen($name) > 100 || strlen($classes) > 100 || !preg_match('/\A[A-Za-z0-9_ -]*\z/', $classes))
            throw new AppException('Enter a status name and valid CSS classes (up to 100 bytes).');
        if ($default && !$active)
            throw new AppException('The default status must be active.');
        $db = $this->connect();
        $db->beginTransaction();
        try {
            $db->query('SELECT status_serial FROM statuses ORDER BY status_serial FOR UPDATE')->fetchAll();
            $stmt = $db->prepare('SELECT status_serial FROM statuses WHERE LOWER(status_name)=LOWER(?) AND status_serial<>?');
            $stmt->execute([$name, $id]);
            if ($stmt->fetch())
                throw new AppException('A status with that name already exists.', 409);
            if ($default)
                $db->exec('UPDATE statuses SET status_default=0');
            if ($id) {
                $stmt = $db->prepare('SELECT status_serial FROM statuses WHERE status_serial=?');
                $stmt->execute([$id]);
                if (!$stmt->fetch())
                    throw new AppException('Status not found.', 404);
                $stmt = $db->prepare('UPDATE statuses SET status_name=?,status_classes=?,status_active=?,status_default=?,status_changer=?,status_changed=? WHERE status_serial=?');
                $stmt->execute([$name, $classes, (int) $active, (int) $default, $_SESSION[self::USER_NAME] ?? '', $this->getTimestamp(), $id]);
            } else {
                $stmt = $db->prepare('INSERT INTO statuses(status_name,status_classes,status_active,status_default,status_creator,status_created) VALUES(?,?,?,?,?,?)');
                $stmt->execute([$name, $classes, (int) $active, (int) $default, $_SESSION[self::USER_NAME] ?? '', $this->getTimestamp()]);
            }
            $db->commit();
        } catch (Throwable $e) {
            if ($db->inTransaction())
                $db->rollBack();throw $e;
        }
    }
}