from flask import Flask, render_template, request, redirect, url_for, flash, g, session
import smtplib
from email.mime.text import MIMEText
import mysql.connector
from werkzeug.security import check_password_hash
#mpesa imports
import requests
import base64
import logging
from datetime import datetime
from datetime import date
from datetime import datetime, timedelta
from flask import jsonify
from b2c import B2C
import json
from decimal import Decimal
import logging
import sys
app = Flask(__name__)
app.secret_key = 'c943215a5fc8e370750a138de45cf1ef'  # Replace with a strong secret key
  # M-Pesa API Credentials
CONSUMER_KEY = "RifKpWJqOMv3pmzNaYP9A6xOr7NuL0HGqRG6VHXX6HPwz74v"
CONSUMER_SECRET = "SMbb3ol4FWBlcokQj9AX7KMRoJdrs1GI7hAr4nGDMz8oubjDqHaWGTCK6GG7Tj8l"
B2C_CONSUMER_KEY = "RifKpWJqOMv3pmzNaYP9A6xOr7NuL0HGqRG6VHXX6HPwz74v"
B2C_CONSUMER_SECRET = "SMbb3ol4FWBlcokQj9AX7KMRoJdrs1GI7hAr4nGDMz8oubjDqHaWGTCK6GG7Tj8l"
SHORTCODE = "4160337"  # e.g., 174379 (for Daraja sandbox)
PASSKEY = "24bc7367063b51cbfae01fc5c362c7abe6855bbb97826079be2b41f7835e6e48"
CALLBACK_URL = "https://www.techcodesoftwaresolutions.co.ke/juakalienterprises/callback"
BASE_URL = "https://api.safaricom.co.ke"  # Live URL
INITIATOR_NAME = "HENRYMUNYOKI"
SECURITY_CREDENTIAL = "XOrJGffEySKBGdFqWqFkMchW4AyKurIPjw5vLV9pnzXTmvq6whXcEQXP1qqAoji3xZqCLBQOCitEpDfphKTST0KA0+5jw36yyQ3i3WtGirpk4fA2dwEfLA+xFe1b6lHZQYuV9dAsfiavaAJJa18Cxlp1BiBZ5urlWpMEO8okGVnC92qKUOfYYg7d97PS0C1ssgwEtH8di7obBn5rIXtLTSWKO4aOKzWaoJhoZ7ylLc70aDWJUW3OWDaqspw4l1kKaBLVqhZskuHFz0mofxxE3fgaAQVR/LPgKW8HKqFQFm4fo9FEx7fdpD5U25lZ32R7LOXeTM1ysUSh6qRlJquo+Q=="
TIMEOUT_URL = "https://www.techcodesoftwaresolutions.co.ke/juakalienterprises/b2c/timeout"
RESULT_URL = "https://www.techcodesoftwaresolutions.co.ke/juakalienterprises/b2c/result"


# Logging Configuration
logging.basicConfig(filename="mpesa.log", level=logging.DEBUG, format="%(asctime)s - %(levelname)s - %(message)s")

# MySQL Configuration
DB_CONFIG = {
    'host': "localhost",
    'user': "techcod2_techcod2_juakalienterpr",
    'password': "techcod2_juakalienterpr@",
    'database': "techcod2_juakalienterprises"
}


def get_db():
    """Establish a database connection for each request."""
    if 'db' not in g:
        try:
            g.db = mysql.connector.connect(**DB_CONFIG)
            print("Database connection established!")  # Debugging line
        except Exception as e:
            print(f"Database connection error: {e}")  # Debugging line
            raise e
    return g.db


@app.teardown_appcontext
def close_db(error):
    """Close the database connection after each request."""
    db = g.pop('db', None)
    if db:
        db.close()
      
@app.route('/')
def home():
    return render_template('index.html')

@app.route('/about')
def about():
    return render_template('about.html')

@app.route('/team')
def team():
    return render_template('Team.html')

@app.route('/terms')
def terms():
    return render_template('Terms.html')

@app.route('/knowledge')
def knowledge():
    return render_template('knowledgebase.html')

@app.route('/coming_soon')
def coming_soon():
    return render_template('coming_soon.html')
@app.route('/packages')
def packages():
    return render_template('packages.html')

@app.route('/invest')
def invest():
    if 'user_id' not in session:
        flash("You need to log in to access the invest page.", "danger")
        return redirect(url_for('login'))
    return render_template('invest.html')


@app.route('/contact')
def contact():
    return render_template('contact.html')

@app.route('/referrals')
def referrals():
    if 'user_id' not in session:
        flash('You need to log in to view referrals.', 'danger')
        return redirect(url_for('login'))

    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Fetch referral code
    cursor.execute("SELECT referral_code FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()

    # If no referral code, generate one
    if not user['referral_code']:
        import random, string
        referral_code = ''.join(random.choices(string.ascii_uppercase + string.digits, k=6))
        cursor.execute("UPDATE users SET referral_code = %s WHERE id = %s", (referral_code, user_id))
        db.commit()
    else:
        referral_code = user['referral_code']

    # Fetch users referred by the current user with join date from referrals table
    cursor.execute("""
        SELECT u.name, u.email, r.created_at
        FROM referrals r
        JOIN users u ON u.id = r.referred_user_id
        WHERE r.referrer_id = %s
        ORDER BY r.created_at DESC
    """, (user_id,))
    referred_users = cursor.fetchall()

    cursor.close()

    referral_link = f"{request.url_root}register?ref={referral_code}"

    return render_template('referrals.html', referral_link=referral_link, referred_users=referred_users)



@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']

        db = get_db()
        cursor = db.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE email = %s AND password = %s", (email, password))
        user = cursor.fetchone()
        if user:
            session['user_id'] = user['id']
            session['email'] = user['email']
            flash('Login successful!', 'success')
            return redirect(url_for('dashboard'))
        else:
            flash('Invalid email or password', 'danger')
    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    referrer_id = None
    referrer_name = None

    if request.method == 'GET':
        # Get referral code from the URL
        ref_code = request.args.get('ref')
        if ref_code:
            db = get_db()
            cursor = db.cursor(dictionary=True)
            cursor.execute("SELECT id, name FROM users WHERE referral_code = %s", (ref_code,))
            referrer = cursor.fetchone()
            if referrer:
                referrer_id = referrer['id']
                referrer_name = referrer['name']
            cursor.close()

    if request.method == 'POST':
        form_type = request.form.get('form_type')
        db = get_db()
        cursor = db.cursor()

        try:
            if form_type == "personal":
                name = request.form['name']
                email = request.form['email']
                password = request.form['password']
                id_number = request.form['id_number']
                contact = request.form['contact']
                gender = request.form['gender']

                # Get referrer_id from the hidden input
                referrer_id = request.form.get('referrer_id')
                if referrer_id:
                    referrer_id = int(referrer_id)
                else:
                    referrer_id = None

                import random, string
                referral_code = ''.join(random.choices(string.ascii_uppercase + string.digits, k=6))

                # 1. Insert into users table
                cursor.execute("""
                    INSERT INTO users (name, email, password, id_number, contact, gender, referrer_id, referral_code) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """, (name, email, password, id_number, contact, gender, referrer_id, referral_code))

                # 2. Get new user ID
                new_user_id = cursor.lastrowid

                # 3. Add referral record if applicable
                if referrer_id:
                    cursor.execute(
                        "INSERT INTO referrals (referrer_id, referred_user_id) VALUES (%s, %s)",
                        (referrer_id, new_user_id)
                    )

            elif form_type == "business":
                business_name = request.form['business_name']
                incorporation_date = request.form['incorporation_date']
                country = request.form['country']
                ceo_name = request.form['ceo_name']
                email = request.form['email']
                phone = request.form['phone']
                about = request.form['about']

                cursor.execute("""
                    INSERT INTO business_users (business_name, incorporation_date, country, ceo_name, email, phone, about) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """, (business_name, incorporation_date, country, ceo_name, email, phone, about))

            db.commit()
            flash('Registration successful!', 'success')
            return redirect(url_for('login'))

        except Exception as e:
            db.rollback()
            flash(f'An error occurred: {str(e)}', 'danger')

        finally:
            cursor.close()

    return render_template('register.html', referrer_id=referrer_id, referrer_name=referrer_name)



@app.route('/dashboard')
def dashboard():
    if 'user_id' not in session:
        flash('Please log in to access the dashboard.', 'warning')
        return redirect(url_for('login'))

    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Bonus earnings for this user
    cursor.execute("SELECT SUM(bonus_amount) as total_bonus FROM bonuses WHERE referred_user_id = %s", (user_id,))
    bonus_record = cursor.fetchone()
    bonus_total = float(bonus_record['total_bonus']) if bonus_record and bonus_record['total_bonus'] else 0.0

    # Referral earnings from others this user referred
    cursor.execute("""
        SELECT SUM(cf.direct_received) as referral_earnings
        FROM company_funds cf
        JOIN users u ON cf.user_id = u.id
        WHERE u.referrer_id = %s
    """, (user_id,))
    referral_record = cursor.fetchone()
    referral_total = float(referral_record['referral_earnings']) if referral_record and referral_record['referral_earnings'] else 0.0

    # 🟢 Fetch actual wallet balance from balance table
    cursor.execute("SELECT total_balance FROM balance WHERE user_id = %s", (user_id,))
    balance_row = cursor.fetchone()
    total_balance = float(balance_row['total_balance']) if balance_row and balance_row['total_balance'] else 0.0

    cursor.close()

    return render_template('dashboard.html',
                           email=session.get('email'),
                           bonus_total=bonus_total,
                           referral_total=referral_total,
                           total_balance=total_balance)


# Get M-Pesa Token
# Get M-Pesa Token for STK Push (C2B)
def get_c2b_token():
    url = f"{BASE_URL}/oauth/v1/generate?grant_type=client_credentials"
    auth = (CONSUMER_KEY, CONSUMER_SECRET)
    response = requests.get(url, auth=auth)

    if response.status_code != 200:
        logging.error(f"Failed to get C2B token: {response.text}")
        return None

    token = response.json().get("access_token")
    if not token:
        logging.error(f"C2B Access token missing in response: {response.json()}")
    return token

# Get M-Pesa Token for B2C
def get_b2c_token():
    url = f"{BASE_URL}/oauth/v1/generate?grant_type=client_credentials"
    auth = (B2C_CONSUMER_KEY, B2C_CONSUMER_SECRET)
    response = requests.get(url, auth=auth)

    if response.status_code != 200:
        logging.error(f"Failed to get B2C token: {response.text}")
        return None

    token = response.json().get("access_token")
    if not token:
        logging.error(f"B2C Access token missing in response: {response.json()}")
    return token


# STK Push Request
def stk_push(phone_number, amount):
    access_token = get_c2b_token()
    if not access_token:
        logging.error("STK Push failed: Unable to get M-Pesa token.")
        return {"error": "Failed to authenticate with M-Pesa."}

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

    url = f"{BASE_URL}/mpesa/stkpush/v1/processrequest"
    headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}
    payload = {
        "BusinessShortCode": SHORTCODE,
        "Password": password,
        "Timestamp": timestamp,
        "TransactionType": "CustomerPayBillOnline",
        "Amount": amount,
        "PartyA": phone_number,
        "PartyB": SHORTCODE,
        "PhoneNumber": phone_number,
        "CallBackURL": CALLBACK_URL,
        "AccountReference": "Deposit",
        "TransactionDesc": "Deposit to Wallet"
    }

    response = requests.post(url, json=payload, headers=headers)
    logging.debug(f"STK Push Request: {payload}")
    logging.debug(f"STK Push Response: {response.text}")
    return response.json()
# B2C Withdraw
def b2c_withdraw(phone_number, amount):
    access_token = get_b2c_token()
    if not access_token:
        logging.error("B2C Withdrawal failed: Unable to get M-Pesa token.")
        return {"error": "Failed to authenticate with M-Pesa."}

    url = f"{BASE_URL}/mpesa/b2c/v1/paymentrequest"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json"
    }

    payload = {
        "InitiatorName": INITIATOR_NAME,
        "SecurityCredential": SECURITY_CREDENTIAL,
        "CommandID": "BusinessPayment",
        "Amount": int(amount),
        "PartyA": SHORTCODE,
        "PartyB": phone_number,
        "Remarks": "User withdrawal from account",
        "QueueTimeOutURL": TIMEOUT_URL,     # ✅ Correct usage
        "ResultURL": RESULT_URL,            # ✅ Correct usage
        "Occasion": "User Withdrawal"
    }

    try:
        logging.info("Initiating B2C withdrawal for %s KES to %s", amount, phone_number)
        logging.debug("B2C Payload: %s", json.dumps(payload, indent=2))

        response = requests.post(url, json=payload, headers=headers)
        response.raise_for_status()

        response_data = response.json()
        logging.debug("B2C Response: %s", json.dumps(response_data, indent=2))

        return response_data

    except requests.exceptions.HTTPError as http_err:
        logging.error("HTTP error during B2C withdrawal: %s", str(http_err))
        return {"error": "HTTP Error during B2C withdrawal."}
    except Exception as e:
        logging.exception("Unexpected exception during B2C withdrawal:")
        return {"error": str(e)}

@app.route('/b2c/result', methods=['GET', 'POST'])
def b2c_result():
    if request.method == 'GET':
        return render_template_string("""
            <h2>B2C Result Callback</h2>
            <p>This endpoint receives results of M-Pesa B2C withdrawals.</p>
            <p>Method: GET (for viewing), POST (for callback)</p>
        """)

    try:
        data = request.get_json()
        logging.info("B2C Result callback received: %s", json.dumps(data, indent=2))

        result = data.get('Result', {})
        amount = 0
        phone = ""
        user_id = None

        for param in result.get("ResultParameters", {}).get("ResultParameter", []):
            if param["Key"] == "TransactionAmount":
                amount = float(param["Value"])
            elif param["Key"] == "ReceiverPartyPublicName":
                phone = param["Value"].split(' ')[0].replace('-', '').strip()

        db = get_db()
        cursor = db.cursor()

        cursor.execute("""
            SELECT id, user_id FROM withdrawals
            WHERE phone = %s AND amount = %s AND status = 'Pending'
            ORDER BY created_at DESC LIMIT 1
        """, (phone, amount))
        withdrawal = cursor.fetchone()

        if withdrawal:
            user_id = withdrawal[1]
            cursor.execute("""
                UPDATE withdrawals
                SET status = 'Completed'
                WHERE id = %s
            """, (withdrawal[0],))

            cursor.execute("""
                UPDATE balance 
                SET total_balance = total_balance - %s
                WHERE user_id = %s
            """, (amount, user_id))

            db.commit()

        return jsonify({"ResultReceived": "OK"}), 200

    except Exception as e:
        logging.exception("Error processing B2C result callback:")
        return jsonify({"error": str(e)}), 500


@app.route('/b2c/timeout', methods=['GET', 'POST'])
def b2c_timeout():
    if request.method == 'GET':
        return render_template_string("""
            <h2>B2C Timeout Callback</h2>
            <p>This endpoint receives timeout notices from M-Pesa for B2C withdrawals.</p>
            <p>Method: GET (for viewing), POST (for callback)</p>
        """)

    try:
        data = request.get_json()
        logging.warning("B2C Timeout callback received: %s", json.dumps(data, indent=2))

        # Optional: Update matching withdrawal to 'Failed (Timeout)'
        return jsonify({"TimeoutReceived": "OK"}), 200

    except Exception as e:
        logging.exception("Error processing B2C timeout callback:")
        return jsonify({"error": str(e)}), 500

@app.route('/insert_deposit', methods=['POST'])
def insert_deposit():
    if 'user_id' not in session:
        flash("You need to log in to deposit.", "danger")
        return redirect(url_for('login'))

    user_id = session['user_id']
    data = request.get_json()
    amount = data.get('amount')
    phone_number = data.get('phone_number')

    if not amount or not phone_number:
        flash("Amount and phone number are required.", "danger")
        return redirect(url_for('deposit'))

    try:
        amount = float(amount)
    except ValueError:
        flash("Please enter a valid amount.", "danger")
        return redirect(url_for('deposit'))

    # Send STK Push
    mpesa_response = stk_push(phone_number, amount)

    if mpesa_response.get("ResponseCode") == "0":
        db = get_db()
        cursor = db.cursor(dictionary=True)

        try:
            now = datetime.now()
            one_hour_ago = now - timedelta(hours=1)

            # Check for an existing deposit within the last hour
            cursor.execute("""
                SELECT id FROM deposits
                WHERE user_id = %s AND created_at >= %s
                ORDER BY created_at DESC LIMIT 1
            """, (user_id, one_hour_ago))
            existing = cursor.fetchone()

            if existing:
                # Replace the existing deposit record (UPDATE)
                cursor.execute("""
                    UPDATE deposits
                    SET amount = %s, phone_number = %s, created_at = NOW()
                    WHERE id = %s
                """, (amount, phone_number, existing['id']))
            else:
                # Insert new deposit record
                cursor.execute("""
                    INSERT INTO deposits (user_id, amount, phone_number, created_at)
                    VALUES (%s, %s, %s, NOW())
                """, (user_id, amount, phone_number))

            # Recalculate balance by summing deposits (just 1 per hour now)
            cursor.execute("SELECT SUM(amount) AS total_amount FROM deposits WHERE user_id = %s", (user_id,))
            result = cursor.fetchone()
            total_deposits = result['total_amount'] if result and result['total_amount'] else 0

            # Update or insert balance
            cursor.execute("SELECT total_balance FROM balance WHERE user_id = %s", (user_id,))
            balance_record = cursor.fetchone()

            if balance_record:
                cursor.execute("UPDATE balance SET total_balance = %s WHERE user_id = %s", (total_deposits, user_id))
            else:
                cursor.execute("INSERT INTO balance (user_id, total_balance) VALUES (%s, %s)", (user_id, total_deposits))

            db.commit()
            flash(f"Deposit of KSh {amount} successful (latest for the hour)!", "success")

        except Exception as e:
            db.rollback()
            flash(f"An error occurred: {str(e)}", "danger")
        finally:
            cursor.close()

    else:
        flash("STK Push failed. Please try again.", "danger")

    return redirect(url_for('deposit'))

@app.route('/withdraw', methods=['GET', 'POST'])
def withdraw():
    user_id = session.get('user_id')
    if not user_id:
        flash('You need to log in to withdraw.', 'danger')
        return redirect(url_for('login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Fetch user's contact
    cursor.execute("SELECT contact FROM users WHERE id = %s", (user_id,))
    user_data = cursor.fetchone()
    
    if not user_data or not user_data['contact']:
        session.clear()
        flash('Your contact number is missing. Please update your profile.', 'danger')
        return redirect(url_for('login'))

    phone_number = user_data['contact']

    # Fetch balance
    cursor.execute("SELECT total_balance FROM balance WHERE user_id = %s", (user_id,))
    balance_result = cursor.fetchone()
    total_balance = balance_result['total_balance'] if balance_result else 0

    if request.method == 'POST':
        try:
            amount = float(request.form['amount'])
            if amount <= 0:
                flash("Please enter a valid amount greater than zero.", "danger")
                return redirect(url_for('withdraw'))
        except ValueError:
            flash("Please enter a valid withdrawal amount.", "danger")
            return redirect(url_for('withdraw'))

        if amount > total_balance:
            flash('Insufficient balance for withdrawal.', 'danger')
        else:
            try:
                # Send B2C request
                response = b2c_withdraw(phone_number, amount)

                if response.get("ResponseCode") == "0":
                    # Success: Deduct and insert as Completed
                    cursor.execute("""
                        UPDATE balance 
                        SET total_balance = total_balance - %s 
                        WHERE user_id = %s
                    """, (amount, user_id))

                    cursor.execute("""
                        INSERT INTO withdrawals (user_id, amount, phone, status, created_at) 
                        VALUES (%s, %s, %s, %s, %s)
                    """, (user_id, amount, phone_number, 'Completed', datetime.now()))
                    
                    db.commit()
                    flash('Withdrawal successful! Funds sent to your M-Pesa.', 'success')

                else:
                    # Failure: Insert as Failed
                    error_msg = response.get("errorMessage", response.get("error", "Unknown error"))
                    cursor.execute("""
                        INSERT INTO withdrawals (user_id, amount, phone, status, created_at) 
                        VALUES (%s, %s, %s, %s, %s)
                    """, (user_id, amount, phone_number, 'Failed', datetime.now()))
                    
                    db.commit()
                    flash(f'M-Pesa withdrawal failed: {error_msg}', 'danger')

            except Exception as e:
                db.rollback()
                flash(f'An error occurred during withdrawal: {str(e)}', 'danger')

    # Load withdrawal history
    cursor.execute("""
        SELECT * FROM withdrawals 
        WHERE user_id = %s 
        ORDER BY created_at DESC
    """, (user_id,))
    withdrawals = cursor.fetchall()

    cursor.close()
    return render_template('withdraw.html', 
                           withdrawals=withdrawals, 
                           total_balance=total_balance, 
                           user_phone=phone_number)
@app.route('/bonuses')
def bonuses():
    if 'user_id' not in session:
        flash("Please log in to view bonuses.", "warning")
        return redirect(url_for('login'))

    user_id = session['user_id']
    db = get_db()
    cursor = db.cursor(dictionary=True)

    cursor.execute("""
        SELECT b.package_name, b.package_price, b.bonus_amount, b.created_at 
        FROM bonuses b 
        WHERE b.referrer_id = %s 
        ORDER BY b.created_at DESC
    """, (user_id,))
    bonuses = cursor.fetchall()
    cursor.close()

    return render_template('bonus_view.html', bonuses=bonuses)


@app.route('/logout')
def logout():
    session.clear()
    flash('You have been logged out.', 'info')
    return redirect(url_for('login'))


@app.route('/buy_package', methods=['POST'])
def buy_package():
    if 'user_id' not in session:
        flash("Please log in to continue.", "danger")
        return redirect(url_for('login'))

    user_id = session['user_id']
    package_name = request.form.get('package_name')
    try:
        package_price = float(request.form.get('package_price'))
        direct_earning = float(request.form.get('direct_earning'))
    except (TypeError, ValueError):
        flash("Invalid package data.", "danger")
        return redirect(url_for('packages'))

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # ❗ Check if user already purchased a package
    cursor.execute("SELECT COUNT(*) AS total FROM purchases WHERE user_id = %s", (user_id,))
    has_purchased = cursor.fetchone()['total'] > 0
    cursor.close()

    if has_purchased:
        return render_template(
            'buy_package.html',
            package_name=package_name,
            package_price=package_price,
            direct_received=direct_earning,
            already_purchased=True  # flag to show error in template
        )

    return render_template(
        'buy_package.html',
        package_name=package_name,
        package_price=package_price,
        direct_received=direct_earning,
        already_purchased=False
    )


@app.route('/process_payment', methods=['POST'])
def process_payment():
    if 'user_id' not in session:
        flash("You need to log in to proceed.", "danger")
        return redirect(url_for('login'))

    user_id = session['user_id']
    package_name = request.form.get('package_name')

    try:
        package_price = float(request.form.get('package_price'))
        direct_received = float(request.form.get('direct_received'))
    except (TypeError, ValueError):
        flash("Invalid package details.", "danger")
        return redirect(url_for('packages'))

    daily_bonus_amount = round(package_price / 180, 2)
    bonus_start_date = date.today()

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Get user's contact and referrer
    cursor.execute("SELECT contact, referrer_id FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    if not user:
        flash("User not found.", "danger")
        return redirect(url_for('packages'))

    phone_number = user['contact']
    referrer_id = user.get('referrer_id')

    # Check if this is the user's first purchase
    cursor.execute("SELECT COUNT(*) AS purchase_count FROM purchases WHERE user_id = %s", (user_id,))
    is_first_purchase = cursor.fetchone()['purchase_count'] == 0

    # Send STK Push
    stk_response = stk_push(phone_number, package_price)
    if 'errorMessage' in stk_response:
        flash(f"STK Push Failed: {stk_response['errorMessage']}", "danger")
        return redirect(url_for('packages'))

    try:
        # 1. Insert purchase
        cursor.execute("""
            INSERT INTO purchases (user_id, package_name, package_price, direct_received, daily_bonus_amount, daily_bonus_start_date)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (user_id, package_name, package_price, direct_received, daily_bonus_amount, bonus_start_date))

        # 2. Insert into company_funds
        company_amount = round(package_price - direct_received, 2)
        referrer_paid = 1 if is_first_purchase and referrer_id else 0
        cursor.execute("""
            INSERT INTO company_funds (user_id, package_name, package_price, direct_received, company_amount, referrer_paid)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (user_id, package_name, package_price, direct_received, company_amount, referrer_paid))

        # 3. Insert referrer bonus if exists
        if referrer_id:
            bonus_amount = round(0.03 * package_price, 2)
            cursor.execute("""
                INSERT INTO bonuses (referrer_id, referred_user_id, package_name, package_price, bonus_amount)
                VALUES (%s, %s, %s, %s, %s)
            """, (referrer_id, user_id, package_name, package_price, bonus_amount))

            cursor.execute("SELECT total_balance FROM balance WHERE user_id = %s", (referrer_id,))
            ref_balance = cursor.fetchone()
            if ref_balance:
                new_total = round(float(ref_balance['total_balance']) + bonus_amount, 2)
                cursor.execute("UPDATE balance SET total_balance = %s WHERE user_id = %s", (new_total, referrer_id))
            else:
                cursor.execute("INSERT INTO balance (user_id, total_balance) VALUES (%s, %s)", (referrer_id, bonus_amount))

        # 4. Direct commission to referrer (only on first purchase), else to user
        pay_user_id = referrer_id if referrer_id and is_first_purchase else user_id
        cursor.execute("SELECT total_balance FROM balance WHERE user_id = %s", (pay_user_id,))
        payee_balance = cursor.fetchone()
        if payee_balance:
            new_direct_total = round(float(payee_balance['total_balance']) + direct_received, 2)
            cursor.execute("UPDATE balance SET total_balance = %s WHERE user_id = %s", (new_direct_total, pay_user_id))
        else:
            cursor.execute("INSERT INTO balance (user_id, total_balance) VALUES (%s, %s)", (pay_user_id, direct_received))

        # 5. Insert first daily bonus record for user (next day from 9:00 A.M)
        tomorrow = date.today() + timedelta(days=1)
        cursor.execute("""
            INSERT INTO daily_earnings (user_id, amount, earned_on)
            VALUES (%s, %s, %s)
        """, (user_id, daily_bonus_amount, tomorrow))

        db.commit()
        flash("STK Push sent. Please complete payment on your phone.", "info")
        return redirect(url_for('packages'))

    except Exception as e:
        db.rollback()
        flash(f"Payment processing error: {e}", "danger")
        return redirect(url_for('packages'))

    finally:
        cursor.close()


def stk_push_donate(phone_number, amount):
    access_token = get_c2b_token()
    if not access_token:
        logging.error("STK Push (Donation) failed: Unable to get M-Pesa token.")
        return {"error": "Failed to authenticate with M-Pesa."}

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

    url = f"{BASE_URL}/mpesa/stkpush/v1/processrequest"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json"
    }

    payload = {
        "BusinessShortCode": SHORTCODE,
        "Password": password,
        "Timestamp": timestamp,
        "TransactionType": "CustomerPayBillOnline",
        "Amount": amount,
        "PartyA": phone_number,
        "PartyB": SHORTCODE,
        "PhoneNumber": phone_number,
        "CallBackURL": CALLBACK_URL,
        "AccountReference": "Donation",
        "TransactionDesc": "Donation to Juakali Youth Enterprise"
    }

    response = requests.post(url, json=payload, headers=headers)
    logging.debug(f"STK Push Donation Request: {payload}")
    logging.debug(f"STK Push Donation Response: {response.text}")
    return response.json()

@app.route('/donate', methods=['GET', 'POST'])
def donate():
    if request.method == 'POST':
        amount = request.form.get('amount')
        phone = request.form.get('phone')

        if not amount or not phone:
            flash("Amount and phone number are required.", "danger")
            return render_template('donate.html')

        try:
            amount = float(amount)
        except ValueError:
            flash("Invalid donation amount.", "danger")
            return render_template('donate.html')

        # Call donation-specific STK push
        response = stk_push_donate(phone, amount)

        # Handle API response
        if response.get("ResponseCode") == "0":
            db = get_db()
            cursor = db.cursor()
            user_id = session.get('user_id')  # Optional

            try:
                cursor.execute(
                    "INSERT INTO donations (user_id, amount, phone, created_at) VALUES (%s, %s, %s, NOW())",
                    (user_id, amount, phone)
                )
                db.commit()
                flash(f"Thank you! An M-Pesa prompt has been sent to {phone}. Please complete the donation of KSh {amount}.", "success")
            except Exception as e:
                db.rollback()
                flash(f"Database error: {str(e)}", "danger")
            finally:
                cursor.close()
        else:
            flash("M-Pesa STK Push failed. Please try again.", "danger")

        return render_template('donate.html')

    return render_template('donate.html')

@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']

        db = get_db()
        cursor = db.cursor(dictionary=True)
        cursor.execute("SELECT * FROM admin WHERE email = %s", (email,))
        admin = cursor.fetchone()
        cursor.close()
        db.close()

        if admin and admin['password'] == password:  # Direct comparison
            session['admin_logged_in'] = True
            session['admin_email'] = admin['email']  # Store admin email in session
            return redirect(url_for('admin_dashboard'))
        else:
            flash("Invalid email or password", "danger")

    return render_template('Admin/Login.html')  # Render login.html in Admin folder


@app.route('/admin/dashboard')
def admin_dashboard():
    if not session.get('admin_logged_in'):
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Fetch total registered users
    cursor.execute("SELECT COUNT(*) AS total_users FROM users")
    total_users = cursor.fetchone()['total_users']

    # Fetch overall total funds from the balance table
    cursor.execute("SELECT SUM(total_balance) AS total_funds FROM balance")
    result = cursor.fetchone()
    total_funds = result['total_funds'] if result['total_funds'] is not None else 0

    cursor.close()
    db.close()

    # Pass admin email and fetched values to the dashboard template
    return render_template(
        'Admin/dashboard.html',
        total_users=total_users,
        total_funds=total_funds,
        admin_email=session.get('admin_email')
    )

@app.route('/admin/logout')
def admin_logout():
    session.pop('admin_logged_in', None)
    session.pop('admin_email', None)
    return redirect(url_for('admin_login'))
# Route to display registered users in a table view (Manage Users)
@app.route('/admin/manageusers', methods=['GET'])
def manage_users():
    if not session.get('admin_logged_in'):
        flash('Please log in as admin to manage users.', 'danger')
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT * FROM users ORDER BY id ASC")
    users = cursor.fetchall()
    cursor.close()
    return render_template('Admin/manageusers.html', users=users)
    
@app.route('/admin/managedonations', methods=['GET', 'POST'])
def manage_donations():
    if not session.get('admin_logged_in'):
        flash('Please log in as admin to manage donations.', 'danger')
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Fetch admin contact first
    admin_contact = ''
    if 'admin_id' in session:
        cursor.execute("SELECT contact FROM admin WHERE id = %s", (session['admin_id'],))
        row = cursor.fetchone()
        if row:
            admin_contact = row['contact']
        else:
            flash("Admin contact not found in database.", "warning")

    # Handle form POST
    if request.method == 'POST':
        amount = request.form['amount']
        phone = request.form['phone']

        try:
            amount = float(amount)
        except ValueError:
            flash("Invalid amount entered.", "danger")
            return redirect(url_for('manage_donations'))

        # Trigger M-Pesa B2C withdrawal
        result = b2c_withdraw(phone, amount)

        if result.get("ResponseCode") == "0":
            flash(f"Withdrawal of KES {amount} to {phone} was successfully initiated.", "success")
        else:
            error_msg = result.get("error") or result.get("errorMessage") or "Failed to initiate withdrawal."
            flash(f"Withdrawal failed: {error_msg}", "danger")

        return redirect(url_for('manage_donations'))

    # Fetch donation records
    cursor.execute("SELECT * FROM donations ORDER BY id ASC")
    donations = cursor.fetchall()

    # Calculate total donations
    cursor.execute("SELECT SUM(amount) AS total_amount FROM donations")
    total = cursor.fetchone()['total_amount'] or 0

    cursor.close()
    return render_template('Admin/managedonations.html', donations=donations, total=total, admin_contact=admin_contact)

@app.route('/admin/manage_companyfunds', methods=['GET', 'POST'])
def manage_companyfunds():
    if not session.get('admin_logged_in'):
        flash('Please log in as admin to manage donations.', 'danger')
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)

    # Fetch admin contact first
    admin_contact = ''
    if 'admin_id' in session:
        cursor.execute("SELECT contact FROM admin WHERE id = %s", (session['admin_id'],))
        row = cursor.fetchone()
        if row:
            admin_contact = row['contact']
        else:
            flash("Admin contact not found in database.", "warning")

    # Handle form POST
    if request.method == 'POST':
        amount = request.form['amount']
        phone = request.form['phone']

        try:
            amount = float(amount)
        except ValueError:
            flash("Invalid amount entered.", "danger")
            return redirect(url_for('manage_companyfunds'))

        # Trigger M-Pesa B2C withdrawal
        result = b2c_withdraw(phone, amount)

        if result.get("ResponseCode") == "0":
            flash(f"Withdrawal of KES {amount} to {phone} was successfully initiated.", "success")

            # Record the withdrawal in company_withdrawals table
            cursor.execute(
                "INSERT INTO company_withdrawals (amount, phone) VALUES (%s, %s)",
                (amount, phone)
            )
            db.commit()
        else:
            error_msg = result.get("error") or result.get("errorMessage") or "Failed to initiate withdrawal."
            flash(f"Withdrawal failed: {error_msg}", "danger")

        return redirect(url_for('manage_companyfunds'))

    # Fetch all company_funds records
    cursor.execute("SELECT * FROM company_funds ORDER BY id ASC")
    company_funds = cursor.fetchall()

    # Get total funds from donations
    cursor.execute("SELECT SUM(company_amount) AS total_amount FROM company_funds")
    total_funds = cursor.fetchone()['total_amount'] or 0

    # Get total amount withdrawn
    cursor.execute("SELECT SUM(amount) AS total_withdrawn FROM company_withdrawals")
    total_withdrawn = cursor.fetchone()['total_withdrawn'] or 0

    # Final total to display = donations - withdrawals
    total = total_funds - total_withdrawn

    cursor.close()
    return render_template(
        'Admin/managecompanyfunds.html',
        company_funds=company_funds,
        total=total,
        admin_contact=admin_contact
    )


@app.route('/delete_company_fund/<int:fund_id>', methods=['POST'])
def delete_company_fund(fund_id):
    db_conn = get_db()
    cursor = db_conn.cursor()

    try:
        cursor.execute("DELETE FROM company_funds WHERE id = %s", (fund_id,))
        db_conn.commit()
        flash('Company fund record deleted successfully.', 'success')
    except Exception as e:
        db_conn.rollback()
        flash(f'Error deleting record: {str(e)}', 'danger')
    finally:
        cursor.close()

    return redirect(url_for('manage_companyfunds'))
# Route to handle deletion of a user
@app.route('/admin/delete_user/<int:user_id>', methods=['POST'])
def delete_user(user_id):
    if not session.get('admin_logged_in'):
        flash('Please log in as admin to manage users.', 'danger')
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
        db.commit()
        flash('User deleted successfully!', 'success')
    except Exception as e:
        db.rollback()
        flash('An error occurred: ' + str(e), 'danger')
    finally:
        cursor.close()
    return redirect(url_for('manage_users'))


@app.route('/admin/company_funds')
def company_funds():
    if not session.get('admin_logged_in'):
        flash("Please log in as admin.", "danger")
        return redirect(url_for('admin_login'))

    db = get_db()
    cursor = db.cursor(dictionary=True)
    try:
        cursor.execute("SELECT * FROM company_funds ORDER BY created_at DESC")
        funds = cursor.fetchall()
    except Exception as e:
        funds = []
        flash(f"An error occurred: {str(e)}", "danger")
    finally:
        cursor.close()
        db.close()

    return render_template('Admin/company_funds.html', funds=funds)

logging.basicConfig(
    level=logging.ERROR,  # Log only errors
    format="%(asctime)s - %(levelname)s - %(message)s",
    handlers=[
        logging.StreamHandler(sys.stderr),  # Log to stderr
        logging.FileHandler("stderr.log")  # Log to stderr.log file
    ]
)
if __name__ == '__main__':
    app.run(debug=True)