app.py

from flask import Flask, request, render_template_string, render_template, jsonify
import sqlite3
import os
import random
import requests
import time
import re
import json
import base64
import logging

from unidecode import unidecode

api_key_sys = os.getenv('api_key_sys')  
# Ваш API-ключ
gc_url_gru = os.getenv('gc_url_gru')

gc_url_export = os.getenv('gc_url_export')  # URL для экспорта данных

start_up = os.getenv('start_up')   

gc_url = os.getenv('gc_url') 
gc_url_form = os.getenv('gc_url_form')  
gc_api = os.getenv('gc_api')  

wa_url = os.getenv('wa_url') 
wa_api_key = os.getenv('wa_api_key') 


up_db = os.getenv('up_db') 

id_gru = os.getenv('id_gru') 
date_from = "2022-01-01"


export_id = ""  

code_executed = False


status = "active"








app = Flask(__name__, template_folder="./")
app.config['DEBUG'] = True
UPLOAD_FOLDER = 'static'
logging.basicConfig(level=logging.DEBUG)
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

DATABASES = ['data_gc.db', 'data1.db', 'data2.db', 'data3.db', 'data4.db', 'data5.db']

def init_db(db_name):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS contacts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            phone TEXT NOT NULL,
            email TEXT NOT NULL,
            vk_id TEXT NOT NULL,
            chat_id TEXT NOT NULL,
            ws_st TEXT NOT NULL,
            ws_stop TEXT NOT NULL,
            web_st INTEGER,
            fin_prog INTEGER,
            b_city TEXT NOT NULL,
            b_fin TEXT NOT NULL,
            b_ban TEXT NOT NULL,
            b_ign TEXT NOT NULL,
            b_baners TEXT NOT NULL,
            b_butt TEXT NOT NULL,
            b_mess TEXT NOT NULL,           
            shop_st TEXT NOT NULL,
            curator TEXT NOT NULL,
            pr1 TEXT NOT NULL,
            pr2 TEXT NOT NULL,
            pr3 TEXT NOT NULL,
            pr4 TEXT NOT NULL,
            pr5 TEXT NOT NULL,
            ad_url TEXT NOT NULL,
            key_pr TEXT NOT NULL,
            n_con TEXT NOT NULL,
            canal TEXT NOT NULL,
            data_t TEXT NOT NULL
        )
    ''')
    conn.commit()
    conn.close()

for db in DATABASES:
    init_db(db)



template = {
    "username": "name",
    "phone": "phone",
    "email": "email",
    "city": "b_city",
    "finished": "b_fin",
    "ban": "b_ban",
    "ignore": "b_ign",
    "banners": "b_baners",  
    "buttons": "b_butt",    
    "messages": "b_mess"
}

mapping_template = {
    "username": "name",
    "phone": "phone",
    "email": "email",
    "city": "b_city",
    "finished": "b_fin",
    "ban": "b_ban",
    "ignore": "b_ign",
    "banners": "b_baners",  
    "buttons": "b_butt",    
    "messages": "b_mess"
}








# Функция для выполнения HTTP запросов
def fetch(url):
    try:
        response = requests.get(url)
        response.raise_for_status()
        print(f"GET запрос к {url} выполнен успешно.")
        return response.json()
    except requests.RequestException as e:
        print(f"Ошибка при выполнении GET запроса к {url}: {e}")
        return None

# Функция для отправки оповещения на другой сервер
def send_notification(url, data):
    try:
        response = requests.post(url, json=data)
        response.raise_for_status()
        print(f"POST запрос к {url} выполнен успешно.")
        return response.json()
    except requests.RequestException as e:
        print(f"Ошибка при выполнении POST запроса к {url}: {e}")
        return None

# Основная функция для отправки запросов
def initialize_requests():
    global code_executed, export_id
    print(f"Функция initialize_requests вызвана. start_up: {start_up}, code_executed: {code_executed}")

    if start_up == '1' and not code_executed:
        try:
            # Первый запрос
            url_template = f"{gc_url_gru}/{id_gru}/users?key={gc_api}&created_at[from]={date_from}&status={status}"
            data = fetch(url_template)
            if data and data.get("success"):
                export_id = data.get("info", {}).get("export_id", "")
                print("Export ID:", export_id)  # Отладочное сообщение

                # Отправка оповещения на другой сервер
                notification_url = "https://skyauto.me/cllbck/217669590/29245685/bGZuMDRZZUpLZ3VJR2oxcC9CQmh0UT0?api=1&uid=535939344"
                notification_data = {
                    "message": "Первый запрос был выполнен",
                    "export_id": export_id
                }
                notification_response = send_notification(notification_url, notification_data)
                print("Ответ от сервера оповещения:", notification_response)

                code_executed = True  # Устанавливаем флаг выполнения кода
            else:
                raise Exception(f"Ошибка в ответе от сервера: {data.get('error_message') if data else 'Нет данных'}")
        except Exception as e:
            print(f"Ошибка: {e}")
    else:
        print("Системная переменная start_up не равна '1' или код уже выполнялся.")





















# Маршрут для экспорта пользователя
@app.route('/export_user', methods=['GET'])
def export_user():
    try:
        export_id = request.args.get('export_id')
        if not export_id:
            raise Exception("export_id не найден в параметрах запроса")

        print(f"Получен export_id: {export_id}")  # Отладочное сообщение

        # Отправка третьего запроса для выгрузки базы данных
        third_url_template = f"{gc_url_export}/{export_id}?key={gc_api}"
        response = fetch(third_url_template)
        if response and response.get("success"):
            print("Ответ сервера:")
            print(response)  # Вывод ответа сервера в консоль сервера
            return jsonify(response), 200
        else:
            raise Exception(f"Ошибка в ответе от сервера: {response.get('error_message') if response else 'Нет данных'}")
    except Exception as e:
        print(f"Ошибка: {e}")  # Вывод ошибки в консоль сервера
        return jsonify({"error": str(e)}), 500


















def send_second_request(export_id):
    if export_id is None:
        raise Exception("export_id is None")
    # Формирование URL для второго запроса
    export_url_template = f"https://school.riverpsy.com/pl/api/account/exports/{export_id}?key=jqgxSMUnHWoKUcxF3MHSb77VUMk7HpFbO9SHnfVYwHtwqe1S81lqeKxrLPoSPWCephtYQuJwMFsCXEFmyByXdruDpDFgf6L7ij66K9ji0Kf2qAIwbTqEyJGB5MOHwyHl"
    try:
        response = requests.get(export_url_template)
        response.raise_for_status()
        return response.json()  # Возвращаем JSON-ответ сервера
    except requests.RequestException as e:
        raise Exception(f"Ошибка при выполнении запроса: {e}")

def load_data_from_json(json_data):
    if 'info' not in json_data or 'items' not in json_data['info'] or 'fields' not in json_data['info']:
        raise ValueError("Invalid JSON structure")

    items = json_data['info']['items']
    fields = json_data['info']['fields']

    db = 'data_gc.db'  # Указываем конкретную базу данных
    conn = sqlite3.connect(db)
    cursor = conn.cursor()

    for item in items:
        user_data = dict(zip(fields, item))
        # Проверяем наличие значений для полей и устанавливаем значения по умолчанию, если они отсутствуют
        user_data.setdefault('vk_id', '')
        user_data.setdefault('chat_id', '')
        user_data.setdefault('ws_st', '')
        user_data.setdefault('ws_stop', '')
        user_data.setdefault('web_st', '')
        user_data.setdefault('fin_prog', '')
        user_data.setdefault('b_city', '')
        user_data.setdefault('b_fin', '')
        user_data.setdefault('b_ban', '')
        user_data.setdefault('b_ign', '')
        user_data.setdefault('b_baners', '')  
        user_data.setdefault('b_butt', '')
        user_data.setdefault('b_mess', '')
        user_data.setdefault('shop_st', '')
        user_data.setdefault('curator', '')
        user_data.setdefault('pr1', '')
        user_data.setdefault('pr2', '')
        user_data.setdefault('pr3', '')
        user_data.setdefault('pr4', '')
        user_data.setdefault('pr5', '')
        user_data.setdefault('ad_url', '')
        user_data.setdefault('key_pr', '')
        user_data.setdefault('n_con', '')        
        user_data.setdefault('canal', '')
        user_data.setdefault('data_t', '')
        # Убираем плюс в начале телефона, если он присутствует
        if 'Телефон' in user_data and user_data['Телефон'].startswith('+'):
            user_data['Телефон'] = user_data['Телефон'][1:]

        query = '''
            INSERT INTO contacts (
                name, phone, email, vk_id, chat_id, ws_st, ws_stop, web_st, fin_prog, b_city, b_fin, b_ban, b_ign, b_baners, b_butt, b_mess, shop_st, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, canal, data_t
            ) VALUES (
                :Имя, :Телефон, :Email, :vk_id, :chat_id, :ws_st, :ws_stop, :web_st, :fin_prog, :b_city, :b_fin, :b_ban, :b_ign, :b_baners, :b_butt, :b_mess, :shop_st, :pr1, :pr2, :pr3, :pr4, :pr5, :ad_url, :curator, :key_pr, :n_con, :canal, :data_t
            )
            '''
        cursor.execute(query, user_data)

    conn.commit()
    conn.close()














@app.route('/start', methods=['GET'])
def start():
    export_id = request.args.get('export_id')
    api_key_sys_control = request.args.get('api_sys')

    if export_id is None:
        return json.dumps({"error": "export_id is required"}), 400

    if api_key_sys_control != api_key_sys:
        return json.dumps({"error": "Unauthorized access"}), 403

    try:
        json_data = send_second_request(export_id)
        load_data_from_json(json_data)
        return "Data loaded successfully", 200
    except Exception as e:
        return json.dumps({"error": str(e)}), 500





    
def randomize_message(template):
    def replace_placeholder(match):
        options = match.group(1).split('|')
        return random.choice(options)

    return re.sub(r'\{([^}]+)\}', replace_placeholder, template)

def clean_phone_number(phone):
    if phone.startswith('+'):
        return phone[1:]
    return phone

def send_message(chat_id, message):
    #base_url = os.getenv('wa_url')
    #api_key = os.getenv('wa_api_key')
    full_url = f"{wa_url}{wa_api_key}"
    payload = {
        "chatId": chat_id,
        "message": message
    }
    headers = {
        'Content-Type': 'application/json'
    }
    response = requests.request("POST", full_url, headers=headers, json=payload)
    try:
        response_json = response.json()
    except ValueError:
        response_json = {"error": "Invalid JSON response"}
    return response_json

def check_and_send_mailings(mesage_db1, clean_db):
    try:
        results = []
        for database in DATABASES:
            conn = sqlite3.connect(database)
            cursor = conn.cursor()
            cursor.execute('SELECT name, phone FROM contacts')
            contacts = cursor.fetchall()
            conn.close()

            for contact in contacts:
                name = contact[0]
                chat_id = f"{clean_phone_number(contact[1])}@c.us"
                message = randomize_message(mesage_db1)
                message = message.replace('[[nemes]]', name)  # Подстановка имени
                send_result = send_message(chat_id, message)
                results.append({
                    "chat_id": chat_id,
                    "message": message,
                    "result": send_result
                })

            if clean_db == '1':
                conn = sqlite3.connect(database)
                cursor = conn.cursor()
                cursor.execute('DELETE FROM contacts')
                conn.commit()
                conn.close()

        return jsonify({"status": "success", "results": results}), 200
    except Exception as e:
        print(f"Error sending mailings: {e}")
        return jsonify({"status": "error", "message": str(e)}), 500

#С проверкой sys
@app.route('/start_db', methods=['GET'])
def start_mailings():
    mesage_db1 = request.args.get('mesage')
    clean_db = request.args.get('clean_db')
    api_sys_control = request.args.get('api_sys')
    

    if not mesage_db1:
        return "Parameter 'mesage' is required.", 400

    if api_sys_control != api_key_sys:
        return "EUR 22", 200

    return check_and_send_mailings(mesage_db1, clean_db)




        


@app.route('/add_data_gc', methods=['GET'])
def add_data_gc():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "err api key", 200

        name = request.args.get('name')
        phone = request.args.get('phone')
        email = request.args.get('email')
        vk_id = request.args.get('vk_id', '')
        chat_id = request.args.get('chat_id')
        ws_statys = request.args.get('ws_st')
        ws_stop = request.args.get('ws_stop')
        web_statys = request.args.get('web_st', 0, type=int)
        fin_progress = request.args.get('fin_prog', 0, type=int)
        shop_statys_full = request.args.get('shop_st')
        pr1 = request.args.get('pr1')
        pr2 = request.args.get('pr2')
        pr3 = request.args.get('pr3')
        pr4 = request.args.get('pr4')
        pr5 = request.args.get('pr5')
        ad_url = request.args.get('ad_url')
        curator = request.args.get('curator')
        key_pr = request.args.get('key_pr', '')
        n_con = request.args.get('n_con', '')
        canal = request.args.get('canal', '')

        if not name or not phone or not email:
            return "Parameters 'name', 'phone', and 'email' are required.", 400

        # Clean up phone number by removing any leading plus sign
        if phone.startswith('+'):
            phone = phone[1:]

        # Получение текущего времени в московском часовом поясе
        utc_now = datetime.utcnow()
        msk_tz = pytz.timezone('Europe/Moscow')
        msk_now = utc_now.replace(tzinfo=pytz.utc).astimezone(msk_tz)
        data_t = msk_now.strftime('%Y-%m-%d %H:%M:%S')

        conn = sqlite3.connect('data_gc.db')
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM contacts WHERE phone = ? OR email = ?', (phone, email))
        existing_contact = cursor.fetchone()

        if existing_contact:
            cursor.execute('''
                UPDATE contacts SET
                    name = ?, email = ?, vk_id = ?, chat_id = ?, ws_st = ?, ws_stop = ?, 
                    web_st = ?, fin_prog = ?, shop_st = ?, pr1 = ?, pr2 = ?, 
                    pr3 = ?, pr4 = ?, pr5 = ?, ad_url = ?, curator = ?, key_pr = ?, n_con = ?, 
                    canal = ?, data_t = ?
                WHERE phone = ? OR email = ?
            ''', (name, email, vk_id, chat_id, ws_statys, ws_stop, web_statys, fin_progress, 
                  shop_statys_full, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, 
                  canal, data_t, phone, email))
        else:
            cursor.execute('''
                INSERT INTO contacts (
                    name, phone, email, vk_id, chat_id, ws_st, ws_stop, web_st, fin_prog, 
                    shop_st, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, canal, data_t
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (name, phone, email, vk_id, chat_id, ws_statys, ws_stop, web_statys, fin_progress, 
                  shop_statys_full, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, canal, data_t))

        conn.commit()
        conn.close()

        return f"Contact updated/added: {name} - {phone} - {email}", 200
    except Exception as e:
        print(f"Error adding/updating contact: {e}")
        return "Internal Server Error", 500








@app.route('/add_data_ras', methods=['GET'])
def add_data_ras():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        name = request.args.get('name')
        phone = request.args.get('phone')
        email = request.args.get('email')
        vk_id = request.args.get('vk_id', '')
        chat_id = request.args.get('chat_id', '')
        ws_statys = request.args.get('ws_st', '')
        ws_stop = request.args.get('ws_stop', '')
        web_statys = request.args.get('web_st', 0, type=int)
        fin_progress = request.args.get('fin_prog', 0, type=int)
        shop_statys_full = request.args.get('shop_st', '')
        pr1 = request.args.get('pr1', '')
        pr2 = request.args.get('pr2', '')
        pr3 = request.args.get('pr3', '')
        pr4 = request.args.get('pr4', '')
        pr5 = request.args.get('pr5', '')
        ad_url = request.args.get('ad_url', '')
        curator = request.args.get('curator', '')
        key_pr = request.args.get('key_pr', '')
        n_con = request.args.get('n_con', '')
        canal = request.args.get('canal', '')

        if not name or not phone or not email:
            return "Parameters 'name', 'phone', and 'email' are required.", 400

        # Clean up phone number by removing any leading plus sign
        if phone.startswith('+'):
            phone = phone[1:]

        # Получение текущего времени в московском часовом поясе
        utc_now = datetime.utcnow()
        msk_tz = pytz.timezone('Europe/Moscow')
        msk_now = utc_now.replace(tzinfo=pytz.utc).astimezone(msk_tz)
        data_t = msk_now.strftime('%Y-%m-%d %H:%M:%S')

        conn = sqlite3.connect('data1.db')
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM contacts WHERE phone = ? OR email = ?', (phone, email))
        existing_contact = cursor.fetchone()

        if existing_contact:
            cursor.execute('''
                UPDATE contacts SET
                    name = ?, email = ?, vk_id = ?, chat_id = ?, ws_st = ?, ws_stop = ?, 
                    web_st = ?, fin_prog = ?, shop_st = ?, pr1 = ?, pr2 = ?, 
                    pr3 = ?, pr4 = ?, pr5 = ?, ad_url = ?, curator = ?, key_pr = ?, n_con = ?, 
                    canal = ?, data_t = ?
                WHERE phone = ? OR email = ?
            ''', (name, email, vk_id, chat_id, ws_statys, ws_stop, web_statys, fin_progress, 
                  shop_statys_full, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, 
                  canal, data_t, phone, email))
        else:
            cursor.execute('''
                INSERT INTO contacts (
                    name, phone, email, vk_id, chat_id, ws_st, ws_stop, web_st, fin_prog, 
                    shop_st, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, canal, data_t
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (name, phone, email, vk_id, chat_id, ws_statys, ws_stop, web_statys, fin_progress, 
                  shop_statys_full, pr1, pr2, pr3, pr4, pr5, ad_url, curator, key_pr, n_con, canal, data_t))

        conn.commit()
        conn.close()

        return f"Contact updated/added: {name} - {phone} - {email}", 200
    except Exception as e:
        print(f"Error adding/updating contact: {e}")
        return "Internal Server Error", 500



    


       




        



@app.route('/data_gc')
def show_data_gc():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        conn = sqlite3.connect('data_gc.db')
        cursor = conn.cursor()
        cursor.execute('SELECT name, phone, email FROM contacts')
        contacts = cursor.fetchall()
        cursor.execute('SELECT COUNT(*) FROM contacts')
        total_users = cursor.fetchone()[0]
        conn.close()

        return render_template('data_gc.html', contacts=contacts, total_users=total_users)
    except Exception as e:
        print(f"Error showing contacts: {e}")
        return "Internal Server Error", 500


@app.route('/data_ras')
def show_data_ras():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        conn = sqlite3.connect('data1.db')
        cursor = conn.cursor()
        cursor.execute('SELECT name, phone, email FROM contacts')
        contacts = cursor.fetchall()
        cursor.execute('SELECT COUNT(*) FROM contacts')
        total_users = cursor.fetchone()[0]
        conn.close()

        return render_template('data_ras.html', contacts=contacts, total_users=total_users)
    except Exception as e:
        print(f"Error showing contacts: {e}")
        return "Internal Server Error", 500

































@app.route('/data_gc_tab', methods=['GET'])
def data_gc_tab():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('data_gc_tab.html')







@app.route('/data_gc_tab_out', methods=['GET'])
def data_gc_tab_out():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        conn = sqlite3.connect('data_gc.db')
        cursor = conn.cursor()
        cursor.execute('''
            SELECT id, name, phone, email, vk_id, chat_id, ws_st, ws_stop, web_st, fin_prog,
            b_city, b_fin, b_ban, b_ign, b_baners, b_butt, b_mess, shop_st, curator,
            pr1, pr2, pr3, pr4, pr5, ad_url, key_pr, n_con, canal, data_t
            FROM contacts
        ''')
        contacts = cursor.fetchall()
        conn.close()

        contacts_json = [{
            'id': contact[0], 'name': contact[1], 'phone': contact[2], 'email': contact[3],
            'vk_id': contact[4], 'chat_id': contact[5], 'ws_st': contact[6], 'ws_stop': contact[7],
            'web_st': contact[8], 'fin_prog': contact[9], 'b_city': contact[10], 'b_fin': contact[11],
            'b_ban': contact[12], 'b_ign': contact[13], 'b_baners': contact[14], 'b_butt': contact[15],
            'b_mess': contact[16], 'shop_st': contact[17], 'curator': contact[18], 'pr1': contact[19],
            'pr2': contact[20], 'pr3': contact[21], 'pr4': contact[22], 'pr5': contact[23],
            'ad_url': contact[24], 'key_pr': contact[25], 'n_con': contact[26], 'canal': contact[27],
            'data_t': contact[28]
        } for contact in contacts]
        return jsonify(contacts_json), 200
    except Exception as e:
        error_message = f"Error getting data from data_gc: {e}"
        print(error_message)
        return error_message, 500



        
        


























@app.route('/send_request', methods=['POST'])
def send_request():
    token = request.form.get('token')
    min_date = request.form.get('minDate')
    url = f'https://online.bizon365.ru/api/v1/webinars/reports/getlist?minDate={min_date}'
    
    response = requests.get(url, headers={'X-Token': token})
    
    if response.status_code == 200:
        data = response.json()
        webinar_ids = [item['webinarId'] for item in data['list']]
        return jsonify(webinar_ids)
    else:
        return jsonify({'error': 'Failed to fetch data from the API'}), response.status_code







DATABASE_NAME = 'data_gc.db'

def update_or_insert_user(db_name, user_data, mapping_template):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # Получение email пользователя из данных
    email = user_data.get('email')
    if not email:
        logging.error(f"User data missing email: {user_data}")
        return

    logging.debug(f"Processing user with email: {email}")
    
    # Проверка существования пользователя в базе данных по email
    cursor.execute("SELECT web_st FROM contacts WHERE email = ?", (email,))
    user = cursor.fetchone()
    logging.debug(f"User found: {user}")

    # Вынесение увеличения значения web_st в отдельный блок
    web_st_value = 3  # Инициализация значения web_st
    if user:
        # Проверка текущего значения web_st и его инкрементация
        current_web_st = user[0] if user[0] is not None and user[0] != "" else 0
        web_st_value = int(current_web_st) + 1
        logging.debug(f"Calculated web_st_value: {web_st_value}")

        # Обновление значения web_st
        cursor.execute("UPDATE contacts SET web_st = ? WHERE email = ?", (web_st_value, email))
        conn.commit()
        conn.close()
        logging.debug(f"User {email} web_st updated to {web_st_value}")
    else:
        conn.close()
        logging.debug(f"User {email} not found, proceeding with insert")

    # Открываем соединение снова для остальных операций
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # Преобразование данных пользователя на основе шаблона сопоставления
    transformed_data = {}
    for json_key, db_column in mapping_template.items():
        value = user_data.get(json_key, "")
        if isinstance(value, list):
            transformed_data[db_column] = "1" if value else "0"
        else:
            transformed_data[db_column] = str(value)
    logging.debug(f"Transformed data: {transformed_data}")

    # Заполнение обязательных полей значениями по умолчанию
    required_fields = [
        "vk_id", "chat_id", "ws_st", "ws_stop", "web_st", "fin_prog",
        "b_city", "b_fin", "b_ban", "b_ign", "b_baners", "b_butt", "b_mess",
        "shop_st", "curator", "pr1", "pr2", "pr3", "pr4", "pr5", "ad_url",
        "key_pr", "n_con", "canal", "data_t"
    ]
    for field in required_fields:
        if field not in transformed_data:
            transformed_data[field] = ""
    logging.debug(f"Transformed data after adding required fields: {transformed_data}")

    # Обработка номера телефона, если он есть
    if 'phone' in user_data:
        phone = user_data['phone']
        if phone.startswith('+'):
            phone = phone[1:]
        transformed_data['phone'] = phone
    logging.debug(f"Transformed data after phone processing: {transformed_data}")

    # Добавление значения web_st в данные для вставки
    transformed_data['web_st'] = web_st_value

    # Обновление данных пользователя в базе данных
    if user:
        update_query = "UPDATE contacts SET "
        update_values = []
        for column, value in transformed_data.items():
            update_query += f"{column} = ?, "
            update_values.append(value)
        update_query = update_query.rstrip(", ") + " WHERE email = ?"
        update_values.append(email)
        logging.debug(f"Update query: {update_query} with values: {update_values}")
        cursor.execute(update_query, update_values)
    else:
        columns = ', '.join(transformed_data.keys())
        placeholders = ', '.join('?' for _ in transformed_data)
        insert_query = f"INSERT INTO contacts ({columns}) VALUES ({placeholders})"
        insert_values = list(transformed_data.values())
        logging.debug(f"Insert query: {insert_query} with values: {insert_values}")
        cursor.execute(insert_query, insert_values)

    # Подтверждение изменений и закрытие соединения
    conn.commit()
    conn.close()
    logging.debug(f"User with email {email} processed successfully")

@app.route('/send_get_request', methods=['GET'])
def send_get_request():
    token = request.args.get('token')
    webinarId = request.args.get('webinarId')
    url = f'https://online.bizon365.ru/api/v1/webinars/reports/get?webinarId={webinarId}'

    response = requests.get(url, headers={'X-Token': token})

    if response.status_code == 200:
        data = response.json()

        report = data.get('report', {})
        messages = data.get('messages', {})

        report_json_str = report.get('report', '{}')
        try:
            report_json = json.loads(report_json_str)
        except json.JSONDecodeError:
            report_json = {}

        messages_json_str = report.get('messages', '{}')
        try:
            messages_json = json.loads(messages_json_str)
        except json.JSONDecodeError:
            messages_json = {}

        users_meta = report_json.get('usersMeta', {})

        # Обновление или добавление каждого пользователя в базу данных data_gc.db
        processed_emails = set()
        for user_id, user_data in users_meta.items():
            user_data['messages'] = messages_json
            email = user_data.get('email')
            if email not in processed_emails:
                update_or_insert_user(DATABASE_NAME, user_data, mapping_template)
                processed_emails.add(email)

        return jsonify({'status': 'User data saved successfully'})
    else:
        return jsonify({'error': 'Failed to fetch data from the API'}), response.status_code





















@app.route('/biz_v', methods=['GET'])
def biz_v():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('biz_v.html')























@app.route('/ver', methods=['GET'])
def veref():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('ver.html')

@app.route('/se_mes', methods=['GET'])
def se_mes():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes.html')

@app.route('/se_mes_im', methods=['GET'])
def se_mes_im():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_im.html')

@app.route('/se_mes_ran', methods=['GET'])
def se_mes_ran():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_ran.html')

@app.route('/se_mes_im_ran', methods=['GET'])
def se_mes_im_ran():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_im_ran.html')

@app.route('/se_mes_im2', methods=['GET'])
def se_mes_im2():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_im2.html')

@app.route('/se_mes_f', methods=['GET'])
def se_mes_f():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_f.html')

@app.route('/up_gr', methods=['GET'])
def up_gr():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('up_gr.html')

@app.route('/up_user_gp', methods=['GET'])
def up_user_gp():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('up_user_gp.html')

@app.route('/del_user_gp', methods=['GET'])
def del_user_gp():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('del_user_gp.html')

@app.route('/up_ad', methods=['GET'])
def up_ad():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('up_ad.html')

@app.route('/del_ad', methods=['GET'])
def del_ad():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('del_ad.html')

@app.route('/se_opr', methods=['GET'])
def se_opr():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_opr.html')

@app.route('/online', methods=['GET'])
def online():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('online.html')

@app.route('/se_mes_f_gc', methods=['GET'])
def se_mes_f_gc():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('se_mes_f_gc.html')














@app.route('/total_users', methods=['GET'])
def total_users():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        total_users_gc = 0
        total_users_ras = 0

        conn_gc = sqlite3.connect('data_gc.db')
        cursor_gc = conn_gc.cursor()
        cursor_gc.execute('SELECT COUNT(*) FROM contacts')
        total_users_gc = cursor_gc.fetchone()[0]
        conn_gc.close()

        conn_ras = sqlite3.connect('data1.db')
        cursor_ras = conn_ras.cursor()
        cursor_ras.execute('SELECT COUNT(*) FROM contacts')
        total_users_ras = cursor_ras.fetchone()[0]
        conn_ras.close()

        return jsonify({
            "total_users_gc": total_users_gc,
            "total_users_ras": total_users_ras
        }), 200
    except Exception as e:
        print(f"Error getting total users: {e}")
        return "Internal Server Error", 500








@app.route('/all_users_gc', methods=['GET'])
def all_users_gc():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        conn = sqlite3.connect('data_gc.db')
        cursor = conn.cursor()
        cursor.execute('SELECT name, phone, email FROM contacts')
        contacts = cursor.fetchall()
        conn.close()

        return jsonify(contacts), 200
    except Exception as e:
        print(f"Error getting all users from data_gc: {e}")
        return "Internal Server Error", 500



@app.route('/all_users_ras', methods=['GET'])
def all_users_ras():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        conn = sqlite3.connect('data1.db')
        cursor = conn.cursor()
        cursor.execute('SELECT name, phone, email FROM contacts')
        contacts = cursor.fetchall()
        conn.close()

        return jsonify(contacts), 200
    except Exception as e:
        print(f"Error getting all users from data_ras: {e}")
        return "Internal Server Error", 500




        

@app.route('/gc_db_no_email', methods=['GET'])
def gc_db_no_email():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        # Чтение параметров из GET-запроса
        name_d = request.args.get('name', '')
        phone_d = request.args.get('phone', '')
        pr1_d = request.args.get('pr1', '')
        pr2_d = request.args.get('pr2', '')
        pr3_d = request.args.get('pr3', '')

        # Проверка базы данных на наличие email по номеру телефона
        conn = sqlite3.connect('data_gc.db')
        cursor = conn.cursor()
        cursor.execute('SELECT email FROM contacts WHERE phone = ?', (phone_d,))
        result = cursor.fetchone()
        email_d = result[0] if result else ''
        conn.close()

        # Формирование JSON
        json_data = {
            "user": {
                "email": email_d,
                "phone": phone_d,
                "first_name": name_d,
                "addfields": {
                    "pr1": pr1_d,
                    "pr2": pr2_d,
                    "pr3": pr3_d
                }
            },
            "system": {
                "refresh_if_exists": 1
            },
            "session": {
                "utm_source": "",
                "utm_medium": "",
                "utm_content": "",
                "utm_campaign": "",
                "utm_group": "",
                "gcpc": "",
                "gcao": "",
                "referer": ""
            }
        }

        # Конвертация JSON в Base64
        json_str = json.dumps(json_data)
        params_d = base64.b64encode(json_str.encode('utf-8')).decode('utf-8')

        # Данные для отправки в теле запроса
        data = {
            'key': gc_api,
            'action': 'add',
            'params': params_d
        }

        # Отправка POST-запроса с данными в формате "form-data"
        response = requests.post(gc_url, data=data)

        # Возвращаем ответ от тестового адреса
        return {
            'status_code': response.status_code,
            'response_body': response.text
        }
    except Exception as e:
        print(f"Error in gc_db_no_email: {e}")
        return "Internal Server Error", 500





@app.route('/gc_db_email', methods=['GET'])
def gc_db_email():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        # Чтение параметров из GET-запроса
        name_d = request.args.get('name', '')
        email_d = request.args.get('email', '')
        phone_d = request.args.get('phone', '')
        pr1_d = request.args.get('pr1', '')
        pr2_d = request.args.get('pr2', '')
        pr3_d = request.args.get('pr3', '')

        # Формирование JSON
        json_data = {
            "user": {
                "email": email_d,
                "phone": phone_d,
                "first_name": name_d,
                "addfields": {
                    "pr1": pr1_d,
                    "pr2": pr2_d,
                    "pr3": pr3_d
                }
            },
            "system": {
                "refresh_if_exists": 1
            },
            "session": {
                "utm_source": "",
                "utm_medium": "",
                "utm_content": "",
                "utm_campaign": "",
                "utm_group": "",
                "gcpc": "",
                "gcao": "",
                "referer": ""
            }
        }

        # Конвертация JSON в Base64
        json_str = json.dumps(json_data)
        params_d = base64.b64encode(json_str.encode('utf-8')).decode('utf-8')

        # Данные для отправки в теле запроса
        data = {
            'key': gc_api,
            'action': action_d,
            'params': params_d
        }

        # Отправка POST-запроса с данными в формате "form-data"
        response = requests.post(gc_url, data=data)

        # Возвращаем ответ от тестового адреса
        return {
            'status_code': response.status_code,
            'response_body': response.text
        }
    except Exception as e:
        print(f"Error in gc_db_email: {e}")
        return "Internal Server Error", 500













@app.route('/gc_forms', methods=['GET'])
def gc_forms():
    try:
        api_sys_control = request.args.get('api_sys')

        if api_sys_control != api_key_sys:
            return "EUR 22", 200

        # Чтение параметров из GET-запроса
        name_d = request.args.get('name', '')
        email_d = request.args.get('email', '')
        phone_d = request.args.get('phone', '')
        cod_pred = request.args.get('cod_pred', '')
        price = request.args.get('price', '')        
        utm_source = request.args.get('utm_source', '')
        utm_medium = request.args.get('utm_medium', '')
        utm_content = request.args.get('utm_content', '')
        utm_campaign = request.args.get('utm_campaign', '')
        utm_group = request.args.get('utm_group', '')
        gcpc = request.args.get('gcpc', '')
        # Формирование JSON
        json_data = {
            "user": {
                "email": email_d,
                "phone": phone_d,
                "first_name": name_d,
                "addfields": {
                    "pr1": "",
                    "pr2": "",
                    "pr3": ""
                }
            },
            "system": {
                "refresh_if_exists": 1
            },
            "session": {
                "utm_source": utm_source,
                "utm_medium": utm_medium,
                "utm_content": utm_content,
                "utm_campaign": utm_campaign,
                "utm_group": utm_group,
                "gcpc": gcpc,
                "gcao": "",
                "referer": ""
            },
        "deal":{
            "offer_code": cod_pred,
            "deal_cost": price
        }
        }

        # Конвертация JSON в Base64
        json_str = json.dumps(json_data)
        params_d = base64.b64encode(json_str.encode('utf-8')).decode('utf-8')

        # Данные для отправки в теле запроса
        data = {
            'key': gc_api,
            'action': action_d,
            'params': params_d
        }

        # Отправка POST-запроса с данными в формате "form-data"
        response = requests.post(gc_url_form, data=data)

        # Возвращаем ответ от тестового адреса
        return {
            'status_code': response.status_code,
            'response_body': response.text
        }
    except Exception as e:
        print(f"Error in gc_db_email: {e}")
        return "Internal Server Error", 500
































initialize_requests()



if __name__ == '__main__':
    
    app.run(host='0.0.0.0', port=int(os.environ.get('PORT', 7860)))