Creating an advanced-level database structure and Flask application for a Chatbots Project involves designing a robust database schema, implementing chatbot functionality, and creating a user-friendly interface using Bootstrap 5. Below is a detailed breakdown of the project.

1. Database Structure

The database will include the following tables:

• Users: For user authentication and chatbot interactions.
• Chatbots: For storing chatbot details.
• Conversations: For storing user-chatbot interactions.
• Messages: For storing individual messages in a conversation.
• Intents: For chatbot intents and responses.
• Entities: For chatbot entities and keywords.

SQL Schema

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') NOT NULL DEFAULT 'user'
);
CREATE TABLE Chatbots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES Users(id)
);
CREATE TABLE Conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    chatbot_id INT,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (chatbot_id) REFERENCES Chatbots(id)
);
CREATE TABLE Messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT,
    sender ENUM('user', 'chatbot') NOT NULL,
    message TEXT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES Conversations(id)
);
CREATE TABLE Intents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    chatbot_id INT,
    response TEXT NOT NULL,
    FOREIGN KEY (chatbot_id) REFERENCES Chatbots(id)
);
CREATE TABLE Entities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    chatbot_id INT,
    keywords TEXT NOT NULL,
    FOREIGN KEY (chatbot_id) REFERENCES Chatbots(id)
);

2. Flask Application Structure

The Flask application will have the following structure:

chatbots_project/
│
├── app/
│   ├── __init__.py
│   ├── models.py
│   ├── routes/
│   │   ├── auth.py
│   │   ├── chatbots.py
│   │   ├── conversations.py
│   │   ├── intents.py
│   │   └── entities.py
│   ├── templates/
│   │   ├── base.html
│   │   ├── auth/
│   │   │   ├── login.html
│   │   │   └── register.html
│   │   ├── chatbots/
│   │   │   ├── list.html
│   │   │   ├── add.html
│   │   │   └── edit.html
│   │   ├── conversations/
│   │   │   ├── list.html
│   │   │   └── chat.html
│   │   ├── intents/
│   │   │   ├── list.html
│   │   │   ├── add.html
│   │   │   └── edit.html
│   │   └── entities/
│   │       ├── list.html
│   │       ├── add.html
│   │       └── edit.html
│   └── static/
│       ├── css/
│       │   └── styles.css
│       └── js/
│           └── scripts.js
│
├── config.py
├── requirements.txt
└── run.py

3. Flask Code

app/__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
from flask_bootstrap import Bootstrap5
db = SQLAlchemy()
login_manager = LoginManager()
def create_app():
    app = Flask(__name__)
    app.config.from_pyfile('config.py')
    db.init_app(app)
    login_manager.init_app(app)
    Bootstrap5(app)
    from .routes.auth import auth_bp
    from .routes.chatbots import chatbots_bp
    from .routes.conversations import conversations_bp
    from .routes.intents import intents_bp
    from .routes.entities import entities_bp
    app.register_blueprint(auth_bp)
    app.register_blueprint(chatbots_bp)
    app.register_blueprint(conversations_bp)
    app.register_blueprint(intents_bp)
    app.register_blueprint(entities_bp)
    return app

app/models.py

from . import db
from flask_login import UserMixin
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    role = db.Column(db.Enum('admin', 'user'), default='user')
class Chatbot(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    created_by = db.Column(db.Integer, db.ForeignKey('user.id'))
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
class Conversation(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    chatbot_id = db.Column(db.Integer, db.ForeignKey('chatbot.id'))
    started_at = db.Column(db.DateTime, default=db.func.current_timestamp())
class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    conversation_id = db.Column(db.Integer, db.ForeignKey('conversation.id'))
    sender = db.Column(db.Enum('user', 'chatbot'), nullable=False)
    message = db.Column(db.Text, nullable=False)
    sent_at = db.Column(db.DateTime, default=db.func.current_timestamp())
class Intent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    chatbot_id = db.Column(db.Integer, db.ForeignKey('chatbot.id'))
    response = db.Column(db.Text, nullable=False)
class Entity(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    chatbot_id = db.Column(db.Integer, db.ForeignKey('chatbot.id'))
    keywords = db.Column(db.Text, nullable=False)

app/routes/auth.py

from flask import Blueprint, render_template, redirect, url_for, flash
from flask_login import login_user, logout_user, login_required
from .models import User
from . import db
auth_bp = Blueprint('auth', __name__)
@auth_bp.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user = User.query.filter_by(username=username).first()
        if user and user.verify_password(password):
            login_user(user)
            return redirect(url_for('chatbots.list'))
        flash('Invalid username or password')
    return render_template('auth/login.html')
@auth_bp.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('auth.login'))
@auth_bp.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        new_user = User(username=username, password_hash=generate_password_hash(password))
        db.session.add(new_user)
        db.session.commit()
        flash('Registration successful! Please log in.')
        return redirect(url_for('auth.login'))
    return render_template('auth/register.html')

app/routes/chatbots.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Chatbot
from . import db
chatbots_bp = Blueprint('chatbots', __name__)
@chatbots_bp.route('/chatbots', methods=['GET'])
def list():
    chatbots = Chatbot.query.all()
    return render_template('chatbots/list.html', chatbots=chatbots)
@chatbots_bp.route('/chatbots/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        name = request.form['name']
        description = request.form['description']
        new_chatbot = Chatbot(name=name, description=description, created_by=current_user.id)
        db.session.add(new_chatbot)
        db.session.commit()
        flash('Chatbot added successfully!')
        return redirect(url_for('chatbots.list'))
    return render_template('chatbots/add.html')
@chatbots_bp.route('/chatbots/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    chatbot = Chatbot.query.get_or_404(id)
    if request.method == 'POST':
        chatbot.name = request.form['name']
        chatbot.description = request.form['description']
        db.session.commit()
        flash('Chatbot updated successfully!')
        return redirect(url_for('chatbots.list'))
    return render_template('chatbots/edit.html', chatbot=chatbot)

app/routes/conversations.py

from flask import Blueprint, render_template
from .models import Conversation
conversations_bp = Blueprint('conversations', __name__)
@conversations_bp.route('/conversations', methods=['GET'])
def list():
    conversations = Conversation.query.all()
    return render_template('conversations/list.html', conversations=conversations)
@conversations_bp.route('/conversations/chat/<int:id>', methods=['GET'])
def chat(id):
    conversation = Conversation.query.get_or_404(id)
    messages = conversation.messages  # Assuming a relationship is set up
    return render_template('conversations/chat.html', conversation=conversation, messages=messages)

app/routes/intents.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Intent
from . import db
intents_bp = Blueprint('intents', __name__)
@intents_bp.route('/intents', methods=['GET'])
def list():
    intents = Intent.query.all()
    return render_template('intents/list.html', intents=intents)
@intents_bp.route('/intents/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        name = request.form['name']
        response = request.form['response']
        new_intent = Intent(name=name, response=response, chatbot_id=request.form['chatbot_id'])
        db.session.add(new_intent)
        db.session.commit()
        flash('Intent added successfully!')
        return redirect(url_for('intents.list'))
    return render_template('intents/add.html')
@intents_bp.route('/intents/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    intent = Intent.query.get_or_404(id)
    if request.method == 'POST':
        intent.name = request.form['name']
        intent.response = request.form['response']
        db.session.commit()
        flash('Intent updated successfully!')
        return redirect(url_for('intents.list'))
    return render_template('intents/edit.html', intent=intent)

app/routes/entities.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Entity
from . import db
entities_bp = Blueprint('entities', __name__)
@entities_bp.route('/entities', methods=['GET'])
def list():
    entities = Entity.query.all()
    return render_template('entities/list.html', entities=entities)
@entities_bp.route('/entities/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        name = request.form['name']
        keywords = request.form['keywords']
        new_entity = Entity(name=name, keywords=keywords, chatbot_id=request.form['chatbot_id'])
        db.session.add(new_entity)
        db.session.commit()
        flash('Entity added successfully!')
        return redirect(url_for('entities.list'))
    return render_template('entities/add.html')
@entities_bp.route('/entities/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    entity = Entity.query.get_or_404(id)
    if request.method == 'POST':
        entity.name = request.form['name']
        entity.keywords = request.form['keywords']
        db.session.commit()
        flash('Entity updated successfully!')
        return redirect(url_for('entities.list'))
    return render_template('entities/edit.html', entity=entity)

app/templates/base.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="{{ url_for('static', filename='css/styles.css') }}">
    <title>{% block title %}Chatbot Project{% endblock %}</title>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-light bg-light">
        <div class="container-fluid">
            <a class="navbar-brand" href="{{ url_for('chatbots.list') }}">Chatbots</a>
            <div class="collapse navbar-collapse" id="navbarNav">
                <ul class="navbar-nav">
                    <li class="nav-item">
                        <a class="nav-link" href="{{ url_for('auth.login') }}">Login</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" href="{{ url_for('auth.register') }}">Register</a>
                    </li>
                </ul>
            </div>
        </div>
    </nav>
    <div class="container mt-4">
        {% with messages = get_flashed_messages() %}
            {% if messages %}
                <div class="alert alert-info">
                    {% for message in messages %}
                        <p>{{ message }}</p>
                    {% endfor %}
                </div>
            {% endif %}
        {% endwith %}
        {% block content %}{% endblock %}
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

app/templates/auth/login.html

{% extends 'base.html' %}
{% block title %}Login{% endblock %}
{% block content %}
<h2>Login</h2>
<form method="POST">
    <div class="mb-3">
        <label for="username" class="form-label">Username</label>
        <input type="text" class="form-control" id="username" name="username" required>
    </div>
    <div class="mb-3">
        <label for="password" class="form-label">Password</label>
        <input type="password" class="form-control" id="password" name="password" required>
    </div>
    <button type="submit" class="btn btn-primary">Login</button>
</form>
{% endblock %}

app/templates/auth/register.html

{% extends 'base.html' %}
{% block title %}Register{% endblock %}
{% block content %}
<h2>Register</h2>
<form method="POST">
    <div class="mb-3">
        <label for="username" class="form-label">Username</label>
        <input type="text" class="form-control" id="username" name="username" required>
    </div>
    <div class="mb-3">
        <label for="password" class="form-label">Password</label>
        <input type="password" class="form-control" id="password" name="password" required>
    </div>
    <button type="submit" class="btn btn-primary">Register</button>
</form>
{% endblock %}

app/templates/chatbots/list.html

{% extends 'base.html' %}
{% block title %}Chatbots{% endblock %}
{% block content %}
<h2>Chatbots</h2>
<a href="{{ url_for('chatbots.add') }}" class="btn btn-success">Add Chatbot</a>
<table class="table mt-3">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Description</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for chatbot in chatbots %}
        <tr>
            <td>{{ chatbot.id }}</td>
            <td>{{ chatbot.name }}</td>
            <td>{{ chatbot.description }}</td>
            <td>
                <a href="{{ url_for('chatbots.edit', id=chatbot.id) }}" class="btn btn-warning">Edit</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

app/templates/chatbots/add.html

{% extends 'base.html' %}
{% block title %}Add Chatbot{% endblock %}
{% block content %}
<h2>Add Chatbot</h2>
<form method="POST">
    <div class="mb-3">
        <label for="name" class="form-label">Chatbot Name</label>
        <input type="text" class="form-control" id="name" name="name" required>
    </div>
    <div class="mb-3">
        <label for="description" class="form-label">Description</label>
        <textarea class="form-control" id="description" name="description" required></textarea>
    </div>
    <button type="submit" class="btn btn-primary">Add Chatbot</button>
</form>
{% endblock %}

app/templates/chatbots/edit.html

{% extends 'base.html' %}
{% block title %}Edit Chatbot{% endblock %}
{% block content %}
<h2>Edit Chatbot</h2>
<form method="POST">
    <div class="mb-3">
        <label for="name" class="form-label">Chatbot Name</label>
        <input type="text" class="form-control" id="name" name="name" value="{{ chatbot.name }}" required>
    </div>
    <div class="mb-3">
        <label for="description" class="form-label">Description</label>
        <textarea class="form-control" id="description" name="description" required>{{ chatbot.description }}</textarea>
    </div>
    <button type="submit" class="btn btn-primary">Update Chatbot</button>
</form>
{% endblock %}

app/templates/conversations/list.html

{% extends 'base.html' %}
{% block title %}Conversations{% endblock %}
{% block content %}
<h2>Conversations</h2>
<table class="table mt-3">
    <thead>
        <tr>
            <th>ID</th>
            <th>User ID</th>
            <th>Chatbot ID</th>
            <th>Started At</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for conversation in conversations %}
        <tr>
            <td>{{ conversation.id }}</td>
            <td>{{ conversation.user_id }}</td>
            <td>{{ conversation.chatbot_id }}</td>
            <td>{{ conversation.started_at }}</td>
            <td>
                <a href="{{ url_for('conversations.chat', id=conversation.id) }}" class="btn btn-info">View Chat</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

app/templates/conversations/chat.html

{% extends 'base.html' %}
{% block title %}Chat{% endblock %}
{% block content %}
<h2>Chat with Chatbot</h2>
<div class="chat-window">
    {% for message in messages %}
    <div class="message {{ message.sender }}">
        <strong>{{ message.sender }}:</strong> {{ message.message }}
    </div>
    {% endfor %}
</div>
{% endblock %}

app/templates/intents/list.html

{% extends 'base.html' %}
{% block title %}Intents{% endblock %}
{% block content %}
<h2>Intents</h2>
<a href="{{ url_for('intents.add') }}" class="btn btn-success">Add Intent</a>
<table class="table mt-3">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Response</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for intent in intents %}
        <tr>
            <td>{{ intent.id }}</td>
            <td>{{ intent.name }}</td>
            <td>{{ intent.response }}</td>
            <td>
                <a href="{{ url_for('intents.edit', id=intent.id) }}" class="btn btn-warning">Edit</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

app/templates/intents/add.html

{% extends 'base.html' %}
{% block title %}Add Intent{% endblock %}
{% block content %}
<h2>Add Intent</h2>
<form method="POST">
    <div class="mb-3">
        <label for="name" class="form-label">Intent Name</label>
        <input type="text" class="form-control" id="name" name="name" required>
    </div>
    <div class="mb-3">
        <label for="response" class="form-label">Response</label>
        <textarea class="form-control" id="response" name="response" required></textarea>
    </div>
    <input type="hidden" name="chatbot_id" value="{{ chatbot_id }}">
    <button type="submit" class="btn btn-primary">Add Intent</button>
</form>
{% endblock %}

app/templates/intents/edit.html

{% extends 'base.html' %}
{% block title %}Edit Intent{% endblock %}
{% block content %}
<h2>Edit Intent</h2>
<form method="POST">
    <div class="mb-3">
        <label for="name" class="form-label">Intent Name</label>
        <input type="text" class="form-control" id="name" name="name" value="{{ intent.name }}" required>
    </div>
    <div class="mb-3">
        <label for="response" class="form-label">Response</label>
        <textarea class="form-control" id="response" name="response" required>{{ intent.response }}</textarea>
    </div>
    <button type="submit" class="btn btn-primary">Update Intent</button>
</form>
{% endblock %}

app/templates/entities/list.html

{% extends 'base.html' %}
{% block title %}Entities{% endblock %}
{% block content %}
<h2>Entities</h2>
<a href="{{ url_for('entities.add') }}" class="btn btn-success">Add Entity</a>
<table class="table mt-3">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Keywords</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for entity in entities %}
        <tr>
            <td>{{ entity.id }}</td>
            <td>{{ entity.name }}</td>
            <td>{{ entity.keywords }}</td>
            <td>
                <a href="{{ url_for('entities.edit', id=entity.id) }}" class="btn btn-warning">Edit</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

app/templates/entities/add.html

{% extends 'base.html' %}
{% block title %}Add Entity{% endblock %}
{% block content %}
<h2>Add Entity</h2>
<form method="POST">
    <div class ```html
="mb-3">
        <label for="name" class="form-label">Entity Name</label>
        <input type="text" class="form-control" id="name" name="name" required>
    </div>
    <div class="mb-3">
        <label for="keywords" class="form-label">Keywords</label>
        <textarea class="form-control" id="keywords" name="keywords" required></textarea>
    </div>
    <input type="hidden" name="chatbot_id" value="{{ chatbot_id }}">
    <button type="submit" class="btn btn-primary">Add Entity</button>
</form>
{% endblock %}

app/templates/entities/edit.html

{% extends 'base.html' %}
{% block title %}Edit Entity{% endblock %}
{% block content %}
<h2>Edit Entity</h2>
<form method="POST">
    <div class="mb-3">
        <label for="name" class="form-label">Entity Name</label>
        <input type="text" class="form-control" id="name" name="name" value="{{ entity.name }}" required>
    </div>
    <div class="mb-3">
        <label for="keywords" class="form-label">Keywords</label>
        <textarea class="form-control" id="keywords" name="keywords" required>{{ entity.keywords }}</textarea>
    </div>
    <button type="submit" class="btn btn-primary">Update Entity</button>
</form>
{% endblock %}

4. Configuration

config.py

import os
class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'your_secret_key'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///site.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

5. Requirements

Flask
Flask-SQLAlchemy
Flask-Login
Flask-Bootstrap

6. Run the Application

run.py

from app import create_app
app = create_app()
if __name__ == '__main__':
    app.run(debug=True)

Conclusion

This structure provides a comprehensive foundation for an advanced chatbot project using Flask and Bootstrap 5. You can expand upon this by adding features such as user roles, chatbot training, and more sophisticated conversation handling.