-- --------------------------------------------
-- UPDATE FOR sod_workflow
-- because of legacy risks needs approval dates
-- These scripts create dates for existing
-- completed workflows that have been approved
--
-- these runs with SAFE MODE OFF
-- --------------------------------------------
SET SQL_SAFE_UPDATES = 0;

UPDATE sod_workflow
	JOIN sod_workflow_approval 
		ON sod_workflow_approval.handle = sod_workflow.handle
SET first_approval = (
	SELECT sod_workflow_approval.timestamp 
		FROM sod_workflow_approval
	WHERE 
		sod_workflow_approval.handle = sod_workflow.handle AND 
        sod_workflow_approval.action = 'approved'
    ORDER BY sod_workflow_approval.timestamp DESC LIMIT 1)
WHERE 
	sod_workflow.status = 'approved';

UPDATE sod_workflow
	JOIN sod_workflow_approval 
		ON sod_workflow_approval.handle = sod_workflow.handle
SET latest_approval = (
	SELECT sod_workflow_approval.timestamp 
		FROM sod_workflow_approval
	WHERE 
		sod_workflow_approval.handle = sod_workflow.handle AND 
        sod_workflow_approval.action = 'approved'
    ORDER BY sod_workflow_approval.timestamp DESC LIMIT 1)
WHERE 
	sod_workflow.status = 'approved';
	
SET SQL_SAFE_UPDATES = 1;