"""
Database setup script for inventory MCP server with Supabase.
This script creates the database tables and inserts sample data.
"""
import psycopg2
from psycopg2 import Error
from urllib.parse import urlparse
import os
import sys
# Supabase configuration
SUPABASE_URL = "https://sitomhzoqsoqpkaglxzg.supabase.co"
SUPABASE_ANON_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InNpdG9taHpvcXNvcXBrYWdseHpnIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NjUwNjI4MDQsImV4cCI6MjA4MDYzODgwNH0.uk78pyHh_RcjbAJh_VI3Uu1elHAnnDqQjClkAoursxg"
def get_connection_info():
"""Get database connection information."""
print("=" * 60)
print("Supabase Database Setup")
print("=" * 60)
print()
print(f"Supabase URL: {SUPABASE_URL}")
print()
print("For direct PostgreSQL connection, you need:")
print("1. Database password (from Supabase Dashboard > Settings > Database)")
print("2. The connection pooler port (usually 6543) or direct port (5432)")
print()
# Extract host
host = urlparse(SUPABASE_URL).netloc
# Get connection details
db_password = os.getenv("SUPABASE_DB_PASSWORD")
if not db_password:
db_password = input("Enter your Supabase database password: ").strip()
db_user = os.getenv("SUPABASE_DB_USER", "postgres")
db_name = os.getenv("SUPABASE_DB_NAME", "postgres")
port_input = input("Enter port (5432 for direct, 6543 for pooler) [5432]: ").strip()
db_port = port_input if port_input else "5432"
return host, db_user, db_password, db_name, db_port
def setup_database():
"""Set up the inventory database in Supabase."""
try:
host, db_user, db_password, db_name, db_port = get_connection_info()
print()
print(f"Connecting to Supabase PostgreSQL at {host}:{db_port}...")
# Try to connect
conn = psycopg2.connect(
host=host,
user=db_user,
password=db_password,
database=db_name,
port=db_port,
sslmode="require"
)
if conn:
cursor = conn.cursor()
# Create table (PostgreSQL syntax)
print("Creating 'inventory' table...")
cursor.execute("""
CREATE TABLE IF NOT EXISTS inventory (
item_id VARCHAR(50),
product_name VARCHAR(100),
location VARCHAR(50),
quantity INT DEFAULT 0,
PRIMARY KEY (item_id, location)
)
""")
conn.commit()
# Check if data already exists
cursor.execute("SELECT COUNT(*) FROM inventory")
count = cursor.fetchone()[0]
if count == 0:
# Insert sample data
print("Inserting sample data...")
sample_data = [
('LAP-001', 'Dell Inspiron Laptop', 'Bengaluru', 25),
('LAP-002', 'HP Pavilion Laptop', 'Mumbai', 15),
('LAP-003', 'Lenovo ThinkPad Laptop', 'Bengaluru', 10),
('LAP-004', 'Apple MacBook Air', 'Delhi', 8),
('LAP-005', 'Asus VivoBook', 'Mumbai', 12),
('LAP-006', 'Acer Aspire 7', 'Pune', 14),
('MOB-001', 'iPhone 14', 'Bengaluru', 40),
('MOB-002', 'Samsung Galaxy S23', 'Mumbai', 35),
('MOB-003', 'OnePlus 11', 'Delhi', 20),
('MOB-004', 'Google Pixel 7', 'Bengaluru', 18),
('MOB-005', 'Xiaomi Redmi Note 12', 'Mumbai', 22),
('MOB-006', 'Realme 12 Pro', 'Pune', 16),
('TAB-001', 'Apple iPad Air', 'Delhi', 14),
('TAB-002', 'Samsung Galaxy Tab S8', 'Bengaluru', 17),
('TAB-003', 'Lenovo Tab M10', 'Mumbai', 9),
('TAB-004', 'Microsoft Surface Go', 'Delhi', 11),
('TAB-005', 'Amazon Fire HD 10', 'Bengaluru', 13),
('TAB-006', 'iBall Slide', 'Pune', 8),
('ACC-001', 'Logitech Mouse', 'Mumbai', 50),
('ACC-002', 'Dell Keyboard', 'Delhi', 45),
('ACC-003', 'HP USB-C Dock', 'Bengaluru', 60),
('ACC-004', 'Samsung 25W Charger', 'Mumbai', 30),
('ACC-005', 'Apple AirPods', 'Delhi', 55),
('ACC-006', 'Boat Headphones', 'Pune', 20),
]
cursor.executemany(
"INSERT INTO inventory (item_id, product_name, location, quantity) VALUES (%s, %s, %s, %s)",
sample_data
)
conn.commit()
print(f"Inserted {len(sample_data)} sample records.")
else:
print(f"Database already contains {count} records. Skipping data insertion.")
print("\n✓ Database setup completed successfully!")
print(f" Database: {db_name}")
print(f" Table: inventory")
print(f" Records: {count if count > 0 else len(sample_data)}")
cursor.close()
conn.close()
print("\n" + "=" * 60)
print("Setup Complete!")
print("=" * 60)
print("\nTo use the server, set the environment variable:")
print(f" SUPABASE_DB_PASSWORD={db_password}")
print("\nOr update inventory-mcp-server.py with your credentials.")
print("\nRun the server with: python inventory-mcp-server.py")
return True
except Error as e:
print(f"\n✗ Error: {e}")
print("\nTroubleshooting:")
print("1. Verify your database password in Supabase Dashboard")
print(" Settings > Database > Connection string")
print("2. Try using port 6543 (connection pooler) instead of 5432")
print("3. Ensure SSL is enabled (sslmode=require)")
print("4. Check that your IP is allowed in Supabase firewall settings")
return False
except Exception as e:
print(f"\n✗ Unexpected error: {e}")
return False
if __name__ == "__main__":
success = setup_database()
if not success:
sys.exit(1)