Activity 20: Python Orm Sqlalchemy

Use localhost only

1. Set up the Environment

• Install Python if it's not already installed. You can download it from python.org.

• Set up a virtual environment:

    python -m venv venv
    venv\Scripts\activate
  

  

• Install required libraries:

    pip install sqlalchemy mysql-connector-python
  

  

2. Create the Database and Schema

The SQL schema you provided can be translated into Python code using SQLAlchemy ORM.

ORMQuiz

from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey, Enum, Boolean, DateTime, DECIMAL, TIMESTAMP
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ormquiz_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Define the User model
class User(Base):
    __tablename__ = 'User'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    password = Column(String(255), nullable=False)
    role = Column(Enum('teacher', 'student', name='role_enum'), nullable=False, default='student')
    createdAt = Column(DateTime, default=datetime.utcnow)
    updatedAt = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

# Define the Quiz model
class Quiz(Base):
    __tablename__ = 'Quiz'

    quiz_id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    description = Column(Text)
    quiz_code = Column(String(255), unique=True)
    teacher_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    duration = Column(Integer)
    createdAt = Column(DateTime, default=datetime.utcnow)
    updatedAt = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    teacher = relationship('User', back_populates='quizzes')

# Define the Question model
class Question(Base):
    __tablename__ = 'Question'

    question_id = Column(Integer, primary_key=True, autoincrement=True)
    quiz_id = Column(Integer, ForeignKey('Quiz.quiz_id'), nullable=False)
    question_text = Column(Text, nullable=False)
    question_type = Column(Enum('MCQ', 'True/False', 'Short Answer', 'Multimedia', name='question_type_enum'), nullable=False)
    createdAt = Column(DateTime, default=datetime.utcnow)
    updatedAt = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    quiz = relationship('Quiz', back_populates='questions')

# Define the OptionTable model
class OptionTable(Base):
    __tablename__ = 'OptionTable'

    option_id = Column(Integer, primary_key=True, autoincrement=True)
    quiz_id = Column(Integer, ForeignKey('Quiz.quiz_id'), nullable=False)
    question_id = Column(Integer, ForeignKey('Question.question_id'), nullable=False)
    option_text = Column(String(255), nullable=False)
    is_correct = Column(Boolean, default=False)

# Define the Answer model
class Answer(Base):
    __tablename__ = 'Answer'

    answer_id = Column(Integer, primary_key=True, autoincrement=True)
    question_id = Column(Integer, ForeignKey('Question.question_id'), nullable=False)
    student_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    answer_text = Column(Text)
    submitted_at = Column(DateTime, default=datetime.utcnow)

# Define the Result model
class Result(Base):
    __tablename__ = 'Result'

    result_id = Column(Integer, primary_key=True, autoincrement=True)
    quiz_id = Column(Integer, ForeignKey('Quiz.quiz_id'), nullable=False)
    student_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    score = Column(DECIMAL(5,2))
    submitted_at = Column(DateTime, default=datetime.utcnow)

# Relationship setups
User.quizzes = relationship('Quiz', back_populates='teacher')
Quiz.questions = relationship('Question', back_populates='quiz')




# Create tables
Base.metadata.create_all(engine)

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Add 5 Users
users = [
    User(name='Jov Roncal 1', email='jovroncal1@example.com', password='password123', role='teacher'),
    User(name='Jov Roncal 2', email='jovroncal2@example.com', password='password123', role='student'),
    User(name='Jov Roncal 3', email='jovroncal3@example.com', password='password123', role='student'),
    User(name='Jov Roncal 4', email='jovroncal4@example.com', password='password123', role='student'),
    User(name='Jov Roncal 5', email='jovroncal5@example.com', password='password123', role='teacher')
]

session.add_all(users)
session.commit()

# Add 5 Quizzes
quizzes = [
    Quiz(title='Math Quiz 2', description='Intermediate Math Quiz', teacher_id=1, quiz_code='MATH456', duration=40),
    Quiz(title='Science Quiz 2', description='Advanced Science Quiz', teacher_id=2, quiz_code='SCI456', duration=50),
    Quiz(title='History Quiz 2', description='Advanced History Quiz', teacher_id=3, quiz_code='HIST456', duration=30),
    Quiz(title='Geography Quiz 2', description='Advanced Geography Quiz', teacher_id=4, quiz_code='GEO456', duration=45),
    Quiz(title='Literature Quiz 2', description='Advanced Literature Quiz', teacher_id=5, quiz_code='LIT456', duration=60)
]
session.add_all(quizzes)
session.commit()

# Add 5 Questions for each quiz (total 5 quizzes)
questions = [
    # Math Quiz 2
    Question(quiz_id=1, question_text='What is 15 * 3?', question_type='MCQ'),
    Question(quiz_id=1, question_text='True or False: Pi is a rational number.', question_type='True/False'),
    # Science Quiz 2
    Question(quiz_id=2, question_text='What is the chemical symbol for Water?', question_type='MCQ'),
    Question(quiz_id=2, question_text='True or False: Light travels faster than sound.', question_type='True/False'),
    # History Quiz 2
    Question(quiz_id=3, question_text='Who wrote the Declaration of Independence?', question_type='Short Answer')
]

session.add_all(questions)
session.commit()

# Add 5 Options for each question
options = [
    # Math Quiz 2 - 1st Question
    OptionTable(quiz_id=1, question_id=1, option_text='40', is_correct=False),
    OptionTable(quiz_id=1, question_id=1, option_text='45', is_correct=True),
    # Science Quiz 2 - 1st Question
    OptionTable(quiz_id=2, question_id=3, option_text='H2O', is_correct=True),
    OptionTable(quiz_id=2, question_id=3, option_text='CO2', is_correct=False),
    # History Quiz 2 - 1st Question
    OptionTable(quiz_id=3, question_id=5, option_text='Thomas Jefferson', is_correct=True)
]

session.add_all(options)
session.commit()

# Add 5 Answers for questions
answers = [
    Answer(question_id=1, student_id=1, answer_text='45'),
    Answer(question_id=2, student_id=2, answer_text='False'),
    Answer(question_id=3, student_id=3, answer_text='H2O'),
    Answer(question_id=4, student_id=4, answer_text='True'),
    Answer(question_id=5, student_id=5, answer_text='Thomas Jefferson')
]

session.add_all(answers)
session.commit()

# Add 5 Results
results = [
    Result(quiz_id=1, student_id=1, score=85.0),
    Result(quiz_id=2, student_id=2, score=95.0),
    Result(quiz_id=3, student_id=3, score=80.0),
    Result(quiz_id=4, student_id=4, score=90.0),
    Result(quiz_id=5, student_id=5, score=98.0)
]

session.add_all(results)
session.commit()

print("Dummy data inserted successfully.")

# Create a new user
def add_user():
    user_data = {
        "name": "ramon jov",
        "email": "hotdog@example.com",
        "password": "password123",
        "role": "student"
    }
    new_user = User(**user_data)
    session.add(new_user)
    session.commit()
    print(f"User titled '{new_user.name}' added.")


# Read all users
def get_all_users():
    users = session.query(User).all()
    if users:
        for user in users:
            print(f"User: {user.name}, Email: {user.email}, Role: {user.role}")
    else:
        print("No users found.")


# Update an existing user
def update_user():
    user_to_update = session.query(User).filter_by(user_id=1).first()
    if user_to_update:
        user_to_update.name = "Jov Roncal"
        user_to_update.role = "teacher"
        session.commit()
        print(f"User ID {user_to_update.user_id} updated to '{user_to_update.name}' with role '{user_to_update.role}'.")
    else:
        print("User not found.")


# Delete a user
def delete_user():
    user_to_delete = session.query(User).filter_by(user_id=6).first()
    if user_to_delete:
        session.delete(user_to_delete)
        session.commit()
        print(f"User titled '{user_to_delete.name}' deleted.")
    else:
        print("User not found.")


# Running the CRUD operations for User
if __name__ == "__main__":
    print("\n--- User CRUD Operations ---")
    print("\n--- Initial Users ---")
    get_all_users()

    print("\nAdding a new user...")
    add_user()

    print("\nReading users after adding...")
    get_all_users()

    print("\nUpdating a user...")
    update_user()

    print("\nReading users after updating...")
    get_all_users()

    print("\nDeleting a user...")
    delete_user()

    print("\nReading users after deleting...")
    get_all_users()

USER

QUIZ

QUESTION

OPTIONTABLE

ANSWER

RESULT

ORMEcommerce

from sqlalchemy import create_engine, Column, Integer, String, Text, DECIMAL, DateTime, Enum, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from datetime import datetime

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ecommerce_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Define the User model
class User(Base):
    __tablename__ = 'USER'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(255), unique=True, nullable=False)
    password = Column(String(255), nullable=False)
    name = Column(String(255))
    address = Column(String(255))
    phone = Column(String(255))
    role = Column(Enum('customer', 'admin', name='role_enum'), default='customer')
    created_at = Column(DateTime, default=datetime.utcnow)

# Define the Product model
class Product(Base):
    __tablename__ = 'PRODUCTS'

    product_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    brand = Column(String(255))
    description = Column(Text)
    price = Column(DECIMAL(10, 2))
    stock = Column(Integer)
    category = Column(String(255))
    SKU = Column(String(255), unique=True)
    image_url = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)

# Define the Order model
class Order(Base):
    __tablename__ = 'ORDERS'

    order_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('USER.user_id'))
    total_amount = Column(DECIMAL(10, 2))
    status = Column(Enum('pending', 'shipped', 'delivered', 'cancelled', name='order_status_enum'), default='pending')
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User', back_populates='orders')

# Define the OrderItem model
class OrderItem(Base):
    __tablename__ = 'ORDER_ITEMS'

    order_item_id = Column(Integer, primary_key=True, autoincrement=True)
    order_id = Column(Integer, ForeignKey('ORDERS.order_id'))
    product_id = Column(Integer, ForeignKey('PRODUCTS.product_id'))
    quantity = Column(Integer, nullable=False)
    price = Column(DECIMAL(10, 2))

    order = relationship('Order', back_populates='order_items')
    product = relationship('Product', back_populates='order_items')

# Define the Review model
class Review(Base):
    __tablename__ = 'REVIEWS'

    review_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('USER.user_id'))
    product_id = Column(Integer, ForeignKey('PRODUCTS.product_id'))
    rating = Column(Integer, nullable=False)
    comment = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User', back_populates='reviews')
    product = relationship('Product', back_populates='reviews')

# Set up the relationships in the models
User.orders = relationship('Order', back_populates='user')
Order.order_items = relationship('OrderItem', back_populates='order')
Product.order_items = relationship('OrderItem', back_populates='product')
Product.reviews = relationship('Review', back_populates='product')
User.reviews = relationship('Review', back_populates='user')

# Create tables
Base.metadata.create_all(engine)

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Sample data to insert into tables (5 values for each table)
def add_sample_data():
    # Add Users
    user1 = User(email='customer1@example.com', password='password123', name='Ramon Jov', role='customer')
    user2 = User(email='customer2@example.com', password='password123', name='Ramon', role='customer')
    user3 = User(email='admin1@example.com', password='admin123', name='Admin User', role='admin')
    user4 = User(email='customer3@example.com', password='password123', name='Jov Roncal', role='customer')
    user5 = User(email='customer4@example.com', password='password123', name='Roncal ', role='customer')
    session.add_all([user1, user2, user3, user4, user5])

    # Add Products (Cellphones)
    product1 = Product(name='iPhone 14', brand='Apple', description='Latest iPhone model with 5G support', price=999.99, stock=50, category='Cellphone', SKU='IPH14-001', image_url='iphone14.jpg')
    product2 = Product(name='Samsung Galaxy S22', brand='Samsung', description='Premium Android smartphone with excellent camera', price=799.99, stock=40, category='Cellphone', SKU='SGS22-002', image_url='galaxys22.jpg')
    product3 = Product(name='Google Pixel 6', brand='Google', description='Android phone with Google\'s exclusive features', price=599.99, stock=60, category='Cellphone', SKU='PIX6-003', image_url='pixel6.jpg')
    product4 = Product(name='OnePlus 9 Pro', brand='OnePlus', description='High-performance Android phone with fast charging', price=899.99, stock=30, category='Cellphone', SKU='OP9P-004', image_url='oneplus9pro.jpg')
    product5 = Product(name='Xiaomi Mi 11', brand='Xiaomi', description='Affordable flagship phone with great value', price=699.99, stock=70, category='Cellphone', SKU='XM11-005', image_url='xiaomi11.jpg')
    session.add_all([product1, product2, product3, product4, product5])

    # Add Orders
    order1 = Order(user_id=1, total_amount=1799.98, status='pending')
    order2 = Order(user_id=2, total_amount=1399.98, status='shipped')
    order3 = Order(user_id=4, total_amount=1499.98, status='delivered')
    order4 = Order(user_id=5, total_amount=1299.98, status='pending')
    order5 = Order(user_id=3, total_amount=899.99, status='cancelled')
    session.add_all([order1, order2, order3, order4, order5])

    # Add Order Items
    order_item1 = OrderItem(order_id=1, product_id=1, quantity=1, price=999.99)
    order_item2 = OrderItem(order_id=1, product_id=2, quantity=1, price=799.99)
    order_item3 = OrderItem(order_id=2, product_id=3, quantity=1, price=599.99)
    order_item4 = OrderItem(order_id=2, product_id=4, quantity=1, price=899.99)
    order_item5 = OrderItem(order_id=3, product_id=2, quantity=2, price=799.99)
    session.add_all([order_item1, order_item2, order_item3, order_item4, order_item5])

    # Add Reviews
    review1 = Review(user_id=1, product_id=1, rating=5, comment='Excellent phone, worth the price!')
    review2 = Review(user_id=2, product_id=2, rating=4, comment='Good phone but the battery could be better.')
    review3 = Review(user_id=4, product_id=3, rating=5, comment='Great camera and performance!')
    review4 = Review(user_id=5, product_id=4, rating=4, comment='Solid performance but a bit bulky.')
    review5 = Review(user_id=3, product_id=5, rating=3, comment='Affordable but lacks some premium features.')
    session.add_all([review1, review2, review3, review4, review5])

    session.commit()

# Insert sample data
add_sample_data()

print("Sample data inserted successfully.")

# Closing the session
session.close()

from sqlalchemy import create_engine, Column, Integer, String, Text, DECIMAL, DateTime, Enum, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from datetime import datetime

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ecommerce_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Define the User model
class User(Base):
    __tablename__ = 'USER'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(255), unique=True, nullable=False)
    password = Column(String(255), nullable=False)
    name = Column(String(255))
    address = Column(String(255))
    phone = Column(String(255))
    role = Column(Enum('customer', 'admin', name='role_enum'), default='customer')
    created_at = Column(DateTime, default=datetime.utcnow)

# Set up session
Session = sessionmaker(bind=engine)
session = Session()

# Ensure the tables exist
Base.metadata.create_all(engine)

# 1. CREATE User: Insert a new user if the email does not already exist
def add_user(email, password, name, address, phone, role='customer'):
    # Check if user already exists by email
    existing_user = session.query(User).filter_by(email=email).first()
    if existing_user:
        print(f"User with email {email} already exists.")
        return existing_user
    else:
        new_user = User(email=email, password=password, name=name, address=address, phone=phone, role=role)
        session.add(new_user)
        session.commit()
        print(f"User '{name}' added with email {email}.")
        return new_user

# 2. READ User: Fetch all users
def get_all_users():
    users = session.query(User).all()
    if users:
        for user in users:
            print(f"User ID: {user.user_id}, Name: {user.name}, Email: {user.email}, Role: {user.role}")
    else:
        print("No users found.")

# 3. UPDATE User: Update user's name or role by user_id
def update_user(user_id, new_name=None, new_role=None):
    user_to_update = session.query(User).filter_by(user_id=user_id).first()
    if user_to_update:
        if new_name:
            user_to_update.name = new_name
        if new_role:
            user_to_update.role = new_role
        session.commit()
        print(f"User ID {user_to_update.user_id} updated to '{user_to_update.name}' with role '{user_to_update.role}'.")
    else:
        print(f"User with ID {user_id} not found.")

# 4. DELETE User: Delete a user by user_id
def delete_user(user_id):
    user_to_delete = session.query(User).filter_by(user_id=user_id).first()
    if user_to_delete:
        session.delete(user_to_delete)
        session.commit()
        print(f"User with ID {user_to_delete.user_id} deleted.")
    else:
        print(f"User with ID {user_id} not found.")

# Example usage of CRUD operations

def perform_crud_operations():
    # Create Users
    add_user('newcustomer@example.com', 'password123', 'New Customer', '123 Street', '555-1234', 'customer')
    add_user('admin@example.com', 'adminpassword', 'Admin User', '456 Admin St', '555-5678', 'admin')

    # Read all Users
    print("\n--- All Users ---")
    get_all_users()

    # Update a User (Change role of user with ID 1)
    print("\n--- Updating User ID 1 ---")
    update_user(1, new_name='Updated Customer', new_role='admin')

    # Read all Users after update
    print("\n--- All Users After Update ---")
    get_all_users()

    # Delete a User (Delete user with ID 2)
    print("\n--- Deleting User ID 2 ---")
    delete_user(2)

    # Read all Users after delete
    print("\n--- All Users After Deletion ---")
    get_all_users()

# Run CRUD operations
perform_crud_operations()

# Close session
session.close()

user tables

products tables

orders tables

order_items tables

reviews tables

ORMJobBoard

from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, ForeignKey, Enum, DECIMAL, Date, Boolean
from sqlalchemy.orm import declarative_base, relationship, sessionmaker  # Corrected import
from sqlalchemy.dialects.mysql import BLOB
from datetime import datetime, timezone
import random  # Add missing import

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ormjobboard_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Define the Authentication model
class Authentication(Base):
    __tablename__ = 'Authentication'

    authentication_id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(255), nullable=False)
    email = Column(String(255), nullable=False)
    password_hash = Column(String(255), nullable=False)
    role = Column(Enum('user', 'admin', name='role_enum'), nullable=False)
    created_at = Column(DateTime, default=datetime.now(timezone.utc))  # UPDATED LINE
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc))  # UPDATED LINE
    deleted_at = Column(DateTime, nullable=True)

# Define the User model
class User(Base):
    __tablename__ = 'User'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    authentication_id = Column(Integer, ForeignKey('Authentication.authentication_id'), nullable=False)
    name = Column(String(255), nullable=False)
    birth_date = Column(Date, nullable=True)
    skills = Column(Text, nullable=True)
    work_experience = Column(Text, nullable=True)
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc))  # UPDATED LINE

    authentication = relationship('Authentication', backref='users')

# Define the Job Posting model
class JobPosting(Base):
    __tablename__ = 'Job_Posting'

    job_id = Column(Integer, primary_key=True, autoincrement=True)
    employer_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    job_title = Column(String(255), nullable=False)
    job_description = Column(Text, nullable=True)
    location = Column(String(255), nullable=True)
    category = Column(String(255), nullable=True)
    industry = Column(String(255), nullable=True)
    min_salary = Column(DECIMAL(10, 2), nullable=True)
    max_salary = Column(DECIMAL(10, 2), nullable=True)
    created_at = Column(DateTime, default=datetime.now(timezone.utc))  # UPDATED LINE
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc))  # UPDATED LINE
    deleted_at = Column(DateTime, nullable=True)

    employer = relationship('User', backref='job_postings')

# Define the Job Performance model
class JobPerformance(Base):
    __tablename__ = 'Job_Performance'

    performance_id = Column(Integer, primary_key=True, autoincrement=True)
    job_id = Column(Integer, ForeignKey('Job_Posting.job_id'), nullable=False)
    applicants_count = Column(Integer, nullable=True)
    views_count = Column(Integer, nullable=True)
    open_date = Column(DateTime, nullable=True)
    close_date = Column(DateTime, nullable=True)
    time_to_fill = Column(Integer, nullable=True)
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc))  # UPDATED LINE

    job = relationship('JobPosting', backref='performances')

# Define the Job Interaction model
class JobInteraction(Base):
    __tablename__ = 'Job_Interaction'

    interaction_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    job_id = Column(Integer, ForeignKey('Job_Posting.job_id'), nullable=False)
    interaction_type = Column(Enum('view', 'apply', 'save', name='interaction_type_enum'), nullable=False)
    interaction_date = Column(DateTime, default=datetime.now(timezone.utc))  # UPDATED LINE
    is_applied = Column(Boolean, default=False)

    user = relationship('User', backref='job_interactions')
    job = relationship('JobPosting', backref='job_interactions')

# Define the Application model
class Application(Base):
    __tablename__ = 'Application'

    application_id = Column(Integer, primary_key=True, autoincrement=True)
    job_seeker_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    job_id = Column(Integer, ForeignKey('Job_Posting.job_id'), nullable=False)
    resume = Column(BLOB, nullable=True)
    status = Column(Enum('pending', 'interview', 'rejected', 'accepted', name='application_status_enum'), nullable=False)
    skills = Column(Text, nullable=True)
    work_experience = Column(Text, nullable=True)
    applied_at = Column(DateTime, default=datetime.now(timezone.utc))  # UPDATED LINE
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc))  # UPDATED LINE
    deleted_at = Column(DateTime, nullable=True)

    job_seeker = relationship('User', backref='applications')
    job = relationship('JobPosting', backref='applications')

# Define the Message model
class Message(Base):
    __tablename__ = 'Message'

    message_id = Column(Integer, primary_key=True, autoincrement=True)
    sender_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    recipient_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    application_id = Column(Integer, ForeignKey('Application.application_id'), nullable=True)
    message_text = Column(Text, nullable=False)
    is_read = Column(Boolean, default=False)
    message_type = Column(Enum('text', 'image', 'file', name='message_type_enum'), nullable=False)
    sent_at = Column(DateTime, default=datetime.now(timezone.utc))  # UPDATED LINE
    deleted_at = Column(DateTime, nullable=True)

    sender = relationship('User', foreign_keys=[sender_id], backref='sent_messages')
    recipient = relationship('User', foreign_keys=[recipient_id], backref='received_messages')
    application = relationship('Application', backref='messages')


# Create tables
Base.metadata.create_all(engine)

print("Tables created successfully.")

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Add 5 Authentication records
authentications = [
    Authentication(username='user1', email='user1@example.com', password_hash='hashed_password1', role='user', created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Authentication(username='admin1', email='admin1@example.com', password_hash='hashed_password2', role='admin', created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Authentication(username='user2', email='user2@example.com', password_hash='hashed_password3', role='user', created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Authentication(username='user3', email='user3@example.com', password_hash='hashed_password4', role='user', created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Authentication(username='admin2', email='admin2@example.com', password_hash='hashed_password5', role='admin', created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc))
]
session.add_all(authentications)
session.commit()

# Add 5 User records
users = [
    User(authentication_id=1, name='Roncal', birth_date=datetime(1990, 5, 15), skills='Python, SQL', work_experience='2 years as a developer', updated_at=datetime.now(timezone.utc)),
    User(authentication_id=2, name='Jov', birth_date=datetime(1985, 3, 20), skills='JavaScript, React', work_experience='5 years as a frontend developer', updated_at=datetime.now(timezone.utc)),
    User(authentication_id=3, name='Ramon', birth_date=datetime(1992, 7, 25), skills='Java, Spring', work_experience='3 years as a backend developer', updated_at=datetime.now(timezone.utc)),
    User(authentication_id=4, name='Roel', birth_date=datetime(1988, 8, 30), skills='HTML, CSS, JavaScript', work_experience='4 years as a web developer', updated_at=datetime.now(timezone.utc)),
    User(authentication_id=5, name='Ramon Jov', birth_date=datetime(1995, 12, 5), skills='PHP, Laravel', work_experience='1 year as a full-stack developer', updated_at=datetime.now(timezone.utc))
]
session.add_all(users)
session.commit()

# Add 5 Job Posting records
job_postings = [
    JobPosting(employer_id=1, job_title='Frontend Developer', job_description='Looking for a frontend developer with React experience.', location='New York', category='Engineering', industry='Tech', min_salary=60000, max_salary=80000, created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    JobPosting(employer_id=2, job_title='Backend Developer', job_description='Looking for a backend developer with Java experience.', location='San Francisco', category='Engineering', industry='Tech', min_salary=70000, max_salary=90000, created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    JobPosting(employer_id=3, job_title='Full Stack Developer', job_description='Looking for a full-stack developer with experience in Node.js and React.', location='Austin', category='Engineering', industry='Tech', min_salary=75000, max_salary=95000, created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    JobPosting(employer_id=4, job_title='Web Developer', job_description='Looking for a web developer with HTML, CSS, and JavaScript skills.', location='Chicago', category='Engineering', industry='Tech', min_salary=50000, max_salary=70000, created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    JobPosting(employer_id=5, job_title='PHP Developer', job_description='Looking for a PHP developer with experience in Laravel.', location='Seattle', category='Engineering', industry='Tech', min_salary=65000, max_salary=85000, created_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc))
]
session.add_all(job_postings)
session.commit()

# Add 5 Job Performance records
job_performances = [
    JobPerformance(job_id=1, applicants_count=random.randint(5, 50), views_count=random.randint(100, 1000), open_date=datetime.now(timezone.utc), close_date=datetime.now(timezone.utc), time_to_fill=random.randint(10, 30), updated_at=datetime.now(timezone.utc)),
    JobPerformance(job_id=2, applicants_count=random.randint(5, 50), views_count=random.randint(100, 1000), open_date=datetime.now(timezone.utc), close_date=datetime.now(timezone.utc), time_to_fill=random.randint(10, 30), updated_at=datetime.now(timezone.utc)),
    JobPerformance(job_id=3, applicants_count=random.randint(5, 50), views_count=random.randint(100, 1000), open_date=datetime.now(timezone.utc), close_date=datetime.now(timezone.utc), time_to_fill=random.randint(10, 30), updated_at=datetime.now(timezone.utc)),
    JobPerformance(job_id=4, applicants_count=random.randint(5, 50), views_count=random.randint(100, 1000), open_date=datetime.now(timezone.utc), close_date=datetime.now(timezone.utc), time_to_fill=random.randint(10, 30), updated_at=datetime.now(timezone.utc)),
    JobPerformance(job_id=5, applicants_count=random.randint(5, 50), views_count=random.randint(100, 1000), open_date=datetime.now(timezone.utc), close_date=datetime.now(timezone.utc), time_to_fill=random.randint(10, 30), updated_at=datetime.now(timezone.utc))
]
session.add_all(job_performances)
session.commit()

# Add 5 Job Interaction records
job_interactions = [
    JobInteraction(user_id=1, job_id=1, interaction_type='view', interaction_date=datetime.now(timezone.utc), is_applied=False),
    JobInteraction(user_id=2, job_id=2, interaction_type='apply', interaction_date=datetime.now(timezone.utc), is_applied=True),
    JobInteraction(user_id=3, job_id=3, interaction_type='save', interaction_date=datetime.now(timezone.utc), is_applied=False),
    JobInteraction(user_id=4, job_id=4, interaction_type='view', interaction_date=datetime.now(timezone.utc), is_applied=False),
    JobInteraction(user_id=5, job_id=5, interaction_type='apply', interaction_date=datetime.now(timezone.utc), is_applied=True)
]
session.add_all(job_interactions)
session.commit()

# Add 5 Application records
applications = [
    Application(job_seeker_id=1, job_id=1, status='pending', skills='React, JavaScript', work_experience='3 years as a frontend developer', applied_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Application(job_seeker_id=2, job_id=2, status='accepted', skills='Java, Spring', work_experience='5 years as a backend developer', applied_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Application(job_seeker_id=3, job_id=3, status='interview', skills='Node.js, Express', work_experience='2 years as a full-stack developer', applied_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Application(job_seeker_id=4, job_id=4, status='rejected', skills='HTML, CSS', work_experience='4 years as a web developer', applied_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc)),
    Application(job_seeker_id=5, job_id=5, status='pending', skills='PHP, Laravel', work_experience='1 year as a full-stack developer', applied_at=datetime.now(timezone.utc), updated_at=datetime.now(timezone.utc))
]
session.add_all(applications)
session.commit()

# Add 5 Message records
messages = [
    Message(sender_id=1, recipient_id=2, application_id=1, message_text='Looking forward to your response.', message_type='text', sent_at=datetime.now(timezone.utc)),
    Message(sender_id=2, recipient_id=3, application_id=2, message_text='We have shortlisted you for the interview.', message_type='text', sent_at=datetime.now(timezone.utc)),
    Message(sender_id=3, recipient_id=4, application_id=3, message_text='Your application has been rejected.', message_type='text', sent_at=datetime.now(timezone.utc)),
    Message(sender_id=4, recipient_id=5, application_id=4, message_text='We would like to know more about your experience with PHP.', message_type='text', sent_at=datetime.now(timezone.utc)),
    Message(sender_id=5, recipient_id=1, application_id=5, message_text='We would like to schedule an interview with you.', message_type='text', sent_at=datetime.now(timezone.utc))
]
session.add_all(messages)
session.commit()

print("Additional sample data inserted successfully.")


print("Sample data inserted successfully.")

from sqlalchemy import create_engine, Column, Integer, String, Text, DECIMAL, DateTime, Enum, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from ormjobboard import Authentication

# Database URL (replace with your actual connection details)
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ormjobboard_db"

# Base class for the model
Base = declarative_base()

# Define the Authentication model


# Create the engine and session
engine = create_engine(DATABASE_URL)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# CRUD Operations

# 1. Create Authentication Record
def create_authentication(username, email, password_hash, role):
    new_authentication = Authentication(
        username=username,
        email=email,
        password_hash=password_hash,
        role=role
    )
    session.add(new_authentication)
    session.commit()
    print(f"Authentication record for {username} added successfully.")

# 2. Read Authentication Records
def read_authentication():
    authentications = session.query(Authentication).all()
    if authentications:
        for auth in authentications:
            print(f"ID: {auth.authentication_id}, Username: {auth.username}, Email: {auth.email}, Role: {auth.role}")
    else:
        print("No authentication records found.")

# 3. Update Authentication Record
def update_authentication(authentication_id, new_username=None, new_email=None, new_password_hash=None, new_role=None):
    auth_to_update = session.query(Authentication).filter(Authentication.authentication_id == authentication_id).first()
    if auth_to_update:
        if new_username:
            auth_to_update.username = new_username
        if new_email:
            auth_to_update.email = new_email
        if new_password_hash:
            auth_to_update.password_hash = new_password_hash
        if new_role:
            auth_to_update.role = new_role
        session.commit()
        print(f"Authentication record ID {authentication_id} updated successfully.")
    else:
        print(f"Authentication record ID {authentication_id} not found.")

# 4. Delete Authentication Record
def delete_authentication(authentication_id):
    auth_to_delete = session.query(Authentication).filter(Authentication.authentication_id == authentication_id).first()
    if auth_to_delete:
        session.delete(auth_to_delete)
        session.commit()
        print(f"Authentication record ID {authentication_id} deleted successfully.")
    else:
        print(f"Authentication record ID {authentication_id} not found.")

# Test the CRUD functions
if __name__ == "__main__":
    # Create a new authentication record
    create_authentication('Jovie', 'jovie@example.com', 'hashed_password_123', 'user')

    # Read all authentication records
    print("\n--- Reading Authentication Records ---")
    read_authentication()

    # Update an existing authentication record (example: updating username)
    update_authentication(6, new_username='jovie_updated')

    # Read all authentication records after the update
    print("\n--- Reading Authentication Records After Update ---")
    read_authentication()

    # Delete an authentication record
    delete_authentication(6)

    # Read all authentication records after deletion
    print("\n--- Reading Authentication Records After Deletion ---")
    read_authentication()

authentication

user

job_posting

application

job_interaction

job_performance

ORMEventManagement

from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey, Date
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ormeventmanagement_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Admin model
class Admin(Base):
    __tablename__ = 'Admin'

    admin_id = Column(Integer, primary_key=True, autoincrement=True)
    admin_username = Column(String(255))
    admin_email = Column(String(255))
    admin_password = Column(String(255))

# User model
class User(Base):
    __tablename__ = 'User'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    user_gmail = Column(String(255))
    user_password = Column(String(255))
    user_firstname = Column(String(255))
    user_lastname = Column(String(255))

# Events model
class Events(Base):
    __tablename__ = 'Events'

    event_id = Column(Integer, primary_key=True, autoincrement=True)
    event_title = Column(String(255))
    event_description = Column(Text)
    event_additional_description = Column(Text)
    event_address = Column(String(255))
    event_planner = Column(String(255))
    event_image = Column(String(255))
    event_status = Column(String(255))
    event_start = Column(Date)

# Invited model
class Invited(Base):
    __tablename__ = 'Invited'

    invitation_id = Column(Integer, primary_key=True, autoincrement=True)
    invited_name = Column(String(255))
    event_id = Column(Integer, ForeignKey('Events.event_id'), nullable=False)
    attendee_type = Column(String(255))
    seat_number = Column(String(255))

    event = relationship('Events', back_populates='invited')

# Attendees model
class Attendees(Base):
    __tablename__ = 'Attendees'

    attendee_id = Column(Integer, primary_key=True, autoincrement=True)
    invitation_id = Column(Integer, ForeignKey('Invited.invitation_id'), nullable=False)

    invitation = relationship('Invited', back_populates='attendees')

# Agenda model
class Agenda(Base):
    __tablename__ = 'Agenda'

    agenda_id = Column(Integer, primary_key=True, autoincrement=True)
    event_id = Column(Integer, ForeignKey('Events.event_id'), nullable=False)
    agenda_name = Column(String(255))
    agenda_time_start = Column(String(255))
    agenda_time_end = Column(String(255))

    event = relationship('Events', back_populates='agenda')


# Relationships
Events.invited = relationship('Invited', back_populates='event', cascade="all, delete-orphan")
Invited.attendees = relationship('Attendees', back_populates='invitation', cascade="all, delete-orphan")
Events.agenda = relationship('Agenda', back_populates='event', cascade="all, delete-orphan")


# Create tables
Base.metadata.create_all(engine)

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Add data to the tables

# 1. Admin
admins = [
    Admin(admin_username='admin1', admin_email='admin1@example.com', admin_password='adminpass1'),
    Admin(admin_username='admin2', admin_email='admin2@example.com', admin_password='adminpass2'),
    Admin(admin_username='admin3', admin_email='admin3@example.com', admin_password='adminpass3'),
    Admin(admin_username='admin4', admin_email='admin4@example.com', admin_password='adminpass4'),
    Admin(admin_username='admin5', admin_email='admin5@example.com', admin_password='adminpass5')
]

# 2. User
users = [
    User(user_gmail='user1@example.com', user_password='userpass1', user_firstname='John', user_lastname='Doe'),
    User(user_gmail='user2@example.com', user_password='userpass2', user_firstname='Jane', user_lastname='Doe'),
    User(user_gmail='user3@example.com', user_password='userpass3', user_firstname='Jim', user_lastname='Beam'),
    User(user_gmail='user4@example.com', user_password='userpass4', user_firstname='Jack', user_lastname='Daniels'),
    User(user_gmail='user5@example.com', user_password='userpass5', user_firstname='Jill', user_lastname='Smith')
]

# 3. Events
events = [
    Events(event_title='Tech Conference 2024', event_description='A conference on technology trends',
           event_additional_description='Networking sessions included', event_address='123 Tech St',
           event_planner='TechCorp', event_image='tech_event.jpg', event_status='Scheduled', event_start='2024-05-10'),
    Events(event_title='Health & Wellness Fair', event_description='A fair promoting healthy living',
           event_additional_description='Free health checkups available', event_address='456 Wellness Ave',
           event_planner='Wellness Inc.', event_image='wellness_fair.jpg', event_status='Scheduled', event_start='2024-06-12'),
    Events(event_title='Business Expo 2024', event_description='An expo for entrepreneurs and startups',
           event_additional_description='Includes pitching sessions', event_address='789 Startup Blvd',
           event_planner='Expo Ltd.', event_image='business_expo.jpg', event_status='Scheduled', event_start='2024-07-15'),
    Events(event_title='Music Festival 2024', event_description='A celebration of music and culture',
           event_additional_description='Multiple stages with various genres', event_address='321 Music Ave',
           event_planner='Festivals Co.', event_image='music_festival.jpg', event_status='Scheduled', event_start='2024-08-20'),
    Events(event_title='Food & Wine Gala', event_description='A fine dining experience with wine pairings',
           event_additional_description='Exclusive tasting menu', event_address='654 Gourmet Rd',
           event_planner='Gala Events', event_image='food_wine_gala.jpg', event_status='Scheduled', event_start='2024-09-25')
]

# 4. Invited
invited = [
    Invited(invited_name='Alice', event_id=1, attendee_type='VIP', seat_number='A1'),
    Invited(invited_name='Bob', event_id=2, attendee_type='General', seat_number='B2'),
    Invited(invited_name='Charlie', event_id=3, attendee_type='VIP', seat_number='C3'),
    Invited(invited_name='David', event_id=4, attendee_type='VIP', seat_number='D4'),
    Invited(invited_name='Eve', event_id=5, attendee_type='General', seat_number='E5')
]

# 5. Attendees
attendees = [
    Attendees(invitation_id=1),
    Attendees(invitation_id=2),
    Attendees(invitation_id=3),
    Attendees(invitation_id=4),
    Attendees(invitation_id=5)
]

# 6. Agenda
agenda = [
    Agenda(event_id=1, agenda_name='Opening Keynote', agenda_time_start='09:00', agenda_time_end='10:00'),
    Agenda(event_id=2, agenda_name='Health Talk', agenda_time_start='10:00', agenda_time_end='11:00'),
    Agenda(event_id=3, agenda_name='Business Panel', agenda_time_start='14:00', agenda_time_end='15:00'),
    Agenda(event_id=4, agenda_name='Concert Performance', agenda_time_start='18:00', agenda_time_end='20:00'),
    Agenda(event_id=5, agenda_name='Wine Tasting', agenda_time_start='17:00', agenda_time_end='18:00')
]

# Insert the data into tables
session.add_all(admins)
session.add_all(users)
session.add_all(events)
session.add_all(invited)
session.add_all(attendees)
session.add_all(agenda)

session.commit()

print("Dummy data inserted successfully.")

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from ormeventmanagement import Admin

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jovs123@localhost/ormeventmanagement_db"
engine = create_engine(DATABASE_URL)

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Create Admin
def create_admin(username, email, password):
    new_admin = Admin(admin_username=username, admin_email=email, admin_password=password)
    session.add(new_admin)
    session.commit()
    print(f"Admin '{new_admin.admin_username}' created successfully.")

# Read Admins
def read_all_admins():
    admins = session.query(Admin).all()
    if admins:
        for admin in admins:
            print(f"Admin ID: {admin.admin_id}, Username: {admin.admin_username}, Email: {admin.admin_email}")
    else:
        print("No admins found.")

# Update Admin
def update_admin(admin_id, new_username=None, new_email=None, new_password=None):
    admin = session.query(Admin).filter(Admin.admin_id == admin_id).first()
    if admin:
        if new_username:
            admin.admin_username = new_username
        if new_email:
            admin.admin_email = new_email
        if new_password:
            admin.admin_password = new_password
        session.commit()
        print(f"Admin ID {admin_id} updated.")
    else:
        print(f"Admin with ID {admin_id} not found.")

# Delete Admin
def delete_admin(admin_id):
    admin = session.query(Admin).filter(Admin.admin_id == admin_id).first()
    if admin:
        session.delete(admin)
        session.commit()
        print(f"Admin ID {admin_id} deleted.")
    else:
        print(f"Admin with ID {admin_id} not found.")

# Running the CRUD operations
if __name__ == "__main__":
    # Create a new admin
    create_admin('admin6', 'admin6@example.com', 'adminpass6')

    # Read all admins
    print("\nReading all admins:")
    read_all_admins()

    # Update an existing admin (e.g., update admin with ID 1)
    print("\nUpdating admin with ID 1:")
    update_admin(1, new_username='admin1_updated', new_email='admin1_updated@example.com')

    # Read again after update
    print("\nReading all admins after update:")
    read_all_admins()

    # Delete an admin (e.g., delete admin with ID 5)
    print("\nDeleting admin with ID 5:")
    delete_admin(5)

    # Read again after delete
    print("\nReading all admins after delete:")
    read_all_admins()

admin

user

events

agenda

attendees

invited

ORMTravelBooking

from sqlalchemy import create_engine, Column, Integer, String, Text, DECIMAL, DateTime, Date, Enum, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from datetime import datetime

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jov123@localhost/ormtravelbooking_db"
engine = create_engine(DATABASE_URL)

# Base class for all models
Base = declarative_base()

# Define the User model
class User(Base):
    __tablename__ = 'User'

    user_id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    password_hash = Column(String(255), nullable=False)
    email = Column(String(100), nullable=False)
    phone_number = Column(String(20))
    first_name = Column(String(50), nullable=False)
    last_name = Column(String(50), nullable=False)
    user_role = Column(Enum('ADMIN', 'USERS', name='user_role_enum'), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    deleted_at = Column(DateTime)

# Define the Tour model
class Tour(Base):
    __tablename__ = 'Tours'

    tour_id = Column(Integer, primary_key=True, autoincrement=True)
    tour_name = Column(String(100), nullable=False)
    description = Column(Text, nullable=False)
    price = Column(DECIMAL(10, 2), nullable=False)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=False)
    seats_available = Column(Integer, nullable=False)
    image_url = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    deleted_at = Column(DateTime)

# Define the Booking model
class Booking(Base):
    __tablename__ = 'Bookings'

    booking_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    tour_id = Column(Integer, ForeignKey('Tours.tour_id'), nullable=False)
    booking_date = Column(DateTime, default=datetime.utcnow)
    travel_date = Column(Date, nullable=False)
    seats_booked = Column(Integer, nullable=False)
    total_amount = Column(DECIMAL(10, 2), nullable=False)
    payment_status = Column(Enum('SUCCESS', 'FAILED', name='payment_status_enum'), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User', back_populates='bookings')
    tour = relationship('Tour', back_populates='bookings')

# Define the Payment model
class Payment(Base):
    __tablename__ = 'Payment'

    payment_id = Column(Integer, primary_key=True, autoincrement=True)
    booking_id = Column(Integer, ForeignKey('Bookings.booking_id'), nullable=False)
    payment_date = Column(DateTime, default=datetime.utcnow)
    amount = Column(DECIMAL(10, 2), nullable=False)
    payment_method = Column(Enum('GCASH', name='payment_method_enum'), nullable=False)
    payment_status = Column(Enum('SUCCESS', 'FAILED', name='payment_status_enum'), nullable=False)
    transaction_id = Column(String(100), nullable=False)

    booking = relationship('Booking', back_populates='payments')

# Define the Review model
class Review(Base):
    __tablename__ = 'Reviews'

    review_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    tour_id = Column(Integer, ForeignKey('Tours.tour_id'), nullable=False)
    rating = Column(Integer, nullable=False)
    comment = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User', back_populates='reviews')
    tour = relationship('Tour', back_populates='reviews')

# Define the AdminLogs model
class AdminLog(Base):
    __tablename__ = 'AdminLogs'

    log_id = Column(Integer, primary_key=True, autoincrement=True)
    admin_id = Column(Integer, ForeignKey('User.user_id'), nullable=False)
    action_type = Column(Enum('ADD', 'DELETE', 'UPDATE', name='action_type_enum'), nullable=False)
    description = Column(Text, nullable=False)
    timestamp = Column(DateTime, default=datetime.utcnow)

    admin = relationship('User', back_populates='admin_logs')


# Add relationships for back_populates
User.bookings = relationship('Booking', back_populates='user')
User.reviews = relationship('Review', back_populates='user')
User.admin_logs = relationship('AdminLog', back_populates='admin')

Tour.bookings = relationship('Booking', back_populates='tour')
Tour.reviews = relationship('Review', back_populates='tour')

Booking.payments = relationship('Payment', back_populates='booking')


# Create tables
Base.metadata.create_all(engine)

# Session creation
Session = sessionmaker(bind=engine)
session = Session()

# Add 5 Users
users = [
    User(username='john_doe', password_hash='hashed_pw_1', email='john@example.com', phone_number='1234567890', first_name='John', last_name='Doe', user_role='ADMIN'),
    User(username='jane_doe', password_hash='hashed_pw_2', email='jane@example.com', phone_number='0987654321', first_name='Jane', last_name='Doe', user_role='USERS'),
    User(username='sam_smith', password_hash='hashed_pw_3', email='sam@example.com', phone_number='1122334455', first_name='Sam', last_name='Smith', user_role='USERS'),
    User(username='emily_brown', password_hash='hashed_pw_4', email='emily@example.com', phone_number='5566778899', first_name='Emily', last_name='Brown', user_role='USERS'),
    User(username='david_lee', password_hash='hashed_pw_5', email='david@example.com', phone_number='6677889900', first_name='David', last_name='Lee', user_role='ADMIN')
]
session.add_all(users)
session.commit()

# Add 5 Tours
tours = [
    Tour(tour_name='Paris Adventure', description='Tour of Paris including major landmarks', price=1000.00, start_date=datetime(2024, 5, 1).date(), end_date=datetime(2024, 5, 7).date(), seats_available=20),
    Tour(tour_name='Rome Exploration', description='Explore the historical sites of Rome', price=800.00, start_date=datetime(2024, 6, 15).date(), end_date=datetime(2024, 6, 20).date(), seats_available=15),
    Tour(tour_name='Tokyo Experience', description='Experience the best of Tokyo and Japan', price=1200.00, start_date=datetime(2024, 7, 1).date(), end_date=datetime(2024, 7, 10).date(), seats_available=25),
    Tour(tour_name='New York City Tour', description='Discover the Big Apple on this guided tour', price=900.00, start_date=datetime(2024, 8, 1).date(), end_date=datetime(2024, 8, 5).date(), seats_available=30),
    Tour(tour_name='London City Break', description='A short trip around London\'s top attractions', price=850.00, start_date=datetime(2024, 9, 10).date(), end_date=datetime(2024, 9, 15).date(), seats_available=10)
]
session.add_all(tours)
session.commit()

# Add 5 Bookings
bookings = [
    Booking(user_id=2, tour_id=1, travel_date=datetime(2024, 5, 2).date(), seats_booked=2, total_amount=2000.00, payment_status='SUCCESS'),
    Booking(user_id=3, tour_id=2, travel_date=datetime(2024, 6, 16).date(), seats_booked=1, total_amount=800.00, payment_status='FAILED'),
    Booking(user_id=4, tour_id=3, travel_date=datetime(2024, 7, 2).date(), seats_booked=3, total_amount=3600.00, payment_status='SUCCESS'),
    Booking(user_id=5, tour_id=4, travel_date=datetime(2024, 8, 2).date(), seats_booked=2, total_amount=1800.00, payment_status='SUCCESS'),
    Booking(user_id=1, tour_id=5, travel_date=datetime(2024, 9, 11).date(), seats_booked=1, total_amount=850.00, payment_status='FAILED')
]
session.add_all(bookings)
session.commit()

# Add 5 Payments
payments = [
    Payment(booking_id=1, amount=2000.00, payment_method='GCASH', payment_status='SUCCESS', transaction_id='TXN001'),
    Payment(booking_id=2, amount=800.00, payment_method='GCASH', payment_status='FAILED', transaction_id='TXN002'),
    Payment(booking_id=3, amount=3600.00, payment_method='GCASH', payment_status='SUCCESS', transaction_id='TXN003'),
    Payment(booking_id=4, amount=1800.00, payment_method='GCASH', payment_status='SUCCESS', transaction_id='TXN004'),
    Payment(booking_id=5, amount=850.00, payment_method='GCASH', payment_status='FAILED', transaction_id='TXN005')
]
session.add_all(payments)
session.commit()

from datetime import datetime, timezone

# Add 5 Reviews
reviews = [
    Review(user_id=2, tour_id=1, rating=5, comment='Amazing tour, highly recommend!', created_at=datetime.now(timezone.utc)),
    Review(user_id=3, tour_id=2, rating=4, comment='Great experience but not enough time to explore.', created_at=datetime.now(timezone.utc)),
    Review(user_id=4, tour_id=3, rating=5, comment='The best tour I\'ve ever had!', created_at=datetime.now(timezone.utc)),
    Review(user_id=5, tour_id=4, rating=3, comment='Good, but the schedule was too tight.', created_at=datetime.now(timezone.utc)),
    Review(user_id=1, tour_id=5, rating=4, comment='Great tour, but the weather wasn\'t great.', created_at=datetime.now(timezone.utc))
]
session.add_all(reviews)
session.commit()

# Add 5 Admin Logs
admin_logs = [
    AdminLog(admin_id=1, action_type='ADD', description='Added new user: jane_doe.', timestamp=datetime.now(timezone.utc)),
    AdminLog(admin_id=1, action_type='UPDATE', description='Updated user details for john_doe.', timestamp=datetime.now(timezone.utc)),
    AdminLog(admin_id=2, action_type='DELETE', description='Deleted tour: Paris Adventure.', timestamp=datetime.now(timezone.utc)),
    AdminLog(admin_id=2, action_type='ADD', description='Added new tour: Rome Exploration.', timestamp=datetime.now(timezone.utc)),
    AdminLog(admin_id=1, action_type='UPDATE', description='Updated payment status for booking ID 1.', timestamp=datetime.now(timezone.utc))
]
session.add_all(admin_logs)
session.commit()

print("Dummy data inserted successfully.")

from sqlalchemy import create_engine, Column, Integer, String, Enum, DateTime
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.exc import IntegrityError
from ormtravelbooking import User

# Database URL
DATABASE_URL = "mysql+mysqlconnector://root:jov123@localhost/ormtravelbooking_db"
engine = create_engine(DATABASE_URL, echo=True)

# Base class for all models
Base = declarative_base()


# Session creation
Session = sessionmaker(bind=engine)


def get_session():
    return Session()


# CRUD operations

# Create a new user
def create_user(username, password_hash, email, phone_number, first_name, last_name, user_role):
    session = get_session()
    try:
        print(f"Attempting to create user: {username}")
        new_user = User(
            username=username,
            password_hash=password_hash,
            email=email,
            phone_number=phone_number,
            first_name=first_name,
            last_name=last_name,
            user_role=user_role
        )
        session.add(new_user)
        session.commit()
        print(f"User {username} created successfully.")
    except IntegrityError:
        session.rollback()
        print(f"Error: User {username} already exists or other integrity issue.")
    finally:
        session.close()


# Get user by ID
def get_user_by_id(user_id):
    session = get_session()
    print(f"Retrieving user with ID {user_id}")
    user = session.query(User).filter(User.user_id == user_id).first()
    session.close()
    if user:
        print(f"User found: {user.first_name} {user.last_name}, Role: {user.user_role}")
    else:
        print(f"User with ID {user_id} not found.")
    return user


# Update user information
def update_user(user_id, username=None, password_hash=None, email=None, phone_number=None, first_name=None,
                last_name=None, user_role=None):
    session = get_session()
    print(f"Attempting to update user with ID {user_id}")
    user = session.query(User).filter(User.user_id == user_id).first()

    if user:
        if username:
            user.username = username
        if password_hash:
            user.password_hash = password_hash
        if email:
            user.email = email
        if phone_number:
            user.phone_number = phone_number
        if first_name:
            user.first_name = first_name
        if last_name:
            user.last_name = last_name
        if user_role:
            user.user_role = user_role
        session.commit()
        print(f"User {user_id} updated successfully.")
    else:
        print(f"User {user_id} not found.")

    session.close()


# Delete a user
def delete_user(user_id):
    session = get_session()
    print(f"Attempting to delete user with ID {user_id}")
    user = session.query(User).filter(User.user_id == user_id).first()

    if user:
        session.delete(user)
        session.commit()
        print(f"User {user_id} deleted successfully.")
    else:
        print(f"User {user_id} not found.")

    session.close()


# Get all users
def get_all_users():
    session = get_session()
    print("Retrieving all users.")
    users = session.query(User).all()
    session.close()
    if users:
        print("All Users:")
        for user in users:
            print(f"ID: {user.user_id}, Name: {user.first_name} {user.last_name}, Role: {user.user_role}")
    else:
        print("No users found.")
    return users


# Demonstration of CRUD operations

def main():
    print("Starting CRUD operations...\n")

    # Create users (only if they don't already exist)
    print("\n--- Creating Users ---")
    create_user('jov_roncal', 'hashed_pw_6', 'jov@example.com', '1234123412', 'Jov', 'Roncal', 'USERS')
    create_user('ramon_roncal', 'hashed_pw_7', 'ramon@example.com', '9876987698', 'Ramon', 'Roncal', 'USERS')

    # Get all users
    print("\n--- Retrieving All Users ---")
    users = get_all_users()

    # Get user by ID
    print("\n--- Retrieving User by ID ---")
    user = get_user_by_id(1)  # Assuming user with ID 1 exists
    if user:
        print(f"User with ID 1: {user.first_name} {user.last_name}, {user.user_role}")

    # Update a user's information
    print("\n--- Updating User ---")
    update_user(1, first_name="John", last_name="Lenon Updated")

    # Get updated user by ID
    user = get_user_by_id(1)
    if user:
        print(f"Updated user: {user.first_name} {user.last_name}, {user.user_role}")

    # Delete a user
    print("\n--- Deleting User ---")
    delete_user(6)  # Deleting user with ID 2

    # Get all users after deletion
    print("\n--- Retrieving All Users After Deletion ---")
    users = get_all_users()


if __name__ == '__main__':
    main()

user

tours

bookings

payment

reviews

adminlogs

https://github.com/JovRoncal/ACTIVITY-20-PYTHON-ORM-SQLALCHEMY