#!/usr/bin/env python
import optparse
import psycopg2
import sqlite3
import datetime
from roundup.date import Date

def _convert_date(value):
    if value is None:
        return value
    return datetime.datetime(*Date(str(value)).get_tuple()[:6])

def _convert_boolean(value):
    return bool(value)

_to_convert = {
    '_msg': (('_date', _convert_date),),
    '_user': (('_newissuecopy', _convert_boolean),),
    }

def _convert(cursor, table, columns):
    for row in cursor:
        row = list(row)
        extra_columns = ()
        if table.startswith('_'):
            extra_columns = (('_activity', _convert_date),
                ('_creation', _convert_date))
        elif table.endswith('__journal'):
            extra_columns = (('date', _convert_date),)
        for column, fnct in (_to_convert.get(table, ()) + extra_columns):
            if column in columns:
                i = columns.index(column)
                row[i] = fnct(row[i])
        row = tuple(row)
        yield row

def main(sqlite_cursor, pg_cursor):
    sqlite_cursor.execute('SELECT name FROM sqlite_master '
        'WHERE type = ?', ('table',))
    for table, in sqlite_cursor.fetchall():
        pg_cursor.execute('SELECT relname FROM pg_class '
            'WHERE relkind = %s AND relname = %s', ('r', table,))
        if not bool(pg_cursor.rowcount):
            continue
        sqlite_cursor.execute('SELECT * from "%s"' % table)
        columns = [x[0] for x in sqlite_cursor.description]
        query = ('INSERT INTO "%s" (%s) VALUES (%s)'
            % (table, ','.join('"%s"' % x for x in columns),
                ','.join(('%s',) * len(columns))))
        pg_cursor.executemany(query, _convert(sqlite_cursor, table, columns))
    sqlite_cursor.execute('SELECT name, num FROM ids')
    for name, num in sqlite_cursor:
        pg_cursor.execute('ALTER SEQUENCE "_%s_ids" RESTART WITH %%s'
            % name, (num,))

if __name__ == '__main__':
    parser = optparse.OptionParser(version='0.1')
    parser.add_option('--sqlite', dest='file', help='SQLite database file')
    parser.add_option('--pg', dest='dsn', help='dsn for PostgreSQL')

    opt, args = parser.parse_args()
    pg_conn, sqlite_conn = None, None
    pg_cursor, sqlite_cursor = None, None
    try:
        pg_conn = psycopg2.connect(opt.dsn)
        pg_cursor = pg_conn.cursor()
        sqlite_conn = sqlite3.connect(opt.file,
            detect_types=sqlite3.PARSE_DECLTYPES)
        sqlite_cursor = sqlite_conn.cursor()
        main(sqlite_cursor, pg_cursor)
        pg_conn.commit()
    finally:
        if pg_cursor:
            pg_cursor.close()
        if pg_conn:
            pg_conn.close()
        if sqlite_cursor:
            sqlite_cursor.close()
        if sqlite_conn:
            sqlite_conn.close()