package admin

import (
	"context"
	"fmt"
	"strings"
	"time"
)

type AdminOverview struct {
	WindowHours             int64
	TotalWorkspaces         int64
	NewWorkspaces           int64
	ActiveWorkspaces        int64
	TotalUsers              int64
	VerifiedUsers           int64
	ActiveUsers             int64
	RecentLoginUsers        int64
	TotalAgents             int64
	ActiveAgents            int64
	TotalMachines           int64
	OnlineMachines          int64
	PendingWorkspaceInvites int64
	MessageCount            int64
	HumanMessageCount       int64
	AgentMessageCount       int64
}

type AdminWorkspaceSummary struct {
	ID                 string
	Slug               string
	Name               string
	OwnerUserID        string
	OwnerEmail         string
	OwnerDisplayName   string
	CreatedAt          time.Time
	HumanMemberCount   int64
	AgentCount         int64
	MachineCount       int64
	ChannelCount       int64
	PendingInviteCount int64
	MessageCount       int64
	LastMessageAt      *time.Time
	LastHumanMessageAt *time.Time
	LastAgentMessageAt *time.Time
}

type AdminUserSummary struct {
	ID                  string
	Email               string
	Name                string
	DisplayName         string
	IsAdmin             bool
	EmailVerified       bool
	CreatedAt           time.Time
	WorkspaceCount      int64
	OwnedWorkspaceCount int64
	LastLoginAt         *time.Time
	LastHumanMessageAt  *time.Time
}

func clampAdminLimit(limit int, fallback int) int {
	if limit <= 0 {
		return fallback
	}
	if limit > 200 {
		return 200
	}
	return limit
}

func (store *Store) LoadAdminOverview(ctx context.Context, userID string, since time.Time) (AdminOverview, error) {
	if err := store.requireSystemAdminUser(ctx, userID); err != nil {
		return AdminOverview{}, err
	}

	row := store.db.QueryRow(ctx, `
WITH login_users AS (
    SELECT DISTINCT user_id
    FROM user_sessions
    WHERE created_at >= $1
      AND revoked_at IS NULL
),
message_users AS (
    SELECT DISTINCT m.user_id
    FROM scope_messages msg
    JOIN members m ON m.id = msg.author_member_id
    WHERE msg.created_at >= $1
      AND m.type = 'human'
      AND m.user_id IS NOT NULL
),
active_users AS (
    SELECT user_id FROM login_users
    UNION
    SELECT user_id FROM message_users
),
active_workspaces AS (
    SELECT DISTINCT workspace_id
    FROM scope_messages
    WHERE created_at >= $1
),
agent_message_activity AS (
    SELECT DISTINCT a.id
    FROM scope_messages msg
    JOIN agents a ON a.member_id = msg.author_member_id
    WHERE msg.created_at >= $1
),
active_agents AS (
    SELECT id FROM agent_message_activity
),
recent_messages AS (
    SELECT
        COUNT(*) AS total_count,
        SUM(CASE WHEN author_type = 'human' THEN 1 ELSE 0 END) AS human_count,
        SUM(CASE WHEN author_type = 'agent' THEN 1 ELSE 0 END) AS agent_count
    FROM scope_messages
    WHERE created_at >= $1
)
SELECT
    (SELECT COUNT(*) FROM workspaces),
    (SELECT COUNT(*) FROM workspaces WHERE created_at >= $1),
    (SELECT COUNT(*) FROM active_workspaces),
    (SELECT COUNT(*) FROM users),
    (SELECT COUNT(*) FROM users WHERE email_verified_at IS NOT NULL),
    (SELECT COUNT(*) FROM active_users),
    (SELECT COUNT(*) FROM login_users),
    (SELECT COUNT(*) FROM agents),
    (SELECT COUNT(*) FROM active_agents),
    (SELECT COUNT(*) FROM machines),
    0,
    (SELECT COUNT(*) FROM workspace_invites WHERE accepted_at IS NULL AND revoked_at IS NULL AND expires_at > NOW()),
    COALESCE((SELECT total_count FROM recent_messages), 0),
    COALESCE((SELECT human_count FROM recent_messages), 0),
    COALESCE((SELECT agent_count FROM recent_messages), 0)
`, since)

	var overview AdminOverview
	overview.WindowHours = int64(time.Since(since).Hours())
	if err := row.Scan(
		&overview.TotalWorkspaces,
		&overview.NewWorkspaces,
		&overview.ActiveWorkspaces,
		&overview.TotalUsers,
		&overview.VerifiedUsers,
		&overview.ActiveUsers,
		&overview.RecentLoginUsers,
		&overview.TotalAgents,
		&overview.ActiveAgents,
		&overview.TotalMachines,
		&overview.OnlineMachines,
		&overview.PendingWorkspaceInvites,
		&overview.MessageCount,
		&overview.HumanMessageCount,
		&overview.AgentMessageCount,
	); err != nil {
		return AdminOverview{}, fmt.Errorf("load admin overview: %w", err)
	}
	return overview, nil
}

func (store *Store) ListAdminWorkspaces(ctx context.Context, userID string, query string, since time.Time, activeOnly bool, limit int) ([]AdminWorkspaceSummary, error) {
	if err := store.requireSystemAdminUser(ctx, userID); err != nil {
		return nil, err
	}

	normalizedQuery := "%" + strings.ToLower(strings.TrimSpace(query)) + "%"
	normalizedLimit := clampAdminLimit(limit, 100)
	rows, err := store.db.Query(ctx, `
WITH owner_members AS (
    SELECT workspace_id, user_id
    FROM members
    WHERE type = 'human'
      AND workspace_role = 'owner'
),
human_counts AS (
    SELECT workspace_id, COUNT(*) AS human_member_count
    FROM members
    WHERE type = 'human'
    GROUP BY workspace_id
),
agent_counts AS (
    SELECT workspace_id, COUNT(*) AS agent_count
    FROM agents
    GROUP BY workspace_id
),
machine_counts AS (
    SELECT workspace_id, COUNT(*) AS machine_count
    FROM machines
    GROUP BY workspace_id
),
channel_counts AS (
    SELECT s.workspace_id, COUNT(*) AS channel_count
    FROM scopes s
    JOIN scope_channel_profiles p ON p.scope_id = s.id
    WHERE s.type = 'channel'
      AND s.status = 'active'
      AND s.archived_at IS NULL
      AND p.subsystem = 'im'
    GROUP BY s.workspace_id
),
invite_counts AS (
    SELECT workspace_id, COUNT(*) AS pending_invite_count
    FROM workspace_invites
    WHERE accepted_at IS NULL
      AND revoked_at IS NULL
      AND expires_at > NOW()
    GROUP BY workspace_id
),
message_activity AS (
    SELECT
        workspace_id,
        COUNT(*) AS message_count,
        MAX(created_at) AS last_message_at,
        MAX(CASE WHEN author_type = 'human' THEN created_at ELSE NULL END) AS last_human_message_at,
        MAX(CASE WHEN author_type = 'agent' THEN created_at ELSE NULL END) AS last_agent_message_at
    FROM scope_messages
    WHERE created_at >= $1
    GROUP BY workspace_id
)
SELECT
    w.id,
    w.slug,
    w.name,
    COALESCE(owner_user.id, ''),
    COALESCE(owner_user.email, ''),
    COALESCE(NULLIF(owner_user.display_name, ''), owner_user.name, ''),
    w.created_at,
    COALESCE(hc.human_member_count, 0),
    COALESCE(ac.agent_count, 0),
    COALESCE(mc.machine_count, 0),
    COALESCE(cc.channel_count, 0),
    COALESCE(ic.pending_invite_count, 0),
    COALESCE(ma.message_count, 0),
    ma.last_message_at,
    ma.last_human_message_at,
    ma.last_agent_message_at
FROM workspaces w
LEFT JOIN owner_members om ON om.workspace_id = w.id
LEFT JOIN users owner_user ON owner_user.id = om.user_id
LEFT JOIN human_counts hc ON hc.workspace_id = w.id
LEFT JOIN agent_counts ac ON ac.workspace_id = w.id
LEFT JOIN machine_counts mc ON mc.workspace_id = w.id
LEFT JOIN channel_counts cc ON cc.workspace_id = w.id
LEFT JOIN invite_counts ic ON ic.workspace_id = w.id
LEFT JOIN message_activity ma ON ma.workspace_id = w.id
WHERE ($2 = '%%'
       OR LOWER(w.id) LIKE $2
       OR LOWER(w.slug) LIKE $2
       OR LOWER(w.name) LIKE $2
       OR LOWER(COALESCE(owner_user.email, '')) LIKE $2)
  AND ($3 = FALSE OR ma.workspace_id IS NOT NULL)
ORDER BY COALESCE(ma.last_message_at, w.created_at) DESC, w.id ASC
LIMIT $4
`, since, normalizedQuery, activeOnly, normalizedLimit)
	if err != nil {
		return nil, fmt.Errorf("list admin workspaces: %w", err)
	}
	defer rows.Close()

	workspaces := make([]AdminWorkspaceSummary, 0)
	for rows.Next() {
		var item AdminWorkspaceSummary
		if err := rows.Scan(
			&item.ID,
			&item.Slug,
			&item.Name,
			&item.OwnerUserID,
			&item.OwnerEmail,
			&item.OwnerDisplayName,
			&item.CreatedAt,
			&item.HumanMemberCount,
			&item.AgentCount,
			&item.MachineCount,
			&item.ChannelCount,
			&item.PendingInviteCount,
			&item.MessageCount,
			&item.LastMessageAt,
			&item.LastHumanMessageAt,
			&item.LastAgentMessageAt,
		); err != nil {
			return nil, fmt.Errorf("scan admin workspace: %w", err)
		}
		workspaces = append(workspaces, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate admin workspaces: %w", err)
	}
	return workspaces, nil
}

func (store *Store) ListAdminUsers(ctx context.Context, userID string, query string, since time.Time, activeOnly bool, limit int) ([]AdminUserSummary, error) {
	if err := store.requireSystemAdminUser(ctx, userID); err != nil {
		return nil, err
	}

	normalizedQuery := "%" + strings.ToLower(strings.TrimSpace(query)) + "%"
	normalizedLimit := clampAdminLimit(limit, 100)
	rows, err := store.db.Query(ctx, `
WITH workspace_counts AS (
    SELECT user_id, COUNT(*) AS workspace_count
    FROM members
    WHERE type = 'human'
      AND user_id IS NOT NULL
    GROUP BY user_id
),
owned_workspace_counts AS (
    SELECT user_id, COUNT(*) AS owned_workspace_count
    FROM members
    WHERE type = 'human'
      AND workspace_role = 'owner'
      AND user_id IS NOT NULL
    GROUP BY user_id
),
last_login AS (
    SELECT user_id, MAX(created_at) AS last_login_at
    FROM user_sessions
    WHERE revoked_at IS NULL
    GROUP BY user_id
),
last_human_message AS (
    SELECT m.user_id, MAX(msg.created_at) AS last_human_message_at
    FROM scope_messages msg
    JOIN members m ON m.id = msg.author_member_id
    WHERE m.type = 'human'
      AND m.user_id IS NOT NULL
    GROUP BY m.user_id
)
SELECT
    u.id,
    COALESCE(u.email, ''),
    u.name,
    COALESCE(NULLIF(u.display_name, ''), u.name),
    u.is_admin,
    u.email_verified_at IS NOT NULL,
    u.created_at,
    COALESCE(wc.workspace_count, 0),
    COALESCE(owc.owned_workspace_count, 0),
    ll.last_login_at,
    lhm.last_human_message_at
FROM users u
LEFT JOIN workspace_counts wc ON wc.user_id = u.id
LEFT JOIN owned_workspace_counts owc ON owc.user_id = u.id
LEFT JOIN last_login ll ON ll.user_id = u.id
LEFT JOIN last_human_message lhm ON lhm.user_id = u.id
WHERE ($2 = '%%'
       OR LOWER(u.id) LIKE $2
       OR LOWER(COALESCE(u.email, '')) LIKE $2
       OR LOWER(u.name) LIKE $2
       OR LOWER(COALESCE(u.display_name, '')) LIKE $2)
  AND ($3 = FALSE OR COALESCE(ll.last_login_at >= $1, FALSE) OR COALESCE(lhm.last_human_message_at >= $1, FALSE))
ORDER BY COALESCE(lhm.last_human_message_at, ll.last_login_at, u.created_at) DESC, u.id ASC
LIMIT $4
`, since, normalizedQuery, activeOnly, normalizedLimit)
	if err != nil {
		return nil, fmt.Errorf("list admin users: %w", err)
	}
	defer rows.Close()

	users := make([]AdminUserSummary, 0)
	for rows.Next() {
		var item AdminUserSummary
		if err := rows.Scan(
			&item.ID,
			&item.Email,
			&item.Name,
			&item.DisplayName,
			&item.IsAdmin,
			&item.EmailVerified,
			&item.CreatedAt,
			&item.WorkspaceCount,
			&item.OwnedWorkspaceCount,
			&item.LastLoginAt,
			&item.LastHumanMessageAt,
		); err != nil {
			return nil, fmt.Errorf("scan admin user: %w", err)
		}
		users = append(users, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate admin users: %w", err)
	}
	return users, nil
}