import sqlite3
import os
import random
from datetime import datetime, timedelta
import requests
import ollama

# ------------------------------------------------------------
# PATHS
# ------------------------------------------------------------
DB_PATH = r"c:\Temp\projectTracker\DB\projects.db"
HTML_PATH = r"c:\Temp\projectTracker\projects.html"

os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)

# ------------------------------------------------------------
# OLLAMA HEALTH CHECK (CORRECTED)
# ------------------------------------------------------------
def check_ollama_health():
    print("\n🔍 Checking Ollama server health...")

    # 1. Check if server responds
    try:
        r = requests.get("http://localhost:11434/api/tags", timeout=3)
        if r.status_code == 200:
            print("✅ Ollama server is responding.")
        else:
            print(f"⚠️ Ollama responded with HTTP {r.status_code}")
            return False
    except Exception as e:
        print("❌ Ollama server is NOT responding.")
        print(f"   Error details: {e}")
        return False

    # 2. Check installed models
    try:
        tags = r.json()
        model_names = [m["name"] for m in tags.get("models", [])]
        print(f"📦 Installed models: {model_names}")

        # Accept mistral:latest or any mistral variant
        if not any(m.startswith("mistral") for m in model_names):
            print("❌ No mistral model found.")
            print("   Run:  ollama pull mistral")
            return False

        print("✅ Mistral model detected.")
    except Exception as e:
        print("⚠️ Could not parse model list:", e)
        return False

    return True

# ------------------------------------------------------------
# AI DESCRIPTION GENERATOR (CORRECTED)
# ------------------------------------------------------------
def generate_description(project_number: int) -> str:
    print(f"\n🧠 Generating description for Project {project_number}...")

    if not check_ollama_health():
        print("❌ Skipping AI generation. Using fallback description.")
        return f"[AI ERROR] Could not generate description for Project {project_number} because Ollama is not responding."

    prompt = f"""
Generate a unique, detailed, technology-focused project description for a project manager.
The description must be 3–5 sentences and must be unique for Project {project_number}.
It should reference modern engineering domains such as cloud, AI, DevOps, security,
distributed systems, automation, or data engineering.

Format: A single paragraph, no bullet points.
"""

    try:
        response = ollama.generate(model="mistral:latest", prompt=prompt)
        text = response.get("response", "").strip()

        if not text:
            raise ValueError("Empty response from model.")

        print("✅ AI description generated successfully.")
        return text

    except Exception as e:
        print(f"❌ AI generation failed for Project {project_number}: {e}")
        return f"[AI ERROR] Description generation failed: {e}"

# ------------------------------------------------------------
# CREATE DATABASE + TABLE
# ------------------------------------------------------------
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS projects (
    project_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT NOT NULL,
    priority TEXT NOT NULL,
    start_date TEXT NOT NULL,
    end_date TEXT NOT NULL,
    manager TEXT NOT NULL,
    budget REAL NOT NULL,
    percent_complete INTEGER NOT NULL,
    risk_level TEXT NOT NULL,
    last_updated TEXT NOT NULL
)
""")

conn.commit()

# ------------------------------------------------------------
# SAMPLE DATA POOLS
# ------------------------------------------------------------
statuses = ["Not Started", "In Progress", "On Hold", "Completed"]
priorities = ["Low", "Medium", "High", "Critical"]
managers = ["Alice", "Bob", "Carlos", "Diana", "Evelyn", "Frank", "Grace"]
risks = ["Low", "Medium", "High", "Critical"]

# ------------------------------------------------------------
# GENERATE 100 PROJECTS
# ------------------------------------------------------------
def random_date(start_days_ago=400):
    days = random.randint(1, start_days_ago)
    return datetime.now() - timedelta(days=days)

cur.execute("DELETE FROM projects")  # reset

for i in range(1, 101):
    start = random_date()
    end = start + timedelta(days=random.randint(30, 300))

    name = f"Project {i}"
    description = generate_description(i)
    status = random.choice(statuses)
    priority = random.choice(priorities)
    manager = random.choice(managers)
    budget = round(random.uniform(5000, 250000), 2)
    percent = random.randint(0, 100)
    risk = random.choice(risks)
    updated = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    cur.execute("""
        INSERT INTO projects
        (name, description, status, priority, start_date, end_date, manager, budget, percent_complete, risk_level, last_updated)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        name,
        description,
        status,
        priority,
        start.strftime("%Y-%m-%d"),
        end.strftime("%Y-%m-%d"),
        manager,
        budget,
        percent,
        risk,
        updated
    ))

conn.commit()

# ------------------------------------------------------------
# LOAD PROJECTS FOR HTML OUTPUT
# ------------------------------------------------------------
cur.execute("SELECT * FROM projects ORDER BY project_id")
rows = cur.fetchall()
conn.close()

# ------------------------------------------------------------
# BUILD HTML REPORT
# ------------------------------------------------------------
html = f"""
<!DOCTYPE html>
<html>
<head>
<title>Project Tracker Dashboard</title>
<style>
    body {{
        font-family: Arial, sans-serif;
        background: #1e1e1e;
        color: #e0e0e0;
        padding: 20px;
    }}
    h1 {{
        color: #4fc3f7;
    }}
    table {{
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
    }}
    th {{
        background: #333;
        padding: 10px;
        border-bottom: 2px solid #555;
    }}
    td {{
        padding: 8px;
        border-bottom: 1px solid #444;
        vertical-align: top;
    }}
    tr:hover {{
        background: #2a2a2a;
    }}
</style>
</head>
<body>

<h1>Project Tracker Dashboard</h1>
<p>Generated: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}</p>

<table>
<tr>
    <th>ID</th>
    <th>Name</th>
    <th>Description</th>
    <th>Status</th>
    <th>Priority</th>
    <th>Start Date</th>
    <th>End Date</th>
    <th>Manager</th>
    <th>Budget</th>
    <th>% Complete</th>
    <th>Risk</th>
    <th>Last Updated</th>
</tr>
"""

for r in rows:
    html += f"""
<tr>
    <td>{r[0]}</td>
    <td>{r[1]}</td>
    <td>{r[2]}</td>
    <td>{r[3]}</td>
    <td>{r[4]}</td>
    <td>{r[5]}</td>
    <td>{r[6]}</td>
    <td>{r[7]}</td>
    <td>${r[8]:,.2f}</td>
    <td>{r[9]}%</td>
    <td>{r[10]}</td>
    <td>{r[11]}</td>
</tr>
"""

html += """
</table>
</body>
</html>
"""

# ------------------------------------------------------------
# WRITE HTML FILE
# ------------------------------------------------------------
os.makedirs(os.path.dirname(HTML_PATH), exist_ok=True)

with open(HTML_PATH, "w", encoding="utf-8") as f:
    f.write(html)

print(f"\n📁 Database created: {DB_PATH}")
print(f"📄 HTML report created: {HTML_PATH}")