db = DAL("sqlite://storage.sqlite")

db.define_table('closures',
                Field('closureid'),
                Field('lognum'),
                Field('route'),
                Field('direction'),
                Field('facility'),
                Field('startdate','date', comment="Must be stored as YYYY-MM-DD"),
                Field('enddate','date', comment="Must be stored as YYYY-MM-DD"),
                Field('starttime',length=4, comment="Must be stored as HHMM"),
                Field('endtime',length=4, comment="Must be stored as HHMM"),
                Field('startcounty'),
                Field('endcounty'),
                Field('startlocation'),
                Field('endlocation'),
                Field('closuretype'),
                Field('worktype'),
                Field('estdelay'),
                Field('tmpcozeep'),
                Field('tmpdetour'),
                Field('supervisor'),
                Field('fieldrep'),
                Field('meetingplace'),
                Field('reason'),
                Field('remarks'),
                Field('s1097date'),
                Field('s1097time'),
                Field('s1097user'),
                Field('s1097phone'),
                Field('s1098date'),
                Field('s1098time'),
                Field('s1098user'),
                Field('s1098phone'),
                Field('s1022date'),
                Field('s1022time'),
                Field('s1022user'),
                Field('s1022phone'),
                format = '%(closureid)s %(lognum)s')

db.closures.closureid.requires = IS_NOT_IN_DB(db, db.closures.closureid)
#db.closures.closureid.requires = IS_NOT_EMPTY()
db.closures.route.requires = IS_NOT_EMPTY()
db.closures.direction.requires = IS_NOT_EMPTY()
db.closures.facility.requires = IS_NOT_EMPTY()
db.closures.startcounty.requires = IS_NOT_EMPTY()
db.closures.startlocation.requires = IS_NOT_EMPTY()
db.closures.endlocation.requires = IS_NOT_EMPTY()
db.closures.closuretype.requires = IS_NOT_EMPTY()
db.closures.worktype.requires = IS_NOT_EMPTY()
db.closures.supervisor.requires = IS_NOT_EMPTY()

db.define_table('supervisors',
                Field('name'),
                Field('radiocallnum')
                )
db.supervisors.truncate() # delete all records and reset the counter of the id.
db.supervisors.import_from_csv_file(open('supervisors.csv', 'r')) #import the file

db.define_table('streets',
                Field('route'),
                Field('street')
                )
db.streets.truncate()
db.streets.import_from_csv_file(open('streets.csv', 'r')) #import the file

db.define_table('crew',
                Field('name'),
                Field('radiocallnum')
                )
db.supervisors.truncate() # delete all records and reset the counter of the id.
db.supervisors.import_from_csv_file(open('crew.csv', 'r')) #import the file