import sqlite3 as sql

conn=sql.connect('university_database.db')

c=conn.cursor()

c.execute("""CREATE TABLE student(
                            ID INTEGER PRIMARY KEY,
                            name TEXT NOT NULL,
                            dept_name TEXT,
                            tot_cred REAL CHECK(tot_cred>=0),
                            FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
          
)""")


c.execute("""CREATE TABLE department(
                            dept_name TEXT PRIMARY KEY,
                            bulding TEXT,
                            buget REAL CHECK(buget>0)
)""")

c.execute("""CREATE TABLE instructor(
                            ID INTEGER,
                            name TEXT NOT NULL,
                            dept_name TEXT,
                            salary REAL CHECK(salary>29000),
                            FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL

)""")


c.execute("""CREATE TABLE section(
                            course_id INTEGER,
                            sec_id INTEGER ,
                            semester TEXT CHECK(semester IN('Fall','Winter','Summer','Spring')),
                            year INTEGER CHECK(year>1701 and year<2100),
                            building TEXT,
                            room_number INTEGER,
                            time_slot_id INTEGER,
                            PRIMARY KEY(course_id,sec_id,semester,year),
                            FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE,
                            FOREIGN KEY(building,room_number) REFERENCES classroom(building,room_number) ON DELETE SET NULL

)""")

c.execute("""CREATE TABLE course(
                            course_id INTEGER PRIMARY KEY,
                            title TEXT,
                            dept_name,
                            credits REAL CHECK(credits>0),
                            FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
)""")

c.execute("""CREATE TABLE classroom(
                            building TEXT,
                            room_number INTEGER,
                            capacity INTEGER,
                            PRIMARY KEY(building,room_number)
)""")

c.execute("""CREATE TABLE advisor(
                            s_id INTEGER PRIMARY KEY,
                            i_id INTEGER,
                            FOREIGN KEY(s_id) REFERENCES student (ID) ON DELETE SET NULL,
                            FOREIGN KEY(i_id) REFERENCES instructor (ID) ON DELETE CASCADE
)""")

c.execute("""CREATE TABLE prerep(
                            course_id INTEGER,
                            prerep_id INTEGER,
                            PRIMARY KEY(course_id,prerep_id),
                            FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE,
                            FOREIGN KEY(prerep_id) REFERENCES course(course_id)
)""")

c.execute("""CREATE TABLE takes(
                            ID INTEGER,
                            course_id INTEGER,
                            sec_id INTEGER,
                            semester TEXT,
                            year INTEGER,
                            grade TEXT,
                            PRIMARY KEY(ID,course_id,sec_id,semester,year),
                            FOREIGN KEY(course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year) ON DELETE CASCADE,
                            FOREIGN KEY(ID) REFERENCES student(ID) ON DELETE CASCADE
)""")

c.execute("""CREATE TABLE teaches(
                            ID INTEGER,
                            course_id INTEGER,
                            sec_id INTEGER,
                            semester TEXT,
                            year INTEGER,
                            PRIMARY KEY(ID,course_id,sec_id,semester,year)
                            FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year) ON DELETE CASCADE,
                            FOREIGN KEY(ID) REFERENCES instructor (ID) ON DELETE CASCADE
                            
)""")

c.execute("""CREATE TABLE time_slot(
                            time_slot_id INTEGER,
                            day INTEGER,
                            start_hr INTEGER CHECK(start_hr>=0 and start_hr<=24),
                            start_min INTEGER CHECK(start_min >= 0 and start_min < 60),
                            end_hr INTEGER CHECK (end_hr >= 0 and end_hr < 24),
                            end_min INTEGER CHECK (end_min >= 0 and end_min < 60),
                            PRIMARY KEY(time_slot_id,day,start_hr,start_min)
)""")


 
conn.commit()
conn.close()