# Generated by Django 3.1.3 on 2020-12-04 12:34

from django.db import migrations
from django.db.migrations import exceptions


def code(apps, schema_editor):
    if schema_editor.connection.vendor == 'sqlite':
        schema_editor.execute("""
            CREATE VIEW sales_result_per_month AS
                SELECT
                    ROW_NUMBER() OVER() AS id,
                    t.sales_month,
                    t.amount AS target_amount,
                    SUM(r.amount) AS total_amount
                FROM
                    sales_target t
                LEFT OUTER JOIN
                    sales_result r
                ON
                    STRFTIME('%Y-%m', t.sales_month) = STRFTIME('%Y-%m', r.sales_date)
                GROUP BY
                    t.sales_month
                ORDER BY
                    sales_month;
        """)
    elif schema_editor.connection.vendor == 'postgresql':
        schema_editor.execute("""
            CREATE VIEW sales_result_per_month AS
                SELECT
                    ROW_NUMBER() OVER() AS id,
                    t.sales_month,
                    t.amount AS target_amount,
                    SUM(r.amount) AS total_amount
                FROM
                    sales_target t
                LEFT OUTER JOIN
                    sales_result r
                ON
                    TO_CHAR(t.sales_month, 'YYYY-MM') = TO_CHAR(r.sales_date, 'YYYY-MM')
                GROUP BY
                    t.sales_month, t.amount
                ORDER BY
                    t.sales_month;
        """)
    elif schema_editor.connection.vendor == 'mysql':
        schema_editor.execute("""
            CREATE VIEW sales_result_per_month AS
                SELECT
                    ROW_NUMBER() OVER() AS id,
                    t.sales_month,
                    t.amount AS target_amount,
                    SUM(r.amount) AS total_amount
                FROM
                    sales_target t
                LEFT OUTER JOIN
                    sales_result r
                ON
                    DATE_FORMAT(t.sales_month, '%Y-%m') = DATE_FORMAT(r.sales_date, '%Y-%m')
                GROUP BY
                    t.sales_month;
                ORDER BY
                    t.sales_month;
            """, params=None)
    else:
        raise exceptions.BadMigrationError(
            'Database vendor should be SQLite, PostgreSQL, or MySQL.')


def reverse_code(apps, schema_editor):
    schema_editor.execute('DROP VIEW IF EXISTS sales_result_per_month;')


class Migration(migrations.Migration):
    dependencies = [
        ('sales', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(code, reverse_code, atomic=False)
    ]
