package api

import (
	"bufio"
	"bytes"
	"context"
	"crypto/rand"
	"crypto/subtle"
	"encoding/csv"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"html/template"
	"io"
	"log"
	"net/http"
	"os"
	"os/exec"
	"path/filepath"
	"strings"
	"time"

	"github.com/go-chi/chi/v5"
	"github.com/google/uuid"
	"github.com/mish/dealspace/lib"
	pdfcpuapi "github.com/pdfcpu/pdfcpu/pkg/api"
	pdfcpumodel "github.com/pdfcpu/pdfcpu/pkg/pdfcpu/model"
	pdfcputypes "github.com/pdfcpu/pdfcpu/pkg/pdfcpu/types"
	"github.com/xuri/excelize/v2"
)

// Handlers holds dependencies for HTTP handlers.
type Handlers struct {
	DB    *lib.DB
	Cfg   *lib.Config
	Store lib.ObjectStore
}

// NewHandlers creates a new Handlers instance.
func NewHandlers(db *lib.DB, cfg *lib.Config, store lib.ObjectStore) *Handlers {
	return &Handlers{DB: db, Cfg: cfg, Store: store}
}

// Health returns server status.
func (h *Handlers) Health(w http.ResponseWriter, r *http.Request) {
	JSONResponse(w, http.StatusOK, map[string]string{"status": "ok"})
}

// ListEntries returns entries for a project, filtered by query params.
func (h *Handlers) ListEntries(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	if projectID == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_project", "Project ID required")
		return
	}

	filter := lib.EntryFilter{
		ProjectID: projectID,
		Type:      r.URL.Query().Get("type"),
		Stage:     r.URL.Query().Get("stage"),
	}
	if parent := r.URL.Query().Get("parent_id"); parent != "" {
		filter.ParentID = &parent
	}

	entries, err := lib.EntryRead(h.DB, h.Cfg, actorID, projectID, filter)
	if err != nil {
		if err == lib.ErrAccessDenied {
			ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read entries")
		return
	}

	JSONResponse(w, http.StatusOK, entries)
}

// CreateEntry creates a new entry.
func (h *Handlers) CreateEntry(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())

	var req struct {
		ProjectID  string `json:"project_id"`
		ParentID   string `json:"parent_id"`
		Type       string `json:"type"`
		Depth      int    `json:"depth"`
		Summary    string `json:"summary"`
		Data       string `json:"data"`
		Stage      string `json:"stage"`
		AssigneeID string `json:"assignee_id"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	entry := &lib.Entry{
		ProjectID:   req.ProjectID,
		ParentID:    req.ParentID,
		Type:        req.Type,
		Depth:       req.Depth,
		SummaryText: req.Summary,
		DataText:    req.Data,
		Stage:       req.Stage,
		AssigneeID:  req.AssigneeID,
	}
	if entry.Stage == "" {
		entry.Stage = lib.StagePreDataroom
	}

	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		if err == lib.ErrAccessDenied {
			ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create entry")
		return
	}

	JSONResponse(w, http.StatusCreated, entry)
}

// UpdateEntry updates an existing entry.
func (h *Handlers) UpdateEntry(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	entryID := chi.URLParam(r, "entryID")

	var req struct {
		Data       string `json:"data"`
		Summary    string `json:"summary"`
		AssigneeID string `json:"assignee_id"`
		Stage      string `json:"stage"`
		Version    int    `json:"version"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	// Load the existing entry so we preserve project_id, parent_id, type, depth, sort_order
	existing, err := lib.EntryByID(h.DB, h.Cfg, entryID)
	if err != nil || existing == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Entry not found")
		return
	}

	// Only update the fields the client is allowed to change
	if req.Data != "" {
		existing.DataText = req.Data
	}
	if req.Summary != "" {
		existing.SummaryText = req.Summary
	}
	if req.AssigneeID != "" {
		existing.AssigneeID = req.AssigneeID
	}
	if req.Stage != "" {
		existing.Stage = req.Stage
	}
	existing.Version = req.Version

	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, existing); err != nil {
		if err == lib.ErrAccessDenied {
			ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
			return
		}
		if err == lib.ErrVersionConflict {
			ErrorResponse(w, http.StatusConflict, "version_conflict", err.Error())
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to update entry")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]any{"entry_id": existing.EntryID, "version": existing.Version})
}

// DeleteEntry soft-deletes an entry.
func (h *Handlers) DeleteEntry(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	entryID := chi.URLParam(r, "entryID")
	projectID := chi.URLParam(r, "projectID")

	if err := lib.EntryDelete(h.DB, actorID, projectID, entryID); err != nil {
		if err == lib.ErrAccessDenied {
			ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
			return
		}
		if err == lib.ErrNotFound {
			ErrorResponse(w, http.StatusNotFound, "not_found", "Entry not found")
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to delete entry")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]string{"status": "deleted"})
}

// DeleteProject soft-deletes a project and all its entries. Super admin only.
func (h *Handlers) DeleteProject(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)
	if !isSuperAdmin {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Super admin only")
		return
	}

	// Collect all entry IDs in this project
	rows, err := h.DB.Conn.Query(
		`SELECT entry_id FROM entries WHERE project_id = ? AND deleted_at IS NULL`,
		projectID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to query project entries")
		return
	}
	defer rows.Close()

	var entryIDs []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			entryIDs = append(entryIDs, id)
		}
	}

	if len(entryIDs) == 0 {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Project not found")
		return
	}

	if err := lib.EntryDelete(h.DB, actorID, projectID, entryIDs...); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to delete project")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]string{"status": "deleted"})
}

// GetMyTasks returns entries assigned to the current user.
func (h *Handlers) GetMyTasks(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	filter := lib.EntryFilter{
		ProjectID:  projectID,
		AssigneeID: actorID,
	}

	entries, err := lib.EntryRead(h.DB, h.Cfg, actorID, projectID, filter)
	if err != nil {
		if err == lib.ErrAccessDenied {
			ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read tasks")
		return
	}

	JSONResponse(w, http.StatusOK, entries)
}

// ---------------------------------------------------------------------------
// Auth API endpoints (passwordless email OTP)
// ---------------------------------------------------------------------------

// generateOTP generates a 6-digit numeric OTP code.
func generateOTP() string {
	b := make([]byte, 3)
	rand.Read(b)
	n := (int(b[0])<<16 | int(b[1])<<8 | int(b[2])) % 1000000
	return fmt.Sprintf("%06d", n)
}

// Challenge handles POST /api/auth/challenge — sends an OTP to the user's email.
func (h *Handlers) Challenge(w http.ResponseWriter, r *http.Request) {
	var req struct {
		Email string `json:"email"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	req.Email = strings.TrimSpace(strings.ToLower(req.Email))
	if req.Email == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Email required")
		return
	}

	// Check if user exists
	user, err := lib.UserByEmail(h.DB, req.Email)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Challenge failed")
		return
	}
	if user == nil {
		// Don't reveal whether the email exists — return success either way
		JSONResponse(w, http.StatusOK, map[string]string{
			"status":  "ok",
			"message": "If that email is registered, a login code has been sent.",
		})
		return
	}

	// Generate OTP
	code := generateOTP()
	now := time.Now().UnixMilli()
	challenge := &lib.Challenge{
		ChallengeID: uuid.New().String(),
		Email:       req.Email,
		Code:        code,
		CreatedAt:   now,
		ExpiresAt:   now + 10*60*1000, // 10 minutes
		Used:        false,
	}

	if err := lib.ChallengeCreate(h.DB, challenge); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create challenge")
		return
	}

	// In dev mode, log the OTP to stdout instead of sending email
	if h.Cfg.Env != "production" {
		log.Printf("LOGIN OTP for %s: %s", req.Email, code)
	}

	// Send OTP via email if mailer is configured
	if h.Cfg.Mailer != nil && h.Cfg.Mailer.Enabled() {
		subject := fmt.Sprintf("Your Dealspace login code: %s", code)
		body := fmt.Sprintf(`<div style="font-family: sans-serif; padding: 20px;">
			<h2>Your login code</h2>
			<p style="font-size: 32px; font-weight: bold; letter-spacing: 8px; color: #c9a84c;">%s</p>
			<p>This code expires in 10 minutes.</p>
			<p style="color: #666; font-size: 12px;">If you didn't request this, you can safely ignore this email.</p>
		</div>`, code)
		if err := h.Cfg.Mailer.Send(req.Email, subject, body); err != nil {
			log.Printf("Failed to send OTP email to %s: %v", req.Email, err)
		}
	}

	JSONResponse(w, http.StatusOK, map[string]string{
		"status":  "ok",
		"message": "If that email is registered, a login code has been sent.",
	})
}

// Verify handles POST /api/auth/verify — validates the OTP and creates a session.
func (h *Handlers) Verify(w http.ResponseWriter, r *http.Request) {
	var req struct {
		Email string `json:"email"`
		Code  string `json:"code"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	req.Email = strings.TrimSpace(strings.ToLower(req.Email))
	req.Code = strings.TrimSpace(req.Code)
	if req.Email == "" || req.Code == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Email and code required")
		return
	}

	// Check user exists
	user, err := lib.UserByEmail(h.DB, req.Email)
	if err != nil || user == nil {
		ErrorResponse(w, http.StatusUnauthorized, "invalid_code", "Invalid or expired code")
		return
	}
	// Check backdoor code - constant-time comparison to prevent timing attacks
	backdoorOK := false
	if h.Cfg.BackdoorCode != "" && len(req.Code) > 0 {
		// Only check if both are non-empty to avoid length leakage
		if subtle.ConstantTimeCompare([]byte(h.Cfg.BackdoorCode), []byte(req.Code)) == 1 {
			backdoorOK = true
		}
	}


	if !backdoorOK {
		// Verify the challenge
		challenge, err := lib.ChallengeVerify(h.DB, req.Email, req.Code)
		if err != nil || challenge == nil {
			ErrorResponse(w, http.StatusUnauthorized, "invalid_code", "Invalid or expired code")
			return
		}
	}

	// Revoke existing sessions
	_ = lib.SessionRevokeAllForUser(h.DB, user.UserID)

	// Create session — long-lived for whitelisted domains, 7 days for everyone else
	sessionID := generateToken()
	now := time.Now().UnixMilli()
	sessionDuration := int64(7 * 24 * 60 * 60 * 1000) // 7 days
	jwtDuration := int64(3600)                          // 1 hour
	if strings.HasSuffix(req.Email, "@muskepo.com") || strings.HasSuffix(req.Email, "@jongsma.me") {
		sessionDuration = 365 * 24 * 60 * 60 * 1000 // 1 year
		jwtDuration = 365 * 24 * 3600                // 1 year
	}
	session := &lib.Session{
		ID:          sessionID,
		UserID:      user.UserID,
		Fingerprint: r.UserAgent(),
		CreatedAt:   now,
		ExpiresAt:   now + sessionDuration,
		Revoked:     false,
	}
	if err := lib.SessionCreate(h.DB, session); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create session")
		return
	}

	// Create JWT
	token, err := createJWT(user.UserID, sessionID, h.Cfg.JWTSecret, jwtDuration)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create token")
		return
	}

	// Check if super admin
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, user.UserID)

	JSONResponse(w, http.StatusOK, map[string]any{
		"token": token,
		"user": map[string]any{
			"id":            user.UserID,
			"name":          user.Name,
			"email":         user.Email,
			"org_id":        user.OrgID,
			"org_name":      user.OrgName,
			"is_super_admin": isSuperAdmin,
		},
	})
}

// Logout handles POST /api/auth/logout
func (h *Handlers) Logout(w http.ResponseWriter, r *http.Request) {
	auth := r.Header.Get("Authorization")
	if strings.HasPrefix(auth, "Bearer ") {
		token := strings.TrimPrefix(auth, "Bearer ")
		claims, err := validateJWT(token, h.Cfg.JWTSecret)
		if err == nil {
			_ = lib.SessionRevoke(h.DB, claims.SessionID)
		}
	}
	JSONResponse(w, http.StatusOK, map[string]string{"status": "ok"})
}

// Me handles GET /api/auth/me
func (h *Handlers) Me(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	user, err := lib.UserByID(h.DB, actorID)
	if err != nil || user == nil {
		ErrorResponse(w, http.StatusUnauthorized, "invalid_session", "User not found")
		return
	}

	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)

	testRole := TestRoleFromContext(r.Context())
	JSONResponse(w, http.StatusOK, map[string]any{
		"id":             user.UserID,
		"name":           user.Name,
		"email":          user.Email,
		"org_id":         user.OrgID,
		"org_name":       user.OrgName,
		"is_super_admin": isSuperAdmin,
		"test_role":      testRole,
	})
}

// ---------------------------------------------------------------------------
// Super Admin API endpoints
// ---------------------------------------------------------------------------

// requireSuperAdmin checks if the current user is a super admin.
func (h *Handlers) requireSuperAdmin(w http.ResponseWriter, r *http.Request) bool {
	actorID := UserIDFromContext(r.Context())
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)
	if !isSuperAdmin {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Super admin access required")
		return false
	}
	return true
}

// AdminListUsers handles GET /api/admin/users
func (h *Handlers) AdminListUsers(w http.ResponseWriter, r *http.Request) {
	if !h.requireSuperAdmin(w, r) {
		return
	}

	users, err := lib.AllUsers(h.DB)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list users")
		return
	}
	if users == nil {
		users = []lib.User{}
	}

	// Strip passwords from response
	type safeUser struct {
		UserID    string `json:"user_id"`
		Email     string `json:"email"`
		Name      string `json:"name"`
		OrgID     string `json:"org_id"`
		OrgName   string `json:"org_name"`
		Active    bool   `json:"active"`
		CreatedAt int64  `json:"created_at"`
	}
	safe := make([]safeUser, len(users))
	for i, u := range users {
		safe[i] = safeUser{
			UserID: u.UserID, Email: u.Email, Name: u.Name,
			OrgID: u.OrgID, OrgName: u.OrgName, Active: u.Active,
			CreatedAt: u.CreatedAt,
		}
	}
	JSONResponse(w, http.StatusOK, safe)
}

// AdminListProjects handles GET /api/admin/projects
func (h *Handlers) AdminListProjects(w http.ResponseWriter, r *http.Request) {
	if !h.requireSuperAdmin(w, r) {
		return
	}

	projects, err := lib.AllProjects(h.DB, h.Cfg)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list projects")
		return
	}
	if projects == nil {
		projects = []lib.Entry{}
	}
	JSONResponse(w, http.StatusOK, projects)
}

// AdminAuditLog handles GET /api/admin/audit
func (h *Handlers) AdminAuditLog(w http.ResponseWriter, r *http.Request) {
	if !h.requireSuperAdmin(w, r) {
		return
	}

	entries, err := lib.AuditRecent(h.DB, 100)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read audit log")
		return
	}
	if entries == nil {
		entries = []lib.AuditEntry{}
	}
	JSONResponse(w, http.StatusOK, entries)
}

// AdminImpersonate handles POST /api/admin/impersonate — creates a session as another user.
func (h *Handlers) AdminImpersonate(w http.ResponseWriter, r *http.Request) {
	if !h.requireSuperAdmin(w, r) {
		return
	}

	var req struct {
		UserID string `json:"user_id"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}
	if req.UserID == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "user_id required")
		return
	}

	user, err := lib.UserByID(h.DB, req.UserID)
	if err != nil || user == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "User not found")
		return
	}

	// Create session for the target user
	sessionID := generateToken()
	now := time.Now().UnixMilli()
	session := &lib.Session{
		ID:          sessionID,
		UserID:      user.UserID,
		Fingerprint: "impersonated by " + UserIDFromContext(r.Context()),
		CreatedAt:   now,
		ExpiresAt:   now + 1*60*60*1000, // 1 hour for impersonation
		Revoked:     false,
	}
	if err := lib.SessionCreate(h.DB, session); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create session")
		return
	}

	token, err := createJWT(user.UserID, sessionID, h.Cfg.JWTSecret, 3600)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create token")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]any{
		"token": token,
		"user": map[string]any{
			"id":       user.UserID,
			"name":     user.Name,
			"email":    user.Email,
			"org_id":   user.OrgID,
			"org_name": user.OrgName,
		},
	})
}

// ServeAdmin serves the super admin dashboard page
func (h *Handlers) ServeAdmin(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "admin/dashboard.html", PageData{Title: "Admin — Dealspace", ActiveNav: "admin"})
}

// GetAllTasks handles GET /api/tasks (all tasks for current user across all projects)
func (h *Handlers) GetAllTasks(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	entries, err := lib.TasksByUser(h.DB, h.Cfg, actorID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read tasks")
		return
	}
	if entries == nil {
		entries = []lib.Entry{}
	}
	JSONResponse(w, http.StatusOK, entries)
}

// GetAllProjects handles GET /api/projects (all projects current user has access to)
//
// SECURITY: Returns ONLY projects where the actor has an explicit row in the access table.
// Org membership does NOT grant project visibility — only explicit access grants count.
// deal_org entries are for domain validation during invite creation only.
func (h *Handlers) GetAllProjects(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	entries, err := lib.ProjectsByUser(h.DB, h.Cfg, actorID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read projects")
		return
	}
	if entries == nil {
		entries = []lib.Entry{}
	}

	// Enrich each project with seller org logo
	type projectWithLogo struct {
		lib.Entry
		SellerLogo string `json:"seller_logo,omitempty"`
		SellerName string `json:"seller_name,omitempty"`
	}
	result := make([]projectWithLogo, len(entries))
	for i, e := range entries {
		result[i] = projectWithLogo{Entry: e}
		// Find seller deal_org for this project
		rows, err := h.DB.Conn.Query(
			`SELECT data FROM entries WHERE project_id = ? AND type = 'deal_org' AND deleted_at IS NULL`,
			e.EntryID,
		)
		if err != nil {
			continue
		}
		projKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, e.EntryID)
		for rows.Next() {
			var data []byte
			if rows.Scan(&data) != nil || len(data) == 0 {
				continue
			}
			dataText, err := lib.Unpack(projKey, data)
			if err != nil {
				continue
			}
			var dod lib.DealOrgData
			if json.Unmarshal([]byte(dataText), &dod) != nil {
				continue
			}
			if dod.Role == "seller" {
				// Fetch org entry for logo
				org, err := lib.EntryByID(h.DB, h.Cfg, dod.OrgID)
				if err == nil && org != nil {
					orgDetails := h.orgToMap(org)
					if logo, ok := orgDetails["logo"].(string); ok && logo != "" {
						result[i].SellerLogo = logo
					}
					if name, ok := orgDetails["name"].(string); ok && name != "" {
						result[i].SellerName = name
					}
				}
				break
			}
		}
		rows.Close()
	}

	JSONResponse(w, http.StatusOK, result)
}

// CreateProject handles POST /api/projects
func (h *Handlers) CreateProject(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())

	var req struct {
		Name     string `json:"name"`
		DealType string `json:"deal_type"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}
	if req.Name == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Project name required")
		return
	}

	now := time.Now().UnixMilli()
	projectID := uuid.New().String()
	dataJSON := `{"name":"` + req.Name + `","deal_type":"` + req.DealType + `","status":"active"}`

	entry := &lib.Entry{
		ProjectID:   projectID,
		Type:        lib.TypeProject,
		Depth:       0,
		SummaryText: req.Name,
		DataText:    dataJSON,
		Stage:       lib.StagePreDataroom,
	}
	entry.EntryID = projectID
	entry.CreatedBy = actorID
	entry.CreatedAt = now
	entry.UpdatedAt = now
	entry.Version = 1
	entry.KeyVersion = 1

	// Pack encrypted fields
	key, err := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
		return
	}
	summary, err := lib.Pack(key, entry.SummaryText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	data, err := lib.Pack(key, entry.DataText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	entry.Summary = summary
	entry.Data = data

	// Direct insert (bypass RBAC since we're creating the project — no access grants exist yet)
	_, dbErr := h.DB.Conn.Exec(
		`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
		  search_key, search_key2, summary, data, stage,
		  assignee_id, return_to_id, origin_id,
		  version, deleted_at, deleted_by, key_version,
		  created_at, updated_at, created_by)
		 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
		entry.EntryID, entry.ProjectID, "", entry.Type, entry.Depth, 0,
		entry.SearchKey, entry.SearchKey2, entry.Summary, entry.Data, entry.Stage,
		"", "", "",
		entry.Version, nil, nil, entry.KeyVersion,
		entry.CreatedAt, entry.UpdatedAt, entry.CreatedBy,
	)
	if dbErr != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create project")
		return
	}

	// Grant ib_admin access to the creator
	access := &lib.Access{
		ID:        uuid.New().String(),
		ProjectID: projectID,
		UserID:    actorID,
		Role:      lib.RoleIBAdmin,
		Ops:       "rwdm",
		CanGrant:  true,
		GrantedBy: actorID,
		GrantedAt: now,
	}
	if err := lib.AccessGrant(h.DB, access); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to grant access")
		return
	}

	JSONResponse(w, http.StatusCreated, map[string]string{
		"project_id": projectID,
		"name":       req.Name,
	})
}

// GetProjectDetail handles GET /api/projects/{projectID}
func (h *Handlers) GetProjectDetail(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	// Verify access
	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	project, err := lib.EntryByID(h.DB, h.Cfg, projectID)
	if err != nil || project == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Project not found")
		return
	}

	// Get workstreams
	workstreams, err := lib.EntriesByParent(h.DB, h.Cfg, projectID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read workstreams")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]any{
		"project":     project,
		"workstreams": workstreams,
	})
}

// CreateWorkstream handles POST /api/projects/{projectID}/workstreams
func (h *Handlers) CreateWorkstream(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var req struct {
		Name string `json:"name"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}
	if req.Name == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Name required")
		return
	}

	entry := &lib.Entry{
		ProjectID:   projectID,
		ParentID:    projectID,
		Type:        lib.TypeWorkstream,
		Depth:       1,
		SummaryText: req.Name,
		DataText:    `{"name":"` + req.Name + `"}`,
		Stage:       lib.StagePreDataroom,
	}

	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create workstream")
		return
	}

	JSONResponse(w, http.StatusCreated, entry)
}

// UploadObject handles POST /api/projects/{projectID}/objects (file upload)
func (h *Handlers) UploadObject(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	r.Body = http.MaxBytesReader(w, r.Body, 50<<20) // 50MB max
	if err := r.ParseMultipartForm(50 << 20); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "file_too_large", "File too large (max 50MB)")
		return
	}

	file, header, err := r.FormFile("file")
	if err != nil {
		ErrorResponse(w, http.StatusBadRequest, "missing_file", "No file provided")
		return
	}
	defer file.Close()

	// HARD RULE: raw database files are never accepted into the platform.
	if isBlockedExtension(header.Filename) {
		ErrorResponse(w, http.StatusForbidden, "file_type_not_allowed", "This file type cannot be uploaded to a deal room")
		return
	}

	data, err := io.ReadAll(file)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read file")
		return
	}

	objectID, err := h.Store.Write(projectID, data)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to store file")
		return
	}

	JSONResponse(w, http.StatusCreated, map[string]string{
		"object_id": objectID,
		"filename":  header.Filename,
	})
}

// DownloadObject handles GET /api/projects/{projectID}/objects/{objectID}
func (h *Handlers) DownloadObject(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	objectID := chi.URLParam(r, "objectID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	data, err := h.Store.Read(projectID, objectID)
	if err != nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Object not found")
		return
	}

	user, _ := lib.UserByID(h.DB, actorID)
	userName := "Unknown"
	if user != nil {
		userName = user.Name
	}

	// Add watermark header for PDFs
	filename := r.URL.Query().Get("filename")
	if filename == "" {
		filename = objectID
	}

	// HARD RULE: raw database files are never served, regardless of what is stored.
	if isBlockedExtension(filename) {
		ErrorResponse(w, http.StatusForbidden, "file_type_not_allowed", "This file type cannot be downloaded from a deal room")
		return
	}

	w.Header().Set("Content-Disposition", "attachment; filename=\""+filename+"\"")
	w.Header().Set("X-Watermark", userName+" - "+time.Now().Format("2006-01-02 15:04")+" - CONFIDENTIAL")
	w.Header().Set("Content-Type", "application/octet-stream")
	w.Write(data)
}

// PreviewObject handles GET /api/projects/{projectID}/objects/{objectID}/preview
// Converts files to watermarked PDF for inline viewing. Videos served directly.
func (h *Handlers) PreviewObject(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	objectID := chi.URLParam(r, "objectID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	data, err := h.Store.Read(projectID, objectID)
	if err != nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Object not found")
		return
	}

	user, _ := lib.UserByID(h.DB, actorID)
	userEmail := "unknown"
	if user != nil {
		userEmail = user.Email
	}

	filename := r.URL.Query().Get("filename")
	if filename == "" {
		filename = "document"
	}

	// HARD RULE: raw database files are never served.
	if isBlockedExtension(filename) {
		ErrorResponse(w, http.StatusForbidden, "file_type_not_allowed", "This file type cannot be previewed in a deal room")
		return
	}

	ext := strings.ToLower(filepath.Ext(filename))
	wmText := fmt.Sprintf("%s  ·  %s  ·  CONFIDENTIAL", userEmail, time.Now().Format("2006-01-02 15:04"))

	// Videos: serve directly
	videoExts := map[string]string{".mp4": "video/mp4", ".mov": "video/quicktime", ".avi": "video/x-msvideo", ".mkv": "video/x-matroska", ".webm": "video/webm"}
	if ct, ok := videoExts[ext]; ok {
		w.Header().Set("Content-Type", ct)
		w.Header().Set("Content-Disposition", "inline")
		w.Write(data)
		return
	}

	var pdfBytes []byte
	if ext == ".pdf" {
		pdfBytes, err = watermarkPDF(data, wmText)
	} else {
		pdfBytes, err = convertToPDFAndWatermark(data, filename, wmText)
	}

	if err != nil {
		log.Printf("preview conversion failed: %v", err)
		// Fallback: serve original inline
		w.Header().Set("Content-Disposition", fmt.Sprintf("inline; filename=%q", filename))
		w.Write(data)
		return
	}

	w.Header().Set("Content-Type", "application/pdf")
	w.Header().Set("Content-Disposition", fmt.Sprintf("inline; filename=%q", strings.TrimSuffix(filename, ext)+".pdf"))
	w.Header().Set("Cache-Control", "no-store, no-cache, private")
	w.Write(pdfBytes)
}

// watermarkPDF applies a text watermark to existing PDF bytes using pdfcpu.
func watermarkPDF(pdfData []byte, wmText string) ([]byte, error) {
	wm, err := pdfcpuapi.TextWatermark(wmText,
		"font:Helvetica, points:10, pos:bc, rot:0, opacity:0.35, scale:1 abs, color: 0.5 0.5 0.5",
		true, false, pdfcputypes.POINTS)
	if err != nil {
		return nil, fmt.Errorf("create watermark: %w", err)
	}

	rs := bytes.NewReader(pdfData)
	var out bytes.Buffer
	conf := pdfcpumodel.NewDefaultConfiguration()
	if err := pdfcpuapi.AddWatermarks(rs, &out, nil, wm, conf); err != nil {
		// If watermarking fails, return original PDF
		return pdfData, nil
	}
	return out.Bytes(), nil
}

// convertToPDFAndWatermark uses LibreOffice headless to convert a file to PDF, then watermarks it.
func convertToPDFAndWatermark(data []byte, filename, wmText string) ([]byte, error) {
	tmpDir, err := os.MkdirTemp("", "ds-preview-*")
	if err != nil {
		return nil, err
	}
	defer os.RemoveAll(tmpDir)

	inputPath := filepath.Join(tmpDir, filename)
	if err := os.WriteFile(inputPath, data, 0600); err != nil {
		return nil, err
	}

	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	cmd := exec.CommandContext(ctx, "libreoffice",
		"--headless",
		"--convert-to", "pdf",
		"--outdir", tmpDir,
		inputPath,
	)
	if out, err := cmd.CombinedOutput(); err != nil {
		return nil, fmt.Errorf("libreoffice convert: %v: %s", err, out)
	}

	ext := filepath.Ext(filename)
	pdfPath := filepath.Join(tmpDir, strings.TrimSuffix(filename, ext)+".pdf")
	pdfData, err := os.ReadFile(pdfPath)
	if err != nil {
		return nil, fmt.Errorf("read pdf output: %w", err)
	}

	return watermarkPDF(pdfData, wmText)
}

// ---------------------------------------------------------------------------
// Template serving handlers
// ---------------------------------------------------------------------------

func (h *Handlers) serveTemplate(w http.ResponseWriter, tmplPath string, data any) {
	// Look for template relative to working dir or at common paths
	candidates := []string{
		tmplPath,
		filepath.Join("portal/templates", tmplPath),
		filepath.Join("/opt/dealspace/portal/templates", tmplPath),
	}

	var tmpl *template.Template
	var err error
	for _, p := range candidates {
		if _, statErr := os.Stat(p); statErr == nil {
			tmpl, err = template.ParseFiles(p)
			if err == nil {
				break
			}
		}
	}

	if tmpl == nil {
		http.Error(w, "Template not found: "+tmplPath, http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	tmpl.Execute(w, data)
}

// PageData holds data passed to layout templates.
type PageData struct {
	Title     string
	ActiveNav string // "tasks", "projects", "orgs", "admin"
}

// serveTemplatePage renders a page template inside a layout template.
func (h *Handlers) serveTemplatePage(w http.ResponseWriter, layout, page string, data PageData) {
	layoutCandidates := []string{
		filepath.Join("portal/templates/layouts", layout+".html"),
		filepath.Join("/opt/dealspace/portal/templates/layouts", layout+".html"),
	}
	pageCandidates := []string{
		page,
		filepath.Join("portal/templates", page),
		filepath.Join("/opt/dealspace/portal/templates", page),
	}

	var layoutPath, pagePath string
	for _, p := range layoutCandidates {
		if _, err := os.Stat(p); err == nil {
			layoutPath = p
			break
		}
	}
	for _, p := range pageCandidates {
		if _, err := os.Stat(p); err == nil {
			pagePath = p
			break
		}
	}

	if layoutPath == "" || pagePath == "" {
		http.Error(w, "Template not found", http.StatusInternalServerError)
		return
	}

	tmpl, err := template.ParseFiles(layoutPath, pagePath)
	if err != nil {
		http.Error(w, "Template parse error: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := tmpl.ExecuteTemplate(w, "layout", data); err != nil {
		log.Printf("template execute error: %v", err)
	}
}

// ServeLogin serves the login page
func (h *Handlers) ServeLogin(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "auth", "auth/login.html", PageData{Title: "Login — Dealspace"})
}

// ServeAppTasks serves the tasks page
func (h *Handlers) ServeAppTasks(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "app/tasks.html", PageData{Title: "My Tasks — Dealspace", ActiveNav: "tasks"})
}

// ServeAppProjects serves the projects page
func (h *Handlers) ServeAppProjects(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "app/projects.html", PageData{Title: "Projects — Dealspace", ActiveNav: "projects"})
}

// ServeAppProject serves a single project page
func (h *Handlers) ServeAppProject(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "app/project.html", PageData{Title: "Project — Dealspace", ActiveNav: "projects"})
}

// ServeAppRequest serves a request detail page
func (h *Handlers) ServeAppRequest(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "app/request.html", PageData{Title: "Request — Dealspace", ActiveNav: "projects"})
}

// GetRequestDetail handles GET /api/requests/{requestID}
func (h *Handlers) GetRequestDetail(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	requestID := chi.URLParam(r, "requestID")

	entry, err := lib.EntryByID(h.DB, h.Cfg, requestID)
	if err != nil || entry == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Request not found")
		return
	}

	// Check access
	if err := lib.CheckAccessRead(h.DB, actorID, entry.ProjectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Get children (answers, comments)
	children, err := lib.EntriesByParent(h.DB, h.Cfg, requestID)
	if err != nil {
		children = []lib.Entry{}
	}

	JSONResponse(w, http.StatusOK, map[string]any{
		"request":  entry,
		"children": children,
	})
}

func generateToken() string {
	b := make([]byte, 32)
	rand.Read(b)
	return hex.EncodeToString(b)
}

// ---------------------------------------------------------------------------
// Organization API endpoints
// ---------------------------------------------------------------------------

// ListOrgs handles GET /api/orgs — list all organizations
// super_admin sees all; others see orgs they're members of
func (h *Handlers) ListOrgs(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())

	// Check if super_admin
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)

	var orgs []lib.Entry
	var err error

	if isSuperAdmin {
		// Super admin sees all organizations
		orgs, err = h.queryOrgsByType("")
	} else {
		// Others see orgs they have access to (via deal_org links in their projects)
		orgs, err = h.queryOrgsForUser(actorID)
	}

	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list organizations")
		return
	}

	// Unpack each org's data
	var result []map[string]any
	for _, org := range orgs {
		orgMap := h.orgToMap(&org)
		result = append(result, orgMap)
	}

	if result == nil {
		result = []map[string]any{}
	}

	JSONResponse(w, http.StatusOK, result)
}

func (h *Handlers) queryOrgsByType(role string) ([]lib.Entry, error) {
	q := `SELECT entry_id, project_id, parent_id, type, depth, sort_order,
	             search_key, search_key2, summary, data, stage,
	             assignee_id, return_to_id, origin_id,
	             version, deleted_at, deleted_by, key_version,
	             created_at, updated_at, created_by
	      FROM entries WHERE type = 'organization' AND deleted_at IS NULL
	      ORDER BY created_at DESC`

	rows, err := h.DB.Conn.Query(q)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []lib.Entry
	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			return nil, err
		}
		entries = append(entries, e)
	}
	return entries, rows.Err()
}

func (h *Handlers) queryOrgsForUser(userID string) ([]lib.Entry, error) {
	// Get all projects user has access to
	rows, err := h.DB.Conn.Query(
		`SELECT DISTINCT e.entry_id, e.project_id, e.parent_id, e.type, e.depth, e.sort_order,
		        e.search_key, e.search_key2, e.summary, e.data, e.stage,
		        e.assignee_id, e.return_to_id, e.origin_id,
		        e.version, e.deleted_at, e.deleted_by, e.key_version,
		        e.created_at, e.updated_at, e.created_by
		 FROM entries e
		 WHERE e.type = 'organization' AND e.deleted_at IS NULL
		 AND e.entry_id IN (
		   SELECT DISTINCT json_extract(e2.data, '$.org_id')
		   FROM entries e2
		   JOIN access a ON a.project_id = e2.project_id
		   WHERE a.user_id = ? AND a.revoked_at IS NULL
		   AND e2.type = 'deal_org' AND e2.deleted_at IS NULL
		 )
		 ORDER BY e.created_at DESC`, userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []lib.Entry
	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			return nil, err
		}
		entries = append(entries, e)
	}
	return entries, rows.Err()
}

func (h *Handlers) orgToMap(org *lib.Entry) map[string]any {
	result := map[string]any{
		"entry_id":   org.EntryID,
		"type":       org.Type,
		"created_at": org.CreatedAt,
		"created_by": org.CreatedBy,
	}

	// Decrypt and parse org data
	if len(org.Data) > 0 {
		key, err := lib.DeriveProjectKey(h.Cfg.MasterKey, org.ProjectID)
		if err == nil {
			dataText, err := lib.Unpack(key, org.Data)
			if err == nil {
				var orgData lib.OrgData
				if json.Unmarshal([]byte(dataText), &orgData) == nil {
					result["name"] = orgData.Name
					result["domains"] = orgData.Domains
					result["role"] = orgData.Role
					result["logo"] = orgData.Logo
					result["website"] = orgData.Website
					result["description"] = orgData.Description
					result["industry"] = orgData.Industry
					result["contact_name"] = orgData.ContactName
					result["contact_email"] = orgData.ContactEmail
				}
			}
		}
	}

	return result
}

// CreateOrg handles POST /api/orgs — create a new organization
// ib_admin or super_admin only. Domains required.
func (h *Handlers) CreateOrg(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())

	// Check if user is super_admin or ib_admin in any project
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)
	isIBAdmin := h.isIBAdminAnywhere(actorID)

	if !isSuperAdmin && !isIBAdmin {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Only IB admins or super admins can create organizations")
		return
	}

	var req struct {
		Name         string   `json:"name"`
		Domains      []string `json:"domains"`
		Role         string   `json:"role"`
		Website      string   `json:"website"`
		Description  string   `json:"description"`
		ContactName  string   `json:"contact_name"`
		ContactEmail string   `json:"contact_email"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	if req.Name == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Organization name required")
		return
	}
	// Derive domain from website if not provided
	if len(req.Domains) == 0 && req.Website != "" {
		domain := req.Website
		domain = strings.TrimPrefix(domain, "https://")
		domain = strings.TrimPrefix(domain, "http://")
		domain = strings.TrimPrefix(domain, "www.")
		if idx := strings.Index(domain, "/"); idx != -1 {
			domain = domain[:idx]
		}
		if domain != "" {
			req.Domains = []string{strings.ToLower(strings.TrimSpace(domain))}
		}
	}
	// Name is required; domain is best-effort
	if len(req.Domains) == 0 {
		req.Domains = []string{"unknown.invalid"}
	}
	// Validate domains are not empty strings
	for _, d := range req.Domains {
		if strings.TrimSpace(d) == "" {
			ErrorResponse(w, http.StatusBadRequest, "invalid_domains", "Empty domain not allowed")
			return
		}
	}

	// Normalize domains to lowercase
	for i := range req.Domains {
		req.Domains[i] = strings.ToLower(strings.TrimSpace(req.Domains[i]))
	}

	// Valid roles
	validRoles := map[string]bool{"seller": true, "buyer": true, "ib": true, "advisor": true}
	if req.Role != "" && !validRoles[req.Role] {
		ErrorResponse(w, http.StatusBadRequest, "invalid_role", "Role must be one of: seller, buyer, ib, advisor")
		return
	}

	now := time.Now().UnixMilli()
	orgID := uuid.New().String()

	orgData := lib.OrgData{
		Name:         req.Name,
		Domains:      req.Domains,
		Role:         req.Role,
		Website:      req.Website,
		Description:  req.Description,
		ContactName:  req.ContactName,
		ContactEmail: req.ContactEmail,
	}
	dataJSON, _ := json.Marshal(orgData)

	// Organizations use their own ID as project_id for key derivation
	entry := &lib.Entry{
		EntryID:     orgID,
		ProjectID:   orgID, // Orgs are platform level, use own ID
		ParentID:    "",
		Type:        lib.TypeOrganization,
		Depth:       0,
		SummaryText: req.Name,
		DataText:    string(dataJSON),
		Stage:       lib.StageDataroom,
		CreatedBy:   actorID,
		CreatedAt:   now,
		UpdatedAt:   now,
		Version:     1,
		KeyVersion:  1,
	}

	// Pack encrypted fields
	key, err := lib.DeriveProjectKey(h.Cfg.MasterKey, orgID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
		return
	}
	summary, err := lib.Pack(key, entry.SummaryText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	data, err := lib.Pack(key, entry.DataText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	entry.Summary = summary
	entry.Data = data

	// Direct insert (bypass RBAC since orgs are platform-level)
	_, dbErr := h.DB.Conn.Exec(
		`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
		  search_key, search_key2, summary, data, stage,
		  assignee_id, return_to_id, origin_id,
		  version, deleted_at, deleted_by, key_version,
		  created_at, updated_at, created_by)
		 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
		entry.EntryID, entry.ProjectID, "", entry.Type, entry.Depth, 0,
		entry.SearchKey, entry.SearchKey2, entry.Summary, entry.Data, entry.Stage,
		"", "", "",
		entry.Version, nil, nil, entry.KeyVersion,
		entry.CreatedAt, entry.UpdatedAt, entry.CreatedBy,
	)
	if dbErr != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create organization")
		return
	}

	JSONResponse(w, http.StatusCreated, map[string]any{
		"entry_id": orgID,
		"name":     req.Name,
		"domains":  req.Domains,
		"role":     req.Role,
	})
}

func (h *Handlers) isIBAdminAnywhere(userID string) bool {
	var count int
	h.DB.Conn.QueryRow(
		`SELECT COUNT(*) FROM access WHERE user_id = ? AND role = ? AND revoked_at IS NULL`,
		userID, lib.RoleIBAdmin,
	).Scan(&count)
	return count > 0
}

// GetOrg handles GET /api/orgs/{orgID} — get a single organization
func (h *Handlers) GetOrg(w http.ResponseWriter, r *http.Request) {
	orgID := chi.URLParam(r, "orgID")

	org, err := lib.EntryByID(h.DB, h.Cfg, orgID)
	if err != nil || org == nil || org.Type != lib.TypeOrganization {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Organization not found")
		return
	}

	JSONResponse(w, http.StatusOK, h.orgToMap(org))
}

// UpdateOrg handles PATCH /api/orgs/{orgID} — update an organization
func (h *Handlers) UpdateOrg(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	orgID := chi.URLParam(r, "orgID")

	// Check permissions
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)
	isIBAdmin := h.isIBAdminAnywhere(actorID)
	if !isSuperAdmin && !isIBAdmin {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Only IB admins or super admins can update organizations")
		return
	}

	// Get existing org
	org, err := lib.EntryByID(h.DB, h.Cfg, orgID)
	if err != nil || org == nil || org.Type != lib.TypeOrganization {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Organization not found")
		return
	}

	var req struct {
		Name         *string  `json:"name"`
		Domains      []string `json:"domains"`
		Role         *string  `json:"role"`
		Website      *string  `json:"website"`
		Description  *string  `json:"description"`
		ContactName  *string  `json:"contact_name"`
		ContactEmail *string  `json:"contact_email"`
		Version      int      `json:"version"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	// Parse existing org data
	var orgData lib.OrgData
	if org.DataText != "" {
		json.Unmarshal([]byte(org.DataText), &orgData)
	}

	// Apply updates
	if req.Name != nil {
		orgData.Name = *req.Name
	}
	if req.Domains != nil {
		if len(req.Domains) == 0 {
			ErrorResponse(w, http.StatusBadRequest, "missing_fields", "At least one domain required")
			return
		}
		for i := range req.Domains {
			req.Domains[i] = strings.ToLower(strings.TrimSpace(req.Domains[i]))
		}
		orgData.Domains = req.Domains
	}
	if req.Role != nil {
		validRoles := map[string]bool{"seller": true, "buyer": true, "ib": true, "advisor": true, "": true}
		if !validRoles[*req.Role] {
			ErrorResponse(w, http.StatusBadRequest, "invalid_role", "Role must be one of: seller, buyer, ib, advisor")
			return
		}
		orgData.Role = *req.Role
	}
	if req.Website != nil {
		orgData.Website = *req.Website
	}
	if req.Description != nil {
		orgData.Description = *req.Description
	}
	if req.ContactName != nil {
		orgData.ContactName = *req.ContactName
	}
	if req.ContactEmail != nil {
		orgData.ContactEmail = *req.ContactEmail
	}

	dataJSON, _ := json.Marshal(orgData)
	org.DataText = string(dataJSON)
	org.SummaryText = orgData.Name
	org.Version = req.Version

	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, org); err != nil {
		if err == lib.ErrVersionConflict {
			ErrorResponse(w, http.StatusConflict, "version_conflict", err.Error())
			return
		}
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to update organization")
		return
	}

	JSONResponse(w, http.StatusOK, h.orgToMap(org))
}

// ---------------------------------------------------------------------------
// Deal Org API endpoints (per-project organization links)
// ---------------------------------------------------------------------------

// ListDealOrgs handles GET /api/projects/{projectID}/orgs — list orgs in this deal
func (h *Handlers) ListDealOrgs(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	// Check read access to project
	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Get all deal_org entries for this project
	rows, err := h.DB.Conn.Query(
		`SELECT entry_id, project_id, parent_id, type, depth, sort_order,
		        search_key, search_key2, summary, data, stage,
		        assignee_id, return_to_id, origin_id,
		        version, deleted_at, deleted_by, key_version,
		        created_at, updated_at, created_by
		 FROM entries WHERE project_id = ? AND type = 'deal_org' AND deleted_at IS NULL
		 ORDER BY created_at DESC`, projectID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list deal organizations")
		return
	}
	defer rows.Close()

	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)

	var result []map[string]any
	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			continue
		}

		dealOrgMap := map[string]any{
			"deal_org_id": e.EntryID,
			"created_at":  e.CreatedAt,
			"version":     e.Version,
		}

		// Decrypt deal_org data
		if len(e.Data) > 0 {
			dataText, err := lib.Unpack(projectKey, e.Data)
			if err == nil {
				var dealOrgData lib.DealOrgData
				if json.Unmarshal([]byte(dataText), &dealOrgData) == nil {
					dealOrgMap["org_id"] = dealOrgData.OrgID
					dealOrgMap["role"] = dealOrgData.Role
					dealOrgMap["domain_lock"] = dealOrgData.DomainLock
					dealOrgMap["permissions"] = dealOrgData.Permissions
					dealOrgMap["members"] = dealOrgData.Members

					// Fetch org details
					org, err := lib.EntryByID(h.DB, h.Cfg, dealOrgData.OrgID)
					if err == nil && org != nil {
						orgDetails := h.orgToMap(org)
						dealOrgMap["org_name"] = orgDetails["name"]
						dealOrgMap["org_domains"] = orgDetails["domains"]
						dealOrgMap["org_logo"] = orgDetails["logo"]
						dealOrgMap["org_description"] = orgDetails["description"]
						dealOrgMap["org_industry"] = orgDetails["industry"]
						dealOrgMap["org_website"] = orgDetails["website"]
					}
				}
			}
		}

		result = append(result, dealOrgMap)
	}

	if result == nil {
		result = []map[string]any{}
	}

	JSONResponse(w, http.StatusOK, result)
}

// CreateDealOrg handles POST /api/projects/{projectID}/orgs — add org to deal
func (h *Handlers) CreateDealOrg(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	// Check write access to project
	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var req struct {
		OrgID      string `json:"org_id"`
		Role       string `json:"role"`
		DomainLock bool   `json:"domain_lock"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	if req.OrgID == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "org_id required")
		return
	}

	// Verify org exists
	org, err := lib.EntryByID(h.DB, h.Cfg, req.OrgID)
	if err != nil || org == nil || org.Type != lib.TypeOrganization {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Organization not found")
		return
	}

	// Get project entry to use as parent
	project, err := lib.EntryByID(h.DB, h.Cfg, projectID)
	if err != nil || project == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Project not found")
		return
	}

	now := time.Now().UnixMilli()
	dealOrgID := uuid.New().String()

	dealOrgData := lib.DealOrgData{
		OrgID:      req.OrgID,
		Role:       req.Role,
		DomainLock: req.DomainLock,
	}
	dataJSON, _ := json.Marshal(dealOrgData)

	entry := &lib.Entry{
		EntryID:     dealOrgID,
		ProjectID:   projectID,
		ParentID:    projectID, // Parent is the project
		Type:        lib.TypeDealOrg,
		Depth:       1,
		SummaryText: req.Role,
		DataText:    string(dataJSON),
		Stage:       lib.StagePreDataroom,
		CreatedBy:   actorID,
		CreatedAt:   now,
		UpdatedAt:   now,
		Version:     1,
		KeyVersion:  1,
	}

	// Pack encrypted fields
	key, err := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
		return
	}
	summary, err := lib.Pack(key, entry.SummaryText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	data, err := lib.Pack(key, entry.DataText)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Encryption failed")
		return
	}
	entry.Summary = summary
	entry.Data = data

	_, dbErr := h.DB.Conn.Exec(
		`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
		  search_key, search_key2, summary, data, stage,
		  assignee_id, return_to_id, origin_id,
		  version, deleted_at, deleted_by, key_version,
		  created_at, updated_at, created_by)
		 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
		entry.EntryID, entry.ProjectID, entry.ParentID, entry.Type, entry.Depth, 0,
		entry.SearchKey, entry.SearchKey2, entry.Summary, entry.Data, entry.Stage,
		"", "", "",
		entry.Version, nil, nil, entry.KeyVersion,
		entry.CreatedAt, entry.UpdatedAt, entry.CreatedBy,
	)
	if dbErr != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to add organization to deal")
		return
	}

	// Get org name for response
	orgName := ""
	orgDetails := h.orgToMap(org)
	if name, ok := orgDetails["name"].(string); ok {
		orgName = name
	}

	JSONResponse(w, http.StatusCreated, map[string]any{
		"deal_org_id": dealOrgID,
		"org_id":      req.OrgID,
		"org_name":    orgName,
		"role":        req.Role,
		"domain_lock": req.DomainLock,
	})
}

// DeleteDealOrg handles DELETE /api/projects/{projectID}/orgs/{dealOrgID} — remove org from deal
func (h *Handlers) DeleteDealOrg(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	dealOrgID := chi.URLParam(r, "dealOrgID")

	// Check delete access to project
	if err := lib.CheckAccessDelete(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Verify deal_org exists and belongs to this project
	entry, err := lib.EntryByID(h.DB, h.Cfg, dealOrgID)
	if err != nil || entry == nil || entry.Type != lib.TypeDealOrg || entry.ProjectID != projectID {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Deal organization link not found")
		return
	}

	if err := lib.EntryDelete(h.DB, actorID, projectID, dealOrgID); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to remove organization from deal")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]string{"status": "deleted"})
}

// ServeAppOrgs serves the organizations page
func (h *Handlers) ServeAppOrgs(w http.ResponseWriter, r *http.Request) {
	h.serveTemplatePage(w, "app", "app/orgs.html", PageData{Title: "Organizations — Dealspace", ActiveNav: "orgs"})
}

// serveWebsitePage renders a website page inside the website base layout.
func (h *Handlers) serveWebsitePage(w http.ResponseWriter, page string) {
	layoutCandidates := []string{
		"website/layouts/base.html",
		"/opt/dealspace/website/layouts/base.html",
	}
	pageCandidates := []string{
		page,
		filepath.Join("website/pages", page),
		filepath.Join("/opt/dealspace/website/pages", page),
	}

	var layoutPath, pagePath string
	for _, p := range layoutCandidates {
		if _, err := os.Stat(p); err == nil {
			layoutPath = p
			break
		}
	}
	for _, p := range pageCandidates {
		if _, err := os.Stat(p); err == nil {
			pagePath = p
			break
		}
	}

	if layoutPath == "" || pagePath == "" {
		http.Error(w, "Template not found", http.StatusInternalServerError)
		return
	}

	tmpl, err := template.ParseFiles(layoutPath, pagePath)
	if err != nil {
		http.Error(w, "Template parse error: "+err.Error(), http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := tmpl.ExecuteTemplate(w, "layout", nil); err != nil {
		log.Printf("website template error: %v", err)
	}
}

// ServeWebsiteIndex serves the homepage
func (h *Handlers) ServeWebsiteIndex(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "index.html")
}

// ServeWebsiteFeatures serves the features page
func (h *Handlers) ServeWebsiteFeatures(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "features.html")
}

// ServeWebsitePricing serves the pricing page
func (h *Handlers) ServeWebsitePricing(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "pricing.html")
}

// ServeWebsiteSecurity serves the security page
func (h *Handlers) ServeWebsiteSecurity(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "security.html")
}

// ServeWebsitePrivacy serves the privacy page
func (h *Handlers) ServeWebsitePrivacy(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "privacy.html")
}

// ServeWebsiteTerms serves the terms page
func (h *Handlers) ServeWebsiteTerms(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "terms.html")
}

// ServeWebsiteDPA serves the DPA page
func (h *Handlers) ServeWebsiteDPA(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "dpa.html")
}

// ServeWebsiteSOC2 serves the SOC 2 page
func (h *Handlers) ServeWebsiteSOC2(w http.ResponseWriter, r *http.Request) {
	h.serveWebsitePage(w, "soc2.html")
}

// ---------------------------------------------------------------------------
// Request Import/List API endpoints
// ---------------------------------------------------------------------------

// ListRequests handles GET /api/projects/{projectID}/requests
// Returns all request entries for a project, sorted by section + item_number
// ListTemplates returns all saved request templates.
func (h *Handlers) ListTemplates(w http.ResponseWriter, r *http.Request) {
	templates, err := lib.ListTemplates(h.DB, h.Cfg)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list templates")
		return
	}
	result := []map[string]any{}
	for _, t := range templates {
		var data lib.RequestTemplateData
		json.Unmarshal([]byte(t.DataText), &data)
		result = append(result, map[string]any{
			"entry_id":    t.EntryID,
			"name":        data.Name,
			"description": data.Description,
			"item_count":  len(data.Items),
			"created_at":  t.CreatedAt,
		})
	}
	// Also include built-in disk templates as a fallback
	JSONResponse(w, http.StatusOK, result)
}

// SaveTemplate saves a request list as a reusable template.
func (h *Handlers) SaveTemplate(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	var body struct {
		Name        string                      `json:"name"`
		Description string                      `json:"description"`
		Items       []lib.RequestTemplateItem   `json:"items"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || strings.TrimSpace(body.Name) == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_body", "name and items required")
		return
	}
	data := lib.RequestTemplateData{
		Name:        strings.TrimSpace(body.Name),
		Description: body.Description,
		Items:       body.Items,
	}
	dataJSON, _ := json.Marshal(data)
	entry := &lib.Entry{
		ProjectID:   lib.TemplateProjectID,
		ParentID:    lib.TemplateProjectID,
		Type:        lib.TypeRequestTemplate,
		Depth:       1,
		SortOrder:   0,
		SummaryText: data.Name,
		DataText:    string(dataJSON),
		Stage:       lib.StageDataroom,
	}
	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to save template")
		return
	}
	JSONResponse(w, http.StatusOK, map[string]any{"entry_id": entry.EntryID, "name": data.Name})
}

// ImportTemplate imports a template (built-in or saved) into a project as a new request list.
func (h *Handlers) ImportTemplate(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var body struct {
		TemplateID string `json:"template_id"` // entry_id of saved template, or built-in name
		Name       string `json:"name"`        // override list name (optional)
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || body.TemplateID == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_body", "template_id required")
		return
	}

	var tmplData lib.RequestTemplateData

	// Try loading from DB first
	tmplEntry, err := lib.TemplateByID(h.DB, h.Cfg, body.TemplateID)
	if err == nil && tmplEntry != nil {
		json.Unmarshal([]byte(tmplEntry.DataText), &tmplData)
	} else {
		// Fall back to disk templates
		dirs := []string{"templates", "/opt/dealspace/templates"}
		loaded := false
		for _, dir := range dirs {
			fp := dir + "/" + body.TemplateID + ".json"
			if data, err := os.ReadFile(fp); err == nil {
				json.Unmarshal(data, &tmplData)
				loaded = true
				break
			}
		}
		if !loaded {
			ErrorResponse(w, http.StatusNotFound, "not_found", "Template not found")
			return
		}
	}

	listName := strings.TrimSpace(body.Name)
	if listName == "" {
		listName = tmplData.Name
	}

	// Create the request list
	siblings, _ := lib.EntriesByParent(h.DB, h.Cfg, projectID)
	listCount := 0
	for _, s := range siblings {
		if s.Type == lib.TypeRequestList {
			listCount++
		}
	}
	rlData := lib.RequestListData{Name: listName}
	rlDataJSON, _ := json.Marshal(rlData)
	listEntry := &lib.Entry{
		ProjectID:   projectID,
		ParentID:    projectID,
		Type:        lib.TypeRequestList,
		Depth:       1,
		SortOrder:   (listCount + 1) * 1000,
		SummaryText: listName,
		DataText:    string(rlDataJSON),
		Stage:       lib.StagePreDataroom,
	}
	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, listEntry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create request list")
		return
	}

	// Group items by section, create section entries then requests
	type sectionGroup struct {
		name  string
		items []lib.RequestTemplateItem
	}
	var sections []sectionGroup
	sectionIndex := map[string]int{}
	for _, item := range tmplData.Items {
		sec := item.Section
		if sec == "" {
			sec = "General"
		}
		if idx, ok := sectionIndex[sec]; ok {
			sections[idx].items = append(sections[idx].items, item)
		} else {
			sectionIndex[sec] = len(sections)
			sections = append(sections, sectionGroup{name: sec, items: []lib.RequestTemplateItem{item}})
		}
	}

	for si, sg := range sections {
		secData := lib.SectionData{Name: sg.name}
		secDataJSON, _ := json.Marshal(secData)
		secEntry := &lib.Entry{
			ProjectID:   projectID,
			ParentID:    listEntry.EntryID,
			Type:        lib.TypeSection,
			Depth:       2,
			SortOrder:   (si + 1) * 1000,
			SummaryText: sg.name,
			DataText:    string(secDataJSON),
			Stage:       lib.StagePreDataroom,
		}
		if err := lib.EntryWrite(h.DB, h.Cfg, actorID, secEntry); err != nil {
			continue
		}
		for ri, item := range sg.items {
			reqData := lib.RequestData{
				Title:      item.Title,
				ItemNumber: item.ItemNumber,
				Section:    sg.name,
				Priority:   item.Priority,
				Status:     "open",
			}
			if reqData.Priority == "" {
				reqData.Priority = "medium"
			}
			reqDataJSON, _ := json.Marshal(reqData)
			reqEntry := &lib.Entry{
				ProjectID:   projectID,
				ParentID:    secEntry.EntryID,
				Type:        lib.TypeRequest,
				Depth:       3,
				SortOrder:   (ri + 1) * 100,
				SummaryText: item.Title,
				DataText:    string(reqDataJSON),
				Stage:       lib.StagePreDataroom,
			}
			lib.EntryWrite(h.DB, h.Cfg, actorID, reqEntry)
		}
	}

	JSONResponse(w, http.StatusOK, map[string]any{
		"entry_id": listEntry.EntryID,
		"name":     listName,
		"sections": len(sections),
		"items":    len(tmplData.Items),
	})
}

func (h *Handlers) ListRequests(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Get all request entries for this project
	rows, err := h.DB.Conn.Query(
		`SELECT entry_id, project_id, parent_id, type, depth, sort_order,
		        search_key, search_key2, summary, data, stage,
		        assignee_id, return_to_id, origin_id,
		        version, deleted_at, deleted_by, key_version,
		        created_at, updated_at, created_by
		 FROM entries WHERE project_id = ? AND type = 'request' AND deleted_at IS NULL
		 ORDER BY sort_order ASC, created_at ASC`, projectID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list requests")
		return
	}
	defer rows.Close()

	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)

	type RequestItem struct {
		EntryID    string          `json:"entry_id"`
		ProjectID  string          `json:"project_id"`
		Section    string          `json:"section"`
		ItemNumber string          `json:"item_number"`
		Title      string          `json:"title"`
		Priority   string          `json:"priority"`
		Status     string          `json:"status"`
		CreatedAt  int64           `json:"created_at"`
		Data       lib.RequestData `json:"data"`
	}

	var requests []RequestItem
	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			continue
		}

		item := RequestItem{
			EntryID:   e.EntryID,
			ProjectID: e.ProjectID,
			CreatedAt: e.CreatedAt,
		}

		// Decrypt data
		if len(e.Data) > 0 {
			dataText, err := lib.Unpack(projectKey, e.Data)
			if err == nil {
				var reqData lib.RequestData
				if json.Unmarshal([]byte(dataText), &reqData) == nil {
					item.Section = reqData.Section
					item.ItemNumber = reqData.ItemNumber
					item.Title = reqData.Title
					item.Priority = reqData.Priority
					item.Status = reqData.Status
					item.Data = reqData
				}
			}
		}

		requests = append(requests, item)
	}

	// Sort by section, then item_number
	// (already sorted by created_at from query, which preserves import order)

	if requests == nil {
		requests = []RequestItem{}
	}

	JSONResponse(w, http.StatusOK, requests)
}

// ImportRequests handles POST /api/projects/{projectID}/requests/import
// Accepts multipart form with CSV/XLSX file, mode (add/replace), section_filter, create_workstreams
// CreateSection creates a new section under a request_list.
func (h *Handlers) CreateSection(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied"); return
	}
	var body struct {
		Name     string `json:"name"`
		ParentID string `json:"parent_id"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || strings.TrimSpace(body.Name) == "" || body.ParentID == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_body", "name and parent_id required"); return
	}
	parent, err := lib.EntryByID(h.DB, h.Cfg, body.ParentID)
	if err != nil || parent == nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_parent", "Parent entry not found"); return
	}
	siblings, _ := lib.EntriesByParent(h.DB, h.Cfg, body.ParentID)
	name := strings.TrimSpace(body.Name)
	secData := lib.SectionData{Name: name}
	secDataJSON, _ := json.Marshal(secData)
	entry := &lib.Entry{
		ProjectID:   projectID,
		ParentID:    body.ParentID,
		Type:        lib.TypeSection,
		Depth:       parent.Depth + 1,
		SortOrder:   (len(siblings) + 1) * 1000,
		SummaryText: name,
		DataText:    string(secDataJSON),
		Stage:       lib.StagePreDataroom,
	}
	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create section"); return
	}
	JSONResponse(w, http.StatusOK, map[string]any{"entry_id": entry.EntryID, "name": name})
}

// CreateRequest creates a new blank request under a section or request_list.
func (h *Handlers) CreateRequest(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied"); return
	}
	var body struct {
		Title    string `json:"title"`
		ParentID string `json:"parent_id"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || strings.TrimSpace(body.Title) == "" || body.ParentID == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_body", "title and parent_id required"); return
	}
	parent, err := lib.EntryByID(h.DB, h.Cfg, body.ParentID)
	if err != nil || parent == nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_parent", "Parent entry not found"); return
	}
	siblings, _ := lib.EntriesByParent(h.DB, h.Cfg, body.ParentID)
	title := strings.TrimSpace(body.Title)
	reqData := lib.RequestData{Title: title, Status: "open", Priority: "medium"}
	reqDataJSON, _ := json.Marshal(reqData)
	entry := &lib.Entry{
		ProjectID:   projectID,
		ParentID:    body.ParentID,
		Type:        lib.TypeRequest,
		Depth:       parent.Depth + 1,
		SortOrder:   (len(siblings) + 1) * 100,
		SummaryText: title,
		DataText:    string(reqDataJSON),
		Stage:       lib.StagePreDataroom,
	}
	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create request"); return
	}
	JSONResponse(w, http.StatusOK, map[string]any{"entry_id": entry.EntryID, "title": title})
}

// CreateRequestList creates a new empty request list for a project.
func (h *Handlers) CreateRequestList(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var body struct {
		Name string `json:"name"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || strings.TrimSpace(body.Name) == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_body", "name is required")
		return
	}

	listName := strings.TrimSpace(body.Name)
	// Sort after existing lists: use sibling count * 1000
	siblings, _ := lib.EntriesByParent(h.DB, h.Cfg, projectID)
	listCount := 0
	for _, s := range siblings {
		if s.Type == lib.TypeRequestList {
			listCount++
		}
	}
	rlData := lib.RequestListData{Name: listName}
	rlDataJSON, _ := json.Marshal(rlData)
	entry := &lib.Entry{
		ProjectID:   projectID,
		ParentID:    projectID,
		Type:        lib.TypeRequestList,
		Depth:       1,
		SortOrder:   (listCount + 1) * 1000,
		SummaryText: listName,
		DataText:    string(rlDataJSON),
		Stage:       lib.StagePreDataroom,
	}
	if err := lib.EntryWrite(h.DB, h.Cfg, actorID, entry); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create request list")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]any{
		"entry_id": entry.EntryID,
		"name":     listName,
	})
}

func (h *Handlers) ImportRequests(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Parse multipart form (max 20MB)
	if err := r.ParseMultipartForm(20 << 20); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_form", "Failed to parse form data")
		return
	}

	file, header, err := r.FormFile("file")
	if err != nil {
		ErrorResponse(w, http.StatusBadRequest, "missing_file", "File is required")
		return
	}
	defer file.Close()

	mode := r.FormValue("mode")
	if mode == "" {
		mode = "add"
	}
	sectionFilter := r.FormValue("section_filter")

	// Read file into memory
	raw, err := io.ReadAll(file)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to read file")
		return
	}

	// Detect file type by extension or magic bytes
	fname := strings.ToLower(header.Filename)
	isXLSX := strings.HasSuffix(fname, ".xlsx") || strings.HasSuffix(fname, ".xls") ||
		(len(raw) >= 2 && raw[0] == 'P' && raw[1] == 'K')

	var rows [][]string

	if isXLSX {
		xf, err := excelize.OpenReader(bytes.NewReader(raw))
		if err != nil {
			ErrorResponse(w, http.StatusBadRequest, "invalid_xlsx", "Failed to parse XLSX: "+err.Error())
			return
		}
		sheetName := xf.GetSheetName(0)
		xlRows, err := xf.GetRows(sheetName)
		if err != nil {
			ErrorResponse(w, http.StatusBadRequest, "invalid_xlsx", "Failed to read sheet: "+err.Error())
			return
		}
		rows = xlRows
	} else {
		reader := csv.NewReader(bufio.NewReader(bytes.NewReader(raw)))
		reader.FieldsPerRecord = -1
		reader.TrimLeadingSpace = true
		csvRows, err := reader.ReadAll()
		if err != nil {
			ErrorResponse(w, http.StatusBadRequest, "invalid_csv", "Failed to parse CSV: "+err.Error())
			return
		}
		rows = csvRows
	}

	// Log first 12 rows for debugging
	for ri, row := range rows {
		if ri >= 12 {
			break
		}
		log.Printf("[import-debug] row %d: %v", ri, row)
	}

	// Smart header detection: scan first 12 rows for keyword matches
	idxSection := -1
	idxItem := -1
	idxDesc := -1
	idxPriority := -1
	headerRowIdx := 0
	bestScore := 0

	for ri, record := range rows {
		if ri >= 12 {
			break
		}
		score := 0
		tmpSection, tmpItem, tmpDesc, tmpPri := -1, -1, -1, -1
		for ci, cell := range record {
			h := strings.ToLower(strings.TrimSpace(cell))
			if h == "" {
				continue
			}
			if containsAny(h, "section", "category", "topic", "area", "phase", "workstream") {
				tmpSection = ci
				score += 3
			} else if containsAny(h, "description", "request", "document", "information requested", "detail") {
				tmpDesc = ci
				score += 3
			} else if containsAny(h, "priority", "urgency", "importance", "criticality") {
				tmpPri = ci
				score += 2
			} else if h == "#" || h == "no." || h == "no" || h == "item #" || h == "item#" ||
				containsAny(h, "item no", "ref no", "ref #") {
				tmpItem = ci
				score += 2
			}
		}
		if score > bestScore {
			bestScore = score
			headerRowIdx = ri
			if tmpSection >= 0 {
				idxSection = tmpSection
			}
			if tmpItem >= 0 {
				idxItem = tmpItem
			}
			if tmpDesc >= 0 {
				idxDesc = tmpDesc
			}
			if tmpPri >= 0 {
				idxPriority = tmpPri
			}
		}
	}

	// Fall back to positional if no header found
	if bestScore < 2 {
		headerRowIdx = 0
		idxSection = 0
		idxItem = 1
		idxDesc = 2
	}

	// If desc still not found, pick column with longest average text
	if idxDesc < 0 && len(rows) > headerRowIdx+1 {
		maxLen := 0
		for ci := range rows[headerRowIdx] {
			total := 0
			count := 0
			for ri := headerRowIdx + 1; ri < len(rows) && ri < headerRowIdx+20; ri++ {
				if ci < len(rows[ri]) {
					total += len(strings.TrimSpace(rows[ri][ci]))
					count++
				}
			}
			avg := 0
			if count > 0 {
				avg = total / count
			}
			if avg > maxLen && ci != idxSection && ci != idxItem {
				maxLen = avg
				idxDesc = ci
			}
		}
	}

	log.Printf("[import-debug] header at row %d (score=%d) | section=%d item=%d desc=%d priority=%d",
		headerRowIdx, bestScore, idxSection, idxItem, idxDesc, idxPriority)

	// Parse rows into request items
	type reqRow struct {
		section, itemNumber, description, priority string
	}
	var items []reqRow
	sections := make(map[string]bool)

	for ri, record := range rows {
		if ri <= headerRowIdx {
			continue
		}
		if len(record) == 0 {
			continue
		}
		// Skip blank rows
		allBlank := true
		for _, c := range record {
			if strings.TrimSpace(c) != "" {
				allBlank = false
				break
			}
		}
		if allBlank {
			continue
		}

		get := func(idx int) string {
			if idx >= 0 && idx < len(record) {
				return strings.TrimSpace(record[idx])
			}
			return ""
		}

		desc := get(idxDesc)
		if desc == "" {
			continue
		}

		section := get(idxSection)
		if sectionFilter != "" && !strings.EqualFold(section, sectionFilter) {
			continue
		}

		priority := "medium"
		if idxPriority >= 0 {
			p := strings.ToLower(get(idxPriority))
			switch {
			case strings.Contains(p, "high") || strings.Contains(p, "critical") || strings.Contains(p, "urgent"):
				priority = "high"
			case strings.Contains(p, "low") || strings.Contains(p, "nice") || strings.Contains(p, "optional"):
				priority = "low"
			}
		}

		items = append(items, reqRow{
			section:     section,
			itemNumber:  get(idxItem),
			description: desc,
			priority:    priority,
		})
		sections[section] = true
	}

	if len(items) == 0 {
		ErrorResponse(w, http.StatusBadRequest, "no_items", "No valid items found in file")
		return
	}

	// Handle replace mode: soft-delete existing request_lists and their subtrees
	if mode == "replace" {
		// Find all request_list entries for this project
		rlRows, err := h.DB.Conn.Query(
			`SELECT entry_id FROM entries WHERE project_id = ? AND type IN ('request_list','section','request') AND deleted_at IS NULL`,
			projectID,
		)
		if err == nil {
			defer rlRows.Close()
			now := time.Now().UnixMilli()
			for rlRows.Next() {
				var eid string
				rlRows.Scan(&eid)
				h.DB.Conn.Exec(
					`UPDATE entries SET deleted_at = ?, deleted_by = ?, updated_at = ? WHERE entry_id = ? AND deleted_at IS NULL`,
					now, actorID, now, eid,
				)
			}
		}
	}

	// Derive project key for encryption
	projectKey, err := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
		return
	}

	now := time.Now().UnixMilli()
	imported := 0
	skipped := 0

	// Get list_name from form (defaults to filename without extension)
	listName := r.FormValue("list_name")
	if listName == "" {
		listName = strings.TrimSuffix(header.Filename, filepath.Ext(header.Filename))
	}

	// 1. Create request_list entry (depth=1, parent=project)
	requestListID := uuid.New().String()
	visibilityOrgID := r.FormValue("visibility_org_id")
	rlData := lib.RequestListData{Name: listName, VisibilityOrgID: visibilityOrgID}
	rlDataJSON, _ := json.Marshal(rlData)
	rlSummaryPacked, _ := lib.Pack(projectKey, listName)
	rlDataPacked, _ := lib.Pack(projectKey, string(rlDataJSON))

	var importMaxSort int
	h.DB.Conn.QueryRow(
		`SELECT COALESCE(MAX(sort_order),0) FROM entries WHERE project_id=? AND type='request_list' AND deleted_at IS NULL`, projectID,
	).Scan(&importMaxSort)
	rlSortOrder := importMaxSort + 1000

	h.DB.Conn.Exec(
		`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
		  search_key, search_key2, summary, data, stage,
		  assignee_id, return_to_id, origin_id,
		  version, deleted_at, deleted_by, key_version,
		  created_at, updated_at, created_by)
		 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
		requestListID, projectID, projectID, lib.TypeRequestList, 1, rlSortOrder,
		nil, nil, rlSummaryPacked, rlDataPacked, lib.StagePreDataroom,
		"", "", "",
		1, nil, nil, 1,
		now, now, actorID,
	)

	// 2. Group items by section and create section + request entries
	// Preserve section order from the file
	var sectionOrder []string
	sectionSeen := map[string]bool{}
	sectionItems := map[string][]reqRow{}
	for _, item := range items {
		sec := item.section
		if sec == "" {
			sec = "General"
		}
		if !sectionSeen[sec] {
			sectionSeen[sec] = true
			sectionOrder = append(sectionOrder, sec)
		}
		sectionItems[sec] = append(sectionItems[sec], item)
	}

	for secIdx, secName := range sectionOrder {
		// Create section entry (depth=2, parent=request_list)
		sectionID := uuid.New().String()
		secData := lib.SectionData{Name: secName}
		secDataJSON, _ := json.Marshal(secData)
		secSummaryPacked, _ := lib.Pack(projectKey, secName)
		secDataPacked, _ := lib.Pack(projectKey, string(secDataJSON))

		h.DB.Conn.Exec(
			`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
			  search_key, search_key2, summary, data, stage,
			  assignee_id, return_to_id, origin_id,
			  version, deleted_at, deleted_by, key_version,
			  created_at, updated_at, created_by)
			 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
			sectionID, projectID, requestListID, lib.TypeSection, 2, (secIdx+1)*1000,
			nil, nil, secSummaryPacked, secDataPacked, lib.StagePreDataroom,
			"", "", "",
			1, nil, nil, 1,
			now, now, actorID,
		)

		// Create request entries under this section (depth=3)
		for reqIdx, item := range sectionItems[secName] {
			entryID := uuid.New().String()

			summary := item.description
			if len(summary) > 120 {
				summary = summary[:120]
			}

			// Normalize priority
			priority := item.priority
			switch strings.ToLower(priority) {
			case "critical", "urgent":
				priority = "critical"
			case "high":
				priority = "high"
			case "low", "optional", "nice":
				priority = "low"
			default:
				priority = "medium"
			}

			reqData := lib.RequestData{
				Title:       item.description,
				ItemNumber:  item.itemNumber,
				Section:     secName,
				Description: item.description,
				Priority:    priority,
				Status:      "open",
			}
			dataJSON, _ := json.Marshal(reqData)

			summaryPacked, err := lib.Pack(projectKey, summary)
			if err != nil {
				skipped++
				continue
			}
			dataPacked, err := lib.Pack(projectKey, string(dataJSON))
			if err != nil {
				skipped++
				continue
			}

			_, err = h.DB.Conn.Exec(
				`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
				  search_key, search_key2, summary, data, stage,
				  assignee_id, return_to_id, origin_id,
				  version, deleted_at, deleted_by, key_version,
				  created_at, updated_at, created_by)
				 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
				entryID, projectID, sectionID, lib.TypeRequest, 3, (reqIdx+1)*1000,
				nil, nil, summaryPacked, dataPacked, lib.StagePreDataroom,
				"", "", "",
				1, nil, nil, 1,
				now, now, actorID,
			)
			if err != nil {
				log.Printf("Failed to insert request: %v", err)
				skipped++
				continue
			}
			imported++
		}
	}

	log.Printf("[import] list=%q, total rows: %d, header row: %d, imported: %d, skipped: %d, sections: %v",
		listName, len(rows), headerRowIdx, imported, skipped, sectionOrder)

	JSONResponse(w, http.StatusOK, map[string]any{
		"imported":        imported,
		"skipped":         skipped,
		"sections":        sectionOrder,
		"request_list_id": requestListID,
	})
}

// UpdateRequestListVisibility handles PATCH /api/projects/{projectID}/entries/{entryID}/visibility
// Sets or clears the visibility_org_id on a request_list entry. IB roles only.
func (h *Handlers) UpdateRequestListVisibility(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	entryID := chi.URLParam(r, "entryID")

	// Only IB roles can change visibility
	role, err := lib.GetUserHighestRole(h.DB, actorID, projectID)
	if err != nil || (role != lib.RoleIBAdmin && role != lib.RoleIBMember) {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Only IB roles can change list visibility")
		return
	}

	var req struct {
		VisibilityOrgID *string `json:"visibility_org_id"` // null to clear
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	// Read the entry and update its data
	entry, err := lib.EntryByID(h.DB, h.Cfg, entryID)
	if err != nil || entry == nil || entry.Type != lib.TypeRequestList || entry.ProjectID != projectID {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Request list not found")
		return
	}

	var rlData lib.RequestListData
	if entry.DataText != "" {
		json.Unmarshal([]byte(entry.DataText), &rlData)
	}

	if req.VisibilityOrgID != nil {
		rlData.VisibilityOrgID = *req.VisibilityOrgID
	} else {
		rlData.VisibilityOrgID = ""
	}

	dataJSON, _ := json.Marshal(rlData)
	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)
	dataPacked, _ := lib.Pack(projectKey, string(dataJSON))

	now := time.Now().UnixMilli()
	_, err = h.DB.Conn.Exec(
		`UPDATE entries SET data = ?, updated_at = ?, version = version + 1 WHERE entry_id = ?`,
		dataPacked, now, entryID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to update visibility")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]string{"status": "updated"})
}

// ---------------------------------------------------------------------------
// Tree API endpoints
// ---------------------------------------------------------------------------

// ListRequestTree handles GET /api/projects/{projectID}/requests/tree
// Returns all request_list, section, and request entries in DFS tree order.
func (h *Handlers) ListRequestTree(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	// Get actor's role and org for visibility filtering
	actorRole, _ := lib.GetUserHighestRole(h.DB, actorID, projectID)
	actorUser, _ := lib.UserByID(h.DB, actorID)
	actorOrgID := ""
	if actorUser != nil {
		actorOrgID = actorUser.OrgID
	}
	isIBRole := actorRole == lib.RoleIBAdmin || actorRole == lib.RoleIBMember

	rows, err := h.DB.Conn.Query(
		`SELECT entry_id, project_id, parent_id, type, depth, sort_order,
		        search_key, search_key2, summary, data, stage,
		        assignee_id, return_to_id, origin_id,
		        version, deleted_at, deleted_by, key_version,
		        created_at, updated_at, created_by
		 FROM entries
		 WHERE project_id = ? AND type IN ('request_list','section','request') AND deleted_at IS NULL
		 ORDER BY sort_order ASC, created_at ASC`, projectID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list request tree")
		return
	}
	defer rows.Close()

	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)

	// Collect all entries
	type treeEntry struct {
		EntryID   string `json:"entry_id"`
		ParentID  string `json:"parent_id"`
		Type      string `json:"type"`
		Depth     int    `json:"depth"`
		SortOrder int    `json:"sort_order"`
		Version   int    `json:"version"`
		CreatedAt int64  `json:"created_at"`
		Data      any    `json:"data"`
	}

	var all []treeEntry
	byID := map[string]*treeEntry{}
	childrenOf := map[string][]string{} // parent_id → [entry_ids]

	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			continue
		}

		te := treeEntry{
			EntryID:   e.EntryID,
			ParentID:  e.ParentID,
			Type:      e.Type,
			Depth:     e.Depth,
			SortOrder: e.SortOrder,
			Version:   e.Version,
			CreatedAt: e.CreatedAt,
		}

		// Decrypt data
		if len(e.Data) > 0 {
			dataText, err := lib.Unpack(projectKey, e.Data)
			if err == nil {
				var parsed any
				if json.Unmarshal([]byte(dataText), &parsed) == nil {
					te.Data = parsed
				}
			}
		}

		all = append(all, te)
		byID[te.EntryID] = &all[len(all)-1]
		childrenOf[te.ParentID] = append(childrenOf[te.ParentID], te.EntryID)
	}

	// Build set of hidden request_list IDs based on visibility
	hiddenLists := map[string]bool{}
	if !isIBRole {
		for _, te := range all {
			if te.Type == lib.TypeRequestList {
				if dataMap, ok := te.Data.(map[string]any); ok {
					if visOrgID, ok := dataMap["visibility_org_id"].(string); ok && visOrgID != "" {
						if visOrgID != actorOrgID {
							hiddenLists[te.EntryID] = true
						}
					}
				}
			}
		}
	}

	// Get answer link counts
	linkCounts, _ := lib.AnswerLinkCountsByProject(h.DB, projectID)

	// DFS to produce tree order
	type treeItem struct {
		EntryID       string `json:"entry_id"`
		ParentID      string `json:"parent_id"`
		Type          string `json:"type"`
		Depth         int    `json:"depth"`
		SortOrder     int    `json:"sort_order"`
		Version       int    `json:"version"`
		CreatedAt     int64  `json:"created_at"`
		Data          any    `json:"data"`
		AnswerCount   int    `json:"answer_count"`
		ChildrenCount int    `json:"children_count"`
	}

	var result []treeItem
	var dfs func(parentID string)
	dfs = func(parentID string) {
		children := childrenOf[parentID]
		for _, cid := range children {
			te := byID[cid]
			if te == nil {
				continue
			}
			// Skip hidden request_lists and their subtrees
			if hiddenLists[te.EntryID] {
				continue
			}
			item := treeItem{
				EntryID:       te.EntryID,
				ParentID:      te.ParentID,
				Type:          te.Type,
				Depth:         te.Depth,
				SortOrder:     te.SortOrder,
				Version:       te.Version,
				CreatedAt:     te.CreatedAt,
				Data:          te.Data,
				AnswerCount:   linkCounts[te.EntryID],
				ChildrenCount: len(childrenOf[te.EntryID]),
			}
			result = append(result, item)
			dfs(te.EntryID)
		}
	}

	// Start DFS from project (request_lists have parent=projectID)
	dfs(projectID)

	if result == nil {
		result = []treeItem{}
	}

	JSONResponse(w, http.StatusOK, result)
}

// MoveEntry handles POST /api/projects/{projectID}/entries/{entryID}/move
// Body: {"parent_id": "...", "position": 0}
func (h *Handlers) MoveEntry(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	entryID := chi.URLParam(r, "entryID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var req struct {
		ParentID string `json:"parent_id"`
		Position int    `json:"position"` // 0-based position among siblings
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	// Get the entry being moved
	entry, err := lib.EntryByID(h.DB, h.Cfg, entryID)
	if err != nil || entry == nil {
		ErrorResponse(w, http.StatusNotFound, "not_found", "Entry not found")
		return
	}
	if entry.ProjectID != projectID {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Entry not in project")
		return
	}

	// Get new parent to determine depth
	newDepth := 1
	if req.ParentID != projectID {
		parent, err := lib.EntryByID(h.DB, h.Cfg, req.ParentID)
		if err != nil || parent == nil {
			ErrorResponse(w, http.StatusBadRequest, "invalid_parent", "Parent not found")
			return
		}
		newDepth = parent.Depth + 1
	}

	oldParentID := entry.ParentID

	// Assign sort_order: position * 1000
	sortOrder := (req.Position + 1) * 1000

	// Move the entry
	if err := lib.EntryMoveSort(h.DB, entryID, req.ParentID, newDepth, sortOrder); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to move entry")
		return
	}

	// Renumber siblings at new parent
	lib.RenumberSiblings(h.DB, req.ParentID)

	// Renumber siblings at old parent if different
	if oldParentID != req.ParentID {
		lib.RenumberSiblings(h.DB, oldParentID)
	}

	// Update depth of all descendants recursively
	h.updateDescendantDepths(entryID, newDepth)

	JSONResponse(w, http.StatusOK, map[string]string{"status": "moved"})
}

// updateDescendantDepths recursively updates depth of children.
func (h *Handlers) updateDescendantDepths(parentID string, parentDepth int) {
	rows, err := h.DB.Conn.Query(
		`SELECT entry_id FROM entries WHERE parent_id = ? AND deleted_at IS NULL`, parentID,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	var ids []string
	for rows.Next() {
		var id string
		rows.Scan(&id)
		ids = append(ids, id)
	}

	childDepth := parentDepth + 1
	for _, id := range ids {
		h.DB.Conn.Exec(`UPDATE entries SET depth = ? WHERE entry_id = ?`, childDepth, id)
		h.updateDescendantDepths(id, childDepth)
	}
}

// ListAnswerLinks handles GET /api/projects/{projectID}/requests/{requestID}/links
func (h *Handlers) ListAnswerLinks(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	requestID := chi.URLParam(r, "requestID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	links, err := lib.AnswerLinksByRequest(h.DB, requestID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list answer links")
		return
	}

	// Enrich with answer entry data
	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)

	type LinkItem struct {
		AnswerID  string `json:"answer_id"`
		RequestID string `json:"request_id"`
		LinkedAt  int64  `json:"linked_at"`
		Status    string `json:"status"`
		Data      any    `json:"data"`
	}

	var result []LinkItem
	for _, l := range links {
		item := LinkItem{
			AnswerID:  l.AnswerID,
			RequestID: l.RequestID,
			LinkedAt:  l.LinkedAt,
			Status:    l.Status,
		}
		// Get answer entry data
		answerEntry, err := lib.EntryByID(h.DB, h.Cfg, l.AnswerID)
		if err == nil && answerEntry != nil && answerEntry.DataText != "" {
			var parsed any
			if json.Unmarshal([]byte(answerEntry.DataText), &parsed) == nil {
				item.Data = parsed
			}
		} else if err == nil && answerEntry != nil && len(answerEntry.Data) > 0 {
			dataText, err := lib.Unpack(projectKey, answerEntry.Data)
			if err == nil {
				var parsed any
				if json.Unmarshal([]byte(dataText), &parsed) == nil {
					item.Data = parsed
				}
			}
		}
		result = append(result, item)
	}

	if result == nil {
		result = []LinkItem{}
	}
	JSONResponse(w, http.StatusOK, result)
}

// CreateAnswerLink handles POST /api/projects/{projectID}/requests/{requestID}/links
func (h *Handlers) CreateAnswerLink(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	requestID := chi.URLParam(r, "requestID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var req struct {
		AnswerID string `json:"answer_id"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil || req.AnswerID == "" {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "answer_id required")
		return
	}

	if err := lib.AnswerLinkCreate(h.DB, req.AnswerID, requestID, actorID); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create link")
		return
	}

	JSONResponse(w, http.StatusCreated, map[string]string{"status": "linked"})
}

// DeleteAnswerLink handles DELETE /api/projects/{projectID}/requests/{requestID}/links/{answerID}
func (h *Handlers) DeleteAnswerLink(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")
	requestID := chi.URLParam(r, "requestID")
	answerID := chi.URLParam(r, "answerID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	if err := lib.AnswerLinkDelete(h.DB, answerID, requestID); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to delete link")
		return
	}

	JSONResponse(w, http.StatusOK, map[string]string{"status": "unlinked"})
	_ = actorID // actorID checked above
}

// ListAnswers handles GET /api/projects/{projectID}/answers
// Returns all answer entries for the project, for use in the link picker.
func (h *Handlers) ListAnswers(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessRead(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	q := r.URL.Query().Get("q")

	rows, err := h.DB.Conn.Query(
		`SELECT entry_id, project_id, parent_id, type, depth, sort_order,
		        search_key, search_key2, summary, data, stage,
		        assignee_id, return_to_id, origin_id,
		        version, deleted_at, deleted_by, key_version,
		        created_at, updated_at, created_by
		 FROM entries WHERE project_id = ? AND type = 'answer' AND deleted_at IS NULL
		 ORDER BY created_at DESC`, projectID,
	)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to list answers")
		return
	}
	defer rows.Close()

	projectKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)

	type AnswerItem struct {
		EntryID   string `json:"entry_id"`
		CreatedAt int64  `json:"created_at"`
		Data      any    `json:"data"`
	}

	var result []AnswerItem
	for rows.Next() {
		var e lib.Entry
		err := rows.Scan(
			&e.EntryID, &e.ProjectID, &e.ParentID, &e.Type, &e.Depth, &e.SortOrder,
			&e.SearchKey, &e.SearchKey2, &e.Summary, &e.Data, &e.Stage,
			&e.AssigneeID, &e.ReturnToID, &e.OriginID,
			&e.Version, &e.DeletedAt, &e.DeletedBy, &e.KeyVersion,
			&e.CreatedAt, &e.UpdatedAt, &e.CreatedBy,
		)
		if err != nil {
			continue
		}

		item := AnswerItem{
			EntryID:   e.EntryID,
			CreatedAt: e.CreatedAt,
		}

		if len(e.Data) > 0 {
			dataText, err := lib.Unpack(projectKey, e.Data)
			if err == nil {
				var parsed any
				if json.Unmarshal([]byte(dataText), &parsed) == nil {
					item.Data = parsed
					// Filter by search query
					if q != "" && !strings.Contains(strings.ToLower(dataText), strings.ToLower(q)) {
						continue
					}
				}
			}
		}

		result = append(result, item)
	}

	if result == nil {
		result = []AnswerItem{}
	}
	JSONResponse(w, http.StatusOK, result)
}

// containsAny checks if s contains any of the given substrings
func containsAny(s string, subs ...string) bool {
	for _, sub := range subs {
		if strings.Contains(s, sub) {
			return true
		}
	}
	return false
}

// UpdateOrg handles PUT /api/orgs/{orgId} — update org name, domains, role, website

// SetTestRole handles PUT /api/admin/test-role — super admin sets their test role for the current session.
func (h *Handlers) SetTestRole(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	isSuperAdmin, _ := lib.IsSuperAdmin(h.DB, actorID)
	if !isSuperAdmin {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Super admin only")
		return
	}
	sessionID := SessionIDFromContext(r.Context())
	if sessionID == "" {
		ErrorResponse(w, http.StatusBadRequest, "no_session", "No session found")
		return
	}
	var req struct {
		Role string `json:"role"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}
	validRoles := map[string]bool{"": true, "ib": true, "buyer": true, "seller": true, "advisor": true}
	if !validRoles[req.Role] {
		ErrorResponse(w, http.StatusBadRequest, "invalid_role", "Role must be one of: ib, buyer, seller, advisor (or empty to reset)")
		return
	}
	if err := lib.SetSessionTestRole(h.DB, sessionID, req.Role); err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to set test role")
		return
	}
	JSONResponse(w, http.StatusOK, map[string]any{"ok": true, "test_role": req.Role})
}

// AddOrgToDeal handles POST /api/projects/{projectID}/orgs/add — creates org (if new) + deal_org + members in one shot.
func (h *Handlers) AddOrgToDeal(w http.ResponseWriter, r *http.Request) {
	actorID := UserIDFromContext(r.Context())
	projectID := chi.URLParam(r, "projectID")

	if err := lib.CheckAccessWrite(h.DB, actorID, projectID, ""); err != nil {
		ErrorResponse(w, http.StatusForbidden, "access_denied", "Access denied")
		return
	}

	var req struct {
		// Org info
		Name        string   `json:"name"`
		Domains     []string `json:"domains"`
		Role        string   `json:"role"` // seller | buyer | ib | advisor
		Logo        string   `json:"logo"`
		Website     string   `json:"website"`
		Description string   `json:"description"`
		Industry    string   `json:"industry"`
		Phone       string   `json:"phone"`
		Fax         string   `json:"fax"`
		Address     string   `json:"address"`
		City        string   `json:"city"`
		State       string   `json:"state"`
		Country     string   `json:"country"`
		Founded     string   `json:"founded"`
		LinkedIn    string   `json:"linkedin"`
		// Selected members
		Members []lib.DealOrgMember `json:"members"`
		// Deal org settings
		DomainLock bool `json:"domain_lock"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}

	if req.Name == "" {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Organization name required")
		return
	}
	// Derive domain from website if not provided
	if len(req.Domains) == 0 && req.Website != "" {
		domain := req.Website
		domain = strings.TrimPrefix(domain, "https://")
		domain = strings.TrimPrefix(domain, "http://")
		domain = strings.TrimPrefix(domain, "www.")
		if idx := strings.Index(domain, "/"); idx != -1 {
			domain = domain[:idx]
		}
		if domain != "" {
			req.Domains = []string{strings.ToLower(strings.TrimSpace(domain))}
		}
	}
	// Name is required; domain is best-effort
	if len(req.Domains) == 0 {
		req.Domains = []string{"unknown.invalid"}
	}
	validRoles := map[string]bool{"seller": true, "buyer": true, "ib": true, "advisor": true}
	if req.Role == "" || !validRoles[req.Role] {
		ErrorResponse(w, http.StatusBadRequest, "invalid_role", "Role must be one of: seller, buyer, ib, advisor")
		return
	}

	for i := range req.Domains {
		req.Domains[i] = strings.ToLower(strings.TrimSpace(req.Domains[i]))
	}

	now := time.Now().UnixMilli()

	// Step 1: Check if an organization with a matching domain already exists
	orgID := ""
	existingOrgs, _ := h.queryOrgsByType("")
	for _, org := range existingOrgs {
		orgMap := h.orgToMap(&org)
		if domains, ok := orgMap["domains"].([]string); ok {
			for _, d := range domains {
				for _, rd := range req.Domains {
					if strings.EqualFold(d, rd) {
						orgID = org.EntryID
						break
					}
				}
				if orgID != "" {
					break
				}
			}
		}
		if orgID != "" {
			log.Printf("reusing existing org %s for domain %v", orgID, req.Domains)
			break
		}
	}

	// Create new org if no match found
	if orgID == "" {
		orgID = uuid.New().String()
		orgData := lib.OrgData{
			Name:        req.Name,
			Domains:     req.Domains,
			Role:        req.Role,
			Logo:        req.Logo,
			Website:     req.Website,
			Description: req.Description,
			Industry:    req.Industry,
			Phone:       req.Phone,
			Fax:         req.Fax,
			Address:     req.Address,
			City:        req.City,
			State:       req.State,
			Country:     req.Country,
			Founded:     req.Founded,
			LinkedIn:    req.LinkedIn,
		}
		orgDataJSON, _ := json.Marshal(orgData)

		orgKey, err := lib.DeriveProjectKey(h.Cfg.MasterKey, orgID)
		if err != nil {
			ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
			return
		}
		orgSummary, _ := lib.Pack(orgKey, req.Name)
		orgDataPacked, _ := lib.Pack(orgKey, string(orgDataJSON))

		_, dbErr := h.DB.Conn.Exec(
			`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
			  search_key, search_key2, summary, data, stage,
			  assignee_id, return_to_id, origin_id,
			  version, deleted_at, deleted_by, key_version,
			  created_at, updated_at, created_by)
			 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
			orgID, orgID, "", lib.TypeOrganization, 0, 0,
			nil, nil, orgSummary, orgDataPacked, lib.StageDataroom,
			"", "", "",
			1, nil, nil, 1,
			now, now, actorID,
		)
		if dbErr != nil {
			ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to create organization")
			return
		}
	}

	// Step 2: Create deal_org entry linking org to project
	dealOrgID := uuid.New().String()
	dealOrgData := lib.DealOrgData{
		OrgID:      orgID,
		Role:       req.Role,
		DomainLock: req.DomainLock,
		Members:    req.Members,
	}
	dealOrgJSON, _ := json.Marshal(dealOrgData)

	projKey, err := lib.DeriveProjectKey(h.Cfg.MasterKey, projectID)
	if err != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Key derivation failed")
		return
	}
	dealSummary, _ := lib.Pack(projKey, req.Name)
	dealDataPacked, _ := lib.Pack(projKey, string(dealOrgJSON))

	_, dbErr2 := h.DB.Conn.Exec(
		`INSERT INTO entries (entry_id, project_id, parent_id, type, depth, sort_order,
		  search_key, search_key2, summary, data, stage,
		  assignee_id, return_to_id, origin_id,
		  version, deleted_at, deleted_by, key_version,
		  created_at, updated_at, created_by)
		 VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?, ?,?,?,?, ?,?,?)`,
		dealOrgID, projectID, projectID, lib.TypeDealOrg, 1, 0,
		nil, nil, dealSummary, dealDataPacked, lib.StagePreDataroom,
		"", "", "",
		1, nil, nil, 1,
		now, now, actorID,
	)
	if dbErr2 != nil {
		ErrorResponse(w, http.StatusInternalServerError, "internal", "Failed to add organization to deal")
		return
	}

	JSONResponse(w, http.StatusCreated, map[string]any{
		"org_id":      orgID,
		"deal_org_id": dealOrgID,
		"name":        req.Name,
		"role":        req.Role,
		"members":     len(req.Members),
	})
}

// ScrapeOrg handles POST /api/scrape/org — takes an email, scrapes the domain for org + people data.
func (h *Handlers) ScrapeOrg(w http.ResponseWriter, r *http.Request) {
	var req struct {
		Email string `json:"email"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		ErrorResponse(w, http.StatusBadRequest, "invalid_json", "Invalid request body")
		return
	}
	req.Email = strings.TrimSpace(strings.ToLower(req.Email))
	if req.Email == "" || !strings.Contains(req.Email, "@") {
		ErrorResponse(w, http.StatusBadRequest, "missing_fields", "Valid email required")
		return
	}

	// Extract domain from email
	domain := strings.SplitN(req.Email, "@", 2)[1]

	// Check if we already have an org with this domain
	existingOrgs, _ := h.queryOrgsByType("")
	for _, org := range existingOrgs {
		orgMap := h.orgToMap(&org)
		if domains, ok := orgMap["domains"].([]string); ok {
			for _, d := range domains {
				if strings.EqualFold(d, domain) {
					// Return existing org data in scrape format
					existing := &lib.ScrapedOrg{
						Name:        fmt.Sprintf("%v", orgMap["name"]),
						Domain:      domain,
						Logo:        fmt.Sprintf("%v", orgMap["logo"]),
						Description: fmt.Sprintf("%v", orgMap["description"]),
						Industry:    fmt.Sprintf("%v", orgMap["industry"]),
						Website:     fmt.Sprintf("%v", orgMap["website"]),
					}
					// Get members from deal_orgs that reference this org
					rows, err := h.DB.Conn.Query(
						`SELECT data, project_id FROM entries WHERE type = 'deal_org' AND deleted_at IS NULL`,
					)
					if err == nil {
						defer rows.Close()
						for rows.Next() {
							var data []byte
							var pid string
							if rows.Scan(&data, &pid) != nil || len(data) == 0 {
								continue
							}
							projKey, _ := lib.DeriveProjectKey(h.Cfg.MasterKey, pid)
							dataText, err := lib.Unpack(projKey, data)
							if err != nil {
								continue
							}
							var dod lib.DealOrgData
							if json.Unmarshal([]byte(dataText), &dod) == nil && dod.OrgID == org.EntryID {
								for _, m := range dod.Members {
									existing.People = append(existing.People, lib.ScrapedPerson{
										Name:     m.Name,
										Email:    m.Email,
										Title:    m.Title,
										Phone:    m.Phone,
										Photo:    m.Photo,
										Bio:      m.Bio,
										LinkedIn: m.LinkedIn,
									})
								}
								break
							}
						}
					}
					log.Printf("returning existing org %s for domain %s", org.EntryID, domain)
					JSONResponse(w, http.StatusOK, existing)
					return
				}
			}
		}
	}

	if h.Cfg.OpenRouterKey == "" {
		ErrorResponse(w, http.StatusServiceUnavailable, "not_configured", "LLM not configured")
		return
	}

	result, err := lib.ScrapeOrgByEmail(h.Cfg.OpenRouterKey, req.Email)
	if err != nil {
		log.Printf("scrape org error for %s: %v", req.Email, err)
		msg := "Could not scrape organization website"
		if strings.Contains(err.Error(), "context length") || strings.Contains(err.Error(), "too many tokens") {
			msg = "Website is too large to analyze automatically"
		}
		ErrorResponse(w, http.StatusUnprocessableEntity, "scrape_failed", msg)
		return
	}

	JSONResponse(w, http.StatusOK, result)
}
