`db/seed/flyers.js`

172 lines
'use strict';
/**
 * Seed creative_assets for RENTV flyer files.
 *
 * Per §22: check for the two flyer files. They are expected to be absent
 * (confirmed absent at /Users/macstudio3/Downloads/ and /mnt/data/).
 * When absent: insert GENERATED_PLACEHOLDER creative_assets rows and an
 * audit_log entry requesting admin upload.
 *
 * When present: copy to public/seed/rentv/, compute sha256 + image dimensions,
 * label AUTHORIZED_RENTV_ASSET.
 *
 * Idempotent: checks by file_name before inserting.
 */

const fs = require('fs');
const path = require('path');
const crypto = require('crypto');

const FLYER_SEARCH_PATHS = [
  '/mnt/data',
  '/Users/macstudio3/Downloads',
];

const FLYERS = [
  {
    file_name: 'Property Spotlight Flyer REV April 2025.jpg',
    mime_type: 'image/jpeg',
    alt_text:
      'RENTV Property Spotlight Flyer — revised April 2025. Shows RENTV property spotlight eblast product details and pricing.',
    product_key: 'property_spotlight_eblast',
    notes: 'April 2025 version of the RENTV Property Spotlight product flyer.',
  },
  {
    file_name: 'Corp Flyer Apr 2026 V3.jpg',
    mime_type: 'image/jpeg',
    alt_text:
      'RENTV Corporate Flyer — April 2026 Version 3. Shows RENTV advertising product suite, audience figures, and updated rates.',
    product_key: 'website_banner',
    notes: 'April 2026 v3 RENTV corporate media kit flyer.',
  },
];

function findFlyer(fileName) {
  for (const dir of FLYER_SEARCH_PATHS) {
    const full = path.join(dir, fileName);
    if (fs.existsSync(full)) return full;
  }
  return null;
}

function sha256File(filePath) {
  const data = fs.readFileSync(filePath);
  return crypto.createHash('sha256').update(data).digest('hex');
}

/** Parse JPEG dimensions from header bytes (minimal, no external deps). */
function jpegDimensions(filePath) {
  try {
    const fd = fs.openSync(filePath, 'r');
    const buf = Buffer.alloc(65536);
    fs.readSync(fd, buf, 0, 65536, 0);
    fs.closeSync(fd);
    // Scan for SOF0/SOF1/SOF2 markers (0xFFC0, 0xFFC1, 0xFFC2)
    for (let i = 0; i < buf.length - 8; i++) {
      if (buf[i] === 0xff && (buf[i + 1] === 0xc0 || buf[i + 1] === 0xc1 || buf[i + 1] === 0xc2)) {
        const height = buf.readUInt16BE(i + 5);
        const width = buf.readUInt16BE(i + 7);
        return { width, height };
      }
    }
  } catch (_) {
    // ignore
  }
  return { width: null, height: null };
}

async function seedFlyers(client) {
  let inserted = 0;
  let skipped = 0;
  let placeholders = 0;

  // Ensure public/seed/rentv/ directory exists (relative to project root)
  const PROJECT_ROOT = path.resolve(__dirname, '../..');
  const publicSeedDir = path.join(PROJECT_ROOT, 'public', 'seed', 'rentv');
  if (!fs.existsSync(publicSeedDir)) {
    fs.mkdirSync(publicSeedDir, { recursive: true });
  }

  for (const flyer of FLYERS) {
    // Idempotency check
    const existing = await client.query(
      `SELECT id FROM creative_assets WHERE file_name=$1 LIMIT 1`,
      [flyer.file_name]
    );
    if (existing.rows.length > 0) {
      skipped++;
      continue;
    }

    const foundPath = findFlyer(flyer.file_name);

    if (foundPath) {
      // File present — compute checksum and dims, copy to public/seed/rentv/
      const destPath = path.join(publicSeedDir, flyer.file_name);
      fs.copyFileSync(foundPath, destPath);

      const checksum = sha256File(foundPath);
      const { width, height } = jpegDimensions(foundPath);
      const objectKey = `seed/rentv/${flyer.file_name}`;

      await client.query(
        `INSERT INTO creative_assets
           (file_name, mime_type, width, height, checksum, object_key,
            capture_method, rights_status, captured_at, alt_text)
         VALUES ($1,$2,$3,$4,$5,$6,'MANUAL_UPLOAD','EXPORT_ALLOWED',now(),$7)`,
        [flyer.file_name, flyer.mime_type, width, height, checksum, objectKey, flyer.alt_text]
      );

      const alFoundExist = await client.query(
        `SELECT id FROM audit_logs WHERE action='SEED_ASSET_FOUND' AND detail->>'file_name'=$1 LIMIT 1`,
        [flyer.file_name]
      );
      if (alFoundExist.rows.length === 0) {
        await client.query(
          `INSERT INTO audit_logs (action, entity_table, actor, detail)
           VALUES ('SEED_ASSET_FOUND','creative_assets','seed',$1::jsonb)`,
          [JSON.stringify({ file_name: flyer.file_name, checksum, width, height, object_key: objectKey })]
        );
      }
      inserted++;
    } else {
      // File absent — create GENERATED_PLACEHOLDER
      await client.query(
        `INSERT INTO creative_assets
           (file_name, mime_type, capture_method, rights_status, alt_text)
         VALUES ($1,$2,'GENERATED_PLACEHOLDER','INTERNAL_EVIDENCE_ONLY',$3)`,
        [flyer.file_name, flyer.mime_type, flyer.alt_text]
      );
      placeholders++;

      const alMissExist = await client.query(
        `SELECT id FROM audit_logs WHERE action='SEED_ASSET_MISSING' AND detail->>'file_name'=$1 LIMIT 1`,
        [flyer.file_name]
      );
      if (alMissExist.rows.length === 0) {
        await client.query(
          `INSERT INTO audit_logs (action, entity_table, actor, detail)
           VALUES ('SEED_ASSET_MISSING','creative_assets','seed',$1::jsonb)`,
          [
            JSON.stringify({
              file_name: flyer.file_name,
              note:
                'Flyer asset not found at expected paths (' +
                FLYER_SEARCH_PATHS.join(', ') +
                '). Admin upload card required. ' +
                flyer.notes,
              admin_action_required: true,
              searched_paths: FLYER_SEARCH_PATHS,
              product_key: flyer.product_key,
            }),
          ]
        );
      }
    }
  }

  return { inserted, skipped, placeholders };
}

module.exports = { seedFlyers };