-- ------------------------------------------------------------
-- Migration 004: Super Admin + granular admin permissions.
-- Run this ONLY if you already have an existing ltda_association
-- database from before this feature was added. Fresh installs
-- using schema.sql already include these.
-- ------------------------------------------------------------

ALTER TABLE users
    ADD COLUMN is_super_admin TINYINT(1) NOT NULL DEFAULT 0 AFTER role;

CREATE TABLE IF NOT EXISTS admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    permission_key VARCHAR(50) NOT NULL,
    granted_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_permission (user_id, permission_key),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Make every existing admin a super admin so nobody is locked out of
-- anything they could already do before this migration ran. Once this
-- is applied, sign in as one of them and use Admin > Admins & Permissions
-- to hand out narrower roles to the rest of your team going forward.
UPDATE users SET is_super_admin = 1 WHERE role = 'admin';