import sqlite3, csv 


def convert_row(inp_row):
    output_row=[]
    for i in inp_row:
        print i, type(i)
        if isinstance(i,str) or isinstance(i,unicode): 
            if len(i)==0: 
                i='NULL'
        output_row.append(i)
    return output_row

conn=sqlite3.connect("test.db")

cursor=conn.cursor()

create_2mass='''create table TWOMASS (ID INT, 
                                 RD DOUBLE, 
                                 RAVIZIER DOUBLE, 
                                 DECVIZIER DOUBLE,  
                                 RA2000 DOUBLE,
                                 DEC2000 DOUBLE,
                                 TWOMASSID CHAR(18),
                                 JMAG DOUBLE,
                                 EJMAG DOUBLE,
                                 HMAG DOUBLE,
                                 EHMAG DOUBLE,
                                 KMAG DOUBLE,
                                 EKMAG DOUBLE,
                                 QFLG CHAR(3),
                                 RFLAG CHAR(3),
                                 BFLAG CHAR(3),
                                 CFLAG CHAR(3),
                                 XFLAG INT,
                                 AFLG INT)'''

create_usno='''create table USNO (ID INT, 
                                 RD DOUBLE, 
                                 RAVIZIER DOUBLE, 
                                 DECVIZIER DOUBLE,  
                                 USNOID CHAR(14),
                                 RA2000 DOUBLE,
                                 DEC2000 DOUBLE,
                                 ACTFLAG CHAR(2),
                                 MFLAG CHAR(2),
                                 BMAG DOUBLE,
                                 RMAG DOUBLE,
                                 EPOCH DOUBLE)'''


create_crossid='''create table CROSSID  (CROSSID INT, 
                                          IDTWOMASS INT, 
                                          IDUSNOA2 INT, 
                                          PRIMARY KEY (CROSSID), 
                                          FOREIGN KEY (IDTWOMASS) REFERENCES TWOMASS(ID), 
                                          FOREIGN KEY (IDUSNOA2) REFERENCES USNO(ID)) 
               '''

cursor.execute('''drop table if exists TWOMASS ''')
cursor.execute('''drop table if exists USNO ''')
cursor.execute('''drop table if exists CROSSID ''')
cursor.execute(create_2mass)
cursor.execute(create_usno)
cursor.execute(create_crossid)
cursor.close()

f_2mass=open("2MASS.tsu","r")
f_usno=open("USNO.tsu","r")

r_2mass=csv.reader(f_2mass,delimiter="|")
r_usno=csv.reader(f_usno,delimiter="|")
rn_2mass=0

cursor=conn.cursor()

i_c=0
for row in r_2mass:
    rn_2mass+=1
    if len(row)>0:
        if rn_2mass>=81:
            insert_2mass='''INSERT INTO TWOMASS VALUES("%i",''' % i_c +','.join('"%s"' % r_i for r_i in row)+''')'''
            cursor.execute(insert_2mass)
            i_c+=1
print i_c
rn_usno=0
i_c=0
for row in r_usno:
    rn_usno+=1
    if len(row)>0:
        if rn_usno>=57:
            insert_usno='''INSERT INTO USNO VALUES("%i",''' % i_c +','.join('"%s"' % r_i for r_i in row)+''')'''
            cursor.execute(insert_usno)
            i_c+=1
print i_c

cursor.close()

cursor1=conn.cursor()
cursor3=conn.cursor()

cursor1.execute("SELECT ID, RA2000, DEC2000 FROM TWOMASS")
sr=1.0/60.0/60.0*15.0

id_cross=1
while True: 
    row=cursor1.fetchone()
    if row:
        select_str='''SELECT ID FROM USNO WHERE (%s -RA2000)*(%s -RA2000)+(%s -DEC2000)*(%s-DEC2000) <= %s''' % ( row[1],row[1],row[2],row[2],sr*sr )
        cursor2=conn.cursor()
        cursor2.execute(select_str)
        row2 = cursor2.fetchall()
        if len(row2) >0:
            for newid in row2 :
                ingest_statement ='''INSERT INTO CROSSID VALUES (%s ,%s ,% s )''' % ( id_cross,row[0],newid[0])
#                print ingest_statement
                cursor3.execute(ingest_statement)
#                print id_cross,row[0], newid
                id_cross = id_cross +1
        cursor2.close()
    else:
        break
print id_cross



cursor1.close()
cursor3.close()

conn.commit()
conn.close()