"""
Audio Recording Studio — collaborative audio collection tool.
Flask + SQLite backend.  Deploy on HF Spaces (Docker) or any server.
"""

import os
import sqlite3
import uuid
from datetime import datetime

from flask import (
    Flask, render_template, request, jsonify, send_from_directory, send_file
)

app = Flask(__name__)

DATA_DIR = os.environ.get("DATA_DIR", "data")
DB_PATH = os.path.join(DATA_DIR, "recordings.db")
AUDIO_DIR = os.path.join(DATA_DIR, "audio")
os.makedirs(AUDIO_DIR, exist_ok=True)


# ── Database ────────────────────────────────────────────────────

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    with get_db() as db:
        db.execute("""
            CREATE TABLE IF NOT EXISTS recordings (
                id          TEXT PRIMARY KEY,
                user        TEXT NOT NULL,
                name        TEXT NOT NULL,
                filename    TEXT NOT NULL,
                duration    REAL DEFAULT 0,
                created_at  TEXT NOT NULL,
                notes       TEXT DEFAULT ''
            )
        """)


init_db()


# ── Routes ──────────────────────────────────────────────────────

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


@app.route("/api/recordings", methods=["GET"])
def list_recordings():
    with get_db() as db:
        rows = db.execute(
            "SELECT * FROM recordings ORDER BY created_at DESC"
        ).fetchall()
    return jsonify([dict(r) for r in rows])


@app.route("/api/recordings", methods=["POST"])
def create_recording():
    audio = request.files.get("audio")
    if not audio:
        return jsonify({"error": "No audio file"}), 400

    user = request.form.get("user", "Anonymous").strip() or "Anonymous"
    name = request.form.get("name", "").strip()
    duration = float(request.form.get("duration", 0))
    notes = request.form.get("notes", "").strip()

    rec_id = uuid.uuid4().hex[:12]
    ts = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    if not name:
        name = f"Recording {ts}"

    filename = f"{rec_id}.webm"
    audio.save(os.path.join(AUDIO_DIR, filename))

    with get_db() as db:
        db.execute(
            "INSERT INTO recordings (id, user, name, filename, duration, created_at, notes) "
            "VALUES (?, ?, ?, ?, ?, ?, ?)",
            (rec_id, user, name, filename, duration, ts, notes),
        )

    return jsonify({
        "id": rec_id, "user": user, "name": name,
        "filename": filename, "duration": duration,
        "created_at": ts, "notes": notes,
    }), 201


@app.route("/api/recordings/<rec_id>", methods=["PATCH"])
def update_recording(rec_id):
    data = request.get_json(force=True)
    fields, values = [], []
    for col in ("name", "notes", "user"):
        if col in data:
            fields.append(f"{col} = ?")
            values.append(data[col])
    if not fields:
        return jsonify({"error": "Nothing to update"}), 400

    values.append(rec_id)
    with get_db() as db:
        db.execute(f"UPDATE recordings SET {', '.join(fields)} WHERE id = ?", values)
    return jsonify({"ok": True})


@app.route("/api/recordings/<rec_id>", methods=["DELETE"])
def delete_recording(rec_id):
    with get_db() as db:
        row = db.execute("SELECT filename FROM recordings WHERE id = ?", (rec_id,)).fetchone()
        if row:
            path = os.path.join(AUDIO_DIR, row["filename"])
            if os.path.exists(path):
                os.remove(path)
            db.execute("DELETE FROM recordings WHERE id = ?", (rec_id,))
    return jsonify({"ok": True})


@app.route("/audio/<filename>")
def serve_audio(filename):
    return send_from_directory(AUDIO_DIR, filename)


@app.route("/api/export")
def export_excel():
    import pandas as pd

    with get_db() as db:
        rows = db.execute("SELECT * FROM recordings ORDER BY created_at DESC").fetchall()

    data = []
    for r in rows:
        data.append({
            "Name": r["name"],
            "User": r["user"],
            "Timestamp": r["created_at"],
            "Duration (s)": round(r["duration"], 1),
            "Notes": r["notes"],
            "Audio File": r["filename"],
        })

    df = pd.DataFrame(data) if data else pd.DataFrame(
        columns=["Name", "User", "Timestamp", "Duration (s)", "Notes", "Audio File"]
    )
    path = os.path.join(DATA_DIR, "export.xlsx")
    df.to_excel(path, index=False)
    return send_file(path, as_attachment=True, download_name="recordings.xlsx")


if __name__ == "__main__":
    port = int(os.environ.get("PORT", 7860))
    app.run(host="0.0.0.0", port=port, debug=False)