package organization

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

func (txs TxStore) EnsureOrganizationUnitBackedTeamForUser(ctx context.Context, userID string, organizationID string, unitID string) (OrganizationTeamView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationTeamView{}, err
	}

	return withTxValue(ctx, txs.db, func(tx coredb.Tx) (OrganizationTeamView, error) {
		team, err := txs.ensureOrganizationUnitBackedTeam(ctx, tx, organizationID, unitID, userID)
		if err != nil {
			return OrganizationTeamView{}, err
		}

		return organizationTeamViewFromRecord(team), nil
	})
}

func (txs TxStore) ensureOrganizationUnitBackedTeam(ctx context.Context, tx coredb.Tx, organizationID string, unitID string, userID string) (organizationTeamRecord, error) {
	unit, err := txs.loadOrganizationUnit(ctx, tx, organizationID, unitID)
	if err != nil {
		return organizationTeamRecord{}, err
	}
	if unit.Status != organizationUnitStatusActive {
		return organizationTeamRecord{}, badRequest("unit is not active")
	}

	existingTeam, hasExistingTeam, err := txs.loadOrganizationUnitBackedTeam(ctx, tx, organizationID, unitID)
	if err != nil {
		return organizationTeamRecord{}, err
	}
	if hasExistingTeam {
		if err := txs.reconcileOrganizationUnitBackedTeamMemberships(ctx, tx, organizationID, unitID, userID); err != nil {
			return organizationTeamRecord{}, err
		}
		return existingTeam, nil
	}

	teamID, err := identifiers.NewEntityID("orgt")
	if err != nil {
		return organizationTeamRecord{}, fmt.Errorf("generate organization team id: %w", err)
	}
	now := time.Now().UTC()
	slug := unitBackedTeamSlug(unit.Slug, unit.ID)
	description := "Unit-backed team for " + unit.Name
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_teams (
    id,
    organization_id,
    name,
    slug,
    description,
    status,
    source,
    is_default,
    backing_unit_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, 'active', 'unit_backed', FALSE, $6, $7, $7)
`, teamID, organizationID, unit.Name, slug, description, unit.ID, now); err != nil {
		return organizationTeamRecord{}, fmt.Errorf("insert unit backed organization team: %w", err)
	}

	if err := txs.reconcileOrganizationUnitBackedTeamMemberships(ctx, tx, organizationID, unitID, userID); err != nil {
		return organizationTeamRecord{}, err
	}
	return txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
}

func (txs TxStore) loadOrganizationUnitBackedTeam(ctx context.Context, queryer queryRower, organizationID string, unitID string) (organizationTeamRecord, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT ot.id,
       ot.name,
       ot.slug,
       ot.description,
       ot.source,
       ot.status,
       ot.is_default,
       COALESCE(ot.backing_unit_id, ''),
       ot.external_provider,
       ot.external_id,
       ot.synced_at,
       ot.sync_mode,
       ot.sync_source,
       COALESCE(osr.id, ''),
       COALESCE(osr.directory_revision, 0),
       COALESCE(osr.status, '')
FROM organization_teams ot
LEFT JOIN organization_directory_sync_runs osr
  ON osr.id = ot.last_sync_run_id
WHERE ot.organization_id = $1
  AND ot.backing_unit_id = $2
  AND ot.source = 'unit_backed'
`, organizationID, unitID)

	var team organizationTeamRecord
	var syncedAt sql.NullTime
	if err := row.Scan(
		&team.ID,
		&team.Name,
		&team.Slug,
		&team.Description,
		&team.Source,
		&team.Status,
		&team.IsDefault,
		&team.BackingUnitID,
		&team.ExternalProvider,
		&team.ExternalID,
		&syncedAt,
		&team.SyncMode,
		&team.SyncSource,
		&team.LastSyncRunID,
		&team.LastSyncRevision,
		&team.LastSyncStatus,
	); err != nil {
		if coredb.IsNotFound(err) {
			return organizationTeamRecord{}, false, nil
		}
		return organizationTeamRecord{}, false, fmt.Errorf("load organization unit backed team: %w", err)
	}
	if syncedAt.Valid {
		team.SyncedAt = &syncedAt.Time
	}
	return team, true, nil
}

func (txs TxStore) reconcileOrganizationUnitBackedTeamMemberships(ctx context.Context, tx coredb.Tx, organizationID string, unitID string, userID string) error {
	team, hasTeam, err := txs.loadOrganizationUnitBackedTeam(ctx, tx, organizationID, unitID)
	if err != nil {
		return err
	}
	if !hasTeam {
		return nil
	}

	unitAccountIDs, err := txs.listActiveOrganizationMemberIDsForUnit(ctx, tx, organizationID, unitID)
	if err != nil {
		return err
	}
	derivedAccountIDs, err := txs.listActiveDerivedOrganizationMemberIDsForTeam(ctx, tx, organizationID, team.ID)
	if err != nil {
		return err
	}

	unitAccountSet := make(map[string]struct{}, len(unitAccountIDs))
	for _, accountID := range unitAccountIDs {
		unitAccountSet[accountID] = struct{}{}
	}
	derivedAccountSet := make(map[string]struct{}, len(derivedAccountIDs))
	for _, accountID := range derivedAccountIDs {
		derivedAccountSet[accountID] = struct{}{}
	}

	changedAccounts := make(map[string]struct{})
	for _, accountID := range unitAccountIDs {
		if _, ok := derivedAccountSet[accountID]; ok {
			continue
		}
		if err := txs.upsertDerivedOrganizationTeamMembership(ctx, tx, organizationID, team.ID, accountID, userID); err != nil {
			return err
		}
		changedAccounts[accountID] = struct{}{}
	}
	for _, accountID := range derivedAccountIDs {
		if _, ok := unitAccountSet[accountID]; ok {
			continue
		}
		if err := txs.deactivateDerivedOrganizationTeamMembership(ctx, tx, organizationID, team.ID, accountID); err != nil {
			return err
		}
		changedAccounts[accountID] = struct{}{}
	}

	if len(changedAccounts) == 0 {
		return nil
	}

	workspaces, err := txs.listGrantedWorkspacesForTeam(ctx, tx, organizationID, team.ID)
	if err != nil {
		return err
	}
	if len(workspaces) == 0 {
		return nil
	}

	accountIDs := make([]string, 0, len(changedAccounts))
	for accountID := range changedAccounts {
		accountIDs = append(accountIDs, accountID)
	}
	sort.Strings(accountIDs)

	for _, workspaceID := range workspaces {
		for _, accountID := range accountIDs {
			if _, _, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, accountID); err != nil {
				return err
			}
		}
	}
	return nil
}

func (txs TxStore) upsertDerivedOrganizationTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, teamID string, accountID string, userID string) error {
	membershipID, err := identifiers.NewEntityID("oatm")
	if err != nil {
		return fmt.Errorf("generate organization team membership id: %w", err)
	}
	now := time.Now().UTC()
	if err := execPortableUpdateThenInsert(
		ctx,
		tx,
		`
SELECT 1
FROM organization_member_team_memberships
WHERE organization_member_id = $1
  AND organization_team_id = $2
`,
		[]any{accountID, teamID},
		`
UPDATE organization_member_team_memberships
SET membership_role = 'team_member',
    source = 'derived',
    status = 'active',
    is_primary = FALSE,
    updated_at = $3
WHERE organization_member_id = $1
  AND organization_team_id = $2
`,
		[]any{accountID, teamID, now},
		`
INSERT INTO organization_member_team_memberships (
    id,
    organization_id,
    organization_team_id,
    organization_member_id,
    membership_role,
    source,
    status,
    is_primary,
    created_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, 'team_member', 'derived', 'active', FALSE, $5, $6, $6)
`,
		[]any{membershipID, organizationID, teamID, accountID, userID, now},
	); err != nil {
		return fmt.Errorf("upsert derived organization team membership: %w", err)
	}
	return nil
}

func (txs TxStore) deactivateDerivedOrganizationTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, teamID string, accountID string) error {
	if _, err := tx.Exec(ctx, `
UPDATE organization_member_team_memberships
SET status = 'inactive', is_primary = FALSE, updated_at = $4
WHERE organization_id = $1
  AND organization_team_id = $2
  AND organization_member_id = $3
  AND source = 'derived'
  AND status = 'active'
`, organizationID, teamID, accountID, time.Now().UTC()); err != nil {
		return fmt.Errorf("deactivate derived organization team membership: %w", err)
	}
	return nil
}

func (txs TxStore) listActiveOrganizationMemberIDsForUnit(ctx context.Context, queryer queryer, organizationID string, unitID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT oaum.organization_member_id
FROM organization_member_unit_memberships oaum
JOIN organization_members om ON om.id = oaum.organization_member_id
WHERE oaum.organization_id = $1
  AND oaum.organization_unit_id = $2
  AND oaum.status = 'active'
  AND om.status = 'active'
ORDER BY oaum.organization_member_id ASC
`, organizationID, unitID)
	if err != nil {
		return nil, fmt.Errorf("list active organization members for unit: %w", err)
	}
	defer rows.Close()

	accountIDs := make([]string, 0)
	for rows.Next() {
		var accountID string
		if err := rows.Scan(&accountID); err != nil {
			return nil, fmt.Errorf("scan active organization member for unit: %w", err)
		}
		accountIDs = append(accountIDs, accountID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization members for unit: %w", err)
	}
	return accountIDs, nil
}

func (txs TxStore) listActiveDerivedOrganizationMemberIDsForTeam(ctx context.Context, queryer queryer, organizationID string, teamID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT tm.organization_member_id
FROM organization_member_team_memberships tm
JOIN organization_members om ON om.id = tm.organization_member_id
WHERE tm.organization_id = $1
  AND tm.organization_team_id = $2
  AND tm.source = 'derived'
  AND tm.status = 'active'
  AND om.status = 'active'
ORDER BY tm.organization_member_id ASC
`, organizationID, teamID)
	if err != nil {
		return nil, fmt.Errorf("list active derived organization members for team: %w", err)
	}
	defer rows.Close()

	accountIDs := make([]string, 0)
	for rows.Next() {
		var accountID string
		if err := rows.Scan(&accountID); err != nil {
			return nil, fmt.Errorf("scan active derived organization member for team: %w", err)
		}
		accountIDs = append(accountIDs, accountID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active derived organization members for team: %w", err)
	}
	return accountIDs, nil
}

func organizationTeamViewFromRecord(team organizationTeamRecord) OrganizationTeamView {
	return OrganizationTeamView{
		ID:                       team.ID,
		Name:                     team.Name,
		Slug:                     team.Slug,
		Description:              team.Description,
		Status:                   team.Status,
		Source:                   team.Source,
		IsDefault:                team.IsDefault,
		OrganizationSyncMetadata: organizationSyncMetadataView(team.ExternalProvider, team.ExternalID, team.SyncedAt, team.SyncMode, team.SyncSource, team.LastSyncRunID, team.LastSyncRevision, team.LastSyncStatus),
	}
}

func unitBackedTeamSlug(unitSlug string, unitID string) string {
	base := "unit-" + strings.Trim(strings.TrimSpace(unitSlug), "-")
	if base == "unit-" {
		base = "unit"
	}

	suffix := slugifyOrganizationName(unitID)
	suffix = strings.TrimPrefix(suffix, "orgu-")
	if len(suffix) > 8 {
		suffix = suffix[len(suffix)-8:]
	}
	if suffix != "" {
		base += "-" + suffix
	}
	if len(base) > 63 {
		base = strings.TrimRight(base[:63], "-")
	}
	return base
}