src/database/mockup_data.py

import sqlite3
import uuid

def generate_uuid():
    return str(uuid.uuid4())

def insert_mock_data():
    conn = sqlite3.connect('hello_earth_data_2.db')
    cursor = conn.cursor()

    mock_data = []

    # Project 1
    project_1_id = generate_uuid()
    deliverable_1_1_id = generate_uuid()
    deliverable_1_2_id = generate_uuid()
    mock_data.append({
        "project": {
            "project_id": project_1_id,
            "title": "Mangrove Restoration",
            "status": "started",
            "detail_information": "Restoring coastal ecosystems using mangrove plantations.",
            "start_date": "2025-01-01",
            "end_date": "2025-12-31"
        },
        "deliverables": [
            {
                "deliverable_id": deliverable_1_1_id,
                "title": "Area Assessment",
                "status": "done",
                "risk_level": "green",
                "risk_level_rationale": "No environmental threats detected.",
                "start_date": "2025-01-05",
                "end_date": "2025-03-01"
            },
            {
                "deliverable_id": deliverable_1_2_id,
                "title": "Planting Phase",
                "status": "ongoing",
                "risk_level": "yellow",
                "risk_level_rationale": "Tide schedule interference.",
                "start_date": "2025-03-15",
                "end_date": "2025-08-30"
            }
        ],
        "budgets": [
            {
                "id": generate_uuid(),
                "deliverable_id": deliverable_1_1_id,
                "wage": 1000.0,
                "materials": 1500.0,
                "tools_equipment": 500.0,
                "services": 800.0,
                "misc": 100.0
            },
            {
                "id": generate_uuid(),
                "deliverable_id": deliverable_1_2_id,
                "wage": 1200.0,
                "materials": 2000.0,
                "tools_equipment": 700.0,
                "services": 1000.0,
                "misc": 150.0
            }
        ],
        "expenses": [
            {
                "expense_id": generate_uuid(),
                "associated_deliverable_id": deliverable_1_1_id,
                "seller_name": "EcoTools Ltd.",
                "seller_address": "101 Earth Ave",
                "seller_phone_number": "0811111111",
                "buyer_name": "Mangrove Org",
                "buyer_address": "900 Forest Blvd",
                "transaction_date": "2025-01-20",
                "total_payment_amount": 1800.0,
                "expense_description": "Survey equipment rental",
                "status": "approved"
            },
            {
                "expense_id": generate_uuid(),
                "associated_deliverable_id": deliverable_1_1_id,
                "seller_name": "Green Supplies Co.",
                "seller_address": "202 Eco Rd",
                "seller_phone_number": "0822222222",
                "buyer_name": "Mangrove Org",
                "buyer_address": "900 Forest Blvd",
                "transaction_date": "2025-02-10",
                "total_payment_amount": 1200.0,
                "expense_description": "Protective gear and safety kits",
                "status": "approved"
            },
            {
                "expense_id": generate_uuid(),
                "associated_deliverable_id": deliverable_1_1_id,
                "seller_name": "FieldTech Services",
                "seller_address": "303 Survey Lane",
                "seller_phone_number": "0833333333",
                "buyer_name": "Mangrove Org",
                "buyer_address": "900 Forest Blvd",
                "transaction_date": "2025-02-25",
                "total_payment_amount": 2000.0,
                "expense_description": "Field technician support",
                "status": "approved"
            }

        ]
    })

    # Project 2
    project_2_id = generate_uuid()
    deliverable_2_1_id = generate_uuid()
    deliverable_2_2_id = generate_uuid()
    mock_data.append({
        "project": {
            "project_id": project_2_id,
            "title": "Urban Tree Planting",
            "status": "started",
            "detail_information": "Planting trees across the city for shade and air quality.",
            "start_date": "2025-02-01",
            "end_date": "2025-11-30"
        },
        "deliverables": [
            {
                "deliverable_id": deliverable_2_1_id,
                "title": "Community Engagement",
                "status": "done",
                "risk_level": "green",
                "risk_level_rationale": "High public interest and participation.",
                "start_date": "2025-02-05",
                "end_date": "2025-04-01"
            }
        ],
        "budgets": [
            {
                "id": generate_uuid(),
                "deliverable_id": deliverable_2_1_id,
                "wage": 1500.0,
                "materials": 1000.0,
                "tools_equipment": 300.0,
                "services": 500.0,
                "misc": 50.0
            }
        ],
        "expenses": []
    })

    # Project 3
    project_3_id = generate_uuid()
    deliverable_3_1_id = generate_uuid()
    deliverable_3_2_id = generate_uuid()
    mock_data.append({
        "project": {
            "project_id": project_3_id,
            "title": "River Clean-up Campaign",
            "status": "started",
            "detail_information": "Mobilizing volunteers to clean plastic from major rivers.",
            "start_date": "2025-03-01",
            "end_date": "2025-09-30"
        },
        "deliverables": [
            {
                "deliverable_id": deliverable_3_1_id,
                "title": "Equipment Procurement",
                "status": "done",
                "risk_level": "green",
                "risk_level_rationale": "Procurement completed on time.",
                "start_date": "2025-03-05",
                "end_date": "2025-04-01"
            },
            {
                "deliverable_id": deliverable_3_2_id,
                "title": "Volunteer Training",
                "status": "ongoing",
                "risk_level": "yellow",
                "risk_level_rationale": "Training attendance dropped by 10%.",
                "start_date": "2025-04-10",
                "end_date": "2025-06-30"
            }
        ],
        "budgets": [],
        "expenses": []
    })

    # === INSERT DATA INTO DB ===
    for item in mock_data:
        project = item["project"]
        cursor.execute('''
            INSERT INTO Project (project_id, title, status, detail_information, start_date, end_date)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (
            project["project_id"], project["title"], project["status"],
            project["detail_information"], project["start_date"], project["end_date"]
        ))

        for d in item.get("deliverables", []):
            cursor.execute('''
                INSERT INTO Deliverable (deliverable_id, project_id, title, status, risk_level,
                                         risk_level_rationale, start_date, end_date)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                d["deliverable_id"], project["project_id"], d["title"], d["status"],
                d["risk_level"], d["risk_level_rationale"], d["start_date"], d["end_date"]
            ))

        for b in item.get("budgets", []):
            cursor.execute('''
                INSERT INTO Deliverable_Budget (id, deliverable_id, wage, materials, tools_equipment, services, misc)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (
                b["id"], b["deliverable_id"], b["wage"], b["materials"],
                b["tools_equipment"], b["services"], b["misc"]
            ))

        for e in item.get("expenses", []):
            cursor.execute('''
                INSERT INTO Expense (expense_id, associated_deliverable_id, seller_name, seller_address, seller_phone_number,
                                     buyer_name, buyer_address, transaction_date, total_payment_amount, expense_description, status)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                e["expense_id"], e["associated_deliverable_id"], e["seller_name"], e["seller_address"],
                e["seller_phone_number"], e["buyer_name"], e["buyer_address"],
                e["transaction_date"], e["total_payment_amount"], e["expense_description"], e["status"]
            ))

    conn.commit()
    conn.close()
    print("✅ Multiple projects inserted with UUIDs.")

if __name__ == '__main__':
    insert_mock_data()