import os
import csv
import io
import datetime
import bcrypt
import jwt
import mysql.connector
from functools import wraps
from flask import Flask, render_template, request, jsonify, send_file, session
from flask_cors import CORS
from apscheduler.schedulers.background import BackgroundScheduler

app = Flask(__name__)
app.secret_key = 'tonjumtechrecords-secret-key-change-in-production'
CORS(app)

DB_CONFIG = {
    'host': '10.7.0.1',
    'user': 'nick',
    'password': 'nick9924Tucker9924##',
    'database': 'TonjumTechRecords',
    'charset': 'utf8mb4'
}
JWT_SECRET = 'tonjumtechrecords-jwt-secret-change-in-production'
JWT_ALGORITHM = 'HS256'


def get_db():
    conn = mysql.connector.connect(**DB_CONFIG)
    return conn


def token_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        token = None
        auth_header = request.headers.get('Authorization')
        if auth_header and auth_header.startswith('Bearer '):
            token = auth_header.split(' ')[1]
        if not token:
            return jsonify({'message': 'Token is missing'}), 401
        try:
            data = jwt.decode(token, JWT_SECRET, algorithms=[JWT_ALGORITHM])
            current_user = data['username']
        except jwt.ExpiredSignatureError:
            return jsonify({'message': 'Token has expired'}), 401
        except jwt.InvalidTokenError:
            return jsonify({'message': 'Invalid token'}), 401
        return f(current_user, *args, **kwargs)
    return decorated


def advance_billing_date(next_date, interval):
    d = next_date
    if interval == 'Weekly':
        return d + datetime.timedelta(weeks=1)
    elif interval == 'Monthly':
        month = d.month + 1
        year = d.year
        if month > 12:
            month = 1
            year += 1
        day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
                          31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1])
        return d.replace(year=year, month=month, day=day)
    elif interval == 'Quarterly':
        month = d.month + 3
        year = d.year
        if month > 12:
            month -= 12
            year += 1
        day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
                          31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1])
        return d.replace(year=year, month=month, day=day)
    elif interval == 'Annually':
        year = d.year + 1
        day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
                          31, 30, 31, 30, 31, 31, 30, 31, 30, 31][d.month - 1])
        return d.replace(year=year, day=day)


def run_subscription_billing():
    with app.app_context():
        try:
            conn = get_db()
            cursor = conn.cursor(dictionary=True)
            today = datetime.date.today()
            cursor.execute("""
                SELECT s.*, c.Name AS ClientName FROM Subscriptions s
                JOIN Clients c ON c.Id = s.ClientId
                WHERE s.IsActive = TRUE AND s.NextBillingDate <= %s
            """, (today,))
            due = cursor.fetchall()
            for sub in due:
                if sub['EndDate'] and sub['NextBillingDate'] > sub['EndDate']:
                    cursor.execute("UPDATE Subscriptions SET IsActive = FALSE WHERE Id = %s", (sub['Id'],))
                    conn.commit()
                    continue
                cursor.execute("""
                    INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, SubscriptionId)
                    VALUES ('Income', %s, %s, %s, 'Other', %s, %s)
                """, (sub['NextBillingDate'], sub['ClientId'], sub['Amount'],
                      f"Subscription: {sub['ServiceName']} (auto-billed)", sub['Id']))
                next_date = advance_billing_date(sub['NextBillingDate'], sub['Interval'])
                if sub['EndDate'] and next_date > sub['EndDate']:
                    cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s, IsActive = FALSE WHERE Id = %s",
                                   (next_date, sub['Id']))
                else:
                    cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s WHERE Id = %s",
                                   (next_date, sub['Id']))
                conn.commit()
            cursor.close()
            conn.close()
        except Exception as e:
            print(f"[SubscriptionBilling] Error: {e}")


scheduler = None

def start_scheduler():
    global scheduler
    if scheduler is not None:
        return
    scheduler = BackgroundScheduler()
    scheduler.add_job(run_subscription_billing, 'interval', hours=1, id='subscription_billing')
    scheduler.start()
    print("[Scheduler] Started subscription billing service")
    run_subscription_billing()


# --- Auth ---

@app.route('/api/auth/login', methods=['POST'])
def login():
    data = request.get_json()
    username = data.get('username', '')
    password = data.get('password', '')
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM Users WHERE Username = %s", (username,))
    user = cursor.fetchone()
    cursor.close()
    conn.close()
    if user and bcrypt.checkpw(password.encode(), user['PasswordHash'].encode()):
        token = jwt.encode({
            'username': user['Username'],
            'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=24)
        }, JWT_SECRET, algorithm=JWT_ALGORITHM)
        return jsonify({'token': token, 'username': user['Username']})
    return jsonify({'message': 'Invalid credentials'}), 401


# --- Dashboard ---

@app.route('/api/dashboard/summary')
@token_required
def dashboard_summary(current_user):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    today = datetime.date.today()
    first_of_month = today.replace(day=1)
    year_start = today.replace(month=1, day=1)

    cursor.execute("""
        SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
        FROM Transactions
    """)
    total_profit = cursor.fetchone()['total']

    cursor.execute("""
        SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
        FROM Transactions WHERE TransactionDate >= %s
    """, (today - datetime.timedelta(days=365),))
    last_365 = cursor.fetchone()['total']

    cursor.execute("""
        SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
        FROM Transactions WHERE TransactionDate >= %s
    """, (today - datetime.timedelta(days=30),))
    last_30 = cursor.fetchone()['total']

    months_data = []
    for i in range(11, -1, -1):
        m = today.month - i
        y = today.year
        if m <= 0:
            m += 12
            y -= 1
        month_start = datetime.date(y, m, 1)
        if m == 12:
            month_end = datetime.date(y + 1, 1, 1)
        else:
            month_end = datetime.date(y, m + 1, 1)
        cursor.execute("""
            SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE 0 END), 0) AS income,
                   COALESCE(SUM(CASE WHEN Type='Expense' THEN Amount ELSE 0 END), 0) AS expense
            FROM Transactions WHERE TransactionDate >= %s AND TransactionDate < %s
        """, (month_start, month_end))
        row = cursor.fetchone()
        months_data.append({
            'month': f'{y}-{m:02d}',
            'income': float(row['income']),
            'expense': float(row['expense'])
        })

    cursor.execute("""
        SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
        FROM Transactions WHERE TransactionDate >= %s
    """, (year_start,))
    ytd_profit = cursor.fetchone()['total']

    cursor.execute("""
        SELECT * FROM Subscriptions WHERE IsActive = TRUE
        AND (EndDate IS NULL OR EndDate > %s)
    """, (today,))
    active_subs = cursor.fetchall()
    active_subs_count = len(active_subs)
    mrr_map = {'Weekly': 4.33, 'Monthly': 1, 'Quarterly': 1/3, 'Annually': 1/12}
    active_subs_total = sum(float(s['Amount']) * mrr_map.get(s['Interval'], 1) for s in active_subs)
    year_end = datetime.date(today.year, 12, 31)

    future_sub_projected = 0.0
    for sub in active_subs:
        bdate = sub['NextBillingDate']
        while bdate <= year_end:
            if sub['EndDate'] and bdate > sub['EndDate']:
                break
            future_sub_projected += float(sub['Amount'])
            bdate = advance_billing_date(bdate, sub['Interval'])

    projected = float(ytd_profit) + future_sub_projected

    cursor.close()
    conn.close()
    return jsonify({
        'totalProfit': float(total_profit),
        'last365': float(last_365),
        'last30': float(last_30),
        'projected': float(projected),
        'monthlyHistory': months_data,
        'activeSubscriptions': active_subs_count,
        'activeSubscriptionsTotal': float(active_subs_total)
    })


# --- Transactions ---

@app.route('/api/transactions', methods=['GET'])
@token_required
def list_transactions(current_user):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    page = request.args.get('page', 1, type=int)
    limit = request.args.get('limit', 50, type=int)
    offset = (page - 1) * limit
    ttype = request.args.get('type')
    client_id = request.args.get('client_id', type=int)
    from_date = request.args.get('from')
    to_date = request.args.get('to')

    where = []
    params = []
    if ttype:
        where.append("t.Type = %s")
        params.append(ttype)
    if client_id:
        where.append("t.ClientId = %s")
        params.append(client_id)
    if from_date:
        where.append("t.TransactionDate >= %s")
        params.append(from_date)
    if to_date:
        where.append("t.TransactionDate <= %s")
        params.append(to_date)

    where_clause = ('WHERE ' + ' AND '.join(where)) if where else ''
    cursor.execute(f"""
        SELECT t.*, c.Name AS ClientName, s.ServiceName AS SubscriptionName
        FROM Transactions t
        LEFT JOIN Clients c ON c.Id = t.ClientId
        LEFT JOIN Subscriptions s ON s.Id = t.SubscriptionId
        {where_clause}
        ORDER BY t.TransactionDate DESC, t.Id DESC
        LIMIT %s OFFSET %s
    """, params + [limit, offset])
    transactions = cursor.fetchall()
    for tx in transactions:
        tx['Amount'] = float(tx['Amount'])
        tx['HasImage'] = tx['ImageData'] is not None
        tx['ImageData'] = None
        tx.pop('UpdatedAt', None)
        if tx['CreatedAt']:
            tx['CreatedAt'] = tx['CreatedAt'].isoformat()
        if tx['TransactionDate']:
            tx['TransactionDate'] = tx['TransactionDate'].isoformat()

    cursor.execute(f"SELECT COUNT(*) AS cnt FROM Transactions t {where_clause}", params)
    total = cursor.fetchone()['cnt']
    cursor.close()
    conn.close()
    return jsonify({'transactions': transactions, 'total': total, 'page': page, 'limit': limit})


@app.route('/api/transactions', methods=['POST'])
@token_required
def create_transaction(current_user):
    ttype = request.form.get('type')
    date_str = request.form.get('date')
    client_name = request.form.get('client_name', '').strip()
    client_id = request.form.get('client_id', type=int)
    amount = request.form.get('amount', type=float)
    payment_method = request.form.get('payment_method')
    description = request.form.get('description', '')
    image_file = request.files.get('image')

    if not all([ttype, date_str, amount, payment_method]):
        return jsonify({'message': 'Missing required fields'}), 400

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

    if client_name and not client_id:
        cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
        conn.commit()
        cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
        row = cursor.fetchone()
        client_id = row['Id'] if row else None
    elif client_id:
        pass

    image_data = None
    image_content_type = None
    if image_file and image_file.filename:
        image_data = image_file.read()
        image_content_type = image_file.content_type or 'image/jpeg'

    cursor.execute("""
        INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, ImageData, ImageContentType)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """, (ttype, date_str, client_id, amount, payment_method, description, image_data, image_content_type))
    conn.commit()
    tx_id = cursor.lastrowid
    cursor.close()
    conn.close()
    return jsonify({'message': 'Transaction created', 'id': tx_id}), 201


@app.route('/api/transactions/<int:tx_id>', methods=['PUT'])
@token_required
def update_transaction(current_user, tx_id):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM Transactions WHERE Id = %s", (tx_id,))
    existing = cursor.fetchone()
    if not existing:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Not found'}), 404

    ttype = request.form.get('type', existing['Type'])
    date_str = request.form.get('date', existing['TransactionDate'].isoformat() if existing['TransactionDate'] else None)
    client_name = request.form.get('client_name', '').strip()
    client_id = request.form.get('client_id', type=int)
    amount = request.form.get('amount', type=float) or float(existing['Amount'])
    payment_method = request.form.get('payment_method', existing['PaymentMethod'])
    description = request.form.get('description', existing['Description'] or '')
    image_file = request.files.get('image')

    if client_name and not client_id:
        cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
        conn.commit()
        cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
        row = cursor.fetchone()
        client_id = row['Id'] if row else existing['ClientId']
    else:
        client_id = client_id if client_id is not None else existing['ClientId']

    if image_file and image_file.filename:
        image_data = image_file.read()
        image_content_type = image_file.content_type or 'image/jpeg'
        cursor.execute("""
            UPDATE Transactions SET Type=%s, TransactionDate=%s, ClientId=%s, Amount=%s,
                PaymentMethod=%s, Description=%s, ImageData=%s, ImageContentType=%s
            WHERE Id=%s
        """, (ttype, date_str, client_id, amount, payment_method, description, image_data, image_content_type, tx_id))
    else:
        cursor.execute("""
            UPDATE Transactions SET Type=%s, TransactionDate=%s, ClientId=%s, Amount=%s,
                PaymentMethod=%s, Description=%s
            WHERE Id=%s
        """, (ttype, date_str, client_id, amount, payment_method, description, tx_id))

    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Transaction updated'})


@app.route('/api/transactions/<int:tx_id>', methods=['DELETE'])
@token_required
def delete_transaction(current_user, tx_id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM Transactions WHERE Id = %s", (tx_id,))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Transaction deleted'})


@app.route('/api/transactions/<int:tx_id>/image')
def get_image(tx_id):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT ImageData, ImageContentType FROM Transactions WHERE Id = %s", (tx_id,))
    row = cursor.fetchone()
    cursor.close()
    conn.close()
    if row and row['ImageData']:
        return send_file(io.BytesIO(row['ImageData']), mimetype=row['ImageContentType'] or 'image/jpeg')
    return jsonify({'message': 'No image'}), 404


@app.route('/api/transactions/export/csv')
@token_required
def export_csv(current_user):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT t.TransactionDate, c.Name AS ClientName, t.Amount, t.Description
        FROM Transactions t
        LEFT JOIN Clients c ON c.Id = t.ClientId
        ORDER BY t.TransactionDate DESC
    """)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(['Date', 'Client', 'Amount', 'Description'])
    for r in rows:
        writer.writerow([
            r['TransactionDate'].isoformat() if r['TransactionDate'] else '',
            r['ClientName'] or '',
            float(r['Amount']) if r['Amount'] else 0,
            r['Description'] or ''
        ])
    mem = io.BytesIO(output.getvalue().encode('utf-8'))
    return send_file(mem, mimetype='text/csv', as_attachment=True, download_name='transactions.csv')


# --- Clients ---

@app.route('/api/clients', methods=['GET'])
@token_required
def list_clients(current_user):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM Clients ORDER BY Name ASC")
    clients = cursor.fetchall()
    cursor.close()
    conn.close()
    return jsonify(clients)


@app.route('/api/clients', methods=['POST'])
@token_required
def create_client(current_user):
    data = request.get_json()
    name = data.get('name', '').strip()
    if not name:
        return jsonify({'message': 'Name is required'}), 400
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("""
            INSERT INTO Clients (Name, ContactName, Phone, Email, Address)
            VALUES (%s, %s, %s, %s, %s)
        """, (name, data.get('contact_name', ''), data.get('phone', ''), data.get('email', ''), data.get('address', '')))
        conn.commit()
        client_id = cursor.lastrowid
        cursor.close()
        conn.close()
        return jsonify({'message': 'Client created', 'id': client_id}), 201
    except mysql.connector.IntegrityError:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Client already exists'}), 409


@app.route('/api/clients/<int:client_id>', methods=['PUT'])
@token_required
def update_client(current_user, client_id):
    data = request.get_json()
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM Clients WHERE Id = %s", (client_id,))
    existing = cursor.fetchone()
    if not existing:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Not found'}), 404
    cursor.execute("""
        UPDATE Clients SET Name=%s, ContactName=%s, Phone=%s, Email=%s, Address=%s
        WHERE Id=%s
    """, (
        data.get('name', existing['Name']),
        data.get('contact_name', existing['ContactName'] or ''),
        data.get('phone', existing['Phone'] or ''),
        data.get('email', existing['Email'] or ''),
        data.get('address', existing['Address'] or ''),
        client_id
    ))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Client updated'})


@app.route('/api/clients/<int:client_id>', methods=['DELETE'])
@token_required
def delete_client(current_user, client_id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM Clients WHERE Id = %s", (client_id,))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Client deleted'})


# --- Subscriptions ---

@app.route('/api/subscriptions', methods=['GET'])
@token_required
def list_subscriptions(current_user):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT s.*, c.Name AS ClientName
        FROM Subscriptions s
        JOIN Clients c ON c.Id = s.ClientId
        ORDER BY s.IsActive DESC, s.NextBillingDate ASC
    """)
    subs = cursor.fetchall()
    for s in subs:
        s['Amount'] = float(s['Amount'])
        if s['StartDate']:
            s['StartDate'] = s['StartDate'].isoformat()
        if s['EndDate']:
            s['EndDate'] = s['EndDate'].isoformat()
        if s['NextBillingDate']:
            s['NextBillingDate'] = s['NextBillingDate'].isoformat()
        if s['CreatedAt']:
            s['CreatedAt'] = s['CreatedAt'].isoformat()
        if s['UpdatedAt']:
            s['UpdatedAt'] = s['UpdatedAt'].isoformat()
    cursor.close()
    conn.close()
    return jsonify(subs)


@app.route('/api/subscriptions', methods=['POST'])
@token_required
def create_subscription(current_user):
    data = request.get_json()
    client_id = data.get('client_id')
    client_name = data.get('client_name', '').strip()
    service_name = data.get('service_name', '').strip()
    amount = data.get('amount')
    if amount is not None:
        amount = float(amount)
    interval = data.get('interval')
    start_date = data.get('start_date')
    end_date = data.get('end_date')

    if not all([amount, interval, start_date, service_name]):
        return jsonify({'message': 'Missing required fields'}), 400

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

    if client_name and not client_id:
        cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
        conn.commit()
        cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
        row = cursor.fetchone()
        client_id = row['Id'] if row else None

    if not client_id:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Client is required'}), 400

    cursor.execute("""
        INSERT INTO Subscriptions (ClientId, ServiceName, Amount, `Interval`, StartDate, EndDate, NextBillingDate)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (client_id, service_name, amount, interval, start_date, end_date or None, start_date))
    conn.commit()
    sub_id = cursor.lastrowid
    cursor.close()
    conn.close()
    return jsonify({'message': 'Subscription created', 'id': sub_id}), 201


@app.route('/api/subscriptions/<int:sub_id>', methods=['PUT'])
@token_required
def update_subscription(current_user, sub_id):
    data = request.get_json()
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM Subscriptions WHERE Id = %s", (sub_id,))
    existing = cursor.fetchone()
    if not existing:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Not found'}), 404

    client_name = data.get('client_name', '').strip()
    client_id = data.get('client_id', existing['ClientId'])
    if client_name:
        cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
        conn.commit()
        cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
        row = cursor.fetchone()
        if row:
            client_id = row['Id']

    cursor.execute("""
        UPDATE Subscriptions SET ClientId=%s, ServiceName=%s, Amount=%s,
            `Interval`=%s, StartDate=%s, EndDate=%s, IsActive=%s
        WHERE Id=%s
    """, (
        data.get('client_id', existing['ClientId']),
        data.get('service_name', existing['ServiceName']),
        data.get('amount', existing['Amount']),
        data.get('interval', existing['Interval']),
        data.get('start_date', existing['StartDate']),
        data.get('end_date', existing['EndDate']),
        data.get('is_active', existing['IsActive']),
        sub_id
    ))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Subscription updated'})


@app.route('/api/subscriptions/<int:sub_id>', methods=['DELETE'])
@token_required
def delete_subscription(current_user, sub_id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("UPDATE Subscriptions SET IsActive = FALSE WHERE Id = %s", (sub_id,))
    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Subscription deactivated'})


@app.route('/api/subscriptions/<int:sub_id>/record-payment', methods=['POST'])
@token_required
def record_subscription_payment(current_user, sub_id):
    conn = get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT s.*, c.Name AS ClientName FROM Subscriptions s
        JOIN Clients c ON c.Id = s.ClientId WHERE s.Id = %s
    """, (sub_id,))
    sub = cursor.fetchone()
    if not sub or not sub['IsActive']:
        cursor.close()
        conn.close()
        return jsonify({'message': 'Subscription not found or inactive'}), 404

    today = datetime.date.today()
    cursor.execute("""
        INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, SubscriptionId)
        VALUES ('Income', %s, %s, %s, 'Other', %s, %s)
    """, (today, sub['ClientId'], sub['Amount'],
          f"Subscription: {sub['ServiceName']} (manual payment)", sub_id))

    next_date = advance_billing_date(today, sub['Interval'])
    if sub['EndDate'] and next_date > sub['EndDate']:
        cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s, IsActive = FALSE WHERE Id = %s",
                       (next_date, sub_id))
    else:
        cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s WHERE Id = %s", (next_date, sub_id))

    conn.commit()
    cursor.close()
    conn.close()
    return jsonify({'message': 'Payment recorded'})


# --- Frontend Routes ---

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


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


if __name__ == '__main__':
    is_reloader = os.environ.get('WERKZEUG_RUN_MAIN') == 'true'
    if not app.debug or is_reloader:
        start_scheduler()
    app.run(host='0.0.0.0', port=5740, debug=True, use_reloader=True)