import os
import json
import base64
import secrets
from datetime import datetime
from decimal import Decimal, InvalidOperation
from io import BytesIO

import mysql.connector
import qrcode
import requests
from dotenv import load_dotenv
from flask import Flask, jsonify, render_template, request, send_from_directory
from mysql.connector import Error
from PIL import Image


load_dotenv()

app = Flask(__name__)
app.config["SECRET_KEY"] = os.getenv("SECRET_KEY") or secrets.token_hex(32)
app.config["QR_FOLDER"] = os.path.join(app.root_path, "static", "qrcodes")
os.makedirs(app.config["QR_FOLDER"], exist_ok=True)


# -------------------------------------------------------------------
# Configuration
# -------------------------------------------------------------------

MPESA_ENV = os.getenv("MPESA_ENV", "sandbox").lower()

if MPESA_ENV == "production":
    MPESA_BASE_URL = "https://api.safaricom.co.ke"
else:
    MPESA_BASE_URL = "https://sandbox.safaricom.co.ke"

MPESA_CONSUMER_KEY = os.getenv("MPESA_CONSUMER_KEY", "")
MPESA_CONSUMER_SECRET = os.getenv("MPESA_CONSUMER_SECRET", "")
MPESA_SHORTCODE = os.getenv("MPESA_SHORTCODE", "174379")
MPESA_PASSKEY = os.getenv("MPESA_PASSKEY", "")
MPESA_CALLBACK_URL = os.getenv("MPESA_CALLBACK_URL", "")
MPESA_MERCHANT_NAME = os.getenv("MPESA_MERCHANT_NAME", "My POS Shop")
MPESA_QR_TRX_CODE = os.getenv("MPESA_QR_TRX_CODE", "BG")


# -------------------------------------------------------------------
# Database helpers
# -------------------------------------------------------------------

def get_db_connection():
    """Open a new MySQL connection using .env settings."""
    return mysql.connector.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "root"),
        password=os.getenv("DB_PASSWORD", ""),
        database=os.getenv("DB_NAME", "mpesa_pos_db"),
        autocommit=False
    )


def fetch_one(query, params=None):
    """Execute a SELECT query and return one dictionary row."""
    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)

    try:
        cursor.execute(query, params or ())
        return cursor.fetchone()
    finally:
        cursor.close()
        connection.close()


def fetch_all(query, params=None):
    """Execute a SELECT query and return all dictionary rows."""
    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)

    try:
        cursor.execute(query, params or ())
        return cursor.fetchall()
    finally:
        cursor.close()
        connection.close()


def create_order(items):
    """
    Create an order and its items.
    Returns the newly inserted order dictionary.
    """
    order_reference = (
        f"POS-{datetime.now().strftime('%Y%m%d-%H%M%S')}-"
        f"{secrets.token_hex(3).upper()}"
    )

    total_amount = sum(
        Decimal(str(item["price"])) * int(item.get("quantity", 1))
        for item in items
    )

    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)

    try:
        cursor.execute(
            """
            INSERT INTO orders (order_reference, total_amount, status)
            VALUES (%s, %s, 'PENDING')
            """,
            (order_reference, total_amount)
        )
        order_id = cursor.lastrowid

        item_sql = """
            INSERT INTO order_items
            (order_id, item_name, quantity, unit_price, line_total)
            VALUES (%s, %s, %s, %s, %s)
        """

        for index, item in enumerate(items, start=1):
            item_name = str(item.get("name") or f"Item {index}").strip()
            quantity = int(item.get("quantity", 1))
            price = Decimal(str(item["price"]))
            line_total = price * quantity

            cursor.execute(
                item_sql,
                (order_id, item_name, quantity, price, line_total)
            )

        connection.commit()

        cursor.execute(
            "SELECT * FROM orders WHERE id = %s",
            (order_id,)
        )
        return cursor.fetchone()

    except Exception:
        connection.rollback()
        raise

    finally:
        cursor.close()
        connection.close()


def update_order_qr(order_id, filename):
    """Save the generated QR file name for the order."""
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute(
            "UPDATE orders SET qr_filename = %s WHERE id = %s",
            (filename, order_id)
        )
        connection.commit()
    finally:
        cursor.close()
        connection.close()


def update_order_stk_ids(order_id, merchant_request_id, checkout_request_id):
    """Store Daraja identifiers after an accepted STK request."""
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute(
            """
            UPDATE orders
            SET merchant_request_id = %s,
                checkout_request_id = %s,
                status = 'PROCESSING'
            WHERE id = %s
            """,
            (merchant_request_id, checkout_request_id, order_id)
        )
        connection.commit()
    finally:
        cursor.close()
        connection.close()


def insert_callback_log(callback_type, checkout_request_id, payload):
    """Store every received callback, even invalid/unknown callbacks."""
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute(
            """
            INSERT INTO mpesa_callback_logs
            (callback_type, checkout_request_id, payload)
            VALUES (%s, %s, %s)
            """,
            (
                callback_type,
                checkout_request_id,
                json.dumps(payload)
            )
        )
        connection.commit()
    finally:
        cursor.close()
        connection.close()


# -------------------------------------------------------------------
# Daraja authentication and API helpers
# -------------------------------------------------------------------

def daraja_is_configured():
    """Check whether live/sandbox Daraja credentials are configured."""
    ignored_values = {
        "",
        "dAWdQA3fNbVtGmLZDxN4z0l0BJYm2ydVPpvAGz1SrhjehGSk",
        "JDGq8HUqPosVbgTuPukyA1nybZgcfyHbuakAAcnYbr4ibGGq22cHNoI8NW9HoZEI"
        
    }

    return (
        MPESA_CONSUMER_KEY not in ignored_values
        and MPESA_CONSUMER_SECRET not in ignored_values
    )


def get_daraja_access_token():
    """Request an OAuth access token from Daraja."""
    if not daraja_is_configured():
        return None

    url = f"{MPESA_BASE_URL}/oauth/v1/generate?grant_type=client_credentials"

    response = requests.get(
        url,
        auth=(MPESA_CONSUMER_KEY, MPESA_CONSUMER_SECRET),
        timeout=30
    )
    response.raise_for_status()

    access_token = response.json().get("access_token")
    if not access_token:
        raise ValueError("Daraja did not return an access token.")

    return access_token


def normalise_phone_number(phone):
    """
    Convert Kenyan numbers to 2547XXXXXXXX.
    Supports 0712345678, 712345678, +254712345678, 254712345678.
    """
    phone = str(phone).strip().replace(" ", "").replace("-", "")

    if phone.startswith("+"):
        phone = phone[1:]

    if phone.startswith("0") and len(phone) == 10:
        phone = "254" + phone[1:]

    elif phone.startswith("7") and len(phone) == 9:
        phone = "254" + phone

    if not (
        phone.startswith("2547")
        and len(phone) == 12
        and phone.isdigit()
    ):
        raise ValueError(
            "Use a valid Kenyan phone number, e.g. 0712345678."
        )

    return phone


def generate_local_qr(order):
    """
    Development-only QR fallback.
    It is an image demonstration, not a real M-PESA payment QR.
    """
    payload = (
        f"SIMULATION|ORDER={order['order_reference']}|"
        f"AMOUNT={order['total_amount']}|"
        f"SHORTCODE={MPESA_SHORTCODE}"
    )

    qr = qrcode.QRCode(
        version=None,
        error_correction=qrcode.constants.ERROR_CORRECT_M,
        box_size=10,
        border=4
    )
    qr.add_data(payload)
    qr.make(fit=True)

    image = qr.make_image(fill_color="black", back_color="white")

    filename = f"sim_{order['order_reference']}.png"
    filepath = os.path.join(app.config["QR_FOLDER"], filename)
    image.save(filepath)

    update_order_qr(order["id"], filename)

    return {
        "success": True,
        "simulation": True,
        "qr_url": f"/static/qrcodes/{filename}",
        "order_reference": order["order_reference"],
        "amount": str(order["total_amount"]),
        "message": (
            "Simulation QR created. It cannot receive real M-PESA payments."
        )
    }


def generate_daraja_qr(order):
    """
    Generate a real Dynamic QR through Daraja.
    The returned image is saved to static/qrcodes.
    """
    if not daraja_is_configured():
        return generate_local_qr(order)

    token = get_daraja_access_token()

    payload = {
        "MerchantName": MPESA_MERCHANT_NAME,
        "RefNo": order["order_reference"],
        "Amount": int(Decimal(str(order["total_amount"]))),
        "TrxCode": MPESA_QR_TRX_CODE,
        "CPI": MPESA_SHORTCODE,
        "Size": "300"
    }

    response = requests.post(
        f"{MPESA_BASE_URL}/mpesa/qrcode/v1/generate",
        json=payload,
        headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json"
        },
        timeout=30
    )

    response_data = response.json()

    if response.status_code != 200 or "QRCode" not in response_data:
        raise ValueError(
            response_data.get(
                "errorMessage",
                response_data.get("ResponseDescription", "QR generation failed.")
            )
        )

    image_data = base64.b64decode(response_data["QRCode"])
    image = Image.open(BytesIO(image_data))

    filename = f"daraja_{order['order_reference']}.png"
    filepath = os.path.join(app.config["QR_FOLDER"], filename)
    image.save(filepath)

    update_order_qr(order["id"], filename)

    return {
        "success": True,
        "simulation": False,
        "qr_url": f"/static/qrcodes/{filename}",
        "order_reference": order["order_reference"],
        "amount": str(order["total_amount"]),
        "message": "Dynamic M-PESA QR code generated."
    }


def initiate_stk_push(order, phone_number):
    """
    Send an STK push. Do not mark an order PAID here.
    The callback endpoint will determine the actual final result.
    """
    if not daraja_is_configured():
        raise ValueError(
            "Daraja credentials are missing. Add them to .env first."
        )

    if not MPESA_PASSKEY or MPESA_PASSKEY.startswith("PASTE_"):
        raise ValueError(
            "MPESA_PASSKEY is missing. Add the Daraja passkey to .env."
        )

    if not MPESA_CALLBACK_URL.startswith("https://"):
        raise ValueError(
            "MPESA_CALLBACK_URL must be a publicly reachable HTTPS URL."
        )

    phone_number = normalise_phone_number(phone_number)
    token = get_daraja_access_token()

    timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
    password_string = f"{MPESA_SHORTCODE}{MPESA_PASSKEY}{timestamp}"
    password = base64.b64encode(password_string.encode()).decode()

    payload = {
        "BusinessShortCode": MPESA_SHORTCODE,
        "Password": password,
        "Timestamp": timestamp,
        "TransactionType": "CustomerPayBillOnline",
        "Amount": int(Decimal(str(order["total_amount"]))),
        "PartyA": phone_number,
        "PartyB": MPESA_SHORTCODE,
        "PhoneNumber": phone_number,
        "CallBackURL": MPESA_CALLBACK_URL,
        "AccountReference": order["order_reference"],
        "TransactionDesc": f"Payment for {order['order_reference']}"
    }

    response = requests.post(
        f"{MPESA_BASE_URL}/mpesa/stkpush/v1/processrequest",
        json=payload,
        headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json"
        },
        timeout=30
    )

    response_data = response.json()

    if response.status_code != 200:
        raise ValueError(
            response_data.get(
                "errorMessage",
                response_data.get("ResponseDescription", "STK push failed.")
            )
        )

    checkout_request_id = response_data.get("CheckoutRequestID")
    merchant_request_id = response_data.get("MerchantRequestID")

    if not checkout_request_id:
        raise ValueError(
            response_data.get(
                "ResponseDescription",
                "Daraja did not return CheckoutRequestID."
            )
        )

    update_order_stk_ids(
        order["id"],
        merchant_request_id,
        checkout_request_id
    )

    return {
        "success": True,
        "message": response_data.get(
            "CustomerMessage",
            "STK payment prompt sent. Enter your M-PESA PIN."
        ),
        "checkout_request_id": checkout_request_id,
        "order_reference": order["order_reference"]
    }


# -------------------------------------------------------------------
# Callback processing
# -------------------------------------------------------------------

def callback_items_to_dict(callback_metadata):
    """Convert Daraja CallbackMetadata Item array into a Python dictionary."""
    values = {}

    if not callback_metadata:
        return values

    for item in callback_metadata.get("Item", []):
        name = item.get("Name")
        value = item.get("Value")

        if name:
            values[name] = value

    return values


def process_stk_callback(payload):
    """
    Validate and save an STK callback.
    It only marks PAID when:
    - ResultCode is 0
    - CheckoutRequestID belongs to our pending order
    - Callback amount matches the expected order total
    """
    stk_callback = payload.get("Body", {}).get("stkCallback", {})
    checkout_request_id = stk_callback.get("CheckoutRequestID")
    result_code = stk_callback.get("ResultCode")
    result_desc = stk_callback.get("ResultDesc", "No result description.")

    insert_callback_log("STK_CALLBACK", checkout_request_id, payload)

    if not checkout_request_id:
        return False, "Missing CheckoutRequestID in callback."

    order = fetch_one(
        "SELECT * FROM orders WHERE checkout_request_id = %s",
        (checkout_request_id,)
    )

    if not order:
        return False, "Callback received for an unknown order."

    metadata = callback_items_to_dict(stk_callback.get("CallbackMetadata"))
    callback_json = json.dumps(payload)

    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        # Any non-zero result means payment did not complete.
        if result_code != 0:
            status = "CANCELLED" if result_code == 1032 else "FAILED"

            cursor.execute(
                """
                UPDATE orders
                SET status = %s,
                    result_code = %s,
                    result_description = %s,
                    raw_callback = %s
                WHERE id = %s
                """,
                (
                    status,
                    result_code,
                    result_desc,
                    callback_json,
                    order["id"]
                )
            )
            connection.commit()
            return True, f"Order marked {status}."

        amount_received = metadata.get("Amount")
        receipt_number = metadata.get("MpesaReceiptNumber")
        transaction_date = metadata.get("TransactionDate")
        phone_number = metadata.get("PhoneNumber")

        # Do not trust a callback blindly: amount must match our saved order.
        if amount_received is None:
            raise ValueError("Successful callback has no Amount.")

        expected_amount = Decimal(str(order["total_amount"]))
        received_amount = Decimal(str(amount_received))

        if received_amount != expected_amount:
            cursor.execute(
                """
                UPDATE orders
                SET status = 'FAILED',
                    result_code = %s,
                    result_description = %s,
                    raw_callback = %s
                WHERE id = %s
                """,
                (
                    result_code,
                    (
                        f"Amount mismatch. Expected {expected_amount}; "
                        f"received {received_amount}."
                    ),
                    callback_json,
                    order["id"]
                )
            )
            connection.commit()
            return False, "Amount mismatch; order was not marked paid."

        cursor.execute(
            """
            UPDATE orders
            SET status = 'PAID',
                customer_phone = %s,
                mpesa_receipt_number = %s,
                mpesa_transaction_date = %s,
                result_code = %s,
                result_description = %s,
                raw_callback = %s,
                paid_at = NOW()
            WHERE id = %s
            """,
            (
                str(phone_number) if phone_number else None,
                receipt_number,
                str(transaction_date) if transaction_date else None,
                result_code,
                result_desc,
                callback_json,
                order["id"]
            )
        )

        connection.commit()
        return True, "Payment saved successfully."

    except Exception:
        connection.rollback()
        raise

    finally:
        cursor.close()
        connection.close()


# -------------------------------------------------------------------
# Web pages
# -------------------------------------------------------------------

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


@app.route("/transactions")
def transactions_page():
    """View recent transactions in the browser."""
    orders = fetch_all(
        """
        SELECT id, order_reference, total_amount, status,
               customer_phone, mpesa_receipt_number,
               created_at, paid_at
        FROM orders
        ORDER BY id DESC
        LIMIT 100
        """
    )
    return render_template("transactions.html", orders=orders)


# -------------------------------------------------------------------
# Frontend API routes
# -------------------------------------------------------------------

@app.route("/api/orders", methods=["POST"])
def api_create_order():
    """
    Request body example:
    {
      "items": [
        {"name": "Bread", "price": 60, "quantity": 1},
        {"name": "Milk", "price": 70, "quantity": 2}
      ]
    }
    """
    data = request.get_json(silent=True) or {}
    items = data.get("items", [])

    if not isinstance(items, list) or len(items) == 0:
        return jsonify({
            "success": False,
            "error": "Add at least one item before creating an order."
        }), 400

    cleaned_items = []

    try:
        for index, item in enumerate(items, start=1):
            name = str(item.get("name") or f"Item {index}").strip()
            quantity = int(item.get("quantity", 1))
            price = Decimal(str(item.get("price")))

            if not name:
                raise ValueError("Every item must have a name.")

            if quantity < 1:
                raise ValueError("Quantity must be at least 1.")

            if price <= 0:
                raise ValueError("Item price must be greater than zero.")

            cleaned_items.append({
                "name": name,
                "quantity": quantity,
                "price": str(price)
            })

        order = create_order(cleaned_items)

        return jsonify({
            "success": True,
            "order_id": order["id"],
            "order_reference": order["order_reference"],
            "amount": str(order["total_amount"]),
            "status": order["status"]
        })

    except (ValueError, InvalidOperation) as error:
        return jsonify({
            "success": False,
            "error": str(error)
        }), 400

    except Error as error:
        return jsonify({
            "success": False,
            "error": f"Database error: {error}"
        }), 500


@app.route("/api/orders/<int:order_id>/qr", methods=["POST"])
def api_generate_order_qr(order_id):
    """Generate and save a QR image for a previously created order."""
    try:
        order = fetch_one(
            "SELECT * FROM orders WHERE id = %s",
            (order_id,)
        )

        if not order:
            return jsonify({
                "success": False,
                "error": "Order not found."
            }), 404

        if order["status"] == "PAID":
            return jsonify({
                "success": False,
                "error": "This order is already paid."
            }), 400

        result = generate_daraja_qr(order)
        return jsonify(result)

    except Exception as error:
        return jsonify({
            "success": False,
            "error": str(error)
        }), 500


@app.route("/api/orders/<int:order_id>/stk-push", methods=["POST"])
def api_stk_push(order_id):
    """
    Request body:
    {
      "phone_number": "254790981933"
    }
    """
    data = request.get_json(silent=True) or {}
    phone_number = data.get("phone_number", "")

    try:
        order = fetch_one(
            "SELECT * FROM orders WHERE id = %s",
            (order_id,)
        )

        if not order:
            return jsonify({
                "success": False,
                "error": "Order not found."
            }), 404

        if order["status"] == "PAID":
            return jsonify({
                "success": False,
                "error": "This order has already been paid."
            }), 400

        result = initiate_stk_push(order, phone_number)
        return jsonify(result)

    except (ValueError, requests.RequestException) as error:
        return jsonify({
            "success": False,
            "error": str(error)
        }), 400

    except Error as error:
        return jsonify({
            "success": False,
            "error": f"Database error: {error}"
        }), 500


@app.route("/api/orders/<int:order_id>", methods=["GET"])
def api_order_status(order_id):
    """Used by the browser to check whether the callback marked payment as PAID."""
    order = fetch_one(
        """
        SELECT id, order_reference, total_amount, status,
               customer_phone, mpesa_receipt_number,
               result_code, result_description,
               created_at, paid_at
        FROM orders
        WHERE id = %s
        """,
        (order_id,)
    )
    if order["status"] == "PAID":
        return True, "Duplicate callback ignored; order is already paid."

    if not order:
        return jsonify({
            "success": False,
            "error": "Order not found."
        }), 404

    for key in ("total_amount", "created_at", "paid_at"):
        if order.get(key) is not None:
            order[key] = str(order[key])

    return jsonify({
        "success": True,
        "order": order
    })


# -------------------------------------------------------------------
# M-PESA callback endpoint
# -------------------------------------------------------------------

@app.route("/api/mpesa/stk-callback", methods=["POST"])
def mpesa_stk_callback():
    """
    Safaricom posts the STK result to this route.
    Do not add login protection to this endpoint.
    Your PUBLIC_BASE_URL / MPESA_CALLBACK_URL must point here.
    """
    payload = request.get_json(silent=True)

    if not payload:
        return jsonify({
            "ResultCode": 1,
            "ResultDesc": "Invalid JSON callback payload."
        }), 400

    try:
        success, message = process_stk_callback(payload)

        # Return a simple valid JSON acknowledgement.
        # Safaricom does not need your internal database details.
        return jsonify({
            "ResultCode": 0,
            "ResultDesc": "Callback received."
        }), 200

    except Exception as error:
        app.logger.exception("STK callback processing failed: %s", error)

        return jsonify({
            "ResultCode": 1,
            "ResultDesc": "Callback received but processing failed."
        }), 500


# -------------------------------------------------------------------
# Development utility route - remove or protect in production
# -------------------------------------------------------------------



if __name__ == "__main__":
    print("=" * 65)
    print("M-PESA QR POS SYSTEM WITH MYSQL AND CALLBACK SUPPORT")
    print("=" * 65)
    print("Open: http://127.0.0.1:5000")
    print("Transactions: http://127.0.0.1:5000/transactions")
    print("Callback URL:", MPESA_CALLBACK_URL or "Not configured")
    print("=" * 65)

    app.run(debug=False, host="0.0.0.0", port=5000)