1package migrations
2
3import (
4 "database/sql"
5 "embed"
6 "fmt"
7 "log"
8 "sort"
9 "strconv"
10 "strings"
11)
12
13//go:embed *.sql
14var migrationFiles embed.FS
15
16// Migration represents a single database migration
17type Migration struct {
18 Version int
19 Name string
20 SQL string
21}
22
23// GetMigrations returns all embedded migrations sorted by version
24func GetMigrations() ([]Migration, error) {
25 entries, err := migrationFiles.ReadDir(".")
26 if err != nil {
27 return nil, fmt.Errorf("failed to read embedded migrations directory: %w", err)
28 }
29
30 var migrations []Migration
31 for _, entry := range entries {
32 if !strings.HasSuffix(entry.Name(), ".sql") {
33 continue
34 }
35
36 // Parse version from filename (e.g., "001_initial.sql" -> version 1)
37 parts := strings.SplitN(entry.Name(), "_", 2)
38 if len(parts) != 2 {
39 log.Printf("Warning: skipping migration file with invalid name format: %s", entry.Name())
40 continue
41 }
42
43 versionStr := parts[0]
44 version, err := strconv.Atoi(versionStr)
45 if err != nil {
46 log.Printf("Warning: skipping migration file with invalid version: %s", entry.Name())
47 continue
48 }
49
50 // Extract name without extension
51 name := strings.TrimSuffix(parts[1], ".sql")
52
53 // Read SQL content
54 content, err := migrationFiles.ReadFile(entry.Name())
55 if err != nil {
56 return nil, fmt.Errorf("failed to read migration file %s: %w", entry.Name(), err)
57 }
58
59 migrations = append(migrations, Migration{
60 Version: version,
61 Name: name,
62 SQL: string(content),
63 })
64 }
65
66 // Sort by version
67 sort.Slice(migrations, func(i, j int) bool {
68 return migrations[i].Version < migrations[j].Version
69 })
70
71 return migrations, nil
72}
73
74// RunEmbeddedMigrations applies all pending embedded migrations to the database
75func RunEmbeddedMigrations(db *sql.DB) error {
76 // Create migrations tracking table if it doesn't exist
77 if err := createMigrationsTable(db); err != nil {
78 return fmt.Errorf("failed to create migrations table: %w", err)
79 }
80
81 // NOTE: Legacy databases without migration tracking should be deleted and recreated.
82 // All new databases will have migration tracking from the start.
83 // Backfill logic was removed to maintain idempotency - migrations are the single source of truth.
84
85 migrations, err := GetMigrations()
86 if err != nil {
87 return fmt.Errorf("failed to load migrations: %w", err)
88 }
89
90 for _, migration := range migrations {
91 if err := applyMigration(db, migration); err != nil {
92 return fmt.Errorf("failed to apply migration %d (%s): %w", migration.Version, migration.Name, err)
93 }
94 }
95
96 return nil
97}
98
99// createMigrationsTable creates the table for tracking applied migrations
100func createMigrationsTable(db *sql.DB) error {
101 schema := `
102 CREATE TABLE IF NOT EXISTS schema_migrations (
103 version INTEGER PRIMARY KEY,
104 name TEXT NOT NULL,
105 applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
106 );`
107
108 _, err := db.Exec(schema)
109 return err
110}
111
112// applyMigration applies a single migration if it hasn't been applied yet
113func applyMigration(db *sql.DB, migration Migration) error {
114 // Check if migration has already been applied
115 var count int
116 err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations WHERE version = ?", migration.Version).Scan(&count)
117 if err != nil {
118 return fmt.Errorf("failed to check migration status: %w", err)
119 }
120
121 if count > 0 {
122 // Migration already applied, skip
123 return nil
124 }
125
126 log.Printf("Applying migration %03d: %s", migration.Version, migration.Name)
127
128 // Use transaction for safety
129 tx, err := db.Begin()
130 if err != nil {
131 return fmt.Errorf("failed to begin transaction: %w", err)
132 }
133 defer func() {
134 if err != nil {
135 tx.Rollback()
136 }
137 }()
138
139 // Execute migration SQL
140 if _, err := tx.Exec(migration.SQL); err != nil {
141 return fmt.Errorf("failed to execute migration SQL: %w", err)
142 }
143
144 // Record migration as applied
145 if _, err := tx.Exec(
146 "INSERT INTO schema_migrations (version, name) VALUES (?, ?)",
147 migration.Version, migration.Name,
148 ); err != nil {
149 return fmt.Errorf("failed to record migration: %w", err)
150 }
151
152 if err := tx.Commit(); err != nil {
153 return fmt.Errorf("failed to commit migration transaction: %w", err)
154 }
155
156 log.Printf("Successfully applied migration %03d: %s", migration.Version, migration.Name)
157 return nil
158}
159
160// GetAppliedMigrations returns the list of applied migration versions
161func GetAppliedMigrations(db *sql.DB) ([]int, error) {
162 // First check if migrations table exists
163 var count int
164 err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'").Scan(&count)
165 if err != nil {
166 return nil, fmt.Errorf("failed to check if migrations table exists: %w", err)
167 }
168
169 if count == 0 {
170 // Migrations table doesn't exist, no migrations applied
171 return []int{}, nil
172 }
173
174 rows, err := db.Query("SELECT version FROM schema_migrations ORDER BY version")
175 if err != nil {
176 return nil, fmt.Errorf("failed to query applied migrations: %w", err)
177 }
178 defer rows.Close()
179
180 var versions []int
181 for rows.Next() {
182 var version int
183 if err := rows.Scan(&version); err != nil {
184 return nil, fmt.Errorf("failed to scan migration version: %w", err)
185 }
186 versions = append(versions, version)
187 }
188
189 if err := rows.Err(); err != nil {
190 return nil, fmt.Errorf("error iterating over migration rows: %w", err)
191 }
192
193 return versions, nil
194}
195
196// VerifyAllMigrationsApplied scans all embedded migrations and verifies they have been applied
197// Returns an error if any migrations are missing or if verification fails
198func VerifyAllMigrationsApplied(db *sql.DB) error {
199 // Get all embedded migrations
200 allMigrations, err := GetMigrations()
201 if err != nil {
202 return fmt.Errorf("failed to get embedded migrations: %w", err)
203 }
204
205 // Get applied migrations from database
206 appliedVersions, err := GetAppliedMigrations(db)
207 if err != nil {
208 return fmt.Errorf("failed to get applied migrations: %w", err)
209 }
210
211 // Create map for quick lookup
212 appliedMap := make(map[int]bool)
213 for _, version := range appliedVersions {
214 appliedMap[version] = true
215 }
216
217 // Check each migration is applied
218 var missingMigrations []Migration
219 for _, migration := range allMigrations {
220 if !appliedMap[migration.Version] {
221 missingMigrations = append(missingMigrations, migration)
222 }
223 }
224
225 // Report results
226 if len(missingMigrations) > 0 {
227 log.Printf("WARNING: %d migrations are not applied:", len(missingMigrations))
228 for _, migration := range missingMigrations {
229 log.Printf(" - Migration %03d: %s", migration.Version, migration.Name)
230 }
231 return fmt.Errorf("%d migrations are not applied", len(missingMigrations))
232 }
233
234 log.Printf("Migration verification: All %d migrations are applied ✓", len(allMigrations))
235 return nil
236}