-- Migration: Add user roles
-- Date: 2025-01-XX
-- Description: Adds role column to users table for organization-level permissions

-- ===== Add role column to users table =====
-- Try to add column, ignore error if it already exists
ALTER TABLE users 
ADD COLUMN role ENUM('admin', 'member') DEFAULT 'member' AFTER company_id;

-- Try to add index, ignore error if it already exists
ALTER TABLE users 
ADD INDEX idx_role (role);

-- Set first user of each company as admin
-- This sets users as admin if they are the first user (by created_at) in their company
UPDATE users u1
SET role = 'admin'
WHERE u1.id IN (
  SELECT id FROM (
    SELECT u2.id
    FROM users u2
    INNER JOIN (
      SELECT company_id, MIN(created_at) as first_created_at
      FROM users
      GROUP BY company_id
    ) first_users ON u2.company_id = first_users.company_id 
      AND u2.created_at = first_users.first_created_at
  ) AS subquery
);

