package organization

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

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

type organizationLLMProxyKeyRecord struct {
	ID              string
	OrganizationID  string
	Label           string
	TokenHash       string
	TokenHint       string
	Status          string
	LastUsedAt      sql.NullTime
	LastErrorAt     sql.NullTime
	LastErrorCode   string
	CreatedByUserID string
	CreatedAt       time.Time
	UpdatedAt       time.Time
}

func (txs TxStore) ListOrganizationLLMProxyKeysForUser(ctx context.Context, userID string, organizationID string) ([]OrganizationLLMProxyKeyView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	rows, err := txs.db.Query(ctx, `
SELECT id, organization_id, label, token_hint, status, last_used_at, last_error_at, last_error_code, created_at, updated_at
FROM organization_llm_proxy_keys
WHERE organization_id = $1
ORDER BY created_at ASC, id ASC
`, organizationID)
	if err != nil {
		return nil, fmt.Errorf("list organization llm proxy keys: %w", err)
	}
	defer rows.Close()

	items := make([]OrganizationLLMProxyKeyView, 0)
	for rows.Next() {
		item, err := scanOrganizationLLMProxyKeyView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan organization llm proxy key: %w", err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization llm proxy keys: %w", err)
	}
	return items, nil
}

func (txs TxStore) CreateOrganizationLLMProxyKeyForUser(ctx context.Context, userID string, organizationID string, params CreateOrganizationLLMProxyKeyParams) (OrganizationLLMProxyKeyCreateResult, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationLLMProxyKeyCreateResult{}, err
	}
	return txs.createOrganizationLLMProxyKey(ctx, organizationID, strings.TrimSpace(params.Label), userID)
}

func (txs TxStore) DeleteOrganizationLLMProxyKeyForUser(ctx context.Context, userID string, organizationID string, keyID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	result, err := txs.db.Exec(ctx, `
DELETE FROM organization_llm_proxy_keys
WHERE organization_id = $1
  AND id = $2
`, organizationID, keyID)
	if err != nil {
		return fmt.Errorf("delete organization llm proxy key: %w", err)
	}
	if result.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) createOrganizationLLMProxyKey(ctx context.Context, organizationID string, label string, createdByUserID string) (OrganizationLLMProxyKeyCreateResult, error) {
	if !txs.hasOrganizationProxyKeySigningKey() {
		return OrganizationLLMProxyKeyCreateResult{}, badRequest("organization llm proxy key storage is not configured on server")
	}
	if label == "" {
		return OrganizationLLMProxyKeyCreateResult{}, badRequest("label is required")
	}
	keyID, err := identifiers.NewEntityID("olpk")
	if err != nil {
		return OrganizationLLMProxyKeyCreateResult{}, fmt.Errorf("generate organization llm proxy key id: %w", err)
	}
	plaintext, err := secrets.NewToken(32)
	if err != nil {
		return OrganizationLLMProxyKeyCreateResult{}, fmt.Errorf("generate organization llm proxy key token: %w", err)
	}
	now := time.Now().UTC()
	if _, err := txs.db.Exec(ctx, `
INSERT INTO organization_llm_proxy_keys (
    id,
    organization_id,
    label,
    token_hash,
    token_hint,
    status,
    created_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $8)
`, keyID, organizationID, label, secrets.HashToken(plaintext), tokenHint(plaintext), organizationLLMProxyKeyStatusActive, createdByUserID, now); err != nil {
		return OrganizationLLMProxyKeyCreateResult{}, fmt.Errorf("insert organization llm proxy key: %w", err)
	}
	view, err := txs.loadOrganizationLLMProxyKey(ctx, txs.db, organizationID, keyID)
	if err != nil {
		return OrganizationLLMProxyKeyCreateResult{}, err
	}
	return OrganizationLLMProxyKeyCreateResult{Key: view, Token: plaintext}, nil
}

func (txs TxStore) loadOrganizationLLMProxyKey(ctx context.Context, queryer queryRower, organizationID string, keyID string) (OrganizationLLMProxyKeyView, error) {
	row := queryer.QueryRow(ctx, `
SELECT id, organization_id, label, token_hint, status, last_used_at, last_error_at, last_error_code, created_at, updated_at
FROM organization_llm_proxy_keys
WHERE organization_id = $1
  AND id = $2
`, organizationID, keyID)
	item, err := scanOrganizationLLMProxyKeyView(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationLLMProxyKeyView{}, ErrNotFound
		}
		return OrganizationLLMProxyKeyView{}, fmt.Errorf("load organization llm proxy key: %w", err)
	}
	return item, nil
}

func (txs TxStore) loadOrganizationLLMProxyKeyByTokenHash(ctx context.Context, tokenHash string) (organizationLLMProxyKeyRecord, error) {
	row := txs.db.QueryRow(ctx, `
SELECT id, organization_id, label, token_hash, token_hint, status, last_used_at, last_error_at, last_error_code, created_by_user_id, created_at, updated_at
FROM organization_llm_proxy_keys
WHERE token_hash = $1
  AND status = $2
`, tokenHash, organizationLLMProxyKeyStatusActive)
	item, err := scanOrganizationLLMProxyKeyRecord(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return organizationLLMProxyKeyRecord{}, ErrNotFound
		}
		return organizationLLMProxyKeyRecord{}, fmt.Errorf("load organization llm proxy key: %w", err)
	}
	return item, nil
}

func (txs TxStore) listActiveOrganizationProxyAccessScope(ctx context.Context, organizationID string) ([]string, []string, error) {
	providerRows, err := txs.db.Query(ctx, `
SELECT
    oprgp.provider_id
FROM organization_provider_route_group_provider_refs oprgp
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgp.organization_id
 AND oprg.route_id = oprgp.route_id
 AND oprg.id = oprgp.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprgp.organization_id
 AND opr.id = oprgp.route_id
JOIN organization_providers op
  ON op.organization_id = oprgp.organization_id
 AND op.id = oprgp.provider_id
WHERE oprgp.organization_id = $1
  AND oprg.status = $2
  AND opr.status = $2
  AND op.status = $2
GROUP BY oprgp.provider_id
ORDER BY MIN(oprg.priority) ASC, MIN(oprgp.ref_order) ASC, oprgp.provider_id ASC
`, organizationID, organizationProviderStatusActive)
	if err != nil {
		return nil, nil, fmt.Errorf("list active organization provider refs for proxy scope: %w", err)
	}
	defer providerRows.Close()

	allowedProviderIDs := make([]string, 0)
	for providerRows.Next() {
		var providerID string
		if err := providerRows.Scan(&providerID); err != nil {
			return nil, nil, fmt.Errorf("scan active organization provider ref for proxy scope: %w", err)
		}
		if trimmedProviderID := strings.TrimSpace(providerID); trimmedProviderID != "" {
			allowedProviderIDs = append(allowedProviderIDs, trimmedProviderID)
		}
	}
	if err := providerRows.Err(); err != nil {
		return nil, nil, fmt.Errorf("iterate active organization provider refs for proxy scope: %w", err)
	}

	modelRows, err := txs.db.Query(ctx, `
SELECT
    oprgm.model_id
FROM organization_provider_route_group_model_refs oprgm
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgm.organization_id
 AND oprg.route_id = oprgm.route_id
 AND oprg.id = oprgm.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprgm.organization_id
 AND opr.id = oprgm.route_id
JOIN organization_provider_route_group_provider_refs oprgp
  ON oprgp.organization_id = oprgm.organization_id
 AND oprgp.route_id = oprgm.route_id
 AND oprgp.route_group_id = oprgm.route_group_id
JOIN organization_providers op
  ON op.organization_id = oprgp.organization_id
 AND op.id = oprgp.provider_id
WHERE oprgm.organization_id = $1
  AND oprg.status = $2
  AND opr.status = $2
  AND op.status = $2
GROUP BY oprgm.model_id
ORDER BY MIN(oprg.priority) ASC, MIN(oprgm.ref_order) ASC, oprgm.model_id ASC
`, organizationID, organizationProviderStatusActive)
	if err != nil {
		return nil, nil, fmt.Errorf("list active organization route group model refs for proxy scope: %w", err)
	}
	defer modelRows.Close()

	allowedModels := make([]string, 0)
	for modelRows.Next() {
		var modelID string
		if err := modelRows.Scan(&modelID); err != nil {
			return nil, nil, fmt.Errorf("scan active organization route group model ref for proxy scope: %w", err)
		}
		if trimmedModelID := strings.TrimSpace(modelID); trimmedModelID != "" {
			allowedModels = append(allowedModels, trimmedModelID)
		}
	}
	if err := modelRows.Err(); err != nil {
		return nil, nil, fmt.Errorf("iterate active organization route group model refs for proxy scope: %w", err)
	}
	return allowedProviderIDs, allowedModels, nil
}

func scanOrganizationLLMProxyKeyView(scanner interface{ Scan(...any) error }) (OrganizationLLMProxyKeyView, error) {
	var item OrganizationLLMProxyKeyView
	var lastUsedAt sql.NullTime
	var lastErrorAt sql.NullTime
	if err := scanner.Scan(
		&item.ID,
		&item.OrganizationID,
		&item.Label,
		&item.TokenHint,
		&item.Status,
		&lastUsedAt,
		&lastErrorAt,
		&item.LastErrorCode,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return OrganizationLLMProxyKeyView{}, err
	}
	if lastUsedAt.Valid {
		t := lastUsedAt.Time.UTC()
		item.LastUsedAt = &t
	}
	if lastErrorAt.Valid {
		t := lastErrorAt.Time.UTC()
		item.LastErrorAt = &t
	}
	return item, nil
}

func scanOrganizationLLMProxyKeyRecord(scanner interface{ Scan(...any) error }) (organizationLLMProxyKeyRecord, error) {
	var item organizationLLMProxyKeyRecord
	if err := scanner.Scan(
		&item.ID,
		&item.OrganizationID,
		&item.Label,
		&item.TokenHash,
		&item.TokenHint,
		&item.Status,
		&item.LastUsedAt,
		&item.LastErrorAt,
		&item.LastErrorCode,
		&item.CreatedByUserID,
		&item.CreatedAt,
		&item.UpdatedAt,
	); err != nil {
		return organizationLLMProxyKeyRecord{}, err
	}
	return item, nil
}