import argparse
import csv
import os
import sys

from configparser import ConfigParser
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import Unicode
from sqlalchemy import text
from sqlalchemy.orm import mapper
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship


# Setup commandline args
parser = argparse.ArgumentParser(
    description='Load a CSV file into a db', add_help=False)
parser.add_argument(
    '-?', '--help', action='help', help='Show this help message and exit')
parser.add_argument(
    '-f', '--filename', help='The file to be imported.', type=str)
parser.add_argument(
    '-t', '--table_name', help='The table into which the file will be '
                               'loaded. IF THE TABLE EXISTS, IT WILL BE '
                               'DROPPED.',
    type=str)
parser.add_argument(
    '-h', '--host', help='The host DB into which the file will be loaded',
    type=str)
parser.add_argument(
    '-u', '--user', help='The username with which to connect to the host DB',
    type=str)
parser.add_argument(
    '-p', '--password', help='The password which corresponds to the passed '
                         'username', type=str)
parser.add_argument(
    '-d', '--database', help='The database in which to create the table',
    type=str)
parser.add_argument(
    '-i', '--ini_file', help='A configuration file which holds login '
                             'information', type=str)
parser.add_argument(
    '-o', '--only-one', help='Only ingest one line.', action='store_true')

# Parse commandline args
args = parser.parse_args()

if args.only_one:
    print('Only loading first line.')

# parse ini_file if passed
if args.ini_file:
    config = ConfigParser()
    config.read(args.ini_file)
    args.filename = config.get('config', 'filename')
    args.table_name = config.get('config', 'table_name')
    args.host = config.get('config', 'host')
    args.user = config.get('config', 'user')
    args.password = config.get('config', 'password')
    args.database = config.get('config', 'database')

engine = create_engine(
    'mysql+pymysql://'+args.user+':'+args.password+'@'+args.host+'/'
    +args.database+'?charset=utf8mb4',
    encoding='utf8',
    convert_unicode=True)

table = None
metadata = MetaData(bind=engine)

with open(args.filename, encoding='utf8') as f:
    # assume first line is header
    cf = csv.DictReader(f, delimiter=',')
    for row in cf:
        if table is None:
            # create the table
            table = Table(
                args.table_name,
                metadata,
                Column('id', Integer, primary_key=True),
                *(Column(rowname, String(255))
                      for rowname in row.keys()),
                mysql_DEFAULT_CHARSET='utf8mb4')
            table.drop(checkfirst=True)
            table.create()

        # insert data into the table
        table.insert().values(**row).execute()

        if args.only_one:
            break


class CsvTable(object): pass
mapper(CsvTable, table)
session = create_session(bind=engine, autocommit=False, autoflush=True)

pass

for r in session.query(CsvTable).filter(CsvTable.Status != '32'):
    print(r.Status)

#
# # Create SQLAlchemy tables
# Base = declarative_base()
#
#
# class YoutubeReport(Base, args.table_name, header):
#     __tablename__ = args.table_name
#
#     for item in header:
#
#         # Here we define columns for the table person
#         # Notice that each column is also a normal Python instance attribute.
#         id = Column(Integer, primary_key=True)
#         name = Column(String(250), nullable=False)
#
#
# class Address(Base):
#     __tablename__ = 'address'
#     # Here we define columns for the table address.
#     # Notice that each column is also a normal Python instance attribute.
#     id = Column(Integer, primary_key=True)
#     street_name = Column(String(250))
#     street_number = Column(String(250))
#     post_code = Column(String(250), nullable=False)
#     person_id = Column(Integer, ForeignKey('person.id'))
#     person = relationship(Person)
#
#
# # Create an engine that stores data in the local directory's
# # sqlalchemy_example.db file.
# engine = create_engine('sqlite:///sqlalchemy_example.db')
#
# # Create all tables in the engine. This is equivalent to "Create Table"
# # statements in raw SQL.
# Base.metadata.create_all(engine)
#
#
#
#
#
#
# createDefinition = ""
#
# # Build the header definition
# for item in header:
#     if not_first:
#         createDefinition += ', '
#
#     item = item[:50]+'...' if (item.len() > 50) else item
#
#     not_first = True
#
# # Create table
# query = 'CREATE TABLE IF NOT EXISTS `%s` (`ROW_NUM` MEDIUMINT UNSIGNED, %s  ' \
#         'NOT NULL AUTO_INCREMENT, PRIMARY KEY(`ROW_NUM`) ) ENGINE = MYISAM ' \
#         'AUTO_INCREMENT = 2 CHARACTER SET utf8;'
#
# curs.execute(query, args.table_name, createDefinition)

# # Add Indices
# $query = sprintf('ALTER TABLE `%s`
# 	                          ADD INDEX (`dms_issued_id`),
# 	                          ADD INDEX (`STATUS`),
# 	                          ADD INDEX (`NOTES`),
# 	                          ADD INDEX (`LABEL ID`),
# 	                          ADD INDEX (`GOOD TUID`),
# 	                          ADD INDEX (`GOOD ISRC`),
# 	                          ADD INDEX (`GOOD UPC`),
# 	                          ADD INDEX (`total`);', $table_name);
#
# # Load file
# $input_file = addslashes($file);
#
# 	    $query = sprintf('LOAD DATA LOCAL INFILE '%s'
#                       INTO TABLE `%s`
# 	                      FIELDS TERMINATED BY ''. $delimeter . ''
#                       ESCAPED BY ''
#                       OPTIONALLY ENCLOSED BY '\''
#                       LINES TERMINATED BY '\\n'
# 	                      IGNORE 1 LINES;', dirname($_SERVER['SCRIPT_FILENAME']) . '/' . $input_file, $table_name);
#
#
# # Post install update:
# $query = sprintf('UPDATE `%s`
# 							SET dms_issued_id = 'SKIP'
# 							WHERE (trim(artist_name) = ''
# 									OR artist_name IS NULL)
# 								AND (trim(track_name) = ''
# 									OR track_name IS NULL)
# 								AND (dms_issued_id IS NULL
# 									OR TRIM(dms_issued_id) = '');', $table_name);
#