NaimalcreativityAI/physical-ai-chatbot-api

1"""2Database migration script for authentication tables.3Creates users, user_backgrounds, and personalized_content tables.4"""5 6import psycopg27import os8from dotenv import load_dotenv9 10load_dotenv()11 12 13def create_tables():14    """Create all authentication and personalization tables."""15    DATABASE_URL = os.getenv("DATABASE_URL")16 17    if not DATABASE_URL:18        raise ValueError("DATABASE_URL not found in environment variables")19 20    print("🔌 Connecting to Neon Postgres...")21    conn = psycopg2.connect(DATABASE_URL)22    cur = conn.cursor()23 24    try:25        print("📊 Creating users table...")26        cur.execute(27            """28        CREATE TABLE IF NOT EXISTS users (29          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),30          email VARCHAR(255) UNIQUE NOT NULL,31          name VARCHAR(255) NOT NULL,32          password_hash VARCHAR(255) NOT NULL,33          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,34          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP35        );36        """37        )38 39        print("📊 Creating index on users.email...")40        cur.execute(41            """42        CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);43        """44        )45 46        print("📊 Creating user_backgrounds table...")47        cur.execute(48            """49        CREATE TABLE IF NOT EXISTS user_backgrounds (50          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),51          user_id UUID REFERENCES users(id) ON DELETE CASCADE,52          software_experience VARCHAR(50) NOT NULL CHECK (software_experience IN ('Beginner', 'Intermediate', 'Advanced')),53          hardware_experience VARCHAR(50) NOT NULL CHECK (hardware_experience IN ('Beginner', 'Intermediate', 'Advanced')),54          programming_languages TEXT[],55          robotics_background TEXT,56          learning_goals TEXT,57          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,58          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,59          UNIQUE(user_id)60        );61        """62        )63 64        print("📊 Creating index on user_backgrounds.user_id...")65        cur.execute(66            """67        CREATE INDEX IF NOT EXISTS idx_user_backgrounds_user_id ON user_backgrounds(user_id);68        """69        )70 71        print("📊 Creating personalized_content table...")72        cur.execute(73            """74        CREATE TABLE IF NOT EXISTS personalized_content (75          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),76          user_id UUID REFERENCES users(id) ON DELETE CASCADE,77          chapter_number INT NOT NULL,78          lesson_number INT NOT NULL,79          original_content_hash VARCHAR(64) NOT NULL,80          personalized_content TEXT NOT NULL,81          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,82          UNIQUE(user_id, chapter_number, lesson_number, original_content_hash)83        );84        """85        )86 87        print("📊 Creating index on personalized_content lookup...")88        cur.execute(89            """90        CREATE INDEX IF NOT EXISTS idx_personalized_content_lookup 91        ON personalized_content(user_id, chapter_number, lesson_number);92        """93        )94 95        conn.commit()96        print("✅ All tables created successfully!")97 98        # Verify tables99        cur.execute(100            """101        SELECT table_name FROM information_schema.tables 102        WHERE table_schema = 'public' 103        AND table_name IN ('users', 'user_backgrounds', 'personalized_content');104        """105        )106        tables = cur.fetchall()107        print(f"\n📋 Verified tables: {[t[0] for t in tables]}")108 109    except Exception as e:110        conn.rollback()111        print(f"❌ Error creating tables: {e}")112        raise113    finally:114        cur.close()115        conn.close()116        print("🔌 Database connection closed.")117 118 119if __name__ == "__main__":120    create_tables()121