import sqlite3
import streamlit as st

# --- DATABASE FUNCTIONS ---
# Initialize the SQLite database
def init_db():
    conn = sqlite3.connect("project_management.db")  # Creates a database file
    c = conn.cursor()
    # Create a table for projects
    c.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            start_date TEXT,
            end_date TEXT,
            priority TEXT,
            budget REAL
        )
    ''')
    conn.commit()
    conn.close()

# Add a new project to the database
def add_project(name, description, start_date, end_date, priority, budget):
    conn = sqlite3.connect("project_management.db")
    c = conn.cursor()
    c.execute("INSERT INTO projects (name, description, start_date, end_date, priority, budget) VALUES (?, ?, ?, ?, ?, ?)", 
              (name, description, start_date, end_date, priority, budget))
    conn.commit()
    conn.close()

# Retrieve all projects from the database
def view_projects():
    conn = sqlite3.connect("project_management.db")
    c = conn.cursor()
    c.execute("SELECT * FROM projects")
    rows = c.fetchall()
    conn.close()
    return rows

# Delete a project by ID
def delete_project(project_id):
    conn = sqlite3.connect("project_management.db")
    c = conn.cursor()
    c.execute("DELETE FROM projects WHERE id=?", (project_id,))
    conn.commit()
    conn.close()

# --- STREAMLIT FRONT-END ---
def main():
    st.title("Software Management Tool")
    st.write("*Developed by Mansoor Sarookh, CS Student at GPGC Swabi*")

    # Initialize the database
    init_db()

    # Sidebar menu
    menu = ["Add New Project", "View All Projects", "Delete Project"]
    choice = st.sidebar.selectbox("Menu", menu)

    # Add New Project
    if choice == "Add New Project":
        st.subheader("Add New Project")
        name = st.text_input("Project Name")
        description = st.text_area("Project Description")
        start_date = st.date_input("Start Date")
        end_date = st.date_input("End Date")
        priority = st.selectbox("Priority", ["Low", "Medium", "High"])
        budget = st.number_input("Budget (in USD)", min_value=0.0, step=100.0)

        if st.button("Add Project"):
            if name and description:
                add_project(name, description, str(start_date), str(end_date), priority, budget)
                st.success(f"Project '{name}' added successfully!")
            else:
                st.error("Please fill in all required fields.")

    # View All Projects
    elif choice == "View All Projects":
        st.subheader("All Projects")
        data = view_projects()
        if data:
            for row in data:
                st.write(f"*ID:* {row[0]} | *Name:* {row[1]} | *Description:* {row[2]}")
                st.write(f"*Start Date:* {row[3]} | *End Date:* {row[4]} | *Priority:* {row[5]} | *Budget:* ${row[6]}")
                st.write("---")
        else:
            st.info("No projects available. Add some projects first.")

    # Delete a Project
    elif choice == "Delete Project":
        st.subheader("Delete a Project")
        project_id = st.number_input("Enter Project ID to Delete", min_value=1, step=1)
        if st.button("Delete"):
            delete_project(project_id)
            st.success(f"Project with ID {project_id} deleted successfully!")

if _name_ == "_main_":
    main()