package namedcredential

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

	"github.com/pingcap-inc/nexuscrm-connectors/internal/id"
)

// repository is the internal data-access interface for namedcredential.
// Defined as unexported so only the service layer can depend on it.
type repository interface {
	ListDefinitions(ctx context.Context) ([]Definition, error)
	GetDefinitionByDeveloperName(ctx context.Context, devName string) (*Definition, error)
	UpsertDefinition(ctx context.Context, def *Definition) error
	DeleteDefinition(ctx context.Context, devName string) error

	ListSecretMetadataByDefinitionID(ctx context.Context, defID string) ([]SecretMetadata, error)
	GetSecret(ctx context.Context, defID string, kind string) (*Secret, error)
	UpsertSecret(ctx context.Context, sec *Secret) error
}

// sqlRepository is the SQL-backed repository implementation.
type sqlRepository struct {
	db     *sql.DB
	cipher *SecretCipher // may be nil; see NewRepository doc comment
}

// NewRepository constructs a SQL-backed repository using the provided DB handle
// and SecretCipher. If cipher is nil, methods that touch SecretValue (UpsertSecret,
// GetSecret) fail with a clear error; methods that only touch Definition rows or
// count secrets remain functional. Production callers that need secret R/W must
// construct with a non-nil cipher.
func NewRepository(db *sql.DB, cipher *SecretCipher) repository {
	return &sqlRepository{db: db, cipher: cipher}
}

func (r *sqlRepository) ListDefinitions(ctx context.Context) ([]Definition, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT Id, DeveloperName, Label, Endpoint, PrincipalType, AuthType,
		        COALESCE(Description,''), COALESCE(OwnerPack,''), COALESCE(CreatedById,''),
		        CreatedDate, LastModifiedDate
		   FROM _System_NamedCredential
		  ORDER BY DeveloperName`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []Definition
	for rows.Next() {
		var d Definition
		if err := rows.Scan(
			&d.ID, &d.DeveloperName, &d.Label, &d.Endpoint, &d.PrincipalType, &d.AuthType,
			&d.Description, &d.OwnerPack, &d.CreatedByID,
			&d.CreatedDate, &d.LastModifiedDate,
		); err != nil {
			return nil, err
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

func (r *sqlRepository) GetDefinitionByDeveloperName(ctx context.Context, devName string) (*Definition, error) {
	row := r.db.QueryRowContext(ctx,
		`SELECT Id, DeveloperName, Label, Endpoint, PrincipalType, AuthType,
		        COALESCE(Description,''), COALESCE(OwnerPack,''), COALESCE(CreatedById,''),
		        CreatedDate, LastModifiedDate
		   FROM _System_NamedCredential
		  WHERE DeveloperName = ?`, devName)

	var d Definition
	err := row.Scan(
		&d.ID, &d.DeveloperName, &d.Label, &d.Endpoint, &d.PrincipalType, &d.AuthType,
		&d.Description, &d.OwnerPack, &d.CreatedByID,
		&d.CreatedDate, &d.LastModifiedDate,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &d, nil
}

func (r *sqlRepository) UpsertDefinition(ctx context.Context, def *Definition) error {
	now := time.Now().UTC()
	if def.ID == "" {
		def.ID = id.Generate()
	}
	if def.CreatedDate.IsZero() {
		def.CreatedDate = now
	}
	def.LastModifiedDate = now

	_, err := r.db.ExecContext(ctx,
		`INSERT INTO _System_NamedCredential
		        (Id, DeveloperName, Label, Endpoint, PrincipalType, AuthType,
		         Description, OwnerPack, CreatedById, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		 ON DUPLICATE KEY UPDATE
		        Label = VALUES(Label),
		        Endpoint = VALUES(Endpoint),
		        PrincipalType = VALUES(PrincipalType),
		        AuthType = VALUES(AuthType),
		        Description = VALUES(Description),
		        OwnerPack = VALUES(OwnerPack),
		        LastModifiedDate = VALUES(LastModifiedDate)`,
		def.ID, def.DeveloperName, def.Label, def.Endpoint,
		def.PrincipalType, def.AuthType,
		nullableString(def.Description), nullableString(def.OwnerPack),
		nullableString(def.CreatedByID),
		def.CreatedDate, def.LastModifiedDate,
	)
	return err
}

func (r *sqlRepository) DeleteDefinition(ctx context.Context, devName string) error {
	_, err := r.db.ExecContext(ctx,
		`DELETE FROM _System_NamedCredential WHERE DeveloperName = ?`, devName)
	return err
}

func (r *sqlRepository) ListSecretMetadataByDefinitionID(ctx context.Context, defID string) ([]SecretMetadata, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT SecretKind, COALESCE(MaskedHint,''), RotatedAt
		   FROM _System_NamedCredentialSecret
		  WHERE NamedCredentialId = ?
		  ORDER BY SecretKind`, defID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []SecretMetadata
	for rows.Next() {
		var m SecretMetadata
		if err := rows.Scan(&m.SecretKind, &m.MaskedHint, &m.RotatedAt); err != nil {
			return nil, err
		}
		out = append(out, m)
	}
	return out, rows.Err()
}

func (r *sqlRepository) GetSecret(ctx context.Context, defID string, kind string) (*Secret, error) {
	if r.cipher == nil {
		return nil, fmt.Errorf("namedcredential: decrypt requires SecretCipher (NEXUSCRM_NC_MASTER_KEY env var unset)")
	}
	row := r.db.QueryRowContext(ctx,
		`SELECT Id, NamedCredentialId, SecretKind, SecretValue,
		        COALESCE(MaskedHint,''), RotatedAt, CreatedDate, LastModifiedDate
		   FROM _System_NamedCredentialSecret
		  WHERE NamedCredentialId = ? AND SecretKind = ?`, defID, kind)

	var s Secret
	err := row.Scan(
		&s.ID, &s.NamedCredentialID, &s.SecretKind, &s.SecretValue,
		&s.MaskedHint, &s.RotatedAt, &s.CreatedDate, &s.LastModifiedDate,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	plaintext, err := r.cipher.Decrypt(s.SecretValue)
	if err != nil {
		return nil, fmt.Errorf("decrypt secret value: %w", err)
	}
	s.SecretValue = plaintext
	return &s, nil
}

func (r *sqlRepository) UpsertSecret(ctx context.Context, sec *Secret) error {
	if r.cipher == nil {
		return fmt.Errorf("namedcredential: encrypt requires SecretCipher (NEXUSCRM_NC_MASTER_KEY env var unset)")
	}
	now := time.Now().UTC()
	if sec.ID == "" {
		sec.ID = id.Generate()
	}
	if sec.CreatedDate.IsZero() {
		sec.CreatedDate = now
	}
	sec.LastModifiedDate = now
	sec.RotatedAt = now

	ciphertext, err := r.cipher.Encrypt(sec.SecretValue)
	if err != nil {
		return fmt.Errorf("encrypt secret value: %w", err)
	}

	_, err = r.db.ExecContext(ctx,
		`INSERT INTO _System_NamedCredentialSecret
		        (Id, NamedCredentialId, SecretKind, SecretValue, MaskedHint,
		         RotatedAt, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
		 ON DUPLICATE KEY UPDATE
		        SecretValue = VALUES(SecretValue),
		        MaskedHint = VALUES(MaskedHint),
		        RotatedAt = VALUES(RotatedAt),
		        LastModifiedDate = VALUES(LastModifiedDate)`,
		sec.ID, sec.NamedCredentialID, sec.SecretKind, ciphertext,
		nullableString(sec.MaskedHint),
		sec.RotatedAt, sec.CreatedDate, sec.LastModifiedDate,
	)
	return err
}

// nullableString converts an empty string to nil for nullable SQL columns.
func nullableString(s string) interface{} {
	if s == "" {
		return nil
	}
	return s
}