"""
gs_client.py  –  raiz do projeto (junto com app.py)
────────────────────────────────────────────────────
Lê credenciais flat do secrets.toml e expõe read_ws / write_ws.

Uso:
    from gs_client import read_ws, write_ws

    df = read_ws("users_auth")           # planilha MAIN (padrão)
    df = read_ws("users_work", "users")  # planilha USERS
    write_ws("tasks", df)
"""

import os
import pandas as pd
import gspread
from google.oauth2.service_account import Credentials
from functools import lru_cache
import streamlit as st
import time
from dotenv import load_dotenv
load_dotenv()

_SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
]

@st.cache_resource
def _client():
    info = {
        "type":                        os.getenv("GS_TYPE", "service_account"),
        "project_id":                  os.getenv("GS_PROJECT_ID", ""),
        "private_key_id":              os.getenv("GS_PRIVATE_KEY_ID", ""),
        "private_key":                 os.getenv("GS_PRIVATE_KEY", "").replace("\\n", "\n"),
        "client_email":                os.getenv("GS_CLIENT_EMAIL", ""),
        "client_id":                   os.getenv("GS_CLIENT_ID", ""),
        "auth_uri":                    os.getenv("GS_AUTH_URI", "https://accounts.google.com/o/oauth2/auth"),
        "token_uri":                   os.getenv("GS_TOKEN_URI", "https://oauth2.googleapis.com/token"),
        "auth_provider_x509_cert_url": os.getenv("GS_AUTH_PROVIDER_CERT_URL", "https://www.googleapis.com/oauth2/v1/certs"),
        "client_x509_cert_url":        os.getenv("GS_CLIENT_CERT_URL", ""),
        "universe_domain":             os.getenv("GS_UNIVERSE_DOMAIN", "googleapis.com"),
    }
    creds = Credentials.from_service_account_info(info, scopes=_SCOPES)
    return gspread.authorize(creds)

def _url(spreadsheet: str) -> str:
    return (os.getenv("GS_SPREADSHEET_USERS", "")
            if spreadsheet == "users"
            else os.getenv("GS_SPREADSHEET_MAIN", ""))

@st.cache_data(ttl=600, show_spinner=False)
def read_ws(worksheet: str, spreadsheet: str = "main", retries: int = 3) -> pd.DataFrame:
    url = _url(spreadsheet)
    for attempt in range(retries):
        try:
            if attempt:
                time.sleep(min(attempt * 3, 10))
            sh  = _client().open_by_url(url)
            ws  = sh.worksheet(worksheet)
            # UNFORMATTED_VALUE retorna os números como valores reais do Google Sheets
            # sem formatação de exibição (sem remover vírgula/ponto)
            rows   = ws.get_all_values()          # strings brutas, linha a linha
            if not rows:
                return pd.DataFrame()
            header = rows[0]
            data   = rows[1:]
            df     = pd.DataFrame(data, columns=header)
            return _fix(df)
        except Exception as e:
            if "quota" in str(e).lower() or attempt == retries - 1:
                return pd.DataFrame()
    return pd.DataFrame()

def write_ws(worksheet: str, df: pd.DataFrame, spreadsheet: str = "main") -> bool:
    try:
        sh  = _client().open_by_url(_url(spreadsheet))
        ws  = sh.worksheet(worksheet)
        df2 = df.fillna("").astype(str)
        ws.clear()
        ws.update([df2.columns.tolist()] + df2.values.tolist())
        read_ws.clear()
        return True
    except Exception as e:
        st.error(f"Erro ao salvar planilha: {e}")
        return False


def _parse_br(val):
    """
    Converte string numérica (formato BR ou padrão) para float.

    Casos:
        já numérico  → retorna direto
        "2994"       → 2994.0
        "4500,8"     → 4500.8      (vírgula = decimal, sem milhar)
        "4.500,8"    → 4500.8      (ponto = milhar, vírgula = decimal)
        "100.551,11" → 100551.11
        "375.212,3"  → 375212.3
        "11.402,53"  → 11402.53
        "4500.8"     → 4500.8      (ponto decimal padrão)
    """
    if isinstance(val, (int, float)):
        return float(val)
    if not isinstance(val, str):
        return None
    s = val.strip()
    if s in ("", "-", "N/A", "n/a"):
        return None

    # Formato BR: tem vírgula → vírgula é decimal, pontos são milhar
    if "," in s:
        try:
            return float(s.replace(".", "").replace(",", "."))
        except ValueError:
            return None

    # Sem vírgula, com ponto
    if "." in s:
        parts = s.split(".")
        # Se TODOS os segmentos após o primeiro têm exatamente 3 dígitos
        # e o primeiro tem 1-3 dígitos → ponto é separador de milhar
        if (1 <= len(parts[0]) <= 3 and
                all(len(p) == 3 for p in parts[1:])):
            try:
                return float(s.replace(".", ""))
            except ValueError:
                return None
        # Caso contrário, ponto é decimal
        try:
            return float(s)
        except ValueError:
            return None

    # Sem ponto, sem vírgula → inteiro puro
    try:
        return float(s)
    except ValueError:
        return None


def _fix(df: pd.DataFrame) -> pd.DataFrame:
    """
    Para cada coluna, tenta converter todos os valores com _parse_br.
    Só aplica a conversão se TODOS os valores não-vazios converteram com sucesso.
    Isso evita converter colunas de texto como mes_ano ("01/2025") ou nomes.
    """
    for col in df.columns:
        # pula colunas já numéricas
        if df[col].dtype != "object":
            continue

        mask_nonempty = df[col].str.strip() != ""
        if mask_nonempty.sum() == 0:
            continue

        converted = df[col].apply(_parse_br)
        failed = (mask_nonempty & converted.isna()).sum()

        if failed == 0:
            df[col] = converted
        else:
            df[col] = df[col].fillna("").astype(str).replace("nan", "")

    return df