google_sheet_sync.py

import requests
import json
import base64
from io import BytesIO
import openpyxl

WEB_APP_URL = "https://script.google.com/macros/s/AKfycbxxKu82K0V3VFn7tRlj3ddzB7Wy-iPyrupAV9tZzjBdhcXZPWFIXodJv6D03M-X_KSn/exec"

def get_val_case_insensitive(d, *keys, default=""):
    if not isinstance(d, dict):
        return default
    d_lower = {str(k).lower(): v for k, v in d.items()}
    for k in keys:
        if str(k).lower() in d_lower:
            val = d_lower[str(k).lower()]
            if val is not None:
                return str(val).strip()
    return default

def fetch_all_from_sheet():
    """गूगल शीट से JSON डेटा और टेम्पलेट्स फेच करता है[cite: 7]"""
    try:
        response = requests.get(f"{WEB_APP_URL}?action=get_data", timeout=20, allow_redirects=True)
        if response.status_code == 200:
            res_text = response.text.strip()
            if res_text.startswith("<"):
                return None
            return response.json()
    except Exception:
        pass
    return None

def clear_sheet_cache():
    """चूंकि अब Streamlit हट चुका है, यह फंक्शन कंपैटिबिलिटी के लिए खाली रखा गया है[cite: 7]"""
    pass

def push_all_to_sheet(shippers_json_payload):
    """पुराने कोड और रूल्स सेव करने के लिए कम्पाटिबल फंक्शन[cite: 7]"""
    try:
        payload = {
            "action": "save_shipper_json",
            "shippers_data": shippers_json_payload
        }
        response = requests.post(WEB_APP_URL, data=json.dumps(payload), timeout=120)
        if response.status_code == 200:
            return True
        return False
    except Exception:
        return False

def push_rules_to_sheet(shippers_json_payload):
    """केवल रूल्स (JSON) को गूगल शीट पर सेव करने के लिए[cite: 7]"""
    return push_all_to_sheet(shippers_json_payload)

def push_template_file_to_sheet(shipper_name, file_bytes):
    """टेम्पलेट फाइल को बेस64 में बदलकर गूगल शीट पर भेजता है[cite: 7]"""
    try:
        b64_str = base64.b64encode(file_bytes).decode('utf-8') if file_bytes else ""
        
        payload = {
            "action": "save_template_file",
            "shipper": shipper_name,
            "file_base64": b64_str
        }
        res = requests.post(WEB_APP_URL, data=json.dumps(payload), timeout=120)
        
        if res.status_code == 200:
            return True
        return False
    except Exception:
        return False

def load_template_bytes_from_sheet(shipper_name):
    """गूगल शीट से शिपर की Base64 फाइल को डिकोड करके बाइट्स लौटाता है[cite: 7]"""
    data = fetch_all_from_sheet()
    if not data:
        return None
    
    shippers_dict = data.get("shippers", {})
    if shipper_name in shippers_dict:
        s_data = shippers_dict[shipper_name]
        b64_str = s_data.get("file_base64", "")
        if b64_str and len(b64_str.strip()) > 0:
            try:
                clean_b64 = b64_str.lstrip("'").strip().replace(" ", "+")
                missing_padding = len(clean_b64) % 4
                if missing_padding:
                    clean_b64 += '=' * (4 - missing_padding)
                
                decoded_bytes = base64.b64decode(clean_b64)
                if decoded_bytes.startswith(b'PK'):
                    return decoded_bytes
            except Exception:
                pass
    return None

def load_template_from_sheet(shipper_name):
    raw_bytes = load_template_bytes_from_sheet(shipper_name)
    if raw_bytes:
        try:
            return openpyxl.load_workbook(BytesIO(raw_bytes))
        except Exception:
            pass
    return None

def get_detailed_shipper_rules_overview():
    """
    Google Sheet se sabhi shippers ke header rules, item table rules aur template status 
    ki poori detail fetch karke ek structured Markdown overview banata hai.
    """
    try:
        data = fetch_all_from_sheet()
        if not data or not isinstance(data, dict):
            return "❌ Google Sheet से डेटा फेच करने में असमर्थ।"
        
        shippers = data.get("shippers", {})
        if not shippers:
            return "⚠️ गूगल शीट पर कोई भी शिपर रूल्स डेटाबेस नहीं मिला।"
        
        overview_text = "📊 **Google Sheet Live Shippers & Rules Master Report**\n\n"
        
        for s_name, s_details in shippers.items():
            overview_text += f"🏢 **Shipper: {s_name}**\n"
            
            # Template Status
            has_tpl = bool(s_details.get("file_base64", ""))
            tpl_status = "✅ Uploaded & Saved" if has_tpl else "❌ Not Uploaded"
            overview_text += f"   - **Template Excel File:** {tpl_status}\n"
            overview_text += f"   - **Assigned Parser:** `{s_details.get('item_table_rule_name', 'parser_welspun')}`\n"
            
            # Header Rules Details
            mapping_rules = s_details.get("mapping_rules", {})
            overview_text += f"   - **Header Rules ({len(mapping_rules)}):**\n"
            if mapping_rules:
                for h_field, h_info in mapping_rules.items():
                    col = h_info.get('cell', 'N/A')
                    kw = h_info.get('keyword', 'N/A')
                    logic = h_info.get('logic', 'Main Invo')
                    overview_text += f"     • `{h_field}` ➡️ Cell: **{col}** | Keyword: *{kw}* | Doc: {logic}\n"
            else:
                overview_text += "     • कोई Header Rule सेट नहीं है।\n"
            
            # Item Table Rules Details
            item_rules = s_details.get("item_table_rules", {})
            overview_text += f"   - **Item Table Rules ({len(item_rules)}):**\n"
            if item_rules:
                for i_field, i_info in item_rules.items():
                    col = i_info.get('col', 'N/A')
                    rule_type = i_info.get('type', 'PDF Row')
                    rule_val = i_info.get('rule', 'N/A')
                    overview_text += f"     • `{i_field}` ➡️ Col: **{col}** | Type: {rule_type} | Rule: *{rule_val}*\n"
            else:
                overview_text += "     • कोई Item Table Rule सेट नहीं है।\n"
                
            overview_text += "\n" + "-"*50 + "\n\n"
            
        return overview_text
    except Exception as e:
        return f"Error loading detailed rules: {str(e)}"