shipper_data.py

import os
import json
import pdfplumber
import io
from io import BytesIO
import re
import gradio as gr

from pdf_engine import detect_igst_status, extract_header_value
from google_sheet_sync import (
    fetch_all_from_sheet, push_rules_to_sheet, push_template_file_to_sheet, 
    load_template_bytes_from_sheet
)
import requests

LOCAL_DATA_DIR = "local_shipper_data"
TEMPLATES_DIR = os.path.join(LOCAL_DATA_DIR, "templates")

def ensure_local_directories():
    if not os.path.exists(LOCAL_DATA_DIR):
        os.makedirs(LOCAL_DATA_DIR)
    if not os.path.exists(TEMPLATES_DIR):
        os.makedirs(TEMPLATES_DIR)

def fetch_data_from_google_sheet():
    try:
        data = fetch_all_from_sheet()
        if isinstance(data, dict) and data:
            return data
    except Exception as e:
        pass
    return {}

def load_shipper_database():
    ensure_local_directories()
    sheet_data = fetch_data_from_google_sheet()
    shippers_dict = sheet_data.get("shippers", {}) if isinstance(sheet_data, dict) else {}
    if shippers_dict:
        return shippers_dict
    else:
        json_path = os.path.join(LOCAL_DATA_DIR, "shippers_rules.json")
        if os.path.exists(json_path):
            try:
                with open(json_path, "r", encoding="utf-8") as f:
                    saved_data = json.load(f)
                    if isinstance(saved_data, dict) and saved_data:
                        return saved_data
            except Exception:
                pass
    return {}

def save_shipper_database_to_sheet(shipper_database):
    ensure_local_directories()
    json_path = os.path.join(LOCAL_DATA_DIR, "shippers_rules.json")
    try:
        with open(json_path, "w", encoding="utf-8") as f:
            json.dump(shipper_database, f, indent=4)
        shippers_payload = {}
        for s_name, s_data in shipper_database.items():
            shippers_payload[s_name] = {
                "mapping_rules": s_data.get("mapping_rules", {}),
                "item_table_rules": s_data.get("item_table_rules", {}),
                "item_table_rule_name": s_data.get("item_table_rule_name", "parser_welspun"),
                "igst_config": s_data.get("igst_config", {})
            }
        push_rules_to_sheet(shippers_payload)
        return True, "🎉 Rules successfully saved and synced to Google Sheet!"
    except Exception as e:
        return False, f"Save Error: {str(e)}"

def render_shipper_data():
    db = load_shipper_database()
    shippers_list = sorted(list(db.keys()))

    gr.Markdown("### 🏢 Add Shipper Name & No-Code Visual Mapping Builder")
    gr.Markdown("कीवर्ड, बॉक्स एक्सट्रैक्शन और पोजीशन के जरिए स्मार्ट टेस्ट और सेव टूल।")
    
    # 1. Shipper Selection Dropdown (Jab tak select na ho, niche ka kuch nahi dikhega)
    selected_shipper_dropdown = gr.Dropdown(
        choices=shippers_list, 
        label="1. कॉन्फ़िगर करने के लिए शिपर चुनें:", 
        value=None, 
        interactive=True
    )

    # Config Section Wrapper (Initially Hidden)
    with gr.Column(visible=False) as config_container:
        shipper_title_md = gr.Markdown("### ⚙️ शिपर प्रोफाइल:")
        
        # 📁 1. Template File Section
        gr.Markdown("---")
        gr.Markdown("### 📁 1. टेम्पलेट फ़ाइल अपलोड (Full Job Excel Template)")
        
        with gr.Row() as tpl_upload_row:
            tpl_file_input = gr.File(label="Blank Full Job Excel Format File (Template) चुनें", file_types=[".xlsx", ".xls"])
            save_tpl_btn = gr.Button("🚀 Save Template to Google Sheet", variant="primary")
            
        with gr.Row(visible=False) as tpl_status_row:
            tpl_status_txt = gr.Textbox(label="Template Status", interactive=False, scale=3)
            replace_tpl_file = gr.File(label="🔄 Replace Template", file_types=[".xlsx", ".xls"], scale=2)
            confirm_replace_btn = gr.Button("🚀 Confirm & Replace", variant="secondary")
            delete_tpl_btn = gr.Button("🗑️ Delete Template", variant="stop")
            
        tpl_action_msg = gr.Textbox(label="Status Message", interactive=False)

        # 🧪 2. Sample PDF Upload & Text Inspector
        gr.Markdown("---")
        gr.Markdown("### 🧪 2. Sample PDF Upload & Text Viewer")
        with gr.Row():
            test_pdf_input = gr.File(label="टेस्ट करने के लिए सैंपल इनवॉइस PDF अपलोड करें", file_types=[".pdf"], scale=1)
            pdf_preview_box = gr.Textbox(label="Extracted PDF Text Preview", lines=4, scale=2, interactive=False)

        # ⚡ 3. Smart Test & Save Generator
        gr.Markdown("---")
        gr.Markdown("### ⚡ 3. Smart Test & Save Generator (Box & Position)")
        with gr.Row():
            target_val_box = gr.Textbox(label="1. टारगेट वैल्यू:")
            keyword_box = gr.Textbox(label="2. मुख्य कीवर्ड:")
            direction_radio = gr.Radio(["Right (आगे)", "Below (नीचे)", "📦 Extract Inside Box (डब्बा)"], label="3. दिशा / तरीका:", value="Right (आगे)")
            offset_num = gr.Number(value=1, label="4. Index:")

        test_run_btn = gr.Button("🧪 Test Extraction First", variant="secondary")
        test_output_box = gr.Textbox(label="Test Result", interactive=False)

        target_field_dropdown = gr.Dropdown(choices=[], label="5. यह लॉजिक किस हेडर फील्ड पर सेव करना है?", interactive=True)
        save_tested_rule_btn = gr.Button("💾 Confirm & Save to Shipper Rule", variant="primary")

        # 🛠️ 4. Header Fields Mapping Rules Table
        gr.Markdown("---")
        gr.Markdown("### 🛠️ 4. Header Fields Mapping & Regex Rules")
        header_rules_dataframe = gr.Dataframe(
            headers=["Field Name", "Source Doc", "Keyword", "Cell", "Prompt", "Result Ex", "Local Python/Regex Logic"],
            datatype=["str", "str", "str", "str", "str", "str", "str"],
            row_count=5,
            col_count=7,
            interactive=True,
            label="Header Mapping Rules"
        )

        # 📋 5. Dynamic Item Table Rules & Mapping
        gr.Markdown("---")
        gr.Markdown("### 📋 5. Dynamic Item Table Rules & Mapping")
        parser_dropdown = gr.Dropdown(["parser_welspun", "parser_polycab", "parser_bkt", "parser_vapi_welspun"], label="इस शिपर के लिए आइटम पार्सर चुनें:")
        item_rules_dataframe = gr.Dataframe(
            headers=["Item Field Name", "Excel Col", "Source Type", "Extraction Rule / Keyword", "Result Example"],
            datatype=["str", "str", "str", "str", "str"],
            row_count=5,
            col_count=5,
            interactive=True,
            label="Item Table Rules"
        )

        # ⚙️ 6. IGST & Lut Configuration
        gr.Markdown("---")
        gr.Markdown("### ⚙️ 6. IGST & Lut Configuration")
        with gr.Row():
            lut_kws_box = gr.Textbox(label="LUT Keywords (कॉमा से अलग करें):")
            paid_kws_box = gr.Textbox(label="Paid Keywords (कॉमा से अलग करें):")

        save_all_btn = gr.Button("💾 Save All Rules & Sync to Google Sheet", variant="primary", size="lg")
        sync_status_box = gr.Textbox(label="Sync Status", interactive=False)

    # --- Event Handlers & Dynamic Binding ---
    def on_shipper_select(shipper_name):
        if not shipper_name:
            return (
                gr.update(visible=False), "", gr.update(visible=True), gr.update(visible=False), 
                "", [], [], "parser_welspun", "", "", gr.update(choices=[])
            )
        
        current_db = load_shipper_database()
        s_info = current_db.get(shipper_name, {})
        
        # Template Check
        t_bytes = load_template_bytes_from_sheet(shipper_name)
        has_tpl = t_bytes is not None and len(t_bytes) > 0
        tpl_msg = f"✅ 'Full Job Excel Format File (Template)' गूगल शीट पर अपलोडेड एवं सुरक्षित है।" if has_tpl else "ℹ️ इस शिपर के लिए अभी कोई टेम्पलेट अपलोड नहीं की गई है।"
        
        # Header Rules Rows
        mapping_rules = s_info.get("mapping_rules", {})
        h_rows = [[f, v.get("logic", "Main Invoice"), v.get("keyword", ""), v.get("cell", ""), v.get("ai_prompt", ""), v.get("result_example", ""), v.get("extracted_logic", "")] for f, v in mapping_rules.items()]
        if not h_rows: h_rows = [["", "Main Invoice", "", "", "", "", ""]]
        
        # Item Rules Rows
        item_rules = s_info.get("item_table_rules", {})
        i_rows = [[i, v.get("col", "K"), v.get("type", "PDF Row Item"), v.get("rule", ""), v.get("result_example", "")] for i, v in item_rules.items()]
        if not i_rows: i_rows = [["", "K", "PDF Row Item", "", ""]]
        
        parser_name = s_info.get("item_table_rule_name", "parser_welspun")
        igst_cfg = s_info.get("igst_config", {})
        lut_val = igst_cfg.get("lut_keywords", "LUT, UNDER LUT, UNDER BOND")
        paid_val = igst_cfg.get("paid_keywords", "SUPPLY MEANT FOR EXPORT ON PAYMENT OF IGST.")
        
        field_choices = list(mapping_rules.keys())
        
        return (
            gr.update(visible=True), 
            f"### ⚙️ शिपर प्रोफाइल: **{shipper_name}**",
            gr.update(visible=not has_tpl),
            gr.update(visible=has_tpl),
            tpl_msg,
            h_rows,
            i_rows,
            parser_name,
            lut_val,
            paid_val,
            gr.update(choices=field_choices, value=field_choices[0] if field_choices else None)
        )

    selected_shipper_dropdown.change(
        fn=on_shipper_select,
        inputs=selected_shipper_dropdown,
        outputs=[
            config_container, shipper_title_md, tpl_upload_row, tpl_status_row, 
            tpl_status_txt, header_rules_dataframe, item_rules_dataframe, 
            parser_dropdown, lut_kws_box, paid_kws_box, target_field_dropdown
        ]
    )

    # Template Save & Replace Handlers
    def save_tpl_action(shipper_name, file_obj):
        if not shipper_name or not file_obj:
            return "❌ कृपया शिपर चुनें और फाइल अपलोड करें!"
        with open(file_obj.name, "rb") as f:
            success = push_template_file_to_sheet(shipper_name, f.read())
        return "🎉 टेम्पलेट सफलतापूर्वक गूगल शीट पर सेव हो गई!" if success else "❌ अपलोड फेल हो गया!"

    save_tpl_btn.click(fn=save_tpl_action, inputs=[selected_shipper_dropdown, tpl_file_input], outputs=tpl_action_msg)
    confirm_replace_btn.click(fn=save_tpl_action, inputs=[selected_shipper_dropdown, replace_tpl_file], outputs=tpl_action_msg)
    
    def delete_tpl_action(shipper_name):
        if not shipper_name: return "❌ शिपर नहीं चुना गया।"
        push_template_file_to_sheet(shipper_name, b"")
        return "🗑️ टेम्पलेट डिलीट हो गई!"
    delete_tpl_btn.click(fn=delete_tpl_action, inputs=[selected_shipper_dropdown], outputs=tpl_action_msg)

    # Save All Rules Handler
    def save_all_rules(shipper_name, h_data, i_data, parser_val, lut_val, paid_val):
        if not shipper_name: return "❌ शिपर नहीं चुना गया।"
        db = load_shipper_database()
        s_info = db.setdefault(shipper_name, {})
        
        # Map Header Rules
        new_mapping = {}
        for row in h_data:
            if row and row[0]:
                new_mapping[row[0]] = {
                    "logic": row[1], "keyword": row[2], "cell": row[3], 
                    "ai_prompt": row[4], "result_example": row[5], "extracted_logic": row[6]
                }
        s_info["mapping_rules"] = new_mapping
        
        # Map Item Rules
        new_items = {}
        for row in i_data:
            if row and row[0]:
                new_items[row[0]] = {
                    "col": row[1], "type": row[2], "rule": row[3], "result_example": row[4]
                }
        s_info["item_table_rules"] = new_items
        s_info["item_table_rule_name"] = parser_val
        s_info["igst_config"] = {"lut_keywords": lut_val, "paid_keywords": paid_val}
        
        _, msg = save_shipper_database_to_sheet(db)
        return msg

    save_all_btn.click(
        fn=save_all_rules,
        inputs=[selected_shipper_dropdown, header_rules_dataframe, item_rules_dataframe, parser_dropdown, lut_kws_box, paid_kws_box],
        outputs=sync_status_box
    )