from PyQt4.QtSql import *
from PyQt4 import *
from PyQt4.QtCore import *
from Ui_editTeamView import Ui_TeamEditDialog
import sys
class mainApp(Ui_TeamEditDialog):
	def __init__(self,parentDlg):
		super(Ui_TeamEditDialog, self).__init__()
		#Ui_TeamEditDialog.__init__(self)
		self.setupUi(parentDlg)
		
		self.db = QSqlDatabase.addDatabase("QSQLITE")
		self.db.setDatabaseName("test.sqlite")
		self.db.open()
		
		q = QSqlQuery()
		# check if first run
		q.exec_("SELECT * FROM TEAMS")
		if q.lastError().type() == QSqlError.StatementError:
			q.exec_("""CREATE TABLE teams (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
				name VARCHAR(255) NOT NULL,
				coachName VARCHAR(255) NULL,
				contactName VARCHAR(255) NULL,
				contactPhone VARCHAR(20) NULL,
				contactEmail VARCHAR(255) NULL)""")
			q.exec_("""CREATE TABLE players (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
				firstName VARCHAR(255) NOT NULL,
				lastName VARCHAR(255) NOT NULL,
				dob DATE NOT NULL,
				id_team INTEGER NOT NULL,
				FOREIGN KEY (id_team) REFERENCES teams)""")
			for i in ["1", "2", "3"]:
				q.prepare("""INSERT INTO teams (name, coachName, contactName, contactPhone, contactEmail) VALUES (:name,:coach,:contact,:phone,:email)""")
				q.bindValue(":name", QVariant(QString("testT"+i)))
				q.bindValue(":coach", QVariant(QString("testCoach"+i)))
				q.bindValue(":phone", QVariant(QString("testPhone2"+i)))
				q.bindValue(":contact", QVariant(QString("testContact2"+i)))
				q.bindValue(":email", QVariant(QString("test2@bakerstreetsystems.co.uk"+i)))
				q.exec_()
			for i in ["1", "2"]:
				q.exec_("""INSERT INTO players (firstName,lastName,dob,id_team) VALUES ('Ciccio%s','Pasticcio%s','15/12/1979',1)""" % (i, i ))
		# main model for team list
		self.teamsModel = QSqlRelationalTableModel(parentDlg, self.db)
		self.teamsModel.setTable("teams")
		self.teamsModel.select()
		# setup team list
		self.listTeams.setModel(self.teamsModel)
		self.listTeams.setModelColumn(1)
		# setup team details editor
		self.teamMapper = QtGui.QDataWidgetMapper(parentDlg)
		self.teamMapper.setSubmitPolicy(QtGui.QDataWidgetMapper.ManualSubmit)
		self.teamMapper.setModel(self.teamsModel)
		self.teamMapper.addMapping(self.lineTeamName, 1)
		self.teamMapper.addMapping(self.lineCoachName, 2)
		self.teamMapper.addMapping(self.lineContactName, 3)
		self.teamMapper.addMapping(self.lineContactPhone, 4)
		self.teamMapper.addMapping(self.lineContactEmail, 5)
		QObject.connect(self.listTeams.selectionModel(), SIGNAL("currentRowChanged(QModelIndex,QModelIndex)"),  self._moveToTeam)
		# setup players model
		self.playersModel = QSqlRelationalTableModel(parentDlg, self.db)
		self.playersModel.setTable("players")
		self.playersModel.setRelation(4,QSqlRelation("teams", "id","name"))
		# setup players table
		self.tablePlayers.setModel(self.playersModel)
		self.tablePlayers.setColumnHidden(0, True)
		self.tablePlayers.setColumnHidden(4, True)
		self.tablePlayers.verticalHeader().setVisible(False)
		self.tablePlayers.horizontalHeader().setStretchLastSection(True)
		
	def _moveToTeam(self, currIndex, fromIndex):
		""" utility function """
		if currIndex.isValid():
			self.teamMapper.setCurrentIndex(currIndex.row())
			id = currIndex.model().record(currIndex.row()).value("id").toInt()[0]
			self.playersModel.reset()
			self.playersModel.setFilter(QString("id_team = %1").arg(id))
		else:
			self.playersModel.setFilter("id_team = -1")
		self.playersModel.select()
		print str(self.playersModel.rowCount())
		# hide headers if no records
		self.tablePlayers.horizontalHeader().setVisible(self.playersModel.rowCount() > 0)
		
if __name__ == "__main__":

	app = QtGui.QApplication(sys.argv)
	mainDlg = QtGui.QDialog()
	appWindow = mainApp(mainDlg) 
	mainDlg.show()
	sys.exit(app.exec_())