package dbstore

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

	"git.linuxforward.com/byop/byop-engine/models"
	_ "github.com/mattn/go-sqlite3" // Import SQLite driver for compatibility
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// Store defines the interface for all database operations.
// This will include methods for all models (User, Client, App, Component, Deployment, Ticket, etc.)
type Store interface {
	// User methods
	CreateUser(ctx context.Context, user *models.User) error
	GetUserByEmail(ctx context.Context, email string) (*models.User, error)
	GetUserByID(ctx context.Context, id uint) (*models.User, error)
	GetUsers(ctx context.Context) ([]*models.User, error)
	UpdateUser(ctx context.Context, user *models.User) error
	DeleteUser(ctx context.Context, id uint) error
	CreateDefaultAdmin(ctx context.Context) error
	GetByUsername(ctx context.Context, username string) (*models.User, error)

	// Client methods
	CreateClient(ctx context.Context, client *models.Client) error
	GetAllClients(ctx context.Context) ([]*models.Client, error)
	GetClientByID(ctx context.Context, id uint) (*models.Client, error)
	UpdateClient(ctx context.Context, client *models.Client) error
	DeleteClient(ctx context.Context, id uint) error

	// App methods
	CreateApp(ctx context.Context, app *models.App) error
	GetAppByID(ctx context.Context, id uint) (*models.App, error)
	GetAppsByUserID(ctx context.Context, userID uint) ([]*models.App, error)
	UpdateApp(ctx context.Context, app *models.App) error
	DeleteApp(ctx context.Context, id uint) error
	UpdateAppStatus(ctx context.Context, appID uint, status string, message string) error
	UpdateAppPreview(ctx context.Context, appID uint, previewID uint, previewURL string) error
	GetAllApps(ctx context.Context) ([]*models.App, error)
	UpdateAppCurrentImage(ctx context.Context, appID uint, imageTag string, imageURI string) error

	// Component methods
	CreateComponent(ctx context.Context, component *models.Component) error
	GetComponentByID(ctx context.Context, id uint) (*models.Component, error)
	GetComponentsByUserID(ctx context.Context, userID uint) ([]*models.Component, error)
	GetAllComponents(ctx context.Context) ([]*models.Component, error)
	UpdateComponent(ctx context.Context, component *models.Component) error
	UpdateComponentStatus(ctx context.Context, componentID uint, status string, errorMsg string) error
	DeleteComponent(ctx context.Context, id uint) error

	// Deployment methods
	CreateDeployment(ctx context.Context, deployment *models.Deployment) error
	GetDeploymentByID(ctx context.Context, id uint) (*models.Deployment, error)
	GetDeploymentsByClientID(ctx context.Context, clientID uint) ([]*models.Deployment, error)
	GetAllDeployments(ctx context.Context) ([]*models.Deployment, error)
	UpdateDeployment(ctx context.Context, deployment *models.Deployment) error
	DeleteDeployment(ctx context.Context, id uint) error

	// Preview methods
	CreatePreview(ctx context.Context, preview *models.Preview) error
	GetPreviewByID(ctx context.Context, id uint) (*models.Preview, error)
	GetPreviewByAppID(ctx context.Context, appID uint) (*models.Preview, error)
	UpdatePreview(ctx context.Context, preview *models.Preview) error
	DeletePreview(ctx context.Context, id uint) error
	UpdatePreviewVPS(ctx context.Context, previewID uint, vpsID string, ipAddress string, previewURL string) error
	UpdatePreviewStatus(ctx context.Context, previewID uint, status string, errorMsg string) error
	UpdatePreviewBuildLogs(ctx context.Context, previewID uint, logs string) error
	UpdatePreviewDeployLogs(ctx context.Context, previewID uint, logs string) error
	GetAllPreviews(ctx context.Context) ([]*models.Preview, error)
	GetPreviewsByStatus(ctx context.Context, status string) ([]*models.Preview, error)
	GetPreviewsByAppID(ctx context.Context, appID uint) ([]*models.Preview, error)

	// Ticket methods
	CreateTicket(ctx context.Context, ticket *models.Ticket) error
	GetTicketByID(ctx context.Context, id uint) (*models.Ticket, error)
	GetTickets(ctx context.Context) ([]*models.Ticket, error)
	UpdateTicket(ctx context.Context, ticket *models.Ticket) error

	// TicketComment methods
	CreateTicketComment(ctx context.Context, comment *models.TicketComment) error
	GetTicketComments(ctx context.Context, ticketID uint) ([]*models.TicketComment, error)

	// BuildJob methods
	CreateBuildJob(ctx context.Context, job *models.BuildJob) error
	GetBuildJobByID(ctx context.Context, id uint) (*models.BuildJob, error)
	UpdateBuildJob(ctx context.Context, job *models.BuildJob) error
	UpdateBuildJobStatus(ctx context.Context, id uint, status models.BuildStatus, errorMessage string) error
	AppendBuildJobLog(ctx context.Context, id uint, logMessage string) error
	GetQueuedBuildJobs(ctx context.Context, limit int) ([]*models.BuildJob, error)
	GetBuildJobsByAppID(ctx context.Context, appID uint, page, pageSize int) ([]*models.BuildJob, int64, error)

	// General DB methods
	GetDB() *sql.DB
	GetGormDB() *gorm.DB
	Close() error
}

// SQLiteStore implements the Store interface for SQLite using GORM
type SQLiteStore struct {
	db    *gorm.DB
	rawDB *sql.DB // Keep for backward compatibility and specific raw SQL operations
	dsn   string
}

// NewSQLiteStore initializes a new SQLiteStore with GORM
func NewSQLiteStore(dataSourceName string) (*SQLiteStore, error) {
	// First check if the database file exists
	isNewDb := !fileExists(dataSourceName)
	if isNewDb {
		// Create the database file if it doesn't exist
		file, err := os.Create(dataSourceName)
		if err != nil {
			return nil, fmt.Errorf("failed to create SQLite database file: %w", err)
		}
		defer file.Close()
	}

	// Open GORM database
	gormDB, err := gorm.Open(sqlite.Open(dataSourceName), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent), // Change to logger.Info for debugging
	})
	if err != nil {
		return nil, fmt.Errorf("failed to open database with GORM: %w", err)
	}

	// Get underlying SQL DB for backward compatibility
	rawDB, err := gormDB.DB()
	if err != nil {
		return nil, fmt.Errorf("failed to get underlying SQL DB: %w", err)
	}

	if err := rawDB.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	// Enable foreign keys in SQLite
	err = gormDB.Exec("PRAGMA foreign_keys = ON").Error
	if err != nil {
		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
	}

	// Auto-migrate all models
	err = gormDB.AutoMigrate(
		&models.User{},
		&models.Client{},
		&models.App{},
		&models.Component{},
		&models.Deployment{},
		&models.Preview{},
		&models.Ticket{},
		&models.TicketComment{},
		&models.Provider{},
		&models.BuildJob{},
	)
	if err != nil {
		return nil, fmt.Errorf("failed to auto-migrate models: %w", err)
	}

	store := &SQLiteStore{
		db:    gormDB,
		rawDB: rawDB,
		dsn:   dataSourceName,
	}

	return store, nil
}

// fileExists checks if a file exists
func fileExists(filename string) bool {
	_, err := os.Stat(filename)
	return !os.IsNotExist(err)
}

// GetDB returns the raw SQL database instance for backward compatibility
func (m *SQLiteStore) GetDB() *sql.DB {
	return m.rawDB
}

// GetGormDB returns the GORM database instance
func (m *SQLiteStore) GetGormDB() *gorm.DB {
	return m.db
}

// Connect establishes a connection to the SQLite database
func (m *SQLiteStore) Connect() error {
	// Connection is already established in NewSQLiteStore
	return nil
}

// Disconnect closes the connection to the SQLite database
func (m *SQLiteStore) Disconnect() error {
	sqlDB, err := m.db.DB()
	if err != nil {
		return fmt.Errorf("failed to get underlying SQL DB: %w", err)
	}
	return sqlDB.Close()
}

// Close provides a more standard name for closing the database connection.
// It simply calls Disconnect.
func (m *SQLiteStore) Close() error {
	return m.Disconnect()
}