###################################################################
# SQLITE - Drop and Create Multiple tables
###################################################################
import sqlite3

conn = sqlite3.connect('/sqlite/sqlite.db')
curs = conn.cursor()

curs.execute("DROP TABLE IF EXISTS class")
curs.execute("DROP TABLE IF EXISTS student")
curs.execute("DROP TABLE IF EXISTS instructor")
curs.execute("DROP TABLE IF EXISTS course")
curs.execute("DROP TABLE IF EXISTS payment")


### Create student table ###########################################
sql = """
CREATE TABLE student
(
    student_id    INTEGER       NOT NULL,
    lname         VARCHAR(20)   NOT NULL,
    fname         VARCHAR(20)   NOT NULL,
    ssn           CHAR(11)      NOT NULL,
    sex           CHAR(1)       NOT NULL,

    CONSTRAINT student_pk  PRIMARY KEY (student_id), 
    CONSTRAINT stu_ssn_uq  UNIQUE      (ssn), 
    CONSTRAINT stu_sex_chk CHECK       (sex IN ('M', 'F'))
)
"""
curs.execute(sql)


### Create instructor table ########################################
sql = """
CREATE TABLE instructor
(
    instructor_id INTEGER       NOT NULL PRIMARY KEY,
    lname         VARCHAR(20)   NOT NULL,
    fname         VARCHAR(20)   NOT NULL,
    ssn           CHAR(11)      NOT NULL UNIQUE,
    sex           CHAR(1)       NOT NULL CHECK (sex='M' OR sex='F')
)
"""
curs.execute(sql)


### Create course table ############################################
sql = """
CREATE TABLE course
(
    course_id    CHAR(8)      NOT NULL PRIMARY KEY,
    description  VARCHAR(40)  NOT NULL,
    price        INTEGER      NOT NULL
)
"""
curs.execute(sql)


### Create class table ##############################################
sql = """
CREATE TABLE class
(
    class_id      INTEGER       NOT NULL,
    course_id     CHAR(8)       NOT NULL,
    session_id    INTEGER       NOT NULL,
    inst_ssn      CHAR(11)      NOT NULL,
    stu_ssn       CHAR(11)      NOT NULL,

    CONSTRAINT class_pk    PRIMARY KEY (class_id),
    CONSTRAINT course_fk   FOREIGN KEY (course_id)  REFERENCES course (course_id),
    CONSTRAINT inst_ssn_fk FOREIGN KEY (inst_ssn)   REFERENCES instructor (ssn),
    CONSTRAINT stu_ssn_fk  FOREIGN KEY (stu_ssn)    REFERENCES student (ssn),
    CONSTRAINT course_uniq UNIQUE      (course_id, inst_ssn, stu_ssn)
)
"""
curs.execute(sql)


# create address table #############################################
sql = """
CREATE TABLE address
(
    address_id INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    stu_id     INTEGER,
    inst_id    INTEGER,
    addr_type  CHAR(1)       NOT NULL,
    street1    VARCHAR(50)   NOT NULL,
    street2    VARCHAR(50),
    city       VARCHAR(25)   NOT NULL,
    state      CHAR(2),
    country    VARCHAR(20),       
        
    CONSTRAINT addr_stu_id_fk  FOREIGN KEY (stu_id)    REFERENCES student    (student_id),
    CONSTRAINT addr_inst_id_fk FOREIGN KEY (inst_id)   REFERENCES instructor (instructor_id)
);
"""
curs.execute(sql)


### Create class payment ##############################################
sql = """
CREATE TABLE payment
(   
    payment_num  INTEGER        NOT NULL PRIMARY KEY AUTOINCREMENT,    
    vendor       VARCHAR(40)    NOT NULL,   
    amount       DECIMAL(6,2)   DEFAULT 0,
    description  VARCHAR(40)    NOT NULL,
    pay_date     TIMESTAMP      DEFAULT CURRENT_TIMESTAMP 
);
"""
curs.execute(sql)

conn.commit()
curs.close()
conn.close()