package resourcepool

import (
	"context"
	"time"

	resourcepoolrepo "github.com/tidbcloud/tidb-management-service/nextgen-global-service/domain/repository/resourcepool"
	"github.com/tidbcloud/tidb-management-service/nextgen-global-service/infra/repoimpl/schema"
	"github.com/tidbcloud/tidb-management-service/utilities/bizerr"
)

const ClusterPoolIDToUint64 = "CAST(clusters.shared_pool_id AS UNSIGNED)"

type IdleInfoResult struct {
	ResourcePoolId uint64     // resource_pools.id
	OwnerId        uint64     // resource_pools.owner_id
	CreatedAt      time.Time  // resource_pools.created_at
	IdleStartTime  *time.Time // last_deleted_cluster.deleted_at
	LastClusterId  uint64     // last_deleted_cluster.id
}

// ListIdle queries and returns a list of ResourcePools that are currently in an "idle" state,
// along with the calculated start time of their idle period.
//
// **Goal:**
// Identify ResourcePools that are not currently associated with any "live" (non-deleted) Clusters,
// and determine the exact timestamp when they became idle.
//
// **Idle Definition:**
// 1. The ResourcePool itself must be alive (`resource_pools.deleted_at IS NULL`).
// 2. The ResourcePool must not be associated with any currently active Cluster (`clusters.deleted_at IS NULL`).
//
// **Idle Start Time Logic:**
// The IdleStartTime is defined as the moment the ResourcePool's last activity concluded:
//   - If the ResourcePool has a history of use (i.e., associated Clusters were deleted), the IdleStartTime
//     is the **deleted_at timestamp of the last Cluster to be deleted**.
//   - If the ResourcePool has never been used,
//     the IdleStartTime is the ResourcePool's own **created_at timestamp**.
//
// **Implementation Details:**
//  1. **NOT IN Subquery (`subQueryAlivePoolIDs`):** Used to filter out ResourcePools whose IDs are present
//     in the list of pools associated with currently active Clusters, enforcing idle condition (2).
//     **Technical Note:** This subquery uses `rdb.Table("clusters").Unscoped()` to prevent GORM from
//     erroneously pushing the main query's `resource_pools.deleted_at` condition into the subquery,
//     which avoids "Unknown column" errors.
//  2. **Window Function Subquery (`subQueryLastDeletedCluster`):** Utilizes the SQL `ROW_NUMBER()` window function.
//     It partitions by `shared_pool_id` and orders by `deleted_at` DESC to efficiently find the `id` and `deleted_at`
//     of the **last deleted Cluster**. This subquery also uses `Unscoped()` to correctly query soft-deleted records.
//  3. **Main Query & Joins:** Performs a `LEFT JOIN` with the result of step (2). The `LEFT JOIN` is placed
//     correctly before the `WHERE` clause in the resulting SQL. The `ON` clause explicitly uses the alias
//     `last_deleted_cluster.shared_pool_id` for correct column referencing.
//  4. **Go Result Mapping:** The Go code checks if the retrieved `IdleStartTime` is NULL. If it is, the
//     `ResourcePool.CreatedAt` is used instead, correctly implementing the defined idle start time logic.
func (i *resourcePoolRepoImpl) ListIdle(ctx context.Context) ([]*resourcepoolrepo.IdleInfo, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "resourcePoolRepo.ListIdle")
	defer span.End()

	rdb := i.provider.ReadDB(ctx)

	subQueryInUsePoolIDs := rdb.Table("clusters").Unscoped().
		Select(ClusterPoolIDToUint64).
		Where("deleted_at IS NULL").
		Where("shared_pool_id IS NOT NULL AND shared_pool_id != ''").
		Group("shared_pool_id")

	rankedClusterSubQuery := rdb.Model(&schema.Cluster{}).Unscoped().
		Select("clusters.id, clusters.deleted_at, clusters.shared_pool_id, ROW_NUMBER() OVER (PARTITION BY clusters.shared_pool_id ORDER BY clusters.deleted_at DESC) as rn").
		Where("clusters.deleted_at IS NOT NULL")

	subQueryLastDeletedCluster := rdb.Table("(?) AS t", rankedClusterSubQuery).
		Select("t.id, t.deleted_at, t.shared_pool_id").
		Where("t.rn = 1")

	var resultSchemas []IdleInfoResult
	err := rdb.Model(&schema.ResourcePool{}).
		Select("resource_pools.id AS resource_pool_id, "+
			"resource_pools.owner_id AS owner_id, "+
			"resource_pools.created_at AS created_at, "+
			"last_deleted_cluster.deleted_at AS idle_start_time, "+
			"last_deleted_cluster.id AS last_cluster_id").
		Joins("LEFT JOIN (?) AS last_deleted_cluster ON "+
			"CAST(last_deleted_cluster.shared_pool_id AS UNSIGNED) = resource_pools.id", subQueryLastDeletedCluster).
		Where("resource_pools.id NOT IN (?)", subQueryInUsePoolIDs).
		Find(&resultSchemas).Error

	if err != nil {
		return nil, bizerr.WrapGormError(err, "failed to list idle resource pools")
	}

	idleInfos := make([]*resourcepoolrepo.IdleInfo, len(resultSchemas))
	for i, res := range resultSchemas {
		idleStartTime := res.CreatedAt
		if res.IdleStartTime != nil {
			idleStartTime = *res.IdleStartTime
		}

		idleInfos[i] = &resourcepoolrepo.IdleInfo{
			ResourcePoolId: res.ResourcePoolId,
			OwnerId:        res.OwnerId,
			CreatedAt:      res.CreatedAt,
			IdleStartTime:  idleStartTime,
			LastClusterId:  res.LastClusterId,
		}
	}

	return idleInfos, nil
}