← back to Animate Museum Posts

src/database.js

210 lines

import pg from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const { Pool } = pg;

// PostgreSQL connection
const pool = new Pool({
  host: process.env.POSTGRES_HOST || 'localhost',
  port: process.env.POSTGRES_PORT || 5432,
  database: process.env.POSTGRES_DB || 'museum_posts',
  user: process.env.POSTGRES_USER || 'postgres',
  password: process.env.POSTGRES_PASSWORD || 'postgres'
});

/**
 * Initialize database schema
 */
export async function initDatabase() {
  const client = await pool.connect();
  try {
    await client.query(`
      CREATE TABLE IF NOT EXISTS scheduled_posts (
        id SERIAL PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        artist VARCHAR(255),
        year VARCHAR(50),
        museum VARCHAR(255),
        video_path TEXT NOT NULL,
        thumbnail_path TEXT,
        scheduled_time TIMESTAMP NOT NULL,
        status VARCHAR(50) DEFAULT 'pending',
        tweet_id VARCHAR(100),
        tweet_url TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        posted_at TIMESTAMP,
        error_message TEXT,
        motion_settings JSONB
      )
    `);

    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_scheduled_time ON scheduled_posts(scheduled_time);
      CREATE INDEX IF NOT EXISTS idx_status ON scheduled_posts(status);
    `);

    console.log('Database initialized');
  } finally {
    client.release();
  }
}

/**
 * Add a scheduled post
 */
export async function addScheduledPost(post) {
  const { title, artist, year, museum, videoPath, thumbnailPath, scheduledTime, motionSettings } = post;

  const result = await pool.query(`
    INSERT INTO scheduled_posts
    (title, artist, year, museum, video_path, thumbnail_path, scheduled_time, motion_settings)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
    RETURNING id
  `, [title, artist, year, museum, videoPath, thumbnailPath, scheduledTime, JSON.stringify(motionSettings)]);

  return result.rows[0].id;
}

/**
 * Get all scheduled posts
 */
export async function getAllPosts() {
  const result = await pool.query(`
    SELECT * FROM scheduled_posts
    ORDER BY scheduled_time ASC
  `);
  return result.rows;
}

/**
 * Get pending posts ready to be posted
 */
export async function getPendingPosts() {
  const result = await pool.query(`
    SELECT * FROM scheduled_posts
    WHERE status = 'pending'
    AND scheduled_time <= NOW()
    ORDER BY scheduled_time ASC
  `);
  return result.rows;
}

/**
 * Get upcoming posts
 */
export async function getUpcomingPosts() {
  const result = await pool.query(`
    SELECT * FROM scheduled_posts
    WHERE status = 'pending'
    ORDER BY scheduled_time ASC
  `);
  return result.rows;
}

/**
 * Update post status after posting
 */
export async function markAsPosted(id, tweetId, tweetUrl) {
  await pool.query(`
    UPDATE scheduled_posts
    SET status = 'posted',
        tweet_id = $2,
        tweet_url = $3,
        posted_at = NOW()
    WHERE id = $1
  `, [id, tweetId, tweetUrl]);
}

/**
 * Mark post as failed
 */
export async function markAsFailed(id, errorMessage) {
  await pool.query(`
    UPDATE scheduled_posts
    SET status = 'failed',
        error_message = $2
    WHERE id = $1
  `, [id, errorMessage]);
}

/**
 * Delete a scheduled post
 */
export async function deletePost(id) {
  // Get the video path first to delete the file
  const result = await pool.query(`
    SELECT video_path, thumbnail_path FROM scheduled_posts WHERE id = $1
  `, [id]);

  if (result.rows.length > 0) {
    await pool.query(`DELETE FROM scheduled_posts WHERE id = $1`, [id]);
    return result.rows[0];
  }
  return null;
}

/**
 * Get post by ID
 */
export async function getPostById(id) {
  const result = await pool.query(`
    SELECT * FROM scheduled_posts WHERE id = $1
  `, [id]);
  return result.rows[0];
}

/**
 * Update a scheduled post
 */
export async function updatePost(id, updates) {
  const { title, artist, scheduledTime, status } = updates;
  const setClauses = [];
  const values = [id];
  let paramIndex = 2;

  if (title !== undefined) {
    setClauses.push(`title = $${paramIndex++}`);
    values.push(title);
  }
  if (artist !== undefined) {
    setClauses.push(`artist = $${paramIndex++}`);
    values.push(artist);
  }
  if (scheduledTime !== undefined) {
    setClauses.push(`scheduled_time = $${paramIndex++}`);
    values.push(scheduledTime);
  }
  if (status !== undefined) {
    setClauses.push(`status = $${paramIndex++}`);
    values.push(status);
  }

  if (setClauses.length === 0) return null;

  const result = await pool.query(`
    UPDATE scheduled_posts
    SET ${setClauses.join(', ')}
    WHERE id = $1
    RETURNING *
  `, values);

  return result.rows[0];
}

/**
 * Get posting status (paused or active)
 */
let postingPaused = false;

export function isPaused() {
  return postingPaused;
}

export function setPaused(paused) {
  postingPaused = paused;
}

export { pool };