"""
This script generates SQL DDL files from CSV files provided by YouTube.

Usage:
> python gen.py report_file.csv

It creates output file `report_file.csv.ddl.sql`
You can
1. use it for queries/create_temp_staging_raw_<report_name>.sql
2. use it create staging_raw table
   Add fields 'mcn_account text, download_date date' in the beginning
   Add field 'filename text' at the end of the table
Sample: https://github.com/theorchard/database/pull/16020
"""
import re
import sys

import pandas as pd


def sqlize_column_name(column_name):
    """Sqlize column name."""
    return re.sub(r'[^\w]+', '_', column_name).lower()


def df_to_ddl(df, table_name='staging_raw_youtube_monthly_'):
    """Convert DataFrame to SQL DDL."""
    df = df.rename(columns=sqlize_column_name)
    column_type = {
        'upc': 'TEXT',
        'day': 'DATE',
    }
    # force revenue columns of type real
    for column in df.columns:
        if 'revenue' in column:
            column_type[column] = 'REAL'

    ddl_create = pd.io.sql.get_schema(
        frame=df,
        name=table_name,
        dtype=column_type,
    )
    # we want case-insensitive names
    # https://docs.snowflake.com/en/sql-reference/identifiers-syntax#double-quoted-identifiers  # noqa: E501
    ddl_create = ddl_create.replace('"', '')
    return ddl_create


def csv_to_ddl(csv_filename: str):
    """Convert CSV to DDL."""
    # disable na_filter to not treat empty as "nan"
    df = pd.read_csv(csv_filename, na_filter=False)
    return df_to_ddl(df)


if __name__ == '__main__':
    csv_filename = sys.argv[1]
    ddl_filename = f'{csv_filename}.ddl.sql'
    ddl_statemens = csv_to_ddl(csv_filename)
    with open(ddl_filename, 'w') as ddl_file:
        ddl_file.write(ddl_statemens)