AI_SOC_MCP_Server_Sher

-- Sample database schema for the Text2SQL MCP Server -- User management tables CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, full_name TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_login TIMESTAMP, is_active BOOLEAN DEFAULT TRUE ); CREATE TABLE IF NOT EXISTS user_sessions ( session_id TEXT PRIMARY KEY, user_id INTEGER NOT NULL, start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, end_time TIMESTAMP, ip_address TEXT, user_agent TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ); -- Product catalog tables CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES categories(id) ); CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT, price DECIMAL(10, 2) NOT NULL, stock_quantity INTEGER DEFAULT 0, category_id INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id) ); CREATE TABLE IF NOT EXISTS product_attributes ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, name TEXT NOT NULL, value TEXT NOT NULL, FOREIGN KEY (product_id) REFERENCES products(id) ); -- Order management tables CREATE TABLE IF NOT EXISTS orders ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE IF NOT EXISTS order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); -- Insert sample data INSERT INTO users (username, email, full_name) VALUES ('john_doe', 'john@example.com', 'John Doe'), ('jane_smith', 'jane@example.com', 'Jane Smith'), ('bob_johnson', 'bob@example.com', 'Bob Johnson'); INSERT INTO categories (name, description) VALUES ('Electronics', 'Electronic devices and accessories'), ('Clothing', 'Apparel and fashion items'), ('Books', 'Books and publications'); INSERT INTO products (name, description, price, stock_quantity, category_id) VALUES ('Smartphone', 'Latest smartphone with advanced features', 699.99, 50, 1), ('Laptop', 'High-performance laptop for professionals', 1299.99, 30, 1), ('T-shirt', 'Cotton t-shirt, various colors', 19.99, 100, 2), ('Jeans', 'Denim jeans, slim fit', 49.99, 75, 2), ('Programming Guide', 'Comprehensive programming reference', 39.99, 25, 3); INSERT INTO orders (user_id, status, total_amount) VALUES (1, 'completed', 749.98), (2, 'processing', 1299.99), (3, 'completed', 89.98); INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 1, 699.99), (1, 3, 1, 49.99), (2, 2, 1, 1299.99), (3, 3, 2, 39.99), (3, 5, 1, 9.99);