package organization

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

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

type queryer interface {
	queryRower
	Query(context.Context, string, ...any) (coredb.Rows, error)
}

type organizationTeamRecord struct {
	ID               string
	Name             string
	Slug             string
	Description      string
	Source           string
	Status           string
	IsDefault        bool
	BackingUnitID    string
	ExternalProvider string
	ExternalID       string
	SyncedAt         *time.Time
	SyncMode         string
	SyncSource       string
	LastSyncRunID    string
	LastSyncRevision int64
	LastSyncStatus   string
}

type organizationTeamMembershipRecord struct {
	ID             string
	MemberID       string
	TeamID         string
	MembershipRole string
	Source         string
	Status         string
	IsPrimary      bool
}

const (
	organizationTeamMembershipRoleMember    = "team_member"
	organizationTeamMembershipSourceDirect  = "direct"
	organizationTeamMembershipSourceDerived = "derived"
	organizationTeamMembershipStatusActive  = "active"
)

func (txs TxStore) ListOrganizationTeamMembersForUser(ctx context.Context, userID string, organizationID string, teamID string) ([]OrganizationTeamMemberView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if _, err := txs.loadOrganizationTeam(ctx, txs.db, organizationID, teamID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT tm.organization_team_id,
       om.id,
       u.id,
       COALESCE(u.email, ''),
       u.name,
       u.display_name,
       tm.membership_role,
       tm.source,
       tm.status,
       tm.is_primary,
       pt.id,
       pt.name
FROM organization_member_team_memberships tm
JOIN organization_members om ON om.id = tm.organization_member_id
JOIN users u ON u.id = om.user_id
JOIN organization_teams pt ON pt.id = om.primary_team_id
WHERE tm.organization_id = $1
  AND tm.organization_team_id = $2
  AND tm.status = 'active'
  AND om.status = 'active'
ORDER BY tm.is_primary DESC, COALESCE(u.email, '') ASC, om.id ASC
`, organizationID, teamID)
	if err != nil {
		return nil, fmt.Errorf("list organization team members: %w", err)
	}
	defer rows.Close()

	memberships := make([]OrganizationTeamMemberView, 0)
	for rows.Next() {
		var view OrganizationTeamMemberView
		if err := rows.Scan(
			&view.TeamID,
			&view.MemberID,
			&view.UserID,
			&view.Email,
			&view.Name,
			&view.DisplayName,
			&view.MembershipRole,
			&view.Source,
			&view.Status,
			&view.IsPrimary,
			&view.PrimaryTeamID,
			&view.PrimaryTeamName,
		); err != nil {
			return nil, fmt.Errorf("scan organization team member: %w", err)
		}
		memberships = append(memberships, view)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization team members: %w", err)
	}
	return memberships, nil
}

func (txs TxStore) AddOrganizationTeamMemberForUser(ctx context.Context, userID string, organizationID string, teamID string, params AddOrganizationTeamMemberParams) (OrganizationTeamMemberView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationTeamMemberView{}, err
	}
	if err := txs.requireOrganizationLocalDirectoryWritesAllowed(ctx, txs.db, organizationID); err != nil {
		return OrganizationTeamMemberView{}, err
	}

	memberID := strings.TrimSpace(params.MemberID)
	if memberID == "" {
		return OrganizationTeamMemberView{}, badRequest("memberId is required")
	}

	return withTxValue(ctx, txs.db, func(tx coredb.Tx) (OrganizationTeamMemberView, error) {
		team, err := txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return OrganizationTeamMemberView{}, err
		}
		if err := requireLocallyMutableTeam(team); err != nil {
			return OrganizationTeamMemberView{}, err
		}
		member, err := txs.loadActiveOrganizationMember(ctx, tx, organizationID, memberID)
		if err != nil {
			return OrganizationTeamMemberView{}, err
		}
		if err := requireLocallyMutableMember(member); err != nil {
			return OrganizationTeamMemberView{}, err
		}

		if err := txs.upsertOrganizationTeamMembership(ctx, tx, organizationID, teamID, memberID, userID, false); err != nil {
			return OrganizationTeamMemberView{}, err
		}
		if params.Primary {
			if err := txs.switchPrimaryTeamMembership(ctx, tx, organizationID, memberID, teamID, userID); err != nil {
				return OrganizationTeamMemberView{}, err
			}
		}

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

		view, err := txs.loadOrganizationTeamMemberView(ctx, tx, organizationID, teamID, memberID)
		if err != nil {
			return OrganizationTeamMemberView{}, err
		}

		return view, nil
	})
}

func (txs TxStore) SwitchOrganizationMemberPrimaryTeamForUser(ctx context.Context, userID string, organizationID string, memberID string, teamID string) (OrganizationMemberView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationMemberView{}, err
	}
	if err := txs.requireOrganizationLocalDirectoryWritesAllowed(ctx, txs.db, organizationID); err != nil {
		return OrganizationMemberView{}, err
	}

	return withTxValue(ctx, txs.db, func(tx coredb.Tx) (OrganizationMemberView, error) {
		team, err := txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return OrganizationMemberView{}, err
		}
		if err := requireLocallyMutableTeam(team); err != nil {
			return OrganizationMemberView{}, err
		}
		member, err := txs.loadActiveOrganizationMember(ctx, tx, organizationID, memberID)
		if err != nil {
			return OrganizationMemberView{}, err
		}
		if err := requireLocallyMutableMember(member); err != nil {
			return OrganizationMemberView{}, err
		}
		if err := txs.upsertOrganizationTeamMembership(ctx, tx, organizationID, teamID, memberID, userID, false); err != nil {
			return OrganizationMemberView{}, err
		}
		if err := txs.switchPrimaryTeamMembership(ctx, tx, organizationID, memberID, teamID, userID); err != nil {
			return OrganizationMemberView{}, err
		}

		workspaces, err := txs.listGrantedWorkspacesForTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return OrganizationMemberView{}, err
		}
		for _, workspaceID := range workspaces {
			if _, _, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, member.MemberID); err != nil {
				return OrganizationMemberView{}, err
			}
		}

		view, err := txs.loadOrganizationMemberView(ctx, tx, organizationID, memberID)
		if err != nil {
			return OrganizationMemberView{}, err
		}

		return view, nil
	})
}

func (txs TxStore) RemoveOrganizationTeamMemberForUser(ctx context.Context, userID string, organizationID string, teamID string, memberID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if err := txs.requireOrganizationLocalDirectoryWritesAllowed(ctx, txs.db, organizationID); err != nil {
		return err
	}

	return withTx(ctx, txs.db, func(tx coredb.Tx) error {
		team, err := txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return err
		}
		if err := requireLocallyMutableTeam(team); err != nil {
			return err
		}
		member, err := txs.loadActiveOrganizationMember(ctx, tx, organizationID, memberID)
		if err != nil {
			return err
		}
		if err := requireLocallyMutableMember(member); err != nil {
			return err
		}
		membership, err := txs.loadOrganizationTeamMembership(ctx, tx, organizationID, teamID, memberID)
		if err != nil {
			return err
		}
		if team.IsDefault {
			return badRequest("default team membership cannot be removed")
		}
		if membership.IsPrimary {
			return badRequest("switch primary team before removing this membership")
		}

		if _, err := tx.Exec(ctx, `
UPDATE organization_member_team_memberships
SET status = 'inactive', updated_at = $4
WHERE organization_id = $1 AND organization_team_id = $2 AND organization_member_id = $3
`, organizationID, teamID, memberID, time.Now().UTC()); err != nil {
			return fmt.Errorf("deactivate organization team membership: %w", err)
		}

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

		return nil
	})
}

func (txs TxStore) loadOrganizationTeam(ctx context.Context, queryer queryRower, organizationID string, teamID string) (organizationTeamRecord, 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.id = $2
`, organizationID, teamID)

	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{}, ErrNotFound
		}
		return organizationTeamRecord{}, fmt.Errorf("load organization team: %w", err)
	}
	if syncedAt.Valid {
		team.SyncedAt = &syncedAt.Time
	}
	return team, nil
}

func (txs TxStore) loadOrganizationTeamMembership(ctx context.Context, queryer queryRower, organizationID string, teamID string, memberID string) (organizationTeamMembershipRecord, error) {
	row := queryer.QueryRow(ctx, `
SELECT id, organization_member_id, organization_team_id, membership_role, source, status, is_primary
FROM organization_member_team_memberships
WHERE organization_id = $1 AND organization_team_id = $2 AND organization_member_id = $3
`, organizationID, teamID, memberID)

	var membership organizationTeamMembershipRecord
	if err := row.Scan(&membership.ID, &membership.MemberID, &membership.TeamID, &membership.MembershipRole, &membership.Source, &membership.Status, &membership.IsPrimary); err != nil {
		if coredb.IsNotFound(err) {
			return organizationTeamMembershipRecord{}, ErrNotFound
		}
		return organizationTeamMembershipRecord{}, fmt.Errorf("load organization team membership: %w", err)
	}
	return membership, nil
}

func (txs TxStore) loadOrganizationTeamMemberView(ctx context.Context, queryer queryRower, organizationID string, teamID string, memberID string) (OrganizationTeamMemberView, error) {
	row := queryer.QueryRow(ctx, `
SELECT tm.organization_team_id,
       om.id,
       u.id,
       COALESCE(u.email, ''),
       u.name,
       u.display_name,
       tm.membership_role,
       tm.source,
       tm.status,
       tm.is_primary,
       pt.id,
       pt.name
FROM organization_member_team_memberships tm
JOIN organization_members om ON om.id = tm.organization_member_id
JOIN users u ON u.id = om.user_id
JOIN organization_teams pt ON pt.id = om.primary_team_id
WHERE tm.organization_id = $1
  AND tm.organization_team_id = $2
  AND tm.organization_member_id = $3
`, organizationID, teamID, memberID)

	var view OrganizationTeamMemberView
	if err := row.Scan(
		&view.TeamID,
		&view.MemberID,
		&view.UserID,
		&view.Email,
		&view.Name,
		&view.DisplayName,
		&view.MembershipRole,
		&view.Source,
		&view.Status,
		&view.IsPrimary,
		&view.PrimaryTeamID,
		&view.PrimaryTeamName,
	); err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationTeamMemberView{}, ErrNotFound
		}
		return OrganizationTeamMemberView{}, fmt.Errorf("load organization team member view: %w", err)
	}
	return view, nil
}

func (txs TxStore) loadOrganizationMemberView(ctx context.Context, queryer queryRower, organizationID string, memberID string) (OrganizationMemberView, error) {
	row := queryer.QueryRow(ctx, `
SELECT om.id, u.id, COALESCE(u.email, ''), u.name, u.display_name, t.id, t.name, om.status, om.org_role, om.source, om.default_workspace_role,
       om.external_provider, om.external_id, om.synced_at, om.sync_mode, om.sync_source,
       COALESCE(osr.id, ''),
       COALESCE(osr.directory_revision, 0),
       COALESCE(osr.status, '')
FROM organization_members om
JOIN users u ON u.id = om.user_id
JOIN organization_teams t ON t.id = om.primary_team_id
LEFT JOIN organization_directory_sync_runs osr ON osr.id = om.last_sync_run_id
WHERE om.organization_id = $1 AND om.id = $2
`, organizationID, memberID)

	var account OrganizationMemberView
	var syncedAt sql.NullTime
	if err := row.Scan(
		&account.ID,
		&account.UserID,
		&account.Email,
		&account.Name,
		&account.DisplayName,
		&account.PrimaryTeamID,
		&account.PrimaryTeamName,
		&account.Status,
		&account.Role,
		&account.Source,
		&account.DefaultWorkspaceRole,
		&account.ExternalProvider,
		&account.ExternalID,
		&syncedAt,
		&account.SyncMode,
		&account.SyncSource,
		&account.LastSyncRunID,
		&account.LastSyncDirectoryRevision,
		&account.LastSyncRunStatus,
	); err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationMemberView{}, ErrNotFound
		}
		return OrganizationMemberView{}, fmt.Errorf("load organization member view: %w", err)
	}
	if syncedAt.Valid {
		account.SyncedAt = &syncedAt.Time
	}
	account.OrganizationSyncMetadata = organizationSyncMetadataView(
		account.ExternalProvider,
		account.ExternalID,
		account.SyncedAt,
		account.SyncMode,
		account.SyncSource,
		account.LastSyncRunID,
		account.LastSyncDirectoryRevision,
		account.LastSyncRunStatus,
	)
	return account, nil
}

func (txs TxStore) upsertOrganizationTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, teamID string, accountID string, userID string, primary bool) error {
	now := time.Now().UTC()
	membershipID, err := identifiers.NewEntityID("oatm")
	if err != nil {
		return fmt.Errorf("generate organization team membership id: %w", err)
	}
	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 status = 'active',
    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', 'direct', 'active', $5, $6, $7, $7)
`,
		[]any{membershipID, organizationID, teamID, accountID, primary, userID, now},
	); err != nil {
		return fmt.Errorf("upsert organization team membership: %w", err)
	}
	return nil
}

func (txs TxStore) switchPrimaryTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, accountID string, teamID string, userID string) error {
	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
UPDATE organization_member_team_memberships
SET is_primary = FALSE, updated_at = $3
WHERE organization_id = $1 AND organization_member_id = $2 AND status = 'active'
`, organizationID, accountID, now); err != nil {
		return fmt.Errorf("clear primary team membership: %w", err)
	}
	if _, err := tx.Exec(ctx, `
UPDATE organization_member_team_memberships
SET is_primary = TRUE, status = 'active', updated_at = $4
WHERE organization_id = $1 AND organization_member_id = $2 AND organization_team_id = $3
`, organizationID, accountID, teamID, now); err != nil {
		return fmt.Errorf("set primary team membership: %w", err)
	}
	if _, err := tx.Exec(ctx, `
UPDATE organization_members
SET primary_team_id = $3, updated_at = $4
WHERE organization_id = $1 AND id = $2
`, organizationID, accountID, teamID, now); err != nil {
		return fmt.Errorf("update organization member primary team: %w", err)
	}
	return nil
}

func (txs TxStore) listGrantedWorkspacesForTeam(ctx context.Context, queryer queryer, organizationID string, teamID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT workspace_id
FROM organization_workspace_team_grants
WHERE organization_id = $1 AND organization_team_id = $2 AND status = 'active'
ORDER BY workspace_id ASC
`, organizationID, teamID)
	if err != nil {
		return nil, fmt.Errorf("list granted workspaces for team: %w", err)
	}
	defer rows.Close()

	workspaces := make([]string, 0)
	for rows.Next() {
		var workspaceID string
		if err := rows.Scan(&workspaceID); err != nil {
			return nil, fmt.Errorf("scan granted workspace: %w", err)
		}
		workspaces = append(workspaces, workspaceID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate granted workspaces: %w", err)
	}
	return workspaces, nil
}

func requireLocallyMutableTeam(team organizationTeamRecord) error {
	if team.Status != organizationTeamStatusActive {
		return badRequest("team is not active")
	}
	if team.SyncMode != "" && team.SyncMode != organizationSyncModeLocal {
		return badRequest("team is managed by provider sync")
	}
	switch team.Source {
	case organizationTeamSourceManual:
		return nil
	case organizationTeamSourceSynced:
		return nil
	case "unit_backed", "system":
		return badRequest("team memberships are managed by provider sync")
	default:
		return badRequest("team memberships are not locally editable")
	}
}

func requireLocallyMutableMember(member activeOrganizationMember) error {
	if member.SyncMode != "" && member.SyncMode != organizationSyncModeLocal {
		return badRequest("member is managed by provider sync")
	}
	return nil
}