import csv
import os

from sql.create_table import CREATE_TABLE_SQL
from sql.select_table_summary import SELECT_TABLE_SUMMARY 

import config


def main():
    """Main function."""
    table_csv = read_table_csv() 
    
    create_outfile = 'data/build_tables.sql'
    select_outfile = 'data/table_summary.sql'
    create_sql_list = []
    select_sql_list = []
    
    for table_name, upc_list in table_csv.items():
        upc_str_list = ',\n'.join(upc_list)
        create_sql = CREATE_TABLE_SQL.format(
            table_name=table_name,
            source_table_name=config.SOURCE_TABLE_NAME,
            upc_list=upc_str_list
        )
        create_sql_list.append(create_sql)
        
        select_sql = SELECT_TABLE_SUMMARY.format(table_name=table_name)
        select_sql_list.append(select_sql)
        
        
    with open(create_outfile, 'w') as out:
        out.writelines(create_sql_list)
        
    select_sql = 'UNION'.join(select_sql_list)
    
    with open(select_outfile, 'w') as out:
        out.writelines(select_sql)
        out.write('ORDER BY table_name asc\n;')

def read_table_csv():
    with open(config.FILE_TO_PROCESS, 'r') as csvfile:
        reader = csv.reader(csvfile, delimiter=',')
        
        table_dict = {}
        upc_list = []
        table_name = None
        for row in reader:
            if not row[0]:
                break
            
            if table_name is None:
               table_name = row[5]
                
            if row[5] != table_name:
                table_dict[table_name] = upc_list
                table_name = row[5]
                upc_list = []
            
            upc_list.append(row[1])
        
        table_dict[table_name] = upc_list
        
    first_key = list(table_dict.keys())[0]
    del(table_dict[first_key])
    return table_dict       
                

    

if __name__ == '__main__':
    main()