#!/usr/bin/python

import sqlite3
con = sqlite3.connect('crawl.db');
cur = con.cursor();

def createDB():
  with open ("createDB.sql", "r") as myfile:
    cur.executescript(myfile.read().replace('\n', ' '));

def purgeDB():
  with open ("purgeDB.sql", "r") as myfile:
    cur.executescript(myfile.read().replace('\n', ' '));

zone_query="""
SELECT w,h FROM sector WHERE id=:id
""";

def getZone():
  cur.execute(zone_query,{"id":1});
  return cur.fetchone();

entity_query="""
SELECT x,y,tag 
FROM completeEntityView 
WHERE id=:id
""";

def getEntity(id):
  cur.execute(entity_query,{"id":id});
  return cur.fetchall();

all_entity_query="""
SELECT x,y,tag 
FROM completeEntityView
""";

def getAllEntity():
  cur.execute(all_entity_query);
  return cur.fetchall();

move_query="""
UPDATE entity 
SET x=x+:x,y=y+:y 
WHERE id=:id
""";

def doMove(x,y):
  cur.execute(move_query,{"x":x, "y":y, "id":1});

will_go_out_the_sector_query=""" 
SELECT id 
FROM completeEntityInSectorView
WHERE id=:id
AND (nx+:mx<0 OR nx+:mx>=w OR ny+:my<0 OR ny+:my>=h)
""";

def willGorOutTheSector(entity_id,(mx,my)):
  cur.execute(will_go_out_the_sector_query,{"mx":mx,"my":my,"id":entity_id});
  for i in cur.fetchall():
    return True;
  return False;

will_collide_with_entity_query = """
SELECT id FROM willCollideWithEntity 
WHERE id=:id 
AND entity_part_rx = mover_part_rx+:mx 
AND entity_part_ry = mover_part_ry+:my 
GROUP BY entity.id
""";

def willCollideWithEntity(entity_id,(mx,my)):
  cur.execute(will_collide_with_entity_query,{"mx":mx,"my":my,"id":entity_id});
  for i in cur.fetchall():
    return True;
  return False;

if __name__ == "__main__":
  createDB();
  with open ("popDB.sql", "r") as myfile:
    cur.executescript(myfile.read().replace('\n', ' '));
  
  import cursedTower
  import time;
  cursedTower.init();
  control = {
    ord('w'):(0,-1),
    ord('s'):(0,1),
    ord('a'):(-1,0),
    ord('d'):(1,0)
  };
  c=None;
  while c!=ord('q'):
    time.sleep(0.1);
    c = cursedTower.getkey();
    if c in control:
      if not willGorOutTheSector(1,control[c]):
        if not willCollideWithEntity(1,control[c]):
          doMove(control[c][0], control[c][1]);
          #cursedTower.log("collide");
    #cursedTower.drawRoom(getZone());
    cursedTower.drawSprite(getAllEntity());
    cursedTower.refresh();
  cursedTower.end();
  
  purgeDB();
  con.commit();
  cur.close();