from flask import Flask, request, jsonify
from twilio.rest import Client
import mysql.connector
from mysql.connector import Error
from datetime import datetime, timedelta
from random import randint
import re

app = Flask(__name__)

# ✅ Database config
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': ''  # Change if using custom password
}

# ✅ Twilio config
TWILIO_ACCOUNT_SID = 'ACc05634f2136e7037daeb12b62e217535'
TWILIO_AUTH_TOKEN = '2b605cd22ee4e802fc561ccdeda291ed'
TWILIO_WHATSAPP_NUMBER = 'whatsapp:+14155238886'  # Twilio sandbox sender

@app.route('/whatsapp_bot/send_otp', methods=['POST'])
def send_otp():
    admin_id = request.form.get('admin_id', '').strip()
    school_code = request.form.get('school_id', '').strip()
    phone_input = request.form.get('phone', '').strip()

    if not admin_id or not school_code or not phone_input:
        return jsonify({'status': 'fail', 'message': 'Missing input'}), 200

    try:
        # ✅ Connect to central DB to get school DB name
        central_conn = mysql.connector.connect(database='central_db', **db_config)
        central_cursor = central_conn.cursor(dictionary=True)
        central_cursor.execute("SELECT db_name FROM schools WHERE id = %s", (school_code,))
        school = central_cursor.fetchone()

        if not school:
            return jsonify({'status': 'fail', 'message': 'Invalid school code'}), 200

        school_db = school['db_name']

        # ✅ Connect to the school DB
        school_conn = mysql.connector.connect(database=school_db, **db_config)
        school_cursor = school_conn.cursor(dictionary=True)

        # ✅ Look up user by admin_id
        school_cursor.execute("""
            SELECT phone, verification_code, verification_expiry 
            FROM users WHERE admin_id = %s
        """, (admin_id,))
        user = school_cursor.fetchone()

        if not user:
            return jsonify({'status': 'fail', 'message': 'User not found'}), 200

        db_phone = user['phone'].strip()

        # ✅ Compare input phone with DB phone
        if db_phone != phone_input:
            return jsonify({'status': 'fail', 'message': 'Wrong phone number'}), 200

        now = datetime.now()

        # ✅ Reuse or generate OTP
        if user['verification_code'] and user['verification_expiry'] and user['verification_expiry'] > now:
            otp = user['verification_code']
            expiry = user['verification_expiry']
            message_status = 'Reusing valid OTP.'
        else:
            otp = str(randint(100000, 999999))
            expiry = now + timedelta(minutes=10)
            school_cursor.execute("""
                UPDATE users 
                SET verification_code = %s, verification_expiry = %s 
                WHERE admin_id = %s
            """, (otp, expiry, admin_id))
            school_conn.commit()
            message_status = 'Generated new OTP.'

        # ✅ Format phone for WhatsApp
        formatted_phone = re.sub(r'^0', '+254', db_phone)
        recipient = f'whatsapp:{formatted_phone}'

        # ✅ Send OTP via Twilio
        client = Client(TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN)
        message_body = f"""
Your OTP is: {otp}
It expires at: {expiry.strftime('%Y-%m-%d %H:%M:%S')}.
If you did not request this, please ignore it.
"""

        message = client.messages.create(
            from_=TWILIO_WHATSAPP_NUMBER,
            to=recipient,
            body=message_body.strip()
        )

        return jsonify({
            'status': 'success',
            'message': f'OTP sent via WhatsApp. {message_status}',
            'sid': message.sid,
            'otp': otp,  # ❗ For testing only – remove in production
            'expiry': expiry.strftime('%Y-%m-%d %H:%M:%S')
        }), 200

    except Error as db_err:
        return jsonify({'status': 'fail', 'message': 'Database error', 'error': str(db_err)}), 500
    except Exception as e:
        return jsonify({'status': 'fail', 'message': 'Failed to send OTP.', 'error': str(e)}), 500
    finally:
        if 'central_cursor' in locals(): central_cursor.close()
        if 'central_conn' in locals() and central_conn.is_connected(): central_conn.close()
        if 'school_cursor' in locals(): school_cursor.close()
        if 'school_conn' in locals() and school_conn.is_connected(): school_conn.close()

# ✅ Run the Flask server
if __name__ == '__main__':
    app.run(debug=True)