← back to SDCC Stories
server.js
652 lines
/**
* SDCC Story Engine — Port 7410
* Borrower stories → Gemini AI → Social media graphics → Instagram/X scheduling
* Auth: admin / DWSecure2024!
*/
const express = require('express');
const helmet = require('helmet');
const path = require('path');
const fs = require('fs');
const cron = require('node-cron');
const Database = require('better-sqlite3');
const { importData, downloadFreshData } = require('./lib/data-import');
const { generateStory, generateGraphic, generateLawmakerGraphic, COLORS } = require('./lib/gemini');
const { exchangeCodeForToken, getLongLivedToken, getIGBusinessAccount, publishToInstagram } = require('./lib/instagram-api');
const PORT = process.env.PORT || 7410;
const AUTH_USER = 'admin';
const AUTH_PASS = 'DWSecure2024!';
// ─── Database Setup ───
const db = new Database(path.join(__dirname, 'db', 'stories.db'));
db.pragma('journal_mode = WAL');
db.exec(`
CREATE TABLE IF NOT EXISTS candidates (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT,
zip_code TEXT,
age TEXT,
amount_owed_raw TEXT,
amount_low INTEGER DEFAULT 0,
amount_high INTEGER DEFAULT 0,
amount_display TEXT,
income_type TEXT,
income_range TEXT,
income_amount_low INTEGER DEFAULT 0,
income_amount_high INTEGER DEFAULT 0,
emotional_impact TEXT,
affordability_impacts_raw TEXT,
affordability_count INTEGER DEFAULT 0,
alt_spending TEXT,
story TEXT,
key_quote TEXT,
sharing_consent TEXT,
press_ready TEXT,
is_voter TEXT,
employment TEXT,
education TEXT,
race_ethnicity TEXT,
gender TEXT,
marital_status TEXT,
household_size TEXT,
loan_types_raw TEXT,
repayment_plan TEXT,
monthly_payment TEXT,
first_loan_year TEXT,
story_type TEXT,
platform_fit TEXT,
story_strength INTEGER DEFAULT 0,
story_category TEXT,
press_pitch_angle TEXT,
candidate_score INTEGER DEFAULT 0,
tier TEXT DEFAULT 'D',
district_code TEXT,
representative_name TEXT,
representative_party TEXT,
data_json TEXT,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS stories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
candidate_id INTEGER REFERENCES candidates(id),
story_one_liner TEXT,
instagram_caption TEXT,
x_post TEXT,
hashtags TEXT,
story_category TEXT,
image_path TEXT,
lawmaker_image_path TEXT,
status TEXT DEFAULT 'draft',
scheduled_at TEXT,
posted_at TEXT,
ig_media_id TEXT,
platform TEXT DEFAULT 'instagram',
lane TEXT DEFAULT 'action',
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT
);
CREATE TABLE IF NOT EXISTS schedule_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
story_id INTEGER,
platform TEXT,
status TEXT,
message TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
`);
// Default settings
const defaultSettings = {
ig_app_id: '', ig_app_secret: '', ig_access_token: '', ig_business_account_id: '',
app_url: 'http://45.61.58.125:' + PORT,
post_time_morning: '09:00', post_time_evening: '18:00',
auto_schedule: 'false'
};
for (const [key, value] of Object.entries(defaultSettings)) {
db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)').run(key, value);
}
function getSetting(key) { return db.prepare('SELECT value FROM settings WHERE key = ?').get(key)?.value || ''; }
function setSetting(key, value) { db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)').run(key, value); }
// ─── ZIP to Congressional District (lightweight lookup) ───
let districtLookup = {};
try {
const { Pool } = require('pg');
const pool = new Pool({ connectionString: (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/sdcc'), max: 2 });
pool.query('SELECT zip_codes, district_code, representative_name, representative_party FROM congressional_districts WHERE representative_name IS NOT NULL LIMIT 500')
.then(result => {
for (const row of result.rows) {
for (const zip of (row.zip_codes || [])) {
districtLookup[zip] = { district: row.district_code, name: row.representative_name, party: row.representative_party };
}
}
console.log('[StoryEngine] Loaded ' + Object.keys(districtLookup).length + ' ZIP-to-district mappings');
})
.catch(() => console.log('[StoryEngine] No congressional district data available'));
} catch (e) {
console.log('[StoryEngine] pg not available — district lookup disabled');
}
function lookupDistrict(zip) {
if (!zip) return null;
return districtLookup[zip.toString().trim().slice(0, 5)] || null;
}
// ─── Data Import ───
function loadCandidates() {
const data = importData();
const insert = db.prepare(
'INSERT OR REPLACE INTO candidates (id, first_name, last_name, email, zip_code, age,' +
'amount_owed_raw, amount_low, amount_high, amount_display,' +
'income_type, income_range, income_amount_low, income_amount_high,' +
'emotional_impact, affordability_impacts_raw, affordability_count, alt_spending,' +
'story, key_quote, sharing_consent, press_ready, is_voter,' +
'employment, education, race_ethnicity, gender, marital_status, household_size,' +
'loan_types_raw, repayment_plan, monthly_payment, first_loan_year,' +
'story_type, platform_fit, story_strength, story_category, press_pitch_angle,' +
'candidate_score, tier, district_code, representative_name, representative_party,' +
'data_json, updated_at)' +
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,' +
'?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime(\'now\'))'
);
const tx = db.transaction(() => {
for (const c of data) {
const district = lookupDistrict(c.zip_code);
insert.run(
c.id, c.first_name, c.last_name || '', c.email || '', c.zip_code, c.age,
c.amount_owed_raw, c.amount_low, c.amount_high, c.amount_display,
c.income_type, c.income_range, c.income_amount_low, c.income_amount_high,
c.emotional_impact, c.affordability_impacts_raw, c.affordability_count, c.alt_spending,
c.story, c.key_quote, c.sharing_consent, c.press_ready, c.is_voter,
c.employment, c.education, c.race_ethnicity, c.gender, c.marital_status, c.household_size,
c.loan_types_raw, c.repayment_plan, c.monthly_payment, c.first_loan_year,
c.story_type || '', c.platform_fit || '', c.story_strength || 0, c.story_category || '', c.press_pitch_angle || '',
c.candidate_score, c.tier,
district ? district.district : '', district ? district.name : '', district ? district.party : '',
JSON.stringify(c)
);
}
});
tx();
return data.length;
}
// ─── Preloaded Content Ideas ───
const CONTENT_IDEAS = [
{ title: 'Borrower Spotlight', description: 'Feature a Tier A borrower with emotional story + debt amount', lane: 'action', platform: 'both' },
{ title: 'By The Numbers', description: 'Stat graphic: total debt, avg amount, % affected by affordability', lane: 'policy', platform: 'both' },
{ title: 'What Would You Do?', description: 'Highlight what borrowers would spend loan payments on instead', lane: 'action', platform: 'instagram' },
{ title: 'Lawmaker Alert', description: 'Tag a representative with a constituent story from their district', lane: 'policy', platform: 'x' },
{ title: 'Story Thread', description: '5-post carousel of different borrower situations', lane: 'action', platform: 'instagram' },
{ title: 'Resource Drop', description: 'Share repayment plan info, forgiveness options, or servicer tips', lane: 'resources', platform: 'both' },
{ title: 'Emotional Impact', description: 'Focus on mental health + relationship impacts of student debt', lane: 'action', platform: 'both' },
{ title: 'Voter Voice', description: 'Highlight borrowers who are voters and how debt affects their civic engagement', lane: 'policy', platform: 'x' },
{ title: 'Affordability Crisis', description: 'What people CANNOT afford because of student loans', lane: 'action', platform: 'both' },
{ title: 'Share Your Story CTA', description: 'Call to action encouraging others to submit their story', lane: 'resources', platform: 'both' },
{ title: 'Generational Impact', description: 'Stories from different age groups showing long-term debt effects', lane: 'action', platform: 'instagram' },
{ title: 'Default and Collections', description: 'Highlight borrowers facing default or collections actions', lane: 'action', platform: 'both' }
];
// ─── Express App ───
const app = express();
// Security headers via helmet (added 2026-05-04 overnight YOLO loop)
app.use(helmet({ contentSecurityPolicy: false }));
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true }));
app.use('/generated', express.static(path.join(__dirname, 'generated')));
app.use(express.static(path.join(__dirname, 'public')));
function requireAuth(req, res, next) {
if (req.path === '/health' || req.path === '/auth/instagram/callback') return next();
const auth = req.headers.authorization;
if (!auth || !auth.startsWith('Basic ')) {
res.setHeader('WWW-Authenticate', 'Basic realm="SDCC Story Engine"');
return res.status(401).json({ error: 'Auth required' });
}
const decoded = Buffer.from(auth.split(' ')[1], 'base64').toString();
const [user, pass] = decoded.split(':');
if (user === AUTH_USER && pass === AUTH_PASS) return next();
res.status(401).json({ error: 'Invalid credentials' });
}
app.get('/health', (req, res) => {
const candidateCount = db.prepare('SELECT COUNT(*) as c FROM candidates').get().c;
const storyCount = db.prepare('SELECT COUNT(*) as c FROM stories').get().c;
res.json({ status: 'ok', agent: 'SDCC Story Engine', port: PORT, candidates: candidateCount, stories: storyCount });
});
app.use(requireAuth);
// ─── Candidates API ───
app.get('/api/candidates', (req, res) => {
const tier = req.query.tier;
const page = parseInt(req.query.page) || 1;
const limit = parseInt(req.query.limit) || 50;
const search = req.query.search;
const sort = req.query.sort || 'candidate_score';
const order = req.query.order || 'desc';
const offset = (page - 1) * limit;
let where = '1=1';
const params = [];
if (tier) { where += ' AND tier = ?'; params.push(tier); }
if (search) { where += ' AND (first_name LIKE ? OR zip_code LIKE ? OR emotional_impact LIKE ?)'; params.push('%' + search + '%', '%' + search + '%', '%' + search + '%'); }
const allowedSort = ['candidate_score', 'amount_high', 'affordability_count', 'first_name', 'tier', 'zip_code'];
const sortCol = allowedSort.includes(sort) ? sort : 'candidate_score';
const sortOrder = order === 'asc' ? 'ASC' : 'DESC';
const total = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE ' + where).get(...params).c;
const rows = db.prepare('SELECT id, first_name, last_name, zip_code, age, amount_owed_raw, amount_display,' +
'emotional_impact, affordability_impacts_raw, affordability_count, alt_spending,' +
'candidate_score, tier, sharing_consent, press_ready, is_voter,' +
'district_code, representative_name, representative_party,' +
'income_type, income_range, story, key_quote' +
' FROM candidates WHERE ' + where + ' ORDER BY ' + sortCol + ' ' + sortOrder + ' LIMIT ? OFFSET ?')
.all(...params, limit, offset);
res.json({ total, page, limit, candidates: rows });
});
app.get('/api/candidates/:id', (req, res) => {
const row = db.prepare('SELECT * FROM candidates WHERE id = ?').get(req.params.id);
if (!row) return res.status(404).json({ error: 'Not found' });
let fullData = {};
try { fullData = JSON.parse(row.data_json || '{}'); } catch (e) { /* ignore */ }
const stories = db.prepare('SELECT * FROM stories WHERE candidate_id = ? ORDER BY created_at DESC').all(row.id);
res.json({ candidate: row, fullData, stories });
});
app.get('/api/heatmap', (req, res) => {
const byTier = db.prepare('SELECT tier, COUNT(*) as count FROM candidates GROUP BY tier').all();
const byDistrict = db.prepare('SELECT district_code, representative_name, representative_party, tier, COUNT(*) as count FROM candidates WHERE district_code != \'\' GROUP BY district_code, tier ORDER BY count DESC').all();
const byState = db.prepare('SELECT SUBSTR(district_code, 1, 2) as state, COUNT(*) as count, AVG(candidate_score) as avg_score FROM candidates WHERE district_code != \'\' GROUP BY state ORDER BY count DESC').all();
res.json({ byTier, byDistrict, byState });
});
app.get('/api/stats', (req, res) => {
const total = db.prepare('SELECT COUNT(*) as c FROM candidates').get().c;
const tierA = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE tier = \'A\'').get().c;
const tierB = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE tier = \'B\'').get().c;
const tierC = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE tier = \'C\'').get().c;
const withStory = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE story != \'\'').get().c;
const withConsent = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE sharing_consent LIKE \'%yes%\' OR sharing_consent LIKE \'%Yes%\'').get().c;
const pressReady = db.prepare('SELECT COUNT(*) as c FROM candidates WHERE press_ready LIKE \'%yes%\' OR press_ready LIKE \'%Yes%\'').get().c;
const totalStories = db.prepare('SELECT COUNT(*) as c FROM stories').get().c;
const posted = db.prepare('SELECT COUNT(*) as c FROM stories WHERE status = \'posted\'').get().c;
const scheduled = db.prepare('SELECT COUNT(*) as c FROM stories WHERE status = \'scheduled\'').get().c;
res.json({ total, tierA, tierB, tierC, withStory, withConsent, pressReady, totalStories, posted, scheduled });
});
// ─── Story Generation ───
app.post('/api/stories/generate', async (req, res) => {
const candidate = db.prepare('SELECT * FROM candidates WHERE id = ?').get(req.body.candidate_id);
if (!candidate) return res.status(404).json({ error: 'Candidate not found' });
let fullData = {};
try { fullData = JSON.parse(candidate.data_json || '{}'); } catch (e) { /* ignore */ }
try {
const story = await generateStory({ ...candidate, ...fullData });
const id = db.prepare('INSERT INTO stories (candidate_id, story_one_liner, instagram_caption, x_post, hashtags, story_category, lane) VALUES (?, ?, ?, ?, ?, ?, ?)').run(
candidate.id, story.story_one_liner, story.instagram_caption, story.x_post,
JSON.stringify(story.hashtags || []), story.story_category || 'hardship', 'action'
).lastInsertRowid;
res.json({ id, ...story });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.post('/api/stories/:id/generate-graphic', async (req, res) => {
const story = db.prepare('SELECT * FROM stories WHERE id = ?').get(req.params.id);
if (!story) return res.status(404).json({ error: 'Story not found' });
const candidate = db.prepare('SELECT * FROM candidates WHERE id = ?').get(story.candidate_id);
let fullData = {};
try { fullData = JSON.parse(candidate.data_json || '{}'); } catch (e) { /* ignore */ }
try {
const result = await generateGraphic({ ...candidate, ...fullData }, story.story_one_liner, { lane: story.lane });
if (result.imageBuffer) {
const imgPath = path.join(__dirname, 'generated', 'story-' + story.id + '.png');
fs.writeFileSync(imgPath, result.imageBuffer);
db.prepare('UPDATE stories SET image_path = ? WHERE id = ?').run('/generated/story-' + story.id + '.png', story.id);
res.json({ success: true, image_path: '/generated/story-' + story.id + '.png' });
} else {
res.status(500).json({ error: 'Image generation failed' });
}
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.post('/api/stories/:id/generate-lawmaker-graphic', async (req, res) => {
const story = db.prepare('SELECT * FROM stories WHERE id = ?').get(req.params.id);
if (!story) return res.status(404).json({ error: 'Story not found' });
const candidate = db.prepare('SELECT * FROM candidates WHERE id = ?').get(story.candidate_id);
if (!candidate.representative_name) return res.status(400).json({ error: 'No lawmaker mapped' });
const lawmaker = { name: candidate.representative_name, party: candidate.representative_party, district: candidate.district_code, state: (candidate.district_code || '').split('-')[0] };
try {
const result = await generateLawmakerGraphic(candidate, lawmaker, story.story_one_liner);
if (result.imageBuffer) {
const imgPath = path.join(__dirname, 'generated', 'lawmaker-' + story.id + '.png');
fs.writeFileSync(imgPath, result.imageBuffer);
db.prepare('UPDATE stories SET lawmaker_image_path = ? WHERE id = ?').run('/generated/lawmaker-' + story.id + '.png', story.id);
res.json({ success: true, image_path: '/generated/lawmaker-' + story.id + '.png' });
} else {
res.status(500).json({ error: 'Image generation failed' });
}
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// ─── Global Search (candidates + stories + settings) ───
// GET /api/search?q=<query>&limit=25
// Returns unified results grouped by type. Replaces the narrow ?search= on
// /api/candidates (still kept for back-compat) with cross-table coverage.
app.get('/api/search', (req, res) => {
const q = (req.query.q || '').trim();
const limit = Math.min(parseInt(req.query.limit, 10) || 25, 50);
if (q.length < 2) return res.json({ results: [], query: q, counts: {}, total: 0 });
const pat = '%' + q + '%';
// Candidates — wide LIKE across name, location, story, demographics, district.
const candidates = db.prepare(`
SELECT id, first_name, last_name, zip_code, tier, candidate_score,
amount_display, district_code, representative_name,
emotional_impact, story_category
FROM candidates
WHERE first_name LIKE ? OR last_name LIKE ? OR email LIKE ?
OR zip_code LIKE ? OR (first_name || ' ' || last_name) LIKE ?
OR story LIKE ? OR key_quote LIKE ?
OR emotional_impact LIKE ? OR affordability_impacts_raw LIKE ?
OR alt_spending LIKE ? OR employment LIKE ? OR education LIKE ?
OR story_category LIKE ? OR press_pitch_angle LIKE ?
OR representative_name LIKE ? OR district_code LIKE ? OR tier LIKE ?
OR story_type LIKE ?
ORDER BY CASE
WHEN (first_name || ' ' || last_name) LIKE ? THEN 0
WHEN candidate_score IS NOT NULL THEN 1
ELSE 2
END,
candidate_score DESC NULLS LAST
LIMIT ?
`).all(pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, pat, limit);
// Stories — join candidate for context, search caption + post + one-liner + hashtags.
const stories = db.prepare(`
SELECT s.id, s.candidate_id, s.story_one_liner, s.instagram_caption, s.x_post,
s.story_category, s.status, s.scheduled_at, s.posted_at, s.lane,
c.first_name, c.last_name, c.tier, c.zip_code
FROM stories s
LEFT JOIN candidates c ON s.candidate_id = c.id
WHERE s.story_one_liner LIKE ? OR s.instagram_caption LIKE ?
OR s.x_post LIKE ? OR s.hashtags LIKE ?
OR s.story_category LIKE ? OR s.lane LIKE ? OR s.status LIKE ?
ORDER BY s.created_at DESC
LIMIT ?
`).all(pat, pat, pat, pat, pat, pat, pat, limit);
// Settings — useful for IG/app config lookups by key (e.g. "ig_app_id").
const settings = db.prepare(`
SELECT key, value
FROM settings
WHERE key LIKE ? OR value LIKE ?
ORDER BY key
LIMIT ?
`).all(pat, pat, limit);
const results = [];
for (const c of candidates) {
const name = ((c.first_name || '') + ' ' + (c.last_name || '')).trim() || '(no name)';
const sub = [c.tier ? 'Tier ' + c.tier : null, c.zip_code, c.district_code].filter(Boolean).join(' · ');
const meta = [c.amount_display, c.emotional_impact && c.emotional_impact.slice(0, 60)].filter(Boolean).join(' · ');
results.push({ id: c.id, type: 'candidate', title: name, subtitle: sub, meta, score: c.candidate_score });
}
for (const s of stories) {
const candidateName = ((s.first_name || '') + ' ' + (s.last_name || '')).trim() || '(unlinked)';
const sub = [s.lane, s.status, s.story_category].filter(Boolean).join(' · ');
const meta = (s.story_one_liner || s.instagram_caption || s.x_post || '').slice(0, 80);
results.push({ id: s.id, type: 'story', title: candidateName + ' — story #' + s.id, subtitle: sub, meta });
}
for (const st of settings) {
const safeValue = /token|secret|password|key$/i.test(st.key)
? (st.value ? '(set — ' + st.value.length + ' chars)' : '(empty)')
: (st.value || '').slice(0, 80);
results.push({ id: st.key, type: 'setting', title: st.key, subtitle: 'Setting', meta: safeValue });
}
res.json({
results,
query: q,
counts: {
candidates: candidates.length,
stories: stories.length,
settings: settings.length,
},
total: results.length,
});
});
// ─── Stories CRUD ───
app.get('/api/stories', (req, res) => {
const status = req.query.status;
const platform = req.query.platform;
const limit = parseInt(req.query.limit) || 50;
let where = '1=1';
const params = [];
if (status) { where += ' AND s.status = ?'; params.push(status); }
if (platform) { where += ' AND s.platform = ?'; params.push(platform); }
const rows = db.prepare('SELECT s.*, c.first_name, c.last_name, c.amount_owed_raw, c.zip_code, c.tier, c.district_code, c.representative_name FROM stories s LEFT JOIN candidates c ON s.candidate_id = c.id WHERE ' + where + ' ORDER BY s.created_at DESC LIMIT ?').all(...params, limit);
res.json(rows);
});
app.get('/api/stories/:id', (req, res) => {
const story = db.prepare('SELECT s.*, c.first_name, c.last_name, c.amount_owed_raw, c.zip_code, c.tier, c.district_code, c.representative_name, c.emotional_impact, c.alt_spending, c.affordability_impacts_raw FROM stories s LEFT JOIN candidates c ON s.candidate_id = c.id WHERE s.id = ?').get(req.params.id);
if (!story) return res.status(404).json({ error: 'Not found' });
res.json(story);
});
app.put('/api/stories/:id', (req, res) => {
const fields = ['instagram_caption', 'x_post', 'status', 'scheduled_at', 'platform', 'lane'];
const updates = [];
const params = [];
for (const f of fields) {
if (req.body[f] !== undefined) { updates.push(f + ' = ?'); params.push(req.body[f]); }
}
if (updates.length === 0) return res.status(400).json({ error: 'No fields' });
params.push(req.params.id);
db.prepare('UPDATE stories SET ' + updates.join(', ') + ' WHERE id = ?').run(...params);
res.json({ success: true });
});
app.delete('/api/stories/:id', (req, res) => {
db.prepare('DELETE FROM stories WHERE id = ?').run(req.params.id);
res.json({ success: true });
});
// ─── Calendar ───
app.get('/api/calendar', (req, res) => {
const stories = db.prepare('SELECT s.id, s.story_one_liner, s.status, s.scheduled_at, s.platform, s.lane, s.image_path, c.first_name, c.amount_owed_raw, c.tier FROM stories s LEFT JOIN candidates c ON s.candidate_id = c.id WHERE s.scheduled_at IS NOT NULL ORDER BY s.scheduled_at').all();
res.json(stories);
});
app.post('/api/calendar/auto-fill', async (req, res) => {
const days = parseInt(req.body.days) || 7;
const start = req.body.startDate ? new Date(req.body.startDate) : new Date();
const slots = [];
for (let d = 0; d < days; d++) {
const day = new Date(start);
day.setDate(day.getDate() + d);
const dateStr = day.toISOString().split('T')[0];
slots.push({ datetime: dateStr + 'T09:00:00-08:00', platform: 'instagram' });
slots.push({ datetime: dateStr + 'T18:00:00-08:00', platform: 'x' });
}
const usedIds = db.prepare('SELECT DISTINCT candidate_id FROM stories').all().map(r => r.candidate_id);
const placeholders = usedIds.length > 0 ? usedIds.map(() => '?').join(',') : '0';
const candidates = db.prepare('SELECT * FROM candidates WHERE tier IN (\'A\', \'B\') AND id NOT IN (' + placeholders + ') ORDER BY candidate_score DESC LIMIT ?').all(...usedIds, slots.length);
const results = [];
for (let i = 0; i < Math.min(slots.length, candidates.length); i++) {
const candidate = candidates[i];
let fullData = {};
try { fullData = JSON.parse(candidate.data_json || '{}'); } catch (e) { /* ignore */ }
try {
const story = await generateStory({ ...candidate, ...fullData });
const id = db.prepare('INSERT INTO stories (candidate_id, story_one_liner, instagram_caption, x_post, hashtags, story_category, lane, platform, status, scheduled_at) VALUES (?, ?, ?, ?, ?, ?, \'action\', ?, \'scheduled\', ?)').run(
candidate.id, story.story_one_liner, story.instagram_caption, story.x_post,
JSON.stringify(story.hashtags || []), story.story_category || 'hardship',
slots[i].platform, slots[i].datetime
).lastInsertRowid;
results.push({ id, candidate: candidate.first_name, scheduled: slots[i].datetime, platform: slots[i].platform });
} catch (e) {
results.push({ error: e.message, candidate: candidate.first_name });
}
await new Promise(r => setTimeout(r, 1500));
}
res.json({ scheduled: results.length, slots: results });
});
// ─── Content Ideas ───
app.get('/api/ideas', (req, res) => { res.json(CONTENT_IDEAS); });
// ─── Data Import ───
app.post('/api/import', (req, res) => {
try { res.json({ success: true, imported: loadCandidates() }); }
catch (e) { res.status(500).json({ error: e.message }); }
});
app.post('/api/import/refresh', async (req, res) => {
try {
await downloadFreshData();
res.json({ success: true, downloaded: true, imported: loadCandidates() });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// ─── Instagram OAuth ───
app.get('/auth/instagram', (req, res) => {
const appId = getSetting('ig_app_id');
const redirectUri = encodeURIComponent(getSetting('app_url') + '/auth/instagram/callback');
res.redirect('https://www.facebook.com/v18.0/dialog/oauth?client_id=' + appId + '&redirect_uri=' + redirectUri + '&scope=instagram_basic,instagram_content_publish,pages_show_list,pages_read_engagement&response_type=code');
});
app.get('/auth/instagram/callback', async (req, res) => {
if (!req.query.code) return res.status(400).send('No code');
try {
const appId = getSetting('ig_app_id');
const appSecret = getSetting('ig_app_secret');
const redirectUri = getSetting('app_url') + '/auth/instagram/callback';
const tokenResult = await exchangeCodeForToken(appId, appSecret, redirectUri, req.query.code);
if (!tokenResult.access_token) return res.status(500).send('Token exchange failed');
const longLived = await getLongLivedToken(appId, appSecret, tokenResult.access_token);
setSetting('ig_access_token', longLived.access_token || tokenResult.access_token);
const igAccountId = await getIGBusinessAccount(longLived.access_token || tokenResult.access_token);
if (igAccountId) setSetting('ig_business_account_id', igAccountId);
res.redirect('/?connected=true');
} catch (e) { res.status(500).send('OAuth error: ' + e.message); }
});
app.get('/api/instagram/status', (req, res) => {
const token = getSetting('ig_access_token');
const accountId = getSetting('ig_business_account_id');
res.json({ connected: !!(token && accountId), hasToken: !!token, hasAccountId: !!accountId });
});
app.post('/api/stories/:id/publish', async (req, res) => {
const story = db.prepare('SELECT * FROM stories WHERE id = ?').get(req.params.id);
if (!story) return res.status(404).json({ error: 'Not found' });
if (!story.image_path) return res.status(400).json({ error: 'Generate graphic first' });
const token = getSetting('ig_access_token');
const accountId = getSetting('ig_business_account_id');
if (!token || !accountId) return res.status(400).json({ error: 'Instagram not connected' });
try {
const imageUrl = getSetting('app_url') + story.image_path;
const hashtags = JSON.parse(story.hashtags || '[]');
const caption = story.instagram_caption + '\n\n' + hashtags.map(function(h) { return '#' + h; }).join(' ');
const result = await publishToInstagram(accountId, token, imageUrl, caption);
db.prepare('UPDATE stories SET status = \'posted\', posted_at = datetime(\'now\'), ig_media_id = ? WHERE id = ?').run(result.id || '', story.id);
res.json({ success: true, mediaId: result.id });
} catch (e) {
db.prepare('UPDATE stories SET status = \'failed\' WHERE id = ?').run(story.id);
res.status(500).json({ error: e.message });
}
});
// ─── Settings ───
app.get('/api/settings', (req, res) => {
const all = db.prepare('SELECT * FROM settings').all();
const settings = {};
for (const row of all) {
settings[row.key] = (row.key.includes('secret') || row.key.includes('token')) ? '***' + row.value.slice(-6) : row.value;
}
res.json(settings);
});
app.put('/api/settings', (req, res) => {
for (const [key, value] of Object.entries(req.body)) {
if (value !== undefined && !String(value).startsWith('***')) setSetting(key, value);
}
res.json({ success: true });
});
// ─── Scheduler: 2x daily at 9am + 6pm PT ───
cron.schedule('0 9,18 * * *', async () => {
if (getSetting('auto_schedule') !== 'true') return;
const now = new Date().toISOString();
const due = db.prepare('SELECT * FROM stories WHERE status = \'scheduled\' AND scheduled_at <= ? LIMIT 1').get(now);
if (!due) return;
const token = getSetting('ig_access_token');
const accountId = getSetting('ig_business_account_id');
if (!token || !accountId) return;
try {
if (due.platform === 'instagram' && due.image_path) {
const imageUrl = getSetting('app_url') + due.image_path;
const hashtags = JSON.parse(due.hashtags || '[]');
const caption = due.instagram_caption + '\n\n' + hashtags.map(function(h) { return '#' + h; }).join(' ');
const result = await publishToInstagram(accountId, token, imageUrl, caption);
db.prepare('UPDATE stories SET status = \'posted\', posted_at = datetime(\'now\'), ig_media_id = ? WHERE id = ?').run(result.id || '', due.id);
console.log('[Scheduler] Posted story #' + due.id + ' to Instagram');
} else {
db.prepare('UPDATE stories SET status = \'ready_to_post\' WHERE id = ?').run(due.id);
}
} catch (e) {
db.prepare('UPDATE stories SET status = \'failed\' WHERE id = ?').run(due.id);
console.error('[Scheduler] Failed:', e.message);
}
}, { timezone: 'America/Los_Angeles' });
// ─── Serve Frontend ───
app.get('/', (req, res) => { res.sendFile(path.join(__dirname, 'public', 'index.html')); });
// ─── Start ───
app.listen(PORT, '0.0.0.0', () => {
console.log('[StoryEngine] SDCC Story Engine running on port ' + PORT);
try {
const count = loadCandidates();
console.log('[StoryEngine] Loaded ' + count + ' candidates from CSV');
} catch (e) {
console.error('[StoryEngine] Initial import failed:', e.message);
}
});