import pandas as pd
import matplotlib.pyplot as plt
from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
import os
import xgboost as xgb
import numpy as np
from datetime import date
# pip install flask-SQLAlchemy, pkg-config, mysqlclient

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://diederic_elsids:xyai)xk!dT~K@diederich.knight.domains/diederic_GRP6_Movies'

model = xgb.Booster()
model.load_model("model/Apr27_model.model")
#model.load_model("model/Apr27_model.model")

db=SQLAlchemy(app)

# This works
             #query = text("select livingArea from PROPERTIES")
             #result =  db.session.execute(query).fetchall()
             #print(result)
             #print("DB YEA")
             #return render_template("movies.html", theMovies = result)

# Home Page
@app.route('/')
def home():
    print("Home/Filter")

    page = request.args.get("page", 1, type=int)

    filters = {
        "min_price": request.args.get("min_price"),
        "max_price": request.args.get("max_price"),
        "bedrooms": request.args.get("bedrooms"),
        "bathrooms": request.args.get("bathrooms"),
        "city": request.args.get("city"),
        "county": request.args.get("county"),
        "min_sqft": request.args.get("min_sqft"),
        "garage": request.args.get("garage")
    }

    properties = get_properties(filters, page=page)
    
    #properties = get_properties(page=page)
    
    return render_template("index.html",
                           properties=properties,
                           prediction=None,
                           today=date.today().isoformat(),
                           page=page,
                           filters=filters)
             
    #return render_template("index.html")

#def get_properties():
#    query = text("""
#        SELECT
#        SALE.price AS price,
#        PROPERTIES.livingArea AS livingArea
#        FROM PROPERTIES
#        JOIN SALE
#            ON PROPERTIES.zpid = SALE.zpid
#        WHERE SALE.price IS NOT NULL
#        AND PROPERTIES.livingArea IS NOT NULL
#        ORDER BY SALE.price DESC
#        LIMIT 20
#    """)
#    return db.session.execute(query).mappings().all()


def get_properties(filters=None, page=1, per_page=20):
    print("Get_Prop")

    offset = (page - 1) * per_page
    
    sql = """
        SELECT
            SALE.price AS price,
            PROPERTIES.livingArea AS livingArea,
            PROPERTIES.bedrooms,
            PROPERTIES.bathrooms,
            PROPERTIES.city,
            PROPERTIES.yearBuilt
        FROM PROPERTIES
        JOIN SALE
            ON PROPERTIES.zpid = SALE.zpid
        LEFT JOIN FEATURES
            ON PROPERTIES.zpid = FEATURES.zpid
        WHERE 1=1
    """

    params = {}

    if filters:

        if filters.get("min_price"):
            sql += " AND SALE.price >= :min_price"
            params["min_price"] = filters["min_price"]

        if filters.get("max_price"):
            sql += " AND SALE.price <= :max_price"
            params["max_price"] = filters["max_price"]

        if filters.get("bedrooms"):
            sql += " AND PROPERTIES.bedrooms >= :bedrooms"
            params["bedrooms"] = filters["bedrooms"]

        if filters.get("bathrooms"):
            sql += " AND PROPERTIES.bathrooms >= :bathrooms"
            params["bathrooms"] = filters["bathrooms"]

        if filters.get("city"):
            sql += " AND PROPERTIES.city = :city"
            params["city"] = filters["city"]

        if filters.get("county"):
            sql += " AND PROPERTIES.county = :county"
            params["county"] = filters["county"]

        if filters.get("min_sqft"):
            sql += " AND PROPERTIES.livingArea >= :min_sqft"
            params["min_sqft"] = filters["min_sqft"]

        if filters.get("garage"):
            val = filters["garage"]

            val = val == "TRUE"
            
            sql += " AND FEATURES.has_garage = :garage"
            params["garage"] = val

    sql += """ ORDER BY SALE.price ASC
               LIMIT :limit_amt
               OFFSET :offset_amt"""

    params["limit_amt"] = 20
    params["offset_amt"] = offset
    #Limit 50, get a page next thing

    return db.session.execute(text(sql), params
                              ).mappings().all()




# ----------------------------------
# FORM 1: Filter Section
# ---------------------------------
#@app.route('/filter', methods=['POST'])
#def filter():
#    print("Filter")
#
#    page = request.args.get("page", 1, type=int)
#
#    filters = {
#        "min_price": request.form.get("min_price"),
#        "max_price": request.form.get("max_price"),
#        "bedrooms": request.form.get("bedrooms"),
#        "bathrooms": request.form.get("bathrooms"),
#        "city": request.form.get("city"),
#        "county": request.form.get("county"),
#        "min_sqft": request.form.get("min_sqft"),
#        "garage": request.form.get("garage")
#    }
#
#    properties = get_properties(filters, page=page)
#
#    return render_template(
#        "index.html",
#        properties=properties,
#        prediction=None,
#        page=page
#    )

# ----------------------------------
# FORM 2: Price Prediction
# ----------------------------------
@app.route('/predict', methods=['POST'])
def predict():
    print("Predict")
    #print(model.feature_names)
    
    try:

        def get_int(name):
            value = request.form.get(name, '').strip()
            return int(value) if value else None

        def get_float(name):
            value = request.form.get(name, '').strip()
            return float(value) if value else None

        # Get form data
        beds = get_int('beds')
        baths = get_float('baths')
        livingArea = get_int('livingArea')
        year = get_int('year')
        lot = get_int('lotSize')

        walk_score = get_int("walk_score")
        bike_score = get_int("bike_score")
        transit_score = get_int("transit_score")

        city = request.form.get("city")
        county = request.form.get("county")

        has_garage = request.form.get("has_garage")
        has_public_water = request.form.get("has_public_water")
        has_sewer = request.form.get("has_sewer")

        dateSold = request.form.get("dateSold")
        

        # Fill defaults
        if beds is None:
            beds = 3

        if baths is None:
            baths = 2

        if livingArea is None:
            livingArea = 1734

        if year is None:
            year = 1975

        if lot is None:
            lot = 11761

        if walk_score is None:
            walk_score = 25

        if bike_score is None:
            bike_score = 37

        if transit_score is None:
            transit_score = 33

        if has_garage is None:
            has_garage = "TRUE"

        if has_public_water is None:
            has_public_water = "TRUE"

        if has_sewer is None:
            has_sewer = "TRUE"

        if dateSold is None:
            dateSold = today

        #Convert Date
        dateSold = pd.to_datetime(dateSold)
        dateSold = (dateSold - pd.Timestamp("1970-01-01")).days


        # Create model input
        #X = pd.DataFrame([{
        #    "bedrooms": beds,
        #    "bathrooms": baths,
        #    "livingArea": livingArea,
        #    "yearBuilt": year,
        #    "lotSize": lot
        #}])
        #matrix = xgb.DMatrix(X)

        # --------------------------
        # Feature Names
        # --------------------------
        feature_names = model.feature_names


        # Create Zero Vector
        X = np.zeros((1, len(feature_names)))

        # helper lookup
        idx = {name: i for i, name in enumerate(feature_names)}


        # Numeric Variables
        X[0, idx["dateSold"]] = dateSold
        X[0, idx["bedrooms"]] = beds
        X[0, idx["bathrooms"]] = baths
        X[0, idx["livingArea"]] = livingArea
        X[0, idx["lotSize"]] = lot
        X[0, idx["yearBuilt"]] = year
        X[0, idx["walk_score"]] = walk_score
        X[0, idx["bike_score"]] = bike_score
        X[0, idx["transit_score"]] = transit_score


        # City One-Hot
        city_col = "city" + city
        if city_col in idx:
            X[0, idx[city_col]] = 1

        # County One-Hot
        county_col = "county" + county
        if county_col in idx:
            X[0, idx[county_col]] = 1


        # Boolean TRUE columns
        if has_garage == "TRUE":
            X[0, idx["has_garageTRUE"]] = 1

        if has_public_water == "TRUE":
            X[0, idx["has_public_waterTRUE"]] = 1

        if has_sewer == "TRUE":
            X[0, idx["has_sewerTRUE"]] = 1


        # Prediction
        matrix = xgb.DMatrix(
            X,
            feature_names=feature_names)

        # Predict
        predicted_price = float(model.predict(matrix)[0])

        # Load table data
        properties = get_properties(page=1)

        # FIll for filters=filtesr to not break
        filters = {
            "min_price": request.args.get("min_price"),
            "max_price": request.args.get("max_price"),
            "bedrooms": request.args.get("bedrooms"),
            "bathrooms": request.args.get("bathrooms"),
            "city": request.args.get("city"),
            "county": request.args.get("county"),
            "min_sqft": request.args.get("min_sqft"),
            "garage": request.args.get("garage")
        }

        return render_template(
            "index.html",
            properties=properties,
            prediction=round(predicted_price, 0),
            page=1,
            filters=filters
        )

    except Exception as e:
        print("PREDICT ERROR:", e)
        return str(e)

if __name__ == '__main__':
    app.run(debug=True)