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()