rm-hull / gps-routes-api / 24308993190

func MigratePostgresToSQLite(pgConnStr, sqliteFile string, dryRun bool, maxRecords int) {

        var finalConnStr string

        if pgConnStr == "" {

                // Build connection string from environment variables using the same pattern as ping_database.go

                config := db.ConfigFromEnv()

                finalConnStr = fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s search_path=%s",

                        config.Host, config.Port, config.User, config.Password, config.DBName, config.SSLMode, config.Schema+",public")

                log.Printf("🔧 Using PostgreSQL connection from environment variables: %s@%s:%d/%s",

                        config.User, config.Host, config.Port, config.DBName)

        } else {

                finalConnStr = pgConnStr

        }

        log.Printf("🔄 SQLite3 Migration Tool")

        log.Printf("📊 Source (PostgreSQL): %s", maskConnStr(finalConnStr))

        log.Printf("📁 Target (SQLite): %s", sqliteFile)

        if dryRun {

                log.Printf("⚠️  DRY RUN: Validation only, no writes")

        }

        pgCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second)

        defer cancel()

        pgPool, err := pgxpool.New(pgCtx, finalConnStr)

        if err != nil {

                log.Fatalf("❌ Failed to connect to PostgreSQL: %v", err)

        }

        defer pgPool.Close()

        log.Print("✅ Connected to PostgreSQL")

        // Validate PostgreSQL has data

        var pgCount int64

        err = pgPool.QueryRow(context.Background(), "SELECT COUNT(*) FROM routes").Scan(&pgCount)

        if err != nil {

                log.Fatalf("❌ Failed to query PostgreSQL: %v", err)

        }

        log.Printf("📈 PostgreSQL routes count: %d", pgCount)

        if pgCount == 0 {

                log.Fatal("❌ No routes found in PostgreSQL database")

        }

        if maxRecords > 0 && int64(maxRecords) < pgCount {

                log.Printf("⏱️  Limiting migration to %d records (out of %d available)", maxRecords, pgCount)

                pgCount = int64(maxRecords)

        }

        expectedCount := pgCount

        // Create/prepare SQLite database

        if err := setupSQLiteDB(sqliteFile, dryRun); err != nil {

                log.Fatalf("❌ Failed to setup SQLite: %v", err)

        }

        if dryRun {

                log.Print("🔍 Dry-run mode: skipping data migration")

                os.Exit(0)

        }

        sqliteDB, err := sql.Open("sqlite3", sqliteFile)

        if err != nil {

                log.Fatalf("❌ Failed to open SQLite: %v", err)

        }

        defer sqliteDB.Close()

        log.Print("🚀 Starting data migration...")

        migrated, err := migrateRoutes(pgPool, sqliteDB, maxRecords)

        if err != nil {

                log.Fatalf("❌ Migration failed: %v", err)

        }

        log.Printf("✅ Routes migrated: %d", migrated)

        expectedCount = migrated

        nearbyMigrated, err := migrateNearby(pgPool, sqliteDB)

        if err != nil {

                log.Printf("⚠️  Nearby migration failed (non-critical): %v", err)

        } else {

                log.Printf("✅ Nearby records migrated: %d", nearbyMigrated)

        }

        imagesMigrated, err := migrateImages(pgPool, sqliteDB)

        if err != nil {

                log.Printf("⚠️  Images migration failed (non-critical): %v", err)

        } else {

                log.Printf("✅ Images migrated: %d", imagesMigrated)

        }

        detailsMigrated, err := migrateDetails(pgPool, sqliteDB)

        if err != nil {

                log.Printf("⚠️  Details migration failed (non-critical): %v", err)

        } else {

                log.Printf("✅ Details migrated: %d", detailsMigrated)

        }

        log.Print("🔍 Validating migration...")

        if err := validateMigration(pgPool, sqliteDB, expectedCount); err != nil {

                log.Fatalf("❌ Validation failed: %v", err)

        }

        log.Print("✅ Migration validation passed!")

        log.Printf("📊 Final SQLite database: %s", sqliteFile)

func setupSQLiteDB(dbPath string, dryRun bool) error {

        if dryRun {

                // Use in-memory DB for dry-run

                dbPath = ":memory:"

        }

        db, err := sql.Open("sqlite3", dbPath+"?_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)")

        if err != nil {

                return err

        }

        defer db.Close()

        // Try to enable FTS5 if available by creating a temporary FTS5 virtual table.

        _, err = db.Exec("CREATE VIRTUAL TABLE IF NOT EXISTS __fts5_check__ USING fts5(content)")

        if err != nil {

                log.Printf("⚠️  FTS5 not available, creating schema without full-text search: %v", err)

                return createSchemaWithoutFTS5(db)

        }

        _, err = db.Exec("DROP TABLE IF EXISTS __fts5_check__")

        if err != nil {

                return fmt.Errorf("failed to clean up temporary FTS5 table: %w", err)

        }

        schema, err := os.ReadFile("db/migrations/00002_sqlite_schema.sql")

        if err != nil {

                return fmt.Errorf("failed to read schema file: %w", err)

        }

        if _, err := db.Exec(string(schema)); err != nil {

                return fmt.Errorf("failed to create schema: %w", err)

        }

        return nil

func migrateRoutes(pgPool *pgxpool.Pool, sqliteDB *sql.DB, maxRecords int) (int64, error) {

        ctx := context.Background()

        // Fetch from PostgreSQL (with limit if specified)

        query := `

                SELECT

                        object_id, created_at::text, ref, title, headline_image_url, gpx_url,

                        ST_X(_geoloc) as longitude, ST_Y(_geoloc) as latitude,

                        distance_km, description, video_url, display_address, postcode,

                        district, county, region, state, country, estimated_duration,

                        difficulty, array_to_json(terrain), array_to_json(points_of_interest),

                        array_to_json(facilities), route_type, array_to_json(activities)

                FROM routes

        `

        if maxRecords > 0 {

                query += fmt.Sprintf(" LIMIT %d", maxRecords)

        }

        rows, err := pgPool.Query(ctx, query)

        if err != nil {

                return 0, fmt.Errorf("failed to query PostgreSQL routes: %w", err)

        }

        defer rows.Close()

        // Prepare SQLite insert statement

        stmt, err := sqliteDB.Prepare(`

                INSERT INTO routes (

                        object_id, created_at, ref, title, headline_image_url, gpx_url,

                        latitude, longitude, distance_km, description, video_url,

                        display_address, postcode, district, county, region, state, country,

                        estimated_duration, difficulty, terrain, points_of_interest,

                        facilities, route_type, activities

                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

        `)

        if err != nil {

                return 0, fmt.Errorf("failed to prepare SQLite statement: %w", err)

        }

        defer stmt.Close()

        // Begin transaction

        tx, err := sqliteDB.Begin()

        if err != nil {

                return 0, fmt.Errorf("failed to begin transaction: %w", err)

        }

        var count int64

        for rows.Next() {

                var objectID, ref, title, description, createdAt, estimatedDuration, difficulty string

                var headlineImageURL, gpxURL, videoURL, displayAddress, postcode sql.NullString

                var district, county, region, state, country, routeType sql.NullString

                var longitude, latitude, distanceKm sql.NullFloat64

                var terrain, poi, facilities, activities sql.NullString

                err := rows.Scan(

                        &objectID, &createdAt, &ref, &title, &headlineImageURL, &gpxURL,

                        &longitude, &latitude, &distanceKm, &description, &videoURL,

                        &displayAddress, &postcode, &district, &county, &region, &state,

                        &country, &estimatedDuration, &difficulty, &terrain, &poi,

                        &facilities, &routeType, &activities,

                )

                if err != nil {

                        return 0, fmt.Errorf("failed to scan row: %w", err)

                }

                terrainJSON := "[]"

                if terrain.Valid {

                        terrainJSON = terrain.String

                }

                poiJSON := "[]"

                if poi.Valid {

                        poiJSON = poi.String

                }

                facilitiesJSON := "[]"

                if facilities.Valid {

                        facilitiesJSON = facilities.String

                }

                activitiesJSON := "[]"

                if activities.Valid {

                        activitiesJSON = activities.String

                }

                headlineImageURLVal := ""

                if headlineImageURL.Valid {

                        headlineImageURLVal = headlineImageURL.String

                }

                gpxURLVal := ""

                if gpxURL.Valid {

                        gpxURLVal = gpxURL.String

                }

                videoURLVal := ""

                if videoURL.Valid {

                        videoURLVal = videoURL.String

                }

                var displayAddressVal, postcodeVal string

                if displayAddress.Valid {

                        displayAddressVal = displayAddress.String

                }

                if postcode.Valid {

                        postcodeVal = postcode.String

                }

                districtVal := ""

                if district.Valid {

                        districtVal = district.String

                }

                countyVal := ""

                if county.Valid {

                        countyVal = county.String

                }

                regionVal := ""

                if region.Valid {

                        regionVal = region.String

                }

                stateVal := ""

                if state.Valid {

                        stateVal = state.String

                }

                countryVal := ""

                if country.Valid {

                        countryVal = country.String

                }

                routeTypeVal := ""

                if routeType.Valid {

                        routeTypeVal = routeType.String

                }

                var latVal, lonVal, distVal float64

                if latitude.Valid {

                        latVal = latitude.Float64

                }

                if longitude.Valid {

                        lonVal = longitude.Float64

                }

                if distanceKm.Valid {

                        distVal = distanceKm.Float64

                }

                _, err = stmt.Exec(

                        objectID, createdAt, ref, title, headlineImageURLVal, gpxURLVal,

                        latVal, lonVal, distVal, description, videoURLVal,

                        displayAddressVal, postcodeVal, districtVal, countyVal, regionVal, stateVal,

                        countryVal, estimatedDuration, difficulty, terrainJSON, poiJSON,

                        facilitiesJSON, routeTypeVal, activitiesJSON,

                )

                if err != nil {

                        tx.Rollback()

                        return 0, fmt.Errorf("failed to insert route %s: %w", objectID, err)

                }

                count++

                if count%1000 == 0 {

                        log.Printf("  ⏳ Migrated %d routes...", count)

                }

        if rows.Err() != nil {

                tx.Rollback()

                return 0, fmt.Errorf("query error: %w", rows.Err())

        }

        if err := tx.Commit(); err != nil {

                return 0, fmt.Errorf("failed to commit transaction: %w", err)

        }

        return count, nil

func migrateNearby(pgPool *pgxpool.Pool, sqliteDB *sql.DB) (int64, error) {

        ctx := context.Background()

        rows, err := pgPool.Query(ctx, "SELECT route_object_id, description, object_id, ref FROM nearby")

        if err != nil {

                return 0, err

        }

        defer rows.Close()

        stmt, err := sqliteDB.Prepare("INSERT INTO nearby (route_object_id, description, object_id, ref) VALUES (?, ?, ?, ?)")

        if err != nil {

                return 0, err

        }

        defer stmt.Close()

        var count int64

        for rows.Next() {

                var routeID, description, objectID, ref string

                if err := rows.Scan(&routeID, &description, &objectID, &ref); err != nil {

                        return 0, err

                }

                if _, err := stmt.Exec(routeID, description, objectID, ref); err != nil {

                        return 0, err

                }

                count++

        return count, rows.Err()

func migrateImages(pgPool *pgxpool.Pool, sqliteDB *sql.DB) (int64, error) {

        ctx := context.Background()

        rows, err := pgPool.Query(ctx, "SELECT route_object_id, src, title, caption FROM images")

        if err != nil {

                return 0, err

        }

        defer rows.Close()

        stmt, err := sqliteDB.Prepare("INSERT INTO images (route_object_id, src, title, caption) VALUES (?, ?, ?, ?)")

        if err != nil {

                return 0, err

        }

        defer stmt.Close()

        var count int64

        for rows.Next() {

                var routeID, src, title, caption string

                if err := rows.Scan(&routeID, &src, &title, &caption); err != nil {

                        return 0, err

                }

                if _, err := stmt.Exec(routeID, src, title, caption); err != nil {

                        return 0, err

                }

                count++

        return count, rows.Err()

func migrateDetails(pgPool *pgxpool.Pool, sqliteDB *sql.DB) (int64, error) {

        ctx := context.Background()

        rows, err := pgPool.Query(ctx, "SELECT route_object_id, subtitle, content FROM details")

        if err != nil {

                return 0, err

        }

        defer rows.Close()

        stmt, err := sqliteDB.Prepare("INSERT INTO details (route_object_id, subtitle, content) VALUES (?, ?, ?)")

        if err != nil {

                return 0, err

        }

        defer stmt.Close()

        var count int64

        for rows.Next() {

                var routeID, subtitle, content string

                if err := rows.Scan(&routeID, &subtitle, &content); err != nil {

                        return 0, err

                }

                if _, err := stmt.Exec(routeID, subtitle, content); err != nil {

                        return 0, err

                }

                count++

        return count, rows.Err()

func validateMigration(pgPool *pgxpool.Pool, sqliteDB *sql.DB, expectedCount int64) error {

        var sqliteRouteCount int64

        err := sqliteDB.QueryRow("SELECT COUNT(*) FROM routes").Scan(&sqliteRouteCount)

        if err != nil {

                return fmt.Errorf("failed to get SQLite count: %w", err)

        }

        if expectedCount != sqliteRouteCount {

                return fmt.Errorf("route count mismatch: expected=%d, SQLite=%d", expectedCount, sqliteRouteCount)

        }

        log.Printf("✅ Record counts match: %d routes", sqliteRouteCount)

        return nil

func arrayToJSON(pgArray string) string {

        if pgArray == "" || pgArray == "{}" {

                return "[]"

        }

        pgArray = pgArray[1 : len(pgArray)-1]

        // Split by comma and quote each element

        elements := make([]string, 0)

        var current string

        for _, char := range pgArray {

                if char == ',' {

                        if current != "" {

                                elements = append(elements, fmt.Sprintf(`"%s"`, current))

                                current = ""

                        }

                } else {

                        current += string(char)

                }

        if current != "" {

                elements = append(elements, fmt.Sprintf(`"%s"`, current))

        }

        jsonArray := "["

        for i, elem := range elements {

                jsonArray += elem

                if i < len(elements)-1 {

                        jsonArray += ","

                }

        jsonArray += "]"

        return jsonArray

func maskConnStr(connStr string) string {

        // Mask password for logging

        if len(connStr) > 20 {

                return connStr[:10] + "***" + connStr[len(connStr)-10:]

        }

        return "***"

func createSchemaWithoutFTS5(db *sql.DB) error {

        // Create basic schema without FTS5 virtual table and triggers

        schema := `

-- SQLite3 Schema for gps-routes-api (without FTS5)

-- Basic functionality without full-text search

-- Enable required features

PRAGMA foreign_keys = ON;

PRAGMA journal_mode = WAL;

-- Main routes table

CREATE TABLE routes (

    object_id TEXT PRIMARY KEY,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    ref TEXT NOT NULL,

    title TEXT NOT NULL,

    headline_image_url TEXT,

    gpx_url TEXT,

    latitude REAL,

    longitude REAL,

    _geoloc BLOB,

    distance_km REAL,

    description TEXT NOT NULL,

    video_url TEXT,

    display_address TEXT,

    postcode TEXT,

    district TEXT,

    county TEXT,

    region TEXT,

    state TEXT,

    country TEXT,

    estimated_duration TEXT,

    difficulty TEXT,

    terrain JSON,

    points_of_interest JSON,

    facilities JSON,

    route_type TEXT,

    activities JSON,

    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

);

-- Scalar field indexes (B-tree)

CREATE INDEX idx_routes_district ON routes (district);

CREATE INDEX idx_routes_county ON routes (county);

CREATE INDEX idx_routes_region ON routes (region);

CREATE INDEX idx_routes_country ON routes (country);

CREATE INDEX idx_routes_route_type ON routes (route_type);

CREATE INDEX idx_routes_difficulty ON routes (difficulty);

-- JSON expression indexes for array fields

CREATE INDEX idx_routes_terrain ON routes (json_extract(terrain, '$[0]'));

CREATE INDEX idx_routes_activities ON routes (json_extract(activities, '$[0]'));

CREATE INDEX idx_routes_facilities ON routes (json_extract(facilities, '$[0]'));

-- Spatial indexes (lat/lng B-tree for bounding box queries)

CREATE INDEX idx_routes_lat_lng ON routes (latitude, longitude);

-- Related tables

CREATE TABLE nearby (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    route_object_id TEXT NOT NULL REFERENCES routes(object_id) ON DELETE CASCADE,

    description TEXT,

    object_id TEXT,

    ref TEXT

);

CREATE TABLE images (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    route_object_id TEXT NOT NULL REFERENCES routes(object_id) ON DELETE CASCADE,

    src TEXT NOT NULL,

    title TEXT,

    caption TEXT

);

CREATE TABLE details (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    route_object_id TEXT NOT NULL REFERENCES routes(object_id) ON DELETE CASCADE,

    subtitle TEXT NOT NULL,

    content TEXT NOT NULL

);

-- Performance pragmas

PRAGMA cache_size = 10000;

PRAGMA synchronous = NORMAL;

PRAGMA temp_store = MEMORY;

        `

        if _, err := db.Exec(schema); err != nil {

                return fmt.Errorf("failed to create basic schema: %w", err)

        }

        return nil