photochamber-webserver/model/mediaService.js

const path = require('path');
const fs = require('fs');
const MetaStruct = require('../src/filetype/metaStruct.js').MetaStruct;
const MediaFileModel = require('./mediaItem.js').MediaFileModel;
const { MediaFileTagModel } = require('./mediaItemTag.js');
const { MediaFileMetaModel } = require('./mediaItemMeta.js');
const { AccessModel, ACCESS_TYPE, ACCESS_TO, ACCESS_GRANT } = require('./access.js');

function Media()
{
}

function MediaStruct(i) {
    this.path = i.path;
    this.fileName = path.parse(i.path).name;
    this.md5sum = i.md5sum;
    this.fixedSum = i.fixedSum;
    this.date = i.date;
    this.meta = {};
    this.tags = [];
    this.fixedTags = [];
    this.accessType = -1;
    this.version = i.version;
}

MediaStruct.prototype.pushMeta = function(key, value) {
    if (key && value && !this.meta[key]) {
        let type = '';
        let roTypes = [
            'photochamberImport', 'height', 'width', 'iso', 'focal',
            'fNumber', 'exposureTime', 'camera', 'lensModel',
            'exposureTimeStr', 'libraryPath', 'compression',
            'software', 'fileSize', 'geoHash', 'exposureProgram',
            'orientation'
        ];
        if (['photochamberImport', 'dateTime'].indexOf(key) >= 0)
            type = 'date';
        else if (['height', 'width', 'iso', 'focal', 'fNumber', 'exposureTime', 'orientation'].indexOf(key) >= 0)
            type = 'number';
        else if (['geoHash', 'gpsLocation'].indexOf(key) >= 0)
            type = 'geoData';
        else if (['artist', 'camera', 'lensModel', 'exposureTimeStr', 'libraryPath', 'compression',
                'software', 'geoCountry', 'geoAdmin', 'geoCity', 'exposureProgram'].indexOf(key) >= 0)
            type = 'string';
        else if (['fileSize'].indexOf(key) >= 0)
            type = 'octet';
        else console.log(`Unknown meta type ${key} (${value})`);
        this.meta[key] = {
            type: type,
            canWrite: roTypes.indexOf(key) === -1,
            value: value
        };
    }
}

MediaStruct.prototype.pushTag = function(tag, isFixedTag) {
    if (!tag)
        return;
    if (!isFixedTag && this.tags.indexOf(tag) === -1)
        this.tags.push(tag);
    if (isFixedTag && this.fixedTags.indexOf(tag) === -1)
        this.fixedTags.push(tag);
}

MediaStruct.prototype.computeAccess = function(accessList) {
    if (this.accessType > -1)
        return this.accessType;
    if (!fs.existsSync(this.path))
        return this.accessType = ACCESS_GRANT.none;
    const checkTag = function(tags, access) {
        if (!tags.length)
            return false;
        for (let i of tags)
            if (i.startsWith(access.accessToData+'/') || i === access.accessToData)
                return true;
        return false;
    }
    const checkMeta = function(metas, access) {
        if (!access.accessToDataDeserialized)
            return false;
        let metaKey = Object.keys(access.accessToDataDeserialized)[0];
        let meta = metas[metaKey]?.value;
        return meta && metaKey && meta == access.accessToDataDeserialized[metaKey];
    }
    this.accessType = ACCESS_GRANT.none;
    for (let i of accessList) {
        if (i.accessTo === ACCESS_TO.everything ||
            (i.accessTo === ACCESS_TO.item && i.accessToData === this.fixedSum) ||
            (i.accessTo === ACCESS_TO.meta && checkMeta(this.meta, i)) ||
            (i.accessTo === ACCESS_TO.tag && checkTag([].concat(this.fixedTags, this.tags), i))) {
            if (i.grant === ACCESS_GRANT.write)
                return this.accessType = ACCESS_GRANT.write;
            if (i.grant === ACCESS_GRANT.read ||
                (i.grant === ACCESS_GRANT.readNoMeta && this.accessType === ACCESS_GRANT.none))
                this.accessType = i.grant;
        }
    }
    return this.accessType;
}

MediaStruct.prototype.HaveAccess = function(accessList) {
    return this.computeAccess(accessList) > 0;
}

async function buildAccessList(app, accessIds) {
    accessIds = Object.keys(accessIds || {}).reduce((acc, i) => {
        accessIds[i].linkId && acc.links.push(accessIds[i].linkId);
        accessIds[i].ldapDn && acc.ldap.push(accessIds[i].ldapDn);
        accessIds[i].email && acc.emails.push(accessIds[i].email);
        return acc;
    }, {links:[], emails: [], ldap: []});
    accessIds.accData = [].concat(accessIds.ldap, accessIds.emails, accessIds.links);
    accessIds.links = accessIds.links.map(x => '?').join(',');
    accessIds.emails = accessIds.emails.map(x => '?').join(',');
    accessIds.ldap = accessIds.ldap.map(x => '?').join(',');
    let accessList = (await app.databaseHelper.runSql(`select * from access where (
        (type=${ACCESS_TYPE.ldapAccount} AND typeData in (${accessIds.ldap})) OR
        (type=${ACCESS_TYPE.email} AND typeData in (${accessIds.emails})) OR
        (type=${ACCESS_TYPE.link} AND typeData in (${accessIds.links})) OR
        type=${ACCESS_TYPE.everyOne}
    )`, accessIds.accData)).map(data => {
        let result = new AccessModel;
        result.fromDb(data);
        return result;
    });
    return (accessList || []).filter(i => i.grant !== ACCESS_GRANT.none);
}

function reduceReqToMediaStruct(acc, i) {
    let obj = acc[i.fixedSum] = acc[i.fixedSum] || new MediaStruct(i);
    obj.pushMeta(i.metaKey, i.metaValue);
    obj.pushTag(i.mediaTag, i.isFixedTag);
    return acc;
}

module.exports.fetchMultiple = async function(app, md5sums, accessList, maxVersion) {
    let result = ((await app.databaseHelper.runSql(`
        select mediaFile.path, mediaFile.md5sum, mediaFile.date, mediaFile.fixedSum,
        mediaMeta.key as metaKey, mediaMeta.value as metaValue,
        mediaTag.tag as mediaTag, (mediaTag.fromMeta or mediaTag.fromAutotag) as isFixedTag,
        mediaFile.version
        from mediaFile
        left join mediaMeta on mediaMeta.md5sum=mediaFile.fixedSum
        left join mediaTag on mediaTag.md5sum=mediaFile.fixedSum
        where mediaFile.inaccessible=0 and mediaFile.version>? and mediaFile.fixedSum in (` +md5sums.map(x => '?').join(',')+`)`, [maxVersion].concat(md5sums))) || []).reduce(reduceReqToMediaStruct, {}) || null;
    accessList = await buildAccessList(app, accessList);
    for (let key in result)
        if (!result[key].HaveAccess(accessList))
            delete result[key];
    return result;
}

module.exports.fetchOne = async function(app, md5sum, accessList, maxVersion) {
    return (await module.exports.fetchMultiple(app, [md5sum], accessList, maxVersion))[md5sum] || null;
}

function fetchAccessSubQuery(args, access) {
    let result = [];
    if (access.filter(i => i.accessTo === ACCESS_TO.everything).length) return "1=1";
    for (let i of access) {
        if (i.accessTo === ACCESS_TO.item) {
            result.push(`fixedSum=?`);
            args.push(i.accessToData);
        }
        else if (i.accessTo === ACCESS_TO.meta && i.accessToDataDeserialized) {
            result.push('(mediaMeta.key=? and mediaMeta.value=?)');
            const metaKey = Object.keys(i.accessToDataDeserialized)[0];
            args.push(metaKey);
            args.push(i.accessToDataDeserialized[metaKey]);
        }
        else if (i.accessTo === ACCESS_TO.tag && i.accessToData) {
            result.push('(mediaTag.tag=? or mediaTag.tag like ?)');
            args.push(i.accessToData);
            args.push(`${i.accessToData}/%`);
        }
    }
    return result.join(" or ") || "1=0";
}

function fetchMediasBuildSubQuery(startTs, count, access, maxVersion) {
    let query = "select distinct fixedSum from mediaFile";
    let whereClause = ` where inaccessible=0 and version>?`;
    let accessWhere = null;
    let args = [maxVersion];

    // access filter
    if (access !== undefined) {
        accessWhere = fetchAccessSubQuery(args, access);

        // join
        if (accessWhere)
            query += ` left join mediaMeta on mediaMeta.md5sum=mediaFile.fixedSum` +
                ` left join mediaTag on mediaTag.md5sum=mediaFile.fixedSum`;

        if (accessWhere)
            whereClause += ` and (${accessWhere})`;
    }

    // date filter
    if (startTs) {
        whereClause += ' and date <?'
        args.push(startTs);
    }

    // order and limit
    query += whereClause + " order by date desc ";

    if (count > 0) {
        query += "limit ?";
        args.push(count);
    }

    return {
        query: query,
        args: args
    };
}

async function removeMeta(app, mediaFixedSums, key) {
    await app.databaseHelper.remove(MediaFileMetaModel, { md5sum: mediaFixedSums, key: key, fromFile: 0 });
}

async function updateMeta(app, mediaFixedSums, key, newValue) {
    await removeMeta(app, mediaFixedSums, key);
    if (newValue) {
        try {
            await app.databaseHelper.insertMultipleSameTable(mediaFixedSums.map(x => new MediaFileMetaModel(x, key, newValue, false)));
        }
        catch (err) {
            console.error(err);
            return false;
        }
    }
    if (key === 'gpsLocation') {
        try {
            let metaStruct = new MetaStruct();
            let geoLatLng = JSON.parse(newValue);
            await metaStruct.setGPSInfoFromLatLng(geoLatLng[0], geoLatLng[1]);
            await updateMeta(app, mediaFixedSums, 'geoHash', metaStruct.geoHash);
            await updateMeta(app, mediaFixedSums, 'geoCountry', metaStruct.geoCountry);
            await updateMeta(app, mediaFixedSums, 'geoCity', metaStruct.geoCity);
            await updateMeta(app, mediaFixedSums, 'geoAdmin', metaStruct.geoAdmin);
        } catch (err) {
            console.error(err);
            return false;
        }
    }
    return true;
}

module.exports.updateMeta = async function(app, mediaFixedSums, key, newValue) {
    await Promise.all(mediaFixedSums.map(x => module.exports.updateVersionInDb(app, x)));
    return await updateMeta(app, mediaFixedSums, key, newValue);
}

module.exports.removeMedia = async function(app, media) {
    await app.databaseHelper.remove(MediaFileTagModel, { md5sum: media.fixedSum });
    await app.databaseHelper.remove(MediaFileMetaModel, { md5sum: media.fixedSum });
    await app.databaseHelper.remove(MediaFileModel, { fixedSum: media.fixedSum });
    await app.libraryManager.removeMedia(media.path);
}

module.exports.fetchMedias = async function(app, startTs, count, access, maxVersion) {
    let subQuery = fetchMediasBuildSubQuery(startTs, count, access, maxVersion);
    let result = ((await app.databaseHelper.runSql(`
        select mediaFile.path, mediaFile.md5sum, mediaFile.date, mediaFile.fixedSum,
        mediaMeta.key as metaKey, mediaMeta.value as metaValue,
        mediaTag.tag as mediaTag, (mediaTag.fromMeta or mediaTag.fromAutotag) as isFixedTag,
        mediaFile.version
        from mediaFile
        left join mediaMeta on mediaMeta.md5sum=mediaFile.fixedSum
        left join mediaTag on mediaTag.md5sum=mediaFile.fixedSum
        where mediaFile.fixedSum in (${subQuery.query})`, subQuery.args)) || [])
    .reduce(reduceReqToMediaStruct, {});

    result = Object.keys(result).map(i => result[i]).sort((a, b) => b.date-a.date);
    return result;
};

module.exports.fetchMediasSumWithAccess = async function(app, access) {
    const subQuery = fetchMediasBuildSubQuery(0, -1, await buildAccessList(app, access), 0);
    return (await app.databaseHelper.runSql(subQuery.query, subQuery.args)).map(x => x.fixedSum);
};

module.exports.fetchMediasWithAccess = async function(app, startTs, count, access, maxVersion) {
    let result = [];
    let lastTs = startTs;

    access = await buildAccessList(app, access);
    while (result.length < count) {
        let tmp = await module.exports.fetchMedias(app, lastTs, 25, access, maxVersion);
        if (!tmp.length)
            return result;
        lastTs = tmp[tmp.length-1].date;
        tmp = tmp.filter(i => i.HaveAccess(access));
        if (tmp.length)
            result = result.concat(tmp);
    }
    return result.slice(0, count);
};

module.exports.updateVersionInDb = function(app, fixedSum) {
    return app.databaseHelper.rawUpdate(MediaFileModel.prototype, { fixedSum: fixedSum }, { version: Date.now() });
}

module.exports.getMediaRange = async function(app) {
    let result = await app.databaseHelper.runSql("select min(date) as _min, max(date) as _max, max(version) as _version from mediaFile");
    return {
        min: result?.[0]?._min || 0,
        max: result?.[0]?._max || 0,
        maxVersion: result?.[0]?._version || 0
    };
}