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

    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):
        now = datetime.utcnow()
        # If still active, extend from expiry; otherwise start fresh
        base = self.expiry_date if (self.expiry_date and
                                    self.expiry_date > now) else now
        self.expiry_date    = base + timedelta(days=SUBSCRIPTION_DAYS)
        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_connection():
    """Open a RouterOS API connection."""
    try:
        conn = routeros_api.RouterOsApiPool(
            MT_HOST, username=MT_USER, password=MT_PASS,
            port=MT_PORT, plaintext_login=True
        )
        return conn.get_api()
    except Exception as e:
        log.error(f"MikroTik connection failed: {e}")
        return None


def mikrotik_add_user(email, password):
    """Add hotspot user to MikroTik."""
    api = get_mikrotik_connection()
    if not api:
        return False
    try:
        hotspot = api.get_resource('/ip/hotspot/user')
        # Check if user already exists
        existing = hotspot.get(name=email)
        if existing:
            # Update password and enable
            hotspot.set(id=existing[0]['id'],
                        password=password,
                        disabled='no',
                        profile=MT_PROFILE)
            log.info(f"MikroTik: Updated user {email}")
        else:
            hotspot.add(name=email, password=password,
                        profile=MT_PROFILE, disabled='no')
            log.info(f"MikroTik: Added user {email}")
        return True
    except Exception as e:
        log.error(f"MikroTik add user error: {e}")
        return False


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')}"


# ── Routes ───────────────────────────────────────────────────

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


@app.route('/register', methods=['GET'])
def register():
    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():
    """
    Paystack redirects here after payment.
    Verify the transaction and activate the user.
    """
    ref = request.args.get('ref') or request.args.get('reference')
    if not ref:
        flash('Invalid payment reference.', 'error')
        return redirect(url_for('register'))

    result = paystack_verify(ref)

    if (result.get('status') and
            result['data'].get('status') == 'success'):
        # Payment successful
        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:
            password = session.pop('pending_password', None)
            sub.activate()
            db.session.commit()

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

            # Add/reactivate on MikroTik
            if password:
                mt_ok = mikrotik_add_user(sub.email, password)
                sub.mikrotik_added = mt_ok
                db.session.commit()
            else:
                log.warning(f"No password in session for {sub.email} — "
                            "MikroTik not updated")
            # Notify admin
            send_admin_notification(sub, AMOUNT_KOBO / 100, ref)
            
            return render_template('success.html', subscriber=sub)
        else:
            flash('User not found. Contact support.', 'error')
            return redirect(url_for('register'))
    else:
        # Payment failed
        sub_id = session.get('subscriber_id')
        if sub_id:
            sub = Subscriber.query.get(sub_id)
            if sub:
                sub.payment_status = 'failed'
                plog = PaymentLog(
                    subscriber_id=sub.id,
                    paystack_ref=ref,
                    amount=AMOUNT_KOBO / 100,
                    status='failed',
                    gateway_response=str(result)
                )
                db.session.add(plog)
                db.session.commit()

        return render_template('failed.html')


@app.route('/paystack/webhook', methods=['POST'])
def paystack_webhook():
    """
    Paystack webhook for server-to-server notification.
    Validates signature and handles charge.success event.
    """
    sig = request.headers.get('X-Paystack-Signature', '')
    payload = request.get_data()
    expected = hmac.new(PAYSTACK_SECRET.encode(),
                        payload, hashlib.sha512).hexdigest()

    if not hmac.compare_digest(sig, expected):
        log.warning("Invalid Paystack webhook signature")
        return jsonify({'status': 'invalid'}), 400

    data = request.get_json()
    if data.get('event') == 'charge.success':
        ref  = data['data']['reference']
        sub  = Subscriber.query.filter_by(paystack_ref=ref).first()
        if sub and sub.payment_status != 'active':
            sub.activate()
            db.session.commit()
            log.info(f"Webhook: Activated {sub.email}")

    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)


# ── Email Helper ─────────────────────────────────────────────
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;">
              © 2025 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'))

        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"Reset email sent to {to_email}")
        return True
    except Exception as e:
        log.error(f"Email send failed: {e}")
        return False


def send_admin_notification(subscriber, amount, ref):
    """Send payment notification email to admin."""
    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:
        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;">
              © 2025 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'))

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

        log.info(f"Admin notification sent for payment by {subscriber.email}")
    except Exception as e:
        log.error(f"Admin notification email failed: {e}")


# ── Forgot / Reset Password Routes ───────────────────────────
@app.route('/forgot-password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        email = request.form.get('email', '').strip().lower()
        # Always show the same message — never reveal if email exists
        flash('If that email is registered, a reset link has been sent.', 'success')

        sub = Subscriber.query.filter_by(email=email).first()
        if sub:
            # Invalidate any previous unused tokens for this email
            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}/reset-password/{token}"
            send_reset_email(email, reset_url, sub.first_name)
            log.info(f"Password reset token generated for {email}")

        return redirect(url_for('forgot_password'))

    return render_template('forgot_password.html')


@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'))

        # Update password in DB
        sub.set_password(password)

        # Update password on MikroTik too
        mikrotik_add_user(sub.email, password)

        # Mark token as used
        reset.used = True
        db.session.commit()

        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':
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '').strip()
        if username == ADMIN_USER and password == ADMIN_PASS:
            session['admin_logged_in'] = True
            session['admin_user']      = username
            session['admin_login_time'] = datetime.utcnow().isoformat()
            session.permanent = False
            log.info(f"Admin login: {username} from {request.remote_addr}")
            return redirect(url_for('admin_dashboard'))
        log.warning(f"Failed admin login attempt from {request.remote_addr}")
        flash('Invalid username 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():
    # DB stats
    total      = Subscriber.query.count()
    active     = Subscriber.query.filter_by(payment_status='active').count()
    expired    = Subscriber.query.filter_by(payment_status='expired').count()
    pending    = Subscriber.query.filter_by(payment_status='pending').count()

    # Revenue
    from sqlalchemy import func
    total_gross = float(db.session.query(
                     func.sum(PaymentLog.amount)
                 ).filter_by(status='success').scalar() or 0)

    # Paystack fee: 1.5% + ₦100 per transaction, capped at ₦2000
    successful_logs = PaymentLog.query.filter_by(status='success').all()
    total_fees = sum(
        min((float(l.amount) * 0.015) + 100, 2000)
        for l in successful_logs if l.amount
    )
    revenue = total_gross - total_fees

    # Recent subscribers (last 5)
    recent     = Subscriber.query.order_by(
                     Subscriber.created_at.desc()
                 ).limit(5).all()

    # Expiring soon (within 3 days)
    soon       = datetime.utcnow() + timedelta(days=3)
    expiring   = Subscriber.query.filter(
                     Subscriber.payment_status == 'active',
                     Subscriber.expiry_date <= soon
                 ).count()

    # MikroTik active sessions
    mt_sessions = get_mikrotik_active_sessions()

    # Build uptime lookup dict: {email: uptime}
    mt_uptime = {s.get('user', ''): s.get('uptime', '—') for s in mt_sessions}

    return render_template('admin/dashboard.html',
        total=total, active=active, expired=expired,
        pending=pending, revenue=revenue, recent=recent,
        expiring=expiring, mt_sessions=len(mt_sessions),
        mt_uptime=mt_uptime
    )


@app.route('/admin/subscribers')
@admin_required
def admin_subscribers():
    search  = request.args.get('search', '').strip()
    status  = request.args.get('status', '')
    page    = request.args.get('page', 1, type=int)

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

    subscribers = query.order_by(Subscriber.created_at.desc()).paginate(
        page=page, per_page=20, error_out=False
    )
    return render_template('admin/subscribers.html',
        subscribers=subscribers, search=search, status=status
    )


@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/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}")
    return redirect(url_for('admin_subscribers'))


@app.route('/admin/sessions')
@admin_required
def admin_sessions():
    mt_sessions = get_mikrotik_active_sessions()
    mt_users    = get_mikrotik_all_users()

    # Map MikroTik users to DB records
    emails = [u.get('name','') for u in mt_users]
    db_users = {s.email: s for s in
                Subscriber.query.filter(Subscriber.email.in_(emails)).all()}

    return render_template('admin/sessions.html',
        mt_sessions=mt_sessions,
        mt_users=mt_users,
        db_users=db_users
    )


@app.route('/admin/payments')
@admin_required
def admin_payments():
    page = request.args.get('page', 1, type=int)
    logs = PaymentLog.query.order_by(
               PaymentLog.created_at.desc()
           ).paginate(page=page, per_page=25, error_out=False)

    from sqlalchemy import func

    total_collected  = float(db.session.query(
        func.sum(PaymentLog.amount)
    ).filter_by(status='success').scalar() or 0)

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

    # 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}

    return render_template('admin/payments.html',
        logs=logs,
        total_collected=total_collected,
        successful_count=successful_count,
        failed_count=failed_count,
        subscribers=subscribers
    )


@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():
    try:
        headers = {'Authorization': f'Bearer {PAYSTACK_SECRET}'}
        r = requests.get('https://api.paystack.co/settlement',
                        headers=headers, timeout=15)
        data = r.json()
        settlements = data.get('data', []) if data.get('status') else []
    except Exception as e:
        log.error(f"Paystack settlements fetch error: {e}")
        settlements = []

    # Gross comes from our PaymentLog (what customer actually paid)
    from sqlalchemy import func
    total_gross = float(db.session.query(
        func.sum(PaymentLog.amount)
    ).filter_by(status='success').scalar() or 0)

    # Calculate all totals from settlements data
    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
    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
    )
@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.
    Disables MikroTik users whose subscription has expired.
    """
    with app.app_context():
        now = datetime.utcnow()
        expired = Subscriber.query.filter(
            Subscriber.payment_status == 'active',
            Subscriber.expiry_date < now
        ).all()

        for sub in expired:
            sub.payment_status = 'expired'
            mikrotik_disable_user(sub.email)
            mikrotik_remove_active_session(sub.email)
            db.session.commit()
            log.info(f"Expired & disabled: {sub.email}")


scheduler = BackgroundScheduler()
scheduler.add_job(check_expired_subscribers, 'interval', hours=1)
scheduler.start()


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

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5010, debug=True)
