#!/usr/bin/python

import sqlite3

################################################################################
#                      private
################################################################################

DBFILE = "movitator.db";

def _querywritetuple(query, tpl):
  """
  apply modification to databe
  """
  con = sqlite3.connect(DBFILE);
  cur = con.cursor();
  cur.execute(query,tpl);
  con.commit();
  cur.close();

def _queryreadtuple(query,tpl):
  """
  query database with param
  yield result
  """
  con = sqlite3.connect(DBFILE);
  cur = con.cursor();
  cur.execute(query,tpl);
  for result in cur.fetchall():
    yield result;
  con.commit();
  cur.close();

def _queryread(query):
  """
  query database
  yield result
  """
  con = sqlite3.connect(DBFILE);
  cur = con.cursor();
  cur.execute(query);
  for result in cur.fetchall():
    yield result;
  con.commit();
  cur.close();

################################################################################
#                      create
################################################################################

ADDMOVIE = """INSERT INTO movie(numero,title,imdb,about) 
VALUES(?,?,?,?)""";

def addmovie(numero,title,imdb="",about=""):
  """
  add movie to the database
  """
  _querywritetuple(ADDMOVIE,(numero, title,imdb,about));

ADDPEOPLE = """INSERT INTO people(name,about) 
VALUES(?,?)""";

def addpeople(name,about=""):
  """
  add people to the database
  """
  _querywritetuple(ADDPEOPLE,(name,about));

BORROWMOVIE = """INSERT INTO borrow(movie_id,people_id,borrowed,returned) 
VALUES(?,?,DateTime('now'),NULL)""";

def borrowmovie(movieid, peopleid):
  """
  people borrow movie
  """
  _querywritetuple(BORROWMOVIE,(movieid,peopleid));

MANAGEMOVIE = """UPDATE movie SET title=?,imdb=?,about=?
WHERE id=?""";

################################################################################
#                      update
################################################################################

def managemovie(ID,title,imdb="",about=""):
  """
  change information about a movie
  """
  _querywritetuple(MANAGEMOVIE,(title,imdb,about,ID));

MANAGEPEOPLE = """UPDATE people SET name=?,about=?
WHERE id=?""";

def managepeople(ID,name,about=""):
  """
  change information about people
  """
  _querywritetuple(MANAGEPEOPLE,(name,about,ID));

RETURNMOVIE = """UPDATE borrow SET returned=DateTime('now')
WHERE movie_id=? AND returned IS NULL""";

def returnmovie(movieid):
  """
  people return a movie
  """
  _querywritetuple(RETURNMOVIE,(movieid,));

################################################################################
#                      read
################################################################################

SHOWMOVIEDUE = """SELECT * FROM notreturnedmovie"""

def showmoviedue():
  """
  all movie not returned
  yield (movie_id,people_id,movie_numero,movie_title,borrower,borrowed)
  """
  return _queryread(SHOWMOVIEDUE);

SHOWMOVIEDUEBYPEOPLE = """SELECT * FROM notreturnedmovie
WHERE borrower LIKE %?%"""

def showmovieduebypeople(peoplename):
  """
  all movie not returned by a people
  yield (movie_id,people_id,movie_numero,movie_title,borrower,borrowed)
  """
  return _queryreadtuple(SHOWMOVIEDUEBYPEOPLE,(peoplename,));

SHOWDISPONNIBLEMOVIE = """SELECT * FROM disponiblemovie""";

def showdisponniblemovie():
  """
  all movie not currently borrowed
  yield (id,numero,title,imdb,about)
  """
  return _queryread(SHOWDISPONNIBLEMOVIE);

SHOWMOVIE = """SELECT * FROM movie""";

def showmovie():
  """
  all movie
  yield (id,numero,title,imdb,about)
  """
  return _queryread(SHOWMOVIE);

SHOWMOVIEBYNAME = """SELECT * FROM movie 
WHERE title LIKE ?""";

def showmoviebyname(moviename):
  """
  all movie whose name look alike
  yield (id,numero,title,imdb,about)
  """
  return _queryreadtuple(SHOWMOVIEBYNAME,(moviename,));

SHOWMOVIEBYNUMBER = """SELECT * FROM movie 
WHERE numero=?""";

def showmoviebynumber(movienumber):
  """
  all movie that matche movienumber
  should be only one
  yield (id,numero,title,imdb,about)
  """
  return _queryreadtuple(SHOWMOVIEBYNUMBER,(movienumber,));

SHOWPEOPLE = """SELECT * FROM PEOPLE""";

def showpeople():
  """
  """
  return _queryread(SHOWPEOPLE);

SHOWPEOPLEBYNAME = """SELECT * FROM PEOPLE
WHERE name LIKE ?""";

def findpeople(name):
  """
  """
  return _queryreadtuple(SHOWPEOPLEBYNAME,(name,));

################################################################################
#                      test
################################################################################
if __name__ == "__main__":
  def test():
    peopleid = [];
    movieid = [];

    print "test movie table";
    addmovie(1,"titre1","","un film");
    addmovie(2,"titre2","","");
    for i in showmovie():
      print i;
      movieid.append(i[0]);

    print "test people table";
    addpeople("george","");
    addpeople("maurice","il put");
    for i in showpeople():
      print i;
      peopleid.append(i[0]);
    
    print "borrow some movie";
    borrowmovie(movieid[0],peopleid[0]);
    borrowmovie(movieid[1],peopleid[0]);
    for i in _queryread("SELECT * FROM borrow"):
      print i;

    print "show due movie";
    for i in showmoviedue():
      print i;

    print "return one";
    returnmovie(movieid[1]);
    for i in showmoviedue():
      print i;

    dispo = [];
    print "now what is disponnible";
    for i in showdisponniblemovie():
      print i;
      dispo.append(i[0]);

    print "lets rerent this movie";

    print "post test clear all";
    con = sqlite3.connect(DBFILE);
    cur = con.cursor();
    cur.executescript("""
      DELETE FROM borrow WHERE 1;
      DELETE FROM people WHERE 1;
      DELETE FROM movie WHERE 1;
    """);
    con.commit();
    con.close();
    print "done";

    print "\(^_^)/", "yeeee";
  
  import sys;

  if len(sys.argv) > 1 and sys.argv[1] == "test":
    pass;
    #test();
  elif len(sys.argv) > 3 and sys.argv[1] == "addmovie":
    numero = sys.argv[2];
    title = sys.argv[3];
    if 4 in (i for i,x in enumerate(sys.argv)):
      imbd = sys.argv[4];
    else:
      imdb = "";
    if 5 in (i for i,x in enumerate(sys.argv)):
      plot = sys.argv[5];
    else:
      plot = "";
    addmovie(numero,title,imdb,plot);
  elif len(sys.argv) > 2 and sys.argv[1] == "addpeople":
    name = sys.argv[2];
    if 3 in (i for i,x in enumerate(sys.argv)):
      comment = sys.argv[2];
    else:
      comment = "";
    addpeople(name,comment);
  elif len(sys.argv) > 1 and sys.argv[1] == "showpeople":
    print "people";
    if 2 in (i for i,x in enumerate(sys.argv)):
      lst = findpeople(sys.argv[2]);
    else:
      lst = showpeople();
    for id,name,about in lst:
      print "name:",name;
      print "about:",about;
      print
  elif len(sys.argv) > 1 and sys.argv[1] == "showmovie":
    print "movie";
    if 2 in (i for i,x in enumerate(sys.argv)):
      if sys.argv[2].isdigit():
        lst = showmoviebynumber(sys.argv[2]);
      else:
        lst = showmoviebyname(sys.argv[2]);
    else:
      lst = showmovie();
    for id,numero,title,imdb,about in lst:
      print "numero:",numero;
      print "title:",title;
      print "imdb:",imdb;
      print "about:",about;
      print
  elif len(sys.argv) > 1 and sys.argv[1] == "showdue":
    print "movie currently due";
    if 2 in (i for i,x in enumerate(sys.argv)):
      lst = showmovieduebypeople(sys.argv[2]);
    else:
      lst = showmoviedue();
    for id1,id2,numero,title,name,rented in lst:
      print "name:",name;
      print "rented",rented;
      print "numero:",numero;
      print "title:",title;
      print
  elif len(sys.argv) > 1 and sys.argv[1] == "showdisponnible":
    print "disponnible movie";
    for id,numero,title,imdb,about in showdisponniblemovie():
      print "numero:",numero;
      print "title:",title;
      print "imdb:",imdb;
      print "about:",about;
      print
  elif len(sys.argv) > 3 and sys.argv[1] == "borrow":
    for people in findpeople(sys.argv[2]):
      for movie in showmoviebynumber(sys.argv[3]):
        borrowmovie(movie[0], people[0]);
        break;
      break;
  elif len(sys.argv) > 2 and sys.argv[1] == "return":
    for movie in showmoviebynumber(sys.argv[2]):
      returnmovie(movie[0]);
  else:
    print "usage:",sys.argv[0],"command, value, [facultative value]";
    print "command:";
    print "   addmovie numero \"title\" [imdb] [\"plot\"] ";
    print "   addpeople \"name\" [\"comment\"] ";
    print "   showmovie [\"title or number\"]";
    print "   showpeople [\"name\"]";
    print "   showdue [\"name\"]";
    print "   showdisponnible";
    print "   borrow \"peoplename\" \"movienumber\"";
    print "   return \"movienumber\"";