#!/usr/bin/python3

import subprocess
import datetime
#pip install mysql-connector-python
import mysql.connector
import time

from mysql.connector.connection import MySQLConnection

def SaveMySQL():

  mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  auth_plugin='mysql_native_password',
  database="database",
  collation='utf8mb4_unicode_ci',
  password="password"
)
  mycursor = mydb.cursor()
  sql = "INSERT INTO failed (message) VALUES ('"+str(failedstr)+"')"
  mycursor.execute(sql)
  mydb.commit()

def Security():
    print(d)
    #awk 'match($6,/Failed/) -F { print $1, $2, $3,$6,$9,$11,$12,$13;exit;}' /var/log/auth.log
    a = subprocess.Popen(["awk",'match($6,/Failed/) && /'+d+'/ -F { print $1, $2, $3,$6,$7,$9,$11,$12,$13,$14,$15;exit;}',"/var/log/auth.log"], 
    sdout = subprocess.PIPE)
    #output = str(a.communicate())
    output = a.stdout.read().strip()
    #outputlist.append(output)
    return output

if __name__ == '__main__':

    while True:
        #d = datetime.datetime.now()
        from datetime import datetime, timedelta
        #take off a minute
        global d
        d = datetime.today() - timedelta(hours=0, minutes=0)
        t = datetime.today() - timedelta(hours=0, minutes=0)
        d = d.strftime("%b %d")
        t = t.strftime("%H:%M:%S")
        day = ('01','02','03','04','05','06','07','08','09')
        for daystr in day:
         if d.find(daystr) !=-1:
          d=d.replace('0',' ')
        d=d+' '+t
        time.sleep(1)
        #d="Mar  1 22:07:55"

        with open('checked.txt','w+') as file:
         #  file.write('Checked the log file for failed messages. at '+d)
           failed = Security()
        if (failed):
            with open('Failed.txt','a+') as File:
                failedstr = str(failed)
                failedstr=failedstr.replace("b'","")
                failedstr=failedstr.replace("'","")
                print(failedstr)
                SaveMySQL()
                #os.system('mail -s "You have an intuder at richard@richard-Thinkcentre-M77" rchrdperreault@gmail.com')
                subprocess.call(['/home/richard/sendmail.sh', "You have an intruder at richard@richard-Thinkcentre-M77",failed])

#!/usr/bin/python3

from ast import Global, Pass
from itertools import count
from sre_parse import GLOBAL_FLAGS
import subprocess
import datetime
from telnetlib import DO
import threading
import mysql.connector
from time import sleep
import requests
import json
import os

def load_config():
    """
    Load Telegram bot keys and MySQL parameters from a JSON file
    located one directory above the current script.

    Returns:
        dict: Dictionary with 'telegram' and 'mysql' configuration.
    """
    try:
        # Get the path to the config.json one directory up
        base_dir = os.path.dirname(os.path.abspath(__file__))
        config_path = os.path.join(base_dir, '..', 'config.json')

        with open(config_path, 'r') as file:
            config = json.load(file)

        telegram_config = config.get('telegram', {})
        mysql_config = config.get('mysql', {})
        openrouter_config = config.get('openrouter', {})

        return {
            'telegram': telegram_config,
            'mysql': mysql_config,
            'openrouter': openrouter_config 
        }

    except FileNotFoundError:
        print(f"Error: File '{config_path}' not found.")
    except json.JSONDecodeError:
        print(f"Error: File '{config_path}' is not valid JSON.")
    except Exception as e:
        print(f"Unexpected error: {e}")

    return {}

config = load_config()

bot_token = config['telegram'].get('bot_token')
admin_id = config['telegram'].get('admin_id')

mysql_params = config['mysql']

#Url to check call database
url= config['telegram'].get('call-url')

# paste your api key here
api_key = config['telegram'].get('api-key')

OPENROUTER_API_KEY = config['openrouter'].get('api_key')

#  Function to query OpenRouter
def ask_openrouter(prompt):
    headers = {
        "Authorization": f"Bearer {OPENROUTER_API_KEY}",
        "Content-Type": "application/json"
    }

    data = {
        "model": "z-ai/glm-4.5-air:free",  # You can change this to other models like "anthropic/claude-2"
        "messages": [
            {"role": "system", "content": "You are a helpful assistant."},
            {"role": "user", "content": prompt}
        ]
    }
    response = requests.post(
            "https://openrouter.ai/api/v1/chat/completions",
            headers=headers,
            json=data,
            timeout=30  # seconds
        )
    #response = requests.post("https://openrouter.ai/api/v1/chat/completions", headers=headers, json=data)

    if response.status_code == 200:
        return response.json()["choices"][0]["message"]["content"]
    else:
        return f"Error from OpenRouter: {response.status_code} - {response.text}"

#pip install python-telegram-bot --upgrade
from telegram.ext import Updater
updater = Updater(token=bot_token, use_context=True)

import logging
logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
                     level=logging.INFO)

from telegram.ext.messagehandler import MessageHandler
from telegram.ext.filters import Filters                     

from telegram import Update
from telegram.ext import CallbackContext,ContextTypes

global counter
counter=0

global calldate
calldate=["",""]

def telegram_bot_sendtext(bot_message):

   bot_token = config['telegram'].get('bot_token')
   bot_chatID = config['telegram'].get('chat_id')
   send_text = 'https://api.telegram.org/bot' + bot_token + '/sendMessage?chat_id=' + bot_chatID + '&parse_mode=Markdown&text=' + bot_message

   response = requests.get(send_text)

   return response.json()

def GetData(sql):

    mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    auth_plugin='mysql_native_password',
    database=config['mysql'].get('database'),
    collation='utf8mb4_unicode_ci',
    password=config['mysql'].get('password')
)

    mycursor = mydb.cursor()

    mycursor.execute(sql)

    myresult = mycursor.fetchall()

    return myresult

def checkcall(counter):
    try:
        while True:
           sleep(2)
           for x in GetData("SELECT * FROM phonecalls ORDER BY ID DESC LIMIT 1"):
                phonenumber = x[1]
                name = x[2]
                datevar = x[3]
                calldate[counter]=datevar

                counter+=1

                if counter==2:
                    counter = 0

                if calldate[0] != calldate[1]:
                 test = telegram_bot_sendtext(name+" called\n"+phonenumber+"\n"+str(datevar))
                 print(name)
                 print(phonenumber)
                 print(str(datevar))
    except:
      pass    

def unknown_text_commands(update: Update, context: CallbackContext):
    message = update.message.text
    callval = message.find("/calls")
    weatherval = message.find("/weather")
    webhitsval= message.find("/webhits")
    clementsprojectsval= message.find("/clementsprojects")
    length = len(message)
    count=0
    calls=""
    weatherloc=""
    webhits=""
    clementsprojects=""
    if (callval > -1) or (weatherval > -1) or (webhitsval >-1) or (clementsprojectsval >-1):
        if callval > -1:
        #Show more than one calls 
            for mess in message:
                if count>=6:
                    calls = calls + message[count]
                count+=1
                #print(str(count)+" "+calls)  
            if callval > -1 and length > 6:
                context.bot.send_message(chat_id=update.effective_chat.id, \
                text="Here is the last "+calls+" calls")

                for x in GetData("SELECT * FROM phonecalls ORDER BY ID DESC LIMIT "+calls):
                    phonenumber = x[1]
                    name = x[2]
                    datevar = x[3]

                    context.bot.send_message(chat_id=update.effective_chat.id, \
                    text=name+" called with number "+phonenumber+" at "+str(datevar)+"\n")

        if clementsprojectsval > -1:
            print(clementsprojectsval)
            count=0
        #Show more than one calls 
            for mess in message:
                if count>=17:
                    clementsprojects = clementsprojects + message[count]
                count+=1
                #print(str(count)+" "+calls)  
            if clementsprojectsval > -1 and length > 14:
                context.bot.send_message(chat_id=update.effective_chat.id, \
                text="Here is the last "+clementsprojects+" Clements Projects view")

                for x in GetData("SELECT DISTINCT Date,Name,Email,IP,Location FROM Clements_Projects ORDER BY Date DESC LIMIT "+clementsprojects):
                    datevar = x[0]
                    name = x[1]
                    email = x[2]
                    ip=x[3]
                    location=x[4]

                    context.bot.send_message(chat_id=update.effective_chat.id, \
                    text="A new view to Clements Projects for "+name+" and email "+email+" the "+str(datevar)+" with ip "+ip+" from "+location+"\n")

        if webhitsval>-1:
              print(webhitsval)
              count=0
              for mess in message:
                if count>=8:
                    webhits = webhits + message[count]
                count+=1
                #print(str(count)+" "+webhits)

              if  webhitsval > -1 and length > 8:
                print("Here is the last "+webhits+" webhits")
                context.bot.send_message(chat_id=update.effective_chat.id, \
                text="Here is the last "+webhits+" webhits") 

                url = "https://rj2shop.com/webhits?user="+str(webhits)

                # Fetch the JSON data from the URL
                response = requests.get(url)
                # Check if the request was successful 
                if response.status_code == 200: 
                # Parse the JSON data 
                  data = response.json()
                  #data=response.text
                  # Alternatively: json.loads(response.text)
                  print(data)

                  if isinstance(data, list):
                    for item in data:
                        datevar = item.get("date")
                        ip = item.get("ip")
                        location = item.get("location")
                        type_var = item.get("type")

                        print(datevar)
                        print(ip)
                        print(location)
                        print(type_var)

                        context.bot.send_message(chat_id=update.effective_chat.id, \
                        text="The last webhit was made the "+str(datevar)+" with ip "+ip+" from "+location+" for "+type_var)    
                        
        if weatherval>-1:
            count=0
            #Weather location find            
            for mess in message:
                if count>=8:
                    weatherloc = weatherloc + message[count]
                count+=1
                print(str(count)+" "+weatherloc)  
            if weatherval > -1 and length > 8:
                print(weatherloc)
            context.bot.send_message(chat_id=update.effective_chat.id, \
            text=DoWeather(weatherloc))
         
    elif (message.find("/")>-1 and callval!=0) or (message.find("/")>-1 and weatherval!=0) or (message.find("/")>-1 and webhitsval!=0) or (message.find("/")>-1 and xmasgreetings!=0):
        context.bot.send_message(chat_id=update.effective_chat.id, \
        text="Sorry '%s' is not a valid command" % update.message.text)            
        
    else:
        context.bot.send_message(chat_id=update.effective_chat.id, \
        text=update.message.text)

# Telegram message handler

def ask(update: Update, context: CallbackContext):
    update.message.reply_text("Send me any message and I'll reply with AI magic!")

def start(update: Update, context: CallbackContext):
    context.bot.send_message(chat_id=update.effective_chat.id, \
    text="I'm a bot, please talk to me!")

def Convert(x):
    with open('sysinfo.txt','w+') as File:
         File.write(str(x))
	#convert.sh = "cat sysinfo.txt | sed 's/\\n/\n/g' > sysinfo.fix"
    s=subprocess.check_output(["./convert.sh"])
    with open('sysinfo.fix','r+') as File:
         x=File.read()
    return x


def sysinfo(update: Update, context: CallbackContext):
    s=subprocess.check_output(["inxi","-F"])
    context.bot.send_message(chat_id=update.effective_chat.id, \
    text=str(Convert(str(s))))

def DoWeather(weatherloc):
    data = requests.get(f"https://api.openweathermap.org/data/2.5/weather?q={weatherloc}&units=metric&APPID={api_key}")
    x=f"Location: {data.json().get('name')}, {data.json().get('sys').get('country')}"+"\n"
    x+=f"Temperature: {data.json().get('main')['temp']}°C"+"\n"
    x+=f"Weather: {data.json().get('weather')[0].get('main')}"+"\n"
    x+=f"Min/Max Temperature: {data.json().get('main')['temp_min']}°C/{data.json().get('main')['temp_max']}°C"+"\n"
    x+=f"Humidity: {data.json().get('main')['humidity']}%"+"\n"
    x+=f"Wind: {data.json().get('wind')['speed']} km/h"
    return x

def weather(update: Update, context: CallbackContext):
    context.bot.send_message(chat_id=update.effective_chat.id, \
    text=DoWeather("Boucherville"))

def help(update: Update, context: CallbackContext):
    context.bot.send_message(chat_id=update.effective_chat.id, \
    text="""/calls - To get the last call.
calls(x) - Show the last x calls ex: /calls5 
shows the 5 last calls.
/failed - To get the last failed attempt
to login onto my Linux computer.
/sysinfo - Give a detailed report of my
Linux computer.
/weather - Gives weather for Boucherville, Québec
weather(x) - Show weather for certain location
ex: /weatherorlando shows weather in Orlando
/webhits - Gives the webhits of RJ2Shop
webhits(x) Shows last x hits ex:/webhits5
/clementsprojects - name and email of Clements Projects
clementsprojects(x) Shows last x greetings ex:/clementsprojects5
/help - This is the help commands.
Any other text - Claude the AI Assistant""") 

def webhits(update: Update, context: CallbackContext):     
    
        url = "https://rj2shop.com/webhits?user=1"

        # Fetch the JSON data from the URL
        response = requests.get(url)
        # Check if the request was successful 
        if response.status_code == 200: 
            # Parse the JSON data 
           data = response.json()
           #data=response.text
             # Alternatively: json.loads(response.text)
           print(data)

           if isinstance(data, list):
             for item in data:
                 datevar = item.get("date")
                 ip = item.get("ip")
                 location = item.get("location")
                 type_var = item.get("type")

           print(datevar)
           print(ip)
           print(location)
           print(type_var)

           context.bot.send_message(chat_id=update.effective_chat.id, \
           text="The last webhit was made the "+str(datevar)+" with ip "+ip+" from "+location+" for "+type_var)
    
def clementsprojects(update: Update, context: CallbackContext):

    for x in GetData("SELECT * FROM Clements_Projects ORDER BY ID DESC LIMIT 1"):
        datevar = x[1]
        name = x[2]
        email = x[3]
        ip=x[4]
        location=x[5]

    context.bot.send_message(chat_id=update.effective_chat.id, \
    text="A new view of Clements Projects for "+name+" and email "+email+" the "+str(datevar)+" with ip "+ip+" from "+location+"\n")

def calls(update: Update, context: CallbackContext):

    for x in GetData("SELECT * FROM phonecalls ORDER BY ID DESC LIMIT 1"):
        phonenumber = x[1]
        name = x[2]
        datevar = x[3]

    context.bot.send_message(chat_id=update.effective_chat.id, \
    text="The last call was "+name+" with number "+phonenumber+" at "+str(datevar))

def unknown_text(update: Update, context: CallbackContext):
    user_input = update.message.text
    ai_response = ask_openrouter(user_input)
    update.message.reply_text(ai_response)

def failed(update: Update, context: CallbackContext):

    for x in GetData("SELECT message FROM failed ORDER BY ID DESC LIMIT 1"):
        message = x[0]

    context.bot.send_message(chat_id=update.effective_chat.id, \
    text="The last failed attempt to log onto my computer was\n"+message)

dispatcher = updater.dispatcher

from telegram.ext import CommandHandler
start_handler = CommandHandler('hi', start)
dispatcher.add_handler(start_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('calls', calls)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('failed', failed)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('sysinfo', sysinfo)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('weather', weather)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('clementsprojects', clementsprojects)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('webhits', webhits)
dispatcher.add_handler(calls_handler)

from telegram.ext import CommandHandler
calls_handler = CommandHandler('help', help)
dispatcher.add_handler(calls_handler)

dispatcher.add_handler(CommandHandler("ask", ask))
dispatcher.add_handler(MessageHandler(Filters.text & ~Filters.command, unknown_text))

#x = threading.Thread(target=checkcall, args=(0,), daemon=False)
#fx.start()

updater.dispatcher.add_handler(MessageHandler(Filters.text, unknown_text_commands))

updater.start_polling()

import openpyxl
from openpyxl import Workbook
from unicodedata import decimal
import numpy as np
from datetime import datetime
import pyodbc
import pandas as pd

connection_string = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\Users\rchrd\Documents\Richard\Richards_Health.mdb;'
)

cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()


# SELECT Diabetes.Datevar, Diabetes.Timevar, Diabetes.Reading
# FROM Diabetes
# WHERE (((Diabetes.Datevar)>#8/28/2022#) And ((Diabetes.Timevar)<=#12/30/1899 7:0:0#) And ((Year(Diabetes.Datevar))=2022))
# ORDER BY Diabetes.Datevar DESC;

SQL1="""
{CALL Mourning_Gluclose_reading}
"""

crsr.execute(SQL1)

rows = crsr.fetchall()

print(rows)

workbook = openpyxl.load_workbook("C:\Users\rchrd\Documents\Richard\Diabetes.xlsm",read_only=False,keep_vba=True)

sheet = workbook.active

Datevar = []
Average_Reading = []
Timevar = []
x=4
count=0

sheet["A3"]="Richard's Mourning Glucose Reading"

for row in rows:
   x+=1
   Datevar.append((row[0]))
   sheet["A"+str(x)]=Datevar[count]
   Timevar.append((row[1]))
   sheet["B"+str(x)]=Timevar[count]
   Average_Reading.append(float(row[2]))
   sheet["C"+str(x)]=Average_Reading[count]
   count+=1

cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()


# SELECT Diabetes.Datevar, Diabetes.Timevar, Diabetes.Reading
# FROM Diabetes
# WHERE (((Diabetes.Datevar)>#8/28/2022#) AND ((Diabetes.Timevar)>=#12/30/1899 7:0:0# And (Diabetes.Timevar)<#12/30/1899 17:0:0#))
# ORDER BY Diabetes.Datevar DESC;

SQL1="""
{CALL Afternoon_Glucose_Reading}
"""

crsr.execute(SQL1)

rows = crsr.fetchall()

print(rows)

Datevar = []
Average_Reading = []
Timevar = []
x=4
count=0

sheet["E3"]="Richard's Afternoon Glucose Reading"

for row in rows:
   x+=1
   Datevar.append((row[0]))
   sheet["E"+str(x)]=Datevar[count]
   Timevar.append((row[1]))
   sheet["F"+str(x)]=Timevar[count]
   Average_Reading.append(float(row[2]))
   sheet["G"+str(x)]=Average_Reading[count]
   count+=1   

cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()

# SELECT Diabetes.Datevar, Diabetes.Timevar, Diabetes.Reading
# FROM Diabetes
# WHERE (((Diabetes.Datevar)>#8/28/2022#) And ((Diabetes.Timevar)>=#12/30/1899 17:0:0#) And ((Year(Diabetes.Datevar))=2022))
# ORDER BY Diabetes.Datevar DESC;

SQL1="""
{CALL Evening_Gluclose_reading}
"""

crsr.execute(SQL1)

rows = crsr.fetchall()

print(rows)

Datevar = []
Average_Reading = []
Timevar = []
x=4
count=0

sheet["i3"]="Richard's Evening Glucose Reading"

for row in rows:
   x+=1
   Datevar.append((row[0]))
   sheet["i"+str(x)]=Datevar[count]
   Timevar.append((row[1]))
   sheet["j"+str(x)]=Timevar[count]
   Average_Reading.append(float(row[2]))
   sheet["k"+str(x)]=Average_Reading[count]
   count+=1      

workbook.save(filename="C:\Users\rchrd\Documents\Richard\Diabetes.xlsm")