#!/usr/bin/env python3
"""
Frigate Counter Dashboard
A beautiful, interactive Flask web app for viewing daily batch counting data.
"""
import os
import sqlite3
from datetime import datetime, timedelta

from flask import Flask, render_template, jsonify, request
from werkzeug.serving import WSGIRequestHandler


app = Flask(__name__, template_folder='templates')
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'frigate-counter-dashboard')

DB_PATH = os.getenv('DB_PATH', 'frigate_counter.db')


# ------------------------------------------------------------------ #
# Database helpers
# ------------------------------------------------------------------ #
def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def get_counting_date(dt=None, cutoff_str="08:00"):
    """Replicate the service logic for determining the counting date."""
    if dt is None:
        dt = datetime.now()
    cutoff = datetime.strptime(cutoff_str, "%H:%M").time()
    print(f"DT: {dt.time()}, CO: {cutoff}")
    if dt.time() >= cutoff:
        print(f"Return Current day: {dt.date()}")
        return dt.date().isoformat()
    print(f"Return -1 day: {dt.date() - timedelta(days=1)}")
    return (dt.date() - timedelta(days=1)).isoformat()


# ------------------------------------------------------------------ #
# Routes
# ------------------------------------------------------------------ #
@app.route('/')
def index():
    """Main dashboard page."""
    return render_template('dashboard.html')


@app.route('/api/summary')
def api_summary():
    """Get summary statistics for the dashboard cards."""
    conn = get_db()
    cur = conn.cursor()

    # Today's counting date (based on cutoff)
    today = get_counting_date()

    # Today's stats
    cur.execute("""
        SELECT COALESCE(total_count, 0) as total_count,
               COALESCE(total_batches, 0) as total_batches
        FROM daily_summaries
        WHERE counting_date = ?
    """, (today,))
    today_row = cur.fetchone()

    # Yesterday's stats
    yesterday = (datetime.strptime(today, "%Y-%m-%d").date() - timedelta(days=1)).isoformat()
    cur.execute("""
        SELECT COALESCE(total_count, 0) as total_count,
               COALESCE(total_batches, 0) as total_batches
        FROM daily_summaries
        WHERE counting_date = ?
    """, (yesterday,))
    yesterday_row = cur.fetchone()

    # All-time totals
    cur.execute("""
        SELECT COALESCE(SUM(total_count), 0) as grand_total,
               COALESCE(SUM(total_batches), 0) as grand_batches,
               COUNT(DISTINCT counting_date) as total_days
        FROM daily_summaries
    """)
    all_time = cur.fetchone()

    # Average per day
    cur.execute("""
        SELECT ROUND(AVG(total_count), 1) as avg_per_day
        FROM daily_summaries
    """)
    avg = cur.fetchone()

    # Best day
    cur.execute("""
        SELECT counting_date, total_count
        FROM daily_summaries
        ORDER BY total_count DESC
        LIMIT 1
    """)
    best = cur.fetchone()

    conn.close()

    return jsonify({
        'today': {
            'date': today,
            'total_count': today_row['total_count'] if today_row else 0,
            'total_batches': today_row['total_batches'] if today_row else 0,
        },
        'yesterday': {
            'date': yesterday,
            'total_count': yesterday_row['total_count'] if yesterday_row else 0,
            'total_batches': yesterday_row['total_batches'] if yesterday_row else 0,
        },
        'all_time': {
            'grand_total': all_time['grand_total'],
            'grand_batches': all_time['grand_batches'],
            'total_days': all_time['total_days'],
        },
        'average_per_day': avg['avg_per_day'] or 0,
        'best_day': {
            'date': best['counting_date'] if best else None,
            'count': best['total_count'] if best else 0,
        }
    })


@app.route('/api/daily-data')
def api_daily_data():
    """Get daily data for charts and table."""
    days = request.args.get('days', 30, type=int)
    date_from = (datetime.now() - timedelta(days=days)).date().isoformat()

    conn = get_db()
    cur = conn.cursor()

    # Daily summaries for chart
    cur.execute("""
        SELECT counting_date, total_count, total_batches,
               ROUND(CAST(total_count AS FLOAT) / total_batches, 1) as avg_per_batch
        FROM daily_summaries
        WHERE counting_date >= ?
        ORDER BY counting_date ASC
    """, (date_from,))

    daily_data = []
    for row in cur.fetchall():
        daily_data.append({
            'date': row['counting_date'],
            'total_count': row['total_count'],
            'total_batches': row['total_batches'],
            'avg_per_batch': row['avg_per_batch'] or 0,
        })

    conn.close()
    return jsonify(daily_data)


@app.route('/api/day-detail/<date>')
def api_day_detail(date):
    """Get detailed batch information for a specific day."""
    conn = get_db()
    cur = conn.cursor()

    # Batches for this day
    cur.execute("""
        SELECT batch_number, count, start_time, end_time,
               ROUND(
                   (julianday(end_time) - julianday(start_time)) * 24 * 60, 1
               ) as duration_minutes
        FROM batches
        WHERE counting_date = ?
        ORDER BY batch_number ASC
    """, (date,))

    batches = []
    total_duration = 0
    for row in cur.fetchall():
        duration = row['duration_minutes'] or 0
        total_duration += duration
        batches.append({
            'batch_number': row['batch_number'],
            'count': row['count'],
            'start_time': row['start_time'],
            'end_time': row['end_time'],
            'duration_minutes': duration,
        })

    # Summary for the day
    cur.execute("""
        SELECT total_count, total_batches
        FROM daily_summaries
        WHERE counting_date = ?
    """, (date,))
    summary = cur.fetchone()

    conn.close()

    return jsonify({
        'date': date,
        'total_count': summary['total_count'] if summary else 0,
        'total_batches': summary['total_batches'] if summary else 0,
        'total_duration_minutes': round(total_duration, 1),
        'avg_duration_minutes': round(total_duration / len(batches), 1) if batches else 0,
        'batches': batches,
    })


@app.route('/api/recent-batches')
def api_recent_batches():
    """Get the most recent batches across all days."""
    limit = request.args.get('limit', 10, type=int)

    conn = get_db()
    cur = conn.cursor()

    cur.execute("""
        SELECT counting_date, batch_number, count, start_time, end_time,
               ROUND(
                   (julianday(end_time) - julianday(start_time)) * 24 * 60, 1
               ) as duration_minutes
        FROM batches
        ORDER BY end_time DESC
        LIMIT ?
    """, (limit,))

    batches = []
    for row in cur.fetchall():
        batches.append({
            'date': row['counting_date'],
            'batch_number': row['batch_number'],
            'count': row['count'],
            'start_time': row['start_time'],
            'end_time': row['end_time'],
            'duration_minutes': row['duration_minutes'] or 0,
        })

    conn.close()
    return jsonify(batches)


@app.route('/api/available-dates')
def api_available_dates():
    """Get list of all available counting dates."""
    conn = get_db()
    cur = conn.cursor()

    cur.execute("""
        SELECT counting_date, total_count, total_batches
        FROM daily_summaries
        ORDER BY counting_date DESC
    """)

    dates = []
    for row in cur.fetchall():
        dates.append({
            'date': row['counting_date'],
            'total_count': row['total_count'],
            'total_batches': row['total_batches'],
        })

    conn.close()
    return jsonify(dates)


# ------------------------------------------------------------------ #
# Run
# ------------------------------------------------------------------ #
if __name__ == '__main__':
    # Suppress Flask's default request log spam
    WSGIRequestHandler.protocol_version = "HTTP/1.1"
    
    port = int(os.getenv('DASHBOARD_PORT', 80))
    host = os.getenv('DASHBOARD_HOST', '0.0.0.0')
    debug = os.getenv('FLASK_DEBUG', 'false').lower() == 'true'

    print(f"🚀 Dashboard running at http://{host}:{port}")
    app.run(host=host, port=port, debug=debug)