from trac.db import Table, Column, Index, DatabaseManager

def do_upgrade(env, ver, cursor):
    cursor.execute("CREATE TEMPORARY TABLE session_old AS SELECT * FROM session")
    cursor.execute("DROP TABLE session")
    cursor.execute("CREATE TEMPORARY TABLE ticket_change_old AS SELECT * FROM ticket_change")
    cursor.execute("DROP TABLE ticket_change")

    # A more normalized session schema where the attributes are stored in
    # a separate table
    tables = [Table('session', key=('sid', 'authenticated'))[
                Column('sid'),
                Column('authenticated', type='int'),
                Column('last_visit', type='int'),
                Index(['last_visit']),
                Index(['authenticated'])],
              Table('session_attribute', key=('sid', 'authenticated', 'name'))[
                Column('sid'),
                Column('authenticated', type='int'),
                Column('name'),
                Column('value')],
              Table('ticket_change', key=('ticket', 'time', 'field'))[
                Column('ticket', type='int'),
                Column('time', type='int'),
                Column('author'),
                Column('field'),
                Column('oldvalue'),
                Column('newvalue'),
                Index(['ticket']),
                Index(['time'])]]
    
    db_connector, _ = DatabaseManager(env)._get_connector()
    for table in tables:
        for stmt in db_connector.to_sql(table):
            cursor.execute(stmt)

    # Add an index to the temporary table to speed up the conversion
    cursor.execute("CREATE INDEX session_old_sid_idx ON session_old(sid)")
    # Insert the sessions into the new table
    db = env.get_db_cnx()
    cursor.execute("INSERT INTO session (sid, last_visit, authenticated) "
                   "SELECT distinct s.sid,COALESCE(%s,0),s.authenticated "
                   "FROM session_old AS s LEFT JOIN session_old AS s2 "
                   "ON (s.sid=s2.sid AND s2.var_name='last_visit') "
                   "WHERE s.sid IS NOT NULL"
                   % db.cast('s2.var_value', 'int'))
    cursor.execute("INSERT INTO session_attribute "
                   "(sid, authenticated, name, value) "
                   "SELECT s.sid, s.authenticated, s.var_name, s.var_value "
                   "FROM session_old s "
                   "WHERE s.var_name <> 'last_visit' AND s.sid IS NOT NULL")

    # Insert ticket change data into the new table
    cursor.execute("INSERT INTO ticket_change "
                   "(ticket, time, author, field, oldvalue, newvalue) "
                   "SELECT ticket, time, author, field, oldvalue, newvalue "
                   "FROM ticket_change_old")

    cursor.execute("DROP TABLE session_old")
    cursor.execute("DROP TABLE ticket_change_old")