M-Project/backend/migrations/011_add_permission_system.sql

-- 권한 시스템 개선 마이그레이션
-- 새로운 사용자 역할 추가 및 개별 권한 테이블 생성

-- 1. 새로운 사용자 역할 추가
ALTER TYPE userrole ADD VALUE 'super_admin';
ALTER TYPE userrole ADD VALUE 'manager';

-- 2. 사용자별 개별 권한 테이블 생성
CREATE TABLE IF NOT EXISTS user_permissions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission VARCHAR(50) NOT NULL,
    granted BOOLEAN DEFAULT TRUE,
    granted_by_id INTEGER REFERENCES users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    revoked_at TIMESTAMP WITH TIME ZONE,
    notes TEXT,
    UNIQUE(user_id, permission)
);

-- 3. 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_user_permissions_user_id ON user_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_permissions_permission ON user_permissions(permission);
CREATE INDEX IF NOT EXISTS idx_user_permissions_granted ON user_permissions(granted);

-- 4. 기본 권한 설정 (기존 관리자에게 super_admin 권한 부여)
UPDATE users SET role = 'super_admin' WHERE username = 'hyungi';

-- 5. 권한 확인 함수 생성
CREATE OR REPLACE FUNCTION check_user_permission(p_user_id INTEGER, p_permission VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    user_role userrole;
    has_permission BOOLEAN := FALSE;
BEGIN
    -- 사용자 역할 가져오기
    SELECT role INTO user_role FROM users WHERE id = p_user_id AND is_active = TRUE;
    
    IF user_role IS NULL THEN
        RETURN FALSE;
    END IF;
    
    -- super_admin은 모든 권한 보유
    IF user_role = 'super_admin' THEN
        RETURN TRUE;
    END IF;
    
    -- 개별 권한 확인
    SELECT granted INTO has_permission 
    FROM user_permissions 
    WHERE user_id = p_user_id 
      AND permission = p_permission 
      AND granted = TRUE 
      AND revoked_at IS NULL;
    
    -- 개별 권한이 없으면 역할 기반 기본 권한 확인
    IF has_permission IS NULL THEN
        -- 기본 권한 매트릭스
        CASE 
            WHEN p_permission IN ('issues.create', 'issues.view') THEN
                has_permission := TRUE; -- 모든 사용자
            WHEN p_permission IN ('issues.edit', 'issues.review', 'daily_work.create', 'daily_work.view', 'daily_work.edit') THEN
                has_permission := user_role IN ('admin', 'manager'); -- 관리자, 매니저
            WHEN p_permission IN ('projects.create', 'projects.edit', 'issues.delete', 'daily_work.delete') THEN
                has_permission := user_role = 'admin'; -- 관리자만
            WHEN p_permission IN ('projects.delete', 'users.create', 'users.edit', 'users.delete', 'users.change_role') THEN
                has_permission := user_role = 'super_admin'; -- 최고 관리자만
            ELSE
                has_permission := FALSE;
        END CASE;
    END IF;
    
    RETURN COALESCE(has_permission, FALSE);
END;
$$ LANGUAGE plpgsql;

-- 6. 권한 부여 함수 생성
CREATE OR REPLACE FUNCTION grant_user_permission(
    p_user_id INTEGER, 
    p_permission VARCHAR, 
    p_granted_by_id INTEGER,
    p_notes TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
BEGIN
    INSERT INTO user_permissions (user_id, permission, granted, granted_by_id, notes)
    VALUES (p_user_id, p_permission, TRUE, p_granted_by_id, p_notes)
    ON CONFLICT (user_id, permission) 
    DO UPDATE SET 
        granted = TRUE,
        granted_by_id = p_granted_by_id,
        granted_at = NOW(),
        revoked_at = NULL,
        notes = p_notes;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 7. 권한 취소 함수 생성
CREATE OR REPLACE FUNCTION revoke_user_permission(
    p_user_id INTEGER, 
    p_permission VARCHAR, 
    p_revoked_by_id INTEGER,
    p_notes TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
BEGIN
    UPDATE user_permissions 
    SET granted = FALSE,
        revoked_at = NOW(),
        notes = p_notes
    WHERE user_id = p_user_id 
      AND permission = p_permission;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 8. 사용자 권한 목록 조회 뷰 생성
CREATE OR REPLACE VIEW user_permissions_view AS
SELECT 
    u.id as user_id,
    u.username,
    u.full_name,
    u.role,
    up.permission,
    up.granted,
    up.granted_at,
    up.revoked_at,
    granted_by.username as granted_by_username,
    up.notes
FROM users u
LEFT JOIN user_permissions up ON u.id = up.user_id
LEFT JOIN users granted_by ON up.granted_by_id = granted_by.id
WHERE u.is_active = TRUE
ORDER BY u.username, up.permission;