import cx_Oracle
import config as cfg
def createAndInsert():
    
    #Open database
    conn = cx_Oracle.connect(cfg.username, cfg.password, cfg.dsn, encoding=cfg.encoding)
    cur = conn.cursor()

    # Dropping tables if they exist
    try:
        cur.execute('''drop table kart''')
    except Exception as e:
        print(e)
        
    try:
        cur.execute(''' drop table products''')
    except Exception as e:
        print(e)

    try:
        cur.execute(''' drop table users''')
    except Exception as e:
        print(e)

    try:
        cur.execute('''drop table categories''')
    except Exception as e:
        print(e)

    try:
        cur.execute('''CREATE TABLE users(
            userId NUMBER(10),
            password varchar2(255),
            email varchar2(255),
            firstName varchar2(255),
            lastName varchar2(255),
            address1 varchar2(255),
            address2 varchar2(255),
            zipcode varchar2(255),
            city varchar2(255),
            state varchar2(255),
            country varchar2(255),
            phone varchar2(255),
            PRIMARY KEY(userId)
            )''')


        cur.execute('''CREATE TABLE categories(
            categoryId NUMBER(10),
            name varchar2(255),
            PRIMARY KEY(categoryId)
            )''')


        cur.execute('''CREATE TABLE products(
            productId NUMBER(10),
            name varchar2(255),
            price BINARY_DOUBLE,
            description varchar2(255),
            image varchar2(255),
            stock NUMBER(10),
            categoryId NUMBER(10),
            FOREIGN KEY(categoryId) REFERENCES categories(categoryId),
            PRIMARY KEY(productId)
            )''')


        cur.execute('''CREATE TABLE kart(userId NUMBER(10),
            productId NUMBER(10),
            FOREIGN KEY(userId) REFERENCES users(userId),
            FOREIGN KEY(productId) REFERENCES products(productId)
            )''')



        cur.execute('''INSERT INTO users(userId, password, email, firstName, lastName, address1, address2, zipcode, city, state, country, phone) 
        VALUES
        ('1', '0cc175b9c0f1b6a831c399e269772661', 'abcd@example.com', 'Harsh', 'Shah', 'scaa', 'asa', 'as', 'asc', 'dasd', 'dfas', 'dsa')''')




        cur.execute('''INSERT INTO categories(categoryId, name) 
        SELECT 1, 'Men' FROM dual UNION ALL 
        SELECT 2, 'Books' FROM dual UNION ALL 
        SELECT 3, 'Computers and Accessories' FROM dual UNION ALL
        SELECT 4, 'Movies, Music and Video Games'FROM dual UNION ALL 
        SELECT 5, 'Jwelery, Watches and Eyewear' FROM dual UNION ALL 
        SELECT 6, 'Women' FROM dual''')



        cur.execute('''INSERT INTO products(productId, name, price, description, image, stock, categoryId)  
            SELECT 2, 'Second', '2.0', 'Second Item', 'Kinkaku_Ji_by_Elizabeth_K_Joseph.jpg', 2, 1 FROM dual UNION ALL 
            SELECT 3, 'First', '1.0', 'First book.', 'Untitled_by_Troy_Jarrell.jpg', 1, 2 FROM dual UNION ALL 
            SELECT 4, 'T Shirt 1', '1.0', 'First T shirt', 'Kinkaku_Ji_by_Elizabeth_K_Joseph.jpg', 1, 1 FROM dual UNION ALL 
            SELECT 5, 'T Shirt 2', '2.0', 'Second T shirt', 'The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg', 2, 1 FROM dual UNION ALL 
            SELECT 6, 'T Shirt 3', '3.0', 'Third tshirt', 'Untitled_by_Troy_Jarrell.jpg', 3, 1 FROM dual UNION ALL 
            SELECT 7, 'T Shirt 4', '4.0', 'Fourth T shirt', 'Untitled_by_Aaron_Burden.jpg', 4, 1 FROM dual UNION ALL 
            SELECT 8, 'T Shirt 5', '5.0', 'FIfth Tshirt', 'The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg', 5, 1 FROM dual UNION ALL 
            SELECT 9, 'Book 1', '1.0', 'FIrst Book', 'Mountainous_View_by_Sven_Scheuermeier.jpg', 1, 2 FROM dual UNION ALL 
            SELECT 10, 'Book 2', '2.0', 'Second Book', 'The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg', 2, 2 FROM dual UNION ALL 
            SELECT 11, 'Book 3', '3.0', 'Third book.', 'Untitled_0026_by_Mike_Sinko.jpg', 3, 2 FROM dual UNION ALL 
            SELECT 12, 'Book 4', '4.0', 'Fourth book.', 'Untitled_7019_by_Mike_Sinko.jpg', 4, 2 FROM dual UNION ALL 
            SELECT 13, 'Book 5', '5.0', 'Fifth book.', 'Untitled_by_Troy_Jarrell.jpg', 5, 2 FROM dual UNION ALL 
            SELECT 14, 'Computer 1', '1.0', 'First computer', 'Untitled_by_Aaron_Burden.jpg', 1, 3 FROM dual UNION ALL 
            SELECT 15, 'Movie 1', '1.0', 'First mvoie', 'Yellow_Jacket_by_Manuel_Frei.png', 1, 4 FROM dual UNION ALL 
            SELECT 16, 'Jwelery 1', '1.0', 'First jwelery', 'Kinkaku_Ji_by_Elizabeth_K_Joseph.jpg', 1, 5 FROM dual UNION ALL 
            SELECT 17, 'Saree 1', '1.0', 'First saree', 'Mountainous_View_by_Sven_Scheuermeier.jpg', 1, 6 FROM dual''')
    except Exception as e:
        print(e)

    conn.commit()
    cur.close()
    conn.close()

if __name__ == '__main__':
    createAndInsert()