"""
Collnetwork WiFi Subscriber Portal
===================================
Flask app handling:
  - User registration & login
  - Paystack payment (₦3,000 / 30 days)
  - MikroTik RouterOS API integration
  - Automatic expiry management via APScheduler
"""

import os, hashlib, hmac, requests, logging, secrets, smtplib, csv, io
from flask import (Flask, render_template, request,
                   redirect, url_for, jsonify, session, flash, make_response)
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import datetime, timedelta
from dotenv import load_dotenv
from flask import (Flask, render_template, request,
                   redirect, url_for, jsonify, session, flash)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
from werkzeug.security import generate_password_hash, check_password_hash
from apscheduler.schedulers.background import BackgroundScheduler
import routeros_api


load_dotenv()

#  App & DB Setup 
app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'changeme')

app.config['SQLALCHEMY_DATABASE_URI'] = (
    f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASS')}"
    f"@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT', 3306)}/{os.getenv('DB_NAME')}"
)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Logging to file + console 
os.makedirs('/var/log/collnetwork', exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s %(levelname)s %(message)s',
    handlers=[
        logging.FileHandler('/var/log/collnetwork/app.log'),
        logging.StreamHandler()
    ]
)
log = logging.getLogger(__name__)


#  Constants 
PAYSTACK_SECRET      = os.getenv('PAYSTACK_SECRET_KEY')
PAYSTACK_PUBLIC      = os.getenv('PAYSTACK_PUBLIC_KEY')
AMOUNT_KOBO          = int(os.getenv('SUBSCRIPTION_AMOUNT', 300000))   # ₦3,000
SUBSCRIPTION_DAYS    = int(os.getenv('SUBSCRIPTION_DAYS', 30))
APP_URL              = os.getenv('APP_URL', 'https://wifiregister.collnetwork.net')

SMTP_HOST            = os.getenv('SMTP_HOST', 'smtp.gmail.com')
SMTP_PORT            = int(os.getenv('SMTP_PORT', 587))
SMTP_USER            = os.getenv('SMTP_USER')
SMTP_PASS            = os.getenv('SMTP_PASS')
SMTP_FROM            = os.getenv('SMTP_FROM', 'noreply@collnetwork.net')
RESET_TOKEN_EXPIRY   = 60  # minutes

ADMIN_USER           = os.getenv("ADMIN_USER", "admin")
ADMIN_PASS           = os.getenv("ADMIN_PASS", "changeme")

# Session expires when browser closes (no permanent cookie)
app.config["SESSION_PERMANENT"] = False

MT_HOST              = os.getenv('MIKROTIK_HOST')
MT_PORT              = int(os.getenv('MIKROTIK_PORT', 8728))
MT_USER              = os.getenv('MIKROTIK_USER')
MT_PASS              = os.getenv('MIKROTIK_PASS')
MT_PROFILE           = os.getenv('MIKROTIK_HOTSPOT_PROFILE', 'default')

ADMIN_USER           = os.getenv('ADMIN_USER', 'admin')
ADMIN_PASS           = os.getenv('ADMIN_PASS', 'Admin@Coll2025')
ADMIN_SESSION_HOURS  = int(os.getenv('ADMIN_SESSION_HOURS', 8))


# Admin Auth Decorator 
from functools import wraps
def admin_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not session.get('admin_logged_in'):
            flash('Please log in to access the admin panel.', 'error')
            return redirect(url_for('admin_login'))
        login_time = session.get('admin_login_time')
        if login_time:
            elapsed = datetime.utcnow() - datetime.fromisoformat(login_time)
            if elapsed > timedelta(hours=ADMIN_SESSION_HOURS):
                session.clear()
                flash('Session expired. Please log in again.', 'error')
                return redirect(url_for('admin_login'))
        return f(*args, **kwargs)
    return decorated



# Database Models

class Site(db.Model):
    __tablename__ = 'sites'

    id                  = db.Column(db.Integer, primary_key=True)
    name                = db.Column(db.String(100), nullable=False)
    slug                = db.Column(db.String(50),  nullable=False, unique=True)
    register_url        = db.Column(db.String(100), nullable=False)
    amount_kobo         = db.Column(db.Integer,     nullable=False, default=300000)
    commission_kobo     = db.Column(db.Integer,     nullable=False, default=0)
    subscription_days   = db.Column(db.Integer,     nullable=False, default=30)
    paystack_subaccount = db.Column(db.String(100), nullable=True)
    mikrotik_host       = db.Column(db.String(50),  nullable=False)
    mikrotik_port       = db.Column(db.Integer,     nullable=False, default=8728)
    mikrotik_user       = db.Column(db.String(50),  nullable=False)
    mikrotik_pass       = db.Column(db.String(100), nullable=False)
    mikrotik_profile    = db.Column(db.String(50),  nullable=False, default='default')
    active              = db.Column(db.Boolean,     nullable=False, default=True)
    created_at          = db.Column(db.DateTime,    default=datetime.utcnow)

    subscribers         = db.relationship('Subscriber', backref='site', lazy=True)

    def __repr__(self):
        return f'<Site {self.slug}>'
    
class AdminUser(db.Model):
    __tablename__ = 'admin_users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=True) # Null until they set it
    role = db.Column(db.String(50), default='admin') # 'super_admin' or 'admin'
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    site_id = db.Column(db.Integer, db.ForeignKey('sites.id'),
                      nullable=True)


    def set_password(self, pw):
        self.password_hash = generate_password_hash(pw)

    def check_password(self, pw):
        return check_password_hash(self.password_hash, pw)

class Subscriber(db.Model):
    __tablename__ = 'subscribers'
    id             = db.Column(db.Integer, primary_key=True)
    first_name     = db.Column(db.String(100), nullable=False)
    surname        = db.Column(db.String(100), nullable=False)
    email          = db.Column(db.String(255), unique=True, nullable=False)
    password_hash  = db.Column(db.String(255), nullable=False)
    phone          = db.Column(db.String(20))
    ip_address     = db.Column(db.String(45))
    payment_status = db.Column(db.Enum('active','expired','pending','failed'),
                               default='pending')
    paystack_ref   = db.Column(db.String(100))
    last_payment   = db.Column(db.DateTime)
    expiry_date    = db.Column(db.DateTime)
    mikrotik_added = db.Column(db.Boolean, default=False)
    created_at     = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at     = db.Column(db.DateTime, default=datetime.utcnow,
                               onupdate=datetime.utcnow)
    site_id = db.Column(db.Integer, db.ForeignKey('sites.id'),
                       nullable=False, default=1)

    def set_password(self, pw):
        self.password_hash = generate_password_hash(pw)

    def check_password(self, pw):
        return check_password_hash(self.password_hash, pw)

    def activate(self, days=None):
        now = datetime.utcnow()
        # Use the passed 'days' or fall back to the global constant
        days_to_add = days if days is not None else SUBSCRIPTION_DAYS
    
        base = self.expiry_date if (self.expiry_date and self.expiry_date > now) else now
        self.expiry_date = base + timedelta(days=days_to_add)
        self.last_payment = now
        self.payment_status = 'active'



    def __repr__(self):
        return f'<Subscriber {self.email}>'


class PasswordReset(db.Model):
    __tablename__ = 'password_resets'
    id         = db.Column(db.Integer, primary_key=True)
    email      = db.Column(db.String(255), nullable=False, index=True)
    token      = db.Column(db.String(128), unique=True, nullable=False)
    expires_at = db.Column(db.DateTime, nullable=False)
    used       = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def is_valid(self):
        return not self.used and datetime.utcnow() < self.expires_at


class PaymentLog(db.Model):
    __tablename__ = 'payment_logs'
    id               = db.Column(db.Integer, primary_key=True)
    subscriber_id    = db.Column(db.Integer, db.ForeignKey('subscribers.id'))
    paystack_ref     = db.Column(db.String(100))
    amount           = db.Column(db.Numeric(10, 2))
    status           = db.Column(db.String(50))
    gateway_response = db.Column(db.Text)
    created_at       = db.Column(db.DateTime, default=datetime.utcnow)




class TrialUser(db.Model):
    __tablename__ = 'trial_users'
    id         = db.Column(db.Integer, primary_key=True)
    mac        = db.Column(db.String(20), unique=True, nullable=False, index=True)
    username   = db.Column(db.String(100), nullable=False)
    ip_address = db.Column(db.String(45))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


#  MikroTik Helpers 


def get_mikrotik_api(site):
    """
    Return a connected routeros_api instance for the given site.
    Raises exception on failure — caller must handle.
    """
    api_pool = routeros_api.RouterOsApiPool(
        site.mikrotik_host,
        username    = site.mikrotik_user,
        password    = site.mikrotik_pass,
        port        = site.mikrotik_port,
        plaintext_login = True
    )
    api = api_pool.get_api()
    return api_pool, api


def mikrotik_add_user_for_site(site, email, password, full_name=''):
    """
    Add or update a hotspot user on the correct MikroTik for this site.
    Drop-in replacement for mikrotik_add_user() but site-aware.
    """
    api_pool = None
    try:
        api_pool, api = get_mikrotik_api(site)
        hotspot = api.get_resource('/ip/hotspot/user')

        # Check if user already exists
        all_users  = hotspot.get()
        existing   = [u for u in all_users if u.get('name') == email]

        if existing:
            hotspot.set(id=existing[0]['id'],
                        password=password,
                        comment=full_name)
            log.info(f"MikroTik [{site.slug}]: Updated {email}")
        else:
            hotspot.add(
                name     = email,
                password = password,
                profile  = site.mikrotik_profile,
                comment  = full_name
            )
            log.info(f"MikroTik [{site.slug}]: Added {email}")

        api_pool.disconnect()
        return True

    except Exception as e:
        log.error(f"MikroTik [{site.slug}]: Failed for {email}: {e}")
        if api_pool:
            try: api_pool.disconnect()
            except: pass
        return False


def mikrotik_remove_user_for_site(site, email):
    """Disable/remove expired user on the correct MikroTik."""
    api_pool = None
    try:
        api_pool, api = get_mikrotik_api(site)
        hotspot   = api.get_resource('/ip/hotspot/user')
        all_users = hotspot.get()
        existing  = [u for u in all_users if u.get('name') == email]

        if existing:
            hotspot.remove(id=existing[0]['id'])
            log.info(f"MikroTik [{site.slug}]: Removed {email}")

        api_pool.disconnect()
        return True

    except Exception as e:
        log.error(f"MikroTik [{site.slug}]: Remove failed for {email}: {e}")
        if api_pool:
            try: api_pool.disconnect()
            except: pass
        return False

def get_mikrotik_connection():
    """Open a stable RouterOS API connection with timeout handling."""
    try:
        # We create a pool but immediately get the api to ensure a fresh handshake
        connection = routeros_api.RouterOsApiPool(
            MT_HOST, 
            username=MT_USER, 
            password=MT_PASS,
            port=MT_PORT, 
            plaintext_login=True
        )
        return connection.get_api()
    except Exception as e:
        log.error(f"MikroTik connection failed: {e}")
        return None



def mikrotik_disable_user(email):
    """Disable a hotspot user on MikroTik (keeps record)."""
    api = get_mikrotik_connection()
    if not api:
        return False
    try:
        hotspot = api.get_resource('/ip/hotspot/user')
        existing = hotspot.get(name=email)
        if existing:
            hotspot.set(id=existing[0]['id'], disabled='yes')
            log.info(f"MikroTik: Disabled user {email}")
        return True
    except Exception as e:
        log.error(f"MikroTik disable user error: {e}")
        return False


def mikrotik_remove_active_session(email):
    """Kick active session for a user."""
    api = get_mikrotik_connection()
    if not api:
        return
    try:
        active = api.get_resource('/ip/hotspot/active')
        sessions = active.get(user=email)
        for s in sessions:
            active.remove(id=s['id'])
        log.info(f"MikroTik: Removed active session for {email}")
    except Exception as e:
        log.error(f"MikroTik remove session error: {e}")


def get_mikrotik_active_sessions():
    """Fetch all active hotspot sessions from MikroTik."""
    api = get_mikrotik_connection()
    if not api:
        return []
    try:
        active = api.get_resource('/ip/hotspot/active')
        return active.get()
    except Exception as e:
        log.error(f"MikroTik active sessions error: {e}")
        return []


def get_mikrotik_all_users():
    """Fetch all hotspot users from MikroTik."""
    api = get_mikrotik_connection()
    if not api:
        return []
    try:
        users = api.get_resource('/ip/hotspot/user')
        return users.get()
    except Exception as e:
        log.error(f"MikroTik users fetch error: {e}")
        return []


# Paystack Helpers
def paystack_initialize(email, amount, ref, callback_url, metadata=None):
    """Initialize a Paystack transaction."""
    headers = {'Authorization': f'Bearer {PAYSTACK_SECRET}',
               'Content-Type': 'application/json'}
    payload = {
        'email':        email,
        'amount':       amount,
        'reference':    ref,
        'callback_url': callback_url,
        'bearer':       'subaccount',
        'metadata':     metadata or {}
    }
    r = requests.post('https://api.paystack.co/transaction/initialize',
                      json=payload, headers=headers, timeout=15)
    return r.json()





def paystack_verify(ref):
    """Verify a Paystack transaction by reference."""
    headers = {'Authorization': f'Bearer {PAYSTACK_SECRET}'}
    r = requests.get(f'https://api.paystack.co/transaction/verify/{ref}',
                     headers=headers, timeout=15)
    return r.json()


def generate_ref(email):
    """Generate a unique Paystack reference."""
    ts = datetime.utcnow().strftime('%Y%m%d%H%M%S%f')
    return f"COLL-{ts}-{email[:6].upper().replace('@','AT')}"

def is_capacity_full():
    # List of emails that don't count towards the limit
    test_emails = ['collinks79@gmail.com', 'onyegbadocu@fuotuoke.edu.ng', 'collnetwork2@gmail.com', 'kosumuo@yahoo.com']
    
    # Count active subscribers who ARE NOT in the test list
    paying_active_count = Subscriber.query.filter(
        Subscriber.payment_status == 'active',
        ~Subscriber.email.in_(test_emails)
    ).count()
    
    return paying_active_count >= 400


#
@app.route('/')
def index():
    return redirect(url_for('register'))


@app.route('/register', methods=['GET'])
def register():
    if is_capacity_full():
        return render_template('register.html', 
            error="Portal at Capacity: We have reached the maximum of 400 active users. Please try again later.")
    return render_template('register.html',
                           paystack_public_key=PAYSTACK_PUBLIC)


@app.route('/submit', methods=['POST'])
def submit():
    """
    Handle registration form submission.
    1. Capture form data
    2. Check if user exists in DB
    3. If exists → renew: go straight to payment
    4. If new → create pending record, go to payment
    """
    first_name = request.form.get('first_name', '').strip()
    surname    = request.form.get('surname', '').strip()
    email      = request.form.get('email', '').strip().lower()
    password   = request.form.get('password', '').strip()
    phone      = request.form.get('phone', '').strip()
    ip_address = request.remote_addr

    # Basic validation
    if not all([first_name, surname, email, password]):
        flash('All fields are required.', 'error')
        return redirect(url_for('register'))

    # Store plain password temporarily in session (needed for MikroTik)
    # It is NEVER stored in DB — only the hash is.
    session['pending_password'] = password
    session['pending_email']    = email

    existing = Subscriber.query.filter_by(email=email).first()

    if existing:
        # Returning user — update details and go to payment
        existing.first_name  = first_name
        existing.surname     = surname
        existing.phone       = phone
        existing.ip_address  = ip_address
        existing.payment_status = 'pending'
        ref = generate_ref(email)
        existing.paystack_ref = ref
        db.session.commit()
        session['subscriber_id'] = existing.id
    else:
        # New user — create pending record
        sub = Subscriber(
            first_name=first_name,
            surname=surname,
            email=email,
            phone=phone,
            ip_address=ip_address,
            payment_status='pending'
        )
        sub.set_password(password)
        ref = generate_ref(email)
        sub.paystack_ref = ref
        db.session.add(sub)
        db.session.commit()
        session['subscriber_id'] = sub.id

    # Initialize Paystack
    callback = f"{APP_URL}/verify?ref={ref}"
    meta = {
        'subscriber_id': session['subscriber_id'],
        'first_name': first_name,
        'surname': surname,
        'custom_fields': [
            {'display_name': 'Plan', 'variable_name': 'plan',
             'value': '30-Day Internet Subscription'}
        ]
    }
    result = paystack_initialize(email, AMOUNT_KOBO, ref, callback, meta)

    if result.get('status') and result['data'].get('authorization_url'):
        return redirect(result['data']['authorization_url'])
    else:
        flash('Payment gateway error. Please try again.', 'error')
        log.error(f"Paystack init failed: {result}")
        return redirect(url_for('register'))


@app.route('/verify')
def verify():
    ref = request.args.get('ref') or request.args.get('reference')
    if not ref:
        flash('Invalid payment reference.', 'error')
        return redirect(url_for('register'))

    # FIX: Prevent duplicate logging if user refreshes the page
    existing_log = PaymentLog.query.filter_by(paystack_ref=ref).first()
    if existing_log and existing_log.status == 'success':
        sub = Subscriber.query.get(existing_log.subscriber_id)
        site = Site.query.get(sub.site_id) or Site.query.get(1)
        return render_template('nduth/success.html', subscriber=sub, site=site, now=datetime.utcnow())

    # Verify with Paystack
    result = paystack_verify(ref)
    if not (result.get('status') and
            result['data'].get('status') == 'success'):
        # Payment failed
        sub_id = session.get('subscriber_id')
        if sub_id:
            sub = Subscriber.query.get(sub_id)
            if sub:
                sub.payment_status = 'failed'
                db.session.add(PaymentLog(
                    subscriber_id    = sub.id,
                    paystack_ref     = ref,
                    amount           = (session.get('site_id') and
                                       Site.query.get(session['site_id']).amount_kobo or
                                       AMOUNT_KOBO) / 100,
                    status           = 'failed',
                    gateway_response = str(result)
                ))
                db.session.commit()
        return render_template('failed.html')
      # Payment succeeded — find subscriber
    sub = Subscriber.query.filter_by(paystack_ref=ref).first()
    if not sub:
        sub_id = session.get('subscriber_id')
        sub    = Subscriber.query.get(sub_id) if sub_id else None

    if not sub:
        flash('Subscriber not found. Contact support.', 'error')
        return redirect(url_for('register'))

    # Get site
    site = Site.query.get(sub.site_id) or Site.query.get(1)

    password = session.pop('pending_password', None)
    sub.activate(days=site.subscription_days)
    db.session.commit()

    # Log payment
    db.session.add(PaymentLog(
        subscriber_id    = sub.id,
        paystack_ref     = ref,
        amount           = site.amount_kobo / 100,
        status           = 'success',
        gateway_response = str(result['data'])
    ))
    db.session.commit()

    # Add to correct MikroTik
    hotspot_password = password or sub.email
    mt_ok = mikrotik_add_user_for_site(
        site, sub.email, hotspot_password,
        f"{sub.first_name} {sub.surname}"
    )
    sub.mikrotik_added = mt_ok
    db.session.commit()

    # Notify admin
    send_admin_notification(sub, site.amount_kobo / 100, ref)
    log.info(f"Payment complete [{site.slug}] for {sub.email}")

    # When rendering the template, ensure now is passed correctly
    if site.slug == 'nduth':
        return render_template('nduth/success.html', subscriber=sub, site=site, now=datetime.utcnow())
    return render_template('success.html', subscriber=sub, now=datetime.utcnow())


@app.route('/paystack/webhook', methods=['POST'])
def paystack_webhook():
    sig       = request.headers.get('X-Paystack-Signature', '')
    body      = request.get_data()
    expected  = hmac.new(
        PAYSTACK_SECRET.encode(), body, hashlib.sha512
    ).hexdigest()

    if sig != expected:
        log.warning("Webhook: invalid signature")
        return jsonify({'status': 'invalid'}), 400

    event = request.get_json(silent=True) or {}
    log.info(f"Webhook event: {event.get('event')}")

    if event.get('event') == 'charge.success':
        data = event.get('data', {})
        ref  = data.get('reference')
        if not ref:
            return jsonify({'status': 'ok'}), 200

        sub = Subscriber.query.filter_by(paystack_ref=ref).first()
        if sub and sub.payment_status != 'active':
            site = Site.query.get(sub.site_id) or Site.query.get(1)

            sub.activate(days=site.subscription_days)
            db.session.commit()

            if not sub.mikrotik_added:
                mt_ok = mikrotik_add_user_for_site(
                    site, sub.email, sub.email,
                    f"{sub.first_name} {sub.surname}"
                )
                sub.mikrotik_added = mt_ok
                db.session.commit()
                log.info(f"Webhook: MikroTik updated [{site.slug}] for {sub.email}")

            send_admin_notification(sub, site.amount_kobo / 100, ref)

    return jsonify({'status': 'ok'}), 200


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        email    = request.form.get('email', '').lower().strip()
        password = request.form.get('password', '')
        sub = Subscriber.query.filter_by(email=email).first()
        if sub and sub.check_password(password):
            session['subscriber_id'] = sub.id
            return render_template('success.html', subscriber=sub)
        flash('Invalid email or password.', 'error')
    return render_template('login.html')


@app.route('/status')
def status():
    sub_id = session.get('subscriber_id')
    if not sub_id:
        return redirect(url_for('login'))
    sub = Subscriber.query.get(sub_id)
    return render_template('success.html', subscriber=sub)



def paystack_initialize_for_site(site, email, ref):
    """
    Initialize a Paystack payment for the given site.
    Automatically includes subaccount + split if site has one.
    """
    payload = {
        'email':     email,
        'amount':    site.amount_kobo,
        'reference': ref,
        'callback_url': f"{APP_URL}/verify",
        'metadata': {
            'site_id':   site.id,
            'site_slug': site.slug
        }
    }

    if site.paystack_subaccount:
        payload['subaccount']          = site.paystack_subaccount
        payload['bearer']              = 'subaccount'  # subaccount bears Paystack fees
        payload['transaction_charge']  = site.commission_kobo  # your cut in kobo

    headers = {
        'Authorization': f'Bearer {PAYSTACK_SECRET}',
        'Content-Type':  'application/json'
    }

    r = requests.post(
        'https://api.paystack.co/transaction/initialize',
        json=payload, headers=headers, timeout=10
    )
    return r.json()


# ── NDUTH registration page ───────────────────────────────────

@app.route('/nduth/register', methods=['GET', 'POST'])
def nduth_register():
    site = Site.query.filter_by(slug='nduth', active=True).first_or_404()

    if request.method == 'GET':
        return render_template('nduth/register.html', site=site)

    # POST — same logic as Otuoke but uses site object
    first_name = request.form.get('first_name', '').strip()
    surname    = request.form.get('surname', '').strip()
    email      = request.form.get('email', '').strip().lower()
    phone      = request.form.get('phone', '').strip()
    password   = request.form.get('password', '').strip()
    confirm    = request.form.get('confirm_password', '').strip()

    # Validation
    errors = []
    if not all([first_name, surname, email, phone, password]):
        errors.append('All fields are required.')
    if password != confirm:
        errors.append('Passwords do not match.')
    if len(password) < 6:
        errors.append('Password must be at least 6 characters.')
    if errors:
        for e in errors:
            flash(e, 'error')
        return render_template('nduth/register.html', site=site,
                               first_name=first_name, surname=surname,
                               email=email, phone=phone)

    # Check existing subscriber for this site
    existing = Subscriber.query.filter_by(email=email, site_id=site.id).first()
    if existing:
        if existing.payment_status == 'active':
            flash('You already have an active subscription. Please log in.', 'info')
            return redirect(url_for('nduth_login'))
        # Re-use existing record for retry
        sub = existing
    else:
        # New subscriber
        sub = Subscriber(
            first_name = first_name,
            surname    = surname,
            email      = email,
            phone      = phone,
            site_id    = site.id
        )
        db.session.add(sub)

    sub.set_password(password)

    # Generate unique Paystack reference
    ref = f"NDUTH-{datetime.utcnow().strftime('%Y%m%d%H%M%S%f')[:20]}-" \
          f"{surname[:6].upper()}"
    sub.paystack_ref    = ref
    sub.payment_status  = 'pending'
    db.session.commit()

    # Store in session for verify route
    session['subscriber_id']   = sub.id
    session['pending_password'] = password
    session['site_id']          = site.id

    # Initialize Paystack payment
    result = paystack_initialize_for_site(site, email, ref)

    if result.get('status'):
        payment_url = result['data']['authorization_url']
        log.info(f"NDUTH payment initialized for {email} ref={ref}")
        return redirect(payment_url)
    else:
        flash('Payment initialization failed. Please try again.', 'error')
        log.error(f"NDUTH Paystack init failed for {email}: {result}")
        return render_template('nduth/register.html', site=site)


# ── NDUTH login ───────────────────────────────────────────────

@app.route('/nduth/login', methods=['GET', 'POST'])
def nduth_login():
    # Catch router variables from the URL
    mac = request.args.get('mac')
    dst = request.args.get('linkorig')
    
    site = Site.query.filter_by(slug='nduth').first_or_404()
    
    if request.method == 'POST':
        email = request.form.get('email', '').strip().lower()
        password = request.form.get('password', '')
        sub = Subscriber.query.filter_by(email=email, site_id=site.id).first()

        if sub and sub.check_password(password):
            session['subscriber_id'] = sub.id
            # Temporarily store password to pass to the MikroTik auto-login form
            session['temp_pass'] = password 
            session['dst_link'] = dst
            return redirect(url_for('nduth_dashboard'))
        
        flash('Invalid NDUTH credentials.', 'error')
    
    return render_template('nduth/login.html', site=site)




# ── NDUTH Dashboard ───────────────────────────────────────────────
@app.route('/nduth/dashboard')
def nduth_dashboard():
    sub_id = session.get('subscriber_id')
    if not sub_id: return redirect(url_for('nduth_login'))

    sub = Subscriber.query.get(sub_id)
    site = Site.query.filter_by(slug='nduth').first()
    
    return render_template('nduth/dashboard.html', 
                           subscriber=sub, 
                           site=site,
                           sub_plain_password=session.get('temp_pass'),
                           dst_link=session.get('dst_link'))


# ── NDUTH forgot password ─────────────────────────────────────

@app.route('/nduth/forgot-password', methods=['GET', 'POST'])
def nduth_forgot_password():
    site = Site.query.filter_by(slug='nduth', active=True).first_or_404()

    if request.method == 'POST':
        email = request.form.get('email', '').strip().lower()
        log.info(f"NDUTH forgot password request for: {email}")

        sub = Subscriber.query.filter_by(email=email, site_id=site.id).first()

        if not sub:
            flash('No NDUTH account found with that email address.', 'error')
            return redirect(url_for('nduth_forgot_password'))

        PasswordReset.query.filter_by(email=email, used=False).delete()
        db.session.commit()

        token = secrets.token_urlsafe(48)
        reset = PasswordReset(
            email      = email,
            token      = token,
            expires_at = datetime.utcnow() + timedelta(minutes=RESET_TOKEN_EXPIRY)
        )
        db.session.add(reset)
        db.session.commit()

        reset_url = f"{APP_URL}/nduth/reset-password/{token}"
        send_reset_email(email, reset_url, sub.first_name)

        # Open walled garden for email provider
        domains_to_add, tag_domain = get_domains_for_email(email)
        walled_garden_add_domains_for_site(site, domains_to_add, tag_domain)

        flash(
            f'A reset link has been sent to {email}. '
            'You can now open your email app to find it.',
            'success'
        )
        return redirect(url_for('nduth_forgot_password'))

    return render_template('nduth/forgot_password.html', site=site)


# ══════════════════════════════════════════════════════════════
# NDUTH DASHBOARD ROUTE
# ══════════════════════════════════════════════════════════════



#Email Helper
def send_admin_invitation_email(to_email, invite_url):
    """Send a professional invitation email for new administrators."""
    if not SMTP_USER or not SMTP_PASS:
        log.error("SMTP credentials not configured for admin invitation")
        return False
    try:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = '🚀 Invitation: Activate Your Collnetwork Admin Account'
        msg['From']    = f'Collnetwork Admin Portal <{SMTP_FROM}>'
        msg['To']      = to_email

        html_body = f"""
        <div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;background:#f8fafc;
                    border-radius:16px;overflow:hidden;border:1px solid #e2e8f0;">
          <div style="background:linear-gradient(135deg,#1e1b4b,#4338ca);padding:28px 32px;text-align:center;">
            <h1 style="color:white;margin:0;font-size:22px;">Collnetwork Technology LTD</h1>
            <p style="color:rgba(255,255,255,0.8);margin:6px 0 0;font-size:13px;">Administrator Access Invitation</p>
          </div>
          <div style="padding:32px; background: white;">
            <p style="color:#1e293b;font-size:16px;">Hello,</p>
            <p style="color:#475569;font-size:14px;line-height:1.6;margin:12px 0;">
              You have been invited as an <strong>Administrator</strong> for the Collnetwork WiFi Subscriber Portal. 
              This account allows you to manage subscribers, view sessions, and monitor payments.
            </p>
            <p style="color:#475569;font-size:14px;line-height:1.6;">
              Please click the button below to set up your secure password and activate your dashboard access.
            </p>
            <div style="text-align:center;margin:30px 0;">
              <a href="{invite_url}"
                 style="background:#4338ca; color:white; padding:14px 32px; border-radius:10px; 
                        text-decoration:none; font-size:15px; font-weight:700; display:inline-block;">
                🔐 Activate My Admin Account
              </a>
            </div>
            <p style="color:#94a3b8;font-size:12px;line-height:1.6; border-top: 1px solid #f1f5f9; padding-top: 20px;">
              This invitation link is secure and will expire in 24 hours. If you were not expecting this invitation, 
              please contact the Principal Network Administrator.
            </p>
          </div>
          <div style="background:#f1f5f9;padding:14px 32px;text-align:center;">
            <p style="color:#64748b;font-size:12px;margin:0;">
              © 2026 Collnetwork Technology LTD — Administrator Portal
            </p>
          </div>
        </div>
        """
        msg.attach(MIMEText(html_body, 'html'))

        with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.sendmail(SMTP_FROM, to_email, msg.as_string())

        log.info(f"Admin invitation sent to {to_email}")
        return True
    except Exception as e:
        log.error(f"Admin invitation failed: {e}")
        return False

# Add this to your constants
TELEGRAM_TOKEN = os.getenv('TELEGRAM_TOKEN')
TELEGRAM_ADMIN_ID = os.getenv('TELEGRAM_ADMIN_ID')
def notify_admin(message):
    """Send alert to Telegram bot."""
    if not TELEGRAM_TOKEN or not TELEGRAM_ADMIN_ID:
        log.warning("Telegram credentials missing in .env")
        return
    url = f"https://api.telegram.org/bot{TELEGRAM_TOKEN}/sendMessage"
    payload = {
        "chat_id": TELEGRAM_ADMIN_ID,
        "text": f"\U0001f4e1 <b>COLLNETWORK TECHNOLOGY LTD</b>\n{'─'*24}\n{message}",
        "parse_mode": "HTML"
    }
    try:
        r = requests.post(url, json=payload, timeout=5)
        if r.status_code != 200:
            log.error(f"Telegram API Error: {r.text}")
        else:
            log.info("Telegram notification sent")
    except Exception as e:
        log.error(f"Telegram notification failed: {e}")




def send_admin_notification(subscriber, amount, ref):
    """Send payment notification email to admin + Telegram alert."""

    # ── Telegram alert (fast, independent of email) ───────────
    notify_admin(
        f"💰 <b>New Payment Received</b>\n"
        f"Name:    {subscriber.first_name} {subscriber.surname}\n"
        f"Email:   {subscriber.email}\n"
        f"Phone:   {subscriber.phone or '—'}\n"
        f"Amount:  ₦{amount:,.2f}\n"
        f"Expiry:  {subscriber.expiry_date.strftime('%d %b %Y') if subscriber.expiry_date else '—'}\n"
        f"Ref:     {ref}"
    )

    # ── Email notification ────────────────────────────────────
    if not SMTP_USER or not SMTP_PASS:
        return
    admin_email    = os.getenv('ADMIN_EMAIL')
    admin_email_cc = os.getenv('ADMIN_EMAIL_CC', '')
    if not admin_email:
        log.warning("ADMIN_EMAIL not set in .env — skipping email notification")
        return
    try:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = f'💰 New Payment – {subscriber.first_name} {subscriber.surname}'
        msg['From']    = f'Collnetwork WiFi Portal <{SMTP_FROM}>'
        msg['To']      = admin_email
        if admin_email_cc:
            msg['Cc'] = admin_email_cc

        html_body = f"""
        <div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;
                    background:#f3eeff;border-radius:16px;overflow:hidden;">
          <div style="background:linear-gradient(135deg,#3b1f6e,#6b3fa0);
                      padding:24px 32px;text-align:center;">
            <h1 style="color:white;margin:0;font-size:20px;">💰 New Subscription Payment</h1>
            <p style="color:rgba(255,255,255,0.8);margin:6px 0 0;font-size:13px;">
              Collnetwork WiFi Portal
            </p>
          </div>
          <div style="padding:28px 32px;">
            <table style="width:100%;border-collapse:collapse;">
              <tr style="border-bottom:1px solid #e2d9f5;">
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Subscriber</td>
                <td style="padding:10px 0;color:#1a0a3c;font-size:14px;font-weight:700;text-align:right;">
                  {subscriber.first_name} {subscriber.surname}
                </td>
              </tr>
              <tr style="border-bottom:1px solid #e2d9f5;">
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Email</td>
                <td style="padding:10px 0;color:#1a0a3c;font-size:13px;text-align:right;">
                  {subscriber.email}
                </td>
              </tr>
              <tr style="border-bottom:1px solid #e2d9f5;">
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Phone</td>
                <td style="padding:10px 0;color:#1a0a3c;font-size:13px;text-align:right;">
                  {subscriber.phone or '—'}
                </td>
              </tr>
              <tr style="border-bottom:1px solid #e2d9f5;">
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Amount Paid</td>
                <td style="padding:10px 0;font-size:16px;font-weight:700;color:#16a34a;text-align:right;">
                  ₦{amount:,.2f}
                </td>
              </tr>
              <tr style="border-bottom:1px solid #e2d9f5;">
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Expiry Date</td>
                <td style="padding:10px 0;color:#1a0a3c;font-size:13px;text-align:right;">
                  {subscriber.expiry_date.strftime('%d %b %Y') if subscriber.expiry_date else '—'}
                </td>
              </tr>
              <tr>
                <td style="padding:10px 0;color:#7c6fa0;font-size:13px;font-weight:700;">Reference</td>
                <td style="padding:10px 0;color:#6b3fa0;font-size:11px;
                           font-family:monospace;text-align:right;">
                  {ref}
                </td>
              </tr>
            </table>
            <div style="margin-top:24px;text-align:center;">
              <a href="{APP_URL}/admin/subscribers"
                 style="background:linear-gradient(135deg,#6b3fa0,#3b1f6e);color:white;
                        padding:12px 28px;border-radius:10px;text-decoration:none;
                        font-size:14px;font-weight:700;display:inline-block;">
                View in Admin Panel →
              </a>
            </div>
          </div>
          <div style="background:#3b1f6e;padding:12px 32px;text-align:center;">
            <p style="color:rgba(255,255,255,0.6);font-size:12px;margin:0;">
              © 2026 Collnetwork Technology LTD
            </p>
          </div>
        </div>
        """

        plain_body = (
            f"New payment received!\n\n"
            f"Name:      {subscriber.first_name} {subscriber.surname}\n"
            f"Email:     {subscriber.email}\n"
            f"Phone:     {subscriber.phone or '—'}\n"
            f"Amount:    ₦{amount:,.2f}\n"
            f"Expiry:    {subscriber.expiry_date.strftime('%d %b %Y') if subscriber.expiry_date else '—'}\n"
            f"Reference: {ref}\n\n"
            f"View admin panel: {APP_URL}/admin/subscribers"
        )

        msg.attach(MIMEText(plain_body, 'plain'))
        msg.attach(MIMEText(html_body, 'html'))

        try:
            with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
                server.starttls()
                server.login(SMTP_USER, SMTP_PASS)
                recipients = [admin_email]
                if admin_email_cc:
                    recipients.append(admin_email_cc)
                server.sendmail(SMTP_FROM, recipients, msg.as_string())
        except smtplib.SMTPServerDisconnected:
            pass  # Exim sends 250 after QUIT — email was delivered

        log.info(f"Admin notification sent for payment by {subscriber.email}")

    except Exception as e:
        log.error(f"Admin notification email failed: {e}")




# ── Walled Garden Helpers ─────────────────────────────────────

# ── FIX 1: send_reset_email ───────────────────────────────────
# Some SMTP servers (including Exim/cPanel) send a 250 reply after
# the QUIT command which smtplib misreads as an exception.
# Catch SMTPServerDisconnected separately and treat 250 as success.

def send_reset_email(to_email, reset_url, first_name):
    """Send password reset email via SMTP."""
    if not SMTP_USER or not SMTP_PASS:
        log.error("SMTP credentials not configured in .env")
        return False
    try:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = 'Reset Your Collnetwork Password'
        msg['From']    = f'Collnetwork Technology LTD <{SMTP_FROM}>'
        msg['To']      = to_email

        html_body = f"""
        <div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;background:#f3eeff;
                    border-radius:16px;overflow:hidden;">
          <div style="background:linear-gradient(135deg,#3b1f6e,#6b3fa0);padding:28px 32px;text-align:center;">
            <h1 style="color:white;margin:0;font-size:22px;">Collnetwork Technology LTD</h1>
            <p style="color:rgba(255,255,255,0.8);margin:6px 0 0;font-size:13px;">Password Reset Request</p>
          </div>
          <div style="padding:32px;">
            <p style="color:#1a0a3c;font-size:15px;">Hi <strong>{first_name}</strong>,</p>
            <p style="color:#4a5568;font-size:14px;line-height:1.6;margin:12px 0;">
              We received a request to reset your Collnetwork WiFi password.
              Click the button below to set a new password. This link expires in
              <strong>{RESET_TOKEN_EXPIRY} minutes</strong>.
            </p>
            <div style="text-align:center;margin:28px 0;">
              <a href="{reset_url}"
                 style="background:linear-gradient(135deg,#22c55e,#16a34a);color:white;
                        padding:14px 32px;border-radius:10px;text-decoration:none;
                        font-size:15px;font-weight:700;display:inline-block;">
                🔑 Reset My Password
              </a>
            </div>
            <p style="color:#718096;font-size:12px;line-height:1.6;">
              If you did not request a password reset, you can safely ignore this email.
              Your password will remain unchanged.<br><br>
              This link will expire at
              <strong>{(datetime.utcnow() + timedelta(minutes=RESET_TOKEN_EXPIRY)).strftime('%H:%M UTC')}</strong>.
            </p>
          </div>
          <div style="background:#3b1f6e;padding:14px 32px;text-align:center;">
            <p style="color:rgba(255,255,255,0.6);font-size:12px;margin:0;">
              © 2026 Collnetwork Technology LTD
            </p>
          </div>
        </div>
        """

        plain_body = (
            f"Hi {first_name},\n\nReset your Collnetwork WiFi password here:\n{reset_url}\n\n"
            f"This link expires in {RESET_TOKEN_EXPIRY} minutes.\n\n"
            "If you didn't request this, ignore this email."
        )

        msg.attach(MIMEText(plain_body, 'plain'))
        msg.attach(MIMEText(html_body, 'html'))

        try:
            with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
                server.starttls()
                server.login(SMTP_USER, SMTP_PASS)
                server.sendmail(SMTP_FROM, to_email, msg.as_string())
        except smtplib.SMTPServerDisconnected:
            # Exim/cPanel sends 250 after QUIT — smtplib raises this
            # but the email was already delivered successfully. Ignore it.
            pass

        log.info(f"Reset email sent to {to_email}")
        return True

    except Exception as e:
        log.error(f"Email send failed: {e}")
        return False

# ── Mail provider domain map ──────────────────────────────────
# For major providers, we need to whitelist more than just *.domain
# because their login pages load from different domains.
# For unknown/custom domains (e.g. fuotuoke.edu.ng), *.domain is enough.

MAIL_PROVIDER_DOMAINS = {
    'gmail.com':      ['*.google.com', '*.googleapis.com', '*.gstatic.com',
                       '*.googleusercontent.com', 'accounts.google.com',
                       'mail.google.com'],
    'googlemail.com': ['*.google.com', '*.googleapis.com', '*.gstatic.com',
                       'accounts.google.com', 'mail.google.com'],
    'yahoo.com':      ['*.yahoo.com', '*.yimg.com', '*.yahooapis.com',
                       'login.yahoo.com', 'mail.yahoo.com'],
    'yahoo.co.uk':    ['*.yahoo.com', '*.yimg.com', 'mail.yahoo.com'],
    'ymail.com':      ['*.yahoo.com', '*.yimg.com', 'mail.yahoo.com'],
    'outlook.com':    ['*.microsoft.com', '*.live.com', '*.office.com',
                       '*.microsoftonline.com', 'outlook.live.com',
                       'login.microsoftonline.com', '*.office365.com'],
    'hotmail.com':    ['*.microsoft.com', '*.live.com', 'outlook.live.com',
                       'login.microsoftonline.com'],
    'live.com':       ['*.microsoft.com', '*.live.com', 'outlook.live.com',
                       'login.microsoftonline.com'],
    'icloud.com':     ['*.apple.com', '*.icloud.com', 'appleid.apple.com',
                       'idmsa.apple.com'],
    'me.com':         ['*.apple.com', '*.icloud.com', 'appleid.apple.com'],
}


def get_domains_for_email(email):
    """
    Return the list of walled garden domains to whitelist for this email.
    Known providers → use the full domain list.
    Unknown/custom domains → just whitelist *.domain (their own mail server).
    """
    domain = email.split('@')[-1].lower()
    if domain in MAIL_PROVIDER_DOMAINS:
        return MAIL_PROVIDER_DOMAINS[domain], domain
    else:
        # Custom domain e.g. fuotuoke.edu.ng — just open *.fuotuoke.edu.ng
        return [f'*.{domain}'], domain


def walled_garden_add_domains(domains, tag_domain):
    """
    Add a list of domains to MikroTik walled garden.
    All entries are tagged with comment pwd-reset:<tag_domain>
    so they can all be removed together after the reset.
    """
    api_pool = None
    added = []
    try:
        api_pool = routeros_api.RouterOsApiPool(
            MT_HOST, username=MT_USER, password=MT_PASS,
            port=MT_PORT, plaintext_login=True
        )
        api = api_pool.get_api()
        wg = api.get_resource('/ip/hotspot/walled-garden')

        # Fetch existing entries once
        try:
            all_entries = wg.get()
            existing_hosts = {e.get('dst-host') for e in all_entries}
        except Exception:
            existing_hosts = set()

        for host in domains:
            if host in existing_hosts:
                log.info(f"Walled garden: {host} already exists, skipping")
                continue
            try:
                wg.add(
                    dst_host=host,
                    action='allow',
                    comment=f'pwd-reset:{tag_domain}'
                )
                added.append(host)
                log.info(f"Walled garden: added {host} (tag={tag_domain})")
            except Exception as e:
                log.error(f"Walled garden: failed to add {host}: {e}")

        api_pool.disconnect()
        log.info(f"Walled garden: added {len(added)} entries for {tag_domain}")
        return True

    except Exception as e:
        log.error(f"Walled garden add_domains FAILED for {tag_domain}: {e}")
        if api_pool:
            try: api_pool.disconnect()
            except: pass
        return False


def walled_garden_remove_domain(domain):
    """
    Remove ALL walled garden entries tagged pwd-reset:<domain>.
    Handles multi-entry providers like Gmail (removes all Google domains at once).
    """
    api_pool = None
    try:
        api_pool = routeros_api.RouterOsApiPool(
            MT_HOST, username=MT_USER, password=MT_PASS,
            port=MT_PORT, plaintext_login=True
        )
        api = api_pool.get_api()
        wg = api.get_resource('/ip/hotspot/walled-garden')

        all_entries = wg.get()
        to_remove = [
            e for e in all_entries
            if e.get('comment', '') == f'pwd-reset:{domain}'
        ]

        if not to_remove:
            log.warning(f"Walled garden: no entries found for pwd-reset:{domain}")
        else:
            for entry in to_remove:
                wg.remove(id=entry['id'])
                log.info(f"Walled garden: removed {entry.get('dst-host')} (tag={domain})")

        api_pool.disconnect()
        return True

    except Exception as e:
        log.error(f"Walled garden remove FAILED for {domain}: {e}")
        if api_pool:
            try: api_pool.disconnect()
            except: pass
        return False


def walled_garden_cleanup_expired():
    """
    Runs every hour via scheduler.
    Removes temp walled garden entries whose reset tokens have expired or been used.
    """
    api_pool = None
    log.info("Walled garden cleanup: starting ...")
    try:
        api_pool = routeros_api.RouterOsApiPool(
            MT_HOST, username=MT_USER, password=MT_PASS,
            port=MT_PORT, plaintext_login=True
        )
        api = api_pool.get_api()
        wg = api.get_resource('/ip/hotspot/walled-garden')

        all_entries = wg.get()
        # Find unique tag domains from temp entries
        tag_domains = set(
            e.get('comment', '').replace('pwd-reset:', '')
            for e in all_entries
            if e.get('comment', '').startswith('pwd-reset:')
        )

        log.info(f"Walled garden cleanup: found {len(tag_domains)} tagged domain(s)")

        with app.app_context():
            for domain in tag_domains:
                active_reset = PasswordReset.query.filter(
                    PasswordReset.email.like(f'%@{domain}'),
                    PasswordReset.used == False,
                    PasswordReset.expires_at > datetime.utcnow()
                ).first()

                if not active_reset:
                    # Remove all entries for this tag domain
                    to_remove = [
                        e for e in all_entries
                        if e.get('comment', '') == f'pwd-reset:{domain}'
                    ]
                    for entry in to_remove:
                        wg.remove(id=entry['id'])
                        log.info(f"Walled garden cleanup: removed {entry.get('dst-host')}")
                else:
                    log.info(f"Walled garden cleanup: keeping {domain} (active token exists)")

        api_pool.disconnect()
        log.info("Walled garden cleanup: done")

    except Exception as e:
        log.error(f"Walled garden cleanup FAILED: {e}")
        if api_pool:
            try: api_pool.disconnect()
            except: pass


def get_admin_site_filter():
    admin_id = session.get('admin_id')
    if not admin_id:
        # Fallback: look up by email for existing sessions
        admin_email = session.get('admin_user')
        admin = AdminUser.query.filter_by(email=admin_email).first() if admin_email else None
    else:
        admin = AdminUser.query.get(admin_id)

    if not admin:
        return None, None

    if admin.site_id is None:
        return admin, None       # super admin — sees all
    return admin, admin.site_id  # site admin — scoped

# ── Updated forgot_password route ────────────────────────────
@app.route('/forgot-password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        email = request.form.get('email', '').strip().lower()
        log.info(f"Forgot password request for: {email}")

        sub = Subscriber.query.filter_by(email=email).first()

        if not sub:
            # Issue 2 FIX: Tell the user their email was not found
            # (acceptable here since this is a paid WiFi portal —
            # not a security-sensitive app where enumeration is a risk)
            log.warning(f"Forgot password: no subscriber found for {email}")
            flash(
                'No account found with that email address. '
                'Please check the email you used during registration, '
                'or contact support.',
                'error'
            )
            return redirect(url_for('forgot_password'))

        # Valid subscriber — generate reset token
        PasswordReset.query.filter_by(email=email, used=False).delete()
        db.session.commit()

        token = secrets.token_urlsafe(48)
        reset = PasswordReset(
            email      = email,
            token      = token,
            expires_at = datetime.utcnow() + timedelta(minutes=RESET_TOKEN_EXPIRY)
        )
        db.session.add(reset)
        db.session.commit()
        log.info(f"Reset token saved to DB for {email}")

        reset_url = f"{APP_URL}/reset-password/{token}"
        email_ok = send_reset_email(email, reset_url, sub.first_name)
        log.info(f"Reset email {'sent' if email_ok else 'FAILED'} for {email}")

        # Open walled garden for this user's mail provider
        domains_to_add, tag_domain = get_domains_for_email(email)
        log.info(f"Walled garden: opening {len(domains_to_add)} domain(s) for {tag_domain}")
        walled_garden_add_domains(domains_to_add, tag_domain)

        flash(
            f'A password reset link has been sent to {email}. '
            f'You can now open your email app to find it.',
            'success'
        )
        return redirect(url_for('forgot_password'))

    return render_template('forgot_password.html')


# ── Updated reset_password route ─────────────────────────────
@app.route('/reset-password/<token>', methods=['GET', 'POST'])
def reset_password(token):
    reset = PasswordReset.query.filter_by(token=token).first()

    if not reset or not reset.is_valid():
        flash('This reset link is invalid or has expired. Please request a new one.', 'error')
        return redirect(url_for('forgot_password'))

    if request.method == 'POST':
        password = request.form.get('password', '').strip()
        confirm  = request.form.get('confirm_password', '').strip()

        if len(password) < 6:
            flash('Password must be at least 6 characters.', 'error')
            return render_template('reset_password.html', token=token)

        if password != confirm:
            flash('Passwords do not match.', 'error')
            return render_template('reset_password.html', token=token)

        sub = Subscriber.query.filter_by(email=reset.email).first()
        if not sub:
            flash('Account not found. Please register.', 'error')
            return redirect(url_for('register'))

        sub.set_password(password)
        mikrotik_add_user(sub.email, password)

        reset.used = True
        db.session.commit()

        # Close walled garden — remove all domains added for this email
        _, tag_domain = get_domains_for_email(reset.email)
        walled_garden_remove_domain(tag_domain)
        log.info(f"Walled garden: closed for {tag_domain} after reset by {reset.email}")

        log.info(f"Password reset complete for {sub.email}")
        flash('Password updated successfully! You can now log in to the WiFi.', 'success')
        return redirect(url_for('login'))

    return render_template('reset_password.html', token=token)


# Trial Routes 
@app.route('/trial')
def trial():
    """
    MikroTik calls this with ?mac=XX:XX:XX:XX:XX:XX in the URL.
    1. Check if MAC already used a trial
    2. If yes → show already_used page
    3. If no  → create MikroTik user with trial profile, save to DB, show credentials
    """
    mac = request.args.get('mac', '').strip().upper()
    ip  = request.args.get('ip', request.remote_addr)

    if not mac:
        return render_template('trial_no_mac.html')

    # Check if already used
    existing = TrialUser.query.filter_by(mac=mac).first()
    if existing:
        return render_template('trial_used.html', created_at=existing.created_at)

    # Generate unique trial credentials
    short_id  = secrets.token_hex(3).upper()           # e.g. A1B2C3
    username  = f"trial-{short_id}"
    password  = secrets.token_hex(4)                    # e.g. a1b2c3d4

    # Add to MikroTik with trial profile + 1hr timeout
    api = get_mikrotik_connection()
    if not api:
        return render_template('trial_error.html',
                               message="Service temporarily unavailable. Please try again.")
    try:
        hotspot = api.get_resource('/ip/hotspot/user')
        hotspot.add(
            name=username,
            password=password,
            profile='trial',
            comment=f'Trial user - MAC:{mac}',
            disabled='no'
        )
        log.info(f"Trial user created: {username} for MAC {mac}")
    except Exception as e:
        log.error(f"Trial MikroTik error: {e}")
        return render_template('trial_error.html',
                               message="Could not create trial account. Please try again.")

    # Save to DB
    trial_record = TrialUser(mac=mac, username=username, ip_address=ip)
    db.session.add(trial_record)
    db.session.commit()

    return render_template('trial_success.html',
                           username=username,
                           password=password,
                           duration='1 Hour')



# ══════════════════════════════════════════════════════════════
# ── ADMIN PANEL ROUTES ────────────────────────────────────────
# ══════════════════════════════════════════════════════════════

@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    if session.get('admin_logged_in'):
        return redirect(url_for('admin_dashboard'))
        
    if request.method == 'POST':
        # 1. Get credentials from the form
        email_input = request.form.get('username', '').strip().lower() # Using 'username' field for email
        password_input = request.form.get('password', '').strip()
        
        # 2. Query the new admin_users table
        admin = AdminUser.query.filter_by(email=email_input).first()
        
        # 3. Verify the admin exists and the password matches the hash
        if admin and admin.check_password(password_input):
            session['admin_logged_in'] = True
            session['admin_user']       = admin.email
            session['admin_role']       = admin.role
            session['admin_id']         = admin.id   # ← ADD THIS
            session['admin_login_time'] = datetime.utcnow().isoformat()
            
            log.info(f"Admin login success: {admin.email} (Role: {admin.role}) from {request.remote_addr}")
            return redirect(url_for('admin_dashboard'))
        
        # If we reach here, login failed
        log.warning(f"Failed admin login attempt for {email_input} from {request.remote_addr}")
        flash('Invalid email or password.', 'error')
        
    return render_template('admin/login.html')

@app.route('/admin/logout')
def admin_logout():
    session.pop('admin_logged_in', None)
    session.pop('admin_user', None)
    flash('You have been logged out.', 'success')
    return redirect(url_for('admin_login'))


@app.route('/admin')
@app.route('/admin/dashboard')
@admin_required
def admin_dashboard():
    admin, site_filter = get_admin_site_filter()
    base_q = Subscriber.query
    if site_filter:
        base_q = base_q.filter_by(site_id=site_filter)

    total = base_q.count()
    active = base_q.filter_by(payment_status='active').count()
    expired = base_q.filter_by(payment_status='expired').count()
    pending = base_q.filter_by(payment_status='pending').count()

    rev_q = db.session.query(func.sum(PaymentLog.amount)).join(Subscriber)
    if site_filter:
        rev_q = rev_q.filter(Subscriber.site_id == site_filter)
    revenue = float(rev_q.filter(PaymentLog.status == 'success').scalar() or 0)

    site_breakdown = []
    mt_sessions_split = {}
    mt_uptime = {}
    
    # Use a set to track unique sessions by MAC address to avoid double-counting
    unique_macs = set() 
    all_sites = Site.query.filter_by(active=True).all()

    for s in all_sites:
        s_active = Subscriber.query.filter_by(site_id=s.id, payment_status='active').count()
        s_rev = db.session.query(func.sum(PaymentLog.amount)).join(Subscriber).filter(
            Subscriber.site_id == s.id, PaymentLog.status == 'success'
        ).scalar() or 0

        site_breakdown.append({
            'name': s.name, 'slug': s.slug, 'active': s_active, 'revenue': float(s_rev)
        })

        try:
            api_pool, api = get_mikrotik_api(s)
            sessions = api.get_resource('/ip/hotspot/active').get()
            
            # Count for this specific site card
            mt_sessions_split[s.slug] = len(sessions)
            
            for sess in sessions:
                unique_macs.add(sess.get('mac-address')) # Track uniqueness
                user = sess.get('user', '')
                if user:
                    mt_uptime[user] = sess.get('uptime', '')
            api_pool.disconnect()
        except Exception as e:
            log.warning(f"Dashboard MikroTik error for {s.slug}: {e}")
            mt_sessions_split[s.slug] = 0

    recent = base_q.order_by(Subscriber.created_at.desc()).limit(10).all()
    exp_window = datetime.utcnow() + timedelta(days=3)
    expiring = base_q.filter(Subscriber.payment_status == 'active', 
                             Subscriber.expiry_date <= exp_window, 
                             Subscriber.expiry_date > datetime.utcnow()).count()

    return render_template('admin/dashboard.html',
        total=total, active=active, expired=expired, pending=pending,
        revenue=revenue, expiring=expiring,
        mt_sessions=len(unique_macs), # This will now show 4, not 8
        mt_sessions_split=mt_sessions_split,
        site_breakdown=site_breakdown, mt_uptime=mt_uptime,
        recent=recent, is_super=(admin.site_id is None)
    )

@app.route('/admin/manage-admins', methods=['GET', 'POST'])
@admin_required
def manage_admins():
    is_super = session.get('admin_role') == 'super_admin'
    sites    = Site.query.filter_by(active=True).all()

    if request.method == 'POST':
        if not is_super:
            flash('Access Denied: Only Super Admins can invite administrators.', 'error')
            return redirect(url_for('manage_admins'))

        new_email     = request.form.get('email', '').strip().lower()
        selected_role = request.form.get('role', 'admin')
        selected_site = request.form.get('site_id', '')  # '' = super admin, or site id

        if not new_email:
            flash('Email is required.', 'error')
        elif AdminUser.query.filter_by(email=new_email).first():
            flash('This email is already registered as an admin.', 'error')
        else:
            # site_id = None means super admin (all sites)
            site_id_val = int(selected_site) if selected_site else None

            new_admin = AdminUser(
                email   = new_email,
                role    = selected_role,
                site_id = site_id_val
            )
            db.session.add(new_admin)

            token = secrets.token_urlsafe(48)
            reset = PasswordReset(
                email      = new_email,
                token      = token,
                expires_at = datetime.utcnow() + timedelta(hours=24)
            )
            db.session.add(reset)
            db.session.commit()

            invite_url = f"{APP_URL}/admin/setup-password/{token}"
            send_admin_invitation_email(new_email, invite_url)

            scope_label = Site.query.get(site_id_val).name if site_id_val else 'All Sites (Super Admin)'
            flash(f'Invitation sent to {new_email} — Scope: {scope_label}', 'success')
            log.info(f"Admin invited: {new_email} role={selected_role} site_id={site_id_val}")

    role_filter = request.args.get('role_filter')
    all_admins  = AdminUser.query.filter_by(role=role_filter).all() if role_filter \
                  else AdminUser.query.all()

    # Attach site name to each admin for display
    admin_list = []
    for a in all_admins:
        site_name = Site.query.get(a.site_id).name if a.site_id else 'All Sites'
        admin_list.append({'admin': a, 'site_name': site_name})

    return render_template('admin/manage_admins.html',
    admins   = admin_list, # Change 'admin_list' to 'admins'
    is_super = is_super,
    sites    = sites
)
# @app.route('/admin/manage-admins', methods=['GET', 'POST'])
# @admin_required
# def manage_admins():
#     # Only allow Super Admins to send invitations
#     is_super = session.get('admin_role') == 'super_admin'
    
#     if request.method == 'POST':
#         if not is_super:
#             flash('Access Denied: Only Super Admins can invite new administrators.', 'error')
#             return redirect(url_for('manage_admins'))

#         new_email = request.form.get('email', '').strip().lower()
#         selected_role = request.form.get('role', 'admin')

#         if not new_email:
#             flash('Email is required.', 'error')
#         elif AdminUser.query.filter_by(email=new_email).first():
#             flash('This email is already an admin.', 'error')
#         else:
#             new_admin = AdminUser(email=new_email, role=selected_role)
#             db.session.add(new_admin)
            
#             token = secrets.token_urlsafe(48)
#             reset = PasswordReset(
#                 email=new_email,
#                 token=token,
#                 expires_at=datetime.utcnow() + timedelta(hours=24)
#             )
#             db.session.add(reset)
#             db.session.commit()

#             invite_url = f"{APP_URL}/admin/setup-password/{token}"
#             send_admin_invitation_email(new_email, invite_url)
#             flash(f'Invitation sent to {new_email}!', 'success')

#     # Filtering logic: if a role is selected in a search/filter, handle it here
#     role_filter = request.args.get('role_filter')
#     if role_filter:
#         all_admins = AdminUser.query.filter_by(role=role_filter).all()
#     else:
#         all_admins = AdminUser.query.all()
        
#     return render_template('admin/manage_admins.html', admins=all_admins, is_super=is_super)

@app.route('/admin/manage-admins/delete/<int:admin_id>', methods=['POST'])
@admin_required
def delete_admin_account(admin_id):
    # Hard block: Only Super Admins can delete
    if session.get('admin_role') != 'super_admin':
        flash('Access Denied: Only Super Admins can remove accounts.', 'error')
        return redirect(url_for('manage_admins'))

    admin_to_delete = AdminUser.query.get_or_404(admin_id)
    
    if admin_to_delete.role == 'super_admin':
        flash('Super Admin accounts are protected.', 'error')
        return redirect(url_for('manage_admins'))
    
    email = admin_to_delete.email
    PasswordReset.query.filter_by(email=email).delete()
    db.session.delete(admin_to_delete)
    db.session.commit()
    
    flash(f'Administrator {email} removed.', 'success')
    return redirect(url_for('manage_admins'))

@app.route('/admin/setup-password/<token>', methods=['GET', 'POST'])
def admin_setup_password(token):
    # 1. Check if token is valid for an Admin invitation
    reset = PasswordReset.query.filter_by(token=token).first()

    if not reset or not reset.is_valid():
        flash('This invitation link is invalid or has expired.', 'error')
        return redirect(url_for('admin_login'))

    if request.method == 'POST':
        password = request.form.get('password', '').strip()
        confirm  = request.form.get('confirm_password', '').strip()

        if len(password) < 8:
            flash('Admin passwords must be at least 8 characters for security.', 'error')
            return render_template('admin/setup_password.html', token=token)

        if password != confirm:
            flash('Passwords do not match.', 'error')
            return render_template('admin/setup_password.html', token=token)

        # 2. Find the Admin associated with this email
        admin = AdminUser.query.filter_by(email=reset.email).first()
        if not admin:
            flash('Admin account not found.', 'error')
            return redirect(url_for('admin_login'))

        # 3. Update the Admin Password (and only the Admin)
        admin.set_password(password)
        reset.used = True
        db.session.commit()

        log.info(f"Admin account activated: {admin.email}")
        flash('Your administrator account is now active! Please log in.', 'success')
        return redirect(url_for('admin_login'))

    return render_template('admin/setup_password.html', token=token)


@app.route('/admin/subscriber/<int:sub_id>/force-push', methods=['POST'])
@admin_required
def admin_force_push(sub_id):
    """Fix users who failed MikroTik registration by using email as password."""
    sub = Subscriber.query.get_or_404(sub_id)
    
    # We use email as the fallback password since we can't see the original hash
    temp_password = sub.email 
    full_name = f"{sub.first_name} {sub.surname}"
    
    mt_ok = mikrotik_add_user(sub.email, temp_password, full_name)
    
    if mt_ok:
        sub.mikrotik_added = True
        db.session.commit()
        flash(f"Success! {sub.email} pushed to MikroTik. Password set to: {temp_password}", "success")
    else:
        flash("MikroTik connection error. Please check if the router is online.", "error")
        
    return redirect(url_for('admin_subscribers'))



@app.route('/admin/subscribers')
@admin_required
def admin_subscribers():
    admin, site_filter = get_admin_site_filter()
    base_q = Subscriber.query

    # Capture filters from URL
    selected_site = request.args.get('site_id', type=int)
    status = request.args.get('status')
    search = request.args.get('search', '').strip()

    # Apply Site Filter (Site admins are locked to their site, Super admins can choose)
    if site_filter:
        base_q = base_q.filter_by(site_id=site_filter)
    elif selected_site:
        base_q = base_q.filter_by(site_id=selected_site)

    if status:
        base_q = base_q.filter_by(payment_status=status)
    
    if search:
        base_q = base_q.filter(
            db.or_(
                Subscriber.email.ilike(f'%{search}%'),
                Subscriber.first_name.ilike(f'%{search}%'),
                Subscriber.surname.ilike(f'%{search}%'),
                Subscriber.phone.ilike(f'%{search}%')
            )
        )

    subscribers = base_q.order_by(Subscriber.created_at.desc()).all()
    all_sites = Site.query.all() # Needed for the dropdown filter

    return render_template('admin/subscribers.html',
        subscribers=subscribers,
        admin=admin,
        is_super=admin.site_id is None,
        status_filter=status,
        search=search,
        all_sites=all_sites,
        selected_site=selected_site
    )


@app.route('/admin/subscriber/<int:sub_id>/toggle', methods=['POST'])
@admin_required
def admin_toggle_user(sub_id):
    sub = Subscriber.query.get_or_404(sub_id)
    action = request.form.get('action')

    if action == 'disable':
        sub.payment_status = 'expired'
        mikrotik_disable_user(sub.email)
        mikrotik_remove_active_session(sub.email)
        flash(f'User {sub.email} has been disabled.', 'success')
        log.info(f"Admin disabled user: {sub.email}")

    elif action == 'enable':
        sub.payment_status = 'active'
        if not sub.expiry_date or sub.expiry_date < datetime.utcnow():
            sub.expiry_date = datetime.utcnow() + timedelta(days=SUBSCRIPTION_DAYS)
        # Re-enable on MikroTik — we don't have the password so just enable
        api = get_mikrotik_connection()
        if api:
            try:
                hotspot = api.get_resource('/ip/hotspot/user')
                existing = hotspot.get(name=sub.email)
                if existing:
                    hotspot.set(id=existing[0]['id'], disabled='no')
            except Exception as e:
                log.error(f"MikroTik enable error: {e}")
        flash(f'User {sub.email} has been enabled.', 'success')
        log.info(f"Admin enabled user: {sub.email}")

    db.session.commit()
    return redirect(url_for('admin_subscribers'))


@app.route('/admin/sites')
@admin_required
def admin_sites():
    admin, site_filter = get_admin_site_filter()
    if site_filter:
        # Site admins can't access this
        return redirect(url_for('admin_dashboard'))

    sites = Site.query.all()
    site_stats = []

    for site in sites:
        total   = Subscriber.query.filter_by(site_id=site.id).count()
        active  = Subscriber.query.filter_by(site_id=site.id,
                                             payment_status='active').count()
        revenue = db.session.query(func.sum(PaymentLog.amount)).join(
            Subscriber, PaymentLog.subscriber_id == Subscriber.id
        ).filter(
            PaymentLog.status  == 'success',
            Subscriber.site_id == site.id
        ).scalar() or 0

        site_stats.append({
            'site':    site,
            'total':   total,
            'active':  active,
            'revenue': float(revenue)
        })

    return render_template('admin/sites.html',
        site_stats = site_stats,
        admin      = admin
    )



@app.route('/admin/subscriber/<int:sub_id>/delete', methods=['POST'])
@admin_required
def admin_delete_user(sub_id):
    sub = Subscriber.query.get_or_404(sub_id)
    email = sub.email

    # Remove from MikroTik first
    api = get_mikrotik_connection()
    if api:
        try:
            hotspot = api.get_resource('/ip/hotspot/user')
            existing = hotspot.get(name=email)
            if existing:
                hotspot.remove(id=existing[0]['id'])
        except Exception as e:
            log.error(f"MikroTik delete error: {e}")

    # Delete payment logs first (FK constraint)
    PaymentLog.query.filter_by(subscriber_id=sub_id).delete()
    PasswordReset.query.filter_by(email=email).delete()
    db.session.delete(sub)
    db.session.commit()

    flash(f'User {email} deleted from DB and MikroTik.', 'success')
    log.info(f"Admin deleted user: {email}")

@app.route('/admin/sessions')
@admin_required
def admin_sessions():
    admin, site_filter = get_admin_site_filter()
    
    # Get Site ID from URL if super admin is filtering
    selected_site = request.args.get('site_id', type=int)
    
    # Determine which sites to query
    if site_filter:
        sites_to_check = Site.query.filter_by(id=site_filter).all()
    elif selected_site:
        sites_to_check = Site.query.filter_by(id=selected_site).all()
    else:
        sites_to_check = Site.query.filter_by(active=True).all()

    all_mt_sessions = []
    all_mt_users = []
    subscribers = Subscriber.query.all()
    sub_map = {s.email: s for s in subscribers}

    for site in sites_to_check:
        try:
            api_pool, api = get_mikrotik_api(site)
            
            # 1. Process Active Sessions (Online)
            raw_sessions = api.get_resource('/ip/hotspot/active').get()
            for s in raw_sessions:
                user_email = s.get('user', '')
                db_sub = sub_map.get(user_email)
                
                # Tag and Add
                s['_site_name'] = site.name
                # If testing with 1 router, we only show users belonging to this site
                # OR Trial users (to keep them visible)
                if not db_sub or db_sub.site_id == site.id:
                    all_mt_sessions.append(s)

            # 2. Process Hotspot User List (Registry)
            raw_users = api.get_resource('/ip/hotspot/user').get()
            for u in raw_users:
                user_name = u.get('name', '')
                db_sub = sub_map.get(user_name)
                
                # PROFESSIONAL FILTER: Remove 'default-trial' and orphan trials from registry
                # Only show if they exist in our Database
                if db_sub and db_sub.site_id == site.id:
                    u['_site_name'] = site.name
                    all_mt_users.append(u)

            api_pool.disconnect()
        except Exception as e:
            log.warning(f"Sessions fetch failed [{site.slug}]: {e}")

    all_sites = Site.query.all()
    return render_template('admin/sessions.html',
        mt_sessions=all_mt_sessions,
        mt_users=all_mt_users,
        db_users=sub_map,
        all_sites=all_sites,
        selected_site=selected_site,
        is_super=(admin.site_id is None)
    )
@app.route('/admin/payments')
@admin_required
def admin_payments():
    admin, site_filter = get_admin_site_filter()
    page = request.args.get('page', 1, type=int)
    selected_site = request.args.get('site_id', type=int)

    # Base query for logs
    query = PaymentLog.query.join(Subscriber, PaymentLog.subscriber_id == Subscriber.id)

    # Apply site filtering
    if site_filter:
        query = query.filter(Subscriber.site_id == site_filter)
    elif selected_site:
        query = query.filter(Subscriber.site_id == selected_site)

    # Calculate statistics based on the filtered query
    total_collected = db.session.query(func.sum(PaymentLog.amount)).filter(
        PaymentLog.id.in_(db.session.query(query.subquery().c.id)),
        PaymentLog.status == 'success'
    ).scalar() or 0

    successful_count = query.filter(PaymentLog.status == 'success').count()
    failed_count = query.filter(PaymentLog.status == 'failed').count()

    # Paginate results
    logs = query.order_by(PaymentLog.created_at.desc()).paginate(page=page, per_page=25, error_out=False)

    # Build subscriber lookup for display
    sub_ids = [l.subscriber_id for l in logs.items if l.subscriber_id]
    subs = Subscriber.query.filter(Subscriber.id.in_(sub_ids)).all()
    subscribers = {s.id: s for s in subs}
    all_sites = Site.query.all()

    return render_template('admin/payments.html',
        logs=logs,
        total_collected=float(total_collected),
        successful_count=successful_count,
        failed_count=failed_count,
        subscribers=subscribers,
        all_sites=all_sites,
        selected_site=selected_site,
        is_super=(admin.site_id is None)
    ) 



@app.route('/admin/export/csv')
@admin_required
def admin_export_csv():
    subscribers = Subscriber.query.order_by(Subscriber.created_at.desc()).all()

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow([
        'ID', 'First Name', 'Surname', 'Email', 'Phone',
        'IP Address', 'Status', 'Last Payment', 'Expiry Date',
        'MikroTik Added', 'Created At'
    ])
    for s in subscribers:
        writer.writerow([
            s.id, s.first_name, s.surname, s.email, s.phone or '',
            s.ip_address or '',
            s.payment_status,
            s.last_payment.strftime('%Y-%m-%d %H:%M') if s.last_payment else '',
            s.expiry_date.strftime('%Y-%m-%d %H:%M') if s.expiry_date else '',
            'Yes' if s.mikrotik_added else 'No',
            s.created_at.strftime('%Y-%m-%d %H:%M') if s.created_at else ''
        ])

    output.seek(0)
    response = make_response('\ufeff' + output.getvalue())
    response.headers['Content-Type'] = 'text/csv; charset=utf-8-sig'
    response.headers['Content-Disposition'] = (
        f'attachment; filename=subscribers_{datetime.utcnow().strftime("%Y%m%d_%H%M")}.csv'
    )
    log.info(f"Admin exported CSV: {len(subscribers)} records")
    return response


# Paystack Settlements
@app.route('/admin/settlements')
@admin_required
def admin_settlements():
    admin, site_filter = get_admin_site_filter()
    LAUNCH_DATE = "2026-03-14"

    try:
        headers = {'Authorization': f'Bearer {PAYSTACK_SECRET}'}

        if site_filter:
            # Fetch this site's subaccount
            site = Site.query.get(site_filter)

            if site and site.paystack_subaccount:
                # Fetch settlements for this specific subaccount
                r = requests.get(
                    f'https://api.paystack.co/settlement?subaccount={site.paystack_subaccount}',
                    headers=headers, timeout=15
                )
            else:
                # Site has no subaccount — show empty
                r = None

            all_settlements = r.json().get('data', []) if r and r.ok else []
        else:
            # Super admin — fetch all settlements
            r = requests.get(
                'https://api.paystack.co/settlement',
                headers=headers, timeout=15
            )
            all_settlements = r.json().get('data', []) if r.ok else []

        # Filter by launch date
        settlements = [
            s for s in all_settlements
            if s.get('settlement_date', '') >= LAUNCH_DATE
        ]

    except Exception as e:
        log.error(f"Paystack settlements fetch error: {e}")
        settlements = []

    total_net   = sum(s.get('total_amount', 0) for s in settlements) / 100
    total_fees  = sum(s.get('total_fees',  0) for s in settlements) / 100
    total_gross = total_net + total_fees

    return render_template('admin/settlements.html',
        settlements = settlements,
        total_gross = total_gross,
        total_fees  = total_fees,
        total_net   = total_net,
        is_super    = admin.site_id is None,
        site_filter = site_filter
    )
@app.route('/admin/settlements/export')
@admin_required
def export_settlements():
    try:
        headers = {'Authorization': f'Bearer {PAYSTACK_SECRET}'}
        r = requests.get('https://api.paystack.co/settlement',
                        headers=headers, timeout=15)
        settlements = r.json().get('data', [])
    except Exception as e:
        log.error(f"Settlements export error: {e}")
        settlements = []

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow([
        'Settlement ID', 'Date', 'Gross (NGN)', 'Paystack Fee (NGN)',
        'Net to Bank (NGN)', 'Status'
    ])

    # Write each settlement row
    for s in settlements:
        net   = s.get('total_amount', 0) / 100
        fee   = s.get('total_fees', 0) / 100
        gross = net + fee
        writer.writerow([
            s.get('id'),
            s.get('settlement_date', '')[:10],
            f"{gross:.2f}",
            f"{fee:.2f}",
            f"{net:.2f}",
            'Settled' if s.get('status') == 'success' else s.get('status', '')
        ])

    # Totals row — OUTSIDE the for loop
    total_gross = sum((s.get('total_amount', 0) + s.get('total_fees', 0)) for s in settlements) / 100
    total_fees  = sum(s.get('total_fees', 0) for s in settlements) / 100
    total_net   = sum(s.get('total_amount', 0) for s in settlements) / 100

    writer.writerow([])  # blank separator row
    writer.writerow([
        'TOTAL', '',
        f"{total_gross:.2f}",
        f"{total_fees:.2f}",
        f"{total_net:.2f}",
        ''
    ])

    output.seek(0)
    response = make_response('\ufeff' + output.getvalue())
    response.headers['Content-Type'] = 'text/csv; charset=utf-8-sig'
    response.headers['Content-Disposition'] = (
        f'attachment; filename=settlements_{datetime.utcnow().strftime("%Y%m%d")}.csv'
    )
    return response


#  Expiry Scheduler 
def check_expired_subscribers():
    """
    Runs every hour.
    Checks all sites for expired subscribers and disables them
    on their respective MikroTik routers.
    Also sends 7-day renewal reminder emails.
    """
    with app.app_context():
        now     = datetime.utcnow()
        sites   = Site.query.filter_by(active=True).all()

        for site in sites:
            log.info(f"Expiry check: processing site [{site.slug}] ...")

            # Find expired active subscribers for this site
            expired_subs = Subscriber.query.filter(
                Subscriber.site_id       == site.id,
                Subscriber.payment_status == 'active',
                Subscriber.expiry_date   <= now
            ).all()

            for sub in expired_subs:
                sub.payment_status = 'expired'
                db.session.commit()
                mikrotik_remove_user_for_site(site, sub.email)
                log.info(f"Expired [{site.slug}]: {sub.email}")

                notify_admin(
                    f"⏰ <b>Subscription Expired</b> [{site.slug.upper()}]\n"
                    f"Name:  {sub.first_name} {sub.surname}\n"
                    f"Email: {sub.email}\n"
                    f"Expired: {sub.expiry_date.strftime('%d %b %Y %H:%M') if sub.expiry_date else '—'}"
                )

            # 7-day renewal reminders
            reminder_window = now + timedelta(days=7)
            reminder_subs = Subscriber.query.filter(
                Subscriber.site_id        == site.id,
                Subscriber.payment_status  == 'active',
                Subscriber.expiry_date    <= reminder_window,
                Subscriber.expiry_date    >  now
            ).all()

            for sub in reminder_subs:
                send_expiry_reminder(sub, site)
                log.info(f"Reminder sent [{site.slug}]: {sub.email}")

        log.info("Expiry check: done")


def send_expiry_reminder(subscriber, site):
    """Send 7-day renewal reminder email to subscriber."""
    if not SMTP_USER or not SMTP_PASS:
        return
    try:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = f'⚠️ Your {site.name} WiFi expires in 7 days'
        msg['From']    = f'{site.name} <{SMTP_FROM}>'
        msg['To']      = subscriber.email

        # Choose renew URL based on site
        if site.slug == 'nduth':
            renew_url = f"{APP_URL}/nduth/login"
        else:
            renew_url = f"{APP_URL}/login"

        amount_display = f"₦{site.amount_kobo / 100:,.0f}"

        html_body = f"""
        <div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;
                    background:#f0f7ff;border-radius:16px;overflow:hidden;">
          <div style="background:linear-gradient(135deg,#0a2540,#1a4a7a);
                      padding:24px 32px;text-align:center;">
            <h1 style="color:white;margin:0;font-size:20px;">⚠️ Subscription Expiring Soon</h1>
            <p style="color:rgba(255,255,255,0.8);margin:6px 0 0;font-size:13px;">
              {site.name}
            </p>
          </div>
          <div style="padding:28px 32px;">
            <p style="color:#1a0a3c;font-size:15px;">Hi <strong>{subscriber.first_name}</strong>,</p>
            <p style="color:#4a5568;font-size:14px;line-height:1.6;margin:12px 0;">
              Your WiFi subscription expires on
              <strong>{subscriber.expiry_date.strftime('%d %B %Y') if subscriber.expiry_date else '—'}</strong>.
              Renew now to avoid losing access.
            </p>
            <div style="background:#fff;border:1px solid #bfdbfe;border-radius:12px;
                        padding:16px;margin:20px 0;text-align:center;">
              <div style="color:#1e40af;font-size:13px;font-weight:600;">Renewal Amount</div>
              <div style="color:#1e40af;font-size:28px;font-weight:800;">{amount_display}</div>
              <div style="color:#3b82f6;font-size:12px;">30 days access</div>
            </div>
            <div style="text-align:center;">
              <a href="{renew_url}"
                 style="background:linear-gradient(135deg,#0a2540,#1a4a7a);
                        color:white;padding:14px 32px;border-radius:10px;
                        text-decoration:none;font-size:15px;font-weight:700;
                        display:inline-block;">
                🔄 Renew My Subscription
              </a>
            </div>
          </div>
          <div style="background:#0a2540;padding:12px 32px;text-align:center;">
            <p style="color:rgba(255,255,255,0.5);font-size:12px;margin:0;">
              © 2026 Collnetwork Technology LTD
            </p>
          </div>
        </div>
        """

        plain_body = (
            f"Hi {subscriber.first_name},\n\n"
            f"Your {site.name} WiFi subscription expires on "
            f"{subscriber.expiry_date.strftime('%d %B %Y') if subscriber.expiry_date else '—'}.\n\n"
            f"Renew here: {renew_url}\n\n"
            f"Amount: {amount_display} for 30 days."
        )

        msg.attach(MIMEText(plain_body, 'plain'))
        msg.attach(MIMEText(html_body, 'html'))

        try:
            with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
                server.starttls()
                server.login(SMTP_USER, SMTP_PASS)
                server.sendmail(SMTP_FROM, subscriber.email, msg.as_string())
        except smtplib.SMTPServerDisconnected:
            pass  # Exim quirk — email delivered

        log.info(f"Expiry reminder sent to {subscriber.email} [{site.slug}]")

    except Exception as e:
        log.error(f"Expiry reminder failed for {subscriber.email}: {e}")





#-------Monnify Sections----------------
#  Monnify / Moniepoint POS Integration 
MONNIFY_API_KEY = os.getenv('MONNIFY_API_KEY')
MONNIFY_SECRET_KEY = os.getenv('MONNIFY_SECRET_KEY')
MONNIFY_CONTRACT_CODE = os.getenv('MONNIFY_CONTRACT_CODE')
MONNIFY_BASE_URL = "https://api.monnify.com/api/v1" # Use "sandbox.monnify..." for testing

def get_monnify_token():
    """Authenticate with Monnify to get a Bearer token."""
    auth_str = f"{MONNIFY_API_KEY}:{MONNIFY_SECRET_KEY}"
    encoded_auth = hashlib.base64.b64encode(auth_str.encode()).decode()
    headers = {'Authorization': f'Basic {encoded_auth}'}
    r = requests.post(f"{MONNIFY_BASE_URL}/auth/login", headers=headers)
    return r.json().get('responseBody', {}).get('accessToken')

@app.route('/admin/pos-register', methods=['GET', 'POST'])
@admin_required
def admin_pos_register():
    if request.method == 'POST':
        # 1. Capture Data
        email = request.form.get('email').lower().strip()
        first_name = request.form.get('first_name').strip()
        surname = request.form.get('surname').strip()
        
        # 2. Check if exists or Create
        sub = Subscriber.query.filter_by(email=email).first()
        if not sub:
            sub = Subscriber(first_name=first_name, surname=surname, email=email, payment_status='pending')
            sub.set_password(secrets.token_hex(8)) # Random temp password
            db.session.add(sub)
            db.session.commit()
        
        # 3. Trigger POS (Logic would call Moniepoint Terminal API here)
        flash(f"User {email} registered. Please complete payment on the POS terminal.", "success")
        return redirect(url_for('admin_subscribers'))

    return render_template('admin/pos_register.html')

@app.route('/admin/subscriber/<int:sub_id>/pos-renew', methods=['POST'])
@admin_required
def admin_pos_renew(sub_id):
    """Trigger a POS renewal for an existing subscriber."""
    sub = Subscriber.query.get_or_404(sub_id)
    
    # In a real Moniepoint integration, you would trigger the Terminal API here
    # For now, we set a reference and log the attempt
    new_ref = generate_ref(sub.email)
    sub.paystack_ref = new_ref # Repurposing this field for tracking
    db.session.commit()
    
    log.info(f"Admin {session['admin_user']} initiated POS renewal for {sub.email}")
    
    flash(f"POS Payment request sent for {sub.first_name}. Please check the terminal.", "success")
    return redirect(url_for('admin_subscribers'))

@app.route('/monnify/webhook', methods=['POST'])
def monnify_webhook():
    """Handles successful POS payments from Moniepoint/Monnify."""
    # Monnify sends a signature in the header: 'monnify-signature'
    data = request.get_json()
    
    # Logic to verify signature goes here...
    
    if data.get('paymentStatus') == 'PAID':
        email = data['customer']['email']
        sub = Subscriber.query.filter_by(email=email).first()
        if sub:
            sub.activate() # Extends 30 days
            mikrotik_add_user(sub.email, sub.email) # Safe fallback password
            db.session.commit()
            log.info(f"POS Payment Success: Activated {email}")
            
    return jsonify({"status": "ok"}), 200




# ── Start Scheduler (module level — runs under Gunicorn too) ──
scheduler = BackgroundScheduler()
scheduler.add_job(func=check_expired_subscribers,   trigger='interval', hours=1)
scheduler.add_job(func=walled_garden_cleanup_expired, trigger='interval', hours=1)
scheduler.start()
log.info("Background scheduler started — multi-site expiry check every 1h")


# ── DB Init ───────────────────────────────────────────────────
with app.app_context():
    db.create_all()

# ── Startup Telegram notification ────────────────────────────
with app.app_context():
    try:
        notify_admin("🚀 <b>Server Online</b>\nCollnetwork WiFi Portal started successfully.")
    except Exception as e:
        log.error(f"Startup notification failed: {e}")

# ── Local dev only (Gunicorn ignores this block) ──────────────
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5010, debug=True)