package api

import (
	"context"
	"database/sql"
	"fmt"
	"net/http"
	"strconv"
	"strings"

	"github.com/gin-gonic/gin"

	"github.com/rycroftapparel/workpulse-api/internal/httpapi"
)

// countActiveOrgDivisions returns how many active master rows exist (used for report validation and analytics mode).
func (s *Server) countActiveOrgDivisions(ctx context.Context) (int, error) {
	var n int
	err := s.DB.QueryRowContext(ctx, `SELECT COUNT(*) FROM org_divisions WHERE is_active = true`).Scan(&n)
	return n, err
}

// normalizeReportDivision trims input; empty means NULL in DB. When at least one active master exists,
// non-empty division must match an active row's code (case-insensitive); returns canonical code from DB.
func (s *Server) normalizeReportDivision(ctx context.Context, div string) (string, error) {
	d := strings.TrimSpace(div)
	if d == "" {
		return "", nil
	}
	n, err := s.countActiveOrgDivisions(ctx)
	if err != nil {
		return "", err
	}
	if n == 0 {
		return d, nil
	}
	var code string
	err = s.DB.QueryRowContext(ctx,
		`SELECT code FROM org_divisions WHERE is_active = true AND LOWER(TRIM(code)) = LOWER(TRIM($1)) LIMIT 1`,
		d,
	).Scan(&code)
	if err == sql.ErrNoRows {
		return "", fmt.Errorf("division must match an active org division code")
	}
	if err != nil {
		return "", err
	}
	return code, nil
}

func roleIsSuperadmin(c *gin.Context) bool {
	v, ok := c.Get("role")
	if !ok {
		return false
	}
	r, ok := v.(string)
	return ok && r == "superadmin"
}

// normalizeOrgRole trims and defaults empty to "staff"; max length 64.
func normalizeOrgRole(s string) (string, error) {
	r := strings.TrimSpace(s)
	if r == "" {
		return "staff", nil
	}
	if len(r) > 64 {
		return "", fmt.Errorf("orgRole must be at most 64 characters")
	}
	return r, nil
}

func (s *Server) listOrgDivisions(c *gin.Context) {
	ctx, cancel := s.ctx(c)
	defer cancel()

	limit, _ := strconv.Atoi(c.DefaultQuery("limit", "200"))
	if limit < 1 {
		limit = 1
	}
	if limit > 500 {
		limit = 500
	}
	offset, _ := strconv.Atoi(c.DefaultQuery("offset", "0"))
	if offset < 0 {
		offset = 0
	}

	activeQ := strings.ToLower(strings.TrimSpace(c.Query("active")))
	super := roleIsSuperadmin(c)

	var where string
	switch {
	case !super:
		where = `WHERE is_active = true`
	case activeQ == "" || activeQ == "1" || activeQ == "true":
		where = `WHERE is_active = true`
	case activeQ == "0" || activeQ == "false":
		where = `WHERE is_active = false`
	case activeQ == "all":
		where = ``
	default:
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "active must be 1, 0, or all"))
		return
	}

	countSQL := `SELECT COUNT(*) FROM org_divisions ` + where
	var total int
	if err := s.DB.QueryRowContext(ctx, countSQL).Scan(&total); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	listSQL := `SELECT id, code, name, description, sort_order, is_active, created_at, updated_at FROM org_divisions ` + where +
		` ORDER BY sort_order ASC, name ASC LIMIT $1 OFFSET $2`
	listArgs := []interface{}{limit, offset}

	rows, err := s.DB.QueryContext(ctx, listSQL, listArgs...)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rows.Close()

	var list []gin.H
	for rows.Next() {
		var id uint64
		var code, name string
		var description sql.NullString
		var sortOrder int
		var isActive bool
		var createdAt, updatedAt interface{}
		if err := rows.Scan(&id, &code, &name, &description, &sortOrder, &isActive, &createdAt, &updatedAt); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		h := gin.H{
			"id": id, "code": code, "name": name, "sortOrder": sortOrder, "isActive": isActive,
			"createdAt": createdAt, "updatedAt": updatedAt,
		}
		if description.Valid {
			h["description"] = description.String
		}
		list = append(list, h)
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"divisions": list, "total": total}))
}

type orgDivisionCreateBody struct {
	Code        string  `json:"code" binding:"required"`
	Name        string  `json:"name" binding:"required"`
	Description *string `json:"description"`
	SortOrder   *int    `json:"sortOrder"`
}

func (s *Server) createOrgDivision(c *gin.Context) {
	var body orgDivisionCreateBody
	if err := c.ShouldBindJSON(&body); err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
		return
	}
	code := strings.TrimSpace(body.Code)
	name := strings.TrimSpace(body.Name)
	if code == "" || name == "" {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "code and name are required"))
		return
	}
	sortOrder := 0
	if body.SortOrder != nil {
		sortOrder = *body.SortOrder
	}
	var desc interface{}
	if body.Description != nil {
		d := strings.TrimSpace(*body.Description)
		if d != "" {
			desc = d
		} else {
			desc = nil
		}
	} else {
		desc = nil
	}

	ctx, cancel := s.ctx(c)
	defer cancel()

	var id uint64
	err := s.DB.QueryRowContext(ctx,
		`INSERT INTO org_divisions (code, name, description, sort_order) VALUES ($1, $2, $3, $4) RETURNING id`,
		code, name, desc, sortOrder,
	).Scan(&id)
	if err != nil {
		if strings.Contains(strings.ToLower(err.Error()), "unique") || strings.Contains(err.Error(), "idx_org_divisions_code_ci") {
			c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "division code already exists"))
			return
		}
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusCreated, httpapi.OK(gin.H{"id": id}))
}

type orgDivisionPatchBody struct {
	Name        *string `json:"name"`
	Description *string `json:"description"`
	SortOrder   *int    `json:"sortOrder"`
	IsActive    *bool   `json:"isActive"`
}

func (s *Server) patchOrgDivision(c *gin.Context) {
	id, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid id"))
		return
	}
	var body orgDivisionPatchBody
	if err := c.ShouldBindJSON(&body); err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
		return
	}
	if body.Name == nil && body.Description == nil && body.SortOrder == nil && body.IsActive == nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "no fields to update"))
		return
	}

	ctx, cancel := s.ctx(c)
	defer cancel()

	var n int64
	err = s.DB.QueryRowContext(ctx, `SELECT COUNT(*) FROM org_divisions WHERE id = $1`, id).Scan(&n)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if n == 0 {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}

	if body.Name != nil {
		name := strings.TrimSpace(*body.Name)
		if name == "" {
			c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "name cannot be empty"))
			return
		}
		_, err = s.DB.ExecContext(ctx, `UPDATE org_divisions SET name = $1 WHERE id = $2`, name, id)
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}
	if body.Description != nil {
		d := strings.TrimSpace(*body.Description)
		var v interface{}
		if d == "" {
			v = nil
		} else {
			v = d
		}
		_, err = s.DB.ExecContext(ctx, `UPDATE org_divisions SET description = $1 WHERE id = $2`, v, id)
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}
	if body.SortOrder != nil {
		_, err = s.DB.ExecContext(ctx, `UPDATE org_divisions SET sort_order = $1 WHERE id = $2`, *body.SortOrder, id)
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}
	if body.IsActive != nil {
		_, err = s.DB.ExecContext(ctx, `UPDATE org_divisions SET is_active = $1 WHERE id = $2`, *body.IsActive, id)
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"id": id}))
}

func (s *Server) deleteOrgDivision(c *gin.Context) {
	id, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid id"))
		return
	}
	ctx, cancel := s.ctx(c)
	defer cancel()
	res, err := s.DB.ExecContext(ctx, `UPDATE org_divisions SET is_active = false WHERE id = $1`, id)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"id": id}))
}

// --- Division members (org_division_members + org_role; superadmin only) ---

func (s *Server) orgDivisionRow(ctx context.Context, id uint64) (found, isActive bool, err error) {
	err = s.DB.QueryRowContext(ctx, `SELECT is_active FROM org_divisions WHERE id = $1`, id).Scan(&isActive)
	if err == sql.ErrNoRows {
		return false, false, nil
	}
	if err != nil {
		return false, false, err
	}
	return true, isActive, nil
}

func (s *Server) listOrgDivisionMembers(c *gin.Context) {
	divID, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid division id"))
		return
	}
	ctx, cancel := s.ctx(c)
	defer cancel()
	found, _, err := s.orgDivisionRow(ctx, divID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !found {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}
	rows, err := s.DB.QueryContext(ctx,
		`SELECT u.id, u.name, u.email, u.is_active, m.org_role
		 FROM org_division_members m
		 INNER JOIN users u ON u.id = m.user_id
		 WHERE m.division_id = $1
		 ORDER BY u.name ASC, u.id ASC`,
		divID,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rows.Close()
	var members []gin.H
	for rows.Next() {
		var uid uint64
		var name, email, orgRole string
		var isActive bool
		if err := rows.Scan(&uid, &name, &email, &isActive, &orgRole); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		members = append(members, gin.H{
			"userId": uid, "name": name, "email": email, "isActive": isActive, "orgRole": orgRole,
		})
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"divisionId": divID, "members": members}))
}

type orgDivisionPutMemberEntry struct {
	UserID  uint64 `json:"userId" binding:"required"`
	OrgRole string `json:"orgRole"`
}

type orgDivisionPutMembersBody struct {
	Members []orgDivisionPutMemberEntry `json:"members"`
	UserIDs []uint64                    `json:"userIds"`
}

func dedupeUint64(ids []uint64) []uint64 {
	seen := make(map[uint64]struct{}, len(ids))
	var out []uint64
	for _, id := range ids {
		if id == 0 {
			continue
		}
		if _, ok := seen[id]; ok {
			continue
		}
		seen[id] = struct{}{}
		out = append(out, id)
	}
	return out
}

func (s *Server) putOrgDivisionMembers(c *gin.Context) {
	divID, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid division id"))
		return
	}
	var body orgDivisionPutMembersBody
	if err := c.ShouldBindJSON(&body); err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
		return
	}

	roleByUser := make(map[uint64]string)
	for _, m := range body.Members {
		if m.UserID == 0 {
			continue
		}
		role, err := normalizeOrgRole(m.OrgRole)
		if err != nil {
			c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
			return
		}
		roleByUser[m.UserID] = role
	}
	for _, uid := range dedupeUint64(body.UserIDs) {
		if _, ok := roleByUser[uid]; !ok {
			roleByUser[uid] = "staff"
		}
	}

	ctx, cancel := s.ctx(c)
	defer cancel()
	found, active, err := s.orgDivisionRow(ctx, divID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !found {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}
	if !active {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "cannot assign members to an inactive division"))
		return
	}

	tx, err := s.DB.BeginTx(ctx, nil)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer func() { _ = tx.Rollback() }()

	for uid := range roleByUser {
		var one int
		err := tx.QueryRowContext(ctx, `SELECT 1 FROM users WHERE id = $1`, uid).Scan(&one)
		if err == sql.ErrNoRows {
			c.JSON(http.StatusBadRequest, httpapi.Fail("validation", fmt.Sprintf("user id %d not found", uid)))
			return
		}
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}

	if _, err := tx.ExecContext(ctx, `DELETE FROM org_division_members WHERE division_id = $1`, divID); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	for uid, role := range roleByUser {
		_, err := tx.ExecContext(ctx,
			`INSERT INTO org_division_members (division_id, user_id, org_role) VALUES ($1, $2, $3)`,
			divID, uid, role,
		)
		if err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
	}
	if err := tx.Commit(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"divisionId": divID, "memberCount": len(roleByUser)}))
}

type orgDivisionPostMemberBody struct {
	UserID  uint64 `json:"userId" binding:"required"`
	OrgRole string `json:"orgRole"`
}

func (s *Server) postOrgDivisionMember(c *gin.Context) {
	divID, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid division id"))
		return
	}
	var body orgDivisionPostMemberBody
	if err := c.ShouldBindJSON(&body); err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
		return
	}
	if body.UserID == 0 {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid userId"))
		return
	}
	role, err := normalizeOrgRole(body.OrgRole)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", err.Error()))
		return
	}
	ctx, cancel := s.ctx(c)
	defer cancel()
	found, active, err := s.orgDivisionRow(ctx, divID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !found {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}
	if !active {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "cannot assign members to an inactive division"))
		return
	}
	var exists int
	err = s.DB.QueryRowContext(ctx, `SELECT 1 FROM users WHERE id = $1`, body.UserID).Scan(&exists)
	if err == sql.ErrNoRows {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "user not found"))
		return
	}
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	_, err = s.DB.ExecContext(ctx,
		`INSERT INTO org_division_members (division_id, user_id, org_role) VALUES ($1, $2, $3)
		 ON CONFLICT (division_id, user_id) DO UPDATE SET org_role = EXCLUDED.org_role, updated_at = NOW()`,
		divID, body.UserID, role,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusCreated, httpapi.OK(gin.H{"divisionId": divID, "userId": body.UserID, "orgRole": role}))
}

func (s *Server) deleteOrgDivisionMember(c *gin.Context) {
	divID, err := strconv.ParseUint(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid division id"))
		return
	}
	userID, err := strconv.ParseUint(c.Param("userId"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid user id"))
		return
	}
	ctx, cancel := s.ctx(c)
	defer cancel()
	found, _, err := s.orgDivisionRow(ctx, divID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !found {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "division not found"))
		return
	}
	res, err := s.DB.ExecContext(ctx,
		`DELETE FROM org_division_members WHERE division_id = $1 AND user_id = $2`,
		divID, userID,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		c.JSON(http.StatusNotFound, httpapi.Fail("not_found", "user is not assigned to this division"))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{"divisionId": divID, "userId": userID}))
}