// Package slowquery provides implementation of query on slow log files.
// Slow log files of the user TiDB cluster is written to S3 via tidbcloud/vector.
package sqls

import (
	"context"
	"errors"

	"github.com/tidbcloud/observability/o11ydiagnosis/pkg/storage"
)

var ErrUnsupportedOrderBy = errors.New("unsupported order by")

// FieldInfo represents information about a field/column in a table
type FieldInfo struct {
	Name string `json:"name"`
	Type string `json:"type"`
}

// StatementsSummaryParams drives DuckDB aggregation for /statements/summary.
type StatementsSummaryParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	Filter     string
	StartUnix  int64
	EndUnix    int64
	Top        int
	OrderBy    string // total_ru | exec_count | sum_latency
	Step       int    // seconds
	Dimension  string // user | sqltypes | digest | db | table (default digest)
	DataSource string
}

// StatementsFieldValuesParams drives DuckDB distinct-value lookup for /statements/field_values.
type StatementsFieldValuesParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	Filter     string
	Field      string
	Limit      int
	DataSource string
}

// StatementsTopStatsParams drives DuckDB aggregation for /statements/topstats.
type StatementsTopStatsParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	Filter     string
	Top        int
	Dimension  string // user | sqltypes | digest
	OrderBy    string // total_ru | exec_count | sum_latency
	DataSource string
}

// StatementsDataSourceProbeParams drives a cheap S3 probe for /statements/datasource.
type StatementsDataSourceProbeParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ClusterID  string
	SharedTier bool
	NextGen    bool
}

// StatementsDataSourcesResponse is returned by GET /statements/datasource.
type StatementsDataSourcesResponse struct {
	V2Available bool `json:"v2_available"`
}

// duckDBAdapter is an interface for querying slow logs.
type duckDBAdapter interface {
	// List lists slow logs which match the conditions described in ListParams.
	ListTables(ctx context.Context, params *ListTableParams) ([]string, error)
	ListTableFields(ctx context.Context, params *ListTableFieldsParams) ([]FieldInfo, error)
	ListTableData(ctx context.Context, params *ListTableDataParams) (*ListTableDataResponse, error)
	GetTableDataByID(ctx context.Context, params *GetTableDataByIDParams) (map[string]interface{}, error)
	QueryStatementsSummary(ctx context.Context, params *StatementsSummaryParams) ([]StatementsSummaryItem, error)
	QueryStatementsTopStats(ctx context.Context, params *StatementsTopStatsParams) ([]StatementsTopStatsItem, error)
	QueryStatementsFieldValues(ctx context.Context, params *StatementsFieldValuesParams) ([]string, error)
	ProbeStatementsDataSources(ctx context.Context, params *StatementsDataSourceProbeParams) (*StatementsDataSourcesResponse, error)
	getSQLsBaseURL(tenantID, o11yClusterID, env, provider, region string, sharedTier, nextGen bool) string
	// StatementsAvailableFields(ctx context.Context) ([]string, error)
	// StatementsList(ctx context.Context, params *StatementsListParams) ([]*StatementsLog, error)
	// StatementsPlans(ctx context.Context, params *StatementsPlansParams) ([]*ListTableDataResponse, error)
	// StatementsPlanDetail(ctx context.Context, params *StatementsDetailParams) (*ListTableDataResponse, error)
	// StatementsStmtTypes(ctx context.Context) ([]string, error)
}

type ListTableParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	Type       string
	DataSource string
}

type ListTableFieldsParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	TableName  string
	DataSource string
}

type ListTableDataParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	TableName  string
	StartTime  string
	EndTime    string
	Fields     []string
	GroupBy    string
	Filter     string
	Sort       string
	Limit      int
	Offset     int
	DataSource string
}

type GetTableDataByIDParams struct {
	Env        string
	Provider   string
	Region     string
	TenantID   string
	ProjectID  string
	ClusterID  string
	SharedTier bool
	NextGen    bool
	Byoc       bool
	TableName  string
	ID         int64
	DataSource string
}

// NewSlowQuery creates a new SlowQuery implementation.
func NewSQLsQuery(store storage.Storage) duckDBAdapter {
	return &sqlsQuery{
		store: store,
	}
}

type sqlsQuery struct {
	store storage.Storage
}

func (s *sqlsQuery) ListTableFields(ctx context.Context, params *ListTableFieldsParams) ([]FieldInfo, error) {
	return nil, nil
}

func (s *sqlsQuery) ListTableData(ctx context.Context, params *ListTableDataParams) (*ListTableDataResponse, error) {
	return nil, nil
}

func (s *sqlsQuery) GetTableDataByID(ctx context.Context, params *GetTableDataByIDParams) (map[string]interface{}, error) {
	return nil, nil
}

func (s *sqlsQuery) QueryStatementsSummary(ctx context.Context, params *StatementsSummaryParams) ([]StatementsSummaryItem, error) {
	return nil, errors.New("QueryStatementsSummary is not implemented for non-DuckDB adapter")
}

func (s *sqlsQuery) QueryStatementsTopStats(ctx context.Context, params *StatementsTopStatsParams) ([]StatementsTopStatsItem, error) {
	return nil, errors.New("QueryStatementsTopStats is not implemented for non-DuckDB adapter")
}

func (s *sqlsQuery) QueryStatementsFieldValues(ctx context.Context, params *StatementsFieldValuesParams) ([]string, error) {
	return nil, errors.New("QueryStatementsFieldValues is not implemented for non-DuckDB adapter")
}

func (s *sqlsQuery) ProbeStatementsDataSources(ctx context.Context, params *StatementsDataSourceProbeParams) (*StatementsDataSourcesResponse, error) {
	return nil, errors.New("ProbeStatementsDataSources is not implemented for non-DuckDB adapter")
}

func (s *sqlsQuery) ListTables(ctx context.Context, params *ListTableParams) ([]string, error) {
	return nil, nil
}

func (s *sqlsQuery) getSQLsBaseURL(tenantID, o11yClusterID, env, provider, region string, sharedTier, nextGen bool) string {
	return ""
}

//nolint:unused // Stub function, kept for potential future use
func (s *sqlsQuery) getClusterTables(ctx context.Context) (map[string]string, error) {
	return nil, nil
}

type ClusterHeader struct {
	Provider   *string `header:"x-provider"`
	Region     *string `header:"x-region"`
	TenantID   *string `header:"x-org-id"`
	ProjectID  *string `header:"x-project-id,omitempty"`
	ClusterID  *string `header:"x-cluster-id"`
	DeployType *string `header:"x-deploy-type"` // "dedicated" or "shared"
	Env        *string `header:"x-env"`
	BizType    *string `header:"x-biz-type"`
}

const (
	TierDedicated = "dedicated"
	TierShared    = "shared"
	TierNextGen   = "nextgen"
	TierPremium   = "premium"
)

func (h *ClusterHeader) Validate() error {
	if h.Provider == nil {
		return errors.New("provider not found")
	}
	if h.Region == nil {
		return errors.New("region not found")
	}
	if h.TenantID == nil {
		return errors.New("org id not found")
	}
	if h.ProjectID == nil {
		var emptyString string
		h.ProjectID = &emptyString
	}
	if h.ClusterID == nil || *h.ClusterID == "" {
		return errors.New("cluster id not found")
	}
	if h.DeployType == nil {
		return errors.New("deploy type not found")
	}
	if h.Env == nil {
		prodString := "prod"
		h.Env = &prodString
	}
	if h.BizType == nil {
		bizType := "unknown"
		h.BizType = &bizType
	}

	return nil
}

type StatementsListParams struct {
	Provider     string
	Region       string
	OrgID        uint64
	ProjectID    uint64
	ClusterID    string
	SharedTier   bool
	StartUnixSec int64
	EndUnixSec   int64
	Fields       []string
	Schemas      []string
	StmtTypes    []string
	Text         string
}

type StatementsPlansParams struct {
	Provider     string
	Region       string
	OrgID        uint64
	ProjectID    uint64
	ClusterID    string
	SharedTier   bool
	StartUnixSec int64
	EndUnixSec   int64
	Digest       string
	SchemaName   string
}

type StatementsDetailParams struct {
	Provider     string
	Region       string
	OrgID        uint64
	ProjectID    uint64
	ClusterID    string
	SharedTier   bool
	StartUnixSec int64
	EndUnixSec   int64
	Digest       string
	Plans        []string
	SchemaName   string
}

type StatementsModel struct {
	BeginTime                      int     `json:"summary_begin_time,omitempty"`
	EndTime                        int     `json:"summary_end_time,omitempty"`
	DigestText                     string  `json:"digest_text,omitempty"`
	Digest                         string  `json:"digest,omitempty"`
	ExecCount                      int     `json:"exec_count,omitempty"`
	StmtType                       string  `json:"stmt_type,omitempty"`
	SumErrors                      int     `json:"sum_errors,omitempty"`
	SumWarnings                    int     `json:"sum_warnings,omitempty"`
	SumLatency                     int     `json:"sum_latency,omitempty"`
	MaxLatency                     int     `json:"max_latency,omitempty"`
	MinLatency                     int     `json:"min_latency,omitempty"`
	AvgLatency                     int     `json:"avg_latency,omitempty"`
	AvgParseLatency                int     `json:"avg_parse_latency,omitempty"`
	MaxParseLatency                int     `json:"max_parse_latency,omitempty"`
	AvgCompileLatency              int     `json:"avg_compile_latency,omitempty"`
	MaxCompileLatency              int     `json:"max_compile_latency,omitempty"`
	SumCopTaskNum                  int     `json:"sum_cop_task_num,omitempty"`
	MaxCopProcessTime              int     `json:"max_cop_process_time,omitempty"`
	MaxCopWaitTime                 int     `json:"max_cop_wait_time,omitempty"`
	AvgProcessTime                 int     `json:"avg_process_time,omitempty"`
	MaxProcessTime                 int     `json:"max_process_time,omitempty"`
	AvgWaitTime                    int     `json:"avg_wait_time,omitempty"`
	MaxWaitTime                    int     `json:"max_wait_time,omitempty"`
	AvgBackoffTime                 int     `json:"avg_backoff_time,omitempty"`
	MaxBackoffTime                 int     `json:"max_backoff_time,omitempty"`
	AvgTotalKeys                   int     `json:"avg_total_keys,omitempty"`
	MaxTotalKeys                   int     `json:"max_total_keys,omitempty"`
	AvgProcessedKeys               int     `json:"avg_processed_keys,omitempty"`
	MaxProcessedKeys               int     `json:"max_processed_keys,omitempty"`
	AvgPrewriteTime                int     `json:"avg_prewrite_time,omitempty"`
	MaxPrewriteTime                int     `json:"max_prewrite_time,omitempty"`
	AvgCommitTime                  int     `json:"avg_commit_time,omitempty"`
	MaxCommitTime                  int     `json:"max_commit_time,omitempty"`
	AvgGetCommitTsTime             int     `json:"avg_get_commit_ts_time,omitempty"`
	MaxGetCommitTsTime             int     `json:"max_get_commit_ts_time,omitempty"`
	AvgCommitBackoffTime           int     `json:"avg_commit_backoff_time,omitempty"`
	MaxCommitBackoffTime           int     `json:"max_commit_backoff_time,omitempty"`
	AvgResolveLockTime             int     `json:"avg_resolve_lock_time,omitempty"`
	MaxResolveLockTime             int     `json:"max_resolve_lock_time,omitempty"`
	AvgLocalLatchWaitTime          int     `json:"avg_local_latch_wait_time,omitempty"`
	MaxLocalLatchWaitTime          int     `json:"max_local_latch_wait_time,omitempty"`
	AvgWriteKeys                   int     `json:"avg_write_keys,omitempty"`
	MaxWriteKeys                   int     `json:"max_write_keys,omitempty"`
	AvgWriteSize                   int     `json:"avg_write_size,omitempty"`
	MaxWriteSize                   int     `json:"max_write_size,omitempty"`
	AvgPrewriteRegions             int     `json:"avg_prewrite_regions,omitempty"`
	MaxPrewriteRegions             int     `json:"max_prewrite_regions,omitempty"`
	AvgTxnRetry                    int     `json:"avg_txn_retry,omitempty"`
	MaxTxnRetry                    int     `json:"max_txn_retry,omitempty"`
	SumBackoffTimes                int     `json:"sum_backoff_times,omitempty"`
	AvgMem                         int     `json:"avg_mem,omitempty"`
	MaxMem                         int     `json:"max_mem,omitempty"`
	AvgDisk                        int     `json:"avg_disk,omitempty"`
	MaxDisk                        int     `json:"max_disk,omitempty"`
	AvgAffectedRows                int     `json:"avg_affected_rows,omitempty"`
	FirstSeen                      int     `json:"first_seen,omitempty"`
	LastSeen                       int     `json:"last_seen,omitempty"`
	SampleUser                     string  `json:"sample_user,omitempty"`
	QuerySampleText                string  `json:"query_sample_text,omitempty"`
	PrevSampleText                 string  `json:"prev_sample_text,omitempty"`
	SchemaName                     string  `json:"schema_name,omitempty"`
	TableNames                     string  `json:"table_names,omitempty"`
	IndexNames                     string  `json:"index_names,omitempty"`
	PlanCount                      int     `json:"plan_count,omitempty"`
	Plan                           string  `json:"plan,omitempty"`
	PlanDigest                     string  `json:"plan_digest,omitempty"`
	BinaryPlan                     string  `json:"binary_plan,omitempty"`
	BinaryPlanText                 string  `json:"binary_plan_text,omitempty"`
	PlanHint                       string  `json:"plan_hint,omitempty"`
	PlanInBinding                  bool    `json:"plan_in_binding,omitempty"`
	MaxRocksdbDeleteSkippedCount   uint    `json:"max_rocksdb_delete_skipped_count,omitempty"`
	AvgRocksdbDeleteSkippedCount   uint    `json:"avg_rocksdb_delete_skipped_count,omitempty"`
	MaxRocksdbKeySkippedCount      uint    `json:"max_rocksdb_key_skipped_count,omitempty"`
	AvgRocksdbKeySkippedCount      uint    `json:"avg_rocksdb_key_skipped_count,omitempty"`
	MaxRocksdbBlockCacheHitCount   uint    `json:"max_rocksdb_block_cache_hit_count,omitempty"`
	AvgRocksdbBlockCacheHitCount   uint    `json:"avg_rocksdb_block_cache_hit_count,omitempty"`
	MaxRocksdbBlockReadCount       uint    `json:"max_rocksdb_block_read_count,omitempty"`
	AvgRocksdbBlockReadCount       uint    `json:"avg_rocksdb_block_read_count,omitempty"`
	MaxRocksdbBlockReadByte        uint    `json:"max_rocksdb_block_read_byte,omitempty"`
	AvgRocksdbBlockReadByte        uint    `json:"avg_rocksdb_block_read_byte,omitempty"`
	SumRu                          float64 `json:"sum_ru,omitempty"`
	SumRuV2                        float64 `json:"sum_ru_v2,omitempty"`
	AvgRuV2                        float64 `json:"avg_ru_v2,omitempty"`
	MaxRuV2                        float64 `json:"max_ru_v2,omitempty"`
	SumRru                         float64 `json:"sum_rru,omitempty"`
	SumWru                         float64 `json:"sum_wru,omitempty"`
	SumRUWaitDuration              int64   `json:"sum_ru_wait_duration,omitempty"`
	AvgRu                          float64 `json:"avg_ru,omitempty"`
	AvgRru                         float64 `json:"avg_rru,omitempty"`
	AvgWru                         float64 `json:"avg_wru,omitempty"`
	MaxRRU                         float64 `json:"max_rru,omitempty"`
	MaxWRU                         float64 `json:"max_wru,omitempty"`
	MaxRUWaitDuration              int64   `json:"max_ru_wait_duration,omitempty"`
	SumTidbCPU                     int64   `json:"sum_tidb_cpu,omitempty"`
	AvgTidbCPU                     float64 `json:"avg_tidb_cpu,omitempty"`
	SumTikvCPU                     int64   `json:"sum_tikv_cpu,omitempty"`
	AvgTikvCPU                     float64 `json:"avg_tikv_cpu,omitempty"`
	PlanCacheHits                  int64   `json:"plan_cache_hits,omitempty"`
	PlanInCache                    bool    `json:"plan_in_cache,omitempty"`
	PlanCacheUnqualifiedCount      int64   `json:"plan_cache_unqualified_count,omitempty"`
	PlanCacheUnqualifiedLastReason string  `json:"plan_cache_unqualified_last_reason,omitempty"`
	SumIAReadSegmentCount          int64   `json:"sum_ia_read_segment_count,omitempty"`
	AvgIAReadSegmentCount          float64 `json:"avg_ia_read_segment_count,omitempty"`
	MaxIAReadSegmentCount          int64   `json:"max_ia_read_segment_count,omitempty"`
	SumIARemoteReadSegmentSize     int64   `json:"sum_ia_remote_read_segment_size,omitempty"`
	AvgIARemoteReadSegmentSize     float64 `json:"avg_ia_remote_read_segment_size,omitempty"`
	MaxIARemoteReadSegmentSize     int64   `json:"max_ia_remote_read_segment_size,omitempty"`
	SumIARemoteReadSegmentWaitTime int64   `json:"sum_ia_remote_read_segment_wait_time,omitempty"`
	AvgIARemoteReadSegmentWaitTime float64 `json:"avg_ia_remote_read_segment_wait_time,omitempty"`
	MaxIARemoteReadSegmentWaitTime int64   `json:"max_ia_remote_read_segment_wait_time,omitempty"`
}

type StatementsLog struct {
	Digest       string `json:"digest"`
	Query        string `json:"query"`
	Instance     string `json:"instance"`
	DB           string `json:"db"`
	ConnectionID string `json:"connection_id"`
}

var StatementAvailableFields = []string{
	"summary_begin_time",
	"summary_end_time",
	"digest_text",
	"digest",
	"exec_count",
	"stmt_type",
	"sum_errors",
	"sum_warnings",
	"sum_latency",
	"max_latency",
	"min_latency",
	"avg_latency",
	"avg_parse_latency",
	"max_parse_latency",
	"avg_compile_latency",
	"max_compile_latency",
	"sum_cop_task_num",
	"max_cop_process_time",
	"max_cop_wait_time",
	"avg_process_time",
	"max_process_time",
	"avg_wait_time",
	"max_wait_time",
	"avg_backoff_time",
	"max_backoff_time",
	"avg_total_keys",
	"max_total_keys",
	"avg_processed_keys",
	"max_processed_keys",
	"avg_prewrite_time",
	"max_prewrite_time",
	"avg_commit_time",
	"max_commit_time",
	"avg_get_commit_ts_time",
	"max_get_commit_ts_time",
	"avg_commit_backoff_time",
	"max_commit_backoff_time",
	"avg_resolve_lock_time",
	"max_resolve_lock_time",
	"avg_local_latch_wait_time",
	"max_local_latch_wait_time",
	"avg_write_keys",
	"max_write_keys",
	"avg_write_size",
	"max_write_size",
	"avg_prewrite_regions",
	"max_prewrite_regions",
	"avg_txn_retry",
	"max_txn_retry",
	"sum_backoff_times",
	"avg_mem",
	"max_mem",
	"avg_disk",
	"max_disk",
	"avg_affected_rows",
	"first_seen",
	"last_seen",
	"sample_user",
	"user",
	"query_sample_text",
	"prev_sample_text",
	"schema_name",
	"table_names",
	"index_names",
	"plan_count",
	"plan",
	"plan_digest",
	"binary_plan",
	"plan_hint",
	"plan_in_binding",
	"max_rocksdb_delete_skipped_count",
	"avg_rocksdb_delete_skipped_count",
	"max_rocksdb_key_skipped_count",
	"avg_rocksdb_key_skipped_count",
	"max_rocksdb_block_cache_hit_count",
	"avg_rocksdb_block_cache_hit_count",
	"max_rocksdb_block_read_count",
	"avg_rocksdb_block_read_count",
	"max_rocksdb_block_read_byte",
	"avg_rocksdb_block_read_byte",
	"sum_ru",
	"sum_ru_v2",
	"avg_ru_v2",
	"max_ru_v2",
	"sum_rru",
	"sum_wru",
	"sum_ru_wait_duration",
	"avg_ru",
	"avg_rru",
	"avg_wru",
	"max_rru",
	"max_wru",
	"max_ru_wait_duration",
	"sum_tidb_cpu",
	"avg_tidb_cpu",
	"sum_tikv_cpu",
	"avg_tikv_cpu",
	"plan_cache_hits",
	"plan_in_cache",
	"plan_cache_unqualified_count",
	"plan_cache_unqualified_last_reason",
	"sum_ia_read_segment_count",
	"avg_ia_read_segment_count",
	"max_ia_read_segment_count",
	"sum_ia_remote_read_segment_size",
	"avg_ia_remote_read_segment_size",
	"max_ia_remote_read_segment_size",
	"sum_ia_remote_read_segment_wait_time",
	"avg_ia_remote_read_segment_wait_time",
	"max_ia_remote_read_segment_wait_time",
}

var StatementsStmtTypes = []string{
	"AlterTable",
	"AnalyzeTable",
	"Begin",
	"Change",
	"Commit",
	"CompactTable",
	"CreateDatabase",
	"CreateIndex",
	"CreateTable",
	"CreateView",
	"CreateUser",
	"Delete",
	"DropDatabase",
	"DropIndex",
	"DropView",
	"DropTable",
	"DescTable",
	"ExplainAnalyzeSQL",
	"ExplainSQL",
	"Replace",
	"Insert",
	"LoadData",
	"Rollback",
	"Select",
	"Set",
	"Show",
	"TruncateTable",
	"Update",
	"Grant",
	"Revoke",
	"Deallocate",
	"Execute",
	"Prepare",
	"Use",
	"CreateBinding",
	"IndexAdvise",
	"DropBinding",
	"Trace",
	"Shutdown",
	"Savepoint",
	"other",
}