# app.py
from flask import Flask, render_template, jsonify, request
import pytz
import logging
import mysql.connector
from datetime import datetime, timedelta
from dotenv import load_dotenv
import os

# MySQL connection settings
load_dotenv()
DB_CONFIG = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME")
}

TIMEZONE = "America/Los_Angeles"

LOG_FILE = "/var/www/home/dcrapping/app_data.log"
# -----------------------
# Logging setup
# -----------------------
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.FileHandler(LOG_FILE),
        logging.StreamHandler()
    ]
)

app = Flask(__name__)

@app.route("/")
def index():
    return render_template("index.html")

@app.route("/fetch-db-data")
def get_data():
    all_slots = [f"{h:02d}:00" for h in range(0, 24, 2)]
    tz = pytz.timezone(TIMEZONE)
    today = datetime.now(tz).date()

    filter_val = request.args.get("filter", "today")
    custom_date = request.args.get("date")

    date_condition = ""
    params = []

    if filter_val == "today":
        date_condition = "DATE(scraped_at) = %s"
        params.append(today.strftime("%Y-%m-%d"))

    elif filter_val == "yesterday":
        yesterday = today - timedelta(days=1)
        date_condition = "DATE(scraped_at) = %s"
        params.append(yesterday.strftime("%Y-%m-%d"))

    elif filter_val == "last7days":
        start_date = today - timedelta(days=7)
        end_date = today - timedelta(days=1)
        date_condition = "DATE(scraped_at) BETWEEN %s AND %s"
        params.extend([start_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d")])
        logging.info(f"Filtering for LAST 7 DAYS (excluding today): From {params[0]} To {params[1]}")

    elif filter_val == "custom" and custom_date:
        date_condition = "DATE(scraped_at) = %s"
        params.append(custom_date)

    else:
        return jsonify({"error": "Invalid filter"}), 400

    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        query = f"""
            SELECT slot, SUM(available) AS available, SUM(busy) AS busy
            FROM psychic_stats
            WHERE {date_condition}
            GROUP BY slot
            ORDER BY slot
        """
        cursor.execute(query, tuple(params))
        rows = cursor.fetchall()
        conn.close()

        data_dict = {row[0]: (row[1], row[2]) for row in rows}

        available, busy = [], []
        for slot in all_slots:
            if slot in data_dict:
                available.append(data_dict[slot][0])
                busy.append(data_dict[slot][1])
            else:
                available.append(0)
                busy.append(0)

        #  Check if no data (all zeros)
        if all(v == 0 for v in available + busy):
            return jsonify({"slots": [], "available": [], "busy": [], "message": "No data found for this date"})

        return jsonify({"slots": all_slots, "available": available, "busy": busy})

    except mysql.connector.Error as e:
        logging.error(f"DB Error: {e}")
        return jsonify({"error": "Database error occurred"}), 500


if __name__ == "__main__":
    app.run(debug=True, host="127.0.0.1", port=5000)