import psycopg2

"""
Explanation: API
End of explanation
"""


class CloudDatabase(object):
    # Init database and input ip
    # 初始化数据库并传入ip
    def __init__(self, database, user, password, ip="127.0.0.1", port="5432"):
        self.database = database
        self.user = user
        self.password = password
        self.ip = ip
        self.port = port

    # Connect database and set input as host, return connection and cursor
    # 连接ip端数据库并返回connection和cursor
    def db_connect(self):
        self.connection = psycopg2.connect(database=self.database, user=self.user,
                                           password=self.password, host=self.ip, port=self.port)
        self.cursor = self.connection.cursor()

    def execute(self, sql):
        try:
            self.cursor.execute(sql)
            self.connection.commit()
        except Exception as e:
            self.connection.rollback()
            
    # Write log file
    # 输出日志
    def write_log(self, e):
        self.connection.rollback()
        with open("log.txt", 'a') as log_file:
            log_file.writelines(str(e))

database = CloudDatabase("PlaceEmotion", "postgres", "postgres", "127.0.0.1")
database.db_connect()

# Test API
import requests
sql = '''
SELECT key, secret
FROM API
WHERE type = 'flickr'
'''
database.execute(sql)
for api in database.cursor.fetchall():
    print(api[0], api[1])

"""
Explanation: CREATE TABLE API
(key CHARACTER VARYING(100) NOT NULL PRIMARY KEY,
secret CHARACTER VARYING(100) NOT NULL,
type CHARACTER VARYING(10) NOT NULL,
start_use BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX key_index ON API(key);
CREATE INDEX secret_index ON API(secret);
CREATE INDEX type_index ON API(type);
CREATE INDEX start_use_index ON API(start_use);  
INSERT INTO API VALUES
('199ed59000c39dd0844b59d01fa7570c','4a2ce28f1bb8a1fe','flickr', FALSE),
('81ec25b2e0093c6f2c2e70da0175a7ea','6eaa89f23ae0762e','flickr', FALSE),
('4f3c045b20127210215889331a6ab134','2010f9a1cf1abe18','flickr', FALSE),
('382e669299b2ea33fa2288fd7180326a','b556d443c16be15e','flickr', FALSE),
('b422ff64b04ecee4c169ca01a21f5bcb','57d1f80344df6188','flickr', FALSE),
('042e20a01e7080ae8a7a4889208d215f','c2d8a1f6b9760cb9','flickr', FALSE),
('fc7b495434337ecb3ea080a3f410b0c6','8ed6d435785a2023','flickr', FALSE),
('696005e9ef76d9ef438c99f21605b322','9085b2dbbae8ede5','flickr', FALSE),
('7ce333ecbbf8ab34a6843e0edeeed7f4','c72121c17578668c','flickr', FALSE),
('2c8b19dfe58aded2e3a756bfba941558','9ef37b12d94a3da4','flickr', FALSE),
('3d3687711d33d98a847a34e094cd228d','27b2d210f4142b6b','flickr', FALSE),
('3f647b14450052251bbda3d4f8e3efd4','afa71fda070402ad','flickr', FALSE),
('60ff62a1aa6701d0bbd642663cf35d96','729cd4f7b7c92931','flickr', FALSE),
('75b8a444d318c769ae8142b1351a2b3c','a8723ba65b8ca827','flickr', FALSE),
('089c5cfa0ff527fdb25cd8b0a547e914','2dbdc4ce5d3e752b','flickr', FALSE),
('40e4f0df81f75752f74e36683808d7ab','f1a8144378ed93d4','flickr', FALSE),
('622f1f3e57c9f337d1212444831ff475','fafddabfbe0405b7','flickr', FALSE),
('1f7c3204baf7159d4ec9e833ee21d11f','90b9ed8a906812f3','flickr', FALSE);  
INSERT INTO API VALUES
('DCTCyC_D_3ZGW4VZVkmj25IJNUuNdXT4','T9NzaLTeiSe_Rqtye0pUCs3Ed-ZQiq6V','Face++',False),
('Dm18HrboYUuAVeX6A-6Y0q27OkE3cvN','3FPheINGB2qLTDcV-FOpjloXmOjDDG4A','Face++',False),
('AB3ubqE6FNZGPxu8NE4c1F0OJCn8HInI','VHH-kDCN1MQ_Ru2qq21YPyOwVF-e2Hmb','Face++',False),
('HGLXmBY-gO6WsCB4ZkFpEadYDD3SO4_S','oN8M-apFgr-4V4qW2W7TSuyUHy2Gw89','Face++',False),
('Xgv1FMjf7zaGVfcR_hlpdyif3Ez0q1m5','ISIsT6DYelUG9iPYS3NEKJ1dTfyReSph','Face++',False),
('D8KfUEN_6PM1yCHveoKuxgZ0GT21X1Bh','7vXMF7dsWNa7CIZiSOKs5JZp_XACHvf','Face++',False),
('Fu4-6MVuWgGCUeIxjSYRusH8eVilBtAz','epLYs0n7YLAWaYjA7HlXuLILHjKnL5iJ','Face++',False),
('mhFDxxTfQy6Rd4u298kwFvxbCxAMCpHq','yMZBadr5wVFsUWSZ_I0jo0w9vzjDVEii','Face++',False),
('1iPU-v_kLZs5OXjUZBlFL6ooTci8U3Yp','jTt_gIkc2bqnYGKucZboLuSQuUBzT2Vk','Face++',False),
('h_KYerXL2QeTmsAJGGBiPZFxl7_QXmiO','v1uoVifc12-1SYRsyNxgSR5k4QQqyK9u','Face++',False),
('sPG0n8LFaQfxxglBtbverq7iJgPs8DMQ','HBd-aaVTudtepT9bhybHKtJaLx7sMw1Y','Face++',False),
('hOLmeAvF4_gPFycCYZO4HoyJsFa7rlHc','gqAKxnvHvxpgt-mnHZMc7EwCD2XyHk0-','Face++',False),
('nBbJ3_x3xmnmc7CJO_VrK8vT8lmGlZIB','HB12_nEr3Y2I9rgKCtN0KKYX-LPA-sUU','Face++',False),
('_VTJAxAE34Xt-h236X1LVbO6gMJwszLN','f9roZWjSMH7csIVn4RHuGQ6ZDOwxhuCx','Face++',False),
('oSzQJ7Owxqk2T5yZuIHKoJ3s_n11BDQP','wv-sD-NgKWm0kRK757UxUDsbuzj0TYs ','Face++',False),
('NCv1iO90aVJ4Axy-FjE2SemVMP50Fg8e','xjKxi7n4qfO9h3N1uAThcMD01L6478YK','Face++',False),
('jFIzayRBM4X3-YJzzXBkZlzXy5MPe1Pl','spDfyMN7FnwnS5hbtnxFhxtuFmKeNPRs','Face++',False),
('TUWlFehF2kAEowOZRVyLa-0XzjoFKvUR','d5T4Lpx6DAFMTNRr6kfkcr6ZuQgfhosc','Face++',False),
('RsqKoZtF2Zcj1zKdn0i4-3yTCrtxeuVo','J2qGXnlTLcUqdS9CJ_6M-gt57vVohjy-','Face++',False),
('bbhAbpc7IrCGCWzrUu1bftZhR9iSkOh4','2jTiWxN2dCdTefw8ZbX0V8f_EOVzxbvk','Face++',False),
('8k8rid_MisVBXRSuAeiLVA5ymeT_8eYQ','gw2dA-e0fwv20icOC6OucBcRgc9yPi8k','Face++',False),
('ebfsStmOrxvn-kppu-1AXrsYmrJSmB0','NqvtZaDgEfV4U5m2cn-7agaw5WEpLL2z','Face++',False),
('mDCcCZx7EqeQjRu2yv8byuCB_xWkFMyd','fn73kxqkkP0mesI-gcPBk0BUmrAmdzTe','Face++',False);
End of explanation
"""


# Import all sampling points
file = open('/media/raid/PlaceEmotion/pts200_us.csv', 'r')
st = file.read()
file.close()
sql = '''
INSERT INTO location(city_name, lat, lon)
VALUES  
{0}
'''.format(st)
#print(sql)
database.execute(sql)

"""
Explanation: Location
CREATE TABLE location
(id SERIAL PRIMARY KEY,
city_name CHARACTER VARYING(100) NOT NULL,
lat FLOAT NOT NULL,
lon FLOAT NOT NULL,
start_query BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX id_index ON location(id);
CREATE INDEX city_name_index ON location(city_name);
CREATE INDEX start_query_index ON location(start_query);
End of explanation
"""


import datetime
DATE=datetime.date(2012,1,1) 
while(True): 
    DATE2=DATE+datetime.timedelta(days=10) 
    datemin ="{0}-{1}-{2}".format(DATE.year,DATE.month,DATE.day) 
    datemax ="{0}-{1}-{2}".format(DATE2.year,DATE2.month,DATE2.day) 
    DATE=DATE+datetime.timedelta(days=10) 
    print(datemin,datemax) 
    #get_photo_from_location(db_connection, db_cursor, site, latitude, longitude, datemin, datemax) 
    if DATE.year==2018 and DATE.month==11: 
        break 

"""
Explanation: CREATE TABLE photo
(id BIGINT PRIMARY KEY,
url TEXT NOT NULL,
city CHARACTER VARYING(100) NOT NULL,
face_number INTEGER DEFAULT(NULL),
start_detect BOOLEAN NOT NULL DEFAULT(FALSE),
start_info BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX photo_id_index ON photo(id);
CREATE INDEX photo_start_info_index ON photo(start_info);
CREATE INDEX photo_start_detect_index ON photo(start_detect);
CREATE INDEX photo_face_number_index ON photo(face_number);  
CREATE TABLE photo_info
(id BIGINT PRIMARY KEY, 
owner CHARACTER VARYING (30), 
owner_location CHARACTER VARYING (30),  
lat FLOAT,
lon FLOAT,
photo_take_date DATE,
photo_upload BIGINT,
accuracy INTEGER,
geotag TEXT,
neighbourhood TEXT;
locality TEXT;
county TEXT;
region TEXT;
country TEXT;
);
CREATE INDEX photo_id_index ON photo(id);
CREATE INDEX photo_lat_index ON photo(lat);
CREATE INDEX photo_lon_index ON photo(lon);
CREATE INDEX photo_date_index ON photo(photo_take_date);
CREATE INDEX photo_f_hasface_index ON photo(f_hasface);
CREATE INDEX photo_start_detect_index ON photo(start_detect);
CREATE INDEX photo_start_info_index ON photo(start_info);
CREATE INDEX photo_start_recog_index ON photo(start_recog);
CREATE INDEX photo_facenum_index ON photo(facenum);  
CREATE INDEX photo_lat_index ON photo(lat);
CREATE INDEX photo_lon_index ON photo(lon);
CREATE INDEX photo_date_index ON photo(photo_take_date);
CREATE INDEX photo_f_hasface_index ON photo(f_hasface);
CREATE INDEX photo_start_detect_index ON photo(start_detect);  
Four layers framework:
    1. API generation and location selection: return API and lat, lon, city.
    2. collect photo url and id to photo table: input API and lat, lon, city, return photo id and url.
    3. get photo information to photo_info table: input photo id, return photoinfo.
    4. detect photo face info to face table. input photo id, return emotion info.
End of explanation
"""


