populate_database.py

# populate_database.py

import mysql.connector
from mysql.connector import Error
import os
from dotenv import load_dotenv
import random
from datetime import datetime, timedelta

load_dotenv()


def get_connection():
    try:
        conn = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', ''),
            database=os.getenv('DB_NAME', 'bookstore')
        )
        return conn
    except Error as e:
        print(f"Error: {e}")
        return None


def populate_database():
    conn = get_connection()
    if not conn:
        print("Failed to connect to database")
        return

    cursor = conn.cursor()

    try:
        print("=" * 60)
        print("POPULATING DATABASE WITH REAL BOOKS")
        print("=" * 60)

        # ==================== PUBLISHERS ====================
        print("\n→ Adding Publishers...")

        publishers = [
            ("Penguin Random House", "1745 Broadway, New York, NY", "212-782-9000"),
            ("HarperCollins", "195 Broadway, New York, NY", "212-207-7000"),
            ("Simon & Schuster", "1230 Avenue of the Americas, New York", "212-698-7000"),
            ("Hachette Book Group", "1290 Avenue of the Americas, New York", "212-364-1100"),
            ("Macmillan Publishers", "120 Broadway, New York, NY", "646-307-5151"),
            ("O'Reilly Media", "1005 Gravenstein Highway North, Sebastopol, CA", "707-827-7000"),
            ("Wiley", "111 River Street, Hoboken, NJ", "201-748-6000"),
            ("Oxford University Press", "Great Clarendon Street, Oxford, UK", "+44-1865-556767"),
            ("Cambridge University Press", "University Printing House, Cambridge, UK", "+44-1223-358331"),
            ("National Geographic", "1145 17th Street NW, Washington, DC", "202-857-7000"),
            ("DK Publishing", "1450 Broadway, New York, NY", "212-213-4800"),
            ("Scholastic", "557 Broadway, New York, NY", "212-343-6100"),
        ]

        for pub in publishers:
            cursor.execute("""
                INSERT IGNORE INTO Publisher (Name, Address, PhoneNumber)
                VALUES (%s, %s, %s)
            """, pub)

        conn.commit()
        print(f"   ✓ Added {len(publishers)} publishers")

        # Get publisher IDs
        cursor.execute("SELECT PublisherID, Name FROM Publisher")
        pub_map = {name: pid for pid, name in cursor.fetchall()}

        # ==================== AUTHORS ====================
        print("\n→ Adding Authors...")

        authors = [
            # Science/Technology Authors
            "Stephen Hawking",
            "Carl Sagan",
            "Neil deGrasse Tyson",
            "Richard Dawkins",
            "Bill Bryson",
            "Brian Greene",
            "Michio Kaku",
            "Robert C. Martin",
            "Martin Fowler",
            "Eric Matthes",
            "Al Sweigart",
            "Luciano Ramalho",
            # History Authors
            "Yuval Noah Harari",
            "Doris Kearns Goodwin",
            "David McCullough",
            "Ron Chernow",
            "Erik Larson",
            "Howard Zinn",
            "Jared Diamond",
            "Anthony Beevor",
            # Art/Literature Authors
            "J.R.R. Tolkien",
            "C.S. Lewis",
            "George R.R. Martin",
            "J.K. Rowling",
            "Stephen King",
            "Ernest Hemingway",
            "F. Scott Fitzgerald",
            "Jane Austen",
            # Religion/Philosophy Authors
            "C.S. Lewis",
            "Dalai Lama",
            "Thich Nhat Hanh",
            "Karen Armstrong",
            "Reza Aslan",
            "Joseph Campbell",
            # Geography/Travel Authors
            "Bill Bryson",
            "Michael Palin",
            "Paul Theroux",
            "Tim Marshall",
        ]

        # Remove duplicates
        authors = list(set(authors))

        for author in authors:
            cursor.execute("INSERT IGNORE INTO Author (Name) VALUES (%s)", (author,))

        conn.commit()
        print(f"   ✓ Added {len(authors)} authors")

        # Get author IDs
        cursor.execute("SELECT AuthorID, Name FROM Author")
        author_map = {name: aid for aid, name in cursor.fetchall()}

        # ==================== BOOKS ====================
        print("\n→ Adding Books...")

        # (ISBN, Title, Year, Price, Category, Stock, Threshold, Publisher, [Authors])
        books = [
            # ===== SCIENCE BOOKS =====
            ("978-0553380163", "A Brief History of Time", 1988, 18.99, "Science",
             random.randint(15, 50), 5, "Penguin Random House", ["Stephen Hawking"]),

            ("978-0345539434", "The Grand Design", 2010, 16.99, "Science",
             random.randint(15, 50), 5, "Penguin Random House", ["Stephen Hawking"]),

            ("978-0345409461", "Cosmos", 1980, 17.00, "Science",
             random.randint(15, 50), 5, "Penguin Random House", ["Carl Sagan"]),

            ("978-0393350876", "Astrophysics for People in a Hurry", 2017, 15.99, "Science",
             random.randint(20, 60), 8, "HarperCollins", ["Neil deGrasse Tyson"]),

            ("978-0544272996", "The Gene: An Intimate History", 2016, 20.00, "Science",
             random.randint(15, 40), 5, "Macmillan Publishers", ["Richard Dawkins"]),

            ("978-0767908184", "A Short History of Nearly Everything", 2003, 18.00, "Science",
             random.randint(20, 50), 8, "Penguin Random House", ["Bill Bryson"]),

            ("978-0393338102", "The Elegant Universe", 1999, 17.95, "Science",
             random.randint(15, 40), 5, "HarperCollins", ["Brian Greene"]),

            ("978-0385514255", "Physics of the Future", 2011, 16.95, "Science",
             random.randint(15, 40), 5, "Penguin Random House", ["Michio Kaku"]),

            ("978-0134685991", "Effective Java", 2018, 54.99, "Science",
             random.randint(20, 50), 8, "O'Reilly Media", ["Robert C. Martin"]),

            ("978-0596517748", "JavaScript: The Good Parts", 2008, 29.99, "Science",
             random.randint(15, 40), 5, "O'Reilly Media", ["Martin Fowler"]),

            ("978-1593279288", "Python Crash Course", 2019, 35.99, "Science",
             random.randint(30, 70), 10, "O'Reilly Media", ["Eric Matthes"]),

            ("978-1593279929", "Automate the Boring Stuff with Python", 2019, 39.95, "Science",
             random.randint(25, 60), 8, "O'Reilly Media", ["Al Sweigart"]),

            ("978-1491957660", "Fluent Python", 2022, 59.99, "Science",
             random.randint(20, 45), 8, "O'Reilly Media", ["Luciano Ramalho"]),

            ("978-0132350884", "Clean Code", 2008, 49.99, "Science",
             random.randint(25, 55), 10, "O'Reilly Media", ["Robert C. Martin"]),

            ("978-0201633610", "Design Patterns", 1994, 59.99, "Science",
             random.randint(15, 35), 5, "O'Reilly Media", ["Martin Fowler"]),

            # ===== HISTORY BOOKS =====
            ("978-0062316097", "Sapiens: A Brief History of Humankind", 2015, 24.99, "History",
             random.randint(30, 70), 10, "HarperCollins", ["Yuval Noah Harari"]),

            ("978-0062464316", "Homo Deus: A Brief History of Tomorrow", 2017, 24.99, "History",
             random.randint(25, 55), 8, "HarperCollins", ["Yuval Noah Harari"]),

            ("978-0525558996", "21 Lessons for the 21st Century", 2018, 28.99, "History",
             random.randint(20, 50), 8, "Penguin Random House", ["Yuval Noah Harari"]),

            ("978-0743270755", "Team of Rivals", 2005, 22.00, "History",
             random.randint(15, 40), 5, "Simon & Schuster", ["Doris Kearns Goodwin"]),

            ("978-0743226721", "John Adams", 2001, 20.00, "History",
             random.randint(15, 40), 5, "Simon & Schuster", ["David McCullough"]),

            ("978-0143034759", "1776", 2005, 18.00, "History",
             random.randint(20, 45), 8, "Penguin Random House", ["David McCullough"]),

            ("978-0143110217", "Alexander Hamilton", 2004, 22.00, "History",
             random.randint(25, 55), 8, "Penguin Random House", ["Ron Chernow"]),

            ("978-0307388537", "The Devil in the White City", 2003, 17.00, "History",
             random.randint(20, 50), 8, "Penguin Random House", ["Erik Larson"]),

            ("978-0060838652", "A People's History of the United States", 1980, 19.99, "History",
             random.randint(15, 40), 5, "HarperCollins", ["Howard Zinn"]),

            ("978-0393317558", "Guns, Germs, and Steel", 1997, 18.95, "History",
             random.randint(20, 50), 8, "HarperCollins", ["Jared Diamond"]),

            ("978-0143035732", "Collapse", 2005, 18.00, "History",
             random.randint(15, 40), 5, "Penguin Random House", ["Jared Diamond"]),

            ("978-0140285017", "Stalingrad", 1998, 20.00, "History",
             random.randint(15, 35), 5, "Penguin Random House", ["Anthony Beevor"]),

            ("978-0143118091", "D-Day: The Battle for Normandy", 2009, 20.00, "History",
             random.randint(15, 40), 5, "Penguin Random House", ["Anthony Beevor"]),

            # ===== ART/LITERATURE BOOKS =====
            ("978-0547928227", "The Hobbit", 1937, 15.99, "Art",
             random.randint(40, 80), 15, "Hachette Book Group", ["J.R.R. Tolkien"]),

            ("978-0544003415", "The Lord of the Rings", 1954, 35.99, "Art",
             random.randint(30, 60), 10, "Hachette Book Group", ["J.R.R. Tolkien"]),

            ("978-0061120084", "The Chronicles of Narnia", 1950, 29.99, "Art",
             random.randint(25, 55), 8, "HarperCollins", ["C.S. Lewis"]),

            ("978-0553103540", "A Game of Thrones", 1996, 18.00, "Art",
             random.randint(35, 70), 12, "Penguin Random House", ["George R.R. Martin"]),

            ("978-0553108033", "A Clash of Kings", 1998, 18.00, "Art",
             random.randint(30, 60), 10, "Penguin Random House", ["George R.R. Martin"]),

            ("978-0439708180", "Harry Potter and the Sorcerer's Stone", 1997, 12.99, "Art",
             random.randint(50, 100), 20, "Scholastic", ["J.K. Rowling"]),

            ("978-0439064873", "Harry Potter and the Chamber of Secrets", 1998, 12.99, "Art",
             random.randint(45, 90), 18, "Scholastic", ["J.K. Rowling"]),

            ("978-0439136365", "Harry Potter and the Prisoner of Azkaban", 1999, 12.99, "Art",
             random.randint(45, 90), 18, "Scholastic", ["J.K. Rowling"]),

            ("978-1501142970", "It", 1986, 19.99, "Art",
             random.randint(25, 55), 8, "Simon & Schuster", ["Stephen King"]),

            ("978-1501175466", "The Shining", 1977, 17.00, "Art",
             random.randint(30, 60), 10, "Simon & Schuster", ["Stephen King"]),

            ("978-0684801223", "The Old Man and the Sea", 1952, 14.99, "Art",
             random.randint(20, 50), 8, "Macmillan Publishers", ["Ernest Hemingway"]),

            ("978-0743273565", "The Great Gatsby", 1925, 15.99, "Art",
             random.randint(35, 70), 12, "Macmillan Publishers", ["F. Scott Fitzgerald"]),

            ("978-0141439518", "Pride and Prejudice", 1813, 9.99, "Art",
             random.randint(30, 65), 10, "Penguin Random House", ["Jane Austen"]),

            ("978-0141439587", "Emma", 1815, 10.99, "Art",
             random.randint(25, 50), 8, "Penguin Random House", ["Jane Austen"]),

            # ===== RELIGION/PHILOSOPHY BOOKS =====
            ("978-0060652920", "Mere Christianity", 1952, 15.99, "Religion",
             random.randint(20, 50), 8, "HarperCollins", ["C.S. Lewis"]),

            ("978-0060652883", "The Screwtape Letters", 1942, 14.99, "Religion",
             random.randint(20, 45), 8, "HarperCollins", ["C.S. Lewis"]),

            ("978-1594489501", "The Book of Joy", 2016, 16.00, "Religion",
             random.randint(20, 50), 8, "Penguin Random House", ["Dalai Lama"]),

            ("978-0062236845", "The Art of Happiness", 1998, 16.99, "Religion",
             random.randint(25, 55), 10, "Penguin Random House", ["Dalai Lama"]),

            ("978-0807012390", "The Miracle of Mindfulness", 1975, 14.95, "Religion",
             random.randint(20, 45), 8, "Penguin Random House", ["Thich Nhat Hanh"]),

            ("978-0061189432", "A History of God", 1993, 18.99, "Religion",
             random.randint(15, 40), 5, "HarperCollins", ["Karen Armstrong"]),

            ("978-0812981483", "Zealot: The Life and Times of Jesus of Nazareth", 2013, 16.00, "Religion",
             random.randint(20, 45), 8, "Penguin Random House", ["Reza Aslan"]),

            ("978-1577315933", "The Hero with a Thousand Faces", 1949, 18.95, "Religion",
             random.randint(15, 40), 5, "HarperCollins", ["Joseph Campbell"]),

            # ===== GEOGRAPHY BOOKS =====
            ("978-1501117909", "In a Sunburned Country", 2000, 17.00, "Geography",
             random.randint(20, 50), 8, "Penguin Random House", ["Bill Bryson"]),

            ("978-0767903868", "A Walk in the Woods", 1998, 16.99, "Geography",
             random.randint(25, 55), 10, "Penguin Random House", ["Bill Bryson"]),

            ("978-0312427566", "Around the World in 80 Days", 2004, 16.99, "Geography",
             random.randint(15, 40), 5, "Macmillan Publishers", ["Michael Palin"]),

            ("978-0618658947", "The Great Railway Bazaar", 1975, 16.95, "Geography",
             random.randint(15, 40), 5, "Hachette Book Group", ["Paul Theroux"]),

            ("978-1501121470", "Prisoners of Geography", 2015, 17.00, "Geography",
             random.randint(25, 55), 10, "Simon & Schuster", ["Tim Marshall"]),

            ("978-1501183218", "The Power of Geography", 2021, 18.99, "Geography",
             random.randint(20, 50), 8, "Simon & Schuster", ["Tim Marshall"]),

            ("978-1426217784", "Atlas of the World", 2019, 195.00, "Geography",
             random.randint(5, 15), 3, "National Geographic", ["Bill Bryson"]),

            ("978-1465438027", "Travel: The Guide", 2016, 30.00, "Geography",
             random.randint(10, 30), 5, "DK Publishing", ["Michael Palin"]),
        ]

        books_added = 0
        for book_data in books:
            isbn, title, year, price, category, stock, threshold, publisher, book_authors = book_data

            # Get publisher ID
            pub_id = pub_map.get(publisher)
            if not pub_id:
                print(f"   ⚠ Publisher not found: {publisher}")
                continue

            # Insert book
            try:
                cursor.execute("""
                    INSERT IGNORE INTO Book 
                    (ISBN, Title, PublicationYear, Price, Category, StockQuantity, ThresholdQuantity, PublisherID)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """, (isbn, title, year, price, category, stock, threshold, pub_id))

                # Link authors
                for author_name in book_authors:
                    author_id = author_map.get(author_name)
                    if author_id:
                        cursor.execute("""
                            INSERT IGNORE INTO BookAuthor (ISBN, AuthorID)
                            VALUES (%s, %s)
                        """, (isbn, author_id))

                books_added += 1

            except Error as e:
                print(f"   ⚠ Error adding book {title}: {e}")

        conn.commit()
        print(f"   ✓ Added {books_added} books")

        # ==================== SAMPLE CUSTOMERS ====================
        print("\n→ Adding Sample Customers...")

        customers = [
            ("john_doe", "pass123", "John", "Doe", "john.doe@email.com", "555-0101", "123 Main St, New York"),
            ("jane_smith", "pass123", "Jane", "Smith", "jane.smith@email.com", "555-0102", "456 Oak Ave, Boston"),
            ("bob_wilson", "pass123", "Bob", "Wilson", "bob.wilson@email.com", "555-0103", "789 Pine Rd, Chicago"),
            ("alice_johnson", "pass123", "Alice", "Johnson", "alice.j@email.com", "555-0104", "321 Elm St, Seattle"),
            ("charlie_brown", "pass123", "Charlie", "Brown", "charlie.b@email.com", "555-0105", "654 Cedar Ln, Miami"),
        ]

        customer_ids = []
        for cust in customers:
            username, password, fname, lname, email, phone, address = cust

            # Check if user exists
            cursor.execute("SELECT UserID FROM User WHERE Username = %s", (username,))
            existing = cursor.fetchone()

            if existing:
                customer_ids.append(existing[0])
            else:
                cursor.execute("""
                    INSERT INTO User (Username, Password, FName, LName, Email, Phone)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """, (username, password, fname, lname, email, phone))

                user_id = cursor.lastrowid
                cursor.execute("INSERT INTO Customer (UserID, Address) VALUES (%s, %s)", (user_id, address))
                customer_ids.append(user_id)

        conn.commit()
        print(f"   ✓ Added {len(customers)} customers")

        # ==================== SAMPLE ORDERS ====================
        print("\n→ Adding Sample Orders...")

        # Get all book ISBNs and prices
        cursor.execute("SELECT ISBN, Price FROM Book")
        book_prices = {isbn: float(price) for isbn, price in cursor.fetchall()}
        book_isbns = list(book_prices.keys())

        orders_created = 0

        for customer_id in customer_ids:
            # Create 2-5 orders per customer
            num_orders = random.randint(2, 5)

            for _ in range(num_orders):
                # Random date in last 90 days
                days_ago = random.randint(1, 90)
                order_date = datetime.now() - timedelta(days=days_ago)

                # Create order
                cursor.execute("""
                    INSERT INTO Sales_Order (CustomerID, Date, TotalAmount)
                    VALUES (%s, %s, %s)
                """, (customer_id, order_date.strftime("%Y-%m-%d"), 0))

                order_id = cursor.lastrowid

                # Add 1-4 items to order
                num_items = random.randint(1, 4)
                selected_books = random.sample(book_isbns, min(num_items, len(book_isbns)))

                total_amount = 0
                for isbn in selected_books:
                    quantity = random.randint(1, 3)
                    price = book_prices[isbn]
                    subtotal = price * quantity
                    total_amount += subtotal

                    cursor.execute("""
                        INSERT INTO Sales_Order_Item (OrderID, ISBN, Quantity, Price)
                        VALUES (%s, %s, %s, %s)
                    """, (order_id, isbn, quantity, price))

                # Update order total
                cursor.execute("""
                    UPDATE Sales_Order SET TotalAmount = %s WHERE OrderID = %s
                """, (total_amount, order_id))

                orders_created += 1

        conn.commit()
        print(f"   ✓ Created {orders_created} sample orders")

        # ==================== SUMMARY ====================
        print("\n" + "=" * 60)
        print("DATABASE POPULATION COMPLETE!")
        print("=" * 60)

        # Show counts
        tables = ['Publisher', 'Author', 'Book', 'User', 'Customer', 'Sales_Order']
        print("\n→ Record Counts:")
        for table in tables:
            cursor.execute(f"SELECT COUNT(*) FROM {table}")
            count = cursor.fetchone()[0]
            print(f"   • {table}: {count}")

        # Show category distribution
        print("\n→ Books by Category:")
        cursor.execute("""
            SELECT Category, COUNT(*) as Count 
            FROM Book 
            GROUP BY Category 
            ORDER BY Count DESC
        """)
        for cat, count in cursor.fetchall():
            print(f"   • {cat}: {count}")

        print("\n" + "=" * 60)
        print("Test Credentials:")
        print("  Admin: admin / admin123")
        print("  Customer: john_doe / pass123")
        print("           jane_smith / pass123")
        print("           bob_wilson / pass123")
        print("=" * 60)

    except Error as e:
        print(f"Error: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    populate_database()