import sqlite3
import random

def create_sample_db(db_path="sample_project.db"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Drop tables if exist
    cursor.executescript("""
    DROP TABLE IF EXISTS enrollments;
    DROP TABLE IF EXISTS orders;
    DROP TABLE IF EXISTS students;
    DROP TABLE IF EXISTS courses;
    """)

    # Create tables
    cursor.executescript("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        marks INTEGER
    );

    CREATE TABLE courses (
        course_id INTEGER PRIMARY KEY,
        course_name TEXT
    );

    CREATE TABLE enrollments (
        id INTEGER PRIMARY KEY,
        student_id INTEGER,
        course_id INTEGER,
        FOREIGN KEY(student_id) REFERENCES students(id),
        FOREIGN KEY(course_id) REFERENCES courses(course_id)
    );

    CREATE TABLE orders (
        order_id INTEGER PRIMARY KEY,
        student_id INTEGER,
        amount INTEGER,
        FOREIGN KEY(student_id) REFERENCES students(id)
    );
    """)

    # Insert students (20 records)
    names = ["Rohit", "Amit", "Neha", "Priya", "Karan", "Simran", "Raj", "Anjali", "Vikram", "Sneha"]
    
    students = []
    for i in range(1, 21):
        students.append((
            i,
            random.choice(names) + str(i),
            random.randint(18, 25),
            random.randint(50, 100)
        ))

    cursor.executemany("INSERT INTO students VALUES (?, ?, ?, ?)", students)

    # Insert courses
    courses = [
        (1, "Math"),
        (2, "Science"),
        (3, "History"),
        (4, "Computer Science")
    ]
    cursor.executemany("INSERT INTO courses VALUES (?, ?)", courses)

    # Insert enrollments (30 records)
    enrollments = []
    for i in range(1, 31):
        enrollments.append((
            i,
            random.randint(1, 20),
            random.randint(1, 4)
        ))

    cursor.executemany("INSERT INTO enrollments VALUES (?, ?, ?)", enrollments)

    # Insert orders (25 records)
    orders = []
    for i in range(1, 26):
        orders.append((
            i,
            random.randint(1, 20),
            random.randint(100, 2000)
        ))

    cursor.executemany("INSERT INTO orders VALUES (?, ?, ?)", orders)

    conn.commit()
    conn.close()

    print(f"✅ Database created: {db_path}")


# Run this
# create_sample_db() 

def export_to_sql(db_path="sample_project.db", sql_path="sample_project.sql"):
    conn = sqlite3.connect(db_path)
    with open(sql_path, "w") as f:
        for line in conn.iterdump():
            f.write(f"{line}\n")
    conn.close()
    print(f"✅ Exported to {sql_path}")
    return 
# Run this
# export_to_sql()  


import sqlite3  

def extract_schema(db_path): 
    conn = sqlite3.connect(db_path) 
    cursor = conn.cursor() 

    cursor.execute("""
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name NOT LIKE 'sqlite_%';
    """) 

    tables = cursor.fetchall() 
    schema_chunks = [] 

    for (table_name,) in tables:
        cursor.execute(f"PRAGMA table_info({table_name});")
        columns = cursor.fetchall()

        col_names = [col[1] for col in columns]

        chunk = f"Table: {table_name} ({', '.join(col_names)})"
        schema_chunks.append(chunk)

    conn.close()
    return schema_chunks 


print(extract_schema("sample_project.sql"))