import os, json, sqlite3, secrets, subprocess, platform, shutil, re, time, hmac, threading
from email.utils import parseaddr
from pathlib import Path
from flask import Flask, render_template, request, redirect, session, flash, jsonify, abort
from werkzeug.security import generate_password_hash, check_password_hash
from app.providers.database_engine import DatabaseProvider, DatabaseProviderError

APP=Path("/opt/scpanel")
CFG=Path("/etc/scpanel")
DATA=Path("/var/lib/scpanel")
DB=DATA/"scpanel.db"
ADMIN_ENV=CFG/"admin.env"

def load_version():
    v=os.environ.get("SCPANEL_VERSION","").strip()
    if re.fullmatch(r"\d+\.\d+\.\d+(?:P\d+)?", v): return v
    try:
        v=(APP/"RELEASE_VERSION").read_text().strip()
        if re.fullmatch(r"\d+\.\d+\.\d+(?:P\d+)?", v): return v
    except Exception: pass
    return "1.4.1"

VERSION=load_version()
app=Flask(__name__)
app.secret_key=os.environ.get("SCPANEL_SECRET_KEY") or secrets.token_hex(32)
app.config.update(
    SESSION_COOKIE_HTTPONLY=True,
    SESSION_COOKIE_SAMESITE="Lax",
    SESSION_COOKIE_SECURE=True,
    MAX_CONTENT_LENGTH=2*1024*1024,
)

_csrf_lock=threading.RLock()
_login_lock=threading.RLock()
_login_failures={}
_monitor_cache={"ts":0.0,"data":None}
_monitor_lock=threading.RLock()

def csrf_token():
    token=session.get("_csrf_token")
    if not token:
        token=secrets.token_urlsafe(32)
        session["_csrf_token"]=token
    return token

def _login_allowed(ip, username):
    now=time.time()
    with _login_lock:
        key=(ip or "-", username or "-")
        rec=_login_failures.get(key, [0, now])
        if now-rec[1] > 600:
            rec=[0, now]
        return rec[0] < 5

def _login_failed(ip, username):
    now=time.time()
    with _login_lock:
        key=(ip or "-", username or "-")
        rec=_login_failures.get(key, [0, now])
        if now-rec[1] > 600: rec=[0, now]
        rec[0]+=1; rec[1]=rec[1] if rec[0]>1 else now
        _login_failures[key]=rec

def _login_success(ip, username):
    with _login_lock: _login_failures.pop((ip or "-", username or "-"), None)

@app.context_processor
def security_ctx(): return {"csrf_token": csrf_token()}

@app.before_request
def port_router():
    # The same WSGI app serves admin and user HTTPS ports; route by listener port
    # so opening :2083 never falls back to the admin login.
    try:
        port=request.environ.get("SERVER_PORT") or request.host.rsplit(":",1)[-1]
        port=int(port)
    except Exception:
        port=2087
    if port==2083 and (request.path=="/" or request.path.startswith("/admin")):
        return redirect("/user/login")
    if port==2087 and request.path=="/":
        return redirect("/admin/login")

@app.before_request
def security_guard():
    # All state-changing endpoints except authentication require a CSRF token.
    if request.method in {"POST","PUT","PATCH","DELETE"} and request.endpoint not in {"admin_login","user_login"}:
        supplied=request.form.get("csrf_token") or request.headers.get("X-CSRFToken")
        expected=session.get("_csrf_token")
        if not expected or not supplied or not hmac.compare_digest(str(supplied),str(expected)):
            if request.path.startswith("/api/"):
                return jsonify(ok=False,error="CSRF validation failed."),403
            abort(403, description="CSRF validation failed.")

@app.after_request
def security_headers(resp):
    resp.headers.setdefault("X-Content-Type-Options","nosniff")
    resp.headers.setdefault("X-Frame-Options","DENY")
    resp.headers.setdefault("Referrer-Policy","strict-origin-when-cross-origin")
    resp.headers.setdefault("Permissions-Policy","camera=(), microphone=(), geolocation=()")
    resp.headers.setdefault("Cache-Control","no-store" if request.path.startswith("/api/") else "private, no-cache")
    return resp

def db():
    DATA.mkdir(parents=True,exist_ok=True)
    c=sqlite3.connect(DB)
    c.row_factory=sqlite3.Row
    return c

def init():
    c=db()
    c.executescript("""
    CREATE TABLE IF NOT EXISTS packages(
      id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE,description TEXT,
      disk_mb INTEGER DEFAULT 0,bandwidth_mb INTEGER DEFAULT 0,sites INTEGER DEFAULT 0,
      dbs INTEGER DEFAULT 0,mail INTEGER DEFAULT 0,ftp INTEGER DEFAULT 0,enabled INTEGER DEFAULT 1);
    CREATE TABLE IF NOT EXISTS accounts(
      id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT UNIQUE,email TEXT,password TEXT,
      package_id INTEGER,domain TEXT,enabled INTEGER DEFAULT 1,suspended INTEGER DEFAULT 0,
      system_user TEXT,home_dir TEXT,webroot TEXT,login_shell TEXT DEFAULT '/bin/bash',
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
    CREATE TABLE IF NOT EXISTS websites(
      id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER NOT NULL,domain TEXT NOT NULL UNIQUE,
      aliases TEXT DEFAULT '',document_root TEXT NOT NULL,php_version TEXT DEFAULT 'auto',
      webserver TEXT NOT NULL DEFAULT 'nginx',redirect_to TEXT DEFAULT '',https_enforce INTEGER DEFAULT 0,
      enabled INTEGER DEFAULT 1,created_at TEXT DEFAULT CURRENT_TIMESTAMP,updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_websites_account_id ON websites(account_id);
    CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY,value TEXT);
    CREATE TABLE IF NOT EXISTS app_actions(
      id INTEGER PRIMARY KEY AUTOINCREMENT,app_key TEXT NOT NULL,action TEXT NOT NULL,status TEXT NOT NULL,
      message TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
    CREATE TABLE IF NOT EXISTS audit_log(
      id INTEGER PRIMARY KEY AUTOINCREMENT,actor TEXT NOT NULL,action TEXT NOT NULL,
      target_type TEXT,target_id INTEGER,target TEXT,message TEXT,ip TEXT,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP);
    CREATE TABLE IF NOT EXISTS tasks(
      id TEXT PRIMARY KEY, kind TEXT NOT NULL, app_key TEXT, operation TEXT NOT NULL,
      status TEXT NOT NULL, progress INTEGER NOT NULL DEFAULT 0, step TEXT, message TEXT,
      output TEXT, error TEXT, actor TEXT, ip TEXT, attempts INTEGER NOT NULL DEFAULT 0,
      max_attempts INTEGER NOT NULL DEFAULT 1, created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      started_at TEXT, finished_at TEXT);
    CREATE TABLE IF NOT EXISTS domains(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER, domain TEXT NOT NULL UNIQUE,
      parent_domain TEXT DEFAULT '', kind TEXT NOT NULL DEFAULT 'primary', target TEXT DEFAULT '',
      redirect_to TEXT DEFAULT '', enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE SET NULL);
    CREATE INDEX IF NOT EXISTS idx_domains_account ON domains(account_id);
    CREATE TABLE IF NOT EXISTS dns_zones(
      id INTEGER PRIMARY KEY AUTOINCREMENT, domain_id INTEGER NOT NULL UNIQUE,
      zone TEXT NOT NULL UNIQUE, serial INTEGER NOT NULL DEFAULT 1, enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(domain_id) REFERENCES domains(id) ON DELETE CASCADE);
    CREATE TABLE IF NOT EXISTS dns_records(
      id INTEGER PRIMARY KEY AUTOINCREMENT, zone_id INTEGER NOT NULL, name TEXT NOT NULL,
      type TEXT NOT NULL, value TEXT NOT NULL, ttl INTEGER NOT NULL DEFAULT 3600, priority INTEGER DEFAULT 0,
      enabled INTEGER NOT NULL DEFAULT 1, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(zone_id) REFERENCES dns_zones(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_dns_records_zone ON dns_records(zone_id);
    CREATE TABLE IF NOT EXISTS database_inventory(
      id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT NOT NULL DEFAULT 'mysql',
      name TEXT NOT NULL UNIQUE, account_id INTEGER, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE SET NULL);
    CREATE INDEX IF NOT EXISTS idx_database_inventory_account ON database_inventory(account_id);
    CREATE TABLE IF NOT EXISTS mailboxes(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, email TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL, quota_mb INTEGER NOT NULL DEFAULT 1024, enabled INTEGER NOT NULL DEFAULT 1,
      autoresponder_enabled INTEGER NOT NULL DEFAULT 0, autoresponder_subject TEXT DEFAULT '', autoresponder_message TEXT DEFAULT '',
      created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_mailboxes_account ON mailboxes(account_id);
    CREATE TABLE IF NOT EXISTS mail_forwarders(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, source TEXT NOT NULL, destination TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 1, created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(account_id,source,destination), FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_mail_forwarders_account ON mail_forwarders(account_id);
    CREATE TABLE IF NOT EXISTS cron_jobs(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, name TEXT NOT NULL,
      schedule TEXT NOT NULL, command TEXT NOT NULL, working_dir TEXT NOT NULL, enabled INTEGER NOT NULL DEFAULT 1,
      last_run TEXT, next_run TEXT, last_exit_code INTEGER, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_cron_jobs_account ON cron_jobs(account_id);
    CREATE TABLE IF NOT EXISTS ftp_accounts(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, username TEXT NOT NULL,
      system_username TEXT NOT NULL UNIQUE, directory TEXT NOT NULL, quota_mb INTEGER NOT NULL DEFAULT 0,
      enabled INTEGER NOT NULL DEFAULT 1, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(account_id,username), FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_ftp_accounts_account ON ftp_accounts(account_id);
    CREATE TABLE IF NOT EXISTS sftp_accounts(
      id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, username TEXT NOT NULL,
      system_username TEXT NOT NULL UNIQUE, sftp_root TEXT NOT NULL, directory TEXT NOT NULL, quota_mb INTEGER NOT NULL DEFAULT 0,
      enabled INTEGER NOT NULL DEFAULT 1, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(account_id,username), FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_sftp_accounts_account ON sftp_accounts(account_id);
    CREATE TABLE IF NOT EXISTS sftp_keys(
      id INTEGER PRIMARY KEY AUTOINCREMENT, sftp_id INTEGER NOT NULL, fingerprint TEXT NOT NULL,
      public_key TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP, UNIQUE(sftp_id,fingerprint),
      FOREIGN KEY(sftp_id) REFERENCES sftp_accounts(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_sftp_keys_account ON sftp_keys(sftp_id);
    CREATE TABLE IF NOT EXISTS firewall_rules(
      id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, port INTEGER NOT NULL, protocol TEXT NOT NULL DEFAULT 'tcp',
      source TEXT NOT NULL DEFAULT '0.0.0.0/0', enabled INTEGER NOT NULL DEFAULT 1, protected INTEGER NOT NULL DEFAULT 0,
      description TEXT DEFAULT '', created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
    CREATE INDEX IF NOT EXISTS idx_firewall_port ON firewall_rules(port,protocol);
    CREATE TABLE IF NOT EXISTS ssl_certificates(
      id INTEGER PRIMARY KEY AUTOINCREMENT, website_id INTEGER NOT NULL UNIQUE,
      domain TEXT NOT NULL UNIQUE, status TEXT NOT NULL DEFAULT 'missing',
      auto_renew INTEGER NOT NULL DEFAULT 1, force_https INTEGER NOT NULL DEFAULT 0,
      certificate_path TEXT DEFAULT '', key_path TEXT DEFAULT '',
      issued_at TEXT DEFAULT '', expires_at TEXT DEFAULT '',
      last_renewed_at TEXT DEFAULT '', revoked_at TEXT DEFAULT '',
      created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(website_id) REFERENCES websites(id) ON DELETE CASCADE);
    CREATE INDEX IF NOT EXISTS idx_ssl_domain ON ssl_certificates(domain);
    """)
    # Safe migrations for v1.2.7/v1.2.8 databases.
    cols={r["name"] for r in c.execute("PRAGMA table_info(packages)")}
    for name,typ,default in [("description","TEXT","''"),("disk_mb","INTEGER","0"),("bandwidth_mb","INTEGER","0"),
                              ("sites","INTEGER","0"),("dbs","INTEGER","0"),("mail","INTEGER","0"),("ftp","INTEGER","0"),("enabled","INTEGER","1")]:
        if name not in cols: c.execute(f"ALTER TABLE packages ADD COLUMN {name} {typ} DEFAULT {default}")
    cols={r["name"] for r in c.execute("PRAGMA table_info(accounts)")}
    for name,typ,default in [("suspended","INTEGER","0"),("system_user","TEXT","''"),("home_dir","TEXT","''"),
                              ("webroot","TEXT","''"),("login_shell","TEXT","'/bin/bash'"),("updated_at","TEXT","CURRENT_TIMESTAMP")]:
        if name not in cols: c.execute(f"ALTER TABLE accounts ADD COLUMN {name} {typ} DEFAULT {default}")
    # Safe migration for v1.3.0 website manager: older v1.2.x databases may not
    # have the websites table yet.  Keep this idempotent so fresh and upgraded
    # installations converge on the same schema before /admin is rendered.
    c.execute("""CREATE TABLE IF NOT EXISTS websites(
      id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER NOT NULL,domain TEXT NOT NULL UNIQUE,
      aliases TEXT DEFAULT '',document_root TEXT NOT NULL,php_version TEXT DEFAULT 'auto',
      webserver TEXT NOT NULL DEFAULT 'nginx',redirect_to TEXT DEFAULT '',https_enforce INTEGER DEFAULT 0,
      enabled INTEGER DEFAULT 1,created_at TEXT DEFAULT CURRENT_TIMESTAMP,updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE)""")
    c.execute("CREATE INDEX IF NOT EXISTS idx_websites_account_id ON websites(account_id)")
    wcols={r["name"] for r in c.execute("PRAGMA table_info(websites)")}
    for name,typ,default in [("aliases","TEXT","''"),("document_root","TEXT","''"),("php_version","TEXT","'auto'"),
                              ("webserver","TEXT","'nginx'"),("redirect_to","TEXT","''"),("https_enforce","INTEGER","0"),
                              ("enabled","INTEGER","1"),("created_at","TEXT","CURRENT_TIMESTAMP"),("updated_at","TEXT","CURRENT_TIMESTAMP")]:
        if name not in wcols: c.execute(f"ALTER TABLE websites ADD COLUMN {name} {typ} DEFAULT {default}")
    # v1.3.1 domain/DNS migration: converge existing website primary domains and aliases into the domain inventory.
    try:
        existing_websites=c.execute("SELECT account_id,domain,aliases FROM websites").fetchall()
        for w in existing_websites:
            c.execute("INSERT OR IGNORE INTO domains(account_id,domain,parent_domain,kind,target) VALUES(?,?,?,?,?)",(w['account_id'],w['domain'],'','primary',''))
            did=c.execute("SELECT id FROM domains WHERE domain=?",(w['domain'],)).fetchone()['id']
            for alias in (w['aliases'] or '').split():
                if valid_domain(alias): c.execute("INSERT OR IGNORE INTO domains(account_id,domain,parent_domain,kind,target) VALUES(?,?,?,?,?)",(w['account_id'],alias,w['domain'],'alias',''))
    except Exception:
        pass
    # v1.3.2 database inventory migration. Existing MariaDB/MySQL databases are synced lazily by the manager.
    c.execute("CREATE TABLE IF NOT EXISTS database_inventory(id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT NOT NULL DEFAULT 'mysql', name TEXT NOT NULL UNIQUE, account_id INTEGER, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE SET NULL)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_database_inventory_account ON database_inventory(account_id)")
    for x in [("Starter","Starter hosting plan",10240,102400,1,1,5,1),
              ("Business","Business hosting plan",51200,512000,5,10,50,10),
              ("Unlimited","Unlimited hosting plan",0,0,0,0,0,0)]:
        c.execute("INSERT OR IGNORE INTO packages(name,description,disk_mb,bandwidth_mb,sites,dbs,mail,ftp) VALUES(?,?,?,?,?,?,?,?)",x)
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('admin_email','')")
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('timezone','UTC')")
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('log_retention_days','30')")
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('user_http_port','2082')")
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('hostname',?)",(platform.node(),))
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('admin_http_port','2086')")
    c.execute("INSERT OR IGNORE INTO settings(key,value) VALUES('admin_https_port','2087')")
    # v1.3.6 SSL inventory migration: every managed website gets a certificate row.
    for _w in c.execute('SELECT id,domain,https_enforce FROM websites').fetchall():
        _crt=Path('/etc/scpanel/ssl')/_w['domain']/'fullchain.pem'; _key=Path('/etc/scpanel/ssl')/_w['domain']/'privkey.pem'
        _status='valid' if _crt.exists() and _key.exists() else 'missing'
        c.execute("INSERT OR IGNORE INTO ssl_certificates(website_id,domain,status,force_https,certificate_path,key_path) VALUES(?,?,?,?,?,?)",(_w['id'],_w['domain'],_status,int(_w['https_enforce']),str(_crt),str(_key)))
        c.execute("UPDATE ssl_certificates SET force_https=?,certificate_path=?,key_path=?,updated_at=CURRENT_TIMESTAMP WHERE website_id=?",(int(_w['https_enforce']),str(_crt),str(_key),_w['id']))
    c.commit(); c.close()

# Recover durable task state once per Gunicorn worker process.
# P3 runs a single WSGI worker, so startup recovery is deterministic: queued
# tasks resume; tasks interrupted mid-operation are marked failed for review.
try:
    from app.task_engine import init as _init_tasks, recover_pending as _recover_tasks
    _init_tasks(); _recover_tasks()
except Exception:
    pass

def admin_ok(username,password):
    if username != "scroot": return False
    try:
        import hmac, crypt
        stored={}
        for line in ADMIN_ENV.read_text().splitlines():
            if "=" in line:
                k,v=line.split("=",1); stored[k]=v
        return hmac.compare_digest(password,stored.get("SCPANEL_ADMIN_PASSWORD",""))
    except Exception:
        return False

def services():
    """Return dynamically detected service/application state.
    Provider discovery is authoritative; no hard-coded Installed flags.
    """
    try:
        from app.providers.service_engine import catalog
        return [(x["name"], bool(x.get("installed"))) for x in catalog()]
    except Exception:
        return []

def nav(admin=True):
    if admin:
        return [
        ("OVERVIEW", "", True),("Dashboard","/admin",False),
        ("HOSTING", "", True),("Hosting Accounts","/admin/accounts",False),("Packages","/admin/packages",False),
        ("Websites","/admin/websites",False),("Domains","/admin/domains",False),("File Manager","/admin/files",False),
        ("Databases","/admin/databases",False),("Email","/admin/email",False),("FTP / SFTP","/admin/ftp",False),
        ("DNS","/admin/dns",False),("SSL / Let's Encrypt","/admin/ssl",False),("Cron Jobs","/admin/cron",False),("Backups","/admin/backups",False),
        ("SERVER", "", True),("Task Center","/admin/tasks",False),("Monitor","/admin/monitor",False),("Services","/admin/services",False),("Firewall & Ports","/admin/firewall",False),
        ("SETTINGS","", True),("Admin User","/admin/settings",False),("App Store","/admin/store",False),("Audit Log","/admin/audit",False),("Update Manager","/admin/updates",False)]
    return [
        ("OVERVIEW","",True),("Dashboard","/user",False),("HOSTING","",True),
        ("Websites","/user/websites",False),("Domains","/user/domains",False),("File Manager","/user/files",False),
        ("Databases","/user/databases",False),("Email","/user/email",False),("FTP / SFTP","/user/ftp",False),
        ("DNS","/user/dns",False),("SSL / Let's Encrypt","/user/ssl",False),("Cron Jobs","/user/cron",False),("Backups","/user/backups",False),
        ("ACCOUNT","",True),("My Account","/user/account",False)]

@app.context_processor
def ctx(): return {"version":VERSION,"nav":nav(session.get("role")=="admin")}

@app.route("/")
def home(): return redirect("/admin/login")

@app.route("/admin/login",methods=["GET","POST"])
def admin_login():
    if request.method=="POST":
        u=request.form.get("username","").strip()
        if not _login_allowed(request.remote_addr,u):
            flash("Too many failed attempts. Try again in a few minutes.")
            return render_template("login.html",kind="Admin"),429
        if admin_ok(u,request.form.get("password","")):
            _login_success(request.remote_addr,u)
            session.clear(); session.update(role="admin",username="scroot",_csrf_token=secrets.token_urlsafe(32)); return redirect("/admin")
        _login_failed(request.remote_addr,u); flash("Invalid username or password.")
    return render_template("login.html",kind="Admin")

@app.route("/user/login",methods=["GET","POST"])
def user_login():
    if request.method=="POST":
        u=request.form.get("username",""); p=request.form.get("password","")
        c=db(); a=c.execute("SELECT * FROM accounts WHERE username=? AND enabled=1",(u,)).fetchone(); c.close()
        if not _login_allowed(request.remote_addr,u):
            flash("Too many failed attempts. Try again in a few minutes.")
            return render_template("login.html",kind="User"),429
        if a and password_matches(p,a["password"]):
            _login_success(request.remote_addr,u)
            session.clear(); session.update(role="user",username=u,account_id=a["id"],_csrf_token=secrets.token_urlsafe(32)); return redirect("/user")
        _login_failed(request.remote_addr,u); flash("Invalid username or password.")
    return render_template("login.html",kind="User")


# ---- Update Manager --------------------------------------------------------
@app.route('/admin/updates')
def admin_updates():
    x=admin_required()
    if x:return x
    from app.providers import update_engine
    try: manifest=update_engine.fetch_manifest()
    except Exception as e: manifest={'error':str(e),'versions':[],'latest':None,'current_version':update_engine.installed_version()}
    return render_template('admin/updates.html',manifest=manifest,state=update_engine._state(),runtime=update_engine.verify_runtime(),changelog=update_engine.changelog())
@app.route('/admin/updates/action',methods=['POST'])
def admin_updates_action():
    x=admin_required()
    if x:return x
    from app.providers import update_engine
    action=request.form.get('action')
    try:
        if action=='check':
            m=update_engine.fetch_manifest(); latest=m.get('latest'); flash(('Latest: '+latest['version']) if latest else 'No verified update available.')
        elif action=='download':
            m=update_engine.fetch_manifest(); v=request.form.get('version'); item=next((x for x in m.get('versions',[]) if x['version']==v),None)
            if not item: raise update_engine.UpdateError('Requested version is not in the verified manifest.')
            update_engine.download(item); flash('Package downloaded and SHA256 verified: '+v)
        elif action=='update':
            st=update_engine._state(); path=st.get('download',{}).get('path'); update_engine.apply(path); flash('Update applied and runtime version verified.')
        elif action=='rollback':
            update_engine.rollback(); flash('Rollback completed and runtime version verified.')
        elif action=='runtime':
            r=update_engine.verify_runtime(); flash('Version consistency: '+('PASS' if r.get('all_match') else 'FAIL'))
        elif action=='changelog': flash('Changelog is available below.')
        else: raise update_engine.UpdateError('Unknown update action.')
    except Exception as e: flash('Update Manager: '+str(e))
    return redirect('/admin/updates')
@app.route('/api/admin/update/status')
def api_admin_update_status():
    x=admin_required()
    if x:return x
    from app.providers import update_engine
    return jsonify(runtime=update_engine.verify_runtime(),state=update_engine._state())

@app.route("/admin/return")
def admin_return():
    if not session.get("impersonating"): return redirect("/user")
    audit("account.login_as_user.end","hosting_account",session.get("account_id"),session.get("username"),"Admin impersonation session ended")
    session.clear(); session.update(role="admin",username="scroot",_csrf_token=secrets.token_urlsafe(32)); return redirect("/admin/accounts")

@app.route("/logout")
def logout():
    if session.get("impersonating"): return redirect("/admin/return")
    session.clear(); return redirect("/admin/login")

USERNAME_RE=re.compile(r"^[a-z][a-z0-9_-]{0,31}$")
DOMAIN_RE=re.compile(r"^(?=.{1,253}$)(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z]{2,63}$", re.I)
RESERVED_USERS={"root","scroot","daemon","bin","sys","sync","games","man","lp","mail","news","uucp","proxy","www-data","backup","list","irc","gnats","nobody","systemd-network","systemd-resolve","messagebus","_apt"}

def audit(action,target_type=None,target_id=None,target=None,message=""):
    try:
        c=db(); c.execute("INSERT INTO audit_log(actor,action,target_type,target_id,target,message,ip) VALUES(?,?,?,?,?,?,?)",
                         (session.get("username","system"),action,target_type,target_id,target,message,request.remote_addr or "-")); c.commit(); c.close()
    except Exception: pass

def password_matches(password, stored):
    if not stored: return False
    try:
        if stored.startswith(("scrypt:","pbkdf2:")): return check_password_hash(stored,password)
    except Exception: pass
    return hmac.compare_digest(password,stored)

def valid_username(u):
    return bool(USERNAME_RE.fullmatch(u)) and u not in RESERVED_USERS and not u.startswith("-" )

def valid_domain(d): return bool(DOMAIN_RE.fullmatch(d.lower().rstrip('.')))

def system_user_exists(u):
    return shutil.which("id") is not None and subprocess.run(["id","-u",u],stdout=subprocess.DEVNULL,stderr=subprocess.DEVNULL).returncode==0

def run_system(argv, input_text=None, timeout=120):
    if input_text is not None and not isinstance(input_text, str):
        raise TypeError("input_text must be a string or None")
    return subprocess.run(
        argv,
        input=input_text,
        text=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.STDOUT,
        timeout=timeout,
        check=False,
    )

def create_system_account(username,password,home_dir):
    if not shutil.which("useradd") or not shutil.which("chpasswd"):
        raise RuntimeError("Required Linux account tools are unavailable.")
    if system_user_exists(username): raise RuntimeError("Linux username already exists on this server.")
    r=run_system(["useradd","--create-home","--home-dir",home_dir,"--shell","/bin/bash",username])
    if r.returncode: raise RuntimeError("Could not create Linux user: "+r.stdout.strip()[-500:])
    try:
        r=run_system(["chpasswd"],username+":"+password+"\n")
        if r.returncode: raise RuntimeError("Could not set Linux user password: "+r.stdout.strip()[-500:])
        Path(home_dir,"public_html").mkdir(parents=True,exist_ok=True)
        Path(home_dir,"public_html","index.html").touch(exist_ok=True)
        run_system(["chown","-R",f"{username}:{username}",home_dir])
        run_system(["chmod","750",home_dir]); run_system(["chmod","755",str(Path(home_dir,"public_html"))])
    except Exception:
        run_system(["userdel","--remove",username])
        shutil.rmtree(home_dir,ignore_errors=True)
        raise

def set_system_state(username,enabled=True,shell="/bin/bash"):
    if not system_user_exists(username): return
    if enabled:
        run_system(["usermod","-U",username])
        run_system(["usermod","-s",shell,username])
    else:
        run_system(["usermod","-L",username])
        run_system(["usermod","-s","/usr/sbin/nologin",username])

def delete_system_account(username,home_dir):
    if system_user_exists(username) and shutil.which("userdel"):
        r=run_system(["userdel","--remove",username],180)
        if r.returncode:
            raise RuntimeError("Could not remove Linux user: "+r.stdout.strip()[-500:])
    if home_dir and home_dir.startswith("/home/") and Path(home_dir).name==username:
        shutil.rmtree(home_dir,ignore_errors=True)

def package_for(c,package_id):
    if not package_id: return None
    return c.execute("SELECT * FROM packages WHERE id=? AND enabled=1",(package_id,)).fetchone()

def admin_required():
    if session.get("role")!="admin": return redirect("/admin/login")
def user_required():
    if session.get("role")!="user": return redirect("/user/login")

@app.route("/admin")
def admin_dash():
    x=admin_required()
    if x:return x
    c=db(); counts={}
    for label,table in [("Hosting Accounts","accounts"),("Packages","packages")]:
        counts[label]=c.execute(f"SELECT COUNT(*) n FROM {table}").fetchone()["n"]
    counts.update({"Websites":c.execute("SELECT COUNT(*) n FROM websites").fetchone()["n"],"Domains":c.execute("SELECT COUNT(DISTINCT domain) n FROM websites").fetchone()["n"],"Databases":0,"Email":0})
    c.close()
    return render_template("admin/dashboard.html",counts=counts,hostname=get_setting("hostname",platform.node()),osname=platform.platform())

@app.route("/admin/accounts")
def accounts():
    x=admin_required()
    if x:return x
    c=db(); rows=c.execute("""SELECT a.*,p.name package,p.disk_mb,p.bandwidth_mb,p.sites,p.dbs,p.mail,p.ftp
                              FROM accounts a LEFT JOIN packages p ON p.id=a.package_id ORDER BY a.id DESC""").fetchall()
    pkgs=c.execute("SELECT * FROM packages WHERE enabled=1 ORDER BY name").fetchall(); c.close()
    return render_template("admin/accounts.html",accounts=rows,packages=pkgs)

@app.route("/admin/accounts/create",methods=["POST"])
def create_account():
    x=admin_required()
    if x:return x
    u=request.form.get("username","").strip().lower(); pw=request.form.get("password",""); email=request.form.get("email","").strip(); domain=request.form.get("domain","").strip().lower().rstrip('.')
    package_id=request.form.get("package_id") or None
    if not valid_username(u): flash("Invalid username. Use 1–32 lowercase letters, numbers, _ or -, and do not use a reserved system name."); return redirect("/admin/accounts")
    if len(pw)<8: flash("Password must be at least 8 characters."); return redirect("/admin/accounts")
    if not email or not parseaddr(email)[1] == email: flash("Enter a valid email address."); return redirect("/admin/accounts")
    if not valid_domain(domain): flash("Enter a valid primary domain."); return redirect("/admin/accounts")
    c=db(); pkg=package_for(c,package_id)
    if not pkg: c.close(); flash("Select an enabled package."); return redirect("/admin/accounts")
    home=f"/home/{u}"; webroot=f"/home/{u}/public_html"
    try:
        create_system_account(u,pw,home)
        c.execute("INSERT INTO accounts(username,email,password,package_id,domain,enabled,suspended,system_user,home_dir,webroot,login_shell) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
                  (u,email,generate_password_hash(pw),pkg["id"],domain,1,0,u,home,webroot,"/bin/bash"))
        aid=c.execute("SELECT last_insert_rowid() id").fetchone()["id"]
        c.commit(); audit("account.create","hosting_account",aid,u,f"Package={pkg['name']}; domain={domain}"); flash(f"Hosting account {u} created successfully.")
    except sqlite3.IntegrityError:
        c.rollback();
        if system_user_exists(u):
            try: delete_system_account(u,home)
            except Exception: pass
        flash("Username already exists in SCpanel.")
    except Exception as exc:
        c.rollback()
        if system_user_exists(u):
            try: delete_system_account(u,home)
            except Exception: pass
        flash("Account creation failed: "+str(exc))
    finally: c.close()
    return redirect("/admin/accounts")

@app.route("/admin/accounts/<int:aid>/edit",methods=["POST"])
def edit_account(aid):
    x=admin_required()
    if x:return x
    c=db(); a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone(); pkg=package_for(c,request.form.get("package_id"))
    if not a: c.close(); flash("Account not found."); return redirect("/admin/accounts")
    email=request.form.get("email","").strip(); domain=request.form.get("domain","").strip().lower().rstrip('.'); newpw=request.form.get("password","")
    if not email or not parseaddr(email)[1] == email: c.close(); flash("Enter a valid email address."); return redirect("/admin/accounts")
    if not valid_domain(domain): c.close(); flash("Invalid primary domain."); return redirect("/admin/accounts")
    if not pkg: c.close(); flash("Select an enabled package."); return redirect("/admin/accounts")
    try:
        if newpw:
            if len(newpw)<8: raise RuntimeError("Password must be at least 8 characters.")
            if a["system_user"]: 
                r=run_system(["chpasswd"],a["system_user"]+":"+newpw+"\n")
                if r.returncode: raise RuntimeError("Could not update Linux password.")
            c.execute("UPDATE accounts SET password=?,email=?,domain=?,package_id=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",
                      (generate_password_hash(newpw),email,domain,pkg["id"],aid))
            audit("account.password_change","hosting_account",aid,a["username"],"Linux and panel password changed")
        else:
            c.execute("UPDATE accounts SET email=?,domain=?,package_id=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(email,domain,pkg["id"],aid))
        c.commit(); audit("account.edit","hosting_account",aid,a["username"],f"Package={pkg['name']}; domain={domain}"); flash("Hosting account updated.")
    except Exception as exc: c.rollback(); flash("Account update failed: "+str(exc))
    finally: c.close()
    return redirect("/admin/accounts")

def _account_state(aid, enabled, suspended=False):
    c=db(); a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone()
    if not a: c.close(); raise RuntimeError("Account not found.")
    u=a["system_user"] or a["username"]
    set_system_state(u,enabled=enabled)
    c.execute("UPDATE accounts SET enabled=?,suspended=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(1 if enabled else 0,1 if suspended else 0,aid)); c.commit(); c.close()
    audit("account.suspend" if suspended else ("account.enable" if enabled else "account.disable"),"hosting_account",aid,a["username"],"Linux account state synchronized")

@app.route("/admin/accounts/<int:aid>/toggle",methods=["POST"])
def toggle_account(aid):
    x=admin_required()
    if x:return x
    c=db(); a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone(); c.close()
    if not a: flash("Account not found."); return redirect("/admin/accounts")
    try: _account_state(aid,not bool(a["enabled"]),False); flash("Account enabled." if not a["enabled"] else "Account disabled.")
    except Exception as exc: flash("Account state change failed: "+str(exc))
    return redirect("/admin/accounts")

@app.route("/admin/accounts/<int:aid>/suspend",methods=["POST"])
def suspend_account(aid):
    x=admin_required()
    if x:return x
    try: _account_state(aid,False,True); flash("Hosting account suspended.")
    except Exception as exc: flash("Suspend failed: "+str(exc))
    return redirect("/admin/accounts")

@app.route("/admin/accounts/<int:aid>/unsuspend",methods=["POST"])
def unsuspend_account(aid):
    x=admin_required()
    if x:return x
    try: _account_state(aid,True,False); flash("Hosting account unsuspended and enabled.")
    except Exception as exc: flash("Unsuspend failed: "+str(exc))
    return redirect("/admin/accounts")

@app.route("/admin/accounts/<int:aid>/delete",methods=["POST"])
def delete_account(aid):
    x=admin_required()
    if x:return x
    c=db(); a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone()
    if not a: c.close(); flash("Account not found."); return redirect("/admin/accounts")
    try:
        delete_system_account(a["system_user"] or a["username"],a["home_dir"] or f"/home/{a['username']}")
        c.execute("DELETE FROM accounts WHERE id=?",(aid,)); c.commit(); audit("account.delete","hosting_account",aid,a["username"],"Linux user and home directory removed"); flash("Hosting account deleted completely.")
    except Exception as exc: c.rollback(); flash("Delete failed: "+str(exc))
    finally: c.close()
    return redirect("/admin/accounts")

@app.route("/admin/accounts/<int:aid>/login",methods=["POST"])
def login_as_user(aid):
    x=admin_required()
    if x:return x
    c=db(); a=c.execute("SELECT * FROM accounts WHERE id=? AND enabled=1 AND suspended=0",(aid,)).fetchone(); c.close()
    if not a: flash("Account is not available for login."); return redirect("/admin/accounts")
    session.clear(); session.update(role="user",username=a["username"],account_id=a["id"],impersonating=True,admin_return="scroot")
    audit("account.login_as_user","hosting_account",aid,a["username"],"Admin impersonation session started")
    return redirect("/user")

@app.route("/admin/audit")
def audit_page():
    x=admin_required()
    if x:return x
    c=db(); rows=c.execute("SELECT * FROM audit_log ORDER BY id DESC LIMIT 200").fetchall(); c.close()
    return render_template("module.html",title="Audit Log",description="Recent administrative and account actions.",items=rows)



def get_setting(key, default=""):
    try:
        c=db(); row=c.execute("SELECT value FROM settings WHERE key=?",(key,)).fetchone(); c.close()
        return row["value"] if row else default
    except Exception: return default

def valid_php_version(v):
    return v in {"auto","8.1","8.2","8.3","8.4","8.5"} or bool(re.fullmatch(r"php[0-9.]+-fpm",v or ""))

def php_fpm_socket(version):
    if version and version.startswith("php") and version.endswith("-fpm"):
        base=version[:-4]
        cand=Path("/run/php")/(base+".sock")
        if cand.exists(): return str(cand)
    sockets=sorted(Path("/run/php").glob("php*-fpm.sock")) if Path("/run/php").exists() else []
    if sockets: return str(sockets[-1])
    return ""

def website_cert(domain):
    base=Path("/etc/scpanel/ssl")/domain
    return (base/"fullchain.pem",base/"privkey.pem")

def _safe_aliases(raw, primary):
    vals=[]
    for x in (raw or "").replace(","," ").split():
        x=x.lower().rstrip('.')
        if x and valid_domain(x) and x!=primary and x not in vals: vals.append(x)
    return vals

def _website_config(w, account):
    domain=w["domain"]; root=Path(w["document_root"]); aliases=_safe_aliases(w["aliases"],domain)
    webserver=w["webserver"]; redirect=w["redirect_to"].strip(); php=php_fpm_socket(w["php_version"])
    if webserver=="apache":
        conf=f"# Managed by SIKON Cpanel; website_id={w['id']}\n<VirtualHost *:80>\n    ServerName {domain}\n    ServerAlias {' '.join(aliases)}\n    DocumentRoot {root}\n    <Directory {root}>\n        AllowOverride All\n        Require all granted\n    </Directory>\n"
        if redirect and valid_domain(redirect): conf += f"    Redirect permanent / https://{redirect}/\n"
        elif w["https_enforce"]:
            conf += f"    Redirect permanent / https://{domain}/\n"
        if php: conf += f"    <FilesMatch \\.php$>\n        SetHandler \"proxy:unix:{php}|fcgi://localhost/\"\n    </FilesMatch>\n"
        conf += "</VirtualHost>\n"
        crt,key=website_cert(domain)
        if crt.exists() and key.exists():
            conf += f"\n<VirtualHost *:443>\n    ServerName {domain}\n    ServerAlias {' '.join(aliases)}\n    DocumentRoot {root}\n    SSLEngine on\n    SSLCertificateFile {crt}\n    SSLCertificateKeyFile {key}\n    <Directory {root}>\n        AllowOverride All\n        Require all granted\n    </Directory>\n"
            if php: conf += f"    <FilesMatch \\.php$>\n        SetHandler \"proxy:unix:{php}|fcgi://localhost/\"\n    </FilesMatch>\n"
            conf += "</VirtualHost>\n"
        return conf
    conf=f"# Managed by SIKON Cpanel; website_id={w['id']}\nserver {{\n    listen 80;\n    server_name {domain} {' '.join(aliases)};\n    root {root};\n    index index.php index.html index.htm;\n    access_log /var/log/nginx/{domain}.access.log;\n    error_log /var/log/nginx/{domain}.error.log;\n"
    if redirect and valid_domain(redirect): conf += f"    return 301 https://{redirect}$request_uri;\n"
    elif w["https_enforce"]:
        conf += f"    return 301 https://{domain}$request_uri;\n"
    else:
        conf += "    location / { try_files $uri $uri/ /index.php?$query_string; }\n"
        if php: conf += f"    location ~ \\.php$ {{ include snippets/fastcgi-php.conf; fastcgi_pass unix:{php}; }}\n"
    conf += "}\n"
    crt,key=website_cert(domain)
    if crt.exists() and key.exists():
        conf += f"\nserver {{\n    listen 443 ssl http2;\n    server_name {domain} {' '.join(aliases)};\n    root {root};\n    index index.php index.html index.htm;\n    ssl_certificate {crt};\n    ssl_certificate_key {key};\n"
        if php: conf += f"    location / {{ try_files $uri $uri/ /index.php?$query_string; }}\n    location ~ \\.php$ {{ include snippets/fastcgi-php.conf; fastcgi_pass unix:{php}; }}\n"
        conf += "}\n"
    return conf

def _write_website_config(w, account):
    webserver=w["webserver"]
    if webserver=="apache":
        path=Path("/etc/apache2/sites-available")/f"scpanel-{w['id']}-{w['domain']}.conf"
    else:
        path=Path("/etc/nginx/sites-available")/f"scpanel-{w['id']}-{w['domain']}.conf"
    path.parent.mkdir(parents=True,exist_ok=True); path.write_text(_website_config(w,account),encoding="utf-8")
    if webserver=="nginx":
        link=Path("/etc/nginx/sites-enabled")/path.name; link.parent.mkdir(parents=True,exist_ok=True)
        if w["enabled"]: link.unlink(missing_ok=True); link.symlink_to(path)
        else: link.unlink(missing_ok=True)
        cmd=["nginx","-t"]
    else:
        enabled=Path("/etc/apache2/sites-enabled")/path.name; enabled.parent.mkdir(parents=True,exist_ok=True)
        if w["enabled"]: enabled.unlink(missing_ok=True); enabled.symlink_to(path)
        else: enabled.unlink(missing_ok=True)
        cmd=["apache2ctl","configtest"]
    r=run_system(cmd,timeout=30)
    if r.returncode: raise RuntimeError(f"{webserver} configuration test failed: {r.stdout.strip()[-800:]}")
    unit="nginx" if webserver=="nginx" else "apache2"
    r=run_system(["systemctl","reload",unit],timeout=30)
    if r.returncode: raise RuntimeError(f"Could not safely reload {unit}: {r.stdout.strip()[-800:]}")
    return str(path)

def _available_php_versions():
    # Only expose PHP runtimes that are actually installed or verifiably available
    # from the current OS repository. Never present a fictional runtime choice.
    out=["auto"]
    try:
        from app.providers.service_engine import php_runtime_versions
        for v in php_runtime_versions():
            if v not in out: out.append(v)
    except Exception:
        pass
    return out

@app.route("/admin/websites", methods=["GET"])
def websites_page():
    x=admin_required()
    if x:return x
    c=db(); rows=c.execute("SELECT w.*,a.username,a.domain account_domain FROM websites w JOIN accounts a ON a.id=w.account_id ORDER BY w.id DESC").fetchall(); accounts=c.execute("SELECT id,username,domain FROM accounts WHERE enabled=1 AND suspended=0 ORDER BY username").fetchall(); c.close()
    versions=_available_php_versions()
    # Preserve an existing website's selected version in the edit dropdown even
    # if its runtime is temporarily unavailable; creation only offers verified runtimes.
    for row in rows:
        if row["php_version"] not in versions: versions.append(row["php_version"])
    return render_template("admin/websites.html",websites=rows,accounts=accounts,php_versions=versions)

@app.route("/admin/websites/create",methods=["POST"])
def create_website():
    x=admin_required()
    if x:return x
    try:
        account_id=int(request.form.get("account_id","0")); domain=request.form.get("domain","").strip().lower().rstrip('.'); aliases=request.form.get("aliases",""); docroot=request.form.get("document_root","").strip(); php=request.form.get("php_version","auto"); webserver=request.form.get("webserver","nginx"); redirect=request.form.get("redirect_to","").strip(); https=1 if request.form.get("https_enforce") else 0
        if not valid_domain(domain): raise RuntimeError("Invalid domain.")
        if webserver not in {"nginx","apache"}: raise RuntimeError("Unsupported web server.")
        if not valid_php_version(php): raise RuntimeError("Unsupported PHP version.")
        if php != "auto" and php not in _available_php_versions(): raise RuntimeError(f"PHP {php} is not installed or verified in the current OS repository.")
        c=db(); a=c.execute("SELECT * FROM accounts WHERE id=? AND enabled=1 AND suspended=0",(account_id,)).fetchone()
        if not a: c.close(); raise RuntimeError("Select an active hosting account.")
        if https and not all(x.exists() for x in website_cert(domain)): raise RuntimeError("HTTPS enforcement requires an existing certificate in /etc/scpanel/ssl/<domain>/.")
        if not docroot: docroot=f"/home/{a['username']}/public_html"
        rp=Path(docroot).resolve(); home=Path(a["home_dir"]).resolve()
        if home not in rp.parents and rp!=home: raise RuntimeError("Document root must remain inside the account home directory.")
        rp.mkdir(parents=True,exist_ok=True); run_system(["chown","-R",f"{a['system_user'] or a['username']}:{a['system_user'] or a['username']}",str(rp)],timeout=60)
        aliases_list=_safe_aliases(aliases,domain)
        c.execute("INSERT INTO websites(account_id,domain,aliases,document_root,php_version,webserver,redirect_to,https_enforce) VALUES(?,?,?,?,?,?,?,?)",(account_id,domain," ".join(aliases_list),str(rp),php,webserver,redirect,https)); wid=c.execute("SELECT last_insert_rowid() id").fetchone()["id"]; c.commit(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone()
        try: _write_website_config(w,a)
        except Exception: c.execute("DELETE FROM websites WHERE id=?",(wid,)); c.commit(); raise
        c.close(); audit("website.create","website",wid,domain,f"Account={a['username']}; webserver={webserver}; php={php}"); flash(f"Website {domain} created and {webserver} configuration reloaded.")
    except Exception as exc: flash("Website creation failed: "+str(exc))
    return redirect("/admin/websites")

@app.route("/admin/websites/<int:wid>/edit",methods=["POST"])
def edit_website(wid):
    x=admin_required()
    if x:return x
    c=db(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone()
    if not w: c.close(); flash("Website not found."); return redirect("/admin/websites")
    a=c.execute("SELECT * FROM accounts WHERE id=?",(w["account_id"],)).fetchone()
    try:
        domain=request.form.get("domain",w["domain"]).strip().lower().rstrip('.')
        aliases=request.form.get("aliases","")
        docroot=request.form.get("document_root","").strip() or f"/home/{a['username']}/public_html"
        php=request.form.get("php_version",w["php_version"])
        webserver=request.form.get("webserver",w["webserver"])
        redirect_to=request.form.get("redirect_to","").strip()
        https=1 if request.form.get("https_enforce") else 0
        if not valid_domain(domain): raise RuntimeError("Invalid primary domain.")
        if webserver not in {"nginx","apache"}: raise RuntimeError("Unsupported web server.")
        if not valid_php_version(php): raise RuntimeError("Unsupported PHP version.")
        if php != w["php_version"] and php != "auto" and php not in _available_php_versions(): raise RuntimeError(f"PHP {php} is not installed or verified in the current OS repository.")
        if redirect_to and not valid_domain(redirect_to): raise RuntimeError("Invalid redirect domain.")
        if https and not all(x.exists() for x in website_cert(domain)): raise RuntimeError("HTTPS enforcement requires an existing certificate for the primary domain.")
        rp=Path(docroot).resolve(); home=Path(a["home_dir"]).resolve()
        if home not in rp.parents and rp!=home: raise RuntimeError("Document root must remain inside the account home directory.")
        rp.mkdir(parents=True,exist_ok=True)
        owner=a['system_user'] or a['username']; run_system(["chown","-R",f"{owner}:{owner}",str(rp)],timeout=60)
        aliases_list=_safe_aliases(aliases,domain)
        # Keep the DB change transactional until config validation/reload succeeds.
        old=dict(w)
        c.execute("UPDATE websites SET domain=?,aliases=?,document_root=?,php_version=?,webserver=?,redirect_to=?,https_enforce=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(domain," ".join(aliases_list),str(rp),php,webserver,redirect_to,https,wid))
        c.commit(); neww=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone()
        try:
            _write_website_config(neww,a)
        except Exception:
            # Restore database row; generated config is removed/rebuilt from old values where possible.
            c.execute("UPDATE websites SET domain=?,aliases=?,document_root=?,php_version=?,webserver=?,redirect_to=?,https_enforce=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(old['domain'],old['aliases'],old['document_root'],old['php_version'],old['webserver'],old['redirect_to'],old['https_enforce'],wid)); c.commit()
            raise
        c.close(); audit("website.edit","website",wid,domain,f"Website settings updated; webserver={webserver}; php={php}"); flash(f"Website {domain} updated and configuration safely reloaded.")
    except sqlite3.IntegrityError:
        try: c.rollback(); c.close()
        except Exception: pass
        flash("A website with this primary domain already exists.")
    except Exception as exc:
        try: c.close()
        except Exception: pass
        flash("Website update failed: "+str(exc))
    return redirect("/admin/websites")

@app.route("/admin/websites/<int:wid>/toggle",methods=["POST"])
def toggle_website(wid):
    x=admin_required()
    if x:return x
    c=db(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone(); a=c.execute("SELECT * FROM accounts WHERE id=?",(w["account_id"],)).fetchone() if w else None
    if not w: c.close(); flash("Website not found."); return redirect("/admin/websites")
    try:
        new=0 if w["enabled"] else 1
        c.execute("UPDATE websites SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(new,wid))
        candidate=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone()
        _write_website_config(candidate,a)
        c.commit(); c.close(); audit("website.enable" if new else "website.disable","website",wid,candidate["domain"],"Web server configuration validated and reloaded"); flash("Website enabled." if new else "Website disabled.")
    except Exception as exc: c.rollback(); c.close(); flash("Website state change failed; previous database state preserved: "+str(exc))
    return redirect("/admin/websites")

@app.route("/admin/websites/<int:wid>/delete",methods=["POST"])
def delete_website(wid):
    x=admin_required()
    if x:return x
    c=db(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone()
    if not w: c.close(); flash("Website not found."); return redirect("/admin/websites")
    try:
        if w["webserver"]=="apache": path=Path("/etc/apache2/sites-available")/f"scpanel-{w['id']}-{w['domain']}.conf"; link=Path("/etc/apache2/sites-enabled")/path.name; unit="apache2"; test_cmd=["apache2ctl","configtest"]
        else: path=Path("/etc/nginx/sites-available")/f"scpanel-{w['id']}-{w['domain']}.conf"; link=Path("/etc/nginx/sites-enabled")/path.name; unit="nginx"; test_cmd=["nginx","-t"]
        backup=path.read_text(errors="replace") if path.exists() else None; was_enabled=link.is_symlink() or link.exists()
        link.unlink(missing_ok=True); path.unlink(missing_ok=True)
        r=run_system(test_cmd,timeout=30)
        if r.returncode:
            if backup is not None: path.write_text(backup,encoding="utf-8")
            if was_enabled and not link.exists(): link.symlink_to(path)
            run_system(["systemctl","reload",unit],timeout=30)
            raise RuntimeError("Configuration test failed after delete; website was restored: "+r.stdout.strip()[-800:])
        r=run_system(["systemctl","reload",unit],timeout=30)
        if r.returncode:
            if backup is not None: path.write_text(backup,encoding="utf-8")
            if was_enabled and not link.exists(): link.symlink_to(path)
            run_system(["systemctl","reload",unit],timeout=30)
            raise RuntimeError("Web server reload failed; website was restored: "+r.stdout.strip()[-800:])
        c.execute("DELETE FROM websites WHERE id=?",(wid,)); c.commit(); c.close(); audit("website.delete","website",wid,w["domain"],"Configuration validated, removed and server reloaded"); flash("Website deleted and configuration removed.")
    except Exception as exc: c.rollback(); c.close(); flash("Website delete failed: "+str(exc))
    return redirect("/admin/websites")

@app.route("/admin/websites/<int:wid>/ssl",methods=["POST"])
def website_ssl(wid):
    x=admin_required()
    if x:return x
    c=db(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone(); c.close()
    if not w: flash("Website not found."); return redirect("/admin/websites")
    try:
        from app.task_engine import create
        task=create("website",str(wid),"ssl",session.get("username","scroot"),request.remote_addr or "-",max_attempts=2)
        audit("website.ssl.task.create","website",wid,w["domain"],f"SSL task {task['id']} queued")
        flash(f"SSL certificate task queued for {w['domain']}. Track it in Task Center.")
    except Exception as exc: flash("Could not create SSL task: "+str(exc))
    return redirect("/admin/websites")

@app.route("/admin/websites/<int:wid>/logs")
def website_logs(wid):
    x=admin_required()
    if x:return x
    c=db(); w=c.execute("SELECT * FROM websites WHERE id=?",(wid,)).fetchone(); c.close()
    if not w: return jsonify(ok=False,error="Website not found."),404
    domain=w["domain"]; files=[Path("/var/log/nginx")/f"{domain}.access.log",Path("/var/log/nginx")/f"{domain}.error.log"]
    if w["webserver"]=="apache": files=[Path("/var/log/apache2")/f"{domain}.access.log",Path("/var/log/apache2")/f"{domain}.error.log"]
    return jsonify(domain=domain,logs={str(f):f.read_text(errors="replace")[-12000:] if f.exists() else "" for f in files})

def _ssl_info(domain):
    crt,key=website_cert(domain)
    info={'exists':False,'status':'missing','domain':domain,'certificate_path':str(crt),'key_path':str(key),'issued_at':'','expires_at':'','days_left':None,'issuer':'','subject':'','serial':''}
    if not crt.exists() or not key.exists(): return info
    try:
        r=run_system(['openssl','x509','-in',str(crt),'-noout','-startdate','-enddate','-issuer','-subject','-serial'],timeout=15)
        if r.returncode: raise RuntimeError(r.stdout.strip()[-500:])
        vals={}
        for line in r.stdout.splitlines():
            if '=' in line:
                k,v=line.split('=',1); vals[k.strip()]=v.strip()
        from datetime import datetime,timezone
        def parse(v):
            try:return datetime.strptime(v,'%b %d %H:%M:%S %Y %Z').replace(tzinfo=timezone.utc)
            except Exception:return None
        start=parse(vals.get('notBefore','')); end=parse(vals.get('notAfter','')); now=datetime.now(timezone.utc)
        info.update(exists=True,issued_at=start.isoformat() if start else vals.get('notBefore',''),expires_at=end.isoformat() if end else vals.get('notAfter',''),days_left=max(-99999,int((end-now).total_seconds()//86400)) if end else None,issuer=vals.get('issuer',''),subject=vals.get('subject',''),serial=vals.get('serial',''))
        if end and end < now: info['status']='expired'
        elif end and (end-now).total_seconds() <= 30*86400: info['status']='expiring'
        else: info['status']='valid'
    except Exception as exc:
        info['status']='error'; info['error']=str(exc)
    return info

def _ssl_rows(account_id=None):
    c=db(); q="SELECT s.*,w.account_id,w.domain AS website_domain,w.webserver,w.document_root,w.enabled AS website_enabled,a.username,a.home_dir FROM ssl_certificates s JOIN websites w ON w.id=s.website_id JOIN accounts a ON a.id=w.account_id"; args=[]
    if account_id is not None:q+=" WHERE w.account_id=?";args.append(account_id)
    rows=c.execute(q+" ORDER BY s.id DESC",args).fetchall();c.close();out=[]
    for r in rows:
        d=dict(r);d.update(_ssl_info(r['domain']));d['force_https']=int(r['force_https']);d['auto_renew']=int(r['auto_renew']);out.append(d)
    return out

def _ssl_find(wid, account_id=None):
    c=db();q="SELECT s.*,w.account_id,w.domain AS website_domain,w.webserver,w.document_root,w.enabled AS website_enabled,a.username,a.home_dir FROM ssl_certificates s JOIN websites w ON w.id=s.website_id JOIN accounts a ON a.id=w.account_id WHERE s.website_id=?";args=[wid]
    if account_id is not None:q+=" AND w.account_id=?";args.append(account_id)
    r=c.execute(q,args).fetchone();c.close();return r

@app.route('/admin/ssl')
def admin_ssl():
    x=admin_required()
    if x:return x
    c=db(); websites=c.execute("SELECT w.*,a.username FROM websites w JOIN accounts a ON a.id=w.account_id WHERE w.enabled=1 ORDER BY w.domain").fetchall(); c.close(); return render_template('admin/ssl.html',certificates=_ssl_rows(),websites=websites,certbot=bool(shutil.which('certbot')),admin_mode=True)

@app.route('/user/ssl')
def user_ssl():
    x=user_required()
    if x:return x
    c=db(); websites=c.execute("SELECT * FROM websites WHERE account_id=? AND enabled=1 ORDER BY domain",(session['account_id'],)).fetchall(); c.close()
    return render_template('admin/ssl.html',certificates=_ssl_rows(session['account_id']),websites=websites,certbot=bool(shutil.which('certbot')),admin_mode=False)

def _ssl_task_route(wid,operation,admin=True):
    x=admin_required() if admin else user_required()
    if x:return x
    row=_ssl_find(wid,None if admin else session['account_id']); dest='/admin/ssl' if admin else '/user/ssl'
    if not row: flash('Website/certificate not found or access denied.'); return redirect(dest)
    if operation in {'issue','renew'} and not shutil.which('certbot'): flash('Certbot is not installed. Install it from App Store first.'); return redirect(dest)
    try:
        from app.task_engine import create
        task=create('ssl',f'website:{wid}',operation,session.get('username','system'),request.remote_addr or '-',max_attempts=2)
        audit(f'ssl.{operation}.task.create','ssl_certificate',wid,row['domain'],f'SSL {operation} task {task["id"]} queued');flash(f"SSL {operation} task queued for {row['domain']}. Track it in Task Center.")
    except Exception as exc: flash('Could not queue SSL task: '+str(exc))
    return redirect(dest)

for _role,_admin in [('admin',True),('user',False)]:
    for _op in ('issue','renew','revoke','delete'):
        def _mk(wid,operation=_op,admin=_admin): return _ssl_task_route(wid,operation,admin)
        _mk.__name__=f'{_role}_ssl_{_op}'
        app.route(f'/{_role}/ssl/<int:wid>/{_op}',methods=['POST'],endpoint=_mk.__name__)(_mk)

def _ssl_toggle(wid,admin=True):
    x=admin_required() if admin else user_required()
    if x:return x
    row=_ssl_find(wid,None if admin else session['account_id']);dest='/admin/ssl' if admin else '/user/ssl'
    if not row:flash('Website/certificate not found or access denied.');return redirect(dest)
    new=not bool(row['force_https'])
    if new and _ssl_info(row['domain'])['status'] not in {'valid','expiring'}:flash('Force HTTPS requires a valid certificate. Issue/renew the certificate first.');return redirect(dest)
    try:
        c=db();c.execute('UPDATE websites SET https_enforce=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(int(new),wid));c.execute('UPDATE ssl_certificates SET force_https=?,updated_at=CURRENT_TIMESTAMP WHERE website_id=?',(int(new),wid));c.commit();w=c.execute('SELECT * FROM websites WHERE id=?',(wid,)).fetchone();a=c.execute('SELECT * FROM accounts WHERE id=?',(w['account_id'],)).fetchone();c.close();_write_website_config(w,a);audit('ssl.force_https','ssl_certificate',wid,row['domain'],f'enabled={new}');flash('Force HTTPS enabled.' if new else 'Force HTTPS disabled.')
    except Exception as exc:flash('Could not update Force HTTPS: '+str(exc))
    return redirect(dest)
for _role,_admin in [('admin',True),('user',False)]:
    def _mk_https(wid,admin=_admin):return _ssl_toggle(wid,admin)
    _mk_https.__name__=f'{_role}_ssl_https';app.route(f'/{_role}/ssl/<int:wid>/https',methods=['POST'],endpoint=_mk_https.__name__)(_mk_https)

for _role,_admin in [('admin',True),('user',False)]:
    def _mk_auto(wid,admin=_admin):
        x=admin_required() if admin else user_required()
        if x:return x
        row=_ssl_find(wid,None if admin else session['account_id']);dest='/admin/ssl' if admin else '/user/ssl'
        if not row:flash('Certificate not found.');return redirect(dest)
        enabled=1 if request.form.get('enabled')=='1' else 0;c=db();c.execute('UPDATE ssl_certificates SET auto_renew=?,updated_at=CURRENT_TIMESTAMP WHERE website_id=?',(enabled,wid));c.commit();c.close();audit('ssl.auto_renew','ssl_certificate',wid,row['domain'],f'enabled={enabled}');flash('Automatic renewal enabled.' if enabled else 'Automatic renewal disabled.');return redirect(dest)
    _mk_auto.__name__=f'{_role}_ssl_auto';app.route(f'/{_role}/ssl/<int:wid>/auto-renew',methods=['POST'],endpoint=_mk_auto.__name__)(_mk_auto)

@app.route('/api/ssl/status')
def api_ssl_status():
    if session.get('role')=='admin':rows=_ssl_rows()
    elif session.get('role')=='user':rows=_ssl_rows(session['account_id'])
    else:return jsonify(ok=False,error='Authentication required.'),401
    return jsonify(ok=True,certificates=rows,certbot=bool(shutil.which('certbot')))

@app.route("/admin/packages")
def packages():
    x=admin_required()
    if x:return x
    c=db(); rows=c.execute("SELECT * FROM packages ORDER BY id").fetchall(); c.close()
    return render_template("admin/packages.html",packages=rows)

@app.route("/admin/settings",methods=["GET","POST"])
def settings():
    x=admin_required()
    if x:return x
    if request.method=="POST":
        pw=request.form.get("password",""); email=request.form.get("email","")
        timezone=request.form.get("timezone","UTC").strip() or "UTC"
        hostname=request.form.get("hostname","").strip()
        if hostname and not re.fullmatch(r"[A-Za-z0-9][A-Za-z0-9.-]{0,252}",hostname): flash("Invalid hostname."); hostname=get_setting("hostname",platform.node())
        retention=request.form.get("log_retention_days","30").strip()
        if not re.fullmatch(r"(?:[1-9][0-9]?|[12][0-9]{2}|365)", retention):
            flash("Log retention must be between 1 and 365 days.")
            retention="30"
        if pw:
            # installer-generated plain password is replaced by new plain password for compatibility
            lines={}
            for line in ADMIN_ENV.read_text().splitlines():
                if "=" in line: k,v=line.split("=",1); lines[k]=v
            lines["SCPANEL_ADMIN_PASSWORD"]=pw
            ADMIN_ENV.write_text("\n".join(f"{k}={v}" for k,v in lines.items())+"\n")
        c=db(); c.execute("INSERT OR REPLACE INTO settings(key,value) VALUES('admin_email',?)",(email,)); c.execute("INSERT OR REPLACE INTO settings(key,value) VALUES('timezone',?)",(timezone,)); c.execute("INSERT OR REPLACE INTO settings(key,value) VALUES('log_retention_days',?)",(retention,)); c.execute("INSERT OR REPLACE INTO settings(key,value) VALUES('hostname',?)",(hostname or platform.node(),)); c.commit(); c.close()
        try:
            cp=CFG/'config.json'; cfg=json.loads(cp.read_text()) if cp.exists() else {}
            cfg['timezone']=timezone; cfg['log_retention_days']=int(retention)
            cp.write_text(json.dumps(cfg,separators=(',',':')))
        except Exception:
            pass
        flash("Admin and operations settings updated.")
    c=db(); rows={r['key']:r['value'] for r in c.execute("SELECT key,value FROM settings WHERE key IN ('admin_email','timezone','log_retention_days','hostname','admin_http_port','admin_https_port')")}; c.close()
    return render_template("admin/settings.html",email=rows.get('admin_email',''),timezone=rows.get('timezone','UTC'),log_retention_days=rows.get('log_retention_days','30'),hostname=rows.get('hostname',platform.node()),admin_http_port=rows.get('admin_http_port','2086'),admin_https_port=rows.get('admin_https_port','2087'))

@app.route("/admin/services")
def services_page():
    x=admin_required()
    if x:return x
    return render_template("admin/services.html",services=_cached_services())

@app.route("/api/services")
def api_services():
    x=admin_required()
    if x:return x
    return jsonify(services=_cached_services())

@app.route("/api/services/<key>/action",methods=["POST"])
def api_service_action(key):
    x=admin_required()
    if x:return x
    op=(request.json or {}).get("operation","") if request.is_json else request.form.get("operation","")
    if op not in {"start","stop","restart","enable","disable","reload","status","version","health","logs"}: return jsonify(ok=False,error="Unsupported service action."),400
    from app.providers.service_engine import action
    try:
        result=action(key,op)
        audit("service."+op,"service",None,key,"Service action completed")
        return jsonify(ok=True,result=result)
    except Exception as exc:return jsonify(ok=False,error=str(exc)),400

@app.route("/api/services/<key>/php-settings",methods=["POST"])
def api_php_settings(key):
    x=admin_required()
    if x:return x
    try:
        from app.providers.service_engine import save_php_settings
        result=save_php_settings(key,(request.json or {}).get("settings",{}))
        audit("service.php.settings.save","service",None,key,result.get("message","PHP settings saved"))
        return jsonify(ok=True,**result)
    except Exception as exc:return jsonify(ok=False,error=str(exc)),400

@app.route("/api/services/<key>/fpm-settings",methods=["POST"])
def api_fpm_settings(key):
    x=admin_required()
    if x:return x
    try:
        from app.providers.service_engine import save_fpm_settings
        result=save_fpm_settings(key,(request.json or {}).get("settings",{}))
        audit("service.fpm.settings.save","service",None,key,result.get("message","FPM settings saved"))
        return jsonify(ok=True,**result)
    except Exception as exc:return jsonify(ok=False,error=str(exc)),400

@app.route("/api/services/<key>/config",methods=["GET","POST"])
def api_service_config(key):
    x=admin_required()
    if x:return x
    from app.providers.service_engine import config_info, save_config
    try:
        if request.method=="GET": return jsonify(ok=True,**config_info(key))
        content=(request.json or {}).get("content","") if request.is_json else request.form.get("content","")
        result=save_config(key,content)
        audit("service.config.save","service",None,key,result.get("message","Configuration saved"))
        return jsonify(ok=True,**result)
    except Exception as exc:return jsonify(ok=False,error=str(exc)),400

@app.route("/admin/services/<path:key>")
def service_detail(key):
    x=admin_required()
    if x:return x
    try:
        from app.providers.service_engine import _state
        svc=_state(key)
    except Exception as exc:
        flash(str(exc)); return redirect("/admin/services")
    return render_template("admin/service_detail.html",service=svc,is_php=key.startswith("php-"))

@app.route("/admin/store")
def app_store():
    x=admin_required()
    if x:return x
    return render_template("admin/store.html",apps=_cached_services())

@app.route("/admin/store/<key>/<operation>",methods=["POST"])
def app_store_action(key,operation):
    x=admin_required()
    if x:return x
    from app.providers.service_engine import SPECS
    allowed={"install","uninstall","reinstall","start","stop","restart","enable","disable"}
    if key not in SPECS or operation not in allowed:
        flash("Unsupported App Store operation."); return redirect("/admin/store")
    try:
        from app.task_engine import create
        task=create("appstore",key,operation,session.get("username","scroot"),request.remote_addr or "-",max_attempts=2,requested_version=request.form.get("version", ""))
        audit("appstore.task.create","app",None,key,f"Task {task['id']} queued: {operation}")
        flash(f"{SPECS[key].name} {operation} task queued.")
    except Exception as exc:
        flash(f"Could not create task: {exc}")
    return redirect("/admin/store")

@app.route("/api/store")
def api_store():
    x=admin_required()
    if x:return x
    return jsonify(apps=_cached_services())

@app.route("/api/store/<key>/<operation>",methods=["POST"])
def api_store_action(key,operation):
    x=admin_required()
    if x:return x
    from app.providers.service_engine import SPECS
    if key not in SPECS or operation not in {"install","uninstall","reinstall","start","stop","restart","enable","disable"}:
        return jsonify(ok=False,error="Unsupported operation."),400
    try:
        from app.task_engine import create
        task=create("appstore",key,operation,session.get("username","scroot"),request.remote_addr or "-",max_attempts=2,requested_version=(request.json or {}).get("version","") if request.is_json else request.form.get("version",""))
        audit("appstore.task.create","app",None,key,f"Task {task['id']} queued: {operation}")
        return jsonify(ok=True,task=task)
    except Exception as exc:
        return jsonify(ok=False,error=str(exc)),500

@app.route("/api/store/<key>/prepare",methods=["POST"])
def api_store_prepare(key):
    x=admin_required()
    if x:return x
    try:
        from app.providers.service_engine import _php_version_from_key, _ensure_php_repository
        v=_php_version_from_key(key)
        if not v: return jsonify(ok=False,error="Repository preparation is only available for PHP runtimes."),400
        _ensure_php_repository(v)
        from app.providers.service_engine import available_versions
        return jsonify(ok=True,versions=available_versions(key),message=f"PHP {v} repository verified.")
    except Exception as exc:return jsonify(ok=False,error=str(exc)),400

@app.route("/api/store/<key>/versions")
def api_store_versions(key):
    x=admin_required()
    if x:return x
    from app.providers.service_engine import SPECS, available_versions
    if key not in SPECS:return jsonify(ok=False,error="Unknown application."),404
    try:return jsonify(ok=True,versions=available_versions(key))
    except Exception as exc:return jsonify(ok=False,error=str(exc)),500

@app.route("/admin/tasks")
def tasks_page():
    x=admin_required()
    if x:return x
    from app.task_engine import list_tasks
    return render_template("admin/tasks.html",tasks=list_tasks(200))

@app.route("/api/tasks")
def api_tasks():
    x=admin_required()
    if x:return x
    from app.task_engine import list_tasks
    return jsonify(tasks=list_tasks(200))

@app.route("/api/tasks/<task_id>")
def api_task(task_id):
    x=admin_required()
    if x:return x
    from app.task_engine import get
    task=get(task_id)
    if not task:return jsonify(ok=False,error="Task not found."),404
    return jsonify(task=task)

@app.route("/api/tasks/<task_id>/live")
def api_task_live(task_id):
    x=admin_required()
    if x:return x
    from app.task_engine import get, live_output
    task=get(task_id)
    if not task:return jsonify(ok=False,error="Task not found."),404
    task["live_output"]=live_output(task_id)
    return jsonify(ok=True,task=task)

@app.route("/api/tasks/<task_id>/cancel",methods=["POST"])
def api_task_cancel(task_id):
    x=admin_required()
    if x:return x
    from app.task_engine import cancel
    try:
        return jsonify(ok=True,task=cancel(task_id,session.get("username","scroot"),request.remote_addr or "-"))
    except Exception as exc:
        return jsonify(ok=False,error=str(exc)),400

@app.route("/api/tasks/<task_id>/delete",methods=["POST"])
def api_task_delete(task_id):
    x=admin_required()
    if x:return x
    from app.task_engine import get
    task=get(task_id)
    if not task:return jsonify(ok=False,error="Task not found."),404
    if task.get("status") in {"running","queued"}: return jsonify(ok=False,error="Stop/cancel the active task before deleting it."),400
    c=db(); c.execute("DELETE FROM tasks WHERE id=?",(task_id,)); c.commit(); c.close()
    audit("task.delete","task",None,task_id,"Task history deleted")
    return jsonify(ok=True)

@app.route("/api/tasks/<task_id>/retry",methods=["POST"])
def api_task_retry(task_id):
    x=admin_required()
    if x:return x
    from app.task_engine import retry
    try:
        return jsonify(ok=True,task=retry(task_id,session.get("username","scroot"),request.remote_addr or "-"))
    except Exception as exc:
        return jsonify(ok=False,error=str(exc)),400

@app.route("/admin/store/<key>/logs")
def app_store_logs(key):
    x=admin_required()
    if x:return x
    from app.providers.service_engine import action
    try: result=action(key,"logs")
    except Exception as exc: abort(404,str(exc))
    return jsonify(result)

def _read_meminfo():
    data={}
    try:
        for line in Path("/proc/meminfo").read_text().splitlines():
            k,v=line.split(":",1); data[k.strip()]=int(v.strip().split()[0])*1024
    except Exception: pass
    total=data.get("MemTotal",0); avail=data.get("MemAvailable",data.get("MemFree",0))
    used=max(0,total-avail)
    return {"total":total,"available":avail,"used":used,"percent":round(used*100/total,1) if total else 0,"swap_total":data.get("SwapTotal",0),"swap_free":data.get("SwapFree",0)}

def _read_cpu():
    try:
        vals=Path("/proc/stat").read_text().splitlines()[0].split()[1:]
        nums=list(map(int,vals[:8])); idle=nums[3]+(nums[4] if len(nums)>4 else 0); total=sum(nums)
        prev=Path("/var/lib/scpanel/.cpu_prev"); now=f"{total} {idle}"
        old=prev.read_text().split() if prev.exists() else []
        prev.parent.mkdir(parents=True,exist_ok=True); prev.write_text(now)
        if len(old)==2:
            ot,oi=map(int,old); dt=max(1,total-ot); di=max(0,idle-oi); return round((1-di/dt)*100,1)
    except Exception: pass
    return 0.0

def _net_io():
    rx=tx=0; interfaces=[]
    try:
        for line in Path("/proc/net/dev").read_text().splitlines()[2:]:
            if ":" not in line: continue
            iface,rest=line.split(":",1); iface=iface.strip()
            if iface=="lo": continue
            f=rest.split(); irx,itx=int(f[0]),int(f[8]); rx+=irx; tx+=itx
            interfaces.append({"name":iface,"rx":irx,"tx":itx})
    except Exception: pass
    return {"rx":rx,"tx":tx,"interfaces":interfaces}

def _disk_io():
    read_bytes=write_bytes=0
    try:
        for line in Path("/proc/diskstats").read_text().splitlines():
            f=line.split()
            if len(f)<14: continue
            name=f[2]
            if not re.match(r"^(sd[a-z]+|vd[a-z]+|xvd[a-z]+|nvme[0-9]+n[0-9]+)$",name): continue
            read_bytes += int(f[5])*512
            write_bytes += int(f[9])*512
    except Exception: pass
    return {"read_bytes":read_bytes,"write_bytes":write_bytes}

def _disk_usage():
    u=shutil.disk_usage("/")
    return {"total":u.total,"used":u.used,"free":u.free,"percent":round(u.used*100/u.total,1) if u.total else 0}

def _processes(limit=20):
    rows=[]
    for d in Path("/proc").iterdir():
        if not d.name.isdigit(): continue
        try:
            stat=(d/"stat").read_text(); name=stat.split("(",1)[1].rsplit(")",1)[0]
            fields=stat.rsplit(")",1)[1].split(); state=fields[0]; rss=int((d/"statm").read_text().split()[1])*4096
            rows.append({"pid":int(d.name),"name":name,"state":state,"memory":rss})
        except Exception: continue
    return sorted(rows,key=lambda x:x["memory"],reverse=True)[:limit]

@app.route("/admin/monitor")
def monitor_page():
    x=admin_required()
    if x:return x
    return render_template("admin/monitor.html")

@app.route("/api/health")
def api_health():
    checks={"database":False,"release":False,"admin_service":False,"user_service":False}
    try:
        c=db(); c.execute("SELECT 1").fetchone(); c.close(); checks["database"]=True
    except Exception: pass
    try: checks["release"]=(APP/"RELEASE_VERSION").read_text().strip()==VERSION
    except Exception: pass
    for key,unit in (("admin_service","scpanel-admin"),("user_service","scpanel-user")):
        if shutil.which("systemctl"):
            r=run_system(["systemctl","is-active",unit],timeout=10); checks[key]=(r.returncode==0 and r.stdout.strip()=="active")
    ok=all(checks.values())
    return jsonify(ok=ok,version=VERSION,checks=checks),200 if ok else 503

def _monitor_snapshot(force=False):
    global _monitor_cache
    now=time.time()
    with _monitor_lock:
        if not force and _monitor_cache.get("data") is not None and now-_monitor_cache.get("ts",0)<2.5:
            return _monitor_cache["data"]
        mem=_read_meminfo(); disk=_disk_usage(); net=_net_io(); load=os.getloadavg()
        inventory=_service_health_snapshot()
        known_apps=[]
        try:
            from app.providers.service_engine import SPECS
            known_apps=[x for x in inventory if x.get("key") in SPECS and x.get("installed")]
        except Exception:
            known_apps=[]
        dio=_disk_io()
        swap_total=mem.get("swap_total",0); swap_free=mem.get("swap_free",0); mem["swap_used"]=max(0,swap_total-swap_free); mem["swap_percent"]=round(mem["swap_used"]*100/swap_total,1) if swap_total else 0
        try:
            prevp=DATA/".monitor_prev.json"; prev=json.loads(prevp.read_text()) if prevp.exists() else {}
        except Exception: prev={}
        dt=max(0.5,now-float(prev.get("ts",now)))
        rates={"read_bps":max(0,dio["read_bytes"]-int(prev.get("read_bytes",dio["read_bytes"])))/dt,"write_bps":max(0,dio["write_bytes"]-int(prev.get("write_bytes",dio["write_bytes"])))/dt,"rx_bps":max(0,net["rx"]-int(prev.get("rx",net["rx"])))/dt,"tx_bps":max(0,net["tx"]-int(prev.get("tx",net["tx"])))/dt}
        try: DATA.mkdir(parents=True,exist_ok=True); prevp.write_text(json.dumps({"ts":now,"read_bytes":dio["read_bytes"],"write_bytes":dio["write_bytes"],"rx":net["rx"],"tx":net["tx"]}))
        except Exception: pass
        data={"timestamp":now,"timestamp_utc":time.strftime("%Y-%m-%dT%H:%M:%SZ",time.gmtime()),"cpu_percent":_read_cpu(),"memory":mem,"disk":disk,"disk_io":dio,"rates":rates,"network":net,"load":{"1m":load[0],"5m":load[1],"15m":load[2]},"uptime":Path("/proc/uptime").read_text().split()[0],"processes":_processes(),"services":inventory,"apps":known_apps}
        _monitor_cache={"ts":now,"data":data}
        return data

@app.route("/api/monitor")
def api_monitor():
    x=admin_required()
    if x:return x
    return jsonify(_monitor_snapshot())

_service_cache={"ts":0.0,"data":[]}
_service_lock=threading.RLock()

def _service_health_snapshot():
    """Return cached installed-service health for the monitoring dashboard."""
    try:
        with _service_lock:
            data=_service_cache.get("data") or []
            return [{"key":s.get("key"),"name":s.get("name"),"installed":bool(s.get("installed")),"running":bool(s.get("running")),"enabled":bool(s.get("enabled")),"version":s.get("version",""),"unit":s.get("unit","")} for s in data]
    except Exception:
        return []


def _cached_services(ttl=8.0, force=False):
    global _service_cache
    now=time.time()
    with _service_lock:
        if not force and _service_cache["data"] and now-_service_cache["ts"] < ttl:
            return _service_cache["data"]
        from app.providers.service_engine import catalog
        # catalog() itself can use the persistent on-disk inventory. The request
        # path therefore remains fast after boot while the background refresher
        # keeps the inventory current.
        data=catalog(force=force)
        _service_cache={"ts":now,"data":data}
        return data

def _refresh_services_background():
    try:
        from app.providers.service_engine import background_refresh
        background_refresh()
        with _service_lock:
            global _service_cache
            from app.providers.service_engine import catalog
            _service_cache={"ts":time.time(),"data":catalog()}
    except Exception:
        pass

_service_refresh_started=False
try:
    if not _service_refresh_started:
        _service_refresh_started=True
        threading.Thread(target=_refresh_services_background,daemon=True,name="scpanel-service-refresh").start()
except Exception:
    pass

@app.route("/api/monitor/services")
def api_monitor_services():
    x=admin_required()
    if x:return x
    return jsonify(services=_cached_services())

@app.route("/api/version")
def api_version(): return jsonify(version=VERSION,admin_port=int(get_setting("admin_http_port","2086")),admin_https_port=int(get_setting("admin_https_port","2087")),user_port=2083)


DNS_TYPES={'A','AAAA','CNAME','MX','TXT','NS','SRV','CAA'}

def _dns_zone_path(zone):
    return Path('/etc/bind/zones')/f'db.{zone}'

def _dns_serial(zone_row):
    try:
        return int(zone_row['serial'])
    except Exception:
        return int(time.strftime('%Y%m%d'))*100 + 1

def _ensure_zone(c, domain_id, zone):
    z=c.execute('SELECT * FROM dns_zones WHERE domain_id=?',(domain_id,)).fetchone()
    if not z:
        c.execute('INSERT INTO dns_zones(domain_id,zone,serial) VALUES(?,?,?)',(domain_id,zone,int(time.strftime('%Y%m%d'))*100+1))
        c.commit(); z=c.execute('SELECT * FROM dns_zones WHERE domain_id=?',(domain_id,)).fetchone()
    return z

def _valid_dns_value(rtype,value):
    value=value.strip()
    if not value or len(value)>1024: return False
    if rtype in {'A','AAAA'}:
        import ipaddress
        try: ipaddress.ip_address(value); return True
        except Exception: return False
    if rtype in {'CNAME','NS','MX'}: return valid_domain(value.rstrip('.')) or (rtype=='MX' and re.fullmatch(r'\d+\s+[A-Za-z0-9_.-]+\.?',value)) is not None
    if rtype=='SRV': return re.fullmatch(r'\d+\s+\d+\s+\d+\s+[A-Za-z0-9_.-]+\.?',value) is not None
    if rtype=='CAA': return bool(re.fullmatch(r'\d+\s+"[A-Za-z0-9_-]+"\s+".*"',value))
    return True

def _render_zone(c,z):
    domain=z['zone']; serial=_dns_serial(z)
    rows=c.execute('SELECT * FROM dns_records WHERE zone_id=? AND enabled=1 ORDER BY id',(z['id'],)).fetchall()
    lines=[f'$TTL 3600',f'@ IN SOA ns1.{domain}. hostmaster.{domain}. (',f'  {serial}',f'  3600',f'  900',f'  1209600',f'  3600',f')',f'@ IN NS ns1.{domain}.']
    for r in rows:
        name=r['name'] or '@'; typ=r['type']; val=r['value']; ttl=r['ttl'] or 3600
        if typ=='MX' and not re.match(r'^\d+\s',val): val=f"{r['priority'] or 10} {val}"
        lines.append(f'{name} {ttl} IN {typ} {val}')
    return '\n'.join(lines)+'\n'

def _ensure_bind_zone_include(domain):
    if not shutil.which('named-checkconf') or not Path('/etc/bind').exists(): return
    inc=Path('/etc/bind/scpanel-zones.conf')
    line=f'zone \"{domain}\" {{ type master; file \"/etc/bind/zones/db.{domain}\"; }};'
    existing=inc.read_text(errors='replace') if inc.exists() else ''
    if line not in existing:
        inc.parent.mkdir(parents=True,exist_ok=True); inc.write_text((existing.rstrip()+'\n' if existing.strip() else '')+line+'\n',encoding='utf-8')
    local=Path('/etc/bind/named.conf.local')
    marker='include \"/etc/bind/scpanel-zones.conf\";'
    text=local.read_text(errors='replace') if local.exists() else ''
    if marker not in text:
        local.parent.mkdir(parents=True,exist_ok=True); local.write_text(text.rstrip()+'\n'+marker+'\n',encoding='utf-8')

def _remove_bind_zone_include(domain):
    inc=Path('/etc/bind/scpanel-zones.conf')
    if inc.exists():
        lines=[x for x in inc.read_text(errors='replace').splitlines() if f'zone \"{domain}\"' not in x]
        inc.write_text(('\n'.join(lines)+'\n') if lines else '',encoding='utf-8')

def _apply_dns_zone(c,zone_id):
    z=c.execute('SELECT * FROM dns_zones WHERE id=?',(zone_id,)).fetchone()
    if not z: raise RuntimeError('DNS zone not found.')
    if not z['enabled']: return {'validated':True,'applied':False,'message':'Zone is disabled.'}
    p=_dns_zone_path(z['zone']); p.parent.mkdir(parents=True,exist_ok=True)
    _ensure_bind_zone_include(z['zone'])
    old=p.read_text(errors='replace') if p.exists() else None
    p.write_text(_render_zone(c,z),encoding='utf-8')
    if shutil.which('named-checkzone'):
        r=run_system(['named-checkzone',z['zone'],str(p)],30)
        if r.returncode:
            if old is None:p.unlink(missing_ok=True)
            else:p.write_text(old,encoding='utf-8')
            raise RuntimeError('DNS zone validation failed: '+r.stdout.strip()[-1200:])
    if shutil.which('rndc'):
        r=run_system(['rndc','reload',z['zone']],30)
        if r.returncode:
            # Keep file but do not pretend runtime reload succeeded.
            raise RuntimeError('DNS zone saved but reload failed: '+r.stdout.strip()[-1200:])
    return {'validated':True,'applied':True,'path':str(p),'message':'Zone validated and applied.'}

@app.route('/admin/domains')
def domains_page():
    x=admin_required()
    if x:return x
    c=db(); rows=c.execute("SELECT d.*,a.username FROM domains d LEFT JOIN accounts a ON a.id=d.account_id ORDER BY d.id DESC").fetchall(); accounts=c.execute("SELECT id,username,domain FROM accounts WHERE enabled=1 AND suspended=0 ORDER BY username").fetchall(); c.close()
    return render_template('admin/domains.html',domains=rows,accounts=accounts)

@app.route('/admin/domains/create',methods=['POST'])
def create_domain():
    x=admin_required()
    if x:return x
    domain=request.form.get('domain','').strip().lower().rstrip('.'); parent=request.form.get('parent_domain','').strip().lower().rstrip('.'); kind=request.form.get('kind','primary'); target=request.form.get('target','').strip(); redirect_to=request.form.get('redirect_to','').strip().lower().rstrip('.')
    if not valid_domain(domain): flash('Invalid domain or subdomain.'); return redirect('/admin/domains')
    if kind not in {'primary','alias','subdomain','redirect'}: kind='primary'
    if kind=='subdomain' and (not parent or not valid_domain(parent) or not domain.endswith('.'+parent)): flash('Subdomain must belong to a valid parent domain.'); return redirect('/admin/domains')
    if redirect_to and not valid_domain(redirect_to): flash('Invalid redirect target.'); return redirect('/admin/domains')
    c=db()
    try:
        aid=request.form.get('account_id') or None
        c.execute('INSERT INTO domains(account_id,domain,parent_domain,kind,target,redirect_to) VALUES(?,?,?,?,?,?)',(int(aid) if aid else None,domain,parent,kind,target,redirect_to)); did=c.execute('SELECT last_insert_rowid() id').fetchone()['id']; _ensure_zone(c,did,domain); c.commit(); audit('domain.create','domain',did,domain,f'kind={kind}'); flash(f'Domain {domain} added.')
    except sqlite3.IntegrityError: c.rollback(); flash('Domain already exists.')
    finally: c.close()
    return redirect('/admin/domains')

@app.route('/admin/domains/<int:did>/toggle',methods=['POST'])
def toggle_domain(did):
    x=admin_required()
    if x:return x
    c=db(); d=c.execute('SELECT * FROM domains WHERE id=?',(did,)).fetchone()
    if not d: c.close(); flash('Domain not found.'); return redirect('/admin/domains')
    new=0 if d['enabled'] else 1; c.execute('UPDATE domains SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(new,did)); c.execute('UPDATE dns_zones SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE domain_id=?',(new,did)); c.commit(); c.close(); audit('domain.toggle','domain',did,d['domain'],f'enabled={new}'); flash('Domain enabled.' if new else 'Domain disabled.'); return redirect('/admin/domains')

@app.route('/admin/domains/<int:did>/delete',methods=['POST'])
def delete_domain(did):
    x=admin_required()
    if x:return x
    c=db(); d=c.execute('SELECT * FROM domains WHERE id=?',(did,)).fetchone()
    if not d: c.close(); flash('Domain not found.'); return redirect('/admin/domains')
    p=_dns_zone_path(d['domain']); _remove_bind_zone_include(d['domain'])
    c.execute('DELETE FROM domains WHERE id=?',(did,)); c.commit(); c.close(); p.unlink(missing_ok=True); audit('domain.delete','domain',did,d['domain'],'Domain and DNS zone removed'); flash('Domain deleted.'); return redirect('/admin/domains')

@app.route('/admin/dns')
def dns_page():
    x=admin_required()
    if x:return x
    c=db(); zones=c.execute("SELECT z.*,d.domain FROM dns_zones z JOIN domains d ON d.id=z.domain_id ORDER BY d.domain").fetchall(); records={z['id']:c.execute('SELECT * FROM dns_records WHERE zone_id=? ORDER BY id',(z['id'],)).fetchall() for z in zones}; c.close()
    return render_template('admin/dns.html',zones=zones,records=records,dns_types=sorted(DNS_TYPES))

@app.route('/admin/dns/<int:zone_id>/records/create',methods=['POST'])
def dns_record_create(zone_id):
    x=admin_required()
    if x:return x
    name=request.form.get('name','@').strip(); typ=request.form.get('type','A').upper(); value=request.form.get('value','').strip(); ttl=int(request.form.get('ttl','3600') or 3600); priority=int(request.form.get('priority','0') or 0)
    if typ not in DNS_TYPES or not (60<=ttl<=86400) or not _valid_dns_value(typ,value): flash('Invalid DNS record.'); return redirect('/admin/dns')
    c=db(); z=c.execute('SELECT * FROM dns_zones WHERE id=?',(zone_id,)).fetchone()
    if not z: c.close(); flash('Zone not found.'); return redirect('/admin/dns')
    c.execute('INSERT INTO dns_records(zone_id,name,type,value,ttl,priority) VALUES(?,?,?,?,?,?)',(zone_id,name,typ,value,ttl,priority)); c.execute('UPDATE dns_zones SET serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(zone_id,));
    try: result=_apply_dns_zone(c,zone_id); c.commit(); flash(result['message'])
    except Exception as exc: c.rollback(); flash(str(exc))
    c.close(); return redirect('/admin/dns')

@app.route('/admin/dns/records/<int:rid>/edit',methods=['POST'])
def dns_record_edit(rid):
    x=admin_required()
    if x:return x
    name=request.form.get('name','@').strip(); typ=request.form.get('type','A').upper(); value=request.form.get('value','').strip(); ttl=int(request.form.get('ttl','3600') or 3600); priority=int(request.form.get('priority','0') or 0)
    if typ not in DNS_TYPES or not (60<=ttl<=86400) or not _valid_dns_value(typ,value): flash('Invalid DNS record.'); return redirect('/admin/dns')
    c=db(); r=c.execute('SELECT * FROM dns_records WHERE id=?',(rid,)).fetchone()
    if not r: c.close(); flash('DNS record not found.'); return redirect('/admin/dns')
    zone_id=r['zone_id']; c.execute('UPDATE dns_records SET name=?,type=?,value=?,ttl=?,priority=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(name,typ,value,ttl,priority,rid)); c.execute('UPDATE dns_zones SET serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(zone_id,))
    try: result=_apply_dns_zone(c,zone_id); c.commit(); flash(result['message'])
    except Exception as exc: c.rollback(); flash(str(exc))
    c.close(); return redirect('/admin/dns')

@app.route('/admin/dns/<int:zone_id>/import',methods=['POST'])
def dns_import(zone_id):
    x=admin_required()
    if x:return x
    raw=request.form.get('zone_text','')
    if len(raw)>200000: flash('Zone import is too large.'); return redirect('/admin/dns')
    c=db(); z=c.execute('SELECT * FROM dns_zones WHERE id=?',(zone_id,)).fetchone()
    if not z: c.close(); flash('Zone not found.'); return redirect('/admin/dns')
    parsed=[]
    for line in raw.splitlines():
        line=line.strip()
        if not line or line.startswith(';') or line.startswith('$') or ' SOA ' in line : continue
        parts=line.split()
        if len(parts)<5: continue
        name,ttl,_,typ=parts[:4]; value=' '.join(parts[4:])
        if typ not in DNS_TYPES or not _valid_dns_value(typ,value): continue
        parsed.append((name.rstrip('.'),typ,value,int(ttl) if ttl.isdigit() else 3600,0))
    if not parsed: c.close(); flash('No valid supported DNS records found in import.'); return redirect('/admin/dns')
    c.execute('DELETE FROM dns_records WHERE zone_id=?',(zone_id,))
    c.executemany('INSERT INTO dns_records(zone_id,name,type,value,ttl,priority) VALUES(?,?,?,?,?,?)',[(zone_id,*r) for r in parsed]); c.execute('UPDATE dns_zones SET serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(zone_id,))
    try: result=_apply_dns_zone(c,zone_id); c.commit(); flash(f'Imported {len(parsed)} records. '+result['message'])
    except Exception as exc: c.rollback(); flash(str(exc))
    c.close(); return redirect('/admin/dns')

@app.route('/admin/dns/records/<int:rid>/delete',methods=['POST'])
def dns_record_delete(rid):
    x=admin_required()
    if x:return x
    c=db(); r=c.execute('SELECT * FROM dns_records WHERE id=?',(rid,)).fetchone()
    if not r: c.close(); flash('DNS record not found.'); return redirect('/admin/dns')
    zone_id=r['zone_id']; c.execute('DELETE FROM dns_records WHERE id=?',(rid,)); c.execute('UPDATE dns_zones SET serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(zone_id,))
    try: result=_apply_dns_zone(c,zone_id); c.commit(); flash(result['message'])
    except Exception as exc: c.rollback(); flash(str(exc))
    c.close(); return redirect('/admin/dns')

@app.route('/admin/dns/<int:zone_id>/toggle',methods=['POST'])
def dns_zone_toggle(zone_id):
    x=admin_required()
    if x:return x
    c=db(); z=c.execute('SELECT * FROM dns_zones WHERE id=?',(zone_id,)).fetchone()
    if not z: c.close(); flash('Zone not found.'); return redirect('/admin/dns')
    new=0 if z['enabled'] else 1; c.execute('UPDATE dns_zones SET enabled=?,serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(new,zone_id));
    try:
        if new: _apply_dns_zone(c,zone_id)
        else: _dns_zone_path(z['zone']).unlink(missing_ok=True)
        c.commit(); flash('Zone enabled.' if new else 'Zone disabled.')
    except Exception as exc: c.rollback(); flash(str(exc))
    c.close(); return redirect('/admin/dns')

@app.route('/admin/dns/<int:zone_id>/export')
def dns_export(zone_id):
    x=admin_required()
    if x:return x
    c=db(); z=c.execute('SELECT * FROM dns_zones WHERE id=?',(zone_id,)).fetchone()
    if not z: c.close(); abort(404)
    text=_render_zone(c,z); c.close(); return app.response_class(text,mimetype='text/plain',headers={'Content-Disposition':f'attachment; filename=db.{z["zone"]}'})

DB_PRIVS=["SELECT","INSERT","UPDATE","DELETE","CREATE","DROP","ALTER","INDEX","CREATE VIEW","SHOW VIEW","TRIGGER","REFERENCES","EXECUTE","LOCK TABLES"]

def _sync_database_inventory(account_id=None):
    """Discover real non-system databases and converge them into panel inventory.
    User sync is prefix-scoped and never claims another account's database.
    """
    try: items=DatabaseProvider.list_databases()
    except Exception: return []
    c=db()
    if account_id:
        account=c.execute("SELECT username,system_user FROM accounts WHERE id=?",(account_id,)).fetchone()
        prefix=((account["system_user"] or account["username"])+'_') if account else ''
        items=[x for x in items if prefix and x["name"].startswith(prefix)]
    existing={r["name"]:r for r in c.execute("SELECT * FROM database_inventory").fetchall()}
    for item in items:
        if item["name"] not in existing:
            c.execute("INSERT OR IGNORE INTO database_inventory(provider,name,account_id) VALUES('mysql',?,?)",(item["name"],account_id))
        elif account_id and existing[item["name"]]["account_id"] not in (None,account_id):
            continue
    c.commit(); c.close(); return items

def _db_rows(account_id=None, admin=False):
    try: real=DatabaseProvider.list_databases()
    except Exception: real=[]
    usage={x["name"]:x for x in real}
    c=db()
    if admin: rows=c.execute("SELECT * FROM database_inventory ORDER BY name").fetchall()
    else: rows=c.execute("SELECT * FROM database_inventory WHERE account_id=? ORDER BY name",(account_id,)).fetchall()
    out=[]
    for r in rows:
        x=dict(r); meta=usage.get(r["name"],{})
        x.update({"exists":bool(meta),"size_human":meta.get("size_human","—"),"host":meta.get("host","localhost"),"port":meta.get("port",3306)})
        out.append(x)
    c.close(); return out

def _ensure_db_inventory(name, account_id=None):
    c=db(); c.execute("INSERT OR IGNORE INTO database_inventory(provider,name,account_id) VALUES('mysql',?,?)",(name,account_id)); c.commit(); c.close()

def _db_name_for_user(requested, account):
    requested=(requested or '').strip()
    if not requested: raise DatabaseProviderError('Database name is required.')
    if not re.fullmatch(r'[A-Za-z0-9_$-]{1,48}', requested): raise DatabaseProviderError('Database name may contain only letters, numbers, _, -, $.')
    if account:
        prefix=(account["system_user"] or account["username"] or 'account')+'_'
        if not requested.startswith(prefix): requested=prefix+requested
    return requested[:64]

def _phpmyadmin_installed():
    try:
        from app.providers.service_engine import detect
        return bool(detect('phpmyadmin').get('installed'))
    except Exception:
        return False

def _web_endpoint_url():
    """Return the browser-reachable HTTP(S) base for phpMyAdmin.
    SCpanel itself stays on 2087/2083; phpMyAdmin is opened on the normal web
    listener so the browser gets a real phpMyAdmin session in a new tab.
    """
    import socket
    host=request.host.rsplit(':',1)[0] if request.host else 'localhost'
    for port,scheme in ((443,'https'),(80,'http')):
        try:
            with socket.create_connection(('127.0.0.1',port),timeout=0.7):
                return f'{scheme}://{host}/phpmyadmin/'
        except Exception:
            pass
    return ''

def _open_phpmyadmin():
    if not _phpmyadmin_installed():
        flash('phpMyAdmin is not installed. Install it from App Store first.')
        return redirect('/admin/store')
    target=_web_endpoint_url()
    if not target:
        return ('phpMyAdmin is installed, but no HTTP/HTTPS web server is listening on port 80 or 443. Install/configure Nginx or Apache, then try again.',503)
    return redirect(target,code=302)

@app.route('/admin/databases')
def admin_databases():
    x=admin_required()
    if x:return x
    _sync_database_inventory()
    c=db(); accounts=c.execute("SELECT id,username,domain,system_user FROM accounts ORDER BY username").fetchall(); c.close()
    try: info=DatabaseProvider.server_info()
    except Exception: info={"host":"localhost","port":3306,"version":"","engine":"MariaDB/MySQL"}
    try: users=DatabaseProvider.list_users() if DatabaseProvider.available() else []
    except Exception: users=[]
    return render_template('admin/databases.html',databases=_db_rows(admin=True),accounts=accounts,users=users,server=info,privileges=DB_PRIVS,error='',phpmyadmin_installed=_phpmyadmin_installed())

@app.route('/admin/databases/phpmyadmin')
def admin_databases_phpmyadmin():
    x=admin_required()
    if x:return x
    return _open_phpmyadmin()

@app.route('/admin/databases/sync',methods=['POST'])
def admin_databases_sync():
    x=admin_required()
    if x:return x
    try:
        _sync_database_inventory(); flash('Database inventory synchronized from MariaDB/MySQL.')
    except Exception as e: flash(str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/create',methods=['POST'])
def admin_database_create():
    x=admin_required()
    if x:return x
    try:
        name=_db_name_for_user(request.form.get('name'),None); aid=request.form.get('account_id') or None
        DatabaseProvider.create_database(name)
        _ensure_db_inventory(name,int(aid) if aid else None)
        audit('database.create','database',None,name,'MariaDB/MySQL database created')
        flash(f'Database {name} created successfully.')
    except Exception as e: flash('Database creation failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/<int:did>/delete',methods=['POST'])
def admin_database_delete(did):
    x=admin_required()
    if x:return x
    c=db(); r=c.execute('SELECT * FROM database_inventory WHERE id=?',(did,)).fetchone(); c.close()
    if not r: flash('Database not found.'); return redirect('/admin/databases')
    try:
        DatabaseProvider.delete_database(r['name'])
        c=db(); c.execute('DELETE FROM database_inventory WHERE id=?',(did,)); c.commit(); c.close()
        audit('database.delete','database',did,r['name'],'MariaDB/MySQL database deleted')
        flash(f'Database {r["name"]} deleted.')
    except Exception as e: flash('Database deletion failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/<int:did>/rename',methods=['POST'])
def admin_database_rename(did):
    x=admin_required()
    if x:return x
    c=db(); r=c.execute('SELECT * FROM database_inventory WHERE id=?',(did,)).fetchone(); c.close()
    if not r: flash('Database not found.'); return redirect('/admin/databases')
    try:
        new=_db_name_for_user(request.form.get('new_name'),None)
        DatabaseProvider.rename_database(r['name'],new)
        c=db(); c.execute('UPDATE database_inventory SET name=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(new,did)); c.commit(); c.close()
        audit('database.rename','database',did,new,f'MariaDB/MySQL database renamed from {r["name"]}')
        flash(f'Database renamed to {new}.')
    except Exception as e: flash('Database rename failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/users/create',methods=['POST'])
def admin_database_user_create():
    x=admin_required()
    if x:return x
    try:
        user=(request.form.get('user') or '').strip(); host=(request.form.get('host') or 'localhost').strip(); password=request.form.get('password') or ''
        DatabaseProvider.create_user(user,host,password); audit('database.user.create','database_user',None,user,'MariaDB/MySQL user created'); flash(f'Database user {user}@{host} saved.')
    except Exception as e: flash('User creation failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/users/password',methods=['POST'])
def admin_database_user_password():
    x=admin_required()
    if x:return x
    try:
        user=request.form.get('user','').strip(); host=request.form.get('host','localhost').strip(); DatabaseProvider.set_user_password(user,host,request.form.get('password') or '')
        audit('database.user.password','database_user',None,user,'Database user password changed'); flash(f'Password changed for {user}@{host}.')
    except Exception as e: flash('Password change failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/users/delete',methods=['POST'])
def admin_database_user_delete():
    x=admin_required()
    if x:return x
    try:
        user=request.form.get('user','').strip(); host=request.form.get('host','localhost').strip(); DatabaseProvider.delete_user(user,host); audit('database.user.delete','database_user',None,user,'Database user deleted'); flash(f'Database user {user}@{host} deleted.')
    except Exception as e: flash('User deletion failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/privileges',methods=['POST'])
def admin_database_privileges():
    x=admin_required()
    if x:return x
    try:
        user=request.form.get('user','').strip(); host=request.form.get('host','localhost').strip(); database=request.form.get('database','').strip(); action=request.form.get('action','grant')
        selected=request.form.getlist('privilege')
        if action=='revoke': DatabaseProvider.revoke_database(user,host,database,selected or None); msg='Privileges revoked safely.'
        else: DatabaseProvider.grant_database(user,host,database,selected); msg='Privileges granted safely.'
        audit('database.privilege.'+action,'database',None,database,f'{user}@{host}: {", ".join(selected) or "ALL"}'); flash(msg)
    except Exception as e: flash('Privilege update failed: '+str(e))
    return redirect('/admin/databases')

@app.route('/admin/databases/grants',methods=['GET'])
def admin_database_grants():
    x=admin_required()
    if x:return x
    try: return jsonify(ok=True,grants=DatabaseProvider.grants(request.args.get('user',''),request.args.get('host','localhost')))
    except Exception as e:return jsonify(ok=False,error=str(e)),400

def _account_db_user(requested, account):
    requested=(requested or '').strip()
    base=(account['system_user'] or account['username'] or 'account')
    prefix=base+'_'
    if not re.fullmatch(r'[A-Za-z0-9_$-]{1,48}', requested):
        raise DatabaseProviderError('Database username may contain only letters, numbers, _, -, $.')
    if not requested.startswith(prefix): requested=prefix+requested
    return requested[:80]


# ----------------------------- Email system -----------------------------
EMAIL_LOCAL_RE=re.compile(r'^[a-zA-Z0-9][a-zA-Z0-9._-]{0,63}$')

def _mail_provider():
    from app.providers import mail_engine
    return mail_engine

def _account_domains(account_id):
    c=db(); a=c.execute('SELECT id,username,domain FROM accounts WHERE id=?',(account_id,)).fetchone()
    vals=[]
    if a and a['domain']: vals.append(a['domain'].lower().rstrip('.'))
    if a:
        for r in c.execute("SELECT domain FROM domains WHERE account_id=? AND enabled=1 ORDER BY domain",(account_id,)):
            if r['domain'] not in vals: vals.append(r['domain'].lower().rstrip('.'))
    c.close(); return vals

def _mail_rows(account_id=None):
    c=db()
    if account_id:
        boxes=[dict(x) for x in c.execute('SELECT id,account_id,email,quota_mb,enabled,autoresponder_enabled,autoresponder_subject,autoresponder_message,created_at FROM mailboxes WHERE account_id=? ORDER BY email',(account_id,))]
        fw=[dict(x) for x in c.execute('SELECT * FROM mail_forwarders WHERE account_id=? ORDER BY source,destination',(account_id,))]
    else:
        boxes=[dict(x) for x in c.execute('SELECT id,account_id,email,quota_mb,enabled,autoresponder_enabled,autoresponder_subject,autoresponder_message,created_at FROM mailboxes ORDER BY email')]
        fw=[dict(x) for x in c.execute('SELECT * FROM mail_forwarders ORDER BY source,destination')]
    c.close()
    return boxes,fw

def _rebuild_mail():
    boxes,fw=_mail_rows(); return _mail_provider().rebuild(boxes,fw)

def _mailbox_form_email(local,domain):
    local=(local or '').strip(); domain=(domain or '').strip().lower().rstrip('.')
    if not EMAIL_LOCAL_RE.fullmatch(local): raise ValueError('Invalid mailbox name.')
    if not valid_domain(domain): raise ValueError('Invalid mail domain.')
    return f'{local}@{domain}'

def _mail_domain_owned(account_id,domain): return domain.lower().rstrip('.') in _account_domains(account_id)

def _sync_autoresponder(row):
    return _mail_provider().apply_autoresponder(row['email'],bool(row['autoresponder_enabled']),row['autoresponder_subject'] or '',row['autoresponder_message'] or '')

def _mail_error_redirect(exc,path): flash(str(exc)); return redirect(path)

@app.route('/admin/email')
def admin_email():
    x=admin_required()
    if x:return x
    boxes,fw=_mail_rows(); c=db(); accounts=[dict(a) for a in c.execute('SELECT id,username,domain FROM accounts ORDER BY username')]; c.close()
    st=_mail_provider().stack_status()
    domains_by_account={a['id']:_account_domains(a['id']) for a in accounts}
    return render_template('admin/email.html',mailboxes=boxes,forwarders=fw,accounts=accounts,domains_by_account=domains_by_account,status=st,admin_mode=True)

@app.route('/user/email')
def user_email():
    x=user_required()
    if x:return x
    aid=session['account_id']; boxes,fw=_mail_rows(aid); domains=_account_domains(aid); st=_mail_provider().stack_status()
    return render_template('admin/email.html',mailboxes=boxes,forwarders=fw,accounts=[],domains=domains,status=st,admin_mode=False,account_id=aid)

@app.route('/admin/email/rebuild',methods=['POST'])
def admin_email_rebuild():
    x=admin_required()
    if x:return x
    try: _rebuild_mail(); audit('email.rebuild','mail',None,'mail-stack','Postfix/Dovecot configuration rebuilt'); flash('Postfix/Dovecot mail configuration rebuilt and validated.')
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

@app.route('/admin/email/mailbox/create',methods=['POST'])
def admin_email_mailbox_create():
    x=admin_required()
    if x:return x
    aid=int(request.form.get('account_id') or 0); local=request.form.get('local',''); domain=request.form.get('domain',''); pw=request.form.get('password',''); quota=request.form.get('quota_mb','1024')
    c=db(); a=c.execute('SELECT * FROM accounts WHERE id=?',(aid,)).fetchone(); c.close()
    try:
        if not a: raise ValueError('Hosting account not found.')
        if not _mail_domain_owned(aid,domain): raise ValueError('Domain is not assigned to this hosting account.')
        email=_mailbox_form_email(local,domain)
        x=_mail_provider().create_mailbox(email,pw,quota)
        c=db(); c.execute('INSERT INTO mailboxes(account_id,email,password_hash,quota_mb,enabled) VALUES(?,?,?,?,1)',(aid,email,x['password_hash'],x['quota_mb'])); c.commit(); c.close(); _rebuild_mail(); audit('email.mailbox.create','mailbox',None,email,'Mailbox created'); flash(f'Mailbox {email} created.')
    except Exception as exc:
        try: _mail_provider().delete_mailbox(email)
        except Exception: pass
        flash(str(exc))
    return redirect('/admin/email')

@app.route('/user/email/mailbox/create',methods=['POST'])
def user_email_mailbox_create():
    x=user_required()
    if x:return x
    aid=session['account_id']; local=request.form.get('local',''); domain=request.form.get('domain',''); pw=request.form.get('password',''); quota=request.form.get('quota_mb','1024')
    try:
        if not _mail_domain_owned(aid,domain): raise ValueError('Domain is not assigned to your hosting account.')
        email=_mailbox_form_email(local,domain); x=_mail_provider().create_mailbox(email,pw,quota)
        c=db(); c.execute('INSERT INTO mailboxes(account_id,email,password_hash,quota_mb,enabled) VALUES(?,?,?,?,1)',(aid,email,x['password_hash'],x['quota_mb'])); c.commit(); c.close(); _rebuild_mail(); audit('email.mailbox.create','mailbox',None,email,'Mailbox created'); flash(f'Mailbox {email} created.')
    except Exception as exc: flash(str(exc))
    return redirect('/user/email')

def _mailbox_row(mid,account_id=None):
    c=db(); q='SELECT * FROM mailboxes WHERE id=?'; args=[mid]
    if account_id: q+=' AND account_id=?'; args.append(account_id)
    r=c.execute(q,args).fetchone(); c.close(); return r

@app.route('/admin/email/mailbox/<int:mid>/delete',methods=['POST'])
def admin_email_mailbox_delete(mid):
    x=admin_required()
    if x:return x
    r=_mailbox_row(mid)
    if not r: flash('Mailbox not found.'); return redirect('/admin/email')
    try:
        _mail_provider().delete_mailbox(r['email']); c=db(); c.execute('DELETE FROM mailboxes WHERE id=?',(mid,)); c.commit(); c.close(); _rebuild_mail(); audit('email.mailbox.delete','mailbox',mid,r['email'],'Mailbox deleted'); flash('Mailbox deleted.')
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

@app.route('/user/email/mailbox/<int:mid>/delete',methods=['POST'])
def user_email_mailbox_delete(mid):
    x=user_required()
    if x:return x
    r=_mailbox_row(mid,session['account_id'])
    if not r: flash('Mailbox not found.'); return redirect('/user/email')
    try:
        _mail_provider().delete_mailbox(r['email']); c=db(); c.execute('DELETE FROM mailboxes WHERE id=?',(mid,)); c.commit(); c.close(); _rebuild_mail(); audit('email.mailbox.delete','mailbox',mid,r['email'],'Mailbox deleted'); flash('Mailbox deleted.')
    except Exception as exc: flash(str(exc))
    return redirect('/user/email')

def _mailbox_action(mid,action,account_id=None):
    r=_mailbox_row(mid,account_id)
    if not r: raise ValueError('Mailbox not found.')
    c=db()
    if action=='toggle':
        new=0 if r['enabled'] else 1; c.execute('UPDATE mailboxes SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(new,mid))
        c.commit(); c.close(); _rebuild_mail(); return 'Mailbox enabled.' if new else 'Mailbox disabled.'
    if action=='password':
        pw=request.form.get('password',''); h=_mail_provider().change_password(r['email'],pw); c.execute('UPDATE mailboxes SET password_hash=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(h,mid)); c.commit(); c.close(); _rebuild_mail(); return 'Mailbox password changed.'
    if action=='quota':
        q=max(1,min(int(request.form.get('quota_mb','1024')),1024000)); c.execute('UPDATE mailboxes SET quota_mb=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(q,mid)); c.commit(); c.close(); _rebuild_mail(); return f'Mailbox quota set to {q} MB.'
    if action=='autoresponder':
        enabled=1 if request.form.get('enabled')=='1' else 0; subject=request.form.get('subject','').strip(); message=request.form.get('message','').strip();
        _mail_provider().apply_autoresponder(r['email'],enabled,subject,message); c.execute('UPDATE mailboxes SET autoresponder_enabled=?,autoresponder_subject=?,autoresponder_message=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(enabled,subject,message,mid)); c.commit(); c.close(); return 'Autoresponder updated.'
    raise ValueError('Unsupported mailbox action.')

for prefix,guard,aidfn in [('/admin',admin_required,lambda:None),('/user',user_required,lambda:session.get('account_id'))]:
    pass

@app.route('/admin/email/mailbox/<int:mid>/action',methods=['POST'])
def admin_email_mailbox_action(mid):
    x=admin_required()
    if x:return x
    try: flash(_mailbox_action(mid,request.form.get('action',''))); audit('email.mailbox.'+request.form.get('action','action'),'mailbox',mid,None,'Mailbox settings changed')
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

@app.route('/user/email/mailbox/<int:mid>/action',methods=['POST'])
def user_email_mailbox_action(mid):
    x=user_required()
    if x:return x
    try: flash(_mailbox_action(mid,request.form.get('action',''),session['account_id']))
    except Exception as exc: flash(str(exc))
    return redirect('/user/email')

def _forwarder_create(account_id):
    source=request.form.get('source','').strip().lower(); destination=request.form.get('destination','').strip().lower()
    if '@' not in source or '@' not in destination: raise ValueError('Forwarder source and destination must be email addresses.')
    _,sd=_mail_provider()._email(source); _mail_provider()._email(destination)
    if not _mail_domain_owned(account_id,sd): raise ValueError('Forwarder source domain is not assigned to this account.')
    c=db(); c.execute('INSERT INTO mail_forwarders(account_id,source,destination,enabled) VALUES(?,?,?,1)',(account_id,source,destination)); c.commit(); c.close(); _rebuild_mail(); return source

@app.route('/admin/email/forwarder/create',methods=['POST'])
def admin_email_forwarder_create():
    x=admin_required()
    if x:return x
    try: aid=int(request.form.get('account_id') or 0); _forwarder_create(aid); audit('email.forwarder.create','forwarder',None,request.form.get('source',''),'Forwarder created'); flash('Forwarder created.')
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

@app.route('/user/email/forwarder/create',methods=['POST'])
def user_email_forwarder_create():
    x=user_required()
    if x:return x
    try: _forwarder_create(session['account_id']); flash('Forwarder created.')
    except Exception as exc: flash(str(exc))
    return redirect('/user/email')

def _forwarder_delete(fid,account_id=None):
    c=db(); q='SELECT * FROM mail_forwarders WHERE id=?'; args=[fid]
    if account_id:q+=' AND account_id=?';args.append(account_id)
    r=c.execute(q,args).fetchone()
    if not r:c.close();raise ValueError('Forwarder not found.')
    c.execute('DELETE FROM mail_forwarders WHERE id=?',(fid,));c.commit();c.close();_rebuild_mail();return r

@app.route('/admin/email/forwarder/<int:fid>/delete',methods=['POST'])
def admin_email_forwarder_delete(fid):
    x=admin_required()
    if x:return x
    try:r=_forwarder_delete(fid);audit('email.forwarder.delete','forwarder',fid,r['source'],'Forwarder deleted');flash('Forwarder deleted.')
    except Exception as exc:flash(str(exc))
    return redirect('/admin/email')

@app.route('/user/email/forwarder/<int:fid>/delete',methods=['POST'])
def user_email_forwarder_delete(fid):
    x=user_required()
    if x:return x
    try:_forwarder_delete(fid,session['account_id']);flash('Forwarder deleted.')
    except Exception as exc:flash(str(exc))
    return redirect('/user/email')

@app.route('/admin/email/dkim',methods=['POST'])
def admin_email_dkim():
    x=admin_required()
    if x:return x
    try:
        domain=request.form.get('domain','').strip().lower(); data=_mail_provider().configure_dkim(domain); audit('email.dkim.generate','domain',None,domain,'DKIM key generated'); flash('DKIM key generated. Add the displayed DNS TXT record, then enable OpenDKIM.')
        return render_template('admin/email_dkim.html',data=data,domains=_account_domains(int(request.form.get('account_id') or 0)) if request.form.get('account_id') else [domain])
    except Exception as exc: flash(str(exc)); return redirect('/admin/email')

@app.route('/admin/email/dns-records',methods=['POST'])
def admin_email_dns_records():
    x=admin_required()
    if x:return x
    try:
        domain=request.form.get('domain','').strip().lower(); c=db(); d=c.execute('SELECT id FROM domains WHERE domain=?',(domain,)).fetchone(); c.close()
        if not d: raise ValueError('Domain is not in SCpanel domain inventory.')
        c=db(); z=_ensure_zone(c,d['id'],domain)
        records=[('@','TXT','v=spf1 mx ~all',3600,0),('_dmarc','TXT','v=DMARC1; p=none; rua=mailto:postmaster@'+domain,3600,0)]
        for name,typ,val,ttl,pri in records:
            if not c.execute('SELECT 1 FROM dns_records WHERE zone_id=? AND name=? AND type=?',(z['id'],name,typ)).fetchone(): c.execute('INSERT INTO dns_records(zone_id,name,type,value,ttl,priority) VALUES(?,?,?,?,?,?)',(z['id'],name,typ,val,ttl,pri))
        c.execute('UPDATE dns_zones SET serial=serial+1,updated_at=CURRENT_TIMESTAMP WHERE id=?',(z['id'],)); c.commit(); _apply_dns_zone(c,z['id']); c.close(); flash('SPF and DMARC starter records added and DNS zone validated.')
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

@app.route('/admin/email/setup',methods=['POST'])
def admin_email_setup():
    x=admin_required()
    if x:return x
    try:
        r=_rebuild_mail(); flash(r.get('message','Mail stack configured.'))
    except Exception as exc: flash(str(exc))
    return redirect('/admin/email')

# ----------------------------- FTP / SFTP -----------------------------
def _transfer_provider():
    from app.providers import ftp_engine
    return ftp_engine

def _transfer_rows(aid=None):
    c=db()
    if aid:
        f=[dict(r) for r in c.execute('SELECT * FROM ftp_accounts WHERE account_id=? ORDER BY username',(aid,))]
        s=[dict(r) for r in c.execute('SELECT * FROM sftp_accounts WHERE account_id=? ORDER BY username',(aid,))]
    else:
        f=[dict(r) for r in c.execute('SELECT * FROM ftp_accounts ORDER BY username')]
        s=[dict(r) for r in c.execute('SELECT * FROM sftp_accounts ORDER BY username')]
    keys={}
    for r in c.execute('SELECT * FROM sftp_keys ORDER BY id'):
        keys.setdefault(r['sftp_id'],[]).append(dict(r))
    c.close()
    for r in s: r['keys']=keys.get(r['id'],[])
    return f,s

def _transfer_account(aid):
    c=db(); a=c.execute('SELECT * FROM accounts WHERE id=?',(int(aid),)).fetchone(); c.close()
    if not a: raise ValueError('Hosting account not found.')
    return a

def _transfer_system_username(account, kind, username):
    raw=re.sub(r'[^a-zA-Z0-9_-]','_',username.strip().lower())
    prefix=f's{int(account["id"])}{kind}_'
    candidate=(prefix+raw)[:31]
    if not re.fullmatch(r'[a-z][a-z0-9_-]{2,31}',candidate): raise ValueError('Invalid transfer username.')
    return candidate

def _transfer_render(admin_mode, aid=None):
    accounts=[]
    if admin_mode:
        c=db(); accounts=[dict(x) for x in c.execute('SELECT id,username,domain,system_user,home_dir,enabled,suspended FROM accounts ORDER BY username')]; c.close()
    account=_transfer_account(aid) if aid else None
    ftp,sftp=_transfer_rows(aid)
    st=_transfer_provider().status()
    for r in ftp: r['usage_bytes']=_transfer_provider().usage(r['directory'])
    for r in sftp: r['usage_bytes']=_transfer_provider().usage(r['directory'])
    return render_template('admin/ftp.html',admin_mode=admin_mode,accounts=accounts,account=account,ftp_accounts=ftp,sftp_accounts=sftp,status=st)

@app.route('/admin/ftp')
def admin_ftp():
    x=admin_required()
    if x:return x
    return _transfer_render(True)

@app.route('/user/ftp')
def user_ftp():
    x=user_required()
    if x:return x
    return _transfer_render(False,session['account_id'])

def _ftp_create(admin_mode):
    aid=request.form.get('account_id') if admin_mode else session['account_id']
    a=_transfer_account(aid)
    try:
        name=request.form.get('username','').strip().lower(); directory=request.form.get('directory','public_html').strip() or 'public_html'; password=request.form.get('password',''); quota=max(0,min(int(request.form.get('quota_mb','0') or 0),1048576))
        c=db(); dup=c.execute('SELECT 1 FROM ftp_accounts WHERE account_id=? AND username=?',(a['id'],name)).fetchone(); c.close()
        if dup: raise ValueError('FTP username already exists for this hosting account.')
        sysname=_transfer_system_username(a,'f',name)
        x=_transfer_provider().create_ftp(dict(a),sysname,directory,password,quota,True)
        c=db(); c.execute('INSERT INTO ftp_accounts(account_id,username,system_username,directory,quota_mb,enabled) VALUES(?,?,?,?,?,1)',(a['id'],name,sysname,x['directory'],quota)); c.commit(); c.close()
        _transfer_provider().apply_ftp([dict(r) for r in db().execute('SELECT * FROM ftp_accounts').fetchall()]); audit('ftp.create','ftp_account',None,name,f'FTP account created for {a["username"]}'); flash(f'FTP account {name} created.')
    except Exception as exc: flash('FTP create failed: '+str(exc))
    return redirect('/admin/ftp' if admin_mode else '/user/ftp')

@app.route('/admin/ftp/create',methods=['POST'])
def admin_ftp_create():
    x=admin_required()
    if x:return x
    return _ftp_create(True)

@app.route('/user/ftp/create',methods=['POST'])
def user_ftp_create():
    x=user_required()
    if x:return x
    return _ftp_create(False)

def _sftp_create(admin_mode):
    aid=request.form.get('account_id') if admin_mode else session['account_id']; a=_transfer_account(aid)
    try:
        name=request.form.get('username','').strip().lower(); password=request.form.get('password',''); key=request.form.get('public_key','').strip(); quota=max(0,min(int(request.form.get('quota_mb','0') or 0),1048576))
        if key:
            import base64,hashlib
            parts=key.split()
            if len(parts)<2: raise ValueError('Invalid SSH public key.')
            base64.b64decode(parts[1]+'===',validate=True)
        c=db(); dup=c.execute('SELECT 1 FROM sftp_accounts WHERE account_id=? AND username=?',(a['id'],name)).fetchone(); c.close()
        if dup: raise ValueError('SFTP username already exists for this hosting account.')
        sysname=_transfer_system_username(a,'s',name); x=_transfer_provider().create_sftp(dict(a),sysname,password,quota,key)
        c=db(); c.execute('INSERT INTO sftp_accounts(account_id,username,system_username,sftp_root,directory,quota_mb,enabled) VALUES(?,?,?,?,?,?,1)',(a['id'],name,sysname,x['sftp_root'],x['directory'],quota)); sid=c.execute('SELECT last_insert_rowid()').fetchone()[0]
        if key:
            import hashlib,base64
            parts=key.split(); fp='SHA256:'+base64.b64encode(hashlib.sha256(base64.b64decode(parts[1]+'===' )).digest()).decode().rstrip('=')
            c.execute('INSERT OR IGNORE INTO sftp_keys(sftp_id,fingerprint,public_key) VALUES(?,?,?)',(sid,fp,key))
        c.commit(); c.close(); _transfer_provider().apply_sftp([dict(r) for r in db().execute('SELECT * FROM sftp_accounts').fetchall()]); audit('sftp.create','sftp_account',sid,name,f'SFTP account created for {a["username"]}'); flash(f'SFTP account {name} created.')
    except Exception as exc: flash('SFTP create failed: '+str(exc))
    return redirect('/admin/ftp' if admin_mode else '/user/ftp')

@app.route('/admin/ftp/sftp/create',methods=['POST'])
def admin_sftp_create():
    x=admin_required()
    if x:return x
    return _sftp_create(True)

@app.route('/user/ftp/sftp/create',methods=['POST'])
def user_sftp_create():
    x=user_required()
    if x:return x
    return _sftp_create(False)

def _transfer_action(kind, rid, admin_mode):
    aid=request.form.get('account_id') if admin_mode else session['account_id']; action=request.form.get('action','')
    table='ftp_accounts' if kind=='ftp' else 'sftp_accounts'
    try:
        c=db(); row=c.execute(f'SELECT * FROM {table} WHERE id=? AND account_id=?',(rid,aid)).fetchone(); c.close()
        if not row: raise ValueError('Transfer account not found.')
        a=_transfer_account(aid); p=_transfer_provider()
        if action=='toggle':
            enabled=0 if row['enabled'] else 1
            (p.enable_user if enabled else p.disable_user)(row['system_username'])
            c=db(); c.execute(f'UPDATE {table} SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(enabled,rid)); c.commit(); c.close()
        elif action=='password':
            p.change_password(row['system_username'],request.form.get('password',''))
        elif action=='quota':
            q=max(0,min(int(request.form.get('quota_mb','0') or 0),1048576)); result=p.quota_apply(row['system_username'],q,row['directory']); c=db(); c.execute(f'UPDATE {table} SET quota_mb=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(q,rid)); c.commit(); c.close(); flash('Quota saved.'+((' Filesystem quota enforced.' if result.get('enforced') else ' Filesystem quota is not enabled on this server; usage is still tracked.')))
        elif kind=='ftp' and action=='directory':
            directory=p.change_directory(dict(a),row['system_username'],request.form.get('directory','public_html')); c=db(); c.execute('UPDATE ftp_accounts SET directory=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(directory,rid)); c.commit(); c.close()
        elif action=='key_add' and kind=='sftp':
            key=request.form.get('public_key','').strip(); p.add_key(row['system_username'],key,row['sftp_root']); import hashlib,base64; parts=key.split(); fp='SHA256:'+base64.b64encode(hashlib.sha256(base64.b64decode(parts[1]+'===' )).digest()).decode().rstrip('='); c=db(); c.execute('INSERT OR IGNORE INTO sftp_keys(sftp_id,fingerprint,public_key) VALUES(?,?,?)',(rid,fp,key)); c.commit(); c.close()
        elif action=='key_delete' and kind=='sftp':
            key=request.form.get('public_key',''); p.remove_key(row['sftp_root'],key); c=db(); c.execute('DELETE FROM sftp_keys WHERE sftp_id=? AND public_key=?',(rid,key)); c.commit(); c.close()
        else: raise ValueError('Unsupported action.')
        if kind=='ftp': p.apply_ftp([dict(r) for r in db().execute('SELECT * FROM ftp_accounts').fetchall()])
        else: p.apply_sftp([dict(r) for r in db().execute('SELECT * FROM sftp_accounts').fetchall()])
        flash('FTP/SFTP settings updated.')
    except Exception as exc: flash('Operation failed: '+str(exc))
    return redirect('/admin/ftp' if admin_mode else '/user/ftp')

@app.route('/admin/ftp/<int:rid>/action',methods=['POST'])
def admin_ftp_action(rid):
    x=admin_required()
    if x:return x
    return _transfer_action('ftp',rid,True)

@app.route('/user/ftp/<int:rid>/action',methods=['POST'])
def user_ftp_action(rid):
    x=user_required()
    if x:return x
    return _transfer_action('ftp',rid,False)

@app.route('/admin/ftp/sftp/<int:rid>/action',methods=['POST'])
def admin_sftp_action(rid):
    x=admin_required()
    if x:return x
    return _transfer_action('sftp',rid,True)

@app.route('/user/ftp/sftp/<int:rid>/action',methods=['POST'])
def user_sftp_action(rid):
    x=user_required()
    if x:return x
    return _transfer_action('sftp',rid,False)

def _transfer_delete(kind,rid,admin_mode):
    aid=request.form.get('account_id') if admin_mode else session['account_id']; table='ftp_accounts' if kind=='ftp' else 'sftp_accounts'
    try:
        c=db(); row=c.execute(f'SELECT * FROM {table} WHERE id=? AND account_id=?',(rid,aid)).fetchone(); c.close()
        if not row: raise ValueError('Transfer account not found.')
        p=_transfer_provider(); p.delete(row['system_username'],row['sftp_root'] if kind=='sftp' else None)
        c=db(); c.execute(f'DELETE FROM {table} WHERE id=?',(rid,)); c.commit(); c.close()
        if kind=='ftp': p.apply_ftp([dict(r) for r in db().execute('SELECT * FROM ftp_accounts').fetchall()])
        else: p.apply_sftp([dict(r) for r in db().execute('SELECT * FROM sftp_accounts').fetchall()])
        audit(kind+'.delete',kind+'_account',rid,row['username'],kind.upper()+' account deleted'); flash(kind.upper()+' account deleted.')
    except Exception as exc: flash('Delete failed: '+str(exc))
    return redirect('/admin/ftp' if admin_mode else '/user/ftp')

@app.route('/admin/ftp/<int:rid>/delete',methods=['POST'])
def admin_ftp_delete(rid):
    x=admin_required()
    if x:return x
    return _transfer_delete('ftp',rid,True)

@app.route('/user/ftp/<int:rid>/delete',methods=['POST'])
def user_ftp_delete(rid):
    x=user_required()
    if x:return x
    return _transfer_delete('ftp',rid,False)

@app.route('/admin/ftp/sftp/<int:rid>/delete',methods=['POST'])
def admin_sftp_delete(rid):
    x=admin_required()
    if x:return x
    return _transfer_delete('sftp',rid,True)

@app.route('/user/ftp/sftp/<int:rid>/delete',methods=['POST'])
def user_sftp_delete(rid):
    x=user_required()
    if x:return x
    return _transfer_delete('sftp',rid,False)


@app.route('/user/databases')
def user_databases():
    x=user_required()
    if x:return x
    aid=session['account_id']; _sync_database_inventory(aid)
    c=db(); account=c.execute('SELECT * FROM accounts WHERE id=?',(aid,)).fetchone(); c.close()
    try: info=DatabaseProvider.server_info()
    except Exception: info={"host":"localhost","port":3306,"version":"","engine":"MariaDB/MySQL"}
    all_users=DatabaseProvider.list_users() if DatabaseProvider.available() else []
    prefix=(account['system_user'] or account['username'] or 'account')+'_'
    users=[u for u in all_users if u['user'].startswith(prefix)]
    return render_template('user/databases.html',databases=_db_rows(aid),account=account,server=info,users=users,privileges=DB_PRIVS)

@app.route('/user/databases/phpmyadmin')
def user_databases_phpmyadmin():
    x=user_required()
    if x:return x
    if not _phpmyadmin_installed(): return redirect('/user/databases')
    target=_web_endpoint_url()
    if not target: return ('phpMyAdmin is installed, but no HTTP/HTTPS web server is listening on port 80 or 443. Install/configure Nginx or Apache, then try again.',503)
    return redirect(target,code=302)

@app.route('/user/databases/create',methods=['POST'])
def user_database_create():
    x=user_required()
    if x:return x
    c=db(); account=c.execute('SELECT * FROM accounts WHERE id=?',(session['account_id'],)).fetchone(); c.close()
    try:
        name=_db_name_for_user(request.form.get('name'),account); DatabaseProvider.create_database(name); _ensure_db_inventory(name,session['account_id']); audit('database.create','database',None,name,'User MariaDB/MySQL database created'); flash(f'Database {name} created successfully.')
    except Exception as e: flash('Database creation failed: '+str(e))
    return redirect('/user/databases')

@app.route('/user/databases/<int:did>/delete',methods=['POST'])
def user_database_delete(did):
    x=user_required();
    if x:return x
    c=db(); r=c.execute('SELECT * FROM database_inventory WHERE id=? AND account_id=?',(did,session['account_id'])).fetchone(); c.close()
    if not r: flash('Database not found or not owned by this account.'); return redirect('/user/databases')
    try:
        DatabaseProvider.delete_database(r['name']); c=db(); c.execute('DELETE FROM database_inventory WHERE id=?',(did,)); c.commit(); c.close(); audit('database.delete','database',did,r['name'],'User database deleted'); flash(f'Database {r["name"]} deleted.')
    except Exception as e: flash('Database deletion failed: '+str(e))
    return redirect('/user/databases')

@app.route('/user/databases/<int:did>/rename',methods=['POST'])
def user_database_rename(did):
    x=user_required();
    if x:return x
    c=db(); r=c.execute('SELECT * FROM database_inventory WHERE id=? AND account_id=?',(did,session['account_id'])).fetchone(); account=c.execute('SELECT * FROM accounts WHERE id=?',(session['account_id'],)).fetchone(); c.close()
    if not r: flash('Database not found or not owned by this account.'); return redirect('/user/databases')
    try:
        new=_db_name_for_user(request.form.get('new_name'),account); DatabaseProvider.rename_database(r['name'],new); c=db(); c.execute('UPDATE database_inventory SET name=?,updated_at=CURRENT_TIMESTAMP WHERE id=?',(new,did)); c.commit(); c.close(); audit('database.rename','database',did,new,'User database renamed'); flash(f'Database renamed to {new}.')
    except Exception as e: flash('Database rename failed: '+str(e))
    return redirect('/user/databases')

@app.route('/user/databases/users/create',methods=['POST'])
def user_database_user_create():
    x=user_required();
    if x:return x
    try:
        c=db(); account=c.execute('SELECT * FROM accounts WHERE id=?',(session['account_id'],)).fetchone(); c.close()
        user=_account_db_user(request.form.get('user'),account); host='localhost'; DatabaseProvider.create_user(user,host,request.form.get('password') or ''); flash(f'Database user {user}@{host} saved.')
    except Exception as e: flash('User creation failed: '+str(e))
    return redirect('/user/databases')

@app.route('/user/databases/users/password',methods=['POST'])
def user_database_user_password():
    x=user_required();
    if x:return x
    try:
        c=db(); account=c.execute('SELECT * FROM accounts WHERE id=?',(session['account_id'],)).fetchone(); c.close()
        user=_account_db_user(request.form.get('user'),account); DatabaseProvider.set_user_password(user,'localhost',request.form.get('password') or ''); flash('Database user password changed.')
    except Exception as e: flash('Password change failed: '+str(e))
    return redirect('/user/databases')

@app.route('/user/databases/privileges',methods=['POST'])
def user_database_privileges():
    x=user_required();
    if x:return x
    aid=session['account_id']; database=request.form.get('database','').strip()
    c=db(); owned=c.execute('SELECT 1 FROM database_inventory WHERE account_id=? AND name=?',(aid,database)).fetchone(); c.close()
    if not owned: flash('Privilege update denied: database is not owned by this account.'); return redirect('/user/databases')
    try:
        c=db(); account=c.execute('SELECT * FROM accounts WHERE id=?',(aid,)).fetchone(); c.close()
        user=_account_db_user(request.form.get('user'),account); host='localhost'; selected=request.form.getlist('privilege'); action=request.form.get('action','grant')
        if action=='revoke': DatabaseProvider.revoke_database(user,host,database,selected or None)
        else: DatabaseProvider.grant_database(user,host,database,selected)
        audit('database.privilege.'+action,'database',None,database,f'User {user}@{host} privilege update'); flash('Database privileges updated safely.')
    except Exception as e: flash('Privilege update failed: '+str(e))
    return redirect('/user/databases')


def _file_account(account_id=None, *, admin=False):
    c=db()
    try:
        if admin:
            if not account_id:
                a=c.execute("SELECT * FROM accounts ORDER BY id LIMIT 1").fetchone()
            else:
                try: aid=int(account_id)
                except Exception: raise FileManagerError("Invalid hosting account.")
                a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone()
        else:
            aid=session.get("account_id")
            a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone()
        if not a: raise FileManagerError("Hosting account not found.")
        if not a["home_dir"] or not str(a["home_dir"]).startswith("/home/"):
            raise FileManagerError("This hosting account has no safe home directory configured.")
        if not admin and (not a["enabled"] or a["suspended"]):
            raise FileManagerError("Your hosting account is disabled or suspended.")
        root=Path(a["home_dir"]).resolve()
        if root.parent != Path("/home") or root.name != (a["system_user"] or a["username"]):
            raise FileManagerError("Hosting filesystem root failed the isolation check.")
        if not root.exists() or not root.is_dir(): raise FileManagerError("Hosting home directory is unavailable.")
        return a,root
    finally:
        c.close()

def _file_render(admin=False, account_id=None, path=".", query=""):
    a,root=_file_account(account_id,admin=admin)
    current=file_relative(root,path)
    rows=file_search(root,query,current) if query else list_dir(root,current)
    parent="." if current=="." else str(Path(current).parent).replace(os.sep,"/")
    return a,root,current,parent,rows

def _file_redirect(admin=False, account_id=None, path="."):
    base="/admin/files" if admin else "/user/files"
    params=[]
    if admin and account_id: params.append("account_id="+str(account_id))
    params.append("path="+path)
    return redirect(base+"?"+"&".join(params))

def _file_action(admin=False, account_id=None):
    try:
        a,root=_file_account(account_id,admin=admin)
    except FileManagerError as exc:
        flash("File Manager: "+str(exc))
        return _file_redirect(admin,account_id,".")
    op=request.form.get("operation","").strip().lower()
    current=request.form.get("path", ".")
    selected=request.form.getlist("selected")
    try:
        if op=="mkdir": create_dir(root,current,request.form.get("name","")); msg="Directory created."
        elif op=="create": create_file(root,current,request.form.get("name","")); msg="File created."
        elif op=="upload":
            fs=request.files.get("file")
            if not fs: raise FileManagerError("Select a file to upload.")
            upload(root,current,fs); msg="File uploaded."
        elif op=="rename":
            src=request.form.get("source") or (selected[0] if selected else "")
            rename(root,src,request.form.get("new_name","")); msg="Renamed successfully."
        elif op=="delete":
            if not selected: raise FileManagerError("Select at least one item to delete.")
            for src in selected: delete(root,src)
            msg=f"Deleted {len(selected)} item(s)."
        elif op in {"copy","move"}:
            if len(selected)!=1: raise FileManagerError("Select exactly one item for copy/move.")
            dest=request.form.get("destination","").strip()
            if not dest: raise FileManagerError("Enter a destination path.")
            (copy_item if op=="copy" else move_item)(root,selected[0],dest); msg=("Copied." if op=="copy" else "Moved.")
        elif op=="zip":
            if not selected: raise FileManagerError("Select at least one item to archive.")
            name=request.form.get("archive_name","").strip() or "archive.zip"
            if not name.lower().endswith(".zip"): name += ".zip"
            archive=file_relative(root,current)+"/"+name if current!="." else name
            zip_items(root,selected,archive); msg="ZIP archive created."
        elif op=="extract":
            src=request.form.get("source") or (selected[0] if selected else "")
            if not src.lower().endswith(".zip"): raise FileManagerError("Select a ZIP archive.")
            destination=request.form.get("destination","").strip() or current
            extract_zip(root,src,destination); msg="Archive extracted safely."
        elif op=="chmod":
            src=request.form.get("source") or (selected[0] if selected else "")
            chmod(root,src,request.form.get("mode","")); msg="Permissions updated."
        elif op=="chown":
            src=request.form.get("source") or (selected[0] if selected else "")
            allowed=[a["system_user"] or a["username"]]
            if admin: allowed=allowed+["root"]
            chown(root,src,request.form.get("owner",""),request.form.get("group",""),allowed_users=allowed,allowed_groups=allowed); msg="Ownership updated."
        elif op=="save":
            src=request.form.get("source") or ""
            write_text(root,src,request.form.get("content", "")); msg="File saved."
            current=str(Path(src).parent).replace(os.sep,"/") or "."
        else: raise FileManagerError("Unknown file operation.")
        audit("file."+op,"file",None,a["username"],msg)
        flash(msg)
    except (FileManagerError, OSError, ValueError) as exc:
        flash("File Manager: "+str(exc))
    return _file_redirect(admin,account_id,current)

@app.route("/admin/files")
def admin_files():
    x=admin_required()
    if x:return x
    c=db(); accounts=c.execute("SELECT id,username,domain,home_dir,system_user,enabled,suspended FROM accounts ORDER BY username").fetchall(); c.close()
    aid=request.args.get("account_id") or (accounts[0]["id"] if accounts else None)
    path=request.args.get("path","."); query=request.args.get("q","").strip()
    try:
        a,root,current,parent,rows=_file_render(True,aid,path,query); error=""
    except FileManagerError as exc:
        a=root=current=parent=None; rows=[]; error=str(exc)
    owner_choices=[a["system_user"] if a else "root","root"] if a else ["root"]
    group_choices=[a["system_user"] if a else "root","root"] if a else ["root"]
    return render_template("file_manager.html",admin_mode=True,accounts=accounts,account=a,root=root,current=current,parent=parent,rows=rows,query=query,error=error,owner_choices=owner_choices,group_choices=group_choices)

@app.route("/user/files")
def user_files():
    x=user_required()
    if x:return x
    path=request.args.get("path","."); query=request.args.get("q","").strip()
    try:
        a,root,current,parent,rows=_file_render(False,None,path,query); error=""
    except FileManagerError as exc:
        a=root=current=parent=None; rows=[]; error=str(exc)
    owner=(a["system_user"] or a["username"]) if a else ""
    return render_template("file_manager.html",admin_mode=False,accounts=[],account=a,root=root,current=current,parent=parent,rows=rows,query=query,error=error,owner_choices=[owner] if owner else [],group_choices=[owner] if owner else [])

@app.route("/admin/files/action",methods=["POST"])
def admin_files_action():
    x=admin_required()
    if x:return x
    return _file_action(True,request.form.get("account_id"))

@app.route("/user/files/action",methods=["POST"])
def user_files_action():
    x=user_required()
    if x:return x
    return _file_action(False,None)

@app.route("/admin/files/download")
def admin_files_download():
    x=admin_required()
    if x:return x
    from flask import send_file
    try:
        a,root=_file_account(request.args.get("account_id"),admin=True); p=safe_path(root,request.args.get("path","."),allow_root=False)
        if not p.is_file() or p.is_symlink(): raise FileManagerError("Only regular files can be downloaded.")
        return send_file(str(p),as_attachment=True,download_name=p.name,max_age=0)
    except FileManagerError as exc: flash("Download: "+str(exc)); return _file_redirect(True,request.args.get("account_id"),request.args.get("dir","."))

@app.route("/user/files/download")
def user_files_download():
    x=user_required()
    if x:return x
    from flask import send_file
    try:
        a,root=_file_account(None,admin=False); p=safe_path(root,request.args.get("path","."),allow_root=False)
        if not p.is_file() or p.is_symlink(): raise FileManagerError("Only regular files can be downloaded.")
        return send_file(str(p),as_attachment=True,download_name=p.name,max_age=0)
    except FileManagerError as exc: flash("Download: "+str(exc)); return _file_redirect(False,None,request.args.get("dir","."))

@app.route("/admin/files/editor")
def admin_files_editor():
    x=admin_required()
    if x:return x
    aid=request.args.get("account_id"); path=request.args.get("path","")
    try: a,root=_file_account(aid,admin=True); content=read_text(root,path); error=""
    except FileManagerError as exc: a=content=None; error=str(exc)
    return render_template("file_editor.html",admin_mode=True,account=a,source=path,content=content,error=error,account_id=aid)

@app.route("/user/files/editor")
def user_files_editor():
    x=user_required()
    if x:return x
    path=request.args.get("path","")
    try: a,root=_file_account(None,admin=False); content=read_text(root,path); error=""
    except FileManagerError as exc: a=content=None; error=str(exc)
    return render_template("file_editor.html",admin_mode=False,account=a,source=path,content=content,error=error,account_id="")

# ---- Backup & Restore -------------------------------------------------------
def _backup_render(admin=False):
    from app.providers import backup_engine
    c=db(); accounts=c.execute("SELECT id,username,domain FROM accounts WHERE enabled=1 AND suspended=0 ORDER BY username").fetchall(); websites=c.execute("SELECT id,domain FROM websites ORDER BY domain").fetchall(); databases=c.execute("SELECT name FROM database_inventory ORDER BY name").fetchall(); c.close()
    return render_template("admin/backups.html",admin_mode=admin,backups=backup_engine.list_backups(),accounts=accounts,websites=websites,databases=databases)
@app.route("/admin/backups")
def admin_backups():
    x=admin_required();
    if x:return x
    return _backup_render(True)
@app.route("/user/backups")
def user_backups():
    x=user_required();
    if x:return x
    return _backup_render(False)
def _backup_action(admin=False):
    x=admin_required() if admin else user_required()
    if x:return x
    from app.providers import backup_engine
    action=request.form.get("action"); kind=request.form.get("kind","full")
    try:
        if action=="create":
            if kind=="full" and not admin: raise backup_engine.BackupError("Only administrators can create full server backups.")
            if kind=="account": out=backup_engine.create(kind,account_id=int(request.form.get("account_id") or session.get("account_id")))
            elif kind=="website": out=backup_engine.create(kind,website_id=int(request.form.get("website_id")))
            elif kind=="database":
                name=request.form.get("database"); c=db(); ok=admin or c.execute("SELECT 1 FROM database_inventory WHERE name=? AND account_id=?",(name,session.get("account_id"))).fetchone(); c.close();
                if not ok: raise backup_engine.BackupError("Database is outside your account.")
                out=backup_engine.create(kind,database=name)
            elif kind=="email": out=backup_engine.create(kind,domain=request.form.get("domain",""))
            else: out=backup_engine.create(kind)
            audit("backup.create","backup",0,out.name,"Backup created"); flash("Backup created: "+out.name)
        elif action=="restore":
            comp=request.form.get("component","full")
            if not admin and comp=="full": raise backup_engine.BackupError("Full restore is administrator-only.")
            backup_engine.restore(request.form.get("backup"),comp,request.form.get("target") or None); audit("backup.restore","backup",0,request.form.get("backup"),"Restore requested: "+comp); flash("Restore completed.")
        elif action=="delete": backup_engine.delete(request.form.get("backup")); flash("Backup deleted.")
        elif action=="retention": backup_engine.apply_retention(int(request.form.get("retention_days") or 30)); flash("Retention cleanup completed.")
        else: raise backup_engine.BackupError("Unknown backup action.")
    except Exception as e: flash("Backup operation failed: "+str(e))
    return redirect("/admin/backups" if admin else "/user/backups")
@app.route("/admin/backups/action",methods=["POST"])
def admin_backups_action(): return _backup_action(True)
@app.route("/user/backups/action",methods=["POST"])
def user_backups_action(): return _backup_action(False)

# ---- Cron Jobs -------------------------------------------------------------
def _cron_account(aid, admin=False):
    c=db();
    if admin: a=c.execute("SELECT * FROM accounts WHERE id=?",(aid,)).fetchone()
    else: a=c.execute("SELECT * FROM accounts WHERE id=?",(session.get("account_id"),)).fetchone()
    c.close()
    if not a: raise ValueError("Hosting account not found.")
    if not a["system_user"] or not a["home_dir"]: raise ValueError("Hosting account has no system home.")
    return a

def _cron_rows(admin=False):
    c=db()
    if admin: rows=c.execute("SELECT j.*,a.username account_username,a.domain,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id ORDER BY j.id DESC").fetchall()
    else: rows=c.execute("SELECT j.*,a.username account_username,a.domain,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.account_id=? ORDER BY j.id DESC",(session.get("account_id"),)).fetchall()
    c.close(); return rows

def _cron_next(schedule):
    try:
        from croniter import croniter
        import datetime
        return croniter(schedule, datetime.datetime.now()).get_next(datetime.datetime).strftime('%Y-%m-%d %H:%M:%S')
    except Exception:
        return 'Calculated by cron daemon'

def _cron_sync(row):
    from app.providers import cron_engine
    cron_engine.install(row) if row['enabled'] else cron_engine.disable(row)

def _cron_render(admin_mode):
    rows=[]
    for r in _cron_rows(admin_mode):
        out,err=__import__('app.providers.cron_engine',fromlist=['read_output']).read_output(r)
        d=dict(r); d['output']=out; d['errors']=err; d['log_size']=len(out)+len(err); rows.append(d)
    c=db(); accounts=c.execute("SELECT id,username,domain,home_dir FROM accounts WHERE enabled=1 AND suspended=0 ORDER BY username").fetchall(); c.close()
    return render_template('admin/cron.html',admin_mode=admin_mode,jobs=rows,accounts=accounts)

@app.route('/admin/cron')
def admin_cron():
    x=admin_required();
    if x:return x
    return _cron_render(True)

@app.route('/user/cron')
def user_cron():
    x=user_required();
    if x:return x
    return _cron_render(False)

def _cron_create(admin):
    aid=request.form.get('account_id') if admin else session.get('account_id')
    name=request.form.get('name','').strip()[:100]; schedule=request.form.get('schedule','').strip(); command=request.form.get('command','').strip(); wd=request.form.get('working_dir','').strip()
    try:
        from app.providers import cron_engine
        a=_cron_account(int(aid),admin); schedule=cron_engine.validate_schedule(schedule); command=cron_engine.validate_command(command); wd=cron_engine.validate_path(wd,a['home_dir'])
        c=db(); cur=c.execute("INSERT INTO cron_jobs(account_id,name,schedule,command,working_dir,enabled,next_run) VALUES(?,?,?,?,?,1,?)",(a['id'],name or 'Cron Job',schedule,command,wd,_cron_next(schedule))); c.commit(); row=c.execute("SELECT j.*,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.id=?",(cur.lastrowid,)).fetchone(); c.close(); _cron_sync(row); audit('cron.create','cron_job',row['id'],name,'Cron job created')
        flash('Cron job created.')
    except Exception as e: flash('Create failed: '+str(e))
    return redirect('/admin/cron' if admin else '/user/cron')

@app.route('/admin/cron/create',methods=['POST'])
def admin_cron_create():
    x=admin_required();
    if x:return x
    return _cron_create(True)
@app.route('/user/cron/create',methods=['POST'])
def user_cron_create():
    x=user_required();
    if x:return x
    return _cron_create(False)

def _cron_action(jid,admin):
    c=db(); row=c.execute("SELECT j.*,a.system_user,a.home_dir,a.username FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.id=?",(jid,)).fetchone(); c.close()
    if not row or (not admin and row['account_id']!=session.get('account_id')): abort(404)
    action=request.form.get('action')
    try:
        from app.providers import cron_engine
        if action in ('enable','disable'):
            enabled=1 if action=='enable' else 0; c=db(); c.execute("UPDATE cron_jobs SET enabled=?,updated_at=CURRENT_TIMESTAMP,next_run=? WHERE id=?",(enabled,_cron_next(row['schedule']) if enabled else '',jid)); c.commit(); row=c.execute("SELECT j.*,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.id=?",(jid,)).fetchone(); c.close(); _cron_sync(row); audit('cron.'+action,'cron_job',jid,row['name'],'Cron status changed')
        elif action=='run':
            rc,out,err=cron_engine.run_now(row); c=db(); c.execute("UPDATE cron_jobs SET last_run=CURRENT_TIMESTAMP,last_exit_code=?,updated_at=CURRENT_TIMESTAMP,next_run=? WHERE id=?",(rc,_cron_next(row['schedule']),jid)); c.commit(); c.close(); flash(('Run completed.' if rc==0 else 'Run failed.')+' Exit code '+str(rc)+'.')
        elif action=='delete':
            cron_engine.remove(row); c=db(); c.execute('DELETE FROM cron_jobs WHERE id=?',(jid,)); c.commit(); c.close(); audit('cron.delete','cron_job',jid,row['name'],'Cron job deleted')
        else: raise ValueError('Unknown action')
    except Exception as e: flash('Cron action failed: '+str(e))
    return redirect('/admin/cron' if admin else '/user/cron')

@app.route('/admin/cron/<int:jid>/action',methods=['POST'])
def admin_cron_action(jid):
    x=admin_required();
    if x:return x
    return _cron_action(jid,True)
@app.route('/user/cron/<int:jid>/action',methods=['POST'])
def user_cron_action(jid):
    x=user_required();
    if x:return x
    return _cron_action(jid,False)

@app.route('/admin/cron/<int:jid>/edit',methods=['POST'])
def admin_cron_edit(jid):
    return _cron_edit(jid,True)
@app.route('/user/cron/<int:jid>/edit',methods=['POST'])
def user_cron_edit(jid):
    return _cron_edit(jid,False)
def _cron_edit(jid,admin):
    x=admin_required() if admin else user_required()
    if x:return x
    c=db(); row=c.execute("SELECT j.*,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.id=?",(jid,)).fetchone(); c.close()
    if not row or (not admin and row['account_id']!=session.get('account_id')): abort(404)
    try:
        from app.providers import cron_engine
        schedule=cron_engine.validate_schedule(request.form.get('schedule','')); command=cron_engine.validate_command(request.form.get('command','')); wd=cron_engine.validate_path(request.form.get('working_dir',''),row['home_dir']); name=request.form.get('name','Cron Job').strip()[:100]
        c=db(); c.execute("UPDATE cron_jobs SET name=?,schedule=?,command=?,working_dir=?,next_run=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(name,schedule,command,wd,_cron_next(schedule),jid)); c.commit(); row=c.execute("SELECT j.*,a.system_user,a.home_dir FROM cron_jobs j JOIN accounts a ON a.id=j.account_id WHERE j.id=?",(jid,)).fetchone(); c.close(); _cron_sync(row); flash('Cron job updated.')
    except Exception as e: flash('Update failed: '+str(e))
    return redirect('/admin/cron' if admin else '/user/cron')

# ---- Firewall + Port Manager ----------------------------------------------
def _settings_map():
    c=db(); rows=c.execute("SELECT key,value FROM settings").fetchall(); c.close(); return {r['key']:r['value'] for r in rows}

def _firewall_rows():
    c=db(); rows=c.execute("SELECT * FROM firewall_rules ORDER BY protected DESC, port, protocol, id").fetchall(); c.close(); return rows

def _firewall_sync_all():
    from app.providers import firewall_engine
    settings=_settings_map(); sync=firewall_engine.sync_panel_ports(settings)
    for r in _firewall_rows():
        firewall_engine.apply(r, bool(r['enabled']))
    return sync

def _ensure_panel_rules():
    from app.providers import firewall_engine
    settings=_settings_map(); ports=firewall_engine.default_panel_ports(settings)
    c=db()
    for name,port,proto in ports:
        row=c.execute("SELECT * FROM firewall_rules WHERE name=?",(name,)).fetchone()
        if row is None:
            c.execute("INSERT INTO firewall_rules(name,port,protocol,source,enabled,protected,description) VALUES(?,?,?,?,1,1,?)",(name,port,proto,'0.0.0.0/0','SCpanel management port — protected from accidental lockout.'))
        else:
            c.execute("UPDATE firewall_rules SET port=?,protocol=?,enabled=1,protected=1,updated_at=CURRENT_TIMESTAMP WHERE id=?",(port,proto,row['id']))
    c.commit(); c.close()

def _firewall_render():
    try:
        _ensure_panel_rules(); status=__import__('app.providers.firewall_engine',fromlist=['status']).status(); sync=__import__('app.providers.firewall_engine',fromlist=['sync_panel_ports']).sync_panel_ports(_settings_map()); error=''
    except Exception as e: status={'backend':'none','active':'unavailable'}; sync={}; error=str(e)
    return render_template('admin/firewall.html',rules=_firewall_rows(),status=status,sync=sync,error=error)

@app.route('/admin/firewall')
def admin_firewall():
    x=admin_required()
    if x:return x
    return _firewall_render()

@app.route('/admin/firewall/create',methods=['POST'])
def admin_firewall_create():
    x=admin_required()
    if x:return x
    from app.providers import firewall_engine
    try:
        name=request.form.get('name','').strip()[:80]
        if not re.fullmatch(r'[A-Za-z0-9][A-Za-z0-9 _.-]{1,79}',name): raise firewall_engine.FirewallError('Invalid rule name.')
        port=firewall_engine.validate_port(request.form.get('port')); proto=firewall_engine.validate_proto(request.form.get('protocol')); src=firewall_engine.validate_source(request.form.get('source'))
        if firewall_engine.protect_port(port,_settings_map()): raise firewall_engine.FirewallError('Panel management ports are protected; edit the protected rule instead.')
        c=db(); cur=c.execute("INSERT INTO firewall_rules(name,port,protocol,source,enabled,protected,description) VALUES(?,?,?,?,1,0,?)",(name,port,proto,src,1,request.form.get('description','').strip()[:240])); c.commit(); row=c.execute("SELECT * FROM firewall_rules WHERE id=?",(cur.lastrowid,)).fetchone(); c.close(); firewall_engine.apply(row,True); audit('firewall.create','firewall_rule',row['id'],name,'Firewall rule created'); flash('Firewall rule added.')
    except Exception as e: flash('Create failed: '+str(e))
    return redirect('/admin/firewall')

@app.route('/admin/firewall/<int:rid>/edit',methods=['POST'])
def admin_firewall_edit(rid):
    x=admin_required()
    if x:return x
    from app.providers import firewall_engine
    c=db(); row=c.execute('SELECT * FROM firewall_rules WHERE id=?',(rid,)).fetchone(); c.close()
    if not row: abort(404)
    try:
        port=firewall_engine.validate_port(request.form.get('port')); proto=firewall_engine.validate_proto(request.form.get('protocol')); src=firewall_engine.validate_source(request.form.get('source'))
        if row['protected'] and not firewall_engine.protect_port(port,_settings_map()): raise firewall_engine.FirewallError('Protected panel rule cannot be moved away from a panel port.')
        if firewall_engine.protect_port(port,_settings_map()) and not row['protected']: raise firewall_engine.FirewallError('Panel management ports are reserved.')
        # remove old backend rule before applying new one, rollback DB on failure.
        firewall_engine.apply(row,False)
        c=db(); c.execute("UPDATE firewall_rules SET port=?,protocol=?,source=?,description=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(port,proto,src,request.form.get('description','').strip()[:240],rid)); c.commit(); newrow=c.execute('SELECT * FROM firewall_rules WHERE id=?',(rid,)).fetchone(); c.close(); firewall_engine.apply(newrow,True); audit('firewall.edit','firewall_rule',rid,row['name'],'Firewall rule updated'); flash('Firewall rule updated.')
    except Exception as e: flash('Update failed: '+str(e))
    return redirect('/admin/firewall')

@app.route('/admin/firewall/<int:rid>/action',methods=['POST'])
def admin_firewall_action(rid):
    x=admin_required()
    if x:return x
    from app.providers import firewall_engine
    c=db(); row=c.execute('SELECT * FROM firewall_rules WHERE id=?',(rid,)).fetchone(); c.close()
    if not row: abort(404)
    action=request.form.get('action','')
    try:
        if action in ('enable','disable'):
            if action=='disable' and row['protected']: raise firewall_engine.FirewallError('Lockout protection: panel management ports cannot be disabled.')
            enabled=action=='enable'; firewall_engine.apply(row,enabled)
            c=db(); c.execute("UPDATE firewall_rules SET enabled=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",(int(enabled),rid)); c.commit(); c.close(); audit('firewall.'+action,'firewall_rule',rid,row['name'],'Firewall rule state changed'); flash('Rule '+action+'d.')
        elif action=='delete':
            if row['protected']: raise firewall_engine.FirewallError('Lockout protection: protected panel ports cannot be deleted.')
            firewall_engine.apply(row,False); c=db(); c.execute('DELETE FROM firewall_rules WHERE id=?',(rid,)); c.commit(); c.close(); audit('firewall.delete','firewall_rule',rid,row['name'],'Firewall rule deleted'); flash('Firewall rule deleted.')
        elif action=='sync':
            _firewall_sync_all(); flash('Firewall, SCpanel port configuration and systemd listener state synchronized.')
        else: raise firewall_engine.FirewallError('Unknown firewall action.')
    except Exception as e: flash('Firewall action failed: '+str(e))
    return redirect('/admin/firewall')

@app.route("/admin/<module>")
def generic_admin(module):
    x=admin_required()
    if x:return x
    titles={"websites":"Websites","domains":"Domains","files":"File Manager","databases":"Databases","email":"Email","ftp":"FTP / SFTP","dns":"DNS","ssl":"SSL / Let's Encrypt","cron":"Cron Jobs","firewall":"Firewall & Ports","store":"App Store","audit":"Audit Log"}
    return render_template("module.html",title=titles.get(module,module.title()),description="Module ready for server integration.",items=[])

@app.route("/user")
def user_dash():
    x=user_required()
    if x:return x
    c=db(); a=c.execute("SELECT a.*,p.name package FROM accounts a LEFT JOIN packages p ON p.id=a.package_id WHERE a.id=?",(session["account_id"],)).fetchone(); c.close()
    return render_template("user/dashboard.html",account=a)

@app.route("/user/<module>")
def generic_user(module):
    x=user_required()
    if x:return x
    titles={"websites":"Websites","domains":"Domains","files":"File Manager","databases":"Databases","email":"Email","ftp":"FTP / SFTP","dns":"DNS","ssl":"SSL / Let's Encrypt","cron":"Cron Jobs","account":"My Account"}
    return render_template("module.html",title=titles.get(module,module.title()),description="Account module ready for integration.",items=[])

init()
# Converge DNS zones after all domain helpers are defined.
try:
    _c=db()
    for _d in _c.execute('SELECT id,domain FROM domains').fetchall(): _ensure_zone(_c,_d['id'],_d['domain'])
    _c.commit(); _c.close()
except Exception:
    pass