import os
import json
import duckdb
from shiny import render, reactive, ui
from google.oauth2 import service_account
from googleapiclient.discovery import build
import requests
import base64
import xml.etree.ElementTree as ET
from dotenv import load_dotenv
import time
import pandas as pd

load_dotenv()

WALMART_CLIENT_ID = os.getenv('WALMART_CLIENT_ID')
WALMART_CLIENT_SECRET = os.getenv('WALMART_CLIENT_SECRET')

SERVICE_ACCOUNT_JSON = json.loads(os.getenv("SERVICE_ACCOUNT_JSON"))
GOOGLE_FOLDER_ID = os.getenv("GOOGLE_DRIVE_FOLDER_ID")
SCOPES = [
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/spreadsheets.readonly"
]


credentials = service_account.Credentials.from_service_account_info(SERVICE_ACCOUNT_JSON, scopes=SCOPES)
drive_service = build("drive", "v3", credentials=credentials)
sheets_service = build("sheets", "v4", credentials=credentials)

def list_sheets_in_folder(folder_id):
    query = (
        f"'{folder_id}' in parents and "
        "mimeType = 'application/vnd.google-apps.spreadsheet' and trashed = false"
    )
    results = drive_service.files().list(q=query, fields="files(id, name)").execute()
    return results.get("files", [])

def load_sheet_to_duckdb(spreadsheet_id, sheet_tab="Sheet1", table_name="active_sheet"):
    values = (
        sheets_service.spreadsheets()
        .values()
        .get(spreadsheetId=spreadsheet_id, range=sheet_tab)
        .execute()
        .get("values", [])
    )
    if not values:
        raise ValueError("Google Sheet is empty or not accessible.")

    headers = values[0]
    # pad rows to header length
    rows = [r + [''] * (len(headers) - len(r)) for r in values[1:]]

    # Build DataFrame (avoids all quoting issues)
    df = pd.DataFrame(rows, columns=headers)

    con = duckdb.connect()
    # Register df and create/replace the table from it
    con.register("temp_df", df)
    con.execute(f'CREATE OR REPLACE TABLE "{table_name}" AS SELECT * FROM temp_df')
    con.unregister("temp_df")
    return con


sheet_index = {}

def server(input, output, session):
    con = {"conn": None}

    @reactive.Effect
    def init_dropdown_from_folder():
        global sheet_index
        try:
            sheets = list_sheets_in_folder(GOOGLE_FOLDER_ID)
            sheet_index = {s['name']: s['id'] for s in sheets}
            sheet_names = list(sheet_index.keys())
            print(f"[DEBUG] Found {len(sheet_names)} sheets in folder: {sheet_names}")

            ui.update_select("sheet_dropdown", choices=sheet_names)

        except Exception as e:
            print(f"[ERROR] Failed to list folder contents or populate dropdown: {e}")

    @reactive.Effect
    def load_selected_sheet():
        sheet_name = input.sheet_dropdown()
        if sheet_name not in sheet_index:
            return
        spreadsheet_id = sheet_index[sheet_name]
        print(f"[DEBUG] Loading sheet: {sheet_name} (ID: {spreadsheet_id})")
        con["conn"] = load_sheet_to_duckdb(spreadsheet_id)

    @output
    @render.table
    def results():
        sheet_name = input.sheet_dropdown()  # force reactivity on sheet change

        try:
            if con["conn"] is None:
                return duckdb.query("SELECT 'Waiting for sheet selection' AS status").to_df()

            name_query = input.name_filter()
            if name_query:
                return con["conn"].execute(
                    "SELECT * FROM active_sheet WHERE item_name ILIKE '%' || ? || '%'",
                    (name_query,)
                ).fetchdf()
            else:
                return con["conn"].execute("SELECT * FROM active_sheet").fetchdf()

        except Exception as e:
            return duckdb.query(f"SELECT 'Error: {str(e).replace('\'','')}' AS error").to_df()