init_db.py

import psycopg2
import psycopg2.extras
import hashlib
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

# PostgreSQL Configuration
def get_db_config():
    database_url = os.environ.get('DATABASE_URL')
    if database_url:
        return database_url
    else:
        return {
            'host': os.environ.get('DB_HOST', 'localhost'),
            'user': os.environ.get('DB_USER', 'postgres'),
            'password': os.environ.get('DB_PASSWORD', ''),
            'database': os.environ.get('DB_NAME', 'store_db'),
            'port': int(os.environ.get('DB_PORT', '5432'))
        }

try:
    db_config = get_db_config()
    
    if isinstance(db_config, str):
        # Render gives postgres:// but psycopg2 needs postgresql://
        db_config = db_config.replace('postgres://', 'postgresql://', 1)
        conn = psycopg2.connect(db_config)
    else:
        conn = psycopg2.connect(**db_config)
    
    conn.autocommit = True
    cursor = conn.cursor()

    def exec_sql(statement, params=None):
        try:
            cursor.execute(statement, params)
        except Exception as error:
            print(f"❌ SQL failed: {statement.strip()}")
            if params is not None:
                print(f"   params: {params}")
            raise error
    
    # Users table
    exec_sql('''
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(20) NOT NULL,
        email VARCHAR(100)
    )
    ''')
    
    # Products table
    exec_sql('''
    CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        price DECIMAL(10, 2) NOT NULL,
        stock INTEGER DEFAULT 0
    )
    ''')

    exec_sql('''
    CREATE UNIQUE INDEX IF NOT EXISTS products_name_key ON products (name)
    ''')
    
    # Orders table
    exec_sql('''
    CREATE TABLE IF NOT EXISTS orders (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        product_id INTEGER REFERENCES products(id),
        quantity INTEGER,
        total_price DECIMAL(10, 2),
        order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # Insert default users with different roles
    users = [
        ('admin', hash_password('admin123'), 'admin', 'admin@store.com'),
        ('manager', hash_password('manager123'), 'manager', 'manager@store.com'),
        ('customer', hash_password('customer123'), 'customer', 'customer@store.com')
    ]
    
    for user in users:
        exec_sql('''
            INSERT INTO users (username, password, role, email) VALUES (%s, %s, %s, %s)
            ON CONFLICT (username) DO NOTHING
        ''', user)
    
    # Insert sample products
    products = [
        ('Laptop', 'High-performance laptop', 999.99, 10),
        ('Mouse', 'Wireless mouse', 29.99, 50),
        ('Keyboard', 'Mechanical keyboard', 79.99, 30),
        ('Monitor', '27-inch 4K monitor', 399.99, 15),
        ('Headphones', 'Noise-cancelling headphones', 199.99, 25)
    ]
    
    for product in products:
        exec_sql('''
            INSERT INTO products (name, description, price, stock) VALUES (%s, %s, %s, %s)
            ON CONFLICT (name) DO NOTHING
        ''', product)
    
    cursor.close()
    conn.close()
    
    print("✓ PostgreSQL Database initialized successfully!")
    print("\nDatabase Info:")
    db_config = get_db_config()
    if isinstance(db_config, str):
        print(f"  Using DATABASE_URL from environment")
    else:
        print(f"  Host: {db_config['host']}")
        print(f"  Port: {db_config['port']}")
        print(f"  Database: {db_config['database']}")
        print(f"  User: {db_config['user']}")
    print("\nDefault users:")
    print("  Admin - username: admin, password: admin123")
    print("  Manager - username: manager, password: manager123")
    print("  Customer - username: customer, password: customer123")

except Exception as e:
    print(f"❌ Error connecting to PostgreSQL: {e}")
    print("\nMake sure PostgreSQL is running and credentials are correct!")
    print("\nEnvironment variables:")
    print("  DATABASE_URL (or individual variables below)")
    print("  DB_HOST (default: localhost)")
    print("  DB_USER (default: postgres)")
    print("  DB_PASSWORD (default: empty)")
    print("  DB_NAME (default: store_db)")
    print("  DB_PORT (default: 5432)")