from flask import Blueprint, request, jsonify, send_file, render_template
import mysql.connector
import os
import io
import tempfile
import pandas as pd

category_bp = Blueprint('category_bp', __name__)  # ✅ Proper name

def get_db_connection():
    return mysql.connector.connect(
        host=os.getenv('MYSQL_HOST'),
        user=os.getenv('MYSQL_USER'),
        password=os.getenv('MYSQL_PASSWORD'),
        database=os.getenv('MYSQL_DB'),
        port=int(os.getenv('MYSQL_PORT', 3306))
    )

# ✅ All routes now reference category_bp
@category_bp.route('/categories', methods=['GET'])
def get_categories():
    conn = get_db_connection()
    cur = conn.cursor(dictionary=True)
    cur.execute("SELECT id, name, parent_id FROM part_categories")
    raw = cur.fetchall()
    cur.close()
    conn.close()

    id_map = {row['id']: row for row in raw}

    def build_path(cat):
        path = [cat['name']]
        while cat['parent_id']:
            parent = id_map.get(cat['parent_id'])
            if not parent:
                break
            path.insert(0, parent['name'])
            cat = parent
        return " > ".join(path)

    for row in raw:
        row['path'] = build_path(row)

    return jsonify(raw)

@category_bp.route('/categories', methods=['POST'])
def create_category():
    data = request.json
    name = data.get('name')
    parent_id = data.get('parent_id')
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO part_categories (name, parent_id) VALUES (%s, %s)",
        (name, parent_id if parent_id else None)
    )
    conn.commit()
    cur.close()
    conn.close()
    return jsonify({'status': 'success'})

@category_bp.route('/categories/<int:id>', methods=['DELETE'])
def delete_category(id):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) FROM parts WHERE category_id = %s", (id,))
    in_use = cur.fetchone()[0]

    force = request.args.get("force", "false").lower() == "true"
    if in_use > 0 and not force:
        cur.close()
        conn.close()
        return jsonify({'status': 'fail', 'message': 'Cannot delete. Category in use.'}), 400

    def delete_recursive(cat_id):
        cur.execute("SELECT id FROM part_categories WHERE parent_id = %s", (cat_id,))
        children = cur.fetchall()
        for child in children:
            delete_recursive(child[0])
        cur.execute("DELETE FROM part_categories WHERE id = %s", (cat_id,))

    delete_recursive(id)
    conn.commit()
    cur.close()
    conn.close()
    return jsonify({'status': 'success'})

@category_bp.route('/category-manager')
def category_manager():
    return render_template("category_manager.html")

@category_bp.route('/categories/export')
def export_categories():
    conn = get_db_connection()
    df = pd.read_sql("SELECT id, name, parent_id FROM part_categories", conn)
    conn.close()

    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    df.to_excel(tmp.name, index=False)
    tmp.seek(0)

    return send_file(tmp.name, as_attachment=True, download_name="categories_export.xlsx")

@category_bp.route('/categories/import', methods=['POST'])
def import_categories():
    if 'file' not in request.files:
        return jsonify({'status': 'fail', 'message': 'No file provided'}), 400

    file = request.files['file']
    try:
        df = pd.read_excel(file)
        conn = get_db_connection()
        cur = conn.cursor()
        for _, row in df.iterrows():
            cur.execute(
                """
                INSERT INTO part_categories (id, name, parent_id)
                VALUES (%s, %s, %s)
                ON DUPLICATE KEY UPDATE name=%s, parent_id=%s
                """,
                (
                    int(row['id']),
                    row['name'],
                    int(row['parent_id']) if not pd.isna(row['parent_id']) else None,
                    row['name'],
                    int(row['parent_id']) if not pd.isna(row['parent_id']) else None
                )
            )
        conn.commit()
        cur.close()
        conn.close()
        return jsonify({'status': 'success'})
    except Exception as e:
        return jsonify({'status': 'fail', 'message': str(e)}), 500