package dbstore

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

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

// CreatePreview creates a new preview record
func (s *SQLiteStore) CreatePreview(ctx context.Context, preview *models.Preview) (int, error) {
	query := `
        INSERT INTO previews (app_id, status, expires_at) 
        VALUES (?, ?, ?)
    `
	result, err := s.db.ExecContext(ctx, query, preview.AppID, preview.Status, preview.ExpiresAt)
	if err != nil {
		return 0, models.NewErrInternalServer("failed to create preview", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, models.NewErrInternalServer("failed to get preview ID after creation", err)
	}

	return int(id), nil
}

// GetPreviewByID retrieves a preview by ID
func (s *SQLiteStore) GetPreviewByID(ctx context.Context, id int) (*models.Preview, error) {
	preview := &models.Preview{}
	query := `
        SELECT id, app_id, status, url, vps_id, ip_address, error_msg, 
               build_logs, deploy_logs, expires_at, created_at, updated_at 
        FROM previews 
        WHERE id = ?
    `
	err := s.db.QueryRowContext(ctx, query, id).Scan(
		&preview.ID, &preview.AppID, &preview.Status, &preview.URL,
		&preview.VPSID, &preview.IPAddress, &preview.ErrorMsg,
		&preview.BuildLogs, &preview.DeployLogs, &preview.ExpiresAt,
		&preview.CreatedAt, &preview.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found", id), err)
		}
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get preview with ID %d", id), err)
	}
	return preview, nil
}

// GetPreviewsByAppID retrieves all previews for an app
func (s *SQLiteStore) GetPreviewsByAppID(ctx context.Context, appID int) ([]*models.Preview, error) {
	query := `
        SELECT id, app_id, status, url, vps_id, ip_address, error_msg, 
               build_logs, deploy_logs, expires_at, created_at, updated_at 
        FROM previews 
        WHERE app_id = ?
        ORDER BY created_at DESC
    `
	rows, err := s.db.QueryContext(ctx, query, appID)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get previews for app ID %d", appID), err)
	}
	defer rows.Close()

	var previews []*models.Preview
	for rows.Next() {
		var preview models.Preview
		err := rows.Scan(
			&preview.ID, &preview.AppID, &preview.Status, &preview.URL,
			&preview.VPSID, &preview.IPAddress, &preview.ErrorMsg,
			&preview.BuildLogs, &preview.DeployLogs, &preview.ExpiresAt,
			&preview.CreatedAt, &preview.UpdatedAt,
		)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan preview row", err)
		}
		previews = append(previews, &preview)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating preview rows for app ID %d", appID), err)
	}

	return previews, nil
}

// GetAllPreviews retrieves all previews (for admin purposes)
func (s *SQLiteStore) GetAllPreviews(ctx context.Context) ([]*models.Preview, error) {
	query := `
        SELECT id, app_id, status, url, vps_id, ip_address, error_msg, 
               build_logs, deploy_logs, expires_at, created_at, updated_at 
        FROM previews 
        ORDER BY created_at DESC
    `
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to get all previews", err)
	}
	defer rows.Close()

	var previews []*models.Preview
	for rows.Next() {
		var preview models.Preview
		err := rows.Scan(
			&preview.ID, &preview.AppID, &preview.Status, &preview.URL,
			&preview.VPSID, &preview.IPAddress, &preview.ErrorMsg,
			&preview.BuildLogs, &preview.DeployLogs, &preview.ExpiresAt,
			&preview.CreatedAt, &preview.UpdatedAt,
		)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan preview row", err)
		}
		previews = append(previews, &preview)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating all preview rows", err)
	}

	return previews, nil
}

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

// UpdatePreviewVPS updates the VPS information for a preview
func (s *SQLiteStore) UpdatePreviewVPS(ctx context.Context, previewID int, vpsID, ipAddress, url string) error {
	query := `
        UPDATE previews 
        SET vps_id = ?, ip_address = ?, url = ?, updated_at = CURRENT_TIMESTAMP 
        WHERE id = ?
    `
	result, err := s.db.ExecContext(ctx, query, vpsID, ipAddress, url, previewID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update preview VPS info for ID %d", previewID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for preview VPS update ID %d", previewID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found for VPS update", previewID), nil)
	}
	return nil
}

// UpdatePreviewBuildLogs updates the build logs for a preview
func (s *SQLiteStore) UpdatePreviewBuildLogs(ctx context.Context, previewID int, buildLogs string) error {
	query := `
        UPDATE previews 
        SET build_logs = ?, updated_at = CURRENT_TIMESTAMP 
        WHERE id = ?
    `
	result, err := s.db.ExecContext(ctx, query, buildLogs, previewID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update preview build logs for ID %d", previewID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for preview build logs update ID %d", previewID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found for build logs update", previewID), nil)
	}
	return nil
}

// UpdatePreviewDeployLogs updates the deploy logs for a preview
func (s *SQLiteStore) UpdatePreviewDeployLogs(ctx context.Context, previewID int, deployLogs string) error {
	query := `
        UPDATE previews 
        SET deploy_logs = ?, updated_at = CURRENT_TIMESTAMP 
        WHERE id = ?
    `
	result, err := s.db.ExecContext(ctx, query, deployLogs, previewID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update preview deploy logs for ID %d", previewID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for preview deploy logs update ID %d", previewID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found for deploy logs update", previewID), nil)
	}
	return nil
}

// UpdatePreview updates a preview record
func (s *SQLiteStore) UpdatePreview(ctx context.Context, preview models.Preview) error {
	query := `
        UPDATE previews 
        SET app_id = ?, status = ?, url = ?, vps_id = ?, ip_address = ?, 
            error_msg = ?, build_logs = ?, deploy_logs = ?, expires_at = ?, 
            updated_at = CURRENT_TIMESTAMP 
        WHERE id = ?
    `
	result, err := s.db.ExecContext(ctx, query,
		preview.AppID, preview.Status, preview.URL, preview.VPSID,
		preview.IPAddress, preview.ErrorMsg, preview.BuildLogs,
		preview.DeployLogs, preview.ExpiresAt, preview.ID,
	)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update preview with ID %d", preview.ID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for preview update ID %d", preview.ID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found for update", preview.ID), nil)
	}
	return nil
}

// DeletePreview deletes a preview record
func (s *SQLiteStore) DeletePreview(ctx context.Context, previewID int) error {
	query := `DELETE FROM previews WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, previewID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to delete preview with ID %d", previewID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for preview deletion ID %d", previewID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("preview with ID %d not found for deletion", previewID), nil)
	}
	return nil
}

// GetExpiredPreviews gets all previews that have expired (for cleanup jobs)
func (s *SQLiteStore) GetExpiredPreviews(ctx context.Context) ([]*models.Preview, error) {
	query := `
        SELECT id, app_id, status, url, vps_id, ip_address, error_msg, 
               build_logs, deploy_logs, expires_at, created_at, updated_at 
        FROM previews 
        WHERE expires_at < datetime('now') AND status != 'stopped'
        ORDER BY expires_at ASC
    `
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to get expired previews", err)
	}
	defer rows.Close()

	var previews []*models.Preview
	for rows.Next() {
		var preview models.Preview
		err := rows.Scan(
			&preview.ID, &preview.AppID, &preview.Status, &preview.URL,
			&preview.VPSID, &preview.IPAddress, &preview.ErrorMsg,
			&preview.BuildLogs, &preview.DeployLogs, &preview.ExpiresAt,
			&preview.CreatedAt, &preview.UpdatedAt,
		)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan expired preview row", err)
		}
		previews = append(previews, &preview)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating expired preview rows", err)
	}

	return previews, nil
}

// GetPreviewsByStatus retrieves all previews with a specific status
func (s *SQLiteStore) GetPreviewsByStatus(ctx context.Context, status string) ([]*models.Preview, error) {
	query := `SELECT id, app_id, status, vps_id, ip_address, url, build_logs, deploy_logs, error_msg, expires_at, created_at, updated_at FROM previews WHERE status = ?`

	rows, err := s.db.QueryContext(ctx, query, status)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get previews with status %s", status), err)
	}
	defer rows.Close()

	var previews []*models.Preview
	for rows.Next() {
		var preview models.Preview
		err := rows.Scan(
			&preview.ID,
			&preview.AppID,
			&preview.Status,
			&preview.VPSID,
			&preview.IPAddress,
			&preview.URL,
			&preview.BuildLogs,
			&preview.DeployLogs,
			&preview.ErrorMsg,
			&preview.ExpiresAt,
			&preview.CreatedAt,
			&preview.UpdatedAt,
		)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan preview row", err)
		}
		previews = append(previews, &preview)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating preview rows with status %s", status), err)
	}

	return previews, nil
}

// UpdateAppPreview updates the app with preview information
func (s *SQLiteStore) UpdateAppPreview(ctx context.Context, appID, previewID int, previewURL string) error {
	query := `
        UPDATE apps 
        SET preview_id = ?, preview_url = ?, status = 'ready', updated_at = CURRENT_TIMESTAMP 
        WHERE id = ?
    `
	result, err := s.db.ExecContext(ctx, query, previewID, previewURL, appID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update app preview info for app ID %d", appID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for app preview update, app ID %d", appID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("app with ID %d not found for preview update", appID), nil)
	}
	return nil
}

// GetPreviewByAppID retrieves the latest preview for an app
func (s *SQLiteStore) GetPreviewByAppID(ctx context.Context, appID int) (*models.Preview, error) {
	query := `
		SELECT id, app_id, status, url, vps_id, ip_address, error_msg, 
			   build_logs, deploy_logs, expires_at, created_at, updated_at 
		FROM previews 
		WHERE app_id = ? 
		ORDER BY created_at DESC 
		LIMIT 1
	`
	preview := &models.Preview{}
	err := s.db.QueryRowContext(ctx, query, appID).Scan(
		&preview.ID, &preview.AppID, &preview.Status, &preview.URL,
		&preview.VPSID, &preview.IPAddress, &preview.ErrorMsg,
		&preview.BuildLogs, &preview.DeployLogs, &preview.ExpiresAt,
		&preview.CreatedAt, &preview.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get latest preview for app ID %d", appID), err)
	}
	return preview, nil
}