import json
import os
from datetime import datetime, timedelta

import pyodbc
import requests
from bs4 import BeautifulSoup

from dotenv import load_dotenv
load_dotenv() # take environment variables from .env.

messageTo = os.getenv('MESSAGE_IS')
driverFrom = {'lxc': '/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1', 'windows': '{ODBC Driver 17 for SQL Server}'}

bot_token = os.getenv('TELEGRAM_BOT_TOKEN')

server = os.getenv('DB_SERVER')
database = os.getenv('DB_DATABASE')
username = os.getenv('DB_USERNAME')
password = os.getenv('DB_PASSWORD')
cnxn = pyodbc.connect(
    'DRIVER=' + driverFrom.get('lxc') + ';SERVER=' + server + ';' +
    'DATABASE=' + database + ';UID=' + username + ';PWD=' + password + ';Encrypt=no')


def main():
    with open('datetime.json') as json_file:
        strDatetime = json.load(json_file)
        date = datetime.fromisoformat(strDatetime) + timedelta(microseconds=10)
        #MS SQL хранит на одно значение больше в микросекундах, поэтому 713372 (Python) < 7133722 (MS SQL)


    cursor = cnxn.cursor()
    cursor.execute("SELECT Id, Title, Body, DateCreate FROM Newses "
                   "WHERE Newses.IsDeleted = 0 AND Newses.DateCreate > ? "
                   "ORDER BY Newses.DateCreate", date)  # например - 2022-04-02 11:19:29.778400
    row = cursor.fetchone()

    while row:
        soup = BeautifulSoup(row[2], features="html.parser")
        textNews = soup.get_text('\n').replace("\n\n\n\n", "\n").replace("\n\n\n", "\n").replace("\n\n", "\n")
        if len(textNews) > 3000:
            textNews = textNews[:3000] + '...'
        params = {'chat_id': messageTo.get('is'),
                  'text': f'{row[1]}\n{textNews}\nhttp://is.ulstu.ru/News/ShowNews/{row[0]}'}
        requests.get(f'https://api.telegram.org/bot{bot_token}/sendMessage',
                     params=params)

        date = row[3]
        row = cursor.fetchone()


    with open('datetime.json', 'w') as outfile:
        json.dump(str(date), outfile)


if __name__ == '__main__':
    main()