from flask import request, jsonify, render_template, request,flash,redirect,url_for,session, send_file
import io
import hashlib
from sqlalchemy import create_engine, MetaData, Table,insert, Column,Integer, Float, DateTime, Boolean,String,inspect,select
from app.models.model import User,DBData,db,data,data_error
from werkzeug.security import generate_password_hash
from werkzeug.utils import secure_filename
import logging
import os
import pandas as pd
from datetime import datetime
import numpy as np
import json
from sqlalchemy.exc import NoSuchTableError,IntegrityError
from pandas.api.types import infer_dtype
import re
from dateutil.parser import parse
import mysql.connector

def register():
    return render_template('register.html')

def add_user():
    return render_template('create_user.html')

def new_create():
    username = request.form.get('username')
    email = request.form.get('email')
    password = request.form.get('password')
    hashed_password = generate_password_hash(password)
    created_by = session.get('id')
    add_users(username, email, hashed_password, created_by)
    flash('User created successfully')
    return redirect(url_for('auth_blueprint.login'))

def create():
    username = request.form.get('username')
    role = request.form.get('role').lower() 
    email = request.form.get('email')
    password = request.form.get('password')
    hashed_password = generate_password_hash(password)
    created_by = session.get('id')
    add_users(username, role, email, hashed_password, created_by)
    flash('User added successfully')
    return redirect(url_for('auth_blueprint.users_display'))

def add_users(username, role, email, hashed_password, created_by):
    user = User(username=username, role=role, email=email, password=hashed_password, created_by=created_by)
    try:
        db.session.add(user)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        

#def add_db():
#    return render_template('add_db.html')
#
#def create_db():
#    connection_name=request.form.get('connection_name')
#    hostname=request.form.get('hostname')
#    port=request.form.get('port')
#    username=request.form.get('username')
#    schemaname=request.form.get('schemaname')
#    password=request.form.get('password')
#    tablename=request.form.get('tablename')
#    created_by=session['id']
#    status=1
#    print('dddddddddddd')
#    return insert_db(connection_name,hostname,port,username,password,schemaname,tablename,created_by,status)

#def insert_db(connection_name,hostname,port,username,schemaname,password,tablename,created_by,status):
#    print()
#    Db=DBData(connection_name=connection_name,hostname=hostname,port=port,username=username,schemaname=schemaname,password=password,tablename=tablename,created_by=created_by,status=status)
#    db.session.add(Db)
#    db.session.commit()
#    flash(' Data Source added successfully')
#    return redirect(url_for('auth_blueprint.data_display'))


def edit_user(user_id):
   # Handle GET request to display the edit page
    if request.method == 'GET':
        user = User.query.get(user_id)
        return render_template('edit_user.html', user=user)

    # Handle POST request to update the user data
    elif request.method == 'POST':
        user = User.query.get(user_id)
        if user is None:
            return "User not found!"

        username = request.form.get('username')
        email = request.form.get('email')

        user.username = username
        user.email = email
        db.session.commit()
        flash('User edited successfully')
        return redirect(url_for('auth_blueprint.users_display'))

def edit_db(user_id):
   # Handle GET request to display the edit page
    if request.method == 'GET':
        db_data = DBData.query.get(user_id)
        return render_template('edit_db.html', db_data=db_data)

    # Handle POST request to update the user data
    elif request.method == 'POST':
        db_data = DBData.query.get(user_id)
        if db_data is None:
            return "User not found!"

        connection_name=request.form.get('connection_name')
        hostname=request.form.get('hostname')
        port=request.form.get('port')
        username=request.form.get('username')
        password=request.form.get('password')
        schemaname=request.form.get('schemaname')
        tablename=request.form.get('tablename')

        db_data.connection_name = connection_name
        db_data.hostname = hostname
        db_data.port = port
        db_data.username = username
        db_data.password = password
        db_data.schemaname = schemaname
        db_data.tablename = tablename

        db.session.commit()
        flash(' Data Source edited successfully')
        return redirect(url_for('auth_blueprint.data_display'))
    
def upload_file():
    connection_name = DBData.query.with_entities(DBData.connection_name).all()
    print(connection_name)
    return render_template('upload.html',connection_name=connection_name)

def save_file():
    file = request.files['excelFile']
    uploaded_by = session['id']
    original_file = file.filename
    file.save(original_file)

    # Create a new data object and associate it with the user
    uploaded_file = data(file_path=original_file, uploaded_by=uploaded_by)
    db.session.add(uploaded_file)
    db.session.commit()
    uploaded_file_id = uploaded_file.id

    # MySQL database connection configuration
    config = {
        'user': 'root',
        'password': '',
        'host': '127.0.0.1',
        'port': 3306,
        'database': 'data_uploader',
        'raise_on_warnings': True,
    }

    # Connect to the MySQL database
    connection = mysql.connector.connect(**config)
    cursor = connection.cursor()

    # Read the Excel file into a DataFrame
    path = original_file
    print('file_path:', path)
    df = pd.read_excel(path)

    # Add additional columns to the DataFrame
    df['added_by'] = uploaded_by
    df['file_id'] = uploaded_file_id

    df.columns = df.columns.str.strip()

    # Create a logger to log the errors
    logging.basicConfig(filename='data_insertion_errors.log', level=logging.ERROR)

    # Loop through the rows and insert data into the MySQL table
    for _, row in df.iterrows():
        # Prepare a list to hold values of existing columns in the DataFrame
        values = []
        
        # List of columns you want to extract data from
        columns_to_extract = [
            'Airline_Code',
            'Airline',
            'Airline_PNR',
            'First_Name',
            'Last_Name',
            'E-mail',
            'Origin',
            'Destination',
            'Amount',
            'Refund_Amount',
            'Refund_Status',
            'Booking_Status',
            'Fare_Analyst',
            'Fare_amt',
            'all_Taxes',
        ]
        
        for col in columns_to_extract:
            if col in row.index:
                # If the column exists, append its value to the 'values' list
                value = row[col]
                # Convert NaN values to None
                value = None if pd.isna(value) else value
                values.append(value)
            else:
                # If the column does not exist, append None to the 'values' list
                values.append(None)
        
        # Append 'uploaded_by' and 'uploaded_file_id' to the 'values' list
        values.extend([uploaded_by, uploaded_file_id])
        # Prepare the SQL query for insertion
        sql = f"INSERT INTO ticket_data (Airline_Code,Airline,Airline_PNR, First_Name, Last_Name, `E-mail`, Origin, Destination, Amount, Refund_Amount, Refund_Status, Booking_Status, Fare_Analyst, Fare_amt, all_Taxes, added_by, file_id) VALUES (%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

        print(sql,values)
        try:
            cursor.execute(sql, values)
        except IntegrityError as e:
            # Check if the error code corresponds to a duplicate entry
            if e.orig.args[0] == 1062:  # Error code for duplicate entry
                # Log only specific information when duplicate entry occurs
                logging.error(f"Duplicate Entry: Airline_PNR={values[0]}, First_Name={values[1]}, Last_Name={values[2]}")
            
            else:
                # Log other IntegrityError with full details
                logging.error(f"Error: {e}. Data: {values}")
        except Exception as e:
            # Log other exceptions with full details
            logging.error(f"Error: {e}. Data: {values}")

    # Commit the changes and close the connection
    connection.commit()
    cursor.close()
    connection.close()

    flash("Data inserted successfully.")
    return redirect(url_for('auth_blueprint.file_display'))


#ef edit_excel(item_id):
#   row_count=request.args.get('row_count')
#   print(row_count)
#   file = data.query.get(item_id)
#   file_id=file.id
#   file_path=file.file_path
#   data_source=file.data_source
#   session['file_path'] = file_path
#   file_data = pd.read_excel(file_path)
#   
#   # Replace NaN values with empty strings
#   file_data.replace(np.nan, '', inplace=True)
#
#   column_data_types = {}
#
#   connection_details = get_connection_details(data_source)
#   print(connection_details)
#   table_name = DBData.query.get(data_source)
#   if connection_details:
#       hostname, port, username, schemaname, password = connection_details
#       connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}"
#       engine = create_engine(connection_string)
#
#       inspector = inspect(engine)
#
#       table_name = table_name.tablename
#       for column in inspector.get_columns(table_name):
#           column_name = column['name']
#           data_type = column['type']
#           python_data_type = map_to_python_data_type(data_type)
#           column_data_types[column_name] = python_data_type
#           print(column_data_types)
#  
#
#   session['column_data_types'] =column_data_types
#   print(column_data_types)
#
#   # Convert datetime columns to MySQL date format
#   for column, data_type in column_data_types.items():
#       if data_type == 'datetime':
#           file_data[column] = file_data[column].apply(convert_to_mysql_datetime)
#
#   # Define the page size
#   if row_count==None:
#       row_count=20
#   else:    
#       row_count =int(row_count) 
#
#   # Get the current page from the request parameters
#   current_page = int(request.args.get('page', 1))
#
#   # Calculate the starting and ending indices for the current page
#   start_index = (current_page - 1) * row_count
#   end_index = start_index + row_count
#
#   # Retrieve the data for the current page
#   page_data = file_data.iloc[start_index:end_index]
#   print(page_data)
#   # Calculate the total number of pages
#   total_rows = len(file_data)
#   num_pages = (total_rows + row_count - 1) // row_count
#   print(num_pages)
#
#   return render_template('edit_file.html',file_id=file_id, page_data=page_data, current_page=current_page, num_pages=num_pages, row_count=row_count, start_index=start_index, end_index=end_index,item_id=item_id,data_source=data_source,column_data_types=column_data_types)

def show_data(item_id):
    row_count=request.args.get('row_count')
    print(row_count)
    
    data_source=1

    connection_details = get_connection_details(data_source)
    print(connection_details)
    table_name = DBData.query.get(data_source)
    table_name = table_name.tablename
    if connection_details:
        hostname, port, username, schemaname, password = connection_details
        connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}"
        engine = create_engine(connection_string)

        # Reflect the table schema into a SQLAlchemy Table object
        metadata = MetaData()
        db_table = Table(table_name, metadata, autoload=True, autoload_with=engine)

       # Load data from the table into a Python array where file_id matches the specified value (item_id)
        connection = engine.connect()
        query = select([db_table]).where(db_table.c.file_id == item_id)
        result_proxy = connection.execute(query)
        data_array = [dict(row) for row in result_proxy]

    # Remove the "added_by" column from the data_array
    for row in data_array:
        row.pop('added_by', None)
        row.pop('file_id',None)

    print(data_array)
    # Define the page size
    if row_count==None:
        row_count=20
    else:    
        row_count =int(row_count) 

    # Get the current page from the request parameters
    current_page = int(request.args.get('page', 1))

    # Calculate the starting and ending indices for the current page
    start_index = (current_page - 1) * row_count
    end_index = start_index + row_count

    # Retrieve the data for the current page
    page_data = data_array[start_index:end_index]
    print(page_data)

    # Calculate the total number of pages
    total_rows = len(data_array)
    num_pages = (total_rows + row_count - 1) // row_count
    print(num_pages)
    return render_template('show_data.html', page_data=page_data, current_page=current_page, num_pages=num_pages, row_count=row_count, start_index=start_index, end_index=end_index,item_id=item_id,data_source=data_source)


#def map_to_python_data_type(db_data_type):
#    # Mapping MySQL data types to Python data types
#    print(db_data_type)
#    data_type_mapping = {
#        'VARCHAR': 'str',
#        'INTEGER': 'int',
#        'FLOAT': 'float',
#        'DATETIME': 'datetime',
#        # Add more mappings for other data types as needed
#    }
#
#    # Try to map the database data type to the corresponding Python data type
#    return data_type_mapping.get(str(db_data_type), 'str')
#
#def convert_to_mysql_datetime(value):
#    try:
#        # Try parsing the value as a datetime using dateutil.parser.parse
#        datetime_value = parse(value)
#        return datetime_value.strftime('%Y-%m-%d %H:%M:%S')
#    except Exception:
#        # If the parsing fails, return the original value (assumed to be already in the desired format)
#        return value
    
#def save_excel():
#    data = request.form.get('data')
#    if data:
#        data = json.loads(data)
#        print('dataaaa=',data)
#        filepath = session.get('file_path')
#        column_data_types=session['column_data_types']
#        success = update_excel(filepath, data)
#        print(success,'success')
#        return "Excel file saved"
#    return "Invalid data"

#def update_excel(filepath, data):
#
#    df = pd.read_excel(filepath, dtype={'column_name': float})
#    maped_data=datatypemapping(data)
#    print('maped_data=',maped_data)
#    for row in maped_data:
#        row_index = row.get('rowIndex')
#        for column_name, edited_value in row.items():
#            if column_name != 'rowIndex':
#                df.at[row_index, column_name] = edited_value
#
#    df.to_excel(filepath, index=False)

#def datatypemapping(data):
#    column_data_types = session.get('column_data_types')
#    print("column_data_types:", column_data_types)
#    
#    # Define a mapping for string representations to Python types or callable functions
#    type_mapping = {
#        'str': str,
#        'int': int,
#        'float': float,
#        'datetime': lambda x: datetime.strptime(x, "%Y-%m-%d %H:%M:%S"),
#    }
#    
#    for item in data:
#        for column, data_type in column_data_types.items():
#            if column in item:
#                print("Converting column:", column)
#                print("Original value:", item[column])
#                
#                # Check if the data_type is valid in the type_mapping
#                if data_type in type_mapping:
#                    try:
#                        # Call the corresponding Python type or callable function to perform data type conversion
#                        item[column] = type_mapping[data_type](item[column])
#                    except ValueError:
#                        
#                        item[column] = None
#                    print("Converted value:", item[column])
#                else:
#                    
#                    item[column] = None
#    
#    return data


def get_connection_details(data_source):
    connection = DBData.query.get(data_source)
    if connection:
        return (
            connection.hostname,
            connection.port,
            connection.username,
            connection.schemaname,
            connection.password,
        )
    else:
        return None

def check_table_exists(connection_id, tablename):
    connection_details = get_connection_details(connection_id)
    print(connection_details)
    if connection_details:
        hostname, port, username, schemaname, password = connection_details
        connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}"
        engine = create_engine(connection_string)
        metadata = MetaData(bind=engine)

        try:
            table = Table(tablename, metadata, autoload=True, autoload_with=engine)
            table_exists = table.exists()
        except NoSuchTableError:
            table_exists = False

        return table_exists

    else:
        return False

def save_data_db():
    item_id = request.form.get('item_id')
    file_id = request.form.get('file_id')
    print('item ID', item_id)
    
    username='root'
    password=''
    hostname='127.0.0.1'
    port='3306'
    schemaname='data_uploader'
    tablename='ticket_detils'

    connection_string = f"mysql+mysqlconnector://{username}:{password}@{hostname}:{port}/{schemaname}"
    engine = create_engine(connection_string)
    metadata = MetaData(bind=engine)

    path = data.query.get(item_id)
    file_path = path.file_path
    print('file_path:', file_path)
    df = pd.read_excel(file_path)

    with engine.begin() as connection:
        print('inside_engin')
        df.to_sql(tablename, connection, if_exists='append', index=False)

    return "Data inserted successfully."

    
def download_excel():
    print('gi')
    config = {
        'user': 'root',
        'password': '',
        'host': '127.0.0.1',
        'port': 3306,
        'database': 'data_uploader',
        'raise_on_warnings': True,
    }

    # Connect to the database
    connection = mysql.connector.connect(**config)
    cursor = connection.cursor()

    # Fetch data from the database (You need to replace 'your_table_name' with the actual table name)
    query = 'SELECT * FROM ticket_data;'
    cursor.execute(query)
    data = cursor.fetchall()

    # Close the database connection
    cursor.close()
    connection.close()

    # Create a DataFrame from the data (assuming you have columns named 'id', 'file_path', and 'created_at')
    df = pd.DataFrame(data, columns=['id','added_by','file_id','Airline_Code', 'Airline', 'Airline_PNR', 'First_Name', 'Last_Name', 'E-mail', 'Origin', 'Destination', 'Amount', 'Refund_Amount', 'Refund_Status', 'Booking_Status', 'Fare_Analyst', 'Fare_amt', 'all_Taxes'])

    # Create an Excel file from the DataFrame
    excel_data = io.BytesIO()
    with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer:
        df.to_excel(writer, sheet_name='Sheet1', index=False)

    excel_data.seek(0)

    # Send the Excel file as a response to the client
    return send_file(
        excel_data,
        attachment_filename='data.xlsx',
        as_attachment=True
    )

# Set up logging configuration
logging.basicConfig(filename='error_log.txt', level=logging.WARNING, format='%(asctime)s - %(levelname)s - %(message)s')

