from alembic import op

from app.dao.returned_letters_dao import insert_or_update_returned_letters

revision = '0311_populate_returned_letters'
down_revision = '0310_returned_letters_table'


def upgrade():
    conn = op.get_bind()
    sql = """
        select id, service_id, reference 
        from notification_history 
        where notification_type = 'letter'
        and notification_status = 'returned-letter'"""
    results = conn.execute(sql)
    returned_letters = results.fetchall()
    references = [x.reference for x in returned_letters]
    insert_or_update_returned_letters(references)


def downgrade():
    pass

from alembic import op

revision = '0311_populate_returned_letters'
down_revision = '0310_returned_letters_table'


def upgrade():
    conn = op.get_bind()
    sql = """
        select id, service_id, reference, updated_at
        from notification_history 
        where notification_type = 'letter'
        and notification_status = 'returned-letter'"""
    insert_sql = """
        insert into returned_letters(id, reported_at, service_id, notification_id, created_at, updated_at) 
        values(uuid_in(md5(random()::text)::cstring), '{}', '{}', '{}', now(), null)
    """

    results = conn.execute(sql)
    returned_letters = results.fetchall()
    for x in returned_letters:
        f = insert_sql.format(x.updated_at.date(), x.service_id, x.id)
        conn.execute(f)


def downgrade():
    pass

Change the insert to use updated_at as the reported_at date
Change the insert to use updated_at as the reported_at date

  from alembic import op
- 
- from app.dao.returned_letters_dao import insert_or_update_returned_letters
  
  revision = '0311_populate_returned_letters'
  down_revision = '0310_returned_letters_table'
  
  
  def upgrade():
      conn = op.get_bind()
      sql = """
-         select id, service_id, reference 
+         select id, service_id, reference, updated_at
          from notification_history 
          where notification_type = 'letter'
          and notification_status = 'returned-letter'"""
+     insert_sql = """
+         insert into returned_letters(id, reported_at, service_id, notification_id, created_at, updated_at) 
+         values(uuid_in(md5(random()::text)::cstring), '{}', '{}', '{}', now(), null)
+     """
+ 
      results = conn.execute(sql)
      returned_letters = results.fetchall()
-     references = [x.reference for x in returned_letters]
-     insert_or_update_returned_letters(references)
+     for x in returned_letters:
+         f = insert_sql.format(x.updated_at.date(), x.service_id, x.id)
+         conn.execute(f)
  
  
  def downgrade():
      pass
  
Change the insert to use updated_at as the reported_at date
## Code Before:
from alembic import op

from app.dao.returned_letters_dao import insert_or_update_returned_letters

revision = '0311_populate_returned_letters'
down_revision = '0310_returned_letters_table'


def upgrade():
    conn = op.get_bind()
    sql = """
        select id, service_id, reference 
        from notification_history 
        where notification_type = 'letter'
        and notification_status = 'returned-letter'"""
    results = conn.execute(sql)
    returned_letters = results.fetchall()
    references = [x.reference for x in returned_letters]
    insert_or_update_returned_letters(references)


def downgrade():
    pass

## Instruction:
Change the insert to use updated_at as the reported_at date
## Code After:
from alembic import op

revision = '0311_populate_returned_letters'
down_revision = '0310_returned_letters_table'


def upgrade():
    conn = op.get_bind()
    sql = """
        select id, service_id, reference, updated_at
        from notification_history 
        where notification_type = 'letter'
        and notification_status = 'returned-letter'"""
    insert_sql = """
        insert into returned_letters(id, reported_at, service_id, notification_id, created_at, updated_at) 
        values(uuid_in(md5(random()::text)::cstring), '{}', '{}', '{}', now(), null)
    """

    results = conn.execute(sql)
    returned_letters = results.fetchall()
    for x in returned_letters:
        f = insert_sql.format(x.updated_at.date(), x.service_id, x.id)
        conn.execute(f)


def downgrade():
    pass
