#!/usr/bin/env python
import MySQLdb
import time,random
import hashlib,time
from random import Random
import copy
from  termcolor import colored
import getpass
import re,sys

jcbPrefixList = [['3', '5']]
ccbPrefixList = [['4', '2']]
voyagerPrefixList = [['8', '6', '9', '9']]

def completed_number(prefix, length):
  """
  'prefix' is the start of the CC number as a string, any number of digits.
  'length' is the length of the CC number to generate. Typically 13 or 16
  """
  ccnumber = prefix
  # generate digits
  while len(ccnumber) < (length - 1):
    digit = str(generator.choice(range(0, 10)))
    ccnumber.append(digit)
  # Calculate sum
  sum = 0
  pos = 0
  reversedCCnumber = []
  reversedCCnumber.extend(ccnumber)
  reversedCCnumber.reverse()
  while pos < length - 1:
    odd = int(reversedCCnumber[pos]) * 2
    if odd > 9:
      odd -= 9
    sum += odd
    if pos != (length - 2):
      sum += int(reversedCCnumber[pos + 1])
    pos += 2
  # Calculate check digit
  checkdigit = ((sum / 10 + 1) * 10 - sum) % 10
  ccnumber.append(str(checkdigit))
  return ''.join(ccnumber)


def credit_card_number(rnd, prefixList, length, howMany):
    result = []
    while len(result) < howMany:
      ccnumber = copy.copy(rnd.choice(prefixList))
      result.append(completed_number(ccnumber, length))
    return result

def PStartinfo():
    print colored ("""Please  choose action you want to do
            1) create database and account , everything is new, before data is gone
            2) add user
            3) check user information
            4) update
            5) Delete
            6) Quit
    """,'green',attrs=['bold','blink'])

def CreateTable():
    read = raw_input("if you  database is exits ,go on will delet and create a new database:yes or no").strip().lower()
    if read == 'yes':
        try:
            conn = MySQLdb.connect(host='localhost',user='root',passwd='asher',port=3306)
            cur = conn.cursor()
            cmd_dropdb = 'drop database if  exists bank'
            cur.execute(cmd_dropdb)
            cmd_createdb = 'create database if not exists bank'
            cmd_createtable = 'create table account(id int  not null auto_increment PRIMARY KEY, kahao varchar(20), name varchar(20), sfid varchar(40),tel varchar(20), passwd varchar(40),quota varchar(20))'
            cur.execute(cmd_createdb)
            conn.select_db('bank')
            cur.execute('%s' % cmd_createtable)
            cur.execute('alter table account AUTO_INCREMENT=10000;')
            conn.commit()
            cur.close()
            conn.close()
            print colored("Your database and account table has create",'blue',attrs=['reverse', 'blink'])
        except MySQLdb.Error,e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
    else:
        Main()

def CreateUser():
    try:
        global generator
        conn = MySQLdb.connect(host='localhost',user='root',passwd='asher',port=3306)
        cur = conn.cursor()
        conn.select_db('bank')
        values = []
        while True:
            generator = Random()
            generator.seed()
            print "Create User,or quit\n"
            user = raw_input("User Name:").strip().lower()
            if len(user) == 0:
                break
                #continue
            if user == 'quit':
                break
            count=cur.execute("select * from account where name='%s'" % user)
            if count == 0:
                sfid = raw_input("Verify ID:")
                if len(sfid) == 0:
                    print "please input a right id"
                    print "repeat !!!!!!!!!!!!!"
                    continue
                tel = raw_input("Cell mobile:")
                #passwd = raw_input("Password:")
                passwd = getpass.getpass("Password:")
                if len(passwd) <=5:
                    print "password must more 5 char or number"
                    passwd = getpass.getpass("Password:")
                   # passwd = raw_input("Password:")
                else:
                    pass
                password_md5 = hashlib.md5(passwd).hexdigest()
                quota = raw_input("Limit quota:")
                kahao = credit_card_number(generator, ccbPrefixList, 16, 1)
                values.append((kahao[0],user,sfid,tel,password_md5,50000))
                cur.executemany('insert into account (kahao,name,sfid,tel,passwd,quota) values(%s,%s,%s,%s,%s,%s)',values)
            else:
                print "this name has exit, please input anthoner\n"
                #conn.commit()
                #cur.close()
                #conn.close()
                #CreateUser()
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

def UpdateUser(user):
    try:
        conn = MySQLdb.connect(host='localhost',user='root',passwd='asher',port=3306)
        cur = conn.cursor()
        conn.select_db('bank')
        values = []

        count=cur.execute("select * from account where name='%s'" % user)
        results=cur.fetchall()
        if results:
            for r in results:

                print colored("%-8s%-18s%-15s%-10s%-15s%-35s%-5s" % r,'yellow',attrs=['bold','blink'])

            print "You just can change password and quota\n"
            passwd = getpass.getpass("Password:")
            #passwd = raw_input("Password:")
            password_md5 = hashlib.md5(passwd).hexdigest()
            quota = raw_input("Limit quota:")
            #cur.execute('update account set passwd="%s",quota="%s" where name=%s' % (password_md5,quota,user))
            cur.execute("update account set passwd='%s',quota='%s' where name='%s'" % (password_md5,quota,user))
        else:
            print "no user"
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

def Checkdb(user):
    try:
        conn=MySQLdb.connect(host='localhost',user='root',passwd='asher',port=3306)
        cur=conn.cursor()
         
        conn.select_db('bank')
        if user == '' or user == 'all':
            count=cur.execute("select * from account")
        else:
            count=cur.execute("select * from account where name='%s'" % user)
        results=cur.fetchall()
        if results:
            for r in results:
                print colored("%-8s%-18s%-15s%-14s%-15s%-35s%-5s" % r,'yellow',attrs=['bold','blink'])
        else:
            print colored("No this user!!!",'red',attrs=['reverse', 'blink'])
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])


def Delete(user):
    try:
        conn=MySQLdb.connect(host='localhost',user='root',passwd='asher',port=3306)
        cur=conn.cursor()
         
        conn.select_db('bank')
        count=cur.execute("select * from account where name='%s'" % user)
        results=cur.fetchall()
        if results:
            for r in results:
                print colored("%-8s%-18s%-15s%-14s%-15s%-35s%-5s" % r,'yellow',attrs=['bold','blink'])
                print colored("This User will will delete",'red',attrs=['reverse','blink'])
            anser = raw_input("Are you sure! yes or no>:")
            if anser == 'yes':
                cur.execute("delete from account where name='%s'" % user)
            else:
                pass
        else:
            print colored("No this user!!!",'red',attrs=['reverse', 'blink'])
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

def Main():
    global user
    user = ''
    PStartinfo()
    while True:
        search = raw_input("please choose your selection:").strip().lower()
        if search == '1' or search == 'create': 
            CreateTable() 
            PStartinfo()
        elif search == '2' or search == 'adduser':
            CreateUser()
            PStartinfo()
        elif search == '3' or search == 'search':
            user = raw_input("please user name:").strip().lower()
            Checkdb(user)
            PStartinfo()
        elif search == '4' or search == 'update':
            user = raw_input('input user ').strip().lower()
            UpdateUser(user)
        elif search == '5' or search == 'delete':
            user = raw_input("please user name you want to delete:").strip().lower()
        #    if Checkdb(user):
            Delete(user)
        elif search == '6' or search == 'quit':
            exit(0)
            #break
        else:
            print colored("Check your input ,something wrong",'red',attrs=['reverse', 'blink'])

if __name__ == '__main__':
    Main()
