# 1. Установка зависимостей
pip install gspread google-auth openai pandas

# 2. Аутентификация через сервисный аккаунт
import gspread
from google.oauth2.service_account import Credentials

# Путь к JSON-ключу сервисного аккаунта
SERVICE_ACCOUNT_FILE = "/opt/secrets/gsheets-service-account.json"
SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file"
]

creds = Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)
gc = gspread.authorize(creds)

# 3. Открываем таблицу (по URL или ID)
# ID таблицы берём из URL: https://docs.google.com/spreadsheets/d/{SHEET_ID}/
SHEET_ID = "1BxiMVs0XRA5nFMjKvBdBZjgmUUqptlbs74OgvE2upms"
sheet = gc.open_by_key(SHEET_ID).sheet1
print(f"✅ Подключено: {sheet.title}, строк: {sheet.row_count}")

def read_range(sheet, range_str: str, as_dict=True) -> list:
    """Читает диапазон: 'A1:D100' → список словарей [{col: val}, ...]"""
    data = sheet.get(range_str)
    if not as_dict or len(data) < 2:
        return data
    headers = data[0]
    return [{headers[i]: row[i] if i < len(row) else "" 
             for i in range(len(headers))} for row in data[1:]]

def write_range(sheet, range_str: str, data: list):
    """Записывает данные в диапазон. data — список списков."""
    sheet.update(range_name=range_str, values=data, value_input_option="USER_ENTERED")
    return {"status": "ok", "range": range_str, "rows": len(data)}

# Пример: чтение таблицы заказов
# A: order_id | B: customer | C: amount | D: status | E: category
orders = read_range(sheet, "A1:E500")
print(f"Загружено заказов: {len(orders)}")
# Вывод: Загружено заказов: 487

from openai import OpenAI
client = OpenAI()
BATCH_SIZE = 20  # Строк за один вызов LLM

def classify_sentiment(reviews: list) -> list:
    """Классифицирует тональность отзывов батчами"""
    results = []
    for i in range(0, len(reviews), BATCH_SIZE):
        batch = reviews[i:i+BATCH_SIZE]
        numbered = "\n".join(f"{j+1}. {r}" for j, r in enumerate(batch))
        prompt = f"""Определи тональность каждого отзыва.
Варианты: positive, negative, neutral.
Верни ТОЛЬКО JSON-массив: ["positive", "negative", ...]

{numbered}"""
        resp = client.chat.completions.create(
            model="gpt-4o-mini", temperature=0,
            messages=[{"role": "user", "content": prompt}],
            response_format={"type": "json_object"}
        )
        batch_results = json.loads(resp.choices[0].message.content)
        results.extend(batch_results.get("sentiments", batch_results))
    return results

# Пайплайн: читаем столбец B (отзывы), классифицируем, пишем в столбец F
data = read_range(sheet, "A1:F500")
reviews = [row.get("review", "") for row in data]
sentiments = classify_sentiment(reviews)
# Записываем результаты в столбец F (Sentiment), начиная с F2
output = [[s] for s in sentiments]
write_range(sheet, f"F2:F{1+len(output)}", output)
print(f"✅ Классифицировано: {len(sentiments)} отзывов")

def generate_report(sheet, data_range="A1:E500") -> str:
    """Генерирует текстовый отчёт по данным таблицы"""
    data = read_range(sheet, data_range)
    if not data:
        return "Нет данных для отчёта"
    
    # Собираем базовую статистику
    headers = list(data[0].keys())
    row_count = len(data)
    sample = json.dumps(data[:10], ensure_ascii=False, indent=2)
    
    prompt = f"""Проанализируй данные таблицы и напиши краткий отчёт на русском.

Заголовки столбцов: {headers}
Всего строк: {row_count}
Образец данных (первые 10 строк):
{sample}

Напиши отчёт в формате:
## 📊 Сводка
(2-3 предложения общих выводов)

## 📈 Ключевые метрики
(числовые показатели если есть)

## ⚠️ Аномалии
(что выглядит необычно)

## 💡 Рекомендации
(2-3 практических совета)"""
    
    resp = client.chat.completions.create(
        model="gpt-4o", temperature=0.4,
        messages=[{"role": "user", "content": prompt}]
    )
    report = resp.choices[0].message.content
    
    # Создаём лист Reports и записываем отчёт
    try:
        report_sheet = sheet.spreadsheet.worksheet("Reports")
    except:
        report_sheet = sheet.spreadsheet.add_worksheet("Reports", 100, 5)
    
    lines = report.split("\n")
    report_sheet.update("A1", [[line] for line in lines])
    return report

# Вывод:
# ## 📊 Сводка
# За отчётный период обработано 487 заказов на сумму 2.4 млн руб...
# ## 📈 Ключевые метрики
# Средний чек: 4 928 руб. Конверсия: 3.2%...

def fill_missing_values(sheet, column_letter: str, context_columns: list):
    """Заполняет пустые ячейки в колонке на основе контекста других колонок"""
    # Читаем всю таблицу
    all_data = sheet.get_all_records()
    headers = list(all_data[0].keys()) if all_data else []
    
    # Находим строки с пустыми значениями в целевой колонке
    target_col_name = None
    col_idx = ord(column_letter) - ord('A')
    if col_idx < len(headers):
        target_col_name = headers[col_idx]
    
    updates = []
    for row_idx, row in enumerate(all_data, start=2):
        current_val = row.get(target_col_name, "") if target_col_name else ""
        if current_val and current_val.strip():
            continue  # Не пустое — пропускаем
        
        # Формируем контекст из соседних колонок
        context = {k: v for k, v in row.items() if k in context_columns}
        prompt = f"""Заполни пропущенное значение в колонке '{target_col_name}'.
Контекст строки: {json.dumps(context, ensure_ascii=False)}
Верни ТОЛЬКО значение, без пояснений."""
        
        resp = client.chat.completions.create(
            model="gpt-4o-mini", temperature=0.2,
            messages=[{"role": "user", "content": prompt}]
        )
        predicted = resp.choices[0].message.content.strip()
        updates.append((row_idx, predicted))
        print(f"Строка {row_idx}: '{current_val}' → '{predicted}'")
    
    # Применяем обновления
    for row_idx, value in updates:
        sheet.update_acell(f"{column_letter}{row_idx}", value)
    
    return {"filled": len(updates)}

import asyncio
from telegram import Update
from telegram.ext import Application, CommandHandler, MessageHandler, filters

# Конфигурация инструментов для LLM-агента
TOOLS_DEFINITION = [
    {
        "type": "function",
        "function": {
            "name": "sheets_read_range",
            "description": "Читает данные из Google Sheets по диапазону (A1:C50)",
            "parameters": {
                "type": "object",
                "properties": {
                    "range": {"type": "string", "description": "Диапазон, например 'Лист1!A1:D100'"}
                },
                "required": ["range"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "sheets_write_range",
            "description": "Записывает данные в Google Sheets",
            "parameters": {
                "type": "object",
                "properties": {
                    "range": {"type": "string"},
                    "data": {"type": "array", "items": {"type": "array"}}
                },
                "required": ["range", "data"]
            }
        }
    }
]

async def handle_message(update: Update, context):
    user_message = update.message.text
    # ReAct-цикл с доступом к Google Sheets
    messages = [
        {"role": "system", "content": "Ты — ИИ-агент Google Sheets. Отвечай на русском, кратко."},
        {"role": "user", "content": user_message}
    ]
    # ... ReAct-цикл с function calling (как в гайде #1) ...
    # Выполняем tools, возвращаем ответ пользователю
    await update.message.reply_text(reply)

app = Application.builder().token("TELEGRAM_BOT_TOKEN").build()
app.add_handler(MessageHandler(filters.TEXT & ~filters.COMMAND, handle_message))
print("🤖 Telegram-бот для Google Sheets запущен")
app.run_polling()

def parse_unstructured_column(sheet, source_col: str, target_cols: list, schema: dict):
    """Парсит неструктурированные данные из source_col в target_cols по схеме.
    schema = {"Город": "string", "Улица": "string", "Дом": "string", ...}"""
    data = sheet.get_all_records()
    source_idx = ord(source_col) - ord('A')
    headers = list(data[0].keys()) if data else []
    source_col_name = headers[source_idx] if source_idx < len(headers) else source_col
    
    results = []
    for row in data:
        raw_text = row.get(source_col_name, "")
        schema_str = json.dumps(schema, ensure_ascii=False)
        prompt = f"""Извлеки структурированные данные из текста.
Схема полей: {schema_str}
Текст: {raw_text}
Верни ТОЛЬКО JSON-объект с полями согласно схеме."""
        resp = client.chat.completions.create(
            model="gpt-4o-mini", temperature=0,
            messages=[{"role": "user", "content": prompt}],
            response_format={"type": "json_object"}
        )
        parsed = json.loads(resp.choices[0].message.content)
        results.append([parsed.get(col, "") for col in target_cols])
    
    # Записываем результат в целевые колонки
    start_col_letter = chr(ord(source_col) + 1)  # Следующая после source
    end_col_letter = chr(ord(start_col_letter) + len(target_cols) - 1)
    range_str = f"{start_col_letter}2:{end_col_letter}{1+len(results)}"
    sheet.update(range_str, results)
    return {"parsed": len(results), "fields": target_cols}

# Пример: парсим адреса из столбца C в столбцы D-H
schema = {"Город": "string", "Улица": "string", "Дом": "string", "Квартира": "string", "Индекс": "string"}
parse_unstructured_column(sheet, "C", ["Город", "Улица", "Дом", "Квартира", "Индекс"], schema)
# Вывод: {'parsed': 487, 'fields': ['Город', 'Улица', 'Дом', 'Квартира', 'Индекс']}