"""
Simplified database setup script for Supabase.
Uses default port 6543 (connection pooler) which is recommended.
"""
import psycopg2
from psycopg2 import Error
import sys
# Supabase configuration
SUPABASE_HOST = "db.sitomhzoqsoqpkaglxzg.supabase.co"
SUPABASE_USER = "postgres"
SUPABASE_DB = "postgres"
SUPABASE_PORT = "6543" # Connection pooler port (recommended)
def setup_database():
"""Set up the inventory database in Supabase."""
print("=" * 60)
print("Supabase Database Setup")
print("=" * 60)
print()
print(f"Host: {SUPABASE_HOST}")
print(f"Port: {SUPABASE_PORT} (Connection Pooler - Recommended)")
print(f"Database: {SUPABASE_DB}")
print(f"User: {SUPABASE_USER}")
print()
print("You need your database password from Supabase Dashboard:")
print("https://supabase.com/dashboard/project/sitomhzoqsoqpkaglxzg/settings/database")
print()
db_password = input("Enter your Supabase database password: ").strip()
if not db_password:
print("\n❌ Password is required. Please run the script again.")
return False
try:
print()
print("Connecting to Supabase...")
conn = psycopg2.connect(
host=SUPABASE_HOST,
user=SUPABASE_USER,
password=db_password,
database=SUPABASE_DB,
port=SUPABASE_PORT,
sslmode="require"
)
cursor = conn.cursor()
# Create table
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 exists
cursor.execute("SELECT COUNT(*) FROM inventory")
count = cursor.fetchone()[0]
if count == 0:
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.")
count = len(sample_data)
else:
print(f"✓ Database already contains {count} records.")
print()
print("=" * 60)
print("✓ Setup Complete!")
print("=" * 60)
print(f" Table: inventory")
print(f" Records: {count}")
print()
print("Next step: Update inventory-mcp-server.py with your password")
print(f" Set environment variable: SUPABASE_DB_PASSWORD={db_password}")
print(" Or edit the file directly.")
cursor.close()
conn.close()
return True
except Error as e:
print(f"\n❌ Database Error: {e}")
print("\nTroubleshooting:")
print("1. Verify password is correct in Supabase Dashboard")
print("2. Check that your IP is allowed (Supabase > Settings > Database > Connection Pooling)")
print("3. Try port 5432 if 6543 doesn't work")
return False
except Exception as e:
print(f"\n❌ Error: {e}")
return False
if __name__ == "__main__":
success = setup_database()
if not success:
sys.exit(1)