meal-prep-vibecoded/database/db.go

package database

import (
	"database/sql"
	"fmt"
	"mealprep/models"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

// InitDB initializes the database and creates tables
func InitDB(dbPath string) error {
	var err error
	DB, err = sql.Open("sqlite3", dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}

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

	// Create tables
	if err = createTables(); err != nil {
		return fmt.Errorf("failed to create tables: %w", err)
	}

	// Run migrations
	if err = runMigrations(); err != nil {
		return fmt.Errorf("failed to run migrations: %w", err)
	}

	return nil
}

func createTables() error {
	schema := `
	-- Users table
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		email TEXT NOT NULL UNIQUE,
		password_hash TEXT NOT NULL,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	);

	-- Sessions table
	CREATE TABLE IF NOT EXISTS sessions (
		token TEXT PRIMARY KEY,
		user_id INTEGER NOT NULL,
		expires_at DATETIME NOT NULL,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
	CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);

	-- Ingredients table (user-isolated)
	CREATE TABLE IF NOT EXISTS ingredients (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		unit TEXT NOT NULL,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
		UNIQUE(user_id, name)
	);

	CREATE INDEX IF NOT EXISTS idx_ingredients_user_id ON ingredients(user_id);

	-- Tags table (user-isolated)
	CREATE TABLE IF NOT EXISTS tags (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
		UNIQUE(user_id, name)
	);

	CREATE INDEX IF NOT EXISTS idx_tags_user_id ON tags(user_id);

	-- Ingredient tags junction table
	CREATE TABLE IF NOT EXISTS ingredient_tags (
		ingredient_id INTEGER NOT NULL,
		tag_id INTEGER NOT NULL,
		PRIMARY KEY (ingredient_id, tag_id),
		FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
		FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
	);

	-- Meals table (user-isolated)
	CREATE TABLE IF NOT EXISTS meals (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		description TEXT,
		meal_type TEXT NOT NULL DEFAULT 'lunch',
		instructions TEXT,
		prep_time INTEGER DEFAULT 0,
		image_url TEXT,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_meals_user_id ON meals(user_id);

	-- Meal ingredients
	CREATE TABLE IF NOT EXISTS meal_ingredients (
		meal_id INTEGER NOT NULL,
		ingredient_id INTEGER NOT NULL,
		quantity REAL NOT NULL,
		PRIMARY KEY (meal_id, ingredient_id),
		FOREIGN KEY (meal_id) REFERENCES meals(id) ON DELETE CASCADE,
		FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE
	);

	-- Week plan
	CREATE TABLE IF NOT EXISTS week_plan (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		date TEXT NOT NULL,
		meal_id INTEGER NOT NULL,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
		FOREIGN KEY (meal_id) REFERENCES meals(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_week_plan_user_id ON week_plan(user_id);
	`

	_, err := DB.Exec(schema)
	return err
}

func runMigrations() error {
	// Check if instructions column exists
	var count int
	err := DB.QueryRow("SELECT COUNT(*) FROM pragma_table_info('meals') WHERE name='instructions'").Scan(&count)
	if err != nil {
		return err
	}

	// Add new meal columns if they don't exist
	if count == 0 {
		_, err = DB.Exec("ALTER TABLE meals ADD COLUMN instructions TEXT")
		if err != nil {
			return err
		}
		_, err = DB.Exec("ALTER TABLE meals ADD COLUMN prep_time INTEGER DEFAULT 0")
		if err != nil {
			return err
		}
		_, err = DB.Exec("ALTER TABLE meals ADD COLUMN image_url TEXT")
		if err != nil {
			return err
		}
	}

	return nil
}

// User operations

func CreateUser(email, passwordHash string) (int64, error) {
	result, err := DB.Exec(
		"INSERT INTO users (email, password_hash) VALUES (?, ?)",
		email, passwordHash,
	)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func GetUserByEmail(email string) (*models.User, error) {
	var user models.User
	err := DB.QueryRow(
		"SELECT id, email, password_hash, created_at FROM users WHERE email = ?",
		email,
	).Scan(&user.ID, &user.Email, &user.PasswordHash, &user.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &user, nil
}

func GetUserByID(userID int) (*models.User, error) {
	var user models.User
	err := DB.QueryRow(
		"SELECT id, email, password_hash, created_at FROM users WHERE id = ?",
		userID,
	).Scan(&user.ID, &user.Email, &user.PasswordHash, &user.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &user, nil
}

// Session operations

func CreateSession(token string, userID int, expiresAt time.Time) error {
	_, err := DB.Exec(
		"INSERT INTO sessions (token, user_id, expires_at) VALUES (?, ?, ?)",
		token, userID, expiresAt,
	)
	return err
}

func GetSession(token string) (*models.Session, error) {
	var session models.Session
	err := DB.QueryRow(
		"SELECT token, user_id, expires_at, created_at FROM sessions WHERE token = ? AND expires_at > datetime('now')",
		token,
	).Scan(&session.Token, &session.UserID, &session.ExpiresAt, &session.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &session, nil
}

func DeleteSession(token string) error {
	_, err := DB.Exec("DELETE FROM sessions WHERE token = ?", token)
	return err
}

func DeleteExpiredSessions() error {
	_, err := DB.Exec("DELETE FROM sessions WHERE expires_at < datetime('now')")
	return err
}

// Ingredient operations (user-isolated)

func GetAllIngredients(userID int) ([]models.Ingredient, error) {
	rows, err := DB.Query(
		"SELECT id, user_id, name, unit FROM ingredients WHERE user_id = ? ORDER BY name",
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ingredients []models.Ingredient
	for rows.Next() {
		var ing models.Ingredient
		if err := rows.Scan(&ing.ID, &ing.UserID, &ing.Name, &ing.Unit); err != nil {
			return nil, err
		}
		ingredients = append(ingredients, ing)
	}
	return ingredients, nil
}

func AddIngredient(userID int, name, unit string) (int64, error) {
	result, err := DB.Exec(
		"INSERT INTO ingredients (user_id, name, unit) VALUES (?, ?, ?)",
		userID, name, unit,
	)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func DeleteIngredient(userID, ingredientID int) error {
	_, err := DB.Exec(
		"DELETE FROM ingredients WHERE id = ? AND user_id = ?",
		ingredientID, userID,
	)
	if err != nil {
		return err
	}

	// Clean up unused tags
	CleanupUnusedTags(userID)

	return nil
}

// Meal operations (user-isolated)

func GetAllMeals(userID int) ([]models.Meal, error) {
	rows, err := DB.Query(
		"SELECT id, user_id, name, description, meal_type, instructions, prep_time, image_url FROM meals WHERE user_id = ? ORDER BY name",
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var meals []models.Meal
	for rows.Next() {
		var meal models.Meal
		if err := rows.Scan(&meal.ID, &meal.UserID, &meal.Name, &meal.Description, &meal.MealType, &meal.Instructions, &meal.PrepTime, &meal.ImageURL); err != nil {
			return nil, err
		}
		meals = append(meals, meal)
	}
	return meals, nil
}

func GetMealByID(userID, mealID int) (*models.Meal, error) {
	var meal models.Meal
	err := DB.QueryRow(
		"SELECT id, user_id, name, description, meal_type, instructions, prep_time, image_url FROM meals WHERE id = ? AND user_id = ?",
		mealID, userID,
	).Scan(&meal.ID, &meal.UserID, &meal.Name, &meal.Description, &meal.MealType, &meal.Instructions, &meal.PrepTime, &meal.ImageURL)
	if err != nil {
		return nil, err
	}
	return &meal, nil
}

func AddMeal(userID int, name, description, mealType, instructions, imageURL string, prepTime int) (int64, error) {
	result, err := DB.Exec(
		"INSERT INTO meals (user_id, name, description, meal_type, instructions, prep_time, image_url) VALUES (?, ?, ?, ?, ?, ?, ?)",
		userID, name, description, mealType, instructions, prepTime, imageURL,
	)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func DeleteMeal(userID, mealID int) error {
	_, err := DB.Exec(
		"DELETE FROM meals WHERE id = ? AND user_id = ?",
		mealID, userID,
	)
	return err
}

// Meal Ingredients operations

func GetMealIngredients(userID, mealID int) ([]models.MealIngredient, error) {
	query := `
		SELECT mi.meal_id, mi.ingredient_id, mi.quantity, i.name, i.unit
		FROM meal_ingredients mi
		JOIN ingredients i ON mi.ingredient_id = i.id
		JOIN meals m ON mi.meal_id = m.id
		WHERE mi.meal_id = ? AND m.user_id = ? AND i.user_id = ?
		ORDER BY i.name
	`
	rows, err := DB.Query(query, mealID, userID, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ingredients []models.MealIngredient
	for rows.Next() {
		var mi models.MealIngredient
		if err := rows.Scan(&mi.MealID, &mi.IngredientID, &mi.Quantity, &mi.IngredientName, &mi.Unit); err != nil {
			return nil, err
		}
		ingredients = append(ingredients, mi)
	}
	return ingredients, nil
}

func AddMealIngredient(userID, mealID, ingredientID int, quantity float64) error {
	// Verify meal and ingredient belong to user
	var count int
	err := DB.QueryRow(
		"SELECT COUNT(*) FROM meals m, ingredients i WHERE m.id = ? AND i.id = ? AND m.user_id = ? AND i.user_id = ?",
		mealID, ingredientID, userID, userID,
	).Scan(&count)
	if err != nil || count == 0 {
		return fmt.Errorf("meal or ingredient not found or doesn't belong to user")
	}

	_, err = DB.Exec(
		"INSERT OR REPLACE INTO meal_ingredients (meal_id, ingredient_id, quantity) VALUES (?, ?, ?)",
		mealID, ingredientID, quantity,
	)
	return err
}

func DeleteMealIngredient(userID, mealID, ingredientID int) error {
	// Verify ownership
	var count int
	err := DB.QueryRow(
		"SELECT COUNT(*) FROM meals WHERE id = ? AND user_id = ?",
		mealID, userID,
	).Scan(&count)
	if err != nil || count == 0 {
		return fmt.Errorf("meal not found or doesn't belong to user")
	}

	_, err = DB.Exec(
		"DELETE FROM meal_ingredients WHERE meal_id = ? AND ingredient_id = ?",
		mealID, ingredientID,
	)
	return err
}

// Week Plan operations (user-isolated)

func GetWeekPlan(userID int) ([]models.WeekPlanEntry, error) {
	query := `
		SELECT wp.id, wp.date, wp.meal_id, m.name, m.meal_type
		FROM week_plan wp
		JOIN meals m ON wp.meal_id = m.id
		WHERE wp.user_id = ?
		ORDER BY wp.date, m.meal_type
	`
	rows, err := DB.Query(query, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []models.WeekPlanEntry
	for rows.Next() {
		var entry models.WeekPlanEntry
		var dateStr string
		if err := rows.Scan(&entry.ID, &dateStr, &entry.MealID, &entry.MealName, &entry.MealType); err != nil {
			return nil, err
		}
		entry.Date, _ = time.Parse("2006-01-02", dateStr)
		entries = append(entries, entry)
	}
	return entries, nil
}

func AddWeekPlanEntry(userID int, date time.Time, mealID int) error {
	// Verify meal belongs to user
	var count int
	err := DB.QueryRow(
		"SELECT COUNT(*) FROM meals WHERE id = ? AND user_id = ?",
		mealID, userID,
	).Scan(&count)
	if err != nil || count == 0 {
		return fmt.Errorf("meal not found or doesn't belong to user")
	}

	dateStr := date.Format("2006-01-02")
	_, err = DB.Exec(
		"INSERT INTO week_plan (user_id, date, meal_id) VALUES (?, ?, ?)",
		userID, dateStr, mealID,
	)
	return err
}

func DeleteWeekPlanEntry(userID, entryID int) error {
	_, err := DB.Exec(
		"DELETE FROM week_plan WHERE id = ? AND user_id = ?",
		entryID, userID,
	)
	return err
}

// Grocery List operations (user-isolated)

func GetGroceryList(userID int) ([]models.GroceryItem, error) {
	query := `
		SELECT i.name, SUM(mi.quantity) as total_quantity, i.unit
		FROM week_plan wp
		JOIN meals m ON wp.meal_id = m.id
		JOIN meal_ingredients mi ON m.id = mi.meal_id
		JOIN ingredients i ON mi.ingredient_id = i.id
		WHERE wp.user_id = ? AND m.user_id = ? AND i.user_id = ?
		GROUP BY i.id, i.name, i.unit
		ORDER BY i.name
	`
	rows, err := DB.Query(query, userID, userID, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []models.GroceryItem
	for rows.Next() {
		var item models.GroceryItem
		if err := rows.Scan(&item.IngredientName, &item.TotalQuantity, &item.Unit); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, nil
}

// Tag operations (user-isolated)

func GetAllTags(userID int) ([]models.Tag, error) {
	rows, err := DB.Query(
		"SELECT id, user_id, name FROM tags WHERE user_id = ? ORDER BY name",
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []models.Tag
	for rows.Next() {
		var tag models.Tag
		if err := rows.Scan(&tag.ID, &tag.UserID, &tag.Name); err != nil {
			return nil, err
		}
		tags = append(tags, tag)
	}
	return tags, nil
}

func GetUsedTags(userID int) ([]models.Tag, error) {
	query := `
		SELECT DISTINCT t.id, t.user_id, t.name
		FROM tags t
		JOIN ingredient_tags it ON t.id = it.tag_id
		WHERE t.user_id = ?
		ORDER BY t.name
	`
	rows, err := DB.Query(query, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []models.Tag
	for rows.Next() {
		var tag models.Tag
		if err := rows.Scan(&tag.ID, &tag.UserID, &tag.Name); err != nil {
			return nil, err
		}
		tags = append(tags, tag)
	}
	return tags, nil
}

func CleanupUnusedTags(userID int) error {
	// Delete tags that have no ingredient associations
	_, err := DB.Exec(`
		DELETE FROM tags
		WHERE user_id = ?
		AND id NOT IN (
			SELECT DISTINCT tag_id FROM ingredient_tags
		)
	`, userID)
	return err
}

func AddTag(userID int, name string) (int64, error) {
	result, err := DB.Exec(
		"INSERT INTO tags (user_id, name) VALUES (?, ?)",
		userID, name,
	)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func GetOrCreateTag(userID int, name string) (int64, error) {
	// Try to get existing tag
	var tagID int64
	err := DB.QueryRow(
		"SELECT id FROM tags WHERE user_id = ? AND name = ?",
		userID, name,
	).Scan(&tagID)

	if err == sql.ErrNoRows {
		// Tag doesn't exist, create it
		return AddTag(userID, name)
	}
	if err != nil {
		return 0, err
	}
	return tagID, nil
}

func DeleteTag(userID, tagID int) error {
	_, err := DB.Exec(
		"DELETE FROM tags WHERE id = ? AND user_id = ?",
		tagID, userID,
	)
	return err
}

// Ingredient tag operations

func GetIngredientTags(userID, ingredientID int) ([]models.Tag, error) {
	query := `
		SELECT t.id, t.user_id, t.name
		FROM tags t
		JOIN ingredient_tags it ON t.id = it.tag_id
		JOIN ingredients i ON it.ingredient_id = i.id
		WHERE it.ingredient_id = ? AND i.user_id = ? AND t.user_id = ?
		ORDER BY t.name
	`
	rows, err := DB.Query(query, ingredientID, userID, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []models.Tag
	for rows.Next() {
		var tag models.Tag
		if err := rows.Scan(&tag.ID, &tag.UserID, &tag.Name); err != nil {
			return nil, err
		}
		tags = append(tags, tag)
	}
	return tags, nil
}

func AddIngredientTag(userID, ingredientID, tagID int) error {
	// Verify ingredient and tag belong to user
	var count int
	err := DB.QueryRow(
		"SELECT COUNT(*) FROM ingredients i, tags t WHERE i.id = ? AND t.id = ? AND i.user_id = ? AND t.user_id = ?",
		ingredientID, tagID, userID, userID,
	).Scan(&count)
	if err != nil || count == 0 {
		return fmt.Errorf("ingredient or tag not found or doesn't belong to user")
	}

	_, err = DB.Exec(
		"INSERT OR IGNORE INTO ingredient_tags (ingredient_id, tag_id) VALUES (?, ?)",
		ingredientID, tagID,
	)
	return err
}

func RemoveIngredientTag(userID, ingredientID, tagID int) error {
	// Verify ownership
	var count int
	err := DB.QueryRow(
		"SELECT COUNT(*) FROM ingredients WHERE id = ? AND user_id = ?",
		ingredientID, userID,
	).Scan(&count)
	if err != nil || count == 0 {
		return fmt.Errorf("ingredient not found or doesn't belong to user")
	}

	_, err = DB.Exec(
		"DELETE FROM ingredient_tags WHERE ingredient_id = ? AND tag_id = ?",
		ingredientID, tagID,
	)
	if err != nil {
		return err
	}

	// Clean up unused tags
	CleanupUnusedTags(userID)

	return nil
}

func GetIngredientsWithTags(userID int) ([]models.Ingredient, error) {
	rows, err := DB.Query(
		"SELECT id, user_id, name, unit FROM ingredients WHERE user_id = ? ORDER BY name",
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ingredients []models.Ingredient
	for rows.Next() {
		var ing models.Ingredient
		if err := rows.Scan(&ing.ID, &ing.UserID, &ing.Name, &ing.Unit); err != nil {
			return nil, err
		}

		// Get tags for this ingredient
		ing.Tags, _ = GetIngredientTags(userID, ing.ID)
		ingredients = append(ingredients, ing)
	}
	return ingredients, nil
}

func SearchIngredientsByTags(userID int, tagNames []string) ([]models.Ingredient, error) {
	if len(tagNames) == 0 {
		return GetIngredientsWithTags(userID)
	}

	// Build query with placeholders for tag names
	placeholders := make([]string, len(tagNames))
	args := []interface{}{userID}
	for i, name := range tagNames {
		placeholders[i] = "?"
		args = append(args, name)
	}
	args = append(args, len(tagNames))

	query := fmt.Sprintf(`
		SELECT DISTINCT i.id, i.user_id, i.name, i.unit
		FROM ingredients i
		JOIN ingredient_tags it ON i.id = it.ingredient_id
		JOIN tags t ON it.tag_id = t.id
		WHERE i.user_id = ? AND t.name IN (%s)
		GROUP BY i.id
		HAVING COUNT(DISTINCT t.id) = ?
		ORDER BY i.name
	`, strings.Join(placeholders, ","))

	rows, err := DB.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ingredients []models.Ingredient
	for rows.Next() {
		var ing models.Ingredient
		if err := rows.Scan(&ing.ID, &ing.UserID, &ing.Name, &ing.Unit); err != nil {
			return nil, err
		}

		// Get tags for this ingredient
		ing.Tags, _ = GetIngredientTags(userID, ing.ID)
		ingredients = append(ingredients, ing)
	}
	return ingredients, nil
}