from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from db.db import get_db_connection
from config.auth import role_required
from datetime import datetime
import json
import mysql.connector
import traceback

notepad_bp = Blueprint('notepad', __name__)


# ===========================
# 📝 ADD NOTE
# POST /notepad/add_note
# ===========================
@notepad_bp.route('/add_note', methods=['POST'])
@jwt_required()
@role_required('admin', 'cashier')
def add_note():
    conn = None
    cursor = None
    try:
        current_user = get_jwt_identity()
        user_id = current_user.get('user_id') if isinstance(current_user, dict) else current_user

        data = request.get_json()

        if not data:
            return jsonify({'message': 'No data provided'}), 400

        title   = data.get('title', '').strip()
        content = data.get('content', '').strip()

        if not title:
            return jsonify({'message': 'Note title is required'}), 400

        if not content:
            return jsonify({'message': 'Note content is required'}), 400

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Make sure table exists
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS notepad (
                note_id     INT AUTO_INCREMENT PRIMARY KEY,
                user_id     INT          NOT NULL,
                title       VARCHAR(255) NOT NULL,
                content     LONGTEXT     NOT NULL,
                created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
                updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted  TINYINT(1)   DEFAULT 0,
                INDEX idx_user_id (user_id)
            )
        """)
        conn.commit()

        cursor.execute(
            """
            INSERT INTO notepad (user_id, title, content, created_at, updated_at)
            VALUES (%s, %s, %s, %s, %s)
            """,
            (user_id, title, content, datetime.now(), datetime.now())
        )
        conn.commit()

        note_id = cursor.lastrowid

        return jsonify({
            'message': 'Note added successfully',
            'note_id': note_id
        }), 201

    except mysql.connector.Error as db_err:
        if conn:
            conn.rollback()
        print(f"[DB ERROR - add_note]: {db_err}")
        traceback.print_exc()
        return jsonify({'message': 'Database error', 'error': str(db_err)}), 500

    except Exception as e:
        if conn:
            conn.rollback()
        print(f"[ERROR - add_note]: {e}")
        traceback.print_exc()
        return jsonify({'message': 'Internal server error', 'error': str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ===========================
# 📋 VIEW ALL NOTES
# GET /notepad/view_all_notebook
# ===========================
@notepad_bp.route('/view_all_notebook', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_all_notebook():
    conn   = None
    cursor = None
    try:
        current_user = get_jwt_identity()
        user_id = current_user.get('user_id') if isinstance(current_user, dict) else current_user

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Optional search query param  ?search=keyword
        search = request.args.get('search', '').strip()

        if search:
            cursor.execute(
                """
                SELECT
                    note_id,
                    user_id,
                    title,
                    content,
                    created_at,
                    updated_at
                FROM notepad
                WHERE user_id = %s
                  AND is_deleted = 0
                  AND (title LIKE %s OR content LIKE %s)
                ORDER BY updated_at DESC
                """,
                (user_id, f'%{search}%', f'%{search}%')
            )
        else:
            cursor.execute(
                """
                SELECT
                    note_id,
                    user_id,
                    title,
                    content,
                    created_at,
                    updated_at
                FROM notepad
                WHERE user_id = %s
                  AND is_deleted = 0
                ORDER BY updated_at DESC
                """,
                (user_id,)
            )

        notes = cursor.fetchall()

        # Serialize datetime objects
        for note in notes:
            if note.get('created_at'):
                note['created_at'] = note['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if note.get('updated_at'):
                note['updated_at'] = note['updated_at'].strftime('%Y-%m-%d %H:%M:%S')

        return jsonify({
            'message': 'Notes fetched successfully',
            'total':   len(notes),
            'notes':   notes
        }), 200

    except mysql.connector.Error as db_err:
        print(f"[DB ERROR - view_all_notebook]: {db_err}")
        traceback.print_exc()
        return jsonify({'message': 'Database error', 'error': str(db_err)}), 500

    except Exception as e:
        print(f"[ERROR - view_all_notebook]: {e}")
        traceback.print_exc()
        return jsonify({'message': 'Internal server error', 'error': str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ===========================
# 🔍 VIEW SINGLE NOTE
# GET /notepad/view_note/<note_id>
# ===========================
@notepad_bp.route('/view_note/<int:note_id>', methods=['GET'])
@jwt_required()
@role_required('admin', 'cashier')
def view_note(note_id):
    conn   = None
    cursor = None
    try:
        current_user = get_jwt_identity()
        user_id = current_user.get('user_id') if isinstance(current_user, dict) else current_user

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            """
            SELECT
                note_id,
                user_id,
                title,
                content,
                created_at,
                updated_at
            FROM notepad
            WHERE note_id = %s
              AND user_id  = %s
              AND is_deleted = 0
            """,
            (note_id, user_id)
        )

        note = cursor.fetchone()

        if not note:
            return jsonify({'message': 'Note not found'}), 404

        if note.get('created_at'):
            note['created_at'] = note['created_at'].strftime('%Y-%m-%d %H:%M:%S')
        if note.get('updated_at'):
            note['updated_at'] = note['updated_at'].strftime('%Y-%m-%d %H:%M:%S')

        return jsonify({
            'message': 'Note fetched successfully',
            'note':    note
        }), 200

    except mysql.connector.Error as db_err:
        print(f"[DB ERROR - view_note]: {db_err}")
        traceback.print_exc()
        return jsonify({'message': 'Database error', 'error': str(db_err)}), 500

    except Exception as e:
        print(f"[ERROR - view_note]: {e}")
        traceback.print_exc()
        return jsonify({'message': 'Internal server error', 'error': str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ===========================
# ✏️ EDIT NOTE
# PUT /notepad/edit_note/<note_id>
# ===========================
@notepad_bp.route('/edit_note/<int:note_id>', methods=['PUT'])
@jwt_required()
@role_required('admin', 'cashier')
def edit_note(note_id):
    conn   = None
    cursor = None
    try:
        current_user = get_jwt_identity()
        user_id = current_user.get('user_id') if isinstance(current_user, dict) else current_user

        data = request.get_json()

        if not data:
            return jsonify({'message': 'No data provided'}), 400

        title   = data.get('title', '').strip()
        content = data.get('content', '').strip()

        if not title:
            return jsonify({'message': 'Note title is required'}), 400

        if not content:
            return jsonify({'message': 'Note content is required'}), 400

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Check note exists and belongs to this user
        cursor.execute(
            """
            SELECT note_id FROM notepad
            WHERE note_id = %s AND user_id = %s AND is_deleted = 0
            """,
            (note_id, user_id)
        )
        existing = cursor.fetchone()

        if not existing:
            return jsonify({'message': 'Note not found or access denied'}), 404

        cursor.execute(
            """
            UPDATE notepad
            SET title      = %s,
                content    = %s,
                updated_at = %s
            WHERE note_id  = %s
              AND user_id  = %s
            """,
            (title, content, datetime.now(), note_id, user_id)
        )
        conn.commit()

        return jsonify({
            'message': 'Note updated successfully',
            'note_id': note_id
        }), 200

    except mysql.connector.Error as db_err:
        if conn:
            conn.rollback()
        print(f"[DB ERROR - edit_note]: {db_err}")
        traceback.print_exc()
        return jsonify({'message': 'Database error', 'error': str(db_err)}), 500

    except Exception as e:
        if conn:
            conn.rollback()
        print(f"[ERROR - edit_note]: {e}")
        traceback.print_exc()
        return jsonify({'message': 'Internal server error', 'error': str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ===========================
# 🗑️ DELETE NOTE (Soft Delete)
# DELETE /notepad/delete_note/<note_id>
# ===========================
@notepad_bp.route('/delete_note/<int:note_id>', methods=['DELETE'])
@jwt_required()
@role_required('admin', 'cashier')
def delete_note(note_id):
    conn   = None
    cursor = None
    try:
        current_user = get_jwt_identity()
        user_id = current_user.get('user_id') if isinstance(current_user, dict) else current_user

        conn   = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Check note exists and belongs to this user
        cursor.execute(
            """
            SELECT note_id FROM notepad
            WHERE note_id = %s AND user_id = %s AND is_deleted = 0
            """,
            (note_id, user_id)
        )
        existing = cursor.fetchone()

        if not existing:
            return jsonify({'message': 'Note not found or access denied'}), 404

        # Soft delete
        cursor.execute(
            """
            UPDATE notepad
            SET is_deleted = 1,
                updated_at = %s
            WHERE note_id = %s
              AND user_id  = %s
            """,
            (datetime.now(), note_id, user_id)
        )
        conn.commit()

        return jsonify({
            'message': 'Note deleted successfully',
            'note_id': note_id
        }), 200

    except mysql.connector.Error as db_err:
        if conn:
            conn.rollback()
        print(f"[DB ERROR - delete_note]: {db_err}")
        traceback.print_exc()
        return jsonify({'message': 'Database error', 'error': str(db_err)}), 500

    except Exception as e:
        if conn:
            conn.rollback()
        print(f"[ERROR - delete_note]: {e}")
        traceback.print_exc()
        return jsonify({'message': 'Internal server error', 'error': str(e)}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()