`media.js`

187 lines
'use strict';
/*
 * Media library — surfaces every image source we have local access to, so the
 * composer's "attach" picker can pull from real assets instead of file upload.
 *
 * Sources (all from the local dw_unified mirror, read-only):
 *   shopify   — shopify_products.image_url   (~139k product images in our acct)
 *   instagram — instagram_posts              (our own DW Instagram posts)
 *   vendor    — social_media_posts (IG) + social_trending_posts + vendor_images
 *               (vendor posts/imagery captured for us)
 *
 * Each row is normalised to: { url, title, source, vendor, caption, link }.
 */
const fs = require('fs');
const path = require('path');
const { q } = require('./db');
const vendorIg = require('./vendor-ig');

const SOURCES = ['shopify', 'instagram', 'vendor'];

// content-hash map { image_url: md5 } from hash-images.js — lets the client
// dedup byte-identical photos (colorway reuploads) precisely. Hot-reloaded on
// mtime change so a fresh hashing run is picked up without a restart.
const HASH_FILE = path.join(__dirname, 'data', 'img-hashes.json');
let _hashes = {}, _hashMtime = 0;
function imgHashes() {
  try {
    const m = fs.statSync(HASH_FILE).mtimeMs;
    if (m !== _hashMtime) { _hashes = JSON.parse(fs.readFileSync(HASH_FILE, 'utf8')); _hashMtime = m; }
  } catch { _hashes = {}; }
  return _hashes;
}

// --- count per source (cheap, drives the source tabs + honest totals) -------
async function counts() {
  const out = { shopify: 0, instagram: 0, vendor: 0 };
  const safe = async (sql) => {
    try { const r = await q(sql); return Number(r.rows[0].c) || 0; }
    catch { return 0; }
  };
  out.shopify = await safe(
    `select count(*) c from shopify_products where image_url ilike 'http%'`);
  out.instagram = await safe(
    `select count(*) c from instagram_posts where image_url ilike 'http%'`);
  // vendor IG = the self-contained vendor-ig store (real cached posts) +
  // whatever IG-platform rows exist in the DB social tables.
  const vIg = await safe(
    `select count(*) c from social_media_posts where image_url ilike 'http%' and platform='instagram'`);
  const vTrend = await safe(
    `select count(*) c from social_trending_posts where post_image_url ilike 'http%'`);
  let vStore = 0; try { vStore = vendorIg.count(); } catch {}
  out.vendor = vStore + vIg + vTrend;
  return out;
}

// --- one source -> normalised rows ------------------------------------------
// q-search is applied per source on its meaningful text columns.
async function fetchShopify({ search, limit, offset }) {
  const params = [];
  let where = `image_url ilike 'http%'`;
  if (search) {
    params.push(`%${search}%`);
    where += ` and (title ilike $${params.length} or vendor ilike $${params.length} or handle ilike $${params.length})`;
  }
  params.push(limit, offset);
  const r = await q(
    `select id, image_url, title, vendor
       from shopify_products
      where ${where}
      order by id desc
      limit $${params.length - 1} offset $${params.length}`, params);
  const H = imgHashes();
  return r.rows.map(row => ({
    url: row.image_url,
    title: row.title || '(untitled)',
    source: 'shopify',
    vendor: row.vendor || '',
    caption: '',
    link: '',
    imgHash: H[row.image_url] || null
  }));
}

async function fetchInstagram({ search, limit, offset }) {
  const params = [];
  let where = `image_url ilike 'http%'`;
  if (search) {
    params.push(`%${search}%`);
    where += ` and (coalesce(caption,'') ilike $${params.length} or coalesce(product_title,'') ilike $${params.length} or coalesce(vendor,'') ilike $${params.length})`;
  }
  params.push(limit, offset);
  const r = await q(
    `select image_url, caption, product_title, vendor, instagram_post_url, posted_at, created_at
       from instagram_posts
      where ${where}
      order by coalesce(posted_at, created_at) desc nulls last
      limit $${params.length - 1} offset $${params.length}`, params);
  return r.rows.map(row => ({
    url: row.image_url,
    title: row.product_title || (row.caption ? row.caption.slice(0, 60) : 'Instagram post'),
    source: 'instagram',
    vendor: row.vendor || '',
    caption: row.caption || '',
    link: row.instagram_post_url || ''
  }));
}

// vendor IG = the local vendor-ig store (real cached posts, images that paint),
// then any IG-platform rows from the DB social tables. We deliberately DROP the
// old `vendor_images` website scrapes — they WAF-block on fetch and aren't IG.
async function fetchVendor({ search, limit, offset }) {
  let store = [];
  try { store = vendorIg.list({ search, limit, offset }); } catch {}
  if (store.length >= limit) return store;

  // top up from DB social IG rows (usually empty today, but real if present)
  const need = limit - store.length;
  const params = [];
  let s1 = '', s2 = '';
  if (search) {
    params.push(`%${search}%`);
    const p = `$${params.length}`;
    s1 = ` and (coalesce(caption,'') ilike ${p} or coalesce(vendor,'') ilike ${p} or coalesce(account_username,'') ilike ${p})`;
    s2 = ` and (coalesce(post_snippet,'') ilike ${p} or coalesce(author_handle,'') ilike ${p})`;
  }
  const dbOffset = Math.max(offset - vendorIg.count(), 0);
  params.push(need, dbOffset);
  const lim = `$${params.length - 1}`, off = `$${params.length}`;
  let rows = [];
  try {
    const r = await q(`
      select * from (
        select image_url as url,
               coalesce(product_title, account_username, 'Vendor Instagram') as title,
               coalesce(vendor, account_username, '') as vendor,
               coalesce(caption,'') as caption, coalesce(post_url,'') as link,
               coalesce(posted_at, created_at) as ts
          from social_media_posts
         where image_url ilike 'http%' and platform='instagram'${s1}
        union all
        select post_image_url as url,
               coalesce(author_display_name, author_handle, 'Trending') as title,
               coalesce(author_handle,'') as vendor,
               coalesce(post_snippet,'') as caption, coalesce(post_url,'') as link,
               last_seen_at as ts
          from social_trending_posts
         where post_image_url ilike 'http%'${s2}
      ) u order by ts desc nulls last limit ${lim} offset ${off}`, params);
    rows = r.rows.map(row => ({
      url: row.url, title: row.title || 'Vendor', source: 'vendor',
      vendor: row.vendor || '', caption: row.caption || '', link: row.link || ''
    }));
  } catch {}
  return [...store, ...rows];
}

// --- public: paged media across one or all sources -------------------------
async function media({ source = 'all', search = '', limit = 60, offset = 0 }) {
  limit = Math.min(Math.max(parseInt(limit, 10) || 60, 1), 200);
  offset = Math.max(parseInt(offset, 10) || 0, 0);
  search = (search || '').trim();

  if (source !== 'all' && SOURCES.includes(source)) {
    const fn = { shopify: fetchShopify, instagram: fetchInstagram, vendor: fetchVendor }[source];
    const items = await fn({ search, limit, offset });
    return { items, source, search, limit, offset, hasMore: items.length === limit };
  }

  // "all": interleave — small sources first (so 3 IG + 20 vendor aren't buried
  // under 139k shopify), then fill the rest from shopify with the same offset.
  if (offset === 0) {
    const [ig, vendor] = await Promise.all([
      fetchInstagram({ search, limit: 200, offset: 0 }),
      fetchVendor({ search, limit: 200, offset: 0 })
    ]);
    const head = [...ig, ...vendor];
    const shopify = await fetchShopify({ search, limit: Math.max(limit - head.length, 0), offset: 0 });
    const items = [...head, ...shopify];
    return { items, source: 'all', search, limit, offset, hasMore: true };
  }
  // subsequent pages of "all" come from shopify (the deep source)
  const items = await fetchShopify({ search, limit, offset });
  return { items, source: 'all', search, limit, offset, hasMore: items.length === limit };
}

module.exports = { media, counts, SOURCES };