# import the library and its Marker clusterization service
import folium
from models.database import SessionLocal
from folium.plugins import MarkerCluster
from models.models import Base, Members, Payments, Deliveries
import logging
from geopy.geocoders import Nominatim
from sqlalchemy import *
import logging
import webbrowser
import re
from config_loader import load_config

def payment_slip_delivered(member: Members, year: int):
    return db.query(Deliveries.delivery_date)\
             .filter(Deliveries.member_id == member.id, extract('year', Deliveries.delivery_date) == year)\
             .count()

def payment_made(member: Members, year: int):
    return db.query(Payments.payment_year)\
             .filter(Payments.member_id == member.id, extract('year', Payments.payment_year) == year)\
             .count()

def update_missing_locs():
    members = db.query(Members).filter(Members.member_geo_latitude == None).all()

    for member in members:
        address = f'{member.member_street} {member.member_streetnumber}, {member.member_zip} {member.member_city}, Austria'
        location = geolocator.geocode(address)

        if location != None:
            #member.member_coordinates = [location.latitude, location.longitude]
            member.member_geo_latitude = location.latitude
            member.member_geo_longitude = location.longitude
            
            logger.info(f'Member: {member.id} updated with geo-coordinates')
        else:
            logger.info(f'Member: {member.id} address couldn\'t be found')

    db.commit()

def checkEmail(email):
    regex = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b'

    if email is None: return False

    # pass the regular expression
    # and the string into the fullmatch() method
    if(re.fullmatch(regex, email)):
        return True
 
    else:
        return False

cfg = load_config()
year = cfg['year']

logger = logging.getLogger(__name__)

geolocator = Nominatim(user_agent="sust", timeout=10)
#geolocator = Bing(api_key='ApbTfEJ024nQCu6DC7TPIlqAcRMfZT8lPjAyMnVRcmo9rqxde5x-IS4fW-nGu0FI')

db = SessionLocal()
#models.Base.metadata.create_all(bind=engine)

update_missing_locs()

#members = db.query(models.Members).filter(models.Members.id.in_([2,4,5,6])).all()

#TODO try
""" members = Members.query.all()
for member in members:
    for delivery in members.deliveries:
        print(f'{delivery.delivery_date}') """

members = db.query(Members.member_geo_latitude, 
                   Members.member_geo_longitude, 
                   Members.member_firstname, 
                   Members.member_surname, 
                   Members.id,
                   Members.member_email)\
            .filter(Members.member_geo_latitude != None, Members.member_count != 0)\
            .all()

# .join(models.Members.member_deliveries)\
# Create a map object and center it to the avarage coordinates to m
coords = [48.284793, 13.880550]
map = folium.Map(location=coords, zoom_start = 13)

# if the points are too close to each other, cluster them, create a cluster overlay with MarkerCluster, add to m
marker_cluster = MarkerCluster().add_to(map)

# draw the markers and assign popup and hover texts
# add the markers the the cluster layers so that they are automatically clustered
#folium.Marker([lat, lon], popup=str(name)+': '+color+'-'+str(clname), icon=folium.Icon(color='black',icon_color='#FFFF00')).add_to(feature_group)
for member in members:
    if member.member_geo_latitude != None and member.member_geo_latitude != None:
        delivered = payment_slip_delivered(member, year)
        paid = payment_made(member, year)
        
        iconColor = "blue"
        hasMail = checkEmail(member.member_email)
        if not paid:
            iconColor = "red"
        elif delivered:
            iconColor = "green"
        elif hasMail:
            iconColor = "orange"

        # if hasMail and delivered:
        #     iconColor = "darkgreen"

        #delivered = member.member_deliveries
        location = (member.member_geo_latitude, member.member_geo_longitude)
        folium.Marker(location=location,
                      popup=f'{member.member_firstname} {member.member_surname}',
                      tooltip=f'\nZugestellt & bezahlt' if (delivered and paid) else
                               'Nicht zugestellt' if not delivered else
                               'Zugestellt, aber nicht bezahlt' if delivered and not paid else 
                               'Weder zugestellt, noch bezahlt',
                      icon=folium.Icon(color=iconColor))\
        .add_to(marker_cluster)

#Display the map
map.save("map.html")
webbrowser.open("map.html")

#func.count(models.Members.member_deliveries).label("count"))\