import re
import math
import MySQLdb
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine


class CSV2DB():
    def __init__(self, csv_path, password, database, 
                 table, host, user, port, columns):
    
        super(CSV2DB, self).__init__
        self.df = pd.read_csv(csv_path)
        self.columns = columns
        self.database = database
        self.table = table
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = mysql.connector.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            port=self.port,
        )

    def select_column(self):
        if self.columns is not None:
            self.df = self.df[self.columns]

    def rm_multi_newline(self):
        self.df = self.df.replace(r'\n+', '\n', regex=True)

    def norm_column_name(self):
        columns = [column.replace('?', '') for column in self.df.columns]
        columns = [re.sub('-| ', '_', column) for column in columns]
        self.df.columns = columns

    def check_length(self):
        df_length = self.df.astype('str').applymap(lambda x: len(x)).max()
        self.name_length = df_length.iloc[0]

        if max(df_length) > 65535:
            print(f'Error: Length of some fields in the "{df_length.idxmax()}" column is too large.\n\
This might cause you to lose some parts of your data during conversion\n\
Please use other dtypes than TEXT in to_sql command. Ex. MEDIUMTEXT or LONGTEXT')
            exit()

    def create_database(self):
        cursor = self.db.cursor()
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.database}")

    def create_engine(self):
        self.engine = create_engine(
            f"mysql+mysqldb://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}?charset=utf8")

    def to_sql(self):
        self.df.to_sql(self.table, self.engine, if_exists='fail', index=True)

    def add_primary_key(self):
        with self.engine.connect() as con:
            con.execute(f'ALTER TABLE `{self.table}` ADD PRIMARY KEY (`index`);')

    def change_type(self):
        name_length = int(math.ceil(self.name_length / 500.0)) * 500
        name_column = self.df.columns[0]

        with self.engine.connect() as con:
            con.execute(f'ALTER TABLE `{self.table}` MODIFY `{name_column}` VARCHAR({name_length});')