DROP VIEW IF EXISTS presenter_accesses;

CREATE ALGORITHM = UNDEFINED VIEW `presenter_accesses` AS

SELECT 
s.id AS store_id,
c.title AS customer_title,
c.subdomain AS subdomain,
s.title AS store_title,
s.store_identifier AS store_identifier,
s.user_agent AS user_agent,
FROM_UNIXTIME(MAX(se.modified)) AS last_modified

FROM store AS s

JOIN customer AS c ON c.id = s.customer_id
JOIN user AS u ON u.id = s.user_id
JOIN user_authentication_actions AS uaa ON uaa.email = u.email
JOIN `session` AS se ON se.id = uaa.session_id

WHERE s.user_agent IS NOT NULL
AND s.user_agent != ''

GROUP BY s.id

ORDER BY se.modified DESC
