DROP EVENT IF EXISTS `Archive Eloverblik states`;
CREATE DEFINER=`[user]`@`[server].%` EVENT `Archive Eloverblik states` ON SCHEDULE EVERY 24 HOUR STARTS '2022-01-01 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO 

INSERT INTO archive (entity_id,event_date,state)
SELECT DISTINCT
	A.entity_id,
	SUBSTR(attributes,LOCATE('Metering date":',attributes)+16,10) AS Dato,
    A.state
FROM
	states A
LEFT JOIN 
archive B on A.entity_id=B.entity_id AND SUBSTR(A.attributes,LOCATE('Metering date":',A.attributes)+16,10)=B.event_date
WHERE
	A.entity_id like 'sensor.eloverblik%'
    AND A.state not in ('unknown','unavailable','')
    AND B.entity_id is Null;

SET GLOBAL event_scheduler = ON;