import math
from datetime import datetime
import requests
from scrapy import Selector
import mysql.connector
from mysql.connector import pooling
import time
import itertools
from config import *

# Database configuration
# dbconfig = {
#     "host": '103.211.218.87',
#     "user": 'root',
#     "password": 'bx9=UE;5I7WU',
#     "database": "UGGGoogleMapsUAT"
#     # "database": "UGGGoogleMapsProd"
# }


dbconfig = {
    "host":DB_HOST,
    "user":DB_USER,
    "password":DB_PASSWORD,
    "database":DB_NAME
}


# Connection pooling setup
pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=30, **dbconfig)

input_table = "input_text"
output_table = "google_data"
google_data_buffer = "google_data_buffer"

# open_connections = []


def get_connection():
    # return pool.get_connection()
    conn = pool.get_connection()
    # open_connections.append(conn)
    return conn


def db_connect(retries=5, delay=2):
    attempt = 0
    while attempt < retries:
        try:
            conn = get_connection()
            return conn
        except mysql.connector.Error as err:
            print(f"Connection attempt {attempt+1} failed: {err}")
            attempt += 1
            time.sleep(delay)
    print("Max retries reached. Could not connect to the database.")
    # close_all_connections()
    # Final retry
    try:
        conn = get_connection()
        return conn
    except mysql.connector.Error as err:
        print(f"Final connection attempt failed: {err}")
        return None


def get_cursor():
    conn = db_connect()
    if conn:
        return conn.cursor(), conn
    else:
        print("Failed to connect to database.")
        return None, None


def insert_data(item, table_name):
    cursor, conn = get_cursor()
    if not conn:
        return
    try:
        columns = ','.join(item.keys())
        placeholders = ', '.join(['%s'] * len(item))
        insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        values = tuple(item.values())
        cursor.execute(insert_query, values)
        conn.commit()

        # Fetch the last inserted ID
        last_id = cursor.lastrowid
        print(f"---------- Data inserted successfully !! ---------------")
        return last_id

    except Exception as er:
        print("Error in Data Insert : ", er)
        return None
    finally:
        cursor.close()
        conn.close()


def update_table(table_name, id, count):
    cursor, conn = get_cursor()
    if not conn:
        return
    try:
        query = f"UPDATE {table_name} SET status = 'Done', count = {count} WHERE id = {id}"
        cursor.execute(query)
        conn.commit()
        print(f"---------- Table updated successfully !! ---------------")
    except Exception as er:
        print("Error in connection : ", er)
    finally:
        cursor.close()
        conn.close()
        

def fetch_data(table_name, id):
    cursor, conn = get_cursor()
    if not conn:
        return None
    try:
        query = f"SELECT id, name, address, driving_distance, mobile_number, latitude, longitude, county_name, input_text FROM {table_name} WHERE id1 = {id}"
        cursor.execute(query)
        rows = cursor.fetchall()
        return rows
    except Exception as er:
        print("Error in fetching data : ", er)
        return None
    finally:
        cursor.close()
        conn.close()
        

def haversine_distance(lat1, lon1, lat2, lon2):
    R = 6371  # Radius of the Earth in kilometers
    lat1, lon1, lat2, lon2 = map(math.radians, [lat1, lon1, lat2, lon2])
    dlat = lat2 - lat1
    dlon = lon2 - lon1
    a = math.sin(dlat / 2) ** 2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon / 2) ** 2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    distance_km = R * c  # Distance in kilometers
    distance_miles = distance_km * 0.621371  # Convert kilometers to miles
    return distance_miles


def get_driving_distance(api_key, origin, destination):
    url = "https://maps.googleapis.com/maps/api/distancematrix/json"
    params = {
        "origins": origin,
        "destinations": destination,
        "mode": "driving",
        "key": api_key
    }
    response = requests.get(url, params=params)
    data = response.json()

    if data["status"] == "OK":
        elements = data["rows"][0]["elements"][0]
        if elements["status"] == "OK":
            distance_meters = elements["distance"]["value"]
            duration = elements["duration"]["text"]
            distance_miles = distance_meters * 0.000621371  # Convert meters to miles
            return distance_miles, duration
        else:
            return None, elements["status"]
    else:
        return None, data["status"]


def save_address_to_db(address_name, latitude, longitude, user_id):
    cursor, conn = get_cursor()
    if not conn:
        return False
    try:
        insert_query = f"INSERT INTO address_lat_long (address, lat, lng, UserId) VALUES (%s, %s, %s, %s)"
        values = (address_name, latitude, longitude, user_id)
        cursor.execute(insert_query, values)
        conn.commit()
        print(f"---------- Address Data Saved successfully !! ---------------")
        return True
    except Exception as er:
        print("Error in Data Insert : ", er)
        return False
    finally:
        cursor.close()
        conn.close()
        

def update_address_in_db(address_id, address_name, latitude, longitude, user_id):
    cursor, conn = get_cursor()
    if not conn:
        return False
    try:
        update_query = f"UPDATE address_lat_long SET address = %s, lat = %s, lng = %s, UserId = %s WHERE id = %s"
        values = (address_name, latitude, longitude, address_id, user_id)
        cursor.execute(update_query, values)
        conn.commit()
        print(f"---------- Address Data Updated successfully !! ---------------")
        return True
    except Exception as er:
        print("Error in Data Insert : ", er)
        return False
    finally:
        cursor.close()
        conn.close()
        

def get_buffer_data_count(user_id):
    cursor, conn = get_cursor()
    if not conn:
        return 0
    try:
        cursor.execute(f"SELECT COUNT(*) FROM google_data_buffer where UserId = '{user_id}'")
        count = cursor.fetchone()[0]
        return count
    except Exception as er:
        print("Error in fetching buffer data count : ", er)
        return 0
    finally:
        cursor.close()
        conn.close()


def check_county(zipcode):
    cursor, conn = get_cursor()
    if not conn:
        return None
    try:
        query = f'select CountyName from countymanual WHERE zipcode = "{zipcode}"'
        cursor.execute(query)
        result = cursor.fetchone()
        if result:
            return result[0]
        else:

            headers = {
                'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
                'accept-language': 'en-US,en;q=0.9',
                'cache-control': 'max-age=0',
                'cookie': '__qca=I0-803126677-1723459017481; _pk_id.29.e1c2=e47ca57c420ad1b0.1722504098.; _lc2_fpi=16307b161960--01j46kxcd0p0y4r564xv0brhtc; _lc2_fpi_meta=%7B%22w%22%3A1722504098208%7D; cookie=37359b70-f17a-459a-aeb1-62a28728d86b; cookie_cst=zix7LPQsHA%3D%3D; _lr_env_src_ats=false; ccuid=ff9050ee-8aa5-4f0f-8a22-8d75c9268fc7; _cc_id=13bf6438d0c66ba46bf2dcc5161800cb; _au_1d=AU1D-0100-001722504099-FRHRDVPM-X1LP; _pk_ref.29.e1c2=%5B%22%22%2C%22%22%2C1723458902%2C%22https%3A%2F%2Funitedstateszipcodes.org%2F%22%5D; _pk_ses.29.e1c2=1; _lr_retry_request=true; ccsid=ea2bdaa9-5a4e-46f9-bf29-e7e7b20e7254; panoramaId_expiry=1723545303193; panoramaId=bb62f90bca56a4cf0c78ce39c395a9fb927a942d87d53a4d6147d0980efd7e46; panoramaIdType=panoDevice; _gid=GA1.2.1318193071.1723458904; __gads=ID=13ab214b61f1a2a8:T=1722504100:RT=1723459520:S=ALNI_Mbcmc-aIwMV6ao4e_0qF7gfvdw7Cw; __gpi=UID=00000eb0dd783411:T=1722504100:RT=1723459520:S=ALNI_MZrNDWReFvRHIWh1JK-oU4b1Qa2Jg; __eoi=ID=e35f44f3cdf1bf0f:T=1722504100:RT=1723459520:S=AA-AfjYXFPFUJOl_XU0b4wXo58fI; _ga_FVWZ0RM4DH=GS1.1.1723459015.10.0.1723459697.60.0.0; connectId=%7B%22vmuid%22%3A%22ZHpOb6SuBLi-PAeIEiIXbmcn1mLqkcra7JCunBeo4_3ZIXGh6crv3QnISA94gN5paKaGbPRmQ0SHRbd9yJU8bw%22%2C%22connectid%22%3A%22ZHpOb6SuBLi-PAeIEiIXbmcn1mLqkcra7JCunBeo4_3ZIXGh6crv3QnISA94gN5paKaGbPRmQ0SHRbd9yJU8bw%22%2C%22connectId%22%3A%22ZHpOb6SuBLi-PAeIEiIXbmcn1mLqkcra7JCunBeo4_3ZIXGh6crv3QnISA94gN5paKaGbPRmQ0SHRbd9yJU8bw%22%2C%22ttl%22%3A86400000%2C%22lastSynced%22%3A1723458902281%2C%22lastUsed%22%3A1723459697895%7D; _ga=GA1.2.947255690.1722504102; cto_bundle=4JMvrF9lekwxV050TjhLNEhZZWJaUndKUWRoYzhaUDhudmFPQXViT1M1cVVNYlRXSXZOUlB2NGlwd1k3NTRySUphR29uWndSRXBHakIwcXFxdlM2JTJGUmhkU3ltQTVnOFFWeVNqNjNIOUFDNm9xbjhDOWNDSmZJNUYlMkJyQklwdUxvQVZ1bURMZXQwMiUyRlJqNThwcDlIcEhCR05wMWolMkZGa3ZpbkJQRG1QYTJDUTZROG94ZyUzRA; datadome=YdD0qyBoaoAnIItN2jqPlLZ3GRIREu~hPAvCe0jcjjWL2bF0ACYwVEZkBztIDeDCeKdJNSac1nwITazCl18z8EIuswZiv05xTjk1oaH8fQIrks6xgSbq2HxbomtvZGUR',
                'priority': 'u=0, i',
                'referer': f'https://www.unitedstateszipcodes.org/{zipcode}/',
                'sec-ch-device-memory': '8',
                'sec-ch-ua': '"Not)A;Brand";v="99", "Google Chrome";v="127", "Chromium";v="127"',
                'sec-ch-ua-arch': '"x86"',
                'sec-ch-ua-full-version-list': '"Not)A;Brand";v="99.0.0.0", "Google Chrome";v="127.0.6533.100", "Chromium";v="127.0.6533.100"',
                'sec-ch-ua-mobile': '?0',
                'sec-ch-ua-model': '""',
                'sec-ch-ua-platform': '"Windows"',
                'sec-fetch-dest': 'document',
                'sec-fetch-mode': 'navigate',
                'sec-fetch-site': 'same-origin',
                'sec-fetch-user': '?1',
                'upgrade-insecure-requests': '1',
                'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36',
            }

            response = requests.get(f'https://www.unitedstateszipcodes.org/{zipcode}/', headers=headers)

            if response.status_code == 200:
                respo = Selector(text=response.text)
                county = respo.xpath(
                    '//table[@class="table table-condensed table-striped"]//th[contains(text(),"County:")]//following-sibling::td//text()').get(
                    default='Not Found')

                # TODO Insert county to table
                if county != 'Not Found':
                    cursor, conn = get_cursor()
                    if not conn:
                        return None
                    ins_query = f'Insert into countymanual (zipcode, CountyName) values("{zipcode}", "{county}")'
                    cursor.execute(ins_query)
                    conn.commit()
                    
                    print("County inserted successfully")
                else:
                    print("County not found")
                    return None
            else:
                print("Error in fetching county : ", response.status_code, " for zipcode : ", zipcode)
                return None

    except Exception as er:
        print("Error in fetching county : ", er)
        return None
    finally:
        cursor.close()
        conn.close()


def get_search_poi(user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        search_qry = f'SELECT search_str FROM search_poi where UserId = "{user_id}"'
        cursor.execute(search_qry)
        search_str = cursor.fetchall()
        cursor.close()
        conn.close()
        
        if search_str:
            list_of_poi = sorted(search_str, key=lambda x: x[0])
            return [item[0] for item in list_of_poi]
        else:
            return []
    except Exception as err:
        print(f"Error fetching points of interest: {err}")
        return []


def get_address_lat_long(user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        add_lat_fetch = f'SELECT id, address, lat, lng FROM address_lat_long where UserId = "{user_id}"'
        cursor.execute(add_lat_fetch)
        add_lat_long = cursor.fetchall()
        cursor.close()
        conn.close()
        
        list_of_addresses = [{'id': id, 'address': address, 'lat': lat, 'lng': lng} for id, address, lat, lng in add_lat_long] if add_lat_long else []
        return sorted(list_of_addresses, key=lambda x: x['address'])
    except Exception as err:
        print(f"Error fetching addresses: {err}")
        return []


def add_poi(poi, user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        in_query = 'INSERT INTO search_poi (search_str, UserId) VALUES (%s, %s)'
        cursor.execute(in_query, (poi,user_id))
        conn.commit()
        cursor.close()
        conn.close()
        
        print("Point of interest added successfully")
        return True
    except Exception as err:
        print(f"Error adding point of interest: {err}")
        return False


def remove_poi(poi, user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        del_query = 'DELETE FROM search_poi WHERE search_str = %s and UserId = %s'
        cursor.execute(del_query, (poi, user_id))
        conn.commit()
        cursor.close()
        conn.close()
        
        print("Point of interest deleted successfully")
        return True
    except Exception as err:
        print(f"Error deleting point of interest: {err}")
        return False


def update_location_in_db(selected_ids,user_data):
    try:
        exit_num = user_data['exit_num']
        highway = user_data['highway']
        user_name = user_data['user_name']

        if not selected_ids:
            print("No IDs provided for location update")
            return False

        conn = db_connect()
        cursor = conn.cursor()
        update_query = f'UPDATE google_data SET exit_num = "{exit_num}", highway = "{highway}", user_name = "{user_name}" WHERE id IN ({",".join(selected_ids)})'
        cursor.execute(update_query)
        conn.commit()
        cursor.close()
        conn.close()
        
        print("Location updated successfully")
        return True
    except Exception as err:
        print(f"Error updating location: {err}")
        return False



def save_data_to_buffer(selected_ids):
    try:
        if selected_ids:
            conn = db_connect()
            cursor = conn.cursor(dictionary=True)
            # Fetch data from google_data table
            format_strings = ','.join(['%s'] * len(selected_ids))
            cursor.execute(f"SELECT * FROM google_data WHERE id IN ({format_strings})", tuple(selected_ids))
            fetched_data = cursor.fetchall()

            if fetched_data:
                # TODO Delete data from google_data_buffer table if already exist for selected ids
                query = "DELETE FROM google_data_buffer WHERE id IN (%s)" % ','.join(['%s'] * len(selected_ids))
                cursor.execute(query, tuple(selected_ids))
                conn.commit()

                for item in fetched_data:
                    # avoid Duplicate Data
                    query = f"SELECT driving_distance FROM google_data_buffer WHERE UserId = '{item['UserId']}' AND latitude = '{item['latitude']}' AND longitude = '{item['longitude']}'"
                    cursor.execute(query)
                    result = cursor.fetchone()

                    if result:
                        if result['driving_distance'] > item['driving_distance']:
                            query = f"Update google_data_buffer SET driving_distance = '{item['driving_distance']}' WHERE UserId = '{item['UserId']}' AND latitude = '{item['latitude']}' AND longitude = '{item['longitude']}'"
                            cursor.execute(query)
                            conn.commit()
                            continue
                        else:
                            continue

                    else:
                        insert_data(item, 'google_data_buffer')

            cursor.close()
            conn.close()

            return True
        else:
            print("No data selected. Please select at least one item.")
            return False
    except Exception as err:
        print(f"Error saving data to buffer: {err}")
        return False

def view_data_from_buffer(user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(f"SELECT * FROM google_data_buffer where UserId = '{user_id}'")
        buffer_data = cursor.fetchall()
        cursor.close()
        conn.close()
        
        return buffer_data
    except Exception as err:
        print(f"Error viewing data from buffer: {err}")
        return None


def view_all_data_from_buffer():
    try:
        conn = db_connect()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(f"SELECT * FROM google_data_buffer")
        buffer_data = cursor.fetchall()
        cursor.close()
        conn.close()

        return buffer_data
    except Exception as err:
        print(f"Error viewing data from buffer: {err}")
        return None


def clear_data_from_buffer(user_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        cursor.execute(f"DELETE FROM google_data_buffer WHERE UserId = '{user_id}'")
        conn.commit()
        cursor.close()
        conn.close()
        
        return True
    except Exception as err:
        print(f"Error clearing data from buffer: {err}")
        return False


def delete_data_from_buffer(data_id):
    try:
        conn = db_connect()
        cursor = conn.cursor()
        delete_query = "DELETE FROM google_data_buffer WHERE id = %s"
        cursor.execute(delete_query, (data_id,))
        conn.commit()
        cursor.close()
        conn.close()
        
        return True
    except Exception as err:
        print(f"Error deleting data from buffer: {err}")
        return False


def delete_selected_data_from_buffer(ids):
    try:
        if ids:
            conn = db_connect()
            cursor = conn.cursor()
            placeholders = ','.join(['%s'] * len(ids))
            delete_query = f"DELETE FROM google_data_buffer WHERE id IN ({placeholders})"
            cursor.execute(delete_query, ids)
            conn.commit()
            cursor.close()
            conn.close()
            
            return True
        else:
            print("No IDs provided for deletion.")
            return False
    except Exception as err:
        print(f"Error deleting selected data from buffer: {err}")
        return False


def authenticate_user(username, password):
    try:
        conn = db_connect()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(f"SELECT * FROM Users WHERE Name = '{username}' AND Password = '{password}'")
        user = cursor.fetchone()
        cursor.close()
        conn.close()
        return user
    except Exception as err:
        print(f"Error authenticating user: {err}")
        return None


def fetch_highways():
    try:
        conn = db_connect()
        cursor = conn.cursor()

        cursor.execute("SELECT DISTINCT highway FROM google_data WHERE highway IS NOT NULL;")
        highways = cursor.fetchall()

        cursor.close()
        conn.close()

        # Format the states for the response
        highways_data = [{'id': i + 1, 'name': state[0]} for i, state in enumerate(highways)]
        return highways_data

    except Exception as err:
        print(f"Error fetching highway: {err}")
        return None


def fetch_exits(highways_tuple):
    try:
        conn = db_connect()
        cursor = conn.cursor()

        # Query to fetch cities based on selected states
        query = "SELECT DISTINCT exit_num FROM google_data WHERE highway IN (%s)" % ','.join(['%s'] * len(highways_tuple))
        cursor.execute(query, highways_tuple)
        highway_exits = cursor.fetchall()
        cursor.close()
        conn.close()

        # Format the cities for the response
        highway_exits_data = [{'id': i + 1, 'name': city[0]} for i, city in enumerate(highway_exits)]

        return highway_exits_data
    except Exception as err:
        print(f"Error fetching exits: {err}")
        return None


def fetch_users():
    try:
        conn = db_connect()
        cursor = conn.cursor()

        cursor.execute("SELECT Name FROM Users")
        users = cursor.fetchall()
        cursor.close()
        conn.close()
        return users
    except Exception as err:
        print(f"Error fetching users: {err}")
        return None


def generate_report(users, highways, exits, start_date, end_date):
    try:
        # Default to 'All' if lists are empty
        if not users:
            users = ['All']
        if not highways:
            highways = ['All']
        if not exits:
            exits = ['All']

        # Initialize connection
        conn = db_connect()
        cursor = conn.cursor()
        values = []

        # Start building the query
        query = "SELECT `name`, `address`, `driving_distance`, `mobile_number`, `latitude`, `longitude`, `county_name`, `user_name`, `highway`, `exit_num`, `input_text`, `created_on` FROM google_data WHERE 1=1"

        if 'All' not in users:
            query += " AND user_name IN (%s)" % ','.join(['%s'] * len(users))
            values.extend(users)  # Flatten the list of users
        if 'All' not in highways:
            query += " AND highway IN (%s)" % ','.join(['%s'] * len(highways))
            values.extend(highways)  # Flatten the list of highways
        if 'All' not in exits:
            query += " AND exit_num IN (%s)" % ','.join(['%s'] * len(exits))
            values.extend(exits)  # Flatten the list of exits

        query += " AND DATE(created_on) BETWEEN %s AND %s"
        values.append(start_date)
        values.append(end_date)

        cursor.execute(query, values)

        report = cursor.fetchall()

        cursor.close()
        conn.close()
        return report
    except Exception as err:
        print(f"Error generating report: {err}")
        return None


def exclude_properties():
    try:
        conn = db_connect()
        cursor = conn.cursor()
        cursor.execute("SELECT PropertyName FROM ExcludeProperties WHERE Status='Active'")
        exclude_properties = [row[0].replace('\xa0', ' ').strip().lower() for row in cursor.fetchall() if row[0]]  # Normalize and clean
    except Exception as err:
        print(f"Error fetching users: {err}")
        exclude_properties = []
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()
    
    return exclude_properties 






