from datetime import date
import pandas as pd
from io import BytesIO
from flask import Flask, request, jsonify, render_template, redirect, url_for, session, send_file
from flask import Response
from werkzeug.wsgi import FileWrapper
import time
import googlemaps
import requests
import os
import sys
from config import *

sys.path.insert(0, os.path.dirname(__file__))

import db_config_master
import io
from functools import wraps
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')


app = Flask(__name__)

app.secret_key = 'abcd'
application = app


def is_logged_in():
    return 'username' in session


def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not is_logged_in():
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        # Authenticate user
        user = db_config_master.authenticate_user(username, password)
        if user:
            session['username'] = username
            session['user_type'] = user['UserType']  # Store user_type in session
            session['user_id'] = user['UserId']  # Store user_type in session
            return redirect(url_for('index'))
        else:
            msg = "Invalid username or password"
            return render_template('login.html', msg=msg)

    return render_template('login.html')


@app.route('/logout')
@login_required
def logout():
    session.pop('username', None)
    session.pop('user_type', None)
    session.pop('user_id', None)
    return redirect(url_for('login'))


@app.route('/', methods=['GET', 'POST'])
def check_api():
    # return jsonify({"success": True, "message": "API Working Successfully"})
    return redirect(url_for('index'))

def miles_to_meters(miles):
    try:
        return miles * 1_609.344
    except Exception as e:
        print("Error converting miles to meters:", e)
        return 0


def get_postal_code(address_components):
    for component in address_components:
        if 'postal_code' in component['types']:
            return component['long_name']
    return None


def data_scrap(response, api_key, input_search, input_lat, input_long, input_miles, business_list):
    exclude_properties = db_config_master.exclude_properties()

    if 'status' in response and response['status'] != 'OK':
        print('Error fetching data - Status:', response['status'])
        return None

    if 'results' not in response:
        print('Error fetching data - No results found')
        return None
    else:
        print('Total results:', len(response['results']))
        for result in response['results']:
            if 'place_id' in result:
                place_id = result['place_id']
                details_url = 'https://maps.googleapis.com/maps/api/place/details/json'
                params = {'place_id': place_id, 'key': api_key}

                response = requests.get(details_url, params=params)
                if response.status_code != 200:
                    print('Error fetching data - Status code:', response.status_code)
                    return None
                data = response.json()

                if 'result' in data:
                    data_items = {}

                    raw_name = data.get('result', {}).get('name', '')
                    clean_name = raw_name.strip().lower().replace('`', "'")  # Normalize name for comparison
                    
                    # Check if the cleaned name exists in exclude_properties
                    if clean_name in exclude_properties:
                        print(f"Skipping excluded property: {raw_name}")
                        continue  # Skip this entry
                    
                    # Process the data further if it's not excluded
                    data_items['name'] = raw_name
                    # data_items['name'] = data.get('result').get('name', '')
                    data_items['address'] = data.get('result').get('formatted_address', '')
                    data_items['website'] = data.get('result').get('website', '')
                    data_items['mobile_number'] = data.get('result').get('formatted_phone_number', '')
                    data_items['url'] = data.get('result').get('url', '')
                    data_items['rating'] = data.get('result').get('rating', '')
                    data_items['user_ratings_total'] = data.get('result').get('user_ratings_total', '')
                    data_items['latitude'] = data.get('result').get('geometry', {}).get('location', {}).get('lat', '')
                    data_items['longitude'] = data.get('result').get('geometry', {}).get('location', {}).get('lng', '')
                    data_items['input_text'] = input_search
                    data_items['input_lat'] = input_lat
                    data_items['input_long'] = input_long
                    data_items['input_miles'] = input_miles

                    postal_code = get_postal_code(data.get('result').get('address_components', []))

                    county = db_config_master.check_county(postal_code)
                    if county:
                        data_items['county_name'] = county
                    else:
                        data_items['county_name'] = ''

                    business_list.append(data_items)
                else:
                    print('Error fetching data - No results found')
                    return None

            else:
                print('Error fetching data - No place_id found')
                return None


def fetch_data(latitude, longitude, miles, input_search):
    business_list = []
    distance = miles_to_meters(int(miles))

    # api_key = open('api_key.txt', 'r').read()
    api_key = GOOGLE_API_KEY
    map_client = googlemaps.Client(api_key)

    response = map_client.places_nearby(location=(latitude, longitude), keyword=input_search, radius=distance)

    data_scrap(response, api_key, input_search, latitude, longitude, miles, business_list)

    next_page_token = response.get('next_page_token')

    while next_page_token:
        time.sleep(2)
        # response = googlemaps.places.places_nearby(client=googlemaps.Client(api_key), location=(latitude, longitude), keyword='EV Charging Station', radius=distance, page_token=next_page_token)
        response = map_client.places_nearby(location=(latitude, longitude), keyword=input_search, radius=distance, page_token=next_page_token)
        data_scrap(response, api_key, input_search, latitude, longitude, miles, business_list)
        next_page_token = response.get('next_page_token')

    return business_list


@app.route('/index', methods=['GET'])
@login_required
def index():
    username = session.get('username')
    user_type = session.get('user_type')
    user_id = session.get('user_id')

    try:
        list_of_poi = db_config_master.get_search_poi(user_id)
        list_of_addresses = db_config_master.get_address_lat_long(user_id)
        buffer_data_count = db_config_master.get_buffer_data_count(user_id)
    except Exception as err:
        print(err)
        list_of_poi = []
        list_of_addresses = []
        buffer_data_count = 0

    # if user_type == 'User':
    return render_template('index.html', list_of_poi=list_of_poi, list_of_addresses=list_of_addresses, buffer_data_count=buffer_data_count, username=username, user_type=user_type)
    # else:
    #     return render_template('reports.html', list_of_poi=list_of_poi, list_of_addresses=list_of_addresses, buffer_data_count=buffer_data_count, username=username)


@app.route('/api/add_poi', methods=['POST'])
@login_required
def add_poi():
    print("Add POI API Calling")
    user_id = session.get('user_id')
    try:
        data = request.get_json()  # Get JSON data from request body
        poi = data.get('poi')

        if db_config_master.add_poi(poi, user_id):
            return jsonify({"success": True, "message": "Point of interest added successfully"}), 200
        else:
            return jsonify({"success": False, "message": "Error adding point of interest"}), 500
    except Exception as err:
        print(err)
        return jsonify({"success": False, "message": "Error adding point of interest"}), 500


@app.route('/api/remove_poi', methods=['POST'])
@login_required
def remove_poi():
    print("Remove POI API Calling")
    user_id = session.get('user_id')
    try:
        data = request.get_json()  # Get JSON data from request body
        poi = data.get('poi')

        if db_config_master.remove_poi(poi, user_id):
            return jsonify({"success": True, "message": "Point of interest deleted successfully"}), 200
        else:
            return jsonify({"success": False, "message": "Error deleting point of interest"}), 500
    except Exception as err:
        print(err)
        return jsonify({"success": False, "message": "Error deleting point of interest"}), 500


@app.route('/save_address', methods=['POST'])
@login_required
def save_address():
    user_id = session.get('user_id')
    try:
        data = request.get_json()
        address_name = data.get('address_name')
        latitude = data.get('latitude')
        longitude = data.get('longitude')

        if not address_name or not latitude or not longitude:
            return jsonify({'error': 'Missing required fields'}), 400

        # Call your function to save the address to the database
        result = db_config_master.save_address_to_db(address_name, latitude, longitude, user_id)

        if result:
            return jsonify({'message': 'Address saved successfully'}), 200
        else:
            return jsonify({'error': 'Failed to save address'}), 500
    except Exception as err:
        return jsonify({'error': f"Error saving address: {err}"}), 500


@app.route('/update_address', methods=['POST'])
@login_required
def update_address():
    user_id = session.get('user_id')
    try:
        data = request.get_json()

        address_name = data.get('address_name')
        latitude = data.get('latitude')
        longitude = data.get('longitude')
        address_id = data.get('id')

        if not address_name or not latitude or not longitude or not address_id:
            return jsonify({'error': 'Missing required fields'}), 400

        # Call your function to update the address in the database
        result = db_config_master.update_address_in_db(address_id, address_name, latitude, longitude, user_id)

        if result:
            return jsonify({'message': 'Address updated successfully'}), 200
        else:
            return jsonify({'error': 'Failed to update address'}), 500
    except Exception as err:
        return jsonify({'error': f"Error updating address: {err}"}), 500


def is_duplicate(result, results):
    return any(result == r for r in results)


@app.route('/populate_data', methods=['POST'])
@login_required
def populate_data():

    user_id = session.get('user_id')
    user_name = session.get('username')

    print("Populate Data API Calling")
    data = request.json
    latitude = data.get('latitude')
    longitude = data.get('longitude')
    input_search = data.get('searchInput')
    from_input = data.get('fromInput')
    to_input = data.get('toInput')

    miles = str(from_input) + ' to ' + str(to_input)

    items = {'input_text': ' and '.join(input_search), 'latitude': latitude, 'longitude': longitude, 'miles': miles, 'UserId': user_id}
    #TODO Input into Database
    input_id = db_config_master.insert_data(items, db_config_master.input_table)

    results = []
    for input_str in input_search:
        if int(from_input) > 0:
            input_miles = int(from_input)
            business_list = fetch_data(latitude, longitude, input_miles, input_str)
            for r in business_list:
                if not is_duplicate(r, results):
                    results.append(r)

        if int(to_input) > 0:
            input_miles = int(to_input)
            business_list = fetch_data(latitude, longitude, input_miles, input_str)
            for r in business_list:
                if not is_duplicate(r, results):
                    results.append(r)

    # api_key = open('api_key.txt', 'r').read()
    api_key = GOOGLE_API_KEY
    if results:
        results = list(results)
        print("Results : ", results)
        for item in results:
            distance_miles, duration = db_config_master.get_driving_distance(api_key, f"{latitude},{longitude}", f"{item['latitude']},{item['longitude']}")
            if not distance_miles <= int(to_input) and distance_miles >= int(from_input):
                continue
            item['id1'] = input_id
            item['distance'] = db_config_master.haversine_distance(float(latitude), float(longitude), item['latitude'], item['longitude'])
            # item['driving_distance'] = distance_miles
            item['driving_distance'] = round(distance_miles, 1)
            item['UserId'] = user_id
            item['user_name'] = user_name
            item['input_miles'] = miles
            db_config_master.insert_data(item, db_config_master.output_table)

        # TODO Update input table
        db_config_master.update_table(db_config_master.input_table, input_id, len(results))
        data = display_data(input_id)
        if data != []:
            return jsonify(data)
        else:
            return jsonify([])
    else:
        db_config_master.update_table(db_config_master.input_table, input_id, len(results))
        return jsonify(results)


def display_data(input_id):
    data = db_config_master.fetch_data(db_config_master.output_table, input_id)
    if data:
        return data
    else:
        return []


@app.route('/save_data', methods=['POST'])
@login_required
def save_data():
    user_id = session.get('user_id')
    data = request.json
    selected_ids = data.get('ids', [])

    user_name = data.get('input_data', {}).get('userName', None)
    exit_num = data.get('input_data', {}).get('exitNumber', None)
    highway = data.get('input_data', {}).get('highwayNumber', None)

    user_data = {
        'user_name': user_name,
        'exit_num': exit_num,
        'highway': highway,
        'UserId': user_id
    }

    #TODO Location update in Database
    if user_data:
        db_config_master.update_location_in_db(selected_ids,user_data)

    if db_config_master.save_data_to_buffer(selected_ids):
        buffer_data_count = db_config_master.get_buffer_data_count(user_id=user_id)

        return jsonify({"message": "Data saved successfully to buffer", "buffer_data_count": buffer_data_count}), 200
    else:
        return jsonify({"message": "No data selected. Please select at least one item."}), 400


@app.route('/view_data', methods=['GET'])
@login_required
def view_data():
    user_id = session.get('user_id')
    username = session.get('username')
    user_type = session.get('user_type')
    buffer_data = db_config_master.view_data_from_buffer(user_id)
    if buffer_data is not None:
        return render_template('view_buffer_data.html', buffer_data=buffer_data, username=username, user_type=user_type), 200
    return jsonify({"message": "Error connecting to the database"}), 500


@app.route('/view_all_data', methods=['GET'])
@login_required
def view_all_data():
    # user_id = session.get('user_id')
    username = session.get('username')
    user_type = session.get('user_type')
    buffer_data = db_config_master.view_all_data_from_buffer()
    if buffer_data is not None:
        return render_template('all_buffer_data.html', buffer_data=buffer_data, username=username, user_type=user_type), 200
    return jsonify({"message": "Error connecting to the database"}), 500


@app.route('/clear_data', methods=['POST'])
@login_required
def clear_data():
    user_id = session.get('user_id')
    if db_config_master.clear_data_from_buffer(user_id):
        return jsonify({"message": "All data cleared from the buffer"}), 200
    return jsonify({"message": "Error connecting to the database"}), 500


@app.route('/delete_data/<int:id>', methods=['DELETE'])
@login_required
def delete_data(id):
    if db_config_master.delete_data_from_buffer(id):
        return jsonify({'message': f"Data with id {id} deleted successfully"})
    return jsonify({'message': f"Data with id {id} not found"}), 404


@app.route('/delete_selected_data', methods=['POST'])
@login_required
def delete_selected_data():
    data = request.get_json()
    ids = data.get('ids', [])

    if db_config_master.delete_selected_data_from_buffer(ids):
        return jsonify({'message': 'Selected data deleted successfully'})
    return jsonify({'message': 'Error deleting selected data'}), 500


@app.route('/admin/reports', methods=['GET'])
@login_required
def admin_reports():
    username = session.get('username')
    user_type = session.get('user_type')
    return render_template('reports.html', username=username, user_type=user_type)



@app.route('/api/report', methods=['POST'])
def report():
    username = session.get('username')
    user_type = session.get('user_type')

    data = request.get_json()

    users = data.get('users', [])
    highways = data.get('highways', [])
    exits = data.get('exits', [])
    start_date = data.get('start_date', '')
    end_date = data.get('end_date', '')
    if end_date == '':
        today = date.today()
        end_date = str(today)

    reports = db_config_master.generate_report(users, highways, exits, start_date, end_date)

    # if reports:
    columns = ['Name', 'Address', 'Distance', 'Mobile', 'Latitude', 'Longitude', 'County', 'User', 'Highway', 'Exit', 'Property Type', 'Working Time']
    df = pd.DataFrame(reports, columns=columns)

    # Add auto-incrementing ID column
    df.insert(0, 'Sr. No.', range(1, len(df) + 1))

    output = BytesIO()
    # with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    #     df.to_excel(writer, index=False)

    with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False, sheet_name='Report', startrow=4)

        workbook = writer.book
        worksheet = writer.sheets['Report']

        # Merge cells for header
        worksheet.merge_range('A2:M3', 'Google Maps Usage Report', workbook.add_format({
            'bold': True,
            'font_size': 20,
            'align': 'center',
            'valign': 'vcenter',
            'border': 2,  # Add thick border
            'border_color': 'black'  # Set border color
        }))

        # Adjust column width for 'Working Time' column
        working_col_width = max(df['Working Time'].astype(str).map(len).max(), len('Working Time'))
        property_col_width = max(df['Property Type'].astype(str).map(len).max(), len('Property Type'))
        worksheet.set_column('M:M', working_col_width)
        worksheet.set_column('L:L', property_col_width)
        worksheet.set_column('H:K', working_col_width)

        user_col_width = max(df['User'].astype(str).map(len).max(), len('User'))
        worksheet.set_column('I:I', user_col_width)

        user_col_width = max(df['Highway'].astype(str).map(len).max(), len('Highway'))
        worksheet.set_column('J:J', user_col_width)



        # Add a blank row
        worksheet.write_row('A4', [''] * (len(columns) + 1), workbook.add_format({'align': 'center'}))
        worksheet.write_row('A1', [''] * (len(columns) + 1), workbook.add_format({'align': 'center'}))

    # Set the file name and return the file
    output.seek(0)
    print(f"Data exported successfully")
    # return send_file(output,
    #              mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    #              as_attachment=True,
    #              download_name='data.xlsx')

    # Create a response object with FileWrapper
    response = Response(FileWrapper(output),
                        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                        direct_passthrough=True)

    # Set headers to provide additional metadata
    response.headers["x-filename"] = 'data.xlsx'
    response.headers["Access-Control-Expose-Headers"] = 'x-filename'
    response.headers["Content-Disposition"] = 'attachment; filename=data.xlsx'

    # Return the response
    return response


@app.route('/api/get_users', methods=['GET'])
def get_users():
    try:
        users = db_config_master.fetch_users()
        users = [user[0] for user in users]
        return jsonify({'users': users})
    except Exception as err:
        print(f"Error fetching states: {err}")
        return jsonify({'highways': []})


@app.route('/api/get_highways', methods=['GET'])
def get_highways():
    try:
        highways = db_config_master.fetch_highways()
        print(highways)
        return jsonify({'highways': highways})
    except Exception as err:
        print(f"Error fetching states: {err}")
        return jsonify({'highways': []})


@app.route('/api/get_exits', methods=['POST'])
def get_exits():
    try:
        highway_selected = request.json.get('state_ids', [])

        highway_names_tuple = tuple(highway_selected)
        highway_exits_data = db_config_master.fetch_exits(highway_names_tuple)

        return jsonify({'exits': highway_exits_data})
    except Exception as err:
        print(f"Error fetching cities: {err}")
        return jsonify({'exits': []})



if __name__ == '__main__':
    app.run(host="0.0.0.0", debug=False, port=int(PORT))