-- ============================================================
-- Tap2Order Auth Migration
-- Populates `permissions` column in `roles` table from the
-- existing role_permissions + permissions tables.
-- Safe to re-run: uses idempotent operations.
-- ============================================================

-- 1. Populate permissions as JSON arrays from role_permissions join
--    Super Admin gets ["*"] (full access wildcard)
UPDATE `roles` r
INNER JOIN (
    SELECT rp.role_id,
           CONCAT('[', GROUP_CONCAT(CONCAT('"', p.name, '"')), ']') AS perm_json
    FROM `role_permissions` rp
    INNER JOIN `permissions` p ON rp.permission_id = p.id
    GROUP BY rp.role_id
) AS grouped ON r.id = grouped.role_id
SET r.permissions = CASE
    WHEN r.name = 'super_admin' THEN '["*"]'
    ELSE grouped.perm_json
END;

-- 2. For any roles without role_permissions entries, set empty array
UPDATE `roles` SET `permissions` = '[]' WHERE `permissions` IS NULL;
