from flask import Blueprint, render_template, request, current_app
from jinja2_fragments import render_block
from flask_htmx import HTMX
from extensions import db # Still need db for execute method
from decorators import validate_person, validate_workout

notes_bp = Blueprint('notes', __name__)
htmx = HTMX()

# --- Database Helper Functions (Moved from db.py) ---

def _fetch_workout_notes_for_person(person_id):
    """Fetches and processes workout notes for a person directly."""
    sql_query = """
    SELECT
        p.name AS person_name,
        w.workout_id,
        to_char(w.start_date, 'Mon DD YYYY') AS formatted_start_date,
        w.note,
        t.filter AS tag_filter,
        t.name AS tag_name
    FROM person p
    LEFT JOIN workout w ON p.person_id = w.person_id AND w.note IS NOT NULL AND w.note <> ''
    LEFT JOIN workout_tag wt ON w.workout_id = wt.workout_id
    LEFT JOIN tag t ON wt.tag_id = t.tag_id
    WHERE p.person_id = %s
    ORDER BY w.start_date DESC, w.workout_id, t.name;
    """
    raw_data = db.execute(sql_query, [person_id])

    if not raw_data:
        # Attempt to get person name even if no notes exist
        person_name_result = db.execute("SELECT name FROM person WHERE person_id = %s", [person_id], one=True)
        person_name = person_name_result['name'] if person_name_result else "Unknown"
        return person_name, []

    person_name = raw_data[0]['person_name']
    workout_notes = {}
    for row in raw_data:
        workout_id = row['workout_id']
        if workout_id and row['note']:
            if workout_id not in workout_notes:
                workout_notes[workout_id] = {
                    'workout_id': workout_id,
                    'formatted_start_date': row['formatted_start_date'],
                    'note': row['note'],
                    'tags': []
                }
            if row['tag_name']:
                workout_notes[workout_id]['tags'].append({
                    'tag_filter': row['tag_filter'],
                    'tag_name': row['tag_name'],
                    'person_id': person_id
                })

    return person_name, list(workout_notes.values())

def _fetch_workout_note(person_id, workout_id):
    """Fetches a single workout note directly."""
    # Simplified query just to get the note for a specific workout
    query = "SELECT note FROM workout WHERE person_id = %s AND workout_id = %s"
    result = db.execute(query, [person_id, workout_id], one=True)
    return result['note'] if result else ''

def _update_workout_note_for_person(person_id, workout_id, note):
    """Updates a workout note directly."""
    db.execute('UPDATE workout SET note=%s WHERE person_id=%s AND workout_id=%s',
               [note, person_id, workout_id], commit=True)

# --- Routes ---

@notes_bp.route("/person/<int:person_id>/notes", methods=['GET'])
@validate_person
def get_person_notes(person_id):
    """Displays all workout notes for a given person."""
    person_name, workout_notes = _fetch_workout_notes_for_person(person_id) # Use local helper
    if htmx:
        return render_block(current_app.jinja_env, 'notes.html', 'content', person_id=person_id, person_name=person_name, workout_notes=workout_notes)
    return render_template('notes.html', person_id=person_id, person_name=person_name, workout_notes=workout_notes)

@notes_bp.route("/person/<int:person_id>/workout/<int:workout_id>/note/edit", methods=['GET'])
@validate_workout
def get_workout_note_edit_form(person_id, workout_id):
    """Returns the form to edit a specific workout note."""
    note = _fetch_workout_note(person_id, workout_id) # Use local helper
    return render_template('partials/workout_note.html', person_id=person_id, workout_id=workout_id, note=note, is_edit=True)

@notes_bp.route("/person/<int:person_id>/workout/<int:workout_id>/note", methods=['PUT'])
@validate_workout
def update_workout_note(person_id, workout_id):
    """Updates a specific workout note."""
    note = request.form.get('note')
    _update_workout_note_for_person(person_id, workout_id, note) # Use local helper
    return render_template('partials/workout_note.html', person_id=person_id, workout_id=workout_id, note=note)

@notes_bp.route("/person/<int:person_id>/workout/<int:workout_id>/note", methods=['GET'])
@validate_workout
def get_workout_note(person_id, workout_id):
    """Returns the display partial for a specific workout note."""
    note = _fetch_workout_note(person_id, workout_id) # Use local helper
    return render_template('partials/workout_note.html', person_id=person_id, workout_id=workout_id, note=note)