← back to Designer Wallcoverings
DW-Agents/dw-agents/dw-blog-agent/server.js
1256 lines
const express = require('express');
const session = require('express-session');
const cookieParser = require('cookie-parser');
const cors = require('cors');
const path = require('path');
const { Pool } = require('pg');
const https = require('https');
const app = express();
const PORT = process.env.PORT || 9889;
// -------------------------------------------------------------------
// Database
// -------------------------------------------------------------------
const pool = new Pool({
connectionString: (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified')
});
// Initialize blog_posts table
async function initDatabase() {
try {
await pool.query(`
CREATE TABLE IF NOT EXISTS blog_posts (
id SERIAL PRIMARY KEY,
shopify_blog_id BIGINT,
shopify_article_id BIGINT,
title VARCHAR(500) NOT NULL,
body_html TEXT NOT NULL,
summary_html TEXT,
seo_title VARCHAR(200),
seo_description VARCHAR(320),
author VARCHAR(200) DEFAULT 'Designer Wallcoverings',
tags TEXT,
handle VARCHAR(500),
image_url TEXT,
vendor VARCHAR(200),
dw_sku VARCHAR(50),
product_id VARCHAR(50),
product_handle VARCHAR(500),
instagram_post_id INTEGER,
status VARCHAR(20) DEFAULT 'draft',
scheduled_at TIMESTAMP,
published_at TIMESTAMP,
error_message TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
)
`);
console.log('blog_posts table ready');
} catch (err) {
console.error('Database init error:', err.message);
}
}
initDatabase();
// -------------------------------------------------------------------
// Middleware
// -------------------------------------------------------------------
app.use(cors({ origin: true, credentials: true }));
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(session({
secret: 'dw-blog-agent-secret-2024',
resave: false,
saveUninitialized: false,
cookie: { maxAge: 24 * 60 * 60 * 1000 }
}));
// -------------------------------------------------------------------
// Authentication
// -------------------------------------------------------------------
const AUTH_USERNAME = 'admin';
const AUTH_PASSWORD = process.env.ADMIN_PASSWORD;
const SSO_TOKEN_NAME = 'dw_agents_sso_token';
const SSO_TOKEN_VALUE = 'dw-agents-authenticated-2025-session';
function isAuthenticated(req) {
if (req.session && req.session.authenticated) return true;
if (req.cookies && req.cookies[SSO_TOKEN_NAME] === SSO_TOKEN_VALUE) {
if (req.session) req.session.authenticated = true;
return true;
}
const authHeader = req.headers.authorization;
if (authHeader && authHeader.startsWith('Basic ')) {
const decoded = Buffer.from(authHeader.slice(6), 'base64').toString();
const [user, pass] = decoded.split(':');
if (user === AUTH_USERNAME && pass === AUTH_PASSWORD) {
if (req.session) req.session.authenticated = true;
return true;
}
}
return false;
}
function authMiddleware(req, res, next) {
if (req.path === '/login' || req.path === '/api/login') return next();
if (req.path === '/api/health' || req.path === '/api/status') return next();
if (isAuthenticated(req)) return next();
if (req.path.startsWith('/api/')) {
return res.status(401).json({ error: 'Authentication required' });
}
return res.redirect('/login');
}
app.use(authMiddleware);
// Static files
app.use(express.static(path.join(__dirname, 'public')));
// -------------------------------------------------------------------
// Shopify Blog API Layer
// -------------------------------------------------------------------
const SHOPIFY_STORE = 'designer-laboratory-sandbox.myshopify.com';
const SHOPIFY_TOKEN = (process.env.SHOPIFY_ADMIN_TOKEN || ''); // Marketing token - has write_content scope
const SHOPIFY_API_VERSION = '2024-01';
function shopifyRequest(method, apiPath, data = null) {
return new Promise((resolve, reject) => {
const options = {
hostname: SHOPIFY_STORE,
path: `/admin/api/${SHOPIFY_API_VERSION}${apiPath}`,
method,
headers: {
'X-Shopify-Access-Token': SHOPIFY_TOKEN,
'Content-Type': 'application/json'
}
};
const req = https.request(options, (res) => {
let body = '';
res.on('data', c => body += c);
res.on('end', () => {
try { resolve({ status: res.statusCode, data: JSON.parse(body) }); }
catch { resolve({ status: res.statusCode, data: body }); }
});
});
req.setTimeout(30000, () => {
req.destroy();
reject(new Error('Shopify request timeout after 30s'));
});
req.on('error', reject);
if (data) req.write(JSON.stringify(data));
req.end();
});
}
// Use the existing "Designer Wallcoverings News" blog
let cachedBlogId = 89036455987;
async function getOrCreateBlog() {
return cachedBlogId;
}
async function createArticle(blogId, articleData) {
const payload = {
article: {
title: articleData.title,
body_html: articleData.body_html,
author: articleData.author || 'Designer Wallcoverings',
tags: articleData.tags || '',
published: true
}
};
if (articleData.image_url) {
payload.article.image = { src: articleData.image_url };
}
return shopifyRequest('POST', `/blogs/${blogId}/articles.json`, payload);
}
async function deleteArticle(blogId, articleId) {
return shopifyRequest('DELETE', `/blogs/${blogId}/articles/${articleId}.json`);
}
// -------------------------------------------------------------------
// Slack Notification
// -------------------------------------------------------------------
function sendSlackNotification(text) {
const msg = JSON.stringify({ text });
const req = https.request({
hostname: 'hooks.slack.com',
path: '/services/T03U65C1G7J/B09RCFHS7PW/7Izxc7OGsDWKPdRALLOocO6O',
method: 'POST',
headers: { 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(msg) }
}, res => { let b = ''; res.on('data', c => b += c); res.on('end', () => console.log('Slack:', b)); });
req.on('error', e => console.error('Slack error:', e.message));
req.write(msg);
req.end();
}
// -------------------------------------------------------------------
// Blog Content Generator
// -------------------------------------------------------------------
function generateBlogContent({ patternName, colorName, vendor, description, dwSku, imageUrl, productHandle }) {
const cleanPatternName = (patternName || 'Wallcovering').replace(/-SAMPLE/g, '');
const cleanColorName = colorName || '';
const cleanVendor = vendor || 'Designer Wallcoverings';
const cleanDwSku = (dwSku || '').replace(/-SAMPLE$/, '');
const cleanDescription = description || `Discover the stunning ${cleanPatternName} wallcovering${cleanColorName ? ' in ' + cleanColorName : ''} from ${cleanVendor}. This exquisite wallcovering brings sophistication and elegance to any interior space, perfect for both commercial and residential applications.`;
const productUrl = productHandle
? `https://www.designerwallcoverings.com/products/${productHandle}`
: 'https://www.designerwallcoverings.com';
const title = `${cleanPatternName}${cleanColorName ? ' ' + cleanColorName : ''} Wallcovering | ${cleanVendor}`;
const heroImageHtml = imageUrl
? `<div style="text-align:center;margin-bottom:30px;"><img src="${imageUrl}" alt="${cleanPatternName} ${cleanColorName} Wallcovering by ${cleanVendor}" style="max-width:100%;border-radius:8px;box-shadow:0 4px 12px rgba(0,0,0,0.1);" /></div>`
: '';
const body_html = `
${heroImageHtml}
<div style="font-family:Georgia,serif;line-height:1.8;color:#333;">
<h2 style="font-size:28px;color:#1a1a2e;margin-bottom:16px;">${cleanPatternName}${cleanColorName ? ' in ' + cleanColorName : ''}</h2>
<p style="font-size:14px;color:#666;margin-bottom:24px;">By <strong>${cleanVendor}</strong>${cleanDwSku ? ' | SKU: ' + cleanDwSku : ''}</p>
<div style="margin-bottom:32px;">
<p style="font-size:16px;line-height:1.8;">${cleanDescription}</p>
</div>
<div style="background:#f8f9fa;border-left:4px solid #0f766e;padding:20px;margin:24px 0;border-radius:0 8px 8px 0;">
<h3 style="margin-top:0;color:#0f766e;">Product Details</h3>
<ul style="list-style:none;padding:0;margin:0;">
<li style="padding:6px 0;"><strong>Design:</strong> ${cleanPatternName}</li>
${cleanColorName ? `<li style="padding:6px 0;"><strong>Colorway:</strong> ${cleanColorName}</li>` : ''}
<li style="padding:6px 0;"><strong>Brand:</strong> ${cleanVendor}</li>
${cleanDwSku ? `<li style="padding:6px 0;"><strong>SKU:</strong> ${cleanDwSku}</li>` : ''}
<li style="padding:6px 0;"><strong>Availability:</strong> Samples and full rolls available</li>
</ul>
</div>
<div style="text-align:center;margin:32px 0;">
<a href="${productUrl}" style="display:inline-block;background:linear-gradient(135deg,#0f766e,#0284c7);color:white;padding:14px 32px;border-radius:8px;text-decoration:none;font-size:16px;font-weight:600;">Shop This Wallcovering</a>
</div>
<div style="border-top:2px solid #e5e7eb;padding-top:24px;margin-top:32px;">
<p style="font-size:14px;color:#666;text-align:center;">
<strong>Designer Wallcoverings</strong> - Your One Stop Luxury Wallcovering Resource for Over 25 Years<br/>
<a href="tel:1-888-373-4564" style="color:#0f766e;text-decoration:none;">1-888-373-4564</a> |
<a href="https://www.designerwallcoverings.com" style="color:#0f766e;text-decoration:none;">designerwallcoverings.com</a><br/>
<em>Samples available for all wallcoverings. Free shipping on orders over $100.</em>
</p>
</div>
</div>`.trim();
// SEO title - max 70 chars
let seo_title = `Shop ${cleanPatternName}${cleanColorName ? ' in ' + cleanColorName : ''} | ${cleanVendor} Wallcovering`;
if (seo_title.length > 70) {
seo_title = `Shop ${cleanPatternName} | ${cleanVendor} Wallcovering`;
}
if (seo_title.length > 70) {
seo_title = seo_title.substring(0, 67) + '...';
}
// SEO description - max 160 chars
let seo_description = `Discover ${cleanPatternName}${cleanColorName ? ' in ' + cleanColorName : ''} from ${cleanVendor}. Premium wallcovering for commercial and residential interiors. Samples available.`;
if (seo_description.length > 160) {
seo_description = seo_description.substring(0, 157) + '...';
}
// Tags
const tagParts = [cleanVendor, 'wallcovering', 'interior design'];
if (cleanPatternName && cleanPatternName !== 'Wallcovering') tagParts.push(cleanPatternName);
if (cleanColorName) tagParts.push(cleanColorName);
const tags = tagParts.join(', ');
return { title, body_html, seo_title, seo_description, tags };
}
// -------------------------------------------------------------------
// Login Routes
// -------------------------------------------------------------------
app.get('/login', (req, res) => {
if (isAuthenticated(req)) return res.redirect('/');
res.send(getLoginPage());
});
app.post('/api/login', (req, res) => {
const { username, password } = req.body;
if (username === AUTH_USERNAME && password === AUTH_PASSWORD) {
req.session.authenticated = true;
res.cookie(SSO_TOKEN_NAME, SSO_TOKEN_VALUE, {
maxAge: 30 * 24 * 60 * 60 * 1000,
httpOnly: false,
sameSite: 'lax'
});
return res.json({ success: true });
}
return res.status(401).json({ error: 'Invalid credentials' });
});
app.get('/api/logout', (req, res) => {
req.session.destroy();
res.clearCookie(SSO_TOKEN_NAME);
res.redirect('/login');
});
// -------------------------------------------------------------------
// Health Check
// -------------------------------------------------------------------
app.get('/api/health', async (req, res) => {
try {
const result = await pool.query('SELECT NOW()');
res.json({
status: 'healthy',
agent: 'dw-blog-agent',
port: PORT,
dbConnected: true,
uptime: process.uptime(),
timestamp: result.rows[0].now
});
} catch (err) {
res.status(500).json({
status: 'unhealthy',
agent: 'dw-blog-agent',
port: PORT,
error: err.message
});
}
});
app.get('/api/status', async (req, res) => {
try {
const result = await pool.query('SELECT NOW()');
res.json({
status: 'healthy',
agent: 'dw-blog-agent',
port: PORT,
dbConnected: true,
uptime: process.uptime(),
timestamp: result.rows[0].now
});
} catch (err) {
res.status(500).json({
status: 'unhealthy',
agent: 'dw-blog-agent',
port: PORT,
error: err.message
});
}
});
// -------------------------------------------------------------------
// Blog Post CRUD
// -------------------------------------------------------------------
// GET /api/posts - List posts with filtering
app.get('/api/posts', async (req, res) => {
try {
const { vendor, status, search } = req.query;
const limit = parseInt(req.query.limit) || 50;
const offset = parseInt(req.query.offset) || 0;
let whereClause = 'WHERE 1=1';
const params = [];
let paramIdx = 1;
if (vendor) {
whereClause += ` AND vendor = $${paramIdx++}`;
params.push(vendor);
}
if (status) {
whereClause += ` AND status = $${paramIdx++}`;
params.push(status);
}
if (search) {
whereClause += ` AND (title ILIKE $${paramIdx} OR body_html ILIKE $${paramIdx} OR tags ILIKE $${paramIdx})`;
params.push(`%${search}%`);
paramIdx++;
}
const countResult = await pool.query(
`SELECT COUNT(*) as total FROM blog_posts ${whereClause}`,
params
);
const result = await pool.query(
`SELECT * FROM blog_posts ${whereClause} ORDER BY created_at DESC LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
[...params, limit, offset]
);
res.json({
success: true,
posts: result.rows,
total: parseInt(countResult.rows[0].total),
limit,
offset
});
} catch (err) {
console.error('Error listing posts:', err);
res.status(500).json({ error: err.message });
}
});
// GET /api/posts/:id - Single post
app.get('/api/posts/:id', async (req, res) => {
try {
const { id } = req.params;
const result = await pool.query('SELECT * FROM blog_posts WHERE id = $1', [id]);
if (result.rows.length === 0) {
return res.status(404).json({ error: 'Post not found' });
}
res.json({ success: true, post: result.rows[0] });
} catch (err) {
console.error('Error fetching post:', err);
res.status(500).json({ error: err.message });
}
});
// POST /api/posts - Create post
app.post('/api/posts', async (req, res) => {
try {
const {
title, body_html, summary_html, seo_title, seo_description,
author, tags, image_url, vendor, dw_sku, product_id,
product_handle, instagram_post_id, status, scheduled_at
} = req.body;
if (!title || !body_html) {
return res.status(400).json({ error: 'title and body_html are required' });
}
// FILTER: Skip posts with "Unknown" in the title
if (title.toLowerCase().includes('unknown')) {
return res.status(400).json({ error: 'Rejected: title contains "Unknown"', skipped: true });
}
// PRIVATE LABEL: Drop all references to Reid Witlin/Whitlin
function scrubPrivateLabel(str) {
if (!str) return str;
return str
.replace(/Reid\s+Wh?it?lin\s+Textiles?\s*Wallcoverings?/gi, 'Phillipe Romano')
.replace(/Reid\s+Wh?it?lin\s+Textiles?/gi, 'Phillipe Romano')
.replace(/Reid\s+Wh?it?lin/gi, 'Phillipe Romano')
.replace(/\s{2,}/g, ' ')
.trim();
}
// FILTER: Check if this SKU was already blogged (dedup by base SKU)
const baseSku = (dw_sku || '').replace(/-SAMPLE$/, '');
if (baseSku) {
const dupCheck = await pool.query(
`SELECT id FROM blog_posts WHERE dw_sku = $1 OR dw_sku = $2 LIMIT 1`,
[baseSku, baseSku + '-SAMPLE']
);
if (dupCheck.rows.length > 0) {
return res.status(409).json({ error: `SKU ${baseSku} already has a blog post`, skipped: true });
}
}
const postStatus = status || 'draft';
if (postStatus === 'scheduled' && !scheduled_at) {
return res.status(400).json({ error: 'scheduled_at is required when status is scheduled' });
}
// Clean SKU: strip -SAMPLE suffix
const cleanSku = (dw_sku || '').replace(/-SAMPLE$/, '') || null;
// Clean title: remove -SAMPLE and scrub private label names
const cleanTitle = scrubPrivateLabel(title.replace(/-SAMPLE/g, ''));
// Clean body_html: remove -SAMPLE and scrub private label names
const cleanBody = scrubPrivateLabel((body_html || '').replace(/-SAMPLE/g, ''));
const handle = cleanTitle
.toLowerCase()
.replace(/[^a-z0-9]+/g, '-')
.replace(/^-|-$/g, '')
.substring(0, 200);
const result = await pool.query(`
INSERT INTO blog_posts (
title, body_html, summary_html, seo_title, seo_description,
author, tags, handle, image_url, vendor, dw_sku, product_id,
product_handle, instagram_post_id, status, scheduled_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
RETURNING *
`, [
cleanTitle, cleanBody, summary_html || null, seo_title || null, seo_description || null,
author || 'Designer Wallcoverings', tags || null, handle, image_url || null,
vendor || null, cleanSku, product_id || null, product_handle || null,
instagram_post_id || null, postStatus, scheduled_at || null
]);
res.status(201).json({ success: true, post: result.rows[0] });
} catch (err) {
console.error('Error creating post:', err);
res.status(500).json({ error: err.message });
}
});
// PUT /api/posts/:id - Update post
app.put('/api/posts/:id', async (req, res) => {
try {
const { id } = req.params;
const fields = req.body;
// Build dynamic SET clause
const allowedFields = [
'title', 'body_html', 'summary_html', 'seo_title', 'seo_description',
'author', 'tags', 'handle', 'image_url', 'vendor', 'dw_sku', 'product_id',
'product_handle', 'instagram_post_id', 'status', 'scheduled_at',
'shopify_blog_id', 'shopify_article_id', 'published_at', 'error_message'
];
const setClauses = [];
const values = [];
let paramIdx = 1;
for (const [key, value] of Object.entries(fields)) {
if (allowedFields.includes(key)) {
setClauses.push(`${key} = $${paramIdx++}`);
values.push(value);
}
}
if (setClauses.length === 0) {
return res.status(400).json({ error: 'No valid fields to update' });
}
setClauses.push(`updated_at = NOW()`);
values.push(id);
const result = await pool.query(
`UPDATE blog_posts SET ${setClauses.join(', ')} WHERE id = $${paramIdx} RETURNING *`,
values
);
if (result.rows.length === 0) {
return res.status(404).json({ error: 'Post not found' });
}
res.json({ success: true, post: result.rows[0] });
} catch (err) {
console.error('Error updating post:', err);
res.status(500).json({ error: err.message });
}
});
// DELETE /api/posts/:id - Delete post
app.delete('/api/posts/:id', async (req, res) => {
try {
const { id } = req.params;
// Check if post exists and has Shopify article
const existing = await pool.query('SELECT * FROM blog_posts WHERE id = $1', [id]);
if (existing.rows.length === 0) {
return res.status(404).json({ error: 'Post not found' });
}
const post = existing.rows[0];
// If published to Shopify, delete from Shopify first
if (post.shopify_article_id && post.shopify_blog_id) {
try {
const deleteResult = await deleteArticle(post.shopify_blog_id, post.shopify_article_id);
console.log(`Shopify article ${post.shopify_article_id} deleted, status: ${deleteResult.status}`);
} catch (shopifyErr) {
console.error('Failed to delete Shopify article:', shopifyErr.message);
// Continue with DB delete even if Shopify delete fails
}
}
await pool.query('DELETE FROM blog_posts WHERE id = $1', [id]);
res.json({ success: true, message: `Post "${post.title}" deleted successfully` });
} catch (err) {
console.error('Error deleting post:', err);
res.status(500).json({ error: err.message });
}
});
// POST /api/posts/:id/publish - Publish post to Shopify
app.post('/api/posts/:id/publish', async (req, res) => {
try {
const { id } = req.params;
const existing = await pool.query('SELECT * FROM blog_posts WHERE id = $1', [id]);
if (existing.rows.length === 0) {
return res.status(404).json({ error: 'Post not found' });
}
const post = existing.rows[0];
// Get or create blog
const blogId = await getOrCreateBlog();
if (!blogId) {
return res.status(500).json({ error: 'Failed to get or create Shopify blog' });
}
// PRIVATE LABEL: Drop all references before publishing
function scrubPL(s) {
if (!s) return s;
return s
.replace(/Reid\s+Wh?it?lin\s+Textiles?\s*Wallcoverings?/gi, 'Phillipe Romano')
.replace(/Reid\s+Wh?it?lin\s+Textiles?/gi, 'Phillipe Romano')
.replace(/Reid\s+Wh?it?lin/gi, 'Phillipe Romano')
.replace(/\s{2,}/g, ' ')
.trim();
}
// Create article in Shopify - ALWAYS use "Designer Wallcoverings" as author
const articleResult = await createArticle(blogId, {
title: scrubPL(post.title),
body_html: scrubPL(post.body_html),
author: 'Designer Wallcoverings',
tags: scrubPL(post.tags) || '',
image_url: post.image_url
});
if (articleResult.status !== 201 || !articleResult.data || !articleResult.data.article) {
const errorMsg = articleResult.data && articleResult.data.errors
? JSON.stringify(articleResult.data.errors)
: `Shopify returned status ${articleResult.status}`;
await pool.query(
`UPDATE blog_posts SET status = 'failed', error_message = $1, updated_at = NOW() WHERE id = $2`,
[errorMsg, id]
);
return res.status(500).json({ error: errorMsg });
}
const article = articleResult.data.article;
// Update post in database
const updated = await pool.query(`
UPDATE blog_posts SET
status = 'published',
published_at = NOW(),
shopify_blog_id = $1,
shopify_article_id = $2,
error_message = NULL,
updated_at = NOW()
WHERE id = $3
RETURNING *
`, [blogId, article.id, id]);
// Send Slack notification
sendSlackNotification(
`*Blog Post Published*\n` +
`Title: ${post.title}\n` +
`Vendor: ${post.vendor || 'N/A'}\n` +
`SKU: ${post.dw_sku || 'N/A'}\n` +
`Shopify Article ID: ${article.id}\n` +
`Status: Published`
);
res.json({ success: true, post: updated.rows[0] });
} catch (err) {
console.error('Error publishing post:', err);
res.status(500).json({ error: err.message });
}
});
// PUT /api/posts/:id/reschedule - Reschedule post
app.put('/api/posts/:id/reschedule', async (req, res) => {
try {
const { id } = req.params;
const { scheduled_at } = req.body;
if (!scheduled_at) {
return res.status(400).json({ error: 'scheduled_at is required' });
}
const result = await pool.query(`
UPDATE blog_posts SET
scheduled_at = $1,
status = 'scheduled',
updated_at = NOW()
WHERE id = $2
RETURNING *
`, [scheduled_at, id]);
if (result.rows.length === 0) {
return res.status(404).json({ error: 'Post not found' });
}
res.json({ success: true, post: result.rows[0] });
} catch (err) {
console.error('Error rescheduling post:', err);
res.status(500).json({ error: err.message });
}
});
// -------------------------------------------------------------------
// Calendar
// -------------------------------------------------------------------
app.get('/api/calendar', async (req, res) => {
try {
const { start, end } = req.query;
let whereClause = '';
const params = [];
if (start && end) {
whereClause = `WHERE COALESCE(scheduled_at, published_at, created_at) >= $1 AND COALESCE(scheduled_at, published_at, created_at) <= $2`;
params.push(start, end);
} else if (start) {
whereClause = `WHERE COALESCE(scheduled_at, published_at, created_at) >= $1`;
params.push(start);
} else if (end) {
whereClause = `WHERE COALESCE(scheduled_at, published_at, created_at) <= $1`;
params.push(end);
}
const result = await pool.query(`
SELECT *,
TO_CHAR(COALESCE(scheduled_at, published_at, created_at), 'YYYY-MM-DD') as date_key
FROM blog_posts
${whereClause}
ORDER BY COALESCE(scheduled_at, published_at, created_at) ASC
`, params);
// Group by date
const dates = {};
let total = 0;
for (const row of result.rows) {
const dk = row.date_key;
if (!dates[dk]) dates[dk] = [];
dates[dk].push(row);
total++;
}
res.json({ success: true, dates, total });
} catch (err) {
console.error('Error fetching calendar:', err);
res.status(500).json({ error: err.message });
}
});
// -------------------------------------------------------------------
// Vendors & Stats
// -------------------------------------------------------------------
app.get('/api/vendors', async (req, res) => {
try {
const result = await pool.query(`
SELECT
vendor,
COUNT(*) as total,
COUNT(*) FILTER (WHERE status = 'draft') as draft_count,
COUNT(*) FILTER (WHERE status = 'scheduled') as scheduled_count,
COUNT(*) FILTER (WHERE status = 'published') as published_count,
COUNT(*) FILTER (WHERE status = 'failed') as failed_count
FROM blog_posts
WHERE vendor IS NOT NULL AND vendor != ''
GROUP BY vendor
ORDER BY total DESC
`);
res.json({ success: true, vendors: result.rows });
} catch (err) {
console.error('Error fetching vendors:', err);
res.status(500).json({ error: err.message });
}
});
app.get('/api/stats', async (req, res) => {
try {
const stats = await pool.query(`
SELECT
COUNT(*) as total,
COUNT(*) FILTER (WHERE status = 'draft') as draft_count,
COUNT(*) FILTER (WHERE status = 'scheduled') as scheduled_count,
COUNT(*) FILTER (WHERE status = 'published') as published_count,
COUNT(*) FILTER (WHERE status = 'failed') as failed_count,
COUNT(DISTINCT vendor) FILTER (WHERE vendor IS NOT NULL AND vendor != '') as vendor_count,
MAX(published_at) as last_published_at
FROM blog_posts
`);
res.json({ success: true, stats: stats.rows[0] });
} catch (err) {
console.error('Error fetching stats:', err);
res.status(500).json({ error: err.message });
}
});
// -------------------------------------------------------------------
// Instagram Import
// -------------------------------------------------------------------
app.get('/api/instagram/recent', async (req, res) => {
try {
const result = await pool.query(`
SELECT * FROM instagram_posts
WHERE status = 'posted'
ORDER BY posted_at DESC
LIMIT 20
`);
res.json({ success: true, posts: result.rows });
} catch (err) {
console.error('Error fetching instagram posts:', err);
res.status(500).json({ error: err.message });
}
});
app.post('/api/instagram/import/:igPostId', async (req, res) => {
try {
const { igPostId } = req.params;
// Get Instagram post
const igResult = await pool.query('SELECT * FROM instagram_posts WHERE id = $1', [igPostId]);
if (igResult.rows.length === 0) {
return res.status(404).json({ error: 'Instagram post not found' });
}
const igPost = igResult.rows[0];
// Generate blog content from Instagram post data
const blogContent = generateBlogContent({
patternName: igPost.product_title || 'Wallcovering',
colorName: '',
vendor: igPost.vendor || 'Designer Wallcoverings',
description: igPost.caption || '',
dwSku: igPost.dw_sku || '',
imageUrl: igPost.image_url || '',
productHandle: ''
});
// Insert as blog draft
const insertResult = await pool.query(`
INSERT INTO blog_posts (
title, body_html, seo_title, seo_description, tags,
handle, image_url, vendor, dw_sku, product_id,
instagram_post_id, status, author
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, 'draft', 'Designer Wallcoverings')
RETURNING *
`, [
blogContent.title,
blogContent.body_html,
blogContent.seo_title,
blogContent.seo_description,
blogContent.tags,
blogContent.title.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '').substring(0, 200),
igPost.image_url || null,
igPost.vendor || null,
igPost.dw_sku || null,
igPost.product_id || null,
parseInt(igPostId)
]);
res.json({ success: true, post: insertResult.rows[0] });
} catch (err) {
console.error('Error importing Instagram post:', err);
res.status(500).json({ error: err.message });
}
});
// -------------------------------------------------------------------
// Content Generation
// -------------------------------------------------------------------
app.post('/api/generate-content', (req, res) => {
try {
const { patternName, colorName, vendor, description, dwSku, imageUrl, productHandle } = req.body;
const content = generateBlogContent({
patternName: patternName || 'Wallcovering',
colorName: colorName || '',
vendor: vendor || 'Designer Wallcoverings',
description: description || '',
dwSku: dwSku || '',
imageUrl: imageUrl || '',
productHandle: productHandle || ''
});
res.json({
success: true,
title: content.title,
body_html: content.body_html,
seo_title: content.seo_title,
seo_description: content.seo_description,
tags: content.tags
});
} catch (err) {
console.error('Error generating content:', err);
res.status(500).json({ error: err.message });
}
});
// -------------------------------------------------------------------
// Scheduled Post Processor
// -------------------------------------------------------------------
async function processScheduledPosts() {
try {
const result = await pool.query(`
SELECT * FROM blog_posts
WHERE status = 'scheduled' AND scheduled_at <= NOW()
ORDER BY scheduled_at ASC
LIMIT 1
`);
if (result.rows.length === 0) return;
const post = result.rows[0];
console.log(`Processing scheduled blog post ${post.id}: ${post.title}`);
// Get or create blog
const blogId = await getOrCreateBlog();
if (!blogId) {
await pool.query(
`UPDATE blog_posts SET status = 'failed', error_message = 'Failed to get or create Shopify blog', updated_at = NOW() WHERE id = $1`,
[post.id]
);
return;
}
// Create article in Shopify
const articleResult = await createArticle(blogId, {
title: post.title,
body_html: post.body_html,
author: post.author || 'Designer Wallcoverings',
tags: post.tags || '',
image_url: post.image_url
});
if (articleResult.status === 201 && articleResult.data && articleResult.data.article) {
const article = articleResult.data.article;
await pool.query(`
UPDATE blog_posts SET
status = 'published',
published_at = NOW(),
shopify_blog_id = $1,
shopify_article_id = $2,
error_message = NULL,
updated_at = NOW()
WHERE id = $3
`, [blogId, article.id, post.id]);
sendSlackNotification(
`*Scheduled Blog Post Published*\n` +
`Title: ${post.title}\n` +
`Vendor: ${post.vendor || 'N/A'}\n` +
`SKU: ${post.dw_sku || 'N/A'}\n` +
`Originally scheduled for: ${new Date(post.scheduled_at).toLocaleString()}\n` +
`Status: Published`
);
console.log(`Blog post ${post.id} published to Shopify as article ${article.id}`);
} else {
const errorMsg = articleResult.data && articleResult.data.errors
? JSON.stringify(articleResult.data.errors)
: `Shopify returned status ${articleResult.status}`;
await pool.query(
`UPDATE blog_posts SET status = 'failed', error_message = $1, updated_at = NOW() WHERE id = $2`,
[errorMsg, post.id]
);
sendSlackNotification(
`*Scheduled Blog Post FAILED*\n` +
`Title: ${post.title}\n` +
`Error: ${errorMsg}`
);
console.error(`Blog post ${post.id} publish failed: ${errorMsg}`);
}
} catch (err) {
console.error('Scheduled post processor error:', err.message);
}
}
// Run scheduler every 60 seconds
setInterval(processScheduledPosts, 60000);
// -------------------------------------------------------------------
// Serve main page
// -------------------------------------------------------------------
app.get('/', (req, res) => {
const indexPath = path.join(__dirname, 'public', 'index.html');
try {
require('fs').accessSync(indexPath);
res.sendFile(indexPath);
} catch {
// If no index.html, serve a basic dashboard
res.send(getDashboardPage());
}
});
// -------------------------------------------------------------------
// Login Page HTML
// -------------------------------------------------------------------
function getLoginPage() {
return `<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DW Blog Agent - Login</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body {
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
background: linear-gradient(135deg, #0f766e, #0284c7, #7c3aed);
min-height: 100vh;
display: flex;
align-items: center;
justify-content: center;
}
.login-card {
background: white;
border-radius: 16px;
padding: 40px;
width: 380px;
box-shadow: 0 20px 60px rgba(0,0,0,0.3);
}
.login-card h1 {
text-align: center;
font-size: 24px;
margin-bottom: 8px;
background: linear-gradient(135deg, #0f766e, #0284c7, #7c3aed);
-webkit-background-clip: text;
-webkit-text-fill-color: transparent;
}
.login-card p { text-align: center; color: #666; margin-bottom: 24px; font-size: 14px; }
.form-group { margin-bottom: 16px; }
.form-group label { display: block; font-size: 13px; font-weight: 600; color: #333; margin-bottom: 6px; }
.form-group input {
width: 100%; padding: 12px; border: 2px solid #e0e0e0; border-radius: 8px; font-size: 16px;
transition: border-color 0.2s;
}
.form-group input:focus { outline: none; border-color: #0284c7; }
.btn {
width: 100%; padding: 14px; border: none; border-radius: 8px; font-size: 16px; font-weight: 600;
color: white; cursor: pointer;
background: linear-gradient(135deg, #0f766e, #0284c7, #7c3aed);
transition: opacity 0.2s;
}
.btn:hover { opacity: 0.9; }
.error { color: #dc2626; text-align: center; font-size: 14px; margin-top: 12px; display: none; }
</style>
</head>
<body>
<div class="login-card">
<h1>DW Blog Agent</h1>
<p>Sign in to manage blog posts</p>
<form id="loginForm">
<div class="form-group">
<label>Username</label>
<input type="text" id="username" name="username" required autocomplete="username">
</div>
<div class="form-group">
<label>Password</label>
<input type="password" id="password" name="password" required autocomplete="current-password">
</div>
<button type="submit" class="btn">Sign In</button>
<div class="error" id="errorMsg">Invalid credentials</div>
</form>
</div>
<script>
document.getElementById('loginForm').addEventListener('submit', async (e) => {
e.preventDefault();
const username = document.getElementById('username').value;
const password = document.getElementById('password').value;
try {
const resp = await fetch('/api/login', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ username, password })
});
const data = await resp.json();
if (data.success) {
window.location.href = '/';
} else {
document.getElementById('errorMsg').style.display = 'block';
}
} catch (err) {
document.getElementById('errorMsg').style.display = 'block';
}
});
</script>
</body>
</html>`;
}
// -------------------------------------------------------------------
// Dashboard Page (fallback if no public/index.html)
// -------------------------------------------------------------------
function getDashboardPage() {
return `<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DW Blog Agent - Dashboard</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background: #f3f4f6; color: #1f2937; }
.header {
background: linear-gradient(135deg, #0f766e, #0284c7, #7c3aed);
color: white; padding: 24px 32px; display: flex; justify-content: space-between; align-items: center;
}
.header h1 { font-size: 22px; }
.header a { color: white; text-decoration: none; font-size: 14px; opacity: 0.8; }
.header a:hover { opacity: 1; }
.container { max-width: 1200px; margin: 0 auto; padding: 24px; }
.stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 16px; margin-bottom: 24px; }
.stat-card { background: white; border-radius: 12px; padding: 20px; box-shadow: 0 1px 3px rgba(0,0,0,0.1); }
.stat-card .label { font-size: 12px; color: #6b7280; text-transform: uppercase; letter-spacing: 0.5px; }
.stat-card .value { font-size: 28px; font-weight: 700; color: #1f2937; margin-top: 4px; }
.stat-card.draft .value { color: #6b7280; }
.stat-card.scheduled .value { color: #d97706; }
.stat-card.published .value { color: #059669; }
.stat-card.failed .value { color: #dc2626; }
.section { background: white; border-radius: 12px; padding: 24px; box-shadow: 0 1px 3px rgba(0,0,0,0.1); margin-bottom: 24px; }
.section h2 { font-size: 18px; margin-bottom: 16px; color: #1f2937; }
.post-table { width: 100%; border-collapse: collapse; }
.post-table th { text-align: left; font-size: 12px; color: #6b7280; text-transform: uppercase; letter-spacing: 0.5px; padding: 8px 12px; border-bottom: 2px solid #e5e7eb; }
.post-table td { padding: 12px; border-bottom: 1px solid #f3f4f6; font-size: 14px; }
.post-table tr:hover { background: #f9fafb; }
.badge { display: inline-block; padding: 2px 10px; border-radius: 12px; font-size: 12px; font-weight: 600; }
.badge.draft { background: #f3f4f6; color: #6b7280; }
.badge.scheduled { background: #fef3c7; color: #d97706; }
.badge.published { background: #d1fae5; color: #059669; }
.badge.failed { background: #fee2e2; color: #dc2626; }
.btn-sm { padding: 6px 14px; border: none; border-radius: 6px; font-size: 13px; font-weight: 600; cursor: pointer; }
.btn-publish { background: #059669; color: white; }
.btn-publish:hover { background: #047857; }
.btn-delete { background: #dc2626; color: white; }
.btn-delete:hover { background: #b91c1c; }
.actions { display: flex; gap: 6px; }
.empty { text-align: center; color: #9ca3af; padding: 40px; font-size: 16px; }
.loading { text-align: center; color: #6b7280; padding: 20px; }
</style>
</head>
<body>
<div class="header">
<h1>DW Blog Agent</h1>
<div>
<a href="/api/health" target="_blank">Health</a>
<span style="margin: 0 8px;">|</span>
<a href="/api/logout">Logout</a>
</div>
</div>
<div class="container">
<div class="stats-grid" id="statsGrid">
<div class="stat-card"><div class="label">Total Posts</div><div class="value" id="statTotal">-</div></div>
<div class="stat-card draft"><div class="label">Drafts</div><div class="value" id="statDraft">-</div></div>
<div class="stat-card scheduled"><div class="label">Scheduled</div><div class="value" id="statScheduled">-</div></div>
<div class="stat-card published"><div class="label">Published</div><div class="value" id="statPublished">-</div></div>
<div class="stat-card failed"><div class="label">Failed</div><div class="value" id="statFailed">-</div></div>
</div>
<div class="section">
<h2>Recent Blog Posts</h2>
<div id="postsContainer"><div class="loading">Loading posts...</div></div>
</div>
</div>
<script>
async function loadStats() {
try {
const resp = await fetch('/api/stats');
const data = await resp.json();
if (data.success) {
document.getElementById('statTotal').textContent = data.stats.total || 0;
document.getElementById('statDraft').textContent = data.stats.draft_count || 0;
document.getElementById('statScheduled').textContent = data.stats.scheduled_count || 0;
document.getElementById('statPublished').textContent = data.stats.published_count || 0;
document.getElementById('statFailed').textContent = data.stats.failed_count || 0;
}
} catch (err) { console.error('Stats error:', err); }
}
async function loadPosts() {
try {
const resp = await fetch('/api/posts?limit=25');
const data = await resp.json();
const container = document.getElementById('postsContainer');
if (!data.success || !data.posts || data.posts.length === 0) {
container.innerHTML = '<div class="empty">No blog posts yet. Use the API to create posts.</div>';
return;
}
let html = '<table class="post-table"><thead><tr><th>Title</th><th>Vendor</th><th>Status</th><th>Date</th><th>Actions</th></tr></thead><tbody>';
for (const p of data.posts) {
const date = p.published_at || p.scheduled_at || p.created_at;
const formattedDate = date ? new Date(date).toLocaleDateString() : '-';
html += '<tr>';
html += '<td style="max-width:300px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;">' + escapeHtml(p.title) + '</td>';
html += '<td>' + escapeHtml(p.vendor || '-') + '</td>';
html += '<td><span class="badge ' + p.status + '">' + p.status + '</span></td>';
html += '<td>' + formattedDate + '</td>';
html += '<td class="actions">';
if (p.status === 'draft' || p.status === 'failed') {
html += '<button class="btn-sm btn-publish" onclick="publishPost(' + p.id + ')">Publish</button>';
}
html += '<button class="btn-sm btn-delete" onclick="deletePost(' + p.id + ')">Delete</button>';
html += '</td>';
html += '</tr>';
}
html += '</tbody></table>';
container.innerHTML = html;
} catch (err) {
document.getElementById('postsContainer').innerHTML = '<div class="empty">Error loading posts.</div>';
console.error('Posts error:', err);
}
}
function escapeHtml(str) {
if (!str) return '';
return str.replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/"/g,'"');
}
async function publishPost(id) {
if (!confirm('Publish this post to Shopify?')) return;
try {
const resp = await fetch('/api/posts/' + id + '/publish', { method: 'POST' });
const data = await resp.json();
if (data.success) {
alert('Post published successfully!');
loadStats();
loadPosts();
} else {
alert('Publish failed: ' + (data.error || 'Unknown error'));
}
} catch (err) { alert('Error: ' + err.message); }
}
async function deletePost(id) {
if (!confirm('Delete this post? This cannot be undone.')) return;
try {
const resp = await fetch('/api/posts/' + id, { method: 'DELETE' });
const data = await resp.json();
if (data.success) {
loadStats();
loadPosts();
} else {
alert('Delete failed: ' + (data.error || 'Unknown error'));
}
} catch (err) { alert('Error: ' + err.message); }
}
loadStats();
loadPosts();
// Auto-refresh every 30 seconds
setInterval(() => { loadStats(); loadPosts(); }, 30000);
</script>
</body>
</html>`;
}
// -------------------------------------------------------------------
// Start Server
// -------------------------------------------------------------------
app.listen(PORT, '0.0.0.0', () => {
console.log(`DW Blog Agent running on port ${PORT}`);
console.log(`Dashboard: http://45.61.58.125:${PORT}`);
sendSlackNotification('DW Blog Agent started on port ' + PORT);
setTimeout(processScheduledPosts, 5000);
});