package orgmembership

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

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

const (
	orgStatusActive                = "active"
	orgRoleMember                  = "org_member"
	orgMemberSourceWorkspaceInvite = "workspace_invite"
	defaultWorkspaceRoleMember     = "member"
)

type WorkspaceInviteMemberParams struct {
	OrganizationID             string
	UserID                     string
	InviterUserID              string
	CheckOrganizationQuotaInTx func(context.Context, coredb.Tx, string, plan.MetricKey, int64) error
}

type WorkspaceInviteMemberResult struct {
	MemberID string
	Created  bool
}

type memberRecord struct {
	ID     string
	Status string
	UserID string
}

func EnsureWorkspaceInviteMemberInTx(
	ctx context.Context,
	tx coredb.Tx,
	params WorkspaceInviteMemberParams,
) (WorkspaceInviteMemberResult, error) {
	organizationID := strings.TrimSpace(params.OrganizationID)
	userID := strings.TrimSpace(params.UserID)
	inviterUserID := strings.TrimSpace(params.InviterUserID)
	if organizationID == "" {
		return WorkspaceInviteMemberResult{}, storeerr.BadRequest("organizationId is required")
	}
	if userID == "" {
		return WorkspaceInviteMemberResult{}, storeerr.BadRequest("userId is required")
	}
	if inviterUserID == "" {
		return WorkspaceInviteMemberResult{}, storeerr.BadRequest("inviterUserId is required")
	}
	if params.CheckOrganizationQuotaInTx == nil {
		return WorkspaceInviteMemberResult{}, fmt.Errorf("organization quota checker is not configured")
	}

	member, memberFound, err := loadOrganizationMemberByUser(ctx, tx, organizationID, userID)
	if err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	now := time.Now().UTC()
	if memberFound {
		return ensureExistingMember(ctx, tx, organizationID, member, userID)
	}
	return createWorkspaceInviteOrganizationMember(ctx, tx, params.CheckOrganizationQuotaInTx, organizationID, userID, inviterUserID, now)
}

func createWorkspaceInviteOrganizationMember(
	ctx context.Context,
	tx coredb.Tx,
	checkQuota func(context.Context, coredb.Tx, string, plan.MetricKey, int64) error,
	organizationID string,
	userID string,
	inviterUserID string,
	now time.Time,
) (WorkspaceInviteMemberResult, error) {
	hasSeat, err := hasActiveSeat(ctx, tx, organizationID, userID)
	if err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	if !hasSeat {
		if err := checkQuota(ctx, tx, organizationID, plan.MetricSeats, 1); err != nil {
			return WorkspaceInviteMemberResult{}, err
		}
	}

	defaultTeamID, err := loadDefaultTeamID(ctx, tx, organizationID)
	if err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	defaultUnitID, err := loadDefaultUnitID(ctx, tx, organizationID)
	if err != nil {
		return WorkspaceInviteMemberResult{}, err
	}

	memberID, err := identifiers.NewEntityID("orgm")
	if err != nil {
		return WorkspaceInviteMemberResult{}, fmt.Errorf("generate organization member id: %w", err)
	}

	if _, err := tx.Exec(ctx, `
INSERT INTO organization_members (
    id,
    organization_id,
    user_id,
    primary_team_id,
    primary_unit_id,
    org_role,
    status,
    source,
    default_workspace_role,
    joined_at,
    invited_by_user_id,
    created_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, 'active', $7, $8, $9, $10, $10, $9, $9)
`, memberID, organizationID, userID, defaultTeamID, defaultUnitID, orgRoleMember, orgMemberSourceWorkspaceInvite, defaultWorkspaceRoleMember, now, inviterUserID); err != nil {
		if coredb.IsUniqueViolation(err) {
			return WorkspaceInviteMemberResult{}, storeerr.Conflict("User already belongs to this organization.")
		}
		return WorkspaceInviteMemberResult{}, fmt.Errorf("insert organization member from workspace invite: %w", err)
	}
	if err := upsertTeamMembership(ctx, tx, organizationID, defaultTeamID, memberID, inviterUserID, true, now); err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	if err := switchPrimaryTeam(ctx, tx, organizationID, memberID, defaultTeamID, now); err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	if err := upsertUnitMembership(ctx, tx, organizationID, defaultUnitID, memberID, inviterUserID, true, now); err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	if err := switchPrimaryUnit(ctx, tx, organizationID, memberID, defaultUnitID, now); err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	if err := reconcileUnitBackedTeamMemberships(ctx, tx, organizationID, defaultUnitID, memberID, inviterUserID, now); err != nil {
		return WorkspaceInviteMemberResult{}, err
	}
	return WorkspaceInviteMemberResult{MemberID: memberID, Created: true}, nil
}

func ensureExistingMember(
	ctx context.Context,
	tx coredb.Tx,
	organizationID string,
	member memberRecord,
	userID string,
) (WorkspaceInviteMemberResult, error) {
	if strings.TrimSpace(member.UserID) != userID {
		return WorkspaceInviteMemberResult{}, storeerr.Conflict("Organization member user does not match request.")
	}
	if member.Status != orgStatusActive {
		return WorkspaceInviteMemberResult{}, storeerr.Forbidden("organization member is inactive")
	}
	return WorkspaceInviteMemberResult{MemberID: member.ID}, nil
}

func loadOrganizationMemberByUser(ctx context.Context, tx coredb.Tx, organizationID string, userID string) (memberRecord, bool, error) {
	row := tx.QueryRow(ctx, `
SELECT id, status, user_id
FROM organization_members
WHERE organization_id = $1
  AND user_id = $2
`, organizationID, userID)
	var member memberRecord
	if err := row.Scan(&member.ID, &member.Status, &member.UserID); err != nil {
		if coredb.IsNotFound(err) {
			return memberRecord{}, false, nil
		}
		return memberRecord{}, false, fmt.Errorf("load organization member by user id: %w", err)
	}
	return member, true, nil
}

func hasActiveSeat(ctx context.Context, tx coredb.Tx, organizationID string, userID string) (bool, error) {
	row := tx.QueryRow(ctx, `
SELECT COUNT(*)
FROM (
  SELECT om.user_id AS user_id
  FROM organization_members om
  WHERE om.organization_id = $1
    AND om.user_id = $2
    AND om.status = 'active'
  UNION
  SELECT m.user_id AS user_id
  FROM members m
  JOIN workspaces w ON w.id = m.workspace_id
  WHERE w.organization_id = $1
    AND m.user_id = $2
    AND m.type = 'human'
    AND m.status = 'active'
) seats
`, organizationID, userID)
	var count int64
	if err := row.Scan(&count); err != nil {
		return false, fmt.Errorf("check active organization seat: %w", err)
	}
	return count > 0, nil
}

func loadDefaultTeamID(ctx context.Context, tx coredb.Tx, organizationID string) (string, error) {
	row := tx.QueryRow(ctx, `
SELECT id
FROM organization_teams
WHERE organization_id = $1 AND is_default = TRUE
`, organizationID)
	var teamID string
	if err := row.Scan(&teamID); err != nil {
		if coredb.IsNotFound(err) {
			return "", storeerr.NotFound("default organization team not found")
		}
		return "", fmt.Errorf("load default organization team: %w", err)
	}
	return teamID, nil
}

func loadDefaultUnitID(ctx context.Context, tx coredb.Tx, organizationID string) (string, error) {
	row := tx.QueryRow(ctx, `
SELECT id
FROM organization_units
WHERE organization_id = $1 AND is_default = TRUE
`, organizationID)
	var unitID string
	if err := row.Scan(&unitID); err != nil {
		if coredb.IsNotFound(err) {
			return "", storeerr.NotFound("default organization unit not found")
		}
		return "", fmt.Errorf("load default organization unit: %w", err)
	}
	return unitID, nil
}

func upsertTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, teamID string, memberID string, userID string, primary bool, now time.Time) error {
	exists, err := rowExists(ctx, tx, `
SELECT 1
FROM organization_member_team_memberships
WHERE organization_member_id = $1
  AND organization_team_id = $2
`, memberID, teamID)
	if err != nil {
		return fmt.Errorf("load organization team membership: %w", err)
	}
	if exists {
		_, err = tx.Exec(ctx, `
UPDATE organization_member_team_memberships
SET status = 'active',
    updated_at = $3
WHERE organization_member_id = $1
  AND organization_team_id = $2
`, memberID, teamID, now)
		if err != nil {
			return fmt.Errorf("reactivate organization team membership: %w", err)
		}
		return nil
	}

	membershipID, err := identifiers.NewEntityID("oatm")
	if err != nil {
		return fmt.Errorf("generate organization team membership id: %w", err)
	}
	if _, err := tx.Exec(ctx, `
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)
`, membershipID, organizationID, teamID, memberID, primary, userID, now); err != nil {
		return fmt.Errorf("insert organization team membership: %w", err)
	}
	return nil
}

func switchPrimaryTeam(ctx context.Context, tx coredb.Tx, organizationID string, memberID string, teamID string, now time.Time) error {
	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, memberID, 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, memberID, 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, memberID, teamID, now); err != nil {
		return fmt.Errorf("update organization member primary team: %w", err)
	}
	return nil
}

func upsertUnitMembership(ctx context.Context, tx coredb.Tx, organizationID string, unitID string, memberID string, userID string, primary bool, now time.Time) error {
	exists, err := rowExists(ctx, tx, `
SELECT 1
FROM organization_member_unit_memberships
WHERE organization_member_id = $1
  AND organization_unit_id = $2
`, memberID, unitID)
	if err != nil {
		return fmt.Errorf("load organization unit membership: %w", err)
	}
	if exists {
		_, err = tx.Exec(ctx, `
UPDATE organization_member_unit_memberships
SET status = 'active',
    updated_at = $3
WHERE organization_member_id = $1
  AND organization_unit_id = $2
`, memberID, unitID, now)
		if err != nil {
			return fmt.Errorf("reactivate organization unit membership: %w", err)
		}
		return nil
	}

	membershipID, err := identifiers.NewEntityID("oaum")
	if err != nil {
		return fmt.Errorf("generate organization unit membership id: %w", err)
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_member_unit_memberships (
    id,
    organization_id,
    organization_unit_id,
    organization_member_id,
    source,
    status,
    is_primary,
    created_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, 'direct', 'active', $5, $6, $7, $7)
`, membershipID, organizationID, unitID, memberID, primary, userID, now); err != nil {
		return fmt.Errorf("insert organization unit membership: %w", err)
	}
	return nil
}

func switchPrimaryUnit(ctx context.Context, tx coredb.Tx, organizationID string, memberID string, unitID string, now time.Time) error {
	if _, err := tx.Exec(ctx, `
UPDATE organization_member_unit_memberships
SET is_primary = FALSE, updated_at = $3
WHERE organization_id = $1 AND organization_member_id = $2 AND status = 'active'
`, organizationID, memberID, now); err != nil {
		return fmt.Errorf("clear primary unit membership: %w", err)
	}
	if _, err := tx.Exec(ctx, `
UPDATE organization_member_unit_memberships
SET is_primary = TRUE, status = 'active', updated_at = $4
WHERE organization_id = $1 AND organization_member_id = $2 AND organization_unit_id = $3
`, organizationID, memberID, unitID, now); err != nil {
		return fmt.Errorf("set primary unit membership: %w", err)
	}
	if _, err := tx.Exec(ctx, `
UPDATE organization_members
SET primary_unit_id = $3, updated_at = $4
WHERE organization_id = $1 AND id = $2
`, organizationID, memberID, unitID, now); err != nil {
		return fmt.Errorf("update organization member primary unit: %w", err)
	}
	return nil
}

func reconcileUnitBackedTeamMemberships(ctx context.Context, tx coredb.Tx, organizationID string, unitID string, memberID string, userID string, now time.Time) error {
	rows, err := tx.Query(ctx, `
SELECT id
FROM organization_teams
WHERE organization_id = $1
  AND backing_unit_id = $2
  AND status = 'active'
ORDER BY id ASC
`, organizationID, unitID)
	if err != nil {
		return fmt.Errorf("list unit-backed organization teams: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var teamID string
		if err := rows.Scan(&teamID); err != nil {
			return fmt.Errorf("scan unit-backed organization team: %w", err)
		}
		if err := upsertDerivedTeamMembership(ctx, tx, organizationID, teamID, memberID, userID, now); err != nil {
			return err
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate unit-backed organization teams: %w", err)
	}
	return nil
}

func upsertDerivedTeamMembership(ctx context.Context, tx coredb.Tx, organizationID string, teamID string, memberID string, userID string, now time.Time) error {
	exists, err := rowExists(ctx, tx, `
SELECT 1
FROM organization_member_team_memberships
WHERE organization_member_id = $1
  AND organization_team_id = $2
`, memberID, teamID)
	if err != nil {
		return fmt.Errorf("load derived organization team membership: %w", err)
	}
	if exists {
		_, err = tx.Exec(ctx, `
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
`, memberID, teamID, now)
		if err != nil {
			return fmt.Errorf("reactivate derived organization team membership: %w", err)
		}
		return nil
	}

	membershipID, err := identifiers.NewEntityID("oatm")
	if err != nil {
		return fmt.Errorf("generate derived organization team membership id: %w", err)
	}
	if _, err := tx.Exec(ctx, `
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)
`, membershipID, organizationID, teamID, memberID, userID, now); err != nil {
		return fmt.Errorf("insert derived organization team membership: %w", err)
	}
	return nil
}

func rowExists(ctx context.Context, tx coredb.Tx, query string, args ...any) (bool, error) {
	var value int
	if err := tx.QueryRow(ctx, query, args...).Scan(&value); err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}
	return true, nil
}