Hermes Agent
108 lines
4.5 KiB
Python
108 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
||
"""Initialize the BSN Intake SQLite database with schema + sample data."""
|
||
|
||
import sqlite3
|
||
import os
|
||
|
||
DB_PATH = os.path.join(os.path.dirname(__file__), "bsn_intake.db")
|
||
|
||
SCHEMA = """
|
||
-- Incoming submissions from all channels
|
||
CREATE TABLE IF NOT EXISTS submissions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
channel TEXT NOT NULL, -- 'whatsapp', 'telegram', 'web_form'
|
||
sender_id TEXT NOT NULL, -- phone number, telegram ID, email
|
||
type TEXT NOT NULL, -- 'text', 'image', 'voice', 'video'
|
||
content TEXT, -- transcribed text or message body
|
||
media_path TEXT, -- local path to saved media file
|
||
status TEXT DEFAULT 'new', -- 'new', 'processing', 'done', 'flagged'
|
||
category TEXT, -- Hermes-classified category
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Knowledge base for the query chatbot
|
||
CREATE TABLE IF NOT EXISTS knowledge (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
key TEXT UNIQUE NOT NULL, -- lookup key (e.g. 'autor_uwe_rosenberg')
|
||
question_pattern TEXT, -- "Where is Uwe Rosenberg?"
|
||
answer TEXT NOT NULL, -- "Hall 3, Booth B12, 10am-6pm"
|
||
category TEXT,
|
||
tags TEXT, -- comma-separated
|
||
valid_from DATE,
|
||
valid_until DATE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- Indexes for fast lookups
|
||
CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
|
||
CREATE INDEX IF NOT EXISTS idx_submissions_channel ON submissions(channel);
|
||
CREATE INDEX IF NOT EXISTS idx_submissions_category ON submissions(category);
|
||
CREATE INDEX IF NOT EXISTS idx_knowledge_key ON knowledge(key);
|
||
CREATE INDEX IF NOT EXISTS idx_knowledge_category ON knowledge(category);
|
||
|
||
-- Cross-channel user profiles (phone = universal ID)
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
phone TEXT UNIQUE NOT NULL,
|
||
name TEXT,
|
||
password_hash TEXT,
|
||
profile_pic TEXT,
|
||
verified INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS user_sessions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER NOT NULL REFERENCES users(id),
|
||
token TEXT UNIQUE NOT NULL,
|
||
channel TEXT DEFAULT 'web',
|
||
expires_at TIMESTAMP NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_user_sessions_token ON user_sessions(token);
|
||
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);
|
||
"""
|
||
|
||
SAMPLE_SUBMISSIONS = [
|
||
("whatsapp", "+491234567890", "text", "Wo ist der Stand von Feuerland? Hallo?", "new", None),
|
||
("whatsapp", "+491234567891", "text", "Catan ist ausverkauft in Halle 5!", "new", "ausverkauft"),
|
||
("telegram", "8520780873", "text", "Habe eben gesehen: Kosmos Halle 3, Stand A42 hat noch Restbestände von Die Crew", "new", "restbestände"),
|
||
("web_form", "besucher@example.com", "text", "Warum gibt es keine barrierefreien Zugänge in Halle 8?", "flagged", "kritik"),
|
||
]
|
||
|
||
SAMPLE_KNOWLEDGE = [
|
||
("feuerland_stand", "Wo ist Feuerland?", "Feuerland Spiele findest du in Halle 3, Stand C17. Geöffnet von 10–18 Uhr.", "verlage", "feuerland, stand, messe", "2026-10-01", "2026-10-04"),
|
||
("catan_verfuegbar", "Ist Catan noch verfügbar?", "Catan war Stand 12.06.2026 in Halle 5 ausverkauft. Kosmos meldet Nachschub für Morgen früh.", "verlage", "catan, kosmos, ausverkauft", "2026-10-01", "2026-10-04"),
|
||
("oeffnungszeiten", "Wann öffnet die Messe?", "Die Spiel Essen 2026 öffnet Donnerstag bis Samstag von 10–18 Uhr, Sonntag von 10–17 Uhr.", "messe", "öffnungszeiten, messe, essen", None, None),
|
||
]
|
||
|
||
|
||
def init():
|
||
conn = sqlite3.connect(DB_PATH)
|
||
conn.executescript(SCHEMA)
|
||
|
||
# Check if data already exists
|
||
count = conn.execute("SELECT COUNT(*) FROM submissions").fetchone()[0]
|
||
if count == 0:
|
||
conn.executemany(
|
||
"INSERT INTO submissions (channel, sender_id, type, content, status, category) VALUES (?,?,?,?,?,?)",
|
||
SAMPLE_SUBMISSIONS,
|
||
)
|
||
print(f"Inserted {len(SAMPLE_SUBMISSIONS)} sample submissions")
|
||
|
||
count = conn.execute("SELECT COUNT(*) FROM knowledge").fetchone()[0]
|
||
if count == 0:
|
||
conn.executemany(
|
||
"INSERT INTO knowledge (key, question_pattern, answer, category, tags, valid_from, valid_until) VALUES (?,?,?,?,?,?,?)",
|
||
SAMPLE_KNOWLEDGE,
|
||
)
|
||
print(f"Inserted {len(SAMPLE_KNOWLEDGE)} sample knowledge entries")
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
print(f"Database initialized: {DB_PATH}")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
init()
|