# -*- coding=utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import sqlite3
import time
import markdown
import MySQLdb
def db(sql):
    try:
        conn = MySQLdb.connect(host = '127.0.0.1',user = 'root',passwd = 'password',db = 'database',port = 3306,charset="utf8")
        cur=conn.cursor()
        cur.execute(sql)
    except Exception as e:
        raise ValueError("DataBase is invalid：%s" % e)
    else:
        if sql.find('INSERT') != -1 or sql.find('UPDATE') != -1:
            conn.commit()
        result = cur.fetchall()
        conn.close()
        cur.close()
        return result

def exe(SQL):
    try:
        db(SQL)
    except Exception as e:
        return '操作失败，原因：%s' % e
    else:
        return '操作成功!'
def edit(TYPE,subject,tags,MARKDOWN,Category,*ID):
    html = markdown.markdown(MARKDOWN).replace('\'','\'\'').replace('\\','\\\\')
    SHORT = markdown.markdown(MARKDOWN.decode('utf8')[0:300].encode('utf8')).replace('\'','\'\'').replace('\\','\\\\')
    MARKDOWN = MARKDOWN.replace('\'','\'\'').replace('\\','\\\\')
    if TYPE=='ADD':
        try:
            db(u'''INSERT INTO MY (TITLE,tags,HTML,MARKDOWN,SHORT,CREATETIME,LEIBIE,HASH) VALUES ('%s',
                                '%s','%s','%s',
                                '%s',now(),'%s',date_format(now(),'%%Y%%m'));''' % (subject,tags,html,MARKDOWN,SHORT,Category))
        except Exception as e:
            print e
            return False
        else:
            return True
    elif TYPE=='EDIT':
        try:
            db(u"UPDATE MY SET TITLE='%s',tags='%s',HTML='%s',MARKDOWN='%s',SHORT='%s',LEIBIE='%s' WHERE ID=%s " % (subject,tags,html,MARKDOWN,SHORT,Category,ID[0]))        
        except Exception as e:
            print e
            return False
        else:
            return True
def MARKDOWN(ID):
    MARKDOWN = db("SELECT title,tags,MARKDOWN,LEIBIE FROM MY WHERE ID='%s'" % ID)
    if MARKDOWN:
        return MARKDOWN[0]
def delete(ID):
    try:
        db("DELETE FROM MY WHERE ID='%s'" % ID)
    except:
        return False
    else:
        return True
    
def check(user,pwd):
    DBPWD=False
    try:
        DBPWD = db("SELECT PASSWORD FROM Ver WHERE USERNAME='%s'" % user)[0][0]
    except:
        pass
    if DBPWD:
        if DBPWD==pwd:
            return True
        else:
            return False
    else:
        return False
def userlist():
    userlist = ("SELECT USER FROM Ver")
    return [i[0] for i in userlist]