import sys
import os
import datetime

# Append project root to sys.path to enable src imports
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

import src.db as db

def run_tests():
    print("--- Starting Kisan-Sathi SQLite Verification ---")
    
    # 1. Initialize DB
    # We will override DB_FILE to run in a test database to avoid polluting real user data
    db.DB_FILE = os.path.join(db.DB_DIR, "kisan_test.db")
    if os.path.exists(db.DB_FILE):
        try: os.remove(db.DB_FILE)
        except: pass
        
    db.init_db()
    print("[TEST] SQLite database initialized successfully.")
    
    # 2. Test User Profile
    res = db.save_profile("Sandeep", "Uttar Pradesh", "Kanpur Dehat", "hi")
    assert res is True
    prof = db.get_profile()
    assert prof is not None
    assert prof["name"] == "Sandeep"
    assert prof["state"] == "Uttar Pradesh"
    assert prof["district"] == "Kanpur Dehat"
    print("[PASS] User profile CRUD verified.")
    
    # 3. Test Add Crop Calendar & Tasks generation
    # Sowing date: 2025-11-15
    cal_id = db.add_calendar("Wheat", "PBW-343", "2025-11-15", "Kanpur Dehat")
    assert cal_id is not None
    assert cal_id > 0
    
    cals = db.get_calendars()
    assert len(cals) == 1
    cal = cals[0]
    assert cal["crop"] == "Wheat"
    assert cal["variety"] == "PBW-343"
    assert cal["sow_date"] == "2025-11-15"
    
    # Verify tasks generated
    # Wheat CRI Irrigation is offset 21 days
    # sow_date 2025-11-15 + 21 days = 2025-12-06
    tasks = cal["tasks"]
    assert len(tasks) > 0
    cri_task = next((t for t in tasks if t["stage"] == "cri"), None)
    assert cri_task is not None
    assert cri_task["target_date"] == "2025-12-06"
    assert cri_task["status"] == "pending"
    print("[PASS] Crop calendar addition and task generation verified.")

    # 3b. Test Update Sowing Date & Recalculation
    # New sowing date: 2025-11-20
    # sow_date 2025-11-20 + 21 days = 2025-12-11
    res = db.update_calendar_sow_date(cal_id, "2025-11-20")
    assert res is True
    
    cals = db.get_calendars()
    cal = cals[0]
    assert cal["sow_date"] == "2025-11-20"
    
    tasks = cal["tasks"]
    cri_task = next((t for t in tasks if t["stage"] == "cri"), None)
    assert cri_task is not None
    assert cri_task["target_date"] == "2025-12-11"
    print("[PASS] Crop calendar update and task date recalculation verified.")

    # 4. Test Task Status Toggle
    task_id = cri_task["id"]
    res = db.update_task_status(task_id, "done")
    assert res is True
    
    cals = db.get_calendars()
    cal = cals[0]
    cri_task_updated = next((t for t in cal["tasks"] if t["id"] == task_id), None)
    assert cri_task_updated is not None
    assert cri_task_updated["status"] == "done"
    print("[PASS] Task status toggling verified.")
    
    # 5. Test Delete Crop Calendar
    res = db.delete_calendar(cal_id)
    assert res is True
    cals = db.get_calendars()
    assert len(cals) == 0
    print("[PASS] Calendar deletion cascade verified.")
    
    # 6. Test Ledger Operations
    db.clear_ledger()
    # Add sale
    res = db.add_ledger_entry("2025-11-15", "sale", "Wheat", "40kg", "", 1200.0)
    assert res is True
    # Add purchase
    res = db.add_ledger_entry("2025-11-16", "purchase", "Urea", "1 bag", "", 450.0)
    assert res is True
    
    entries, summary = db.get_ledger_entries()
    assert len(entries) == 2
    assert summary["income"] == 1200
    assert summary["expense"] == 450
    assert summary["balance"] == 750
    print("[PASS] Ledger transaction additions and calculations verified.")
    
    # Test single row deletion
    entry_to_delete = entries[0]["id"]
    res = db.delete_ledger_entry(entry_to_delete)
    assert res is True
    
    entries, summary = db.get_ledger_entries()
    assert len(entries) == 1
    print("[PASS] Single ledger row deletion verified.")
    
    # Test clear ledger
    res = db.clear_ledger()
    assert res is True
    entries, summary = db.get_ledger_entries()
    assert len(entries) == 0
    assert summary["income"] == 0
    assert summary["expense"] == 0
    assert summary["balance"] == 0
    print("[PASS] Full ledger clearing verified.")
    
    # Clean up test DB
    if os.path.exists(db.DB_FILE):
        try: os.remove(db.DB_FILE)
        except: pass
        
    print("\n[SUCCESS] ALL SQLite OPERATIONS VERIFIED AND PASSED!")

if __name__ == "__main__":
    run_tests()