"""add_currency_to_marketing_account

Revision ID: fe1e0b60fbdf
Revises: dbcf68de163d
Create Date: 2021-11-25 10:55:42.399658

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'fe1e0b60fbdf'
down_revision = '14a6b4bce5f4'
branch_labels = None
depends_on = None


def upgrade():
    op.add_column('MarketingAccount', sa.Column('currency', sa.String()))

    op.execute("""
        UPDATE "MarketingAccount"
            SET currency = subquery.currency
        FROM (
            	SELECT ma_dsp_id, currency
	            FROM (
                    VALUES ('google_1073652073', 'BRL'),
                        ('facebook_220678908623791', 'USD'),
                        ('facebook_227960311301140', 'USD'),
                        ('facebook_2437040523002171', 'USD'),
                        ('facebook_249200361', 'USD'),
                        ('facebook_275771585943279', 'USD'),
                        ('facebook_235269697476674', 'GBP'),
                        ('facebook_241279976492311', 'USD'),
                        ('facebook_249205600', 'USD'),
                        ('facebook_296350974500581', 'CAD'),
                        ('facebook_355257005256501', 'EUR'),
                        ('facebook_358226101665710', 'GBP'),
                        ('facebook_403395130429589', 'USD'),
                        ('facebook_1021693261336794', 'USD'),
                        ('facebook_1036986222978256', 'GBP'),
                        ('facebook_192227596', 'EUR'),
                        ('facebook_51266945', 'USD'),
                        ('facebook_573679296442380', 'USD'),
                        ('facebook_580362375784985', 'USD'),
                        ('facebook_590245861755309', 'GBP'),
                        ('facebook_602334566897630', 'USD'),
                        ('facebook_654207068366389', 'BRL'),
                        ('facebook_664670257199248', 'GBP'),
                        ('facebook_768164510232785', 'USD'),
                        ('facebook_811091662558668', 'GBP'),
                        ('facebook_815060438838979', 'USD'),
                        ('facebook_849177398425807', 'GBP'),
                        ('facebook_903972702946276', 'GBP'),
                        ('facebook_1123328121174211', 'GBP'),
                        ('google_2333825793', 'USD'),
                        ('google_2802154940', 'EUR'),
                        ('google_3029121817', 'USD'),
                        ('facebook_100208066783602', 'USD'),
                        ('facebook_104202143076853', 'BRL'),
                        ('facebook_105108209658736', 'USD'),
                        ('facebook_108170885940775', 'USD'),
                        ('facebook_1359978377408397', 'MXN'),
                        ('facebook_1376013932614746', 'USD'),
                        ('facebook_1376098445939200', 'USD'),
                        ('facebook_1377217462493790', 'USD'),
                        ('facebook_1383134378632539', 'USD'),
                        ('facebook_1191597207517156', 'GBP'),
                        ('facebook_1215142621984765', 'GBP'),
                        ('facebook_157619175665308', 'EUR'),
                        ('facebook_183005119794849', 'USD'),
                        ('facebook_206714723574992', 'USD'),
                        ('google_4732633891', 'USD'),
                        ('facebook_210070156974883', 'USD'),
                        ('facebook_10152362231207721', 'ARS'),
                        ('facebook_10152480687163981', 'EUR'),
                        ('facebook_10153208127585452', 'EUR'),
                        ('facebook_10154355637658586', 'USD'),
                        ('facebook_10154355643988586', 'USD'),
                        ('facebook_10154357887068586', 'USD'),
                        ('facebook_10154360480058586', 'USD'),
                        ('facebook_10154633859042935', 'BRL'),
                        ('facebook_10154760858563586', 'USD'),
                        ('facebook_10156463802893586', 'USD'),
                        ('facebook_1387355094731337', 'USD'),
                        ('facebook_1573546445988895', 'GBP'),
                        ('facebook_1578898082220650', 'USD'),
                        ('facebook_1708809069252994', 'BRL'),
                        ('facebook_1887479554680002', 'USD'),
                        ('facebook_1900646776692048', 'GBP'),
                        ('facebook_2121729928142324', 'USD'),
                        ('facebook_2172078946186087', 'USD'),
                        ('facebook_2217763448284763', 'GBP'),
                        ('facebook_2257399607879318', 'BRL'),
                        ('facebook_2351639881791021', 'COP'),
                        ('facebook_229466875778991', 'GBP'),
                        ('google_5176914881', 'EUR'),
                        ('google_6449900252', 'USD'),
                        ('google_8653387137', 'USD'),
                        ('google_9038993320', 'USD'),
                        ('facebook_237818583774340', 'GBP'),
                        ('facebook_2191370510920980', 'GBP'),
                        ('facebook_562408950939930', 'GBP'),
                        ('facebook_398497008575329', 'GBP'),
                        ('facebook_6030556183653461', 'GBP'),
                        ('facebook_146650574303594', 'GBP'),
                        ('facebook_364576228491122', 'GBP'),
                        ('facebook_624204675652136', 'GBP'),
                        ('facebook_190807566454126', 'GBP'),
                        ('facebook_839890386898613', 'GBP'),
                        ('facebook_549121533085963', 'GBP'),
                        ('facebook_372618867818237', 'GBP'),
                        ('facebook_849177005092513', 'GBP'),
                        ('facebook_175471854658448', 'GBP'),
                        ('facebook_1877756775764242', 'GBP'),
                        ('facebook_177624091142320', 'GBP'),
                        ('facebook_344531734119431', 'GBP'),
                        ('google_3098880474', 'USD'),
                        ('google_6483554878', 'USD'),
                        ('google_2413196617', 'MXN'),
                        ('google_9963444288', 'MXN'),
                        ('facebook_1047672435265977', 'EUR')
	            ) AS q (ma_dsp_id, currency)
        ) AS subquery
        WHERE dsp_id("MarketingAccount".source, "MarketingAccount".external_id) = subquery.ma_dsp_id
    """)

    op.execute('''
        UPDATE "MarketingAccount"
            set name = subq.name
        from (
            select "Label".name as name, "MarketingAccount".id as id
            from "MarketingAccount"
            join "Label" on "MarketingAccount".label_id = "Label".id
            where "MarketingAccount".name is NULL
        ) as subq
        where subq.id = "MarketingAccount".id
    ''')


def downgrade():
    op.drop_column('MarketingAccount', 'currency')