import sqlite3,time

#now sqllite is 3.34 on 2021/10/25
#SQLite 3.7.11
#https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database


ts = int(time.time())
dbname ="databasennn.db%s" %(ts)
#Open database
conn = sqlite3.connect(dbname)
#Create table
conn.execute('''DROP TABLE  IF EXISTS 'users' ;''')
conn.execute('''CREATE TABLE IF NOT EXISTS  users 
		(userId INTEGER PRIMARY KEY, 
		password TEXT,
		email TEXT,
		firstName TEXT,
		lastName TEXT,
		address1 TEXT,
		address2 TEXT,
		zipcode TEXT,
		city TEXT,
		state TEXT,
		country TEXT,
        grade INTEGER DEFAULT 1,
		phone TEXT
		latest_log_on_ip TEXT,
		latest_log_on_time TEXT,
        reg_ip TEXT,
        reg_time INTEGER
		)''')
conn.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')
conn.execute('''INSERT INTO users (userId,password,email,firstName,lastName,address1,address2,zipcode,city,state,country,phone)VALUES(3,'2af54305f183778d87de0c70c591fae4','aly2020616@gmail.com','','','','','','','','',1,'','1635309682','192.168.31.188',1635309542,'192.168.31.188');

conn.execute('''DROP TABLE IF EXISTS 'products';''')
products ='''CREATE TABLE IF NOT EXISTS  products
		(productId INTEGER PRIMARY KEY,
		ts INTEGER,
		name TEXT,
		price REAL,
		description TEXT,
		image TEXT,
		stock INTEGER,
		categoryId INTEGER,
        userId INTEGER, 
        grade INTEGER DEFAULT 9,
		FOREIGN KEY(categoryId) REFERENCES categories(categoryId)
		);'''
conn.execute(products)



products2i ='''INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (2,'Second',2.0,'Second Item','Kinkaku_Ji_by_Elizabeth_K_Joseph.jpg',2,1);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (3,'First',1.0,'First book.','Untitled_by_Troy_Jarrell.jpg',1,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (4,'T Shirt 1',1.0,'First T shirt','Kinkaku_Ji_by_Elizabeth_K_Joseph.jpg',1,1); 
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (5,'T Shirt 2',2.0,'Second T shirt','The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg',2,1);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (6,'T Shirt 3',3.0,'Third tshirt','Untitled_by_Troy_Jarrell.jpg',3,1);          
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (7,'T Shirt 4',4.0,'Fourth T shirt','Untitled_by_Aaron_Burden.jpg',4,1);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (8,'T Shirt 5',5.0,'FIfth Tshirt','The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg',5,1);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (9,'Book 1',1.0,'FIrst Book','Mountainous_View_by_Sven_Scheuermeier.jpg',1,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (10,'Book 2',2.0,'Second Book','The_Sky_Is_The_Limit_by_Kaushik_Panchal.jpg',2,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (11,'Book 3',3.0,'Third book.','Untitled_0026_by_Mike_Sinko.jpg',3,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (12,'Book 4',4.0,'Fourth book.','Untitled_7019_by_Mike_Sinko.jpg',4,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (13,'Book 5',5.0,'Fifth book.','Untitled_by_Troy_Jarrell.jpg',5,2);
INSERT INTO products (productId,name,price,description,image,stock,categoryId) VALUES (14,'Computer 1',1.0,'First computer','Untitled_by_Aaron_Burden.jpg',1,3);
		'''
li2 =products2i.splitlines()


#print(li2)
for ro in li2:
    #print("---")
    #print(ro)
    conn.execute(ro)

conn.execute('''DROP TABLE IF EXISTS 'next_kart';''')
conn.execute('''CREATE TABLE IF NOT EXISTS  next_kart
		(userId INTEGER,
		productId INTEGER,
        ts INTEGER,
		FOREIGN KEY(userId) REFERENCES users(userId),
		FOREIGN KEY(productId) REFERENCES products(productId)
		);;''')

conn.execute('''DROP TABLE IF EXISTS 'kart';''')
conn.execute('''CREATE TABLE IF NOT EXISTS  kart
		(userId INTEGER,
		productId INTEGER,
        ts INTEGER,
		FOREIGN KEY(userId) REFERENCES users(userId),
		FOREIGN KEY(productId) REFERENCES products(productId)
		);''')

conn.execute('''DROP TABLE IF EXISTS 'order' ;''')
conn.execute('''CREATE TABLE IF NOT EXISTS  'order'
		(orderId INTEGER PRIMARY KEY,userId INTEGER,
		
		name TEXT,
        status INTEGER,
        paynum TEXT,
        totalnum INTEGER
		)''') 
conn.execute('''DROP TABLE IF EXISTS 'categories';''')        
conn.execute('''CREATE TABLE IF NOT EXISTS  categories
		(categoryId INTEGER PRIMARY KEY,
		name TEXT
		)''') 
iii = '''INSERT INTO categories (categoryId,name) VALUES (1,'Men');
INSERT INTO categories (categoryId,name) VALUES(2,'Books');
INSERT INTO categories (categoryId,name) VALUES(3,'Computers and Accessories');
INSERT INTO categories (categoryId,name) VALUES(4,'Movies, Music and Video Games');
INSERT INTO categories (categoryId,name) VALUES(5,'Jwelery, Watches and Eyewear');
INSERT INTO categories (categoryId,name) VALUES(6,'Women');
INSERT INTO categories (categoryId,name) VALUES(7,'食物');
INSERT INTO categories (categoryId,name) VALUES(8,'文章');
INSERT INTO categories (categoryId,name) VALUES(9,'活動');
		'''

       
li2 =iii.splitlines()
#print(li2)
for ro in li2:
    #print("---")
    #print(ro)
    conn.execute(ro)
        
        
conn.commit()


conn.close()

