import os
import json
import csv
import pandas as pd
from datetime import datetime

DATA_DIR = os.path.dirname(os.path.abspath(__file__)) + "/data"
LEDGER_FILE = DATA_DIR + "/ledger.csv"

# System prompt for LLM parsing of natural language transactions
PARSING_SYSTEM_PROMPT = (
    "You are a precise data extractor. Your task is to extract financial transaction details from "
    "the farmer's text and return them in EXACTLY the following JSON format. Do not return any other text, "
    "explanation, or markdown blocks. Just the raw JSON.\n\n"
    "JSON Format:\n"
    "{\n"
    "  \"date\": \"string (e.g., today, yesterday, 5 June)\",\n"
    "  \"item\": \"string (e.g., wheat, potato, urea)\",\n"
    "  \"qty\": \"string (e.g., 40kg, 2 bags, 1 unit)\",\n"
    "  \"price\": \"integer (only numbers, e.g. 1200)\",\n"
    "  \"type\": \"string (must be either 'sale' or 'purchase')\"\n"
    "}\n\n"
    "Examples:\n"
    "1. Input: \"sold 40kg wheat for 1200 rupees today\"\n"
    "   Output: {\"date\": \"today\", \"item\": \"wheat\", \"qty\": \"40kg\", \"price\": 1200, \"type\": \"sale\"}\n"
    "2. Input: \"आज मैंने 500 रुपये का यूरिया खरीदा\"\n"
    "   Output: {\"date\": \"today\", \"item\": \"urea\", \"qty\": \"1 unit\", \"price\": 500, \"type\": \"purchase\"}\n"
    "3. Input: \"कल 6000 रुपये का आलू बेचा\"\n"
    "   Output: {\"date\": \"yesterday\", \"item\": \"potato\", \"qty\": \"1 unit\", \"price\": 6000, \"type\": \"sale\"}\n"
)

def init_ledger():
    """Create ledger file with headers if it doesn't exist."""
    os.makedirs(DATA_DIR, exist_ok=True)
    if not os.path.exists(LEDGER_FILE):
        with open(LEDGER_FILE, "w", encoding="utf-8", newline="") as f:
            writer = csv.writer(f)
            writer.writerow(["Date", "Item (सामग्री)", "Quantity (मात्रा)", "Price (मूल्य)", "Type (प्रकार)", "Timestamp"])
        print(f"[ledger.py] Initialized new ledger CSV at {LEDGER_FILE}")

def parse_transaction(text, generate_fn):
    """
    Calls the LLM generation function to parse transaction details.
    Returns a dictionary of parsed details.
    """
    prompt = f"Extract details from this text: \"{text}\""
    response = generate_fn(prompt, system=PARSING_SYSTEM_PROMPT, stream=False)
    
    # Clean up output from any markdown code block wraps
    cleaned = response.strip()
    if cleaned.startswith("```json"):
        cleaned = cleaned[7:]
    if cleaned.startswith("```"):
        cleaned = cleaned[3:]
    if cleaned.endswith("```"):
        cleaned = cleaned[:-3]
    cleaned = cleaned.strip()
    
    # Look for JSON boundaries if there is wrapping text
    json_match = re.search(r"\{.*\}", cleaned, re.DOTALL)
    if json_match:
        cleaned = json_match.group(0)
        
    try:
        data = json.loads(cleaned)
        # Ensure price is a clean integer
        if "price" in data:
            price_str = str(data["price"]).replace(",", "").strip()
            # Extract digits only
            digits = "".join(filter(str.isdigit, price_str))
            data["price"] = int(digits) if digits else 0
        return data
    except Exception as e:
        print(f"[ledger.py] Error parsing JSON from LLM response: {e}. Raw response: {response}")
        # Fallback to empty details
        return {
            "date": "today",
            "item": "",
            "qty": "",
            "price": 0,
            "type": "sale"
        }

import src.db as db

def add_entry(date, item, qty, price, trans_type):
    """Append a transaction entry to the database."""
    try:
        db.add_ledger_entry(date, trans_type, item, qty, "", float(price))
        return True
    except Exception as e:
        print(f"[ledger.py] Error adding entry to ledger: {e}")
        return False

def get_ledger_data():
    """Read ledger and return (dataframe, summary_dict)"""
    try:
        entries, summary = db.get_ledger_entries()
        # Create a dataframe from entries to keep compatibility
        # Table columns: date, type, item, qty, unit, price, created_at
        df_data = []
        for r in entries:
            # Map database keys to columns expected by previous app.py logic
            # columns: ["Date", "Item (सामग्री)", "Quantity (मात्रा)", "Price (मूल्य)", "Type (प्रकार)", "Timestamp"]
            # But wait! app.py line 255-260 does:
            # date_val = r.get("Date", r.get("दिनांक", ""))
            # item_val = r.get("Item (सामग्री)", r.get("Item", ""))
            # qty_val = r.get("Quantity (मात्रा)", r.get("Quantity", ""))
            # price_val = r.get("Price (मूल्य)", r.get("Price", 0))
            # raw_type = str(r.get("Type (प्रकार)", r.get("Type", ""))).lower()
            # If we return a pandas DataFrame with columns matching these keys, it will work.
            # Let's map it:
            df_data.append({
                "Id": r["id"],
                "Date": r["date"],
                "Item": r["item"],
                "Quantity": r["qty"],
                "Price": r["price"],
                "Type": r["type"],
                "Timestamp": r["created_at"]
            })
        df = pd.DataFrame(df_data)
        return df, summary
    except Exception as e:
        print(f"[ledger.py] Error reading ledger: {e}")
        return pd.DataFrame(), {"income": 0, "expense": 0, "balance": 0}

import re