package persistence

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

	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// SessionRepository handles database operations for user sessions
type SessionRepository struct {
	db *sql.DB
}

// NewSessionRepository creates a new SessionRepository
func NewSessionRepository(db *sql.DB) *SessionRepository {
	return &SessionRepository{db: db}
}

// InsertSession creates a new session in the database
func (r *SessionRepository) InsertSession(ctx context.Context, session *models.SystemSession) error {
	values := map[string]interface{}{
		constants.FieldID:                        session.ID,
		constants.FieldSysSession_UserID:         session.UserID,
		constants.FieldSysSession_Token:          session.Token,
		constants.FieldSysSession_ExpiresAt:      session.ExpiresAt,
		constants.FieldSysSession_IPAddress:      session.IPAddress,
		constants.FieldSysSession_UserAgent:      session.UserAgent,
		constants.FieldSysSession_IsRevoked:      session.IsRevoked,
		constants.FieldSysSession_LastActivity:   session.LastActivity,
		constants.FieldSysSession_Scope:          session.Scope,
		constants.FieldSysSession_ConnectedAppID: session.ConnectedAppID,
		constants.FieldSysSession_GrantType:      session.GrantType,
		constants.FieldCreatedDate:               time.Now(),
		constants.FieldLastModifiedDate:          time.Now(),
	}

	qr := query.Insert(constants.TableSession, values).Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetSession retrieves a session by its ID (from JWT claim)
func (r *SessionRepository) GetSession(ctx context.Context, sessionID string) (*models.SystemSession, error) {
	qr := query.From(constants.TableSession).
		Select([]string{
			constants.FieldID, constants.FieldSysSession_UserID, constants.FieldSysSession_Token,
			constants.FieldSysSession_ExpiresAt, constants.FieldSysSession_IPAddress, constants.FieldSysSession_UserAgent,
			constants.FieldSysSession_IsRevoked, constants.FieldSysSession_LastActivity,
			constants.FieldSysSession_Scope, constants.FieldSysSession_ConnectedAppID,
			constants.FieldSysSession_GrantType,
			constants.FieldCreatedDate, constants.FieldLastModifiedDate,
		}).
		Where(constants.FieldID, sessionID).
		Limit(1).
		Build()

	var s models.SystemSession
	var createdDateRaw, lastModifiedDateRaw, lastActivityRaw []byte
	var scopeRaw, connectedAppIdRaw, grantTypeRaw sql.NullString

	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&s.ID,
		&s.UserID,
		&s.Token,
		&s.ExpiresAt,
		&s.IPAddress,
		&s.UserAgent,
		&s.IsRevoked,
		&lastActivityRaw,
		&scopeRaw,
		&connectedAppIdRaw,
		&grantTypeRaw,
		&createdDateRaw,
		&lastModifiedDateRaw,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	// Parse timestamps
	s.LastActivity = parseTime(lastActivityRaw)
	s.CreatedDate = parseTime(createdDateRaw)
	s.LastModifiedDate = parseTime(lastModifiedDateRaw)

	// Convert nullable strings
	if scopeRaw.Valid {
		scope := scopeRaw.String
		s.Scope = &scope
	}
	if connectedAppIdRaw.Valid {
		connectedAppID := connectedAppIdRaw.String
		s.ConnectedAppID = &connectedAppID
	}
	if grantTypeRaw.Valid {
		grantType := grantTypeRaw.String
		s.GrantType = &grantType
	}

	return &s, nil
}

// RevokeSession marks a session as revoked
func (r *SessionRepository) RevokeSession(ctx context.Context, sessionID string) error {
	updates := map[string]interface{}{
		constants.FieldSysSession_IsRevoked: true,
		constants.FieldLastModifiedDate:     time.Now(),
	}

	qr := query.Update(constants.TableSession).
		Set(updates).
		Where(constants.FieldID, sessionID).
		Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// RevokeAllSessionsForUser revokes all active sessions for a given user
func (r *SessionRepository) RevokeAllSessionsForUser(ctx context.Context, userID string) error {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldLastModifiedDate,
		constants.FieldSysSession_UserID,
		constants.FieldSysSession_IsRevoked,
	)
	_, err := r.db.ExecContext(ctx, sqlStr, userID)
	return err
}

// RevokeAllSessionsForConnectedApp revokes all active sessions for a given Connected App.
// Returns the number of rows affected.
func (r *SessionRepository) RevokeAllSessionsForConnectedApp(ctx context.Context, appID string) (int64, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = 0",
		constants.TableSession,
		constants.FieldSysSession_IsRevoked,
		constants.FieldLastModifiedDate,
		constants.FieldSysSession_ConnectedAppID,
		constants.FieldSysSession_IsRevoked,
	)
	result, err := r.db.ExecContext(ctx, sqlStr, appID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// UpdateLastActivity updates the last activity timestamp
func (r *SessionRepository) UpdateLastActivity(ctx context.Context, sessionID string) error {
	updates := map[string]interface{}{
		constants.FieldSysSession_LastActivity: time.Now(),
	}

	qr := query.Update(constants.TableSession).
		Set(updates).
		Where(constants.FieldID, sessionID).
		Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// CleanupExpiredSessions removes sessions that expired more than olderThan ago,
// plus all revoked sessions older than olderThan.
// Returns the number of deleted rows.
func (r *SessionRepository) CleanupExpiredSessions(ctx context.Context, olderThan time.Duration) (int64, error) {
	cutoff := time.Now().Add(-olderThan)
	sqlStr := fmt.Sprintf(
		"DELETE FROM `%s` WHERE (`%s` < ? OR `%s` = 1) AND `%s` < ?",
		constants.TableSession,
		constants.FieldSysSession_ExpiresAt,
		constants.FieldSysSession_IsRevoked,
		constants.FieldSysSession_CreatedDate,
	)
	result, err := r.db.ExecContext(ctx, sqlStr, cutoff, cutoff)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// IsConnectedAppActive checks whether a Connected App is still active by its ID.
func (r *SessionRepository) IsConnectedAppActive(ctx context.Context, appID string) (bool, error) {
	sqlStr := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = ? LIMIT 1",
		constants.ColSysConnectedApp_IsActive,
		constants.TableConnectedApp,
		constants.FieldID,
	)
	var isActive bool
	err := r.db.QueryRowContext(ctx, sqlStr, appID).Scan(&isActive)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil // App deleted = not active
		}
		return false, err
	}
	return isActive, nil
}