from __future__ import annotations

import importlib.util
import mimetypes
import json
import os
import queue
import re
import socket
import sys
from datetime import datetime, timezone
import shutil
import threading
import uuid
from pathlib import Path
from urllib.parse import quote

from fastapi import FastAPI, File, Form, HTTPException, Query, Request, UploadFile
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse, HTMLResponse, RedirectResponse
from fastapi.staticfiles import StaticFiles
from starlette.middleware.wsgi import WSGIMiddleware

from app_core.paths import DB_PATH, LOCAL_ROOT, SHARED_DIR
from app_core.permissions import ACTION_KEYS, EDITABLE_MODULE_KEYS, get_effective_permission_map
from app_core.version import APP_NAME, APP_RELEASE_CHANNEL, APP_VERSION
from tech_pack_parser import parse_tech_pack_pdf
from database import (
    authenticate_user,
    create_password_reset_token,
    delete_costing_styles,
    delete_purchase_order,
    delete_users,
    get_database_mode,
    get_app_setting,
    get_user_by_email,
    get_dashboard_summary,
    get_all_costing_imports,
    get_order,
    init_db,
    is_database_ready,
    get_password_reset_token,
    list_costing_variants_for_web,
    list_orders_for_web,
    list_sample_records,
    list_hr_attendance_records,
    list_hr_employees,
    list_bom_items,
    list_tech_pack_hidden_styles,
    list_tech_pack_records,
    list_users,
    load_permission_settings_from_db,
    register_user,
    save_tech_pack_hidden_styles,
    save_tech_pack_record_patch,
    save_tech_pack_record,
    save_tech_pack_records,
    save_costing_import,
    save_costing_ws_import,
    save_permission_settings_to_db,
    save_purchase_order,
    save_sample_records,
    save_sample_record,
    save_bom_item,
    save_hr_attendance_records,
    save_hr_attendance_record,
    save_hr_employee,
    save_hr_employees,
    set_app_setting,
    normalize_style_identifier,
    hr_attendance_check_in,
    hr_attendance_check_out,
    update_user_active_status,
    update_user_approval_status,
    update_user_role,
    update_user_password_with_token,
    update_costing_ws_row,
    delete_bom_item,
)


SHARED_DIR.mkdir(parents=True, exist_ok=True)
WEB_DIR = Path(__file__).resolve().parent / "web"
DIST_DIR = Path(__file__).resolve().parent.parent / "dist"
ALEX_WEB_DIR = Path(os.environ.get("ALEX_WEB_DIR", str(Path(__file__).resolve().parent.parent / "alex_web")))

app = FastAPI(
    title=f"{APP_NAME} Local Server",
    version=APP_VERSION,
    description="Local backend used to simulate the future online deployment.",
)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

app.mount("/shared", StaticFiles(directory=str(SHARED_DIR)), name="shared")
app.mount("/web/assets", StaticFiles(directory=str(WEB_DIR)), name="web-assets")

TECH_PACK_IMPORT_QUEUE: queue.Queue[dict] = queue.Queue()
TECH_PACK_IMPORT_JOBS: dict[str, dict] = {}
TECH_PACK_IMPORT_LOCK = threading.Lock()
TECH_PACK_IMPORT_JOBS_SETTING_KEY = "tech_pack_import_jobs"


def _load_alex_flask_app() -> object | None:
    alex_entrypoint = ALEX_WEB_DIR / "99_PYTHON" / "web" / "app.py"
    if not alex_entrypoint.exists():
        return None

    previous_base_path = os.environ.get("APP_BASE_PATH")
    added_sys_path = False
    os.environ["APP_BASE_PATH"] = "/alex"
    if str(alex_entrypoint.parent) not in sys.path:
        sys.path.insert(0, str(alex_entrypoint.parent))
        added_sys_path = True
    try:
        spec = importlib.util.spec_from_file_location("sunny_alex_web_app", alex_entrypoint)
        if spec is None or spec.loader is None:
            return None
        module = importlib.util.module_from_spec(spec)
        spec.loader.exec_module(module)
        return getattr(module, "app", None)
    except Exception as exc:
        print(f"[ALEX] Failed to load mounted app: {exc}")
        return None
    finally:
        if previous_base_path is None:
            os.environ.pop("APP_BASE_PATH", None)
        else:
            os.environ["APP_BASE_PATH"] = previous_base_path
        if added_sys_path:
            try:
                sys.path.remove(str(alex_entrypoint.parent))
            except ValueError:
                pass


_alex_flask_app = _load_alex_flask_app()
if _alex_flask_app is not None:
    @app.get("/alex", include_in_schema=False)
    def alex_root_redirect() -> RedirectResponse:
        return RedirectResponse(url="/alex/")

    app.mount("/alex", WSGIMiddleware(_alex_flask_app))


def _persist_tech_pack_import_jobs() -> None:
    with TECH_PACK_IMPORT_LOCK:
        items = [dict(item) for item in TECH_PACK_IMPORT_JOBS.values() if isinstance(item, dict)]
    items.sort(key=lambda item: str(item.get("queued_at", "") or ""))
    set_app_setting(DB_PATH, TECH_PACK_IMPORT_JOBS_SETTING_KEY, {"items": items[-1000:]})


def _load_persisted_tech_pack_import_jobs() -> dict[str, dict]:
    payload = get_app_setting(DB_PATH, TECH_PACK_IMPORT_JOBS_SETTING_KEY, {"items": []})
    items = []
    if isinstance(payload, dict):
        items = list(payload.get("items", []))
    elif isinstance(payload, list):
        items = list(payload)
    jobs: dict[str, dict] = {}
    for item in items:
        if not isinstance(item, dict):
            continue
        job_id = str(item.get("job_id", "")).strip()
        if not job_id:
            continue
        jobs[job_id] = dict(item)
    return jobs


def _restore_pending_tech_pack_import_jobs() -> None:
    restored_jobs = _load_persisted_tech_pack_import_jobs()
    if not restored_jobs:
        return
    with TECH_PACK_IMPORT_LOCK:
        TECH_PACK_IMPORT_JOBS.clear()
        TECH_PACK_IMPORT_JOBS.update(restored_jobs)
    requeued = False
    for job_id, job in restored_jobs.items():
        status = str(job.get("status", "")).strip().lower()
        if status not in {"queued", "processing"}:
            continue
        source_path = Path(str(job.get("source_path", "")).strip())
        if not source_path.exists() or not source_path.is_file():
            _set_tech_pack_import_job(
                job_id,
                status="error",
                finished_at=datetime.now(timezone.utc).isoformat(),
                error="Source file is missing after server restart.",
            )
            continue
        _set_tech_pack_import_job(
            job_id,
            status="queued",
            restarted_at=datetime.now(timezone.utc).isoformat(),
        )
        TECH_PACK_IMPORT_QUEUE.put(
            {
                "job_id": job_id,
                "source_path": str(source_path),
                "source_url": str(job.get("source_url", "")).strip(),
                "base_url": str(job.get("base_url", "")).strip(),
            }
        )
        requeued = True
    if requeued:
        _persist_tech_pack_import_jobs()


def _database_ready() -> bool:
    return is_database_ready(DB_PATH)


@app.on_event("startup")
def startup_init_db() -> None:
    init_db(DB_PATH)
    _restore_pending_tech_pack_import_jobs()
    threading.Thread(target=_tech_pack_import_worker_loop, daemon=True, name="tech-pack-import-worker").start()


def _serialize_row(row: object) -> object:
    if isinstance(row, dict):
        return row
    try:
        return dict(row)  # type: ignore[arg-type]
    except Exception:
        return row


def _safe_local_file(path_value: str) -> Path:
    candidate = Path(path_value).expanduser()
    if not candidate.exists() or not candidate.is_file():
        raise HTTPException(status_code=404, detail="File not found.")

    try:
        candidate.relative_to(LOCAL_ROOT)
    except ValueError as exc:
        raise HTTPException(status_code=403, detail="File is outside the allowed storage root.") from exc

    return candidate


def _safe_local_directory(path_value: str) -> Path:
    raw_value = str(path_value or "").strip()
    if raw_value:
        candidate = Path(raw_value).expanduser()
        if not candidate.is_absolute():
            candidate = LOCAL_ROOT / candidate
    else:
        candidate = LOCAL_ROOT
    candidate = candidate.resolve()
    if not candidate.exists() or not candidate.is_dir():
        raise HTTPException(status_code=404, detail="Folder not found.")

    try:
        candidate.relative_to(LOCAL_ROOT.resolve())
    except ValueError as exc:
        raise HTTPException(status_code=403, detail="Folder is outside the allowed storage root.") from exc

    return candidate


def _safe_local_path(path_value: str) -> Path:
    raw_value = str(path_value or "").strip()
    if not raw_value:
        raise HTTPException(status_code=400, detail="path is required.")
    candidate = Path(raw_value).expanduser()
    candidate = candidate.resolve()
    if not candidate.exists():
        raise HTTPException(status_code=404, detail="Path not found.")
    try:
        candidate.relative_to(LOCAL_ROOT.resolve())
    except ValueError as exc:
        raise HTTPException(status_code=403, detail="Path is outside the allowed storage root.") from exc
    return candidate


def _safe_child_path(base_dir: Path, relative_path: str) -> Path:
    target = (base_dir / str(relative_path or "").strip()).resolve()
    try:
        target.relative_to(LOCAL_ROOT.resolve())
    except ValueError as exc:
        raise HTTPException(status_code=403, detail="Path is outside the allowed storage root.") from exc
    return target


def _effective_permissions_for_user(user: dict | None) -> dict[str, bool]:
    if not isinstance(user, dict):
        return {}
    role = str(user.get("role", "user") or "user").strip().lower() or "user"
    if role == "admin":
        permission_map = {f"module.{module_key}": True for module_key in EDITABLE_MODULE_KEYS}
        permission_map.update({action_key: True for action_key in ACTION_KEYS})
        return permission_map
    email = str(user.get("email", "") or "").strip().lower()
    settings = load_permission_settings_from_db(DB_PATH)
    return get_effective_permission_map(settings, role, email)


def _require_admin_user(email: str) -> dict:
    normalized_email = str(email or "").strip().lower()
    if not normalized_email:
        raise HTTPException(status_code=400, detail="email is required.")
    user = get_user_by_email(DB_PATH, normalized_email)
    if user is None:
        raise HTTPException(status_code=404, detail="User not found.")
    if int(user.get("is_active", 0) or 0) != 1 or str(user.get("approval_status", "") or "").strip().lower() != "approved":
        raise HTTPException(status_code=403, detail="User is not allowed to access server files.")
    if str(user.get("role", "") or "").strip().lower() != "admin":
        raise HTTPException(status_code=403, detail="Only admin accounts can access Folder Explorer.")
    user.pop("password_hash", None)
    return user


def _variant_payload(variant: dict) -> dict:
    image_path = str(variant.get("image_path") or "").strip()
    image_url = f"/api/files?path={quote(image_path)}" if image_path else ""
    return {
        **variant,
        "image_url": image_url,
    }


def _copy_runtime_file_to_shared(path_value: str, *, bucket: str) -> str:
    raw_path = str(path_value or "").strip()
    if not raw_path:
        return ""
    source = Path(raw_path)
    if not source.exists() or not source.is_file():
        return raw_path
    destination_dir = SHARED_DIR / bucket / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
    destination_dir.mkdir(parents=True, exist_ok=True)
    destination = destination_dir / source.name
    shutil.copy2(source, destination)
    return str(destination)


def _sanitize_shared_bucket(bucket: str) -> str:
    normalized = re.sub(r"[^A-Za-z0-9._-]+", "-", str(bucket or "").strip()).strip("-")
    return normalized or "uploads"


def _sanitize_tech_pack_style_folder(style_code: str) -> str:
    normalized = re.sub(r"[^A-Za-z0-9._-]+", "-", str(style_code or "").strip()).strip("-")
    return normalized or "UNKNOWN"


def _dedupe_filename_in_directory(directory: Path, filename: str) -> Path:
    original_name = Path(str(filename or "")).name or "tech-pack.pdf"
    suffix = Path(original_name).suffix or ".pdf"
    stem = Path(original_name).stem or "tech-pack"
    candidate = directory / f"{stem}{suffix}"
    duplicate_index = 1
    while candidate.exists():
        candidate = directory / f"{stem}({duplicate_index}){suffix}"
        duplicate_index += 1
    return candidate


def _finalize_tech_pack_source_path(source_path: Path, style_code: str) -> Path:
    if not source_path.exists() or not source_path.is_file():
        return source_path
    style_dir = SHARED_DIR / "tech-pack-sources" / _sanitize_tech_pack_style_folder(style_code)
    style_dir.mkdir(parents=True, exist_ok=True)
    target_path = _dedupe_filename_in_directory(style_dir, source_path.name)
    if source_path.resolve() == target_path.resolve():
        return target_path
    shutil.move(str(source_path), str(target_path))
    try:
        source_dir = source_path.parent
        if source_dir != style_dir and source_dir.exists() and not any(source_dir.iterdir()):
            source_dir.rmdir()
    except Exception:
        pass
    return target_path


def _public_shared_url(base_url: str, path_value: str | Path) -> str:
    try:
        relative_path = Path(str(path_value)).resolve().relative_to(SHARED_DIR.resolve()).as_posix()
    except Exception:
        return str(path_value)
    return f"{base_url.rstrip('/')}/shared/{relative_path}"


def _apply_public_urls_to_tech_pack_record(record: dict, *, base_url: str, source_url: str) -> dict:
    normalized = json.loads(json.dumps(record or {}))
    normalized["source_files"] = [source_url] if source_url else [str(item).strip() for item in list(normalized.get("source_files", [])) if str(item).strip()]
    preview_images = [str(item).strip() for item in list(normalized.get("preview_images", [])) if str(item).strip()]
    normalized["preview_images"] = [
        _public_shared_url(base_url, item) if item else item
        for item in preview_images
    ]
    image_path = str(normalized.get("image_path", "")).strip()
    if image_path:
        normalized["image_path"] = _public_shared_url(base_url, image_path)
    for section in list(normalized.get("bom_data", [])):
        if not isinstance(section, dict):
            continue
        thumbnail_path = str(section.get("thumbnail_path", "")).strip()
        if thumbnail_path:
            section["thumbnail_path"] = _public_shared_url(base_url, thumbnail_path)
        for item in list(section.get("items", [])):
            if not isinstance(item, dict):
                continue
            item_thumbnail = str(item.get("thumbnail_path", "")).strip()
            if item_thumbnail:
                item["thumbnail_path"] = _public_shared_url(base_url, item_thumbnail)
    return normalized


def _merge_tech_pack_record(existing: dict, inferred: dict) -> dict:
    style_code = str(inferred.get("style_code", "")).strip()
    merged = dict(existing or {})
    merged.setdefault("style_code", style_code)
    merged_files: list[str] = []
    for source_file in list(inferred.get("source_files", [])) + list(merged.get("source_files", [])):
        normalized_source = str(source_file or "").strip()
        if normalized_source and normalized_source not in merged_files:
            merged_files.append(normalized_source)
    merged["style_code"] = style_code
    merged["source_files"] = merged_files
    merged["last_updated"] = str(inferred.get("last_updated", "")).strip()
    for key in (
        "style_name",
        "season",
        "collection",
        "image_path",
        "factory",
        "sample_stage",
        "production_status",
        "quality_status",
        "overview_notes",
        "instructions_notes",
        "stitching_notes",
        "pom_notes",
        "events_notes",
    ):
        incoming_value = str(inferred.get(key, "") or "").strip()
        if incoming_value:
            merged[key] = incoming_value
    preview_images = [str(item).strip() for item in list(inferred.get("preview_images", [])) if str(item).strip()]
    if preview_images:
        merged["preview_images"] = preview_images
    for key in (
        "overview_data",
        "bom_data",
        "construction_data",
        "stitching_data",
        "pom_data",
        "design_intent_data",
        "attachments_data",
    ):
        incoming_value = inferred.get(key, [] if key.endswith("_data") else {})
        if isinstance(incoming_value, dict) and incoming_value:
            merged[key] = incoming_value
        elif isinstance(incoming_value, list) and incoming_value:
            merged[key] = incoming_value
    if list(merged.get("bom_data", [])):
        merged["bom_summary"] = ""
    if list(merged.get("construction_data", [])):
        merged["construction_summary"] = ""
    if isinstance(merged.get("overview_data", {}), dict) and merged.get("overview_data"):
        merged["sample_summary"] = ""
        merged["production_summary"] = ""
    if list(merged.get("stitching_data", [])) or list(merged.get("pom_data", [])):
        merged["quality_summary"] = ""
    return merged


def _set_tech_pack_import_job(job_id: str, **updates) -> None:
    with TECH_PACK_IMPORT_LOCK:
        job = dict(TECH_PACK_IMPORT_JOBS.get(job_id, {}))
        job.update(updates)
        TECH_PACK_IMPORT_JOBS[job_id] = job
    _persist_tech_pack_import_jobs()


def _process_tech_pack_import_job(job_id: str, job: dict) -> dict:
    try:
        _set_tech_pack_import_job(job_id, status="processing", started_at=datetime.now(timezone.utc).isoformat())
        source_path = Path(str(job.get("source_path", "")).strip())
        preview_dir = SHARED_DIR / "tech-pack-previews" / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
        preview_dir.mkdir(parents=True, exist_ok=True)
        parsed = parse_tech_pack_pdf(source_path, preview_dir=preview_dir)
        parsed["last_updated"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        target_style_code = normalize_style_identifier(job.get("target_style_code"))
        parsed_style_code = str(parsed.get("style_code", "")).strip()
        if target_style_code:
            if parsed_style_code and parsed_style_code != target_style_code:
                parsed["source_style_code"] = parsed_style_code
                parsed["customer_search_style_code"] = parsed_style_code
            parsed["style_code"] = target_style_code
        style_code = str(parsed.get("style_code", "")).strip()
        final_source_path = source_path
        final_source_url = str(job.get("source_url", "")).strip()
        if style_code:
            final_source_path = _finalize_tech_pack_source_path(source_path, style_code)
            final_source_url = _public_shared_url(str(job.get("base_url", "")).strip(), final_source_path)
            _set_tech_pack_import_job(
                job_id,
                source_path=str(final_source_path),
                source_url=final_source_url,
                filename=final_source_path.name,
            )
        inferred = _apply_public_urls_to_tech_pack_record(
            parsed,
            base_url=str(job.get("base_url", "")).strip(),
            source_url=final_source_url,
        )
        current_rows = list_tech_pack_records(DB_PATH)
        library_by_style = {
            str(row.get("style_code", "")).strip(): dict(row)
            for row in current_rows
            if str(row.get("style_code", "")).strip()
        }
        style_code = str(inferred.get("style_code", "")).strip()
        existing = dict(library_by_style.get(style_code, {}))
        library_by_style[style_code] = _merge_tech_pack_record(existing, inferred)
        saved_rows = save_tech_pack_records(DB_PATH, list(library_by_style.values()))
        hidden_styles = set(list_tech_pack_hidden_styles(DB_PATH))
        if style_code in hidden_styles:
            hidden_styles.discard(style_code)
            save_tech_pack_hidden_styles(DB_PATH, sorted(hidden_styles))
        _set_tech_pack_import_job(
            job_id,
            status="done",
            finished_at=datetime.now(timezone.utc).isoformat(),
            style_code=style_code,
            saved_count=len(saved_rows),
        )
    except Exception as exc:
        _set_tech_pack_import_job(
            job_id,
            status="error",
            finished_at=datetime.now(timezone.utc).isoformat(),
            error=str(exc),
        )
    with TECH_PACK_IMPORT_LOCK:
        return dict(TECH_PACK_IMPORT_JOBS.get(job_id, {}))


def _queue_tech_pack_import(
    request: Request,
    source_path: Path,
    *,
    original_name: str,
    process_inline: bool = False,
    target_style_code: object = "",
) -> dict:
    relative_path = source_path.relative_to(SHARED_DIR).as_posix()
    base_url = _external_base_url(request)
    source_url = f"{base_url}/shared/{relative_path}"
    job_id = uuid.uuid4().hex
    queued_at = datetime.now(timezone.utc).isoformat()
    job = {
        "job_id": job_id,
        "status": "queued",
        "filename": original_name,
        "source_path": str(source_path),
        "source_url": source_url,
        "base_url": base_url,
        "target_style_code": normalize_style_identifier(target_style_code),
        "queued_at": queued_at,
    }
    TECH_PACK_IMPORT_JOBS[job_id] = job
    _persist_tech_pack_import_jobs()
    if process_inline:
        processed = _process_tech_pack_import_job(job_id, job)
        processed.update({"ok": True, "job_id": job_id, "source_url": processed.get("source_url", source_url)})
        return processed
    TECH_PACK_IMPORT_QUEUE.put(
        {
            "job_id": job_id,
            "source_path": str(source_path),
            "source_url": source_url,
            "base_url": base_url,
        }
    )
    _persist_tech_pack_import_jobs()
    return {
        "ok": True,
        "job_id": job_id,
        "status": "queued",
        "filename": original_name,
        "source_url": source_url,
        "queued_at": queued_at,
    }


def _tech_pack_import_worker_loop() -> None:
    while True:
        job = TECH_PACK_IMPORT_QUEUE.get()
        job_id = str(job.get("job_id", "")).strip()
        try:
            _process_tech_pack_import_job(job_id, job)
        finally:
            TECH_PACK_IMPORT_QUEUE.task_done()


def _prepare_costing_payload_for_host(costing_payload: dict) -> dict:
    normalized = json.loads(json.dumps(costing_payload or {}))
    normalized["debug_dir"] = ""
    items = list(normalized.get("items", []))
    for item in items:
        if not isinstance(item, dict):
            continue
        item["image_path"] = _copy_runtime_file_to_shared(str(item.get("image_path", "")), bucket="costing-images")
        item["page_image_path"] = _copy_runtime_file_to_shared(str(item.get("page_image_path", "")), bucket="costing-pages")
    normalized["items"] = items
    return normalized


def _latest_release_file() -> Path | None:
    package_zip = DIST_DIR / "SunnyERPApp_package.zip"
    if package_zip.exists() and package_zip.is_file():
        return package_zip

    exe_file = DIST_DIR / "SunnyERPApp" / "SunnyERPApp.exe"
    if exe_file.exists() and exe_file.is_file():
        return exe_file

    return None


def _latest_macos_release_file() -> Path | None:
    for filename in ("SunnyERPApp_macOS_arm64.zip", "SunnyERPApp_macOS.zip"):
        release_file = DIST_DIR / filename
        if release_file.exists() and release_file.is_file():
            return release_file
    return None


def _macos_release_payload() -> dict:
    release_file = _latest_macos_release_file()
    metadata_file = DIST_DIR / "macos_latest.json"
    metadata = {}
    if metadata_file.exists() and metadata_file.is_file():
        try:
            metadata = json.loads(metadata_file.read_text(encoding="utf-8"))
        except Exception:
            metadata = {}
    if release_file is None:
        return {
            "available": False,
            "version": metadata.get("version", APP_VERSION),
            "channel": "macos-arm64",
            "download_url": "",
            "guide_url": "/downloads/macos/guide",
            "filename": "",
            "size_bytes": 0,
            "updated_at_utc": "",
            "sha256": "",
            "commit": metadata.get("commit", ""),
            "notes": "No packaged macOS build found in dist yet.",
        }
    return {
        "available": True,
        "version": metadata.get("version", APP_VERSION),
        "channel": metadata.get("channel", "macos-arm64"),
        "download_url": "/downloads/macos/latest",
        "guide_url": "/downloads/macos/guide",
        "filename": release_file.name,
        "size_bytes": release_file.stat().st_size,
        "updated_at_utc": metadata.get(
            "updated_at_utc",
            datetime.fromtimestamp(release_file.stat().st_mtime, tz=timezone.utc).isoformat(),
        ),
        "sha256": metadata.get("sha256", ""),
        "commit": metadata.get("commit", ""),
        "notes": metadata.get("notes", "Latest packaged macOS Apple Silicon build."),
    }


def _release_payload() -> dict:
    release_file = _latest_release_file()
    if release_file is None:
        return {
            "available": False,
            "version": APP_VERSION,
            "channel": APP_RELEASE_CHANNEL,
            "download_url": "",
            "filename": "",
            "size_bytes": 0,
            "updated_at_utc": "",
            "notes": "No packaged desktop build found in dist yet.",
        }

    return {
        "available": True,
        "version": APP_VERSION,
        "channel": APP_RELEASE_CHANNEL,
        "download_url": "/downloads/latest",
        "filename": release_file.name,
        "size_bytes": release_file.stat().st_size,
        "updated_at_utc": datetime.fromtimestamp(release_file.stat().st_mtime, tz=timezone.utc).isoformat(),
        "notes": "Latest packaged desktop build from the local dist folder.",
    }


def _external_base_url(request: Request) -> str:
    forwarded_proto = request.headers.get("x-forwarded-proto")
    forwarded_host = request.headers.get("x-forwarded-host")
    if forwarded_proto and forwarded_host:
        return f"{forwarded_proto}://{forwarded_host}"
    return str(request.base_url).rstrip("/")


@app.get("/")
def root() -> RedirectResponse:
    return RedirectResponse(url="/web", status_code=307)


@app.get("/web", response_class=HTMLResponse)
def web_dashboard() -> HTMLResponse:
    index_path = WEB_DIR / "index.html"
    if not index_path.exists():
        raise HTTPException(status_code=500, detail="Web dashboard assets are missing.")
    html = index_path.read_text(encoding="utf-8")
    asset_version = quote(APP_VERSION, safe="")
    html = html.replace("{{ASSET_VERSION}}", asset_version)
    html = html.replace("{{APP_VERSION}}", APP_VERSION)
    return HTMLResponse(html)


def _render_reset_form(*, token: str, message: str = "", success: bool = False) -> str:
    color = "#2f855a" if success else "#8a4b1f"
    banner = f"<p style='color:{color};white-space:pre-wrap'>{message}</p>" if message else ""
    return f"""
    <!DOCTYPE html>
    <html lang="en">
    <head>
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <title>{APP_NAME} Reset Password</title>
      <link rel="icon" type="image/png" href="/web/assets/favicon.png">
      <style>
        body {{ font-family: Segoe UI, Arial, sans-serif; background: #f4f6f8; margin: 0; padding: 32px; }}
        .card {{ max-width: 480px; margin: 0 auto; background: #fff; border-radius: 16px; padding: 24px; box-shadow: 0 20px 50px rgba(0,0,0,.08); }}
        input {{ width: 100%; padding: 12px; margin-top: 6px; margin-bottom: 14px; box-sizing: border-box; }}
        button {{ background: #b65c2e; color: #fff; border: 0; padding: 12px 16px; cursor: pointer; border-radius: 10px; }}
      </style>
    </head>
    <body>
      <div class="card">
        <h2>Reset Password</h2>
        <p>Enter a new password for your CitiFox ERP App account.</p>
        {banner}
        <form method="post" action="/auth/reset-password">
          <input type="hidden" name="token" value="{token}">
          <label>New Password</label>
          <input type="password" name="password" minlength="8" required>
          <label>Confirm Password</label>
          <input type="password" name="confirm_password" minlength="8" required>
          <button type="submit">Update Password</button>
        </form>
      </div>
    </body>
    </html>
    """


def _render_open_in_app(*, token: str) -> str:
    app_link = f"poapp://reset-password?token={quote(token, safe='')}"
    return f"""
    <!DOCTYPE html>
    <html lang="en">
    <head>
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <title>{APP_NAME} Open In App</title>
      <link rel="icon" type="image/png" href="/web/assets/favicon.png">
      <style>
        body {{ font-family: Segoe UI, Arial, sans-serif; background: #f4f6f8; margin: 0; padding: 32px; }}
        .card {{ max-width: 560px; margin: 0 auto; background: #fff; border-radius: 16px; padding: 24px; box-shadow: 0 20px 50px rgba(0,0,0,.08); }}
        a.button {{ display: inline-block; background: #b65c2e; color: #fff; text-decoration: none; padding: 12px 16px; border-radius: 10px; }}
      </style>
    </head>
    <body>
      <div class="card">
        <h2>Open Reset Form In App</h2>
        <p>The browser will try to open CitiFox ERP App automatically.</p>
        <p>If nothing happens, click the button below.</p>
        <p><a class="button" href="{app_link}">Open in CitiFox ERP App</a></p>
      </div>
      <script>
        window.setTimeout(function() {{
          window.location.href = "{app_link}";
        }}, 250);
      </script>
    </body>
    </html>
    """


@app.get("/auth/reset-password", response_class=HTMLResponse)
def auth_reset_password_form(token: str = Query(..., min_length=20)) -> HTMLResponse:
    token_row = get_password_reset_token(DB_PATH, token)
    if token_row is None:
        return HTMLResponse(_render_reset_form(token=token, message="Reset token is invalid."), status_code=400)
    return HTMLResponse(_render_reset_form(token=token))


@app.get("/auth/open-in-app", response_class=HTMLResponse)
def auth_open_in_app(token: str = Query(..., min_length=20)) -> HTMLResponse:
    token_row = get_password_reset_token(DB_PATH, token)
    if token_row is None:
        return HTMLResponse(_render_reset_form(token=token, message="Reset token is invalid."), status_code=400)
    return HTMLResponse(_render_open_in_app(token=token))


@app.post("/auth/reset-password", response_class=HTMLResponse)
def auth_reset_password_submit(
    token: str = Form(...),
    password: str = Form(...),
    confirm_password: str = Form(...),
) -> HTMLResponse:
    if len(password) < 8:
        return HTMLResponse(_render_reset_form(token=token, message="Password must be at least 8 characters."), status_code=400)
    if password != confirm_password:
        return HTMLResponse(_render_reset_form(token=token, message="Password confirmation does not match."), status_code=400)
    try:
        update_user_password_with_token(DB_PATH, token, password)
    except Exception as exc:
        return HTMLResponse(_render_reset_form(token=token, message=str(exc)), status_code=400)
    return HTMLResponse(_render_reset_form(token=token, message="Password updated successfully. You can return to the desktop app and log in now.", success=True))


@app.get("/health")
def health() -> dict:
    return {
        "ok": True,
        "status": "healthy",
        "service": f"{APP_NAME} Local Server",
        "version": APP_VERSION,
        "channel": APP_RELEASE_CHANNEL,
        "database_mode": get_database_mode(),
        "hostname": socket.gethostname(),
        "timestamp_utc": datetime.now(timezone.utc).isoformat(),
        "database_ready": _database_ready(),
    }


@app.get("/manifests/desktop.json")
def desktop_manifest() -> dict:
    release = _release_payload()
    return {
        "product": APP_NAME,
        "channel": APP_RELEASE_CHANNEL,
        "version": APP_VERSION,
        "download_url": release["download_url"],
        "notes": release["notes"],
        "generated_at_utc": datetime.now(timezone.utc).isoformat(),
    }


@app.get("/server-info")
def server_info(request: Request) -> dict:
    base_url = _external_base_url(request)
    summary = get_dashboard_summary(DB_PATH) if _database_ready() else None
    return {
        "environment": "hosted",
        "hostname": socket.gethostname(),
        "shared_files_url": f"{base_url}/shared",
        "database_mode": get_database_mode(),
        "web_dashboard_url": f"{base_url}/web",
        "database_ready": _database_ready(),
        "latest_release": _release_payload(),
        "summary": summary,
        "next_step": "Connect the desktop app to this hosted API base URL.",
    }


@app.get("/api/releases/latest")
def api_latest_release() -> dict:
    return _release_payload()


@app.get("/api/releases/macos/latest")
def api_latest_macos_release() -> dict:
    return _macos_release_payload()


@app.get("/api/dashboard/summary")
def api_dashboard_summary() -> dict:
    if not _database_ready():
        return {
            "database_ready": False,
            "summary": {
                "orders": 0,
                "line_items": 0,
                "costing_imports": 0,
                "costing_variants": 0,
                "latest_order": None,
            },
        }
    return {
        "database_ready": True,
        "summary": get_dashboard_summary(DB_PATH),
    }


@app.get("/api/orders")
def api_orders(
    search: str = Query(default="", max_length=100),
    limit: int = Query(default=100, ge=1, le=5000),
) -> dict:
    if not _database_ready():
        return {"items": [], "count": 0}

    normalized_search = str(search or "").strip()
    search_digits = "".join(ch for ch in normalized_search if ch.isdigit())
    if search_digits and search_digits == normalized_search and search_digits.startswith("0"):
        normalized_search = search_digits.lstrip("0") or "0"
    rows = list_orders_for_web(DB_PATH, search=normalized_search, limit=limit)
    items = [_serialize_row(row) for row in rows]
    return {"items": items, "count": len(items)}


@app.post("/api/auth/login")
def api_auth_login(payload: dict) -> dict:
    email = str((payload or {}).get("email", "")).strip()
    password = str((payload or {}).get("password", ""))
    user = authenticate_user(DB_PATH, email, password)
    if user is None:
        raise HTTPException(status_code=401, detail="Invalid credentials.")
    return {
        "ok": True,
        "user": user,
        "effective_permissions": _effective_permissions_for_user(user),
    }


@app.get("/api/auth/context")
def api_auth_context(email: str = Query(..., min_length=3)) -> dict:
    user = get_user_by_email(DB_PATH, email)
    if user is None:
        raise HTTPException(status_code=404, detail="User not found.")
    user.pop("password_hash", None)
    if int(user.get("is_active", 0) or 0) != 1 or str(user.get("approval_status", "") or "").strip().lower() != "approved":
        raise HTTPException(status_code=403, detail="User is not approved for web access.")
    return {
        "ok": True,
        "user": user,
        "effective_permissions": _effective_permissions_for_user(user),
    }


@app.post("/api/auth/register")
def api_auth_register(payload: dict) -> dict:
    try:
        user = register_user(
            DB_PATH,
            email=str((payload or {}).get("email", "")).strip(),
            password=str((payload or {}).get("password", "")),
            display_name=str((payload or {}).get("display_name", "")).strip(),
            role=str((payload or {}).get("role", "user")).strip().lower() or "user",
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True, "user": user}


@app.post("/api/auth/password-reset-token")
def api_create_password_reset_token(payload: dict) -> dict:
    try:
        token = create_password_reset_token(
            DB_PATH,
            str((payload or {}).get("email", "")).strip(),
            str((payload or {}).get("expires_at", "")).strip(),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True, "token": token}


@app.get("/api/auth/password-reset-token")
def api_get_password_reset_token(token: str = Query(..., min_length=20)) -> dict:
    token_row = get_password_reset_token(DB_PATH, token)
    if token_row is None:
        raise HTTPException(status_code=404, detail="Reset token not found.")
    return {"ok": True, "token_row": token_row}


@app.post("/api/auth/password-reset")
def api_reset_password(payload: dict) -> dict:
    try:
        update_user_password_with_token(
            DB_PATH,
            str((payload or {}).get("token", "")).strip(),
            str((payload or {}).get("password", "")),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True}


@app.get("/api/users")
def api_users() -> dict:
    items = list_users(DB_PATH)
    return {"items": items, "count": len(items)}


@app.get("/api/user")
def api_user(email: str = Query(..., min_length=3)) -> dict:
    user = get_user_by_email(DB_PATH, email)
    if user is None:
        raise HTTPException(status_code=404, detail="User not found.")
    user.pop("password_hash", None)
    return {"user": user}


@app.post("/api/users/role")
def api_update_user_role(payload: dict) -> dict:
    try:
        update_user_role(
            DB_PATH,
            str((payload or {}).get("email", "")).strip(),
            str((payload or {}).get("role", "user")).strip().lower() or "user",
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True}


@app.post("/api/users/active")
def api_update_user_active(payload: dict) -> dict:
    try:
        update_user_active_status(
            DB_PATH,
            str((payload or {}).get("email", "")).strip(),
            bool((payload or {}).get("is_active", False)),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True}


@app.post("/api/users/approval")
def api_update_user_approval(payload: dict) -> dict:
    try:
        update_user_approval_status(
            DB_PATH,
            str((payload or {}).get("email", "")).strip(),
            str((payload or {}).get("approval_status", "pending")).strip().lower() or "pending",
            approved_by=str((payload or {}).get("approved_by", "")).strip(),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True}


@app.post("/api/users/delete")
def api_delete_users(payload: dict) -> dict:
    emails = [str(item).strip() for item in list((payload or {}).get("emails", []))]
    delete_users(DB_PATH, emails)
    return {"ok": True, "deleted_count": len([item for item in emails if item])}


@app.get("/api/permissions")
def api_permissions() -> dict:
    return {"permissions": load_permission_settings_from_db(DB_PATH)}


@app.post("/api/permissions")
def api_save_permissions(payload: dict) -> dict:
    permissions = (payload or {}).get("permissions", {})
    if not isinstance(permissions, dict):
        raise HTTPException(status_code=400, detail="permissions payload is required.")
    saved = save_permission_settings_to_db(DB_PATH, permissions)
    return {"ok": True, "permissions": saved}


@app.get("/api/app-setting")
def api_get_app_setting(key: str = Query(..., min_length=1)) -> dict:
    value = get_app_setting(DB_PATH, key, None)
    if value is None:
        raise HTTPException(status_code=404, detail="Setting not found.")
    return {"key": key, "value": value}


@app.post("/api/app-setting")
def api_set_app_setting(payload: dict) -> dict:
    key = str((payload or {}).get("key", "")).strip()
    if not key:
        raise HTTPException(status_code=400, detail="key is required.")
    value = (payload or {}).get("value")
    saved = set_app_setting(DB_PATH, key, value)
    return {"ok": True, "key": key, "value": saved}


@app.post("/api/shared/upload")
async def api_upload_shared_file(
    request: Request,
    bucket: str = Form(default="uploads"),
    file: UploadFile = File(...),
) -> dict:
    if file is None or not str(file.filename or "").strip():
        raise HTTPException(status_code=400, detail="file is required.")
    safe_bucket = _sanitize_shared_bucket(bucket)
    original_name = Path(str(file.filename)).name or f"upload-{uuid.uuid4().hex}"
    destination_dir = SHARED_DIR / safe_bucket / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
    destination_dir.mkdir(parents=True, exist_ok=True)
    destination = destination_dir / original_name
    contents = await file.read()
    destination.write_bytes(contents)
    relative_path = destination.relative_to(SHARED_DIR).as_posix()
    base_url = _external_base_url(request)
    return {
        "ok": True,
        "bucket": safe_bucket,
        "filename": original_name,
        "relative_path": relative_path,
        "shared_path": str(destination),
        "url": f"{base_url}/shared/{relative_path}",
    }


@app.post("/api/shared/upload-chunk")
async def api_upload_shared_file_chunk(
    upload_id: str = Form(...),
    bucket: str = Form(default="uploads"),
    filename: str = Form(...),
    chunk_index: int = Form(...),
    total_chunks: int = Form(...),
    file: UploadFile = File(...),
) -> dict:
    normalized_upload_id = re.sub(r"[^A-Za-z0-9_-]+", "", str(upload_id or "").strip())
    original_name = Path(str(filename or "")).name or "upload.bin"
    if not normalized_upload_id:
        raise HTTPException(status_code=400, detail="upload_id is required.")
    if total_chunks <= 0 or chunk_index < 0 or chunk_index >= total_chunks:
        raise HTTPException(status_code=400, detail="Invalid chunk metadata.")
    safe_bucket = _sanitize_shared_bucket(bucket)
    chunk_dir = SHARED_DIR / "_shared_chunk_uploads" / normalized_upload_id
    chunk_dir.mkdir(parents=True, exist_ok=True)
    metadata_path = chunk_dir / "metadata.json"
    metadata_path.write_text(
        json.dumps({"bucket": safe_bucket, "filename": original_name, "total_chunks": total_chunks}, ensure_ascii=False),
        encoding="utf-8",
    )
    chunk_path = chunk_dir / f"{chunk_index:06d}.part"
    chunk_path.write_bytes(await file.read())
    return {
        "ok": True,
        "upload_id": normalized_upload_id,
        "bucket": safe_bucket,
        "filename": original_name,
        "chunk_index": chunk_index,
        "total_chunks": total_chunks,
    }


@app.post("/api/shared/upload-complete")
async def api_upload_shared_file_complete(request: Request, payload: dict) -> dict:
    normalized_upload_id = re.sub(r"[^A-Za-z0-9_-]+", "", str((payload or {}).get("upload_id", "")).strip())
    if not normalized_upload_id:
        raise HTTPException(status_code=400, detail="upload_id is required.")
    chunk_dir = SHARED_DIR / "_shared_chunk_uploads" / normalized_upload_id
    metadata_path = chunk_dir / "metadata.json"
    if not metadata_path.exists():
        raise HTTPException(status_code=400, detail="Upload metadata is missing.")
    try:
        metadata = json.loads(metadata_path.read_text(encoding="utf-8"))
    except Exception:
        metadata = {}
    safe_bucket = _sanitize_shared_bucket(metadata.get("bucket", "uploads"))
    original_name = Path(str(metadata.get("filename", "")).strip()).name or "upload.bin"
    total_chunks = int(metadata.get("total_chunks", 0) or 0)
    if total_chunks <= 0:
        raise HTTPException(status_code=400, detail="total_chunks is required.")
    expected_parts = [chunk_dir / f"{index:06d}.part" for index in range(total_chunks)]
    missing_parts = [part.name for part in expected_parts if not part.exists()]
    if missing_parts:
        raise HTTPException(status_code=400, detail=f"Missing chunk(s): {', '.join(missing_parts[:5])}")
    destination_dir = SHARED_DIR / safe_bucket / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
    destination_dir.mkdir(parents=True, exist_ok=True)
    destination = destination_dir / original_name
    with destination.open("wb") as target:
        for part_path in expected_parts:
            with part_path.open("rb") as source:
                shutil.copyfileobj(source, target)
    shutil.rmtree(chunk_dir, ignore_errors=True)
    relative_path = destination.relative_to(SHARED_DIR).as_posix()
    base_url = _external_base_url(request)
    return {
        "ok": True,
        "bucket": safe_bucket,
        "filename": original_name,
        "relative_path": relative_path,
        "shared_path": str(destination),
        "url": f"{base_url}/shared/{relative_path}",
    }


@app.post("/api/tech-pack/import-source")
async def api_import_tech_pack_source(
    request: Request,
    file: UploadFile = File(...),
    target_style_code: str = Form(default=""),
) -> dict:
    if file is None or not str(file.filename or "").strip():
        raise HTTPException(status_code=400, detail="file is required.")
    original_name = Path(str(file.filename)).name or f"tech-pack-{uuid.uuid4().hex}.pdf"
    destination_dir = SHARED_DIR / "tech-pack-sources" / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
    destination_dir.mkdir(parents=True, exist_ok=True)
    destination = destination_dir / original_name
    contents = await file.read()
    destination.write_bytes(contents)
    return _queue_tech_pack_import(
        request,
        destination,
        original_name=original_name,
        process_inline=True,
        target_style_code=target_style_code,
    )


@app.post("/api/tech-pack/import-source-chunk")
async def api_import_tech_pack_source_chunk(
    upload_id: str = Form(...),
    filename: str = Form(...),
    chunk_index: int = Form(...),
    total_chunks: int = Form(...),
    file: UploadFile = File(...),
) -> dict:
    normalized_upload_id = re.sub(r"[^A-Za-z0-9_-]+", "", str(upload_id or "").strip())
    original_name = Path(str(filename or "")).name or "tech-pack.pdf"
    if not normalized_upload_id:
        raise HTTPException(status_code=400, detail="upload_id is required.")
    if total_chunks <= 0 or chunk_index < 0 or chunk_index >= total_chunks:
        raise HTTPException(status_code=400, detail="Invalid chunk metadata.")
    chunk_dir = SHARED_DIR / "_chunk_uploads" / normalized_upload_id
    chunk_dir.mkdir(parents=True, exist_ok=True)
    chunk_path = chunk_dir / f"{chunk_index:06d}.part"
    chunk_path.write_bytes(await file.read())
    return {
        "ok": True,
        "upload_id": normalized_upload_id,
        "filename": original_name,
        "chunk_index": chunk_index,
        "total_chunks": total_chunks,
    }


@app.post("/api/tech-pack/import-source-complete")
def api_import_tech_pack_source_complete(request: Request, payload: dict) -> dict:
    normalized_upload_id = re.sub(r"[^A-Za-z0-9_-]+", "", str((payload or {}).get("upload_id", "")).strip())
    original_name = Path(str((payload or {}).get("filename", "")).strip()).name or "tech-pack.pdf"
    target_style_code = normalize_style_identifier((payload or {}).get("target_style_code"))
    total_chunks = int((payload or {}).get("total_chunks", 0) or 0)
    if not normalized_upload_id:
        raise HTTPException(status_code=400, detail="upload_id is required.")
    if total_chunks <= 0:
        raise HTTPException(status_code=400, detail="total_chunks is required.")
    chunk_dir = SHARED_DIR / "_chunk_uploads" / normalized_upload_id
    expected_parts = [chunk_dir / f"{index:06d}.part" for index in range(total_chunks)]
    missing_parts = [part.name for part in expected_parts if not part.exists()]
    if missing_parts:
        raise HTTPException(status_code=400, detail=f"Missing chunk(s): {', '.join(missing_parts[:5])}")
    destination_dir = SHARED_DIR / "tech-pack-sources" / datetime.now(timezone.utc).strftime("%Y%m%d") / uuid.uuid4().hex
    destination_dir.mkdir(parents=True, exist_ok=True)
    destination = destination_dir / original_name
    with destination.open("wb") as target:
        for part_path in expected_parts:
            with part_path.open("rb") as source:
                shutil.copyfileobj(source, target)
    shutil.rmtree(chunk_dir, ignore_errors=True)
    return _queue_tech_pack_import(
        request,
        destination,
        original_name=original_name,
        process_inline=True,
        target_style_code=target_style_code,
    )


@app.get("/api/tech-pack/import-jobs")
def api_get_tech_pack_import_jobs(job_ids: str = Query(default="")) -> dict:
    requested_ids = {
        str(item).strip()
        for item in str(job_ids or "").split(",")
        if str(item).strip()
    }
    with TECH_PACK_IMPORT_LOCK:
        if requested_ids:
            items = [dict(TECH_PACK_IMPORT_JOBS[job_id]) for job_id in requested_ids if job_id in TECH_PACK_IMPORT_JOBS]
        else:
            items = list(TECH_PACK_IMPORT_JOBS.values())[-100:]
    return {"items": items, "count": len(items)}


@app.get("/api/tech-pack/records")
def api_get_tech_pack_records() -> dict:
    items = list_tech_pack_records(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/tech-pack/records")
def api_set_tech_pack_records(payload: dict) -> dict:
    items = (payload or {}).get("items", [])
    if not isinstance(items, list):
        raise HTTPException(status_code=400, detail="items is required.")
    saved = save_tech_pack_records(DB_PATH, items)
    return {"ok": True, "items": saved, "count": len(saved)}


@app.post("/api/tech-pack/record")
def api_set_tech_pack_record(payload: dict) -> dict:
    item = (payload or {}).get("item")
    if not isinstance(item, dict):
        raise HTTPException(status_code=400, detail="item is required.")
    saved = save_tech_pack_record(DB_PATH, item)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="style_code is required.")
    return {"ok": True, "item": saved}


@app.post("/api/tech-pack/record-patch")
def api_patch_tech_pack_record(payload: dict) -> dict:
    style_code = str((payload or {}).get("style_code", "")).strip()
    changes = (payload or {}).get("changes")
    if not style_code:
        raise HTTPException(status_code=400, detail="style_code is required.")
    if not isinstance(changes, dict):
        raise HTTPException(status_code=400, detail="changes is required.")
    saved = save_tech_pack_record_patch(DB_PATH, style_code, changes)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="Could not save tech pack patch.")
    return {"ok": True, "item": saved}


@app.get("/api/bom/items")
def api_get_bom_items() -> dict:
    items = list_bom_items(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/bom/item")
def api_set_bom_item(payload: dict) -> dict:
    item = (payload or {}).get("item")
    if not isinstance(item, dict):
        raise HTTPException(status_code=400, detail="item is required.")
    saved = save_bom_item(DB_PATH, item)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="Could not save BOM item.")
    return {"ok": True, "item": saved}


@app.post("/api/bom/item/delete")
def api_delete_bom_item(payload: dict) -> dict:
    item_id = str((payload or {}).get("item_id", "")).strip()
    if not item_id:
        raise HTTPException(status_code=400, detail="item_id is required.")
    deleted_count = delete_bom_item(DB_PATH, item_id)
    return {"ok": True, "deleted_count": deleted_count}


@app.get("/api/samples/records")
def api_get_sample_records() -> dict:
    items = list_sample_records(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/samples/records")
def api_set_sample_records(payload: dict) -> dict:
    items = (payload or {}).get("items", [])
    if not isinstance(items, list):
        raise HTTPException(status_code=400, detail="items is required.")
    saved = save_sample_records(DB_PATH, items)
    return {"ok": True, "items": saved, "count": len(saved)}


@app.post("/api/samples/record")
def api_set_sample_record(payload: dict) -> dict:
    item = (payload or {}).get("item")
    if not isinstance(item, dict):
        raise HTTPException(status_code=400, detail="item is required.")
    saved = save_sample_record(DB_PATH, item)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="record_id is required.")
    return {"ok": True, "item": saved}


@app.get("/api/hr/employees")
def api_get_hr_employees() -> dict:
    items = list_hr_employees(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/hr/employees")
def api_set_hr_employees(payload: dict) -> dict:
    items = (payload or {}).get("items", [])
    if not isinstance(items, list):
        raise HTTPException(status_code=400, detail="items is required.")
    saved = save_hr_employees(DB_PATH, items)
    return {"ok": True, "items": saved, "count": len(saved)}


@app.post("/api/hr/employee")
def api_set_hr_employee(payload: dict) -> dict:
    item = (payload or {}).get("item")
    if not isinstance(item, dict):
        raise HTTPException(status_code=400, detail="item is required.")
    saved = save_hr_employee(DB_PATH, item)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="employee_id is required.")
    return {"ok": True, "item": saved}


@app.get("/api/hr/attendance-records")
def api_get_hr_attendance_records() -> dict:
    items = list_hr_attendance_records(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/hr/attendance-records")
def api_set_hr_attendance_records(payload: dict) -> dict:
    items = (payload or {}).get("items", [])
    if not isinstance(items, list):
        raise HTTPException(status_code=400, detail="items is required.")
    saved = save_hr_attendance_records(DB_PATH, items)
    return {"ok": True, "items": saved, "count": len(saved)}


@app.post("/api/hr/attendance-record")
def api_set_hr_attendance_record(payload: dict) -> dict:
    item = (payload or {}).get("item")
    if not isinstance(item, dict):
        raise HTTPException(status_code=400, detail="item is required.")
    saved = save_hr_attendance_record(DB_PATH, item)
    if not isinstance(saved, dict):
        raise HTTPException(status_code=400, detail="attendance_id is required.")
    return {"ok": True, "item": saved}


@app.post("/api/hr/attendance/check-in")
def api_hr_attendance_check_in(payload: dict) -> dict:
    employee = (payload or {}).get("employee")
    if not isinstance(employee, dict):
        raise HTTPException(status_code=400, detail="employee is required.")
    try:
        saved = hr_attendance_check_in(
            DB_PATH,
            employee,
            actor_email=str((payload or {}).get("actor_email", "")).strip(),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True, "item": saved}


@app.post("/api/hr/attendance/check-out")
def api_hr_attendance_check_out(payload: dict) -> dict:
    employee = (payload or {}).get("employee")
    if not isinstance(employee, dict):
        raise HTTPException(status_code=400, detail="employee is required.")
    try:
        saved = hr_attendance_check_out(
            DB_PATH,
            employee,
            actor_email=str((payload or {}).get("actor_email", "")).strip(),
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    return {"ok": True, "item": saved}


@app.get("/api/tech-pack/hidden-styles")
def api_get_tech_pack_hidden_styles() -> dict:
    items = list_tech_pack_hidden_styles(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/tech-pack/hidden-styles")
def api_set_tech_pack_hidden_styles(payload: dict) -> dict:
    items = (payload or {}).get("items", [])
    if not isinstance(items, list):
        raise HTTPException(status_code=400, detail="items is required.")
    saved = save_tech_pack_hidden_styles(DB_PATH, items)
    return {"ok": True, "items": saved, "count": len(saved)}


@app.get("/api/orders/{order_id}")
def api_order_detail(order_id: int) -> dict:
    if not _database_ready():
        raise HTTPException(status_code=404, detail="Database is not ready.")

    payload = get_order(DB_PATH, order_id)
    if payload is None:
        raise HTTPException(status_code=404, detail="Order not found.")
    return payload


@app.post("/api/orders/import")
def api_import_order(payload: dict) -> dict:
    order = payload.get("order") if isinstance(payload, dict) else None
    if not isinstance(order, dict):
        raise HTTPException(status_code=400, detail="Order payload is required.")
    order_id = save_purchase_order(DB_PATH, order)
    saved = get_order(DB_PATH, order_id)
    return {
        "ok": True,
        "order_id": order_id,
        "order": saved,
    }


@app.post("/api/orders/{order_id}/delete")
def api_delete_order(order_id: int) -> dict:
    deleted_count = delete_purchase_order(DB_PATH, order_id)
    if deleted_count <= 0:
        raise HTTPException(status_code=404, detail="Order not found.")
    return {
        "ok": True,
        "order_id": order_id,
        "deleted_count": deleted_count,
    }


@app.get("/api/costing/variants")
def api_costing_variants(limit: int = Query(default=12, ge=1, le=100)) -> dict:
    if not _database_ready():
        return {"items": [], "count": 0}

    variants = [_variant_payload(item) for item in list_costing_variants_for_web(DB_PATH, limit=limit)]
    return {"items": variants, "count": len(variants)}


@app.get("/api/costing/imports")
def api_costing_imports() -> dict:
    items = get_all_costing_imports(DB_PATH)
    return {"items": items, "count": len(items)}


@app.post("/api/costing/imports")
def api_import_costing(payload: dict) -> dict:
    costing = payload.get("costing") if isinstance(payload, dict) else None
    if not isinstance(costing, dict):
        raise HTTPException(status_code=400, detail="Costing payload is required.")
    normalized = _prepare_costing_payload_for_host(costing)
    costing_import_id = save_costing_import(DB_PATH, normalized)
    return {
        "ok": True,
        "costing_import_id": costing_import_id,
    }


@app.post("/api/costing/ws-imports")
def api_import_costing_ws(payload: dict) -> dict:
    costing = payload.get("costing") if isinstance(payload, dict) else None
    if not isinstance(costing, dict):
        raise HTTPException(status_code=400, detail="Costing worksheet payload is required.")
    costing_import_id = save_costing_ws_import(DB_PATH, costing)
    return {
        "ok": True,
        "costing_import_id": costing_import_id,
    }


@app.post("/api/costing/ws-rows/update")
def api_update_costing_ws_row(payload: dict) -> dict:
    if not isinstance(payload, dict):
        raise HTTPException(status_code=400, detail="Payload is required.")
    updates = payload.get("updates")
    if not isinstance(updates, dict):
        raise HTTPException(status_code=400, detail="updates is required.")
    affected_rows = update_costing_ws_row(
        DB_PATH,
        source_workbook=str(payload.get("source_workbook") or "").strip(),
        source_sheet=str(payload.get("source_sheet") or "").strip(),
        source_row_number=int(payload.get("source_row_number") or 0),
        updates=updates,
    )
    return {
        "ok": True,
        "affected_rows": int(affected_rows),
    }


@app.post("/api/costing/styles/delete")
def api_delete_costing_styles(payload: dict) -> dict:
    raw_keys = payload.get("style_keys") if isinstance(payload, dict) else None
    if not isinstance(raw_keys, list):
        raise HTTPException(status_code=400, detail="style_keys is required.")
    style_keys: list[object] = []
    for item in raw_keys:
        if not isinstance(item, dict):
            continue
        style_keys.append(
            {
                "source_kind": str(item.get("source_kind", "")),
                "collection": str(item.get("collection", "")),
                "season": str(item.get("season", "")),
                "style_code": str(item.get("style_code", "")),
                "source_workbook": str(item.get("source_workbook", "")),
                "source_pdf": str(item.get("source_pdf", "")),
                "source_sheet": str(item.get("source_sheet", "")),
                "source_row_number": item.get("source_row_number"),
            }
        )
    delete_costing_styles(DB_PATH, style_keys)
    return {
        "ok": True,
        "deleted_count": len(style_keys),
    }


@app.get("/api/folder/contents")
def api_folder_contents(
    email: str = Query(..., min_length=3),
    path: str = Query(default=""),
) -> dict:
    _require_admin_user(email)
    folder_path = _safe_local_directory(path)
    folders: list[dict] = []
    files: list[dict] = []
    for child in sorted(folder_path.iterdir(), key=lambda item: (not item.is_dir(), item.name.lower())):
        try:
            resolved = child.resolve()
            resolved.relative_to(LOCAL_ROOT.resolve())
        except Exception:
            continue
        try:
            stat = child.stat()
            modified = datetime.fromtimestamp(stat.st_mtime, tz=timezone.utc).astimezone().strftime("%Y-%m-%d %H:%M:%S")
        except Exception:
            stat = None
            modified = ""
        item_payload = {
            "name": child.name,
            "path": str(resolved),
            "modified": modified,
        }
        if child.is_dir():
            folders.append(item_payload)
        elif child.is_file():
            files.append(
                {
                    **item_payload,
                    "size": int(stat.st_size) if stat is not None else 0,
                }
            )
    return {
        "ok": True,
        "root": str(LOCAL_ROOT.resolve()),
        "path": str(folder_path),
        "folders": folders,
        "files": files,
    }


@app.get("/api/files")
def api_file(path: str = Query(..., min_length=1)) -> FileResponse:
    file_path = _safe_local_file(path)
    media_type, _ = mimetypes.guess_type(file_path.name)
    return FileResponse(file_path, media_type=media_type or "application/octet-stream")


@app.post("/api/files/delete")
def api_delete_file_or_folder(
    email: str = Form(..., min_length=3),
    path: str = Form(..., min_length=1),
) -> dict:
    _require_admin_user(email)
    target_path = _safe_local_path(path)
    if target_path.resolve() == LOCAL_ROOT.resolve():
        raise HTTPException(status_code=403, detail="The storage root cannot be deleted.")

    deleted_kind = "folder" if target_path.is_dir() else "file"
    deleted_path = str(target_path)
    try:
        if target_path.is_dir():
            shutil.rmtree(target_path)
        else:
            target_path.unlink()
    except FileNotFoundError as exc:
        raise HTTPException(status_code=404, detail="Path not found.") from exc
    except Exception as exc:
        raise HTTPException(status_code=500, detail=f"Could not delete path: {exc}") from exc

    return {
        "ok": True,
        "deleted": True,
        "kind": deleted_kind,
        "path": deleted_path,
    }


@app.post("/api/folders/create")
def api_create_folder(payload: dict) -> dict:
    email = str((payload or {}).get("email", "")).strip()
    target_dir = str((payload or {}).get("target_dir", "")).strip()
    folder_name = str((payload or {}).get("folder_name", "")).strip()
    overwrite = bool((payload or {}).get("overwrite", False))
    _require_admin_user(email)
    if not folder_name:
        raise HTTPException(status_code=400, detail="folder_name is required.")
    base_dir = _safe_local_directory(target_dir)
    target_path = _safe_child_path(base_dir, folder_name)
    if target_path.resolve() == LOCAL_ROOT.resolve():
        raise HTTPException(status_code=403, detail="The storage root cannot be overwritten.")
    if target_path.exists():
        if not overwrite:
            raise HTTPException(status_code=409, detail="Folder already exists.")
        if target_path.is_file():
            target_path.unlink()
        else:
            shutil.rmtree(target_path)
    target_path.mkdir(parents=True, exist_ok=True)
    return {
        "ok": True,
        "path": str(target_path),
        "created": True,
    }


@app.post("/api/files/upload")
def api_upload_file_to_folder(
    email: str = Form(..., min_length=3),
    target_dir: str = Form(default=""),
    overwrite: str = Form(default="false"),
    relative_path: str = Form(default=""),
    file: UploadFile = File(...),
) -> dict:
    _require_admin_user(email)
    base_dir = _safe_local_directory(target_dir)
    relative_value = str(relative_path or "").strip() or file.filename or ""
    if not relative_value:
        raise HTTPException(status_code=400, detail="relative_path is required.")
    destination_path = _safe_child_path(base_dir, relative_value)
    overwrite_enabled = str(overwrite or "").strip().lower() in {"1", "true", "yes", "y", "on"}
    if destination_path.exists() and destination_path.is_dir():
        raise HTTPException(status_code=409, detail="A folder already exists at the destination path.")
    if destination_path.exists() and not overwrite_enabled:
        raise HTTPException(status_code=409, detail="File already exists.")
    destination_path.parent.mkdir(parents=True, exist_ok=True)
    with destination_path.open("wb") as handle:
        shutil.copyfileobj(file.file, handle)
    return {
        "ok": True,
        "path": str(destination_path),
        "uploaded": True,
    }


@app.get("/downloads/latest")
def download_latest_release() -> FileResponse:
    release_file = _latest_release_file()
    if release_file is None:
        raise HTTPException(status_code=404, detail="No packaged desktop build is available yet.")

    media_type, _ = mimetypes.guess_type(release_file.name)
    return FileResponse(
        release_file,
        media_type=media_type or "application/octet-stream",
        filename=release_file.name,
    )


@app.get("/downloads/macos/latest")
def download_latest_macos_release() -> FileResponse:
    release_file = _latest_macos_release_file()
    if release_file is None:
        raise HTTPException(status_code=404, detail="No packaged macOS build is available yet.")

    media_type, _ = mimetypes.guess_type(release_file.name)
    return FileResponse(
        release_file,
        media_type=media_type or "application/zip",
        filename=release_file.name,
    )


@app.get("/downloads/macos/guide")
def download_latest_macos_guide() -> FileResponse:
    guide_file = DIST_DIR / "RUN_FOR_TESTERS_macOS.txt"
    if not guide_file.exists() or not guide_file.is_file():
        raise HTTPException(status_code=404, detail="No macOS guide is available yet.")

    return FileResponse(
        guide_file,
        media_type="text/plain; charset=utf-8",
        filename=guide_file.name,
    )
