import sqlite3
import os
from datetime import datetime, timedelta

# Path to the database
DB_PATH = "c:/Users/Aashishbdr/Desktop/Project/CEMS/Fast API/cems.db"

def seed_data():
    if not os.path.exists(DB_PATH):
        print(f"Database not found at {DB_PATH}")
        return

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    print("Seeding Nepali Projects...")
    projects = [
        ("Kathmandu View Tower", "KVT-01", "Old Bus Park, Kathmandu", "ongoing", 500000000),
        ("Pokhara International Airport Road", "PAR-02", "Chinnechaur, Pokhara", "ongoing", 120000000),
        ("Bhairahawa Special Economic Zone", "BSEZ-03", "Bhairahawa, Lumbini", "completed", 85000000),
        ("Dharan-Chatara-Gaighat Road", "DCG-04", "East-West Highway Connection", "pending", 250000000),
        ("Lumbini Master Plan Phase II", "LMP-05", "Lumbini, Rupandehi", "ongoing", 300000000)
    ]
    cursor.executemany("INSERT OR IGNORE INTO project_tb (name, code, location, status, budget) VALUES (?, ?, ?, ?, ?)", projects)

    print("Seeding Nepali Employees...")
    employees = [
        ("Aashish", "Bdr", "Chettri", "aashish@cems.com", "9841234567", "Kathmandu", "Senior Engineer", 1, 1),
        ("Ram", "Kumar", "Shrestha", "ram@cems.com", "9801234567", "Lalitpur", "Site Supervisor", 1, 1),
        ("Sita", "", "Maya", "sita@cems.com", "9811234567", "Bhaktapur", "Accountant", 2, 1),
        ("Hari", "Prasad", "Luitel", "hari@cems.com", "9851234567", "Pokhara", "Project Manager", 1, 1),
        ("Maya", "", "Devi", "maya@cems.com", "9861234567", "Dharan", "Safety Officer", 2, 1)
    ]
    # Check if middle_name column exists or if it's just first/last
    cursor.executemany("INSERT OR IGNORE INTO employee_tb (first_name, middle_name, last_name, email, phone_number, address, designation, gender_id, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", employees)

    print("Seeding Financial Transactions...")
    transactions = [
        ("Initial Project Deposit - KVT", "income", "Revenue", 5000000, "2024-05-01", "INV-1001", 1),
        ("Cement Purchase - Ultratech", "expense", "Material", 120000, "2024-05-05", "VOU-202", 1),
        ("Worker Wages - Week 1", "expense", "Labor", 85000, "2024-05-07", "WAG-01", 1),
        ("Equipment Lease Payment", "expense", "Asset", 45000, "2024-05-10", "LES-99", 1),
        ("Consultancy Fee Received", "income", "Service", 200000, "2024-05-12", "INV-1002", 1)
    ]
    cursor.executemany("INSERT OR IGNORE INTO transaction_tb (title, type, category, amount, transaction_date, reference_number, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)", transactions)

    conn.commit()
    conn.close()
    print("Nepal Test Data Seeded Successfully!")

if __name__ == "__main__":
    seed_data()