hotschpotsh/Pottery-website/server/db_setup.js

const { Client } = require('pg');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

async function setup() {
  // Connection config for the default 'postgres' database
  const config = {
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || 'localhost',
    password: process.env.DB_PASSWORD || '',
    port: process.env.DB_PORT || 5432,
    database: 'postgres'
  };

  const client = new Client(config);

  try {
    await client.connect();
    console.log('Connected to PostgreSQL default database.');

    // Create database
    try {
      await client.query(`CREATE DATABASE ${process.env.DB_NAME || 'pottery_db'}`);
      console.log(`Database ${process.env.DB_NAME || 'pottery_db'} created.`);
    } catch (err) {
      if (err.code === '42P04') {
        console.log(`Database ${process.env.DB_NAME || 'pottery_db'} already exists.`);
      } else {
        throw err;
      }
    }
    await client.end();

    // Connect to the new database to create tables
    const dbClient = new Client({
      ...config,
      database: process.env.DB_NAME || 'pottery_db'
    });

    await dbClient.connect();
    console.log(`Connected to ${process.env.DB_NAME || 'pottery_db'}.`);

    const schemaPath = path.join(__dirname, 'schema.sql');
    const schema = fs.readFileSync(schemaPath, 'utf8');

    await dbClient.query(schema);
    console.log('Tables created successfully.');

    // --- SEED DATA ---
    console.log('Seeding initial data...');

    const products = [
      {
        title: 'Tableware',
        price: 185,
        image: '/collection-tableware.png',
        description: 'A complete hand-thrown tableware set for four. Finished in our signature matte white glaze with raw clay rims.',
        gallery: ['/collection-tableware.png', '/pottery-plates.png', '/ceramic-cups.png'],
        slug: 'tableware-set',
        number: '01',
        aspect_ratio: 'aspect-[3/4]'
      },
      {
        title: 'Lighting',
        price: 240,
        image: '/collection-lighting.png',
        description: 'Sculptural ceramic pendant lights that bring warmth and texture to any space. Each piece is unique.',
        gallery: ['/collection-lighting.png', '/pottery-studio.png', '/collection-vases.png'],
        slug: 'ceramic-lighting',
        number: '04',
        aspect_ratio: 'aspect-[4/3]'
      },
      {
        title: 'Vases',
        price: 95,
        image: '/collection-vases.png',
        description: 'Organic forms inspired by the dunes of Padre Island. Perfect for dried stems or fresh bouquets.',
        gallery: ['/collection-vases.png', '/pottery-vase.png', '/collection-lighting.png'],
        slug: 'organic-vases',
        number: '02',
        aspect_ratio: 'aspect-square'
      }
    ];

    for (const p of products) {
      await dbClient.query(
        'INSERT INTO products (title, price, image, description, gallery, slug, number, aspect_ratio) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT DO NOTHING',
        [p.title, p.price, p.image, p.description, JSON.stringify(p.gallery), p.slug, p.number, p.aspect_ratio]
      );
    }

    const articles = [
      {
        title: 'Product Photography for Small Businesses',
        date: 'Oct 03',
        image: 'https://lh3.googleusercontent.com/aida-public/AB6AXuAipMlYLTcRT_hdc3VePfFIlrA56VzZ5G2y3gcRfmIZMERwGFKq2N19Gqo6mw7uZowXmjl2eJ89TI3Mcud2OyOfadO3mPVF_v0sI0OHupqM49WEFcWzH-Wbu3DL6bQ46F2Y8SIAk-NUQy8psjcIdBKRrM8fqdn4eOPANYTXpVxkLMAm4R0Axy4aEKNdmj917ZKKTxvXB-J8nGlITJkJ-ua7XcZOwGnfK5ttzyWW35A0oOSffCf972gmpV27wrVQgYJNLS7UyDdyQIQ',
        slug: 'product-photography-for-small-businesses',
        category: 'Studio',
        description: "Learning that beautiful products aren't enough on their own — you also need beautiful photos to tell the story.",
        sections: [
          { id: '1', type: 'text', content: 'Mastering Product Photography for Pottery is essential because in the world of handmade business, your work is only as good as the photo that represents it.' },
          { id: '2', type: 'image', content: 'https://images.unsplash.com/photo-1516483638261-f4dbaf036963?q=80&w=2574&auto=format&fit=crop' },
          { id: '3', type: 'text', content: 'Lighting is the single most critical element of successful Product Photography for Pottery. Avoid the harsh, yellow glow of indoor lamps.' }
        ]
      },
      {
        title: 'The Art of Packaging',
        date: 'Jul 15',
        image: 'https://lh3.googleusercontent.com/aida-public/AB6AXuAaWGnX_NYT3S_lOflL2NJZGbWge4AAkvra4ymvF8ag-c1UKsOAIB-rsLVQXW5xIlPZipDiK8-rsLVQXW5xIlPZipDiK8-ysPyv22xdgsvzs4EOXSSCcrT4Lb2YCe0u5orxRaZEA5TgxeoKq15zaWKSlmnHyPGjPd_7yglpfO13eZmbU5KaxFJ1KGO0UAxoO9BpsyCYgbgINMoSz3epGe5ZdwBWRH-5KCzjoLuXimFTLcd5bqg9T1YofTxgy2hWBMJzKkafyEniq8dP6hMmfNCLVcCHHHx0hRU',
        slug: 'the-art-of-packaging',
        category: 'Guide',
        description: "A practical guide for potters who want to package and send their handmade ceramics with care and confidence.",
        sections: [
          { id: '1', type: 'text', content: 'When considering safe delivery—especially for large items—the double-box method is the industry standard.' },
          { id: '2', type: 'text', content: 'The Outer Box: Place the small box inside a larger shipping box, with at least 2 inches of padding on all sides.' }
        ]
      }
    ];

    for (const a of articles) {
      await dbClient.query(
        'INSERT INTO articles (title, date, image, sections, slug, category, description) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT DO NOTHING',
        [a.title, a.date, a.image, JSON.stringify(a.sections), a.slug, a.category, a.description]
      );
    }

    console.log('Seeding complete.');
    await dbClient.end();
    console.log('Setup complete!');
  } catch (err) {
    console.error('Setup failed:', err);
    console.log('\nTIP: Make sure PostgreSQL is running and your password in .env is correct.');
    process.exit(1);
  }
}

setup();