package services

// DashboardService aggregates the logged-in landing-page payload. Scoped to
// Projects the caller can see — same predicate as ProjectService (team-based,
// v2 data model, t-paliad-024).

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"

	"mgit.msbls.de/m/paliad/internal/models"
)

// DashboardService reads paliad.projects/deadlines/appointments/project_events for
// the Dashboard page.
type DashboardService struct {
	db        *sqlx.DB
	users     *UserService
	approvals *ApprovalService
}

func NewDashboardService(db *sqlx.DB, users *UserService) *DashboardService {
	return &DashboardService{db: db, users: users}
}

// SetApprovalService wires the inbox-approvals widget data source. Called
// post-construction so that DashboardService and ApprovalService can be
// stitched together at boot without a circular constructor dependency.
// Safe to leave nil — InboxSummary will then carry pending_count=0 and an
// empty entries list, and the widget renders its empty state.
func (s *DashboardService) SetApprovalService(a *ApprovalService) {
	s.approvals = a
}

// DashboardData is the full payload returned to the frontend.
type DashboardData struct {
	User                 *DashboardUser        `json:"user"`
	DeadlineSummary      DeadlineSummary       `json:"deadline_summary"`
	AppointmentSummary   AppointmentSummary    `json:"appointment_summary"`
	MatterSummary        MatterSummary         `json:"matter_summary"`
	UpcomingDeadlines    []UpcomingDeadline    `json:"upcoming_deadlines"`
	UpcomingAppointments []UpcomingAppointment `json:"upcoming_appointments"`
	RecentActivity       []ActivityEntry       `json:"recent_activity"`
	InboxSummary         InboxSummary          `json:"inbox_summary"`
}

// InboxSummary feeds the inbox-approvals widget on the configurable
// dashboard (t-paliad-219). PendingCount is the precise number of
// approval requests that await this user's approval; Top is a small
// preview list (up to InboxTopCap entries) ordered oldest-pending-first
// so the most urgent appears first.
//
// When the ApprovalService dependency is unwired (knowledge-platform-only
// deployments, tests), PendingCount=0 and Top=[] so the widget renders
// its empty state. The data path is read-only — no writes go through
// the dashboard payload.
type InboxSummary struct {
	PendingCount int          `json:"pending_count"`
	Top          []InboxEntry `json:"top"`
}

// InboxEntry is a single row in InboxSummary.Top — the minimum needed
// to render a clickable preview ("Frist X auf Akte Y, vorgeschlagen am Z").
type InboxEntry struct {
	RequestID    uuid.UUID `json:"id"`
	EntityType   string    `json:"entity_type"`
	EntityTitle  *string   `json:"entity_title,omitempty"`
	ProjectID    uuid.UUID `json:"project_id"`
	ProjectTitle string    `json:"project_title"`
	RequestedAt  time.Time `json:"requested_at"`
	RequesterID  uuid.UUID `json:"requester_id"`
	RequesterName string   `json:"requester_name"`
}

// InboxTopCap caps the preview list. The widget's count setting tops out
// at 10 (see WidgetCatalog inboxCounts); we fetch the cap once and let
// the client trim further per the user's setting.
const InboxTopCap = 10

type DashboardUser struct {
	ID          uuid.UUID `json:"id"`
	Email       string    `json:"email"`
	DisplayName string    `json:"display_name"`
	Office      string    `json:"office"`
	JobTitle    *string   `json:"job_title"`
	GlobalRole  string    `json:"global_role"`
}

// DeadlineSummary feeds the Dashboard "Fristen auf einen Blick" cards.
//
// Bucket math is identical to DeadlineService.SummaryCounts (single source
// of truth via computeDeadlineBucketBounds) so the Dashboard and the
// /deadlines summary cards always show the same numbers (t-paliad-106 →
// t-paliad-110). The Erledigt card is gone; status=completed stays
// reachable via the dropdown filter on the EventsPage but is no longer
// rendered as a card on the dashboard rail.
type DeadlineSummary struct {
	Overdue  int `json:"overdue"   db:"overdue"`
	Today    int `json:"today"     db:"today"`
	ThisWeek int `json:"this_week" db:"this_week"`
	NextWeek int `json:"next_week" db:"next_week"`
	Later    int `json:"later"     db:"later"`
}

// AppointmentSummary feeds the Dashboard "Termine auf einen Blick" rail
// (t-paliad-110). Three cards: Heute / Diese Woche / Später. No Überfällig
// (past appointments aren't urgent — they happened) and no Nächste Woche
// (low-value distinction for appointments per the design doc).
type AppointmentSummary struct {
	Today    int `json:"today"     db:"today"`
	ThisWeek int `json:"this_week" db:"this_week"`
	Later    int `json:"later"     db:"later"`
}

// MatterSummary counts visible Projects by status. Field names kept as
// "matter" for JSON API compatibility with the dashboard client.
type MatterSummary struct {
	Active   int `json:"active"   db:"active"`
	Archived int `json:"archived" db:"archived"`
	Total    int `json:"total"    db:"total"`
}

// UpcomingDeadline is one row for "Kommende Deadlines".
type UpcomingDeadline struct {
	ID           uuid.UUID `json:"id"           db:"id"`
	Title        string    `json:"title"        db:"title"`
	DueDate      string    `json:"due_date"     db:"due_date"`
	ProjectID    uuid.UUID `json:"project_id"   db:"project_id"`
	ProjectTitle string    `json:"project_title" db:"project_title"`
	ProjectRef   string    `json:"project_reference"  db:"project_reference"`
	Urgency      string    `json:"urgency"`
}

// UpcomingAppointment is one row for "Kommende Appointments".
type UpcomingAppointment struct {
	ID           uuid.UUID  `json:"id"            db:"id"`
	Title        string     `json:"title"         db:"title"`
	StartAt      time.Time  `json:"start_at"      db:"start_at"`
	EndAt        *time.Time `json:"end_at"        db:"end_at"`
	Type         *string    `json:"type"          db:"appointment_type"`
	ProjectID    *uuid.UUID `json:"project_id"    db:"project_id"`
	ProjectTitle *string    `json:"project_title" db:"project_title"`
	ProjectRef   *string    `json:"project_reference"   db:"project_reference"`
}

// ActivityEntry is one row in the "Letzte Aktivität" feed.
type ActivityEntry struct {
	Timestamp    time.Time       `json:"timestamp"         db:"timestamp"`
	ActorEmail   *string         `json:"actor_email"       db:"actor_email"`
	ActorName    *string         `json:"actor_name"        db:"actor_name"`
	ProjectID    uuid.UUID       `json:"project_id"        db:"project_id"`
	ProjectTitle string          `json:"project_title"     db:"project_title"`
	ProjectRef   string          `json:"project_reference" db:"project_reference"`
	Action       *string         `json:"action"            db:"action"`
	Details      string          `json:"details"           db:"details"`
	Description  *string         `json:"description"       db:"description"`
	Metadata     json.RawMessage `json:"metadata"          db:"metadata"`
}

// Get builds the full dashboard payload.
func (s *DashboardService) Get(ctx context.Context, userID uuid.UUID) (*DashboardData, error) {
	user, err := s.users.GetByID(ctx, userID)
	if err != nil {
		return nil, err
	}

	data := &DashboardData{
		UpcomingDeadlines:    []UpcomingDeadline{},
		UpcomingAppointments: []UpcomingAppointment{},
		RecentActivity:       []ActivityEntry{},
	}
	if user == nil {
		return data, nil
	}
	data.User = &DashboardUser{
		ID:          user.ID,
		Email:       user.Email,
		DisplayName: user.DisplayName,
		Office:      user.Office,
		JobTitle:    user.JobTitle,
		GlobalRole:  user.GlobalRole,
	}

	now := time.Now()
	today := now.Format("2006-01-02")
	// t-paliad-219 §18 Note B: widen the upcoming windows from 7d → 60d
	// so the per-widget horizon dropdown (7/14/30/60) can filter client-
	// side without re-querying. LIMIT bumps from 10 to 40 for the same
	// reason — the widget's count setting tops out at 20 plus headroom
	// for the agenda widget which can read from the same payload.
	endOfWindow := now.AddDate(0, 0, 60).Format("2006-01-02")
	bounds := computeDeadlineBucketBounds(now.UTC())

	if err := s.loadSummary(ctx, data, user, bounds); err != nil {
		return nil, err
	}
	if err := s.loadUpcomingDeadlines(ctx, data, user, today, endOfWindow); err != nil {
		return nil, err
	}
	if err := s.loadUpcomingAppointments(ctx, data, user, now); err != nil {
		return nil, err
	}
	if err := s.loadRecentActivity(ctx, data, user); err != nil {
		return nil, err
	}
	if err := s.loadInboxSummary(ctx, data, user); err != nil {
		return nil, err
	}

	annotateUrgency(data.UpcomingDeadlines, now)
	return data, nil
}

// loadSummary fills DeadlineSummary + AppointmentSummary + MatterSummary.
//
// Bucket math comes from computeDeadlineBucketBounds (deadline_service.go) so
// the buckets stay in lockstep with /api/deadlines/summary and the unified
// /api/events/summary used by the EventsPage (t-paliad-110).
//
// Bucket model on the Dashboard:
//   - Fristen rail: Überfällig (conditional alarm) · Heute · Diese Woche ·
//     Nächste Woche · Später. Erledigt is no longer rendered as a card —
//     status=completed stays reachable via the EventsPage filter dropdown.
//   - Termine rail: Heute · Diese Woche · Später. No Überfällig, no
//     Nächste Woche (low-value distinction for appointments).
//
// Visibility predicate: see internal/services/visibility.go — global_admin
// shortcut OR any ancestor-or-direct team membership. Applied once via a CTE;
// downstream queries reuse the same helper.
func (s *DashboardService) loadSummary(ctx context.Context, data *DashboardData, user *models.User, bounds deadlineBucketBounds) error {
	query := `
WITH visible_projekte AS (
    SELECT p.id, p.status
      FROM paliad.projects p
     WHERE ` + visibilityPredicatePositional("p", 1) + `
),
deadline_stats AS (
    SELECT
        COUNT(*) FILTER (WHERE f.status = 'pending'  AND f.due_date <  $2::date)                                                    AS overdue,
        COUNT(*) FILTER (WHERE f.status = 'pending'  AND f.due_date =  $2::date)                                                    AS today,
        COUNT(*) FILTER (WHERE f.status = 'pending'  AND f.due_date >= $3::date AND f.due_date <  $4::date)                         AS this_week,
        COUNT(*) FILTER (WHERE f.status = 'pending'  AND f.due_date >= $4::date AND f.due_date <  $5::date)                         AS next_week,
        COUNT(*) FILTER (WHERE f.status = 'pending'  AND f.due_date >= $5::date)                                                    AS later
      FROM paliad.deadlines f
      JOIN visible_projekte v ON v.id = f.project_id
),
appointment_stats AS (
    SELECT
        COUNT(*) FILTER (WHERE t.start_at >= $2       AND t.start_at <  $3)        AS today,
        COUNT(*) FILTER (WHERE t.start_at >= $3       AND t.start_at <  $4)        AS this_week,
        COUNT(*) FILTER (WHERE t.start_at >= $5)                                   AS later
      FROM paliad.appointments t
      LEFT JOIN visible_projekte v ON v.id = t.project_id
     WHERE (t.project_id IS NULL AND t.created_by = $1)
        OR (t.project_id IS NOT NULL AND v.id IS NOT NULL)
),
matter_stats AS (
    SELECT
        COUNT(*) FILTER (WHERE status = 'active')   AS active,
        COUNT(*) FILTER (WHERE status = 'archived') AS archived,
        COUNT(*)                                    AS total
      FROM visible_projekte
)
SELECT ds.overdue, ds.today, ds.this_week, ds.next_week, ds.later,
       aps.today AS appt_today, aps.this_week AS appt_this_week, aps.later AS appt_later,
       ms.active, ms.archived, ms.total
  FROM deadline_stats ds, appointment_stats aps, matter_stats ms`

	var row struct {
		DeadlineSummary
		ApptToday    int `db:"appt_today"`
		ApptThisWeek int `db:"appt_this_week"`
		ApptLater    int `db:"appt_later"`
		MatterSummary
	}
	err := s.db.GetContext(ctx, &row, query,
		user.ID, bounds.today, bounds.tomorrow, bounds.nextMonday, bounds.weekAfter)
	if errors.Is(err, sql.ErrNoRows) {
		return nil
	}
	if err != nil {
		return fmt.Errorf("dashboard summary: %w", err)
	}
	data.DeadlineSummary = row.DeadlineSummary
	data.AppointmentSummary = AppointmentSummary{
		Today:    row.ApptToday,
		ThisWeek: row.ApptThisWeek,
		Later:    row.ApptLater,
	}
	data.MatterSummary = row.MatterSummary
	return nil
}

func (s *DashboardService) loadUpcomingDeadlines(ctx context.Context, data *DashboardData, user *models.User, today, endOfWeek string) error {
	query := `
SELECT f.id,
       f.title,
       to_char(f.due_date, 'YYYY-MM-DD')                 AS due_date,
       p.id                                              AS project_id,
       p.title                                           AS project_title,
       COALESCE(p.reference, '')                         AS project_reference
  FROM paliad.deadlines f
  JOIN paliad.projects p ON p.id = f.project_id
 WHERE f.status = 'pending'
   AND f.due_date >= $2::date
   AND f.due_date <= $3::date
   AND ` + visibilityPredicatePositional("p", 1) + `
 ORDER BY f.due_date ASC
 LIMIT 40`
	if err := s.db.SelectContext(ctx, &data.UpcomingDeadlines, query,
		user.ID, today, endOfWeek); err != nil {
		return fmt.Errorf("dashboard upcoming deadlines: %w", err)
	}
	return nil
}

// loadInboxSummary populates DashboardData.InboxSummary — the open-
// approval count + top InboxTopCap entries for the inbox-approvals
// widget (t-paliad-219). When ApprovalService is unwired (knowledge-
// platform-only deployments, tests), the function is a no-op and the
// widget renders its empty state.
func (s *DashboardService) loadInboxSummary(ctx context.Context, data *DashboardData, user *models.User) error {
	data.InboxSummary = InboxSummary{Top: []InboxEntry{}}
	if s.approvals == nil {
		return nil
	}
	cnt, err := s.approvals.PendingCountForUser(ctx, user.ID)
	if err != nil {
		return fmt.Errorf("dashboard inbox count: %w", err)
	}
	data.InboxSummary.PendingCount = cnt
	if cnt == 0 {
		return nil
	}
	rows, err := s.approvals.ListPendingForApprover(ctx, user.ID, InboxFilter{Limit: InboxTopCap})
	if err != nil {
		return fmt.Errorf("dashboard inbox top: %w", err)
	}
	top := make([]InboxEntry, 0, len(rows))
	for _, r := range rows {
		top = append(top, InboxEntry{
			RequestID:     r.ID,
			EntityType:    r.EntityType,
			EntityTitle:   r.EntityTitle,
			ProjectID:     r.ProjectID,
			ProjectTitle:  r.ProjectTitle,
			RequestedAt:   r.RequestedAt,
			RequesterID:   r.RequestedBy,
			RequesterName: r.RequesterName,
		})
	}
	data.InboxSummary.Top = top
	return nil
}

func (s *DashboardService) loadUpcomingAppointments(ctx context.Context, data *DashboardData, user *models.User, now time.Time) error {
	query := `
SELECT t.id,
       t.title,
       t.start_at,
       t.end_at,
       t.appointment_type,
       t.project_id,
       p.title                    AS project_title,
       COALESCE(p.reference, NULL) AS project_reference
  FROM paliad.appointments t
  LEFT JOIN paliad.projects p ON p.id = t.project_id
 WHERE t.start_at >= $2
   AND t.start_at <  ($2 + interval '60 days')
   AND (
        (t.project_id IS NULL AND t.created_by = $1)
        OR (t.project_id IS NOT NULL AND ` + visibilityPredicatePositional("p", 1) + `)
       )
 ORDER BY t.start_at ASC
 LIMIT 40`
	if err := s.db.SelectContext(ctx, &data.UpcomingAppointments, query,
		user.ID, now); err != nil {
		return fmt.Errorf("dashboard upcoming appointments: %w", err)
	}
	return nil
}

func (s *DashboardService) loadRecentActivity(ctx context.Context, data *DashboardData, user *models.User) error {
	query := `
SELECT COALESCE(e.event_date, e.created_at) AS timestamp,
       u.email                              AS actor_email,
       u.display_name                       AS actor_name,
       e.project_id,
       p.title                              AS project_title,
       COALESCE(p.reference, '')            AS project_reference,
       e.event_type                         AS action,
       e.title                              AS details,
       e.description,
       e.metadata                           AS metadata
  FROM paliad.project_events e
  JOIN paliad.projects p ON p.id = e.project_id
  LEFT JOIN paliad.users u ON u.id = e.created_by
 WHERE ` + visibilityPredicatePositional("p", 1) + `
 ORDER BY COALESCE(e.event_date, e.created_at) DESC
 LIMIT 10`
	if err := s.db.SelectContext(ctx, &data.RecentActivity, query, user.ID); err != nil {
		return fmt.Errorf("dashboard recent activity: %w", err)
	}
	return nil
}

func annotateUrgency(deadlines []UpcomingDeadline, now time.Time) {
	today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	for i := range deadlines {
		due, err := time.ParseInLocation("2006-01-02", deadlines[i].DueDate, now.Location())
		if err != nil {
			deadlines[i].Urgency = "soon"
			continue
		}
		days := int(due.Sub(today).Hours() / 24)
		switch {
		case days < 0:
			deadlines[i].Urgency = "overdue"
		case days == 0:
			deadlines[i].Urgency = "today"
		case days <= 2:
			deadlines[i].Urgency = "urgent"
		default:
			deadlines[i].Urgency = "soon"
		}
	}
}