create_test_databases.py

"""
Script to create sample SQLite databases for testing the Database Viewer app
"""
import sqlite3
import os
from datetime import datetime, timedelta
import random

# Create test_databases folder if it doesn't exist
os.makedirs('test_databases', exist_ok=True)

def create_customers_db():
    """Create a simple customers database"""
    conn = sqlite3.connect('test_databases/customers.db')
    cursor = conn.cursor()
    
    # Create customers table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            phone TEXT,
            city TEXT,
            country TEXT,
            registration_date TEXT,
            is_active INTEGER DEFAULT 1
        )
    ''')
    
    # Sample customer data
    customers = [
        ('John', 'Doe', 'john.doe@email.com', '+1-555-0101', 'New York', 'USA', '2024-01-15', 1),
        ('Jane', 'Smith', 'jane.smith@email.com', '+1-555-0102', 'Los Angeles', 'USA', '2024-02-20', 1),
        ('Mike', 'Johnson', 'mike.j@email.com', '+1-555-0103', 'Chicago', 'USA', '2024-03-10', 1),
        ('Emily', 'Brown', 'emily.b@email.com', '+44-20-5550104', 'London', 'UK', '2024-01-25', 1),
        ('David', 'Wilson', 'david.w@email.com', '+1-555-0105', 'Houston', 'USA', '2024-04-05', 0),
        ('Sarah', 'Davis', 'sarah.d@email.com', '+1-555-0106', 'Phoenix', 'USA', '2024-02-14', 1),
        ('Tom', 'Martinez', 'tom.m@email.com', '+1-555-0107', 'Philadelphia', 'USA', '2024-03-22', 1),
        ('Lisa', 'Anderson', 'lisa.a@email.com', '+61-2-5550108', 'Sydney', 'Australia', '2024-01-30', 1),
        ('Chris', 'Taylor', 'chris.t@email.com', '+1-555-0109', 'San Diego', 'USA', '2024-04-12', 1),
        ('Anna', 'Thomas', 'anna.t@email.com', '+49-30-5550110', 'Berlin', 'Germany', '2024-02-28', 1),
    ]
    
    cursor.executemany('''
        INSERT INTO customers (first_name, last_name, email, phone, city, country, registration_date, is_active)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ''', customers)
    
    conn.commit()
    conn.close()
    print("✓ Created customers.db with 10 customers")

def create_ecommerce_db():
    """Create a more complex e-commerce database with multiple tables"""
    conn = sqlite3.connect('test_databases/ecommerce.db')
    cursor = conn.cursor()
    
    # Products table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT,
            price REAL NOT NULL,
            stock INTEGER DEFAULT 0,
            description TEXT
        )
    ''')
    
    products = [
        ('Laptop Pro 15"', 'Electronics', 1299.99, 25, 'High-performance laptop with 16GB RAM'),
        ('Wireless Mouse', 'Electronics', 29.99, 150, 'Ergonomic wireless mouse'),
        ('USB-C Cable', 'Accessories', 12.99, 500, '6ft USB-C charging cable'),
        ('Desk Chair', 'Furniture', 249.99, 40, 'Ergonomic office chair with lumbar support'),
        ('Monitor 27"', 'Electronics', 349.99, 60, '4K UHD monitor'),
        ('Keyboard Mechanical', 'Electronics', 89.99, 75, 'RGB mechanical keyboard'),
        ('Desk Lamp', 'Furniture', 34.99, 120, 'LED desk lamp with adjustable brightness'),
        ('Laptop Stand', 'Accessories', 45.99, 200, 'Aluminum laptop stand'),
        ('Webcam HD', 'Electronics', 79.99, 85, '1080p HD webcam'),
        ('Phone Stand', 'Accessories', 15.99, 300, 'Adjustable phone stand'),
    ]
    
    cursor.executemany('''
        INSERT INTO products (name, category, price, stock, description)
        VALUES (?, ?, ?, ?, ?)
    ''', products)
    
    # Orders table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_name TEXT NOT NULL,
            customer_email TEXT NOT NULL,
            order_date TEXT NOT NULL,
            total_amount REAL NOT NULL,
            status TEXT DEFAULT 'Pending'
        )
    ''')
    
    orders = [
        ('John Doe', 'john.doe@email.com', '2024-10-01', 1329.98, 'Delivered'),
        ('Jane Smith', 'jane.smith@email.com', '2024-10-02', 379.98, 'Shipped'),
        ('Mike Johnson', 'mike.j@email.com', '2024-10-03', 249.99, 'Processing'),
        ('Emily Brown', 'emily.b@email.com', '2024-10-04', 89.99, 'Delivered'),
        ('David Wilson', 'david.w@email.com', '2024-10-05', 1699.96, 'Pending'),
        ('Sarah Davis', 'sarah.d@email.com', '2024-10-06', 45.99, 'Delivered'),
        ('Tom Martinez', 'tom.m@email.com', '2024-10-07', 79.99, 'Shipped'),
        ('Lisa Anderson', 'lisa.a@email.com', '2024-10-08', 424.97, 'Processing'),
    ]
    
    cursor.executemany('''
        INSERT INTO orders (customer_name, customer_email, order_date, total_amount, status)
        VALUES (?, ?, ?, ?, ?)
    ''', orders)
    
    # Reviews table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS reviews (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER,
            customer_name TEXT,
            rating INTEGER CHECK(rating >= 1 AND rating <= 5),
            comment TEXT,
            review_date TEXT,
            FOREIGN KEY (product_id) REFERENCES products(id)
        )
    ''')
    
    reviews = [
        (1, 'John Doe', 5, 'Excellent laptop! Very fast and reliable.', '2024-10-05'),
        (1, 'Jane Smith', 4, 'Great performance but a bit pricey.', '2024-10-06'),
        (2, 'Mike Johnson', 5, 'Perfect mouse, very comfortable.', '2024-10-07'),
        (4, 'Emily Brown', 5, 'Best chair I ever bought!', '2024-10-08'),
        (5, 'David Wilson', 4, 'Beautiful display, worth the money.', '2024-10-09'),
        (6, 'Sarah Davis', 5, 'Love the mechanical feel!', '2024-10-10'),
        (9, 'Tom Martinez', 3, 'Good webcam but could be better in low light.', '2024-10-11'),
    ]
    
    cursor.executemany('''
        INSERT INTO reviews (product_id, customer_name, rating, comment, review_date)
        VALUES (?, ?, ?, ?, ?)
    ''', reviews)
    
    conn.commit()
    conn.close()
    print("✓ Created ecommerce.db with products, orders, and reviews tables")

def create_employees_db():
    """Create an employees database"""
    conn = sqlite3.connect('test_databases/employees.db')
    cursor = conn.cursor()
    
    # Departments table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS departments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            location TEXT,
            budget REAL
        )
    ''')
    
    departments = [
        ('Engineering', 'Building A', 500000),
        ('Marketing', 'Building B', 250000),
        ('Sales', 'Building B', 300000),
        ('Human Resources', 'Building C', 150000),
        ('Finance', 'Building C', 200000),
    ]
    
    cursor.executemany('''
        INSERT INTO departments (name, location, budget)
        VALUES (?, ?, ?)
    ''', departments)
    
    # Employees table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            department_id INTEGER,
            position TEXT,
            salary REAL,
            hire_date TEXT,
            FOREIGN KEY (department_id) REFERENCES departments(id)
        )
    ''')
    
    employees = [
        ('Alice', 'Johnson', 'alice.j@company.com', 1, 'Senior Engineer', 95000, '2020-03-15'),
        ('Bob', 'Williams', 'bob.w@company.com', 1, 'Software Engineer', 75000, '2021-06-01'),
        ('Carol', 'Davis', 'carol.d@company.com', 1, 'Lead Engineer', 110000, '2019-01-10'),
        ('Dan', 'Miller', 'dan.m@company.com', 2, 'Marketing Manager', 85000, '2020-08-20'),
        ('Eve', 'Garcia', 'eve.g@company.com', 2, 'Content Specialist', 60000, '2022-02-14'),
        ('Frank', 'Martinez', 'frank.m@company.com', 3, 'Sales Director', 100000, '2018-11-05'),
        ('Grace', 'Rodriguez', 'grace.r@company.com', 3, 'Sales Representative', 55000, '2021-09-12'),
        ('Henry', 'Wilson', 'henry.w@company.com', 4, 'HR Manager', 70000, '2020-05-18'),
        ('Iris', 'Lopez', 'iris.l@company.com', 5, 'Financial Analyst', 72000, '2021-03-22'),
        ('Jack', 'Lee', 'jack.l@company.com', 5, 'Accountant', 65000, '2022-07-01'),
    ]
    
    cursor.executemany('''
        INSERT INTO employees (first_name, last_name, email, department_id, position, salary, hire_date)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', employees)
    
    # Projects table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            department_id INTEGER,
            start_date TEXT,
            end_date TEXT,
            status TEXT,
            budget REAL,
            FOREIGN KEY (department_id) REFERENCES departments(id)
        )
    ''')
    
    projects = [
        ('Website Redesign', 1, '2024-01-15', '2024-06-30', 'Completed', 75000),
        ('Mobile App Development', 1, '2024-03-01', '2024-12-31', 'In Progress', 150000),
        ('Q4 Marketing Campaign', 2, '2024-09-01', '2024-12-31', 'In Progress', 50000),
        ('Customer Database Migration', 1, '2024-02-01', '2024-04-30', 'Completed', 40000),
        ('Employee Training Program', 4, '2024-01-01', '2024-12-31', 'In Progress', 25000),
    ]
    
    cursor.executemany('''
        INSERT INTO projects (name, department_id, start_date, end_date, status, budget)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', projects)
    
    conn.commit()
    conn.close()
    print("✓ Created employees.db with departments, employees, and projects tables")

def create_library_db():
    """Create a library database"""
    conn = sqlite3.connect('test_databases/library.db')
    cursor = conn.cursor()
    
    # Books table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            isbn TEXT UNIQUE,
            publisher TEXT,
            year INTEGER,
            genre TEXT,
            copies_available INTEGER DEFAULT 1
        )
    ''')
    
    books = [
        ('The Great Gatsby', 'F. Scott Fitzgerald', '978-0743273565', 'Scribner', 1925, 'Fiction', 3),
        ('To Kill a Mockingbird', 'Harper Lee', '978-0061120084', 'Harper Perennial', 1960, 'Fiction', 4),
        ('1984', 'George Orwell', '978-0451524935', 'Signet Classic', 1949, 'Science Fiction', 5),
        ('Pride and Prejudice', 'Jane Austen', '978-0141439518', 'Penguin Classics', 1813, 'Romance', 2),
        ('The Catcher in the Rye', 'J.D. Salinger', '978-0316769174', 'Little, Brown', 1951, 'Fiction', 3),
        ('Harry Potter and the Sorcerer\'s Stone', 'J.K. Rowling', '978-0439708180', 'Scholastic', 1997, 'Fantasy', 6),
        ('The Hobbit', 'J.R.R. Tolkien', '978-0547928227', 'Houghton Mifflin', 1937, 'Fantasy', 4),
        ('Fahrenheit 451', 'Ray Bradbury', '978-1451673319', 'Simon & Schuster', 1953, 'Science Fiction', 3),
        ('Jane Eyre', 'Charlotte Brontë', '978-0141441146', 'Penguin Classics', 1847, 'Romance', 2),
        ('Animal Farm', 'George Orwell', '978-0451526342', 'Signet Classic', 1945, 'Political Fiction', 4),
    ]
    
    cursor.executemany('''
        INSERT INTO books (title, author, isbn, publisher, year, genre, copies_available)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', books)
    
    # Members table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS members (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            phone TEXT,
            join_date TEXT,
            membership_type TEXT
        )
    ''')
    
    members = [
        ('Alice Cooper', 'alice.c@email.com', '555-0201', '2023-01-15', 'Premium'),
        ('Bob Taylor', 'bob.t@email.com', '555-0202', '2023-03-20', 'Standard'),
        ('Carol White', 'carol.w@email.com', '555-0203', '2023-05-10', 'Premium'),
        ('David Green', 'david.g@email.com', '555-0204', '2023-07-05', 'Standard'),
        ('Emma Black', 'emma.b@email.com', '555-0205', '2023-09-12', 'Standard'),
        ('Frank Blue', 'frank.b@email.com', '555-0206', '2024-01-08', 'Premium'),
    ]
    
    cursor.executemany('''
        INSERT INTO members (name, email, phone, join_date, membership_type)
        VALUES (?, ?, ?, ?, ?)
    ''', members)
    
    # Loans table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS loans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            member_id INTEGER,
            loan_date TEXT,
            due_date TEXT,
            return_date TEXT,
            status TEXT DEFAULT 'Active',
            FOREIGN KEY (book_id) REFERENCES books(id),
            FOREIGN KEY (member_id) REFERENCES members(id)
        )
    ''')
    
    loans = [
        (1, 1, '2024-09-15', '2024-10-15', None, 'Active'),
        (3, 2, '2024-09-20', '2024-10-20', None, 'Active'),
        (6, 3, '2024-09-25', '2024-10-25', '2024-10-10', 'Returned'),
        (7, 4, '2024-09-28', '2024-10-28', None, 'Active'),
        (2, 5, '2024-10-01', '2024-11-01', None, 'Active'),
        (8, 1, '2024-10-05', '2024-11-05', None, 'Active'),
    ]
    
    cursor.executemany('''
        INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date, status)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', loans)
    
    conn.commit()
    conn.close()
    print("✓ Created library.db with books, members, and loans tables")

if __name__ == '__main__':
    print("\n🗄️  Creating test databases...\n")
    
    create_customers_db()
    create_ecommerce_db()
    create_employees_db()
    create_library_db()
    
    print("\n✅ All test databases created successfully in 'test_databases/' folder!")
    print("\nYou can now upload these databases to test the application:")
    print("  - customers.db (Simple customer data)")
    print("  - ecommerce.db (Products, orders, and reviews)")
    print("  - employees.db (Departments, employees, and projects)")
    print("  - library.db (Books, members, and loans)")
    print()