#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Initialize database with all required tables
"""
import sqlite3
import os
# Ensure data directory exists
data_dir = os.path.join(os.path.dirname(__file__), 'data')
os.makedirs(data_dir, exist_ok=True)
db_path = os.path.join(data_dir, 'sentiment_analysis.db')
# Remove old database if exists
if os.path.exists(db_path):
os.remove(db_path)
print(f"šļø Removed old database: {db_path}")
# Connect and create tables
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
print("š Creating tables...")
# Table for customer profiles
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer_profiles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id TEXT UNIQUE NOT NULL,
name TEXT,
context_type TEXT,
first_contact TIMESTAMP,
last_contact TIMESTAMP,
total_interactions INTEGER DEFAULT 0,
churn_risk REAL DEFAULT 0,
lifetime_sentiment REAL DEFAULT 0,
notes TEXT
)
''')
print("ā
Created: customer_profiles")
# Table for conversations
cursor.execute('''
CREATE TABLE IF NOT EXISTS conversations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id TEXT NOT NULL,
context_type TEXT NOT NULL,
analysis_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
messages TEXT NOT NULL,
sentiment_score REAL,
trend TEXT,
risk_level TEXT,
predicted_action TEXT,
confidence REAL
)
''')
print("ā
Created: conversations")
# Table for risk alerts
cursor.execute('''
CREATE TABLE IF NOT EXISTS risk_alerts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id TEXT NOT NULL,
alert_type TEXT,
severity TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
resolved INTEGER DEFAULT 0,
notes TEXT
)
''')
print("ā
Created: risk_alerts")
conn.commit()
# Verify tables exist
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print(f"\nš Tables in database: {len(tables)}")
for table in tables:
cursor.execute(f"SELECT COUNT(*) FROM {table[0]}")
count = cursor.fetchone()[0]
print(f" ā¢ {table[0]}: {count} rows")
conn.close()
print(f"\nā
Database initialized successfully at: {db_path}")
