package dbstore

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

	"git.linuxforward.com/byop/byop-engine/models"
	"github.com/pkg/errors"
)

// Deployment operations
func (s *SQLiteStore) CreateDeployment(ctx context.Context, deployment models.Deployment) (int, error) {
	query := `INSERT INTO deployments (app_id, client_id, name, description, environment, status, url, config, deployed_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
	deployedAt := sql.NullTime{}
	if !deployment.DeployedAt.IsZero() {
		deployedAt.Time = deployment.DeployedAt
		deployedAt.Valid = true
	}

	result, err := s.db.ExecContext(ctx, query, deployment.AppId, deployment.ClientID, deployment.Name, deployment.Description, deployment.Environment, deployment.Status, deployment.URL, deployment.Config, deployedAt)
	if err != nil {
		return 0, models.NewErrInternalServer("failed to create deployment", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, models.NewErrInternalServer("failed to get last insert ID for deployment", err)
	}

	return int(id), nil
}

func (s *SQLiteStore) GetDeploymentsByAppID(ctx context.Context, appID int) ([]*models.Deployment, error) {
	query := `SELECT id, app_id, client_id, name, description, environment, status, url, config, deployed_at, created_at, updated_at FROM deployments WHERE app_id = ?`
	rows, err := s.db.QueryContext(ctx, query, appID)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to query deployments for app ID %d", appID), err)
	}
	defer rows.Close()

	var deployments []*models.Deployment
	for rows.Next() {
		var deployment models.Deployment
		var deployedAt sql.NullTime
		err := rows.Scan(&deployment.ID, &deployment.AppId, &deployment.ClientID, &deployment.Name, &deployment.Description, &deployment.Environment, &deployment.Status, &deployment.URL, &deployment.Config, &deployedAt, &deployment.CreatedAt, &deployment.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan deployment row", err)
		}
		if deployedAt.Valid {
			deployment.DeployedAt = deployedAt.Time
		}
		deployments = append(deployments, &deployment)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating deployment rows for app ID %d", appID), err)
	}

	return deployments, nil
}

func (s *SQLiteStore) GetAllDeployments(ctx context.Context) ([]*models.Deployment, error) {
	query := `SELECT id, app_id, client_id, name, description, environment, status, url, config, deployed_at, created_at, updated_at FROM deployments`
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to query all deployments", err)
	}
	defer rows.Close()

	var deployments []*models.Deployment
	for rows.Next() {
		var deployment models.Deployment
		var deployedAt sql.NullTime
		err := rows.Scan(&deployment.ID, &deployment.AppId, &deployment.ClientID, &deployment.Name, &deployment.Description, &deployment.Environment, &deployment.Status, &deployment.URL, &deployment.Config, &deployedAt, &deployment.CreatedAt, &deployment.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan deployment row", err)
		}
		if deployedAt.Valid {
			deployment.DeployedAt = deployedAt.Time
		}
		deployments = append(deployments, &deployment)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating all deployment rows", err)
	}

	return deployments, nil
}

func (s *SQLiteStore) GetDeploymentByID(ctx context.Context, id int) (*models.Deployment, error) {
	var deployment models.Deployment
	var deployedAt sql.NullTime
	query := `SELECT id, app_id, client_id, name, description, environment, status, url, config, deployed_at, created_at, updated_at FROM deployments WHERE id = ?`
	err := s.db.QueryRowContext(ctx, query, id).Scan(&deployment.ID, &deployment.AppId, &deployment.ClientID, &deployment.Name, &deployment.Description, &deployment.Environment, &deployment.Status, &deployment.URL, &deployment.Config, &deployedAt, &deployment.CreatedAt, &deployment.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, models.NewErrNotFound(fmt.Sprintf("deployment with ID %d not found", id), err)
		}
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get deployment with ID %d", id), err)
	}
	if deployedAt.Valid {
		deployment.DeployedAt = deployedAt.Time
	}
	return &deployment, nil
}

func (s *SQLiteStore) UpdateDeployment(ctx context.Context, deployment *models.Deployment) error {
	deployedAt := sql.NullTime{}
	if !deployment.DeployedAt.IsZero() {
		deployedAt.Time = deployment.DeployedAt
		deployedAt.Valid = true
	}

	query := `UPDATE deployments SET app_id = ?, client_id = ?, name = ?, description = ?, environment = ?, status = ?, url = ?, config = ?, deployed_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, deployment.AppId, deployment.ClientID, deployment.Name, deployment.Description, deployment.Environment, deployment.Status, deployment.URL, deployment.Config, deployedAt, deployment.ID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update deployment with ID %d", deployment.ID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for deployment update ID %d", deployment.ID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("deployment with ID %d not found for update", deployment.ID), nil)
	}
	return nil
}

// DeleteDeployment deletes a deployment by ID with verification checks
func (s *SQLiteStore) DeleteDeployment(ctx context.Context, id int) error {
	// First check if the deployment exists
	deployment, err := s.GetDeploymentByID(ctx, id)
	if err != nil {
		return err // GetDeploymentByID already returns custom errors
	}

	// Check if deployment is currently running
	if deployment.Status == "running" || deployment.Status == "deploying" {
		return models.NewErrConflict(fmt.Sprintf("cannot delete deployment: it is currently %s. Please stop the deployment first", deployment.Status), nil)
	}

	// Proceed with deletion
	query := `DELETE FROM deployments WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, id)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to delete deployment with ID %d", id), err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for deployment deletion ID %d", id), err)
	}
	if rowsAffected == 0 {
		// This case should ideally be caught by GetDeploymentByID earlier, but as a safeguard:
		return models.NewErrNotFound(fmt.Sprintf("deployment with ID %d not found for deletion", id), nil)
	}

	return nil
}

// GetDeploymentsByClientID retrieves all deployments for a given client ID
func (s *SQLiteStore) GetDeploymentsByClientID(ctx context.Context, clientID int) ([]*models.Deployment, error) {
	query := `SELECT id, app_id, client_id, name, description, environment, status, url, config, deployed_at, created_at, updated_at FROM deployments WHERE client_id = ?`
	rows, err := s.db.QueryContext(ctx, query, clientID)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to query deployments for client ID %d", clientID), err)
	}
	defer rows.Close()

	var deployments []*models.Deployment
	for rows.Next() {
		var deployment models.Deployment
		var deployedAt sql.NullTime
		err := rows.Scan(&deployment.ID, &deployment.AppId, &deployment.ClientID, &deployment.Name, &deployment.Description, &deployment.Environment, &deployment.Status, &deployment.URL, &deployment.Config, &deployedAt, &deployment.CreatedAt, &deployment.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan deployment row for client", err)
		}
		if deployedAt.Valid {
			deployment.DeployedAt = deployedAt.Time
		}
		deployments = append(deployments, &deployment)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating deployment rows for client ID %d", clientID), err)
	}

	return deployments, nil
}

// GetDeploymentsByUserID retrieves all deployments for a given user ID
// This assumes a link between deployments and users, which might not be direct.
// If deployments are linked via apps, and apps via users, this query would need to be more complex
// or a direct user_id column added to deployments.
// For now, assuming a direct user_id on deployments or this is handled via a join in a more complex setup.
// If UserID is not directly on the deployments table, this will need adjustment.
// For this example, let's assume there's a user_id on the apps table, and we join through it.
// This requires an `apps` table with `user_id` and `id` (app_id in deployments).
func (s *SQLiteStore) GetDeploymentsByUserID(ctx context.Context, userID int) ([]*models.Deployment, error) {
	// This query assumes deployments are linked to users via the 'apps' table.
	// Adjust if your schema is different (e.g., direct user_id on deployments).
	query := `
		SELECT d.id, d.app_id, d.client_id, d.name, d.description, d.environment, d.status, d.url, d.config, d.deployed_at, d.created_at, d.updated_at 
		FROM deployments d
		INNER JOIN apps a ON d.app_id = a.id
		WHERE a.user_id = ?`

	rows, err := s.db.QueryContext(ctx, query, userID)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to query deployments for user ID %d", userID), err)
	}
	defer rows.Close()

	var deployments []*models.Deployment
	for rows.Next() {
		var deployment models.Deployment
		var deployedAt sql.NullTime
		err := rows.Scan(&deployment.ID, &deployment.AppId, &deployment.ClientID, &deployment.Name, &deployment.Description, &deployment.Environment, &deployment.Status, &deployment.URL, &deployment.Config, &deployedAt, &deployment.CreatedAt, &deployment.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan deployment row for user", err)
		}
		if deployedAt.Valid {
			deployment.DeployedAt = deployedAt.Time
		}
		deployments = append(deployments, &deployment)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating deployment rows for user ID %d", userID), err)
	}

	return deployments, nil
}