poshmanager/server/models/assetModel.js

import crypto from 'node:crypto';
import fs from 'node:fs';
import { config } from '../config.js';
import { db, id, now, visibleClause } from '../db.js';
import { camelAsset, camelAssetFolder, camelAssetLink } from './mappers.js';
import { extnameAny, isPathInside, normalizePackagePath, path, sanitizeFileName } from '../utils/pathUtils.js';

function inferKind(mimeType = '', fileName = '') {
  const mime = mimeType.toLowerCase();
  const ext = extnameAny(fileName);
  if (mime.startsWith('image/')) return 'image';
  if (mime.includes('zip') || ['.zip', '.intunewin', '.7z', '.rar'].includes(ext)) return 'archive';
  if (['.msi', '.msp', '.exe'].includes(ext)) return 'installer';
  if (['.ps1', '.psm1', '.psd1', '.cmd', '.bat'].includes(ext)) return 'script';
  if (['.json', '.xml', '.yaml', '.yml', '.config', '.ini', '.reg', '.admx', '.adml'].includes(ext)) return 'config';
  if (mime.startsWith('text/') || ['.txt', '.md', '.log'].includes(ext)) return 'text';
  if (mime === 'application/pdf' || ['.pdf', '.doc', '.docx'].includes(ext)) return 'document';
  return 'generic';
}

function lockerBucket(kind) {
  const map = {
    archive: 'packages',
    config: 'configs',
    document: 'documents',
    generic: 'generic',
    image: 'images',
    installer: 'packages',
    script: 'scripts',
    text: 'documents'
  };
  return map[kind] || 'generic';
}

function storagePathFor(assetId, kind, originalName) {
  return path.join(config.fileLockerDir, 'assets', lockerBucket(kind), assetId, sanitizeFileName(originalName));
}

function checksumFile(filePath) {
  return crypto.createHash('sha256').update(fs.readFileSync(filePath)).digest('hex');
}

function selectAssetById(assetId, userId) {
  return db.prepare(`
    SELECT a.*, f.name AS folder_name, g.name AS group_name
    FROM assets a
    LEFT JOIN asset_folders f ON f.id = a.folder_id
    LEFT JOIN groups g ON g.id = a.group_id
    WHERE a.id = ? AND ${visibleClause('a')}
  `).get(assetId, userId, userId);
}

export function listAssetFolders(userId) {
  return db.prepare(`
    SELECT f.*, g.name AS group_name
    FROM asset_folders f
    LEFT JOIN groups g ON g.id = f.group_id
    WHERE ${visibleClause('f')}
    ORDER BY f.name
  `).all(userId, userId).map(camelAssetFolder);
}

export function createAssetFolder(payload, userId) {
  const folderId = id('afld');
  db.prepare(`
    INSERT INTO asset_folders (id, parent_id, name, description, visibility, owner_user_id, group_id, created_by, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    folderId,
    payload.parentId || null,
    payload.name,
    payload.description || '',
    payload.visibility,
    userId,
    payload.visibility === 'group' ? payload.groupId || null : null,
    userId,
    now(),
    now()
  );
  return camelAssetFolder(db.prepare('SELECT * FROM asset_folders WHERE id = ?').get(folderId));
}

export function updateAssetFolder(folderId, payload, userId) {
  const existing = db.prepare(`SELECT * FROM asset_folders WHERE id = ? AND ${visibleClause()}`).get(folderId, userId, userId);
  if (!existing) return null;
  db.prepare(`
    UPDATE asset_folders
    SET parent_id = ?, name = ?, description = ?, visibility = ?, group_id = ?, updated_at = ?
    WHERE id = ?
  `).run(
    payload.parentId ?? existing.parent_id,
    payload.name || existing.name,
    payload.description ?? existing.description ?? '',
    payload.visibility || existing.visibility,
    (payload.visibility || existing.visibility) === 'group' ? payload.groupId ?? existing.group_id : null,
    now(),
    folderId
  );
  return camelAssetFolder(db.prepare('SELECT * FROM asset_folders WHERE id = ?').get(folderId));
}

export function deleteAssetFolder(folderId, userId) {
  db.prepare(`DELETE FROM asset_folders WHERE id = ? AND ${visibleClause()}`).run(folderId, userId, userId);
}

export function listAssets(userId) {
  return db.prepare(`
    SELECT a.*, f.name AS folder_name, g.name AS group_name
    FROM assets a
    LEFT JOIN asset_folders f ON f.id = a.folder_id
    LEFT JOIN groups g ON g.id = a.group_id
    WHERE ${visibleClause('a')}
    ORDER BY a.updated_at DESC, a.name
  `).all(userId, userId).map(camelAsset);
}

export function listExecutableAssets(userId) {
  return db.prepare(`
    SELECT a.*, f.name AS folder_name, g.name AS group_name
    FROM assets a
    LEFT JOIN asset_folders f ON f.id = a.folder_id
    LEFT JOIN groups g ON g.id = a.group_id
    WHERE ${visibleClause('a')}
    ORDER BY a.name
  `).all(userId, userId).map((row) => ({
    id: row.id,
    name: row.name,
    originalName: row.original_name,
    kind: row.kind,
    mimeType: row.mime_type,
    sizeBytes: row.size_bytes,
    checksum: row.checksum,
    packagePath: row.package_path || row.original_name,
    localPath: row.storage_path,
    token: `{{asset:${row.id}}}`
  }));
}

export function getAsset(assetId, userId) {
  const row = selectAssetById(assetId, userId);
  return row ? camelAsset(row) : null;
}

export function getAssetFile(assetId, userId) {
  const row = selectAssetById(assetId, userId);
  if (!row) return null;
  const resolved = path.resolve(row.storage_path);
  if (!isPathInside(config.fileLockerDir, resolved)) throw new Error('Asset file path is outside the configured FileLocker directory');
  return { asset: camelAsset(row), filePath: resolved };
}

export function createAssetFromUpload(file, payload, userId) {
  if (!file?.path) throw new Error('A multipart file field named "file" is required');
  const assetId = id('ast');
  const originalName = sanitizeFileName(payload.originalName || file.originalname);
  const mimeType = payload.mimeType || file.mimetype || 'application/octet-stream';
  const kind = inferKind(mimeType, originalName);
  const storagePath = storagePathFor(assetId, kind, originalName);
  const checksum = checksumFile(file.path);
  fs.mkdirSync(path.dirname(storagePath), { recursive: true });
  fs.renameSync(file.path, storagePath);

  db.prepare(`
    INSERT INTO assets (
      id, folder_id, name, original_name, description, kind, mime_type, size_bytes, checksum, storage_path, package_path,
      visibility, owner_user_id, group_id, created_by, created_at, updated_at
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    assetId,
    payload.folderId || null,
    payload.name || originalName,
    originalName,
    payload.description || '',
    kind,
    mimeType,
    file.size,
    checksum,
    storagePath,
    normalizePackagePath(payload.packagePath, originalName),
    payload.visibility,
    userId,
    payload.visibility === 'group' ? payload.groupId || null : null,
    userId,
    now(),
    now()
  );
  return getAsset(assetId, userId);
}

export function updateAsset(assetId, payload, userId) {
  const existing = db.prepare(`SELECT * FROM assets WHERE id = ? AND ${visibleClause()}`).get(assetId, userId, userId);
  if (!existing) return null;
  const visibility = payload.visibility || existing.visibility;
  db.prepare(`
    UPDATE assets
    SET folder_id = ?, name = ?, description = ?, package_path = ?, visibility = ?, group_id = ?, updated_at = ?
    WHERE id = ?
  `).run(
    payload.folderId ?? existing.folder_id,
    payload.name || existing.name,
    payload.description ?? existing.description ?? '',
    normalizePackagePath(payload.packagePath ?? existing.package_path, existing.original_name),
    visibility,
    visibility === 'group' ? payload.groupId ?? existing.group_id : null,
    now(),
    assetId
  );
  return getAsset(assetId, userId);
}

export function deleteAsset(assetId, userId) {
  const row = db.prepare(`SELECT * FROM assets WHERE id = ? AND ${visibleClause()}`).get(assetId, userId, userId);
  if (!row) return false;
  db.prepare('DELETE FROM assets WHERE id = ?').run(assetId);
  try {
    fs.rmSync(row.storage_path, { force: true });
  } catch {
    // Metadata deletion should not fail because an external cleanup already removed the blob.
  }
  return true;
}

export function listAssetLinks(assetId, userId) {
  const asset = selectAssetById(assetId, userId);
  if (!asset) return null;
  return db.prepare(`
    SELECT l.*, a.name AS asset_name
    FROM asset_links l
    LEFT JOIN assets a ON a.id = l.asset_id
    WHERE l.asset_id = ?
    ORDER BY l.created_at DESC
  `).all(assetId).map(camelAssetLink);
}

export function createAssetLink(assetId, payload, userId) {
  const asset = selectAssetById(assetId, userId);
  if (!asset) return null;
  const linkId = id('alnk');
  db.prepare(`
    INSERT INTO asset_links (id, asset_id, target_type, target_id, link_role, package_path, created_by, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    linkId,
    assetId,
    payload.targetType,
    payload.targetId,
    payload.linkRole,
    normalizePackagePath(payload.packagePath || asset.package_path || asset.original_name),
    userId,
    now()
  );
  return camelAssetLink(db.prepare(`
    SELECT l.*, a.name AS asset_name
    FROM asset_links l
    LEFT JOIN assets a ON a.id = l.asset_id
    WHERE l.id = ?
  `).get(linkId));
}

export function deleteAssetLink(linkId, userId) {
  const link = db.prepare(`
    SELECT l.id
    FROM asset_links l
    INNER JOIN assets a ON a.id = l.asset_id
    WHERE l.id = ? AND ${visibleClause('a')}
  `).get(linkId, userId, userId);
  if (!link) return false;
  db.prepare('DELETE FROM asset_links WHERE id = ?').run(linkId);
  return true;
}