queries.py

import sqlite3
from collections import defaultdict
from datetime import datetime

DBPATH = './data/speciesid.db'
NAMEDBPATH = './birdnames.db'


def get_common_name(scientific_name):
    conn = sqlite3.connect(NAMEDBPATH)
    cursor = conn.cursor()

    cursor.execute("SELECT common_name FROM birdnames WHERE scientific_name = ?", (scientific_name,))
    result = cursor.fetchone()

    conn.close()

    if result:
        return result[0]
    else:
        print ("No common name for: " + scientific_name, flush=True)
        return "No common name found."


def recent_detections(num_detections):
    conn = sqlite3.connect(DBPATH)
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM detections ORDER BY detection_time DESC LIMIT ?", (num_detections,))
    results = cursor.fetchall()

    conn.close()

    formatted_results = []
    for result in results:
        detection = {
            'id': result[0],
            'detection_time': result[1],
            'detection_index': result[2],
            'score': result[3],
            'display_name': result[4],
            'category_name': result[5],
            'frigate_event': result[6],
            'camera_name': result[7],
            'common_name': get_common_name(result[4])
        }
        formatted_results.append(detection)

    return formatted_results


def get_daily_summary(date):
    date_str = date.strftime('%Y-%m-%d')
    conn = sqlite3.connect(DBPATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = '''  
        SELECT display_name,  
               COUNT(*) AS total_detections,  
               STRFTIME('%H', detection_time) AS hour,  
               COUNT(*) AS hourly_detections  
        FROM (  
            SELECT *  
            FROM detections  
            WHERE DATE(detection_time) = ?  
        ) AS subquery  
        GROUP BY display_name, hour  
        ORDER BY total_detections DESC, display_name, hour  
    '''

    cursor.execute(query, (date_str,))
    rows = cursor.fetchall()

    summary = defaultdict(lambda: {
        'scientific_name': '',
        'common_name': '',
        'total_detections': 0,
        'hourly_detections': [0] * 24
    })

    for row in rows:
        display_name = row['display_name']
        summary[display_name]['scientific_name'] = display_name
        summary[display_name]['common_name'] = get_common_name(display_name)
        summary[display_name]['total_detections'] += row['hourly_detections']
        summary[display_name]['hourly_detections'][int(row['hour'])] = row['hourly_detections']

    conn.close()
    return dict(summary)


def get_records_for_date_hour(date, hour):
    conn = sqlite3.connect(DBPATH)
    conn.row_factory = sqlite3.Row  # Set the row factory to sqlite3.Row
    cursor = conn.cursor()

    # The SQL query to fetch records for the given date and hour, sorted by detection_time
    query = '''    
        SELECT *    
        FROM detections    
        WHERE strftime('%Y-%m-%d', detection_time) = ? AND strftime('%H', detection_time) = ?    
        ORDER BY detection_time    
    '''

    cursor.execute(query, (date, str(hour).zfill(2)))
    records = cursor.fetchall()

    # Append the common name for each record
    result = []
    for record in records:
        common_name = get_common_name(record['display_name'])  # Access the field by name
        record_dict = dict(record)  # Convert the record to a dictionary
        record_dict['common_name'] = common_name  # Add the 'common_name' key to the record dictionary
        result.append(record_dict)

    conn.close()

    return result


def get_records_for_scientific_name_and_date(scientific_name, date):
    conn = sqlite3.connect(DBPATH)
    conn.row_factory = sqlite3.Row  # Set the row factory to sqlite3.Row
    cursor = conn.cursor()

    # The SQL query to fetch records for the given display_name and date, sorted by detection_time
    query = '''    
        SELECT *    
        FROM detections    
        WHERE display_name = ? AND strftime('%Y-%m-%d', detection_time) = ?    
        ORDER BY detection_time    
    '''

    cursor.execute(query, (scientific_name, date))
    records = cursor.fetchall()

    # Append the common name for each record
    result = []
    for record in records:
        common_name = get_common_name(record['display_name'])  # Access the field by name
        record_dict = dict(record)  # Convert the record to a dictionary
        record_dict['common_name'] = common_name  # Add the 'common_name' key to the record dictionary
        result.append(record_dict)

    conn.close()

    return result


def get_earliest_detection_date():
    conn = sqlite3.connect(DBPATH)
    cursor = conn.cursor()
    cursor.execute("SELECT MIN(date(detection_time)) FROM detections")
    earliest_date = cursor.fetchone()[0]
    conn.close()
    if earliest_date:
        return earliest_date
    else:
        return None