from flask import request, jsonify, render_template, request,flash,redirect,url_for,session, get_flashed_messages
import mysql.connector
from werkzeug.security import generate_password_hash,check_password_hash
from app.models.model import User,db,DBData,data,data_error
from sqlalchemy import func
def login():
    if session_check():
        return redirect(url_for('auth_blueprint.home_page'))
    return render_template('login.html')
    
def profile():
    name=session.get('username')
    email=session.get('email')
    role=session.get('role')
    return render_template('profile.html',name=name,email=email,role=role)

def login_user():
    form_email=request.form.get('email')
    form_password=request.form.get('password')
    print(form_email,'s')
    user=User.query.filter_by(email=form_email).first()
    print(user)
    if user is not None:
        password=user.password
        print(password)
        print(form_password)
        if check_password_hash(password, form_password):
            print(check_password_hash)
            e=session['username'] = user.username
            session['email'] = user.email
            session['password'] = user.password
            session['id'] = user.id
            session['role'] = user.role
            # print(e)
            session.permanent=True
            return redirect(url_for('auth_blueprint.dashboard'))
    return redirect(url_for('auth_blueprint.login'))
    


def home_page():
    if session_check():
        return render_template('dashboard.html')
    return redirect(url_for('auth_blueprint.login'))

def dashboard():
    if session_check():
        return render_template('dashboard.html')
    return redirect(url_for('auth_blueprint.login'))

def logout():
    session.clear()
    print(session)
    return redirect(url_for('auth_blueprint.login'))

def users_display():
    page = request.args.get('page', 1, type=int)
    users_per_page = 10  # Number of users to display per page
    users = User.query.paginate(page=page, per_page=users_per_page)
    # Assuming you are using Flask-Login to manage the user session
    session_id = session.get('id')
    
    # Filter out the user with the ID equal to the session ID
    if session_id:
        users.items = [user for user in users.items if user.id != session_id and user.role != "superadmin"]
        
    print('users',users)
    return render_template('user_display.html', users=users)

def data_display():
    page = request.args.get('page', 1, type=int)  # Get the page number from the request query parameters
    
    per_page = 10  # Number of items to display per page
    data = DBData.query.paginate(page, per_page, error_out=False)  # Paginate the query results
    
    return render_template('data_display.html', data=data)

def file_display():
    role=session['role']
    print(role)
    if role=='checker':
        print('i am in')
        datas = data.query.all()
        data_dicts = [data.__dict__ for data in datas]
        print(data_dicts)
        return render_template('file_display.html', data_dicts=data_dicts)

    else:
        user=session['id']
        datas = data.query.filter_by(uploaded_by=user).all()
        data_dicts = [data.__dict__ for data in datas]
        print(data_dicts)
        return render_template('file_display.html', data_dicts=data_dicts)

def history():
    user = session['id']
    item_id = request.form.get('item_id')
    # Database connection configuration
    config = {
        'user': 'root',
        'password': '',
        'host': '127.0.0.1',
        'port': 3306,
        'database': 'data_uploader',
        'raise_on_warnings': True,
    }
    
    tablename = 'update_history'
    
    # Establish a database connection
    connection = mysql.connector.connect(**config)
    
    try:
        # Create a cursor to interact with the database
        cursor = connection.cursor(dictionary=True)
        
        # Query to retrieve history data
        query = ("SELECT * FROM {} WHERE file_id = %s AND user_id = %s".format(tablename))
        
        # Execute the query with item_id and user
        cursor.execute(query, (item_id, user))
        
        # Fetch all rows of data
        history_data = cursor.fetchall()
        print(history_data)
        return jsonify(history_data)
        
    except mysql.connector.Error as err:
        print("Error:", err)
        jsonify({"error": str(err)})
        
    finally:
        # Close cursor and connection
        cursor.close()
        connection.close()
    
    



#def display_file():
#
#    user = session['id']
#
#    # Subquery to get the count of occurrences of data.id in data_error.file_id
#    subquery = db.session.query(data_error.file_id, func.count(data_error.id).label('error_count')).group_by(data_error.file_id).subquery()
#    print('subquery:',subquery)
#    # Main query to get the data records and join with the subquery to get the error count
#    data_query = db.session.query(data, subquery.c.error_count).outerjoin(subquery, data.id == subquery.c.file_id).filter(data.created_by == user).all()
#
#    data_dicts = []
#    for data_item, error_count in data_query:
#        data_dict = data_item.__dict__
#        data_dict['error_count'] = error_count
#        data_dicts.append(data_dict)
#
#    print(data_dicts)
#    return render_template('display_file.html', data_dicts=data_dicts)

def session_check():
    return 'id' in session