package dbstore

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

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

// App operations
func (s *SQLiteStore) CreateApp(ctx context.Context, app *models.App) (int, error) {
	// Convert components slice to JSON
	componentsJSON, err := json.Marshal(app.Components)
	if err != nil {
		return 0, models.NewErrInternalServer("failed to marshal app components", err)
	}

	// Handle preview_id: if 0, pass NULL to database
	var previewID interface{}
	if app.PreviewID == 0 {
		previewID = nil
	} else {
		previewID = app.PreviewID
	}

	query := `INSERT INTO apps (user_id, name, description, status, components, preview_id, preview_url, current_image_tag, current_image_uri, error_msg, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	now := time.Now().Format(time.RFC3339)
	result, err := s.db.ExecContext(ctx, query, app.UserID, app.Name, app.Description, app.Status, string(componentsJSON), previewID, app.PreviewURL, app.CurrentImageTag, app.CurrentImageURI, app.ErrorMsg, now, now)
	if err != nil {
		return 0, models.NewErrInternalServer("failed to create app", err)
	}

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

	return int(id), nil
}

func (s *SQLiteStore) GetAllApps(ctx context.Context) ([]*models.App, error) {
	query := `SELECT id, user_id, name, description, status, components, preview_id, preview_url, current_image_tag, current_image_uri, error_msg, created_at, updated_at FROM apps`
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to query apps", err)
	}
	defer rows.Close()

	var apps []*models.App
	for rows.Next() {
		var app models.App
		var componentsJSON string
		var previewID sql.NullInt64
		err := rows.Scan(&app.ID, &app.UserID, &app.Name, &app.Description, &app.Status, &componentsJSON, &previewID, &app.PreviewURL, &app.CurrentImageTag, &app.CurrentImageURI, &app.ErrorMsg, &app.CreatedAt, &app.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan app row", err)
		}

		// Handle nullable preview_id
		if previewID.Valid {
			app.PreviewID = int(previewID.Int64)
		} else {
			app.PreviewID = 0
		}

		// Parse components JSON
		if componentsJSON != "" {
			err := json.Unmarshal([]byte(componentsJSON), &app.Components)
			if err != nil {
				return nil, models.NewErrInternalServer("failed to unmarshal app components", err)
			}
		} else {
			app.Components = []int{} // Initialize as empty slice if null
		}

		apps = append(apps, &app)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating app rows", err)
	}

	return apps, nil
}

// GetAppByID retrieves a single app by ID
func (s *SQLiteStore) GetAppByID(ctx context.Context, id int) (*models.App, error) {
	query := `SELECT id, user_id, name, description, status, components, preview_id, preview_url, current_image_tag, current_image_uri, error_msg, created_at, updated_at FROM apps WHERE id = ?`

	var app models.App
	var componentsJSON string
	var previewID sql.NullInt64
	err := s.db.QueryRowContext(ctx, query, id).Scan(&app.ID, &app.UserID, &app.Name, &app.Description, &app.Status, &componentsJSON, &previewID, &app.PreviewURL, &app.CurrentImageTag, &app.CurrentImageURI, &app.ErrorMsg, &app.CreatedAt, &app.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, models.NewErrNotFound(fmt.Sprintf("app with ID %d not found", id), err)
		}
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get app with ID %d", id), err)
	}

	// Handle nullable preview_id
	if previewID.Valid {
		app.PreviewID = int(previewID.Int64)
	} else {
		app.PreviewID = 0
	}

	// Parse components JSON
	if componentsJSON != "" {
		err := json.Unmarshal([]byte(componentsJSON), &app.Components)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to unmarshal app components for app ID "+fmt.Sprint(id), err)
		}
	} else {
		app.Components = []int{} // Initialize as empty slice if null
	}

	return &app, nil
}

// UpdateApp updates an existing app
func (s *SQLiteStore) UpdateApp(ctx context.Context, app *models.App) error {
	// Convert components slice to JSON
	componentsJSON, err := json.Marshal(app.Components)
	if err != nil {
		return models.NewErrInternalServer("failed to marshal app components for update", err)
	}

	// Handle preview_id: if 0, pass NULL to database
	var previewID interface{}
	if app.PreviewID == 0 {
		previewID = nil
	} else {
		previewID = app.PreviewID
	}

	query := `UPDATE apps SET user_id = ?, name = ?, description = ?, status = ?, components = ?, preview_id = ?, preview_url = ?, current_image_tag = ?, current_image_uri = ?, error_msg = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, app.UserID, app.Name, app.Description, app.Status, string(componentsJSON), previewID, app.PreviewURL, app.CurrentImageTag, app.CurrentImageURI, app.ErrorMsg, app.ID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update app with ID %d", app.ID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for app update ID %d", app.ID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("app with ID %d not found for update", app.ID), nil)
	}

	return nil
}

// DeleteApp deletes an app by ID with verification checks
func (s *SQLiteStore) DeleteApp(ctx context.Context, id int) error {
	// First check if the app exists
	_, err := s.GetAppByID(ctx, id)
	if err != nil {
		return err
	}

	// Check if the app is used in any deployments
	deployments, err := s.GetDeploymentsByAppID(ctx, id)
	if err != nil {
		var nfErr *models.ErrNotFound
		if errors.As(err, &nfErr) {
		} else {
			return models.NewErrInternalServer(fmt.Sprintf("failed to check app deployments for app ID %d", id), err)
		}
	}
	if len(deployments) > 0 {
		return models.NewErrConflict(fmt.Sprintf("cannot delete app: it is used in %d deployment(s). Please delete the deployments first", len(deployments)), nil)
	}

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

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for app deletion ID %d", id), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("app with ID %d not found for deletion", id), nil)
	}

	return nil
}

func (s *SQLiteStore) UpdateAppStatus(ctx context.Context, appID int, status, errorMsg string) error {
	query := `UPDATE apps SET status = ?, error_msg = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, status, errorMsg, appID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update app status for ID %d", appID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for app status update ID %d", appID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("app with ID %d not found for status update", appID), nil)
	}
	return nil
}

// UpdateAppCurrentImage updates the current image tag and URI for an app.
func (s *SQLiteStore) UpdateAppCurrentImage(ctx context.Context, appID int, imageTag string, imageURI string) error {
	query := `UPDATE apps SET current_image_tag = ?, current_image_uri = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`
	_, err := s.db.ExecContext(ctx, query, imageTag, imageURI, appID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update app current image for ID %d", appID), err)
	}
	return nil
}