Gallus_Pub/backend/migrate-production.js

// Production migration script - can be run directly with node
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';
import fs from 'fs';
import path from 'path';
import sharp from 'sharp';

// Database schema
const events = sqliteTable('events', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text('title').notNull(),
  date: text('date').notNull(),
  description: text('description').notNull(),
  imageUrl: text('image_url').notNull(),
  displayOrder: integer('display_order').notNull(),
  isPublished: integer('is_published', { mode: 'boolean' }).default(true),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).default(sql`(unixepoch())`),
});

const galleryImages = sqliteTable('gallery_images', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  imageUrl: text('image_url').notNull(),
  altText: text('alt_text').notNull(),
  displayOrder: integer('display_order').notNull(),
  isPublished: integer('is_published', { mode: 'boolean' }).default(true),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(unixepoch())`),
});

// Old events data
const oldEvents = [
  {
    title: "Karaoke",
    date: "2025-12-31",
    description: `Bei uns gibt es Karaoke Mi-Sa!! <br>Seid ihr eine Gruppe und lieber unter euch? ..unseren 2.Stock kannst du auch mieten ;) <br>Reserviere am besten gleich per Whatsapp <a href="tel:+41772322770">077 232 27 70</a>`,
    imageUrl: "/images/events/event_karaoke.webp",
    displayOrder: 0,
  },
  {
    title: "Pub Quiz",
    date: "2025-12-31",
    description: `Jeden Freitag findet unser <b>Pub Quiz</b> statt. Gespielt wird tischweise in 3-4 Runden. <br>Jede Woche gibt es ein anderes Thema. Es geht um Ruhm und Ehre und zusätzlich werden die Sieger der Herzen durch das Publikum gekürt! <3 <br>Auch Einzelpersonen sind herzlich willkommen! <br>*zum mitmachen minimum 1 Getränk konsumieren oder 5CHF`,
    displayOrder: 1,
  },
  {
    title: "Schlager Hüttenzauber Karaoke",
    date: "2025-11-27",
    description: `Ab 19:00 Uhr Eintritt ist Frei! Reservieren unter <a href="tel:+41772322770">077 232 27 70</a>`,
    imageUrl: "/images/events/event_schlager-karaoke.webp",
    displayOrder: 2,
  },
  {
    title: "Adventskalender",
    date: "2025-12-20",
    description: `Jeden Tag neue Überraschungen! Check unsere Social Media Stories!`,
    imageUrl: "/images/events/event_advents-kalender.webp",
    displayOrder: 3,
  },
  {
    title: "Santa Karaoke-Party",
    date: "2025-12-06",
    description: `🤶🏻🎅🏻Komme als Weihnachts-Mann/-Frau und bekomme einen Shot auf's Haus!🤶🏻🎅🏻`,
    imageUrl: "/images/events/event_santa_karaoke.webp",
    displayOrder: 4,
  },
  {
    title: "Weihnachtsferien",
    date: "2025-12-21",
    description: `Wir sind ab 02.01.2026 wieder wie gewohnt für euch da! 🍀. <br> Für Anfragen WA <a href="tel:+41772322770">077 232 27 70</a> Antwort innerhalb 48h`,
    imageUrl: "/images/events/event_ferien.webp",
    displayOrder: 5,
  },
  {
    title: "Neujahrs-Apero",
    date: "2026-01-02",
    description: `18:00-20:00 Uhr`,
    imageUrl: "/images/events/event_neujahrs-apero.webp",
    displayOrder: 6,
  },
];

// Old gallery images
const oldGalleryImages = [
  { imageUrl: "/images/gallery/Gallery7.webp", alt: "Gallery 7", order: 0 },
  { imageUrl: "/images/gallery/Gallery8.webp", alt: "Gallery 8", order: 1 },
  { imageUrl: "/images/gallery/Gallery9.webp", alt: "Gallery 9", order: 2 },
  { imageUrl: "/images/gallery/Gallery6.webp", alt: "Gallery 6", order: 3 },
  { imageUrl: "/images/gallery/Gallery1.webp", alt: "Gallery 1", order: 4 },
  { imageUrl: "/images/gallery/Gallery2.webp", alt: "Gallery 2", order: 5 },
  { imageUrl: "/images/gallery/Gallery3.webp", alt: "Gallery 3", order: 6 },
  { imageUrl: "/images/gallery/Gallery4.webp", alt: "Gallery 4", order: 7 },
  { imageUrl: "/images/gallery/Gallery5.webp", alt: "Gallery 5", order: 8 },
];

async function main() {
  console.log('=== Production Migration Script ===\n');

  const dbPath = process.env.DATABASE_PATH || '/app/data/gallus_cms.db';
  console.log('Database path:', dbPath);

  // Check if database exists
  if (!fs.existsSync(dbPath)) {
    console.error('ERROR: Database not found at:', dbPath);
    console.error('Please ensure the backend has been started at least once to create the database.');
    process.exit(1);
  }

  // Check if images exist
  const dataDir = process.env.GIT_WORKSPACE_DIR || '/app/data';
  const eventsDir = path.join(dataDir, 'images', 'events');
  const galleryDir = path.join(dataDir, 'images', 'gallery');

  console.log('Events images directory:', eventsDir);
  console.log('Gallery images directory:', galleryDir);

  if (!fs.existsSync(eventsDir)) {
    console.error('ERROR: Events images directory not found:', eventsDir);
    process.exit(1);
  }

  if (!fs.existsSync(galleryDir)) {
    console.error('ERROR: Gallery images directory not found:', galleryDir);
    process.exit(1);
  }

  // List available images
  console.log('\nAvailable event images:', fs.readdirSync(eventsDir));
  console.log('Available gallery images:', fs.readdirSync(galleryDir));

  // Connect to database
  const sqlite = new Database(dbPath);
  const db = drizzle(sqlite);

  console.log('\n=== Migrating Events ===\n');

  for (const event of oldEvents) {
    try {
      const [newEvent] = await db.insert(events).values({
        title: event.title,
        date: event.date,
        description: event.description,
        imageUrl: event.imageUrl,
        displayOrder: event.displayOrder,
        isPublished: true,
      }).returning();

      console.log(`✓ Migrated event: ${newEvent.title}`);
    } catch (error) {
      console.error(`✗ Failed to migrate event "${event.title}":`, error.message);
    }
  }

  console.log('\n=== Migrating Gallery Images ===\n');

  for (const img of oldGalleryImages) {
    try {
      const [newImage] = await db.insert(galleryImages).values({
        imageUrl: img.imageUrl,
        altText: img.alt,
        displayOrder: img.order,
        isPublished: true,
      }).returning();

      console.log(`✓ Migrated gallery image: ${newImage.altText}`);
    } catch (error) {
      console.error(`✗ Failed to migrate gallery image "${img.alt}":`, error.message);
    }
  }

  sqlite.close();

  console.log('\n✓ Migration completed successfully!');
  console.log('\nYou can verify the migration by visiting:');
  console.log('- Frontend: https://gallus-pub.ch/');
  console.log('- Admin: https://gallus-pub.ch/admin');
}

main().catch(error => {
  console.error('\n✗ Migration failed:', error);
  process.exit(1);
});