from flask import Flask, render_template, request, redirect, session, flash, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime
from sqlalchemy import func
import openai
import os
import re
from werkzeug.security import generate_password_hash

from functools import wraps
from docx import Document
from docx.shared import Pt, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from bs4 import BeautifulSoup
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
from os import path
from flask_login import LoginManager, login_user, logout_user, login_required, current_user, UserMixin
from pptx import Presentation
from pptx.util import Inches, Pt
from bs4 import BeautifulSoup
from apscheduler.schedulers.background import BackgroundScheduler
from flask import jsonify



app = Flask(__name__)
app.secret_key = "super_secret_key"


# SQLite DB
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['REPORT_FOLDER'] = 'static/reports'
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
os.makedirs(app.config['REPORT_FOLDER'], exist_ok=True)

login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'  # redirect here if not logged in
login_manager.login_message_category = "warning"

db = SQLAlchemy(app)
migrate = Migrate(app, db)  # <-- Flask-Migrate






# --- Models ---


# --- Roles ---

# in your models (app.py)
role_permissions = db.Table(
    'role_permissions',
    db.Column('role_id', db.Integer, db.ForeignKey('role.id'), primary_key=True),
    db.Column('permission_id', db.Integer, db.ForeignKey('permission.id'), primary_key=True)
)

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    description = db.Column(db.Text)
    users = db.relationship('User', backref='role', lazy='dynamic')
    
      # Add this relationship
    permissions = db.relationship(
        'Permission',
        secondary=role_permissions,
        backref=db.backref('roles', lazy='dynamic'),
        lazy='dynamic'
    )

# --- Tenants and Plans ---
class Plan(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)
    max_users = db.Column(db.Integer)
    max_departments = db.Column(db.Integer)
    max_reports = db.Column(db.Integer)
    tenants = db.relationship('Tenant', backref='plan', lazy='dynamic')


class Tenant(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    email = db.Column(db.String(200))
    status = db.Column(db.String(20), default="active")
    plan_id = db.Column(db.Integer, db.ForeignKey('plan.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    users = db.relationship('User', backref='tenant', lazy='dynamic')
    departments = db.relationship('Department', backref='tenant', lazy='dynamic')
    reports = db.relationship('Report', backref='tenant', lazy='dynamic')
    processes = db.relationship('Process', backref='tenant', lazy='dynamic')
    escalations = db.relationship('Escalation', backref='tenant', lazy='dynamic')
    modules = db.relationship('TenantModule', backref='tenant', lazy='dynamic')


# --- Users ---
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    department_id = db.Column(db.Integer, db.ForeignKey('department.id'))
    name = db.Column(db.String(100))
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(500))
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'))

    # Explicit foreign key for department
    department = db.relationship('Department', foreign_keys=[department_id], backref='users', lazy='joined')

    # Processes
    assigned_processes = db.relationship(
        'Process',
        foreign_keys='Process.assigned_user_id',
        backref='assigned_user',
        lazy='dynamic'
    )
    created_processes = db.relationship(
        'Process',
        foreign_keys='Process.created_by_id',
        backref='creator',
        lazy='dynamic'
    )

    # Escalations
    escalations = db.relationship(
        'Escalation',
        foreign_keys='Escalation.escalated_to_user_id',
        backref='escalated_user',
        lazy='dynamic'
    )

class Department(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    name = db.Column(db.String(200), nullable=False)
    parent_id = db.Column(db.Integer, db.ForeignKey('department.id'), nullable=True)
    manager_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Self-referential hierarchy
    children = db.relationship(
        'Department',
        backref=db.backref('parent', remote_side=[id]),
        lazy='dynamic'
    )

    # Explicit foreign key for manager
    manager = db.relationship('User', foreign_keys=[manager_id], backref='managed_departments', lazy='joined')


# --- Reports ---
class Report(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    department_id = db.Column(db.Integer, db.ForeignKey('department.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    type = db.Column(db.String(50))  # 'detailed', 'presentation', 'summary'
    title = db.Column(db.String(200))
    summary = db.Column(db.Text)
    data = db.Column(db.JSON)  # structured data for comparison
    file_path = db.Column(db.String(500))  # path to generated file
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # relationships
    department = db.relationship('Department', backref='reports', lazy='joined')
    user = db.relationship('User', backref='reports', lazy='joined')


# --- Processes ---
class Process(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    department_id = db.Column(db.Integer, db.ForeignKey('department.id'), nullable=False)
    title = db.Column(db.String(200))
    description = db.Column(db.Text)
    assigned_user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    created_by_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    status = db.Column(db.String(50), default="pending")  # pending/in_progress/completed
    due_date = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    # relationships
    department = db.relationship('Department', backref='processes', lazy='joined')


# --- Escalations ---
class Escalation(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    process_id = db.Column(db.Integer, db.ForeignKey('process.id'), nullable=False)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    escalated_to_user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    department_id = db.Column(db.Integer, db.ForeignKey('department.id'))
    message = db.Column(db.Text)
    level = db.Column(db.Integer, default=1)
    status = db.Column(db.String(50), default="pending")  # pending / acknowledged / resolved
    triggered_at = db.Column(db.DateTime, default=datetime.utcnow)
    resolved_at = db.Column(db.DateTime, nullable=True)
    # relationships
    process = db.relationship('Process', backref='escalations', lazy='joined')
    department = db.relationship('Department', backref='escalations', lazy='joined')




# --- Permissions & Modules ---
class Permission(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))


class TenantModule(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'))
    module_name = db.Column(db.String(100))
 
def super_admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            flash("Please log in")
            return redirect(url_for('login'))
        user = User.query.get(session['user_id'])
        if not user.role or user.role.name != 'Super Admin':
            flash("Access denied: Super Admins only")
            return redirect(url_for('dashboard'))
        return f(*args, **kwargs)
    return decorated_function
    
   
def tenant_admin_required(f):
    """
    Ensures the current user has the 'manage_users' or 'manage_departments' permission,
    which effectively means they are a tenant admin.
    """
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated:
            flash("Please log in", "warning")
            return redirect(url_for('login'))

        # Check if user has at least one admin-level permission
        admin_permissions = ['manage_users', 'manage_departments']
        if not current_user.role:
            flash("Access denied: No role assigned", "danger")
            return redirect(url_for('dashboard'))

        if not any(p.name in admin_permissions for p in current_user.role.permissions):
            flash("Access denied: Admins only", "danger")
            return redirect(url_for('dashboard'))

        return f(*args, **kwargs)
    return decorated_function

def permission_required(permission_name):
    """
    Decorator to restrict route access based on Role permissions.
    Usage: @permission_required("manage_users")
    """
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if not current_user.is_authenticated:
                flash("Please log in", "warning")
                return redirect(url_for("login"))

            # Ensure user has a role
            if not current_user.role:
                flash("Access denied: No role assigned", "danger")
                return redirect(url_for("dashboard"))

            # Check if the role has the required permission
            if not any(p.name == permission_name for p in current_user.role.permissions):
                flash("Access denied: You do not have permission", "danger")
                return redirect(url_for("dashboard"))

            return f(*args, **kwargs)
        return decorated_function
    return decorator
    
def module_enabled(module_name):
    def decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            tenant_id = current_user.tenant_id
            if not TenantModule.query.filter_by(tenant_id=tenant_id, module_name=module_name).first():
                flash(f"{module_name} is not enabled for your company", "warning")
                return redirect(url_for("dashboard"))
            return f(*args, **kwargs)
        return wrapper
    return decorator

with app.app_context():
    db.create_all()
    


with app.app_context():

    # -----------------------------
    # 1️⃣ Create System Tenant
    # -----------------------------
    tenant = Tenant.query.filter_by(name='System Tenant').first()
    if not tenant:
        tenant = Tenant(name='System Tenant', email='system@tenant.com')
        db.session.add(tenant)
        db.session.commit()
        print("✅ System Tenant created.")

    # -----------------------------
    # 2️⃣ Seed Permissions
    # -----------------------------
    perm_names = [
        "create_report",
        "delete_report",
        "manage_departments",
        "manage_users",
        "view_reports"
    ]
    for name in perm_names:
        if not Permission.query.filter_by(name=name).first():
            db.session.add(Permission(name=name))
    db.session.commit()
    print("✅ Permissions seeded.")

    # -----------------------------
    # 3️⃣ Seed Roles and assign Permissions
    # -----------------------------
    roles = {
        "Super Admin": perm_names,      # all permissions
        "admin": perm_names,            # all permissions
        "employee": ["create_report", "view_reports"],  # limited
    }

    for role_name, perms in roles.items():
        role = Role.query.filter_by(name=role_name).first()
        if not role:
            role = Role(name=role_name, description=f"{role_name} role")
            db.session.add(role)
            db.session.flush()  # ensures role.id is available

        # Assign permissions
        role.permissions = []
        for perm_name in perms:
            perm = Permission.query.filter_by(name=perm_name).first()
            if perm and perm not in role.permissions:
                role.permissions.append(perm)

    db.session.commit()
    print("✅ Roles seeded and permissions assigned.")

    # -----------------------------
    # 4️⃣ Create Super Admin User
    # -----------------------------
    super_admin = User.query.filter_by(email='superadmin@system.com').first()
    super_admin_role = Role.query.filter_by(name='Super Admin').first()

    if not super_admin:
        hashed_password = generate_password_hash('SuperSecretPassword123', method='pbkdf2:sha256')
        super_admin = User(
            name='Super Admin',
            email='superadmin@system.com',
            password=hashed_password,
            role_id=super_admin_role.id,
            tenant_id=tenant.id,
            department_id=None  # optional
        )
        db.session.add(super_admin)
        db.session.commit()
        print("✅ Super Admin user created.")
        
        
#-----------------------------------------------------------
#                     Helper functions
#-----------------------------------------------------------
def check_process_escalations():
    overdue_processes = Process.query.filter(
        Process.status != "completed",
        Process.due_date < datetime.utcnow()
    ).all()

    for process in overdue_processes:
        escalate_process(process)

# Scheduler
scheduler = BackgroundScheduler()
scheduler.add_job(
    check_process_escalations,
    'interval',
    minutes=5
)
if not scheduler.running:
    scheduler.start()
    

def generate_ai_report(title: str, data: str, report_type: str = "detailed") -> str:
    """
    Generate an AI report in HTML format based on the report type.
    
    report_type: "detailed", "summary", or "presentation"
    """

    # -----------------------------
    # Define prompts for each type
    # -----------------------------
    if report_type == "detailed":
        prompt = f"""
        You are a professional business report writer.
        Create a detailed report titled '{title}'.
        Include sections: Introduction, Analysis, Insights, Conclusion.
        Use the following data to write paragraphs, tables, and reference graphs:
        {data}
        Return valid HTML using <h1>, <h2>, <p> tags. Do NOT include markdown or code blocks.
        """
    elif report_type == "summary":
        prompt = f"""
        You are a professional business report writer.
        Create a concise summary report titled '{title}'.
        Focus on key insights and major conclusions only.
        Use the following data:
        {data}
        Return valid HTML using <h1>, <h2>, <p> tags. Do NOT include detailed tables or code blocks.
        """
    elif report_type == "presentation":
        prompt = f"""
        You are a professional business report writer.
        Create a presentation-style report titled '{title}'.
        Use short paragraphs, clear headings, and bullet points for key ideas.
        Include tables and graphs where needed.
        Base your content on the following data:
        {data}
        Return valid HTML using <h1>, <h2>, <p> tags. Do NOT include markdown or code blocks.
        """
    else:
        raise ValueError(f"Invalid report_type: {report_type}")

    # -----------------------------
    # Call OpenAI
    # -----------------------------
    try:
        response = openai.chat.completions.create(
            model="gpt-4o-mini",
            messages=[{"role": "user", "content": prompt}],
            temperature=0.7
        )
        return response.choices[0].message.content.strip()
    except Exception as e:
        # fallback if API fails
        return f"<h1>Error generating report</h1><p>{str(e)}</p>"

def html_to_pptx(html_content, title="Presentation Report", author="Author", save_path=None):
    """
    Converts AI-generated HTML into a PowerPoint presentation.
    - h1 becomes slide title
    - h2 becomes slide subtitle / new slide
    - p becomes bullet points
    """

    prs = Presentation()

    soup = BeautifulSoup(html_content, "html.parser")

    slides = []

    current_slide = {"title": "", "points": []}

    for element in soup.find_all(["h1", "h2", "p"]):

        if element.name == "h1":
            # start presentation title slide
            slide_layout = prs.slide_layouts[0]
            slide = prs.slides.add_slide(slide_layout)

            slide.shapes.title.text = element.get_text(strip=True)
            slide.placeholders[1].text = f"Prepared by {author}"

        elif element.name == "h2":
            # start new slide
            if current_slide["title"]:
                slides.append(current_slide)

            current_slide = {
                "title": element.get_text(strip=True),
                "points": []
            }

        elif element.name == "p":
            current_slide["points"].append(element.get_text(strip=True))

    # append last slide
    if current_slide["title"]:
        slides.append(current_slide)

    # ----------------------------
    # Build slides
    # ----------------------------
    for s in slides:

        slide_layout = prs.slide_layouts[1]
        slide = prs.slides.add_slide(slide_layout)

        slide.shapes.title.text = s["title"]

        body = slide.shapes.placeholders[1].text_frame
        body.clear()

        for point in s["points"]:
            p = body.add_paragraph()
            p.text = point
            p.level = 0
            p.font.size = Pt(18)

    # ----------------------------
    # Save file
    # ----------------------------
    if save_path:
        os.makedirs(os.path.dirname(save_path), exist_ok=True)
        prs.save(save_path)

    return prs

def escalate_process(process):

    department = Department.query.get(process.department_id)

    if not department:
        return

    manager = User.query.join(Role).filter(
        User.department_id == department.id,
        Role.name == "manager"
    ).first()

    if not manager:
        return

    existing = Escalation.query.filter_by(
        process_id=process.id,
        status="pending"
    ).first()

    if existing:
        return

    escalation = Escalation(
        tenant_id=process.tenant_id,
        department_id=process.department_id,
        process_id=process.id,
        escalated_to_user_id=manager.id,
        level=1,
        status="pending",
        triggered_at=datetime.utcnow()
    )

    db.session.add(escalation)
    db.session.commit()
    
    
# --- HELPER: Get all sub-department IDs ---
def get_department_hierarchy_ids(department_id):
    ids = [department_id]
    sub_departments = Department.query.filter_by(parent_id=department_id).all()
    for dept in sub_departments:
        ids.extend(get_department_hierarchy_ids(dept.id))
    return ids



def get_user_reports(user_id: int):
    """
    Returns all reports created by a user.
    Replace with your actual ORM logic if needed.
    """
    return Report.query.filter_by(user_id=user_id).all()
    


def get_departments_for_user(user_id):

    user = User.query.get(user_id)

    if not user:
        return []

    return Department.query.filter_by(
        tenant_id=user.tenant_id
    ).all()
    
    
def save_report(user_id, department_id, title, file_path, report_type):

    user = User.query.get(user_id)

    new_report = Report(
        tenant_id=user.tenant_id,
        department_id=department_id,
        user_id=user_id,
        type=report_type,
        title=title,
        file_path=file_path,
        created_at=datetime.utcnow()
    )

    db.session.add(new_report)
    db.session.commit()
    
    
def add_toc(document):
    """
    Inserts a Word Table of Contents field.
    Word will auto-populate the TOC when opened and updated.
    """
    paragraph = document.add_paragraph()
    run = paragraph.add_run()

    fldChar1 = OxmlElement('w:fldChar')  # start
    fldChar1.set(qn('w:fldCharType'), 'begin')

    instrText = OxmlElement('w:instrText')
    instrText.set(qn('xml:space'), 'preserve')
    instrText.text = 'TOC \\o "1-3" \\h \\z \\u'  # show levels 1-3, hyperlinks

    fldChar2 = OxmlElement('w:fldChar')  # separate
    fldChar2.set(qn('w:fldCharType'), 'separate')

    fldChar3 = OxmlElement('w:fldChar')  # end
    fldChar3.set(qn('w:fldCharType'), 'end')

    r_element = run._r
    r_element.append(fldChar1)
    r_element.append(instrText)
    r_element.append(fldChar2)
    r_element.append(fldChar3)



def html_to_docx(html_content, title="Report", company_name="Company",
                 author_name="Author", save_path=None):
    """
    Converts OpenAI HTML content into a DOCX file with:
    - Cover page
    - Table of Contents
    - Colored Headings (H1: Blue, H2: Dark Red)
    - Styled Paragraphs (size 12)
    Saves file if save_path is provided.
    Returns the Document object.
    """
    document = Document()

    # -----------------------------
    # Cover Page
    # -----------------------------
    cover_title = document.add_heading(title, 0)
    cover_title.alignment = WD_ALIGN_PARAGRAPH.CENTER

    company_para = document.add_paragraph(company_name)
    company_para.alignment = WD_ALIGN_PARAGRAPH.CENTER

    author_para = document.add_paragraph(f"Prepared by: {author_name}")
    author_para.alignment = WD_ALIGN_PARAGRAPH.CENTER

    date_para = document.add_paragraph(f"Generated on: {datetime.utcnow().strftime('%d %B %Y')}")
    date_para.alignment = WD_ALIGN_PARAGRAPH.CENTER

    document.add_page_break()

    # -----------------------------
    # Table of Contents
    # -----------------------------
    document.add_paragraph("Table of Contents").bold = True
    add_toc(document)
    document.add_page_break()

    # -----------------------------
    # Parse HTML and Add Content
    # -----------------------------
    soup = BeautifulSoup(html_content, "html.parser")
    for element in soup.find_all(['h1', 'h2', 'p']):
        if element.name == 'h1':
            heading = document.add_heading(element.get_text(strip=True), level=1)
            heading.alignment = WD_ALIGN_PARAGRAPH.LEFT
            if heading.runs:
                heading.runs[0].font.color.rgb = RGBColor(0, 0, 255)  # Blue
        elif element.name == 'h2':
            heading = document.add_heading(element.get_text(strip=True), level=2)
            heading.alignment = WD_ALIGN_PARAGRAPH.LEFT
            if heading.runs:
                heading.runs[0].font.color.rgb = RGBColor(139, 0, 0)  # Dark Red
        elif element.name == 'p':
            para = document.add_paragraph(element.get_text(strip=True))
            para.alignment = WD_ALIGN_PARAGRAPH.LEFT
            para.style.font.size = Pt(12)

    # -----------------------------
    # Save file if path provided
    # -----------------------------
    if save_path:
        os.makedirs(os.path.dirname(save_path), exist_ok=True)
        document.save(save_path)

    return document
    
    

def report_access_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):

        if not current_user.is_authenticated:
            flash("Please log in")
            return redirect(url_for('login'))

        if current_user.role and current_user.role.name == 'admin':
            flash("Admins cannot manage reports")
            return redirect(url_for('dashboard'))

        return f(*args, **kwargs)

    return decorated_function
    
    

async def html_to_pdf(html_content, output_path):
    """
    Convert HTML string to PDF using headless Chromium.
    """
    browser = await launch(headless=True, args=['--no-sandbox'])
    page = await browser.newPage()
    await page.setContent(html_content, waitUntil='networkidle0')
    await page.pdf({
        'path': output_path,
        'format': 'A4',
        'printBackground': True,
        'margin': {'top': '40px', 'bottom': '40px', 'left': '40px', 'right': '40px'}
    })
    await browser.close()

def user_has_permission(user, permission_name):
    if not user.role:
        return False

    return any(p.name == permission_name for p in user.role.permissions)
    
#---------------------------------------------------
#    get child departments
#--------------------------------------------------
def get_child_departments(department_id):

    visited = set()
    departments = []

    def collect(dept):
        if dept.id in visited:
            return

        visited.add(dept.id)
        departments.append(dept.id)

        for child in dept.children:
            collect(child)

    dept = Department.query.get(department_id)

    if dept:
        collect(dept)

    return departments
    
    
def get_visible_processes(user):

    dept_ids = get_child_departments(user.department_id)

    processes = Process.query.filter(
        Process.department_id.in_(dept_ids)
    ).all()

    return processes

def check_process_escalations():

    overdue_processes = Process.query.filter(
        Process.status != "completed",
        Process.due_date < datetime.utcnow()
    ).all()

    for process in overdue_processes:
        escalate_process(process)


# --- User loader ---
@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

#------------------------------------------------
#                  Routes
#------------------------------------------------

@app.route('/')
def home():
    return render_template("index.html")
    
@app.route('/about')
def about():
    return render_template("about.html")

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        email = request.form.get('email', '').strip()
        password = request.form.get('password', '').strip()

        user = User.query.filter_by(email=email).first()

        if not user or not check_password_hash(user.password, password):
            flash("Invalid email or password", "danger")
            return redirect(url_for('login'))

        # ✅ Log in user with Flask-Login
        login_user(user)

        # ✅ Set session variables
        session['user_id'] = user.id
        session['role'] = user.role.name if user.role else None

        flash(f"Welcome {user.name}!", "success")

        return redirect(url_for('dashboard'))

    return render_template('login.html')
    
    
@app.route('/signup', methods=['GET','POST'])
def signup():
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        password = request.form['password']
        company_name = request.form['company_name']

        # Check if email already exists
        if User.query.filter_by(email=email).first():
            flash('Email already registered!')
            return redirect(url_for('signup'))

        # Check if company name already exists
        if Tenant.query.filter_by(name=company_name).first():
            flash('Company name already exists!')
            return redirect(url_for('signup'))

        # Create tenant (company)
        tenant = Tenant(
            name=company_name,
            created_at=datetime.utcnow()
        )
        db.session.add(tenant)
        db.session.flush()  
        # flush gives us tenant.id before commit
        
        admin_role = Role.query.filter_by(name="admin").first()
        
        # Create admin user under this tenant
        hashed_password = generate_password_hash(password, method='pbkdf2:sha256')
        user = User(
            name=name,
            email=email,
            password=hashed_password,
            role_id=admin_role.id,
            tenant_id=tenant.id
        )

        db.session.add(user)
        db.session.commit()

        flash('Company created successfully! Please log in.')
        return redirect(url_for('login'))

    return render_template("signup.html")

# --- DASHBOARD ---
@app.route('/dashboard')
@login_required
def dashboard():
    user = current_user
    # Initialize counters
    total_users = total_departments = total_reports = total_processes = total_escalations = 0

    # --- SUPER ADMIN ---
    if user.role.name == "Super Admin":
        total_tenants = Tenant.query.count()
        total_users = User.query.count()
        total_departments = Department.query.count()
        return render_template(
            "dashboard.html",
            user=user,
            is_super_admin=True,
            total_tenants=total_tenants,
            total_users=total_users,
            total_departments=total_departments
        )

    # --- TENANT ADMIN ---
    elif user.role.name == 'admin':
        total_users = User.query.filter_by(tenant_id=user.tenant_id).count()
        total_departments = Department.query.filter_by(tenant_id=user.tenant_id).count()
        total_reports = Report.query.filter_by(tenant_id=user.tenant_id).count()
        total_processes = Process.query.filter_by(tenant_id=user.tenant_id).count()
        total_escalations = Escalation.query.filter_by(tenant_id=user.tenant_id).count()
        return render_template(
            "dashboard.html",
            user=user,
            is_admin=True,
            total_users=total_users,
            total_departments=total_departments,
            total_reports=total_reports,
            total_processes=total_processes,
            total_escalations=total_escalations
        )

    # --- MANAGER ---
    elif user.role.name == 'manager':
        dept_ids = get_department_hierarchy_ids(user.department_id)
        total_reports = Report.query.filter(Report.department_id.in_(dept_ids)).count()
        total_processes = Process.query.filter(Process.department_id.in_(dept_ids)).count()
        total_escalations = Escalation.query.filter(Escalation.department_id.in_(dept_ids)).count()
        return render_template(
            "dashboard.html",
            user=user,
            is_manager=True,
            total_reports=total_reports,
            total_processes=total_processes,
            total_escalations=total_escalations
        )

    # --- EMPLOYEE ---
    else:
        total_reports = Report.query.filter_by(user_id=user.id).count()
        total_processes = Process.query.filter_by(assigned_user_id=user.id).count()
        return render_template(
            "dashboard.html",
            user=user,
            is_employee=True,
            total_reports=total_reports,
            total_processes=total_processes
        )


@app.route('/logout')
@login_required
def logout():
    logout_user()
    session.pop('role', None)
    session.pop('user_id', None)
    flash('You have been logged out.')
    return redirect(url_for('login'))

#----------------------------------------------------------------
#           All Related to a user
#----------------------------------------------------------------

# List all users under the tenant
@app.route('/admin/users')
@permission_required("manage_users")
@tenant_admin_required
def list_users():
    admin = User.query.get(session['user_id'])
    users = User.query.filter_by(tenant_id=admin.tenant_id).all()
    return render_template('list_users.html', users=users)



@app.route('/admin/users/create', methods=['GET', 'POST'])
@permission_required("manage_users")
def create_user():
    admin = User.query.get(session['user_id'])
    departments = Department.query.filter_by(tenant_id=admin.tenant_id).all()

    allowed_roles = [
        'Supervisor',
        'Chief Executive Officer',
        'Chief Technical Officer',
        'Chief Finance Officer',
        'Manager',
        'Employee',
        'General Manager'
    ]

    if request.method == 'POST':
        name = request.form.get('name')
        email = request.form.get('email')
        password = request.form.get('password')
        role_name = request.form.get('role')
        department_id = request.form.get('department_id')

        # Validate role string
        if role_name not in allowed_roles:
            flash("Invalid role selected")
            return redirect(url_for('create_user'))

        # Fetch Role object
        role = Role.query.filter_by(name=role_name).first()
        if not role:
            flash("Selected role does not exist in DB")
            return redirect(url_for('create_user'))

        # Check duplicate email inside same tenant
        if User.query.filter_by(email=email, tenant_id=admin.tenant_id).first():
            flash("Email already exists")
            return redirect(url_for('create_user'))

        # Validate department
        if department_id:
            department = Department.query.filter_by(
                id=int(department_id),
                tenant_id=admin.tenant_id
            ).first()
            if not department:
                flash("Invalid department selected")
                return redirect(url_for('create_user'))
            department_id = department.id
        else:
            department_id = None

        hashed_password = generate_password_hash(password, method='pbkdf2:sha256')

        # Create user
        user = User(
            name=name,
            email=email,
            password=hashed_password,
            role=role,  # ✅ assign Role object
            tenant_id=admin.tenant_id,
            department_id=department_id
        )

        db.session.add(user)
        db.session.commit()

        flash("User created successfully")
        return redirect(url_for('list_users'))

    return render_template(
        'create_user.html',
        departments=departments,
        allowed_roles=allowed_roles
    )
    
    
@app.route('/admin/users/<int:user_id>/edit', methods=['GET', 'POST'])
@permission_required("manage_users")
@tenant_admin_required
def edit_user(user_id):
    admin = User.query.get(session['user_id'])

    # Ensure user belongs to same tenant
    user = User.query.filter_by(id=user_id, tenant_id=admin.tenant_id).first_or_404()
    departments = Department.query.filter_by(tenant_id=admin.tenant_id).all()

    allowed_roles = [
        'Supervisor',
        'Chief Executive Officer',
        'Chief Technical Officer',
        'Chief Finance Officer',
        'Manager',
        'Employee',
        'General Manager'
    ]

    if request.method == 'POST':
        user.name = request.form.get('name')
        user.email = request.form.get('email')

        role_name = request.form.get('role')
        if role_name not in allowed_roles:
            flash("Invalid role selected")
            return redirect(url_for('edit_user', user_id=user.id))

        # Fetch Role object
        role = Role.query.filter_by(name=role_name).first()
        if not role:
            flash("Selected role does not exist in DB")
            return redirect(url_for('edit_user', user_id=user.id))

        user.role = role  # ✅ assign Role object

        department_id = request.form.get('department_id')
        if department_id:
            department = Department.query.filter_by(
                id=int(department_id),
                tenant_id=admin.tenant_id
            ).first()
            if not department:
                flash("Invalid department selected")
                return redirect(url_for('edit_user', user_id=user.id))
            user.department_id = department.id
        else:
            user.department_id = None

        db.session.commit()
        flash("User updated successfully")
        return redirect(url_for('list_users'))

    return render_template(
        'edit_user.html',
        user=user,
        departments=departments,
        allowed_roles=allowed_roles
    )

@app.route('/admin/users/<int:user_id>/delete', methods=['POST'])
@permission_required("manage_users")
@tenant_admin_required
def delete_user(user_id):
    admin = User.query.get(session['user_id'])
    user = User.query.filter_by(id=user_id, tenant_id=admin.tenant_id).first_or_404()
    db.session.delete(user)
    db.session.commit()
    flash("User deleted successfully")
    return redirect(url_for('list_users'))
    
#--------------------------------------------------------------
#                             Reports
#--------------------------------------------------------------

# KEEP THIS ONE (the first)
@app.route('/reports/detailed', methods=['GET', 'POST'])
@login_required
@permission_required("create_report")
def detailed_report():
    user = current_user

    departments = Department.query.filter_by(
        tenant_id=user.tenant_id
    ).all()

    if request.method == 'GET':
        reports = Report.query.filter_by(
            user_id=user.id,
            type='detailed'
        ).order_by(Report.created_at.desc()).all()

        return render_template(
            'detailed_report.html',
            user=user,
            departments=departments,
            reports=reports
        )

    # POST → Generate Report
    department_id = request.form.get('department_id')
    title = request.form.get('title', 'Detailed Report')
    raw_data = request.form.get('raw_data', '')

    department = Department.query.filter_by(
        id=department_id,
        tenant_id=user.tenant_id
    ).first()

    if not department:
        flash("Invalid department selected!", "danger")
        return redirect(url_for('detailed_report'))

    try:
        report_html = generate_ai_report(
            title,
            raw_data,
            report_type="detailed"
        )
    except Exception as e:
        flash(f"OpenAI API failed: {e}", "danger")
        return redirect(url_for('detailed_report'))

    save_dir = app.config.get('REPORT_FOLDER', './reports')
    os.makedirs(save_dir, exist_ok=True)

    import uuid
    filename = f"{title.replace(' ', '_')}_{uuid.uuid4().hex}.docx"
    file_path = os.path.join(save_dir, filename)

    company_name = user.tenant.name if user.tenant else "Company"

    html_to_docx(
        html_content=report_html,
        title=title,
        company_name=company_name,
        author_name=user.name,
        save_path=file_path
    )

    report = Report(
        tenant_id=user.tenant_id,
        department_id=department.id,
        user_id=user.id,
        type="detailed",
        title=title,
        file_path=f"reports/{filename}"
    )

    db.session.add(report)
    db.session.commit()

    flash("Detailed report generated successfully!", "success")
    return redirect(url_for('detailed_report'))
    
@app.route('/reports/presentation', methods=['GET', 'POST'])
@login_required
@permission_required("create_report")
@report_access_required
def presentation_reports():

    user = current_user

    departments = Department.query.filter_by(
        tenant_id=user.tenant_id
    ).all()

    reports = Report.query.filter_by(
        user_id=user.id,
        type='presentation'
    ).order_by(Report.created_at.desc()).all()

    if request.method == 'GET':

        return render_template(
            'presentation_reports.html',
            user=user,
            departments=departments,
            reports=reports
        )

    # -----------------------------
    # POST → Generate Presentation
    # -----------------------------
    department_id = request.form.get('department_id')
    title = request.form.get('title', 'Presentation Report')
    raw_data = request.form.get('raw_data', '')

    department = Department.query.filter_by(
        id=department_id,
        tenant_id=user.tenant_id
    ).first()

    if not department:
        flash("Invalid department selected!", "danger")
        return redirect(url_for('presentation_reports'))

    try:
        report_html = generate_ai_report(
            title,
            raw_data,
            report_type="presentation"
        )
    except Exception as e:
        flash(f"OpenAI API failed: {e}", "danger")
        return redirect(url_for('presentation_reports'))

    save_dir = app.config.get('REPORT_FOLDER', './reports')
    os.makedirs(save_dir, exist_ok=True)

    import uuid
    filename = f"{title.replace(' ', '_')}_{uuid.uuid4().hex}.pptx"
    file_path = os.path.join(save_dir, filename)

    html_to_pptx(
        html_content=report_html,
        title=title,
        author=user.name,
        save_path=file_path
    )

    report = Report(
        tenant_id=user.tenant_id,
        department_id=department.id,
        user_id=user.id,
        type="presentation",
        title=title,
        file_path=f"reports/{filename}"
    )

    db.session.add(report)
    db.session.commit()

    flash("Presentation generated successfully!", "success")

    return redirect(url_for('presentation_reports'))
    

@app.route('/reports/delete/<int:report_id>')
def delete_report(report_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))

    report = Report.query.get_or_404(report_id)

    # security: only owner can delete
    if report.user_id != current_user.id:
        flash("You cannot delete this report")
        return redirect(url_for('dashboard'))

    # delete file from disk
    if report.file_path:
        full_path = os.path.join(app.config['REPORT_FOLDER'], os.path.basename(report.file_path))
        if os.path.exists(full_path):
            os.remove(full_path)

    # delete from database
    db.session.delete(report)
    db.session.commit()

    flash("Report deleted successfully")

    # redirect back to the correct report page
    if report.type == 'detailed':
        return redirect(url_for('detailed_report'))
    elif report.type == 'summary':
        return redirect(url_for('summary_reports'))
    elif report.type == 'presentation':
        return redirect(url_for('presentation_reports'))

    return redirect(url_for('dashboard'))
    
#---------------------------------------------
#             Departments
#---------------------------------------------

# List all departments under the tenant
@app.route('/admin/departments')
@login_required
@permission_required("manage_departments")
@tenant_admin_required
def list_departments():
    admin = User.query.get(session['user_id'])
    departments = Department.query.filter_by(tenant_id=admin.tenant_id).all()
    return render_template('list_departments.html', departments=departments)

# Show form to create a new department
@app.route('/admin/departments/create', methods=['GET'])
@login_required
@permission_required("manage_departments")
@tenant_admin_required
def create_department_form():
    return render_template('create_department.html')

# Handle form submission to create a department
@app.route('/admin/departments/create', methods=['POST'])
@login_required
@permission_required("manage_departments")
@tenant_admin_required
def create_department():
    admin = User.query.get(session['user_id'])
    name = request.form.get('name', '').strip()
    description = request.form.get('description', '').strip()

    if not name:
        flash("Department name is required")
        return redirect(url_for('create_department_form'))

    # Check if a department with the same name exists in this tenant
    if Department.query.filter_by(tenant_id=admin.tenant_id, name=name).first():
        flash("Department already exists")
        return redirect(url_for('create_department_form'))

    department = Department(
        name=name,
        description=description,
        tenant_id=admin.tenant_id
    )
    db.session.add(department)
    db.session.commit()

    flash("Department created successfully!")
    return redirect(url_for('list_departments'))
    
# Edit Department - show form
# Update Department (POST only, from the inline form/modal)
@app.route('/admin/departments/<int:dept_id>/update', methods=['POST'])
@login_required
@permission_required("manage_departments")
@tenant_admin_required
def update_department(dept_id):
    admin = User.query.get(session['user_id'])
    department = Department.query.filter_by(id=dept_id, tenant_id=admin.tenant_id).first_or_404()

    # Grab updated info from form
    department.name = request.form['name']
    department.description = request.form['description']

    db.session.commit()
    flash("Department updated successfully")
    return redirect(url_for('list_departments'))

# Delete Department
@app.route('/admin/departments/<int:dept_id>/delete', methods=['POST'])
@login_required
@permission_required("manage_departments")
@tenant_admin_required
def delete_department(dept_id):
    admin = User.query.get(session['user_id'])
    department = Department.query.filter_by(id=dept_id, tenant_id=admin.tenant_id).first_or_404()

    db.session.delete(department)
    db.session.commit()
    flash("Department deleted successfully")
    return redirect(url_for('list_departments'))



@app.route('/admin/tenants/create', methods=['GET','POST'])
@permission_required("create_tenant")
@super_admin_required
def create_tenant():
    if request.method == "POST":
        tenant_name = request.form.get("name").strip()
        if Tenant.query.filter_by(name=company_name).first():
            flash("Tenant already exists", "danger")
            return redirect(url_for('create_tenant'))

        tenant = Tenant(name=company_name)
        db.session.add(tenant)
        db.session.commit()
        flash("Tenant created successfully", "success")
        return redirect(url_for('dashboard'))

    return render_template("tenant_form.html")


# --- LIST TENANTS (SUPER ADMIN ONLY) ---
@app.route('/tenants')
@login_required
def list_tenants():
    user = current_user
    if user.role.name != "Super Admin":
        return "Access Denied", 403

    tenants = Tenant.query.all()
    tenant_data = []
    for tenant in tenants:
        tenant_users = User.query.filter_by(tenant_id=tenant.id).count()
        tenant_departments = Department.query.filter_by(tenant_id=tenant.id).count()
        # Super admin should NOT see tenant reports/processes
        tenant_data.append({
            "tenant": tenant,
            "users": tenant_users,
            "departments": tenant_departments
        })

    return render_template("list_tenants.html", tenants=tenant_data, user=user)
    
    
@app.route('/tenants/<int:tenant_id>/suspend')
@login_required
def suspend_tenant(tenant_id):

    if current_user.role.name != "Super Admin":
        abort(403)

    tenant = Tenant.query.get_or_404(tenant_id)

    tenant.status = "suspended"

    db.session.commit()

    flash("Tenant suspended successfully", "warning")

    return redirect(url_for('list_tenants'))
    
@app.route('/tenants/<int:tenant_id>/activate')
@login_required
def activate_tenant(tenant_id):

    if current_user.role.name != "Super Admin":
        abort(403)

    tenant = Tenant.query.get_or_404(tenant_id)

    tenant.status = "active"

    db.session.commit()

    flash("Tenant activated", "success")

    return redirect(url_for('list_tenants'))
    
@app.route('/tenants/<int:tenant_id>/users')
@login_required
def tenant_users(tenant_id):

    if current_user.role.name != "Super Admin":
        abort(403)

    users = User.query.filter_by(
        tenant_id=tenant_id
    ).all()

    tenant = Tenant.query.get_or_404(tenant_id)

    return render_template(
        "tenants/tenant_users.html",
        users=users,
        tenant=tenant
    )
    
@app.route('/tenants/<int:tenant_id>/delete')
@login_required
def delete_tenant(tenant_id):

    if current_user.role.name != "Super Admin":
        abort(403)

    tenant = Tenant.query.get_or_404(tenant_id)

    db.session.delete(tenant)

    db.session.commit()

    flash("Tenant deleted", "danger")

    return redirect(url_for('list_tenants'))

#-----------------------------------------------
#        processes and escalations
#-----------------------------------------------

@app.route('/processes/create', methods=['GET','POST'])
@login_required
def create_process():

    if request.method == "POST":

        process = Process(
            tenant_id=current_user.tenant_id,
            department_id=request.form['department_id'],
            title=request.form['title'],
            description=request.form['description'],
            assigned_user_id=request.form['assigned_user'],
            created_by_id=current_user.id,
            due_date=datetime.strptime(
                request.form['due_date'],
                "%Y-%m-%d"
            )
        )

        db.session.add(process)
        db.session.commit()

        flash("Process created", "success")

        return redirect(url_for('process_list'))

    departments = Department.query.filter_by(
        tenant_id=current_user.tenant_id
    ).all()

    users = User.query.filter_by(
        tenant_id=current_user.tenant_id
    ).all()

    return render_template(
        "create_process.html",
        departments=departments,
        users=users
    )
    

@app.route('/escalations')
@login_required
def escalations():

    user = current_user

    escalations = Escalation.query.filter_by(
        escalated_to_user_id=user.id,
        status="pending"
    ).order_by(Escalation.triggered_at.desc()).all()

    return render_template(
        "escalations.html",
        escalations=escalations
    )
    
@app.route('/escalations/<int:id>/resolve')
@login_required
def resolve_escalation(id):

    escalation = Escalation.query.get_or_404(id)

    escalation.status = "resolved"
    escalation.resolved_at = datetime.utcnow()

    db.session.commit()

    flash("Escalation resolved successfully", "success")

    return redirect(url_for('escalations'))
    


from flask import jsonify

# ----------------------------
# Super Admin: System Metrics
# ----------------------------
@app.route('/charts/superadmin/<string:frame>')
@super_admin_required
def superadmin_charts(frame):
    """
    frame: 'daily', 'weekly', 'monthly'
    Returns JSON data for Chart.js
    """

    now = datetime.utcnow()

    if frame == 'daily':
        # last 7 days
        start = now - timedelta(days=6)
        date_format = "%Y-%m-%d"
        labels = [(start + timedelta(days=i)).strftime(date_format) for i in range(7)]
        tenants_count = []
        for i in range(7):
            day = start + timedelta(days=i)
            count = Tenant.query.filter(
                func.date(Tenant.created_at) == day.date()
            ).count()
            tenants_count.append(count)

    elif frame == 'weekly':
        # last 4 weeks
        labels = []
        tenants_count = []
        for i in range(4, 0, -1):
            week_start = now - timedelta(weeks=i)
            week_end = week_start + timedelta(days=6)
            labels.append(f"Week {week_start.isocalendar()[1]}")
            count = Tenant.query.filter(
                Tenant.created_at >= week_start,
                Tenant.created_at <= week_end
            ).count()
            tenants_count.append(count)

    elif frame == 'monthly':
        labels = []
        tenants_count = []
        for i in range(5, 0, -1):
            month_start = datetime(now.year, now.month - i + 1, 1)
            month_end = datetime(now.year, now.month - i + 1, calendar.monthrange(month_start.year, month_start.month)[1])
            labels.append(month_start.strftime("%b %Y"))
            count = Tenant.query.filter(
                Tenant.created_at >= month_start,
                Tenant.created_at <= month_end
            ).count()
            tenants_count.append(count)
    else:
        return jsonify({"error": "Invalid frame"}), 400

    return jsonify({
        "labels": labels,
        "tenants": tenants_count
    })
    
    
# ----------------------------
# Tenant Dashboard: Usage Metrics
# ----------------------------
@app.route('/charts/tenant/<string:frame>')
@login_required
def tenant_charts(frame):
    """
    Returns JSON data for tenant-specific metrics
    e.g., reports created, processes, escalations
    """
    tenant_id = current_user.tenant_id
    now = datetime.utcnow()

    def get_daily_counts(model):
        start = now - timedelta(days=6)
        labels = [(start + timedelta(days=i)).strftime("%Y-%m-%d") for i in range(7)]
        counts = []
        for i in range(7):
            day = start + timedelta(days=i)
            count = model.query.filter(
                model.tenant_id == tenant_id,
                func.date(model.created_at) == day.date()
            ).count()
            counts.append(count)
        return labels, counts

    if frame == 'daily':
        labels, reports_count = get_daily_counts(Report)
        _, processes_count = get_daily_counts(Process)
        _, escalations_count = get_daily_counts(Escalation)

    elif frame == 'weekly':
        labels = []
        reports_count = []
        processes_count = []
        escalations_count = []
        for i in range(4, 0, -1):
            week_start = now - timedelta(weeks=i)
            week_end = week_start + timedelta(days=6)
            labels.append(f"Week {week_start.isocalendar()[1]}")

            reports_count.append(Report.query.filter(
                Report.tenant_id == tenant_id,
                Report.created_at >= week_start,
                Report.created_at <= week_end
            ).count())

            processes_count.append(Process.query.filter(
                Process.tenant_id == tenant_id,
                Process.created_at >= week_start,
                Process.created_at <= week_end
            ).count())

            escalations_count.append(Escalation.query.filter(
                Escalation.tenant_id == tenant_id,
                Escalation.created_at >= week_start,
                Escalation.created_at <= week_end
            ).count())

    elif frame == 'monthly':
        import calendar
        labels = []
        reports_count = []
        processes_count = []
        escalations_count = []
        for i in range(5, 0, -1):
            month = now.month - i + 1
            year = now.year
            if month <= 0:
                month += 12
                year -= 1
            month_start = datetime(year, month, 1)
            month_end = datetime(year, month, calendar.monthrange(year, month)[1])
            labels.append(month_start.strftime("%b %Y"))

            reports_count.append(Report.query.filter(
                Report.tenant_id == tenant_id,
                Report.created_at >= month_start,
                Report.created_at <= month_end
            ).count())

            processes_count.append(Process.query.filter(
                Process.tenant_id == tenant_id,
                Process.created_at >= month_start,
                Process.created_at <= month_end
            ).count())

            escalations_count.append(Escalation.query.filter(
                Escalation.tenant_id == tenant_id,
                Escalation.created_at >= month_start,
                Escalation.created_at <= month_end
            ).count())
    else:
        return jsonify({"error": "Invalid frame"}), 400

    return jsonify({
        "labels": labels,
        "reports": reports_count,
        "processes": processes_count,
        "escalations": escalations_count
    })


if __name__ == '__main__':
    app.run(debug=True)