#modules
import sqlite3
import autopy


import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import winsound
import smtplib
import webbrowser
import random
import matplotlib.pyplot as plt

#Test program
conn=sqlite3.connect("database.db")
c=conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS mathites(onoma TEXT,epitheto TEXT ,onoma_patros TEXT ,hmer_gen TEXT,katagogh TEXT,tmhma TEXT,apousies NUMERIC,mesos_oros TEXT,email_goniou TEXT,aritmos_mhtroou TEXT)")
conn.commit()
def add_student (self):


    onoma=raw_input("dose onoma mathith :")
    epitheto=raw_input("dose epitheto mathith :")
    onoma_patros=raw_input("dose onoma patros :")
    hmer_gen=raw_input("dose hmerominia genhshs :")
    katagogh=raw_input("dose katagogh tou mathith :")
    tmhma=raw_input("dose mou to tmhma")
    apousies=0

    mesos_oros=""
    while True:
        email_goniou=raw_input("dose to email goniou : ")
        try:
            pmsg=random.randint(10000,99999)
            s=smtplib.SMTP("smtp.gmail.com",587)
            s.ehlo()
            s.starttls()
            s.login("kostas2372@gmail.com","myllwcutinlyswpw")
            s.set_debuglevel(1)
            msg = MIMEText("Kalhspera sas hmaste apo to sxoleio tou giou ,\n O kodikos Epiveveoshs einai : "+str(pmsg))
            sender = 'kostas2372@gmail.com'
            recipients = [email_goniou]
            msg['Subject'] = "Kodikos Epiveveoshs"
            msg['From'] = sender
            msg['To'] = ", ".join(recipients)
            s.sendmail(sender, recipients, msg.as_string())
            epiv=raw_input("Dose ton kodiko epiveveoshs se parakalo : ")
            if (epiv==str(pmsg)):
                break
            elif(epiv!=str(pmsg)):
                print "Xanaprospathise me ton neo kodiko"
            else:
                print "error"
        except :
            autopy.alert.alert("Prospathise xan xoris grammata afthn thn fora")
    while True:
        aritmos_mhtroou=str(random.randint(10000,99999))
        conn=sqlite3.connect("database.db")
        c=conn.cursor()
        c.execute("SELECT * FROM mathites WHERE aritmos_mhtroou = (?)",(aritmos_mhtroou,))
        if c.fetchone() is None :
            conn.commit()
            break
        elif c.fetchone() is not None:
            pass

    try:
     conn=sqlite3.connect("database.db")
     c=conn.cursor()
     c.execute("INSERT INTO mathites(onoma, epitheto, onoma_patros, hmer_gen, katagogh, tmhma, apousies, mesos_oros, email_goniou, aritmos_mhtroou) VALUES (? , ?, ?, ?, ?, ?, ?, ?, ?, ?)",
     (onoma,epitheto,onoma_patros,hmer_gen,katagogh,tmhma,apousies,mesos_oros,email_goniou,aritmos_mhtroou))
     conn.commit()

    except:
     autopy.alert.alert("Error","Error")

def remove_student (self):
    conn=sqlite3.connect("database.db")
    c=conn.cursor()
    while True:
      std=raw_input("Which student you want to remove from database ?\n Put the Mhtroou Number :")
     #try:
      c.execute("SELECT * FROM mathites WHERE aritmos_mhtroou = (?)",(std,))
      for row in c.fetchall():
          onoma=row[0]
          ep=row[1]
          onoma_patros=row[2]
          hmer_gen=row[3]
          katagogh=row[4]
          tmhma=row[5]
          apousies=str(row[6])
          mesos_oros=row[7]
          email_goniou=row[8]
          aritmos_mhtroou=str (row[9])
          epiveveosh=raw_input(str("Are you sure that you want to delete the student with these info : \n Name : "+onoma+"\n epitheto : "+ep+"\n onoma patros : "+onoma_patros+"\n hmer. gen : "+hmer_gen+"\n Katagogh : "+katagogh+"\n Tmhma : "+tmhma+"\n Apousies : "+apousies+"\n Mesos Oros : "+mesos_oros+"\n Email Goniou : "+email_goniou+"\n Aritmos Mhtroou : "+aritmos_mhtroou+"\n (Y/N)"))
          if (epiveveosh=="Y" or epiveveosh=="yes" or epiveveosh=="y"):
             apousies=str(row[6])
             aritmos_mhtroou=(row[9])
             mail=smtplib.SMTP('smtp.gmail.com',587)
             mail.ehlo()
             mail.starttls()
             mail.login("kostas2372@gmail.com","myllwcutinlyswpw")
             mail.sendmail("kostas2372@gmail.com",email_goniou,"Your kid is not in our database anymore ")
             c.execute("CREATE TABLE IF NOT EXISTS diegramenoi_mathites(onoma TEXT,epitheto TEXT ,onoma_patros TEXT ,hmer_gen TEXT,katagogh TEXT,tmhma TEXT,apousies NUMERIC,mesos_oros TEXT,email_goniou TEXT,aritmos_mhtroou TEXT)")
             c.execute("INSERT INTO diegramenoi_mathites(onoma, epitheto, onoma_patros, hmer_gen, katagogh, tmhma, apousies, mesos_oros, email_goniou, aritmos_mhtroou) VALUES (? , ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (onoma,ep,onoma_patros,hmer_gen,katagogh,tmhma,apousies,mesos_oros,email_goniou,aritmos_mhtroou))
             c.execute("DELETE FROM mathites WHERE aritmos_mhtroou = (?) ",(std,))
             autopy.alert.alert("This account deleted","SUCCESS")
             conn.commit()
             break
     #except:
             autopy.alert.alert("We could not find this student please try again ")
def apousies(self):
    mathiths=raw_input("Grapse ton arithmo mhtroou tou mathth sto opoion thes na valeis apousia : ")
    conn=sqlite3.connect("database.db")
    c=conn.cursor()
    c.execute("SELECT * FROM mathites WHERE aritmos_mhtroou = (?)",(mathiths,))

    for row in c.fetchall():
      onoma=row[0]
      ep=row[1]
      onoma_patros=row[2]
      hmer_gen=row[3]
      katagogh=row[4]
      tmhma=row[5]
      apousies=str(row[6])
      mesos_oros=row[7]
      email_goniou=row[8]
      aritmos_mhtroou=str (row[9])
      print "Ta stoixeia tou mathith pou thes na valeis apousia : \n Name : "+onoma+"\n epitheto : "+ep+"\n onoma patros : "+onoma_patros+"\n hmer. gen : "+hmer_gen+"\n Katagogh : "+katagogh+"\n Tmhma : "+tmhma+"\n Apousies : "+apousies+"\n Mesos Oros : "+mesos_oros+"\n Email Goniou : "+email_goniou+"\n Aritmos Mhtroou : "+aritmos_mhtroou

      apousies_p_vl=input("Vale poses apousies theleis na tou prostheseis")
      ap_int=row[6]
      telikes_apousies=ap_int+apousies_p_vl
      if (telikes_apousies<0):
        print "You can't have -x apousies try again"
      elif (telikes_apousies>0):
        c.execute("UPDATE mathites SET apousies = (?) WHERE aritmos_mhtroou = (?)",(telikes_apousies,mathiths))
        conn.commit()
        if (telikes_apousies > 114):
            autopy.alert.alert("O mathiths me arithmo mhtroou : "+aritmos_mhtroou+"Kai me onomateponimo : "+onoma+" "+ep+"Exei mhnei apo apousies !","MATHITHS/A EMEINE")
            s=smtplib.SMTP("smtp.gmail.com",587)
            s.ehlo()
            s.starttls()
            s.login("kostas2372@gmail.com","myllwcutinlyswpw")
            s.set_debuglevel(1)
            msg = MIMEText("To paidi sas me aritmo mhtroou : "+str(aritmos_mhtroou)+"  kai onomateponimo : "+onoma+" "+ep+" emeine sthn idia taxh me arithmo apousion : "+str(telikes_apousies)+" Kaleste mas amesa sto 2510-xxx-xxx")
            sender = 'kostas2372@gmail.com'
            recipients = [email_goniou]
            msg['Subject'] = "To paidi sas emeine apo apousies !"
            msg['From'] = sender
            msg['To'] = ", ".join(recipients)
            s.sendmail(sender, recipients, msg.as_string())
        elif(telikes_apousies):
           s=smtplib.SMTP("smtp.gmail.com",587)
           s.ehlo()
           s.starttls()
           s.login("kostas2372@gmail.com","myllwcutinlyswpw")
           s.set_debuglevel(1)
           msg = MIMEText("Oi apousies tou paidiou sas einai : "+str(telikes_apousies))
           sender = 'kostas2372@gmail.com'
           recipients = [email_goniou]
           msg['Subject'] = "Apousies tou paidiou sas"
           msg['From'] = sender
           msg['To'] = ", ".join(recipients)
           s.sendmail(sender, recipients, msg.as_string())