import sqlite3
from reviews import * 
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('course_reviews.db')

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Create a table to store course reviews
cursor.execute('''
    CREATE TABLE IF NOT EXISTS course_reviews (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_name TEXT NOT NULL,
        review TEXT NOT NULL
    )
''')

cursor.execute('''
    CREATE TABLE IF NOT EXISTS course_difficulty (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_name TEXT NOT NULL,
        difficulty_level TEXT NOT NULL
    )
''')

course_difficulty_data = [
    ("Mathematics 1", "Moderate"),
    ("Mathematics 2", "Hard"),
    ("Statistics 1", "Moderate"),
    ("Statistics 2", "Hard"),
    ("Computational Thinking", "Moderate"),
    ("English 1", "Easy"),
    ("English 2", "Easy"),
    ("Python", "Moderate"),
    ("DBMS","Moderate"),
    ("PDSA", "Hard"),
    ("MAD 1", "Easy"),
    ("Java","Moderate"),
    ("MAD 2","Moderate"),
    ("System Commands", "Hard"),
    ("MLP","Moderate"),
    ("BDM", "Easy"),
    ("MLT", "Hard"),
    ("BA", "Easy"),
    ("TDS", "Easy"),
    ("MLF","Hard")
]

# Sample reviews for Mathematics 2 with average to negative sentiment

# Insert sample reviews into the database
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', mathematics_1_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', mathematics_2_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', english_1_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', english_2_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', python_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', computational_thinking_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', statistics_1_reviews)
cursor.executemany('INSERT INTO course_reviews (course_name, review) VALUES (?, ?)', statistics_2_reviews)
cursor.executemany('INSERT INTO course_difficulty (course_name, difficulty_level) VALUES (?, ?)', course_difficulty_data)
# Commit the changes and close the connection
conn.commit()
conn.close()

print("Database and table created successfully.")