Application web de surveillance réseau Mikrotik Netwatch

-- ============================================= -- DATABASE SCHEMA: Mapping & Monitoring Networks -- ============================================= -- 1. ROLES TABLE CREATE TABLE roles ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, -- 'Admin' or 'User' description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 2. USERS TABLE CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, email VARCHAR(100) UNIQUE, full_name VARCHAR(100), role_id INT NOT NULL, balance DECIMAL(10,2) DEFAULT 0.00, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT ); -- 3. ROUTERS TABLE (Mikrotik Netwatch servers) CREATE TABLE routers ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, name VARCHAR(100) NOT NULL, ip_address VARCHAR(45) NOT NULL, api_port INT DEFAULT 8728, username VARCHAR(50) NOT NULL, password_encrypted VARCHAR(255) NOT NULL, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); -- 4. DEVICE_TYPES TABLE CREATE TABLE device_types ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, is_manageable BOOLEAN DEFAULT FALSE, -- TRUE = manageable, FALSE = unmanageable port_count INT DEFAULT 0, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 5. DEVICES TABLE CREATE TABLE devices ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, device_type_id INT NOT NULL, router_id INT NULL, -- assigned router for monitoring name VARCHAR(100) NOT NULL, latitude DECIMAL(10,8) NOT NULL, longitude DECIMAL(11,8) NOT NULL, status ENUM('UP', 'DOWN', 'UNKNOWN') DEFAULT 'UNKNOWN', is_active BOOLEAN DEFAULT TRUE, added_date DATE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (device_type_id) REFERENCES device_types(id) ON DELETE RESTRICT, FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL ); -- 6. PORTS TABLE CREATE TABLE ports ( id INT AUTO_INCREMENT PRIMARY KEY, device_id INT NOT NULL, port_number INT NOT NULL, ip_address VARCHAR(45) NULL, -- assigned IP if manageable is_connected BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY (device_id, port_number), FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ); -- 7. CONNECTIONS TABLE CREATE TABLE connections ( id INT AUTO_INCREMENT PRIMARY KEY, source_port_id INT NOT NULL, destination_port_id INT NOT NULL, ip_address VARCHAR(45) NOT NULL, status ENUM('ACTIVE', 'INACTIVE') DEFAULT 'ACTIVE', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY (source_port_id, destination_port_id), FOREIGN KEY (source_port_id) REFERENCES ports(id) ON DELETE CASCADE, FOREIGN KEY (destination_port_id) REFERENCES ports(id) ON DELETE CASCADE ); -- 8. LOGS TABLE (device status changes & user actions) CREATE TABLE logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, device_id INT NULL, action_type ENUM('DEVICE_ADD', 'DEVICE_REMOVE', 'DEVICE_UPDATE', 'CONNECTION_ADD', 'CONNECTION_REMOVE', 'CONNECTION_MOVE', 'PORT_FORWARD_ADD', 'PORT_FORWARD_REMOVE', 'STATUS_CHANGE', 'LOGIN', 'LOGOUT') NOT NULL, description TEXT, old_value TEXT NULL, new_value TEXT NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL ); -- 9. USER_BALANCES TABLE (transaction history) CREATE TABLE user_balances ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, transaction_type ENUM('CREDIT', 'DEBIT') NOT NULL, description VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); -- 10. DEVICE_STATUS_HISTORY TABLE (UP/DOWN history) CREATE TABLE device_status_history ( id INT AUTO_INCREMENT PRIMARY KEY, device_id INT NOT NULL, status ENUM('UP', 'DOWN') NOT NULL, checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ); -- 11. PORT_FORWARDING_RULES TABLE CREATE TABLE port_forwarding_rules ( id INT AUTO_INCREMENT PRIMARY KEY, device_id INT NOT NULL, router_id INT NOT NULL, src_port INT NOT NULL, dst_port INT NOT NULL, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, removed_at TIMESTAMP NULL, FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE, FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE ); -- ============================================= -- INDEXES FOR PERFORMANCE -- ============================================= CREATE INDEX idx_devices_user ON devices(user_id); CREATE INDEX idx_devices_status ON devices(status); CREATE INDEX idx_ports_device ON ports(device_id); CREATE INDEX idx_connections_source ON connections(source_port_id); CREATE INDEX idx_connections_destination ON connections(destination_port_id); CREATE INDEX idx_logs_user ON logs(user_id); CREATE INDEX idx_logs_device ON logs(device_id); CREATE INDEX idx_status_history_device ON device_status_history(device_id); CREATE INDEX idx_user_balances_user ON user_balances(user_id); -- ============================================= -- TRIGGER: Auto-deduct balance monthly -- ============================================= DELIMITER $$ CREATE TRIGGER deduct_monthly_balance AFTER INSERT ON devices FOR EACH ROW BEGIN DECLARE monthly_fee DECIMAL(10,2) DEFAULT 250.00; DECLARE current_balance DECIMAL(10,2); SELECT balance INTO current_balance FROM users WHERE id = NEW.user_id; IF current_balance < monthly_fee THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance to add device'; ELSE UPDATE users SET balance = balance - monthly_fee WHERE id = NEW.user_id; INSERT INTO user_balances (user_id, amount, transaction_type, description) VALUES (NEW.user_id, monthly_fee, 'DEBIT', CONCAT('Monthly subscription for device: ', NEW.name)); END IF; END$$ DELIMITER ; -- ============================================= -- STORED PROCEDURE: Check balance before device add -- ============================================= DELIMITER $$ CREATE PROCEDURE check_balance_and_add_device( IN p_user_id INT, IN p_device_type_id INT, IN p_router_id INT, IN p_name VARCHAR(100), IN p_latitude DECIMAL(10,8), IN p_longitude DECIMAL(11,8) ) BEGIN DECLARE monthly_fee DECIMAL(10,2) DEFAULT 250.00; DECLARE current_balance DECIMAL(10,2); DECLARE device_id INT; SELECT balance INTO current_balance FROM users WHERE id = p_user_id; IF current_balance < monthly_fee THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance'; ELSE INSERT INTO devices (user_id, device_type_id, router_id, name, latitude, longitude, added_date) VALUES (p_user_id, p_device_type_id, p_router_id, p_name, p_latitude, p_longitude, CURDATE()); SET device_id = LAST_INSERT_ID(); -- Deduct balance UPDATE users SET balance = balance - monthly_fee WHERE id = p_user_id; -- Log transaction INSERT INTO user_balances (user_id, amount, transaction_type, description) VALUES (p_user_id, monthly_fee, 'DEBIT', CONCAT('Subscription for device: ', p_name)); -- Create ports based on device type INSERT INTO ports (device_id, port_number) SELECT device_id, n FROM ( SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 ) numbers WHERE n <= (SELECT port_count FROM device_types WHERE id = p_device_type_id); -- Log action INSERT INTO logs (user_id, device_id, action_type, description) VALUES (p_user_id, device_id, 'DEVICE_ADD', CONCAT('Device added: ', p_name)); END IF; END$$ DELIMITER ;

Application web de surveillance réseau Mikrotik Netwatch

Contenu du PromptEnregistrer

Utilisation

Références

Discussion