-- @query feedback_appdata_lists
-- @db listSync 1.0
SELECT
    l.listID,
    l.siteID,
    l.title,
    l.templateType,
    l.flags,
    l.lastSyncTime,
    l.lastUnsyncReason,
    l.lastResyncStartTime,
    l.lastResyncEndTime,
    l.lastResyncReason,
    COALESCE(d.conflictCount, 0) AS conflictCount,
    COALESCE(d.changeCount, 0) AS changeCount,
    CASE WHEN EXISTS (
        SELECT 1 FROM listSync.a2od_mountpoints am
        WHERE am.siteId = l.siteID AND am.listId = l.listID
    ) THEN 1 ELSE 0 END AS isInA2ODMountpoints
FROM listSync.lists l
LEFT JOIN listSync.list_sync_details d
       ON d.listID = l.listID AND d.siteID = l.siteID