app.py

from flask import Flask, request, render_template_string, render_template, jsonify, Response
import sqlite3
import os
import random
import requests
import time
import re
import json
import base64
import logging
import csv
import io
from urllib.parse import quote

from datetime import datetime
import pytz

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') 
wa_ak = os.getenv('ws_ak') 
ws_url_mes = "/sendMessage/"
ws_url_ver = "/checkWhatsapp/"

up_db = os.getenv('up_db') 

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


export_id = ""  

code_executed = False


status = "active"



current_curator_index = 0
verifikation_start = "1"  # Глобальная переменная для управления верификацией
curator_on_off = "0"  # Глобальная переменная для управления назначением куратора

























































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,
            chat_id TEXT,
            ws_st TEXT,
            ws_stop TEXT,
            web_st INTEGER,
            fin_prog INTEGER,
            b_city TEXT,
            b_fin TEXT NOT NULL, 
            b_ban TEXT,
            b_ign TEXT,
            b_baners TEXT,
            b_butt TEXT,
            b_mess TEXT,           
            shop_st TEXT,
            curator TEXT,
            pr1 TEXT,
            pr2 TEXT,
            pr3 TEXT,
            pr4 TEXT,
            pr5 TEXT,
            gc_url TEXT,
            key_pr TEXT,
            n_con TEXT,
            canal TEXT,
            data_t TEXT,
            utm_source TEXT,
            utm_medium TEXT,
            utm_campaign TEXT,
            utm_term TEXT,
            utm_content TEXT
        )
    ''')
    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"
}




mapping_template_cur = {
    'name': 'Name',
    'phone': 'Phone',
    'email': 'Email'
}



# Функция для выполнения 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('gc_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, gc_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, :gc_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_ak}{ws_url_mes}{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('/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, gc_url, key_pr, n_con, canal, data_t, utm_source, utm_medium, utm_campaign, utm_term, utm_content
            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],
            'gc_url': contact[24], 'key_pr': contact[25], 'n_con': contact[26], 'canal': contact[27],
            'data_t': contact[28],'utm_source': contact[29], 'utm_medium': contact[30], 'utm_campaign': contact[31],
            'utm_term': contact[32], 'utm_content': contact[33]
        } 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



        
        
























forms = "formResponse"



gog_url = "https://docs.google.com/forms/d/e/1FAIpQLSc-JbmXvgpgGq6KrkXsYSsfMACVMyIDnNqrHy6jImGeSRcpiQ/formResponse?usp=pp_url&entry.1556100878={name}&entry.1477412341={email}&entry.1634985541={phone}&entry.1736544219={vk_id}&entry.62153872={chat_id}&entry.1913752768={ws_st}&entry.1768186232={ws_stop}&entry.1198983592={web_st}&entry.994770784={fin_prog}&entry.910932310={b_city}&entry.1923801792={b_fin}&entry.2005444720={b_ban}&entry.741087361={b_ign}&entry.1316159837={b_baners}&entry.355123557={b_butt}&entry.395996312={b_mess}&entry.646571729={shop_st}&entry.578527800={curator}&entry.1936838964={pr1}&entry.1375537366={pr2}&entry.1249356084={pr3}&entry.752547226={pr4}&entry.704766458={pr5}&entry.1837661={gc_url}&entry.398837750={key_pr}&entry.225564240={n_con}&entry.1642320872={canal}&entry.1581826411={data_t}&entry.311131724={utm_source}&entry.1904279859={utm_medium}&entry.740234546={utm_campaign}&entry.880981295={utm_term}&entry.431306383={utm_content}"



DATABASE_NAME = 'data_gc.db'
def send_to_google_forms(user_data, gog_url):
    # Формирование URL с параметрами
    url = gog_url.format(**user_data)
    
    # Отправка POST-запроса
    response = requests.post(url)
    
    if response.status_code == 200:
        logging.debug(f"Data sent to Google Forms successfully for user: {user_data.get('email')}")
    else:
        logging.error(f"Failed to send data to Google Forms for user: {user_data.get('email')}. Response: {response.text}")

        

def update_or_insert_user(db_name, user_data, mapping_template, gog_url):
    # Подключение к базе данных
    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}")

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

    current_web_st = user[0] if user else None
    current_ws_st = user[1] if user else None
    current_messages = user[2] if user else ""
    logging.debug(f"Current web_st: {current_web_st}, current_ws_st: {current_ws_st}, current_messages: {current_messages}")

    # Трансформация данных
    transformed_data = {}
    for json_key, db_column in mapping_template.items():
        value = user_data.get(json_key, "")
        if isinstance(value, list):
            if all(isinstance(item, str) for item in value):
                transformed_data[db_column] = "; ".join(value)
            else:
                logging.error(f"Expected list of strings for key {json_key}, but got: {value}")
                transformed_data[db_column] = ""
        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", "gc_url",
        "key_pr", "n_con", "canal", "data_t", 'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content'
    ]
    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}")

    # Столбцы, которые не нужно перезаписывать
    no_overwrite_columns = ['ws_st', 'curator', 'data_t']  # Добавляйте сюда столбцы

    # Проверка и подготовка значений столбцов
    if current_ws_st is not None and current_ws_st != "":
        transformed_data['ws_st'] = current_ws_st
    else:
        transformed_data['ws_st'] = user_data.get('ws_st', "")

    if current_web_st is not None and current_web_st != "":
        transformed_data['web_st'] = int(current_web_st) + 1
    else:
        transformed_data['web_st'] = 1

    new_messages = transformed_data.get('b_mess', "")
    if current_messages:
        transformed_data['b_mess'] = current_messages + "; " + new_messages
    else:
        transformed_data['b_mess'] = new_messages
    logging.debug(f"Transformed data after message processing: {transformed_data}")

    # Запись данных в базу данных
    if user:
        update_query = "UPDATE contacts SET "
        update_values = []
        for column, value in transformed_data.items():
            if column not in no_overwrite_columns:  # Исключаем столбцы, которые не нужно перезаписывать
                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()

    # Извлечение обновленных данных из базы данных
    cursor.execute("SELECT * FROM contacts WHERE email = ?", (email,))
    updated_user = cursor.fetchone()
    updated_data = dict(zip([column[0] for column in cursor.description], updated_user))

    conn.close()
    logging.debug(f"User with email {email} processed successfully")

    # Отправка данных в Google Forms
    send_to_google_forms(updated_data, gog_url)





@app.route('/send_request', methods=['POST'])
def send_request():
    token = request.form.get('token')
    min_date = request.form.get('minDate')
    type = request.form.get('type')
    url = f'https://online.bizon365.ru/api/v1/webinars/reports/getlist?minDate={min_date}&type={type}&limit=100'
    
    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




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

    try:
        response = requests.get(url, headers={'X-Token': token})
        response.raise_for_status()  # Проверка на ошибки HTTP
        data = response.json()

        if data is None or 'report' not in data:
            return jsonify({'error': 'No report data found'}), 500

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

        if report is None:
            return jsonify({'error': 'No report data found in the response'}), 500

        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', {})

        processed_emails = set()
        for user_id, user_data in users_meta.items():
            user_messages = messages_json.get(user_id, [])
            user_data['messages'] = user_messages
            email = user_data.get('email')
            if email and email not in processed_emails:
                update_or_insert_user(DATABASE_NAME, user_data, mapping_template, gog_url)
                processed_emails.add(email)

        return jsonify({'status': 'User data saved successfully'})
    
    except requests.exceptions.RequestException as e:
        return jsonify({'error': f'API request failed: {str(e)}'}), 500



api_bz = "SkrIONpr3ByeSIuEaBhr1bB8u4aBhSJfH8uEpB2rk7rI_ETrn"
        

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

    data = request.json
    webinar_id = data.get('webinarId')

    if not webinar_id:
        return jsonify({'error': 'webinarId is required'}), 400
    url = f'https://online.bizon365.ru/api/v1/webinars/reports/get?webinarId={webinar_id}'

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


    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', {})

        processed_emails = set()
        for user_id, user_data in users_meta.items():
            user_messages = messages_json.get(user_id, [])
            user_data['messages'] = user_messages
            email = user_data.get('email')
            if email and email not in processed_emails:
                update_or_insert_user(DATABASE_NAME, user_data, mapping_template, gog_url)
                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('/bas_vk_tab', methods=['GET'])
def bas_vk_otob():
    api_sys_control = request.args.get('api_sys')
    if api_sys_control != api_key_sys:
        return "EUR 22", 200
    return render_template('bas_vk_tab.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














curators = ["Anna", "Ekaterina", "Ivan", "Maria", "Sergey", "Olga", "Alex", "Natalia", "Dmitry", "Elena"]


mt_avp = {
    'name': 'Name',
    'phone': 'Phone',
    'email': 'Email'
}

mt_bhelp = {
    'name': 'name',
    'phone': 'phone',
    'email': 'email',
    'ad': 'ws_stop'
}

mt_gc = {
    'name': 'name',
    'phone': 'phone',
    'email': 'email',
    'ad': 'web_st'
}

mt_tl = {
    'name': 'name',
    'phone': 'phone',
    'email': 'email',
    'ad': 'ad_url'
}

mapp_templates = {
    'avp': mt_avp,
    'bhelp': mt_bhelp,
    'gc': mt_gc,
    'ad': mt_tl
}

DATABASE_NAME3 = 'data_gc.db'

def add_or_update_contact(contact_data):
    conn = sqlite3.connect(DATABASE_NAME3)
    cursor = conn.cursor()

    email = contact_data.get('email')
    if not email:
        logging.error(f"Missing email in contact data: {contact_data}")
        return

    utc_now = datetime.utcnow()
    msk_tz = pytz.timezone('Europe/Moscow')
    msk_now = utc_now.replace(tzinfo=pytz.utc).astimezone(msk_tz)
    contact_data['data_t'] = msk_now.strftime('%Y-%m-%d %H:%M:%S')

    # Список всех возможных полей
    fields = [
        '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', 'gc_url', 'key_pr', 'n_con', 'canal', 'data_t', 'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content'
    ]

    # Устанавливаем значения по умолчанию для отсутствующих полей
    for field in fields:
        if field not in contact_data:
            contact_data[field] = ''

    placeholders = ", ".join([f"{field} = ?" for field in fields])

    cursor.execute("SELECT id FROM contacts WHERE email = ?", (email,))
    contact = cursor.fetchone()

    if contact:
        update_query = f"UPDATE contacts SET {placeholders} WHERE id = ?"
        cursor.execute(update_query, (*[contact_data[field] for field in fields], contact[0]))
    else:
        insert_query = f"INSERT INTO contacts ({', '.join(fields)}) VALUES ({', '.join(['?' for _ in fields])})"
        cursor.execute(insert_query, tuple(contact_data[field] for field in fields))

    conn.commit()
    conn.close()



@app.route('/add_data_ver_cur', methods=['GET'])
def add_data_ver_cur():
    global current_curator_index

    veref_on_off = request.args.get('ver', '0')  # Включает "1" и выключает "0" верификацию номера вместо verifikation_start
    curator_on_off = request.args.get('cur', '0')  # Включает "1" и выключает "0" назначение куратора
    
    template_key = request.args.get('template_key', 'avp')
    mapping_template_cur = mapp_templates.get(template_key, mt_avp)

    user_data = {mapping_template_cur[key]: request.args.get(key, "") for key in mapping_template_cur}

    logging.debug(f"Received data: {user_data}")

    if curator_on_off == "1":
        user_data['curator'] = curators[current_curator_index]

    if veref_on_off == "1":
        phone_number = user_data.get('phone', '')
        if not phone_number:
            logging.error("Phone number is empty")
            return jsonify({'status': 'error', 'message': 'Phone number is empty'}), 400
        
        phone_verification_response = verify_phone_number(phone_number)
        if phone_verification_response is not None:
            user_data['ws_st'] = phone_verification_response

    try:
        add_or_update_contact(user_data)
        if curator_on_off == "1":
            current_curator_index = (current_curator_index + 1) % len(curators)
        
        # Отправка данных в Google Forms
        send_to_google_forms(user_data, gog_url)

        return jsonify({'status': 'success', 'message': f'User added with curator {user_data.get("curator", "not assigned")}'})
    except Exception as e:
        logging.error(f"Error adding user: {e}")
        return jsonify({'status': 'error', 'message': str(e)}), 500





















DATABASE2 = 'data_gc.db'

def verify_phone_number(phone_number):
    full_url_ver = f"{wa_url}{wa_ak}{ws_url_ver}{wa_api_key}"
    payload = {"phoneNumber": phone_number}
    headers = {'Content-Type': 'application/json'}
    response = requests.post(full_url_ver, headers=headers, json=payload)
    if response.status_code == 200:
        response_body = response.json()
        return response_body.get('existsWhatsapp', 'false')
    else:
        return "Error"
        
def parse_csv_data(data):
    parsed_data = []
    for item in data:
        for key, value in item.items():
            headers = key.split(';')
            row = value.split(';')
            parsed_data.append(dict(zip(headers, row)))
    return parsed_data

def clean_phone_number(phone_number):
    # Удаляем все символы, кроме цифр
    return re.sub(r'\D', '', phone_number)

def insert_data(data, verify_phone, add_curator):
    global current_curator_index
    conn = sqlite3.connect(DATABASE2)
    cursor = conn.cursor()

    for row in data:
        name = row.get('Name', '')
        phone = row.get('Phone', '').lstrip('+')
        email = row.get('Email', '')
        data_t = row.get('Date', '').strip('"')

        # Очистка номера телефона
        phone = clean_phone_number(phone)

        cursor.execute("SELECT 1 FROM contacts WHERE email = ? OR phone = ?", (email, phone))
        user_exists = cursor.fetchone()

        if user_exists:
            print(f"User with email {email} or phone {phone} already exists. Skipping insert.")
            continue

        if add_curator == "1":
            curator = curators[current_curator_index]
            current_curator_index = (current_curator_index + 1) % len(curators)
        else:
            curator = row.get('curator', '')

        if verify_phone == "1":
            ws_st = verify_phone_number(phone)
        else:
            ws_st = row.get('ws_st', '')

        columns = ['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', 'gc_url', 'key_pr', 'n_con', 'canal', 'data_t', 'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content']
        values = [name, phone, email, row.get('vk_id', ''), row.get('chat_id', ''), ws_st, row.get('ws_stop', ''), row.get('web_st', 0), row.get('fin_prog', 0), row.get('b_city', ''), row.get('b_fin', ''), row.get('b_ban', ''), row.get('b_ign', ''), row.get('b_baners', ''), row.get('b_butt', ''), row.get('b_mess', ''), row.get('shop_st', ''), curator, row.get('pr1', ''), row.get('pr2', ''), row.get('pr3', ''), row.get('pr4', ''), row.get('pr5', ''), row.get('gc_url', ''), row.get('key_pr', ''), row.get('n_con', ''), row.get('canal', ''), data_t, row.get('utm_source', ''), row.get('utm_medium', ''), row.get('utm_campaign', ''), row.get('utm_term', ''), row.get('utm_content', '')]

        placeholders = ', '.join(['?' for _ in columns])
        columns_str = ', '.join(columns)

        query = f'''
            INSERT INTO contacts ({columns_str})
            VALUES ({placeholders})
        '''

        try:
            cursor.execute(query, values)
            # Отправка данных в Google Forms
            user_data = dict(zip(columns, values))
            send_to_google_forms(user_data, gog_url)
        except Exception as e:
            print(f"Error inserting row: {row}")
            print(f"Error message: {str(e)}")
            conn.rollback()
            raise

    conn.commit()
    conn.close()

@app.route('/upload_csv', methods=['POST'])
def upload_csv():
    if 'file' not in request.files:
        return jsonify({"error": "No file part"}), 400
    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "No selected file"}), 400
    if file and file.filename.endswith('.csv'):
        stream = io.StringIO(file.stream.read().decode("UTF8"), newline=None)
        csv_input = csv.DictReader(stream)
        data = [row for row in csv_input]
        parsed_data = parse_csv_data(data)
        verify_phone = request.form.get('verify_phone', '0')
        add_curator = request.form.get('add_curator', '0')
        print(f"Verify Phone: {verify_phone}")
        print(f"Add Curator: {add_curator}")
        insert_data(parsed_data, verify_phone, add_curator)
        return jsonify({"message": "Data uploaded and inserted successfully"})
    return jsonify({"error": "Invalid file format"}), 400

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







DATABASE4 = 'data_gc.db'

def verify_phone_number_b(phone_number):
    full_url_ver = f"{wa_url}{wa_ak}{ws_url_ver}{wa_api_key}"
    payload = {"phoneNumber": phone_number}
    headers = {'Content-Type': 'application/json'}
    response = requests.post(full_url_ver, headers=headers, json=payload)
    if response.status_code == 200:
        response_body = response.json()
        return response_body.get('existsWhatsapp', 'false')
    else:
        return "Error"

def parse_csv_data_b(data):
    parsed_data = []
    for row in data:
        parsed_row = {}
        for key, value in row.items():
            headers = key.split(',')
            values = value.split(',')
            parsed_row.update(dict(zip(headers, values)))
        parsed_data.append(parsed_row)
    return parsed_data

def clean_phone_number_b(phone_number):
    return re.sub(r'\D', '', phone_number)

def insert_data_b(data, verify_phone, add_curator):
    global current_curator_index
    conn = sqlite3.connect(DATABASE4)
    cursor = conn.cursor()

    for row in data:
        name = row.get('Имя', '')
        phone = row.get('WhatsApp', '').lstrip('+')
        email = row.get('Email', '')
        data_t = row.get('Дата', '').strip('"')

        phone = clean_phone_number_b(phone)

        cursor.execute("SELECT 1 FROM contacts WHERE email = ? OR phone = ?", (email, phone))
        user_exists = cursor.fetchone()

        if user_exists:
            print(f"User with email {email} or phone {phone} already exists. Skipping insert.")
            continue

        if add_curator == "1":
            curator = curators[current_curator_index]
            current_curator_index = (current_curator_index + 1) % len(curators)
        else:
            curator = row.get('Куратор', '')

        if verify_phone == "1":
            ws_st = verify_phone_number_b(phone)
        else:
            ws_st = row.get('ws_st', '')

        columns = ['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', 'gc_url', 'key_pr', 'n_con', 'canal', 'data_t', 'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content']
        values = [name, phone, email, row.get('ВКонтакте', ''), row.get('Телеграм', ''), ws_st, row.get('ws_stop', ''), row.get('web_st', 0), row.get('fin_prog', 0), row.get('Город', ''), row.get('b_fin', ''), row.get('b_ban', ''), row.get('b_ign', ''), row.get('b_baners', ''), row.get('b_butt', ''), row.get('Реплики', ''), row.get('Статус покупки', ''), curator, row.get('pr1', ''), row.get('pr2', ''), row.get('pr3', ''), row.get('Канал трафика', ''), row.get('pr5', ''), row.get('GetCurse', ''), row.get('Ключ PR', ''), row.get('n_con', ''), row.get('Канал', ''), data_t, row.get('utm_source', ''), row.get('utm_medium', ''), row.get('utm_campaign', ''), row.get('utm_term', ''), row.get('utm_content', '')]

        placeholders = ', '.join(['?' for _ in columns])
        columns_str = ', '.join(columns)

        query = f'''
            INSERT INTO contacts ({columns_str})
            VALUES ({placeholders})
        '''

        try:
            cursor.execute(query, values)
            user_data = dict(zip(columns, values))
            send_to_google_forms(user_data, gog_url)
        except Exception as e:
            print(f"Error inserting row: {row}")
            print(f"Error message: {str(e)}")
            conn.rollback()
            raise

    conn.commit()
    conn.close()

@app.route('/upload_csv_b', methods=['POST'])
def upload_csv_b():
    if 'file' not in request.files:
        return jsonify({"error": "No file part"}), 400
    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "No selected file"}), 400
    if file and file.filename.endswith('.csv'):
        stream = io.StringIO(file.stream.read().decode("UTF8"), newline=None)
        csv_input = csv.DictReader(stream)
        data = [row for row in csv_input]
        parsed_data = parse_csv_data_b(data)
        verify_phone = request.form.get('verify_phone', '0')
        add_curator = request.form.get('add_curator', '0')
        print(f"Verify Phone: {verify_phone}")
        print(f"Add Curator: {add_curator}")
        insert_data_b(parsed_data, verify_phone, add_curator)
        return jsonify({"message": "Data uploaded and inserted successfully"})
    return jsonify({"error": "Invalid file format"}), 400

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





















@app.route('/gc_in', methods=['GET'])
def add_data_gc_in():
    global current_curator_index

    veref_on_off = request.args.get('ver', '0')  # Включает "1" и выключает "0" верификацию номера вместо verifikation_start
    curator_on_off = request.args.get('cur', '0')  # Включает "1" и выключает "0" назначение куратора
    
    template_key = request.args.get('template_key', 'avp')
    mapping_template_cur = mapp_templates.get(template_key, mt_avp)

    user_data = {mapping_template_cur[key]: request.args.get(key, "") for key in mapping_template_cur}

    if curator_on_off == "1":
        user_data['curator'] = curators[current_curator_index]

    if veref_on_off == "1":
        phone_verification_response = verify_phone_number(user_data.get('phone', ''))
        if phone_verification_response is not None:
            user_data['ws_st'] = phone_verification_response

    try:
        add_or_update_contact(user_data)
        if curator_on_off == "1":
            current_curator_index = (current_curator_index + 1) % len(curators)
        return jsonify({'status': 'success', 'message': f'User added with curator {user_data.get("curator", "not assigned")}'})
    except Exception as e:
        logging.error(f"Error adding user: {e}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

























@app.route('/tl_help.js')
def serve_vk_bridge():
    script_content = """
function mySuccessFunction(form) {
    if (!form) return;
    if (form instanceof jQuery) {
        form = form.get(0);
    }
 
    var obj = {};
    var inputs = form.elements;
    Array.prototype.forEach.call(inputs, function(input) {
        if (input.type === 'radio') {
            if (input.checked) obj[input.name] = input.value;
        } else {
            obj[input.name] = input.value;
        }
    });
  
    var email = obj["Email"] || "";
    var phone = obj["Phone"] || "";
    var name = obj["Name"] || "";
    console.log("name:", name);
    console.log("email:", email);
    console.log("phone:", phone);
  
    var urlParams = new URLSearchParams(window.location.search);
    var utm_source = urlParams.get('utm_source') || "0";
    var utm_medium = urlParams.get('utm_medium') || "0";
    var utm_campaign = urlParams.get('utm_campaign') || "0";
    var utm_content = urlParams.get('utm_content') || "0";
    var utm_term = urlParams.get('utm_term') || "0";
    var gcpc = urlParams.get('gcpc') || "0";
    var redirectUrl;
    if (form.id === formId1) {
        redirectUrl = new URL(redirectUrl1);
    } else if (form.id === formId2) {
        redirectUrl = new URL(redirectUrl2);
    } else if (form.id === formId3) {
        redirectUrl = new URL(redirectUrl3);
    } else {
        console.error('Неизвестный ID формы:', form.id);
        return;
    }
    var queryString = '?ups=' + encodeURIComponent(ups);
    queryString += '&name=' + encodeURIComponent(name);
    queryString += '&email=' + encodeURIComponent(email);
    queryString += '&phone=' + encodeURIComponent(phone);
    queryString += '&utm_source=' + encodeURIComponent(utm_source);
    queryString += '&utm_medium=' + encodeURIComponent(utm_medium);
    queryString += '&utm_campaign=' + encodeURIComponent(utm_campaign);
    queryString += '&utm_content=' + encodeURIComponent(utm_content);
    queryString += '&utm_term=' + encodeURIComponent(utm_term);
    queryString += '&gcpc=' + encodeURIComponent(gcpc);
    console.log('Сформированный URL:', redirectUrl.toString() + queryString);
    window.open(redirectUrl.toString() + queryString, '_blank');
}
if (document.readyState !== 'loading') {
    us_sendFormAfterSuccess();
} else {
    document.addEventListener('DOMContentLoaded', us_sendFormAfterSuccess);
}
function us_sendFormAfterSuccess() {
    var forms = document.querySelectorAll('.js-form-proccess');
    Array.prototype.forEach.call(forms, function(form) {
        form.addEventListener('tildaform:aftersuccess', function(e) {
            e.preventDefault(); 
            mySuccessFunction(form);
        });
    });
}
    """
    return Response(script_content, mimetype='application/javascript')








initialize_requests()



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