import datetime as dt

import sqlalchemy
from sqlalchemy import create_engine, text

USER = ''
PASSWORD = ''

DATE_GTE = dt.date(2010, 4, 7)
DATE_LT = dt.date(2017, 1, 1)

engine = create_engine(
    'mysql://{}:{}@172.16.200.96:3306/a1lite?charset=utf8&autocommit=true'
    .format(USER, PASSWORD),
    echo=False
)

# TODO удалить inboxx

TABLES = [
    ('a1lite.a1payment', 'a1lite_tr_id'),
    ('a1lite.currency_inbox', 'inbox_id'),
    ('a1lite.gate_callback', 'transaction_id'),
    ('a1lite.gate_messages', 'transaction_id'),
    ('a1lite.inbox_beeline_mc', 'external_id'),
    ('a1lite.inbox_obank_mc', 'external_id'),
    ('a1lite.inbox_telecom_mc', 'external_id'),
    ('a1lite.inbox_params', 'inbox_id'),
    ('a1lite.inbox_status_history', 'transaction_id'),
    ('a1lite.income_dupe_protection', 'inbox_id'),
    ('a1lite.transaction2parameter', 'transaction_id'),
    ('a1lite.inbox', 'id'),
]


def iter_days(date_gte, date_lt):
    day = date_gte
    while day < date_lt:
        next_day = day + dt.timedelta(days=1)
        yield day, next_day
        day = next_day


def delete_from_tables(tids):
    for table, key in TABLES:
        sql = text(
            """
            delete from {} where {} in :tids
            """.format(table, key)
        )
        r = engine.execute(sql, tids=tids)
        print (table, 'deleted', r.rowcount)
        # result = engine.execute(sql, tids=tids)
        # for row in result:
        #     print(table, row)


def main():
    sql = text(
        """
        select i.id from a1lite.inbox i
        join mpconfig.mp_partner p on p.id = i.partner_id
        where created BETWEEN :date_gte and :date_lt
        and p.billing_id != 16
        """
    )
    for date_gte, date_lt in iter_days(DATE_GTE, DATE_LT):
        print(date_gte, date_lt, end=' ')
        result = engine.execute(sql, date_gte=date_gte, date_lt=date_lt)
        tids = tuple(inbox_id for inbox_id, in result)
        if not tids:
            print('no tids')
            continue

        print('')

        delete_from_tables(tids)


if __name__ == '__main__':
    main()