-- Monzer requested fixes: added-by tracking + export/import support
-- Safe to run multiple times on MySQL 5.7+.

SET @has_items_added_by := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='items' AND COLUMN_NAME='added_by');
SET @sql := IF(@has_items_added_by=0, 'ALTER TABLE items ADD COLUMN added_by INT NULL AFTER account_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_occasions_added_by := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='occasions' AND COLUMN_NAME='added_by');
SET @sql := IF(@has_occasions_added_by=0, 'ALTER TABLE occasions ADD COLUMN added_by INT NULL AFTER notes', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Backfill old rows with account owner/admin when possible.
UPDATE items i
LEFT JOIN users u ON u.account_id=i.account_id AND (u.role_id=1 OR u.id IS NOT NULL)
SET i.added_by=COALESCE(i.added_by,u.id)
WHERE i.added_by IS NULL;

UPDATE occasions o
LEFT JOIN users u ON u.account_id=o.account_id AND (u.role_id=1 OR u.id IS NOT NULL)
SET o.added_by=COALESCE(o.added_by,u.id)
WHERE o.added_by IS NULL;
