import streamlit as st
import sqlite3
from datetime import datetime
from reportlab.lib.pagesizes import A4
from reportlab.pdfgen import canvas

# Initialize Database
conn = sqlite3.connect("work_permits.db")
cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS permits (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        permit_number TEXT UNIQUE,
        permit_type TEXT,
        job_description TEXT,
        location TEXT,
        start_date TEXT,
        end_date TEXT,
        equipment TEXT,
        worker_name TEXT,
        supervisor_name TEXT,
        contact_number TEXT,
        safety_checks TEXT,
        generated_on TEXT
    )
""")
conn.commit()

# Function to generate Permit Number
def generate_permit_number():
    today = datetime.now().strftime("%Y%m%d")
    cursor.execute("SELECT COUNT(*) FROM permits WHERE generated_on LIKE ?", (f"%{today}%",))
    count = cursor.fetchone()[0] + 1
    return f"WP-{today}-{str(count).zfill(3)}"

# Function to save permit to Database
def save_to_db(data):
    cursor.execute("""
        INSERT INTO permits (permit_number, permit_type, job_description, location, start_date, end_date, 
                             equipment, worker_name, supervisor_name, contact_number, safety_checks, generated_on) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, data)
    conn.commit()

# Function to create PDF
def generate_pdf(data):
    pdf_filename = f"{data[0]}.pdf"
    c = canvas.Canvas(pdf_filename, pagesize=A4)
    c.setFont("Helvetica", 12)

    y = 800
    for key, value in zip(["Permit Number", "Permit Type", "Job Description", "Location", "Start Date", 
                           "End Date", "Equipment", "Worker Name", "Supervisor Name", "Contact Number", 
                           "Safety Checks", "Generated On"], data):
        c.drawString(50, y, f"{key}: {value}")
        y -= 20

    c.save()
    return pdf_filename

# Streamlit UI
st.title("🏗️ Work Permit Management System")

# Select Permit Type
permit_types = [
    "Hot Work (Red)", "Cold Work (Blue)", "Electrical Work (Orange/Yellow)",
    "Confined Space Entry (Green)", "Working at Heights (Yellow)",
    "Lockout/Tagout (White/Red)", "Excavation/Trenching (Brown)",
    "Radiation Work (Purple)", "Chemical Handling (Orange/Yellow)",
    "Marine Work (Blue/Green)"
]
permit_type = st.selectbox("Select Work Permit Type:", permit_types)

# Work Details
job_description = st.text_area("Job Description")
location = st.text_input("Work Location")
start_date = st.date_input("Start Date")
end_date = st.date_input("End Date")
equipment = st.text_input("Equipment/Machinery Used")

# Personnel Information
worker_name = st.text_input("Worker Name")
supervisor_name = st.text_input("Supervisor Name")
contact_number = st.text_input("Emergency Contact Number")

# Safety Checklist
st.subheader("✅ Safety Checklist")
safety_checks = []
if st.checkbox("PPE Worn"):
    safety_checks.append("PPE Worn")
if st.checkbox("Hazard Inspection Done"):
    safety_checks.append("Hazard Inspection Done")
if st.checkbox("Fire Extinguisher Available"):
    safety_checks.append("Fire Extinguisher Available")
if st.checkbox("Lockout/Tagout Applied"):
    safety_checks.append("Lockout/Tagout Applied")
if st.checkbox("Atmospheric Testing Completed"):
    safety_checks.append("Atmospheric Testing Completed")

# Generate Work Permit
if st.button("Generate Work Permit"):
    permit_number = generate_permit_number()
    generated_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    # Save to database
    permit_data = (permit_number, permit_type, job_description, location, str(start_date), str(end_date),
                   equipment, worker_name, supervisor_name, contact_number, ", ".join(safety_checks), generated_on)
    save_to_db(permit_data)

    # Generate PDF
    pdf_file = generate_pdf(permit_data)

    # Provide Download Link
    with open(pdf_file, "rb") as file:
        st.download_button(label="📥 Download Work Permit", data=file, file_name=f"{permit_number}.pdf", mime="application/pdf")

    st.success(f"✅ Work Permit {permit_number} generated successfully!")

# View Saved Permits
st.subheader("📋 View Saved Work Permits")
search_type = st.radio("Search by:", ("Date", "Permit Number"))
if search_type == "Date":
    search_date = st.date_input("Select Date")
    cursor.execute("SELECT * FROM permits WHERE generated_on LIKE ?", (f"%{search_date}%",))
elif search_type == "Permit Number":
    permit_no = st.text_input("Enter Permit Number")
    cursor.execute("SELECT * FROM permits WHERE permit_number = ?", (permit_no,))
    
results = cursor.fetchall()
if results:
    for permit in results:
        st.write(f"📄 **Permit Number:** {permit[1]}")
        st.write(f"📌 **Type:** {permit[2]}")
        st.write(f"📍 **Location:** {permit[4]}")
        st.write(f"📅 **Date:** {permit[11]}")
        st.write("---")
else:
    st.warning("No permits found.")

st.success("Work Permit System Ready ✅")