from flask import Flask, request, jsonify
from random import randint

import mysql.connector
from mysql.connector import Error
from datetime import datetime, timedelta

app = Flask(__name__)

# Configuration
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': ''
}

@app.route('/verify_phone', methods=['POST'])
def verify_phone():
    admin_id = request.form.get('admin_id', '').strip()
    phone = request.form.get('phone', '').strip()
    school_code = request.form.get('id', '').strip()

    # ✅ Input check
    if not admin_id or not phone or not school_code:
        return jsonify({'status': 'fail', 'message': 'Missing input'}), 200

    try:
        # ✅ 1. Connect to central DB
        central_conn = mysql.connector.connect(
            database='central_db', **db_config
        )
        central_cursor = central_conn.cursor(dictionary=True)

        # ✅ 2. Get school DB name
        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']

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

        # ✅ 4. Get 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

        stored_phone = user['phone'].strip()
        if stored_phone == phone:
            now = datetime.now()

            # ✅ Check if existing OTP is valid
            existing_otp = user['verification_code']
            existing_expiry = user['verification_expiry']

            if existing_otp and existing_expiry and existing_expiry > now:
                otp = existing_otp
                expiry = existing_expiry
                message = 'Phone matches. Reusing valid OTP.'
            else:
                otp = str(randint(100000, 999999))
                expiry = now + timedelta(minutes=10)

                # ✅ Update DB
                school_cursor.execute("""
                    UPDATE users SET verification_code = %s, verification_expiry = %s 
                    WHERE admin_id = %s
                """, (otp, expiry, admin_id))
                school_conn.commit()
                message = 'Phone matches. New OTP generated.'

            return jsonify({
                'status': 'success',
                'message': message,
                'otp': otp,
                'expiry': expiry.strftime('%Y-%m-%d %H:%M:%S')
            }), 200
        else:
            return jsonify({'status': 'fail', 'message': 'Phone does not match our records'}), 200

    except Error as e:
        return jsonify({
            'status': 'fail',
            'message': 'Database connection failed',
            'error': str(e)
        }), 200

    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()

# You must add this to run the app
if __name__ == '__main__':
    app.run(debug=True)