src/db.py

import os
import sqlite3
import json
import csv
from datetime import datetime, timedelta

DB_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data")
DB_FILE = os.path.join(DB_DIR, "kisan.db")

# Crop-stage templates derived from KVK Kanpur / UP packages
CROP_TEMPLATES = {
    "wheat": {
        "display": {"en": "Wheat", "hi": "गेहूँ", "hinglish": "Gehun"},
        "stages": [
            {
                "key": "sowing",
                "day_offset": 0,
                "window": [0, 0],
                "action": {"en": "Sowing", "hi": "बुवाई", "hinglish": "Sowing"},
                "intervention": {
                    "en": "Apply basal fertilizer dose: 50 kg DAP and 20 kg MOP per acre. (Confirm with agriculture officer)",
                    "hi": "बेसल उर्वरक खुराक डालें: 50 किलो डीएपी और 20 किलो एमओपी प्रति एकड़। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Basal fertilizer dose dalein: 50 kg DAP aur 20 kg MOP per acre. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "cri",
                "day_offset": 21,
                "window": [20, 25],
                "action": {"en": "First Irrigation (CRI Stage)", "hi": "पहली सिंचाई (CRI अवस्था)", "hinglish": "Pehli Irrigation (CRI Stage)"},
                "intervention": {
                    "en": "Irrigate crop (21-25 days after sowing). Top-dress with 40 kg Urea per acre. (Confirm with agriculture officer)",
                    "hi": "फसल की सिंचाई करें (बुवाई के 21-25 दिन बाद)। 40 किलोग्राम यूरिया प्रति एकड़ का छिड़काव करें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Fasal ki irrigation karein (sowing ke 21-25 din baad). 40 kg Urea per acre top-dress karein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "tillering",
                "day_offset": 45,
                "window": [40, 50],
                "action": {"en": "Second Irrigation + Weeding", "hi": "दूसरी सिंचाई + निराई", "hinglish": "Doosri Irrigation + Weeding"},
                "intervention": {
                    "en": "Perform manual weeding or apply recommended weedicide. Apply second irrigation. (Confirm with agriculture officer)",
                    "hi": "हाथ से निराई करें या अनुशंसित शाकनाशी का प्रयोग करें। दूसरी सिंचाई करें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Manual weeding karein ya weedicide apply karein. Doosri irrigation karein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "jointing",
                "day_offset": 65,
                "window": [60, 70],
                "action": {"en": "Third Irrigation + Urea Top-dress", "hi": "तीसरी सिंचाई + यूरिया", "hinglish": "Teesri Irrigation + Urea Top-dress"},
                "intervention": {
                    "en": "Apply second split of Urea (40 kg/acre) and perform third irrigation. (Confirm with agriculture officer)",
                    "hi": "यूरिया की दूसरी खुराक (40 किग्रा/एकड़) डालें और तीसरी सिंचाई करें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Urea ki doosri split (40 kg/acre) dalein aur teesri irrigation karein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "flowering",
                "day_offset": 85,
                "window": [80, 90],
                "action": {"en": "Fourth Irrigation", "hi": "चौथी सिंचाई", "hinglish": "Chauthi Irrigation"},
                "intervention": {
                    "en": "Maintain light moisture in the field. Critical for grain yield. (Confirm with agriculture officer)",
                    "hi": "खेत में हल्की नमी बनाए रखें। दाना बनने की प्रक्रिया के लिए महत्वपूर्ण। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Khet me halki moisture banaye rakhein. Grains ke liye zaroori hai. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "milking",
                "day_offset": 105,
                "window": [100, 110],
                "action": {"en": "Fifth Irrigation", "hi": "पांचवीं सिंचाई", "hinglish": "Panchvi Irrigation"},
                "intervention": {
                    "en": "Irrigate to support grain filling and prevent premature drying. (Confirm with agriculture officer)",
                    "hi": "दाना भरने में सहायता करने और समय से पहले सूखने से बचाने के लिए सिंचाई करें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Irrigate karein taaki grains acche se bharein aur jaldi na sookhein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "harvest",
                "day_offset": 125,
                "window": [120, 135],
                "action": {"en": "Harvesting", "hi": "कटाई", "hinglish": "Harvesting"},
                "intervention": {
                    "en": "Harvest when grains are dry and golden (moisture level < 14%).",
                    "hi": "फसल सुनहरी होने और दाने में 14% से कम नमी होने पर कटाई करें।",
                    "hinglish": "Fasal sunhari hone aur daane me 14% se kam moisture hone par harvesting karein."
                }
            }
        ]
    },
    "potato": {
        "display": {"en": "Potato", "hi": "आलू", "hinglish": "Aloo"},
        "stages": [
            {
                "key": "sowing",
                "day_offset": 0,
                "window": [0, 0],
                "action": {"en": "Sowing", "hi": "बुवाई", "hinglish": "Sowing"},
                "intervention": {
                    "en": "Use certified seed tubers. Spacing 60x20 cm. Apply NPK basal dose. (Confirm with agriculture officer)",
                    "hi": "प्रमाणित बीज कंदों का प्रयोग करें। ६०x२० सेमी दूरी रखें। एनपीके बेसल खुराक डालें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Certified seed tubers use karein. Spacing 60x20 cm. NPK basal dose dalein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "emergence",
                "day_offset": 15,
                "window": [12, 18],
                "action": {"en": "First Irrigation", "hi": "पहली सिंचाई", "hinglish": "Pehli Irrigation"},
                "intervention": {
                    "en": "Apply light irrigation 15 days post planting for uniform emergence. (Confirm with agriculture officer)",
                    "hi": "समान अंकुरण के लिए रोपण के 15 दिन बाद हल्की सिंचाई करें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Uniform emergence ke liye planting ke 15 din baad halki irrigation karein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "earthing",
                "day_offset": 25,
                "window": [20, 30],
                "action": {"en": "Earthing Up + Urea", "hi": "मिट्टी चढ़ाना + यूरिया", "hinglish": "Earthing Up + Urea"},
                "intervention": {
                    "en": "Weed the field. Apply Urea and mound soil around stems to prevent greening. (Confirm with agriculture officer)",
                    "hi": "निराई करें। यूरिया डालें और तने पर मिट्टी चढ़ाएं ताकि कंद हरे न हों। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Weeding karein. Urea dalein aur stem par mitti chadhayein taaki tubers green na ho. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "tuber_initiation",
                "day_offset": 45,
                "window": [40, 50],
                "action": {"en": "Second Irrigation", "hi": "दूसरी सिंचाई", "hinglish": "Doosri Irrigation"},
                "intervention": {
                    "en": "Ensure adequate soil moisture. Critical for tuber formation. (Confirm with agriculture officer)",
                    "hi": "मिट्टी में पर्याप्त नमी सुनिश्चित करें। कंद बनने के लिए यह चरण बहुत महत्वपूर्ण है। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Mitti me moisture banaye rakhein. Tuber formation ke liye zaroori hai. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "bulking",
                "day_offset": 75,
                "window": [70, 80],
                "action": {"en": "Third Irrigation + Blight Check", "hi": "तीसरी सिंचाई + झुलसा निगरानी", "hinglish": "Teesri Irrigation + Blight Check"},
                "intervention": {
                    "en": "Irrigate and check for Late Blight spots. Spray Mancozeb (2g/L) if cloudy/foggy. (Confirm with agriculture officer)",
                    "hi": "सिंचाई करें और पिछेती झुलसा के काले धब्बों की जांच करें। बादल/कोहरा होने पर मैन्कोजेब (2 ग्राम/लीटर) छिड़कें। (कृषि अधिकारी से पुष्टि करें)",
                    "hinglish": "Irrigation karein aur Late Blight ke spots check karein. Mancozeb (2g/L) spray karein. (Agri-officer se confirm karein)"
                }
            },
            {
                "key": "dehaulming",
                "day_offset": 110,
                "window": [105, 115],
                "action": {"en": "Foliage Cutting (Dehaulming)", "hi": "डंठल कटाई (Dehaulming)", "hinglish": "Danthal Katayi (Dehaulming)"},
                "intervention": {
                    "en": "Cut foliage 10 days before harvest to thicken potato skin for storage.",
                    "hi": "खुदाई से 10 दिन पहले डंठल काट लें ताकि भंडारण के लिए छिलका सख्त हो।",
                    "hinglish": "Khodai se 10 din pehle danthal kaat lein taaki storage ke liye skin tight ho jaye."
                }
            },
            {
                "key": "harvest",
                "day_offset": 120,
                "window": [115, 130],
                "action": {"en": "Harvesting", "hi": "खुदाई", "hinglish": "Khodai"},
                "intervention": {
                    "en": "Dig out potatoes carefully in dry weather. Allow tubers to dry in shade.",
                    "hi": "सूखे मौसम में सावधानीपूर्वक आलू की खुदाई करें। कंदों को छाया में सुखाएं।",
                    "hinglish": "Sookhe mausam me potato ki khodai karein. Tubers ko chhaya me sukhayein."
                }
            }
        ]
    }
}

def get_db():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    os.makedirs(DB_DIR, exist_ok=True)
    conn = get_db()
    cursor = conn.cursor()
    
    # 1. Profile Table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS profile (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            state TEXT,
            district TEXT,
            language TEXT
        )
    """)
    
    # 2. Calendars Table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS calendars (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            crop TEXT,
            variety TEXT,
            sow_date TEXT,
            location TEXT,
            created_at TEXT
        )
    """)
    
    # 3. Tasks Table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            calendar_id INTEGER,
            stage TEXT,
            action TEXT,
            intervention TEXT,
            target_date TEXT,
            window_start TEXT,
            window_end TEXT,
            status TEXT DEFAULT 'pending',
            notes TEXT,
            FOREIGN KEY(calendar_id) REFERENCES calendars(id) ON DELETE CASCADE
        )
    """)
    
    # 4. Ledger Table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS ledger (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            type TEXT,
            item TEXT,
            qty TEXT,
            unit TEXT,
            price REAL,
            created_at TEXT
        )
    """)
    
    conn.commit()
    
    # --- Ingestion / Migration ---
    # Migration A: Profile
    cursor.execute("SELECT COUNT(*) FROM profile")
    if cursor.fetchone()[0] == 0:
        json_profile_path = os.path.join(DB_DIR, "user_profile.json")
        if os.path.exists(json_profile_path):
            try:
                with open(json_profile_path, "r", encoding="utf-8") as f:
                    prof_data = json.load(f)
                    cursor.execute(
                        "INSERT INTO profile (name, state, district, language) VALUES (?, ?, ?, ?)",
                        (prof_data.get("name"), prof_data.get("state"), prof_data.get("district"), prof_data.get("language", "hi"))
                    )
                    conn.commit()
                    print("[db.py] Migrated user profile from JSON file.")
            except Exception as e:
                print(f"[db.py] Profile migration error: {e}")
                
    # Migration B: Ledger CSV
    cursor.execute("SELECT COUNT(*) FROM ledger")
    if cursor.fetchone()[0] == 0:
        csv_ledger_path = os.path.join(DB_DIR, "ledger.csv")
        if os.path.exists(csv_ledger_path):
            try:
                with open(csv_ledger_path, "r", encoding="utf-8") as f:
                    reader = csv.reader(f)
                    header = next(reader, None)
                    if header:
                        for row in reader:
                            if len(row) >= 5:
                                date = row[0]
                                item = row[1]
                                qty = row[2]
                                try:
                                    price = float(row[3])
                                except ValueError:
                                    price = 0.0
                                raw_type = str(row[4]).lower()
                                trans_type = "sale" if "sale" in raw_type or "बिक्री" in raw_type else "purchase"
                                timestamp = row[5] if len(row) > 5 else datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                                
                                cursor.execute(
                                    "INSERT INTO ledger (date, type, item, qty, unit, price, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
                                    (date, trans_type, item, qty, "", price, timestamp)
                                )
                        conn.commit()
                        print("[db.py] Migrated ledger records from CSV file.")
            except Exception as e:
                print(f"[db.py] Ledger migration error: {e}")
                
    conn.close()

# --- PROFILE ACTIONS ---
def get_profile():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT name, state, district, language FROM profile LIMIT 1")
    row = cursor.fetchone()
    conn.close()
    if row:
        return dict(row)
    return None

def save_profile(name, state, district, language="hi"):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT id FROM profile LIMIT 1")
    row = cursor.fetchone()
    if row:
        cursor.execute(
            "UPDATE profile SET name = ?, state = ?, district = ?, language = ? WHERE id = ?",
            (name, state, district, language, row["id"])
        )
    else:
        cursor.execute(
            "INSERT INTO profile (name, state, district, language) VALUES (?, ?, ?, ?)",
            (name, state, district, language)
        )
    conn.commit()
    conn.close()
    return True

# --- CALENDAR & TASK ACTIONS ---
def add_calendar(crop, variety, sow_date_str, location):
    # Verify crop template exists
    crop_key = crop.lower()
    if crop_key not in CROP_TEMPLATES:
        raise ValueError(f"Crop '{crop}' has no verified template.")
        
    template = CROP_TEMPLATES[crop_key]
    
    try:
        sow_dt = datetime.strptime(sow_date_str, "%Y-%m-%d")
    except ValueError:
        sow_dt = datetime.now()
        sow_date_str = sow_dt.strftime("%Y-%m-%d")
        
    created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = get_db()
    cursor = conn.cursor()
    
    # 1. Insert calendar
    cursor.execute(
        "INSERT INTO calendars (crop, variety, sow_date, location, created_at) VALUES (?, ?, ?, ?, ?)",
        (crop, variety, sow_date_str, location, created_at)
    )
    calendar_id = cursor.lastrowid
    
    # 2. Insert calculated tasks
    for stage in template["stages"]:
        offset = stage["day_offset"]
        target_dt = sow_dt + timedelta(days=offset)
        target_date_str = target_dt.strftime("%Y-%m-%d")
        
        win_start_offset, win_end_offset = stage["window"]
        win_start_dt = sow_dt + timedelta(days=win_start_offset)
        win_end_dt = sow_dt + timedelta(days=win_end_offset)
        
        win_start_str = win_start_dt.strftime("%Y-%m-%d")
        win_end_str = win_end_dt.strftime("%Y-%m-%d")
        
        cursor.execute(
            """
            INSERT INTO tasks (calendar_id, stage, action, intervention, target_date, window_start, window_end, status)
            VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')
            """,
            (
                calendar_id,
                stage["key"],
                json.dumps(stage["action"]),
                json.dumps(stage["intervention"]),
                target_date_str,
                win_start_str,
                win_end_str
            )
        )
        
    conn.commit()
    conn.close()
    return calendar_id

def get_calendars():
    conn = get_db()
    cursor = conn.cursor()
    
    # Load calendars
    cursor.execute("SELECT id, crop, variety, sow_date, location, created_at FROM calendars ORDER BY id DESC")
    calendars_rows = cursor.fetchall()
    
    calendars_list = []
    for cal in calendars_rows:
        cal_dict = dict(cal)
        cal_id = cal["id"]
        
        # Load tasks for this calendar
        cursor.execute(
            """
            SELECT id, stage, action, intervention, target_date, window_start, window_end, status, notes
            FROM tasks WHERE calendar_id = ? ORDER BY target_date ASC
            """,
            (cal_id,)
        )
        tasks_rows = cursor.fetchall()
        tasks_list = []
        for task in tasks_rows:
            t_dict = dict(task)
            # Decode action and intervention JSON
            try:
                t_dict["action"] = json.loads(t_dict["action"])
            except:
                pass
            try:
                t_dict["intervention"] = json.loads(t_dict["intervention"])
            except:
                pass
            tasks_list.append(t_dict)
            
        cal_dict["tasks"] = tasks_list
        calendars_list.append(cal_dict)
        
    conn.close()
    return calendars_list

def update_calendar_sow_date(calendar_id, new_sow_date_str):
    try:
        new_sow_dt = datetime.strptime(new_sow_date_str, "%Y-%m-%d")
    except ValueError:
        raise ValueError("Invalid date format. Expected YYYY-MM-DD.")
        
    conn = get_db()
    cursor = conn.cursor()
    
    # Get the crop for this calendar
    cursor.execute("SELECT crop FROM calendars WHERE id = ?", (calendar_id,))
    row = cursor.fetchone()
    if not row:
        conn.close()
        raise ValueError(f"Calendar with ID {calendar_id} not found.")
    
    crop = row["crop"]
    crop_key = crop.lower()
    if crop_key not in CROP_TEMPLATES:
        conn.close()
        raise ValueError(f"Crop '{crop}' has no verified template.")
        
    template = CROP_TEMPLATES[crop_key]
    
    # Update the calendar's sow_date
    cursor.execute("UPDATE calendars SET sow_date = ? WHERE id = ?", (new_sow_date_str, calendar_id))
    
    # For each stage in the template, recalculate dates and update the tasks
    for stage in template["stages"]:
        offset = stage["day_offset"]
        target_dt = new_sow_dt + timedelta(days=offset)
        target_date_str = target_dt.strftime("%Y-%m-%d")
        
        win_start_offset, win_end_offset = stage["window"]
        win_start_dt = new_sow_dt + timedelta(days=win_start_offset)
        win_end_dt = new_sow_dt + timedelta(days=win_end_offset)
        
        win_start_str = win_start_dt.strftime("%Y-%m-%d")
        win_end_str = win_end_dt.strftime("%Y-%m-%d")
        
        cursor.execute(
            """
            UPDATE tasks 
            SET target_date = ?, window_start = ?, window_end = ?
            WHERE calendar_id = ? AND stage = ?
            """,
            (target_date_str, win_start_str, win_end_str, calendar_id, stage["key"])
        )
        
    conn.commit()
    conn.close()
    return True

def delete_calendar(calendar_id):
    conn = get_db()
    cursor = conn.cursor()
    # Enable foreign key support first
    cursor.execute("PRAGMA foreign_keys = ON")
    cursor.execute("DELETE FROM calendars WHERE id = ?", (calendar_id,))
    # Also delete associated tasks explicitly in case cascades aren't on in SQLite configuration
    cursor.execute("DELETE FROM tasks WHERE calendar_id = ?", (calendar_id,))
    conn.commit()
    conn.close()
    return True

def update_task_status(task_id, status):
    if status not in ["pending", "done", "skipped"]:
        raise ValueError("Invalid status value.")
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("UPDATE tasks SET status = ? WHERE id = ?", (status, task_id))
    conn.commit()
    conn.close()
    return True

# --- LEDGER ACTIONS ---
def add_ledger_entry(date_str, trans_type, item, qty, unit="", price=0.0):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO ledger (date, type, item, qty, unit, price, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
        (date_str, trans_type, item, qty, unit, price, timestamp)
    )
    conn.commit()
    conn.close()
    return True

def get_ledger_entries():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT id, date, type, item, qty, unit, price, created_at FROM ledger ORDER BY created_at DESC")
    rows = cursor.fetchall()
    
    entries = []
    income = 0.0
    expense = 0.0
    
    for r in rows:
        row_dict = dict(r)
        entries.append(row_dict)
        
        price = row_dict["price"] or 0.0
        t_type = row_dict["type"]
        if t_type == "sale":
            income += price
        else:
            expense += price
            
    conn.close()
    
    summary = {
        "income": int(income),
        "expense": int(expense),
        "balance": int(income - expense)
    }
    return entries, summary

def delete_ledger_entry(entry_id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM ledger WHERE id = ?", (entry_id,))
    conn.commit()
    conn.close()
    return True

def clear_ledger():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM ledger")
    conn.commit()
    conn.close()
    return True

def clear_all_data():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM profile")
    cursor.execute("DELETE FROM calendars")
    cursor.execute("DELETE FROM tasks")
    cursor.execute("DELETE FROM ledger")
    conn.commit()
    conn.close()
    
    # Remove migration files if they exist to prevent automatic re-import
    json_profile_path = os.path.join(DB_DIR, "user_profile.json")
    if os.path.exists(json_profile_path):
        try: os.remove(json_profile_path)
        except: pass
        
    csv_ledger_path = os.path.join(DB_DIR, "ledger.csv")
    if os.path.exists(csv_ledger_path):
        try: os.remove(csv_ledger_path)
        except: pass
        
    return True